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

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.6.2018 0:43 /František Kučera
Červnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 21. 6. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát na téma: F-Droid, aneb svobodný software do vašeho mobilu. Kromě toho budou k vidění i vývojové desky HiFive1 se svobodným/otevřeným čipem RISC-V.
Přidat komentář

23.5.2018 20:55 /Ondřej Čečák
Od pátku 25.5. proběhne na Fakultě informačních technologií ČVUT v Praze openSUSE Conference. Můžete se těšit na spostu zajímavých přednášek, workshopů a také na Release Party nového openSUSE leap 15.0. V na stejném místě proběhne v sobotu 26.5. i seminář o bezpečnosti CryptoFest.
Přidat komentář

20.5.2018 17:45 /Redakce Linuxsoft.cz
Ve čtvrtek 31. května 2018 připravuje webový magazín BusinessIT ve spolupráci s Best Online Média s.r.o. pátý ročník odborné konference Firemní informační systémy 2018. Akce proběhne v kongresovém centru Vavruška (palác Charitas), Karlovo náměstí 5, Praha 2 (u metra Karlovo náměstí) od 9:00 hod. dopoledne do cca 15 hod. odpoledne. Konference je zaměřena na efektivní využití firemních informačních systémů a na to, jak plně využít jejich potenciál. Podrobnější informace na webových stránkách konfrence.
Přidat komentář

14.5.2018 7:28 /František Kučera
Květnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 17. 5. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát na téma: Audio – zvuk v GNU/Linuxu.
Přidat komentář

7.5.2018 16:20 /František Kučera
Na stránkách spolku OpenAlt vyšla fotoreportáž Pražské srazy 2017 dokumentující srazy za uplynulý rok. Květnový pražský sraz na téma audio se bude konat 17. 5. 2018 (místo a čas ještě upřesníme).
Přidat komentář

17.4.2018 0:46 /František Kučera
Dubnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 4. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tématem tohoto srazu bude OpenStreetMap (OSM) aneb svobodné mapy.
Přidat komentář

16.3.2018 22:01 /František Kučera
Kulatý OpenAlt sraz v Praze oslavíme klasicky: u limonády a piva! Přijďte si posedět, dát si dobré jídlo a vybrat z mnoha piv do restaurace Kulový blesk, který najdete v centru Prahy nedaleko metra I. P. Pavlova na adrese Sokolská 13, Praha 2. Sraz se koná ve čtvrtek 22. března a začínáme v 18:00. Heslo: OpenAlt. Vezměte s sebou svoje hračky! Uvítáme, když si s sebou na sraz vezmete svoje oblíbené hračky. Jestli máte nějaký drobný projekt postavený na Arduinu, nějakou zajímavou elektronickou součástku, či třeba i pěkný úlovek z crowdfundingové akce, neváhejte. Oslníte ostatní a o zábavu bude postaráno.
Přidat komentář

13.2.2018 0:41 /František Kučera
Únorový pražský sraz OpenAltu se koná 15. 2. 2018 a tentokrát se vydáme na návštěvu do jednoho pražského datacentra. Sejdeme se v 17:50 v severovýchodní části nástupiště tramvajové zastávky Koh-I-Noor. Po exkurzi se přesuneme do restaurace U Pštrosa (Moskevská 49), kde probereme tradiční témata (svobodný software a hardware, DIY, CNC, SDR, 3D tisk…) a tentokrát bude k vidění i IoT brána od The Things Network.
Přidat komentář

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

> Poslední diskuze

20.2.2018 18:48 / Ivan Majer
portal

20.2.2018 15:57 / Jan Havel
Jak využíváte služby cloudu v podnikání?

16.1.2018 1:08 / Ivan Pittner
verejna ip od o2 ubuntu

15.1.2018 17:26 / Mira Harvalik
Re: Jak udělat HTML/Javascript swiping gallery do mobilu?

30.12.2017 20:16 / Michal Knoll
odmocnina

Více ...

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