Both sides previous revision
Poprzednia wersja
Nowa wersja
|
Poprzednia wersja
|
pl:dydaktyka:ztb:2010:projekty:thankswithbeer:start [2010/06/02 13:12] ztb2010 |
pl:dydaktyka:ztb:2010:projekty:thankswithbeer:start [2019/06/27 15:50] (aktualna) |
| |
{{:pl:dydaktyka:ztb:2010:projekty:thankswithbeer:thankswithbeer_erd.png}} | {{:pl:dydaktyka:ztb:2010:projekty:thankswithbeer:thankswithbeer_erd.png}} |
| |
| ==== Kod SQL (PostgreSQL) bazy danych ==== |
| |
| <code sql> |
| BEGIN; |
| CREATE TABLE "beers_breweryownercontent" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "model_id" integer NOT NULL, |
| "language_id" integer NOT NULL REFERENCES "multilanguage_language" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "desc" text NOT NULL, |
| "status" boolean NOT NULL |
| ) |
| ; |
| CREATE TABLE "beers_breweryowner" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "name" varchar(150) NOT NULL, |
| "brewery_owner_id" integer |
| ) |
| ; |
| ALTER TABLE "beers_breweryownercontent" ADD CONSTRAINT "model_id_refs_id_598fc7fb" FOREIGN KEY ("model_id") REFERENCES "beers_breweryowner" ("id") DEFERRABLE INITIALLY DEFERRED; |
| ALTER TABLE "beers_breweryowner" ADD CONSTRAINT "brewery_owner_id_refs_id_3d4e3b7b" FOREIGN KEY ("brewery_owner_id") REFERENCES "beers_breweryowner" ("id") DEFERRABLE INITIALLY DEFERRED; |
| CREATE TABLE "beers_brewerycontent" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "model_id" integer NOT NULL, |
| "language_id" integer NOT NULL REFERENCES "multilanguage_language" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "desc" text NOT NULL, |
| "status" boolean NOT NULL |
| ) |
| ; |
| CREATE TABLE "beers_brewery" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "name" varchar(150) NOT NULL, |
| "brewery_owner_id" integer REFERENCES "beers_breweryowner" ("id") DEFERRABLE INITIALLY DEFERRED |
| ) |
| ; |
| ALTER TABLE "beers_brewerycontent" ADD CONSTRAINT "model_id_refs_id_2e86a681" FOREIGN KEY ("model_id") REFERENCES "beers_brewery" ("id") DEFERRABLE INITIALLY DEFERRED; |
| CREATE TABLE "beers_beercontent" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "model_id" integer NOT NULL, |
| "language_id" integer NOT NULL REFERENCES "multilanguage_language" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "desc" text NOT NULL, |
| "status" boolean NOT NULL |
| ) |
| ; |
| CREATE TABLE "beers_beer" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "name" varchar(150) NOT NULL, |
| "brewery_id" integer NOT NULL REFERENCES "beers_brewery" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "price_eur" double precision NOT NULL, |
| "lovers" integer NOT NULL, |
| "haters" integer NOT NULL, |
| "logo" varchar(100) NOT NULL, |
| "bottle" varchar(100), |
| "glass" varchar(100) |
| ) |
| ; |
| ALTER TABLE "beers_beercontent" ADD CONSTRAINT "model_id_refs_id_38ce73d1" FOREIGN KEY ("model_id") REFERENCES "beers_beer" ("id") DEFERRABLE INITIALLY DEFERRED; |
| CREATE TABLE "beers_hatedbeerratingcontent" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "model_id" integer NOT NULL, |
| "language_id" integer NOT NULL REFERENCES "multilanguage_language" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "name" varchar(100) NOT NULL |
| ) |
| ; |
| CREATE TABLE "beers_hatedbeerrating" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "value" integer NOT NULL |
| ) |
| ; |
| ALTER TABLE "beers_hatedbeerratingcontent" ADD CONSTRAINT "model_id_refs_id_110e7a67" FOREIGN KEY ("model_id") REFERENCES "beers_hatedbeerrating" ("id") DEFERRABLE INITIALLY DEFERRED; |
| CREATE TABLE "beers_hatedbeer" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "beer_id" integer NOT NULL REFERENCES "beers_beer" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "rating_id" integer NOT NULL REFERENCES "beers_hatedbeerrating" ("id") DEFERRABLE INITIALLY DEFERRED |
| ) |
| ; |
| CREATE TABLE "beers_lovedbeerratingcontent" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "model_id" integer NOT NULL, |
| "language_id" integer NOT NULL REFERENCES "multilanguage_language" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "name" varchar(100) NOT NULL |
| ) |
| ; |
| CREATE TABLE "beers_lovedbeerrating" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "value" integer NOT NULL |
| ) |
| ; |
| ALTER TABLE "beers_lovedbeerratingcontent" ADD CONSTRAINT "model_id_refs_id_4fbf0e87" FOREIGN KEY ("model_id") REFERENCES "beers_lovedbeerrating" ("id") DEFERRABLE INITIALLY DEFERRED; |
| CREATE TABLE "beers_lovedbeer" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "beer_id" integer NOT NULL REFERENCES "beers_beer" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "rating_id" integer NOT NULL REFERENCES "beers_lovedbeerrating" ("id") DEFERRABLE INITIALLY DEFERRED |
| ) |
| ; |
| CREATE TABLE "beers_beersize" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "size" double precision NOT NULL |
| ) |
| ; |
| CREATE TABLE "beers_beering" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "who_id" integer REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "whom_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "beer_id" integer NOT NULL REFERENCES "beers_beer" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "beer_size_id" integer NOT NULL REFERENCES "beers_beersize" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "referrer" varchar(255) NOT NULL, |
| "comment" varchar(160) NOT NULL, |
| "timestamp" timestamp with time zone NOT NULL, |
| "ip" varchar(15) NOT NULL |
| ) |
| ; |
| CREATE TABLE "auth_permission" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "name" varchar(50) NOT NULL, |
| "content_type_id" integer NOT NULL REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "codename" varchar(100) NOT NULL, |
| UNIQUE ("content_type_id", "codename") |
| ) |
| ; |
| CREATE TABLE "auth_group" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "name" varchar(80) NOT NULL UNIQUE |
| ) |
| ; |
| CREATE TABLE "auth_user" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "username" varchar(30) NOT NULL UNIQUE, |
| "first_name" varchar(30) NOT NULL, |
| "last_name" varchar(30) NOT NULL, |
| "email" varchar(75) NOT NULL, |
| "password" varchar(128) NOT NULL, |
| "is_staff" boolean NOT NULL, |
| "is_active" boolean NOT NULL, |
| "is_superuser" boolean NOT NULL, |
| "last_login" timestamp with time zone NOT NULL, |
| "date_joined" timestamp with time zone NOT NULL |
| ) |
| ; |
| CREATE TABLE "auth_message" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "message" text NOT NULL |
| ) |
| ; |
| CREATE TABLE "auth_group_permissions" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "group_id" integer NOT NULL REFERENCES "auth_group" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "permission_id" integer NOT NULL REFERENCES "auth_permission" ("id") DEFERRABLE INITIALLY DEFERRED, |
| UNIQUE ("group_id", "permission_id") |
| ) |
| ; |
| CREATE TABLE "auth_user_groups" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "group_id" integer NOT NULL REFERENCES "auth_group" ("id") DEFERRABLE INITIALLY DEFERRED, |
| UNIQUE ("user_id", "group_id") |
| ) |
| ; |
| CREATE TABLE "auth_user_user_permissions" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, |
| "permission_id" integer NOT NULL REFERENCES "auth_permission" ("id") DEFERRABLE INITIALLY DEFERRED, |
| UNIQUE ("user_id", "permission_id") |
| ) |
| ; |
| CREATE TABLE "multilanguage_language" ( |
| "id" serial NOT NULL PRIMARY KEY, |
| "code" varchar(5) NOT NULL, |
| "name" varchar(16) NOT NULL |
| ) |
| ; |
| COMMIT; |
| </code> |
| |
| ==== Wygenerowany dokładny ERD z SQL ==== |
| |
| {{:pl:dydaktyka:ztb:2010:projekty:thankswithbeer:sql_erd.jpg|}} |
| |
==== Projekt diagramów STD ==== | ==== Projekt diagramów STD ==== |
| {{:pl:dydaktyka:ztb:2010:projekty:thankswithbeer:twb_std_uzytkownik.png|}}{{:pl:dydaktyka:ztb:2010:projekty:thankswithbeer:twb_std_gosc.png|}} |
| |
| ==== Wielojęzyczność ==== |
| |
| W projekcie zastosowaliśmy ulepszone rozwiązanie [[http://code.google.com/p/django-multilingual-model/|multilingualmodel]]. Ulepszenie polegało głównie na lepszej integracji z wbudowanym panelem administracyjnym Django. |
| |
| Całość działania systemu wielojęzykowego obrazuje przykładowy diagram ERD: |
| |
| {{:pl:dydaktyka:ztb:2010:projekty:thankswithbeer:language_erd.jpg|}} |
| |
| Na obrazku widzimy encję BEERS_BEER (unikalny wpis w bazie, który przechowuje główne informacje dla encji - informacje nie związane z językiem). Dla encji tej mamy powiązanie FK (model_id) w encji BEERS_BEERCONTENT, które zawiera pola do tłumaczenia - w przykładzie jedynie pole DESC i STATUS są tymi polami. |
| |
| Do całości brakuje jeszcze przyporządkowania języka dla danego wpisu w tabeli *CONTENT - zupełnie osobna encją MULTILANGUAGE_LANGUAGE. |
| |
| Takie podwójne tworzenie modeli (jeden podstawowy oraz drugi z powiązany z tłumaczeniami pól) stosujemy dla każdej encji wielojęzycznej. Dzięki takiemu rozwiązaniu możemy dodawać kolejne wersje językowe bez konieczności definiowania ich z góry - nic nie stoi na przeszkodzie aby dodać nowy język do tabeli MULTILANGUAGE_LANGUAGE, a następnie dodać wpis w BEERS_BEERCONTENT |
| |
| Aby zachować spójność danych można dodać jeszcze klucz UNIQUE(language, model_id) do każdego modelu *CONTENT, dzięki czemu na 100% nie będziemy mieli 2 wersji tłumaczeń tego samego języka. |