Erinevus lehekülje "PostgreSQL SQL" redaktsioonide vahel

Redaktsioon: 25. märts 2010, kell 21:06

Sissejuhatus

TODO

Andmebaasi klientprogramm

Reeglina sisaldab andmebaasitarkvara lisaks andmebaasi serverile ka mingit terminalil kasutatavat nö käsurea klientprogrammi, nt PostgreSQL puhul on selleks programm psql.

andmebaasi kliendi kasutamine kalkulaatorina

select (5 * 5);
 ?column? 
----------
       25
(1 row)

andmebaasi klient töötab sõnedega

select 'Hello' || ' World!';
   ?column?   
--------------
 Hello World!
(1 row)

Andmebaasi kliendi juures saab määrata esituse formaati, nt (vaikimisi on see aligned)

\pset format html

Select

select lausega saab lihtsamal juhul kasutada andmebaasis kirjeldatud funktsioone, nt

select sqrt(25);

Tabli moodustamine select lause väljundist

select * into pgcrypto.priidutabel from priit.priidutabel;

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;

Andmebaasi hierarhia

andmekluster
andmebaas

create database andmebaasinimi;

skeem

create schema skeeminimi;

tabel

create table ...

Andmetüübid

TODO

Numbrijada

Numbrijada (ingl. k. sequence) moodustamiseks sobib öelda

CREATE SEQUENCE jadanimi start 1;

Relatsioonid

TODO

Tabel

Ajutine tabel eksisteerib kuni andmebaasiühenduse lõpuni

CREATE TEMP TABLE tabelinimi(name varchar(50), vanus integer)

Mõisted

result set - vastus andmebaasile esitatud päringule, või sisaldada mitte midagi, ühte või enamat rida

Misc laused

tabel ühte tehtud päringu tulemuste lisamine tabel kahte

insert into tabel2 (nimi, aadress) select nimi, aadress from tabel1

Andmete kopeerimine failisüsteemi

copy customers to '/tmp/customers.txt';

Andmete kopeerimine failisüsteemist

copy customers from '/tmp/customers.txt';

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

ALTER TABLE tabelinimi ADD PRIMARY KEY (valjanimi_id);

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

Indeksid

TODO

Vaated

TODO

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.*);

Transaktsioon

TODO

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)

Päästik

Päästikuks (ingl. k. trigger) 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();

Kursorite kasutamine

TODO

Pärilus

Pärilus (ingl. k. inheritance) http://www.postgresql.org/docs/current/interactive/ddl-inherit.html võimaldab ...

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

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)

Protseduurilised keeled

TODO

plpgsql

Nt PL/pgSQL on üks PostgreSQL andmebaasi protseduuriline keel.

$ createlang -h 127.0.0.1 -U postgres plpgsql movies

Ja tulemuse kontrollimiseks

movies=# select * from pg_language ;
 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

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

Faktoriaali arvutamise kirjeldab nt selline PL/pgSQL funktsioon, kusjuures tegu on rekursiivse funktsiooniga

CREATE OR REPLACE FUNCTION my_factorial( value INTEGER) RETURNS INTEGER AS $$
  DECLARE
    arg INTEGER;
  BEGIN
  
    arg := value;
 
    IF arg IS NULL OR arg < 0 THEN
      RAISE NOTICE 'Invalid Number' ;
      RETURN NULL;
  
    ELSE

      IF arg = 1 THEN
        RETURN 1;
      ELSE
        DECLARE
          next_value INTEGER;
        BEGIN
          next_value := my_factorial(arg - 1) * arg;
          RETURN next_value;
        END;
      END IF;
 
    END IF;
  END;
$$ LANGUAGE 'plpgsql';

Funktsiooni kasutamiseks sobib öelda

select my_factorial();

Funktsiooni kustutamiseks sobib öelda

drop function liitja(integer, integer);

plproxy

TODO

plperl

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 plpythonu_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

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

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

 use POSIX qw(locale_h);
 
 open (fh, '>>/tmp/locale.log');
 print fh "start\n";
 $lokaat{LC_CTYPE}=setlocale(LC_CTYPE);
 $lokaat{LC_TIME}=setlocale(LC_TIME);
 $lokaat{LC_COLLATE}=setlocale(LC_COLLATE);
 $lokaat{LC_MESSAGES}=setlocale(LC_MESSAGES);
 $lokaat{LC_MONETARY}=setlocale(LC_MONETARY);
 $lokaat{LC_NUMERIC}=setlocale(LC_NUMERIC);
 
 while ( my ($key, $value) = each(%lokaat) ) {
          print fh "$key => $value\n";
              }
 print fh "end\n";
 close fh;
 
 return "i am print_locale";

plpython

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')

pljava

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';

Failisüsteemi kasutamine

Failisüsteemi salvestamine

Andmebaasist päringu tulemuse kopeerimiseks failisüsteemi sobib nt esmalt moodustada ajutine tabel priit.nimed

CREATE TEMP TABLE nimed AS SELECT nimi FROM priit.priidutabel;

ning seejärel tabeli sisu salvestada failisüsteemi öeldes

copy nimed to '/tmp/priit.log';

Failisüsteemist lugemine

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

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

Binary andmete hoidmine andmebaasis

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

Kasulikud lisamaterjalid

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

Erinevus lehekülje "PostgreSQL SQL" redaktsioonide vahel

Redaktsioon: 25. märts 2010, kell 21:06

Sisukord

Sissejuhatus

Andmebaasi klientprogramm

Select

Tehted hulkadega

Andmebaasi hierarhia

Andmetüübid

Numbrijada

Relatsioonid

Tabel

Mõisted

Misc laused

Primaarne võti

Välisvõti

enesele viitav välisvõti

Indeksid

Vaated

Reeglid

Transaktsioon

Jõudlus

Päästik

Kursorite kasutamine

Pärilus

Tabeli partitsioneerimine

Märkused

dblink

pgcrypto

tsearch2

Protseduurilised keeled

plpgsql

plproxy

plperl

plpython

pljava

Failisüsteemi kasutamine

Failisüsteemi salvestamine

Failisüsteemist lugemine

Binary andmete hoidmine andmebaasis

Kasulikud lisamaterjalid

Navigeerimismenüü

Otsing