Vytvoření vlastních typů

Pro další práci, další krok v databázi, bude zapotřebí vědět jak se definují vlastní typy. Vytváření nového typu probíhá vždy nad aktuální databází a schématem (tj. nový typ vytvoříte globálně), pokud schéma neurčíte, je typ vytvořen nad aktuálním schématem (tj. většinou to, které je public). Ten, kdo schéma vytvořil je jeho vlastníkem. Zcela logickým omezením je, že nově vytvářený typ nesmí být pojmenován stejně, jako základní a již existující typy v MySQL. Jedním způsobem použití jsou složené typy, které jsou náplní tohoto článku, druhým užitím je definice "základních" typů, k těm je třeba dodefinovat další funkce (minimálně vstupní a výstupní). Proto se k funkci CREATE TYPE vrátíme v seriálu ještě jednou a to u uložených procedur, čiže prozatím jen zkráceně.

Syntaxe SQL příkazu je:

CREATE TYPE jmeno_typu AS (vycet promennych);

Dvěma omezeními pro nově vytvářené typy jsou, že název nově definovaného typu nesmí začínat podtržítkem (takovéto názvy jsou vyhrazeny pro vnitřní použití) a délka názvu nesmí přesáhnout 62 znaků (v dřívějších verzích PostgreSQL byla délka omezena na 31 znaků).

Dalším místem, kde může pomoci možnost nadefinování si vlastních typů je vytváření polí s proměnnou délkou a v dřívějších verzích i rozsáhlých objektů, pro něž bylo omezením PostgreSQL  (kdy jsou již v PgSQL připraveny a zaregistrovány potřebné pomocné procedury). K oběma těmto možnostem vytvoření vlastních typů se seriál vrátí, až bude u uložených procedur.

Ke zrušení již definovaného typu slouží SQL příkaz DROP TYPE jmeno_typu [, ...] [CASCADE | RESTRICT], kdy je možné současně smazat několik uživatelsky definovaných typů. Pomocí direktivy CASCADE se povolí smazání všech objektů, které na vytvořeném typu závisí (sloupce tabulky, operátory, funkce), RESTRICT toto mazání zakáže.

Pokud je zapotřebí změnit definici typu, je možné použít příkaz ALTER TYPE jmeno_typu. Zatím je možné pouze měnit vlastníka definovaného typu, pomocí ALTER TYPE jmeno_typu OWNER TO novy_vlastnik a převést jej do jiného schématu v rámci jedné uživatelské databáze pomocí ALTER TYPE jmeno_typu SET SCHEMA nove_schema (tato druhá možnost je k dispozici až od PgSQL řady 8.1. Změna definice typu, jako takového zatím není zahrnuta a nezbývá nic jiného, než jej smazat a vytvořit znovu.

Složené datové typy

Programátoři v jazyce C/C++ znají možnost vytvoření vlastního strukturovaného typu, který pak navenek definují jako proměnnou tohoto typu, ale uvniř je několik dalších proměnných, ke kterým se přistupuje prostřednictvím operátoru tečky (.), případně pomocí operátoru šipky (->). Jazyk Pascal zná stejnou strukturu, jen v tomto případě se tento typ jmenuje záznam a pro jeho vytvoření se používá klíčové slovo record.

Ukázka a použití v jazyce C:

#include <iostream.h>
typedef struct{
 unsigned long int osobni_cislo;
 char rod_cislo[10];
 char jmeno[30];
 char prijmeni[35];
 char rod_prijmeni[35];
 char ulice[40];
 char cislodomu[8];
 char obec[50];
 char psc[5]; 
} PERSON;
PERSON zam;
int main(void){
 zam.osobni_cislo = 12;
 cout << zam.osobni_cislo << endl;
 return(0);
}

Použití v jazyce Pascal:

type
 person = record
 osobni_cislo : longint;
 rod_cislo : string[10];
 jmeno : string[30];
 prijmeni : string[35];
 rod_prijmeni : string[35];
 ulice : string[40];
 obec : string[50];
 psc : string[5];
 end;
var
 zam : person;
begin
 zam.rod_cislo := '7506092555';
 writeln(zam.rod_cislo);
end.

Podobné struktury/vlastní datové typy podporuje i databázový server PostgreSQL. Použití snad vyplyne v dalším textu, byť při použití takovýchto struktur se mohou vynořit nemalé problémy s indexací (zatím je nelze jednoduše vytvořit) a efektivita může být horší, než při rozdělení dat do dvou tabulek a jejich provázání referencí. 

CREATE TYPE person AS (
 osobni_cislo BIGINT,
 rod_cislo CHAR(10),
 jmeno VARCHAR(30),
 prijmeni VARCHAR(35),
 rod_prijmeni VARCHAR(35),
 ulice VARCHAR(40),
 cislodomu VARCHAR(8),
 obec VARCHAR(50),
 psc CHAR(5)
);

Použití již definovaného typu při definici tabulky je velmi jednoduché. Pouze se určí název pole v tabulce a nově definovaný typ. Nový typ nelze, v návaznosti na minulé dva díly, použít jako "základ" pro pole, alespoň ne elementárním způsobem, ale z toho co již bylo napsáno je jasné, proč by nebylo rozumné použít takovou konstrukci (rychlost je tím hlavním zádrhelem). Například takto:

CREATE TABLE employees (
 employees bigserial NOT NULL,
 emp_data person
);

Při vkládání lze použít dvě syntaxe, u obou z nich musíme vyjmenovat všechny hodnoty vkládané do, v tomto případě druhé, položky tabulky, která je uživatelsky definovaného typu. Lze to udělat buď pomocí  jejich prostého vypsání, do kulatých závorek, nebo použitím operátoru/konstruktoru ROW.

INSERT INTO "employees" VALUES (nextval('public.employees_employees_seq'::text),
 (12,'7506032559','Petr','Novák','Jankovský','Bílinská cesta',
 '14/596','Ústí nad Labem','40001'));
INSERT INTO "employees" VALUES (nextval('public.employees_employees_seq'::text),
 (ROW(26,'7552032542','Petra','Zedníková','Malá','Zlatá Ulice',
 '55','Teplice','41501')));

Výběr hodnot je jednoduchý. Pokud je zapotřebí vybrat vše stačí zadat SELECT * FROM employees, potud je vše jednoduché a jasné. Pokud je zapotřebí vybrat jen některé položky ze strukturovaného typu, je nutné použít notaci přes tečku a to se dá provést několika způsoby (z nichž poslední je i s vyjmenováním tabulky, což je vhodné při spojování několika tabulek pomocí JOINů).

SELECT (emp_data).rod_cislo, (emp_data).rod_prijmeni FROM employees;

je prvním z nich, pokud zapíšete název databázového sloupečku bez závorek, nebude Vám tento postup fungovat protože bezzávorkový zápis odpovídá zdrojové tabulce a ta v tento moment není definována. Když je zapotřebí dodat do výběru i název tabulky, opět se tento zapisuje do závorek, společně s názvem databázového sloupce, který požadujete:

SELECT (employees.emp_data).rod_cislo,
 (employees.emp_data).rod_prijmeni FROM employees;

Pro UPDATE hodnot se používá stejný zápis, jako pro jejich výběr, ale s jedním drobným rozdílem, v části SET se závorky kolem názvu databázového sloupečku nepíší, v části WHERE jsou vyžadovány. Při update celého řádku poslouží opět operátor ROW:

UPDATE employees SET emp_data.prijmeni='Pekarkova' WHERE employees=2;
UPDATE employees SET emp_data=ROW(12,'7506032559','Petr',
 'Janák','Vopěnka','Modrá',
 '255','Praha','10000') WHERE employees = 2;

Závěr

Definování vlastních typů, speciálně těch složených, bude mít pro spoustu programátorů jisté kouzlo, nicméně nejsem jediný, kdo je proti jejich ukládání v takovéto podobě do databáze. Spíše než ušetření práce, nebo jiných výhod se můžete dočkat velice nepříjemného překvapení, ať už v podobě nižšího výkonu databáze, nebo pozdějších problémů s úpravami databázové struktury. Já osobně se takovýmto uloženým strukturám vyhýbám, protože mám nepoměrně lepší zkušenosti s rozseparováním dat do různých tabulek a jejich vzájemným propojováním.