SQL: the scary bit OR more realistically, welcome to the bit you don't understand yet

[Wm]

I wish you all a good year to come, may your accounts be fruitful

so far I've used fairly obvious tables to demonstrate fairly obvious 
accounting stuff.

we've got a book (a set of accounts) and inside that we have accounts 
and transactions and splits of those transactions

I didn't make all this up you can see someone else's work here

http://wiki.gnucash.org/wiki/SQL

and here

http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png

aside: no-one is promising those are up to date but they should give you 
a good idea

onwards

if you're a text sort of person you'll look at the first URL and notice 
the bit about slots
===
Slots is a sort of catch all for a variety of data lacking a table.
===
otherwise it is what you might expect of structured data

if you're a more visual sort of person you'll look at the second URL and 
notice that the green-ish tables actually exist and include the ones 
I've mentioned so far and that there is a lot of apparent weirdness in 
the "Slots Proxy Tables"

I'm about to de-weird that in the same way that I presented a useable 
CoA by creating a view.


Note about db's:

I'll be using SQLite3 and PostgreSQL interchangeably and I'll do the 
back test myself if anyone shows an interest, MySQL people are going to 
have to roll their own for now, I'll mention the bits that I think 
you'll struggle with as I come across them.

BTW, I'm database agnostic, I think SQLite3 is the way forward for gnc 
and just happen to feel comfortable with my current PostgreSQL setup at 
the moment.  If my working situation was different I might be using 
MySQL but I'm finding it unhelpful in solving the SQL problems gnc 
presents.  Sorry-ish, if you're a MySQL fan, the SQLite3 goats get to 
eat my Christmas tree [1]

[1] google "eat christmas tree" and choose your favourite, there are 
also some nice ones of big cats and bears using them as back rubs, etc 
:)

anyhow

put down what you're eating, either shoo away the children or gather 
them around as you see fit, pour yourself a drink or get a fresh hot one 
or whatever pleases you and try this
===
select count(*) from slots
===
you'll get a number, 4375 for me, don't write in saying what yours is, 
it isn't important.

What *is* all that slots stuff about is the question I'll start 
answering in our next exciting instalment

-- 
Wm...