Add E-R model and SQL bootstrap scripts

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 @@
+-- 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;
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 @@
+-- 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
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 @@
+-- constraints
+
+ALTER TABLE USERS
+ADD CONSTRAINT username_unique UNIQUE (USERNAME);
+
+
+-- defaults
+
+CREATE EXTENSION "uuid-ossp";
+
+ALTER TABLE USERS
+ALTER COLUMN UUID SET DEFAULT gen_uuid();
+
+ALTER TABLE BIDS
+ALTER COLUMN UUID SET DEFAULT gen_uuid();
+
+ALTER TABLE CHARGES
+ALTER COLUMN UUID SET DEFAULT gen_uuid();
+
+ALTER TABLE ITEMS
+ALTER COLUMN UUID SET DEFAULT gen_uuid();
+
+ALTER TABLE TRANSACTIONS
+ALTER COLUMN UUID SET DEFAULT gen_uuid();
+
+COMMIT;
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 @@
+-- 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     TEXT                     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
+);