Add E-R model and SQL bootstrap scripts

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 @@
+-- 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.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,
+    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,
+    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,