>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
