aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorJesse Luehrs <doy@tozt.net>2019-08-23 00:50:15 -0400
committerJesse Luehrs <doy@tozt.net>2019-08-23 00:50:15 -0400
commit51ff59228b2bbc3a92d9b3e867abb1144059034d (patch)
treef4374878df6a04ce83883539467a626700641a5b
parentc0a0be3ecf2ddf0842173cb68002738b033895ff (diff)
downloadynab-export-51ff59228b2bbc3a92d9b3e867abb1144059034d.tar.gz
ynab-export-51ff59228b2bbc3a92d9b3e867abb1144059034d.zip
add helpers for loading data into the db
-rwxr-xr-xbin/load7
-rw-r--r--data/schema.sql56
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)
+);