Something went wrong. Try again.
Something went wrong. Try again.
2.0 kB · 52 lines
SQL
at main
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253CREATE TABLE IF NOT EXISTS budget_category_groups ( id INTEGER PRIMARY KEY NOT NULL, scope_kind TEXT NOT NULL, scope_id INTEGER, name TEXT NOT NULL, position INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS budget_categories ( id INTEGER PRIMARY KEY NOT NULL, group_id INTEGER NOT NULL REFERENCES budget_category_groups(id) ON DELETE CASCADE, name TEXT NOT NULL, note TEXT, tone TEXT, position INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS budget_targets ( category_id INTEGER PRIMARY KEY NOT NULL REFERENCES budget_categories(id) ON DELETE CASCADE, kind TEXT NOT NULL, amount REAL NOT NULL, by_date TEXT);
CREATE TABLE IF NOT EXISTS budget_assignments ( id INTEGER PRIMARY KEY NOT NULL, category_id INTEGER NOT NULL REFERENCES budget_categories(id) ON DELETE CASCADE, month TEXT NOT NULL, assigned REAL NOT NULL, UNIQUE(category_id, month));
CREATE TABLE IF NOT EXISTS budget_ref_type_maps ( id INTEGER PRIMARY KEY NOT NULL, scope_kind TEXT NOT NULL, scope_id INTEGER, ref_type TEXT NOT NULL, category_id INTEGER NOT NULL REFERENCES budget_categories(id) ON DELETE CASCADE);
CREATE INDEX IF NOT EXISTS idx_budget_category_groups_scope ON budget_category_groups(scope_kind, scope_id);CREATE INDEX IF NOT EXISTS idx_budget_categories_group_id ON budget_categories(group_id);CREATE INDEX IF NOT EXISTS idx_budget_assignments_category_month ON budget_assignments(category_id, month);
-- COALESCE(scope_id, -1) so an All-scope (NULL scope_id) ref_type maps to a single row;-- SQLite treats NULLs as distinct in a plain UNIQUE, which would defeat upsert for the All scope.CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_ref_type_maps_unique ON budget_ref_type_maps(scope_kind, COALESCE(scope_id, -1), ref_type);