1 files changed, 265 insertions, 0 deletions

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 Ã