Updated DDL for SQL backend

Phil Longstaff plongstaff at rogers.com
Fri Oct 27 20:43:39 EDT 2006


On Fri, 2006-27-10 at 18:48 -0500, Daniel Espinosa wrote:
> 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.
> 
> 
Hi Daniel,

thanks.  I made some modifications to fix most of the problems.  I have
both PostgreSQL and MySQL set up on my machine so I can test both dbs.
One compatibility problem I have run into is that pgsql doesn't support
BLOB as a data type (they have bytea instead).  This is needed for the
kvp slot binary type.

Thanks for the offer of help.  I plan to concentrate on the core objects
and on SQLite/MySQL.   If you want to do the business objects and pgsql,
then great.

Phil





More information about the gnucash-devel mailing list