From 51ff59228b2bbc3a92d9b3e867abb1144059034d Mon Sep 17 00:00:00 2001
From: Jesse Luehrs <doy@tozt.net>
Date: Fri, 23 Aug 2019 00:50:15 -0400
Subject: add helpers for loading data into the db

---
 bin/load        |  7 +++++++
 data/schema.sql | 56 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 63 insertions(+)
 create mode 100755 bin/load
 create mode 100644 data/schema.sql

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)
+);
-- 
cgit v1.2.3