Last active
April 11, 2021 08:02
-
-
Save jonnyparris/f24a82dc75801691ab79d0bb997affb7 to your computer and use it in GitHub Desktop.
schema dump for db that prisma is struggling to introspect
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE pending_transactions | |
(id TEXT PRIMARY KEY, | |
acct INTEGER, | |
amount INTEGER, | |
description TEXT, | |
date TEXT, | |
FOREIGN KEY(acct) REFERENCES accounts(id)); | |
CREATE TABLE created_budgets (month TEXT PRIMARY KEY); | |
CREATE TABLE spreadsheet_cells | |
(name TEXT PRIMARY KEY, | |
expr TEXT, | |
cachedValue TEXT); | |
CREATE TABLE __migrations__ (id INT PRIMARY KEY NOT NULL); | |
CREATE TABLE banks | |
(id TEXT PRIMARY KEY, | |
bank_id TEXT, | |
name TEXT, | |
tombstone INTEGER DEFAULT 0); | |
CREATE TABLE accounts | |
(id TEXT PRIMARY KEY, | |
account_id TEXT, | |
name TEXT, | |
balance_current INTEGER, | |
balance_available INTEGER, | |
balance_limit INTEGER, | |
mask TEXT, | |
official_name TEXT, | |
type TEXT, | |
subtype TEXT, | |
bank TEXT, | |
offbudget INTEGER DEFAULT 0, | |
closed INTEGER DEFAULT 0, | |
tombstone INTEGER DEFAULT 0, sort_order REAL); | |
CREATE TABLE category_mapping | |
(id TEXT PRIMARY KEY, | |
transferId TEXT); | |
CREATE TABLE transactions | |
(id TEXT PRIMARY KEY, | |
isParent INTEGER DEFAULT 0, | |
isChild INTEGER DEFAULT 0, | |
acct TEXT, | |
category TEXT, | |
amount INTEGER, | |
description TEXT, | |
notes TEXT, | |
date INTEGER, | |
financial_id TEXT, | |
type TEXT, | |
location TEXT, | |
error TEXT, | |
imported_description TEXT, | |
starting_balance_flag INTEGER DEFAULT 0, | |
transferred_id TEXT, | |
sort_order REAL, | |
tombstone INTEGER DEFAULT 0, cleared INTEGER DEFAULT 1, pending INTEGER DEFAULT 0, parent_id TEXT); | |
CREATE TABLE messages_crdt | |
(id INTEGER PRIMARY KEY, | |
timestamp TEXT NOT NULL UNIQUE, | |
dataset TEXT NOT NULL, | |
row TEXT NOT NULL, | |
column TEXT NOT NULL, | |
value BLOB NOT NULL); | |
CREATE TABLE messages_clock (id INTEGER PRIMARY KEY, clock TEXT); | |
CREATE TABLE categories | |
(id TEXT PRIMARY KEY, | |
name TEXT, | |
is_income INTEGER DEFAULT 0, | |
cat_group TEXT, | |
sort_order REAL, | |
tombstone INTEGER DEFAULT 0); | |
CREATE TABLE payees | |
(id TEXT PRIMARY KEY, | |
name TEXT, | |
category TEXT, | |
tombstone INTEGER DEFAULT 0, | |
transfer_acct TEXT); | |
CREATE TABLE payee_rules | |
(id TEXT PRIMARY KEY, | |
payee_id TEXT, | |
type TEXT, | |
value TEXT, | |
tombstone INTEGER DEFAULT 0); | |
CREATE INDEX payee_rules_lowercase_index ON payee_rules(LOWER(value)); | |
CREATE TABLE payee_mapping | |
(id TEXT PRIMARY KEY, | |
targetId TEXT); | |
CREATE TABLE category_groups | |
(id TEXT PRIMARY KEY, | |
name TEXT, | |
is_income INTEGER DEFAULT 0, | |
sort_order REAL, | |
tombstone INTEGER DEFAULT 0); | |
CREATE INDEX trans_category_date ON transactions(category, date); | |
CREATE INDEX trans_category ON transactions(category); | |
CREATE INDEX trans_date ON transactions(date); | |
CREATE TABLE rules | |
(id TEXT PRIMARY KEY, | |
stage TEXT, | |
conditions TEXT, | |
actions TEXT, | |
tombstone INTEGER DEFAULT 0); | |
CREATE INDEX trans_parent_id ON transactions(parent_id); | |
CREATE INDEX trans_sorted ON transactions(date desc, starting_balance_flag, sort_order desc, id); | |
CREATE INDEX messages_crdt_search ON messages_crdt(dataset, row, column, timestamp); | |
CREATE TABLE sqlite_stat1(tbl,idx,stat); | |
CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); | |
CREATE TABLE __meta__ (key TEXT PRIMARY KEY, value TEXT); | |
CREATE VIEW v_transactions_internal AS SELECT _.id, _.isParent AS is_parent, _.isChild AS is_child, CASE WHEN _.isChild = 0 THEN NULL ELSE _.parent_id END AS parent_id, _.acct AS account, CASE WHEN _.isParent = 1 THEN NULL ELSE cm.transferId END AS category, IFNULL(_.amount, 0) AS amount, pm.targetId AS payee, _.notes, _.date, _.financial_id AS imported_id, _.error, _.imported_description AS imported_payee, _.starting_balance_flag, _.transferred_id AS transfer_id, _.sort_order, _.cleared, _.tombstone FROM transactions _ | |
LEFT JOIN category_mapping cm ON cm.id = _.category | |
LEFT JOIN payee_mapping pm ON pm.id = _.description | |
WHERE | |
_.date IS NOT NULL AND | |
_.acct IS NOT NULL AND | |
(_.isChild = 0 OR _.parent_id IS NOT NULL) | |
/* v_transactions_internal(id,is_parent,is_child,parent_id,account,category,amount,payee,notes,date,imported_id,error,imported_payee,starting_balance_flag,transfer_id,sort_order,cleared,tombstone) */; | |
CREATE VIEW v_transactions_internal_alive AS SELECT _.* FROM v_transactions_internal _ | |
LEFT JOIN transactions t2 ON (_.is_child = 1 AND t2.id = _.parent_id) | |
WHERE IFNULL(_.tombstone, 0) = 0 AND IFNULL(t2.tombstone, 0) = 0 | |
/* v_transactions_internal_alive(id,is_parent,is_child,parent_id,account,category,amount,payee,notes,date,imported_id,error,imported_payee,starting_balance_flag,transfer_id,sort_order,cleared,tombstone) */; | |
CREATE VIEW v_transactions AS SELECT _.id, _.is_parent, _.is_child, _.parent_id, a.id AS account, c.id AS category, _.amount, p.id AS payee, _.notes, _.date, _.imported_id, _.error, _.imported_payee, _.starting_balance_flag, _.transfer_id, _.sort_order, _.cleared, _.tombstone FROM v_transactions_internal_alive _ | |
LEFT JOIN payees p ON (p.id = _.payee AND p.tombstone = 0) | |
LEFT JOIN categories c ON (c.id = _.category AND c.tombstone = 0) | |
LEFT JOIN accounts a ON (a.id = _.account AND a.tombstone = 0) | |
ORDER BY _.date desc, _.starting_balance_flag, _.sort_order desc, _.id | |
/* v_transactions(id,is_parent,is_child,parent_id,account,category,amount,payee,notes,date,imported_id,error,imported_payee,starting_balance_flag,transfer_id,sort_order,cleared,tombstone) */; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment