diff options
Diffstat (limited to 'res/sql/pg_all.sql')
-rw-r--r-- | res/sql/pg_all.sql | 265 |
1 files changed, 265 insertions, 0 deletions
diff --git a/res/sql/pg_all.sql b/res/sql/pg_all.sql new file mode 100644 index 0000000..b76cb88 --- /dev/null +++ b/res/sql/pg_all.sql | |||
@@ -0,0 +1,265 @@ | |||
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 | |||
9 | CREATE 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 | ); | ||
17 | ALTER TABLE bids ADD CONSTRAINT bids_PK PRIMARY KEY (uuid); | ||
18 | |||
19 | CREATE 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 | ); | ||
27 | ALTER TABLE charges ADD CONSTRAINT charges_PK PRIMARY KEY (uuid); | ||
28 | |||
29 | CREATE 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 TEXT NOT NULL, | ||
38 | initial_price NUMERIC(8, 2) NOT NULL | ||
39 | ); | ||
40 | ALTER TABLE items ADD CONSTRAINT items_PK PRIMARY KEY (uuid); | ||
41 | |||
42 | CREATE 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 | ); | ||
50 | ALTER TABLE transactions ADD CONSTRAINT transactions_PK PRIMARY KEY (uuid); | ||
51 | |||
52 | CREATE 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 | ); | ||
67 | ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY (uuid); | ||
68 | |||
69 | ALTER TABLE bids ADD CONSTRAINT bids_items_FK FOREIGN KEY (item_uuid) REFERENCES items (uuid); | ||
70 | |||
71 | ALTER TABLE bids ADD CONSTRAINT bids_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); | ||
72 | |||
73 | ALTER TABLE items ADD CONSTRAINT items_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid); | ||
74 | |||
75 | ALTER 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 | ||
114 | |||
115 | -- constraints | ||
116 | |||
117 | CREATE OR REPLACE FUNCTION current_datetime() | ||
118 | RETURNS TIMESTAMP WITH TIME ZONE | ||
119 | AS ' | ||
120 | BEGIN | ||
121 | |||
122 | --RETURN to_timestamp(''2014-01-01'', ''YYYY-MM-DD''); | ||
123 | RETURN now(); | ||
124 | |||
125 | END; | ||
126 | ' LANGUAGE plpgsql; | ||
127 | CREATE OR REPLACE FUNCTION gen_uuid() | ||
128 | RETURNS VARCHAR | ||
129 | AS ' | ||
130 | BEGIN | ||
131 | |||
132 | RETURN upper(cast(uuid_generate_v4() AS VARCHAR)); | ||
133 | |||
134 | END; | ||
135 | ' LANGUAGE plpgsql; | ||
136 | |||
137 | -- constraints | ||
138 | |||
139 | ALTER TABLE USERS | ||
140 | ADD CONSTRAINT username_unique UNIQUE (USERNAME); | ||
141 | |||
142 | |||
143 | -- defaults | ||
144 | |||
145 | CREATE EXTENSION "uuid-ossp"; | ||
146 | |||
147 | ALTER TABLE USERS | ||
148 | ALTER COLUMN UUID SET DEFAULT gen_uuid(); | ||
149 | |||
150 | ALTER TABLE BIDS | ||
151 | ALTER COLUMN UUID SET DEFAULT gen_uuid(); | ||
152 | |||
153 | ALTER TABLE CHARGES | ||
154 | ALTER COLUMN UUID SET DEFAULT gen_uuid(); | ||
155 | |||
156 | ALTER TABLE ITEMS | ||
157 | ALTER COLUMN UUID SET DEFAULT gen_uuid(); | ||
158 | |||
159 | ALTER TABLE TRANSACTIONS | ||
160 | ALTER COLUMN UUID SET DEFAULT gen_uuid(); | ||
161 | |||
162 | COMMIT; | ||
163 | CREATE OR REPLACE VIEW sales AS | ||
164 | |||
165 | SELECT | ||
166 | items.uuid AS item_uuid, | ||
167 | items.user_uuid AS seller_uuid, | ||
168 | items.start_date AS start_date, | ||
169 | items.end_date AS end_date, | ||
170 | best_bids.uuid AS best_bid_uuid, | ||
171 | best_bids.user_uuid AS best_bidder_uuid, | ||
172 | best_bids.offer AS best_offer, | ||
173 | COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges | ||
174 | |||
175 | FROM items | ||
176 | |||
177 | LEFT OUTER JOIN ( | ||
178 | SELECT DISTINCT ON (item_uuid) * | ||
179 | FROM bids | ||
180 | ORDER BY item_uuid, offer DESC | ||
181 | ) best_bids | ||
182 | ON best_bids.item_uuid = items.uuid | ||
183 | |||
184 | LEFT OUTER JOIN ( | ||
185 | SELECT | ||
186 | start_date, | ||
187 | end_date, | ||
188 | SUM(fee) AS fee, | ||
189 | SUM(rate) AS rate | ||
190 | |||
191 | FROM charges | ||
192 | GROUP BY charges.start_date, charges.end_date | ||
193 | ) charges | ||
194 | ON items.end_date BETWEEN charges.start_date AND charges.end_date; | ||
195 | CREATE OR REPLACE VIEW accounts AS | ||
196 | |||
197 | SELECT | ||
198 | users.uuid AS user_uuid, | ||
199 | COALESCE(transactions.balance, 0) AS balance, | ||
200 | COALESCE(sales.open_bids, 0) AS open_bids, | ||
201 | COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity | ||
202 | |||
203 | FROM users | ||
204 | |||
205 | LEFT OUTER JOIN ( | ||
206 | SELECT | ||
207 | user_uuid, | ||
208 | SUM(transactions.amount) AS balance | ||
209 | |||
210 | FROM transactions | ||
211 | WHERE transaction_date < current_datetime() | ||
212 | GROUP BY user_uuid | ||
213 | ) transactions | ||
214 | ON transactions.user_uuid = users.uuid | ||
215 | |||
216 | LEFT OUTER JOIN ( | ||
217 | SELECT | ||
218 | best_bidder_uuid, | ||
219 | SUM(sales.best_offer + sales.charges) AS open_bids | ||
220 | |||
221 | FROM sales | ||
222 | WHERE end_date > current_datetime() | ||
223 | GROUP BY best_bidder_uuid | ||
224 | ) sales | ||
225 | ON sales.best_bidder_uuid = users.uuid; | ||
226 | INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE) | ||
227 | VALUES | ||
228 | ('0C99BEF9-375F-3E4A-E050-D7936EBE2782', 'duf', 'duf@gmail.com', '123456', '2000-01-01 00:00:00.000000', 'Francis', | ||
229 | 'Dumas', 'FR', '86544', '123 rue Bidon', '0123456789', '2001-01-01 00:00:00.000000'); | ||
230 | INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE) | ||
231 | VALUES | ||
232 | ('0C99BEF9-3760-3E4A-E050-D7936EBE2782', 'johnny', 'johnny.martin@wanadoo.fr', 'azerty', '2000-01-01 00:00:00.000000', | ||
233 | 'Johnny', 'Martin', 'FR', '75001', '78 Rue du Faubourg Saint-Honoré', '0000000000', '2015-01-06 00:00:00.000000'); | ||
234 | INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE) | ||
235 | VALUES | ||
236 | ('0C99BEF9-3762-3E4A-E050-D7936EBE2782', 'Lefe', 'lefe@outlook.com', 'mbay', '2000-01-01 00:00:00.000000', 'Estelle', | ||
237 | 'Lefebvre', 'FR', '77600', '82, rue de la Place', '0102030405', '2015-01-16 00:00:00.000000'); | ||
238 | INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES | ||
239 | ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-01-01 08:39:34.000000', | ||
240 | 18000.00, 'VIR'); | ||
241 | INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES | ||
242 | ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-05-02 08:47:26.000000', | ||
243 | 9500.00, 'VIR'); | ||
244 | INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES | ||
245 | ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '2013-03-01 08:50:15.000000', | ||
246 | 30000.00, 'VIR'); | ||
247 | INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES |