Postgres – System do zarządzana relacyjno-obiektową bazą danych, aplikacja jest dostępna na prawie wszystkich platformach (Linux, Windows, Unix). Wydawana jest na podstawie licencji PostgresSql(za darmo, otwarto źródłowa). Projekt zrodził się na Uniwersytecie Kalifornijskim w Berkley, początkowo nosił nazwę Ingres, ale po małych przepychankach w zespole i nieudanych próbach komercjalizacji, projekt powrócił na stare tory i ewoluował do nazwy postgres. Jest w pełni zgodny w myśl zasady ACID, wspiera większość typów danych z standardu SQL2008, system MVVC( comity&rollbaki każdej transakcji), posiada natywny interfejs dla języków C/C++, Java, .Net, Perl, Python i wiele, wiele innych.
Celem tego wpisu,będzie zrobienie małej rozgrzewki z tego wynalazku 🙂 Stworzymy bazę, uzupełnimy ją o jakieś dane, wylistujemy to co nas interesuje i spróbujemy to wszystko przenieś do innej bazy 🙂 Dobra, dosyć tego gadania 🙂 Czas wziąć się do roboty 🙂
1. Instalacja i konfiguracja serwera
Ściągamy najnowsza dostępną paczkę narzędzi+serwer z oficjalnej strony postgresa, kompilujemy i instalujemy.
wget https://ftp.postgresql.org/pub/source/v9.6.2/postgresql-9.6.2.tar.bz2
tar -jxvf postgresql-9.6.2.tar.bz2
cd postgresql-9.6.2
./configure
make & make install
mkdir /run/postgresql
chown postgres /run/postgresql
useradd -m -G users -s /bin/bash postgres
postgres – domyślny użytkownik, którego w ramach będziemy odpalać serwer i operowali na bazie.
2. Przygotowanie bazy do pracy
Na potrzeby zadania, stworzymy bardzo prostą strukturę bazy danych dla przedszkola w dwóch wersjach. A na zakończenie spróbujemy przenieś dane z pierwszej do drugiej struktury 🙂
BazaOne:
Rodzic ma wiele lub 0 dzieci, dziecko musi mieć jednego rodzica.A że ma dwa klucze obce(FK) do tej samej tabeli, może mieć dwóch rodziców.
CREATE TABLE rodzic (
id serial primary key,
imie varchar not null,
nazwisko varchar not null);
CREATE TABLE dziecko (
id SERIAL primary key,
imie varchar not null,
data_urodzenia date not null,
rodzic1id integer references rodzic(id),
rodzic2id integer references rodzic(id)
);
BazaTwo:
Wersja z tablica asocjacyjna( to taka tablica, która przechowuje pary kluczy obcych). Rodzic ma wiele lub zero relacji z tablicą asocjacyjna, dziecko ma 1 lub wiele relacji z tablicą asocjacyjną.
CREATE TABLE rodzic2 (
id serial primary key,
imie varchar not null,
nazwisko varchar not null);
CREATE TABLE dziecko2 (
id SERIAL primary key,
imie varchar not null,
data_urodzenia date not null
);
CREATE TABLE dziecko_rodzic (
id_rodzic integer not null references rodzic2(id),
id_dziecko integer not null references dziecko2(id),
PRIMARY KEY (id_rodzic,id_dziecko)
)
Stworzenie bazy i uruchomienie serwera
su - postgres
mkdir firma
initdb --locale $LANG -E UTF8 -D ./firma
psql -c 'CREATE DATABASE bazaOne;'
psql -c 'CREATE DATABASE bazaTwo;'
psql bazaone < firma/bazaOne.sql
psql bazatwo < firma/bazaTwo.sql
pg_ctl -D ./firma/
initdb – tworzy czyste, podstawowe środowisko i konfiguracje dla serwera postgresa.
psql – Interaktywny terminal zapytań do serwera postgres i bazy danych.
pg_ctl – Uruchamia, zarządza, zatrzymuje instancje serwera postgres
3. Wprowadzanie przykładowych danych do bazy
Żeby ułatwić sobie życie, wykorzystamy pętle, w które dodamy do bazy rekordy w kilku wariantów:
– rodzic bez dzieci,
– dziecko z jednym rodzicem,
– dziecko z obojga rodzicami.
Do połączenia sie z baza, żeby wysyłać do niej zapytania, możemy użyć psql lub pgadmin3.
W przypadku psql:
psql #jesteś zalogowani na użtkowniku postgres
\c bazaone #wybieramy baze
\dt #listuje tabele
DO
$do$
DECLARE
idRodzic1 rodzic.id%TYPE;
idRodzic2 rodzic.id%TYPE;
BEGIN
DELETE FROM dziecko;
DELETE FROM rodzic;
FOR i IN 1..10 LOOP
IF i%2 = 0 THEN
EXECUTE '
Insert INTO rodzic (imie,nazwisko)
VALUES (' || i ||E',\'Kowalski\')';
ELSE
EXECUTE '
Insert INTO rodzic (imie,nazwisko)
VALUES (' || i ||E',\'Fiut\')
RETURNING id';
idRodzic1:=lastval();
EXECUTE '
Insert INTO dziecko (data_urodzenia,rodzic1id,imie)
VALUES (NOW(),'|| idRodzic1|| E',\'Adam\') ';
EXECUTE '
Insert INTO dziecko (data_urodzenia,rodzic1id,imie)
VALUES (NOW(),'|| idRodzic1 || E',\'Ewa\') ';
END IF;
END LOOP;
FOR i IN 1..10 LOOP
EXECUTE '
Insert INTO rodzic (imie,nazwisko)
VALUES ('|| i ||E',\'Nowak\')
RETURNING id';
idRodzic1:=lastval();
EXECUTE '
Insert INTO rodzic (imie,nazwisko)
VALUES ('|| -i ||E',\'Nowak\')
RETURNING id';
idRodzic2:=lastval();
EXECUTE '
Insert INTO dziecko (data_urodzenia,rodzic1id,rodzic2id,imie)
VALUES (NOW(),'|| idRodzic1 ||','|| idRodzic2 || E',\'Jarek\')
';
END LOOP;
END
$do$;
DO %do% .. END %do% – dzięki temu wyrażeniu, możemy używać języku proceduralnego( pętle, if, wysyłać komunikaty notice/rise, tworzyć tymczasowe transakcje).
BEGIN – rozpoczyna tak zwany blok transakcji, jest to mechanizm systemu MVVC. Żeby dane zostały zapisane do bazy danych, cały blok do endu musi się wykonać bezbłędnie.
FOR ..LOOP END LOOP – rozpoczyna i kończy pętle FOR.
END -jest równorzędny z
COMMIT, zatwierdza wprowadzone dane do systemu. Możemy je jeszcze usunąć komendą
ROLLBACK – powrót do stanu przed ostatnią transakcją.
4. Kilka operacji na wylistowaniu danych z bazy
Wylistowac dzieci, bez duplikatów:
SELECT d.id,d.imie,
COALESCE(
(select nazwisko FROM rodzic WHERE id=d.rodzic1id ),
(select nazwisko FROM rodzic WHERE id=d.rodzic2id )) as nazwisko_dziecka,
age(d.data_urodzenia)
FROM dziecko d
LEFT JOIN rodzic p ON p.id = d.rodzic1id AND p.id = d.rodzic2id
COALESCE – funkcja akceptuje nieskończoną ilość argumentów, zwraca pierwszy argument, który nie jest zerem(nuLL).
Wylistować rodziców, na podstawie tego, że posiadają wspólne dziecko:
SELECT
d.rodzic1id ,
(Select imie from rodzic
where id=d.rodzic1id) as imie_rierwszego_czlonka,
d.rodzic2id ,
(Select imie from rodzic
where id=d.rodzic1id) as imie_drugiego_czlonka,
(Select nazwisko from rodzic
where id=d.rodzic1id) as nazwisko
FROM dziecko d
LEFT JOIN rodzic r ON r.id = d.rodzic1id AND r.id = d.rodzic2id
A tu wersja wykorzystana z podzapytaniem.
5. Przeniesienie danych z bazaOne do bazaTwo
\c bazatwo
ALTER TABLE rodzic2 ADD COLUMN old_id_from_bazaOne int;
ALTER TABLE dziecko2 ADD COLUMN old_id_from_bazaOne int;
CREATE EXTENSION dblink;
CREATE VIEW viewBazaOne AS
SELECT *
FROM dblink('dbname=bazaone', 'SELECT
r.imie as rimie,
r.nazwisko as rnazwisko,
r.id as rid,
d.imie as dimie, d.data_urodzenia as dage, d.id as did
FROM dziecko d
RIGHT JOIN rodzic r ON r.id = d.rodzic1id OR r.id = d.rodzic2id;')
AS t1(
rimie text,
rnazwisko text,
rid int,
dimie text,
dage date,
did int);
DO
$do$
DECLARE
rec record;
iddziecko2 dziecko2.id%TYPE;
idRodzic2 rodzic2.id%TYPE;
tx text;
BEGIN
TRUNCATE dziecko_rodzic CASCADE;
DELETE FROM rodzic2;
DELETE FROM dziecko2;
FOR rec IN SELECT * FROM viewbazaone
LOOP
IF EXISTS(SELECT old_id_from_bazaone FROM rodzic2 WHERE old_id_from_bazaone = rec.rid)
THEN
RAISE NOTICE 'rodzic(%) alerdy exists in baza2', rec.rid;
ELSE
INSERT INTO rodzic2(imie,nazwisko,old_id_from_bazaone) VALUES (rec.rimie,rec.rnazwisko,rec.rid);
END IF;
IF (rec.did) is not null
THEN
INSERT INTO dziecko2 (imie,data_urodzenia,old_id_from_bazaone) VALUES (rec.dimie,rec.dage,rec.did);
iddziecko2:=(SELECT id FROM dziecko2 WHERE old_id_from_bazaone = rec.did LIMIT 1);
idRodzic2:= (SELECT id FROM rodzic2 WHERE old_id_from_bazaone = rec.rid LIMIT 1);
INSERT INTO dziecko_rodzic (id_rodzic,id_dziecko) VALUES (idRodzic2,iddziecko2);
END IF;
END LOOP;
END
$do$
CREATE EXTENSION dblink moduł, który pozwala się połączyć z inną bazą PostgresSQL.
View – Jest to taka wirtualna tablica, która jest wypadkową zawartego w niej zapytania SELECT.