MediaWiki API result
This is the HTML representation of the JSON format. HTML is good for debugging, but is unsuitable for application use.
Specify the format parameter to change the output format. To see the non-HTML representation of the JSON format, set format=json.
See the complete documentation, or the API help for more information.
{
"batchcomplete": "",
"continue": {
"gapcontinue": "Relational_and_non-relational_databases",
"continue": "gapcontinue||"
},
"warnings": {
"main": {
"*": "Subscribe to the mediawiki-api-announce mailing list at <https://lists.wikimedia.org/postorius/lists/mediawiki-api-announce.lists.wikimedia.org/> for notice of API deprecations and breaking changes."
},
"revisions": {
"*": "Because \"rvslots\" was not specified, a legacy format has been used for the output. This format is deprecated, and in the future the new format will always be used."
}
},
"query": {
"pages": {
"303": {
"pageid": 303,
"ns": 0,
"title": "Relational Databases",
"revisions": [
{
"contentformat": "text/x-wiki",
"contentmodel": "wikitext",
"*": "== General information ==\n\n ssh reldb@gks-virt(050-061).scc.kit.edu -p24\n\n[[File:agenda.pdf]]\n\n== Session 1 - Setup ==\n\n $ mysql -u root\n\n mysql> show databases;\n mysql> create database playground;\n mysql> show databases;\n\n CTRL-D\n\n $ sudo ls -la /var/lib/mysql/\n $ sudo ls -la /var/lib/mysql/playground\n $ sudo ls -la /var/lib/mysql/mysql\n\n $ mysql -u root\n\n mysql> use mysql;\n mysql> show tables;\n\n mysql> select * from user;\n mysql> create user 'joffrey' identified by 'kings_landing';\n mysql> create user 'joffrey'@'localhost' identified by 'kings_landing';\n mysql> create user 'hodor' identified by 'hodor';\n mysql> create user 'hodor'@'localhost' identified by 'hodor';\n mysql> select * from user;\n\n mysql> drop user 'joffrey';\n mysql> drop user 'hodor';\n\n mysql> select * from user;\n\n CTRL-D\n\n $ mysql -u hodor -p\n\n mysql> use playground;\n\n CTRL-D\n\n $ mysql -u root\n\n mysql> show grants;\n mysql> grant all privileges on playground.* to 'joffrey'@'localhost';\n\n CTRL-D\n\n $ mysql -u joffrey\n\n mysql> show grants;\n\n CTRL-D\n\n== Session 2 - CRUD ==\n\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> show engines;\n\n mysql> create table persons (\n name varchar(256),\n gender char(1)\n ) engine=innodb;\n\n mysql> describe persons;\n\n mysql> insert into persons (name, gender) values ('Eddard Stark', 'm');\n insert into persons (name, gender) values ('Catelyn Stark', 'f');\n insert into persons (name, gender) values ('Robb Stark', 'm');\n insert into persons (name, gender) values ('Jon Snow', 'm');\n insert into persons (name, gender) values ('Arya Stark', 'f');\n insert into persons (name, gender) values ('Tywin Lannister', 'm');\n insert into persons (name, gender) values ('Cersei Lannister', 'f');\n insert into persons (name, gender) values ('Jaime Lannister', 'm');\n insert into persons (name, gender) values ('Joffrey Baratheon', 'm');\n insert into persons (name, gender) values ('Tyrion Lannister', 'm');\n insert into persons (name, gender) values ('Daenerys Targaryen', 'f');\n insert into persons (name, gender) values ('Hodor', 'm');\n insert into persons (name, gender) values ('Ygritte', 'f');\n\n mysql> select * from persons;\n\n mysql> select * from persons where gender='m';\n\n mysql> select gender from persons where name='Hodor';\n\n mysql> select gender, count(*) from persons group by gender;\n\n mysql> select * from persons where name like '%Stark';\n\n mysql> select substring_index(name, ' ', 1) from persons;\n\n mysql> select substring_index(name, ' ', -1) from persons;\n\n mysql> create table persons_2 (\n first_name varchar(128),\n last_name varchar(128),\n noble_house varchar(128),\n gender char(1)\n ) engine=innodb;\n\n mysql> select substring_index(name, ' ', 1) as first_name,\n substring_index(name, ' ', -1) as last_name,\n gender\n from persons;\n\n mysql> insert into persons_2\n select substring_index(name, ' ', 1) as first_name,\n substring_index(name, ' ', -1) as last_name,\n gender\n from persons;\n\n mysql> insert into persons_2 (first_name,\n last_name,\n noble_house,\n gender)\n select substring_index(name, ' ', 1) as first_name,\n substring_index(name, ' ', -1) as last_name,\n substring_index(name, ' ', -1) as noble_house,\n gender\n from persons;\n\n mysql> drop table persons;\n\n mysql> alter table persons_2 rename persons;\n\n mysql> select * from persons;\n\n mysql> update persons set noble_house='Stark' where last_name='Snow';\n\n mysql> update persons set noble_house='Lannister' where last_name='Baratheon';\n\n mysql> update persons set last_name=NULL, noble_house=NULL where first_name in ('Hodor', 'Ygritte');\n\n mysql> select first_name from persons where noble_house='Stark' and gender='f';\n\n mysql> select noble_house, count(*) as members from persons group by noble_house order by members desc;\n\n mysql> delete from persons where first_name='Joffrey';\n\n== Session 3 - Relations ==\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> create table locations (\n name varchar(256),\n owner varchar(256)\n ) engine=innodb;\n\n mysql> insert into locations (name, owner) values ('Winterfell', 'Stark');\n insert into locations (name, owner) values ('Casterly Rock', 'Lannister');\n insert into locations (name, owner) values ('Kings Landing', 'Targaryen');\n insert into locations (name, owner) values ('Nights Watch', 'The Wall');\n insert into locations (name, owner) values ('Beyond the Wall', NULL);\n\n mysql> select *\n from persons, locations;\n\n mysql> select *\n from persons p, locations l\n where p.noble_house = l.owner;\n\n mysql> select p.first_name, p.last_name, l.name from persons p, locations l\n where p.noble_house = l.owner;\n\n mysql> select p.first_name, p.last_name, l.name\n from persons p left join locations l\n on p.noble_house = l.owner;\n\n mysql> select p.first_name, p.last_name, l.name\n from persons p right join locations l\n on p.noble_house = l.owner;\n\n mysql> select p1.first_name, p2.first_name\n from persons p1, persons p2\n where ((p1.gender='m' and p2.gender='f')\n and (p1.noble_house != p2.noble_house));\n\n mysql> create table forces (\n owner varchar(256),\n location varchar(256),\n swordmen int,\n archers int,\n catapults int\n ) engine=innodb;\n\n mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Winterfell', 1000, 1000, 10);\n insert into forces (owner, location, swordmen, archers, catapults) values ('Lannister', 'Casterly Rock', 1000, 1000, 10);\n insert into forces (owner, location, swordmen, archers, catapults) values ('Targaryen', 'Kings Landing', 1000, 1000, 10);\n insert into forces (owner, location, swordmen, archers, catapults) values (NULL, 'Beyond the Wall', 1000, 1000, 10);\n mysql> select * from persons, locations, forces;\n\n mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults\n from persons p, locations l, forces f\n where p.noble_house = l.owner\n and l.owner = f.owner;\n\n mysql> select distinct noble_house from persons;\n\n mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults\n from (select distinct noble_house from persons) p, locations l, forces f\n where p.noble_house = l.owner\n and l.owner = f.owner;\n\n mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Casterly Rock', 300, 10, 1);\n\n mysql> create view battleground as\n select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults\n from (select distinct noble_house from persons) p, locations l, forces f\n where p.noble_house = l.owner and l.owner = f.owner;\n\n mysql> create view noble_houses as\n select distinct noble_house from persons;\n\n mysql> create view battleground as\n select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults\n from noble_houses p, locations l, forces f\n where p.noble_house = l.owner\n and l.owner = f.owner;\n\n mysql> select b1.*\n from battleground b1, battleground b2\n where b1.family != b2.family\n and b1.battlefield = b2.battlefield;\n\n mysql> create view battle as (\n select b1.*\n from battleground b1, battleground b2\n where b1.family != b2.family\n and b1.battlefield = b2.battlefield\n );\n\n mysql> create view strength as\n select family, swordmen*1+archers*2+catapults*10 as battle_strength\n from battle;\n\n mysql> select *\n from strength\n where battle_strength = (select max(battle_strength)\n from strength);\n\n mysql> drop view strength;\n\n mysql> drop view battle;\n\n mysql> drop view battleground;\n\n mysql> drop view noble_houses;\n\n== Session 4 - Constraints ==\n\n $ mysql -u root\n\n mysql> SET @@global.sql_mode = \"STRICT_ALL_TABLES\";\n\n CTRL-D\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> insert into persons values ('First', 'Last', 'House', 'x');\n mysql> insert into persons values ('Second', 'Last', 'House', NULL);\n\n mysql> alter table persons modify gender enum('m', 'f');\n\n mysql> select * from persons;\n\n mysql> update persons set gender=NULL where gender='x';\n\n mysql> alter table persons modify gender enum('m', 'f');\n\n mysql> delete from persons where gender is null;\n\n mysql> alter table persons modify gender enum('m', 'f') not null;\n\n mysql> insert into persons values ('First', 'Last', 'House', 'x');\n\n mysql> alter table persons add constraint pk_persons primary key (first_name, last_name);\n\n mysql> alter table persons add column id int first;\n\n mysql> select * from persons;\n\n mysql> alter table persons drop column id;\n\n mysql> alter table persons add column id int primary key auto_increment first;\n\n mysql> select * from persons;\n\n mysql> alter table persons add unique uq_first_last (first_name, last_name);\n\n mysql> show create table persons;\n\n mysql> alter table locations add constraint pk_locations primary key(name);\n\n== Session 5 - Transactions ==\n\n TERMINAL 0:\n\n $ mysql -u root\n\n mysql> use playground;\n\n mysql> create table tx_test (name varchar(4), age integer) engine=innodb;\n\n mysql> insert into tx_test values ('jack', 28);\n insert into tx_test values ('jill', 25);\n\n mysql> select * from tx_test;\n\n mysql> select @@global.tx_isolation;\n\n mysql> set global transaction isolation level read uncommitted;\n\n --- DIRTY READ ---\n\n TERMINAL 1:\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> start transaction;\n\n mysql> select age from tx_test where name='jack';\n\n TERMINAL 2:\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> start transaction;\n\n mysql> update tx_test set age=21 where name='jack';\n\n TERMINAL 1:\n\n mysql> select age from tx_test where name='jack';\n\n TERMINAL 2:\n\n mysql> rollback;\n\n CTRL-D\n\n --- NON-REPEATABLE READ ---\n\n TERMINAL 0:\n\n mysql> set global transaction isolation level read committed;\n\n TERMINAL 1:\n\n mysql> start transaction;\n\n mysql> select * from tx_test where name='jack';\n\n TERMINAL 2:\n\n mysql> start transaction;\n\n mysql> update tx_test set age=29 where name='jack';\n\n mysql> commit;\n\n CTRL-D\n\n TERMINAL 1:\n\n mysql> select * from tx_test where name='jack';\n\n CTRL-D\n\n --- PHANTOM READ ---\n\n TERMINAL 0:\n\n mysql> set global transaction isolation level repeatable read;\n\n TERMINAL 1:\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> start transaction;\n\n mysql> select * from tx_test where age between 20 and 30;\n\n TERMINAL 2:\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> start transaction;\n\n mysql> insert into tx_test values ('bob', 23);\n\n mysql> commit;\n\n CTRL-D\n\n TERMINAL 1:\n\n mysql> select * from tx_test where age between 20 and 30;\n\n mysql> commit;\n\n mysql> select * from tx_test where age between 20 and 30;\n\n CTRL-D\n\n --- DEADLOCK ---\n\n TERMINAL 0:\n\n mysql> alter table tx_test add money integer;\n\n mysql> update tx_test set money=100;\n\n mysql> delete from tx_test where name='bob';\n\n mysql> set global transaction isolation level read committed;\n\n TERMINAL 1:\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> start transaction;\n\n TERMINAL 2:\n\n $ mysql -u joffrey -p\n\n mysql> use playground;\n\n mysql> start transaction;\n\n TERMINAL 1:\n\n mysql> update tx_test set money=money-50 where name='jack';\n\n TERMINAL 2:\n\n mysql> update tx_test set money=money-50 where name='jill';\n\n TERMINAL 1:\n\n mysql> update tx_test set money=money+50 where name='jill';\n\n TERMINAL 2:\n\n mysql> update tx_test set money=money+50 where name='jack';\n\n TERMINAL 1:\n\n mysql> commit;\n\n== Session 6 - SQL Injection ==\n\n $ mysql -u root -p\n \n mysql> use playground\n \n mysql> create table users (id integer, login varchar(255), password varchar(255)) engine=innodb;\n \n mysql> insert into users values (0, 'root', sha(concat('salt', 'hunter2')));\n \n mysql> select * from users where login='root' and password=sha(concat('salt','hunter2'));\n \n CTRL-D\n\n\n #!/usr/bin/python\n \n import MySQLdb\n import MySQLdb.cursors\n \n session = MySQLdb.connect(host=\"localhost\",\n user=\"joffrey\",\n passwd=\"kings_landing\",\n db=\"playground\",\n cursorclass = MySQLdb.cursors.SSCursor)\n cur = session.cursor()\n \n login = raw_input('login: ')\n password = raw_input('password: ')\n \n print \"select * from users where login=\\'%s\\' and password=sha(concat('salt', '%s'))\" % (login, password)\n \n cur.execute(\"select * from users where login=\\'%s\\' and password=sha(concat('salt', '%s'))\" % (login, password))\n \n if cur.fetchone() is not None:\n print 'logged in'\n else:\n print 'wrong login or password'\n \n cur.close()\n session.close()\n\n $ ./inject.py\n \n login: root\n password: ')) or 1=1;#\n \n $ ./inject.py\n \n login: root\n password: ')) or 1=1; update users set password=sha(concat('salt','mypass'));#\n\n== Session 7 - Indexes ==\n\n #!/usr/bin/python\n \n import random\n import time\n \n import MySQLdb\n \n con = MySQLdb.connect(host=\"localhost\",\n user=\"joffrey\",\n passwd=\"kings_landing\",\n db=\"playground\")\n con.autocommit(True)\n \n cur = con.cursor()\n \n try: cur.execute('drop table large_table')\n except: pass\n \n cur.execute('create table large_table (l char, s varchar(255), n int) engine=innodb')\n \n ts = time.time()\n for i in xrange(1000000):\n if i%10 == 0:\n print i\n cur.execute(\"INSERT INTO large_table VALUES ('%s', '%s', %i)\" % (chr(random.randrange(65, 91)),\n ''.join([chr(random.randrange(65, 91)) for i in xrange(1,16)]),\n random.randint(0,1000000)))\n print 'time taken:', time.time()-ts\n \n con.close()\n\n $ mysql -u joffrey -p\n \n mysql> use playground;\n \n mysql> select l, count(*) from large_table group by l;\n \n mysql> create index idx_l on large_table(l);\n \n mysql> select s, count(*) from large_table where s like 'AB%';\n \n mysql> create index idx_l on large_table(l);\n \n mysql> select s, count(*) from large_table where s like 'AB%';\n\n== Session 8 - Partitioning ==\n\n create table large_table (l char, s varchar(255), n int) engine=innodb\n partition by range (n) (\n partition p0 values less than (500000),\n partition p1 values less than maxvalue\n );\n\n create table large_table (l char, s varchar(255), n int) engine=innodb\n partition by hash(n)\n partitions 10;\n\n create table large_table (l char, s varchar(255), n int) engine=innodb\n partition by list(l) (\n partition p0 values in ('A', ... , 'L'),\n partition p1 values in ('M, ... , 'Z')\n );\n\n== Session 9 - Execution plans ==\n\n $ mysql -u joffrey -p\n \n mysql> use playground\n \n mysql> explain <any statement>\n\n== Session X - Application development ==\n\n /* gcc -Wall $(mysql_config --cflags --libs) basic.c -o basic */\n \n #include <stdio.h>\n #include <stdlib.h>\n \n #include <mysql.h>\n \n int main()\n {\n \n MYSQL *con = mysql_init(NULL);\n MYSQL_RES *cur;\n MYSQL_ROW row;\n int cols, i;\n \n mysql_real_connect(con,\n \"localhost\",\n \"joffrey\",\n \"kings_landing\",\n \"playground\",\n 0, NULL, 0);\n \n mysql_query(con, \"select * from persons\");\n \n cur = mysql_store_result(con);\n \n cols = mysql_num_fields(cur);\n while ((row = mysql_fetch_row(cur))) {\n for(i = 0; i < cols; i++) {\n printf(\"%s \", row[i] ? row[i] : \"<NULL>\");\n }\n printf(\"\\n\");\n }\n \n mysql_free_result(cur);\n mysql_close(con);\n \n return 0;\n }\n\n #!/usr/bin/python\n \n import MySQLdb\n import MySQLdb.cursors\n \n session = MySQLdb.connect(host=\"localhost\",\n user=\"joffrey\",\n passwd=\"kings_landing\",\n db=\"playground\",\n cursorclass = MySQLdb.cursors.SSCursor)\n cur = session.cursor()\n \n cur.execute('select * from persons')\n \n for row in cur.fetchall():\n for col in row:\n print col if col else '<NULL>',\n print\n \n cur.close()\n session.close()\n\n #!/usr/bin/python\n \n import MySQLdb\n import MySQLdb.cursors\n \n from multiprocessing import Process\n from random import randint\n from time import sleep\n \n def get_session():\n return MySQLdb.connect(host='localhost',\n user='joffrey',\n passwd='kings_landing',\n db='playground',\n cursorclass=MySQLdb.cursors.SSCursor)\n \n def bootstrap_tables():\n cur = get_session().cursor()\n try:\n print 'trying to create new table'\n cur.execute('create table mp_test (value int)')\n except:\n print 'table alread existed, truncating instead'\n cur.execute('truncate table mp_test')\n cur.close()\n \n def insert_rows():\n while True:\n print 'inserting'\n session = get_session()\n cur = session.cursor()\n cur.execute('insert into mp_test values (%s)', randint(0,9))\n session.commit()\n cur.close()\n session.close()\n sleep(0.1)\n \n def delete_rows():\n while True:\n print 'deleting'\n session = get_session()\n cur = session.cursor()\n cur.execute('delete from mp_test where value = %s', randint(0,9))\n session.commit()\n cur.close()\n session.close()\n sleep(0.1)\n \n def count_rows():\n while True:\n session = get_session()\n cur = session.cursor()\n cur.execute('select count(*) from mp_test')\n res = cur.fetchone()\n print 'rows', res[0]\n cur.close()\n session.close()\n sleep(1)\n \n if __name__ == '__main__':\n bootstrap_tables()\n p_inserter = Process(target=insert_rows)\n p_deleter = Process(target=delete_rows)\n p_counter = Process(target=count_rows)\n p_inserter.start()\n p_deleter.start()\n p_counter.start()\n p_inserter.join()\n p_deleter.join()\n p_counter.join()"
}
]
},
"376": {
"pageid": 376,
"ns": 0,
"title": "Relational and Non-relational Databases",
"revisions": [
{
"contentformat": "text/x-wiki",
"contentmodel": "wikitext",
"*": "= Overview =\nAuthor: Mario Lassnig, CERN PH-ADP-CO, [mailto:mario.lassnig@cern.ch]\n\n[[File:slides.pdf]]\n\n== PostgreSQL (Setup) ==\n\n vim /var/lib/pgsql/9.4/data/pg_hba.conf\n\nset all to trust\n\nadd line\n\n host all all 0.0.0.0/0 trust\n\n psql -U gridka01 gridka_db\n\nshould not ask for password\nctrl-d to exit\n\n sudo -u postgres /usr/pgsql-9.4/bin/psql -c \"CREATE ROLE gridka01 PASSWORD 'asdf1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;\"\n sudo -u postgres /usr/pgsql-9.4/bin/psql -c \"CREATE DATABASE gridka_db OWNER gridka01 ENCODING 'UTF8'\"\n sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c \"CREATE SCHEMA gridka_schema AUTHORIZATION gridka01\"\n sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c \"GRANT ALL ON SCHEMA gridka_schema TO gridka01\"\n sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c \"GRANT ALL ON DATABASE gridka_db TO gridka01\"\n\n vim /var/lib/pgsql/9.4/data/pg_hba.conf\n\nset all to md5\n\n systemctl restart postgresql-9.4.service\n\n psql -U gridka01 gridka_db\n\nshould ask for password\n\n ldconfig\n\n ls -la /var/lib/pgsql/9.4/data\n\n== PostgreSQL (SQL) ==\n\n psql -U gridka01 gridka_db\n\n\n\n \\l .. list all databases\n \\d .. list all relations\n\n\n\n CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR );\n\n \\d\n\n SELECT * FROM test_table;\n\n INSERT INTO test_table VALUES ('Vincent', 'Vega');\n\n SELECT * FROM test_table;\n\n INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');\n\n SELECT * FROM test_table;\n\n DELETE FROM test_table;\n\n SELECT * FROM test_table;\n\n DROP TABLE test_table;\n\n SELECT * FROM test_table;\n\n== PostgreSQL (C) ==\n\n <nowiki>\n#include <stdio.h>\n#include <stdlib.h>\n\n#include <libpq-fe.h>\n\nint main()\n{\n PGconn *conn;\n conn = PQconnectdb(\"dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'\");\n if (PQstatus(conn) != CONNECTION_OK) {\n printf(\"%s\\n\", PQerrorMessage(conn));\n exit(1);\n }\n\n PGresult *res;\n res = PQexec(conn, \"CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )\");\n if (!res)\n printf(\"%s\\n\", PQerrorMessage(conn));\n PQclear(res);\n\n res = PQexec(conn, \"INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )\");\n if (!res)\n printf(\"%s\\n\", PQerrorMessage(conn));\n PQclear(res);\n\n res = PQexec(conn, \"INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )\");\n if (!res)\n printf(\"%s\\n\", PQerrorMessage(conn));\n PQclear(res);\n\n res = PQexec(conn, \"SELECT * FROM test_table\");\n if (PQresultStatus(res) == PGRES_TUPLES_OK)\n for(int i = 0; i < PQntuples(res); ++i)\n printf(\"%s %s\\n\", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));\n else\n printf(\"%s\\n\", PQerrorMessage(conn));\n PQclear(res);\n\n res = PQexec(conn, \"DELETE FROM test_table\");\n if (!res)\n printf(\"%s\\n\", PQerrorMessage(conn));\n PQclear(res);\n\n PQfinish(conn);\n}\n</nowiki>\n\n gcc -g -std=c11 -Wall -Wextra -I$(/usr/pgsql-9.4/bin/pg_config --includedir) -L$(/usr/pgsql-9.4/bin/pg_config --libdir) -lpq test_pg.c -o test_pg\n\n== PostgreSQL (Python) ==\n\n <nowiki>\nimport psycopg2 as pg\nconn = pg.connect(\"dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'\")\n\ncur = conn.cursor()\n\ncur.execute(\"CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )\")\n\ncur.execute(\"INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )\")\nconn.commit()\n\ncur.execute(\"SELECT * FROM test_table\")\ncur.fetchall()\n\ncur.execute(\"INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )\")\nconn.commit()\n\ncur.execute(\"SELECT * FROM test_table\")\ncur.fetchall()\n\ncur.execute(\"DROP TABLE test_table\")\nconn.commit()\n</nowiki>\n\n== MonetDB (Setup) ==\n\n monetdbd create /tmp/gridka_schema\n monetdbd start /tmp/gridka_schema\n monetdb create gridka_db\n monetdb release gridka_db\n\n== MonetDB (SQL) ==\n\n mclient -u monetdb -d gridka_db (default password: monetdb)\n\n\n CREATE USER \"gridka01\" WITH PASSWORD 'asdf1234' NAME 'gridka01' SCHEMA \"sys\";\n CREATE SCHEMA \"gridka01\" AUTHORIZATION \"gridka01\";\n ALTER USER \"gridka01\" SET SCHEMA \"gridka01\";\n CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) );\n INSERT INTO test_table VALUES ('Vincent', 'Vega');\n INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');\n SELECT * FROM test_table;\n DELETE FROM test_table;\n DROP test_table;\n\n== MonetDB (C) ==\n\n <nowiki>\n#include <stdio.h>\n#include <stdlib.h>\n\n#include <mapi.h>\n\nint main()\n{\n Mapi db;\n db = mapi_connect(\"localhost\", 50000,\n \"gridka01\", \"asdf1234\",\n \"sql\", \"gridka_db\");\n if (mapi_error(db))\n mapi_explain(db, stderr);\n\n MapiHdl res;\n res = mapi_query(db, \"CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )\");\n if (mapi_error(db))\n mapi_explain(db, stderr);\n mapi_close_handle(res);\n\n res = mapi_query(db, \"INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )\");\n if (mapi_error(db))\n mapi_explain(db, stderr);\n mapi_close_handle(res);\n\n res = mapi_query(db, \"INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )\");\n if (mapi_error(db))\n mapi_explain(db, stderr);\n mapi_close_handle(res);\n\n res = mapi_query(db, \"SELECT * FROM test_table\");\n if (mapi_error(db))\n mapi_explain(db, stderr);\n while(mapi_fetch_row(res)) {\n printf(\"%s %s\\n\", mapi_fetch_field(res, 0), mapi_fetch_field(res, 1));\n }\n mapi_close_handle(res);\n\n res = mapi_query(db, \"DELETE FROM test_table\");\n if (mapi_error(db))\n mapi_explain(db, stderr);\n mapi_close_handle(res);\n\n mapi_destroy(db);\n}\n</nowiki>\n\n gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs monetdb-mapi) monetdb_test.c -o monetdb_test\n\n== MonetDB (Python) ==\n\n <nowiki>\nimport monetdb.sql as mo\nconn = mo.connect(database='gridka_db', username='gridka01', hostname='localhost', password='asdf1234')\n\ncur = conn.cursor()\n\ncur.execute(\"CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )\")\n\ncur.execute(\"INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )\")\nconn.commit()\n\ncur.execute(\"SELECT * FROM test_table\")\ncur.fetchall()\n\ncur.execute(\"INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )\")\nconn.commit()\n\ncur.execute(\"SELECT * FROM test_table\")\ncur.fetchall()\n\ncur.execute(\"DROP TABLE test_table\")\nconn.commit()\n</nowiki>\n\n== LevelDB (C) ==\n\n mkdir gridka_db\n\n <nowiki>\n#include <iostream>\n#include <string>\n\n#include <leveldb/db.h>\n\nint main()\n{\n leveldb::DB* db;\n leveldb::Options opts;\n leveldb::ReadOptions ropts;\n leveldb::WriteOptions wopts;\n leveldb::Status status;\n leveldb::Iterator* it;\n\n opts.create_if_missing = true;\n\n status = leveldb::DB::Open(opts, \"gridka_db/test_table\", &db);\n\n status = db->Put(wopts, \"0\", \"{\\\"first_name\\\": \\\"Vincent\\\", \\\"last_name\\\": \\\"Vega\\\"}\");\n status = db->Put(wopts, \"1\", \"{\\\"first_name\\\": \\\"Jules\\\", \\\"last_name\\\": \\\"Winnfield\\\"}\");\n\n std::cout << db->Get(ropts, std::string(\"0\"), &db) << '\\n';\n\n it = db->NewIterator(ropts);\n for(it->SeekToFirst(); it->Valid(); it->Next())\n std::cout << it->key().ToString() << '\\t' << it->value().ToString() << '\\n';\n delete it;\n\n status = db->Delete(wopts, \"0\");\n status = db->Delete(wopts, \"1\");\n\n delete db;\n}\n</nowiki>\n\n g++ -g -std=c++14 -Wall -Werror -pedantic -lleveldb leveldb_test.cpp -o leveldb_test\n\n== LevelDB (Python) ==\n\n <nowiki>\nimport json\nimport leveldb\ndb = leveldb.LevelDB('gridka_db/test_table')\ndb.Put('0', json.dumps({'first_name': 'Vincent', 'last_name': 'Vega'}))\ndb.Put('1', json.dumps({'first_name': 'Jules', 'last_name': 'Winnfield'}))\njson.loads(db.Get('0'))\nit = db.RangeIter()\n[(x[0],json.loads(x[1])) for x in it]\ndb.Delete('0')\ndb.Delete('1')\n</nowiki>\n\n== Redis (Setup) ==\n\n vim /etc/redis/redis.conf\n requirepass asdf1234\n service redis-server restart\n\n== Redis (Native) ==\n\n redis-cli\n auth asdf1234\n\n set test_table:0 '{\"first_name\": \"Vincent\", \"last_name\": \"Vega\"}'\n set test_table:1 '{\"first_name\": \"Jules\", \"last_name\": \"Winnfield\"}'\n\n get test_table:0\n\n hset test_table:0 first_name Vincent\n hset test_table:0 last_name Vega\n hset test_table:1 first_name Jules\n hset test_table:1 last_name Winnfield\n\n hget test_table:0 first_name\n hget test_table:1 last_name\n\n del test_table:0\n del test_table:1\n\n== Redis (C) ==\n\n <nowiki>\n#include <stdio.h>\n#include <stdlib.h>\n\n#include <hiredis.h>\n\nint main()\n{\n redisContext *ctx;\n redisReply *r;\n\n ctx = redisConnect(\"localhost\", 6379);\n if (ctx->err)\n printf(\"error: %s\\n\", ctx->errstr);\n\n r = redisCommand(ctx, \"auth asdf1234\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"hset test_table:0 first_name Vincent\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"hset test_table:0 last_name Vega\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"hset test_table:1 first_name Jules\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"hset test_table:1 last_name Winnfield\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"hget test_table:0 first_name\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n else\n printf(\"%s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"hget test_table:1 last_name\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n else\n printf(\"%s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"del test_table:0\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n r = redisCommand(ctx, \"del test_table:1\");\n if (r->type == REDIS_REPLY_ERROR)\n printf(\"error: %s\\n\", r->str);\n freeReplyObject(r);\n\n redisFree(ctx);\n}\n</nowiki>\n\n gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs hiredis) redis_test.c -o redis_test\n\n== Redis (Python) ==\n\n <nowiki>\nimport redis\ndb = redis.StrictRedis(password='asdf1234')\n\ndb.set('test_table:0', '{\"first_name\": \"Vincent\", \"last_name\": \"Vega\"}')\ndb.set('test_table:1', '{\"first_name\": \"Jules\", \"last_name\": \"Winnfield\"}')\n\ndb.hset('test_table:0', 'first_name', 'Vincent')\ndb.hset('test_table:0', 'last_name', 'Vega')\ndb.hset('test_table:1', 'first_name', 'Jules')\ndb.hset('test_table:1', 'last_name', 'Winnfield')\n\ndb.delete('test_table:0')\ndb.delete('test_table:1')\n</nowiki>\n\n== MongoDB (Setup) ==\n\n semanage port -a -t mongod_port_t -p tcp 27017\n systemclt restart mongod\n chkconfig mongod on\n\n mongo\n show dbs\n use admin\n db.createUser({\"user\": \"admin\", \"pwd\": \"admin\", \"roles\": [\"userAdminAnyDatabase\"]})\n use gridka_db\n show collections\n db.createUser({\"user\": \"gridka01\", \"pwd\": \"asdf1234\", \"roles\": [\"readWrite\"]})\n\n vim /etc/mongod.conf\n\nset auth=true\n\n service mongod restart\n\n== MongoDB (native) ==\n\n mongo gridka_db\n db.test_table.insert({\"first_name\": \"Vincent\", \"last_name\": \"Vega\"})\n mongo -u gridka01 -p asdf1234 gridka_db\n db.test_table.insert({\"first_name\": \"Vincent\", \"last_name\": \"Vega\"})\n db.test_table.insert({\"first_name\": \"Jules\", \"last_name\": \"Winnfield\"})\n db.test_table.find()\n db.test_table.insert({\"you_can_put\": \"whatever you want\"})\n db.test_table.find()\n db.test_table.drop()\n\n== MongoDB (C) ==\n\n <nowiki>\n#include <stdio.h>\n#include <stdlib.h>\n\n#include <bson.h>\n#include <mongoc.h>\n\nint main ()\n{\n mongoc_client_t *client;\n mongoc_collection_t *collection;\n bson_error_t error;\n bson_t *doc;\n bson_t *query;\n mongoc_cursor_t *cursor;\n char *str;\n\n mongoc_init();\n\n client = mongoc_client_new(\"mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db\");\n collection = mongoc_client_get_collection (client,\n \"gridka_db\",\n \"test_table\");\n\n doc = BCON_NEW(\"first_name\",BCON_UTF8(\"Vincent\"),\n \"last_name\", BCON_UTF8(\"Vega\"));\n if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE,\n doc, NULL, &error))\n printf(\"error: %s\\n\", error.message);\n bson_destroy(doc);\n doc = BCON_NEW(\"first_name\", BCON_UTF8(\"Jules\"),\n \"last_name\", BCON_UTF8(\"Winnfield\"));\n if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE,\n doc, NULL, &error))\n printf(\"error: %s\\n\", error.message);\n bson_destroy(doc);\n\n query = bson_new();\n cursor = mongoc_collection_find(collection, MONGOC_QUERY_NONE,\n 0, 0, 0, query, NULL, NULL);\n while(mongoc_cursor_next(cursor, &doc)) {\n str = bson_as_json(doc, NULL);\n printf(\"%s\\n\", str);\n bson_free(str);\n }\n bson_destroy(query);\n mongoc_cursor_destroy(cursor);\n\n if(!mongoc_collection_drop(collection, &error))\n printf(\"error: %s\\n\", error.message);\n\n mongoc_collection_destroy(collection);\n mongoc_client_destroy(client);\n}\n</nowiki>\n\n gcc -g -std=c11 -Wall -Wextra -pedantic $(pkg-config --cflags --libs libmongoc-1.0) mongo_test.c -o mongo_test\n\n== MongoDB (Python) ==\n\n <nowiki>\nfrom pymongo import MongoClient\nclient = MongoClient('mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db')\ndb = client.gridka_db\ndb.test_table.insert_one({'first_name': 'Vincent', 'last_name': 'Vega'})\ndb.test_table.insert_one({'first_name': 'Jules', 'last_name': 'Winnfield'})\n[x for x in db.test_table.find()]\ndb.test_table.insert({'first_name': 'Marcellus', 'occupation': 'businessman'})\n[x for x in db.test_table.find()]\ndb.test_table.drop()\n</nowiki>\n\n== Neo4j (Native) ==\n\n http://localhost:7474/browser/\n\n CREATE (id_0:pulp_fiction {first_name: 'Vincent', last_name: 'Vega'})\n CREATE (id_1:pulp_fiction {first_name: 'Jules', last_name: 'Winnfield'})\n CREATE (id_0)-[:TELLS_STORY {about: 'le big mac'}]->(id_1)\n CREATE (id_1)-[:TELLS_STORY {about: 'foot massage'}]->(id_0)\n\n MATCH (n) RETURN (n)\n MATCH (n) OPTIONAL MATCH (n)-[r]->() DELETE n,r\n\n== Neo4j (Python) ==\n\n <nowiki>\nfrom py2neo import Graph, Node, Relationship\n\ngraph = Graph(\"http://neo4j:asdf1234@localhost:7474/db/data/\")\n\nid_0 = Node(\"pulp_fiction\", first_name='Vincent', last_name='Vega')\nid_1 = Node(\"pulp_fiction\", first_name='Jules', last_name='Winnfield')\ngraph.create(id_0)\ngraph.create(id_1)\ngraph.create(Relationship(id_0, 'TELLS_STORY', id_1, about='le big mac'))\ngraph.create(Relationship(id_1, 'TELLS_STORY', id_0, about='foot massage'))\n</nowiki>\n\n== Query plans ==\n\n <nowiki>\n#!/usr/bin/python\n\nimport random\nimport time\n\nimport psycopg2 as pg\n\nconn = pg.connect(\"dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'\")\n\nconn.autocommit=False\ncur = conn.cursor()\ntry: cur.execute('drop table large_table')\nexcept: pass\nconn.commit()\ncur.execute('create table large_table (l char, s varchar(255), n int)')\nconn.commit()\nconn.autocommit=True\n\nts = time.time()\nfor i in xrange(1000000):\n if i%100 == 0:\n print i\n cur.execute(\"INSERT INTO large_table VALUES ('%s', '%s', %i)\" % (chr(random.randrange(65, 91)),\n ''.join([chr(random.randrange(65, 91)) for i in xrange(1,16)]),\n random.randint(0,1000000)))\nconn.commit()\nprint 'time taken:', time.time()-ts\n\nconn.close()\n</nowiki>\n\n set enable_seqscan=false;\n\n select l, count(*) from large_table group by l;\n select * from large_table where l='A';\n create index idx_l on large_table(l);\n\n <nowiki>\ncreate table large_table (l char, s varchar(255), n int)\n partition by range (n) (\n partition p0 values less than (500000),\n partition p1 values less than maxvalue\n);\n\n\ncreate table large_table (l char, s varchar(255), n int)\n partition by hash(n)\n partitions 10;\n\n\ncreate table large_table (l char, s varchar(255), n int)\n partition by list(l) (\n partition p0 values in ('A', ... , 'L'),\n partition p1 values in ('M, ... , 'Z')\n);\n</nowiki>\n\n== Transactions ==\n\n create table tx_test (name varchar(4), money integer);\n\n insert into tx_test values ('jack', 50);\n insert into tx_test values ('jill', 100);\n\n show transaction isolation level;\n\n\nt1:\n begin;\n select money from tx_test where name='jack';\nt2:\n begin;\n update tx_test set money=money-10 where name='jack';\n commit;\nt1:\n select money from tx_test where name='jack';\n\n\nt1:\n set session characteristics as transaction isolation level repeatable read;\nt2:\n set session characteristics as transaction isolation level repeatable read;\nt1:\n begin;\n select money from tx_test where name='jack';\nt2:\n begin;\n update tx_test set money=money-10 where name='jack';\n commit;\nt1:\n select money from tx_test where name='jack';\n update tx_test set money=money+10 where name='jack';\n\n\nt1:\n set session characteristics as transaction isolation level read committed;\nt2:\n set session characteristics as transaction isolation level read committed;\n\nt1:\n begin;\nt2:\n begin;\nt1:\n update tx_test set money=money-10 where name='jack';\nt2:\n update tx_test set money=money+10 where name='jill';\nt1:\n update tx_test set money=money+10 where name='jill';\nt2:\n update tx_test set money=money+10 where name='jack';\n\n\n== SQL Injection ==\n\n CREATE TABLE users (id integer, login varchar(255), password varchar(255));\n CREATE EXTENSION pgcrypto;\n INSERT INTO users VALUES (0, 'root', crypt('hunter2', 'sha1'));\n SELECT * FROM users;\n\n <nowiki>\n#!/usr/bin/python\n\nimport random\nimport time\n\nimport psycopg2 as pg\n\nconn = pg.connect(\"dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'\")\n\ncur = conn.cursor()\n\nlogin = raw_input('login: ')\npassword = raw_input('password: ')\n\nprint \"select * from users where login=\\'%s\\' and password=crypt('%s', 'sha1')\" % (login, password)\n\ncur.execute(\"select * from users where login=\\'%s\\' and password=crypt('%s', 'sha1')\" % (login, password))\n\nif cur.fetchone():\n print 'logged in'\nelse:\n print 'wrong login or password'\n\ncur.close()\nconn.close()\n</nowiki>\n\n <nowiki>\n python sql_inject.py\n root / hunter2\n root / ','sha1') or 1=1; update users set password=crypt('mypass', 'sha1'); commit; select 1;--\n</nowiki>\n\n== Multiprocess ==\n\n <nowiki>\n#!/usr/bin/python\n\nimport psycopg2 as pg\n\nfrom multiprocessing import Process\nfrom random import randint\nfrom time import sleep\n\ndef get_session():\n return pg.connect(\"dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'\")\n\ndef bootstrap_tables():\n session = get_session()\n cur = session.cursor()\n try:\n print 'trying to create new table'\n cur.execute('create table mp_test (value integer)')\n except:\n print 'table alread existed, truncating instead'\n cur.execute('truncate table mp_test')\n cur.close()\n session.commit()\n\ndef insert_rows():\n while True:\n print 'inserting'\n session = get_session()\n cur = session.cursor()\n cur.execute('insert into mp_test values (%s)', [randint(0,9)])\n session.commit()\n cur.close()\n session.close()\n sleep(0.1)\n\ndef delete_rows():\n while True:\n print 'deleting'\n session = get_session()\n cur = session.cursor()\n cur.execute('delete from mp_test where value = %s', [randint(0,9)])\n session.commit()\n cur.close()\n session.close()\n sleep(0.1)\n\ndef count_rows():\n while True:\n session = get_session()\n cur = session.cursor()\n cur.execute('select count(*) from mp_test')\n res = cur.fetchone()\n print 'rows', res[0]\n cur.close()\n session.close()\n sleep(1)\n\nif __name__ == '__main__':\n bootstrap_tables()\n p_inserter = Process(target=insert_rows)\n p_deleter = Process(target=delete_rows)\n p_counter = Process(target=count_rows)\n p_inserter.start()\n p_deleter.start()\n p_counter.start()\n p_inserter.join()\n p_deleter.join()\n p_counter.join()\n</nowiki>"
}
]
}
}
}
}