Kapownik - Piotr Sinda

Ta strona jest tylko do odczytu. Możesz wyświetlić źródła tej strony ale nie możesz ich zmienić.
====== LibreOffice z silnikiem bazy PostgreSQL ======
===== Założenia =====
Konfiguracja umożliwiająca użytkowanie bez konieczności instalacji i bez uprawnień administratora:
  * **PostgreSQL** - Zastosowanie tego silnika zdejmuje wszelkie ograniczenia bazy HSQLDB. Ponadto bezproblemowa instalacja i uruchomienie bez instalacji. W pełni profesjonalny silnik bazy danych.
  * **DBeaver** - Z uwagi na ograniczenia zarządzania bazą z poziomu LibreOffice Base korzystnie jest użyć do tego celu DBeaver. Przewyższa możliwościami również Admin4. Oczywiście nie wymaga instalacji.
  * **LibreOffice** - Zastosowanie wersji Portable umożliwia użycie w systemie gdzie nie mamy możliwości instalacji oprogramowania.

Opis dotyczy konfiguracji w systemie Windows gdyż najczęściej tam mamy ograniczenia administracyjne.

===== PostgreSQL =====
Pobieranie z [[https://www.enterprisedb.com/download-postgresql-binaries]]
===== DBeaver =====
Pobieranie z [[https://dbeaver.io/download/]]
===== LibreOffice Portable =====
Pobieranie z [[https://www.libreoffice.org/download/portable-versions/]]
===== Konfiguracja i utworzenie bazy =====
Pobrany plik np. ''postgresql-17.4-1-windows-x64-binaries.zip'' rozpakowujemy np. do postaci:
<code>
C:\pgsql
</code>
W czasie pisania niniejszego dokumentu miałem problem z uruchomieniem wersji ''postgresql-18.0-2-windows-x64-binaries.zip''. Otrzymywałem komunikat:
<code>
wykonywanie skryptu ładowania wstępnego ... ok 
wykonywanie inicjacji po ładowaniu wstępnym ... 
proces potomny został zatrzymany przez wyjątek 0xC0000005 
initdb: removing data directory "C:/pgsql_new/pgdata"
</code>
dlatego została użyta poprzednia wersja.

==== Inicjalizacja bazy ====
<code>
C:\pgsql\bin\initdb -D C:\pgsql\pgdata -U postgres -W -E UTF8 -A scram-sha-256
</code>
Jak widać pliki danych zostaną umieszczone w katalogu ''C:\pgsql\data''.
==== Skrypty .vbs uruchamiania i wyłączenia silnika baz ====
**Uruchomienie**
<code vbscript start_postgres.vbs>
Set WshShell = CreateObject("WScript.Shell")
cmd = """C:\pgsql\bin\pg_ctl.exe"" start -D ""C:\pgsql\pgdata"" -l ""C:\pgsql\pgdata\pgsql.log"""
WshShell.Run cmd, 0, False
</code>
Plik możemy podlinkować do Autostart systemu. Otwórz folder autostartu użytkownika przez wpisanie do Eksploratora plików:
<code>
%APPDATA%\Microsoft\Windows\Start Menu\Programs\Startup
</code>

lub możesz to szybko otworzyć:

  * wciśnij **Win+R**,
  * wpisz:

<code>
shell:startup
</code>
  * i naciśnij Enter.

**Wyłączenie**
<code vbscript stop_postgres.vbs>
Set WshShell = CreateObject("WScript.Shell")
cmd = """C:\pgsql\bin\pg_ctl.exe"" stop -D ""C:\pgsql\pgdata"" -m fast"
WshShell.Run cmd, 0, False
</code>

Po ponownym uruchomieniu systemu silnik bazy danych powinien działać i możemy to sprawdzić w ''cmd'':
<code>
C:\pgsql\bin\pg_ctl.exe status -D C:\pgsql\pgdata
</code>

===== Połączenie DBeaver =====
Za pierwszym razem łączymy się do bazy postgres z zaznaczeniem opcji ''Show all databases''. Bez tego nie utworzymy nowej bazy danych. Oczywiście utworzenie nowego użytkownika, bazy i jej stryktury można zrobić "z palca" z poziomu ''cmd'' czy ''Admin4'', ale lepiej od początku przywyknąć do użytkowania DBeaver. W DBeaver otrzymujemy kolorowanie składni, gdzie w ''cmd'' tego nie mamy.

==== Tworzenie użytkownika dla przyszłej bazy ====
<code sql>
CREATE ROLE piotr LOGIN PASSWORD 'piotr123';
</code>
lub
<code sql>
CREATE USER piotr WITH PASSWORD 'piotr123';
</code>
==== Tworzenie bazy dla uprzednio dodanego użytkownika ====
<code sql>
CREATE DATABASE mojadb OWNER piotr;
</code>
==== Tworzenie kolejnego użytkownika utworzonej bazy ====
<code sql>
CREATE USER adam WITH PASSWORD 'adam123';
</code>
==== Nadanie mu uprawnień do połączenia się z bazą ====
<code sql>
GRANT CONNECT ON DATABASE mojadb TO adam;
</code>
==== Nadanie uprawnień do korzystania ze schematu public ====
<code sql>
\c mojadb
GRANT USAGE ON SCHEMA public TO adam;
</code>
==== Nadanie uprawnień do odczytu danych ====
<code sql>
GRANT SELECT ON ALL TABLES IN SCHEMA public TO adam;
</code>
==== Nadanie pełnych uprawnień czyli do odczytu, usuwania, dodawania ====
<code sql>
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO adam;
</code>
==== Automatyczne uprawnienia dla przyszłych tabel ====
<code sql>
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO nowyuzytkownik;
</code>
==== Minimalny ciąg dla nowego użytkownika pracującego z LO Base ====
<code sql>
-- Utwórz użytkownika (z loginem i hasłem)
CREATE USER uzytkownik WITH PASSWORD 'haslo123';

-- Daj mu możliwość łączenia się z bazą
GRANT CONNECT ON DATABASE mojabaza TO uzytkownik;

-- Przełącz się na daną bazę
\c mojabaza

-- Nadaj dostęp do schematu public (gdzie są tabele)
GRANT USAGE ON SCHEMA public TO uzytkownik;

-- Daj pełne prawa do wszystkich tabel i sekwencji (czyli danych)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO uzytkownik;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO uzytkownik;

-- Ustaw, żeby wszystkie NOWE tabele i sekwencje też automatycznie były dostępne
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO uzytkownik;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO uzytkownik;
</code>
==== Dlaczego \c mojabaza? ====
Będą połączonym do serwera postgresql z poziomu użytkownika postgres i użycia ''\c mojabaza'' możemy się rozłączyć i połączyć bezpośrednio do ''mojabaza'' użytkownikiem z uprawnieniami i dokończyć nadawanie uprawnień nowemu użytkownikowi.
===== Tabele =====
==== Tworzenie tabeli 'test_table' ====
Tworzona tabela musi zawierać minimum jedną kolumnę. Wprowadzenie polecenia ''CREATE TABLE test_table ();'' zakończy się błędem ''ERROR:  cannot create a table without columns''.
<code sql>
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    nazwa TEXT NOT NULL,
    opis TEXT,
    data_utworzenia TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
</code>
<WRAP center round important 80%>
Uwaga. Dodana kolumna o typie TEXT skutkuje tym, że po odświeżeniu tabel i wprowadzaniu danych tekstowych czasem w polach mogą pojawić się zera. Jeżeli tak się stanie odświeżenie tabel nie wystarcza. Trzeba wyłączyć i włączyć LO Base.
</WRAP>

==== Dodanie kolumny 'lokalizacja' do tabeli 'test_table' ====
<code sql>
ALTER TABLE test_table
ADD COLUMN lokalizacja TEXT;
</code>

==== Dodanie kolumny 'rok' z wartością domyślną '2025' do tabeli 'test_table' ====
<code sql>
ALTER TABLE test_table
ADD COLUMN rok INTEGER DEFAULT 2025;
</code>

==== Dodanie kolumny 'kraj' z wartością domyślną 'Polska' obowiązkiem wpisu do tabeli 'test_table' ====
<code sql>
ALTER TABLE test_table
ADD COLUMN kraj TEXT NOT NULL DEFAULT 'Polska';
</code>

==== Dodanie kolumny 'artysta_id' z kluczem obcym 'artysci(id)' ====
Dzięki temu ''test_table.artysta_id'' będzie wskazywać na ''artysci.id''.
<code sql>
ALTER TABLE test_table
ADD COLUMN artysta_id INTEGER REFERENCES artysci(id);
</code>


===== Struktura bazy =====

==== Tworzenie przykładowej tabeli artyści ====

<code sql>
CREATE TABLE artysci (
    id SERIAL PRIMARY KEY,
    nazwa TEXT NOT NULL UNIQUE
);
</code>

==== Tworzenie przykładowej tabeli utwory ====

<code sql>
CREATE TABLE utwory (
    id SERIAL PRIMARY KEY,
    tytul TEXT NOT NULL,
    rok INTEGER,
    czas INTERVAL,
    opis TEXT
);
</code>

==== Tworzenie tabeli łącznikowej ====

Ponieważ jeden utwór może mieć wielu artystów, a jeden artysta wiele utworów → relacja **wiele-do-wielu (many-to-many)**.

<code sql>
CREATE TABLE utwor_artysta (
    utwor_id INTEGER REFERENCES utwory(id) ON DELETE CASCADE,
    artysta_id INTEGER REFERENCES artysci(id) ON DELETE CASCADE,
    PRIMARY KEY (utwor_id, artysta_id)
);
</code>

==== Unikanie duplikatów ====

Jeśli chcesz uniknąć duplikatów (np. dwa razy ten sam utwór-artysta), możesz wstawić unikatowe ograniczenie:

<code sql>
ALTER TABLE utwor_artysta ADD CONSTRAINT unikalny_utwor_artysta UNIQUE (utwor_id, artysta_id)
</code>
===== Połączenie LibreOffice Base =====
  * Połącz z istniejąca bazą danych
  * wybierz **PostgreSQL**
  * Dalej
  * Nazwa bazy danych: **mojadb**
  * Serwer: **localhost**
  * Numer portu: **5432**
  * Dalej
  * Nazwa użytkownika: **piotr**
  * Wymagane hasło: **tak**
  * Dalej
  * Zakończ
  * Nadaj nazwę pliku
  * Zapisz