aboutsummaryrefslogtreecommitdiff
path: root/res/sql/orcl_all.sql
blob: b3b4072cc9cce0d3ecb3891d86b6e452be8d71b5 (plain)
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
302
303
304
305
306
307
-- 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.item_name                                                        AS item_name,
    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,
    items.initial_price                                                    AS initial_price,
    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,
    users.username                                                   AS username,
    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);