From afe377a9b10b5c1b2badc74d4b9b636e7c4d0cbf Mon Sep 17 00:00:00 2001 From: Pacien TRAN-GIRARD Date: Tue, 27 Jan 2015 18:01:45 +0100 Subject: Add E-R model and SQL bootstrap scripts --- res/sql/data/orcl_bids.sql | 12 + res/sql/data/orcl_charges.sql | 6 + res/sql/data/orcl_items.sql | 10 + res/sql/data/orcl_transactions.sql | 38 +++ res/sql/data/orcl_users.sql | 13 + res/sql/data/pg_bids.sql | 9 + res/sql/data/pg_charges.sql | 4 + res/sql/data/pg_items.sql | 6 + res/sql/data/pg_transactions.sql | 38 +++ res/sql/data/pg_users.sql | 12 + res/sql/functions/orcl_current_datetime.sql | 10 + res/sql/functions/orcl_gen_uuid.sql | 11 + res/sql/functions/pg_current_datetime.sql | 10 + res/sql/functions/pg_gen_uuid.sql | 9 + res/sql/orcl_all.sql | 301 +++++++++++++++++++++++ res/sql/pg_all.sql | 265 ++++++++++++++++++++ res/sql/tables/orcl_add_constraints_defaults.sql | 36 +++ res/sql/tables/orcl_ddl.sql | 113 +++++++++ res/sql/tables/pg_add_constraints_defaults.sql | 26 ++ res/sql/tables/pg_ddl.sql | 113 +++++++++ res/sql/views/orcl_accounts.sql | 31 +++ res/sql/views/orcl_sales.sql | 49 ++++ res/sql/views/pg_accounts.sql | 31 +++ res/sql/views/pg_sales.sql | 32 +++ 24 files changed, 1185 insertions(+) create mode 100644 res/sql/data/orcl_bids.sql create mode 100644 res/sql/data/orcl_charges.sql create mode 100644 res/sql/data/orcl_items.sql create mode 100644 res/sql/data/orcl_transactions.sql create mode 100644 res/sql/data/orcl_users.sql create mode 100644 res/sql/data/pg_bids.sql create mode 100644 res/sql/data/pg_charges.sql create mode 100644 res/sql/data/pg_items.sql create mode 100644 res/sql/data/pg_transactions.sql create mode 100644 res/sql/data/pg_users.sql create mode 100644 res/sql/functions/orcl_current_datetime.sql create mode 100644 res/sql/functions/orcl_gen_uuid.sql create mode 100644 res/sql/functions/pg_current_datetime.sql create mode 100644 res/sql/functions/pg_gen_uuid.sql create mode 100644 res/sql/orcl_all.sql create mode 100644 res/sql/pg_all.sql create mode 100644 res/sql/tables/orcl_add_constraints_defaults.sql create mode 100644 res/sql/tables/orcl_ddl.sql create mode 100644 res/sql/tables/pg_add_constraints_defaults.sql create mode 100644 res/sql/tables/pg_ddl.sql create mode 100644 res/sql/views/orcl_accounts.sql create mode 100644 res/sql/views/orcl_sales.sql create mode 100644 res/sql/views/pg_accounts.sql create mode 100644 res/sql/views/pg_sales.sql (limited to 'res/sql') diff --git a/res/sql/data/orcl_bids.sql b/res/sql/data/orcl_bids.sql new file mode 100644 index 0000000..c46eeef --- /dev/null +++ b/res/sql/data/orcl_bids.sql @@ -0,0 +1,12 @@ +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); diff --git a/res/sql/data/orcl_charges.sql b/res/sql/data/orcl_charges.sql new file mode 100644 index 0000000..bb8d99d --- /dev/null +++ b/res/sql/data/orcl_charges.sql @@ -0,0 +1,6 @@ +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); diff --git a/res/sql/data/orcl_items.sql b/res/sql/data/orcl_items.sql new file mode 100644 index 0000000..ab17157 --- /dev/null +++ b/res/sql/data/orcl_items.sql @@ -0,0 +1,10 @@ +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); diff --git a/res/sql/data/orcl_transactions.sql b/res/sql/data/orcl_transactions.sql new file mode 100644 index 0000000..fe52e15 --- /dev/null +++ b/res/sql/data/orcl_transactions.sql @@ -0,0 +1,38 @@ +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'); + + +-- transactions for sales + +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E2F-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', + TO_TIMESTAMP_TZ('2013-05-26 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 26000.00, + 'PURCHASE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E30-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', + TO_TIMESTAMP_TZ('2013-05-26 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 6504.00, + 'CHARGES 0C98CCCC-219B-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E31-EF92-E050-D7936EBE4603', '0C99BEF9-3760-3E4A-E050-D7936EBE2782', + TO_TIMESTAMP_TZ('2013-05-26 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 26000.00, + 'SALE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF'); + +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E32-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', + TO_TIMESTAMP_TZ('2013-07-02 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 12900.00, + 'PURCHASE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E33-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', + TO_TIMESTAMP_TZ('2013-07-02 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), - 3229.00, + 'CHARGES 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E34-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', + TO_TIMESTAMP_TZ('2013-07-02 00:00:00.000000', 'YYYY-MM-DD Hh24:MI:SS.FF'), 12900.00, + 'SALE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF'); diff --git a/res/sql/data/orcl_users.sql b/res/sql/data/orcl_users.sql new file mode 100644 index 0000000..11896db --- /dev/null +++ b/res/sql/data/orcl_users.sql @@ -0,0 +1,13 @@ +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')); diff --git a/res/sql/data/pg_bids.sql b/res/sql/data/pg_bids.sql new file mode 100644 index 0000000..18d42ea --- /dev/null +++ b/res/sql/data/pg_bids.sql @@ -0,0 +1,9 @@ +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', '2013-05-05 23:48:16.000000', 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', '2012-05-03 23:50:05.000000', 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', '2012-06-01 23:50:53.000000', 12900.00); diff --git a/res/sql/data/pg_charges.sql b/res/sql/data/pg_charges.sql new file mode 100644 index 0000000..b102ff9 --- /dev/null +++ b/res/sql/data/pg_charges.sql @@ -0,0 +1,4 @@ +INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) +VALUES ('0CA2E8F1-D1EF-49B7-E050-D7936EBE6A05', '2013-01-01 00:00:00.000000', '2013-12-31 23:59:59.999999', 4.00, 0.25); +INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) +VALUES ('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', '2014-01-01 00:00:00.000000', '2014-12-31 23:59:59.999999', 4.20, 0.27); diff --git a/res/sql/data/pg_items.sql b/res/sql/data/pg_items.sql new file mode 100644 index 0000000..42413ca --- /dev/null +++ b/res/sql/data/pg_items.sql @@ -0,0 +1,6 @@ +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', '2013-03-15 00:00:00.000000', + '2013-07-01 23:59:59.999999', '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', '2013-04-25 00:00:00.000000', + '2013-05-25 23:59:59.999999', 'Harley-Davidson', 'Moto', 'Alors une moto ça roule sur deux roues.', 25000.00); diff --git a/res/sql/data/pg_transactions.sql b/res/sql/data/pg_transactions.sql new file mode 100644 index 0000000..40178bd --- /dev/null +++ b/res/sql/data/pg_transactions.sql @@ -0,0 +1,38 @@ +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-01-01 08:39:34.000000', + 18000.00, 'VIR'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-05-02 08:47:26.000000', + 9500.00, 'VIR'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '2013-03-01 08:50:15.000000', + 30000.00, 'VIR'); + + +-- transactions for sales + +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E2F-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', + '2013-05-26 00:00:00.000000', - 26000.00, + 'PURCHASE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E30-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', + '2013-05-26 00:00:00.000000', - 6504.00, + 'CHARGES 0C98CCCC-219B-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E31-EF92-E050-D7936EBE4603', '0C99BEF9-3760-3E4A-E050-D7936EBE2782', + '2013-05-26 00:00:00.000000', 26000.00, + 'SALE 0C98CCCC-219B-C9B1-E050-D7936EBE02BF'); + +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E32-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', + '2013-07-02 00:00:00.000000', - 12900.00, + 'PURCHASE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E33-EF92-E050-D7936EBE4603', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', + '2013-07-02 00:00:00.000000', - 3229.00, + 'CHARGES 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0DA506A8-8E34-EF92-E050-D7936EBE4603', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', + '2013-07-02 00:00:00.000000', 12900.00, + 'SALE 0C98CCCC-21A5-C9B1-E050-D7936EBE02BF'); diff --git a/res/sql/data/pg_users.sql b/res/sql/data/pg_users.sql new file mode 100644 index 0000000..d5bc2f9 --- /dev/null +++ b/res/sql/data/pg_users.sql @@ -0,0 +1,12 @@ +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', '2000-01-01 00:00:00.000000', 'Francis', + 'Dumas', 'FR', '86544', '123 rue Bidon', '0123456789', '2001-01-01 00:00:00.000000'); +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', '2000-01-01 00:00:00.000000', + 'Johnny', 'Martin', 'FR', '75001', '78 Rue du Faubourg Saint-Honoré', '0000000000', '2015-01-06 00:00:00.000000'); +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', '2000-01-01 00:00:00.000000', 'Estelle', + 'Lefebvre', 'FR', '77600', '82, rue de la Place', '0102030405', '2015-01-16 00:00:00.000000'); diff --git a/res/sql/functions/orcl_current_datetime.sql b/res/sql/functions/orcl_current_datetime.sql new file mode 100644 index 0000000..bf454d8 --- /dev/null +++ b/res/sql/functions/orcl_current_datetime.sql @@ -0,0 +1,10 @@ +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; +/ diff --git a/res/sql/functions/orcl_gen_uuid.sql b/res/sql/functions/orcl_gen_uuid.sql new file mode 100644 index 0000000..466fe65 --- /dev/null +++ b/res/sql/functions/orcl_gen_uuid.sql @@ -0,0 +1,11 @@ +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; +/ diff --git a/res/sql/functions/pg_current_datetime.sql b/res/sql/functions/pg_current_datetime.sql new file mode 100644 index 0000000..75b1b96 --- /dev/null +++ b/res/sql/functions/pg_current_datetime.sql @@ -0,0 +1,10 @@ +CREATE OR REPLACE FUNCTION current_datetime() + RETURNS TIMESTAMP WITH TIME ZONE +AS ' +BEGIN + +--RETURN to_timestamp(''2014-01-01'', ''YYYY-MM-DD''); + RETURN now(); + +END; +' LANGUAGE plpgsql; diff --git a/res/sql/functions/pg_gen_uuid.sql b/res/sql/functions/pg_gen_uuid.sql new file mode 100644 index 0000000..c592385 --- /dev/null +++ b/res/sql/functions/pg_gen_uuid.sql @@ -0,0 +1,9 @@ +CREATE OR REPLACE FUNCTION gen_uuid() + RETURNS VARCHAR +AS ' +BEGIN + + RETURN upper(cast(uuid_generate_v4() AS VARCHAR)); + +END; +' LANGUAGE plpgsql; 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, + 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); 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 @@ +-- 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 +); +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 + +-- constraints + +CREATE OR REPLACE FUNCTION current_datetime() + RETURNS TIMESTAMP WITH TIME ZONE +AS ' +BEGIN + + --RETURN to_timestamp(''2014-01-01'', ''YYYY-MM-DD''); + RETURN now(); + +END; +' LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION gen_uuid() + RETURNS VARCHAR +AS ' +BEGIN + + RETURN upper(cast(uuid_generate_v4() AS VARCHAR)); + +END; +' LANGUAGE plpgsql; + +-- 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; +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 ON (item_uuid) * + FROM bids + ORDER BY item_uuid, offer DESC + ) 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, + 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', '2000-01-01 00:00:00.000000', 'Francis', + 'Dumas', 'FR', '86544', '123 rue Bidon', '0123456789', '2001-01-01 00:00:00.000000'); +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', '2000-01-01 00:00:00.000000', + 'Johnny', 'Martin', 'FR', '75001', '78 Rue du Faubourg Saint-Honoré', '0000000000', '2015-01-06 00:00:00.000000'); +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', '2000-01-01 00:00:00.000000', 'Estelle', + 'Lefebvre', 'FR', '77600', '82, rue de la Place', '0102030405', '2015-01-16 00:00:00.000000'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-01-01 08:39:34.000000', + 18000.00, 'VIR'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-05-02 08:47:26.000000', + 9500.00, 'VIR'); +INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES + ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '2013-03-01 08:50:15.000000', + 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', '2013-03-15 00:00:00.000000', + '2013-07-01 23:59:59.999999', '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', '2013-04-25 00:00:00.000000', + '2013-05-25 23:59:59.999999', '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', '2013-01-01 00:00:00.000000', '2013-12-31 23:59:59.999999', 4.00, 0.25); +INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE) +VALUES ('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', '2014-01-01 00:00:00.000000', '2014-12-31 23:59:59.999999', 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', '2013-05-05 23:48:16.000000', 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', '2012-05-03 23:50:05.000000', 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', '2012-06-01 23:50:53.000000', 12900.00); 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 +); +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/views/orcl_accounts.sql b/res/sql/views/orcl_accounts.sql new file mode 100644 index 0000000..400d38f --- /dev/null +++ b/res/sql/views/orcl_accounts.sql @@ -0,0 +1,31 @@ +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, + 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; diff --git a/res/sql/views/orcl_sales.sql b/res/sql/views/orcl_sales.sql new file mode 100644 index 0000000..851d17e --- /dev/null +++ b/res/sql/views/orcl_sales.sql @@ -0,0 +1,49 @@ +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; diff --git a/res/sql/views/pg_accounts.sql b/res/sql/views/pg_accounts.sql new file mode 100644 index 0000000..400d38f --- /dev/null +++ b/res/sql/views/pg_accounts.sql @@ -0,0 +1,31 @@ +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, + 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; diff --git a/res/sql/views/pg_sales.sql b/res/sql/views/pg_sales.sql new file mode 100644 index 0000000..f36d8e7 --- /dev/null +++ b/res/sql/views/pg_sales.sql @@ -0,0 +1,32 @@ +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 ON (item_uuid) * + FROM bids + ORDER BY item_uuid, offer DESC + ) 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; -- cgit v1.2.3