1 -- table declarations :

     2 create table "code_list_item" (

     3     "id" bigint primary key not null,

     4     "name" varchar(100) not null,

     5     "i18n" boolean not null,

     6     "rank" integer not null,

     7     "dflt" boolean not null,

     8     "code_list" varchar(40) not null,

     9     "note" varchar(10240),

    10     "created_at" timestamp not null,

    11     "created_by" bigint,

    12     "updated_at" timestamp not null,

    13     "updated_by" bigint,

    14     "i1" integer not null,

    15     "i2" integer not null,

    16     "i3" integer not null,

    17     "l1" bigint not null,

    18     "l2" bigint not null,

    19     "l3" bigint not null,

    20     "s1" varchar(200) not null,

    21     "s2" varchar(200) not null,

    22     "s3" varchar(200) not null,

    23     "oi1" integer,

    24     "oi2" integer,

    25     "oi3" integer,

    26     "ol1" bigint,

    27     "ol2" bigint,

    28     "ol3" bigint,

    29     "os1" varchar(200),

    30     "os2" varchar(200),

    31     "os3" varchar(200),

    32     "deleted" boolean not null

    33   );

    34 create sequence "code_list_item_id_seq";

    35 -- indexes on code_list_item

    36 create index "code_list_item_code_list_idx" on "code_list_item" ("code_list");

    37 create table "city" (

    38     "id" bigint primary key not null,

    39     "name" varchar(100) not null,

    40     "note" varchar(10240),

    41     "country_id" bigint not null,

    42     "created_at" timestamp not null,

    43     "created_by" bigint,

    44     "updated_at" timestamp not null,

    45     "updated_by" bigint

    46   );

    47 create sequence "city_id_seq";

    48 create table "address" (

    49     "id" bigint primary key not null,

    50     "name" varchar(100) not null,

    51     "street_name" varchar(100) not null,

    52     "street_num" varchar(100) not null,

    53     "zip_code" varchar(100) not null,

    54     "city_id" bigint not null,

    55     "note" varchar(10240),

    56     "created_at" timestamp not null,

    57     "created_by" bigint,

    58     "updated_at" timestamp not null,

    59     "updated_by" bigint

    60   );

    61 create sequence "address_id_seq";

    62 create table "country" (

    63     "id" bigint primary key not null,

    64     "name" varchar(100) not null,

    65     "iso2" varchar(2) not null,

    66     "iso3" varchar(3) not null,

    67     "note" varchar(10240),

    68     "created_at" timestamp not null,

    69     "created_by" bigint,

    70     "updated_at" timestamp not null,

    71     "updated_by" bigint

    72   );

    73 create sequence "country_id_seq";

    74 create table "location" (

    75     "id" bigint primary key not null,

    76     "name" varchar(100) not null,

    77     "address_id" bigint not null,

    78     "note" varchar(10240),

    79     "latitude" double precision not null,

    80     "longitude" double precision not null,

    81     "created_at" timestamp not null,

    82     "created_by" bigint,

    83     "updated_at" timestamp not null,

    84     "updated_by" bigint

    85   );

    86 create sequence "location_id_seq";

    87 create table "contact" (

    88     "id" bigint primary key not null,

    89     "name" varchar(100) not null,

    90     "first_name" varchar(80) not null,

    91     "last_name" varchar(80) not null,

    92     "position" varchar(40),

    93     "note" varchar(10240),

    94     "work_mail" varchar(256) not null,

    95     "work_phone" varchar(40),

    96     "work_mobile" varchar(40) not null,

    97     "private_mail" varchar(256),

    98     "private_phone" varchar(40),

    99     "private_mobile" varchar(40),

   100     "other_mail" varchar(256),

   101     "other_mobile" varchar(40),

   102     "fax" varchar(40),

   103     "created_at" timestamp not null,

   104     "created_by" bigint,

   105     "updated_at" timestamp not null,

   106     "updated_by" bigint

   107   );

   108 create sequence "contact_id_seq";

   109 create table "company" (

   110     "id" bigint primary key not null,

   111     "name" varchar(100) not null,

   112     "dic" varchar(40) not null,

   113     "ico" varchar(40) not null,

   114     "note" varchar(10240),

   115     "status" bigint not null,

   116     "address_id" bigint not null,

   117     "post_adress_id" bigint,

   118     "pin" integer not null,

   119     "created_at" timestamp not null,

   120     "created_by" bigint,

   121     "updated_at" timestamp not null,

   122     "updated_by" bigint

   123   );

   124 create sequence "company_id_seq";

   125 create table "bank_account" (

   126     "id" bigint primary key not null,

   127     "name" varchar(100) not null,

   128     "prefix" bigint not null,

   129     "number" bigint not null,

   130     "bank_code" varchar(100) not null,

   131     "company_id" bigint not null,

   132     "note" varchar(10240),

   133     "created_at" timestamp not null,

   134     "created_by" bigint,

   135     "updated_at" timestamp not null,

   136     "updated_by" bigint

   137   );

   138 create sequence "bank_account_id_seq";

   139 create table "company_contact" (

   140     "contact" bigint not null,

   141     "entity" bigint not null

   142   );

   143 create table "user" (

   144     "id" bigint primary key not null,

   145     "name" varchar(100) not null,

   146     "login" varchar(40) not null,

   147     "note" varchar(10240),

   148     "active" boolean not null,

   149     "password" varchar(128) not null,

   150     "deleted" boolean not null,

   151     "created_at" timestamp not null,

   152     "created_by" bigint,

   153     "updated_at" timestamp not null,

   154     "updated_by" bigint

   155   );

   156 create sequence "user_id_seq";

   157 -- indexes on user

   158 create index "user_login_idx" on "user" ("login");

   159 create table "user_contact" (

   160     "contact" bigint not null,

   161     "entity" bigint not null

   162   );

   163 create table "project" (

   164     "id" bigint primary key not null,

   165     "name" varchar(100) not null,

   166     "description" varchar(40960) not null,

   167     "responsible" bigint not null,

   168     "deadline" timestamp not null,

   169     "ident_s" varchar(256) not null,

   170     "state" bigint not null,

   171     "product_line" bigint,

   172     "note" varchar(10240),

   173     "created_at" timestamp not null,

   174     "created_by" bigint,

   175     "updated_at" timestamp not null,

   176     "updated_by" bigint

   177   );

   178 create sequence "project_id_seq";

   179 create table "task" (

   180     "id" bigint primary key not null,

   181     "name" varchar(100) not null,

   182     "project_id" bigint not null,

   183     "responsible" bigint not null,

   184     "deadline" timestamp not null,

   185     "state" bigint not null,

   186     "task_type" bigint,

   187     "note" varchar(10240),

   188     "created_at" timestamp not null,

   189     "created_by" bigint,

   190     "updated_at" timestamp not null,

   191     "updated_by" bigint

   192   );

   193 create sequence "task_id_seq";

   194 create table "comment" (

   195     "id" bigint primary key not null,

   196     "name" varchar(100) not null,

   197     "task_id" bigint not null,

   198     "note" varchar(10240),

   199     "created_at" timestamp not null,

   200     "created_by" bigint,

   201     "updated_at" timestamp not null,

   202     "updated_by" bigint

   203   );

   204 create sequence "comment_id_seq";

   205 create table "project_company" (

   206     "project" bigint not null,

   207     "company" bigint not null

   208   );

   209 -- foreign key constraints :

   210 alter table "address" add foreign key ("city_id") references "city"("id");

   211 alter table "city" add foreign key ("country_id") references "country"("id");

   212 alter table "location" add foreign key ("address_id") references "address"("id");

   213 alter table "company" add foreign key ("status") references "code_list_item"("id");

   214 alter table "company" add foreign key ("address_id") references "address"("id");

   215 alter table "company" add foreign key ("post_adress_id") references "address"("id") on delete set null;

   216 alter table "bank_account" add foreign key ("company_id") references "company"("id") on delete cascade;

   217 alter table "project" add foreign key ("responsible") references "user"("id");

   218 alter table "project" add foreign key ("product_line") references "code_list_item"("id") on delete set null;

   219 alter table "project" add foreign key ("state") references "code_list_item"("id");

   220 alter table "task" add foreign key ("responsible") references "user"("id");

   221 alter table "task" add foreign key ("task_type") references "code_list_item"("id");

   222 alter table "task" add foreign key ("state") references "code_list_item"("id");

   223 alter table "task" add foreign key ("project_id") references "project"("id");

   224 alter table "comment" add foreign key ("task_id") references "task"("id") on delete cascade;

   225 alter table "company_contact" add foreign key ("entity") references "company"("id") on delete cascade;

   226 alter table "company_contact" add foreign key ("contact") references "contact"("id") on delete cascade;

   227 alter table "user_contact" add foreign key ("entity") references "user"("id") on delete cascade;

   228 alter table "user_contact" add foreign key ("contact") references "contact"("id") on delete cascade;

   229 alter table "project_company" add foreign key ("project") references "project"("id") on delete cascade;

   230 alter table "project_company" add foreign key ("company") references "company"("id") on delete cascade;

   231 -- composite key indexes :

   232 alter table "company_contact" add unique("entity","contact");

   233 alter table "user_contact" add unique("entity","contact");

   234 alter table "project_company" add unique("project","company");

   235 -- column group indexes :

   236 create index "user_deleted_active_idx" on "user" ("deleted","active");