def test_AnyMeta(self, entries, _, options_map): """ 2016-11-20 * name: "TheName" address: "1 Wrong Way" empty: "NotEmpty" Assets:Banking 1 USD color: "Green" address: "1 Right Way" empty: """ rtypes, rrows = query.run_query(entries, options_map, 'SELECT ANY_META("name") as m') self.assertEqual([('TheName', )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT ANY_META("color") as m') self.assertEqual([('Green', )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT ANY_META("address") as m') self.assertEqual([('1 Right Way', )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT ANY_META("empty") as m') self.assertEqual([(None, )], rrows)
def test_GrepN(self, entries, _, options_map): """ 2016-11-20 * "prev match in context next" Assets:Banking 1 USD """ rtypes, rrows = query.run_query( entries, options_map, ''' SELECT GREPN("in", narration, 0) as m ''') self.assertEqual([('in', )], rrows) rtypes, rrows = query.run_query( entries, options_map, ''' SELECT GREPN("match (.*) context", narration, 1) as m ''') self.assertEqual([('in', )], rrows) rtypes, rrows = query.run_query( entries, options_map, ''' SELECT GREPN("(.*) in (.*)", narration, 2) as m ''') self.assertEqual([('context next', )], rrows) rtypes, rrows = query.run_query( entries, options_map, ''' SELECT GREPN("ab(at)hing", "abathing", 1) as m ''') self.assertEqual([('at', )], rrows)
def test_Subst(self, entries, _, options_map): """ 2016-11-20 * "I love candy" Assets:Banking -1 USD 2016-11-21 * "Buy thing thing" Assets:Cash -1 USD """ rtypes, rrows = query.run_query(entries, options_map, ''' SELECT SUBST("[Cc]andy", "carrots", narration) as m where date = 2016-11-20 ''') self.assertEqual([('I love carrots',)], rrows) rtypes, rrows = query.run_query(entries, options_map, ''' SELECT SUBST("thing", "t", narration) as m where date = 2016-11-21 ''') self.assertEqual([('Buy t t',)], rrows) rtypes, rrows = query.run_query(entries, options_map, ''' SELECT SUBST("random", "t", narration) as m where date = 2016-11-21 ''') self.assertEqual([('Buy thing thing',)], rrows) rtypes, rrows = query.run_query(entries, options_map, ''' SELECT SUBST("(love)", "\\1 \\1", narration) as m where date = 2016-11-20 ''') self.assertEqual([('I love love candy',)], rrows) rtypes, rrows = query.run_query(entries, options_map, ''' SELECT SUBST("Assets:.*", "Savings", account) as a, str(sum(position)) as p ''') self.assertEqual([('Savings', '(-2 USD)')], rrows)
def test_Lower(self, entries, _, options_map): """ 2016-11-20 * "I love candy" Assets:Banking -1 USD """ rtypes, rrows = query.run_query(entries, options_map, ''' SELECT Lower(narration) as m where date = 2016-11-20 ''') self.assertEqual([('i love candy',)], rrows)
def test_DateDiffAdjust(self, entries, _, options_map): """ 2016-11-20 * "ok" Assets:Banking -1 STOCK { 5 USD, 2016-10-30 } """ rtypes, rrows = query.run_query( entries, options_map, 'SELECT date_diff(date, cost_date) as m') self.assertEqual([(21, )], rrows) rtypes, rrows = query.run_query( entries, options_map, 'SELECT date_diff(cost_date, date) as m') self.assertEqual([(-21, )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date_add(date, 1) as m') self.assertEqual([(datetime.date(2016, 11, 21), )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date_add(date, -1) as m') self.assertEqual([(datetime.date(2016, 11, 19), )], rrows)
def test_Coalesce(self, entries, _, options_map): """ 2016-11-20 * Assets:Banking 1 USD """ rtypes, rrows = query.run_query(entries, options_map, 'SELECT COALESCE(account, price) as m') self.assertEqual([('Assets:Banking',)], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT COALESCE(price, account) as m') self.assertEqual([('Assets:Banking',)], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT COALESCE(price, cost_number) as m') self.assertEqual([(None,)], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT COALESCE(narration, account) as m') self.assertEqual([('',)], rrows)
def test_Date(self, entries, _, options_map): """ 2016-11-20 * "ok" Assets:Banking 1 USD """ rtypes, rrows = query.run_query(entries, options_map, 'SELECT date(2020, 1, 2) as m') self.assertEqual([(datetime.date(2020, 1, 2), )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date(year, month, 1) as m') self.assertEqual([(datetime.date(2016, 11, 1), )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date(2020, 2, 32) as m') self.assertEqual([(None, )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date("2020-01-02") as m') self.assertEqual([(datetime.date(2020, 1, 2), )], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT parse_date("2016/11/1") as m') self.assertEqual([(datetime.date(2016, 11, 1), )], rrows) rtypes, rrows = query.run_query( entries, options_map, 'SELECT parse_date("2016/11/1", "%Y/%d/%m") as m') self.assertEqual([(datetime.date(2016, 1, 11), )], rrows)
def test_Date(self, entries, _, options_map): """ 2016-11-20 * "ok" Assets:Banking 1 USD """ rtypes, rrows = query.run_query(entries, options_map, 'SELECT date(2020, 1, 2) as m') self.assertEqual([(datetime.date(2020, 1, 2),)], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date(year, month, 1) as m') self.assertEqual([(datetime.date(2016, 11, 1),)], rrows) with self.assertRaisesRegex(ValueError, "day is out of range for month"): rtypes, rrows = query.run_query(entries, options_map, 'SELECT date(2020, 2, 32) as m') rtypes, rrows = query.run_query(entries, options_map, 'SELECT date("2020-01-02") as m') self.assertEqual([(datetime.date(2020, 1, 2),)], rrows) rtypes, rrows = query.run_query(entries, options_map, 'SELECT date("2016/11/1") as m') self.assertEqual([(datetime.date(2016, 11, 1),)], rrows)
def test_run_query(self, entries, _, options): """ 2022-01-01 open Assets:Checking USD 2022-01-01 open Income:ACME USD 2022-01-01 open Expenses:Taxes:Federal USD 2022-01-01 open Assets:Federal:401k IRAUSD 2022-01-01 open Expenses:Taxes:401k IRAUSD 2022-01-01 open Assets:Vacation VACHR 2022-01-01 open Income:Vacation VACHR 2022-01-01 open Expenses:Vacation VACHR 2022-01-01 open Expenses:Tests USD 2022-01-01 * "ACME" "Salary" Assets:Checking 10.00 USD Income:ACME -11.00 USD Expenses:Taxes:Federal 1.00 USD Assets:Federal:401k -2.00 IRAUSD Expenses:Taxes:401k 2.00 IRAUSD Assets:Vacation 5 VACHR Income:Vacation -5 VACHR 2022-01-02 * "Holidays" Assets:Vacation -1 VACHR Expenses:Vacation 2022-01-03 * "Test" Assets:Checking 3.00 USD Expenses:Tests """ sql_query = r""" SELECT account, sum(position) AS amount WHERE root(account, 1) = '{0}' GROUP BY 1 ORDER BY 2 DESC """ rtypes, rrows = query.run_query(entries, options, sql_query, 'Expenses', numberify=True) columns = [rt[0] for rt in rtypes] self.assertEqual(columns, ['account', 'amount (USD)', 'amount (VACHR)', 'amount (IRAUSD)']) self.assertEqual(len(rrows[0]), 4)
def test_run_query(self): rootdir = test_utils.find_repository_root(__file__) filename = path.join(rootdir, 'examples', 'example.beancount') entries, errors, options_map = loader.load_file(filename) assert not errors sql_query = r""" SELECT account, SUM(position) AS amount WHERE account ~ 'Expenses:' GROUP BY 1 ORDER BY 2 DESC """ rtypes, rrows = query.run_query(entries, options_map, sql_query, 'Martin', numberify=True) self.assertEqual( ['account', 'amount (USD)', 'amount (IRAUSD)', 'amount (VACHR)'], [rt[0] for rt in rtypes]) self.assertEqual(len(rrows[0]), 4)