aboutsummaryrefslogtreecommitdiff
path: root/res/sql/orcl_all.sql
diff options
context:
space:
mode:
Diffstat (limited to 'res/sql/orcl_all.sql')
-rw-r--r--res/sql/orcl_all.sql301
1 files changed, 301 insertions, 0 deletions
diff --git a/res/sql/orcl_all.sql b/res/sql/orcl_all.sql
new file mode 100644
index 0000000..b0367de
--- /dev/null
+++ b/res/sql/orcl_all.sql
@@ -0,0 +1,301 @@
1-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840
2-- at: 2015-01-25 15:20:45 CET
3-- site: Oracle Database 11g
4-- type: Oracle Database 11g
5
6
7
8
9CREATE TABLE bids
10(
11 uuid VARCHAR(36) NOT NULL,
12 item_uuid VARCHAR(36) NOT NULL,
13 user_uuid VARCHAR(36) NOT NULL,
14 bid_date TIMESTAMP WITH TIME ZONE NOT NULL,
15 offer NUMERIC(8, 2) NOT NULL
16);
17ALTER TABLE bids ADD CONSTRAINT bids_PK PRIMARY KEY (uuid);
18
19CREATE TABLE charges
20(
21 uuid VARCHAR(36) NOT NULL,
22 start_date TIMESTAMP WITH TIME ZONE NOT NULL,
23 end_date TIMESTAMP WITH TIME ZONE NOT NULL,
24 fee NUMERIC(8, 2) NOT NULL,
25 rate NUMERIC(8, 2) NOT NULL
26);
27ALTER TABLE charges ADD CONSTRAINT charges_PK PRIMARY KEY (uuid);
28
29CREATE TABLE items
30(
31 uuid VARCHAR(36) NOT NULL,
32 user_uuid VARCHAR(36) NOT NULL,
33 start_date TIMESTAMP WITH TIME ZONE NOT NULL,
34 end_date TIMESTAMP WITH TIME ZONE NOT NULL,
35 item_name VARCHAR(20) NOT NULL,
36 short_desc VARCHAR(30) NOT NULL,
37 long_desc CLOB NOT NULL,
38 initial_price NUMERIC(8, 2) NOT NULL
39);
40ALTER TABLE items ADD CONSTRAINT items_PK PRIMARY KEY (uuid);
41
42CREATE TABLE transactions
43(
44 uuid VARCHAR(36) NOT NULL,
45 user_uuid VARCHAR(36) NOT NULL,
46 transaction_date TIMESTAMP WITH TIME ZONE NOT NULL,
47 amount NUMERIC(8, 2) NOT NULL,
48 label VARCHAR(255) NOT NULL
49);
50ALTER TABLE transactions ADD CONSTRAINT transactions_PK PRIMARY KEY (uuid);
51
52CREATE TABLE users
53(
54 uuid VARCHAR(36) NOT NULL,
55 username VARCHAR(20) NOT NULL,
56 email VARCHAR(255) NOT NULL,
57 user_password VARCHAR(255) NOT NULL,
58 creation_date TIMESTAMP WITH TIME ZONE NOT NULL,
59 first_name VARCHAR(255) NOT NULL,
60 last_name VARCHAR(255) NOT NULL,
61 country_code VARCHAR(2) NOT NULL,
62 postal_code VARCHAR(20) NOT NULL,
63 address VARCHAR(255) NOT NULL,
64 phone VARCHAR(20) NOT NULL,
65 birthdate TIMESTAMP WITH TIME ZONE NOT NULL
66);
67ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY (uuid);
68
69ALTER TABLE bids ADD CONSTRAINT bids_items_FK FOREIGN KEY (item_uuid) REFERENCES items (uuid);
70
71ALTER TABLE bids ADD CONSTRAINT bids_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);
72
73ALTER TABLE items ADD CONSTRAINT items_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);
74
75ALTER TABLE transactions ADD CONSTRAINT transactions_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);
76
77
78-- Oracle SQL Developer Data Modeler Summary Report:
79--
80-- CREATE TABLE 5
81-- CREATE INDEX 0
82-- ALTER TABLE 9
83-- CREATE VIEW 0
84-- CREATE PACKAGE 0
85-- CREATE PACKAGE BODY 0
86-- CREATE PROCEDURE 0
87-- CREATE FUNCTION 0
88-- CREATE TRIGGER 0
89-- ALTER TRIGGER 0
90-- CREATE COLLECTION TYPE 0
91-- CREATE STRUCTURED TYPE 0
92-- CREATE STRUCTURED TYPE BODY 0
93-- CREATE CLUSTER 0
94-- CREATE CONTEXT 0
95-- CREATE DATABASE 0
96-- CREATE DIMENSION 0
97-- CREATE DIRECTORY 0
98-- CREATE DISK GROUP 0
99-- CREATE ROLE 0
100-- CREATE ROLLBACK SEGMENT 0
101-- CREATE SEQUENCE 0
102-- CREATE MATERIALIZED VIEW 0
103-- CREATE SYNONYM 0
104-- CREATE TABLESPACE 0
105-- CREATE USER 0
106--
107-- DROP TABLESPACE 0
108-- DROP DATABASE 0
109--
110-- REDACTION POLICY 0
111--
112-- ERRORS 0
113-- WARNINGS 0
114CREATE OR REPLACE FUNCTION current_datetime
115 RETURN TIMESTAMP WITH TIME ZONE
116AS
117 BEGIN
118
119--RETURN TO_TIMESTAMP_TZ('2014-01-01', 'YYYY-MM-DD');
120 RETURN CURRENT_TIMESTAMP;
121
122 END;
123/
124CREATE OR REPLACE FUNCTION gen_uuid
125 RETURN VARCHAR
126AS
127 BEGIN
128
129 RETURN regexp_replace(rawtohex(sys_guid())
130 , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
131 , '\1-\2-\3-\4-\5');
132
133 END;
134/
135
136-- constraints
137
138ALTER TABLE USERS
139ADD CONSTRAINT username_unique UNIQUE (USERNAME);
140
141
142-- defaults
143
144-- oracle can not use user defined functions (gen_uuid()) as default values :c
145
146ALTER TABLE USERS
147MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
148, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
149, '\1-\2-\3-\4-\5');
150
151ALTER TABLE BIDS
152MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
153, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
154, '\1-\2-\3-\4-\5');
155
156ALTER TABLE CHARGES
157MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
158, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
159, '\1-\2-\3-\4-\5');
160
161ALTER TABLE ITEMS
162MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
163, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
164, '\1-\2-\3-\4-\5');
165
166ALTER TABLE TRANSACTIONS
167MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
168, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
169, '\1-\2-\3-\4-\5');
170
171COMMIT;
172CREATE OR REPLACE VIEW sales AS
173
174 SELECT
175 items.uuid AS item_uuid,
176 items.user_uuid AS seller_uuid,
177 items.start_date AS start_date,
178 items.end_date AS end_date,
179 best_bids.uuid AS best_bid_uuid,
180 best_bids.user_uuid AS best_bidder_uuid,
181 best_bids.offer AS best_offer,
182 COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges
183
184 FROM items
185
186 LEFT OUTER JOIN (
187 SELECT
188 DISTINCT
189 item_uuid,
190 first_value(offer)
191 OVER (
192 PARTITION BY item_uuid
193 ORDER BY offer DESC
194 ) offer,
195 first_value(uuid)
196 OVER (
197 PARTITION BY item_uuid
198 ORDER BY offer DESC
199 ) uuid,
200 first_value(user_uuid)
201 OVER (
202 PARTITION BY item_uuid
203 ORDER BY offer DESC
204 ) user_uuid
205
206 FROM bids
207 ) best_bids
208 ON best_bids.item_uuid = items.uuid
209
210 LEFT OUTER JOIN (
211 SELECT
212 start_date,
213 end_date,
214 SUM(fee) AS fee,
215 SUM(rate) AS rate
216
217 FROM charges
218 GROUP BY charges.start_date, charges.end_date
219 ) charges
220 ON items.end_date BETWEEN charges.start_date AND charges.end_date;
221CREATE OR REPLACE VIEW accounts AS
222
223 SELECT
224 users.uuid AS user_uuid,
225 COALESCE(transactions.balance, 0) AS balance,
226 COALESCE(sales.open_bids, 0) AS open_bids,
227 COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity
228
229 FROM users
230
231 LEFT OUTER JOIN (
232 SELECT
233 user_uuid,
234 SUM(transactions.amount) AS balance
235
236 FROM transactions
237 WHERE transaction_date < current_datetime()
238 GROUP BY user_uuid
239 ) transactions
240 ON transactions.user_uuid = users.uuid
241
242 LEFT OUTER JOIN (
243 SELECT
244 best_bidder_uuid,
245 SUM(sales.best_offer + sales.charges) AS open_bids
246
247 FROM sales
248 WHERE end_date > current_datetime()
249 GROUP BY best_bidder_uuid
250 ) sales