world_cities(city,city_ascii,lat,lng,country,iso2,iso3,admin_name,capital,population,id) v PSQL dátami z vyššie uvedeného súboruCOPY world_cities FROM 'world_cities.csv', museli by ste byť prihlásený ako administrátor serveru (root v linuxe), inak PosgreSQL nevie čítať daný súbor. Toto teda nepôjdeword_cities vytvorí tabuľku world_countries(name, iso2, iso3, population, lat, lng), kde name bude názov krajiny, population bude súčtom populácie všetkých miest v krajine a lat,lng bude stredový bod zo všetkých pozícií miest.conn.setAutoCommit(false); conn.commit(); conn.rollback();Connection - Connection c1; Connection c2)
population_changes(country text, year int, population_in int, population_out int) - population_in znamená koľko nových ľudí pribudlo v danej krajine (narodenia + migrácia) a population_out znamená koľko ľudí odbudlo (smrť + migrácia).
covidPandemic(Connection c, String country):
funkcia vypíše na konzolu názov a aktuálnu populáciu danej krajiny, zníži populáciu o 0.1%, zmenu zapíše do tabuľky population_changes a následne zase vypíše na konzolu novú hodnotu populácie (s využitím SELECT dotazu).
Vo funkcii nevolajte ani setAutoCommit(false), ani commit() - budeme ich volať v inej časti kódu
migrationCrisis(Connection c, String srcCountry, String dstCountry):
funkcia vypíše na konzolu názov a aktuálnu populáciu obidvoch krajín, zníži populáciu srcCountry o 1%, a o danú hodnotu zvýši populáciu cieľovej krajiny. Zmeny zapíše do tabuľky population_changes a následne zase vypíše na konzolu nové hodnoty populácie obidvoch krajín (s využitím SELECT dotazu).
Vo funkcii nevolajte ani setAutoCommit(false), ani commit() - budeme ich volať v inej časti kódu
c1.setAutoCommit(false);c2.setAutoCommit(false); covidPandemic(c1, 'Slovakia'); migrationCrisis(c2, 'Ukraine','Slovakia');c1.commit();c2.commit();- skúste to pre viacero krajín za sebou BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE - v JAVE con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);covidPandemic(c1, country), iné migrationCrisis(c2, srcCountry, dstCountry)
"Premature optimization is the root of all evil." (D. Knuth)(Aby sme však príliš nezjednodušovali, pozrite si tiež jeden či dva ďalšie pohľady.)
SELECT relpages, reltuples FROM pg_class WHERE relname = 'ab';
psql na serveri cvika.dcs.fmph.uniba.sk
EXPLAIN SELECT ename, deptno, COUNT(empno) OVER (PARTITION BY deptno) FROM emp;
EXPLAIN SELECT ename, dname, COUNT(empno) OVER (PARTITION BY emp.deptno) FROM emp JOIN dept ON emp.deptno = dept.deptno;
explain analyze select * from ab;
explain analyze select * from ab where b < 4 order by b;
explain analyze select * from ab where b = 4 order by b;
create index i1 on ab (b); -- we hope that adding an index will shorten running time
explain analyze select * from ab where b < 4 order by b;
explain analyze select * from ab where b = 4 order by b;
create index i1h on ab using hash(b);
explain analyze select * from ab where b < 4 order by b;
explain analyze select * from ab where b = 4 order by b;
Vykonajte posledný príkaz desaťkrát po sebe a sledujte, ako sa mení reálny spotrebovaný čas.
drop index i1;
drop index i1h;
explain select * from ab, bc; -- materialize stores the result in memory so that we can look at it more than once
explain select * from ab, bc where ab.b = bc.b;
insert into bc select x.id, x.id + 1 from generate_series(1, 1000000) as x(id);
explain select * from ab, bc where ab.b = bc.b;
explain select b, count(distinct c) from bc where not exists (select 1 from ab, cd where ab.b = bc.b and cd.c < bc.c) group by b having count(distinct c) < 3;
Pozor na použitie analyze: môže to trvať veľmi dlho.
Spusťte psql -f explain.sql. Ideme porovnať plány pre nižšie uvedené dotazy A, B, C, D v niekoľkých rôznych situáciách (líšia sa existenciou indexov a množstvom dát v tabuľkách); odporúčame uložiť si plány pre jednotlivé opakované dotazy do osobitných súborov, aby sa vám ľahko porovnávali.
(A) explain select * from ab, bc where ab.b = bc.b;
(B) explain select * from ab, bc where ab.b = bc.b order by ab.b;
(C) explain select * from ab, bc where ab.b < bc.b;
(D) explain select * from ab, bc where ab.b < bc.b order by ab.b;
Teraz budeme postupne vytvárať indexy a napĺňať tabuľky.
create index i1 on ab (b);
/* run all of A, B, C, D */
create index i2 on bc (b);
/* run all of A, B, C, D */
insert into bc select x.id, x.id + 1 from generate_series(1, 1000000) as x(id);
insert into bc select x.id, x.id + 1 from generate_series(1, 1000000) as x(id);
insert into bc select x.id, x.id + 1 from generate_series(1, 1000000) as x(id);
/* run all of A, B, C, D */
drop index i2;
create index i2composite on bc (b, c);
/* run all of A, B, C, D */
create index i1h on ab using hash(b);
create index i2h on bc using hash(b);
/* run all of A, B, C, D */
drop index i1;
drop index i2composite;
/* run all of A, B, C, D */
Všimnite si, ako "čas" vypočítaný plánovačom rastie, ak index obsahuje nepotrebné stĺpce.
Spusťte psql -f explain.sql a analyzujte plány, ktoré vytvorí plánovač pri nasledujúcich príkazoch.
explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
explain select * from ab, bc, cd where ab.b = bc.b and bc.c < cd.c;
explain select * from ab, bc, cd where ab.b < bc.b and bc.c < cd.c;
create index i3 on cd(c);
explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
explain select * from ab, bc, cd where ab.b = bc.b and bc.c < cd.c;
explain select * from ab, bc, cd where ab.b < bc.b and bc.c < cd.c;
create index i4 on bc (b, c);
explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
explain select * from ab, bc, cd where ab.b = bc.b and bc.c < cd.c;
explain select * from ab, bc, cd where ab.b < bc.b and bc.c < cd.c;
create index i3h on cd using hash(c);
create index i4h on bc using hash(c);
explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
explain select * from ab, bc, cd where ab.b = bc.b and bc.c < cd.c;
explain select * from ab, bc, cd where ab.b < bc.b and bc.c < cd.c;
insert into ab select x.id, x.id + 1 from generate_series(1, 100000) as x(id);
insert into bc select x.id, x.id + 1 from generate_series(1, 100000) as x(id);
insert into cd select x.id, x.id + 1 from generate_series(1, 100000) as x(id);
explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c order by cd.c;
explain select cd.c, count(*) from ab, bc, cd where ab.b = bc.b and bc.c = cd.c group by cd.c;
ALTER USER {vase_prihlasovacie_meno} WITH PASSWORD '{vase_nove_heslo}'
Funkčnosť hesla možno otestovať príkazom psql -h 127.0.0.1 test {vase_prihlasovacie_meno}
ssh -L5432:127.0.0.1:5432 meno_pouzivatela@cvika.dcs.fmph.uniba.sk
\du
\z tablename
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='test';CREATE TABLE test_vasemeno (i INTEGER, t TEXT);a doplňte do nej niekoľko riadkov:
INSERT INTO test_vasemeno VALUES (1, "a");
INSERT INTO test_vasemeno VALUES (2, "b");
[mesto, pocet_dni, pocet_miest], kde pocet_miest určuje počet miest do ktorých za dá dostať z mesta mesto za pocet_dni dní. psql -f world.sql
sqlite3 --init emp.sql emp.db
sqlitebrowser emp.db &alebo spustením
sqlite3 emp.db
SELECT * FROM emp;
sqlite3 emp.db < queries.sql(Na výpočet dotazu môžete tiež využiť záložku Execute SQL v sqlitebrowseri.)
psql -f queries.sql
SELECT * FROM emp;
Za každú z 3 domácich úloh je max. 30 bodov.
Ďalšie body sa získavajú za riešenie úloh jednotlivých cvičení: za každú sadu 0 alebo 1 bod. Do 3 dní po každom cvičení treba riešenia odoslať e-mailom. Následne budú riešenia zbežne zhodnotené: v prípade dostatočnosti bude udelený
1 bod, v prípade nedostatočnosti bude študent vyzvaný riešenie doplniť (ak odovzdal aspoň niečo zmysluplné), na čo má ďalšie 3 dni, a potom dostane definitívne hodnotenie. Nie je potrebné riešiť všetky úlohy; kritérium je mať (aspoň ako-tak správne) vyriešenú aspoň polovicu úloh.
Z bodov udeľovaných za jednotlivé cvičenia je potrebné na úspešné absolvovanie predmetu získať aspoň 9.