SQLite Backend Data

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite Backend Data

Mark-448
Is the SQLite backend file that Gnucash creates usable for custom SQL
queries? If so, where can I read about how to do it? I've taken a quick
look at the tables, and I notice a lot of fields with variations on the
name "guid", filled with what looks like hex data. Can one parse these
back into plain English?
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Backend Data

Russell Mercer
Mark,

The sqlite backend is a complete sqlite database.  If you want to run SQL
queries on it, the following website should be your first stop for sqlite
information:

http://www.sqlite.org/docs.html

The guid field that you keep running across is a Globally Unique
IDentifier.  That is the primary key for each of the tables you are looking
at, and or is the foreign key referencing another linked table.  There is no
parsing back to plain english as there is nothing to understand other than
it should be unique among all other tables and records in your database.
For more information, you might check out:

http://en.wikipedia.org/wiki/Globally_unique_identifier

Lastly, I think a caution is prudent.  Before you do any queries, etc. with
your gnucash data file, I would make sure you back it up.  Not knowing the
programming of gnucash, I don't know what types of things it is sensitive to
in the sqlite file, and it would be terrible if an SQL query inadvertently
corrupted the data file.  I've been curious about this myself but have
refrained due to the same concern.

This is just my yeoman level insight, but I hope it helps.

Russell

On Tue, Feb 1, 2011 at 10:46 PM, Mark <[hidden email]> wrote:

> Is the SQLite backend file that Gnucash creates usable for custom SQL
> queries? If so, where can I read about how to do it? I've taken a quick look
> at the tables, and I notice a lot of fields with variations on the name
> "guid", filled with what looks like hex data. Can one parse these back into
> plain English?
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Backend Data

Geert Janssens
On Wednesday 02 February 2011, Russell Mercer wrote:

> Mark,
>
> The sqlite backend is a complete sqlite database.  If you want to run SQL
> queries on it, the following website should be your first stop for sqlite
> information:
>
> http://www.sqlite.org/docs.html
>
> The guid field that you keep running across is a Globally Unique
> IDentifier.  That is the primary key for each of the tables you are looking
> at, and or is the foreign key referencing another linked table.  There is
> no parsing back to plain english as there is nothing to understand other
> than it should be unique among all other tables and records in your
> database. For more information, you might check out:
>
> http://en.wikipedia.org/wiki/Globally_unique_identifier
>
> Lastly, I think a caution is prudent.  Before you do any queries, etc. with
> your gnucash data file, I would make sure you back it up.  Not knowing the
> programming of gnucash, I don't know what types of things it is sensitive
> to in the sqlite file, and it would be terrible if an SQL query
> inadvertently corrupted the data file.  I've been curious about this
> myself but have refrained due to the same concern.
>
In addition I would add that the database was never meant to be accessed from
outside of GnuCash. You could get away with reading from the tables for your
custom reporting needs, but you should never ever write to your data file
other than via the gnucash access library. That means either via the GnuCash
program itself or via the (experimental and optional) python bindings.

Ignoring this advice exposes you to the real risk of corrupting your data. The
database is only used as a data store. There's no business logic built into it
to ensure data integrity. That's why it's crucial to only modify the data via
the GnuCash interface.

And even for read-only access, you can extract a great deal of useful
information, but some parts will be very hard: some information is stored in
key-value pairs (in the slots table). This data is not suitable at all for
regular db queries, so if you need the info in there, direct access via sql
might be challenging.

So to conclude: you can run sql queries on the sqlite backend data. You should
restrict your queries to read-only though to avoid shooting yourself in the
foot and be aware that not all information is easily interpretable or
retrievable via an sql query.

Geert
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Backend Data

John Ralls-2

On Feb 2, 2011, at 1:21 AM, Geert Janssens wrote:

> On Wednesday 02 February 2011, Russell Mercer wrote:
>> Mark,
>>
>> The sqlite backend is a complete sqlite database.  If you want to run SQL
>> queries on it, the following website should be your first stop for sqlite
>> information:
>>
>> http://www.sqlite.org/docs.html
>>
>> The guid field that you keep running across is a Globally Unique
>> IDentifier.  That is the primary key for each of the tables you are looking
>> at, and or is the foreign key referencing another linked table.  There is
>> no parsing back to plain english as there is nothing to understand other
>> than it should be unique among all other tables and records in your
>> database. For more information, you might check out:
>>
>> http://en.wikipedia.org/wiki/Globally_unique_identifier
>>
>> Lastly, I think a caution is prudent.  Before you do any queries, etc. with
>> your gnucash data file, I would make sure you back it up.  Not knowing the
>> programming of gnucash, I don't know what types of things it is sensitive
>> to in the sqlite file, and it would be terrible if an SQL query
>> inadvertently corrupted the data file.  I've been curious about this
>> myself but have refrained due to the same concern.
>>
> In addition I would add that the database was never meant to be accessed from
> outside of GnuCash. You could get away with reading from the tables for your
> custom reporting needs, but you should never ever write to your data file
> other than via the gnucash access library. That means either via the GnuCash
> program itself or via the (experimental and optional) python bindings.
>
> Ignoring this advice exposes you to the real risk of corrupting your data. The
> database is only used as a data store. There's no business logic built into it
> to ensure data integrity. That's why it's crucial to only modify the data via
> the GnuCash interface.
>
> And even for read-only access, you can extract a great deal of useful
> information, but some parts will be very hard: some information is stored in
> key-value pairs (in the slots table). This data is not suitable at all for
> regular db queries, so if you need the info in there, direct access via sql
> might be challenging.
>
> So to conclude: you can run sql queries on the sqlite backend data. You should
> restrict your queries to read-only though to avoid shooting yourself in the
> foot and be aware that not all information is easily interpretable or
> retrievable via an sql query.

And to that I'll add that none of the relation data is encoded in the database. We'll correct that over time, but for now it's all in the C code that loads from the tables into data structures in the program.

The table layouts are documented at http://wiki.gnucash.org/wiki/SQL

Regards,
John Ralls

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Backend Data

Mark-448
Thank you all for the clarification, and your cautions are all well
taken. I am working only with an expendable copy of Gnucash's SQLite
file, and am interested solely in creating custom views and reports of
my data.

The data I want are mainly budget lines and register transactions. The
questions I have below all have to do with the db design. Is that
viewable anywhere?

Questions:

I'm not sure what "key-value pairs" (in slots table) are. Under what
circumstances, if any, would I need them?

How is the 'transactions' table related to the 'splits' table? Is the

Am I right that dollar amounts are stored absolutely with no decimal
point -- e.g. $123.90 appears as 12390?

Thanks,
Mark


_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Backend Data

John Ralls-2

On Feb 2, 2011, at 5:21 PM, Mark wrote:

> Thank you all for the clarification, and your cautions are all well taken. I am working only with an expendable copy of Gnucash's SQLite file, and am interested solely in creating custom views and reports of my data.
>
> The data I want are mainly budget lines and register transactions. The questions I have below all have to do with the db design. Is that viewable anywhere?
>
> Questions:
>
> I'm not sure what "key-value pairs" (in slots table) are. Under what circumstances, if any, would I need them?
>
> How is the 'transactions' table related to the 'splits' table? Is the
>
> Am I right that dollar amounts are stored absolutely with no decimal point -- e.g. $123.90 appears as 12390?

Key-value-pairs (KVP for short) are a mechanism that's been used to store a variety of metadata. It has allowed developers to add features to Gnucash without changing the way the database is stored, which keeps the database backwards-compatible: Earlier versions just ignore the extra KVP values because they don't know to look for them. What KVP data is associated with what tables is not documented, but it needs to be. The only way to figure out at present is to study the code in the core modules like  engine and business.

Transactions are a container of two or more splits. The splits have the account and amount of each part of a transaction. Join splits.tx_guid = transactions.guid. (For the split to make sense you'll also need to join splits.account_guid = accounts.guid).

Yes, amounts are always integers in Gnucash to avoid rounding errors. Each number has two fields, foo_num and foo_denom. Divide the former by the latter. The result isn't always representable as a decimal (1/3 for example).

Regards,
John Ralls

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.