Difference between revisions of "Relational and Non-relational Databases"
From Gridkaschool
(Created page with "= Overview = Author: Mario Lassnig, CERN PH-ADP-CO, [mailto:mario.lassnig@cern.ch] In this workshop, the students will learn how to use relational and non-relational database...") |
(→LevelDB (C)) |
||
(8 intermediate revisions by the same user not shown) | |||
Line 2: | Line 2: | ||
Author: Mario Lassnig, CERN PH-ADP-CO, [mailto:mario.lassnig@cern.ch] |
Author: Mario Lassnig, CERN PH-ADP-CO, [mailto:mario.lassnig@cern.ch] |
||
+ | [[File:slides.pdf]] |
||
− | In this workshop, the students will learn how to use relational and non-relational databases to build multi-threaded applications. The focus of the workshop is to teach efficient, safe, and fault-tolerant principles when dealing with high-volume and high-throughput database scenarios. |
||
+ | == PostgreSQL (Setup) == |
||
− | A basic understanding of the following things is required: |
||
− | * A programming language (preferably Python or any C-like) |
||
− | * Basic SQL (CREATE, DROP, SELECT, UPDATE, DELETE) |
||
− | * Linux shell scripting (bash or zsh) |
||
+ | vim /var/lib/pgsql/9.4/data/pg_hba.conf |
||
− | The course will cover the following three topics: |
||
+ | set all to trust |
||
− | * When to use relational databases, and when not |
||
− | ** Relational primer |
||
− | ** Non-relational primer |
||
− | ** How to design the data model |
||
+ | add line |
||
− | * Using SQL for fun and profit |
||
− | ** Query plans and performance analysis |
||
− | ** Transactional safety in multi-threaded environments |
||
− | ** How to deal with large amounts of sparse metadata |
||
− | ** Competetive locking and selection strategies |
||
+ | host all all 0.0.0.0/0 trust |
||
− | * Building a fault-tolerant database application |
||
+ | |||
− | ** Distributed transactions across relational and non-relational databases |
||
+ | psql -U gridka01 gridka_db |
||
− | ** SQL injection and forceful breakage |
||
+ | |||
− | ** Application-level mitigation for unexpected database issues |
||
+ | should not ask for password |
||
+ | ctrl-d to exit |
||
+ | |||
+ | sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE ROLE gridka01 PASSWORD 'asdf1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;" |
||
+ | sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE DATABASE gridka_db OWNER gridka01 ENCODING 'UTF8'" |
||
+ | sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "CREATE SCHEMA gridka_schema AUTHORIZATION gridka01" |
||
+ | sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON SCHEMA gridka_schema TO gridka01" |
||
+ | sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON DATABASE gridka_db TO gridka01" |
||
+ | |||
+ | vim /var/lib/pgsql/9.4/data/pg_hba.conf |
||
+ | |||
+ | set all to md5 |
||
+ | |||
+ | systemctl restart postgresql-9.4.service |
||
+ | |||
+ | psql -U gridka01 gridka_db |
||
+ | |||
+ | should ask for password |
||
+ | |||
+ | ldconfig |
||
+ | |||
+ | ls -la /var/lib/pgsql/9.4/data |
||
+ | |||
+ | == PostgreSQL (SQL) == |
||
+ | |||
+ | psql -U gridka01 gridka_db |
||
+ | |||
+ | |||
+ | |||
+ | \l .. list all databases |
||
+ | \d .. list all relations |
||
+ | |||
+ | |||
+ | |||
+ | CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR ); |
||
+ | |||
+ | \d |
||
+ | |||
+ | SELECT * FROM test_table; |
||
+ | |||
+ | INSERT INTO test_table VALUES ('Vincent', 'Vega'); |
||
+ | |||
+ | SELECT * FROM test_table; |
||
+ | |||
+ | INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield'); |
||
+ | |||
+ | SELECT * FROM test_table; |
||
+ | |||
+ | DELETE FROM test_table; |
||
+ | |||
+ | SELECT * FROM test_table; |
||
+ | |||
+ | DROP TABLE test_table; |
||
+ | |||
+ | SELECT * FROM test_table; |
||
+ | |||
+ | == PostgreSQL (C) == |
||
+ | |||
+ | <nowiki> |
||
+ | #include <stdio.h> |
||
+ | #include <stdlib.h> |
||
+ | |||
+ | #include <libpq-fe.h> |
||
+ | |||
+ | int main() |
||
+ | { |
||
+ | PGconn *conn; |
||
+ | conn = PQconnectdb("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'"); |
||
+ | if (PQstatus(conn) != CONNECTION_OK) { |
||
+ | printf("%s\n", PQerrorMessage(conn)); |
||
+ | exit(1); |
||
+ | } |
||
+ | |||
+ | PGresult *res; |
||
+ | res = PQexec(conn, "CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )"); |
||
+ | if (!res) |
||
+ | printf("%s\n", PQerrorMessage(conn)); |
||
+ | PQclear(res); |
||
+ | |||
+ | res = PQexec(conn, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )"); |
||
+ | if (!res) |
||
+ | printf("%s\n", PQerrorMessage(conn)); |
||
+ | PQclear(res); |
||
+ | |||
+ | res = PQexec(conn, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )"); |
||
+ | if (!res) |
||
+ | printf("%s\n", PQerrorMessage(conn)); |
||
+ | PQclear(res); |
||
+ | |||
+ | res = PQexec(conn, "SELECT * FROM test_table"); |
||
+ | if (PQresultStatus(res) == PGRES_TUPLES_OK) |
||
+ | for(int i = 0; i < PQntuples(res); ++i) |
||
+ | printf("%s %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1)); |
||
+ | else |
||
+ | printf("%s\n", PQerrorMessage(conn)); |
||
+ | PQclear(res); |
||
+ | |||
+ | res = PQexec(conn, "DELETE FROM test_table"); |
||
+ | if (!res) |
||
+ | printf("%s\n", PQerrorMessage(conn)); |
||
+ | PQclear(res); |
||
+ | |||
+ | PQfinish(conn); |
||
+ | } |
||
+ | </nowiki> |
||
+ | |||
+ | 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 |
||
+ | |||
+ | == PostgreSQL (Python) == |
||
+ | |||
+ | <nowiki> |
||
+ | import psycopg2 as pg |
||
+ | conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") |
||
+ | |||
+ | cur = conn.cursor() |
||
+ | |||
+ | cur.execute("CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )") |
||
+ | |||
+ | cur.execute("INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )") |
||
+ | conn.commit() |
||
+ | |||
+ | cur.execute("SELECT * FROM test_table") |
||
+ | cur.fetchall() |
||
+ | |||
+ | cur.execute("INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )") |
||
+ | conn.commit() |
||
+ | |||
+ | cur.execute("SELECT * FROM test_table") |
||
+ | cur.fetchall() |
||
+ | |||
+ | cur.execute("DROP TABLE test_table") |
||
+ | conn.commit() |
||
+ | </nowiki> |
||
+ | |||
+ | == MonetDB (Setup) == |
||
+ | |||
+ | monetdbd create /tmp/gridka_schema |
||
+ | monetdbd start /tmp/gridka_schema |
||
+ | monetdb create gridka_db |
||
+ | monetdb release gridka_db |
||
+ | |||
+ | == MonetDB (SQL) == |
||
+ | |||
+ | mclient -u monetdb -d gridka_db (default password: monetdb) |
||
+ | |||
+ | |||
+ | CREATE USER "gridka01" WITH PASSWORD 'asdf1234' NAME 'gridka01' SCHEMA "sys"; |
||
+ | CREATE SCHEMA "gridka01" AUTHORIZATION "gridka01"; |
||
+ | ALTER USER "gridka01" SET SCHEMA "gridka01"; |
||
+ | CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) ); |
||
+ | INSERT INTO test_table VALUES ('Vincent', 'Vega'); |
||
+ | INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield'); |
||
+ | SELECT * FROM test_table; |
||
+ | DELETE FROM test_table; |
||
+ | DROP test_table; |
||
+ | |||
+ | == MonetDB (C) == |
||
+ | |||
+ | <nowiki> |
||
+ | #include <stdio.h> |
||
+ | #include <stdlib.h> |
||
+ | |||
+ | #include <mapi.h> |
||
+ | |||
+ | int main() |
||
+ | { |
||
+ | Mapi db; |
||
+ | db = mapi_connect("localhost", 50000, |
||
+ | "gridka01", "asdf1234", |
||
+ | "sql", "gridka_db"); |
||
+ | if (mapi_error(db)) |
||
+ | mapi_explain(db, stderr); |
||
+ | |||
+ | MapiHdl res; |
||
+ | res = mapi_query(db, "CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )"); |
||
+ | if (mapi_error(db)) |
||
+ | mapi_explain(db, stderr); |
||
+ | mapi_close_handle(res); |
||
+ | |||
+ | res = mapi_query(db, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )"); |
||
+ | if (mapi_error(db)) |
||
+ | mapi_explain(db, stderr); |
||
+ | mapi_close_handle(res); |
||
+ | |||
+ | res = mapi_query(db, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )"); |
||
+ | if (mapi_error(db)) |
||
+ | mapi_explain(db, stderr); |
||
+ | mapi_close_handle(res); |
||
+ | |||
+ | res = mapi_query(db, "SELECT * FROM test_table"); |
||
+ | if (mapi_error(db)) |
||
+ | mapi_explain(db, stderr); |
||
+ | while(mapi_fetch_row(res)) { |
||
+ | printf("%s %s\n", mapi_fetch_field(res, 0), mapi_fetch_field(res, 1)); |
||
+ | } |
||
+ | mapi_close_handle(res); |
||
+ | |||
+ | res = mapi_query(db, "DELETE FROM test_table"); |
||
+ | if (mapi_error(db)) |
||
+ | mapi_explain(db, stderr); |
||
+ | mapi_close_handle(res); |
||
+ | |||
+ | mapi_destroy(db); |
||
+ | } |
||
+ | </nowiki> |
||
+ | |||
+ | gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs monetdb-mapi) monetdb_test.c -o monetdb_test |
||
+ | |||
+ | == MonetDB (Python) == |
||
+ | |||
+ | <nowiki> |
||
+ | import monetdb.sql as mo |
||
+ | conn = mo.connect(database='gridka_db', username='gridka01', hostname='localhost', password='asdf1234') |
||
+ | |||
+ | cur = conn.cursor() |
||
+ | |||
+ | cur.execute("CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )") |
||
+ | |||
+ | cur.execute("INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )") |
||
+ | conn.commit() |
||
+ | |||
+ | cur.execute("SELECT * FROM test_table") |
||
+ | cur.fetchall() |
||
+ | |||
+ | cur.execute("INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )") |
||
+ | conn.commit() |
||
+ | |||
+ | cur.execute("SELECT * FROM test_table") |
||
+ | cur.fetchall() |
||
+ | |||
+ | cur.execute("DROP TABLE test_table") |
||
+ | conn.commit() |
||
+ | </nowiki> |
||
+ | |||
+ | == LevelDB (C) == |
||
+ | |||
+ | mkdir gridka_db |
||
+ | |||
+ | <nowiki> |
||
+ | #include <iostream> |
||
+ | #include <string> |
||
+ | |||
+ | #include <leveldb/db.h> |
||
+ | |||
+ | int main() |
||
+ | { |
||
+ | leveldb::DB* db; |
||
+ | leveldb::Options opts; |
||
+ | leveldb::ReadOptions ropts; |
||
+ | leveldb::WriteOptions wopts; |
||
+ | leveldb::Status status; |
||
+ | leveldb::Iterator* it; |
||
+ | |||
+ | opts.create_if_missing = true; |
||
+ | |||
+ | status = leveldb::DB::Open(opts, "gridka_db/test_table", &db); |
||
+ | |||
+ | status = db->Put(wopts, "0", "{\"first_name\": \"Vincent\", \"last_name\": \"Vega\"}"); |
||
+ | status = db->Put(wopts, "1", "{\"first_name\": \"Jules\", \"last_name\": \"Winnfield\"}"); |
||
+ | |||
+ | std::cout << db->Get(ropts, std::string("0"), &db) << '\n'; |
||
+ | |||
+ | it = db->NewIterator(ropts); |
||
+ | for(it->SeekToFirst(); it->Valid(); it->Next()) |
||
+ | std::cout << it->key().ToString() << '\t' << it->value().ToString() << '\n'; |
||
+ | delete it; |
||
+ | |||
+ | status = db->Delete(wopts, "0"); |
||
+ | status = db->Delete(wopts, "1"); |
||
+ | |||
+ | delete db; |
||
+ | } |
||
+ | </nowiki> |
||
+ | |||
+ | g++ -g -std=c++14 -Wall -Werror -pedantic -lleveldb leveldb_test.cpp -o leveldb_test |
||
+ | |||
+ | == LevelDB (Python) == |
||
+ | |||
+ | <nowiki> |
||
+ | import json |
||
+ | import leveldb |
||
+ | db = leveldb.LevelDB('gridka_db/test_table') |
||
+ | db.Put('0', json.dumps({'first_name': 'Vincent', 'last_name': 'Vega'})) |
||
+ | db.Put('1', json.dumps({'first_name': 'Jules', 'last_name': 'Winnfield'})) |
||
+ | json.loads(db.Get('0')) |
||
+ | it = db.RangeIter() |
||
+ | [(x[0],json.loads(x[1])) for x in it] |
||
+ | db.Delete('0') |
||
+ | db.Delete('1') |
||
+ | </nowiki> |
||
+ | |||
+ | == Redis (Setup) == |
||
+ | |||
+ | vim /etc/redis/redis.conf |
||
+ | requirepass asdf1234 |
||
+ | service redis-server restart |
||
+ | |||
+ | == Redis (Native) == |
||
+ | |||
+ | redis-cli |
||
+ | auth asdf1234 |
||
+ | |||
+ | set test_table:0 '{"first_name": "Vincent", "last_name": "Vega"}' |
||
+ | set test_table:1 '{"first_name": "Jules", "last_name": "Winnfield"}' |
||
+ | |||
+ | get test_table:0 |
||
+ | |||
+ | hset test_table:0 first_name Vincent |
||
+ | hset test_table:0 last_name Vega |
||
+ | hset test_table:1 first_name Jules |
||
+ | hset test_table:1 last_name Winnfield |
||
+ | |||
+ | hget test_table:0 first_name |
||
+ | hget test_table:1 last_name |
||
+ | |||
+ | del test_table:0 |
||
+ | del test_table:1 |
||
+ | |||
+ | == Redis (C) == |
||
+ | |||
+ | <nowiki> |
||
+ | #include <stdio.h> |
||
+ | #include <stdlib.h> |
||
+ | |||
+ | #include <hiredis.h> |
||
+ | |||
+ | int main() |
||
+ | { |
||
+ | redisContext *ctx; |
||
+ | redisReply *r; |
||
+ | |||
+ | ctx = redisConnect("localhost", 6379); |
||
+ | if (ctx->err) |
||
+ | printf("error: %s\n", ctx->errstr); |
||
+ | |||
+ | r = redisCommand(ctx, "auth asdf1234"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "hset test_table:0 first_name Vincent"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "hset test_table:0 last_name Vega"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "hset test_table:1 first_name Jules"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "hset test_table:1 last_name Winnfield"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "hget test_table:0 first_name"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | else |
||
+ | printf("%s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "hget test_table:1 last_name"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | else |
||
+ | printf("%s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "del test_table:0"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | r = redisCommand(ctx, "del test_table:1"); |
||
+ | if (r->type == REDIS_REPLY_ERROR) |
||
+ | printf("error: %s\n", r->str); |
||
+ | freeReplyObject(r); |
||
+ | |||
+ | redisFree(ctx); |
||
+ | } |
||
+ | </nowiki> |
||
+ | |||
+ | gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs hiredis) redis_test.c -o redis_test |
||
+ | |||
+ | == Redis (Python) == |
||
+ | |||
+ | <nowiki> |
||
+ | import redis |
||
+ | db = redis.StrictRedis(password='asdf1234') |
||
+ | |||
+ | db.set('test_table:0', '{"first_name": "Vincent", "last_name": "Vega"}') |
||
+ | db.set('test_table:1', '{"first_name": "Jules", "last_name": "Winnfield"}') |
||
+ | |||
+ | db.hset('test_table:0', 'first_name', 'Vincent') |
||
+ | db.hset('test_table:0', 'last_name', 'Vega') |
||
+ | db.hset('test_table:1', 'first_name', 'Jules') |
||
+ | db.hset('test_table:1', 'last_name', 'Winnfield') |
||
+ | |||
+ | db.delete('test_table:0') |
||
+ | db.delete('test_table:1') |
||
+ | </nowiki> |
||
+ | |||
+ | == MongoDB (Setup) == |
||
+ | |||
+ | semanage port -a -t mongod_port_t -p tcp 27017 |
||
+ | systemclt restart mongod |
||
+ | chkconfig mongod on |
||
+ | |||
+ | mongo |
||
+ | show dbs |
||
+ | use admin |
||
+ | db.createUser({"user": "admin", "pwd": "admin", "roles": ["userAdminAnyDatabase"]}) |
||
+ | use gridka_db |
||
+ | show collections |
||
+ | db.createUser({"user": "gridka01", "pwd": "asdf1234", "roles": ["readWrite"]}) |
||
+ | |||
+ | vim /etc/mongod.conf |
||
+ | |||
+ | set auth=true |
||
+ | |||
+ | service mongod restart |
||
+ | |||
+ | == MongoDB (native) == |
||
+ | |||
+ | mongo gridka_db |
||
+ | db.test_table.insert({"first_name": "Vincent", "last_name": "Vega"}) |
||
+ | mongo -u gridka01 -p asdf1234 gridka_db |
||
+ | db.test_table.insert({"first_name": "Vincent", "last_name": "Vega"}) |
||
+ | db.test_table.insert({"first_name": "Jules", "last_name": "Winnfield"}) |
||
+ | db.test_table.find() |
||
+ | db.test_table.insert({"you_can_put": "whatever you want"}) |
||
+ | db.test_table.find() |
||
+ | db.test_table.drop() |
||
+ | |||
+ | == MongoDB (C) == |
||
+ | |||
+ | <nowiki> |
||
+ | #include <stdio.h> |
||
+ | #include <stdlib.h> |
||
+ | |||
+ | #include <bson.h> |
||
+ | #include <mongoc.h> |
||
+ | |||
+ | int main () |
||
+ | { |
||
+ | mongoc_client_t *client; |
||
+ | mongoc_collection_t *collection; |
||
+ | bson_error_t error; |
||
+ | bson_t *doc; |
||
+ | bson_t *query; |
||
+ | mongoc_cursor_t *cursor; |
||
+ | char *str; |
||
+ | |||
+ | mongoc_init(); |
||
+ | |||
+ | client = mongoc_client_new("mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db"); |
||
+ | collection = mongoc_client_get_collection (client, |
||
+ | "gridka_db", |
||
+ | "test_table"); |
||
+ | |||
+ | doc = BCON_NEW("first_name",BCON_UTF8("Vincent"), |
||
+ | "last_name", BCON_UTF8("Vega")); |
||
+ | if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE, |
||
+ | doc, NULL, &error)) |
||
+ | printf("error: %s\n", error.message); |
||
+ | bson_destroy(doc); |
||
+ | doc = BCON_NEW("first_name", BCON_UTF8("Jules"), |
||
+ | "last_name", BCON_UTF8("Winnfield")); |
||
+ | if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE, |
||
+ | doc, NULL, &error)) |
||
+ | printf("error: %s\n", error.message); |
||
+ | bson_destroy(doc); |
||
+ | |||
+ | query = bson_new(); |
||
+ | cursor = mongoc_collection_find(collection, MONGOC_QUERY_NONE, |
||
+ | 0, 0, 0, query, NULL, NULL); |
||
+ | while(mongoc_cursor_next(cursor, &doc)) { |
||
+ | str = bson_as_json(doc, NULL); |
||
+ | printf("%s\n", str); |
||
+ | bson_free(str); |
||
+ | } |
||
+ | bson_destroy(query); |
||
+ | mongoc_cursor_destroy(cursor); |
||
+ | |||
+ | if(!mongoc_collection_drop(collection, &error)) |
||
+ | printf("error: %s\n", error.message); |
||
+ | |||
+ | mongoc_collection_destroy(collection); |
||
+ | mongoc_client_destroy(client); |
||
+ | } |
||
+ | </nowiki> |
||
+ | |||
+ | gcc -g -std=c11 -Wall -Wextra -pedantic $(pkg-config --cflags --libs libmongoc-1.0) mongo_test.c -o mongo_test |
||
+ | |||
+ | == MongoDB (Python) == |
||
+ | |||
+ | <nowiki> |
||
+ | from pymongo import MongoClient |
||
+ | client = MongoClient('mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db') |
||
+ | db = client.gridka_db |
||
+ | db.test_table.insert_one({'first_name': 'Vincent', 'last_name': 'Vega'}) |
||
+ | db.test_table.insert_one({'first_name': 'Jules', 'last_name': 'Winnfield'}) |
||
+ | [x for x in db.test_table.find()] |
||
+ | db.test_table.insert({'first_name': 'Marcellus', 'occupation': 'businessman'}) |
||
+ | [x for x in db.test_table.find()] |
||
+ | db.test_table.drop() |
||
+ | </nowiki> |
||
+ | |||
+ | == Neo4j (Native) == |
||
+ | |||
+ | http://localhost:7474/browser/ |
||
+ | |||
+ | CREATE (id_0:pulp_fiction {first_name: 'Vincent', last_name: 'Vega'}) |
||
+ | CREATE (id_1:pulp_fiction {first_name: 'Jules', last_name: 'Winnfield'}) |
||
+ | CREATE (id_0)-[:TELLS_STORY {about: 'le big mac'}]->(id_1) |
||
+ | CREATE (id_1)-[:TELLS_STORY {about: 'foot massage'}]->(id_0) |
||
+ | |||
+ | MATCH (n) RETURN (n) |
||
+ | MATCH (n) OPTIONAL MATCH (n)-[r]->() DELETE n,r |
||
+ | |||
+ | == Neo4j (Python) == |
||
+ | |||
+ | <nowiki> |
||
+ | from py2neo import Graph, Node, Relationship |
||
+ | |||
+ | graph = Graph("http://neo4j:asdf1234@localhost:7474/db/data/") |
||
+ | |||
+ | id_0 = Node("pulp_fiction", first_name='Vincent', last_name='Vega') |
||
+ | id_1 = Node("pulp_fiction", first_name='Jules', last_name='Winnfield') |
||
+ | graph.create(id_0) |
||
+ | graph.create(id_1) |
||
+ | graph.create(Relationship(id_0, 'TELLS_STORY', id_1, about='le big mac')) |
||
+ | graph.create(Relationship(id_1, 'TELLS_STORY', id_0, about='foot massage')) |
||
+ | </nowiki> |
||
+ | |||
+ | == Query plans == |
||
+ | |||
+ | <nowiki> |
||
+ | #!/usr/bin/python |
||
+ | |||
+ | import random |
||
+ | import time |
||
+ | |||
+ | import psycopg2 as pg |
||
+ | |||
+ | conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") |
||
+ | |||
+ | conn.autocommit=False |
||
+ | cur = conn.cursor() |
||
+ | try: cur.execute('drop table large_table') |
||
+ | except: pass |
||
+ | conn.commit() |
||
+ | cur.execute('create table large_table (l char, s varchar(255), n int)') |
||
+ | conn.commit() |
||
+ | conn.autocommit=True |
||
+ | |||
+ | ts = time.time() |
||
+ | for i in xrange(1000000): |
||
+ | if i%100 == 0: |
||
+ | print i |
||
+ | cur.execute("INSERT INTO large_table VALUES ('%s', '%s', %i)" % (chr(random.randrange(65, 91)), |
||
+ | ''.join([chr(random.randrange(65, 91)) for i in xrange(1,16)]), |
||
+ | random.randint(0,1000000))) |
||
+ | conn.commit() |
||
+ | print 'time taken:', time.time()-ts |
||
+ | |||
+ | conn.close() |
||
+ | </nowiki> |
||
+ | |||
+ | set enable_seqscan=false; |
||
+ | |||
+ | select l, count(*) from large_table group by l; |
||
+ | select * from large_table where l='A'; |
||
+ | create index idx_l on large_table(l); |
||
+ | |||
+ | <nowiki> |
||
+ | create table large_table (l char, s varchar(255), n int) |
||
+ | partition by range (n) ( |
||
+ | partition p0 values less than (500000), |
||
+ | partition p1 values less than maxvalue |
||
+ | ); |
||
+ | |||
+ | |||
+ | create table large_table (l char, s varchar(255), n int) |
||
+ | partition by hash(n) |
||
+ | partitions 10; |
||
+ | |||
+ | |||
+ | create table large_table (l char, s varchar(255), n int) |
||
+ | partition by list(l) ( |
||
+ | partition p0 values in ('A', ... , 'L'), |
||
+ | partition p1 values in ('M, ... , 'Z') |
||
+ | ); |
||
+ | </nowiki> |
||
+ | |||
+ | == Transactions == |
||
+ | |||
+ | create table tx_test (name varchar(4), money integer); |
||
+ | |||
+ | insert into tx_test values ('jack', 50); |
||
+ | insert into tx_test values ('jill', 100); |
||
+ | |||
+ | show transaction isolation level; |
||
+ | |||
+ | |||
+ | t1: |
||
+ | begin; |
||
+ | select money from tx_test where name='jack'; |
||
+ | t2: |
||
+ | begin; |
||
+ | update tx_test set money=money-10 where name='jack'; |
||
+ | commit; |
||
+ | t1: |
||
+ | select money from tx_test where name='jack'; |
||
+ | |||
+ | |||
+ | t1: |
||
+ | set session characteristics as transaction isolation level repeatable read; |
||
+ | t2: |
||
+ | set session characteristics as transaction isolation level repeatable read; |
||
+ | t1: |
||
+ | begin; |
||
+ | select money from tx_test where name='jack'; |
||
+ | t2: |
||
+ | begin; |
||
+ | update tx_test set money=money-10 where name='jack'; |
||
+ | commit; |
||
+ | t1: |
||
+ | select money from tx_test where name='jack'; |
||
+ | update tx_test set money=money+10 where name='jack'; |
||
+ | |||
+ | |||
+ | t1: |
||
+ | set session characteristics as transaction isolation level read committed; |
||
+ | t2: |
||
+ | set session characteristics as transaction isolation level read committed; |
||
+ | |||
+ | t1: |
||
+ | begin; |
||
+ | t2: |
||
+ | begin; |
||
+ | t1: |
||
+ | update tx_test set money=money-10 where name='jack'; |
||
+ | t2: |
||
+ | update tx_test set money=money+10 where name='jill'; |
||
+ | t1: |
||
+ | update tx_test set money=money+10 where name='jill'; |
||
+ | t2: |
||
+ | update tx_test set money=money+10 where name='jack'; |
||
+ | |||
+ | |||
+ | == SQL Injection == |
||
+ | |||
+ | CREATE TABLE users (id integer, login varchar(255), password varchar(255)); |
||
+ | CREATE EXTENSION pgcrypto; |
||
+ | INSERT INTO users VALUES (0, 'root', crypt('hunter2', 'sha1')); |
||
+ | SELECT * FROM users; |
||
+ | |||
+ | <nowiki> |
||
+ | #!/usr/bin/python |
||
+ | |||
+ | import random |
||
+ | import time |
||
+ | |||
+ | import psycopg2 as pg |
||
+ | |||
+ | conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") |
||
+ | |||
+ | cur = conn.cursor() |
||
+ | |||
+ | login = raw_input('login: ') |
||
+ | password = raw_input('password: ') |
||
+ | |||
+ | print "select * from users where login=\'%s\' and password=crypt('%s', 'sha1')" % (login, password) |
||
+ | |||
+ | cur.execute("select * from users where login=\'%s\' and password=crypt('%s', 'sha1')" % (login, password)) |
||
+ | |||
+ | if cur.fetchone(): |
||
+ | print 'logged in' |
||
+ | else: |
||
+ | print 'wrong login or password' |
||
+ | |||
+ | cur.close() |
||
+ | conn.close() |
||
+ | </nowiki> |
||
+ | |||
+ | <nowiki> |
||
+ | python sql_inject.py |
||
+ | root / hunter2 |
||
+ | root / ','sha1') or 1=1; update users set password=crypt('mypass', 'sha1'); commit; select 1;-- |
||
+ | </nowiki> |
||
+ | |||
+ | == Multiprocess == |
||
+ | |||
+ | <nowiki> |
||
+ | #!/usr/bin/python |
||
+ | |||
+ | import psycopg2 as pg |
||
+ | |||
+ | from multiprocessing import Process |
||
+ | from random import randint |
||
+ | from time import sleep |
||
+ | |||
+ | def get_session(): |
||
+ | return pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") |
||
+ | |||
+ | def bootstrap_tables(): |
||
+ | session = get_session() |
||
+ | cur = session.cursor() |
||
+ | try: |
||
+ | print 'trying to create new table' |
||
+ | cur.execute('create table mp_test (value integer)') |
||
+ | except: |
||
+ | print 'table alread existed, truncating instead' |
||
+ | cur.execute('truncate table mp_test') |
||
+ | cur.close() |
||
+ | session.commit() |
||
+ | |||
+ | def insert_rows(): |
||
+ | while True: |
||
+ | print 'inserting' |
||
+ | session = get_session() |
||
+ | cur = session.cursor() |
||
+ | cur.execute('insert into mp_test values (%s)', [randint(0,9)]) |
||
+ | session.commit() |
||
+ | cur.close() |
||
+ | session.close() |
||
+ | sleep(0.1) |
||
+ | |||
+ | def delete_rows(): |
||
+ | while True: |
||
+ | print 'deleting' |
||
+ | session = get_session() |
||
+ | cur = session.cursor() |
||
+ | cur.execute('delete from mp_test where value = %s', [randint(0,9)]) |
||
+ | session.commit() |
||
+ | cur.close() |
||
+ | session.close() |
||
+ | sleep(0.1) |
||
+ | |||
+ | def count_rows(): |
||
+ | while True: |
||
+ | session = get_session() |
||
+ | cur = session.cursor() |
||
+ | cur.execute('select count(*) from mp_test') |
||
+ | res = cur.fetchone() |
||
+ | print 'rows', res[0] |
||
+ | cur.close() |
||
+ | session.close() |
||
+ | sleep(1) |
||
+ | |||
+ | if __name__ == '__main__': |
||
+ | bootstrap_tables() |
||
+ | p_inserter = Process(target=insert_rows) |
||
+ | p_deleter = Process(target=delete_rows) |
||
+ | p_counter = Process(target=count_rows) |
||
+ | p_inserter.start() |
||
+ | p_deleter.start() |
||
+ | p_counter.start() |
||
+ | p_inserter.join() |
||
+ | p_deleter.join() |
||
+ | p_counter.join() |
||
+ | </nowiki> |
Latest revision as of 10:50, 8 September 2015
Contents
- 1 Overview
- 1.1 PostgreSQL (Setup)
- 1.2 PostgreSQL (SQL)
- 1.3 PostgreSQL (C)
- 1.4 PostgreSQL (Python)
- 1.5 MonetDB (Setup)
- 1.6 MonetDB (SQL)
- 1.7 MonetDB (C)
- 1.8 MonetDB (Python)
- 1.9 LevelDB (C)
- 1.10 LevelDB (Python)
- 1.11 Redis (Setup)
- 1.12 Redis (Native)
- 1.13 Redis (C)
- 1.14 Redis (Python)
- 1.15 MongoDB (Setup)
- 1.16 MongoDB (native)
- 1.17 MongoDB (C)
- 1.18 MongoDB (Python)
- 1.19 Neo4j (Native)
- 1.20 Neo4j (Python)
- 1.21 Query plans
- 1.22 Transactions
- 1.23 SQL Injection
- 1.24 Multiprocess
Overview
Author: Mario Lassnig, CERN PH-ADP-CO, [1]
PostgreSQL (Setup)
vim /var/lib/pgsql/9.4/data/pg_hba.conf
set all to trust
add line
host all all 0.0.0.0/0 trust
psql -U gridka01 gridka_db
should not ask for password ctrl-d to exit
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE ROLE gridka01 PASSWORD 'asdf1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;" sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE DATABASE gridka_db OWNER gridka01 ENCODING 'UTF8'" sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "CREATE SCHEMA gridka_schema AUTHORIZATION gridka01" sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON SCHEMA gridka_schema TO gridka01" sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON DATABASE gridka_db TO gridka01"
vim /var/lib/pgsql/9.4/data/pg_hba.conf
set all to md5
systemctl restart postgresql-9.4.service
psql -U gridka01 gridka_db
should ask for password
ldconfig
ls -la /var/lib/pgsql/9.4/data
PostgreSQL (SQL)
psql -U gridka01 gridka_db
\l .. list all databases \d .. list all relations
CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR );
\d
SELECT * FROM test_table;
INSERT INTO test_table VALUES ('Vincent', 'Vega');
SELECT * FROM test_table;
INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');
SELECT * FROM test_table;
DELETE FROM test_table;
SELECT * FROM test_table;
DROP TABLE test_table;
SELECT * FROM test_table;
PostgreSQL (C)
#include <stdio.h> #include <stdlib.h> #include <libpq-fe.h> int main() { PGconn *conn; conn = PQconnectdb("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'"); if (PQstatus(conn) != CONNECTION_OK) { printf("%s\n", PQerrorMessage(conn)); exit(1); } PGresult *res; res = PQexec(conn, "CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )"); if (!res) printf("%s\n", PQerrorMessage(conn)); PQclear(res); res = PQexec(conn, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )"); if (!res) printf("%s\n", PQerrorMessage(conn)); PQclear(res); res = PQexec(conn, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )"); if (!res) printf("%s\n", PQerrorMessage(conn)); PQclear(res); res = PQexec(conn, "SELECT * FROM test_table"); if (PQresultStatus(res) == PGRES_TUPLES_OK) for(int i = 0; i < PQntuples(res); ++i) printf("%s %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1)); else printf("%s\n", PQerrorMessage(conn)); PQclear(res); res = PQexec(conn, "DELETE FROM test_table"); if (!res) printf("%s\n", PQerrorMessage(conn)); PQclear(res); PQfinish(conn); }
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
PostgreSQL (Python)
import psycopg2 as pg conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") cur = conn.cursor() cur.execute("CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR )") cur.execute("INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )") conn.commit() cur.execute("SELECT * FROM test_table") cur.fetchall() cur.execute("INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )") conn.commit() cur.execute("SELECT * FROM test_table") cur.fetchall() cur.execute("DROP TABLE test_table") conn.commit()
MonetDB (Setup)
monetdbd create /tmp/gridka_schema monetdbd start /tmp/gridka_schema monetdb create gridka_db monetdb release gridka_db
MonetDB (SQL)
mclient -u monetdb -d gridka_db (default password: monetdb)
CREATE USER "gridka01" WITH PASSWORD 'asdf1234' NAME 'gridka01' SCHEMA "sys"; CREATE SCHEMA "gridka01" AUTHORIZATION "gridka01"; ALTER USER "gridka01" SET SCHEMA "gridka01"; CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) ); INSERT INTO test_table VALUES ('Vincent', 'Vega'); INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield'); SELECT * FROM test_table; DELETE FROM test_table; DROP test_table;
MonetDB (C)
#include <stdio.h> #include <stdlib.h> #include <mapi.h> int main() { Mapi db; db = mapi_connect("localhost", 50000, "gridka01", "asdf1234", "sql", "gridka_db"); if (mapi_error(db)) mapi_explain(db, stderr); MapiHdl res; res = mapi_query(db, "CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )"); if (mapi_error(db)) mapi_explain(db, stderr); mapi_close_handle(res); res = mapi_query(db, "INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )"); if (mapi_error(db)) mapi_explain(db, stderr); mapi_close_handle(res); res = mapi_query(db, "INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )"); if (mapi_error(db)) mapi_explain(db, stderr); mapi_close_handle(res); res = mapi_query(db, "SELECT * FROM test_table"); if (mapi_error(db)) mapi_explain(db, stderr); while(mapi_fetch_row(res)) { printf("%s %s\n", mapi_fetch_field(res, 0), mapi_fetch_field(res, 1)); } mapi_close_handle(res); res = mapi_query(db, "DELETE FROM test_table"); if (mapi_error(db)) mapi_explain(db, stderr); mapi_close_handle(res); mapi_destroy(db); }
gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs monetdb-mapi) monetdb_test.c -o monetdb_test
MonetDB (Python)
import monetdb.sql as mo conn = mo.connect(database='gridka_db', username='gridka01', hostname='localhost', password='asdf1234') cur = conn.cursor() cur.execute("CREATE TABLE test_table ( first_name VARCHAR(64), last_name VARCHAR(64) )") cur.execute("INSERT INTO test_table VALUES ( 'Vincent', 'Vega' )") conn.commit() cur.execute("SELECT * FROM test_table") cur.fetchall() cur.execute("INSERT INTO test_table (last_name, first_name) VALUES ( 'Jules', 'Winnfield' )") conn.commit() cur.execute("SELECT * FROM test_table") cur.fetchall() cur.execute("DROP TABLE test_table") conn.commit()
LevelDB (C)
mkdir gridka_db
#include <iostream> #include <string> #include <leveldb/db.h> int main() { leveldb::DB* db; leveldb::Options opts; leveldb::ReadOptions ropts; leveldb::WriteOptions wopts; leveldb::Status status; leveldb::Iterator* it; opts.create_if_missing = true; status = leveldb::DB::Open(opts, "gridka_db/test_table", &db); status = db->Put(wopts, "0", "{\"first_name\": \"Vincent\", \"last_name\": \"Vega\"}"); status = db->Put(wopts, "1", "{\"first_name\": \"Jules\", \"last_name\": \"Winnfield\"}"); std::cout << db->Get(ropts, std::string("0"), &db) << '\n'; it = db->NewIterator(ropts); for(it->SeekToFirst(); it->Valid(); it->Next()) std::cout << it->key().ToString() << '\t' << it->value().ToString() << '\n'; delete it; status = db->Delete(wopts, "0"); status = db->Delete(wopts, "1"); delete db; }
g++ -g -std=c++14 -Wall -Werror -pedantic -lleveldb leveldb_test.cpp -o leveldb_test
LevelDB (Python)
import json import leveldb db = leveldb.LevelDB('gridka_db/test_table') db.Put('0', json.dumps({'first_name': 'Vincent', 'last_name': 'Vega'})) db.Put('1', json.dumps({'first_name': 'Jules', 'last_name': 'Winnfield'})) json.loads(db.Get('0')) it = db.RangeIter() [(x[0],json.loads(x[1])) for x in it] db.Delete('0') db.Delete('1')
Redis (Setup)
vim /etc/redis/redis.conf requirepass asdf1234 service redis-server restart
Redis (Native)
redis-cli auth asdf1234
set test_table:0 '{"first_name": "Vincent", "last_name": "Vega"}' set test_table:1 '{"first_name": "Jules", "last_name": "Winnfield"}'
get test_table:0
hset test_table:0 first_name Vincent hset test_table:0 last_name Vega hset test_table:1 first_name Jules hset test_table:1 last_name Winnfield
hget test_table:0 first_name hget test_table:1 last_name
del test_table:0 del test_table:1
Redis (C)
#include <stdio.h> #include <stdlib.h> #include <hiredis.h> int main() { redisContext *ctx; redisReply *r; ctx = redisConnect("localhost", 6379); if (ctx->err) printf("error: %s\n", ctx->errstr); r = redisCommand(ctx, "auth asdf1234"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "hset test_table:0 first_name Vincent"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "hset test_table:0 last_name Vega"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "hset test_table:1 first_name Jules"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "hset test_table:1 last_name Winnfield"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "hget test_table:0 first_name"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); else printf("%s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "hget test_table:1 last_name"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); else printf("%s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "del test_table:0"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); r = redisCommand(ctx, "del test_table:1"); if (r->type == REDIS_REPLY_ERROR) printf("error: %s\n", r->str); freeReplyObject(r); redisFree(ctx); }
gcc -g -std=c11 -Wall -Wextra $(pkg-config --cflags --libs hiredis) redis_test.c -o redis_test
Redis (Python)
import redis db = redis.StrictRedis(password='asdf1234') db.set('test_table:0', '{"first_name": "Vincent", "last_name": "Vega"}') db.set('test_table:1', '{"first_name": "Jules", "last_name": "Winnfield"}') db.hset('test_table:0', 'first_name', 'Vincent') db.hset('test_table:0', 'last_name', 'Vega') db.hset('test_table:1', 'first_name', 'Jules') db.hset('test_table:1', 'last_name', 'Winnfield') db.delete('test_table:0') db.delete('test_table:1')
MongoDB (Setup)
semanage port -a -t mongod_port_t -p tcp 27017 systemclt restart mongod chkconfig mongod on
mongo show dbs use admin db.createUser({"user": "admin", "pwd": "admin", "roles": ["userAdminAnyDatabase"]}) use gridka_db show collections db.createUser({"user": "gridka01", "pwd": "asdf1234", "roles": ["readWrite"]})
vim /etc/mongod.conf
set auth=true
service mongod restart
MongoDB (native)
mongo gridka_db db.test_table.insert({"first_name": "Vincent", "last_name": "Vega"}) mongo -u gridka01 -p asdf1234 gridka_db db.test_table.insert({"first_name": "Vincent", "last_name": "Vega"}) db.test_table.insert({"first_name": "Jules", "last_name": "Winnfield"}) db.test_table.find() db.test_table.insert({"you_can_put": "whatever you want"}) db.test_table.find() db.test_table.drop()
MongoDB (C)
#include <stdio.h> #include <stdlib.h> #include <bson.h> #include <mongoc.h> int main () { mongoc_client_t *client; mongoc_collection_t *collection; bson_error_t error; bson_t *doc; bson_t *query; mongoc_cursor_t *cursor; char *str; mongoc_init(); client = mongoc_client_new("mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db"); collection = mongoc_client_get_collection (client, "gridka_db", "test_table"); doc = BCON_NEW("first_name",BCON_UTF8("Vincent"), "last_name", BCON_UTF8("Vega")); if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE, doc, NULL, &error)) printf("error: %s\n", error.message); bson_destroy(doc); doc = BCON_NEW("first_name", BCON_UTF8("Jules"), "last_name", BCON_UTF8("Winnfield")); if(!mongoc_collection_insert(collection, MONGOC_INSERT_NONE, doc, NULL, &error)) printf("error: %s\n", error.message); bson_destroy(doc); query = bson_new(); cursor = mongoc_collection_find(collection, MONGOC_QUERY_NONE, 0, 0, 0, query, NULL, NULL); while(mongoc_cursor_next(cursor, &doc)) { str = bson_as_json(doc, NULL); printf("%s\n", str); bson_free(str); } bson_destroy(query); mongoc_cursor_destroy(cursor); if(!mongoc_collection_drop(collection, &error)) printf("error: %s\n", error.message); mongoc_collection_destroy(collection); mongoc_client_destroy(client); }
gcc -g -std=c11 -Wall -Wextra -pedantic $(pkg-config --cflags --libs libmongoc-1.0) mongo_test.c -o mongo_test
MongoDB (Python)
from pymongo import MongoClient client = MongoClient('mongodb://gridka01:asdf1234@localhost:27017/?authSource=gridka_db') db = client.gridka_db db.test_table.insert_one({'first_name': 'Vincent', 'last_name': 'Vega'}) db.test_table.insert_one({'first_name': 'Jules', 'last_name': 'Winnfield'}) [x for x in db.test_table.find()] db.test_table.insert({'first_name': 'Marcellus', 'occupation': 'businessman'}) [x for x in db.test_table.find()] db.test_table.drop()
Neo4j (Native)
http://localhost:7474/browser/
CREATE (id_0:pulp_fiction {first_name: 'Vincent', last_name: 'Vega'}) CREATE (id_1:pulp_fiction {first_name: 'Jules', last_name: 'Winnfield'}) CREATE (id_0)-[:TELLS_STORY {about: 'le big mac'}]->(id_1) CREATE (id_1)-[:TELLS_STORY {about: 'foot massage'}]->(id_0)
MATCH (n) RETURN (n) MATCH (n) OPTIONAL MATCH (n)-[r]->() DELETE n,r
Neo4j (Python)
from py2neo import Graph, Node, Relationship graph = Graph("http://neo4j:asdf1234@localhost:7474/db/data/") id_0 = Node("pulp_fiction", first_name='Vincent', last_name='Vega') id_1 = Node("pulp_fiction", first_name='Jules', last_name='Winnfield') graph.create(id_0) graph.create(id_1) graph.create(Relationship(id_0, 'TELLS_STORY', id_1, about='le big mac')) graph.create(Relationship(id_1, 'TELLS_STORY', id_0, about='foot massage'))
Query plans
#!/usr/bin/python import random import time import psycopg2 as pg conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") conn.autocommit=False cur = conn.cursor() try: cur.execute('drop table large_table') except: pass conn.commit() cur.execute('create table large_table (l char, s varchar(255), n int)') conn.commit() conn.autocommit=True ts = time.time() for i in xrange(1000000): if i%100 == 0: print i cur.execute("INSERT INTO large_table VALUES ('%s', '%s', %i)" % (chr(random.randrange(65, 91)), ''.join([chr(random.randrange(65, 91)) for i in xrange(1,16)]), random.randint(0,1000000))) conn.commit() print 'time taken:', time.time()-ts conn.close()
set enable_seqscan=false;
select l, count(*) from large_table group by l; select * from large_table where l='A'; create index idx_l on large_table(l);
create table large_table (l char, s varchar(255), n int) partition by range (n) ( partition p0 values less than (500000), partition p1 values less than maxvalue ); create table large_table (l char, s varchar(255), n int) partition by hash(n) partitions 10; create table large_table (l char, s varchar(255), n int) partition by list(l) ( partition p0 values in ('A', ... , 'L'), partition p1 values in ('M, ... , 'Z') );
Transactions
create table tx_test (name varchar(4), money integer);
insert into tx_test values ('jack', 50); insert into tx_test values ('jill', 100);
show transaction isolation level;
t1:
begin; select money from tx_test where name='jack';
t2:
begin; update tx_test set money=money-10 where name='jack'; commit;
t1:
select money from tx_test where name='jack';
t1:
set session characteristics as transaction isolation level repeatable read;
t2:
set session characteristics as transaction isolation level repeatable read;
t1:
begin; select money from tx_test where name='jack';
t2:
begin; update tx_test set money=money-10 where name='jack'; commit;
t1:
select money from tx_test where name='jack'; update tx_test set money=money+10 where name='jack';
t1:
set session characteristics as transaction isolation level read committed;
t2:
set session characteristics as transaction isolation level read committed;
t1:
begin;
t2:
begin;
t1:
update tx_test set money=money-10 where name='jack';
t2:
update tx_test set money=money+10 where name='jill';
t1:
update tx_test set money=money+10 where name='jill';
t2:
update tx_test set money=money+10 where name='jack';
SQL Injection
CREATE TABLE users (id integer, login varchar(255), password varchar(255)); CREATE EXTENSION pgcrypto; INSERT INTO users VALUES (0, 'root', crypt('hunter2', 'sha1')); SELECT * FROM users;
#!/usr/bin/python import random import time import psycopg2 as pg conn = pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") cur = conn.cursor() login = raw_input('login: ') password = raw_input('password: ') print "select * from users where login=\'%s\' and password=crypt('%s', 'sha1')" % (login, password) cur.execute("select * from users where login=\'%s\' and password=crypt('%s', 'sha1')" % (login, password)) if cur.fetchone(): print 'logged in' else: print 'wrong login or password' cur.close() conn.close()
python sql_inject.py root / hunter2 root / ','sha1') or 1=1; update users set password=crypt('mypass', 'sha1'); commit; select 1;--
Multiprocess
#!/usr/bin/python import psycopg2 as pg from multiprocessing import Process from random import randint from time import sleep def get_session(): return pg.connect("dbname='gridka_db' user='gridka01' host='localhost' password='asdf1234'") def bootstrap_tables(): session = get_session() cur = session.cursor() try: print 'trying to create new table' cur.execute('create table mp_test (value integer)') except: print 'table alread existed, truncating instead' cur.execute('truncate table mp_test') cur.close() session.commit() def insert_rows(): while True: print 'inserting' session = get_session() cur = session.cursor() cur.execute('insert into mp_test values (%s)', [randint(0,9)]) session.commit() cur.close() session.close() sleep(0.1) def delete_rows(): while True: print 'deleting' session = get_session() cur = session.cursor() cur.execute('delete from mp_test where value = %s', [randint(0,9)]) session.commit() cur.close() session.close() sleep(0.1) def count_rows(): while True: session = get_session() cur = session.cursor() cur.execute('select count(*) from mp_test') res = cur.fetchone() print 'rows', res[0] cur.close() session.close() sleep(1) if __name__ == '__main__': bootstrap_tables() p_inserter = Process(target=insert_rows) p_deleter = Process(target=delete_rows) p_counter = Process(target=count_rows) p_inserter.start() p_deleter.start() p_counter.start() p_inserter.join() p_deleter.join() p_counter.join()