LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> 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 21370×

Komerční sdělení: Pořádáme Kurzy PgSQL

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

pridej.cz

 

DISKUZE

Nejsou žádné diskuzní příspěvky u dané položky.



Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

18.1.2017 0:49 /František Kučera
Členové a příznivci spolku OpenAlt se pravidelně schází v Praze a Brně. Fotky z pražských srazů za uplynulý rok si můžete prohlédnout na stránkách spolku. Příští sraz se koná už 19. ledna – tentokrát je tématem ergonomie ovládání počítače – tzn. klávesnice, myši a další zařízení. Také budete mít příležitost si prohlédnout pražský hackerspace Brmlab.
Přidat komentář

8.1.2017 17:51 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 19. ledna od 18:30 v pražském hackerspacu Brmlab. Tentokrát je tématem srazu ergonomie ovládání počítače – tzn. klávesnice, myši a další zařízení. K vidění bude mechanická klávesnice dasKeyboard, trackball Logitech nebo grafický tablet (a velký touchpad) Wacom. Přineste i vy ukázat svoje zajímavé klávesnice a další HW. V 18:20 je sraz před budovou, v 18:30 jdeme společně dovnitř, je tedy dobré přijít včas. Podle zájmu se později přesuneme do nějaké restaurace v okolí.
Přidat komentář

1.12.2016 22:13 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 8. prosince od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Sraz bude tentokrát tématický. Bude retro! K vidění budou přístroje jako Psion 5mx nebo Palm Z22. Ze svobodného hardwaru pak Openmoko nebo čtečka WikiReader. Přijďte se i vy pochlubit svými legendami, nebo alespoň na pivo. Moderní hardware má vstup samozřejmě také povolen.
Komentářů: 1

4.9.2016 20:13 /Pavel `Goldenfish' Kysilka
PR: Dne 22.9.2016 proběhne v Praze konference Cloud computing v praxi. Tématy bude např. nejnovější trendy v oblasti cloudu a cloudových řešení, provozování ERP v cloudu, o hostování různých typů softwaru, ale třeba i o zálohování dat nabízeném podnikům formou služby.
Přidat komentář

1.9.2016 11:27 /Honza Javorek
Česká konference o Pythonu, PyCon CZ, stále hledá přednášející skrz dobrovolné přihlášky. Máte-li zajímavé téma, neváhejte a zkuste jej přihlásit, uzávěrka je již 12. září. Konference letos přijímá i přednášky v češtině a nabízí pomoc s přípravou začínajícím speakerům. Řečníci mají navíc vstup zadarmo! Více na webu.
Přidat komentář

27.8.2016 8:55 /Delujek
Dnes po 4 letech komunitního vývoje vyšla diaspora 0.6.0.0
diaspora* je open-source, distribuovaná sociální síť s důrazem na soukromý
Více v oficiálním blog-postu
Přidat komentář

24.8.2016 6:44 /Ondřej Čečák
Poslední týden CFP LinuxDays 2016; pokud byste rádi přednášeli na LinuxDays 2016 8. a 9. října v Praze, můžete svůj příspěvek přihlásit, následovat bude veřejné hlasování.
Přidat komentář

9.8.2016 22:56 /Petr Ježek
Zařazení souborového systému reiser4 do jádra 4.7 znamená konečně konec patchování jádra jen kvůli možnosti použít reiser4.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

18.1.2017 20:18 / martin horky
Spolupraca linuxu a microsoftu

17.1.2017 9:57 / Pavel Hrubeš
Re: Externí USB televizní karta

4.1.2017 11:24 / Marcum
extension to house

3.1.2017 10:09 / bolden
country cottages

4.12.2016 22:54 / František Kučera
Dárek

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2017) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze