From c7815088622d39b8792c072366c72836fce3a932 Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Tue, 27 Aug 2019 01:55:59 -0400 Subject: make load script handle the whole database --- bin/load | 26 ++++++++---- data/investments-sheet-schema.sql | 18 ++++++++ data/schema.sql | 18 -------- src/bin/investments-sheet-export.rs | 83 +++++++++++++++++++++++++++++++++++++ src/main.rs | 82 ------------------------------------ 5 files changed, 119 insertions(+), 108 deletions(-) create mode 100644 data/investments-sheet-schema.sql delete mode 100644 data/schema.sql create mode 100644 src/bin/investments-sheet-export.rs delete mode 100644 src/main.rs diff --git a/bin/load b/bin/load index 1f9fa72..6fa7777 100755 --- a/bin/load +++ b/bin/load @@ -1,14 +1,24 @@ #!/bin/sh set -eu -cd "$(dirname "$0")/.." || exit 1 +/home/doy/.cargo/bin/ynab-export +/home/doy/.cargo/bin/ynab-export schema | psql -U metabase money -cargo build -cd data || exit 1 +seq 1000000 | psql -U metabase money -c 'COPY ints FROM STDIN' -rm -f ./*.tsv || true -../target/debug/investments-sheet-export "$@" +psql -U metabase money -c 'COPY accounts FROM STDIN' < accounts.tsv +psql -U metabase money -c 'COPY category_groups FROM STDIN' < category_groups.tsv +psql -U metabase money -c 'COPY categories FROM STDIN' < categories.tsv +psql -U metabase money -c 'COPY months FROM STDIN' < months.tsv +psql -U metabase money -c 'COPY categories_by_month FROM STDIN' < categories_by_month.tsv +psql -U metabase money -c 'COPY payees FROM STDIN' < payees.tsv +psql -U metabase money -c 'COPY transactions FROM STDIN' < transactions.tsv +psql -U metabase money -c 'COPY subtransactions FROM STDIN' < subtransactions.tsv +psql -U metabase money -c 'COPY scheduled_transactions FROM STDIN' < scheduled_transactions.tsv +psql -U metabase money -c 'COPY scheduled_subtransactions FROM STDIN' < scheduled_subtransactions.tsv -psql -U metabase investments < schema.sql -psql -U metabase investments -c 'COPY categories FROM STDIN' < categories.tsv -psql -U metabase investments -c 'COPY holdings FROM STDIN' < holdings.tsv +cargo run --manifest-path "$(dirname "$0")/../Cargo.toml" --bin investments-sheet-export "$@" +psql -U metabase money < "$(dirname "$0")/../data/investments-sheet-schema.sql" + +psql -U metabase money -c 'COPY investment_categories FROM STDIN' < investment_categories.tsv +psql -U metabase money -c 'COPY holdings FROM STDIN' < holdings.tsv diff --git a/data/investments-sheet-schema.sql b/data/investments-sheet-schema.sql new file mode 100644 index 0000000..d538ec6 --- /dev/null +++ b/data/investments-sheet-schema.sql @@ -0,0 +1,18 @@ +DROP TABLE IF EXISTS holdings; +DROP TABLE IF EXISTS categories; + +CREATE TABLE investment_categories ( + name text PRIMARY KEY, + target_allocation integer +); + +CREATE TABLE holdings ( + account text, + symbol text, + name text, + category text REFERENCES categories(name), + shares numeric(10, 3), + price numeric(10, 2), + expense_ratio numeric(5, 2), + PRIMARY KEY (account, symbol, category) +); diff --git a/data/schema.sql b/data/schema.sql deleted file mode 100644 index 2c859ef..0000000 --- a/data/schema.sql +++ /dev/null @@ -1,18 +0,0 @@ -DROP TABLE IF EXISTS holdings; -DROP TABLE IF EXISTS categories; - -CREATE TABLE categories ( - name text PRIMARY KEY, - target_allocation integer -); - -CREATE TABLE holdings ( - account text, - symbol text, - name text, - category text REFERENCES categories(name), - shares numeric(10, 3), - price numeric(10, 2), - expense_ratio numeric(5, 2), - PRIMARY KEY (account, symbol, category) -); diff --git a/src/bin/investments-sheet-export.rs b/src/bin/investments-sheet-export.rs new file mode 100644 index 0000000..0444934 --- /dev/null +++ b/src/bin/investments-sheet-export.rs @@ -0,0 +1,83 @@ +use std::io::Write; + +struct Sheet(Vec>); + +impl Sheet { + fn new(contents: &str) -> Self { + let mut rdr = csv::ReaderBuilder::new() + .has_headers(false) + .from_reader(contents.as_bytes()); + let sheet = rdr + .records() + .map(|record| { + record.unwrap().iter().map(|s| s.to_string()).collect() + }) + .collect(); + Self(sheet) + } + + #[allow(dead_code)] + fn value_at(&self, coord: &str) -> String { + let letter = coord.chars().next().unwrap(); + let num: usize = coord[1..].parse().unwrap(); + let row = num - 1; + let col = ((letter as u32) - ('A' as u32)) as usize; + let Self(sheet) = self; + sheet[row][col].clone() + } + + fn rows(&self) -> impl Iterator> + '_ { + let Self(sheet) = self; + sheet.iter() + } +} + +fn main() { + let sheets: Vec<_> = std::env::args() + .skip(1) + .map(|u| Sheet::new(&reqwest::get(&u).unwrap().text().unwrap())) + .collect(); + + let mut file = + std::fs::File::create("investment_categories.tsv").unwrap(); + for row in sheets[0].rows().skip(1) { + if row[0] == "" { + break; + } + let percentage: f64 = row[1].trim_end_matches('%').parse().unwrap(); + file.write_all( + [ + row[0].as_ref(), + format!("{}", (percentage * 100.0) as u32).as_ref(), + ] + .join("\t") + .as_bytes(), + ) + .unwrap(); + file.write_all(b"\n").unwrap(); + } + file.sync_all().unwrap(); + + let mut file = std::fs::File::create("holdings.tsv").unwrap(); + for row in sheets[1].rows().skip(2) { + if row[0] == "" { + break; + } + file.write_all( + [ + row[0].as_ref(), + if row[1] == "" { "\\N" } else { row[1].as_ref() }, + if row[2] == "" { "\\N" } else { row[2].as_ref() }, + row[3].as_ref(), + row[7].replace(['$', ','].as_ref(), "").as_ref(), + row[8].replace(['$', ','].as_ref(), "").as_ref(), + row[11].as_ref(), + ] + .join("\t") + .as_bytes(), + ) + .unwrap(); + file.write_all(b"\n").unwrap(); + } + file.sync_all().unwrap(); +} diff --git a/src/main.rs b/src/main.rs deleted file mode 100644 index d594413..0000000 --- a/src/main.rs +++ /dev/null @@ -1,82 +0,0 @@ -use std::io::Write; - -struct Sheet(Vec>); - -impl Sheet { - fn new(contents: &str) -> Self { - let mut rdr = csv::ReaderBuilder::new() - .has_headers(false) - .from_reader(contents.as_bytes()); - let sheet = rdr - .records() - .map(|record| { - record.unwrap().iter().map(|s| s.to_string()).collect() - }) - .collect(); - Self(sheet) - } - - #[allow(dead_code)] - fn value_at(&self, coord: &str) -> String { - let letter = coord.chars().next().unwrap(); - let num: usize = coord[1..].parse().unwrap(); - let row = num - 1; - let col = ((letter as u32) - ('A' as u32)) as usize; - let Self(sheet) = self; - sheet[row][col].clone() - } - - fn rows(&self) -> impl Iterator> + '_ { - let Self(sheet) = self; - sheet.iter() - } -} - -fn main() { - let sheets: Vec<_> = std::env::args() - .skip(1) - .map(|u| Sheet::new(&reqwest::get(&u).unwrap().text().unwrap())) - .collect(); - - let mut file = std::fs::File::create("categories.tsv").unwrap(); - for row in sheets[0].rows().skip(1) { - if row[0] == "" { - break; - } - let percentage: f64 = row[1].trim_end_matches('%').parse().unwrap(); - file.write_all( - [ - row[0].as_ref(), - format!("{}", (percentage * 100.0) as u32).as_ref(), - ] - .join("\t") - .as_bytes(), - ) - .unwrap(); - file.write_all(b"\n").unwrap(); - } - file.sync_all().unwrap(); - - let mut file = std::fs::File::create("holdings.tsv").unwrap(); - for row in sheets[1].rows().skip(2) { - if row[0] == "" { - break; - } - file.write_all( - [ - row[0].as_ref(), - if row[1] == "" { "\\N" } else { row[1].as_ref() }, - if row[2] == "" { "\\N" } else { row[2].as_ref() }, - row[3].as_ref(), - row[7].replace(['$', ','].as_ref(), "").as_ref(), - row[8].replace(['$', ','].as_ref(), "").as_ref(), - row[11].as_ref(), - ] - .join("\t") - .as_bytes(), - ) - .unwrap(); - file.write_all(b"\n").unwrap(); - } - file.sync_all().unwrap(); -} -- cgit v1.2.3-54-g00ecf