Archiv pro měsíc: Duben 2004

Kompilace

Soubor postgresql-7.4beta2.tar.bz2 rozbalíme, vlezeme do něj a provedeme magickou trojkombinaci…

tar -xjf postgresql-7.4beta2.tar.bz2
cd postgresql-7.4beta2
./configure --with-openssl --prefix=/usr
make
make install
Místo make install pouštím checkinstall, kterým si dělám balíčky.

Je potřeba udělat uživatele pod kterým se bude databáze spouštět:

groupadd pgsql
useradd -g pgsql -d /home/pgsql_data/ pgsql
mkdir /home/pgsql_data/

chown pgsql:pgsql /home/pgsql_data/
chmod 0700 /home/pgsql_data/

Spuštění

Dalím krokem je inicializace prostoru pro databáze(provedeme jako user pgsql):

initdb /home/pgsql_data/

vytvoření naší pokusné databáze:


createdb test

v souboru /home/pgsql_data/pg_hba.conf si změnte/přidejte nastavení pro ověřování. Soubor /home/pgsql_data/postgresql.conf obsahuje nastaveni databáze.
Pro spouštění databáze jsem si udělal rc.pgsql skript. Nyní můžeme spustit databázi příkazem /etc/rc.d/rc.pgsql start

Tahák na SQL

PostgreSQL ma program psql, který nám umožní ovládání databáze pomocí příkazového řádku. Také můžete použít program pgadmin3, je docela pěkný, grafický a klikací…

Uživatelé

Vytvoření uživatele:
CREATE USER username;

Smazání uživatele:

DROP USER username;

Vlastnosti uživatele:

• superuser
• Má práva na všechno, pouze on může vytvářet nové uživatele.
  Pro vytvoření superuživatele použij: CREATE USER username CREATEUSER
• zakládání databáze
• Má právo na zakládání nových databází
  CREATE USER username CREATEDB
• heslo
• Heslo do databáze je oddělené od systému, takže se musí natsavit zvlášť. Nastavení hesla:CREATE USER username PASSWORD 'heslo'

Parametry zadávané při použití příkazu CREATE se dají upravit příkazem ALTER, takže například změna hesla se provede takto:
ALTER USER username PASSWORD 'noveheslo';

Skupiny

Přidání skupiny:
CREATE GROUP groupname;

Přidání uživatele do skupiny:
ALTER GROUP groupname ADD USER username1, ...

Odstranění uživatele ze skupiny:
ALTER GROUP groupname DROP USER username1, ...


Práva

Práva, která je možné nastavit:
SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE a ALL PRIVILEGES

Pro nastavení práv se používá příkaz GRANT:
GRANT INSERT ON tabulka TO username;
Uživatel provádějící tento příkaz musí být majitelem tabulky.

Přiřazení práv skupině:
GRANT SELECT ON tabulka TO GROUP groupname;

Přiřazení práv speciálnímu uživatelskému jménu PUBLIC přiřadíme práva všem uživatelům databáze.

To revoke a privilege, use the fittingly named REVOKE command:
REVOKE ALL ON accounts FROM PUBLIC;

Vytvoření databáze

CREATE DATABASE databasename;
případně
CREATE DATABASE databasename OWNER username;
nebo z prostředí shellu:

createdb -O username databasename

Výroba tabulek

CREATE TABLE (nazev_pole typ_pole [NULL|NOT NULL][UNIQUE|PRIMARY KEY][REFERENCES tabulka][DEFAULT hodnota][,...])

• NULL – určuje, zda může byt pole prázdné
• UNIQUE – hodnota se v tabulce může vyskytovat pouze jednou
• PRIMARY KEY – primární klíč, automaticky je UNIQUE
• REFERENCES – použijeme pokud je sloupec tabulky zároveň cizím klíčem (vytvoříme referenční integritu)
• DEFAULT – pokud není při zadávání do tabulky pole známo, použije se tato hodnota

Vkládání dat

INSERT INTO tabulka [(pole[,...])] VALUES (hodnota[,...])

Při vkládání lze definovat pořadí polí. Pokud vynecháme definici polí vložíme data v pořadí v jakém jsme vytvořili tabulku.

Výběr dat

SELECT pole [AS nazev][,...] FROM relace zkratka [,...] [WHERE podminka] [ORDER BY poradi]

• pole – Seznam sloupců, které chceme při výběru dat zobrazit oddělený čáarkami.
• AS nazev – pokud chceme aby byl sloupec uvozen jiným nadpisem než je název pole, uvede me ho jako nazev
• Když chceme vybrat všechny pole, můžeme použít znak *
• relace – Název pole můžeme uvodit jménem relace (resp. její zkratkou), abychom jednoznačně identifikovali dané pole v případě stejných názvů polí ve dvou různých tabulkách

Definování podmínek – WHERE

logické operátory: =, <, >
logické spojky: AND, OR, NOT
porovnání řetězců: znak % zastupuje libovolný počet libovolných znaků

Řazení – ORDER BY

Výsledek dotazu můžeme seřadit podle libovolného výrazu. Nejčastěji řadíme záznamy podle jednoho ze sloupců, ale teoreticky můžeme řadit podle libovolného výrazu využívající jednotlivých polí záznamu.

Sloučení tabulek – JOIN

Často potřebujeme spojit několik tabulek dohromady v jednom dotazu. V našem případě chceme spojit tabulky Ucitel a Predmet pomocí pole ucitel v tabulce Predmet. Máme na výběr dvě možnosti. Buď použijeme úplný kartézský součin těchto dvou relací a vybereme pouze záznamy, které mají sobě odpovídající hodnoty sloupců Ucitel.id a Predmet.ucitel. Nebo můžeme použít klauzuli JOIN v definici relací.

SELECT p.nazev AS "Název", u.prijmeni + ", " + u.jmeno AS "Učitel" FROM Predmet p, Ucitel u WHERE p.ucitel=u.id

Tento dotaz vypíše seznam názvů předmětů a k nim příslušných učitelů. Všimněte si, že jsme v definici zobrazených polí použili sloučení řetězců do jednoho. Jedná o tzv. vnější spojení (z angl. outer join).

SELECT p.nazev AS "Název", u.prijmeni + ", " + u.jmeno AS "Učitel" FROM Predmet p JOIN Ucitel u ON p.ucitel=u.id

Tento dotaz je ve výsledku stejný jako dotaz předcházející, ovšem použili jsme tentokrát tzv. vnitřní spojení (z angl. inner join). Použití tohoto typu spojení nám dává větši flexibilitu při definici spojení, což si ukážeme později.

Vypočtené sloupce

Při definici vypisovaných sloupců či při konstrukci výrazů se nemusíme omezit pouze na počítání s jedním řádkem, ale můžeme též počítat s celou množinou sloupce. Pro toto počítání se používají následující funkce:

• SUM() – součet hodnot sloupce
• MIN() – minimální hodnota množiny
• MAX() – maximální hodnota množiny
• AVG() – průměrná hodnota
• COUNT() – počet unikátních hodnot

SELECT min(plat) AS "Minimum", max(plat) AS "Maximum", avg(plat) AS "Průměr" FROM Ucitel

Vypíše minimální, maximální a průměrný plat všech učitelů.