ARCHIV

PostgreSQL (5) - Další datové typy a práce s časem i binarními řetězci

PostgreSQL má zvláštní datové typy, které se jinde moc nevyskytují a minule jsem slíbil, že doplním práci s některými typy.

16.12.2004 15:00 | MaReK Olšavský | Články autora | přečteno 24369×

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.

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.

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

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í.

Verze pro tisk