PostgreSQL SQL

Allikas: Kuutõrvaja
Redaktsioon seisuga 25. märts 2010, kell 00:23 kasutajalt Imre (arutelu | kaastöö) (→‎Reegel)

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;

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 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äline võti

Välise võtme kasutamiseks sobib moodustada tabel selliselt 

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

self-refrencing

Self-refrencing puhul viitab välisvõti 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 jõudmist 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)

Trigger

Triggeriks 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();

Protseduurilised keeled

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

Kursorite kasutamine

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)

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

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