Postgresql Veri Tabanı Objeleri II

VIEW

Sorguyu tutan veri tabanı objesidir. Sanal bir tablo olarak düşünülebilir, veri tutmaz ama tabloların üzerindeki veriye erişmeyi sağlar. Aşağıdaki komut ile oluşturulurlar.

CREATE VIEW view_name AS query;

ÖRNEK

Dvd rental isimli sample veri tabanında yer alan tablolardan;

 customer – müşteri bilgilerini tutar

 address – müşteri adreslerini tutar

 city – şehir bilgilerini tutar

 country– ülke bilgilerini tutar

Aşağıdaki SQL komutu ile veri tabanından bir müşterinin adres bilgisi getirlir.

SELECT cu.customer_id AS id,

    cu.first_name || ‘ ‘ || cu.last_name AS name,

    a.address,

    a.postal_code AS “zip code”,

    a.phone,

    city.city,

    country.country,

        CASE

            WHEN cu.activebool THEN ‘active’

            ELSE ”

        END AS notes,

    cu.store_id AS sid

   FROM customer cu

     INNER JOIN address a USING (address_id)

     INNER JOIN city USING (city_id)

     INNER JOIN country USING (country_id);

Bu karışık sorguyu her defasında kullanmak yerine bir isimli view oluşturulur;

CREATE VIEW detailed_address  AS SELECT cu.customer_id AS id, cu.first_name || ‘ ‘ || cu.last_name AS name, a.address, a.postal_code AS “zip code”, a.phone, city.city, country.country, CASE WHEN cu.activebool THEN ‘active’ ELSE ” END AS notes, cu.store_id AS sid FROM customer cu INNER JOIN address a USING (address_id) INNER JOIN city USING (city_id) INNER JOIN country USING (country_id);

ALTER VIEW detailed_address  RENAME TO detailed_address_new;

DROP VIEW IF EXISTS detailed_address  ;

INDEX

Veri Tabanı üzerinde sorgu performansını arttırmak, aranılan verilerin daha hızlı getirilmesi için kullanılan veri tabanı objesidir. Indexler veriyi ve bulunduğu page ve tuple’ı adresler. Index’in bulunduğu kolana bir sorgu geldiğinde,bu index kontrol edilerek verinin bulunduğu page ve tuple’a gidilir.

Postgresql Veri tabanında INDEX oluşturma işlemi aşağıdaki komut ile yapılır.

CREATE INDEX  index_name  ON table_name

[USING method]

( column_name [ASC | DESC] [NULLS {FIRST | LAST }] );

ORNEK :

Dvd rental sample veri tabanında bulunan address tabloasundaki phone kolonu için index oluşturma işlemi şu şekilde yapılır.

SELECT * FROM  address WHERE phone = ‘223664661973’;

Bu komutun maliyetine bakmak için başına EXPLAIN yazılır ve costu öğrenilir.

Daha sonra phone kolonu üzerinde aşağıdaki komut ile Index oluşturulur.

CREATE INDEX idx_address_phone ON address(phone);

Index oluşturma işleminden sonra aynı sorgunun cost’una tekrar bakılır.

INDEX DROP IŞLEMI

Bir indexi ilgili kolonlardan kaldırmak için çalıştırılması gereken komut aşağıdaki gibidir.

DROP INDEX  [ CONCURRENTLY]

[ IF EXISTS ]  index_name

[ CASCADE | RESTRICT ];

CASCADE Keywordu ile silinecek indexe bağlı objeler de drop edilir.

RESTRICT ise bağlı objeler var ise silme işlemini gerçekleştirmez. Defaul olarak bu seçenek aktiftir.

ORNEK

Dvd rental veri tabanında yer alan actor tablosunda first_name kolonu üzerinde bir index oluşturulsun;

CREATE INDEX idx_actor_first ON actor (first_name);

SELECT  * FROM   actor WHERE first_name = ‘John’;

EXPLAIN ile sorgunun çalışma planına bakıldığında yaratılan indexi kullanmadığı görülür.

Bu durumda silme işlemi için kullanılacak komut şudur;

DROP INDEX idx_actor_first;

Veri tabanı üzerindeki bütün indexleri görebilmek için  pg_indexes tablosu aşağıdaki gibi sorgulanabilir;

SELECT    tablename,    indexname,    indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;

TRIGGER

Veri tabanında bir tablo üzerinde update, insert yada delete operasyonu gerçekleştiğinde otomatik olarak gerçekleştirilecek işlemlerin tanımlandığı veri tabanı objesi trigger’dır.

Trigger oluşturmak için önce bir trigger fonkisyonu aşağudaki komut ile oluşturulur.

CREATE FUNCTION trigger_function() RETURNS trigger AS

Daha sonra ise trigger objesi aşağıdaki komut ile oluşturulur.

CREATE TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} {event [OR …]}

   ON table_name

   [FOR [EACH] {ROW | STATEMENT}]

       EXECUTE PROCEDURE trigger_function

Bu komuttaki event keywordü insert update delete veya Truncate işlemlerinden birtanesi olabilir.

Before After keywordleri trigger’in ne zaman tetikleneceğini gösterir.

ORNEK

Calisanlar ve calisan_kayit tablolarını oluşturup, bir çalışanın soyadı değiştiğinde çalışan kayıt tablosuna bu değişikliğin bilgisini yazan trigger şu şekilde oluşturulur.

CREATE TABLE calisanlar( id SERIAL PRIMARY KEY, isim  VARCHAR(40) NOT NULL, soyisim VARCHAR(40) NOT NULL );

CREATE TABLE calisan_kayit ( id SERIAL PRIMARY KEY, calisan_id INT NOT NULL, soyisim VARCHAR(40) NOT NULL, degisim_tarihi TIMESTAMP(6) NOT NULL )

CREATE OR REPLACE FUNCTION soyisim_degisikligi()

  RETURNS trigger AS

$BODY$

BEGIN

              IF NEW.soyisim <> OLD.soyisim THEN

                             INSERT INTO calisan_kayit(calisan_id,soyisim,degisim_tarihi)

                             VALUES(OLD.id,OLD.soyisim,now());

              END IF;

              RETURN NEW;

END;

$BODY$

Daha sonra trigger oluşturulur.

CREATE TRIGGER trg_soyisim_degisikligi

  BEFORE UPDATE

  ON employees

  FOR EACH ROW

  EXECUTE PROCEDURE soyisim_degisikligi ();

Test kaydı eklenir;

INSERT INTO calisanlar(isim, soyisim) VALUES (‘Ali’, ‘Birinci’);

INSERT INTO calisanlar(isim, soyisim) VALUES (‘Mehmet’, ‘İkinci’);

UPDATE calisanlar SET LAST_NAME=’SONUNCU’ where id=1;

SELECT * FROM calisan_kayit;

DROP TRIGGER trigger_name ON table_name komutu ile trigger objesi silinebilir.

SEQUENCE

Sıralı integer değerleri tutan ve kullandıran veri tabanı obejeleridir. Sequence oluşturmak için kullanılan komut şu şekildedir;

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name

    [ AS { SMALLINT | INT | BIGINT } ]

    [ INCREMENT [ BY ] increment ]

    [ MINVALUE minvalue | NO MINVALUE ]

    [ MAXVALUE maxvalue | NO MAXVALUE ]

    [ START [ WITH ] start ]

    [ CACHE cache ]

    [ [ NO ] CYCLE ]

    [ OWNED BY { table_name.column_name | NONE } ]

INCREMENT : Sequence değerlerinin kaçar karçar artacağını gösterir.

MINVALUE-MAXVALUE : Başlangıç ve bitiş değeri belirtmek için kullanılır. NO MINVALUE ve NO MAXVALUE keywordleri kullanılırsa default değerler başlangıç ve bitiş değerleri olarak set edilir.

START WITH : Sequence’in başlangıç değerini belirtir.

CACHE : Memory’de kaç tane sequence değeri daha hızlı erişim için hazırda tutulacak bilgisini verir.

OWNED BY: Sequence’I bir tablonun kolonunu ile ilişkilendirmek için kullanılır. Tablo veya kolon drop edildiğinde otomatik olarak sequence’de drop edilir.

CYCLE ve NO CYCLE : Sequence limitleri aşıldığı zaman başa dönülüp dönülmeyeceğini belirtir. Default olarak NO CYCLE’dır.

ÖRNEKLER

CREATE SEQUENCE seq_1001 INCREMENT 5 START 100;

SELECT nextval(‘seq_1001’);

CREATE SEQUENCE seq_1002 INCREMENT -1 MINVALUE 1 MAXVALUE 3 START 3 CYCLE;

SELECT nextval(‘seq_1002’);