PostgreSQL SQL

Allikas: Kuutõrvaja
Redaktsioon seisuga 24. märts 2010, kell 01:16 kasutajalt Imre (arutelu | kaastöö) (→‎Relatsioonid)

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

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=18645"