-- 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, users.username AS username, 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, SUM(sales.best_offer + sales.charges) AS open_bids FROM sales WHERE end_date > current_datetime() GROUP BY best_bidder_uuid ) sales ON sales.best_bidder_uuid = users.uuid; INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE) VALUES ('0C99BEF9-375F-3E4A-E050-D7936EBE2782', 'duf', 'duf@gmail.com', '123456', TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Francis', 'Dumas', 'FR', '86544', '123 rue Bidon', '0123456789', TO_TIMESTAMP_TZ('2001-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF')); INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE) VALUES ('0C99BEF9-3760-3E4A-E050-D7936EBE2782', 'johnny', 'johnny.martin@wanadoo.fr', 'azerty', TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Johnny', 'Martin', 'FR', '75001', '78 Rue du Faubourg Saint-Honoré', '0000000000', TO_TIMESTAMP_TZ('2015-01-06 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF')); INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE) VALUES ('0C99BEF9-3762-3E4A-E050-D7936EBE2782', 'Lefe', 'lefe@outlook.com', 'mbay', TO_TIMESTAMP_TZ('2000-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Estelle', 'Lefebvre', 'FR', '77600', '82, rue de la Place', '0102030405', TO_TIMESTAMP_TZ('2015-01-16 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF')); INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', TO_TIMESTAMP_TZ('2013-01-01 08:39:34.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 18000.00, 'VIR'); INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', TO_TIMESTAMP_TZ('2013-05-02 08:47:26.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 9500.00, 'VIR'); INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', TO_TIMESTAMP_TZ('2013-03-01 08:50:15.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 30000.00, 'VIR'); INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES ('0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', TO_TIMESTAMP_TZ('2013-03-15 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), TO_TIMESTAMP_TZ('2013-07-01 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Audi A3', 'Voiture', 'Bonne voiture bon état pas cher', 12900.00); INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES ('0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '0C99BEF9-3760-3E4A-E050-D7936EBE2782', TO_TIMESTAMP_TZ('2013-04-25 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), TO_TIMESTAMP_TZ('2013-05-25 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 'Harley-Davidson', 'Moto', 'Alors une moto ça roule sur deux roues.', 25000.00); INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) VALUES ('0CA2E8F1-D1EF-49B7-E050-D7936EBE6A05', TO_TIMESTAMP_TZ('2013-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), TO_TIMESTAMP_TZ('2013-12-31 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 4.00, 0.25); INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) VALUES ('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', TO_TIMESTAMP_TZ('2014-01-01 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), TO_TIMESTAMP_TZ('2014-12-31 23:59:59.999999', 'YYYY-MM-DD Hh24:MI:SS.FF'), 4.20, 0.27); INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES ('0CA2E8F1-D1F1-49B7-E050-D7936EBE6A05', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2013-05-05 23:48:16.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 26000.00); INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES ('0CA2E8F1-D1F2-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2012-05-03 23:50:05.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 25000.00); INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES ('0CA2E8F1-D1F3-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', TO_TIMESTAMP_TZ('2012-06-01 23:50:53.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 12900.00);