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