Difference between revisions of "GnuCash SQL Examples"
MaestroGlanz (talk | contribs) (→Return a account guid by account path or account path by guid) |
m (Jralls moved page SQL-Requests For Direct Database Access to PostgreSQL-Requests For Direct Database Access: The page content is postgresql-specific and not useful to mysql or sqlite3 users.) |
(No difference)
| |
Revision as of 17:30, 31 July 2022
Contents
- 1 Introduction
- 2 Template
- 3 Create a read-only account for the database
- 4 Request all transactions like shown in the account list
- 5 Request expenses for a certain period and category
- 6 Return a account guid by account path or account path by guid
- 7 Request all child accounts of a given account
- 8 Request the guid of all multi-transactions
Introduction
This page is under construction.
Template
Create a read-only account for the database
If you use the account, which is also used by gnucash, you will be alerted every time gnucash starts up, when it can not get exclusive write permission. This normally makes sense, but we do not want to edit the data anyway. So it's better to create a read-only account first. The example uses postgres, but other database systems are identical.
sudo su postgres # switch to the postgres account to have admin rights in the database
psql # open the sql shell, you can also call psql gnucash, to directly connect to the gnucash database right away
-- now we are in the psql shell. So comments start with double dash -- instead of pound #
postgres=# \c gnucash -- connect to the gnucash database
gnucash=# CREATE ROLE gnucashdbreader WITH ENCRYPTED PASSWORD 'randomUserPassword' LOGIN; -- new user account, which may login
CREATE ROLE -- response from the system
gnucash=# GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO gnucashdbreader; -- You must be connected to the database gnucash, while doing this. PUBLIC is context specific and refers to the current database
Request all transactions like shown in the account list
This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed.
select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name;
To get the right amounts correctly signed adjusted by the factor for the currency, we have to use this sql-querry:
select distinct CAST((CAST(-s2.value_num as DECIMAL)/s2.value_denom) as DECIMAL(10,2)) as amount, t1.post_date as date , t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name
Request expenses for a certain period and category
If you just want to know, how much you spend on certain things during some period, you can use this querry:
SELECT SUM(CAST(-CAST(s2.value_num AS DECIMAL(10,2))/s1.value_denom AS DECIMAL(10,2))) AS amount
FROM splits s1
INNER JOIN splits s2 ON s1.tx_guid=s2.tx_guid
INNER JOIN transactions t1 ON t1.guid=s2.tx_guid
INNER JOIN accounts a1 ON s1.account_guid=a1.guid
INNER JOIN accounts a2 ON s2.account_guid=a2.guid
WHERE a1.name!=a2.name AND t1.post_date >= '<some start date>' AND t1.post_date <= '<some end date>' AND a1.name = '<category>';
Return a account guid by account path or account path by guid
This returns a guid of an account by its path. The path must unlike in the GnuCash frontend start with 'Root Account'.
with recursive path_list as (
select guid, parent_guid,name,concat(name) as path
from accounts
where parent_guid is null -- this defines the start of the recursion
union all
select child.guid,child.parent_guid,child.name,concat(path,':',child.name)
from accounts as child
join path_list as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select guid from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';
or the other way round:
select path from path_list where guid='9bea76f63e5f1caa9b36c4d5e9d0a721';
You can check the output and have a bit more insight by replacing the last line with
select * from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';
Request all child accounts of a given account
This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from sql-workbench.
with recursive all_nested_accounts as (
select guid as start_guid,guid, parent_guid
from accounts
union all
select start_guid,child.guid,child.parent_guid
from accounts as child
join all_nested_accounts as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from all_nested_accounts where start_guid = '31e4ca3657b94feabe31054757aa7a5a';
If you want to have names instead of guids, replace the last line with
select a.name,b.name,c.name from all_nested_accounts as ana join accounts as a on ana.start_guid=a.guid join accounts as b on ana.guid=b.guid join accounts as c on ana.parent_guid=c.guid where start_guid = '31e4ca3657b94feabe31054757aa7a5a';
Request the guid of all multi-transactions
select * from (select tx_guid, count(tx_guid) as tx_count from splits group by tx_guid) as split_lookup_preselect where tx_count>2;
