
diff -r 494b3b9db463 -r eb207b189332 src/main/resources/db/db-schema.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/resources/db/db-schema.sql	Thu May 03 09:22:48 2012 +0200
@@ -0,0 +1,236 @@
+-- table declarations :
+create table "code_list_item" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "i18n" boolean not null,
+    "rank" integer not null,
+    "dflt" boolean not null,
+    "code_list" varchar(40) not null,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint,
+    "i1" integer not null,
+    "i2" integer not null,
+    "i3" integer not null,
+    "l1" bigint not null,
+    "l2" bigint not null,
+    "l3" bigint not null,
+    "s1" varchar(200) not null,
+    "s2" varchar(200) not null,
+    "s3" varchar(200) not null,
+    "oi1" integer,
+    "oi2" integer,
+    "oi3" integer,
+    "ol1" bigint,
+    "ol2" bigint,
+    "ol3" bigint,
+    "os1" varchar(200),
+    "os2" varchar(200),
+    "os3" varchar(200),
+    "deleted" boolean not null
+  );
+create sequence "code_list_item_id_seq";
+-- indexes on code_list_item
+create index "code_list_item_code_list_idx" on "code_list_item" ("code_list");
+create table "city" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "note" varchar(10240),
+    "country_id" bigint not null,
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "city_id_seq";
+create table "address" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "street_name" varchar(100) not null,
+    "street_num" varchar(100) not null,
+    "zip_code" varchar(100) not null,
+    "city_id" bigint not null,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "address_id_seq";
+create table "country" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "iso2" varchar(2) not null,
+    "iso3" varchar(3) not null,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "country_id_seq";
+create table "location" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "address_id" bigint not null,
+    "note" varchar(10240),
+    "latitude" double precision not null,
+    "longitude" double precision not null,
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "location_id_seq";
+create table "contact" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "first_name" varchar(80) not null,
+    "last_name" varchar(80) not null,
+    "position" varchar(40),
+    "note" varchar(10240),
+    "work_mail" varchar(256) not null,
+    "work_phone" varchar(40),
+    "work_mobile" varchar(40) not null,
+    "private_mail" varchar(256),
+    "private_phone" varchar(40),
+    "private_mobile" varchar(40),
+    "other_mail" varchar(256),
+    "other_mobile" varchar(40),
+    "fax" varchar(40),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "contact_id_seq";
+create table "company" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "dic" varchar(40) not null,
+    "ico" varchar(40) not null,
+    "note" varchar(10240),
+    "status" bigint not null,
+    "address_id" bigint not null,
+    "post_adress_id" bigint,
+    "pin" integer not null,
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "company_id_seq";
+create table "bank_account" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "prefix" bigint not null,
+    "number" bigint not null,
+    "bank_code" varchar(100) not null,
+    "company_id" bigint not null,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "bank_account_id_seq";
+create table "company_contact" (
+    "contact" bigint not null,
+    "entity" bigint not null
+  );
+create table "user" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "login" varchar(40) not null,
+    "note" varchar(10240),
+    "active" boolean not null,
+    "password" varchar(128) not null,
+    "deleted" boolean not null,
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "user_id_seq";
+-- indexes on user
+create index "user_login_idx" on "user" ("login");
+create table "user_contact" (
+    "contact" bigint not null,
+    "entity" bigint not null
+  );
+create table "project" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "description" varchar(40960) not null,
+    "responsible" bigint not null,
+    "deadline" timestamp not null,
+    "ident_s" varchar(256) not null,
+    "state" bigint not null,
+    "product_line" bigint,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "project_id_seq";
+create table "task" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "project_id" bigint not null,
+    "responsible" bigint not null,
+    "deadline" timestamp not null,
+    "state" bigint not null,
+    "task_type" bigint,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "task_id_seq";
+create table "comment" (
+    "id" bigint primary key not null,
+    "name" varchar(100) not null,
+    "task_id" bigint not null,
+    "note" varchar(10240),
+    "created_at" timestamp not null,
+    "created_by" bigint,
+    "updated_at" timestamp not null,
+    "updated_by" bigint
+  );
+create sequence "comment_id_seq";
+create table "project_company" (
+    "project" bigint not null,
+    "company" bigint not null
+  );
+-- foreign key constraints :
+alter table "address" add foreign key ("city_id") references "city"("id");
+alter table "city" add foreign key ("country_id") references "country"("id");
+alter table "location" add foreign key ("address_id") references "address"("id");
+alter table "company" add foreign key ("status") references "code_list_item"("id");
+alter table "company" add foreign key ("address_id") references "address"("id");
+alter table "company" add foreign key ("post_adress_id") references "address"("id") on delete set null;
+alter table "bank_account" add foreign key ("company_id") references "company"("id") on delete cascade;
+alter table "project" add foreign key ("responsible") references "user"("id");
+alter table "project" add foreign key ("product_line") references "code_list_item"("id") on delete set null;
+alter table "project" add foreign key ("state") references "code_list_item"("id");
+alter table "task" add foreign key ("responsible") references "user"("id");
+alter table "task" add foreign key ("task_type") references "code_list_item"("id");
+alter table "task" add foreign key ("state") references "code_list_item"("id");
+alter table "task" add foreign key ("project_id") references "project"("id");
+alter table "comment" add foreign key ("task_id") references "task"("id") on delete cascade;
+alter table "company_contact" add foreign key ("entity") references "company"("id") on delete cascade;
+alter table "company_contact" add foreign key ("contact") references "contact"("id") on delete cascade;
+alter table "user_contact" add foreign key ("entity") references "user"("id") on delete cascade;
+alter table "user_contact" add foreign key ("contact") references "contact"("id") on delete cascade;
+alter table "project_company" add foreign key ("project") references "project"("id") on delete cascade;
+alter table "project_company" add foreign key ("company") references "company"("id") on delete cascade;
+-- composite key indexes :
+alter table "company_contact" add unique("entity","contact");
+alter table "user_contact" add unique("entity","contact");
+alter table "project_company" add unique("project","company");
+-- column group indexes :
+create index "user_deleted_active_idx" on "user" ("deleted","active");




