====== Projekt logiczny ======
=====-. Projektowanie w oparciu o zdefiniowany diagram ERD =====
Nieco zmieniony [[new_erd_diagram|diagram ERD]]
CREATE TABLE friends
(
id serial NOT NULL,
id_user integer NOT NULL,
id_friend integer NOT NULL,
status smallint NOT NULL,
CONSTRAINT friends_pkey PRIMARY KEY (id),
CONSTRAINT friends_id_friend_fkey FOREIGN KEY (id_friend)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT friends_id_user_fkey FOREIGN KEY (id_user)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
CREATE TABLE tags
(
id serial NOT NULL,
"name" text,
id_user integer NOT NULL,
color integer,
CONSTRAINT tags_pkey PRIMARY KEY (id),
CONSTRAINT "owner" FOREIGN KEY (id_user)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
CREATE TABLE user_event
(
id serial NOT NULL,
id_user integer NOT NULL,
id_event integer NOT NULL,
CONSTRAINT user_event_pkey PRIMARY KEY (id),
CONSTRAINT user_event_id_event_fkey FOREIGN KEY (id_event)
REFERENCES events (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT user_event_id_user_fkey FOREIGN KEY (id_user)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
CREATE TABLE users
(
id serial NOT NULL,
"name" character varying(30),
"login" text NOT NULL,
"password" character(32) NOT NULL,
session_cookie character(32),
CONSTRAINT id PRIMARY KEY (id),
CONSTRAINT users_login_key UNIQUE (login)
)
CREATE TABLE events
(
id serial NOT NULL,
id_tag integer,
startdate date,
title text,
description text,
duration integer,
CONSTRAINT events_pkey PRIMARY KEY (id),
CONSTRAINT events_id_tag_fkey FOREIGN KEY (id_tag)
REFERENCES tags (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL
)
=====-. Słowniki danych =====
* friends - tabela przechowująca dane o powiązaniach między uzytkownikami
* id - klucz główny tabeli - serial NOT NULL,
* id_user - użytkownik którego dotyczy relacja - klucz obcy do tabeli users.id, integer NOT NULL,
* id_friend - użytkownik z którym związana jest relacja - klucz obcy do tabeli users.id, integer NOT NULL,
* status - status relacji (wysłana, zaakceptowana, odrzucona) - smallint NOT NULL,
* tags - tabela zawierająca dane o utworzonych etykietach
* id - klucz główny tabeli - serial NOT NULL,
* "name" - nazwa etykiety - text,
* id_user - użytkownik z którym związana jest etykieta - klucz obcy do tabeli users.id, integer NOT NULL,
* color - numer schematu kolorów - integer,
* user_event - tabela zawierająca informację o powiązaniach użytkowników z wydarzeniami
* id - klucz główny tabeli - serial NOT NULL,
* id_user - użytkownik którego dotyczy wydarzenie - klucz obcy do tabeli users.id, integer NOT NULL,
* id_event - wydarzenie powiązane z użytkownikiem - klucz obcy do tabeli events.id, integer NOT NULL,
* users - tabela zawierająca informacje o użytkownikach
* id - klucz główny tabeli - serial NOT NULL,
* "name" - nazwa wyświetlana użytkownika - character varying(30),
* "login" - login użytkownika - text NOT NULL,
* "password" - zhashowane hasło użytkownika - character(32) NOT NULL,
* session_cookie - nazwa sesji do "zapamiętaj mnie" - character(32),
* events - tabela zawierająca wydarzenia
* id - klucz główny tabeli - serial NOT NULL,
* id_tag - identyfikator etykiety - klucz obcy do tabeli tag.id, integer,
* startdate - data rozpoczęcia wydarzenia - date,
* title - tytuł wydarzenia - text,
* description - opis wydarzenia - text,
* duration -czas trwania wydarzenia - integer,
=====-. Analiza zależności funkcyjnych i normalizacja tabel =====
**1NF**
* spełniona - każda składowa w każdej krotce jest atomowa (nie daje podzielić się na mniejsze wartości).
**2NF**
* spełniona - bo jest 1NF oraz żaden atrybut wtórny relacji nie jest w pełni funkcyjnie zależny od wszystkich kluczy tej relacji.
**3NF**
* spełniona - bo jest 2 NF oraz każdy atrybut wtórny jest tylko bezpośrednio zależny od klucza głównego.
=====-. Projektowanie operacji na danych =====
W projekcie zastosowane zostało mapowanie OR obsługiwane przez bibliotekę Hibernate.
**Konfiguracja Hibernate'a**
==== Zapytania SQL ====
**Logowanie użytkownika:**
Podczas logowania sprawdzane jest czy w bazie danych istnieje podany login i hasło:
select user0_.id as col_0_0_ from public.users user0_ where user0_.login=? and user0_.password=?
Następnie po sprawdzeniu pobierane są dane użytkownika:
select user0_.id as id0_, user0_.name as name0_, user0_.login as login0_, user0_.password as password0_,
user0_.session_cookie as session5_0_ from public.users user0_ where user0_.login=?
W następnej kolejności pobierane są dodatkowe dane powiązane z bieżącym użytkownikiem.
Wydarzenia pobierane są poprzez wewnętrzne złączenie tabel user_event (tabela asocjacyjna wiążąca użytkowników z wydarzeniami) oraz events (tabela z wydarzeniami):
select […] from public.user_event events0_ inner join public.events event1_
on events0_.id_event=event1_.id where events0_.id_user=?
Dla każdego wydarzenia pobierane są etykiety (dla każdej użytej etykiety):
select tag0_.id as id4_0_, tag0_.name as name4_0_, tag0_.color as color4_0_,
tag0_.id_user as id4_4_0_ from public.tags tag0_ where tag0_.id=?
Następnie pobierane są informacje o znajomych użytkownika:
select […] from public.friends friends0_ where friends0_.id_user=?
Ponieważ tabela friends jest tabelą asocjacyjną, konieczne jest pobranie reszty danych (każde zaytanie oddzielnie, ponieważ tabela friends z punktu widzenia mappera nie jest tablicą asocjacyjną, gdyż mapuje ona na nową klasę Friend, która posiada wskaźniki do użytkownika, jego znajomego, oraz dodatkowo posiada informacje o stopniu znajomości – znojomy, zaproszony, zablokowany):
select user0_.id as id0_0_, user0_.name as name0_0_, user0_.login as login0_0_, user0_.password as password0_0_,
user0_.session_cookie as session5_0_0_ from public.users user0_ where user0_.id=?
Ostatecznie pobierane są także wszystkie etykiety, jakimi może zarządzać dany użytkownik:
select tags0_.id_user as id4_0_1_, tags0_.id as id1_, tags0_.id as id4_0_, tags0_.name as name4_0_, tags0_.color as color4_0_,
tags0_.id_user as id4_4_0_ from public.tags tags0_ where tags0_.id_user=?
**Wyszukiwanie znajomych:**
Wystarczy jedno zapytanie o użytkowników, których nazwa lub login pasują do wzorca
select user0_.id as id0_, user0_.name as name0_, user0_.login as login0_,
user0_.password as password0_, user0_.session_cookie as session5_0_ from
public.users user0_ where user0_.login like ? or user0_.name like ?
**Wysyłanie zaproszenia do znajomego:**
Wysłanie zapytania typu insert:
insert into public.friends (status, id_user, id_friend) values (?, ?, ?)
**Dodawanie wydarzenia:**
Wysyłane są dwa zapytania typu insert, ponieważ należy dodać dane zarówno do tabeli z wydarzeniami, jak i do tabeli asocjacyjnej:
insert into public.events (id_tag, startDate, title, description, duration) values
(?, ?, ?, ?, ?)
oraz:
insert into public.user_event (id_user, id_event) values (?, ?)
**Porównywanie kalendarzy:**
Dla każdego wybranego do porównania użytkownika , konieczne jest pobranie listy jego wydarzeń.
Jest to spowodowane mechanizmem lazy-load. Zbiory (w tym wypadku zbiory wydarzeń) ładowane są dopiero przy pierwszym ich użyciu. Dlatego też dla każdego wybranego użytkownika nastąpi wysłanie zapytania:
select […] from public.user_event events0_ inner join public.events event1_ on
events0_.id_event=event1_.id where events0_.id_user=?