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)
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 -L15432:127.0.0.1:5432 meno_pouzivatela@cvika.dcs.fmph.uniba.sk"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 name, deptno, COUNT(empno) OVER (PARTITION BY deptno) FROM emp;
EXPLAIN SELECT emp.name, dept.name, 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); -- the default index type is btree, we want to try hash index too
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;
\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é z 12 cvičení 0 alebo 1 bod. Z bodov udeľovaných za jednotlivé cvičenia je potrebné na úspešné absolvovanie predmetu získať aspoň 9.
Do 3 dní po každom cvičení treba riešenia zadaných úloh odoslať e-mailom na adresu rjasko (zavinac) dcs.fmph.uniba.sk. 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ých cca 40% úloh z každej témy. Ak je sada úloh rozsiahlejšia, zvyčajne sú aspoň približne zoradené podľa obtiažnosti; ak chcete riešiť minimum úloh, vyberte si aj nejaké náročnejšie z druhej polovice. Odporúčaný postup je ísť zaradom a preskakovať úlohy, pri ktorých je jasné, že ich už vyriešiť viete (napr. sú podobné už vyriešeným úlohám a vidíte, v čom spočíva rozdiel a ako sa s ním vysporiadať).