DJ0pGhav

1# --- Created by Ebean DDL
2# To stop Ebean DDL generation, remove this comment and start using Evolutions
3
4# --- !Ups
5
6create table corporate_customer (
7  id                            integer not null,
8  corporate_id                  varchar(255),
9  company_name                  varchar(255),
10  exchange_number               varchar(255),
11  fax_number                    varchar(255),
12  contact_name                  varchar(255),
13  constraint uq_corporate_customer_corporate_id unique (corporate_id),
14  constraint pk_corporate_customer primary key (id)
15);
16create sequence corporate_customer_seq;
17
18create table customer (
19  id                            integer not null,
20  phone_number                  varchar(255),
21  cell_phone_number             varchar(255),
22  email                         varchar(255),
23  address                       varchar(255),
24  zip_code                      integer,
25  customer_type                 integer,
26  private_customer_id           integer,
27  corporate_customer_id         integer,
28  constraint uq_customer_email unique (email),
29  constraint uq_customer_private_customer_id unique (private_customer_id),
30  constraint uq_customer_corporate_customer_id unique (corporate_customer_id),
31  constraint pk_customer primary key (id)
32);
33create sequence customer_seq;
34
35create table employee (
36  id                            integer not null,
37  group_name                    varchar(255),
38  passhash                      varbinary(32),
39  firstname                     varchar(255),
40  lastname                      varchar(255),
41  email                         varchar(255),
42  constraint uq_employee_email unique (email),
43  constraint pk_employee primary key (id)
44);
45create sequence employee_seq;
46
47create table invoice (
48  id                            integer not null,
49  invoice_amount                decimal(38),
50  invoice_date                  timestamp,
51  payment_date                  timestamp,
52  project_id                    integer,
53  constraint pk_invoice primary key (id)
54);
55create sequence invoice_seq;
56
57create table private_customer (
58  id                            integer not null,
59  firstname                     varchar(255),
60  lastname                      varchar(255),
61  constraint pk_private_customer primary key (id)
62);
63create sequence private_customer_seq;
64
65create table project (
66  id                            integer not null,
67  start_time                    timestamp,
68  finished_time                 timestamp,
69  zip_code                      integer,
70  name                          varchar(255),
71  address                       varchar(255),
72  description                   TEXT,
73  city                          varchar(255),
74  constraint pk_project primary key (id)
75);
76create sequence project_seq;
77
78create table schedule (
79  id                            integer not null,
80  time                          timestamp,
81  description                   TEXT,
82  employee_id                   integer,
83  project_id                    integer,
84  constraint pk_schedule primary key (id)
85);
86create sequence schedule_seq;
87
88alter table customer add constraint fk_customer_private_customer_id foreign key (private_customer_id) references private_customer (id) on delete restrict on update restrict;
89
90alter table customer add constraint fk_customer_corporate_customer_id foreign key (corporate_customer_id) references corporate_customer (id) on delete restrict on update restrict;
91
92alter table invoice add constraint fk_invoice_project_id foreign key (project_id) references project (id) on delete restrict on update restrict;
93create index ix_invoice_project_id on invoice (project_id);
94
95alter table schedule add constraint fk_schedule_employee_id foreign key (employee_id) references employee (id) on delete restrict on update restrict;
96create index ix_schedule_employee_id on schedule (employee_id);
97
98alter table schedule add constraint fk_schedule_project_id foreign key (project_id) references project (id) on delete restrict on update restrict;
99create index ix_schedule_project_id on schedule (project_id);
100
101
102# --- !Downs
103
104alter table customer drop constraint if exists fk_customer_private_customer_id;
105
106alter table customer drop constraint if exists fk_customer_corporate_customer_id;
107
108alter table invoice drop constraint if exists fk_invoice_project_id;
109drop index if exists ix_invoice_project_id;
110
111alter table schedule drop constraint if exists fk_schedule_employee_id;
112drop index if exists ix_schedule_employee_id;
113
114alter table schedule drop constraint if exists fk_schedule_project_id;
115drop index if exists ix_schedule_project_id;
116
117drop table if exists corporate_customer;
118drop sequence if exists corporate_customer_seq;
119
120drop table if exists customer;
121drop sequence if exists customer_seq;
122
123drop table if exists employee;
124drop sequence if exists employee_seq;
125
126drop table if exists invoice;
127drop sequence if exists invoice_seq;
128
129drop table if exists private_customer;
130drop sequence if exists private_customer_seq;
131
132drop table if exists project;
133drop sequence if exists project_seq;
134
135drop table if exists schedule;
136drop sequence if exists schedule_seq;