====== LAB: Integracja z RDBMS ======


Celem laboratorium jest pokazanie możlwości integracji maszyny wnioskującej SWI Prolog z systemami zarządzania relacyjnymi bazami danych (RDBMS).

Interackja taka jest możliwa z wykorzystaniem [[wp>ODBC]].

===== - Konfiguracja ODBC =====

Aby móc skorzystać z tzw. źródła danych ODBC, należy w domowym katalogu utworzyć plik o nazwie ''.odbc.ini'' oraz umieścić w nim poniższą treść:

<code>
[prolog]
Description		= PostgreSQL Unicode
Driver		= PostgreSQL Unicode
Trace		= No
TraceFile		= 
Database		= prolog
Servername		= borg.ia.agh.edu.pl
Username                =
Password                =
Port		        = 5432
Protocol		= 6.4
ReadOnly		= No
RowVersioning		= No
ShowSystemTables	= No
ShowOidColumn		= No
FakeOidIndex		= No
ConnSettings		= 
</code>

**Uwaga**: 
  - Wartości pól ''Username'' oraz ''Password'' będą podane przez prowadzącego na zajęciach.
  - Prawa dostępu do pliku ''.odbc.ini'' powinny być ustawione na 600.

Powyższe działania konfigurują źródło danych ODBC o nazwie ''prolog'' (''[prolog]'').
Źródło to jest skojarzone z systemem zarządzania relacyjnymi bazami danych PostgreSQL działającym na serwerze ''borg.ia.agh.edu.pl'', a konkretnie z bazą danych o nazwie ''prolog'' (''Database'').

**Ćwiczenie**

Sprawdź, czy źródło danych jest poprawnie skonfigurowane. W tym celu uruchom narzędzie ''isql'' umożlwiające wykonywanie zapytań w języku SQL na źródle ODBC:

<code>
isql prolog
</code>

Jeżeli połączenie powiodło się zostanie wyświetlony stosowny komunikat:
<code>
~$ isql prolog
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
</code>

Następnie wykonaj proste zapytanie np. ''SELECT nazwa FROM kompozycje;''. Rezultat powinien być zbliżony do tego poniżej:

<code>
SQL> SELECT nazwa FROM kompozycje;
+-----------------------------------------+
| nazwa                                   |
+-----------------------------------------+
| Bukiet 1                                |
| Bukiet 2                                |
| Bukiet 3                                |
| Kosz 1                                  |
| Kosz rozyczek                           |
| Kosz mix                                |
| Kompozycja suszona                      |
| Koszyk gozdzikowy                       |
| Hiacynty w koszyku                      |
| Biala azalia                            |
| Rozowa azalia                           |
| Orchidea rozowa                         |
| Kosz z azalia                           |
| Wiazanka czerwona                       |
| Bukiecik serc                           |
| Ikebana z rozami                        |
| Kwitnacy kosz                           |
| Sloneczniki z lilia                     |
| Strelicje                               |
| Anturium latem                          |
+-----------------------------------------+
SQLRowCount returns 20
20 rows fetched
SQL>
</code>

===== - ODBC w SWI-Prolog =====

SWI Prolog umożliwia wykonywanie zapytań na źródłach ODBC.
Rezultaty zapytania (pojedynczy wiersz) uzgodniane są ze wskazanymi szukanymi.
Wartości kolejnych kolejnych wierszy dostępne są poprzez mechanizm nawrotów.

Przed wykonaniem zapytania należy nawiązać połączenie ze skonfigurowanym źródłem ODBC.

[[http://www.swi-prolog.org/pldoc/package/odbc.html|Dokumentacja]] dotycząca predykatów obsługujących połączenia ODBC z bazami danych.

===== - Połączenia =====

Do nawiązaywania połączenia ze źródłem danych ODBC służy predykat ''odbc_connect/3''.
Pierwszym argumentem jest nazwa źródła danych ODBC (w poniższym przykładzie ''prolog''), drugim jest identyfikator połączenia, trzecim opcje połączenia, np.

<code prolog>
odbc_connect('prolog', Polaczenie,
             [open(once)
           ]).
</code>

Przed zakończeniem korzystania z połączenia ze źródłem danych należy wywołać predykat
''odbc_disconnect/1'', którego argumentem musi być identyfikator połączenia (zakładając, że szukana ''Polaczenie'' jest uzgodniona z wartością będącą identyfikatorem połączenia utworzonym za pomocą ''odbc_connect/3''):

<code prolog>
odbc_disconnect(Polaczenie).
</code>

Zamiast identyfikatora połączenia można użyć synonimu połączenia, korzystając z opcji ''alias'':

<code prolog>
?- odbc_connect('prolog', _,
             [ alias(prolog),
               open(once)
           ]).
</code>

Tak otwarte połączenie można zakończyć:

<code prolog>
?- odbc_disconnect(prolog).
</code>
===== - Schemat Bazy Danych =====

Schemat bazy danych, dostępnej jako źródło danych ''prolog'' przedstawiony jest poniżej.

<code sql>
CREATE TABLE klienci (
    idklienta varchar(10) NOT NULL,
    haslo varchar(10) NOT NULL,
    nazwa varchar(40) NOT NULL,
    miasto varchar(40) NOT NULL,
    kod char(6) NOT NULL,
    adres varchar(40) NOT NULL,
    email varchar(40),
    telefon varchar(16) NOT NULL,
    fax varchar(16),
    nip char(13),
    regon char(9)
);

CREATE TABLE kompozycje (
    idkompozycji char(5) NOT NULL,
    nazwa varchar(40) NOT NULL,
    opis varchar(100),
    cena numeric(7,2),
    minimum integer,
    stan integer
);


CREATE TABLE odbiorcy (
    idodbiorcy integer NOT NULL,
    nazwa varchar(40) NOT NULL,
    miasto varchar(40) NOT NULL,
    kod char(6) NOT NULL,
    adres varchar(60) NOT NULL
);


CREATE TABLE zamowienia (
    idzamowienia integer NOT NULL,
    idklienta varchar(10) NOT NULL,
    idodbiorcy integer NOT NULL,
    idkompozycji char(5) NOT NULL,
    termin date NOT NULL,
    cena numeric(7,2),
    zaplacone boolean,
    uwagi varchar(200)
);


CREATE TABLE historia (
    idzamowienia integer NOT NULL,
    idklienta varchar(10),
    idkompozycji char(5),
    wartosc numeric(7,2),
    termin date
);

</code>

Kompletny {{:pl:prolog:prolog_lab:baza.sql.txt|skrypt}} z schematem i danymi.

===== - Wykonywanie zapytań =====

Zapytania można realizować korzystając z predykatu ''odbc_query/4'' (albo ''odbc_query/3'', patrz [[http://www.swi-prolog.org/pldoc/doc_for?object=section(3%2c '2.2'%2c swi('%2fdoc%2fpackages%2fodbc.html'))|dokumentacja]]).
Pierwszym argumentem jest identyfikator połączenia, drugim zapytanie w języku SQL, trzecim rezultat zapytania w postaci termu ''row/n'' (argumenty predykatu ''row/n'' będą uzgodnione z wartościami w kolejnych kolumnach zwaracanych przez zapytanie), czwartym lista dodatkowych opcji.

Przykładowe zapytanie zwracające jako wartości szukanych ''Nazwa'', ''Email'' wartości kolumn ''nazwa'' i ''email'' tabeli ''klienci'' będzi wyglądało następująco:

<code prolog>
?- odbc_query(prolog, 'SELECT nazwa, email FROM klienci' ,row(Nazwa, Email), []).
</code>

Ostatni argument ''odbc_query/4'' umożliwia przekazanie dodatkowych opcji, m.in. konwersje typów pomiędzy SQL a Prologiem (patrz [[http://www.swi-prolog.org/pldoc/doc_for?object=section(3%2c '2.2'%2c swi('%2fdoc%2fpackages%2fodbc.html'))|dokumentacja]]).

**Ćwiczenie**

  - Otwórz połączenie do bazy danych korzystając ze źródła danych ''prolog''.
  - Wykonaj następujące zapytanie SQL: ''SELECT SUM(cena), miasto FROM zamowienia JOIN odbiorcy USING (idodbiorcy) GROUP BY miasto;'', przeglądnij wyniki.
  - Zamknij połączenie.

===== - Proste zapytania =====

**Ćwiczenie**

Zaimplementuj:
  - predykat ''op/0'' otwierający połączenie ze źródłem danych ''prolog'',
  - predykat ''cl/0'' zamykający w/w połączenie,
  - predykat ''kompozycje/2'' udostępniający informacje o kompozycji tj. identyfikator oraz cenę. **Uwaga**: może być konieczne zastosowanie konwersji typów, użyteczny predykat: ''atom_number/2'' (patrz [[http://www.swi-prolog.org/pldoc/doc_for?object=section(2%2c '4.21'%2c swi('%2fdoc%2fManual%2fmanipatom.html'))|dokumentacja]]).

Przetestuj działanie w/w predykatów.

===== - Złożone przetwarzanie =====

Częstą rozterką kupojącego jest: mam określoną sumę pieniędzy, ale co moge za to kupić np.:
  * Jakie i ile kompozycji można kupić za 200 zł?
Takie zapytanie jest trudne lub wręcz niemożliwe do zaimplementowania za pomocą SQL.

**Ćwiczenie**

Zaimplementuj predykat ''jakieza/2'', tak aby zapytanie:
<code prolog>
?- jakieza(200,X).
</code>
uzgadniało X z listą nazw identyfikatorów kompozycji kwiatowych, które można kupić mając do dyspozyji 200 zł. **Uwaga**: predykat winien być niedeternistyczny, umożliwiać znalezienie alternatywnych rozwiązań.


===== - Samodzielny program =====

Wykorzystaj kod z poprzedniego ćwiczenia.

**Ćwiczenie**

Zmodyfikuj kod programu z poprzedniego ćwiczenia, tak aby stanowił samodzielną aplikację.
Program po skonsultowaniu, powinien zapytać o kwotę i wyświetlić wszystkie możlwie kombinacje kompozycji kwiatowych, które można za podaną kwotę kupić, podając: identyfikator kompozycji, nazwę oraz cenę.

====== Uwagi, komentarze, propozycje ======

Laboratorium jest za krótkie. Zostaje ok. 30 minut wolnego czasu.

 --- //[[szymek@adres.pl|Szymon Świerkosz]]//