[sqlite] Why sqlite show qualified column names when selecting from views ?
First of all thanks for reply ! I can understand your point, what confuses me is that when we query a view with "a.*" it doesn't qualify the names so I thought that was a mistake when we use individual names otherwise I would expected qualified names there too. Cheers ! > Tue Aug 25 2015 19:45:27 CEST from "Simon Slavin" >Subject: Re: [sqlite] Why sqlite show qualified column names when selecting >from views ? > > On 25 Aug 2015, at 6:13pm, sqlite-mail wrote: > > >>When querying views sqlite shows qualified column names if they are >>specified >> individually. >> > Sorry but this has been mentioned a few times here and won't be changed. >The SQL standard doesn't mention column names so SQL engines are free to do >what they want. > > In SQLite you can depend on column names only if you have specified them >using 'AS'. So I would expect, but haven't tested right now ... > > >>SQL: SELECT a.* FROM tbl_view AS a; >> Column 0: id >> Column 1: name >> SQL: SELECT a.id, a.name FROM tbl_view AS a; >> Column 0: a.id <<<<<<<<<<<<<<<<<<<<< only with individual fields >> Column 1: a.name <<<<<<<<<<<<<<<<<<< >> >> > SQL: SELECT a.id AS id, a.name AS name FROM tbl_view AS a; > Column 0: id > Column 1: name > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Why sqlite show qualified column names when selecting from views ?
Hello !
When querying views sqlite shows qualified column names if they are specified
individually.
Is this the expected result or a bug ?
This behavior breaks some of my code that uses column names for other
purposes.
Cheers !
output of "test-view-alias"
SQL: SELECT a.* FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.* FROM tbl_view AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl_view AS a;
Column 0: a.id?? < only with individual fields
Column 1: a.name <<<
test-view-alias.c
#include
#include "sqlite3.h"
void showStmtColNames(sqlite3 *db, const char *szSQL)
{
??? sqlite3_stmt* stmt;
??? printf("SQL: %s\n", szSQL);
??? int rc = sqlite3_prepare_v2(db, szSQL, -1, , 0);
??? int i, col_count = sqlite3_column_count(stmt);
??? for(i=0; i < col_count; ++i)
??? {
??? ??? printf("Column %d: %s\n", i, sqlite3_column_name(stmt, i));
??? }
??? sqlite3_finalize(stmt);
}
int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? const char dbname_szSQL[] = ":memory:";
??? int rc = sqlite3_open(dbname_szSQL, );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? rc = sqlite3_exec(db, "CREATE TABLE tbl(id, name);", NULL,
NULL, );
??? ??? rc = sqlite3_exec(db, "CREATE VIEW tbl_view AS SELECT a.id,
a.name FROM tbl AS a;", NULL, NULL, );
??? ??? rc = sqlite3_exec(db, "INSERT INTO tbl(id, name) VALUES(1,
'dad');", NULL, NULL, );
??? ???
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl_view AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl_view AS
a;"); //here only we get qualified names
??? ???
??? ??? sqlite3_close(db);
??? }
??? return 0;
}
script to compile "test-view-alias.c"
MYINC=.
gcc -g -O2 \
??? -DSQLITE_DEBUG=1 \
??? -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 \
??? -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-view-alias test-view-alias.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl
[sqlite] There is any reason to sqlite not expand "*" in function calls ?
Thanks for the answer !
How hard would be to implement something like that ?
O maybe a function called "expand" that would return the expanded wildcard
parameters ?
Ex: select json_array(expand(*)) as json from one_table;
Ex2 inside a trigger: select raise("Something is not right here !") where
my_generic_trigger_function(trigger.type, new.*, old.*) = 1;
For triggers would be nice to have a metadata parameter maybe called
"trigger" with info about "table name", "field name", "before/after/instead
insert/update/delete".
?
Maybe now with this new "table-valued functions" we can have an easier way to
emulate "create function", it seems that we can somehow pass parameters and
retrieve variable number of columns.
I always miss something like this mainly on triggers to make generic user
defined functions.
Anyone have any idea about this ?
?
Cheers !
?
> Sat Aug 22 2015 13:01:32 CEST from "Richard Hipp"
>Subject: Re: [sqlite] There is any reason to sqlite not expand "*" in
>function calls ?
>
> On 8/22/15, sqlite-mail wrote:
>
>
>>Then I tried with some custom functions accepting variable number of
>> parameters and realize that "*" is not expanded for function calls.
>>
>> There is any reason for it or it's a forgotten implementation ?
>>
>>
> Well, one reason is that "somefunction(*)" does not expand the "*" to
> a list of all columns in any other function in any other SQL database
> engine, that I am aware of. That behavior is without precedent.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] There is any reason to sqlite not expand "*" in function calls ?
Hello ! I'm testing the new json functions and when I tried this: select json_array(*) as json from one_table; I've got : [] [] .. Then I tried with some custom functions accepting variable number of parameters and realize that "*" is not expanded for function calls. There is any reason for it or it's a forgotten implementation ? Cheers !
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
That's why I think that expose a basic SQL ANSI catalog would make this kind of work and others a lot easier. ? > Fri Aug 21 2015 22:13:00 CEST from "Simon Slavin" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 21 Aug 2015, at 9:07pm, Scott Hess wrote: > > >>That said, writing code to do this manually has potential to be error >> prone. It might make sense under an ENABLE flag. It feels like an obvious >> thing to have, not something out-of-scope like embedding a new language or >> support for a new storage methodology. >> > To do it correctly you'd have to identify all table names used by each >trigger and view. Which means you have to parse the VIEWs and TRIGGERs and >look in all the positions where you'd expect to find a table name. > > I don't know whether the existing SQLite statement parser could be used for >this but it does seem to be a complicated task. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Thank you for your attention ! I'm pointing this here because postgresql do manage this case properly ! And I'm creating a tool to prototype database applications and I'm using sqlite as the primary database, when we are prototyping things can change drastically at any point and if we already have a lot of views/triggers it's a pain in the ass to fix it (postgresql does it fine). Also that we got to this point would be nice if sqlite implemented a basic SQL ANSI data dictioanry,? I mean sqlite already provide most of the info for a basic data dictionary but in a non compliant way "PRAGMAS", although is better than nothing we can not use that info on sql statements like views/joins. Would be nice if sqlite provides virtual ansi views (like postgresql does on top of it's pg_catalog) for : - check_constraints ? - collations - columns - key_column_usage - referential_constraints - sequences - table_constraints - tables - triggered_update_columns - triggers - user_defined_types - views ? Thanks in advance for your time, attention and great work ! Cheers ! ? > Fri Aug 21 2015 19:11:03 CEST from "Richard Hipp" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 8/21/15, sqlite-mail wrote: > >>Then do you think this is a bug ? >> > The word "bug" means it gets the wrong answer. > > The absence of a feature is not a bug, unless that feature is a > required part of the specification for the program. The ability to > rename tables and all dependencies is not a required feature in this > case. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Thanks for your attention! Only to remark on this I tested on postgresql and somehow it knows how deal with it ! "so few (none?)" Cheers ! ? > Fri Aug 21 2015 19:08:58 CEST from "J Decker" Subject: >Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ? > > On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail >wrote: > >>Then do you think this is a bug ? >> >>>Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >>> dependencies ? >> > while it may be considered a nice thing; it's not common practice to > rename tables, so few (none?) sql implementations automatically update > references to tables when they are renamed. Not a bug; more like out > of scope. > > It wouldn't know if you were moving a source table for archival > purposes and going to replace it with another empty one or moving > because you didn't like your original schema. > > >> >>>On 21 Aug 2015, at 12:20pm, sqlite-mail wrote: >>> >>> >>> >>>>Does anybody knows how to rename a table and all it's dependencies in one >>>> go >>>> ? >>>> >>>> >>> Can't be done. Sorry. >>> >>> Simon. >>> ___ >>> 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 > > > ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Then do you think this is a bug ? > Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" >Subject: Re: [sqlite] Is this a bug ? How to rename a table and all >dependencies ? > > On 21 Aug 2015, at 12:20pm, sqlite-mail wrote: > > >>Does anybody knows how to rename a table and all it's dependencies in one >>go >> ? >> > Can't be done. Sorry. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Is this a bug ? Pragmas do not accept qualified names !
Thank you ! That's what I want and looking back in the sqlite documentation I can see now that I was misinterpreting it, in reality I was applying the same principle used on other places to qualify/prefix tables/views/... objects but with your help I could realize that pragmas are an exception to the genral rule. ? Again thank you ! ? > Fri Aug 21 2015 16:42:02 CEST from "Scott Hess" >Subject: Re: [sqlite] Is this a bug ? Pragmas do not accept qualified names ! > > I think you wanted: > PRAGMA attached_db.table_info(one_table); > > -scott > > On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail > wrote: > > >>Hello ! >> >> Today I'm working with sqlite3 with attached databases and when I tried to >> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas >> do >> not work with qualified/prefixed names like: >> >> PRAGMA table_info(attached_db.one_table) >> >> >> >> Is this a bug ? >> >> 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] Is this a bug ? Pragmas do not accept qualified names !
Hello ! Today I'm working with sqlite3 with attached databases and when I tried to get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas do not work with qualified/prefixed names like: PRAGMA table_info(attached_db.one_table) ? Is this a bug ? Cheers !
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Hello ! Here I'm fixing some typos and I also tested on postgresql and there all views are updated properly then I'll say is a bug in sqlite. Does anybody knows how to rename a table and all it's dependencies in one go ? ? The problem: a database has several tables and views that reference/use other tables something simplified like this: CREATE TABLE a(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references a(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name as aname from b left join a on b.a_id =a.id; === ? Now if we do "alter table a rename to a2;" actually sqlite only rename the "a" all foreign key references from "a" to "a2" but leaves all views broken. ===schema after "alter table a rename to a2;" CREATE TABLE "a2"(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references "a2"(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name as aname from b left join a on b.a_id =a.id; === Is this a bug ? Cheers ! ?
[sqlite] Is this a bug ? How to rename a table and all dependencies ?
Hello ! Does anybody knows how to rename a table and all it's dependencies in one go ? ? The problem: a database has several tables and views that reference/use other tables something simplified like this: CREATE TABLE a(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references a(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid; === ? Now if we do "alter table a rename to a2;" actually sqlite only rename the "a" all foreign key references from "a" to "a2" but leaves all views broken. ===schema after "alter table a rename to a2;" CREATE TABLE "a2"(id integer primary key, name text); CREATE TABLE b(id integer primary key, a_id integer references "a2"(id), name text); CREATE VIEW aview AS select * from a; CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid; === Is this a bug ? Cheers ! ?
[sqlite] ATTACH DATABASE statement speed
Hello ! The problem with foreign keys most of the time is not the the referenced table/field (normally primary key that do no need extra index) but the dependent table/field when they do not have a proper index, any time you update/delete a record on the referenced table a linear scan is performed on all dependent tables and that can be a lot time consuming depending on the number of records on then. ? I've got this problem on a heavily foreign key constrained database and it took me a bit to realize that ! Cheers ! > @nameless person known as sqlite-mail, > Yes, I do have foreign keys. But each relate to a primary key; there > are no explicit indexes on this primary keys, but they should not be > needed because primary keys are indexed automatically. > Or are they? > > >
[sqlite] ATTACH DATABASE statement speed
Hello ! Do you have foreign keys on your tables ? And if so do you have indexes on then ? A database with foreign keys and no indexes can run very slow for mas insert/update/delete ? Cheers ! ? > Tue Aug 18 2015 12:38:51 CEST from "Paolo Bolzoni" > Subject: Re: [sqlite] ATTACH DATABASE >statement speed > > It really seems something strange happens at filesystem level. > > This is a simple copy of slightly less than 1gb. It needs 9 seconds > including sync. > % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date > Tue Aug 18 19:22:23 JST 2015 > sending incremental file list > italy-latest.osm.pbf > 946,976,283 100% 123.88MB/s 0:00:07 (xfr#1, to-chk=0/1) > Tue Aug 18 19:22:32 JST 2015 > > > However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks > normal for a while. > (I hope gmail don't mess up with the formatting...) > > 60, 90, 80 MB/s is kinda expected: > 08/18/2015 07:27:38 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 820.00 13.00 62.11 0.26 > 153.34 1.87 2.27 1.14 73.46 1.20 99.80 > 0.00 0.00 1214.50 0.00 94.58 0.00 > 159.49 0.96 0.78 0.78 0.00 0.78 95.20 > 0.00 0.00 1008.50 22.00 78.09 0.41 > 155.99 1.50 1.46 0.96 24.16 0.93 95.80 > > but after some seconds it drops terribly to less than 10MB/s > 08/18/2015 07:29:04 PM > Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s > avgrq-sz avgqu-sz await r_await w_await svctm %util > encplate > 0.00 0.00 124.00 3.50 9.88 0.12 > 160.72 1.67 12.99 11.21 76.14 7.65 97.50 > 0.00 0.00 69.00 18.00 5.68 0.29 > 140.55 1.81 20.92 14.15 46.86 11.38 99.00 > 0.00 0.00 86.00 0.00 7.05 0.00 > 167.91 1.04 12.03 12.03 0.00 11.24 96.70 > > And so, going to 10MB per second it can easily require few hours... > > > I am out of ideas, but thanks for all the support. > > > > On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin wrote: > > >>On 18 Aug 2015, at 7:30am, Paolo Bolzoni >>wrote: >> >> >>>Any other idea of what can I try? Perhaps my filesystem is misconfigured? >>> >> The long time you quote is not standard for SQLite and I don't think >>anyone can help you solve it by knowing picky details of SQLite. I'm even >>surprised that it changed with your -O0 compilation since this suggests >>features of your compiler I didn't know about. >> >> It's possible one of the developer team can help but they're reading this >>and can pitch in if they think so. >> >> Simon. >> ___ >> 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] Enhance the SELECT statement?
Hello ! The query you mention is not the same as: INSERT INTO table_of_intergers SELECT seqno FROM generate ORDER BY seqno; Cheers ! > Mon Aug 17 2015 17:44:58 CEST from "John McKown" > Subject: [sqlite] Enhance the SELECT >statement? > > I use both SQLite3 and PostgreSQL. One thing that would be really useful > for me in SQLite3 which is in PostgreSQL is the INTO phrase. The PostgreSQL > documentation this is here: > http://www.postgresql.org/docs/9.4/interactive/sql-selectinto.html > > The main reason that I could use this would be in conjunction with the WITH > clause. A minor example would be: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno SELECT seqno FROM generate INTO table_of_intergers ORDER BY seqno; > > This could also solve the "problem" that another person had, wanting a > sorted sequence of random numbers: > > WITH RECURSIVE generate AS > ( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate WHERE > seqno<), > random_number_generator > ( SELECT RANDOM() AS random_number FROM generate) > SELECT random_number FROM random_number_generator ORDER BY random_number; > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Patch to add sqlite3_trace_v2
Hello !
After seem the question about "how to sync sqlite3 databases" and proposed
the creation/extension of sqlite3_trace function to make it easy to log
changes to the database I implemented what I'll call sqlite3_trace_v2 that
has an extra parameter to indicate if we only want to log sql statements that
modify the database:
void *sqlite3_trace_v2(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg, int onlyMod);
If the "onlyMod" parameter is not zero than only sql statements that do not
return true to "sqlite3_stmt_readonly" call will be traced.
This is a link to a gist on github
https://gist.github.com/mingodad/f32b680c901e360803bb
The license for this contribution is the same of sqlite.
Cheers !
diff -urB sqlite-src-3081101/src/loadext.c sqlite-src-3081101-2/src/loadext.c
--- sqlite-src-3081101/src/loadext.c??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/loadext.c??? 2015-08-14 11:07:01.779944573
+0100
@@ -94,6 +94,7 @@
?#ifdef SQLITE_OMIT_TRACE
?# define sqlite3_profile?? 0
?# define sqlite3_trace 0
+# define sqlite3_trace_v2 0
?#endif
?
?#ifdef SQLITE_OMIT_GET_TABLE
@@ -238,6 +239,7 @@
?#endif
?? sqlite3_total_changes,
?? sqlite3_trace,
+? sqlite3_trace_v2,
?#ifndef SQLITE_OMIT_DEPRECATED
?? sqlite3_transfer_bindings,
?#else
diff -urB sqlite-src-3081101/src/main.c sqlite-src-3081101-2/src/main.c
--- sqlite-src-3081101/src/main.c??? 2015-07-29 21:06:55.0 +0100
+++ sqlite-src-3081101-2/src/main.c??? 2015-08-14 11:07:36613070869 +0100
@@ -1748,7 +1748,7 @@
?** trace is a pointer to a function that is invoked at the start of each
?** SQL statement.
?*/
-void *sqlite3_trace(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg){
+void *sqlite3_trace_v2(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg, int onlyMod){
?? void *pOld;
?
?#ifdef SQLITE_ENABLE_API_ARMOR
@@ -1758,12 +1758,17 @@
?? }
?#endif
?? sqlite3_mutex_enter(db->mutex);
+? if(onlyMod) db->flags |= SQLITE_SqlTraceModOnly;
+? else db->flags &= ~SQLITE_SqlTraceModOnly;
?? pOld = db->pTraceArg;
?? db->xTrace = xTrace;
?? db->pTraceArg = pArg;
?? sqlite3_mutex_leave(db->mutex);
?? return pOld;
?}
+void *sqlite3_trace(sqlite3 *db, void (*xTrace)(void*,const char*), void
*pArg){
+? return sqlite3_trace_v2(db, xTrace, pArg, 0);
+}
?/*
?** Register a profile function.? The pArg from the previously registered
?** profile function is returned.?
diff -urB sqlite-src-3081101/src/sqlite3ext.h
sqlite-src-3081101-2/src/sqlite3ext.h
--- sqlite-src-3081101/src/sqlite3ext.h??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqlite3ext.h??? 2015-08-14 11:08:19.798466419
+0100
@@ -143,6 +143,7 @@
?? void? (*thread_cleanup)(void);
?? int? (*total_changes)(sqlite3*);
?? void * (*trace)(sqlite3*,void(*xTrace)(void*,const char*),void*);
+? void * (*trace_v2)(sqlite3*,void(*xTrace)(void*,const char*),void*,int);
?? int? (*transfer_bindings)(sqlite3_stmt*,sqlite3_stmt*);
?? void * (*update_hook)(sqlite3*,void(*)(void*,int ,char const*,char
const*,
???
?? sqlite_int64),void*);
@@ -394,6 +395,7 @@
?#define sqlite3_thread_cleanup sqlite3_api->thread_cleanup
?#define sqlite3_total_changes? sqlite3_api->total_changes
?#define sqlite3_trace? sqlite3_api->trace
+#define sqlite3_trace_v2?? sqlite3_api->trace_v2
?#ifndef SQLITE_OMIT_DEPRECATED
?#define sqlite3_transfer_bindings? sqlite3_api->transfer_bindings
?#endif
diff -urB sqlite-src-3081101/src/sqlite.h.in
sqlite-src-3081101-2/src/sqlite.h.in
--- sqlite-src-3081101/src/sqlite.h.in??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqlite.h.in??? 2015-08-14 11:05:14.484471489
+0100
@@ -2708,6 +2708,7 @@
?** subject to change in future versions of SQLite.
?*/
?void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);
+void *sqlite3_trace_v2(sqlite3*, void(*xTrace)(void*,const char*), void*,
int);
?SQLITE_EXPERIMENTAL void *sqlite3_profile(sqlite3*,
??? void(*xProfile)(void*,const char*,sqlite3_uint64), void*);
?
diff -urB sqlite-src-3081101/src/sqliteInt.h
sqlite-src-3081101-2/src/sqliteInt.h
--- sqlite-src-3081101/src/sqliteInt.h??? 2015-07-29 21:06:55.0
+0100
+++ sqlite-src-3081101-2/src/sqliteInt.h??? 2015-08-14 11:09:56.661611593
+0100
@@ -1287,6 +1287,7 @@
?#define SQLITE_VdbeEQP??? 0x0400? /* Debug EXPLAIN QUERY
PLAN */
?#define SQLITE_Vacuum 0x0800? /* Currently in a VACUUM
*/
?#define SQLITE_CellSizeCk 0x1000? /* Check btree cell sizes on
load */
+#define SQLITE_SqlTraceModOnly 0x8000? /* Only output queries
that modify the database */
?
?
?/*
[sqlite] Database sybchronisation
Hello ! ? This request is a common requirement and in my opinion would be better solved by an extension to the actual sqlite3 api functions. ? Actually there is sqlite3_trace that can be used to watch all sql statements executed on a given session. ? One possible way would be to add another parameter that could be a bitwise flag to indicate wich kind of statements we want to watch. ? With that would be trivial to log the statements that change the database to replicate elsewhere. ? ? ? #define SQLITE_TRACE_DML 0x0001 ? #define SQLITE_TRACE_SELECT 0x0002 ? #define SQLITE_TRACE_INSERT 0x0004 ? #define SQLITE_TRACE_UPDATE 0x0008 ? #define SQLITE_TRACE_DELETE 0x0100 ? void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*, int what_to_trace); ? ? ? Cheers ! ?
[sqlite] Small bug in ".dump", ".schema" and ".fullschema"
Hello ! Working with sqlite3 I noticed that sqlite3 ".dump", ".schema" and ".fullschema" outputs the contents of the field "sql" stored in "sqlite_master" and if the sql statement ends with a comment the resulted dump will be invalid see example: === valid sql statement stored on sqlite_master CREATE VIEW "event_event_ticket_list_view" AS SELECT a."id", a."name", a."price", a."deadline", a."seats_max", a."product_id", a."event_id" FROM "event_event_ticket" AS a --LEFT JOIN "product_product" AS b ON a."product_id" = b."id" === ? Sqlite3 only adds a semicolon to the value of "sql" field and in this case we have an unterminated statement. === the above sql statement dumped by sqlite3 CREATE VIEW "event_event_ticket_list_view" AS SELECT a."id", a."name", a."price", a."deadline", a."seats_max", a."product_id", a."event_id" FROM "event_event_ticket" AS a --LEFT JOIN "product_product" AS b ON a."product_id" = b."id";?? < here is the problem === ? Cheers !
[sqlite] Patch that add ".dumpdata" and "BEGIN TRANSACTION; " command to shell.c
Hello again !
After sending the first patch I also realized that when sqlite3 dumps
".schema" or ".fullschema" it doesn't surround the dump with a transaction
and that takes longer and makes the hard disk work hard. So I also surrounded
".schema" and ".fullschema" with a transaction with this extended patch.
Again the same license of sqlite apply to this patch.
=patch to shell.c
--- shell.c
+++ shell.c
@@ -550,10 +550,12 @@
?? sqlite3_stmt *pStmt;?? /* Current statement if any */
?? FILE *pLog;??? /* Write log output here */
?? int *aiIndent; /* Array of indents used in MODE_Explain
*/
?? int nIndent;?? /* Size of array aiIndent[] */
?? int iIndent;?? /* Index of current op in aiIndent[] */
+? int dumpDataOnly; /*when dump a database exclude schema */
+? int doStartTransaction; /* when dumping schema only before first record
output "BEGIN;" */
?};
?
?/*
?** These are the allowed shellFlgs values
?*/
@@ -908,10 +910,11 @@
}
?? }
?? break;
}
case MODE_Semi:
+? if((p->cnt == 0) && p->doStartTransaction ) fprintf(p->out,"BEGIN
TRANSACTION;\n");
case MODE_List: {
?? if( p->cnt++==0 && p->showHeader ){
for(i=0; iout,"%s%s",azCol[i],
?? i==nArg-1 ? p->rowSeparator :
p->colSeparator);
@@ -1658,31 +1661,33 @@
?? if( nArg!=3 ) return 1;
?? zTable = azArg[0];
?? zType = azArg[1];
?? zSql = azArg[2];
??
-? if( strcmp(zTable, "sqlite_sequence")==0 ){
-??? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
-? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
-??? fprintf(p->out, "ANALYZE sqlite_master;\n");
-? }else if( strncmp(zTable, "sqlite_", 7)==0 ){
-??? return 0;
-? }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
-??? char *zIns;
-??? if( !p->writableSchema ){
-? fprintf(p->out, "PRAGMA writable_schema=ON;\n");
-? p->writableSchema = 1;
-??? }
-??? zIns = sqlite3_mprintf(
-?? "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
-?? "VALUES('table','%q','%q',0,'%q');",
-?? zTable, zTable, zSql);
-??? fprintf(p->out, "%s\n", zIns);
-??? sqlite3_free(zIns);
-??? return 0;
-? }else{
-??? fprintf(p->out, "%s;\n", zSql);
+? if( !p->dumpDataOnly ){
+??? if( strcmp(zTable, "sqlite_sequence")==0 ){
+? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
+??? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
+? fprintf(p->out, "ANALYZE sqlite_master;\n");
+??? }else if( strncmp(zTable, "sqlite_", 7)==0 ){
+? return 0;
+??? }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
+? char *zIns;
+? if( !p->writableSchema ){
+??? fprintf(p->out, "PRAGMA writable_schema=ON;\n");
+??? p->writableSchema = 1;
+? }
+? zIns = sqlite3_mprintf(
+ "INSERT INTO
sqlite_master(type,name,tbl_name,rootpage,sql)"
+ "VALUES('table','%q','%q',0,'%q');",
+ zTable, zTable, zSql);
+? fprintf(p->out, "%s\n", zIns);
+? sqlite3_free(zIns);
+? return 0;
+??? }else{
+? fprintf(p->out, "%s;\n", zSql);
+??? }
?? }
?
?? if( strcmp(zType, "table")==0 ){
sqlite3_stmt *pTableInfo = 0;
char *zSelect = 0;
@@ -1789,10 +1794,11 @@
?? ".databases List names and files of attached
databases\n"
?? ".dbinfo ?DB??? Show status information about the
database\n"
?? ".dump ?TABLE? ...? Dump the database in an SQL text format\n"
?? " If TABLE specified,
only dump tables matching\n"
?? " LIKE pattern TABLE.\n"
+? ".dumpdata? ?TABLE? ... Like .dump without schema\n"
?? ".echo on|off?? Turn command echo on or off\n"
?? ".eqp on|off??? Enable or disable automatic EXPLAIN
QUERY PLAN\n"
?? ".exit? Exit this program\n"
?? ".explain ?on|off?? Turn output mode suitable for EXPLAIN on or
off.\n"
?? " With no args, it turns
EXPLAIN on.\n"
@@ -2770,11 +2776,12 @@
?
?? if( c=='d' && strncmp(azArg[0], "dbinfo", n)==0 ){
rc = shell_dbinfo_command(p, nArg, azArg);
?? }else
?
-? if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
+? if( c=='d' && ((strncmp(azArg[0], "dump", n)==0) ||
+? (p->dumpDataOnly = (strncmp(azArg[0], "dumpdata", n)==0))) ){
open_db(p, 0);
/* When playing back a "dump", the content might appear in an order
** which causes immediate foreign key constraints to be violated.
** So disable foreign-key constraint enforcement to prevent
problems. */
if( nArg!=1 && nArg!=2 ){
@@ -2790,32 +2797,36 @@
if( nArg==1 ){
?? run_schema_dump_query(p,
"SELECT name, type, sql FROM sqlite_master "
"WHERE sql NOT NULL AND type=='table' AND
name!='sqlite_sequence'"
?? );
-? run_schema_dump_query(p,
-??? "SELECT name, type, sql FROM sqlite_master "
-??? "WHERE name=='sqlite_sequence'"
-? );
-? run_table_dump_query(p,
-??? "SELECT sql FROM sqlite_master "
-??? "WHERE sql NOT NULL AND
[sqlite] Patch that add ".dumpdata" command to shell.c
Hello ! Here is a small patch that adds ".dumpdata" for "shell.c" in sqlite3. This command should behave exactly like ".dump" but without the database schema. I 'm giving it with the same license as sqlite. Cheers !
[sqlite] Bugs/improvements to sqlite constraints
Hello !
After submitting several emails with subject "Bug in
sqlite3_trace/trigger/delete" and only one reply to then so far I decided to
look a bit more deep on the problem I'm facing using sqlite3 with one
specific database and created a simpler database that can show several
problems and opportunities for improvements in sqlite3.
I probably only discovery this problem because I was using sqlite3_trace to
output the sql from a server application I'm doing.
The bugs/opportunities for improvements found:
1- Missing comma separating table constraints not reported as error.
2- Duplicated table constraints not reported as error.
3- The sqlite3_trace hook function been called with misleading info from
sqlite3 internal DML operations to satisfy "ON DELETE SET NULL". See bellow
the output of the C program with comments. Some applications use the output
of sqlite3_trace to replicate the database and having internal only
operations been send to it will create problems.
4- Sqlite3 do not perform any optimization by joining "table scans" searching
for the same value on more than one column on the same table. See bellow the
output of sqlite3 test-fkbugs.db "explain query plan delete from aa where
id=10";
?
Based on this experience I'm suggesting to remove the output of internal
operations from sqlite3_trace (see the dml operations to satisfy "ON DELETE
SET NULL") and have another trace hook "sqlite3_trace_explain_query" that
would also show at high level the internal sqlite3 operations a kind of mix
of sqlite3_trace + "explain" that would give for this database example an
output like this:
---
/test-sqlite-bug
SQL: INSERT INTO "aa"("id","name") VALUES(10, 'daddad')
SQL: DELETE FROM aa WHERE id=10
0|0|0|SEARCH TABLE aa USING INTEGER PRIMARY KEY (rowid=?)
SQL: -- TRIGGER aa_delete_trigger
0|0|0|SCAN TABLE tbl for constrained_fkey_aa_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ab_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ac_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold
not exists if detected on create table
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold
not exists if detected on create table
0|0|0|SCAN TABLE tbl for constrained_fkey_ad_id << duplicated constraint wold
not exists if detected on create table
0|0|0|UPDATE TABLE tbl constrained_fkey_aa_id? "ON DELETE SET NULL"
0|0|0|UPDATE TABLE tbl constrained_fkey_aa_id? "ON DELETE SET NULL"
---
?
- the database "test-fkbugs.db"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE aa(id INTEGER PRIMARY KEY, name TEXT);
INSERT OR IGNORE INTO aa(id, name) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
CREATE TABLE IF NOT EXISTS tbl(
??? id? INTEGER PRIMARY KEY,
??? name varchar,
??? a_id INTEGER,
??? b_id INTEGER,
??? c_id INTEGER,
??? d_id INTEGER,
??? CONSTRAINT constrained_fkey_aa_id FOREIGN KEY(a_id) REFERENCES aa(id)
ON DELETE SET NULL?? -- missing comma separator not detected
??? CONSTRAINT constrained_fkey_ab_id FOREIGN KEY(b_id) REFERENCES aa(id)
ON DELETE SET NULL,
??? CONSTRAINT constrained_fkey_ac_id FOREIGN KEY(c_id) REFERENCES
aa(id)? -- missing comma separator
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
-- duplicated constraint not detected
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
-- duplicated constraint not detected
??? CONSTRAINT constrained_fkey_ad_id FOREIGN KEY(d_id) REFERENCES aa(id)
-- duplicated constraint not detected
);
INSERT OR IGNORE INTO tbl(id, name) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
CREATE TRIGGER IF NOT EXISTS aa_delete_trigger BEFORE DELETE ON aa
BEGIN
??? SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE OLD.id =
1;
END;
COMMIT;
-
- the C program to see the sqlite3_trace/constraint problem
"test-sqlite-bug.c"
#include
#include "sqlite3.h"
static void db_trace_callback(void *user, const char *sql)
{
??? printf("SQL: %s\n", sql ? sql : "??");
}
int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? int rc = sqlite3_open("test-fkbugs.db", );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? const char insert_szSQL[] = "INSERT INTO aa(id,name) VALUES(10,
'daddad')";
??? ??? const char delete_szSQL[] = "DELETE FROM aa WHERE id=10";
??? ??? sqlite3_trace(db, db_trace_callback, NULL);
??? ??? rc = sqlite3_exec(db, insert_szSQL, NULL, NULL, );
??? ??? rc = sqlite3_exec(db, delete_szSQL, NULL, NULL, );
??? ??? sqlite3_close(db);
??? }
??? return 0;
}
-
- the shell script to compile the C program
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 \
???
[sqlite] Bug in sqlite3_trace/trigger/delete
art 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 \ > ??? -DTHREADSAFE=1 \ > ??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \ > ??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \ > ??? -DSQLITE_DEFAULT_FOREIGN_
[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 "sqlit
[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 integer primary key, name > varchar); > insert or ignore into tbl(id, name
[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, );
}
?
int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? int rc = sqlite3_open("../odoo.db", );
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
