Erinevus lehekülje "PostgreSQL SQL" redaktsioonide vahel

Allikas: Kuutõrvaja
(→‎Trigger)
(→‎PL/Perl)
 
(ei näidata sama kasutaja 268 vahepealset redaktsiooni)
1. rida: 1. rida:
 
===Sissejuhatus===
 
===Sissejuhatus===
  
−
TODO
+
Andmebaasi kasutamise eesmärgiks on saada andmeid salvestada, sälitada ja teha kättesaadavaks.
 +
SQL (Structured Query Languag) keel võimaldab kasutada relatsioonilist andmebaasisüsteemi (RDBMS, relational database management system).
 +
 
 +
Kaasaegsel RDBMS andmebaasil on tavaliselt sellised omadused
 +
 
 +
* http://www.postgresql.org/about.html
 +
* vastab standardile, nt http://en.wikipedia.org/wiki/SQL:2008 (http://www.postgresql.org/docs/8.4/static/features.html)
 +
* toetab MVCC (Multiversion Concurrency Control)
 +
* ACID tugi, http://en.wikipedia.org/wiki/ACID
 +
 
 +
SQL keele võimalusi liigitatakse vahel kolmeks
 +
 
 +
* DML (Data Manipulation Language, nt INSERT, UPDATE, DELETE käsud) - http://en.wikipedia.org/wiki/Data_Manipulation_Language
 +
* DDL (Data Definition Language, nt CREATE, ALTER, DROP käsud) - http://en.wikipedia.org/wiki/Data_Definition_Language
 +
* DCL (Data Control Language, nt GRANT, REVOKE käsud) - http://en.wikipedia.org/wiki/Data_Control_Language
 +
 
 +
Käesolev tekst eeldab, et andmebaasi tarkvara on juurutatud ja kasutajal on olemas sobivad ligipääsud andmebaasiserverile, kusjuures andmete esitamiseks kasutatakse UTF-8 kodeeringut. Näideteks kasutatakse käsurea jaoks selliseid tähistusi
 +
 
 +
* $ või # - vastavalt operatsioonisüsteemi tavakasutaja või juurkasutaja shelli prompt
 +
* psql> - psql andmebaasi sql kliendi prompt
 +
* sql> - suvalise andmebaasi sql kliendi prompt, nt pgadmin SQL query editor ja sealhulgas psql
 +
 
 +
===Andmebaasiga ühendumine===
 +
 
 +
Andmebaasi kasutamise eelduseks on, et
 +
 
 +
* kasutajal on sobiv klientprogramm
 +
* kasutajale on usaldatud ligipääs andmebaasile
 +
 
 +
Tavaliselt toimub andmebaasi kasutamine üle võrgu, st kasutaja töötab andmebaasi klientprogrammiga ühes ja andmebaas töötab teises arvutis. Andmebaasi klientprogrmm ja andmebaasi server suhtlevad omavahel kasutades spetsiaalset selleks ettenähtud protokolli, mis sõltub konkreetsest andmebaasi tarkvarast. Reeglina sisaldab andmebaasitarkvara lisaks andmebaasi serverile ka mingit terminalil kasutatavat nö käsurea klientprogrammi, nt PostgreSQL puhul on selleks programm psql.
 +
 
 +
Andmebaasiühenduse moodustamiseks sobib nt PostgreSQL puhul kasutades psql programmi öelda
 +
 
 +
$ psql -h baas.loomaaed.tartu.ee -U priit baasinimi
 +
Password for user priit: <parool>
 +
sql>
 +
 
 +
kus
  
−
===Andmebaasi klientprogramm===
+
* -h baas.loomaaed.tartu.ee - andmebaasi hostname või ip aadress
 +
* -U priit - kasutajanimi
 +
* baasinimi - andmebaasi nimi
  
−
Reeglina sisaldab andmebaasitarkvara lisaks andmebaasi serverile ka mingit terminalil kasutatavat nö käsurea klientprogrammi, nt PostgreSQL puhul on selleks programm psql.
+
Ilma, et oleks vaja andmebaasis andmeid, saab andmebaasi kasutada nt selliselt
  
 
* andmebaasi kliendi kasutamine kalkulaatorina  
 
* andmebaasi kliendi kasutamine kalkulaatorina  
  
−
  select (5 * 5);
+
  sql> select (5 * 5);
 
   ?column?  
 
   ?column?  
 
  ----------
 
  ----------
17. rida: 56. rida:
 
* andmebaasi klient töötab sõnedega
 
* andmebaasi klient töötab sõnedega
  
−
  select 'Hello' || ' World!';
+
  sql> select 'Hello' || ' World!';
 
     ?column?   
 
     ?column?   
 
  --------------
 
  --------------
25. rida: 64. rida:
 
* Andmebaasi kliendi juures saab määrata esituse formaati, nt (vaikimisi on see aligned)
 
* Andmebaasi kliendi juures saab määrata esituse formaati, nt (vaikimisi on see aligned)
  
−
  \pset format html
+
  psql> \pset format html
 +
 
 +
* arvutada arvu ruutjuur
 +
 
 +
sql> select sqrt(25);
 +
 
 +
===Andmebaasi objektide hierarhia===
 +
 
 +
Andmebaasis sisalduvaid objekte võib kujutleda sellises hierarhias
 +
 
 +
* andmekluster - andmebaasile vastavad failid failisüsteemis, nt kataloogis /var/lib/postgresql/8.4/main, PostgreSQL andmekluster moodustatakse öeldes
 +
 
 +
$ pg_createcluster --locale et_EE.UTF-8 8.4 main --start
 +
 
 +
* andmebaas - käivitatud andmebaasis olev objekt, andmebaasi klustris võib olla mitu andmebaasi
 +
 
 +
sql> create database andmebaasinimi;
 +
 
 +
* skeem - andmebaasis olev objekt, andmebaasis võib olla mitu skeemi
 +
 
 +
sql> create schema skeeminimi;
 +
 
 +
* tabel - skeemis olev objekt, skeemis võib olla mitu tabelit
 +
 
 +
sql> create table skeemininimi.tabelinimi (nimi varchar(50), kaugus integer);
 +
 
 +
===Skeem===
 +
 
 +
Enamus andmbaasi objekte, nt tabelid, mida kasutaja peale andmebaasiga ühendumist kasutab asuvad skeemides (ingl. k. schema) http://www.postgresql.org/docs/current/interactive/ddl-schemas.html. Objektile viitades tuleb skeemi nime kasutada prefiksina, nt olgu andmebaasis kolm skeemi
 +
 
 +
* public - vaikeskeem
 +
* sport_2009 - skeem kuhu on ette nähtud paigutada 2009 aasta treeningute tulemused
 +
* sport_2010 - skeem kuhu on ette nähtud paigutada 2009 aasta treeningute tulemused
 +
 
 +
Seejuures võib olla olemas kaks tabeli nime mõttes sama nimega tabelit, kuid andmebaasis siiski on nad erinevad tabelid
 +
 
 +
sport_2009.ujumine
 +
sport_2010.ujumine
 +
 
 +
Kui tabeliga tegelemisel ei ole esitatud skeemi nime, siis vaikimisi tegeldakse skeemi public objektidega.
 +
 
 +
===Kasutajaõigused===
 +
 
 +
Andmebaasi tavakasutajal on tavaliselt voli tegutseda ühe andmebaasi ühes kasutajanimelises skeemis. Kõik kasutaja poolt moodustatud objektid kuuluvad kasutaja omandusse, st tal on võimalik nendega teha igasuguseid tegevusi, sh neid muuta ja kustutada, lisaks kontrollida neile teiste kasutajate ligipääse.
 +
 
 +
Vaikimisi saab PostgreSQL kasutaja töötada lisaks omanimelisele skeemile ka public skeemis.
 +
 
 +
===Tabel===
 +
 
 +
Andmebaasis paiknevad andmed asuvad tabelites. Tabelis esitatakse andmeid ridade kaupa, kusjuures iga rida koosneb mitmest veerust ehk väljast. Kirjeldades tabelit sellise konkreeste näite alusel
 +
 
 +
id      isikukood    eesnimi  perekonnanimi      synniaeg        sissekande_aeg
 +
  1    37802249999      Priit          Kask    1978-02-24  2010-03-29 15:45:20
 +
  2    46406259999      Maila            Laa    1964-06-25  2010-03-29 15:46:24
 +
  3    38612249999      Mart          Kask    1886-12-24  2010-03-29 15:47:44
 +
 
 +
võib öelda
 +
 
 +
* ühte tabelisse kantakse sama tüüpi objektide erinevate eksemplaride kohta käivaid andmeid
 +
* tabelil on nimi, näiteks võiks see olla inimene
 +
* tabelis esineb viis välja (ingl. k. field), öeldakse ka, et viis atribuuti: id, isikukood, eesnimi, perekonnanimi, synniaeg ja sissekande_aeg
 +
* igal rea väljal on väärtus
 +
* tabelis on kolm rida (ingl. k. row) ehk kirjet (ingl. k. record)
 +
* tabeli atribuutid on alati mingit kindlat tüüpi, nt täisarv, määratletud või määratlemata pikkusega tekst, kuupäev
 +
* põhimõtteliselt ei ole tabelis read järjestatud
 +
* põhimõtteliselt ei ole reas väljad järjestatud
 +
* reeglina on tabelid hästi pikad ja kitsad, st tabelis on suhteliselt palju ridu ja vähe veerge
 +
 
 +
Relatsioonilises andmebaasis on tabeliga seotud lisaks seal paiknevatele andmete ka mitmeid lisaomadusi, nt
 +
 
 +
* tabeli read on unikaalsed
 +
* primaarne võti (ingl. k. primary key) - võtmega seotud välja väärtuse järgi saab rida identifitseerida
 +
* välisvõti (ingl. k. foreign key) - kasutatakse seose loomiseks antud tabeli mõne välja ja mõne teise tabeli samatüübilise välja vahel
 +
* võimalused mitmete tabelite andmete kooskõlalise (ingl. k. integrity) tagamiseks
 +
 
 +
Tabeli moodustamiseks sobib öelda
 +
 
 +
CREATE TABLE inimene
 +
(
 +
  inimene_id serial NOT NULL,
 +
  isikukood character (13),
 +
  eesnimi varchar(50),
 +
  perekonnanimi varchar(50),
 +
  synniaeg date,
 +
  sissekande_aeg timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
 +
  CONSTRAINT inimene_id_pkey PRIMARY KEY (inimene_id)
 +
)
 +
 
 +
kus
 +
 
 +
* tabeli nimi esitatakse tavaliselt ainsuses
 +
* serial tähendab, et välja väärtus on täisarv ja seda suurendatakse iga sissekande tegemisel automaatselt ühe võrra
 +
* NOT NULL tähendab, et andmebaas kontrollib, et väljal oleks määratletud väärtus
 +
* isikukood välja tuleb alati sisestada 13 tähelise pikkusega sõna, ei lühem ega pikem
 +
* eesnimi, perekonnanimi väljade väärtustes võivad olla kuni 50 tähe pikkused sõned
 +
* synniaeg - välja väärtus on kuupäev, st kujul YYYY-MM-DD (aasta-kuu-päev ehk year-month-day)
 +
* sissekande_aeg välja väärtus peab olema kuupäeva ja kellaaeg tüüpi, nt '2010-03-29 15:45:20', kusjuures väärtus sisestatakse kirja moodustamisel automaatselt
 +
* CONSTRAINT tähendab, et inimene_id väljale moodustatakse primaarne võti
 +
 
 +
Relatsioonilises andmebaasis nimetatakse tabelit ka relatsiooniks, ja mitte tablite vahelisi seoseid.
 +
 
 +
===Tabeli kasutamine===
 +
 
 +
Põhiliselt seisneb andmebaasi tabeli kasutamine kolme sorti tegevustes
 +
 
 +
* andmete lisamine (insert) - tabelisse lisatakse rida
 +
* andmete lugemine (select) - tabelist loetakse andmeid
 +
* andmete muutmine (update) - tabeli olemasoleva rea ühe või enama välja väärtusi muudetakse
 +
* andmete kustutamine (delete) - tabelist kustutatakse rida
 +
 
 +
====Andmete lisamine====
 +
 
 +
Tabelisse kirje lisamiseks on käsk insert, nt selliselt
  
−
===Select===
+
sql> insert into inimene (isikukood, eesnimi, perekonnanimi, synniaeg) values ('46705079999', 'Marta', 'Maara','1967-05-07');
  
−
select lausega saab lihtsamal juhul kasutada andmebaasis kirjeldatud funktsioone, nt
+
====Andmete lugemine====
  
−
  select sqrt(25);
+
Tabelist andmete lugemiseks on käsk select, nt küsides rea, kus isikukoodi väärtus on 46705079999
 +
 
 +
  sql> select isikukood, perekonnanimi from inimene where isikukood = '46705079999';
 +
 
 +
====Andmete muutmine====
 +
 
 +
Tabelis andmete muutmiseks on käsk update, nt muudame selle rea perekonnanimi Marat'iks kus on isikukood 46705079999
 +
 
 +
sql> update inimene set perekonnanimi = 'Marat' where isikukood = '46705079999';
 +
 
 +
====Andmete kustutamine====
 +
 
 +
Tabelist andmete kustutamiseks on käsk dalete, nt kustutame rea, kus isikukood on 46705079999
 +
 
 +
sql> delete from inimene where isikukood = '46705079999';
 +
 
 +
Kogu tabeli sisu saab nö kiiresti kustutada öeldes
 +
 
 +
sql> truncate table inimene;
  
 
===Andmetüübid===
 
===Andmetüübid===
 +
 +
PostgreSQL SQL implementatsioon on tugeva tüübiskeemiga (ingl. k. strongly typed) keel, mis tähendab, et teatud tehted saab teha vaid sama tüüpi andmetega või tuleb sooritada eelnevalt vastav tüübiteisendus. Käesolevas punktis käisitletakse PostgreSQLis kasutusel olevaid andmetüüpe (ingl. k. data type) http://www.postgresql.org/docs/current/interactive/datatype.html.
 +
 +
Ilmutatud kujul saab andmete tüüpi väljendada kahel samaväärsel viisil
 +
 +
sql> select varchar(20) 'tere';
 +
  varchar
 +
---------
 +
  tere
 +
 +
sql> select 'tere'::varchar(20);
 +
  varchar
 +
---------
 +
  tere
 +
 +
Lisaks on tüübi täpsustamiseks cast funktsioon, nt
 +
 +
sql> select cast ('Loomaaed on avatud tööpäeviti' as text);
 +
            text           
 +
----------------------------
 +
  Loomaaed on avatud tööpeviti
 +
 +
====Arvulised tüübid====
 +
 +
Näiteks saab kasutada sellised arvutilisi tüüpe
 +
 +
* integer - 4 baiti pikk täisarv, omandab väärtusi vahemikus -2147483648 kuni +2147483647
 +
* serial - automaatselt suurenev täisarv, omandab väärtusi vahemikus 1 kuni 2147483647
 +
* real - 4 baiti pikk reaalarvuline tüüp
 +
 +
====Sõnetüübid====
 +
 +
PostgreSQL'is on olemas sellised sõnetüübid (ingl. k. character type)
 +
 +
* varchar(n), character varying(n) - vahemikus 0 - n muutuva suurusega string
 +
* char(n) - täpselt pikkusega n string
 +
* text - piiramatu pikkusega string
 +
 +
====Kuupäev/kellaaeg tüübid====
 +
 +
Kuupäev/kellaaeg (ingl. k. Date/Time types) tüüpi andmed väljendavad
 +
 +
* date - kuupäeva
 +
* time - kellaaeg
 +
* timestamp - kuupäeva ja kellaaega
 +
 +
Lisaks on võimalik kahe viimase puhul täpsustada ajavööndit.
 +
 +
====Tõeväärtustüübid====
 +
 +
Tõeväärtustüüpi atribuudid saavad omada väärtusi 0 või 1 ehk false või true.
 +
 +
====Võrguaadresstüübid====
 +
 +
* inet - ipv4 ja ipv6 addressi tüüpi andmed
 +
* mac - mac aadressi tüüpi andmed
 +
 +
Nt saab inet kujul esitatud andmetega teha liitmistehet, mis suurendab ip aadressi osa
 +
 +
sql> select ip + 20 from serverivorguseadistused where hostname = 'baas.loomaaed';
 +
 +
====Massiivtüübid====
 +
 +
Massiivitüüp võimaldab atribuudi väärtustena kasutada kõiki eelkirjeldatud andmetüüpe, kuid selliselt, et neist saab moodustada määratlemata pikkusega massiive.
 +
 +
sql> select '{"element üks", "element kaks"}';
 +
 +
====NULL====
 +
 +
NULL väärtust kasutatakse kahes erinevas tähenduses
 +
 +
* väärtust ei ole teada
 +
* väärtus ei ole rakendatav (ingl. k. not applicable, N/A)
 +
 +
Kasulikud lisamaterjalid
 +
 +
* http://en.wikipedia.org/wiki/Null_(SQL)
 +
 +
====Andmetüüpide vahelised teisendused====
 +
 +
Funktsiooni cast abil saab andmete tüüpe teisendandada, nt
 +
 +
sql> select cast (3.14 as text);
 +
 +
===Loendur===
 +
 +
Loenduri (ingl. k. sequence) käest saab küsida järjestikulisi väärtusi, vaikimisi need väärtused kasvavad alatest ühest ühe kaupa.
 +
 +
Loenduri moodustamiseks sobib öelda
 +
 +
CREATE SEQUENCE loendurinimi start 1;
 +
 +
ja loenduri käest saab küsida järgmist väärtust
 +
 +
SELECT nextval('loendurinimi');
 +
 +
Praktiliselt kasutatakse loendureid mõne tabeli unikaalsete automaatselt kasvavate väärtustega attribuudina. Nt moodustades tabeli selliselt
 +
 +
CREATE TABLE mart.inimene
 +
(
 +
  inimene_id serial NOT NULL,
 +
  nimi character varying(50),
 +
  vanus integer,
 +
  CONSTRAINT inimene_id_pkey PRIMARY KEY (inimene_id)
 +
)
 +
 +
tekitatakse automaatselt loendur inimene_inimene_id_seq
 +
 +
CREATE SEQUENCE mart.inimene_inimene_id_seq
 +
  INCREMENT 1
 +
  MINVALUE 1
 +
  MAXVALUE 9223372036854775807
 +
  START 5
 +
  CACHE 1;
 +
 +
===Failisüsteemi kasutamine===
 +
 +
copy käsu abil saab kopeerida andmeid failisüsteemist andmebaasi ja vastupidi. Failis asuvad andmed peavad esinema üks kirje real ning väljad arusaadavalt eraldatud, kuid muus osas on tegu tavalise tekstifailiga.
 +
 +
====Failisüsteemi salvestamine====
 +
 +
Andmebaasi tabeli nimed sisu kopeerimiseks failisüsteemi sobib öelda
 +
 +
sql> copy nimed to '/tmp/priit.txt';
 +
 +
====Failisüsteemist lugemine====
 +
 +
Olemasolevasse tabelisse priit.priidutabel failisüsteemi failist /tmp/andmed.txt andmete kopeerimiseks sobib öelda
 +
 +
copy priit.priidutabel (vanus, nimi) from '/tmp/andmed' with delimiter ' ';
 +
 +
===SQL skripti kasutamine===
 +
 +
psql programmi abil saab täita kliendi failisüsteemis asuvat skripti /tmp/skript.sql
 +
 +
psql> \i /tmp/skript.sql
 +
 +
===Tabelite vahelised seosed===
 +
 +
Tabelite vahelisi seoseid kasutatakse andmete kooskõlalisuse kehtestamiseks ja neid kirjeldatakse võtmete abil.
 +
 +
====Primaarne võti====
 +
 +
Primaarse võtmega (ingl. k. primary key) tabeli moodustamine
 +
 +
CREATE TABLE tabelinimi (
 +
  id          SERIAL PRIMARY KEY,
 +
  title      CHARACTER VARYING(80)
 +
);
 +
 +
Olemasolevale tabelile võtme lisamine
 +
 +
sql> ALTER TABLE tabelinimi ADD CONSTRAINT valjanimi_id_pk PRIMARY KEY (valjanimi_id);
 +
 +
Primaarse võtme eemaldamiseks sobib öelda
 +
 +
sql> ALTER TABLE tabelinimi DROP CONSTRAINT valjanimi_id_pk;
 +
 +
====Välisvõti====
 +
 +
Välisvõtme (ingl. k. foreign key) kasutamiseks sobib moodustada tabel selliselt
 +
 +
CREATE TABLE rentals (
 +
  tape_id    CHARACTER(8) REFERENCES tapes,
 +
  customer_id INTEGER      REFERENCES customers,
 +
  rental_date DATE
 +
);
 +
 +
====Enesele viitav välisvõti====
 +
 +
Enesele viitav välisvõti (ingl. k self-refrencing foreign key) viitab sama tabeli mõnele unikaalsele väljale, nt
 +
 +
CREATE TABLE vanemadjalapsed
 +
(
 +
  inimesenimi character varying(50) NOT NULL,
 +
  lapsenimi character varying(50),
 +
  vanemanimi character varying(50),
 +
  CONSTRAINT vanemajalapsed_pkey PRIMARY KEY (inimesenimi),
 +
  CONSTRAINT vanemadjalapsed_fk FOREIGN KEY (vanemanimi)
 +
      REFERENCES vanemadjalapsed (inimesenimi) MATCH SIMPLE
 +
      ON UPDATE CASCADE ON DELETE NO ACTION
 +
)
 +
 +
kus
 +
 +
* inimesenimi - ...
 +
* lapsenimi - ...
 +
* vanemanimi - ...
 +
* on update cascade - tagab, et kui mõni inimesenimi väärtus muutub, siis muutub ka vastava nime kasutus kõigis vanemanimi lahtrites kus seda on kasutatud
 +
 +
===Indeks===
 +
 +
Indeks (ingl. k. index) http://www.postgresql.org/docs/current/interactive/indexes.html on andmebaasi instrument, mida kasutatakse selleks, et kiirendada päringuid. Põhimõtteliselt on andmebaasi indeks saranane raamatu lõpus kasutatavale indeksile, kuna indeksis on sissekanded esitatud tähestikulises järjekorras, saab leida suhteliselt kiire huvipakkuva sissekande ning seejärel lehekülje numbri järgi koha raamatu tekstis endas.
 +
 +
Andmebaasis indekseeritakse tavaliselt neid tabeli välju, mida kasutatakse tavaliselt tabelist päringute tegemisel nö where järele. Kuna indeksi pidamisega kaasneb täiendav ressursikulu, siis ei maksa indeksitega ka liialdada
 +
 +
* indeks võtab salvestusseadmel märkimisväärselt ruumi
 +
* indeksi moodustamisel on tabel reeglina kirjutamiste jaoks lukustatud olekus
 +
* kuna tabeli andmete muutmisel tuleb muuta vastavalt ka indeksit, siis tuleb andmebaasil selleks teha lisatööd
 +
 +
Tabeli priit.priidutabel atribuudile nimi indeksi moodustamiseks sobib öelda
 +
 +
sql> CREATE INDEX nimi_idx ON priit.priidutabel (nimi);
 +
 +
Indeksi eemaldamiseks sobib öelda
 +
 +
sql> drop index priit.nimi_idx;
 +
 +
Samas skeemis peavad kõigil indeksitel olema unikaalsed nimed. Indeksi moodustamisel peab arvestama, et kuigi tabelit saab lugeda, on sel ajal update, insert ja delete operatsioonid keelatud.
 +
 +
===Vaade===
 +
 +
Vaade (ingl. k. view) http://www.postgresql.org/docs/current/interactive/tutorial-views.html on virtuaalne tabel, mis on moodustatakse igal vaate poole pöördumisel vaate kirjeldamisel kasutatud sql päringu alusel. Iseenesest on vaade ainult lugemiseks ja seal muuta ega sinna lisada andmeid ei saa.
 +
 +
Näiteks olgu olemas kaks tabelit, priit.linn
 +
 +
CREATE TABLE priit.linn
 +
(
 +
  linn_id serial NOT NULL,
 +
  nimi character varying(50),
 +
  CONSTRAINT linn_id_pkey PRIMARY KEY (linn_id)
 +
)
 +
 +
ja priit.inimene
 +
 +
CREATE TABLE priit.inimene
 +
(
 +
  inimene_id serial NOT NULL,
 +
  nimi character varying(50),
 +
  linn_id integer,
 +
  CONSTRAINT nimi_id_pkey PRIMARY KEY (inimene_id),
 +
  CONSTRAINT linn_fkey FOREIGN KEY (linn_id)
 +
      REFERENCES priit.linn (linn_id) MATCH SIMPLE
 +
      ON UPDATE NO ACTION ON DELETE NO ACTION
 +
)
 +
 +
Tartlaste nimesid ja linnanime sisaldava vaate moodustamiseks sobib öelda
 +
 +
sql> create view tartlane as
 +
  select priit.inimene.nimi as inimesenimi, priit.linn.nimi as linnanimi
 +
  from priit.inimene, priit.linn
 +
  where priit.inimene.linn_id=priit.linn.linn_id and priit.linn.nimi='Tartu';
 +
 +
Vaate kasutamiseks sobib öelda
 +
 +
sql> select * from tartlane;
 +
 +
Vaate eemaldamiseks tuleb öelda
 +
 +
sql> drop view tartlane;
 +
 +
Kasulikud lisamaterjalid
 +
 +
* http://en.wikipedia.org/wiki/View_(database)
 +
 +
===Reeglid===
 +
 +
Reeglid (ingl. k. rules) http://www.postgresql.org/docs/current/static/rules.html võimaldavad muuta päringuid enne nende päringu plaanija (ingl. k. query planner) juurde jõudmist ja päringute täitmist.
 +
 +
Nt eeldusel, et skeemis reeglid on olemas tabelid essa ja tessa võiks selline reegel kõik insert sisestused sooritada tabelisse tessa juhul, kui nimi väärtuseks on 'Mart'
 +
 +
CREATE RULE sisesta_tessasse AS
 +
ON INSERT TO reeglid.essa WHERE nimi = 'mart'
 +
DO INSTEAD
 +
    INSERT INTO reeglid.tessa values (new.*);
 +
 +
===Samaaegsete tegevuste kontroll===
 +
 +
Samaaegsete tegevuste kontroll (ingl. k. concurrency control) http://www.postgresql.org/docs/current/interactive/mvcc.html võimaldab tagada, et samaaegselt andmebaasis toimuvad tegevused oleksid vajalikul määral üksteisest isoleeritud.
 +
 +
====Transaktsioon====
 +
 +
Transaktsiooniks nimetatakse sellist järgnevust tegevusi, mis toimuvad kas kõik või siis ei toimu neist ühtegi. See on üks fundamentaalne andmebaaside omandus, et tagada andmete kooskõlalisus.
 +
 +
PostgreSQLis märgib transaktsiooni algus begin ning lõppu end, nt
 +
 +
sql> begin;
 +
sql> select ... ;
 +
sql> update ... ;
 +
sql> commit;
 +
 +
Kui transaktsioon mingil põhjusel ei õnnestunud, siis öeldakse, et sooritatakse rollback (ingl. k. tagasikerimine). Ilmutatud kujul saab teha rollback'i öeldes transaktsiooni lõpus commit asemel
 +
 +
sql> rollback;
 +
 +
====Lukud====
 +
 +
Transaktsiooni sees saab ilmutatult kasutada lukku, nt
 +
 +
sql> begin;
 +
BEGIN
 +
sql> lock table priit.priidutabel in access exclusive mode;
 +
LOCK TABLE
 +
...
 +
sq> commit;
 +
 +
kus access exclusive lukk tagab, et ainult kõneksolev transaktsioon kasutab tabelit priit.priidutabel.
 +
 +
PostgreSQL andmebaasis on kasutada seitse erinevat lukustust
 +
 +
* ACCESS SHARE MODE -
 +
* ROW SHARE MODE -
 +
* ROW EXCLUSIVE MODE
 +
* SHARE MODE
 +
* SHARE ROW EXCLUSIVE MODE
 +
* EXCLUSIVE MODE
 +
* ACCESS EXCLUSIVE MODE
 +
 +
Ülidselt võib öelda, et shared lukud keelevad vastavale ressursile saada samaaegselt mõnda exclusive lukku. Kui mõni teine kasutaja pöördub parasjagu lukustatud ressursi poole, siis ta ei saa veateadet vaid ootab seni kuni ressurss vabastatakse lukust.
 +
 +
====FOR UPDATE====
  
 
TODO
 
TODO
  
−
===Sekventsid===
+
====Tupik====
 +
 
 +
Tupik (ingl. k. deadlock) on selline situatsioon, kus kaks protsessi ootavad üksteise järel, et saada tegevusega edasi minna. Üldiselt PostgreSQL lahendab tupiku olukorrad automaatselt.
 +
 
 +
Kasulikud lisamaterjalid
 +
 
 +
* http://www.phpriot.com/articles/locking-in-postgresql
 +
* http://www.sai.msu.su/~megera/postgres/gist/papers/concurrency/transactions.pdf
 +
* http://www.postgresql.org/files/developer/concurrency.pdf
 +
 
 +
===Protseduurilised keeled===
 +
 
 +
Protseduurilised keeled (ingl. k. procedural language, PL) http://www.postgresql.org/docs/current/interactive/xplang.html võimaldavad kirjutada funktsioone (nt triggerite jaoks) muudes keeltes peale otseselt SQL ja C. PL keeles kirjutatud funktsiooni puhul andmebaas ise ei oska interpreteerida funktsiooni lähtekoodi, kuid see ülesanne delegeeritakse vastava keelega seotud handlerile. Handler esineb C shared teegi kujul ja tegeleb funktsiooniga ise või omakorda kasutab selleks operatsioonisüsteemi paigaldatud teisi teeke.
 +
 
 +
PostgreSQL sisaldab otseselt tuge neljale PL keelele
 +
 
 +
* PL/pgSQL
 +
* PL/Tcl
 +
* PL/Perl
 +
* PL/Python
 +
 
 +
Lisamoodulitena saab kasutada sellised PL keeli, http://www.postgresql.org/docs/current/interactive/external-pl.html
 +
 
 +
* PL/Java - Java, http://pljava.projects.postgresql.org/
 +
* PL/PHP - PHP, http://www.commandprompt.com/community/plphp/
 +
* PL/Py - Python, http://python.projects.postgresql.org/
 +
* PL/R - R, http://www.joeconway.com/plr/
 +
* PL/Ruby - Ruby, http://raa.ruby-lang.org/project/pl-ruby/
 +
* PL/Scheme - Scheme, http://plscheme.projects.postgresql.org/
 +
* PL/sh - Unix shell, http://plsh.projects.postgresql.org/
 +
* PL/Proxy, http://plproxy.projects.postgresql.org/
 +
 
 +
====PL/pgSQL====
 +
 
 +
Lihsamal juhul võiks kirjeldada funktsiooni selline järgnevus
  
−
TODO
+
CREATE OR REPLACE FUNCTION liitja (INTEGER, INTEGER) RETURNS INTEGER AS $$
 +
  BEGIN
 +
    return $1 + $2;
 +
  END;
 +
$$ LANGUAGE 'plpgsql';
 +
 
 +
Funktsiooni kasutamiseks sobib öelda
 +
 
 +
select liitja(10,50);
 +
 
 +
Funktsiooni kustutamiseks sobib öelda
 +
 
 +
drop function liitja(integer, integer);
 +
 
 +
====PL/Perl====
 +
 
 +
PL/Perl (Perl Procedural Language) http://www.postgresql.org/docs/current/interactive/plperl.html võimaldab kirjutada andmebaasi funktsiooni Perl keeles.
 +
 
 +
Nt selline funktsioon tegeleb sünniaasta arvutamisega 2010 aastal
 +
 
 +
CREATE OR REPLACE FUNCTION plperlu_synniaastaarvutaja(character varying, integer)
 +
  RETURNS character varying AS
 +
$BODY$
 +
  $synniaasta=2010 - $_[1];
 +
  return "Kasutaja: $_[0] synniaasta on: $synniaasta";
 +
$BODY$
 +
  LANGUAGE 'plperlu' VOLATILE
 +
 
 +
Funktsiooni kasutamiseks tuleb öelda
 +
 
 +
SQL> select plperlu_synniaastaarvutaja('Priit Kask', '60')
 +
 
 +
plperlu keeles saab ka operatsioonisüsteemi vahendeid kasutada sh failisüsteemi
 +
 
 +
CREATE OR REPLACE FUNCTION pgenv()
 +
  RETURNS character varying AS
 +
$BODY$
 +
open (fh, '>>/tmp/pgenv.log');
 +
foreach $key (sort(keys %ENV)) {
 +
  print fh "$key = $ENV{$key}\n";
 +
}
 +
close fh;
 +
return "printisin just keskkonnamuutujad /tmp/pgenv.log faili";$BODY$
 +
LANGUAGE 'plperlu' VOLATILE
 +
 
 +
Funktsiooni kasutamiseks tuleb öelda
 +
 
 +
SQL> select pgenv();
 +
 
 +
ja tulemusena tekitatakse fail /tmp/pgenv.log.
 +
 
 +
====PL/Proxy====
 +
 
 +
PL/Proxy kasutamiseks peab tarkvara olema paigaldatud ainult sellesse andmebaasi, kust päriguid esitatakse teise andmebaasi.
 +
 
 +
Teises andmebaasis peab olema kirjeldatud huvipakkuv funktsioon, nt
 +
 
 +
CREATE OR REPLACE FUNCTION plproxy.liitja(integer, integer)
 +
  RETURNS integer AS
 +
$BODY$
 +
  BEGIN
 +
    return $1 + $2;
 +
  END;
 +
$BODY$
 +
  LANGUAGE 'plpgsql' VOLATILE
 +
 
 +
ja nö esimeses andmebaasis kirjeldatud plproxy keeles funktsioon
 +
 
 +
CREATE OR REPLACE FUNCTION plproxy.eemal_liitja(arv1 integer, arv2 integer)
 +
  RETURNS text AS
 +
$BODY$
 +
  CONNECT 'dbname=test host=192.168.10.206 user=postgres password=parool';
 +
  SELECT * from plproxy.liitja(arv1, arv2);
 +
$BODY$
 +
  LANGUAGE 'plproxy' VOLATILE
 +
 
 +
Funktsiooni kasutamiseks sobib öelda esimese baasis
 +
 
 +
select plproxy.eemal_liitja(50, 100);
 +
 
 +
Kasulikud lisamaterjalid
 +
 
 +
* http://kaiv.wordpress.com/category/plproxy/
 +
 
 +
====PL/Python====
 +
 
 +
PL/Python (Python Procedural Language) http://www.postgresql.org/docs/current/interactive/plpython.html võimaldab kirjutada andmebaasi funktsiooni Python keeles.
 +
 
 +
Nt selline funktsioon tegeleb sünniaasta arvutamisega 2010 aasta
 +
 
 +
CREATE OR REPLACE FUNCTION plpythonu_synniaastaarvutaja(nimi character varying, vanus integer)
 +
  RETURNS character varying AS
 +
$BODY$
 +
  synniaasta = 2010 - vanus
 +
  return "Kasutaja: " + nimi + "synniaasta on: " + str(synniaasta)
 +
$BODY$
 +
  LANGUAGE 'plpythonu' VOLATILE
 +
 
 +
Funktsiooni kasutamiseks sobib öelda
 +
 
 +
select plpythonu_synniaastaarvutaja('Priit Kask', '60')
 +
 
 +
====PL/Java====
 +
 
 +
* Java teegi andmebaasi kopeerimiseks tuleb öelda
 +
 
 +
  SQL> select sqlj.install_jar('file:///home/mart/helloworld.jar', 'helloworld_jar',  false);
 +
 
 +
Kusjuures baasi laaditud java teekide nimekirja saab küsida
 +
 
 +
  SQL> select jarname, jarorigin from sqlj.jar_repository;
 +
 
 +
* Seostame selle skeemiga, kust PL/Java funktsioone kasutatakse classpath'i öeldes
 +
 
 +
  SQL> SELECT sqlj.set_classpath('skeeminimi', 'helloworld_jar');
 +
 
 +
* PL/Java funktsiooni tekitamine
 +
 
 +
  CREATE OR REPLACE FUNCTION helloworld()
 +
    RETURNS "varchar" AS
 +
      'com.mycompany.helloworld.HelloWorld.helloWorld'
 +
    LANGUAGE 'java' VOLATILE;
 +
 
 +
* PL/Java funktsiooni käivitamine
 +
 
 +
  SQL> select * from helloworld();
 +
 
 +
* PL/Java funktsiooni kasutamine plpgsql funktsioonist
 +
 
 +
  CREATE OR REPLACE FUNCTION testlocale() RETURNS TIMESTAMP AS
 +
  $body$
 +
  DECLARE
 +
    dummy timestamp;
 +
  BEGIN
 +
    perform helloworld();
 +
    dummy := current_timestamp;
 +
    return dummy;
 +
  END;
 +
  $body$
 +
  LANGUAGE 'plpgsql';
 +
 
 +
===Aja kasutamine===
 +
 
 +
PostgreSQL andmebaasis saab aega esitada viiel moel, http://www.postgresql.org/docs/current/interactive/datatype-datetime.html ja http://www.postgresql.org/docs/current/interactive/functions-datetime.html
 +
 
 +
* timestamp without time zone - kuupäev ja kellaaeg ilma ajatsoonita
 +
* timestamp with time zone - kuupäev ja kellaaeg ajatsooniga
 +
* data - kuupäev
 +
* time without time zone - kellaaeg ilma ajatsoonita
 +
* time with time zone - kellaaeg ajatsooniga
 +
* interval - ajavahemik
 +
 
 +
====Aja väljendamine sisendis ja väljundis====
 +
 
 +
PostgreSQL lubab väljandada aega sisendis üsna paindlikult, nt töötavad sellised variandid
 +
 
 +
sql> select timestamp '2010-03-27 22:44:55';
 +
      timestamp     
 +
---------------------
 +
  2010-03-27 22:44:55
 +
 
 +
sql> select timestamp with time zone '2010-03-27 22:44:55';
 +
      timestamptz     
 +
------------------------
 +
  2010-03-27 22:44:55+02
 +
 
 +
sql> select date 'Mar 27 2010';
 +
    date   
 +
------------
 +
  2010-03-27
 +
 
 +
sql> select date '27 Mar 2010';
 +
    date   
 +
------------
 +
  2010-03-27
 +
 
 +
Vaikimisi väljendatakse aega väljundis igal juhtumil ISO standardile vastavalt.
 +
 
 +
====Tehted ajaga====
 +
 
 +
Põhiliselt kasutatakse seoses ajaga kahte tehet: liitmine/lahutamine ja võrdlus. Seejuures on abiks ajavahemiku tüüp 'interval'.
 +
 
 +
* liitmine ja lahutamine, nt arvutame milline oli kuupäev ja kellaaeg 9 päeva ja 23 minutit tagasi
 +
 
 +
sql> select current_timestamp - interval '9 days 23 minutes';
 +
            ?column?           
 +
-------------------------------
 +
  2010-03-18 23:39:08.328026+02
  
−
===Join===
+
* võrdlus, nt võrdleme, kas inimene on sündinud enne või peale 1. jaanuar 1980
  
−
TODO
+
CREATE OR REPLACE FUNCTION kunassyndis(synniaeg timestamp)
 +
  RETURNS character varying AS
 +
$BODY$
 +
  declare
 +
  vastus varchar(50);
 +
begin
 +
    if synniaeg < '1980-01-01' then
 +
        vastus = 'Sündinud enne 1. jaanuar 1980';
 +
    else
 +
        vastus = 'syndinud peale 1. jaanuar 1980';
 +
    end if;
 +
    return vastus;
 +
end;
 +
    $BODY$
 +
LANGUAGE 'plpgsql' VOLATILE
  
−
===Mõisted===
+
ja funktsiooni kasutamine toimub
  
−
* result set - vastus andmebaasile esitatud päringule, või sisaldada mitte midagi, ühte või enamat rida
+
select kunassyndis('1970-02-31');
 +
          kunassyndis         
 +
-------------------------------
 +
  Sündinud enne 1. jaanuar 1980
  
−
===Misc laused===
+
====extract ja date_trunk funktsioonid====
  
−
* tabel ühte tehtud päringu tulemuste lisamine tabel kahte
+
* extract funktsiooni abil saab timestamp tüüpi andmetest välja eraldada elemente, nt küsida sajandit
  
−
  insert into tabel2 (nimi, aadress) select nimi, aadress from tabel1
+
  SELECT EXTRACT(CENTURY FROM TIMESTAMP '1999-12-16 12:21:13');
 +
  date_part
 +
-----------
 +
        20
 +
* date_trunc funktsioon esitab esimesena antud parameetrist kõik vähemtäpsed väljad
  
−
* Andmete kopeerimine failisüsteemi
+
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
 +
  date_trunc     
 +
---------------------
 +
  2001-02-16 20:00:00
  
−
copy customers to '/tmp/customers.txt';
+
====Ajatsoonide vahelised teisendused====
  
−
* Andmete kopeerimine failisüsteemist
+
'at time zone' abil saab teisendada ühe tsooni aega teise tsooni kohalikuks ajaks, nt küsida, et kui Hong Kongis on kell 17:20, mis on siis kohalik aeg Almatas
  
−
  copy customers from '/tmp/customers.txt';
+
  SELECT TIMESTAMP with time zone '2001-02-16 17:20 HKT' at time zone 'ALMT';
 +
      timezone     
 +
---------------------
 +
  2001-02-16 15:20:00
  
−
===Indeksid===
+
====Aeg ja transaktsioon====
  
−
TODO
+
Käesoleva aja väärtusega vastamisel eristab PostgreSQL kahte juhtumit
  
−
===Vaated===
+
* transaktsiooni algus
 +
* reaalne käesolev aeg
  
−
TODO
+
Selliselt
  
−
===Transaktsioon===
+
sql> begin;
 +
BEGIN
 +
sql> select current_timestamp;
 +
              now             
 +
------------------------------
 +
  2010-03-28 12:35:42.77614+03
  
−
TODO
+
sql> select current_timestamp;
 +
              now             
 +
------------------------------
 +
  2010-03-28 12:35:42.77614+03
  
−
===Jõudlus===
+
sql> select clock_timestamp();
 +
        clock_timestamp       
 +
-------------------------------
 +
  2010-03-28 12:35:58.983693+03
  
−
  explain verbose select * from teenused where nimi='teenuse nimi_O_13437';
+
  sql> select clock_timestamp();
−
                                  QUERY PLAN                               
+
        clock_timestamp       
−
  ----------------------------------------------------------------------------
+
  -------------------------------
−
  Bitmap Heap Scan on teenused2 (cost=6.60..196.17 rows=48 width=48)
+
  2010-03-28 12:36:02.399565+03
−
    Output: id, nimi, aadress
+
−
    Recheck Cond: ((nimi)::text = 'teenuse nimi_O_13437'::text)
+
sql> commit;
−
    ->  Bitmap Index Scan on teenused_idx  (cost=0.00..6.59 rows=48 width=0)
+
  COMMIT
−
          Index Cond: ((nimi)::text = 'teenuse nimi_O_13437'::text)
+
 
 +
kust on näha, et currect_timestamp andis samu väärtusi transaktsiooni sees, kusjuures clock_timestamp() vastas reaalse kelleajaga.
  
−
===Trigger===
+
===Päästik===
  
−
Triggeriks nimetatakse sellist funktsiooni, mille täitmine kaasneb automaatselt vastavas tabelis toimuva muudatusega. Nt olgu olemas tabel customers
+
Päästikuks (ingl. k. trigger) http://www.postgresql.org/docs/current/interactive/trigger-datachanges.html nimetatakse sellist funktsiooni, mille täitmine kaasneb automaatselt vastavas tabelis toimuva muudatusega. Nt olgu olemas tabel customers
  
 
  CREATE TABLE customers  
 
  CREATE TABLE customers  
131. rida: 872. rida:
 
     RETURN NULL;
 
     RETURN NULL;
 
   END;
 
   END;
−
  $$ LANGUAGE ‘plpgsql’;
+
  $$ LANGUAGE 'plpgsql';
  
 
Ning lõpuks moodustada trigger archive_customer seostades funktsiooni tabeliga customers
 
Ning lõpuks moodustada trigger archive_customer seostades funktsiooni tabeliga customers
141. rida: 882. rida:
 
     EXECUTE PROCEDURE archive_customer();
 
     EXECUTE PROCEDURE archive_customer();
  
−
===Protseduurilised keeled===
+
===Pärilus===
 +
 
 +
Pärilus (ingl. k. inheritance) http://www.postgresql.org/docs/current/interactive/ddl-inherit.html väljendab asjaolu, et PostgreSQL on objekt-relatsiooniline (teatud mõttes objekt-orienteeritud) andmebaas. Nt võimaldab pärilus moodustada tabeleid, mis moodustavad hierarhia, kus üks tabel pärib ühe või mitme muu tabeli omadusi.Päriluse peamiseks kasutusalaks on järgmises punktis kirjeldatud tabeli partitsioneerimine, antud punktis kirjeldatakse päriluse endaga seotud asjaolusid.
 +
 
 +
Olgu nn master tabel priit.loomad, st tabel mille omadusi mõni teine tabel hakkab pärima selline
 +
 
 +
CREATE TABLE priit.loomad
 +
(
 +
  loom_id serial NOT NULL,
 +
  liik character varying(50),
 +
  vanus integer,
 +
  nimi character varying(50),
 +
  CONSTRAINT loom_id_pkey PRIMARY KEY (loom_id)
 +
)
 +
 
 +
Sisaldugu tabelis priit.loomad endas sellised andmed
 +
 
 +
1  karu  20  Mõmmi
 +
2  hunt  10  Kriim Silm
 +
3  kana  2  Cana
 +
 
 +
Olgu teine tabel priit.loomad_haruldased, mis pärib tabeli priit.loomad omadusi
 +
 
 +
CREATE TABLE priit.loomad_haruldased
 +
(
 +
-- Inherited from table priit.loomad_haruldased:  loom_id integer NOT NULL DEFAULT nextval('priit.loomad_loom_id_seq'::regclass),
 +
-- Inherited from table priit.loomad_haruldased:  liik character varying(50),
 +
-- Inherited from table priit.loomad_haruldased:  vanus integer,
 +
-- Inherited from table priit.loomad_haruldased:  nimi character varying(50),
 +
  haruldusaste character varying,
 +
  CONSTRAINT loom_haruldus_id_key PRIMARY KEY (loom_id)
 +
)
 +
INHERITS (priit.loomad)
 +
 
 +
Sisaldugu priit.loomad_haruldased endas sellised andmed
 +
 
 +
1  merivart      120  Merra    väga haruldane
 +
2  viiger          1  Vii      haruldane
 +
3  peipsi tink    5  Tint    väga haruldane
 +
 
 +
Selline konstruktsioonil on järgmised omadused
 +
 
 +
* tehes päringuid tabelist priit.loomad saab kasutada kõiki andmeid, st tabelis priit.loomad endas sisalduvaid andmeid ning ka tabeli priit.loomad_haruldased andmeid
 +
 
 +
sql> select * from priit.loomad;
 +
  loom_id |    liik    | vanus |    nimi   
 +
---------+--------------+-------+------------
 +
        1 | karu        |    20 | Mõmmi
 +
        2 | hunt        |    10 | Kriim Silm
 +
        3 | kana        |    2 | Cana
 +
        1 | merivart    |    12 | Merra
 +
        2 | viiger      |    1 | Vii
 +
        3 | peipsi tink  |    5 | Tint
 +
 
 +
* kasutades tabelit priit.loomad_haruldased saab teha efektiivselt päriguid haruldaste loomade kohta
 +
 
 +
sql> select * from priit.loomad_haruldased;
 +
  loom_id |    liik    | vanus |  nimi  |  haruldusaste 
 +
---------+--------------+-------+--------+----------------
 +
        1 | merivart    |    12 | Merra  | väga haruldane
 +
        2 | viiger      |    1 | Vii    | haruldane
 +
        3 | peipsi tink  |    5 | Tint  | väga haruldane
 +
 
 +
* kasutades tabelit priit.loomad päringut kaas only direktiiviga, saab küsida kõiki mitte-haruldasi loomi
 +
 
 +
sql> select * from only priit.loomad;
 +
 
 +
  loom_id |    liik    | vanus |    nimi   
 +
---------+--------------+-------+------------
 +
        1 | karu        |    20 | Mõmmi
 +
        2 | hunt        |    10 | Kriim Silm
 +
        3 | kana        |    2 | Cana
 +
 
 +
Andmete sisestamisel peab arvestama, et
 +
 
 +
* andmeid saab sisestada nö füüsilisse tabelisse
 +
* andmeid muutmine toimub ka läbi päriluse, nt
 +
 
 +
sql> update priit.loomad set vanus='10' where nimi='Tint';
 +
 
 +
Päriluse võimalusi otstarbekalt kasutades on võimalik tekitada selline andmemudel, mis on kompaktne ning võimaldab andmetega efektiivselt töötada.
 +
 
 +
Olemasolevate tabelite vahel pärimise tekitamiseks või lõpetamiseks sobib öelda vastavalt
 +
 
 +
alter table priit.loomad_haruldased inherit priit.loomad
 +
alter table priit.loomad_haruldased no inherit priit.loomad
 +
 
 +
Kasulikud lisamaterjalid
 +
 
 +
* http://knol.google.com/k/postgresql-inheritance?hl=en#
 +
 
 +
===Tabeli partitsioneerimine===
 +
 
 +
Tabeli partitsioneerimise http://www.postgresql.org/docs/current/static/ddl-partitioning.html tulemusena koosneb üks suur loogiline tabel mitmest väiksemast füüsilisest tabelist. Partisioneerimisel on sellised eelised
  
−
Nt PL/pgSQL on üks PostgreSQL andmebaasi protseduuriline keel.
+
* päringu jõudlus suureneb teatud juhtumitel, nt kui päring töötab valdavalt ainult mõnes partitsioonis; efekt toimub muuseas tänu selle, et nt ühe suure indeksi asemel on tegu mitme väiksema indeksiga mis mahub paremine mällu
 +
* massilised kirjete kustutamised toimuvad oluliselt kiiremini eeldusel, et andmemudel näeb ette võimaluse kustutada kogu kogu partitsiooni
 +
* harva kasutatavate andmetega partitsioone saab paigutada odavamale ja aeglasemale salvestusseadmele
  
−
$ createlang -h 127.0.0.1 -U postgres plpgsql movies
+
PostgreSQL andmebaasis saab partitsioneerimist korraldada iseensest kahel viisil
  
−
Ja tulemuse kontrollimiseks
+
* toetudes tabeli pärilusele - eelistatud viis, mida järgnevas põhiliselt kirjeldatakse
 +
* kasutades view ja union võimalusi
  
−
movies=# select * from pg_language ;
+
Näiteks kirjeldatakse partitsioneeritud tabeli kasutamist pika ajavahemiku jooksul tabelisse loomadsoovad andmete kogumisel. Efekt tuleneb sellest, et suur hulk andmeid jaotatakse aastate kaupa mitmete füüsiliste tabelite vahel ära.
−
  lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
 
−
----------+----------+---------+--------------+---------------+--------------+--------
 
−
  internal |      10 | f      | f            |            0 |        2246 |
 
−
  c        |      10 | f      | f            |            0 |        2247 |
 
−
  sql      |      10 | f      | t            |            0 |        2248 |
 
−
  plpgsql  |      10 | t      | t            |        16446 |        16447 |
 
−
(4 rows)
 
  
−
Lihsamal juhul võiks kirjeldada funktsiooni selline järgnevus
+
Partitsiooni master tabel partitsioon.loomadsoovad moodustamiseks sobib öelda
  
−
  CREATE OR REPLACE FUNCTION liitja (INTEGER, INTEGER) RETURNS INTEGER AS $$
+
  CREATE TABLE partitsioon.loomadsoovad
−
   BEGIN
+
(
−
    return $1 + $2;
+
  loomadsoovad_id serial NOT NULL,
−
   END;
+
  kuupaev date,
−
  $$ LANGUAGE 'plpgsql';
+
  rasvad integer,
 +
   valgud integer,
 +
  sysivesikud integer,
 +
   CONSTRAINT loomadsoovad_id_pkey PRIMARY KEY (loomadsoovad_id)
 +
  )
  
−
Faktoriaali arvutamise kirjeldab nt selline PL/pgSQL funktsioon, kusjuures tegu on rekursiivse funktsiooniga
+
ning sellest pärinevad tabelid, oluline on rakendada selliseid piiranguid, et partitsioonides ei oleks korduvaid andmeid
  
−
  CREATE OR REPLACE FUNCTION my_factorial( value INTEGER) RETURNS INTEGER AS $$
+
  create table partitsioon.loomadsoovad_2005 (
−
   DECLARE
+
   CHECK ( kuupaev >= DATE '2005-01-01' AND kuupaev < DATE '2005-12-31' )
−
    arg INTEGER;
+
) inherits (partitsioon.loomadsoovad);
−
  BEGIN
 
−
 
 
−
    arg := value;
 
−
 
 
−
    IF arg IS NULL OR arg < 0 THEN
 
−
      RAISE NOTICE 'Invalid Number' ;
 
−
      RETURN NULL;
 
−
 
 
−
    ELSE
 
 
   
 
   
−
      IF arg = 1 THEN
+
create table partitsioon.loomadsoovad_2006 (
−
        RETURN 1;
+
  CHECK ( kuupaev >= DATE '2006-01-01' AND kuupaev < DATE '2006-12-31' )
−
      ELSE
+
) inherits (partitsioon.loomadsoovad);
−
        DECLARE
+
...
−
          next_value INTEGER;
+
 
−
        BEGIN
+
Kirjeldatakse funktsioon
−
          next_value := my_factorial(arg - 1) * arg;
+
 
−
          RETURN next_value;
+
CREATE OR REPLACE FUNCTION partitsioon.loomadsoovad_insert_trigger()
−
        END;
+
RETURNS TRIGGER AS $$
−
      END IF;
+
BEGIN
−
 
+
    INSERT INTO partitsioon.loomadsoovad_2010 VALUES (NEW.*);
−
    END IF;
+
    RETURN NULL;
−
   END;
+
END;
−
  $$ LANGUAGE 'plpgsql';
+
$$
 +
LANGUAGE plpgsql;
 +
 
 +
Kirjeldatakse trigger
 +
 
 +
CREATE TRIGGER insert_loomadsooad_trigger
 +
  BEFORE INSERT
 +
  ON partitsioon.loomadsoovad
 +
  FOR EACH ROW
 +
   EXECUTE PROCEDURE partitsioon.loomadsoovad_insert_trigger();
 +
 
 +
Andmete sisestamine toimub sellised, kusjuures need andmed satuvad tabelisse partitsioon.loomadsoovad_2010
 +
 
 +
  insert into partitsioon.loomadsoovad (kuupaev, rasvad, valgud, sysivesikud) values ('2010-03-25', '50', '500', '5000');
  
−
Funktsiooni kasutamiseks sobib öelda
+
Partitsiooneeritud tabeli kasutamiseks sobib öelda
  
−
  select my_factorial();
+
  select * from partitsioon.loomadsoovad;
  
−
Funktsiooni kustutamiseks sobib öelda
+
või siis ka otse
  
−
  drop function liitja(integer, integer);
+
  select * from partitsioon.loomadsoovad_2010;
  
−
===Kursorite kasutamine===
+
====Märkused====
  
 
TODO
 
TODO
 +
 +
===contrib moodulite kasutamine===
 +
 +
====dblink====
 +
 +
dblink tehnika võimaldab ühe andmebaasi päringutes kasutada teise andmebaasi andmeid.
 +
 +
* Näiteks lihtsalt päringut teha
 +
 +
SELECT *
 +
FROM dblink.dblink('dbname=test host=192.168.10.205 user=postgres','SELECT nimi, vanus FROM priit.priidutabel')
 +
  AS tabelinimi(nimi varchar(50), vanus integer);
 +
 +
* Tekitada teise andmebaasi andmetest vaade
 +
 +
create view dblink.dblink_view as
 +
  select *
 +
    from dblink.dblink('dbname=test host=192.168.10.205 user=postgres', 'SELECT nimi, vanus FROM priit.priidutabel')
 +
    as tabelinimi(nimi varchar(50), vanus integer);
 +
 +
ja vaate kasutamiseks öelda nt
 +
 +
select * from dblink_view;
 +
 +
====pgcrypto====
 +
 +
pgcrypto moodul võimaldab kasutada andmebaasis krüptimise funktsioone. Nt md5 vormingus parooli genereerimiseks sobib öelda
 +
 +
  UPDATE pgcrypto.priidutabel SET parool = pgcrypto.crypt('new password', pgcrypto.gen_salt('md5'));
 +
 +
====tsearch2====
 +
 +
tsearch2 on täisteksti otsingu moodul.
 +
 +
SELECT 'Priit ei ole Mart'::tsvector @@ 'Priit & Mart'::tsquery;
 +
  ?column?
 +
----------
 +
  t
 +
(1 row)
 +
 +
 +
===Massiivi kasutamine===
 +
 +
PostgreSQLi atriibuut saab olla ka massiiv (ingl. k. array). Nt selliselt
 +
 +
CREATE TABLE domeeninimi
 +
(
 +
  domeeninimi_id serial NOT NULL,
 +
  tld character(2),
 +
  domeeninimed character varying(50)[]
 +
)
 +
 +
kus tüüp 'character varying(50)[]' näitab, et tegu on määramatu arvu varchar(50) elementidega massiiviga.
 +
 +
Andmete sisestamine toimub öeldes
 +
 +
sql> insert into domeeninimi (tld, domeeninimed) values ('lv', '{"riga.lv", "pritus.lv", "latvia.lv", "sagrebis.lv"}')
 +
 +
Andmete päring toimub öeldes
 +
 +
sql> select tld, domeeninimed[1] from domeeninimi;
 +
  tld | domeeninimed
 +
-----+--------------
 +
  ee  | tartu.ee
 +
  lv  | riga.lv
 +
 +
===Binary andmete hoidmine andmebaasis===
 +
 +
Vt http://kuutorvaja.eenet.ee/wiki/Python#Binary_andmete_sisestamine
 +
 +
===Tehted hulkadega===
 +
 +
UNION, INTERSECT JA EXECPT abil on võimalik kombineerida mitut select lauset kusjuures
 +
 +
* UNION - esitatakse read, mis on ühes või mõlemas result setis
 +
* INTERSECT - esitatakse read, mis on mõlemas result setis
 +
* EXECPT - esitatakse read, mis on esimeses kuid mitte teises result setis
 +
 +
Näiteks
 +
 +
select nimi from hulgad.hulk1 except select nimi from hulgad.hulk2;
 +
 +
===Misc===
 +
 +
* tabel ühte tehtud päringu tulemuste lisamine tabel kahte
 +
 +
insert into tabel2 (nimi, aadress) select nimi, aadress from tabel1
 +
 +
* coalesce tagastab esimese mitte-null väärtuse
 +
 +
sql> select coalesce(null, 'vaartus');
 +
  coalesce
 +
----------
 +
  vaartus
 +
 +
* Ajutine tabel eksisteerib kuni andmebaasiühenduse lõpuni
 +
 +
CREATE TEMP TABLE tabelinimi(name varchar(50), vanus integer);
 +
 +
* result set - vastus andmebaasile esitatud päringule, või sisaldada mitte midagi, ühte või enamat rida
 +
 +
* jõudlus
 +
 +
explain verbose select * from teenused where nimi='teenuse nimi_O_13437';
 +
                                  QUERY PLAN                               
 +
----------------------------------------------------------------------------
 +
  Bitmap Heap Scan on teenused2  (cost=6.60..196.17 rows=48 width=48)
 +
    Output: id, nimi, aadress
 +
    Recheck Cond: ((nimi)::text = 'teenuse nimi_O_13437'::text)
 +
    ->  Bitmap Index Scan on teenused_idx  (cost=0.00..6.59 rows=48 width=0)
 +
          Index Cond: ((nimi)::text = 'teenuse nimi_O_13437'::text)
 +
 +
* Viivituse esilekutsumiseks sobib kasutada, kus 2 tähendab kahte sekundit
 +
 +
select pg_sleep(2);
 +
 +
* Tabeliga
 +
 +
alter table test.inimene add constraint inimene_id primary key (inimesed_id);
 +
alter table test.inimene add eesnimi varchar(50);
 +
 +
* Tabeli ümbernimetamine
 +
 +
sql> alter table skeeminimi.inimesed rename to inimesed2;
  
 
===Kasulikud lisamaterjalid===
 
===Kasulikud lisamaterjalid===
  
 
* http://www.conjectrix.com/ - PostgreSQL, 2nd edition
 
* http://www.conjectrix.com/ - PostgreSQL, 2nd edition
 +
* Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition, Michael J. Hernandez
 +
* Logic Data Modeling kursus - http://www.youtube.com/watch?v=IiVq8M5DBkk&feature=related
 +
* UML kursus - http://www.youtube.com/watch?v=RRXe1omEGWQ&feature=PlayList&p=749853D118C90D9C&index=1
 +
* UHCL Graduate Database Course - http://www.youtube.com/watch?v=DjHhZLdPjH0&feature=PlayList&p=7E6294372C9EAF06&index=0&playnext=1
 +
* Designing Effective Database Systems, Rebecca M. Riordan
 +
* PostgreSQL: Introduction and Concepts, Bruce Momjian - http://www.postgresql.org/files/documentation/books/aw_pgsql/
 +
* Practical PostgreSQL - John Worsley, Joshua Drake - http://www.commandprompt.com/ppbook/
 +
* http://publib.boulder.ibm.com/infocenter/rsdvhelp/v6r0m1/index.jsp?topic=/com.ibm.xtools.modeler.doc/topics/ccompasn.html
 +
* http://momjian.us/main/writings/pgsql/internalpics.pdf
 +
* http://www.pgcon.org/
 +
* http://www.2ndquadrant.com/
 +
* [[:GIS]]

Viimane redaktsioon: 26. mai 2010, kell 09:28

Sissejuhatus

Andmebaasi kasutamise eesmärgiks on saada andmeid salvestada, sälitada ja teha kättesaadavaks. SQL (Structured Query Languag) keel võimaldab kasutada relatsioonilist andmebaasisüsteemi (RDBMS, relational database management system).

Kaasaegsel RDBMS andmebaasil on tavaliselt sellised omadused

SQL keele võimalusi liigitatakse vahel kolmeks

Käesolev tekst eeldab, et andmebaasi tarkvara on juurutatud ja kasutajal on olemas sobivad ligipääsud andmebaasiserverile, kusjuures andmete esitamiseks kasutatakse UTF-8 kodeeringut. Näideteks kasutatakse käsurea jaoks selliseid tähistusi

  • $ või # - vastavalt operatsioonisüsteemi tavakasutaja või juurkasutaja shelli prompt
  • psql> - psql andmebaasi sql kliendi prompt
  • sql> - suvalise andmebaasi sql kliendi prompt, nt pgadmin SQL query editor ja sealhulgas psql

Andmebaasiga ühendumine

Andmebaasi kasutamise eelduseks on, et

  • kasutajal on sobiv klientprogramm
  • kasutajale on usaldatud ligipääs andmebaasile

Tavaliselt toimub andmebaasi kasutamine üle võrgu, st kasutaja töötab andmebaasi klientprogrammiga ühes ja andmebaas töötab teises arvutis. Andmebaasi klientprogrmm ja andmebaasi server suhtlevad omavahel kasutades spetsiaalset selleks ettenähtud protokolli, mis sõltub konkreetsest andmebaasi tarkvarast. Reeglina sisaldab andmebaasitarkvara lisaks andmebaasi serverile ka mingit terminalil kasutatavat nö käsurea klientprogrammi, nt PostgreSQL puhul on selleks programm psql.

Andmebaasiühenduse moodustamiseks sobib nt PostgreSQL puhul kasutades psql programmi öelda 

$ psql -h baas.loomaaed.tartu.ee -U priit baasinimi
Password for user priit: <parool>
sql>

kus

  • -h baas.loomaaed.tartu.ee - andmebaasi hostname või ip aadress
  • -U priit - kasutajanimi
  • baasinimi - andmebaasi nimi

Ilma, et oleks vaja andmebaasis andmeid, saab andmebaasi kasutada nt selliselt

  • andmebaasi kliendi kasutamine kalkulaatorina
sql> select (5 * 5);
 ?column? 
----------
       25
(1 row)
  • andmebaasi klient töötab sõnedega
sql> select 'Hello' || ' World!';
   ?column?   
--------------
 Hello World!
(1 row)
  • Andmebaasi kliendi juures saab määrata esituse formaati, nt (vaikimisi on see aligned)
psql> \pset format html
  • arvutada arvu ruutjuur
sql> select sqrt(25);

Andmebaasi objektide hierarhia

Andmebaasis sisalduvaid objekte võib kujutleda sellises hierarhias

  • andmekluster - andmebaasile vastavad failid failisüsteemis, nt kataloogis /var/lib/postgresql/8.4/main, PostgreSQL andmekluster moodustatakse öeldes
$ pg_createcluster --locale et_EE.UTF-8 8.4 main --start
  • andmebaas - käivitatud andmebaasis olev objekt, andmebaasi klustris võib olla mitu andmebaasi
sql> create database andmebaasinimi;
  • skeem - andmebaasis olev objekt, andmebaasis võib olla mitu skeemi
sql> create schema skeeminimi;
  • tabel - skeemis olev objekt, skeemis võib olla mitu tabelit
sql> create table skeemininimi.tabelinimi (nimi varchar(50), kaugus integer);

Skeem

Enamus andmbaasi objekte, nt tabelid, mida kasutaja peale andmebaasiga ühendumist kasutab asuvad skeemides (ingl. k. schema) http://www.postgresql.org/docs/current/interactive/ddl-schemas.html. Objektile viitades tuleb skeemi nime kasutada prefiksina, nt olgu andmebaasis kolm skeemi

  • public - vaikeskeem
  • sport_2009 - skeem kuhu on ette nähtud paigutada 2009 aasta treeningute tulemused
  • sport_2010 - skeem kuhu on ette nähtud paigutada 2009 aasta treeningute tulemused

Seejuures võib olla olemas kaks tabeli nime mõttes sama nimega tabelit, kuid andmebaasis siiski on nad erinevad tabelid 

sport_2009.ujumine
sport_2010.ujumine

Kui tabeliga tegelemisel ei ole esitatud skeemi nime, siis vaikimisi tegeldakse skeemi public objektidega.

Kasutajaõigused

Andmebaasi tavakasutajal on tavaliselt voli tegutseda ühe andmebaasi ühes kasutajanimelises skeemis. Kõik kasutaja poolt moodustatud objektid kuuluvad kasutaja omandusse, st tal on võimalik nendega teha igasuguseid tegevusi, sh neid muuta ja kustutada, lisaks kontrollida neile teiste kasutajate ligipääse.

Vaikimisi saab PostgreSQL kasutaja töötada lisaks omanimelisele skeemile ka public skeemis.

Tabel

Andmebaasis paiknevad andmed asuvad tabelites. Tabelis esitatakse andmeid ridade kaupa, kusjuures iga rida koosneb mitmest veerust ehk väljast. Kirjeldades tabelit sellise konkreeste näite alusel 

id      isikukood    eesnimi  perekonnanimi       synniaeg        sissekande_aeg 
 1    37802249999      Priit           Kask     1978-02-24   2010-03-29 15:45:20
 2    46406259999      Maila            Laa     1964-06-25   2010-03-29 15:46:24
 3    38612249999       Mart           Kask     1886-12-24   2010-03-29 15:47:44

võib öelda

  • ühte tabelisse kantakse sama tüüpi objektide erinevate eksemplaride kohta käivaid andmeid
  • tabelil on nimi, näiteks võiks see olla inimene
  • tabelis esineb viis välja (ingl. k. field), öeldakse ka, et viis atribuuti: id, isikukood, eesnimi, perekonnanimi, synniaeg ja sissekande_aeg
  • igal rea väljal on väärtus
  • tabelis on kolm rida (ingl. k. row) ehk kirjet (ingl. k. record)
  • tabeli atribuutid on alati mingit kindlat tüüpi, nt täisarv, määratletud või määratlemata pikkusega tekst, kuupäev
  • põhimõtteliselt ei ole tabelis read järjestatud
  • põhimõtteliselt ei ole reas väljad järjestatud
  • reeglina on tabelid hästi pikad ja kitsad, st tabelis on suhteliselt palju ridu ja vähe veerge

Relatsioonilises andmebaasis on tabeliga seotud lisaks seal paiknevatele andmete ka mitmeid lisaomadusi, nt

  • tabeli read on unikaalsed
  • primaarne võti (ingl. k. primary key) - võtmega seotud välja väärtuse järgi saab rida identifitseerida
  • välisvõti (ingl. k. foreign key) - kasutatakse seose loomiseks antud tabeli mõne välja ja mõne teise tabeli samatüübilise välja vahel
  • võimalused mitmete tabelite andmete kooskõlalise (ingl. k. integrity) tagamiseks

Tabeli moodustamiseks sobib öelda 

CREATE TABLE inimene
(
  inimene_id serial NOT NULL,
  isikukood character (13),
  eesnimi varchar(50),
  perekonnanimi varchar(50),
  synniaeg date,
  sissekande_aeg timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT inimene_id_pkey PRIMARY KEY (inimene_id)
)

kus

  • tabeli nimi esitatakse tavaliselt ainsuses
  • serial tähendab, et välja väärtus on täisarv ja seda suurendatakse iga sissekande tegemisel automaatselt ühe võrra
  • NOT NULL tähendab, et andmebaas kontrollib, et väljal oleks määratletud väärtus
  • isikukood välja tuleb alati sisestada 13 tähelise pikkusega sõna, ei lühem ega pikem
  • eesnimi, perekonnanimi väljade väärtustes võivad olla kuni 50 tähe pikkused sõned
  • synniaeg - välja väärtus on kuupäev, st kujul YYYY-MM-DD (aasta-kuu-päev ehk year-month-day)
  • sissekande_aeg välja väärtus peab olema kuupäeva ja kellaaeg tüüpi, nt '2010-03-29 15:45:20', kusjuures väärtus sisestatakse kirja moodustamisel automaatselt
  • CONSTRAINT tähendab, et inimene_id väljale moodustatakse primaarne võti

Relatsioonilises andmebaasis nimetatakse tabelit ka relatsiooniks, ja mitte tablite vahelisi seoseid.

Tabeli kasutamine

Põhiliselt seisneb andmebaasi tabeli kasutamine kolme sorti tegevustes

  • andmete lisamine (insert) - tabelisse lisatakse rida
  • andmete lugemine (select) - tabelist loetakse andmeid
  • andmete muutmine (update) - tabeli olemasoleva rea ühe või enama välja väärtusi muudetakse
  • andmete kustutamine (delete) - tabelist kustutatakse rida

Andmete lisamine

Tabelisse kirje lisamiseks on käsk insert, nt selliselt 

sql> insert into inimene (isikukood, eesnimi, perekonnanimi, synniaeg) values ('46705079999', 'Marta', 'Maara','1967-05-07');

Andmete lugemine

Tabelist andmete lugemiseks on käsk select, nt küsides rea, kus isikukoodi väärtus on 46705079999 

sql> select isikukood, perekonnanimi from inimene where isikukood = '46705079999';

Andmete muutmine

Tabelis andmete muutmiseks on käsk update, nt muudame selle rea perekonnanimi Marat'iks kus on isikukood 46705079999 

sql> update inimene set perekonnanimi = 'Marat' where isikukood = '46705079999';

Andmete kustutamine

Tabelist andmete kustutamiseks on käsk dalete, nt kustutame rea, kus isikukood on 46705079999 

sql> delete from inimene where isikukood = '46705079999';

Kogu tabeli sisu saab nö kiiresti kustutada öeldes 

sql> truncate table inimene;

Andmetüübid

PostgreSQL SQL implementatsioon on tugeva tüübiskeemiga (ingl. k. strongly typed) keel, mis tähendab, et teatud tehted saab teha vaid sama tüüpi andmetega või tuleb sooritada eelnevalt vastav tüübiteisendus. Käesolevas punktis käisitletakse PostgreSQLis kasutusel olevaid andmetüüpe (ingl. k. data type) http://www.postgresql.org/docs/current/interactive/datatype.html.

Ilmutatud kujul saab andmete tüüpi väljendada kahel samaväärsel viisil 

sql> select varchar(20) 'tere';
 varchar 
---------
 tere

sql> select 'tere'::varchar(20);
 varchar 
---------
 tere

Lisaks on tüübi täpsustamiseks cast funktsioon, nt 

sql> select cast ('Loomaaed on avatud tööpäeviti' as text);
            text            
----------------------------
 Loomaaed on avatud tööpeviti

Arvulised tüübid

Näiteks saab kasutada sellised arvutilisi tüüpe

  • integer - 4 baiti pikk täisarv, omandab väärtusi vahemikus -2147483648 kuni +2147483647
  • serial - automaatselt suurenev täisarv, omandab väärtusi vahemikus 1 kuni 2147483647
  • real - 4 baiti pikk reaalarvuline tüüp

Sõnetüübid

PostgreSQL'is on olemas sellised sõnetüübid (ingl. k. character type)

  • varchar(n), character varying(n) - vahemikus 0 - n muutuva suurusega string
  • char(n) - täpselt pikkusega n string
  • text - piiramatu pikkusega string

Kuupäev/kellaaeg tüübid

Kuupäev/kellaaeg (ingl. k. Date/Time types) tüüpi andmed väljendavad

  • date - kuupäeva
  • time - kellaaeg
  • timestamp - kuupäeva ja kellaaega

Lisaks on võimalik kahe viimase puhul täpsustada ajavööndit.

Tõeväärtustüübid

Tõeväärtustüüpi atribuudid saavad omada väärtusi 0 või 1 ehk false või true.

Võrguaadresstüübid

  • inet - ipv4 ja ipv6 addressi tüüpi andmed
  • mac - mac aadressi tüüpi andmed

Nt saab inet kujul esitatud andmetega teha liitmistehet, mis suurendab ip aadressi osa 

sql> select ip + 20 from serverivorguseadistused where hostname = 'baas.loomaaed';

Massiivtüübid

Massiivitüüp võimaldab atribuudi väärtustena kasutada kõiki eelkirjeldatud andmetüüpe, kuid selliselt, et neist saab moodustada määratlemata pikkusega massiive. 

sql> select '{"element üks", "element kaks"}';

NULL

NULL väärtust kasutatakse kahes erinevas tähenduses

  • väärtust ei ole teada
  • väärtus ei ole rakendatav (ingl. k. not applicable, N/A)

Kasulikud lisamaterjalid

Andmetüüpide vahelised teisendused

Funktsiooni cast abil saab andmete tüüpe teisendandada, nt 

sql> select cast (3.14 as text);

Loendur

Loenduri (ingl. k. sequence) käest saab küsida järjestikulisi väärtusi, vaikimisi need väärtused kasvavad alatest ühest ühe kaupa.

Loenduri moodustamiseks sobib öelda 

CREATE SEQUENCE loendurinimi start 1;

ja loenduri käest saab küsida järgmist väärtust 

SELECT nextval('loendurinimi');

Praktiliselt kasutatakse loendureid mõne tabeli unikaalsete automaatselt kasvavate väärtustega attribuudina. Nt moodustades tabeli selliselt 

CREATE TABLE mart.inimene
(
  inimene_id serial NOT NULL,
  nimi character varying(50),
  vanus integer,
  CONSTRAINT inimene_id_pkey PRIMARY KEY (inimene_id)
)

tekitatakse automaatselt loendur inimene_inimene_id_seq 

CREATE SEQUENCE mart.inimene_inimene_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 5
  CACHE 1;

Failisüsteemi kasutamine

copy käsu abil saab kopeerida andmeid failisüsteemist andmebaasi ja vastupidi. Failis asuvad andmed peavad esinema üks kirje real ning väljad arusaadavalt eraldatud, kuid muus osas on tegu tavalise tekstifailiga.

Failisüsteemi salvestamine

Andmebaasi tabeli nimed sisu kopeerimiseks failisüsteemi sobib öelda 

sql> copy nimed to '/tmp/priit.txt';

Failisüsteemist lugemine

Olemasolevasse tabelisse priit.priidutabel failisüsteemi failist /tmp/andmed.txt andmete kopeerimiseks sobib öelda 

copy priit.priidutabel (vanus, nimi) from '/tmp/andmed' with delimiter ' ';

SQL skripti kasutamine

psql programmi abil saab täita kliendi failisüsteemis asuvat skripti /tmp/skript.sql 

psql> \i /tmp/skript.sql

Tabelite vahelised seosed

Tabelite vahelisi seoseid kasutatakse andmete kooskõlalisuse kehtestamiseks ja neid kirjeldatakse võtmete abil.

Primaarne võti

Primaarse võtmega (ingl. k. primary key) tabeli moodustamine 

CREATE TABLE tabelinimi (
  id          SERIAL PRIMARY KEY,
  title       CHARACTER VARYING(80)
);

Olemasolevale tabelile võtme lisamine 

sql> ALTER TABLE tabelinimi ADD CONSTRAINT valjanimi_id_pk PRIMARY KEY (valjanimi_id);

Primaarse võtme eemaldamiseks sobib öelda 

sql> ALTER TABLE tabelinimi DROP CONSTRAINT valjanimi_id_pk;

Välisvõti

Välisvõtme (ingl. k. foreign key) kasutamiseks sobib moodustada tabel selliselt 

CREATE TABLE rentals (
  tape_id     CHARACTER(8) REFERENCES tapes,
  customer_id INTEGER      REFERENCES customers,
  rental_date DATE
);

Enesele viitav välisvõti

Enesele viitav välisvõti (ingl. k self-refrencing foreign key) viitab sama tabeli mõnele unikaalsele väljale, nt 

CREATE TABLE vanemadjalapsed
(
  inimesenimi character varying(50) NOT NULL,
  lapsenimi character varying(50),
  vanemanimi character varying(50),
  CONSTRAINT vanemajalapsed_pkey PRIMARY KEY (inimesenimi),
  CONSTRAINT vanemadjalapsed_fk FOREIGN KEY (vanemanimi)
     REFERENCES vanemadjalapsed (inimesenimi) MATCH SIMPLE
     ON UPDATE CASCADE ON DELETE NO ACTION
)

kus

  • inimesenimi - ...
  • lapsenimi - ...
  • vanemanimi - ...
  • on update cascade - tagab, et kui mõni inimesenimi väärtus muutub, siis muutub ka vastava nime kasutus kõigis vanemanimi lahtrites kus seda on kasutatud

Indeks

Indeks (ingl. k. index) http://www.postgresql.org/docs/current/interactive/indexes.html on andmebaasi instrument, mida kasutatakse selleks, et kiirendada päringuid. Põhimõtteliselt on andmebaasi indeks saranane raamatu lõpus kasutatavale indeksile, kuna indeksis on sissekanded esitatud tähestikulises järjekorras, saab leida suhteliselt kiire huvipakkuva sissekande ning seejärel lehekülje numbri järgi koha raamatu tekstis endas.

Andmebaasis indekseeritakse tavaliselt neid tabeli välju, mida kasutatakse tavaliselt tabelist päringute tegemisel nö where järele. Kuna indeksi pidamisega kaasneb täiendav ressursikulu, siis ei maksa indeksitega ka liialdada

  • indeks võtab salvestusseadmel märkimisväärselt ruumi
  • indeksi moodustamisel on tabel reeglina kirjutamiste jaoks lukustatud olekus
  • kuna tabeli andmete muutmisel tuleb muuta vastavalt ka indeksit, siis tuleb andmebaasil selleks teha lisatööd

Tabeli priit.priidutabel atribuudile nimi indeksi moodustamiseks sobib öelda 

sql> CREATE INDEX nimi_idx ON priit.priidutabel (nimi);

Indeksi eemaldamiseks sobib öelda 

sql> drop index priit.nimi_idx;

Samas skeemis peavad kõigil indeksitel olema unikaalsed nimed. Indeksi moodustamisel peab arvestama, et kuigi tabelit saab lugeda, on sel ajal update, insert ja delete operatsioonid keelatud.

Vaade

Vaade (ingl. k. view) http://www.postgresql.org/docs/current/interactive/tutorial-views.html on virtuaalne tabel, mis on moodustatakse igal vaate poole pöördumisel vaate kirjeldamisel kasutatud sql päringu alusel. Iseenesest on vaade ainult lugemiseks ja seal muuta ega sinna lisada andmeid ei saa.

Näiteks olgu olemas kaks tabelit, priit.linn 

CREATE TABLE priit.linn
(
  linn_id serial NOT NULL,
  nimi character varying(50),
  CONSTRAINT linn_id_pkey PRIMARY KEY (linn_id)
)

ja priit.inimene 

CREATE TABLE priit.inimene
(
  inimene_id serial NOT NULL,
  nimi character varying(50),
  linn_id integer,
  CONSTRAINT nimi_id_pkey PRIMARY KEY (inimene_id),
  CONSTRAINT linn_fkey FOREIGN KEY (linn_id)
      REFERENCES priit.linn (linn_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Tartlaste nimesid ja linnanime sisaldava vaate moodustamiseks sobib öelda 

sql> create view tartlane as 
  select priit.inimene.nimi as inimesenimi, priit.linn.nimi as linnanimi
  from priit.inimene, priit.linn 
  where priit.inimene.linn_id=priit.linn.linn_id and priit.linn.nimi='Tartu';

Vaate kasutamiseks sobib öelda 

sql> select * from tartlane;

Vaate eemaldamiseks tuleb öelda 

sql> drop view tartlane;

Kasulikud lisamaterjalid

Reeglid

Reeglid (ingl. k. rules) http://www.postgresql.org/docs/current/static/rules.html võimaldavad muuta päringuid enne nende päringu plaanija (ingl. k. query planner) juurde jõudmist ja päringute täitmist.

Nt eeldusel, et skeemis reeglid on olemas tabelid essa ja tessa võiks selline reegel kõik insert sisestused sooritada tabelisse tessa juhul, kui nimi väärtuseks on 'Mart' 

CREATE RULE sisesta_tessasse AS
ON INSERT TO reeglid.essa WHERE nimi = 'mart'
DO INSTEAD
   INSERT INTO reeglid.tessa values (new.*);

Samaaegsete tegevuste kontroll

Samaaegsete tegevuste kontroll (ingl. k. concurrency control) http://www.postgresql.org/docs/current/interactive/mvcc.html võimaldab tagada, et samaaegselt andmebaasis toimuvad tegevused oleksid vajalikul määral üksteisest isoleeritud.

Transaktsioon

Transaktsiooniks nimetatakse sellist järgnevust tegevusi, mis toimuvad kas kõik või siis ei toimu neist ühtegi. See on üks fundamentaalne andmebaaside omandus, et tagada andmete kooskõlalisus.

PostgreSQLis märgib transaktsiooni algus begin ning lõppu end, nt 

sql> begin;
sql> select ... ;
sql> update ... ;
sql> commit;

Kui transaktsioon mingil põhjusel ei õnnestunud, siis öeldakse, et sooritatakse rollback (ingl. k. tagasikerimine). Ilmutatud kujul saab teha rollback'i öeldes transaktsiooni lõpus commit asemel 

sql> rollback;

Lukud

Transaktsiooni sees saab ilmutatult kasutada lukku, nt 

sql> begin;
BEGIN
sql> lock table priit.priidutabel in access exclusive mode;
LOCK TABLE
...
sq> commit;

kus access exclusive lukk tagab, et ainult kõneksolev transaktsioon kasutab tabelit priit.priidutabel.

PostgreSQL andmebaasis on kasutada seitse erinevat lukustust

  • ACCESS SHARE MODE -
  • ROW SHARE MODE -
  • ROW EXCLUSIVE MODE
  • SHARE MODE
  • SHARE ROW EXCLUSIVE MODE
  • EXCLUSIVE MODE
  • ACCESS EXCLUSIVE MODE

Ülidselt võib öelda, et shared lukud keelevad vastavale ressursile saada samaaegselt mõnda exclusive lukku. Kui mõni teine kasutaja pöördub parasjagu lukustatud ressursi poole, siis ta ei saa veateadet vaid ootab seni kuni ressurss vabastatakse lukust.

FOR UPDATE

TODO

Tupik

Tupik (ingl. k. deadlock) on selline situatsioon, kus kaks protsessi ootavad üksteise järel, et saada tegevusega edasi minna. Üldiselt PostgreSQL lahendab tupiku olukorrad automaatselt.

Kasulikud lisamaterjalid

Protseduurilised keeled

Protseduurilised keeled (ingl. k. procedural language, PL) http://www.postgresql.org/docs/current/interactive/xplang.html võimaldavad kirjutada funktsioone (nt triggerite jaoks) muudes keeltes peale otseselt SQL ja C. PL keeles kirjutatud funktsiooni puhul andmebaas ise ei oska interpreteerida funktsiooni lähtekoodi, kuid see ülesanne delegeeritakse vastava keelega seotud handlerile. Handler esineb C shared teegi kujul ja tegeleb funktsiooniga ise või omakorda kasutab selleks operatsioonisüsteemi paigaldatud teisi teeke.

PostgreSQL sisaldab otseselt tuge neljale PL keelele

  • PL/pgSQL
  • PL/Tcl
  • PL/Perl
  • PL/Python

Lisamoodulitena saab kasutada sellised PL keeli, http://www.postgresql.org/docs/current/interactive/external-pl.html

PL/pgSQL

Lihsamal juhul võiks kirjeldada funktsiooni selline järgnevus 

CREATE OR REPLACE FUNCTION liitja (INTEGER, INTEGER) RETURNS INTEGER AS $$
  BEGIN
    return $1 + $2;
  END;
$$ LANGUAGE 'plpgsql';

Funktsiooni kasutamiseks sobib öelda 

select liitja(10,50);

Funktsiooni kustutamiseks sobib öelda 

drop function liitja(integer, integer);

PL/Perl

PL/Perl (Perl Procedural Language) http://www.postgresql.org/docs/current/interactive/plperl.html võimaldab kirjutada andmebaasi funktsiooni Perl keeles.

Nt selline funktsioon tegeleb sünniaasta arvutamisega 2010 aastal 

CREATE OR REPLACE FUNCTION plperlu_synniaastaarvutaja(character varying, integer)
  RETURNS character varying AS
$BODY$
 $synniaasta=2010 - $_[1];
 return "Kasutaja: $_[0] synniaasta on: $synniaasta";
$BODY$
  LANGUAGE 'plperlu' VOLATILE

Funktsiooni kasutamiseks tuleb öelda 

SQL> select plperlu_synniaastaarvutaja('Priit Kask', '60')

plperlu keeles saab ka operatsioonisüsteemi vahendeid kasutada sh failisüsteemi 

CREATE OR REPLACE FUNCTION pgenv()
  RETURNS character varying AS
$BODY$
open (fh, '>>/tmp/pgenv.log');
foreach $key (sort(keys %ENV)) {
  print fh "$key = $ENV{$key}\n";
}
close fh;
return "printisin just keskkonnamuutujad /tmp/pgenv.log faili";$BODY$
LANGUAGE 'plperlu' VOLATILE

Funktsiooni kasutamiseks tuleb öelda 

SQL> select pgenv();

ja tulemusena tekitatakse fail /tmp/pgenv.log.

PL/Proxy

PL/Proxy kasutamiseks peab tarkvara olema paigaldatud ainult sellesse andmebaasi, kust päriguid esitatakse teise andmebaasi.

Teises andmebaasis peab olema kirjeldatud huvipakkuv funktsioon, nt 

CREATE OR REPLACE FUNCTION plproxy.liitja(integer, integer)
  RETURNS integer AS
$BODY$
  BEGIN
    return $1 + $2;
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE

ja nö esimeses andmebaasis kirjeldatud plproxy keeles funktsioon 

CREATE OR REPLACE FUNCTION plproxy.eemal_liitja(arv1 integer, arv2 integer)
  RETURNS text AS
$BODY$
  CONNECT 'dbname=test host=192.168.10.206 user=postgres password=parool';
  SELECT * from plproxy.liitja(arv1, arv2);
$BODY$
  LANGUAGE 'plproxy' VOLATILE

Funktsiooni kasutamiseks sobib öelda esimese baasis 

select plproxy.eemal_liitja(50, 100);

Kasulikud lisamaterjalid

PL/Python

PL/Python (Python Procedural Language) http://www.postgresql.org/docs/current/interactive/plpython.html võimaldab kirjutada andmebaasi funktsiooni Python keeles.

Nt selline funktsioon tegeleb sünniaasta arvutamisega 2010 aasta 

CREATE OR REPLACE FUNCTION plpythonu_synniaastaarvutaja(nimi character varying, vanus integer)
  RETURNS character varying AS
$BODY$ 
 synniaasta = 2010 - vanus
 return "Kasutaja: " + nimi + "synniaasta on: " + str(synniaasta)
$BODY$
  LANGUAGE 'plpythonu' VOLATILE

Funktsiooni kasutamiseks sobib öelda 

select plpythonu_synniaastaarvutaja('Priit Kask', '60')

PL/Java

  • Java teegi andmebaasi kopeerimiseks tuleb öelda
 SQL> select sqlj.install_jar('file:///home/mart/helloworld.jar', 'helloworld_jar',  false);

Kusjuures baasi laaditud java teekide nimekirja saab küsida 

 SQL> select jarname, jarorigin from sqlj.jar_repository;
  • Seostame selle skeemiga, kust PL/Java funktsioone kasutatakse classpath'i öeldes
 SQL> SELECT sqlj.set_classpath('skeeminimi', 'helloworld_jar');
  • PL/Java funktsiooni tekitamine
 CREATE OR REPLACE FUNCTION helloworld()
   RETURNS "varchar" AS
     'com.mycompany.helloworld.HelloWorld.helloWorld'
   LANGUAGE 'java' VOLATILE;
  • PL/Java funktsiooni käivitamine
 SQL> select * from helloworld();
  • PL/Java funktsiooni kasutamine plpgsql funktsioonist
 CREATE OR REPLACE FUNCTION testlocale() RETURNS TIMESTAMP AS
 $body$
 DECLARE
   dummy timestamp;
 BEGIN
   perform helloworld();
   dummy := current_timestamp;
   return dummy;
 END;
 $body$
 LANGUAGE 'plpgsql';

Aja kasutamine

PostgreSQL andmebaasis saab aega esitada viiel moel, http://www.postgresql.org/docs/current/interactive/datatype-datetime.html ja http://www.postgresql.org/docs/current/interactive/functions-datetime.html

  • timestamp without time zone - kuupäev ja kellaaeg ilma ajatsoonita
  • timestamp with time zone - kuupäev ja kellaaeg ajatsooniga
  • data - kuupäev
  • time without time zone - kellaaeg ilma ajatsoonita
  • time with time zone - kellaaeg ajatsooniga
  • interval - ajavahemik

Aja väljendamine sisendis ja väljundis

PostgreSQL lubab väljandada aega sisendis üsna paindlikult, nt töötavad sellised variandid 

sql> select timestamp '2010-03-27 22:44:55';
      timestamp      
---------------------
 2010-03-27 22:44:55

sql> select timestamp with time zone '2010-03-27 22:44:55';
      timestamptz       
------------------------
 2010-03-27 22:44:55+02

sql> select date 'Mar 27 2010';
    date    
------------
 2010-03-27

sql> select date '27 Mar 2010';
    date    
------------
 2010-03-27

Vaikimisi väljendatakse aega väljundis igal juhtumil ISO standardile vastavalt.

Tehted ajaga

Põhiliselt kasutatakse seoses ajaga kahte tehet: liitmine/lahutamine ja võrdlus. Seejuures on abiks ajavahemiku tüüp 'interval'.

  • liitmine ja lahutamine, nt arvutame milline oli kuupäev ja kellaaeg 9 päeva ja 23 minutit tagasi
sql> select current_timestamp - interval '9 days 23 minutes';
           ?column?            
-------------------------------
 2010-03-18 23:39:08.328026+02
  • võrdlus, nt võrdleme, kas inimene on sündinud enne või peale 1. jaanuar 1980
CREATE OR REPLACE FUNCTION kunassyndis(synniaeg timestamp)
  RETURNS character varying AS
$BODY$
  declare
  vastus varchar(50);
begin
    if synniaeg < '1980-01-01' then
       vastus = 'Sündinud enne 1. jaanuar 1980';
    else
       vastus = 'syndinud peale 1. jaanuar 1980';
    end if;
    return vastus;
end;
    $BODY$
LANGUAGE 'plpgsql' VOLATILE

ja funktsiooni kasutamine toimub 

select kunassyndis('1970-02-31');
         kunassyndis          
-------------------------------
 Sündinud enne 1. jaanuar 1980

extract ja date_trunk funktsioonid

  • extract funktsiooni abil saab timestamp tüüpi andmetest välja eraldada elemente, nt küsida sajandit
SELECT EXTRACT(CENTURY FROM TIMESTAMP '1999-12-16 12:21:13');
 date_part 
-----------
       20
  • date_trunc funktsioon esitab esimesena antud parameetrist kõik vähemtäpsed väljad
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
 date_trunc      
---------------------
 2001-02-16 20:00:00

Ajatsoonide vahelised teisendused

'at time zone' abil saab teisendada ühe tsooni aega teise tsooni kohalikuks ajaks, nt küsida, et kui Hong Kongis on kell 17:20, mis on siis kohalik aeg Almatas 

SELECT TIMESTAMP with time zone '2001-02-16 17:20 HKT' at time zone 'ALMT';
      timezone       
---------------------
 2001-02-16 15:20:00

Aeg ja transaktsioon

Käesoleva aja väärtusega vastamisel eristab PostgreSQL kahte juhtumit

  • transaktsiooni algus
  • reaalne käesolev aeg

Selliselt 

sql> begin;
BEGIN
sql> select current_timestamp;
             now              
------------------------------
 2010-03-28 12:35:42.77614+03

sql> select current_timestamp;
             now              
------------------------------
 2010-03-28 12:35:42.77614+03

sql> select clock_timestamp();
       clock_timestamp        
-------------------------------
 2010-03-28 12:35:58.983693+03

sql> select clock_timestamp();
       clock_timestamp        
-------------------------------
 2010-03-28 12:36:02.399565+03

sql> commit;
COMMIT

kust on näha, et currect_timestamp andis samu väärtusi transaktsiooni sees, kusjuures clock_timestamp() vastas reaalse kelleajaga.

Päästik

Päästikuks (ingl. k. trigger) http://www.postgresql.org/docs/current/interactive/trigger-datachanges.html nimetatakse sellist funktsiooni, mille täitmine kaasneb automaatselt vastavas tabelis toimuva muudatusega. Nt olgu olemas tabel customers 

CREATE TABLE customers 
(
  customer_id    integer primary key,
  customer_name  character varying(50) not null,
  phone          character(8) ,
  birth_date     date,
  balance        decimal(7,2)
);

ning tabel customers_archive 

CREATE TABLE customer_archive
( 
  customer_id     integer,
  customer_name   character varying(50) not null,
  phone           character(8) ,
  birth_date      date,
  balance         decimal(7,2) ,
  user_changed    varchar,
  date_changed    date,
  operation       varchar
);

Eesmärgiks on kõik customers tabelis toimunud muudatused arhiveerida tabelis customers_archive. Selleks sobib kirjeldada funktsioon 

CREATE FUNCTION archive_customer() RETURNS TRIGGER AS $$
  BEGIN
    INSERT INTO customer_archive 
      VALUES
      ( 
        OLD.customer_id,
        OLD.customer_name,
        OLD.phone,
        OLD.birth_date,
        OLD.balance,
        CURRENT_USER,
        now() ,
        TG_OP
      );
    RETURN NULL;
  END;
$$ LANGUAGE 'plpgsql';

Ning lõpuks moodustada trigger archive_customer seostades funktsiooni tabeliga customers 

CREATE TRIGGER archive_customer 
  AFTER DELETE OR UPDATE 
  ON customers
  FOR EACH ROW 
    EXECUTE PROCEDURE archive_customer();

Pärilus

Pärilus (ingl. k. inheritance) http://www.postgresql.org/docs/current/interactive/ddl-inherit.html väljendab asjaolu, et PostgreSQL on objekt-relatsiooniline (teatud mõttes objekt-orienteeritud) andmebaas. Nt võimaldab pärilus moodustada tabeleid, mis moodustavad hierarhia, kus üks tabel pärib ühe või mitme muu tabeli omadusi.Päriluse peamiseks kasutusalaks on järgmises punktis kirjeldatud tabeli partitsioneerimine, antud punktis kirjeldatakse päriluse endaga seotud asjaolusid.

Olgu nn master tabel priit.loomad, st tabel mille omadusi mõni teine tabel hakkab pärima selline 

CREATE TABLE priit.loomad
(
  loom_id serial NOT NULL,
  liik character varying(50),
  vanus integer,
  nimi character varying(50),
  CONSTRAINT loom_id_pkey PRIMARY KEY (loom_id)
)

Sisaldugu tabelis priit.loomad endas sellised andmed 

1  karu  20  Mõmmi
2  hunt  10  Kriim Silm
3  kana   2  Cana

Olgu teine tabel priit.loomad_haruldased, mis pärib tabeli priit.loomad omadusi 

CREATE TABLE priit.loomad_haruldased
(
-- Inherited from table priit.loomad_haruldased:  loom_id integer NOT NULL DEFAULT nextval('priit.loomad_loom_id_seq'::regclass),
-- Inherited from table priit.loomad_haruldased:  liik character varying(50),
-- Inherited from table priit.loomad_haruldased:  vanus integer,
-- Inherited from table priit.loomad_haruldased:  nimi character varying(50),
  haruldusaste character varying,
  CONSTRAINT loom_haruldus_id_key PRIMARY KEY (loom_id)
)
INHERITS (priit.loomad)

Sisaldugu priit.loomad_haruldased endas sellised andmed 

1  merivart      120  Merra    väga haruldane
2  viiger          1  Vii      haruldane
3  peipsi tink     5  Tint     väga haruldane

Selline konstruktsioonil on järgmised omadused

  • tehes päringuid tabelist priit.loomad saab kasutada kõiki andmeid, st tabelis priit.loomad endas sisalduvaid andmeid ning ka tabeli priit.loomad_haruldased andmeid
sql> select * from priit.loomad;
 loom_id |     liik     | vanus |    nimi    
---------+--------------+-------+------------
       1 | karu         |    20 | Mõmmi
       2 | hunt         |    10 | Kriim Silm
       3 | kana         |     2 | Cana
       1 | merivart     |    12 | Merra
       2 | viiger       |     1 | Vii
       3 | peipsi tink  |     5 | Tint
  • kasutades tabelit priit.loomad_haruldased saab teha efektiivselt päriguid haruldaste loomade kohta
sql> select * from priit.loomad_haruldased;
 loom_id |     liik     | vanus |  nimi  |  haruldusaste  
---------+--------------+-------+--------+----------------
       1 | merivart     |    12 | Merra  | väga haruldane
       2 | viiger       |     1 | Vii    | haruldane
       3 | peipsi tink  |     5 | Tint   | väga haruldane
  • kasutades tabelit priit.loomad päringut kaas only direktiiviga, saab küsida kõiki mitte-haruldasi loomi
sql> select * from only priit.loomad;

 loom_id |     liik     | vanus |    nimi    
---------+--------------+-------+------------
       1 | karu         |    20 | Mõmmi
       2 | hunt         |    10 | Kriim Silm
       3 | kana         |     2 | Cana

Andmete sisestamisel peab arvestama, et

  • andmeid saab sisestada nö füüsilisse tabelisse
  • andmeid muutmine toimub ka läbi päriluse, nt
sql> update priit.loomad set vanus='10' where nimi='Tint';

Päriluse võimalusi otstarbekalt kasutades on võimalik tekitada selline andmemudel, mis on kompaktne ning võimaldab andmetega efektiivselt töötada.

Olemasolevate tabelite vahel pärimise tekitamiseks või lõpetamiseks sobib öelda vastavalt 

alter table priit.loomad_haruldased inherit priit.loomad
alter table priit.loomad_haruldased no inherit priit.loomad

Kasulikud lisamaterjalid

Tabeli partitsioneerimine

Tabeli partitsioneerimise http://www.postgresql.org/docs/current/static/ddl-partitioning.html tulemusena koosneb üks suur loogiline tabel mitmest väiksemast füüsilisest tabelist. Partisioneerimisel on sellised eelised

  • päringu jõudlus suureneb teatud juhtumitel, nt kui päring töötab valdavalt ainult mõnes partitsioonis; efekt toimub muuseas tänu selle, et nt ühe suure indeksi asemel on tegu mitme väiksema indeksiga mis mahub paremine mällu
  • massilised kirjete kustutamised toimuvad oluliselt kiiremini eeldusel, et andmemudel näeb ette võimaluse kustutada kogu kogu partitsiooni
  • harva kasutatavate andmetega partitsioone saab paigutada odavamale ja aeglasemale salvestusseadmele

PostgreSQL andmebaasis saab partitsioneerimist korraldada iseensest kahel viisil

  • toetudes tabeli pärilusele - eelistatud viis, mida järgnevas põhiliselt kirjeldatakse
  • kasutades view ja union võimalusi

Näiteks kirjeldatakse partitsioneeritud tabeli kasutamist pika ajavahemiku jooksul tabelisse loomadsoovad andmete kogumisel. Efekt tuleneb sellest, et suur hulk andmeid jaotatakse aastate kaupa mitmete füüsiliste tabelite vahel ära.

Partitsiooni master tabel partitsioon.loomadsoovad moodustamiseks sobib öelda 

CREATE TABLE partitsioon.loomadsoovad
(
  loomadsoovad_id serial NOT NULL,
  kuupaev date,
  rasvad integer,
  valgud integer,
  sysivesikud integer,
  CONSTRAINT loomadsoovad_id_pkey PRIMARY KEY (loomadsoovad_id)
)

ning sellest pärinevad tabelid, oluline on rakendada selliseid piiranguid, et partitsioonides ei oleks korduvaid andmeid 

create table partitsioon.loomadsoovad_2005 (
  CHECK ( kuupaev >= DATE '2005-01-01' AND kuupaev < DATE '2005-12-31' ) 
) inherits (partitsioon.loomadsoovad);

create table partitsioon.loomadsoovad_2006 (
  CHECK ( kuupaev >= DATE '2006-01-01' AND kuupaev < DATE '2006-12-31' ) 
) inherits (partitsioon.loomadsoovad);
...

Kirjeldatakse funktsioon 

CREATE OR REPLACE FUNCTION partitsioon.loomadsoovad_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO partitsioon.loomadsoovad_2010 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Kirjeldatakse trigger 

CREATE TRIGGER insert_loomadsooad_trigger
  BEFORE INSERT
  ON partitsioon.loomadsoovad
  FOR EACH ROW
  EXECUTE PROCEDURE partitsioon.loomadsoovad_insert_trigger();

Andmete sisestamine toimub sellised, kusjuures need andmed satuvad tabelisse partitsioon.loomadsoovad_2010 

insert into partitsioon.loomadsoovad (kuupaev, rasvad, valgud, sysivesikud) values ('2010-03-25', '50', '500', '5000');

Partitsiooneeritud tabeli kasutamiseks sobib öelda 

select * from partitsioon.loomadsoovad;

või siis ka otse 

select * from partitsioon.loomadsoovad_2010;

Märkused

TODO

contrib moodulite kasutamine

dblink

dblink tehnika võimaldab ühe andmebaasi päringutes kasutada teise andmebaasi andmeid.

  • Näiteks lihtsalt päringut teha
SELECT *
FROM dblink.dblink('dbname=test host=192.168.10.205 user=postgres','SELECT nimi, vanus FROM priit.priidutabel')
  AS tabelinimi(nimi varchar(50), vanus integer);
  • Tekitada teise andmebaasi andmetest vaade
create view dblink.dblink_view as
  select *
    from dblink.dblink('dbname=test host=192.168.10.205 user=postgres', 'SELECT nimi, vanus FROM priit.priidutabel')
    as tabelinimi(nimi varchar(50), vanus integer);

ja vaate kasutamiseks öelda nt 

select * from dblink_view;

pgcrypto

pgcrypto moodul võimaldab kasutada andmebaasis krüptimise funktsioone. Nt md5 vormingus parooli genereerimiseks sobib öelda 

 UPDATE pgcrypto.priidutabel SET parool = pgcrypto.crypt('new password', pgcrypto.gen_salt('md5'));

tsearch2

tsearch2 on täisteksti otsingu moodul. 

SELECT 'Priit ei ole Mart'::tsvector @@ 'Priit & Mart'::tsquery;
 ?column? 
----------
 t
(1 row)


Massiivi kasutamine

PostgreSQLi atriibuut saab olla ka massiiv (ingl. k. array). Nt selliselt 

CREATE TABLE domeeninimi
(
  domeeninimi_id serial NOT NULL,
  tld character(2),
  domeeninimed character varying(50)[]
)

kus tüüp 'character varying(50)[]' näitab, et tegu on määramatu arvu varchar(50) elementidega massiiviga.

Andmete sisestamine toimub öeldes 

sql> insert into domeeninimi (tld, domeeninimed) values ('lv', '{"riga.lv", "pritus.lv", "latvia.lv", "sagrebis.lv"}')

Andmete päring toimub öeldes 

sql> select tld, domeeninimed[1] from domeeninimi;
 tld | domeeninimed 
-----+--------------
 ee  | tartu.ee
 lv  | riga.lv

Binary andmete hoidmine andmebaasis

Vt http://kuutorvaja.eenet.ee/wiki/Python#Binary_andmete_sisestamine

Tehted hulkadega

UNION, INTERSECT JA EXECPT abil on võimalik kombineerida mitut select lauset kusjuures

  • UNION - esitatakse read, mis on ühes või mõlemas result setis
  • INTERSECT - esitatakse read, mis on mõlemas result setis
  • EXECPT - esitatakse read, mis on esimeses kuid mitte teises result setis

Näiteks 

select nimi from hulgad.hulk1 except select nimi from hulgad.hulk2;

Misc

  • tabel ühte tehtud päringu tulemuste lisamine tabel kahte
insert into tabel2 (nimi, aadress) select nimi, aadress from tabel1
  • coalesce tagastab esimese mitte-null väärtuse
sql> select coalesce(null, 'vaartus');
 coalesce 
----------
 vaartus
  • Ajutine tabel eksisteerib kuni andmebaasiühenduse lõpuni
CREATE TEMP TABLE tabelinimi(name varchar(50), vanus integer);
  • result set - vastus andmebaasile esitatud päringule, või sisaldada mitte midagi, ühte või enamat rida
  • jõudlus
explain verbose select * from teenused where nimi='teenuse nimi_O_13437';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on teenused2  (cost=6.60..196.17 rows=48 width=48)
   Output: id, nimi, aadress
   Recheck Cond: ((nimi)::text = 'teenuse nimi_O_13437'::text)
   ->  Bitmap Index Scan on teenused_idx  (cost=0.00..6.59 rows=48 width=0)
         Index Cond: ((nimi)::text = 'teenuse nimi_O_13437'::text)
  • Viivituse esilekutsumiseks sobib kasutada, kus 2 tähendab kahte sekundit
select pg_sleep(2);
  • Tabeliga
alter table test.inimene add constraint inimene_id primary key (inimesed_id);
alter table test.inimene add eesnimi varchar(50);
  • Tabeli ümbernimetamine
sql> alter table skeeminimi.inimesed rename to inimesed2;

Kasulikud lisamaterjalid

Pärit leheküljelt "https://kuutorvaja.eenet.ee/w/index.php?title=PostgreSQL_SQL&oldid=20074"