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

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ů

12.11.2018 21:28 /Redakce Linuxsoft.cz
22. listopadu 2018 se koná v Praze na Karlově náměstí již pátý ročník konference s tématem Datová centra pro business, která nabídne odpovědi na aktuální a často řešené otázky: Jaké jsou aktuální trendy v oblasti datových center a jak je optimálně využít pro vlastní prospěch? Jak si zajistit odpovídající služby datových center? Podle jakých kritérií vybírat dodavatele služeb? Jak volit vhodné součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně datové centrum spravovat? Jak co nejlépe eliminovat možná rizika? apod. Příznivci LinuxSoftu mohou při registraci uplatnit kód LIN350, který jim přinese zvýhodněné vstupné s 50% slevou.
Přidat komentář

6.11.2018 2:04 /František Kučera
Říjnový pražský sraz spolku OpenAlt se koná v listopadu – již tento čtvrtek – 8. 11. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma umění a technologie, IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

4.10.2018 21:30 /Ondřej Čečák
LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář

18.9.2018 23:30 /František Kučera
Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář

9.9.2018 14:15 /Redakce Linuxsoft.cz
20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business. Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář

12.8.2018 16:58 /František Kučera
Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář

16.7.2018 1:05 /František Kučera
Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář

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ář

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

> Poslední diskuze

5.10.2018 17:12 / Jakub Kuljovsky
Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?

20.9.2018 10:04 / Jan Ober
Jaký kurz a software by jste doporučili pro začínajcího kodéra?

20.9.2018 10:00 / Jan Ober
Re: Gimp

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

Více ...

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