From 615d44041d35ee1a08ddc66423f67a9d48f53ae9 Mon Sep 17 00:00:00 2001 From: Kevin Hoerr Date: Thu, 16 Apr 2026 11:37:21 -0400 Subject: Switch to distrobox as base for development --- src/main/resources/db/20251114-0-init-schema.sql | 53 ------------------------ 1 file changed, 53 deletions(-) delete mode 100644 src/main/resources/db/20251114-0-init-schema.sql (limited to 'src/main/resources/db/20251114-0-init-schema.sql') 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 -); -- cgit v1.3