[sqlite] Bug in sqlite3_trace/trigger/delete
unt_analytic_chart 0|0|0|SCAN TABLE account_analytic_balance 0|0|0|SCAN TABLE account_analytic_balance 0|0|0|SCAN TABLE account_analytic_account 0|0|0|SCAN TABLE account_analytic_account 0|0|0|SCAN TABLE account_analytic_account 0|0|0|SCAN TABLE account_analytic_account 0|0|0|SCAN TABLE account_aged_trial_balance 0|0|0|SCAN TABLE account_aged_trial_balance 0|0|0|SCAN TABLE account_addtmpl_wizard 0|0|0|SCAN TABLE account_addtmpl_wizard 0|0|0|SCAN TABLE account_account_type 0|0|0|SCAN TABLE account_account_type 0|0|0|SCAN TABLE account_account_template 0|0|0|SCAN TABLE account_account_template 0|0|0|SCAN TABLE account_account 0|0|0|SCAN TABLE account_account - > Fri Jul 31 2015 15:34:56 CEST from "sqlite-mail" > Subject: [sqlite] Bug in >sqlite3_trace/trigger/delete > > 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 \ > ??? -DTHREA
[sqlite] Bug in sqlite3_trace/trigger/delete
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" > 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" >> 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 &
[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" > 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 integ
[sqlite] Bug in sqlite3_trace/trigger/delete
On 08/03/2015 07:24 PM, sqlite-mail wrote: > > Hello Dan ! > > I downloaded again > http://www.sqlite.org/2015/sqlite-amalgamation-3081101.zip just and > recompiled it and running I still get the same 1000 lines of the > trigger call. > > My environment is ubuntu 14.04 and I'm using gcc 4.9.2 and now I also > compiled with clang 3.6 and I've got the same 1000 lines of trigger call. > > As you can see from the test program there is not much else than > sqlite3 and the database. > > I also tested before on OS X with the same result, but I can not test > it again now there, later on I'll do it. > > What environment are you testing ? I mean os, compiler, ... > 64-bit Linux with gcc 4.7.1. Are we using the same database? Before running the test program, I get: $ sha1sum ./odoo.db 0f5ac7aa291a9c149cf36bbc6ac4c73a90774c97 ./odoo.db and $ sqlite3 ./odoo.db "SELECT count(*) FROM res_users WHERE id = 7;" 0 Is this as expected? Dan. > Cheers ! > > Mon Aug 03 2015 13:08:08 CEST from "Dan Kennedy" > 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. > > If I build the test program below using the command line provided > with > SQLite 3.8.11.1 and then run it against the oodo.db file from the > link > above, I get a single line of output: > > SQL: DELETE FROM res_users WHERE id=7 > > No errors under valgrind. Is this the expected result? Does it > mean bug > (1) has been fixed already? > > Dan. > > > > > > -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 >
[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. If I build the test program below using the command line provided with SQLite 3.8.11.1 and then run it against the oodo.db file from the link above, I get a single line of output: SQL: DELETE FROM res_users WHERE id=7 No errors under valgrind. Is this the expected result? Does it mean bug (1) has been fixed already? Dan. > > -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 > #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;"; >
[sqlite] Bug in sqlite3_trace/trigger/delete
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
#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
