diff options
-rwxr-xr-x | bin/load | 7 | ||||
-rw-r--r-- | data/schema.sql | 56 |
2 files changed, 63 insertions, 0 deletions
diff --git a/bin/load b/bin/load new file mode 100755 index 0000000..eda089f --- /dev/null +++ b/bin/load @@ -0,0 +1,7 @@ +#!/bin/sh + +psql -U metabase metabase -c 'COPY accounts FROM STDIN' < accounts.tsv +psql -U metabase metabase -c 'COPY category_groups FROM STDIN' < category_groups.tsv +psql -U metabase metabase -c 'COPY categories FROM STDIN' < categories.tsv +psql -U metabase metabase -c 'COPY payees FROM STDIN' < payees.tsv +psql -U metabase metabase -c 'COPY transactions FROM STDIN' < transactions.tsv diff --git a/data/schema.sql b/data/schema.sql new file mode 100644 index 0000000..fe690be --- /dev/null +++ b/data/schema.sql @@ -0,0 +1,56 @@ +DROP TABLE IF EXISTS transactions; +DROP TABLE IF EXISTS payees; +DROP TABLE IF EXISTS categories; +DROP TABLE IF EXISTS category_groups; +DROP TABLE IF EXISTS accounts; +DROP TYPE IF EXISTS cleared_t; +DROP TYPE IF EXISTS flag_color_t; + +CREATE TABLE accounts ( + id text PRIMARY KEY, + name text NOT NULL, + on_budget boolean NOT NULL, + closed boolean NOT NULL, + balance bigint NOT NULL, + cleared_balance bigint NOT NULL, + uncleared_balance bigint NOT NULL +); + +CREATE TABLE category_groups ( + id text PRIMARY KEY, + name text NOT NULL, + hidden boolean NOT NULL +); + +CREATE TABLE categories ( + id text PRIMARY KEY, + category_group_id text REFERENCES category_groups(id) NOT NULL, + name text NOT NULL, + hidden boolean NOT NULL, + budgeted bigint NOT NULL, + activity bigint NOT NULL, + balance bigint NOT NULL +); + +CREATE TABLE payees ( + id text PRIMARY KEY, + name text NOT NULL, + transfer_account_id text REFERENCES accounts(id) +); + +CREATE TYPE cleared_t AS ENUM ('reconciled', 'cleared', 'uncleared'); +CREATE TYPE flag_color_t AS ENUM('red', 'orange', 'yellow', 'green', 'blue', 'purple'); + +CREATE TABLE transactions ( + id text PRIMARY KEY, + transaction_date date NOT NULL, + amount bigint NOT NULL, + memo text, + cleared cleared_t NOT NULL, + approved boolean NOT NULL, + flag_color flag_color_t, + account_id text REFERENCES accounts(id) NOT NULL, + payee_id text REFERENCES payees(id), + category_id text REFERENCES categories(id), + transfer_account_id text REFERENCES accounts(id) +); |