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)
