Mark Johnson wrote:
> The slots table includes an id field which is "auto_increment".
> PostgreSQL does not implement that keyword. Instead, it appears to
> accept it, but ignore it when creating the table. (This may actually be
> libgda's PostgreSQL provider doing that.) Gnucash-gda relies upon that
> field auto-incrementing when inserts are done to the slots table. The
> result is similar to this for every insert to the slots table:
> ERROR: null value in column "slot_id" violates not-null constraint
> STATEMENT: INSERT INTO slots (obj_guid, name, slot_type, int64_val,
> string_val, double_val, timespec_val, guid_val, numeric_val_num,
> numeric_val_denom) VALUES ('77889f8da5fb434ae68891da19bef5ad',
> 'reconcile-info/last-date', 1, 1199170799, NULL, NULL, '1969-12-31',
> NULL, 0, 1)
>
> When saving to the gda backend, both MySql and sqlite have many records
> in the slots table. PostgreSQL ends up with none.
>
GDA provides 1) numerous backends and 2) an object model to build
queries and get results. From what I have seen so far, (1) is more
mature than (2). For example, some of the queries you wrote in your
other e-mail (SELECT * from t1 where t1.guid in (SELECT * from ...))
cannot be represented in the query model. However, I should be able to
create a string and execute it.
It looks as though the postgresql backend has a bug. Using the object
model, I create an XML-based representation of the table. I create the
'slot_id' column with type integer and add the 'AUTOINC' flag to it.
For mysql and sqlite, this is an INTEGER AUTO_INCREMENT column, while
for postgresql, it should be a SERIAL column. I don't really want to
special-case the code for various backends since libgda is supposed to
remove that need. There are other cases where it might be required.
SQLite, for example, does not allow more than one row to be added in an
INSERT statement, whereas mysql and postgresql do. I could speed up
initial save to a blank db by writing all accounts at once, for example,
but sqlite couldn't handle it and would need them to be written one at a
time. Note that the libgda object model does not allow more than 1 row
to be written either, so that would need to be done with SQL directly.
Mark, I am happy if you want to design any of the SQL queries to improve
them.
Phil
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
