Relational Databases

From Gridkaschool
Revision as of 23:10, 1 September 2014 by Mlassnig (talk | contribs) (→‎General information)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

General information

ssh reldb@gks-virt(050-061).scc.kit.edu -p24

File:Agenda.pdf

Session 1 - Setup

$ mysql -u root

mysql> show databases;
mysql> create database playground;
mysql> show databases;

CTRL-D

$ sudo ls -la /var/lib/mysql/
$ sudo ls -la /var/lib/mysql/playground
$ sudo ls -la /var/lib/mysql/mysql

$ mysql -u root

mysql> use mysql;
mysql> show tables;

mysql> select * from user;
mysql> create user 'joffrey' identified by 'kings_landing';
mysql> create user 'joffrey'@'localhost' identified by 'kings_landing';
mysql> create user 'hodor' identified by 'hodor';
mysql> create user 'hodor'@'localhost' identified by 'hodor';
mysql> select * from user;

mysql> drop user 'joffrey';
mysql> drop user 'hodor';

mysql> select * from user;

CTRL-D

$ mysql -u hodor -p

mysql> use playground;

CTRL-D

$ mysql -u root

mysql> show grants;
mysql> grant all privileges on playground.* to 'joffrey'@'localhost';

CTRL-D

$ mysql -u joffrey

mysql> show grants;

CTRL-D

Session 2 - CRUD

$ mysql -u joffrey -p

mysql> use playground;

mysql> show engines;

mysql> create table persons (
        name varchar(256),
        gender char(1)
       ) engine=innodb;

mysql> describe persons;

mysql> insert into persons (name, gender) values ('Eddard Stark', 'm');
       insert into persons (name, gender) values ('Catelyn Stark', 'f');
       insert into persons (name, gender) values ('Robb Stark', 'm');
       insert into persons (name, gender) values ('Jon Snow', 'm');
       insert into persons (name, gender) values ('Arya Stark', 'f');
       insert into persons (name, gender) values ('Tywin Lannister', 'm');
       insert into persons (name, gender) values ('Cersei Lannister', 'f');
       insert into persons (name, gender) values ('Jaime Lannister', 'm');
       insert into persons (name, gender) values ('Joffrey Baratheon', 'm');
       insert into persons (name, gender) values ('Tyrion Lannister', 'm');
       insert into persons (name, gender) values ('Daenerys Targaryen', 'f');
       insert into persons (name, gender) values ('Hodor', 'm');
       insert into persons (name, gender) values ('Ygritte', 'f');

mysql> select * from persons;

mysql> select * from persons where gender='m';

mysql> select gender from persons where name='Hodor';

mysql> select gender, count(*) from persons group by gender;

mysql> select * from persons where name like '%Stark';

mysql> select substring_index(name, ' ', 1) from persons;

mysql> select substring_index(name, ' ', -1) from persons;

mysql> create table persons_2 (
        first_name varchar(128),
        last_name varchar(128),
        noble_house varchar(128),
        gender char(1)
       ) engine=innodb;

mysql> select substring_index(name, ' ', 1) as first_name,
              substring_index(name, ' ', -1) as last_name,
              gender
       from persons;

mysql> insert into persons_2
       select substring_index(name, ' ', 1) as first_name,
              substring_index(name, ' ', -1) as last_name,
              gender
       from persons;

mysql> insert into persons_2 (first_name,
                              last_name,
                              noble_house,
                              gender)
       select substring_index(name, ' ', 1) as first_name,
              substring_index(name, ' ', -1) as last_name,
              substring_index(name, ' ', -1) as noble_house,
              gender
       from persons;

mysql> drop table persons;

mysql> alter table persons_2 rename persons;

mysql> select * from persons;

mysql> update persons set noble_house='Stark' where last_name='Snow';

mysql> update persons set noble_house='Lannister' where last_name='Baratheon';

mysql> update persons set last_name=NULL, noble_house=NULL where first_name in ('Hodor', 'Ygritte');

mysql> select first_name from persons where noble_house='Stark' and gender='f';

mysql> select noble_house, count(*) as members from persons group by noble_house order by members desc;

mysql> delete from persons where first_name='Joffrey';

Session 3 - Relations

$ mysql -u joffrey -p

mysql> use playground;

mysql> create table locations (
        name varchar(256),
        owner varchar(256)
       ) engine=innodb;

mysql> insert into locations (name, owner) values ('Winterfell', 'Stark');
       insert into locations (name, owner) values ('Casterly Rock', 'Lannister');
       insert into locations (name, owner) values ('Kings Landing', 'Targaryen');
       insert into locations (name, owner) values ('Nights Watch', 'The Wall');
       insert into locations (name, owner) values ('Beyond the Wall', NULL);

mysql> select *
       from persons, locations;

mysql> select *
       from persons p, locations l
       where p.noble_house = l.owner;

mysql> select p.first_name, p.last_name, l.name from persons p, locations l
       where p.noble_house = l.owner;

mysql> select p.first_name, p.last_name, l.name
       from persons p left join locations l
       on p.noble_house = l.owner;

mysql> select p.first_name, p.last_name, l.name
       from persons p right join locations l
       on p.noble_house = l.owner;

mysql> select p1.first_name, p2.first_name
       from persons p1, persons p2
       where ((p1.gender='m' and p2.gender='f')
       and (p1.noble_house != p2.noble_house));

mysql> create table forces (
        owner varchar(256),
        location varchar(256),
        swordmen int,
        archers int,
        catapults int
       ) engine=innodb;

mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Winterfell', 1000, 1000, 10);
       insert into forces (owner, location, swordmen, archers, catapults) values ('Lannister', 'Casterly Rock', 1000, 1000, 10);
       insert into forces (owner, location, swordmen, archers, catapults) values ('Targaryen', 'Kings Landing', 1000, 1000, 10);
       insert into forces (owner, location, swordmen, archers, catapults) values (NULL, 'Beyond the Wall', 1000, 1000, 10);
mysql> select * from persons, locations, forces;

mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults
       from persons p, locations l, forces f
       where p.noble_house = l.owner
       and l.owner = f.owner;

mysql> select distinct noble_house from persons;

mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults
       from (select distinct noble_house from persons) p, locations l, forces f
       where p.noble_house = l.owner
       and l.owner = f.owner;

mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Casterly Rock', 300, 10, 1);

mysql> create view battleground as
       select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults
       from (select distinct noble_house from persons) p, locations l, forces f
       where p.noble_house = l.owner and l.owner = f.owner;

mysql> create view noble_houses as
       select distinct noble_house from persons;

mysql> create view battleground as
       select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults
       from noble_houses p, locations l, forces f
       where p.noble_house = l.owner
       and l.owner = f.owner;

mysql> select b1.*
       from battleground b1, battleground b2
       where b1.family != b2.family
       and b1.battlefield = b2.battlefield;

mysql> create view battle as (
        select b1.*
        from battleground b1, battleground b2
        where b1.family != b2.family
        and b1.battlefield = b2.battlefield
       );

mysql> create view strength as
       select family, swordmen*1+archers*2+catapults*10 as battle_strength
       from battle;

mysql> select *
       from strength
       where battle_strength = (select max(battle_strength)
                                from strength);

mysql> drop view strength;

mysql> drop view battle;

mysql> drop view battleground;

mysql> drop view noble_houses;

Session 4 - Constraints

$ mysql -u root

mysql> SET @@global.sql_mode = "STRICT_ALL_TABLES";

CTRL-D

$ mysql -u joffrey -p

mysql> use playground;

mysql> insert into persons values ('First', 'Last', 'House', 'x');
mysql> insert into persons values ('Second', 'Last', 'House', NULL);

mysql> alter table persons modify gender enum('m', 'f');

mysql> select * from persons;

mysql> update persons set gender=NULL where gender='x';

mysql> alter table persons modify gender enum('m', 'f');

mysql> delete from persons where gender is null;

mysql> alter table persons modify gender enum('m', 'f') not null;

mysql> insert into persons values ('First', 'Last', 'House', 'x');

mysql> alter table persons add constraint pk_persons primary key (first_name, last_name);

mysql> alter table persons add column id int first;

mysql> select * from persons;

mysql> alter table persons drop column id;

mysql> alter table persons add column id int primary key auto_increment first;

mysql> select * from persons;

mysql> alter table persons add unique uq_first_last (first_name, last_name);

mysql> show create table persons;

mysql> alter table locations add constraint pk_locations primary key(name);

Session 5 - Transactions

TERMINAL 0:

$ mysql -u root

mysql> use playground;

mysql> create table tx_test (name varchar(4), age integer) engine=innodb;

mysql> insert into tx_test values ('jack', 28);
       insert into tx_test values ('jill', 25);

mysql> select * from tx_test;

mysql> select @@global.tx_isolation;

mysql> set global transaction isolation level read uncommitted;

--- DIRTY READ ---

TERMINAL 1:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> select age from tx_test where name='jack';

TERMINAL 2:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> update tx_test set age=21 where name='jack';

TERMINAL 1:

mysql> select age from tx_test where name='jack';

TERMINAL 2:

mysql> rollback;

CTRL-D

--- NON-REPEATABLE READ ---

TERMINAL 0:

mysql> set global transaction isolation level read committed;

TERMINAL 1:

mysql> start transaction;

mysql> select * from tx_test where name='jack';

TERMINAL 2:

mysql> start transaction;

mysql> update tx_test set age=29 where name='jack';

mysql> commit;

CTRL-D

TERMINAL 1:

mysql> select * from tx_test where name='jack';

CTRL-D

--- PHANTOM READ ---

TERMINAL 0:

mysql> set global transaction isolation level repeatable read;

TERMINAL 1:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> select * from tx_test where age between 20 and 30;

TERMINAL 2:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> insert into tx_test values ('bob', 23);

mysql> commit;

CTRL-D

TERMINAL 1:

mysql> select * from tx_test where age between 20 and 30;

mysql> commit;

mysql> select * from tx_test where age between 20 and 30;

CTRL-D

--- DEADLOCK ---

TERMINAL 0:

mysql> alter table tx_test add money integer;

mysql> update tx_test set money=100;

mysql> delete from tx_test where name='bob';

mysql> set global transaction isolation level read committed;

TERMINAL 1:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

TERMINAL 2:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

TERMINAL 1:

mysql> update tx_test set money=money-50 where name='jack';

TERMINAL 2:

mysql> update tx_test set money=money-50 where name='jill';

TERMINAL 1:

mysql> update tx_test set money=money+50 where name='jill';

TERMINAL 2:

mysql> update tx_test set money=money+50 where name='jack';

TERMINAL 1:

mysql> commit;

Session 6 - SQL Injection

$ mysql -u root -p

mysql> use playground

mysql> create table users (id integer, login varchar(255), password varchar(255)) engine=innodb;

mysql> insert into users values (0, 'root', sha(concat('salt', 'hunter2')));

mysql> select * from users where login='root' and password=sha(concat('salt','hunter2'));

CTRL-D



#!/usr/bin/python

import MySQLdb
import MySQLdb.cursors

session = MySQLdb.connect(host="localhost",
                          user="joffrey",
                          passwd="kings_landing",
                          db="playground",
                          cursorclass = MySQLdb.cursors.SSCursor)
cur = session.cursor()

login = raw_input('login: ')
password = raw_input('password: ')

print "select * from users where login=\'%s\' and password=sha(concat('salt', '%s'))" % (login, password)

cur.execute("select * from users where login=\'%s\' and password=sha(concat('salt', '%s'))" % (login, password))

if cur.fetchone() is not None:
    print 'logged in'
else:
    print 'wrong login or password'

cur.close()
session.close()

$ ./inject.py

login: root
password: ')) or 1=1;#

$ ./inject.py

login: root
password: ')) or 1=1; update users set password=sha(concat('salt','mypass'));#

Session 7 - Indexes

#!/usr/bin/python

import random
import time

import MySQLdb

con = MySQLdb.connect(host="localhost",
                      user="joffrey",
                      passwd="kings_landing",
                      db="playground")
con.autocommit(True)

cur = con.cursor()

try: cur.execute('drop table large_table')
except: pass

cur.execute('create table large_table (l char, s varchar(255), n int) engine=innodb')

ts = time.time()
for i in xrange(1000000):
    if i%10 == 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)))
print 'time taken:', time.time()-ts

con.close()

$ mysql -u joffrey -p

mysql> use playground;

mysql> select l, count(*) from large_table group by l;

mysql> create index idx_l on large_table(l);

mysql> select s, count(*) from large_table where s like 'AB%';

mysql> create index idx_l on large_table(l);

mysql> select s, count(*) from large_table where s like 'AB%';

Session 8 - Partitioning

create table large_table (l char, s varchar(255), n int) engine=innodb
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) engine=innodb
partition by hash(n)
partitions 10;

create table large_table (l char, s varchar(255), n int) engine=innodb
partition by list(l) (
 partition p0 values in ('A', ... , 'L'),
 partition p1 values in ('M, ... , 'Z')
);

Session 9 - Execution plans

$ mysql -u joffrey -p

mysql> use playground

mysql> explain <any statement>

Session X - Application development

/* gcc -Wall $(mysql_config --cflags --libs) basic.c -o basic */

#include <stdio.h>
#include <stdlib.h>

#include <mysql.h>

int main()
{

  MYSQL *con = mysql_init(NULL);
  MYSQL_RES *cur;
  MYSQL_ROW row;
  int cols, i;

  mysql_real_connect(con,
                    "localhost",
                    "joffrey",
                    "kings_landing",
                    "playground",
                    0, NULL, 0);

  mysql_query(con, "select * from persons");

  cur = mysql_store_result(con);

  cols = mysql_num_fields(cur);
  while ((row = mysql_fetch_row(cur))) {
    for(i = 0; i < cols; i++) {
      printf("%s ", row[i] ? row[i] : "<NULL>");
    }
    printf("\n");
  }

  mysql_free_result(cur);
  mysql_close(con);

  return 0;
}

#!/usr/bin/python

import MySQLdb
import MySQLdb.cursors

session = MySQLdb.connect(host="localhost",
                          user="joffrey",
                          passwd="kings_landing",
                          db="playground",
                          cursorclass = MySQLdb.cursors.SSCursor)
cur = session.cursor()

cur.execute('select * from persons')

for row in cur.fetchall():
    for col in row:
        print col if col else '<NULL>',
    print

cur.close()
session.close()

#!/usr/bin/python

import MySQLdb
import MySQLdb.cursors

from multiprocessing import Process
from random import randint
from time import sleep

def get_session():
    return MySQLdb.connect(host='localhost',
                           user='joffrey',
                           passwd='kings_landing',
                           db='playground',
                           cursorclass=MySQLdb.cursors.SSCursor)

def bootstrap_tables():
    cur = get_session().cursor()
    try:
        print 'trying to create new table'
        cur.execute('create table mp_test (value int)')
    except:
        print 'table alread existed, truncating instead'
        cur.execute('truncate table mp_test')
    cur.close()

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()
Retrieved from "https://wiki.scc.kit.edu/gridkaschool/index.php?title=Relational_Databases&oldid=1746"