Add E-R model and SQL bootstrap scripts

4 files changed, 143 insertions, 0 deletions

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;