On Fri, 2006-27-10 at 18:48 -0500, Daniel Espinosa wrote: > 2006/10/26, Phil Longstaff <[EMAIL PROTECTED]>: > > 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 _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
