Schémata

Cluster, Catalog, Schéma, Tabulka

Už víte, že tabulky musíte vytvořit v nějakém schématu. Na obrázku můžete vidět, jaký je vztah cluseteru (DBMS), databáze (catalog) a schématu dle normy SQL. Jak je to ve skutečnosti implementováno v různých DBMS se dozvíte teď a tady. V této kapitole ukážu, jak používat schémata a některá speciální schémata, které máte v databázi k dispozici.

Public schéma
Vytvořní a smazání schéma
Informační schéma
PG katalog schéma
Cluster (DBMS)
MySQL/MariaDB
SQLite
Oracle

Public schéma

Schémata se používají k seskupování logicky souvisejících tabulek, k nastavování přístupových práv (nad schématem), nebo k zabránění kolize jmen v různých aplikacích, které využívají tabulky stejného jména k různým účelům. Nic světoborného v tom nehledejte.

V PostgreSQL vytvoříte databázi (catalog) příkazem CREATE DATABASE nebo skriptem createdb, jak bylo popsáno v kapitole Začínáme s PostgreSQL.

V PostgreSQL se v každé nové databázi automaticky vytvoří schéma public. Všechny databázové objekty, pokud nevytvoříte a neurčíte jiné schéma, se vytvoří automaticky v tomto schématu. Všiměte si sloupečku Schéma v náseldujícím příkladu.

Přesněji řečeno, vytvoří se v schématu, které je první v proměnné search_path. Defaultně je tam nastaveno nejdříve schéma stejné jako je vaše uživatelské jméno a za ním public. Protože jsem ale nikdy nevytvořil schéma schodného jména jako je mé uživatelské jméno (petr), postgres toto jméno ignoruje a použije první existující schéma – public.

K zobrazení hodnoty proměnné slouží v Postgresu příkaz SHOW, k nastavení příkaz SET. Postgres má spousty a spousty proměnných, search_path je jen jedna z nich. Proměnným se budu ještě věnovat v kapitole o konfiguraci.

SHOW search_path;
search_path
----------------
"$user",public
(1 řádka)

Změnit search_path můžete pomocí příkazu SET:

SET search_path TO schema1, schema2, ... ;

Objekty můžte kvalifikovat pomocí jména schématu (můžete přesně určit, z jakého schématu vás objekt zajímá, nebo v jakém ho chcete vytvořit). Dělá se to podobně jako se kvalifikují sloupce tabulkami – pomocí tečkové notace.
Následující dva příkazy dělají totéž (předpokládám že schéma public je první existující schéma v search_path).

CREATE TABLE operator ( id SERIAL PRIMARY KEY NOT NULL, jmeno VARCHAR(20) NOT NULL UNIQUE);
CREATE TABLE public.operator ( id SERIAL PRIMARY KEY NOT NULL, jmeno VARCHAR(20) NOT NULL UNIQUE);

Pokud by nebylo schéma public první existující schéma, například by existovalo schéma petr, tedy stejného jména jako mé uživatelské jméno, pak by první příkaz vytvořil tabulku operator v schématu petr a ne ve schématu public.

První uvedené (a existující) schéma v search_path je tzv. běžné schéma. Používá se všude tam, kde nekvalifikujete objekty pomocí schéma.

V Postgresu můžete ještě kvalifikovat schéma pomocí jména databáze. Předchozí příkaz by se dal napsat takto:

CREATE TABLE rimmer1.public.operator ( id SERIAL PRIMARY KEY NOT NULL, jmeno VARCHAR(20) NOT NULL UNIQUE);

Ovšem pozor. V Postgresu není možné pracovat s jinou databází, než ke které jste připojeni (takže kvalifikace pomocí jména databáze je na prd):

rimmer1=> CREATE TABLE rimmer2.public.operator (id SERIAL PRIMARY KEY NOT NULL, jmeno VARCHAR(20) NOT NULL UNIQUE);
ERROR: cross-database references are not implemented: "rimmer2.public.operator"

Tabulky z různých schémat můžete bez problémů JOINovat, provádět INSERT INTO … SELECT, vytvářet mezi nimi reference atp.

Vytvořní a smazání schéma

Schéma z aktuální databáze zobrazíte metapříkazem \dn.

rimmer1=> \dn
Seznam schémat
Jméno | Vlastník
--------+----------
public | postgres
(1 řádka)

Vytvoření nového schéma pomocí CREATE SCHEMA:

rimmer1=> CREATE SCHEMA petr;
rimmer1=> \dn
Seznam schémat
Jméno | Vlastník
--------+----------
petr | petr
public | postgres
(2 řádky)

Všiměte si, co se stane, když vytvoříte tabulku stejného jména, ale v jiném schématu:

rimmer1=> CREATE TABLE petr.operator ( id SERIAL PRIMARY KEY NOT NULL, jmeno VARCHAR(20) NOT NULL UNIQUE);
NOTICE: CREATE TABLE will create implicit sequence "operator_id_seq" for serial column "operator.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "operator_pkey" for table "operator"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "operator_jmeno_key" for table "operator"
CREATE TABLE
rimmer1=> \dt operator
Seznam relací
Schéma | Jméno | Typ | Vlastník
--------+----------+---------+----------
petr | operator | tabulka | petr

rimmer1=> SELECT * FROM operator;
id | jmeno
----+-------
(0 řádek)

rimmer1=> SELECT * FROM public.operator;
id | jmeno
----+-------------
1 | ?
2 | Pevná linka
3 | O2
4 | T-Mobil
5 | Vodafone
6 | U:fon
(6 řádek)

Metapříkaz \dt zobrazuje jen tabulky, které můžete použít bez kvalifikace schématem. Tváří se, jako by (nová prázdná) tabulka operator existovala jen ve schématu petr.

Tabulka petr ale dál existuje ve schématu public. Všechny relace mezi touto tabulkou a ostatními DB objekty (tabulkami a sekvencemi) zůstávají nezměněné (nová tabulka petr nepřebírá žádné relace).

Schéma smažete příkazem DROP SCHEMA. Pokud mazané schéma obsahuje nějaké databázové objekty, musí se použít ještě CASCADE.

rimmer1=> DROP SCHEMA petr;
ERROR: cannot drop schema petr because other objects depend on it
DETAIL: table operator depends on schema petr
DOPORUČENÍ: Use DROP ... CASCADE to drop the dependent objects too.
rimmer1=> DROP SCHEMA petr CASCADE;
NOTICE: drop cascades to table operator
DROP SCHEMA

Informační schéma

Informační schéma (information_schema) je standardní schéma, které vám poskytuje metadata o databázi (metadata = data o datech). Protože se jedná o standard, měl by toto schéma implementovat každý DBMS (stejně).

O tom, že existuje, se můžete přesvědčit metapříkazem \dnS.

Tabulky v informačním schématu jsou obvykle implementovány jako pohledy (views). Můžete si znich přečíst (vyselectovat) různé informace o databázi.

Například, když se chcete podívat na to, jaké máte v databázi tabulky, můžete to udělat třeba takto:

rimmer1=> SELECT table_catalog, table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public' and table_name like 'zam%';
table_catalog | table_schema | table_name | table_type
---------------+--------------+-------------+------------
rimmer1 | public | zamestnanci | BASE TABLE
(1 řádka)

To je odpověď na to, jak obejít omezení metapříkazu \dt, který zobrazí z tabulek stejného jména jen tu z prvního schématu, kterou najde.

Popis všech tabulek z informačního schéma najdete v dokumentaci The Information Schema. Spousta metapříkazů nedělá nic jiného, než že se podívá do tabulek informačního schématu a zobrazí z nich požadované informace.

PG katalog schéma

Zatímco Informational schéma je standardní pohled na data, v systémovém katalogu (pg_catalog) jsou (meta)data skutečně uložena. Jedná se o regulérní tabulky, které můžete updatovat, ale většinou byste to neměli dělat. Tyto tabulky jsou updatovány automaticky, když je to potřeba (například CREATE TABLE automaticky vloží záznam do tabulky pg_tables).

Seznam tabulek a jejich popis najdete v dokumentaci System Catalogs.

Cluster (DBMS)

Cluster je to, co vám běží na počítači – serverová část postgresu. Ta se stará o všechny databáze, která máte na svém počítači. Pokud máte více počítačů, na kterých vám běží DBMS, dá se zařídit, aby spolu komunikovali. Jeden DBMS může být používán např. pro zálohu, tj. může udržovat kopii všech dat z primárního CLUSTERu. Konfigurace clusterů je už ale nad rámec tohoto kurzu.

MySQL/MariaDB

V MySQL nejsou schéma implementované. Databázové objekty patří konkrétní databázi. Fakticky se ovšem databáze chová spíš jako schéma – můžete například přistupovat k jiným tabulkám z jiné databáze, než ke které jste připojeni.

Schizofrenii MySQL dokládá i to, že CREATE DATABASE a CRAETE SCHEMA dělají to samé – vytvoří databázi.

V MySQL neexistuje nic jako search_path. Běžné schéma je to, ke kterému jste připojeni. Běžné schéma, tedy vlastně databázi, můžete změnit příkazem USE jmeno_database.

USE rimmer1;

Aktuální databázi můžete zjistit pomocí funkce DATABASE(), respektive SCHEMA() (oboje vrací název běžného schématu, tedy databáze :-).

V MySQL najdete databázi information_schema, jejíž význam je stejný jako v PostgreSQL. Dále databázi mysql, jejíž význam odpovídá (zhruba) významu schématu pg_catalog z Postgresu.

SQLite

SQLite nepodporuje schémata. Jeden soubor – jedna databáze a hotovo.

Můžete ale pomocí příkazu ATTACH DATABASE 'filename' AS dbname připojit další databázi (dalšímu souboru).
S tabulkami z takto připojené databáze pracujete pomocí kvalifkátoru dbname.

Běžné schéma je první otevřená databáze. Kvalifikátor pro běžné schéma je main.

$ sqlite3 rimmer1.db3
sqlite> CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> CREATE TABLE main.t2(t1_id INTEGER REFERENCES t1(id));
sqlite> insert into main.t1 values(1);
sqlite> insert into main.t1 values(2);
sqlite> insert into t2 values (1);
sqlite> ATTACH DATABASE 'rimmer2.db3' AS rimmer2;
sqlite> CREATE TABLE rimmer2.t2(t1_id integer references main.t1(id));
Error: near ".": syntax error
sqlite> CREATE TABLE rimmer2.t2 AS SELECT * FROM t2;
sqlite> SELECT * FROM rimmer2.t2;
t1_id
----------
1
sqlite> DROP TABLE rimmer2.t2;

Na příkladu můžete vidět, že není možné vytvořit reference mezi dvěmi databázemi. (Error se týká main.t1).

Oracle

V Oracle každé schéma vlastní jeden DB uživatel. Schéma nelze měnit, ani neexistuje žádná search_path proměnná. Jiná schémata můžete oslovovat (pouze) kvalifikovaně.

DBMS oracle spravuje jen jednu (nepojmenovanou) „databázi“, která obsahuje schémata všech uživatelů dané instance DBMS.

Schéma se vytvoří se založením uživatele a má stejné jméno jako uživatel. Toto schéma je pro uživatele běžné schéma, takže se objekty z něj nemusí kvalifikovat jménem schématu.

Oracle neimplementuje information_schema. Místo toho můžete najít metadata v tabulkách a pohledech (nejen) ze schématu SYS. Na tyto tabulky existují v každém schématu synonyma (podívejte se na Public Synonyms v Oracle SQL Developeru), takže je můžete používat bez kvalifikace.

SELECT * FROM user_tables;
SELECT * FROM SYS.user_tables;

Metadata jsou uložena v tzv. data dictionary view. Seznam všech těchto pohledů získáte následujícím příkazem:

oracle> SELECT * FROM dict;
TABLE_NAME COMMENTS
.................. ..............................................................
USER_CONS_COLUMNS Information about accessible columns in constraint definitions
USER_TABLES Description of the user's own relational tables
...