ARCHIV

PostgreSQL (6) - Uložení, aktualizace a mazání dat.

Kterak data na PostgreSQL server uložiti a kterak je udržeti aktuální.

11.3.2005 06:00 | MaReK Olšavský | Články autora | přečteno 25971×

V minulém díle jsem Vám přislíbil, že se "už" začneme zabývat tím, jak data na server uložit a jak je aktualizovat. Spolu s "dolováním" dat z databáze jsou to nejčastější činnosti, které budete na serveru dělat.

Pracovní databáze

V dalším průběhu seriálu bude rozumné pracovat nad databází. Postupně zadám některé tabulky, které budou malými fragmenty z webshop a redakčního systému. První dvě tabulky se budou týkat uživatele. Pokud si vzpomenete na druhý díl seriálu, kde bylo psáno o normalizaci, data, která jsou potenciálně redundantní je třeba rozdělit. U uživatele je můj pohled na problematiku takovýto - jeden uživatel/login může ve skutečnosti být jako cíl zásilky na několika jménech/adresách, například bude chtít jeden login, ale některé zásilky odesílat na svou soukromou adresu a některé na firmu.

Dvojice tabulek, kterou budu teď používat a se kterou vystačíme možná až do konce seriálu ;-) je uvedena pod tímto odstavcem. Sloupeček hash používám pro klíč vygenerovaný při přihlášení uživatele a předávaný přes url, mimo jiné je generovaný z IP adresy počítače a proxy, takže je minimální šance na jeho přenesení jinam a pokračování v práci, při odhlášení do hashe vkládám prázdný řetězec. Tabulky mají každá nastavený sloupeček id jako unikátní, tzn., že data v něm se nesmějí opakovat. Stejně je logické vytvoření unikátních klíčů pro sloupeček login (přeci jen 2 uživatelé se stejným loginem je nežádoucí stav). Důvod, proč nechávám na heslo "pouhých" 40 znaků je ten, že neukládám heslo v otevřené podobě, ale jako sha1 ze součtu řetězců loginu a hesla (v tomto tutoriálu budu vkládat heslo neencryptované).

-- vytvoreni tabulky
CREATE TABLE users (
    id bigserial NOT NULL,
    login character varying(128) DEFAULT ''::character varying NOT NULL,
    pwd character(40) DEFAULT ''::bpchar NOT NULL,
    visible boolean DEFAULT true NOT NULL,
    hash character(32)
);

-- nastaveni unikátnosti id
ALTER TABLE ONLY users
    ADD CONSTRAINT users_id_key UNIQUE (id);
-- nastaveni unikatniho uzivatele
ALTER TABLE ONLY users
    ADD CONSTRAINT users_login_key UNIQUE (login);

-- vytvoreni tabulky
CREATE TABLE userdetails (
    id bigserial NOT NULL,
    id_user bigint DEFAULT 0 NOT NULL,
    first_name character varying(100) DEFAULT ''::character varying,
    surename character varying(100) DEFAULT ''::character varying,
    firm character varying(100) DEFAULT ''::character varying,
    street character varying(100) DEFAULT ''::character varying,
    house_number character varying(10) DEFAULT ''::character varying,
    city character varying(100) DEFAULT ''::character varying,
    zipcode character varying(10) DEFAULT ''::character varying,
    email character varying(255) DEFAULT ''::character varying,
    state bigint DEFAULT 0,
    phone character varying(15) DEFAULT ''::character varying,
    fax character varying(15) DEFAULT ''::character varying
);

-- nastaveni unikatnosti id
ALTER TABLE ONLY userdetails
    ADD CONSTRAINT userdetails_pkey PRIMARY KEY (id);

Pro správu databáze je velmi užitečným nástrojem phpPgAdmin, který je založen na phpMyAdmin. Bohužel mám v současné době pocit, že phpMyAdmin je funkčně napřed a uživatelsky mnohem příjemější. Pokud nechcete pracovat přes WWW rozhraní, můžete použít PgAdminIII, který byl velice dlouho k mání přímo na stránkách PostgreSQL.org a je jejich oficiálním produktem.

Uložení dat

Pro uložení dat do již existující tabulky na PostgreSQL serveru slouží SQL funkce INSERT, kterou přidáme (obvykle) jeden řádek dat. Při zadávání dat můžete zvolit které hodnoty budete zadávat a pakliže nebude zadávat všechny hodnoty, budou ty nevyplněné naplněny buď hodnotou, kterou jste uvedli jako DEFAULT, nebo NULL, jestliže jste defaultní hodnotu nenastavili.

Syntaxe příkazu je INSERT INTO jmeno_tabulky[sloupecky] VALUES (hodnoty);, kde sloupecky jsou nepoviným parametrem, ale pakliže je neuvedete, musíte do hodnot vložit vše, co jste definovali při vytváření tabulky a to ještě v pořadí, jak jsou sloupečky za sebou, sloupečky můžete vynechávat pouze zleva, nebo můžete místo dat zadat jednu z hodnot NULL (má speciální pravidlo, protože určitě budete nutná data definovat jako DEFAULT hodnota NOT NULL) představující prázdnou hodnotu, nebo DEFAULT pro vložení hodnoty, kterou jste nastavili jako výchozí. Pokud zadáte jména sloupečnů, je třeba potom u vkládaných dat dodržet pořadí, v jaké byly sloupečky vyjmenovány. Pokud je některý ze sloupečků tabulky definován jako serial, nebo bigserial, můžete databázi vnutit číselnou hodnotu, třeba při importu dat ze zálohy, nebo nechat databázi automaticky iterovat hodnoty (lze dokonce nastavit pres sekvenci velikost te iterace).

Příkaz INSERT INTO users VALUES(0,'marek','marek',true,''); uloží uživatele marek s heslem marek, přístupný na řádek s id=0. Pokud se nyní pokusíte vložit INSERT INTO users VALUES(0,'petr','petr',true,'');, PgSQL řádek neuloží a obdržíte chybu, že se pokoušíte vložit data, která mají opakující se unikátní hodnotu. Můžete to napravit třeba tím, že změníte hodnotu ve sloupečku id na jinou (nebo napřed vymažete původní záznam), potom by kód mohl vypadat takto: INSERT INTO users VALUES(5,'petr','petr',true,'');.

Vkládání id ze scriptu je možné, nicméně aby to bylo možné automatizovat, je třeba získat poslední hodnotu (vybrat nejvyšší id) z tabulky, tu iterovat a teprve poté vložit řádek s novým id. Tomuto se lze vyhnout použitím DEFAULT hodnoty, v tomto případě lze napsat vložení řádku jako INSERT INTO users VALUES(DEFAULT,'petra','petra',true,'');. DEFAULT lze použít u všech sloupečků, kde byla při tvorbě zadána výchozí hodnota, nebyla-li tato zadána, vloží se NULL.

V případě MySQL (seriál připravuje Petr Zajíc) existuje možnost syntaxe INSERT INTO jmeno_tabulky SET sloupecek=hodnota[:, sloupecek=hodnota:];, která je podobná syntaxi příkazu UPDATE a je výborně použitelná pokud chce programátor ušetřit pár znaků (jednoduše zamění příkaz pro vkládání a obnovení hodnot). Tuto syntaxi PgSQL nezná.

Další možností je vložení dat z výběru, které si ukážeme v příštím pokračování u výběrů dat z databáze.

Aktualizace dat

Data v databázi by měly být obrazem aktuálních skutečností. K čemu by byla aplikace webového obchodu, kdyby nebylo možné měnit ceny produktů, nebo změnit adresu zákazníka, který se přestěhuje a musí si vytvořit nového uživatele.

K nastavení existujících dat slouží příkaz UPDATE, jehož syntaxí může programátor ovlivnit jeden, nebo více řádků tabulky. Jeho syntaxe je UPDATE jmeno_tabulky SET sloupecek=hodnota[:, sloupecek=hodnota:][ WHERE podminky];, kde klauzule WHERE je volitelná a udává ve kterých větách budou hodnoty aktualizovány, její neuvedení vede ke změně všech vět v databázi. Pokus o update věty která není v záznamu se obejde bez chyby a bez efektu.

Příkazem UPDATE users SET login='vladka', pwd='123', visible='true' WHERE login='marek'; změníme login, heslo a viditelnost u uživatele, který měl původně login marek. UPDATE users SET visible='false' WHERE id<3; zruší viditelnost u uživatelú s id<3. Logické by bylo zrušení viditelnosti u uživatelů, kteří se nepřihlásili déle než půl roku, například, a teprve po roce neaktivity je vymazat. Dává to možnost administrátorovi znovu oživit uživatele, kteří o to zažádají.

Vymazání dat

V praxi je třeba nejen data vkládat a upravovat jejich hodnoty, ale občas také mazat. Například, bude-li vyřazen výrobek z ceníku, je možné jeho parametr visible nastavit na false, aby nebyl zobrazován, ale v praxi je lepší jej smazat, zejména z prostorových důvodů, nebo z důvodů uvolnění loginu pro budoucí použití.

K vymazání věty z tabulky slouží příkaz DELETE FROM jmeno_tabulky [WHERE podminky];, kde WHERE opět určuje, které věty budou příkazem ovlivněny. Při neuvedení klauzule WHERE dojde k vyprázdnění tabulky.

Příkazem DELETE FROM users WHERE id=0 dojde k vymazání řádku s id=0, příkaz DELETE FROM users WHERE id<3 vymaže všechny záznamy z tabulky uživatelů, kde je id menší, než 3.

Poznámky

Pokud vymažete uživatele z tabulky users je třeba vymazat také uživatelské detaily z tabulky userdetails, které budou mít id_user stejné jako id mazaného uživatele. Lze to realizovat "pěšky", kdy napřed do pole uložíte všechna id vyhovující podmínce pro smazání, potom dalším krokem smažete podrobnosti z tabulky userdetails a nakonec vymažete všechny záznamy odpovídající podmínce. Je to nepohodlné a zde přijdou ke slovu triggery (spouště), které jsou při použití MySQL (dle jejich dokumentace) dostupná až od verze 5.0.2, a budou probrány v pozdějším díle.

Neuvedení klauzule WHERE, nebo její uvedení se vždy splněnou podmínkou (například 1=1) má stejný efekt.

Závěr

V příštím díle bude vybírání dat z tabulek a případně i propojení několika tabulek při výběru.

Během psaní seriálu byl ukončen vývoj PostgreSQL, která přinesla nějaké nové vlastnosti a již v této chvíli mohu předběžně přislíbit napsání článku o phpPgAdminu, který používám během psaní celého seriálu.

Verze pro tisk