Question on mysql "account" table

[John Ralls]

> On Oct 11, 2017, at 6:31 AM, Joseph Hesse <joehesse at gmail.com> wrote:
> 
> Here is an example of what I am trying to do.  My expenses in gnucash have items that look like:
> 
> Expenses
>   Auto
>     Gas
>     Service
> 
> When I look in the mysql accounts table I can find the record containing Service but no easy way to find Expenses:Auto:Service. The record containing Service in the accounts table has the item parent_guid so I could use that to locate the parent classification of Service, which is Auto. I can repeat the process until I get a complete description of the account.
> 
> My questions is: Is there sophisticated query where I can add a column to the accounts table containing the full name instead of just the name?
> 
> I know I can do what I want in a programming language like PHP or C++ but I want to know if there is something easier.
> 
> The reason for my questions is I am creating a custom expense report by joining the accounts, splits and transactions table and I want the expense name to have a full path description.
> 

Changing the database schema is not supported. You should also be aware that GnuCash sometimes needs to rewrite the database from scratch and when it does any schema changes you’ve made will be lost.

That said you could create an auxiliary table mapping the fully-qualified account names to their GUIDs (which is what you want for querying the splits table) and GnuCash will ignore it. You’d need to write a recursive tree walking query to do that. It might be possible in a single query but that’s way beyond my SQL-fu; I’d wrap the SQL in a scripting language—perl or python depending on my mood at the moment—to start off with a clean table, compute the tree, and populate it.

Regards,
John Ralls