Postgresql Schema 

CREATE SCHEMA ik_schema;
# schema ismi ile sorgu
Create table ik_schema.personel (…);
Select * from ik_schema.personel;

# veritabanı.schema.tablo
select * from testdb.ik_schema.personel;

#Schema nın silinmesi
Drop schema ik_schema;

#Schema nın tüm objeleri ile silinmesi
Drop schema ik_schema cascade;

# Sahipliği başka bir kullanıcı olan schema
CREATE SCHEMA erp_schema AUTHORIZATION erp;

# Sahiplik değişimi
alter schema test_schema owner to Mehmed ;

# İsim değişikliği
alter schema test_schema rename to yeniisim ;

# schema listesi ve sahipleri
testdb=# \dn
List of schemas
Name         | Owner
-------------+----------
erp_schema   | erp
ik_schema    | ik_role
public       | postgres
test_schema  | mehmed

Objelerin (tablo, view, index, fonksiyon, prosedür vb.) iş mantığına (business logic) ve
hiyerarşisine göre gruplanması ve organize edilmesi için kullanılır.
Örneğin arşiv, denetleme (audit), muhasebe, insan kaynakları gibi farklı uygulamaların
verilerini schema’larla bir birinden ayırmak hem yönetim kolaylığı sağlar hem de
güvenliği artırır.
Veritabanına özeldir yani her veritabanının kendi schemaları vardır.

search_path

testdb=> show search_path ;
search_path
-----------------
"$user", public
testdb=> set search_path='$user', 'ik_schema', 'erp_schema' ;
testdb=> show search_path ;
search_path
--------------------------------
"$user", ik_schema, erp_schema
testdb=> CREATE TABLE t1 (id serial4 NOT NULL,"name" text NULL) ;
testdb=> \dt *.*
List of relations
Schema     | Name | Type | Owner
--------------------+-------------------------+-------+--------
erp_schema | t2 | table | ahmed
ik_schema  | t1 | table | ahmed
testdb=> select * from t2 ; # schema ismi vermeden sorgulanabilir.
id | name
----+------
(0 rows)

SQL cümlelerinde objelerin tam ismini (qualified names – schema.tablo_ismi)
yazmak zahmetli olabiliyor. Bu durumun önüne geçmek için search_path kullanılır.
SQL cümlelerinde schema belirtilmezse objeler önce default schema olan public’te
aranır/oluşturulur. Yani bir tablo oluşturmak istenildiğinde schema ismi verilmezse bu
tablo public schema’da oluşturulur. Select cümlelerinde aynı durum geçerlidir.
Search path’deki ilk schemada oluşturulur. Sorgulamalarda ise sıra ile arayarak devam
eder.

Örnekler

ALTER ROLE ahmed IN DATABASE VT_ismi SET search_path TO
"$user",erp,public;
alter role ahmed set search_path = "$user", ik_schame,public ;
show search_path ;
SET search_path="$user",public,ik_schema;
alter user dataera set search_path=ik_schema ;

ik_schema’sının sahibi ik_role iken;

# Ahmed kullanıcısını oluşturuyoruz
postgres=# create role ahmed login ;
# Ahmed kullanıcısı ile login olup ik_schema’da tablo oluşturulur.
postgres@srv1:~$ psql -d testdb -U ahmed
CREATE TABLE ik_schema.t10 (id serial4 NOT NULL,"name" text NULL) ;
ERROR: permission denied for schema ik_schema
LINE 1: CREATE TABLE ik_schema.t10 (id serial4 NOT NULL,”name”
text ...
postgres=# exit
# postgres kullanıcısı ile bağlanılıp ik_role hakkı verilir.
postgres@srv1:~$ psql
postgres=# grant ik_role to ahmed ;
postgres=# exit

# Tablo oluşturulur.
postgres@srv1:~$ psql -d testdb -U ahmed
testdb=> CREATE TABLE ik_schema.t10 (id serial4 NOT NULL,"name" text NULL) ;

Not: Veritabanı sunucusu üzerinde çalıştığımız için password girmiyoruz. Farklı
bir makineden bağlanmak için önce ahmed kullanıcısına şifre atayıp aşağıdaki
parametrelerle bağlantı yapılması gerekirdi.

psql -h 10.71.6.61 -p 5344 -d testdb -U ahmed -W

# Oluştururken
create role ahmed login password = 'sifre'
# Var olan kullanıcının şifresini değiştirmek ya da atamak için
alter role/user ahmed password 'sifre' ;

Bir schemadaki tüm tabloları listeleme;

#testdb veritabanındaki ik_schema sının tüm tabloları
testdb=# SELECT table_name FROM information_schema.tables
WHERE table_schema = 'ik_schema' and table_catalog = 'testdb';

Bir schema’daki tüm tabloları getiren diğer bir sorgu;

SELECT t.table_name, t.table_type, c.relname, c.relowner,
u.usename
FROM information_schema.tables t
JOIN pg_catalog.pg_class c ON (t.table_name = c.relname)
JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid)
WHERE t.table_schema='ik_schema';

Bir schema’daki tüm tabloların sahipliğini değiştirme;

SELECT 'ALTER TABLE ' || table_name || ' OWNER TO yeni_sahip; '
FROM information_schema.tables WHERE table_schema = 'ik_schema'
and table_catalog = 'testdb';

Bir schemadaki tüm tabloların isimlerini değiştirme (tablo isimlerinin önüne “Sysdata_” eklemek istediğimizde)

SELECT 'alter table ' ||c.relname|| ' rename to SysData_'
||c.relname|| ';'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
WHERE c.relkind ='r'
AND n.nspname='ik_schema'
ORDER BY 1;

Bir schemadaki kolon isimlerini küyük harfe dönüştürme

SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || ' RENAME "' || c.column_name
|| '" TO ' || quote_ident(lower(c.column_name)) || ';' As
ddlsql
FROM information_schema.columns As c
WHERE c.table_schema= 'ik_schema'
AND c.column_name <> lower(c.column_name)
ORDER BY c.table_schema, c.table_name, c.column_name;

Bir schema’da sadece okuma hakkına sahip kullanıcı oluşturma (readonly user);

postgres=# CREATE ROLE okuyucu WITH login password 'sifre'
# Hak verilecek schemanın olduğu veritabanına bağlanılır.
postgres=# \c testdb
GRANT USAGE ON SCHEMA erp_schema TO okuyucu;
GRANT SELECT ON ALL TABLES IN SCHEMA erp_schema TO okuyucu;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA erp_schema TO okuyucu;

postgres@srv1:~$ psql -d testdb -U okuyucu
testdb=> select * from erp_schema.t2 ;
id | name
----+------
(0 rows)

# Oluşturulacak tablolara otomatik olarak bu hakların verilmesi için
alter default privileges in schema erp_schema grant select on tables to okuyucu ;

Schema’ların sahipliğini değiştirmek için SQL scriptlerini üretme;

SELECT DISTINCT 'GRANT'
|| CASE schemaname WHEN 'pg_catalog' THEN 'USAGE' ELSE
'ALL' END
|| ' ON SCHEMA ' || quote_ident(schemaname) || ' TO mehmed;'
FROM pg_tables;

Kategori seçin...