SQLite Backend Data

[John Ralls]

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