diff options
Diffstat (limited to 'src/main/resources/db')
| -rw-r--r-- | src/main/resources/db/20251114-0-init-schema.sql | 53 | ||||
| -rw-r--r-- | src/main/resources/db/20251117-0-summary-view.sql | 65 |
2 files changed, 0 insertions, 118 deletions
diff --git a/src/main/resources/db/20251114-0-init-schema.sql b/src/main/resources/db/20251114-0-init-schema.sql deleted file mode 100644 index 886a961..0000000 --- a/src/main/resources/db/20251114-0-init-schema.sql +++ /dev/null @@ -1,53 +0,0 @@ --- Equity tracker schema --- SQLite does not control types so directly; but it makes it more portable to a different SQL server with specificity - --- enum as table - there may be other equity types, does not affect views directly -create table equity_type -( - equity_type_name VARCHAR(20) PRIMARY KEY NOT NULL -); - -INSERT INTO equity_type (equity_type_name) VALUES - ('STOCK'), - ('ETF'), - ('CRYPTO'); - --- deletion of equity_type does not cascade - equity_type is required for equity_symbol, which is required for equity_change_event, which is required for fiduciary_account. --- equity_symbol, equity_type would never be deleted, but they could be cleaned up if they are orphaned records. -create table equity_symbol -( - equity_symbol_id SMALLINT PRIMARY KEY NOT NULL, - equity_symbol_name VARCHAR(16) NOT NULL, - equity_symbol_type VARCHAR(20) NOT NULL - REFERENCES equity_type(equity_type_name), - equity_symbol_managing_company VARCHAR(96) NOT NULL, - equity_symbol_created_timestamp TIMESTAMP NOT NULL -); - -create table fiduciary_account -( - fiduciary_account_id TINYINT PRIMARY KEY NOT NULL, - fiduciary_account_number UNSIGNED BIG INT NOT NULL, - fiduciary_account_name NVARCHAR(96) NOT NULL, - fiduciary_account_description NVARCHAR(512), - fiduciary_account_created_timestamp TIMESTAMP NOT NULL -); - --- deletion of fiduciary_account row cascades - an account precludes change events. --- deletion of equity_symbol row does not cascade - all events should be recorded for an account, and all events must have symbols -create table equity_change_event -( - equity_change_event_id UNSIGNED BIG INT PRIMARY KEY NOT NULL, - fiduciary_account_id TINYINT NOT NULL - REFERENCES fiduciary_account(fiduciary_account_id) - ON DELETE CASCADE, - equity_symbol_id SMALLINT NOT NULL - REFERENCES equity_symbol(equity_symbol_id), - equity_change_quantity DECIMAL(20, 8) NOT NULL, - equity_change_cost_basis_usd DECIMAL(14, 2) NOT NULL, - equity_change_type VARCHAR(20) NOT NULL - CHECK (equity_change_type IN ('BUY', 'SELL')), -- This is essentially '+' or '-' - equity_change_timestamp TIMESTAMP NOT NULL, - equity_change_type_score NUMERIC AS (CASE WHEN equity_change_type = 'BUY' THEN 1.0 ELSE -1.0 END) STORED, - equity_change_est_price DECIMAL(14, 2) AS (equity_change_cost_basis_usd / equity_change_quantity) STORED -); diff --git a/src/main/resources/db/20251117-0-summary-view.sql b/src/main/resources/db/20251117-0-summary-view.sql deleted file mode 100644 index a4e8004..0000000 --- a/src/main/resources/db/20251117-0-summary-view.sql +++ /dev/null @@ -1,65 +0,0 @@ --- Yucky slow view, since it's sub-querying for each of the summation fields --- AND has a subquery on the inner join to find the right symbols. Anyways it --- should preview relative strength of treating equity transaction data as --- immutable. Any application on top of this should get the full list of --- records so it can construct helpful views of summations based on the history --- of changes, and/or show price data over time -CREATE VIEW account_symbol_summary_view -AS -SELECT - acc.fiduciary_account_id, - acc.fiduciary_account_name, - acc.fiduciary_account_description, - acc.fiduciary_account_number, - acc.fiduciary_account_created_timestamp, - sym.equity_symbol_id, - sym.equity_symbol_type, - sym.equity_symbol_name, - sym.equity_symbol_managing_company, - (SELECT - SUM(ev.equity_change_quantity * ev.equity_change_type_score) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_quantity, - (SELECT - printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score) / SUM(ev.equity_change_quantity * ev.equity_change_type_score)) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_cost_basis_price, - (SELECT - printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score)) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_cost_basis_total_usd, - (SELECT - printf( '%.2f', SUM(ev.equity_change_quantity) * ( - SELECT - iev.equity_change_est_price - FROM equity_change_event iev - WHERE iev.fiduciary_account_id = acc.fiduciary_account_id - AND iev.equity_symbol_id = sym.equity_symbol_id - ORDER BY iev.equity_change_timestamp DESC - LIMIT 1 - )) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_last_effective_price, - (SELECT - ev.equity_change_timestamp - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ORDER BY ev.equity_change_timestamp DESC - LIMIT 1 - ) AS account_symbol_last_update_timestamp -FROM fiduciary_account acc -INNER JOIN equity_symbol sym ON EXISTS( - SELECT * - FROM equity_change_event e - WHERE e.fiduciary_account_id = acc.fiduciary_account_id - AND e.equity_symbol_id = sym.equity_symbol_id -);
\ No newline at end of file |