1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
|
-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840
-- at: 2015-01-25 15:20:45 CET
-- site: Oracle Database 11g
-- type: Oracle Database 11g
CREATE TABLE bids
(
uuid VARCHAR(36) NOT NULL,
item_uuid VARCHAR(36) NOT NULL,
user_uuid VARCHAR(36) NOT NULL,
bid_date TIMESTAMP WITH TIME ZONE NOT NULL,
offer NUMERIC(8, 2) NOT NULL
);
ALTER TABLE bids ADD CONSTRAINT bids_PK PRIMARY KEY (uuid);
CREATE TABLE charges
(
uuid VARCHAR(36) NOT NULL,
start_date TIMESTAMP WITH TIME ZONE NOT NULL,
end_date TIMESTAMP WITH TIME ZONE NOT NULL,
fee NUMERIC(8, 2) NOT NULL,
rate NUMERIC(8, 2) NOT NULL
);
ALTER TABLE charges ADD CONSTRAINT charges_PK PRIMARY KEY (uuid);
CREATE TABLE items
(
uuid VARCHAR(36) NOT NULL,
user_uuid VARCHAR(36) NOT NULL,
start_date TIMESTAMP WITH TIME ZONE NOT NULL,
end_date TIMESTAMP WITH TIME ZONE NOT NULL,
item_name VARCHAR(20) NOT NULL,
short_desc VARCHAR(30) NOT NULL,
long_desc CLOB NOT NULL,
initial_price NUMERIC(8, 2) NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_PK PRIMARY KEY (uuid);
CREATE TABLE transactions
(
uuid VARCHAR(36) NOT NULL,
user_uuid VARCHAR(36) NOT NULL,
transaction_date TIMESTAMP WITH TIME ZONE NOT NULL,
amount NUMERIC(8, 2) NOT NULL,
label VARCHAR(255) NOT NULL
);
ALTER TABLE transactions ADD CONSTRAINT transactions_PK PRIMARY KEY (uuid);
CREATE TABLE users
(
uuid VARCHAR(36) NOT NULL,
username VARCHAR(20) NOT NULL,
email VARCHAR(255) NOT NULL,
user_password VARCHAR(255) NOT NULL,
creation_date TIMESTAMP WITH TIME ZONE NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
country_code VARCHAR(2) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
address VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL,
birthdate TIMESTAMP WITH TIME ZONE NOT NULL
);
ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY (uuid);
ALTER TABLE bids ADD CONSTRAINT bids_items_FK FOREIGN KEY (item_uuid) REFERENCES items (uuid);
ALTER TABLE bids ADD CONSTRAINT bids_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);
ALTER TABLE items ADD CONSTRAINT items_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);
ALTER TABLE transactions ADD CONSTRAINT transactions_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);
-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE 5
-- CREATE INDEX 0
-- ALTER TABLE 9
-- CREATE VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 0
-- ALTER TRIGGER 0
-- CREATE COLLECTION TYPE 0
-- CREATE STRUCTURED TYPE 0
-- CREATE STRUCTURED TYPE BODY 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE MATERIALIZED VIEW 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- REDACTION POLICY 0
--
-- ERRORS 0
-- WARNINGS 0
CREATE OR REPLACE FUNCTION current_datetime
RETURN TIMESTAMP WITH TIME ZONE
AS
BEGIN
--RETURN TO_TIMESTAMP_TZ('2014-01-01', 'YYYY-MM-DD');
RETURN CURRENT_TIMESTAMP;
END;
/
CREATE OR REPLACE FUNCTION gen_uuid
RETURN VARCHAR
AS
BEGIN
RETURN regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');
END;
/
-- constraints
ALTER TABLE USERS
ADD CONSTRAINT username_unique UNIQUE (USERNAME);
-- defaults
-- oracle can not use user defined functions (gen_uuid()) as default values :c
ALTER TABLE USERS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');
ALTER TABLE BIDS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');
ALTER TABLE CHARGES
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');
ALTER TABLE ITEMS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');
ALTER TABLE TRANSACTIONS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');
COMMIT;
CREATE OR REPLACE VIEW sales AS
SELECT
items.uuid AS item_uuid,
items.user_uuid AS seller_uuid,
items.start_date AS start_date,
items.end_date AS end_date,
best_bids.uuid AS best_bid_uuid,
best_bids.user_uuid AS best_bidder_uuid,
best_bids.offer AS best_offer,
COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
FROM items
LEFT OUTER JOIN (
SELECT
DISTINCT
item_uuid,
first_value(offer)
OVER (
PARTITION BY item_uuid
ORDER BY offer DESC
) offer,
first_value(uuid)
OVER (
PARTITION BY item_uuid
ORDER BY offer DESC
) uuid,
first_value(user_uuid)
OVER (
PARTITION BY item_uuid
ORDER BY offer DESC
) user_uuid
FROM bids
) best_bids
ON best_bids.item_uuid = items.uuid
LEFT OUTER JOIN (
SELECT
start_date,
end_date,
SUM(fee) AS fee,
SUM(rate) AS rate
FROM charges
GROUP BY charges.start_date, charges.end_date
) charges
ON items.end_date BETWEEN charges.start_date AND charges.end_date;
CREATE OR REPLACE VIEW accounts AS
SELECT
users.uuid AS user_uuid,
COALESCE(transactions.balance, 0) AS balance,
COALESCE(sales.open_bids, 0) AS open_bids,
COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity
FROM users
LEFT OUTER JOIN (
SELECT
user_uuid,
SUM(transactions.amount) AS balance
FROM transactions
WHERE transaction_date < current_datetime()
GROUP BY user_uuid
) transactions
ON transactions.user_uuid = users.uuid
LEFT OUTER JOIN (
SELECT
best_bidder_uuid,
SUM(sales.best_offer + sales.charges) AS open_bids
FROM sales
WHERE end_date > current_datetime()
GROUP BY best_bidder_uuid
) sales
ON sales.best_bidder_uuid = users.uuid;
INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
VALUES ('0C99BEF9-375F-3E4A-E050-D7936EBE2782', 'duf', 'duf@gmail.com', '123456',
TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Francis', 'Dumas', 'FR', '86544',
'123 rue Bidon', '0123456789', TO_TIMESTAMP_TZ('2001-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'));
INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
VALUES ('0C99BEF9-3760-3E4A-E050-D7936EBE2782', 'johnny', 'johnny.martin@wanadoo.fr', 'azerty',
TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Johnny', 'Martin', 'FR', '75001',
'78 Rue du Faubourg Saint-Honoré', '0000000000',
TO_TIMESTAMP_TZ('2015-01-06 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'));
INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
VALUES ('0C99BEF9-3762-3E4A-E050-D7936EBE2782', 'Lefe', 'lefe@outlook.com', 'mbay',
TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Estelle', 'Lefebvre', 'FR', '77600',
'82, rue de la Place', '0102030405', TO_TIMESTAMP_TZ('2015-01-16 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'));
INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
TO_TIMESTAMP_TZ('2013-01-01 08:39:34.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 18000.00, 'VIR');
INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
TO_TIMESTAMP_TZ('2013-05-02 08:47:26.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 9500.00, 'VIR');
INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
TO_TIMESTAMP_TZ('2013-03-01 08:50:15.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 30000.00, 'VIR');
INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
('0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
TO_TIMESTAMP_TZ('2013-03-15 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
TO_TIMESTAMP_TZ('2013-07-01 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Audi A3', 'Voiture',
'Bonne voiture bon état pas cher', 12900.00);
INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
('0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '0C99BEF9-3760-3E4A-E050-D7936EBE2782',
TO_TIMESTAMP_TZ('2013-04-25 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
TO_TIMESTAMP_TZ('2013-05-25 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Harley-Davidson', 'Moto',
'Alors une moto ça roule sur deux roues.', 25000.00);
INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) VALUES
('0CA2E8F1-D1EF-49B7-E050-D7936EBE6A05', TO_TIMESTAMP_TZ('2013-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
TO_TIMESTAMP_TZ('2013-12-31 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 4.00, 0.25);
INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) VALUES
('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', TO_TIMESTAMP_TZ('2014-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
TO_TIMESTAMP_TZ('2014-12-31 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 4.20, 0.27);
INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
('0CA2E8F1-D1F1-49B7-E050-D7936EBE6A05', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
'0C98CCCC-219B-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2013-05-05 23:48:16.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
26000.00);
INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
('0CA2E8F1-D1F2-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
'0C98CCCC-219B-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2012-05-03 23:50:05.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
25000.00);
INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
('0CA2E8F1-D1F3-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
'0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2012-06-01 23:50:53.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'),
12900.00);
|