aboutsummaryrefslogtreecommitdiff
path: root/res/sql/tables
diff options
context:
space:
mode:
Diffstat (limited to 'res/sql/tables')
-rw-r--r--res/sql/tables/orcl_add_constraints_defaults.sql36
-rw-r--r--res/sql/tables/orcl_ddl.sql113
-rw-r--r--res/sql/tables/pg_add_constraints_defaults.sql26
-rw-r--r--res/sql/tables/pg_ddl.sql113
4 files changed, 288 insertions, 0 deletions
diff --git a/res/sql/tables/orcl_add_constraints_defaults.sql b/res/sql/tables/orcl_add_constraints_defaults.sql
new file mode 100644
index 0000000..4fd386c
--- /dev/null
+++ b/res/sql/tables/orcl_add_constraints_defaults.sql
@@ -0,0 +1,36 @@
1-- constraints
2
3ALTER TABLE USERS
4ADD CONSTRAINT username_unique UNIQUE (USERNAME);
5
6
7-- defaults
8
9-- oracle can not use user defined functions (gen_uuid()) as default values :c
10
11ALTER TABLE USERS
12MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
13, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
14, '\1-\2-\3-\4-\5');
15
16ALTER TABLE BIDS
17MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
18, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
19, '\1-\2-\3-\4-\5');
20
21ALTER TABLE CHARGES
22MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
23, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
24, '\1-\2-\3-\4-\5');
25
26ALTER TABLE ITEMS
27MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
28, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
29, '\1-\2-\3-\4-\5');
30
31ALTER TABLE TRANSACTIONS
32MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
33, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
34, '\1-\2-\3-\4-\5');
35
36COMMIT;
diff --git a/res/sql/tables/orcl_ddl.sql b/res/sql/tables/orcl_ddl.sql
new file mode 100644
index 0000000..78afd5c
--- /dev/null
+++ b/res/sql/tables/orcl_ddl.sql
@@ -0,0 +1,113 @@
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
diff --git a/res/sql/tables/pg_add_constraints_defaults.sql b/res/sql/tables/pg_add_constraints_defaults.sql
new file mode 100644
index 0000000..df9250d
--- /dev/null
+++ b/res/sql/tables/pg_add_constraints_defaults.sql
@@ -0,0 +1,26 @@
1-- constraints
2
3ALTER TABLE USERS
4ADD CONSTRAINT username_unique UNIQUE (USERNAME);
5
6
7-- defaults
8
9CREATE EXTENSION "uuid-ossp";
10
11ALTER TABLE USERS
12ALTER COLUMN UUID SET DEFAULT gen_uuid();
13
14ALTER TABLE BIDS
15ALTER COLUMN UUID SET DEFAULT gen_uuid();
16
17ALTER TABLE CHARGES
18ALTER COLUMN UUID SET DEFAULT gen_uuid();
19
20ALTER TABLE ITEMS
21ALTER COLUMN UUID SET DEFAULT gen_uuid();
22
23ALTER TABLE TRANSACTIONS
24ALTER COLUMN UUID SET DEFAULT gen_uuid();
25
26COMMIT;
diff --git a/res/sql/tables/pg_ddl.sql b/res/sql/tables/pg_ddl.sql
new file mode 100644
index 0000000..0d8b17c
--- /dev/null
+++ b/res/sql/tables/pg_ddl.sql
@@ -0,0 +1,113 @@
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 TEXT 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