Deal with Postgres

      Brak komentarzy do Deal with Postgres

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.

uml sql bazaOne

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) );
baza1.sql

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ą.
uml sql bazaTwo

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) )
bazaTwo.sql

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.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.

Witryna wykorzystuje Akismet, aby ograniczyć spam. Dowiedz się więcej jak przetwarzane są dane komentarzy.