· 9 years ago · Oct 04, 2016, 04:40 PM
1DROP SCHEMA public C ADE;
2CREATE SCHEMA public;
3COMMENT ON SCHEMA public IS 'standard public schema';
4DROP TABLE IF EXISTS "User" ;
5CREATE TABLE IF NOT EXISTS "User" (
6 "idUser" SERIAL NOT NULL ,
7 "name" VARCHAR(45) NOT NULL ,
8 "streetname" VARCHAR(45) NOT NULL ,
9 "city" VARCHAR(45) NOT NULL ,
10 "zipcode" VARCHAR(45) NOT NULL ,
11 "country" VARCHAR(45) NOT NULL ,
12 "province" VARCHAR(45) NOT NULL ,
13 PRIMARY KEY ("idUser") )
14;
15
16
17-- -----------------------------------------------------
18-- Table "Login"
19-- -----------------------------------------------------
20DROP TABLE IF EXISTS "Login" ;
21
22CREATE TABLE IF NOT EXISTS "Login" (
23 "idUser" INT NOT NULL ,
24 "password" VARCHAR(45) NOT NULL ,
25 "email" VARCHAR(45) NOT NULL ,
26 "activation" VARCHAR(45) NOT NULL ,
27 "activated" BOOLEAN NOT NULL ,
28 PRIMARY KEY ("idUser"));
29
30
31-- -----------------------------------------------------
32-- Table "Role"
33-- -----------------------------------------------------
34DROP TABLE IF EXISTS "Role" ;
35
36CREATE TABLE IF NOT EXISTS "Role" (
37 "idRole" INT NOT NULL ,
38 "description" VARCHAR(45) NOT NULL ,
39 PRIMARY KEY ("idRole") )
40;
41
42
43-- -----------------------------------------------------
44-- Table "userRole"
45-- -----------------------------------------------------
46DROP TABLE IF EXISTS "userRole" ;
47
48CREATE TABLE IF NOT EXISTS "userRole" (
49 "idUser" INT NOT NULL ,
50 "idRole" INT NOT NULL ,
51 PRIMARY KEY ("idUser") ,
52 CONSTRAINT "fk_Role"
53 FOREIGN KEY ("idRole" )
54 REFERENCES "Role" ("idRole" )
55 ON DELETE NO ACTION
56 ON UPDATE NO ACTION);
57
58
59INSERT INTO "Role" ("idRole", "description") VALUES (1, 'Customer');
60INSERT INTO "Role" ("idRole", "description") VALUES (2, 'Admin');
61INSERT INTO "Login" ("idUser", "password", "email", "activation", "activated") VALUES (1, 'Karel', 'test@mail.com', 'aaaabbb', FALSE);
62
63
64
65
66-- -----------------------------------------------------
67
68-- Table "Order"
69
70-- -----------------------------------------------------
71
72CREATE TABLE IF NOT EXISTS "Order" (
73 "idOrder" VARCHAR(45) NOT NULL ,
74 "orderDate" TIMESTAMP NOT NULL ,
75 "orderPrice" FLOAT NOT NULL ,
76 "orderState" BOOLEAN NOT NULL ,
77 "orderPaid" BOOLEAN NOT NULL ,
78 PRIMARY KEY ("idOrder") );
79
80
81
82
83
84-- -----------------------------------------------------
85
86-- Table "Ticket"
87
88-- -----------------------------------------------------
89
90CREATE TABLE IF NOT EXISTS "Ticket" (
91 "idTicket" INT NOT NULL ,
92 "ticketIn" BOOLEAN NOT NULL ,
93 "ticketDay" DATE NOT NULL ,
94 "barcode" VARCHAR(45) NOT NULL ,
95 PRIMARY KEY ("idTicket") )
96;
97
98
99
100
101
102-- -----------------------------------------------------
103
104-- Table "orderTickets"
105
106-- -----------------------------------------------------
107
108CREATE TABLE IF NOT EXISTS "orderTickets" (
109 "idTicket" INT NOT NULL ,
110 "idOrder" VARCHAR(45) NOT NULL ,
111 PRIMARY KEY ("idTicket")
112 );
113
114
115
116
117-- -----------------------------------------------------
118
119-- Table "userOrders"
120
121-- -----------------------------------------------------
122
123CREATE TABLE IF NOT EXISTS "userOrders" (
124
125 "idUser" INT NOT NULL ,
126 "idOrder" VARCHAR(45) NOT NULL ,
127 PRIMARY KEY ("idOrder"));
128
129
130
131
132-- -----------------------------------------------------
133
134-- Table "Meal"
135
136-- -----------------------------------------------------
137
138CREATE TABLE IF NOT EXISTS "Meal" (
139
140 "idMeal" INT NOT NULL ,
141 "foodType" BOOLEAN NOT NULL ,
142 "barcode" VARCHAR(45) NOT NULL ,
143 "foodtime" BOOLEAN NOT NULL ,
144 "mealDate" DATE NOT NULL ,
145 "used" BOOLEAN NOT NULL ,
146 PRIMARY KEY ("idMeal") );
147
148 ALTER TABLE "orderMeals"
149 ADD FOREIGN KEY ("idOrders") REFERENCES "Order" ("idOrder");
150 ALTER TABLE "orderMeals"
151 ADD FOREIGN KEY ("idMeals") REFERENCES "Meal" ("idMeal");
152
153
154
155
156-- -----------------------------------------------------
157
158-- Table "orderMeals"
159
160-- -----------------------------------------------------
161
162CREATE TABLE IF NOT EXISTS "orderMeals" (
163
164 "idMeals" INT NOT NULL ,
165"idOrders" VARCHAR(45) NOT NULL ,
166 PRIMARY KEY ("idMeals"));
167 ALTER TABLE "orderMeals"
168 ADD FOREIGN KEY ("idOrders") REFERENCES "Order" ("idOrder");
169 ALTER TABLE "orderMeals"
170 ADD FOREIGN KEY ("idMeals") REFERENCES "Meal" ("idMeal");
171
172
173
174 CREATE TABLE IF NOT EXISTS "userOrders" (
175
176 "idUser" INT NOT NULL ,
177 "idOrder" VARCHAR(45) NOT NULL ,
178 PRIMARY KEY ("idOrder"));
179
180 ALTER TABLE "userOrders"
181 ADD FOREIGN KEY ("idOrder") REFERENCES "Order" ("idOrder");
182 ALTER TABLE "userOrders"
183 ADD FOREIGN KEY ("idUser") REFERENCES "User" ("idUser");
184
185
186
187
188CREATE TABLE IF NOT EXISTS "orderMeals" (
189
190 "idMeals" INT NOT NULL ,
191"idOrders" VARCHAR(45) NOT NULL ,
192 PRIMARY KEY ("idMeals"));
193
194 ALTER TABLE "orderMeals"
195 ADD FOREIGN KEY ("idOrders") REFERENCES "Order" ("idOrder");
196 ALTER TABLE "orderMeals"
197 ADD FOREIGN KEY ("idMeals") REFERENCES "Meal" ("idMeal");
198
199CREATE TABLE IF NOT EXISTS "orderTickets" (
200 "idTicket" INT NOT NULL ,
201 "idOrder" VARCHAR(45) NOT NULL ,
202 PRIMARY KEY ("idTicket")
203 );
204
205 ALTER TABLE "orderTickets"
206 ADD FOREIGN KEY ("idOrder") REFERENCES "Order" ("idOrder");
207 ALTER TABLE "orderTickets"
208 ADD FOREIGN KEY ("idTicket") REFERENCES "Ticket" ("idTicket");
209
210-- -----------------------------------------------------
211-- Table "TicketSales"
212-- -----------------------------------------------------
213CREATE TABLE IF NOT EXISTS "TicketSales" (
214 "Friday" INT NOT NULL ,
215 "Saturday" INT NOT NULL ,
216 "Sunday" INT NOT NULL );
217
218
219-- -----------------------------------------------------
220-- Data for table "TicketSales"
221-- -----------------------------------------------------
222
223INSERT INTO "TicketSales" ("Friday", "Saturday", "Sunday") VALUES (250, 250, 250);
224
225-- -----------------------------------------------------
226-- Data for table "Order"
227-- -----------------------------------------------------
228INSERT INTO "Order" ("idOrder", "orderDate", "orderPrice", "orderState", "orderPaid") VALUES ('1', '2015-12-22 03:51 PM', 13, FALSE, FALSE);
229
230
231
232-- -----------------------------------------------------z
233-- Data for table "Ticket"
234-- -----------------------------------------------------
235INSERT INTO "Ticket" ("idTicket", "ticketIn", "ticketDay", "barcode") VALUES (1, FALSE, '2016-12-16', '12016-12-16');
236INSERT INTO "Ticket" ("idTicket", "ticketIn", "ticketDay", "barcode") VALUES (2, FALSE, '2016-12-16', '22016-12-16');
237INSERT INTO "Ticket" ("idTicket", "ticketIn", "ticketDay", "barcode") VALUES (3, FALSE, '2016-12-17', '32016-12-16');
238INSERT INTO "Ticket" ("idTicket", "ticketIn", "ticketDay", "barcode") VALUES (4, FALSE, '2016-12-18', '42016-12-16');
239
240
241-- -----------------------------------------------------
242-- Data for table "orderTickets"
243-- -----------------------------------------------------
244INSERT INTO "orderTickets" ("idTicket", "idOrder") VALUES (1, '1');
245INSERT INTO "orderTickets" ("idTicket", "idOrder") VALUES (2, '1');
246INSERT INTO "orderTickets" ("idTicket", "idOrder") VALUES (3, '1');
247
248
249-- -----------------------------------------------------
250-- Data for table "userOrders"
251-- -----------------------------------------------------
252INSERT INTO "userOrders" ("idUser", "idOrder") VALUES (1, '1');
253
254
255-- -----------------------------------------------------
256-- Data for table "Meal"
257-- -----------------------------------------------------
258INSERT INTO "Meal" ("idMeal", "foodType", "barcode", "foodtime", "mealDate", "used") VALUES (1, TRUE, '122016-12-16', TRUE, '2016-12-16', FALSE);
259INSERT INTO "Meal" ("idMeal", "foodType", "barcode", "foodtime", "mealDate", "used") VALUES (2, FALSE, '222016-12-16', FALSE, '2016-12-18', FALSE);
260
261
262-- -----------------------------------------------------
263-- Data for table "orderMeals"
264-- -----------------------------------------------------
265INSERT INTO "orderMeals" ("idMeals", "idOrders") VALUES (1, '1');
266INSERT INTO "orderMeals" ("idMeals", "idOrders") VALUES (2, '1');
267
268
269-- -----------------------------------------------------
270-- Data for table "TicketSales"
271-- -----------------------------------------------------
272INSERT INTO "TicketSales" ("Friday", "Saturday", "Sunday") VALUES (250, 250, 250);