Hello again ! I forgot also to mention that sqlite do not check for duplicates table constraint declarations see the extended example bellow:
--------- PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE ab(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE ac(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE ad(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE aconstrained( id INTEGER PRIMARY KEY, a_id INTEGER NOT NULL, b_id INTEGER NOT NULL, c_id INTEGER NOT NULL, d_id INTEGER NOT NULL, CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES aa(id) -- missing comma separator sqlite accept it but postgresql rejects it CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES? ab(id) -- missing comma separator sqlite accept it but postgresql rejects it CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES ac(id), -- here we have the comma separating a constraint CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) ); COMMIT; --------- > Wed Aug 05 2015 12:16:09 CEST from "sqlite-mail" ><sqlite-mail at dev.dadbiz.es> Subject: Re: [sqlite] Bug in >sqlite3_trace/trigger/delete > > >>Hello ! >> >> There is also another small bug in sqlite3 parsing of table >> constraints the documentation say that "CREATE TABLE" will accept one >> or more table constraints separated by comma but the parser do not >> catch the absence of a comma, it still seem to works as expected but >> if we try to move the same sql statements to another database like >> postgresql they'll be rejected. See the example bellow. >> >> And on the original issue of sqlite3_trace/trigger/delete I could see >> that the bug of views created out of order on dump/restore was somehow >> solved with this commit >> https://www.sqlite.org/src/info/70b57dafb3216feb but no mention or >> feedback for the problem with recursive loop when deleting a record >> with a trigger for one specific database also provided on the original >> email. Can someone give some feedback on that open issue ? >> >> >> >> > --------- > > >>PRAGMA foreign_keys=OFF; >> >> BEGIN TRANSACTION; >> >> CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT); >> CREATE TABLE ab(id INTEGER PRIMARY KEY, name TEXT); >> CREATE TABLE ac(id INTEGER PRIMARY KEY, name TEXT); >> CREATE TABLE ad(id INTEGER PRIMARY KEY, name TEXT); >> >> CREATE TABLE aconstrained( >> id INTEGER PRIMARY KEY, >> a_id INTEGER NOT NULL, >> b_id INTEGER NOT NULL, >> c_id INTEGER NOT NULL, >> d_id INTEGER NOT NULL, >> CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES >> aa(id) -- missing comma separator sqlite accept it but postgresql >> rejects it >> CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES >> ab(id) -- missing comma separator sqlite accept it but postgresql >> rejects it >> CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES >> ac(id), -- here we have the comma separating a constraint >> CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES ad(id) >> ); >> >> COMMIT; >> >> --------- >> >> Thanks in advance for your time and attention ! >> >> Mon Aug 03 2015 13:08:08 CEST from "Dan Kennedy" >> <danielk1977 at gmail.com> Subject: Re: [sqlite] Bug in >> sqlite3_trace/trigger/delete >> On 07/31/2015 08:34 PM, sqlite-mail wrote: >> >> Hello ! >> >> I'm using sqlite for a project and with this specific database >> https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB >> uncompressed) >> this is happening: >> >> -1 Registering an sqlite3_trace function when trying to delete >> a record just >> inserted on the table "res_users" the registered sqlite3_trace >> function is >> called lots of times and sometimes it segfaults (I think stack >> overflow), I >> think it enters in a unintended loop. >> >> -2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and >> try to recreate >> the database with "sqlite3 new-odoo.db < odoo.db.sql" we get >> errors for >> tables/views declarations out of order (trying to create a view >> https://www.endad.eu/tmp/odoo.db.zipthat refer to other views >> not yet >> created). >> >> Attached there is the simple "C" test file with a shell file >> to make it with >> the flags I use on this project. >> >> This database uses a lot of foreign keys. >> >> The trigger on the "res_users" table is very simple: >> ----- >> BEFORE DELETE ON "res_users" >> BEGIN >> SELECT RAISE(ABORT, 'Can not remove root/admin user!') >> WHERE OLD.id = 1; >> END; >> ----- >> >> I've also tested with a fresh sqlite3.c/h from >> https://www.sqlite.org/snapshot/sqlite-amalgamation-201507231639.zip. >> >> When tested with a single table with the above trigger with a >> fresh database >> the test program behaves as expected. >> ----- >> CREATE TABLE IF NOT EXISTS tbl(id INTEGER PRIMARY KEY, name >> varchar); >> INSERT OR IGNORE INTO tbl(id, name) VALUES >> (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); >> CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE >> ON tbl >> BEGIN >> SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE >> OLD.id = >> 1; >> END; >> ----- >> >> >> I found a small test that shows how to create a database that >> after ".dump" >> will not properly be restored. >> >> After writing to you about this bug with sqlite3_trace/trigger >> I start >> thinking what I did with this database (odoo.db) that could >> possibly make >> it's ".dump" not usable to restore and I found the reason. >> >> On that database I was constantly droping and recreating >> tables/views with >> slight different fields and that seems what makes sqlite3 get >> lost. >> >> Example that creates a database not correctly restorable: >> -------- >> begin; >> create table if not exists tbl(id integer primary key, name >> varchar); >> insert or ignore into tbl(id, name) values (1,'a'), (2, 'b'); >> create view if not exists tbl_view as select * from tbl; >> create view if not exists tbl_view_view as select * from tbl_view; >> drop view if exists tbl_view; >> create view if not exists tbl_view as select * from tbl; >> end; >> -------- >> >> After creating a database with the above sql we get the >> following from >> ".dump": >> -------- >> PRAGMA foreign_keys=OFF; >> BEGIN TRANSACTION; >> CREATE TABLE tbl(id integer primary key, name varchar); >> INSERT INTO "tbl" VALUES(1,'a'); >> INSERT INTO "tbl" VALUES(2,'b'); >> CREATE VIEW tbl_view_view as select * from tbl_view; --<<<<<< >> here we >> are trying to create a view on another view not yet created >> CREATE VIEW tbl_view as select * from tbl; >> COMMIT; >> -------- >> >> On the ".dump"/".restore" problem it seems that sqlite3 shell >> rely on >> sqlite3_master rowid order to perform the ".dump" and when we >> drop/recreate a >> table/view that other tables/views depends sqlite3 do not >> detect it and >> simply add a new entry at the end of sqlite3_master. >> >> >> -------- shell script to make the bug test program >> MYINC=$HOME/dev/sqlite3 >> #MYINC=. >> >> gcc \ >> -DTHREADSAFE=1 \ >> -DSQLITE_DEFAULT_FILE_FORMAT=4 \ >> -DSQLITE_DEFAULT_AUTOVACUUM=1 \ >> -DSQLITE_DEFAULT_FOREIGN_KEYS= 1 \ >> -DSQLITE_ENABLE_COLUMN_METADATA=1 \ >> -DSQLITE_ENABLE_FTS4=1 \ >> -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \ >> -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \ >> -DSQLITE_ENABLE_RTREE=1 \ >> -DSQLITE_ENABLE_STAT4=1 \ >> -DSQLITE_OMIT_TCL_VARIABLE=1 \ >> -DSQLITE_USE_URI=1 \ >> -DSQLITE_SOUNDEX=1\ >> -o test-sqlite-bug test-sqlite-bug.c -I $MYINC $MYINC/sqlite3.c >> -lpthread -lm -ldl >> --------- >> --------- test-sqlite-bug.c >> #include <stdio.h> >> #include "sqlite3.h" >> >> static const char test_sql[] = >> "CREATE TABLE IF NOT EXISTS tbl(id INTEGER PRIMARY KEY, name >> varchar);" >> "INSERT OR IGNORE INTO tbl(id, name) VALUES " >> "(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');" >> "CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE >> ON tbl >> " >> "BEGIN" >> " SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE >> OLD.id = 1;" >> "END;"; >> >> static void db_trace_callback(void *user, const char *sql) >> { >> printf("SQL: %s\n", sql ? sql : "??"); >> } >> >> static void insertRecord(sqlite3 *db) >> { >> char *errmsg; >> const char szSQL[] = "INSERT INTO >> >> >>\"res_users\"(\"alias_id\",\"company_id\",\"create_uid\",\"login\",\"partner_i >> >> >> d\") VALUES('10','3',4,'daddad','12')"; >> sqlite3_trace(db, db_trace_callback, NULL); >> int rc = sqlite3_exec(db, szSQL, NULL, NULL, &errmsg); >> } >> >> int main(int argc, char *argv[]) >> { >> sqlite3 *db; >> int rc = sqlite3_open("../odoo.db", &db); >> if(rc == SQLITE_OK) >> { >> char *errmsg; >> const char szSQL[] = "DELETE FROM res_users WHERE id=7"; >> sqlite3_trace(db, db_trace_callback, NULL); >> insertRecord(db); >> //rc = sqlite3_exec(db, test_sql, NULL, NULL, &errmsg); >> rc = sqlite3_exec(db, szSQL, NULL, NULL, &errmsg); >> sqlite3_close(db); >> } >> return 0; >> } >> ------- >> ------- output of test-sqlite-bug >> SQL: INSERT INTO >> "res_users"("alias_id","company_id","create_uid","login","partner_id") >> VALUES('10','3',4,'daddad','12') >> SQL: DELETE FROM res_users WHERE id=7 >> SQL: -- TRIGGER res_users_admin_trigger >> SQL: -- TRIGGER >> .. <<<<<< the above line repeated 1000 times >> ------- >> >> Thanks in advance for your time, attention and great work ! >> Cheers ! >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
