Hello, Am 09.12.2010 23:31, schrieb Ed Leafe: > This is a perfect case for database logging. Seeing the actual > queries being called will show where the problem is.
It did, see below. > >> If Dabo _should_ work with composite primary keys, I'd do the >> latter. If that's unimportant anyway, I'd stop using them. > > It should, but since they are not commonly used, there are most > likely places where the code could be improved to handle them. I > never use them, so that code never gets tested by me; I don't believe > Paul uses them either. So a sample app would be a big help in > improving the handling of composite keys in Dabo. Here is a small test: http://dabo.codepad.org/X0tRUmVv Database: taken from tutorial.sqlite and minimized to this schema: sqlite> .schema CREATE TABLE reccat (recid INTEGER NOT NULL REFERENCES recipes(id), catid INTEGER NOT NULL REFERENCES reccats(id), PRIMARY KEY (recid, catid)); CREATE TABLE reccats (id INTEGER PRIMARY KEY, descrp text NOT NULL UNIQUE); CREATE TABLE recipes (id INTEGER PRIMARY KEY, title text NOT NULL UNIQUE, date date NOT NULL default ‚0000-00-00‘); CREATE INDEX recipes_date on recipes (date); Table reccat now has a composite primary key consisting of its two columns. Data: from tutorial table recipes ID 1-20, complete table reccats, all records from reccat with recid 1-20. Start of application: everything looks good, correct entries selected in checklist, correct list of linked categories in terminal. Checked additional category (No. 41, Breakfast and Brunch) for recipe No. 1. Output in terminal: idx = 6, idxKey = 41 addLink, catid = 41 2010-12-12 11:21:53 - INFO - SQL: BEGIN 2010-12-12 11:21:53 - INFO - SQL: begin 2010-12-12 11:21:53 - INFO - SQL: update „reccat“ set „catid“ = ? where „recid"=1 AND „catid"=11 , PARAMS: 41 2010-12-12 11:21:53 - INFO - SQL: COMMIT 2010-12-12 11:21:53 - INFO - SQL: commit This is wrong, of course, it should be an INSERT. Why UPDATE? Closing the application I get the message box „Do you want to save the changes?“. Clicking „Yes“: Save failed, foreign constraint failed. Messages in terminal: 2010-12-12 11:25:04 - INFO - SQL: BEGIN 2010-12-12 11:25:04 - INFO - SQL: begin 2010-12-12 11:25:04 - INFO - FAILED SQL: insert into „reccat“ („recid“, „catid“) values (?,?) , PARAMS: 1, 0 2010-12-12 11:25:04 - INFO - DBQueryException encountered in execute(): foreign key constraint failed insert into „reccat“ („recid“, „catid“) values (?,?) 2010-12-12 11:25:04 - INFO - DBQueryException aufgetreten in save(): foreign key constraint failed 2010-12-12 11:25:04 - ERROR - Error in scanChangedRows: foreign key constraint failed 2010-12-12 11:25:04 - ERROR - Error in scanChangedRows: foreign key constraint failed 2010-12-12 11:25:04 - INFO - SQL: ROLLBACK 2010-12-12 11:25:04 - INFO - SQL: rollback Of course the foreign keys constraint fails if the application tries to insert a record with catid=0. But why does it try to do this? With the original structure of the reccat table (autoincrement primary key id, foreign keys recid, catid) and the corresponding changes in BizCategoriesForRecipe everything works as expected. So the composite primary key really seems to be the culprit. Greetings Sibylle _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users Searchable Archives: http://leafe.com/archives/search/dabo-users This message: http://leafe.com/archives/byMID/[email protected]
