egzemplarze.sql

SELECT e.id, e.rok_wydania, e.lokalizacja_id as lokalizacja_nazwa, e.lokalizacja_id as l_ident,
 e.wydawnictwo_id as wydawnictwo_name, e.wydawnictwo_id as w_ident,
 fullname(u.imie, u.nazwisko) as user_fullname, u.id as u_ident,
 k.tytul as ksiazka_tytul, k.tytul as k_ident,
 fullname(a.imie, a.nazwisko) as autor_fullname, a.id as a_ident,
 wyp.id as wyp_ident, wyp.id IS NOT NULL as wypozyczony
FROM egzemplarz e LEFT OUTER JOIN wypozyczenia wyp ON wyp.egzemplarz = e.id AND wyp.data_zwrotu IS NULL
  LEFT OUTER JOIN uzytkownik u ON wyp.uzytkownik = u.id
  JOIN ksiazka k ON e.ksiazka_id = k.tytul
  JOIN autor a ON a.id = k.autor_id;

funkcje.sql

CREATE OR REPLACE FUNCTION fullname(imie varchar, nazwisko varchar) RETURNS varchar AS $$
  BEGIN
    RETURN imie || ' ' || nazwisko;
  END;
$$ LANGUAGE plpgsql;

DROP FUNCTION search(varchar, varchar);
CREATE FUNCTION search (varchar, varchar) 
  RETURNS TABLE (nazwa varchar, wiecej varchar, ident varchar, typ varchar) AS $$ 
    SELECT * FROM (
    
    SELECT k.tytul as nazwa, 'Autor: ' || fullname(a.imie, a.nazwisko) as wiecej, k.tytul as ident, varchar 'ksiazki' as typ
    FROM ksiazka k JOIN autor a ON k.autor_id = a.id
    WHERE k.tytul LIKE $1 
    
    UNION 
    
    SELECT fullname(a.imie, a.nazwisko) as nazwa, NULL as wiecej, 
      CAST(a.id AS varchar) as ident, varchar 'autorzy' as typ
    FROM autor a 
    WHERE fullname(a.imie, a.nazwisko) LIKE $1
    
    UNION
    
    SELECT w.nazwa as nazwa, w.adres AS wiecej, w.nazwa as ident, varchar 'wydawnictwa' as typ
    FROM wydawnictwo w
    WHERE w.nazwa LIKE $1 OR w.adres LIKE $1
    
    UNION
    
    SELECT l.nazwa as nazwa, l.adres as wiecej, l.nazwa as ident, varchar 'lokalizacje' as typ
    FROM lokalizacja l
    WHERE l.nazwa LIKE $1 OR l.adres LIKE $1
    
    ) u WHERE u.typ = $2 OR $2 IS NULL
    $$
  LANGUAGE SQL;
  
CREATE OR REPLACE FUNCTION jest_wypozyczony(e_ident int)
  RETURNS boolean AS $$
  BEGIN
    RETURN (SELECT count('x') FROM wypozyczenia WHERE egzemplarz = e_ident AND data_zwrotu IS NULL) <> 0;
  END;
  $$ LANGUAGE plpgsql;
  
CREATE OR REPLACE FUNCTION wypozycz(usr_id int, egz_id int, max_wyp int, lend_time int)
RETURNS varchar AS
$$
  BEGIN
    IF (SELECT jest_wypozyczony(egz_id)) THEN
      RETURN varchar 'Ten egzemplarz jest już wypożyczony.';
    END IF;
    IF (SELECT COUNT('x') FROM wypozyczenia WHERE uzytkownik=usr_id AND data_zwrotu IS NULL) >= max_wyp THEN
      RETURN varchar 'Użytkownik osiągnął limit wypożyczeń.';
    END IF;
    INSERT INTO wypozyczenia (uzytkownik, egzemplarz, data_wypozyczenia, termin_zwrotu)
    VALUES (usr_id, egz_id, current_date, (current_date + lend_time));
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

historia.sql

    SELECT fullname(a.imie, a.nazwisko) as autor_fullname, a.id as a_ident,
      k.tytul as ksiazka_tytul, k.tytul as k_ident,
      l.nazwa as lokalizacja_nazwa, l.nazwa as l_ident,
      wyp.id, wyp.data_zwrotu, wyp.termin_zwrotu, wyp.data_wypozyczenia,
      (wyp.data_zwrotu IS NULL) as nieoddany, 
      (wyp.termin_zwrotu < current_date AND wyp.data_zwrotu IS NULL) as po_terminie
    FROM wypozyczenia wyp JOIN uzytkownik u ON wyp.uzytkownik = u.id
      JOIN egzemplarz e ON wyp.egzemplarz = e.id
      JOIN ksiazka k ON e.ksiazka_id = k.tytul
      JOIN autor a ON k.autor_id = a.id
      JOIN lokalizacja l ON e.lokalizacja_id = l.nazwa
    WHERE u.id = %s;

oddaj.sql

UPDATE wypozyczenia SET data_zwrotu = current_date WHERE id = %s

pobierz_egzemplarze.sql

SELECT e.id, l.nazwa as l_nazwa, l.adres as l_adres, e.rok_wydania, e.wydawnictwo_id as w_nazwa, 
  w.id IS NULL AS dostepny, w.termin_zwrotu
FROM egzemplarz e LEFT OUTER JOIN wypozyczenia w ON e.id = w.egzemplarz AND w.data_zwrotu IS NULL
JOIN lokalizacja l ON e.lokalizacja_id = l.nazwa
WHERE e.ksiazka_id = %s
ORDER BY w.termin_zwrotu, l_nazwa ASC;

wydania.sql

SELECT DISTINCT w.*, e.rok_wydania 
FROM wydawnictwo w JOIN egzemplarz e ON e.wydawnictwo_id = w.nazwa
WHERE e.ksiazka_id = %s ORDER BY e.rok_wydania ASC