Updated DDL for SQL backend

[Daniel Espinosa]

2006/10/26, Phil Longstaff <plongstaff at rogers.com>:
> I've attached an updated DDL for the proposed SQL backend.  I used MySQL
> to test, and it creates the tables correctly.

I have tested your DDL in PostgreSQL and it doesn't work, may need to
modify it in a more portable area:

Command:
psql gnucash < gnucash.ddl

Errors:
ERROR:  error de sintaxis en o cerca de «#» at character 1
LINEA 1: # ver 0.03
         ^
ERROR:  error de sintaxis en o cerca de «(» at character 217
LINEA 11:  int64_val int(64),
                        ^
ERROR:  error de sintaxis en o cerca de «FOREIGN» at character 284
LINEA 11:  CONSTRAINT FOREIGN KEY(account_guid_1,account_guid_2,accoun...
                      ^
ERROR:  error de sintaxis en o cerca de «FOREIGN» at character 359
LINEA 16:  CONSTRAINT FOREIGN KEY(parent_guid_1,parent_guid_2,parent_g...
                      ^
NOTICE:  CREATE TABLE / PRIMARY KEY creará el índice implícito
«budgets_pkey» para la tabla «budgets»
CREATE TABLE
ERROR:  error de sintaxis en o cerca de «FOREIGN» at character 302
LINEA 14:  CONSTRAINT FOREIGN KEY(commodity_id) REFERENCES commodities...
                      ^
ERROR:  error de sintaxis en o cerca de «#» at character 190
LINEA 11: # Parent used for composite fs's

>
> Some notes:
>
> 1) This only covers the core objects i.e. the DDL is based on
> src/backend/file/*-v2.c.
>
> 2) The namespace for the commodities table is just a string.  I'm unsure
> about whether it should really be a table.  Currently, only 2 namespaces
> are defined and they are enforced by GC.
>
> 3) All object ids are (or should be) GUIDs.  A GUID is represented by a
> set of 4 int fields.   I use this instead of varbinary because it isn't
> variable length.  I don't use any text type because I don't want any
> issues with 0x00 as a char in a GUID.  Note that some tables DO have
> just int primary keys, but they are ones (e.g. slots) which are really
> part of another table.
>
> 4) All string fields have type 'text'.  This may be too wasteful of
> space.  Are such things as maximum length of an account name segment
> defined?
>
> 5) All numerics have 2 fields, xxx_num and xxx_denom.
>
> 6) I haven't looked too deeply into how a scheduled transaction is
> stored in XML and how that reflects its internal structure.  It appears
> as though there is a gnc:schedxaction which stores the scheduled
> transaction, and that the transaction which is converted into a
> scheduled transaction has slots attached to its splits.  I've redone
> this by adding a sched_tx_splits table which is similar to the splits
> table, except that the values are strings instead of numerics.
>
> I'm taking on this project to scratch a few particular itches: to have
> an SQL backend to speed up app startup and remove the need for saves.  I
> plan to use a db abstraction layer (probably libdbi or libgda), but I
> don't plan to test on more than mysql and SQLite.  I don't know which I
> will use for "production" usage.  I will add transactions to provide
> some data integrity, but I don't use a multi-user environment, and that
> isn't my main goal.  I have a 'books' table which will have 1 row, and
> which has an 'is_open' field which will serve as the lock for the
> database to replace the current lck file.  The db abstraction *should*
> allow a port to postgreSQL or other db's, but I don't plan to test them.
> Since different dbs differ data types and support for features like
> triggers, stored procedures, etc., I will target SQLite and mysql.  It
> might be worthwhile to have a way of using these features if the
> database supports them.  I'm happy to listen to design ideas on how to
> do this.  I plan to allow the business backend to tie into the SQL
> database in the same way that the XML backends tie together.  I will
> implement the business backend, but it will not receive extensive
> testing because I don't use the business piece of GC.  Note that I am
> saying what I will/won't implement.  I'm happy to work with any other
> volunteer who wants to join in and help with any piece.
>

I'll work so hard in the PostgreSQL backend, the bussines objects and
the port using GDA, then take my name as helper.

I'll update my Dia diagram to fit your DDL, this is just to allow
others (any supported by GDA) provider could desing and create it's
own database schema to fit the same functionality.

Even if some plan to add procedures in the backend, this is possible
becouse SQLite3 supports them, I can add as procedures in the class
diagram in the UML model made with Dia.


-- 
Trabajar, la mejor arma para tu superación
"de grano en grano, se hace la arena" (R) (entrámite, pero para los
cuates: LIBRE)