V minulém díle jsem psal o základních datových typech, které nám PgSQL poskytuje a které jsou běžně využívány. Existují ale i další typy, zejména pak ty geometrické, které nám poskytuje a jejichž použití není zcela běžné i když si jej dokáži představit v aplikacích typu GIS, nebo matematicko-fyzikálních (to jsem kdysi dokonce studoval). Dále napíšu některá specifika při práci s typy pro datum/čas a binární řetězce. Pseudotypy (jak je nazývá originální dokumentace) a pole si necháme na později, protože pseudotypy nefungují jako typy pro sloupce databáze, ale jako návratové hodnoty uložených procedur. Pole se mi do tohoto dílu pravděpodobně nevejdou.

Proměnné 2.

Geometrické typy

Geometrické typy reprezentují základní objekty dvourozměrné (2D) objekty. Pokud budete ptřebovat pracovat ve třech, čtyřech, nebo více rozměrech (proč ne, v teoretické fyzice se tomu tak občas děje) budete si muset vytvořit vlastní typy sami. K tomuto slouží příkaz CREATE TYPE, ke kterému se dostaneme v příštím díle.

Jméno	Velikost	Zápis	popis
point	16 bytů	(x,y)	bod v ploše
line	32 bytů	((x1,y1),(x2,y2))	přímka (její vektor)
lseg	32 bytů	((x1,y1),(x2,y2))	úsečka (část přímky s konečným rozměrem)
box	32 bytů	((x1,y1),(x2,y2))	pravoúhlý rovnoměžník (česky odbelník)
path	16 + n*16 bytů	((x1,y1),...)	uzavřená "klikatá" čára, je ekvivalentní polygonu
path	16 + n*16 bytů	[(x1,y1),...]	otevřená "klikatá" čára
polygon	40 + n*16 bytů	((x1,y1),...)	uzavřená "klikatá" čára, je ekvivalentní path
circle	24 bytů	<(x,y),r>	kružnice (střed a poloměr)

Proměnná n znamená počet uzlů/lomů v polygonu, nebo čáře.

Využití těchto typů proměnných/dat může být třeba v plošné geometrii a uložení dat z CAD aplikace. Technické výkresy přeci jen nejsou prostorové a jedná se o průměty do několika (1-3) ploch, podle potřeby.

Jistým omezením těchto typů je, že souřasnice jednotlivých typů mohou být jenom celá čísla, což pro některé aplikace může být omezující, napadá mě, kdybyste potřebovali ukládat geometrickou reprezentaci komplexních čísel, tj. souřadnice x by představovala reálnou část čísla a souřadnice y komplexní. Na problém byste narazili v momentě, kdybyste potřebovali uložit třeba Pi, tj. souřadnice (3.1415,0). To je situace, kdy si budete muset vytvorit vlastni typy, ale o tom opravdu az priste.

Bod (point) je základní geometrický prvek, používá se v syntaxi (x,y), nebo x,y.

Přímka (line) a úsečka (lseg) se liší geomtericky tím, že úsečka má omezenou délku jen mezi body (x1,y1) a (x2,y2), kdežto u přímky tyto dva body udávají směrový vektor a její umístění v ploše. Zápis je možný v několika tvarech ((x1,y1),(x2,y2)) nebo (x1,y1),(x2,y2) a nebo x1, y1, x2, y2. Který z nich si vyberete záleží jen na Vás, ale pokud se jednoho "chytnete" používejte jen ten a nemixujte to dohromady (to platí pro celý vývoj, pakliže jednou začnete používat jakoukoliv konvenci, držte se jí).

Obdelník (box), neboli pravoúhlý rovnoběžník (častěji se v angličtině ale setkáte se slovem rectanle) , jak jsem napsal trochu výše v prostém výčtu typů. Jeho zvláštním případem 2je čtverec, tj. obdelník, který má všechny strany stejně dlouhé. Zápis pro uložení je stejný jako u přímky a úsečky ve tvarech ((x1,y1),(x2,y2)) nebo (x1,y1),(x2,y2) a nebo x1, y1, x2, y2. Pokud si databázi řeknete o data tohoto typu (příkazem SELECT), vrátí Vám je v prvním tvaru.

Cesta (path), jak jsem22 napsal nepěkněji nahoře klikatá čára, složená z navazujících úseček, kde n-tý úsek naleznete mezi souřadnicemi (x_n,y_n) a (x_[n+1],y_[n+1]). V případě, že se jedná o uzavřenou cestu je poslední bod připojen k prvnímu. S římto typem jsou svázány zvláštní funkce PostgreSQL, popen(polygon) a pclose(polygon) k otevření a uzavření cesty "hrubou silou", pokud si nejsme jisti, zdali je cesta otevřená, nebo uzavřená můžeme použít funkce isopen(polygon) a isclosed(polygon). Zápis dat u tohoto typu je možný několika způsoby. Jsou to ((x1,y1), ... ,(xn,yn)) nebo [(x1,y1), ... ,(xn,yn)], (x1,y1), ... ,(xn,yn), (x1,y1, ... ,xn,yn) a nakonec x1,y1, ... ,xn,yn. Pokud data tohoto typu vybíráte z databáze, jsou Vám vráceny v prvním zde uvedeném tvaru.

Mnohoúhelník (polygon) je cokoliv různě pravidelného (šestiúhelník, 8úhelník), či nepravidelného, co má strany složené z úseček a uzavřený obvod. Správná syntaxe zápisu je ((x1,y1), ... ,(xn,yn)), nebo (x1,y1), ... ,(xn,yn), ale také (x1,y1, ... ,xn,yn) a nakonec x1,y1, ... ,xn,yn. Jedná se vlastně o uzavřený ekvivalent path. Pokud data vybíráte, vrací se vám v prvním tvaru.

Kružnice (circle), geometrický útvar mající střed a poloměr (nebo průměr), neboli jak zní přesná definice, jedná se o množinu bodů v ploše, které mají stejnou vzdálenost od středu. Zápis při ukládání je možná jedním z následujících způsobů: <(x,y),r>, ((x,y),r), (x,y),r a x,y,r, kde x,y jsou souřadnice středu a r je poloměr. Pokud jste se někdy zabývali matematikou, tak víte, že pro určení místa a poloměru kružnice vám stačí 3 body a zbytek si dopočítáte. Není třeba snad zase opakovat, že data se vrací v prvním tvaru.

Jak jsem někde v předchozích odstavcích psal, přijměte za svůj jeden způsob zápisu, nejlépe takový, kde na první pohle poznáte typ proměnné (zda-li je to path, nebo polygon či čtverec) a toho se držte. Opakuji to zejména pro začínající programátory, protože ti mají ve zdrojácích největší chaos a změny v zápise.

Síťové adresy (IPv(4/6) a MAC

PostgreSQL nám poskytuje speciální typy i pro síťové adresy IPv4, IPv6 (od verze PostgreSQL 7.4) a mac (hardwarová adresa, která by měla být unikátní pro každý prvek v síti). Jako příklad použití mne napadá logování adresy uživatelských přístupů z aplikace (třeba webové). Je lepší použít tento typ, než patřičně dlouhý text, protože je zde kontrola a speciální operátory a funkce.

Jméno	Velikost	popis
cidr	12, nebo 24 bytů	adresa sítě typu IPv4 nebo IPv6
inet	12, nebo 24 bytů	adresa sítě, nebo zařízení typu IPv4 nebo IPv6
macaddr	6 bytů	HW (mac) adresa zařízení

Z praxe při použití a podle definice vyplývá, že pro uložení IPv4 bude použito 12 bytů a pro IPv6 24 bitů. IPv4 je adresa tak, jak ji známe všichni, tj. 4 čísla v rozsahu 0 až 255, ale protože tento prostor je již dnes zcela vyčerpán a dochází tak i k nemilým věcem, jako je sdílení adresy (uživatelé DirectConnectu vědí, proč je to nemilé), byla vytvořena norma IPv6, která je, na rozdíl od původní 32 bitové adresy, 128 bitová a v dohledné době pravděpodobně nedojde k vyčerpání tohoto adresního prostoru a až ano, tak jsem zvědav na další normu, kolika bitová že bude. Pokud se ptáte, proč pro uložení 32 bitového čísla je třeba 96 bitů a pro uložení 128 bitového 192 bitů, tak vězte, že je to kvůli masce sítě.

Typ cidr kromě adresy uchovává také sítovou masku, to znamená, že kromě adresy je uchovávána také síťová maska. Zápis je ve tvaru addr/netmask, kde adresa je ve tvaru addr.addr.addr.addr a netmask je počet bitů síťové masky. Pokud netmask nezadáte, je dopočítán na základě předchozích vstupů a to vždy tak, aby byl větší, než poslední zadaná a umožnil tak vykrytí všeho.

Typ inet Se chová velmi podobně, jako cidr, ale nemá povinnou síťovou masku. Můžete jí zadat a ona nebude kontrolována, protože cidr Vám nepřijme adresu s bitovou maskou, která nemá jedničky jen vlevo.

Adresa macaddr je na počítači za normálních okolností nenastavitelná, je v podstatě totéž jako jedinečný podpis síťové karty. Samozřejmě, že už jsem viděl i prográmky, které jí uměly měnit a ten nejlepší byl ruské provinience (světe div se), ale jméno už si bohužel nepamatuji. Možná nepotěším ty, kteří by chtěli omezovat přístup v PHP pomocí Mac adresy, protože jsem dodnes nenašel funkci, která by vám jí uměla říci přímo, ale našel jsem na internetu (sláva Googlu) třídu, která by to umět měla, bohužel jsem jí zatím netestoval, protože server, kde měla být neběžel.

Zápis mac adresy je ve tvaru xxxxxx:xxxxxx, xxxxxx-xxxxxx, xxxx.xxxx.xxxx, xx-xx-xx-xx-xx-xx a xx:xx:xx:xx:xx:xx, kde x je šestnáctková číslice v rozsahu Ox0-0xf. Protože opakování je matka moudrosti (?zjistil už někdo, kdo je otec?), tak nezapomeňte si zvyknout na jeden zápis a ten podle možností neměnit. V hexadecimálních číslech jsou akceptovány malá i velká písmena, při získávání dat z databáze je dostaneme vždy v posledním tvaru.

Síťovou adresu zapisujeme při vkládání jak čteřici 8 bitových oddělených tečkami, případně jako osmici 16 bitových čísel oddělených dvojtečkami. Za lomítko můžeme napsat netmask a před adresu, můžeme vložit ::ffff:: pro uložení IPv4 adresy do pole pro IPv6 adresu. Při "dolování" adresy z databáze jsou IPv4 řazeny vždy před IPv6.

Doplňky k minulému dílu

Uložení řetězců a binárních dat

Char stejně tak jako name je uložen s pevnou délkou řetězce, to znamená, že jsou v paměti/na disku při uložení zprava doplněny nulami. Řetězce s proměnnou délkou jsou uloženy tak, že se hned za ně dá null (OxO) a obsazují tedy ve skutečnosti svou délku + 1 byte. V tomto se skrývá drobné riziko, nemůžete uložit řrtězec s kódem \u0000. Pokud se týče binárních typů, jako je bytea (pro "nekonečné" řetězce) a blob je na začátku uložená délka uložených dat. Protože kontroly toho, zdali je další znak \u0000, nebo napřed načtení délky a pak patřičného množství dat, mají určitou režii, je rychlejší používat řetězce s pevnou délkou, jak jsem psal minule.

Specifika bytea

Protože občas můžeme potřebovat i řetězce obsahující ne zrovna košer znaky, lze použít tento speciální typ. Při ukládání si znaky escapnete (pomocí \), nebo převede na ASCII kód a pak uložíte pomocí sekvence \\kód, skrývá se zde jedna menší záludnost, číslo je v jeho osmičkové reprezentaci. Například chcete-li uložit znak apostrofu s kódem 39, tak můžete vložit '\\047', nebo '\'' a při vybírání dat dostanete zpět jen apostrof, tj. '. Pokud potřebujete vložit zpětné lomítko a nepíšete jej kódem, musíte jej napsat dokonce 4x, tj. \\\\. Důvodem je dvoukrokové parsování vstupu, po prvním průchodu zbydou dva (v případě psaní kódem ten jede escapovací) a po druhém průchodu je to správně uložen.

Možná jsem to minule moc nezdůraznil, ale blob a binary large object Vám poskytnou stejné služby, s trochu jinou obsluhou (nemusíte šíleně escapovat).

Práce s datumem a časem

Při práci s datumem a časem máte implicitně nastavenu konvenci americkou, tj. rok-měsíc-den (také označeno jako iso-8601), ale máte možnost přepnout pro tabulku/databázi do konvence evropské či jiné pomocí SET DATESTYLE TO 'EUROPEAN';, nebo jiné SET DATESTYLE TO 'NONEUROPEAN';. Možné formáty jsou ISO, POSTGRES, SQL, US, NONEUROPEAN, EUROPEAN, GERMAN. Doporučuji čtenářům, ať si v našich podmínkách nastaví styl na EUROPEAN a pokud se budou zajímat o to jak vypadá datum a čas v ostatních formátech, nechť jej přepínají a příkazem SELECT si vybírají patřičná data. Parametry lze rozumným způsobem kombinovat, kde rozumným myslím, že první je jeden z ISO, POSTGRES, SQL a druhý se týká země. Například nastavení SET DATESTYLE TO 'SQL, EUROPEAN'; Vám bude poskytovat výstup ve tvaru 24/12/2004 a nastavení SET DATESTYLE TO 'POSTGRES, EUROPEAN' výstup ve tvaru 24-12-2004.

Vstup může být teoreticky ve všech možných formátech, které PgSQL umí (December 24, 2004; 2004-12-24 (preferovaný formát), 24/12/2004, 24-12-2004, ...), pouze v momentech, kdy by se jednalo o nejednoznačný datum, například 1. února 2004 je datum přijat v preferovaném formátu, tj. při nastavení na EUROPEAN 01-02-2004. Data lze zadavat i zpusobem rrrr.ddd, neboli rok a den v roce, bez rozdělovníků a i jako den od počátku Juliánského kalendáře (J235567). Pro všechny možné způsoby zápisu si dovolím Vás odkázat na originální dokumentaci.

Při zadávání času můžeme rozlišit i časovou zónu. Obvyklý způsob zadání je hh:mm:ss, kdy sekundy můžeme zadat i jako desetinné číslo, tj. například 12:00:01.150, odzadu bůžeme po řadě odebrat tisíciny sekundy a sekundy. Preferovaný formát času je 24 hodinový, ale můžeme pomocí přípon vložit i čas rozlišený pomocí přípon AM a PM. Při použití tohoto zápisu bychom 16 hodin našeho času vložili jako 4:00 PM. Časovou zónu určíme číslem s unárním operátorem, například 15:42:15+2, což by odpovídalo našemu letnímu času.

Vložení kompletního časového razítka (timestamp) znamená vložit současně datum i čas, třeba pomocí '2004-12-24 19:00:00+1', což je 24. prosince roku 2004, 19 hodin našeho zimního času.

Interval (časový posun) se vkládá jako časový posun, volitelně s direktivou ago, například 1 12:13:14 znamená interval 1 den, 12 hodin, 13 minut a 14 sekund.

Výstup z databáze se vždy řídí podle nastavení SET DATASTYLE, přičemž pro vstup i výstup bude v Českých a Slovenských luzích, hájích a horách nejbližší nastavení GERMAN, kdy timestamp bude vypadat následujícím způsobem - 24.12.2004 19:00:00+1.

Při ukládání datumu a času do databáze můžeme použít několika funkcí, které jsou v níže uvedené tabulce. Jsou to funkce, které můžeme nahradit i přímým vstupem ze strany klienta, ale většina z nich bude nepoměrně rychlejší, když je necháte vykonat na straně serveru, věřte tomu, nebo ne. Troufám si tvrdit, že ze zde vypsaných funkcí (vstupů) je nejpoužívanější now

vstupn	význam
epoch	1970-01-01 00:00:00+0 (pro neznalé, je to počátek unixového času)
infinity	dále, než všechny timestampy (není jen pro datum)
-infinity	dříve, než všechny timestampy (není jen pro datumy)
yesterday	včerejší půlnoc
today	dnešní půlnoc
now	aktuální datum a čas
tomorrow	zítřejší půlnoc
zulu, allbands, z	pouze čas, půlnoc v GMT

Závěrem

Dnes jsme dokončili základy typů použitelných v PgSQL, ale ještě stále zbývá místo pro jejich další rozšíření. Zájemci se mohou podívat do originální dokumentace a ještě se k typům vrátíme, až budeme dělat procedury uložené na straně serveru (stored procedures), prodože některé pseudotypy s tímto přímo souvisí. Příště se už podíváme na základní SQL dotazy pro ukládání dat, jejich získávání, podle místa i aktualizace a mazání.