Re: [sqlite] Detecting multiple CHECK failures

[gwenn]

Hello,
I think that SQLite reports the first constraint which fails:

http://sqlite.org/changes.html
2012-05-14 (3.7.12)
Report the name of specific CHECK constraints that fail.

sqlite> CREATE TABLE test (data TEXT CONSTRAINT notEmpty CHECK
(length(data) > 0));
sqlite> INSERT INTO test VALUES ('');
Error: CHECK constraint failed: notEmpty

Regards.

On Tue, Oct 7, 2014 at 11:11 PM, Simon Slavin  wrote:
>
> On 7 Oct 2014, at 10:00pm, Peter Haworth  wrote:
>
>> I'm a great believer in using CHECK constraints to do as much validation as
>> possible within the database rather than code it in my application.
>>
>> However, I think I'm right in saying that as soon as a CHECK constraint
>> fails, an error is returned to my application so no other CHECK constraints
>> are executed  In a data entry type of application, this isn't ideal as
>> users would prefer to see all the errors they need to correct in one
>> message.
>
> For most ways in which SQLite can refuse to do something, you have no way to 
> know why it refused.  The results don't include the name of a constraint 
> which failed, or anything else of any use.  You simply get a result code 
> which tells you that the operation failed because of the data in your command 
> (rather than because the command had bad syntax or referred to a 
> table/index/column which didn't exist).
>
>> I can't think of a way round this but wondering if anyone has found a
>> technique to return all CHECK constraint errors at once.
>
> It would appear that in SQLite the CHECK constraints are useful only in 
> ensuring your database doesn't reflect things that are impossible.  It is of 
> no use at all in knowing why a command is rejected.
>
> Ideally, if a result code indicates a constraint failure, there would be a 
> way to retrieve a list of the names of the constraints which would have been 
> violated.  However this is not possible in SQLite3 at all without a major 
> rewrite.  SQLite3 just gets a binary indication of whether any constraints 
> were violated.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missing db name with the SQLITE_ATTACH action callback

[gwenn]

Hello,
I am trying to implement a cache of one connection metadata
(databases, tables, columns, indexes).
It seems possible to automatically update the cache by using an authorizer.
But there is one problem with the SQLITE_ATTACH action:
#define SQLITE_ATTACH   24   /* FilenameNULL*/
#define SQLITE_DETACH   25   /* Database Name   NULL*/
Only the filename is available and is optional/not unique (for
":memory:" and "" temp database).
Would you mind adding the database name as the fourth argument of the
authorizer callback ?
Thanks and regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regression with pragma index_list version 3.8.9

[gwenn]

Hello,
There are two extra columns (origin, partial) in the result returned
by pragma index_list.
Just for your information, it causes a regression test failure because
only 3 columns are expected:
--- FAIL: TestTableIndexes (0.00s)
meta_test.go:163: error listing indexes: incorrect argument count for
Stmt.Scan: have 3 want 5 (wrapper specific error)

if err = s.Scan(nil, , ); err != nil {

I will patch my code...
Regards.

[sqlite] sqlite_column_blob & invalid index

[gwenn]

Hello,
Could you please tell me if sqlite3_errcode/sqlite3_errmsg should be
called after each call to sqlite3_column_blob/text/int/... to check
that the column index is valid ?

I am confused by:
> http://sqlite.org/rescode.html#range
> The SQLITE_RANGE error indices that the parameter number argument to one of 
> the sqlite3_bind routines is out of range.
Here , there is no mention to sqlite3_column_blob.

> http://sqlite.org/c3ref/column_blob.html
> if the column index is out of range, the result is undefined.
Here, there is no mention to SQLITE_RANGE.

> http://www.sqlite.org/cgi/src/artifact/583d56b129dd27f1
> static Mem *columnMem(sqlite3_stmt *pStmt, int i){
> ...
>   sqlite3Error(pVm->db, SQLITE_RANGE);
> ...
> }

> http://www.sqlite.org/cgi/src/artifact/40e333960d53f7d5
> const char *sqlite3ErrStr(int rc){
> ...
>/* SQLITE_RANGE   */ "bind or column index out of range",
> ...
> }
Here, there is a mention to "column index out of range"

Regards.

[sqlite] sqlite_column_blob & invalid index

[gwenn]

Thanks for reply.
Could you please elaborate ?
For me, there is no way to know if the specified column index is
invalid by using the column value returned by
sqlite3_column_blob/text/int .
Regards.

On Sun, Apr 12, 2015 at 11:50 AM, Stephan Beal  wrote:
> On Sun, Apr 12, 2015 at 11:48 AM, gwenn  wrote:
>
>> Hello,
>> Could you please tell me if sqlite3_errcode/sqlite3_errmsg should be
>> called after each call to sqlite3_column_blob/text/int/... to check
>> that the column index is valid ?
>>
>
> Not needed - simply check the result code of your call to
> sqlite3_column_xxx(). (Users should always check the result codes of their
> sqlite3 API calls.)
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite_column_blob & invalid index

[gwenn]

Ok,
Thanks.

On Sun, Apr 12, 2015 at 2:08 PM, Stephan Beal  wrote:
> On Sun, Apr 12, 2015 at 2:04 PM, gwenn  wrote:
>
>> Thanks for reply.
>> Could you please elaborate ?
>> For me, there is no way to know if the specified column index is
>> invalid by using the column value returned by
>> sqlite3_column_blob/text/int .
>>
>
> i was mistaken: the docs say:
>
>
> "If the SQL statement does not currently point to a valid row, or if the
> column index is out of range, the result is undefined. "
>
> But you can figure out the count:
>
> http://sqlite.org/c3ref/column_count.html
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Redundant link in documentation

[gwenn]

Hi,
In the following page:
http://sqlite.org/c3ref/bind_parameter_index.html
there is a link to itself: sqlite3_bind_parameter_index().

Maybe it should have been a link to:
http://sqlite.org/c3ref/bind_parameter_name.html
?
Regards.

[sqlite] Name the arguments in the prototypes.

[gwenn]

Hello,
Would you mind adding argument names in function prototypes ?
http://www.sqlite.org/cgi/src/artifact/1248a78548024bdc

SQLITE_API int SQLITE_STDCALL sqlite3_busy_handler(sqlite3*,
int(*)(void*,int), void*);
versus
SQLITE_API int SQLITE_STDCALL sqlite3_busy_handler(sqlite3 *db,
int(*xBusy)(void *pArg, int count), void *pArg);
...
SQLITE_API SQLITE_EXPERIMENTAL void *SQLITE_STDCALL sqlite3_profile(sqlite3*,
   void(*xProfile)(void*,const char*,sqlite3_uint64), void*);
versus
SQLITE_API SQLITE_EXPERIMENTAL void *SQLITE_STDCALL sqlite3_profile(sqlite3 *db,
   void(*xProfile)(void *pArg, const char *sql, sqlite3_uint64
nanoseconds), void *pArg);
...
SQLITE_API int SQLITE_STDCALL sqlite3_errcode(sqlite3 *db);
SQLITE_API int SQLITE_STDCALL sqlite3_extended_errcode(sqlite3 *db);
SQLITE_API const char *SQLITE_STDCALL sqlite3_errmsg(sqlite3*);
versus
SQLITE_API const char *SQLITE_STDCALL sqlite3_errmsg(sqlite3 *db);
...

I can give it a try and send you a patch if you want.
Regards.

[sqlite] Name the arguments in the prototypes.

[gwenn]

Maybe because I am lazy.
I can read all the documentation attached to the function prototype.
But I can quickly glean what a function does from its prototype.
And it helps autocompletion.

Thanks.

On Sun, Dec 6, 2015 at 12:16 PM, Richard Hipp  wrote:
> On 12/6/15, gwenn  wrote:
>> Hello,
>> Would you mind adding argument names in function prototypes ?
>
> Would you mind explaining why this might be helpful?
>
> --
> 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] Geting the errorcode in Java

[gwenn]

Hello,
Your code looks good to me.
You should report an issue here: https://github.com/xerial/sqlite-jdbc
Regards.

On Mon, Dec 14, 2015 at 8:38 PM, Cecil Westerhof  
wrote:
> I have the following code:
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.Statement;
> import java.sql.SQLException;
>
>
> public class CheckProverbsLocked {
> private CheckProverbsLocked() {
> }
>
> public static void main(String [] args) throws Exception {
> Connection   conn;
> Statementstmt;
>
> Class.forName("org.sqlite.JDBC");
> conn = DriverManager.getConnection("jdbc:sqlite:proverbs.sqlite");
> stmt  = conn.createStatement();
> try {
> stmt.executeUpdate("begin immediate");
> } catch (SQLException e) {
> System.out.println(e.getErrorCode());
> System.out.println(e.getMessage());
> System.out.println(e.getSQLState());
> }
> stmt.close();
> conn.close();
> }
>
> }
>
>
> I get the following output when the database is locked:
> 0
> database is locked
> null
>
> I would expect the first one to be 5. What am I doing wrong?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange behaviour of sqlite3_stmt_busy

[gwenn]

Hello,
sqlite3_stmt_busy returns true after sqlite3_step returns DONE.

Here is the code:
#include 
#include 
#include "sqlite3.h"

int main(int argc, char **argv) {
sqlite3 *db = NULL;
sqlite3_stmt *stmt = NULL;
char *zErrMsg = NULL;
const char *z;
int rc = 0;
rc = sqlite3_open_v2("", , SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL);
if (db == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: unable to open database: %s\n",
sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_exec(db, "BEGIN EXCLUSIVE", NULL, NULL, NULL);
if (SQLITE_OK != rc) {
fprintf(stderr, "Error: tx start: %s\n", sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_prepare_v2(db, "ROLLBACK", -1, , NULL);
if (stmt == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_stmt_busy(stmt);
printf("%s busy before step? %d\n", sqlite3_sql(stmt), rc);
rc = sqlite3_step(stmt);
if (SQLITE_DONE != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}

rc = sqlite3_stmt_busy(stmt);
printf("%s busy after step? %d\n", sqlite3_sql(stmt), rc);

rc = sqlite3_reset(stmt);
if (SQLITE_OK != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}

rc = sqlite3_stmt_busy(stmt);
printf("%s busy after reset? %d\n", sqlite3_sql(stmt), rc);

sqlite3_finalize(stmt);
sqlite3_close(db);
}

And the output:
ROLLBACK busy before step? 0
ROLLBACK busy after step? 1
ROLLBACK busy after reset? 0

SQLite version 3.8.10.2
Darwin Kernel Version 14.4.0

Why does sqlite3_stmt_busy return true even on stmt completion (DONE) ?

Regards.

[sqlite] Strange behaviour of sqlite3_stmt_busy

[gwenn]

Sorry, I misread the documentation.

And do you recommend the strategy used in tclsqlite.c:

  rcs = sqlite3_step(pStmt);
  if( rcs==SQLITE_ROW ){
return TCL_OK;
  }
  ...
  rcs = sqlite3_reset(pStmt);
  ...


Reset the stmt as soon as possible after sqlite3_step (except on SQLITE_ROW) ?
Thanks.

On Fri, Jul 31, 2015 at 12:02 PM, Clemens Ladisch  wrote:
> Stephan Beal wrote:
>> On Thu, Jul 30, 2015 at 11:35 PM, Clemens Ladisch 
>> wrote:
>>> gwenn wrote:
>>>> sqlite3_stmt_busy returns true after sqlite3_step returns DONE.
>>>
>>> The documentation says:
>>> | The sqlite3_stmt_busy(S) interface returns true (non-zero) if the
>>> | prepared statement S has been stepped at least once using
>>> | sqlite3_step(S) but has not run to completion and/or has not been
>>> | reset using sqlite3_reset(S).
>>>
>>> The statement has not been reset, and that "and/or" can be read as "or".
>>
>> Does that means that SQLITE_DONE does _not_ mean "has run to completion"?
>
> No.
>
> Let me rephrase that doc snippet:
> "sqlite3_stmt_busy() returns true if A but not B and/or not C" (where
> A = "stepped", B = "SQLITE_DONE", C = "reset").
>
> I do not know if the "but not" and "and/or" operators use the SQL
> precedence rules.  :)  But there exists a way of interpreting this
> sentence that matches the actual behavuour.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[gwenn]

Hello,
I am not sure but it seems there is a regression between versions
3.7.17 and 3.8.0.
It's impacting custom/user declared function and auxiliary data.

sqlite-amalgamation-3071700 gwen$ gcc
-I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
-I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
sqlite-amalgamation-3071700 gwen$ ./auxdata
loop 1
(0) compiling...
z
(0) reusing...
y
loop 2
(0) reusing...
z
(0) reusing...
y

sqlite-amalgamation-3080300 gwen$ gcc
-I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
-I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
sqlite-amalgamation-3080300 gwen$ ./auxdata
loop 1
(0) compiling...
z
(0) reusing...
y
loop 2
(0) compiling...
z
(0) reusing...
y

The auxiliary data is reused in the second loop with SQLite 3.7.17 but
not with SQLite 3.8.0.
What is the expected/correct behaviour?

Regards

Here is the content of auxdata.c:
#include 
#include 
#include 
#include "sqlite3.h"

static void log(void *pArg, int iErrCode, const char *zMsg) {
printf("(%d) %s\n", iErrCode, zMsg);
}

static void glibRegexpDelete(void *p){
  GRegex *pRegex = (GRegex *)p;
  g_regex_unref(pRegex);
}

static void glibReplaceAllFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
GError *err = NULL;
GRegex *p;
gchar *result = NULL;

(void)argc;  /* Unused parameter */

const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
if (!str) {
return;
}

const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
if (!replacement) {
sqlite3_result_error(ctx, "no replacement string", -1);
return;
}

p = sqlite3_get_auxdata(ctx, 0);
if( !p ){
const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
if( !re ){
//sqlite3_result_error(ctx, "no regexp", -1);
return;
}
p = g_regex_new(re, 0, 0, );

if( p ){
sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
}else{
char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
sqlite3_result_error(ctx, e2, -1);
sqlite3_free(e2);
g_error_free(err);
return;
}
sqlite3_log(0, "compiling...");
} else {
sqlite3_log(0, "reusing...");
}

result = g_regex_replace(p, str, -1, 0, replacement, 0, );
if (err) {
sqlite3_result_error(ctx, err->message, -1);
g_error_free(err);
return;
}
sqlite3_result_text(ctx, result, -1, g_free);
}

int main(int argc, char **argv) {
sqlite3_config(SQLITE_CONFIG_LOG, log, NULL);
sqlite3 *db = NULL;
sqlite3_stmt *stmt = NULL;
char *zErrMsg = NULL;
const char *z;
int rc = 0;
rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (db == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: unable to open database: %s\n", sqlite3_errmsg(db));
exit(1);
}
sqlite3_create_function_v2(db, "regex_replace", 3, SQLITE_UTF8, 0,
glibReplaceAllFunc, NULL, NULL, NULL);
  rc = sqlite3_prepare_v2(db, "select regex_replace('.', 'abcde', r)
from (select 'z' as r union all select 'y')", -1, , NULL);
  if (stmt == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
exit(1);
  }
  for (int i = 1; i <= 2; i++) {
  printf("loop %d\n", i);
 rc = sqlite3_step(stmt);
 while (rc == SQLITE_ROW) {
  z = (const char*)sqlite3_column_text(stmt, 0);
  printf("%s\n", z);
  rc = sqlite3_step(stmt);
 }
 if (SQLITE_OK != rc && SQLITE_DONE != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
 rc = sqlite3_reset(stmt);
 if (SQLITE_OK != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
}
  sqlite3_finalize(stmt);
sqlite3_close(db);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[gwenn]

Here you are:

#include 
#include 
#include "sqlite3.h"

static void reuseAuxDataCountFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
int *reuseAuxDataCount;
int value;
(void)argc;  /* Unused parameter */

reuseAuxDataCount = (int*)sqlite3_get_auxdata(ctx, 0);
if (reuseAuxDataCount == NULL) {
reuseAuxDataCount = (int *)malloc(sizeof(int));
if (reuseAuxDataCount == NULL) {
sqlite3_result_error_nomem(ctx);
return;
}
*reuseAuxDataCount = 0;
sqlite3_set_auxdata(ctx, 0, reuseAuxDataCount, free);
} else {
(*reuseAuxDataCount)++;
}
sqlite3_result_int(ctx, *reuseAuxDataCount);
}

int main(int argc, char **argv) {
sqlite3 *db = NULL;
sqlite3_stmt *stmt = NULL;
char *zErrMsg = NULL;
const char *z;
int rc = 0;
rc = sqlite3_open_v2(":memory:", , SQLITE_OPEN_FULLMUTEX |
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (db == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: unable to open database: %s\n", sqlite3_errmsg(db));
exit(1);
}
sqlite3_create_function_v2(db, "reuseAuxDataCountFunc", 1,
SQLITE_UTF8, 0, reuseAuxDataCountFunc, NULL, NULL, NULL);
// at least, one constant must be passed to make SQLite reuse auxiliary data...
  rc = sqlite3_prepare_v2(db, "select reuseAuxDataCountFunc('test')
from (select 1 union all select 2)", -1, , NULL);
  if (stmt == NULL || SQLITE_OK != rc) {
fprintf(stderr, "Error: prepare stmt: %s\n", sqlite3_errmsg(db));
exit(1);
  }
  for (int i = 1; i <= 2; i++) {
  printf("loop %d\n", i);
 rc = sqlite3_step(stmt);
 while (rc == SQLITE_ROW) {
  z = (const char*)sqlite3_column_text(stmt, 0);
  printf("%s\n", z);
  rc = sqlite3_step(stmt);
 }
 if (SQLITE_OK != rc && SQLITE_DONE != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
 rc = sqlite3_reset(stmt);
 if (SQLITE_OK != rc) {
fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
exit(1);
}
}
  sqlite3_finalize(stmt);
sqlite3_close(db);
}

sqlite-amalgamation-3071700 gwen$ ./auxdata
loop 1
0
1
loop 2
2
3

sqlite-amalgamation-3080300 gwen$ ./auxdata
loop 1
0
1
loop 2
0
1

But it appears that SQLite is behaving as specified in:
http://sqlite.org/c3ref/get_auxdata.html
"SQLite is free to discard the metadata at any time, including:
...
when sqlite3_reset() or sqlite3_finalize() is called for the SQL statement, or
...
"

Sorry for the false alarm.
I will try to find another strategy to keep the compiled regexp...
Regards.

On Sun, Feb 9, 2014 at 7:34 PM, Richard Hipp <d...@sqlite.org> wrote:
> Can you provide an example program that omits the glib.h dependency?
>
>
> On Sun, Feb 9, 2014 at 10:50 AM, gwenn <gwenn.k...@gmail.com> wrote:
>
>> Hello,
>> I am not sure but it seems there is a regression between versions
>> 3.7.17 and 3.8.0.
>> It's impacting custom/user declared function and auxiliary data.
>>
>> sqlite-amalgamation-3071700 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3071700 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) reusing...
>> z
>> (0) reusing...
>> y
>>
>> sqlite-amalgamation-3080300 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3080300 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>>
>> The auxiliary data is reused in the second loop with SQLite 3.7.17 but
>> not with SQLite 3.8.0.
>> What is the expected/correct behaviour?
>>
>> Regards
>>
>> Here is the content of auxdata.c:
>> #include 
>> #include 
>> #include 
>> #include "sqlite3.h"
>>
>> static void log(void *pArg, int iErrCode, const char *zMsg) {
>> printf("(%d) %s\n", iErrCode, zMsg);
>> }
>>
>> static void glibRegexpDelete(void *p){
>>   GRegex *pRegex = (GRegex *)p;
>>   g_regex_unref(pRegex);
>> }
>>
>> static void glibReplaceAllFunc(
>>   sqlite3_context *ctx,
>>   int argc,
>>   sqlite3_value **argv
>> ){
>> GError *err = NULL;
>> GRegex *p;
>> gchar *result = NULL;
>>
>> (void)argc;  /* Unused parameter */
>>
>> const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
>> if (!str) {
>> return;
>

Re: [sqlite] Different behaviour of auxiliary data between 3.7.17 and 3.8.0

[gwenn]

Yes, you are right.
Thanks for the investigation.


On Sun, Feb 9, 2014 at 11:54 PM, Richard Hipp <d...@sqlite.org> wrote:
> This behavior change is in response to ticket
> http://www.sqlite.org/src/info/406d3b2ef9 - a diff across several check-ins
> that makes this change can be seen here:
>
>
> http://www.sqlite.org/src/vdiff?from=b1b0de29fdf7de83=62465ecba7431e1d=1=25
>
> Note that the behavior changes brings the implementation into agreement
> with the historical documentation.  The document was clarified and enhanced
> as part of this change.  But the key statements in the old documentation
> where:
>
> "If [the sqlite3_set_auxdata destructor] is not NULL, SQLite will invoke
> the destructor function given by the 4th parameter to sqlite3_set_auxdata()
> on the metadata when the corresponding function parameter changes or when
> the SQL statement completes, whichever comes first. SQLite is free to call
> the destructor and drop metadata on any parameter of any function at any
> time. The only guarantee is that the destructor will be called before the
> metadata is dropped."
>
> The corresponding text in the revised documentation is similar:
>
> "SQLite is free to discard the metadata at any time, including:
>   *  when the corresponding function parameter changes, or
>   * when [sqlite3_reset()] or [sqlite3_finalize()] is called for the  SQL
> statement, or
>   * when sqlite3_set_auxdata() is invoked again on the same parameter, or
>   * during the original sqlite3_set_auxdata() call when a memory
> allocation error occurs. "
>
> The revised documentation is on the website here:
> http://www.sqlite.org/c3ref/get_auxdata.html
>
> So as far as I can tell, the current implementation is doing what it is
> suppose to do. Or did I misunderstand the complaint?
>
>
>
>
>
> On Sun, Feb 9, 2014 at 10:50 AM, gwenn <gwenn.k...@gmail.com> wrote:
>
>> Hello,
>> I am not sure but it seems there is a regression between versions
>> 3.7.17 and 3.8.0.
>> It's impacting custom/user declared function and auxiliary data.
>>
>> sqlite-amalgamation-3071700 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3071700 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) reusing...
>> z
>> (0) reusing...
>> y
>>
>> sqlite-amalgamation-3080300 gwen$ gcc
>> -I/usr/local/Cellar/glib/2.38.2/include/glib-2.0
>> -I/usr/local/Cellar/glib/2.38.2/lib/glib-2.0/include sqlite3.c
>> auxdata.c -o auxdata -L/usr/local/Cellar/glib/2.38.2/lib -lglib-2.0
>> sqlite-amalgamation-3080300 gwen$ ./auxdata
>> loop 1
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>> loop 2
>> (0) compiling...
>> z
>> (0) reusing...
>> y
>>
>> The auxiliary data is reused in the second loop with SQLite 3.7.17 but
>> not with SQLite 3.8.0.
>> What is the expected/correct behaviour?
>>
>> Regards
>>
>> Here is the content of auxdata.c:
>> #include 
>> #include 
>> #include 
>> #include "sqlite3.h"
>>
>> static void log(void *pArg, int iErrCode, const char *zMsg) {
>> printf("(%d) %s\n", iErrCode, zMsg);
>> }
>>
>> static void glibRegexpDelete(void *p){
>>   GRegex *pRegex = (GRegex *)p;
>>   g_regex_unref(pRegex);
>> }
>>
>> static void glibReplaceAllFunc(
>>   sqlite3_context *ctx,
>>   int argc,
>>   sqlite3_value **argv
>> ){
>> GError *err = NULL;
>> GRegex *p;
>> gchar *result = NULL;
>>
>> (void)argc;  /* Unused parameter */
>>
>> const gchar *str = (const gchar *) sqlite3_value_text(argv[1]);
>> if (!str) {
>> return;
>> }
>>
>> const gchar *replacement = (const gchar *) sqlite3_value_text(argv[2]);
>> if (!replacement) {
>> sqlite3_result_error(ctx, "no replacement string", -1);
>> return;
>> }
>>
>> p = sqlite3_get_auxdata(ctx, 0);
>> if( !p ){
>> const gchar *re = (const gchar *) sqlite3_value_text(argv[0]);
>> if( !re ){
>> //sqlite3_result_error(ctx, "no regexp", -1);
>> return;
>> }
>> p = g_regex_new(re, 0, 0, );
>>
>> if( p ){
>> sqlite3_set_auxdata(ctx, 0, p, glibRegexpDelete);
>> }else{
>> char *e2 = sqlite3_mprintf("%s: %s", re, err->message);
>> sqli

[sqlite] Unqualified table name and pragma

[gwenn]

Hello,
SQLite behaviour is consistent (temp database is searched first):
create table test (main text);
create temporary table test (temporary text);
insert into test values ('unqualified'); -- in temp table
select * from test; -- temp table
-- unqualified
pragma table_info("test"); -- temp table
-- 0|temporary|text|0||0

But pragma documentation page is misleading:
http://sqlite.org/pragma.html
"A pragma may have an optional database name before the pragma name.
The database name is the name of an ATTACH-ed database or it can be
"main" or "temp" for the main and the TEMP databases. If the optional
database name is omitted, "main" is assumed."

pragma table_info("test");
-- is same as
pragma temp.table_info("test");
-- and not:
pragma main.table_info("test");

Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Retrieve a int or a sqlite_int64

[gwenn]

Hello,
Is there any way to differentiate one value persisted with
sqlite3_bind_int from another persisted with sqlite3_bind_int64 ?
How to know which method between sqlite3_value_int and
sqlite3_value_int64 should be used to retrieve the value back ?
Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve a int or a sqlite_int64

[gwenn]

Ok,
Thanks.

On Tue, May 13, 2014 at 11:59 PM, Teg  wrote:
> Hello Charles,
>
> Tuesday, May 13, 2014, 3:12:09 PM, you wrote:
>
> CS> Load it with sqlite3_value_int64 every time. If the number fits in a 32 
> bit
> CS> integer, then you can store it in one.
>
> This is what I do. Everything is 64 bits to be future proof.
>
>
> CS> Charles
> CS> ___
> CS> sqlite-users mailing list
> CS> sqlite-users@sqlite.org
> CS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Best regards,
>  Tegmailto:t...@djii.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shared-Cache Mode

[gwenn]

Hello,
Is there any way to known if one connection participate to shared-cache mode ?
I've read http://sqlite.org/sharedcache.html which specifies how to
set but not how to get the mode!
Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Small bug with .import shell command

[gwenn]

Hello,
When the first value of the first line is empty, .import fails:
$ echo '|test' > ko.csv
$ echo '""|test' > ok.csv
$ sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
sqlite> .import ko.csv test
ko.csv: empty file
sqlite> .import ok.csv test
sqlite>

An error happens also when the table already exists:
sqlite> create table test (id text, data test);
sqlite> .import ko.csv test
ko.csv:1: expected 2 columns but found 1 - extras ignored
sqlite>

Maybe CSVReader.z should be pre-allocated ?

Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Small bug with .import shell command

[gwenn]

Hello,
May I suggest a patch ?

-- shell.c 2014-05-26 18:51:40.0 +0200
+++ shell.orig.c 2014-05-26 18:50:37.0 +0200
@@ -1917,8 +1917,6 @@
 if( c=='\n' ){
   p->nLine++;
   if( p->n>0 && p->z[p->n-1]=='\r' ) p->n--;
-}else if( !p->z && c==cSep ){
-  csv_append_char(p, 0);
 }
 p->cTerm = c;
   }

Regards.

On Sat, May 24, 2014 at 9:46 AM, gwenn <gwenn.k...@gmail.com> wrote:
> Hello,
> When the first value of the first line is empty, .import fails:
> $ echo '|test' > ko.csv
> $ echo '""|test' > ok.csv
> $ sqlite3
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> sqlite> .import ko.csv test
> ko.csv: empty file
> sqlite> .import ok.csv test
> sqlite>
>
> An error happens also when the table already exists:
> sqlite> create table test (id text, data test);
> sqlite> .import ko.csv test
> ko.csv:1: expected 2 columns but found 1 - extras ignored
> sqlite>
>
> Maybe CSVReader.z should be pre-allocated ?
>
> Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing CSV with a random empty line at the end

[gwenn]

Hello,
I doesn't fail for me (it may depend on the constraints on the target
table) but the behaviour is unexpected:

$ echo "1|test
> " > empty.csv
$ sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
sqlite> create table test(opt text, data text not null);
sqlite> .import empty.csv test
empty.csv:2: expected 2 columns but found 1 - filling the rest with NULL
sqlite> select * from test;
1|test
|test

As the bindings are not cleared, it is not a null value but the
previous bound value which is inserted.
Regards.

On Mon, Jun 2, 2014 at 5:41 PM, Gert Van Assche  wrote:
> All,
>
> I received 100.000 UTF-8 files (average size 50kb)  "ready for import" in
> an SQLite db.
> 90% of them go fine, but some files have an empty line at the very end of
> the fine (so an extra EOL before the EOF).
>
> Of course, the import fails... Is there an easy way to get rid of that
> extra empty line before I import the file, or is there a way to ignore an
> empty line?
>
> thanks
>
> Gert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Statement cache does not play well with sqlite3_stmt_readonly

[gwenn]

Hello,
The function "sqlite3_stmt_readonly" returns true/1 for the statement
"DROP TABLE IF EXISTS test" when the table "test" does not exist.
But, if this drop statement is cached, "sqlite3_stmt_readonly" still
returns true even after creating the table "test".
The only way I've found to make "sqlite3_stmt_readonly" returns false
is to execute/step the drop statement.
Do you know another way to make SQLite "reevaluate" the readonly status ?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Statement cache does not play well with sqlite3_stmt_readonly

[gwenn]

Ok,
Thanks.

On Tue, Jun 3, 2014 at 8:42 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Tue, Jun 3, 2014 at 2:27 PM, gwenn <gwenn.k...@gmail.com> wrote:
>
>> Hello,
>> The function "sqlite3_stmt_readonly" returns true/1 for the statement
>> "DROP TABLE IF EXISTS test" when the table "test" does not exist.
>> But, if this drop statement is cached, "sqlite3_stmt_readonly" still
>> returns true even after creating the table "test".
>> The only way I've found to make "sqlite3_stmt_readonly" returns false
>> is to execute/step the drop statement.
>> Do you know another way to make SQLite "reevaluate" the readonly status ?
>>
>
> Thank you for the bug report.
>
> Because this is a very minor issue and because we are well into the test
> cycle for version 3.8.5 already, we are going to defer looking into this
> problem until after the 3.8.5 release.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS module and DB close

[gwenn]

Hello,
How do you prevent double free/finalize of statements created by the
FTS module ?
I am using sqlite3_next_stmt to finalize all dangling statements
before closing the connection but the program crashes because the FTS
module finalizes them too when sqlite3_close is called...
May be I should use sqlite3_close_v2 (not available on MacOS X: SQLite
version 3.7.13) ?

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS module and DB close

[gwenn]

Ok,
Maybe the solution is:
1) try to close the connection: sqlite3_close
2) if error code is SQLITE_BUSY,
 a) use sqlite3_next_stmt to finalize dangling statements
 b) retry to close the connection
Step (1) ensures that FTS related statements are finalized.
Thanks.

On Sat, Jun 7, 2014 at 7:49 PM, gwenn <gwenn.k...@gmail.com> wrote:
> Hello,
> How do you prevent double free/finalize of statements created by the
> FTS module ?
> I am using sqlite3_next_stmt to finalize all dangling statements
> before closing the connection but the program crashes because the FTS
> module finalizes them too when sqlite3_close is called...
> May be I should use sqlite3_close_v2 (not available on MacOS X: SQLite
> version 3.7.13) ?
>
> Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing CSV with a random empty line at the end

[gwenn]

Hello,
I've taken the time to investigate the problem: only the last
parameter is not correctly bound to NULL.

May I suggest a patch:

--- shell_.c 2014-06-15 14:22:39.0 +0200
+++ shell.c 2014-06-15 14:23:11.0 +0200
@@ -2553,7 +2553,7 @@
   "filling the rest with NULL\n",
   sCsv.zFile, startLine, nCol, i+1);
   i++;
-  while( i wrote:
> gwenn, thanks for this. I did not understand what you saw, and then I
> realized my shell exe was probably too old.
> I downloaded the new exe and this solves the problem just fine!
> thanks for your help.
>
> gert
>
>
> 2014-06-02 19:03 GMT+02:00 gwenn <gwenn.k...@gmail.com>:
>
>> Hello,
>> I doesn't fail for me (it may depend on the constraints on the target
>> table) but the behaviour is unexpected:
>>
>> $ echo "1|test
>> > " > empty.csv
>> $ sqlite3
>> SQLite version 3.8.4.3 2014-04-03 16:53:12
>> sqlite> create table test(opt text, data text not null);
>> sqlite> .import empty.csv test
>> empty.csv:2: expected 2 columns but found 1 - filling the rest with NULL
>> sqlite> select * from test;
>> 1|test
>> |test
>>
>> As the bindings are not cleared, it is not a null value but the
>> previous bound value which is inserted.
>> Regards.
>>
>> On Mon, Jun 2, 2014 at 5:41 PM, Gert Van Assche <ger...@gmail.com> wrote:
>> > All,
>> >
>> > I received 100.000 UTF-8 files (average size 50kb)  "ready for import" in
>> > an SQLite db.
>> > 90% of them go fine, but some files have an empty line at the very end of
>> > the fine (so an extra EOL before the EOF).
>> >
>> > Of course, the import fails... Is there an easy way to get rid of that
>> > extra empty line before I import the file, or is there a way to ignore an
>> > empty line?
>> >
>> > thanks
>> >
>> > Gert
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JDBC and savepoints

[gwenn]

Hello,
Are you sure?
http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setSavepoint()
Regards.

On Sun, Jun 29, 2014 at 12:15 PM, hala  wrote:
> JDBC does not support savepoints from SQLite
>
> is there any replacement for savepoints?
>
> if not what to use for bulk inserts to ensure the possibility of rolling
> back without losing much data?
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/JDBC-and-savepoints-tp76304.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 64-bit length BLOB

[gwenn]

Hello,

Could you please confirm that BLOBs created by
sqlite3_bind_blob64()/sqlite3_bind_zeroblob64() can still be read with
sqlite3_blob_read()/sqlite3_blob_bytes() or sqlite3_column_blob
/sqlite3_column_bytes() because their size can never exceed the limit
specified here:
http://sqlite.org/limits.html#max_length
"The current implementation will only support a string or BLOB length
up to 231-1 or 2147483647."

Thanks.

[sqlite] Redundant link in documentation

[gwenn]

Maybe I should have sent a patch:

diff src/sqlite.h.in.bak src/sqlite.h.in
3629c3629
< ** [sqlite3_bind_parameter_index()].
---
> ** [sqlite3_bind_parameter_name()].

Regards.

On Tue, Aug 4, 2015 at 8:14 PM, gwenn  wrote:
> Hi,
> In the following page:
> http://sqlite.org/c3ref/bind_parameter_index.html
> there is a link to itself: sqlite3_bind_parameter_index().
>
> Maybe it should have been a link to:
> http://sqlite.org/c3ref/bind_parameter_name.html
> ?
> Regards.

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[gwenn]

Hello,
Is there any way to know when a prepared statement is recompiled ?
For example:

rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, , NULL);
...
cc = sqlite3_column_count(stmt);
...
rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
'missing'", NULL, NULL, NULL);
...
rc = sqlite3_step(stmt);
// how to know that the column count is not good anymore ?

Regards.

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[gwenn]

SQLITE_SCHEMA is returned only on failure.
But, here, there is not failure.

Maybe I should not store the column count ?
Maybe I should look at schema version
(http://sqlite.org/pragma.html#pragma_schema_version):
"The schema version is used by SQLite each time a query is executed to
ensure that the internal cache of the schema used when compiling the
SQL query matches the schema of the database against which the
compiled query is actually executed."
Is there a better solution ?

Thanks.

On Sat, Sep 19, 2015 at 3:21 PM, Kees Nuyt  wrote:
> On Sat, 19 Sep 2015 11:29:37 +0200, gwenn 
> wrote:
>
>>Hello,
>>Is there any way to know when a prepared statement is recompiled ?
>>For example:
>>
>>rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, , NULL);
>>...
>>cc = sqlite3_column_count(stmt);
>>...
>>rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
>>'missing'", NULL, NULL, NULL);
>>...
>>rc = sqlite3_step(stmt);
>>// how to know that the column count is not good anymore ?
>
> http://www.sqlite.org/rescode.html#schema
>
> --
> Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[gwenn]

Sorry for my bad description.
Imagine that you have a cache of statements (like in tclsqlite.c)
created using sqlite3_prepare_v2.
And you want to avoid calling sqlite3_column_count/sqlite3_column_name
each time you reuse (sqlite3_step) the same statement (with Java (JNI)
or Go (cgo), a native call is not cheap).
Everything works fine if the database schema is not touched.
But if a column is added to one table in your database, your statement
is successfully/transparently recompiled (when calling sqlite3_step)
and the column count may be changed.
My problem is that I don't know that the statement has been recompiled
and that I need to invalidate/update the column count.
If there is no way to know that the statement has been recompiled, I
guess that the column count should not be cached...
But maybe there is already such feature provided by the SQLite API ?

Regards.

On Sat, Sep 19, 2015 at 10:14 PM, R.Smith  wrote:
>
>
> On 2015-09-19 06:41 PM, gwenn wrote:
>>
>> SQLITE_SCHEMA is returned only on failure.
>> But, here, there is not failure.
>>
>> Maybe I should not store the column count ?
>> Maybe I should look at schema version
>> (http://sqlite.org/pragma.html#pragma_schema_version):
>> "The schema version is used by SQLite each time a query is executed to
>> ensure that the internal cache of the schema used when compiling the
>> SQL query matches the schema of the database against which the
>> compiled query is actually executed."
>> Is there a better solution ?
>
>
> I too may be reading this thread wrong, I am not sure we're on the same
> page, but I /THINK/ maybe you are worrying about an unnecessary thing.
> The only way the column count can change for a prepared statement object, is
> if you changed it.
> If you have a prepared statement like "SELECT * FROM t..." then the
> definition might change once the SCHEMA has changed - but that will only
> have effect outside of the implicit transaction you are in, as in you will
> only notice when you reset the prepared statement - and you should always
> check column counts then.
>
> The bit I am not sure about is whether, after a schema change and reset,
> whether that select statement will give an error or simply recompile with a
> new column count - but that can be checked rather easily and then respond
> accordingly.
>
> Hope this makes some sense,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

[gwenn]

Thank you all for your replies.
As suggested, I am going to call sqlite3_column_count each time (after
the first sqlite3_step).

On Mon, Sep 21, 2015 at 5:57 PM, Scott Robison  
wrote:
> On Mon, Sep 21, 2015 at 5:48 AM, Richard Hipp  wrote:
>
>> On 9/21/15, Dominique Devienne  wrote:
>> > On Sun, Sep 20, 2015 at 4:10 PM, Igor Tandetnik 
>> wrote:
>> >
>> >> On 9/20/2015 9:55 AM, gwenn wrote:
>> >>
>> >>> If there is no way to know that the statement has been recompiled, I
>> >>> guess that the column count should not be cached...
>> >>>
>> >>
>> >> You could use sqlite3_prepare (no _v2), then you'd get an error on
>> schema
>> >> change. You would then re-prepare the statement and update your caches.
>> >
>> >
>> > Could perhaps also use the change counter
>> > https://www.sqlite.org/fileformat2.html#chngctr, if you're not using WAL
>> > mode.
>> > You'd get false positives I guess, since both DML and DDL changes would
>> > increment it, and I'm not sure that's any different from checking the
>> > column_count anyway, but just in case it's useful. --DD
>>
>> PRAGMA schema_version
>> (https://www.sqlite.org/pragma.html#pragma_schema_version) does what
>> you want.
>>
>> But here the thing:  It is probably far more expensive to run PRAGMA
>> schema_version than it is to just rerun sqlite3_column_count().  I
>> think this whole conversation is an exercise in premature
>> optimization.  Has anybody actually *measured* a performance problem
>> with sqlite3_column_count()?
>>
>
> I have not, and I don't have the environment to check. The original premise
> as I understand it was that a function call was not expensive because the
> API function itself was expensive, but transitions through the language
> binding in use might be too expensive. I indicated the same thought last
> night, that this might be premature optimization, but even if it is not, I
> can't imagine any way to get this information (that the schema changed so
> discard cached column count) without making *some* api call, and all api
> calls will have the same problem: an expensive language binding transition.
>
> One person did suggest using sqlite3_prepare instead of v2, since it will
> return a schema change error code. My gut instinct is that it'll be easier
> / at least as performant to just continue to use v2 and not cache column
> count after finishing stepping / resetting a statement. But as indicated, I
> can't check that.
>
> The only reason I jumped into this however is that several responses
> weren't answering the asked question, assuring the OP that what was being
> asked couldn't happen. It can and does by design. In order to cache info
> beyond a statement reset, there needs to be a way to invalidate it.
>
> In any case, I think the knowledge on the list has exhausted its ability to
> answer. To summarize:
>
> 1. Checking the schema version will result in at least one language binding
> transition (two if the version changed), whereas checking the column count
> will always only be one transition. Might as well just check the column
> count.
>
> 2. Using prepare vs prepare_v2 would return a schema error, at which point
> the caller could finalize / re-prepare the statement and update his cached
> values. This still involves extra language binding transitions to handle
> the schema change that would have been automatically dealt with by
> prepare_v2. OP would have to test to see if it benefits his use case,
> though it seems unlikely to be any faster than using the easier v2
> interface and calling column count after each first step.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pre-compiled x64 dll

[gwenn]

Hello,
What is the difference between the dll included in:
http://sqlite.org/2015/sqlite-dll-win64-x64-3090200.zip
And the one included in:
http://sqlite.org/2016/sqlite-uap-3100200.vsix Redist/Retail/x64/sqlite3.dll
?
The first one works with libffi, the second doesn't.

Is there a sqlite-dll-win64-x64-3100200.zip somewhere ?
Thanks.

[sqlite] Delete an existing module

[gwenn]

Hello,
Is it possible to delete a module and the associated client data ?
I tried to pass a NULL pointer as the third parameter of
sqlite3_create_module_v2,
sqlite3_create_module_v2(
  db,
  zName,
  NULL,
  NULL,
  NULL
);
but I got a SQLITE_MISUSE error.
Thanks.

[sqlite] Illegal hexadecimal number literal

[gwenn]

Hello,

SQLite version 3.8.10.2 2015-05-20 18:17:19
sqlite> select 0x1g;
1
sqlite> select 1g;
Error: unrecognized token: "1g"

The illegal number 0x1g is not rejected.

Maybe, in tokenice.c,
This block should not return directly but check that z[i] is not an IdChar
  if( z[0]=='0' && (z[1]=='x' || z[1]=='X') && sqlite3Isxdigit(z[2]) ){
for(i=3; sqlite3Isxdigit(z[i]); i++){}
return i;
  }

Regards.

[sqlite] [csv extension] Error while reading long lines

[gwenn]

Hello,
There is a little bug/typo in the csv extension when lines exceed 100
characters:
*** glibc detected *** sqlite3: realloc(): invalid pointer:
0x00ad1a78 ***
=== Backtrace: =
/lib/libc.so.6[0x7f6dab009d16]
/lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
./libSqliteCsv.so[0x7f6da9ef9dbf]

A possible patch is:
--- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05 05:14:30.0
+0100
+++ csv.c 2010-04-18 18:48:04.0 +0200
@@ -160,7 +160,7 @@
 }
   }
   if( bShrink ){
-pCSV->zRow = realloc( pCSV->zRow, n+1 );
+pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
 pCSV->maxRow = n+1;
   }
   return bEol ? pCSV->zRow : 0;

Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [csv extension] Error while reading long lines

[gwenn]

Thanks for this great extension.
It works smoothly with 500Mo files.

And it's a workaround to some shortcomings of the '.import' command:
 - no need to create a table before,
 - no need to delete the header row before/after,
 - no error if the number of columns is not homogeneous,
 - ...
It's a nightmare to work with the CSV format but I have to.

I made a quick and dirty fix to the USE_HEADER_ROW mode to replace
whitespaces, slashes or hyphens by underscores.
But I look for a better solution. Is there any way to make sure a string is
a valid column name?

Regards

On Mon Apr 19 17:32:13 GMT 2010, Shane Harrelson 
wrote:
> Thanks for the report.  The extension is still very a much a
> work-in-progress and any feedback is greatly appreciated.
>
> -Shane
>
>
> On Sun, Apr 18, 2010 at 12:51 PM, gwenn  wrote:
> > Hello,
> > There is a little bug/typo in the csv extension when lines exceed 100
> > characters:
> > *** glibc detected *** sqlite3: realloc(): invalid pointer:
> > 0x00ad1a78 ***
> > === Backtrace: =
> > /lib/libc.so.6[0x7f6dab009d16]
> > /lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
> > ./libSqliteCsv.so[0x7f6da9ef9dbf]
> >
> > A possible patch is:
> > --- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05
05:14:30.0
> > +0100
> > +++ csv.c 2010-04-18 18:48:04.0 +0200
> > @@ -160,7 +160,7 @@
> > }
> >   }
> >   if( bShrink ){
> > -pCSV->zRow = realloc( pCSV->zRow, n+1 );
> > +pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
> > pCSV->maxRow = n+1;
> >   }
> >   return bEol ? pCSV->zRow : 0;
> >
> > Regards.
> > ___
> > sqlite-users mailing list
> > sqlite-users at sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [csv extension] Error while reading long lines

[gwenn]

http://www2.sqlite.org/src/dir?name=ext/csv

On Sat, Apr 24, 2010 at 9:43 AM, Jan <janus...@gmx.net> wrote:

> This sounds very useful. But where can I get this extension?
>
> Sorry, I could not find anything.
>
> Jan
>
> Am 21.04.2010 20:22, schrieb gwenn:
> > Thanks for this great extension.
> > It works smoothly with 500Mo files.
> >
> > And it's a workaround to some shortcomings of the '.import' command:
> >   - no need to create a table before,
> >   - no need to delete the header row before/after,
> >   - no error if the number of columns is not homogeneous,
> >   - ...
> > It's a nightmare to work with the CSV format but I have to.
> >
> > I made a quick and dirty fix to the USE_HEADER_ROW mode to replace
> > whitespaces, slashes or hyphens by underscores.
> > But I look for a better solution. Is there any way to make sure a string
> is
> > a valid column name?
> >
> > Regards
> >
> > On Mon Apr 19 17:32:13 GMT 2010, Shane Harrelson
> > wrote:
> >> Thanks for the report.  The extension is still very a much a
> >> work-in-progress and any feedback is greatly appreciated.
> >>
> >> -Shane
> >>
> >>
> >> On Sun, Apr 18, 2010 at 12:51 PM, gwenn
>  wrote:
> >>> Hello,
> >>> There is a little bug/typo in the csv extension when lines exceed 100
> >>> characters:
> >>> *** glibc detected *** sqlite3: realloc(): invalid pointer:
> >>> 0x00ad1a78 ***
> >>> === Backtrace: =
> >>> /lib/libc.so.6[0x7f6dab009d16]
> >>> /lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
> >>> ./libSqliteCsv.so[0x7f6da9ef9dbf]
> >>>
> >>> A possible patch is:
> >>> --- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05
> > 05:14:30.0
> >>> +0100
> >>> +++ csv.c 2010-04-18 18:48:04.0 +0200
> >>> @@ -160,7 +160,7 @@
> >>>  }
> >>>}
> >>>if( bShrink ){
> >>> -pCSV->zRow = realloc( pCSV->zRow, n+1 );
> >>> +pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
> >>>  pCSV->maxRow = n+1;
> >>>}
> >>>return bEol ? pCSV->zRow : 0;
> >>>
> >>> Regards.
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users at sqlite.org
> >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [csv extension] Error while reading long lines

[gwenn]

While looking in csv1.test, I found a solution to the case when header row
contains spaces: just wrap the column name with double quotes.

Index: ext/csv/csv.c
===
--- ext/csv/csv.c
+++ ext/csv/csv.c
@@ -158,11 +158,11 @@
   pCSV->zRow[n] = '\0';
   bEol = -1;
 }
   }
   if( bShrink ){
-pCSV->zRow = realloc( pCSV->zRow, n+1 );
+pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
 pCSV->maxRow = n+1;
   }
   return bEol ? pCSV->zRow : 0;
 }

@@ -608,11 +608,11 @@
 *pzErr = sqlite3_mprintf("%s", aErrMsg[4]);
 sqlite3_free(zSql);
 csvRelease( pCSV );
 return SQLITE_ERROR;
   }
-  zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail);
+  zSql = sqlite3_mprintf("%s\"%s\"%s", zTmp, zCol, zTail);
 }else{
   zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail);
 }
 sqlite3_free(zTmp);
   }

Index: ext/csv/csv1.test
===
--- ext/csv/csv1.test
+++ ext/csv/csv1.test
@@ -23,10 +23,11 @@
 #
 #   csv-1.*: Creating/destroying csv tables.
 #   csv-2.*: Linear scans of csv data.
 #   csv-3.*: Test renaming an csv table.
 #   csv-4.*: CREATE errors
+#   csv-5.*: Dirty header and long line.
 #

 ifcapable !csv {
   finish_test
   return
@@ -36,10 +37,12 @@
 set test1csv [file join [file dirname [info script]] test1.csv]
 # This file is delimited by '|' and has quoted fields.
 set test2csv [file join [file dirname [info script]] test2.csv]
 # This file is delimited by '|'.  It does NOT have quoted fields.
 set test3csv [file join [file dirname [info script]] test3.csv]
+# This file contains a dirty header and one long line.
+set test4csv [file join [file dirname [info script]] test4.csv]

 #
 # Test cases csv-1.* test CREATE and DROP table statements.
 #

@@ -249,5 +252,14 @@
   catchsql " CREATE VIRTUAL TABLE t1 USING csv('foo') "
 } {1 {Error opening CSV file: 'foo'}}
 do_test csv-4.1.3 {
   catchsql " CREATE VIRTUAL TABLE t1 USING csv(foo foo) "
 } {1 {Error opening CSV file: 'foo foo'}}
+
+#
+# Test cases csv-5.* test file with dirty header and long line.
+#
+
+do_test csv-5.1.1 {
+  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test4csv') "
+  execsql " CREATE VIRTUAL TABLE t2 USING csv('$test4csv', ',',
USE_HEADER_ROW) "
+} {}

ADDEDext/csv/test4.csv
col 1,col.2,col-3,col!4,c...@5,col;6,col%7,col*8,col=9,col'10
123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789

I tried to handle double-quoted fields with embedded line breaks without
success but I am not stuck yet.
Regards.

On Wed, Apr 21, 2010 at 8:22 PM, gwenn <gwenn.k...@gmail.com> wrote:

> Thanks for this great extension.
> It works smoothly with 500Mo files.
>
> And it's a workaround to some shortcomings of the '.import' command:
>  - no need to create a table before,
>  - no need to delete the header row before/after,
>  - no error if the number of columns is not homogeneous,
>  - ...
> It's a nightmare to work with the CSV format but I have to.
>
> I made a quick and dirty fix to the USE_HEADER_ROW mode to replace
> whitespaces, slashes or hyphens by underscores.
> But I look for a better solution. Is there any way to make sure a string is
> a valid column name?
>
> Regards
>
> On Mon Apr 19 17:32:13 GMT 2010, Shane Harrelson 
> wrote:
> > Thanks for the report.  The extension is still very a much a
> > work-in-progress and any feedback is greatly appreciated.
> >
> > -Shane
>
> >
> >
> > On Sun, Apr 18, 2010 at 12:51 PM, gwenn  wrote:
> > > Hello,
> > > There is a little bug/typo in the csv extension when lines exceed 100
> > > characters:
> > > *** glibc detected *** sqlite3: realloc(): invalid pointer:
> > > 0x00ad1a78 ***
> > > === Backtrace: =
> > > /lib/libc.so.6[0x7f6dab009d16]
> > > /lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
> > > ./libSqliteCsv.so[0x7f6da9ef9dbf]
> > >
> > > A possible patch is:
> > > --- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05
> 05:14:30.0
> > > +0100
> > > +++ csv.c 2010-04-18 18:48:04.0 +0200
> > > @@ -160,7 +160,7 @@
> > > }
> > >   }
> > >   if( bShrink ){
> > > -pCSV->zRow = realloc( pCSV->zRow, n+1 );
> > > +pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
> > > pCSV->maxRow = n+1;
> > >   }
> > >   return bEol ? pCSV->zRow : 0;
> > >
> > > Regards.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [csv extension] Error while reading long lines

[gwenn]

Ok,
I've just added support to embedded new lines and partial support to escaped
double-quotes.
By partial support, I mean they are not unescaped yet...

Index: ext/csv/csv.c
===
--- ext/csv/csv.c
+++ ext/csv/csv.c
@@ -120,10 +120,11 @@
 */
 static char *csv_getline( CSV *pCSV ){
   int n = 0;
   int bEol = 0;
   int bShrink = 0;
+  int bQuotedCol = 0;

   /* allocate initial row buffer */
   if( pCSV->maxRow < 1 ){
 pCSV->zRow = sqlite3_malloc( 100 );
 if( pCSV->zRow ){
@@ -150,19 +151,32 @@
   pCSV->zRow[n] = '\0';
   bEol = -1;
   break;
 }
 /* look for line delimiter */
-while( pCSV->zRow[n] ){ n++; }
-if( (n>0) && ((pCSV->zRow[n-1]=='\n') || (pCSV->zRow[n-1]=='\r')) ){
+while( pCSV->zRow[n] ){
+  if( pCSV->zRow[n]=='\"' ){
+if( bQuotedCol ) {
+  if( pCSV->zRow[n+1]=='\"' ) { /* escaped */
+n++;
+  }else{
+bQuotedCol = 0;
+  }
+}else if( n==0 || pCSV->zRow[n-1]==pCSV->cDelim ){
+  bQuotedCol = 1;
+}
+  }
+  n++;
+}
+if( (n>0) && ((pCSV->zRow[n-1]=='\n') || (pCSV->zRow[n-1]=='\r')) &&
!bQuotedCol ){
   pCSV->zRow[n-1] = '\n'; /* uniform line ending */
   pCSV->zRow[n] = '\0';
   bEol = -1;
 }
   }
   if( bShrink ){
-pCSV->zRow = realloc( pCSV->zRow, n+1 );
+pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
 pCSV->maxRow = n+1;
   }
   return bEol ? pCSV->zRow : 0;
 }

@@ -332,17 +346,23 @@
   do{
 /* if it begins with a quote, assume it's a quoted col */
 if( *s=='\"' ){
   s++;  /* skip quote */
   pCSV->aCols[nCol] = s; /* save pointer for this col */
-  /* TBD: handle escaped quotes "" */
   /* find closing quote */
-  s = strchr(s, '\"');
-  if( !s ){
-/* no closing quote */
-pCSV->eof = -1;
-return SQLITE_ERROR;
+  while( 1 ){
+s = strchr(s, '\"');
+if( !s ){
+  /* no closing quote */
+  pCSV->eof = -1;
+  return SQLITE_ERROR;
+}else if ( *(s+1)=='\"' ){
+  /* TBD: replace all escaped quotes by a single one */
+  s+=2;
+}else{
+  break;
+}
   }
   *s = '\0'; /* null terminate this col */
   /* fall through and look for following ",\n" */
   s++;
 }else{
@@ -608,11 +628,11 @@
 *pzErr = sqlite3_mprintf("%s", aErrMsg[4]);
 sqlite3_free(zSql);
 csvRelease( pCSV );
 return SQLITE_ERROR;
   }
-  zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail);
+  zSql = sqlite3_mprintf("%s\"%s\"%s", zTmp, zCol, zTail);
 }else{
   zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail);
 }
 sqlite3_free(zTmp);
   }


On Sat, May 8, 2010 at 3:45 PM, gwenn <gwenn.k...@gmail.com> wrote:

> While looking in csv1.test, I found a solution to the case when header row
> contains spaces: just wrap the column name with double quotes.
>
> Index: ext/csv/csv.c
> ===
> --- ext/csv/csv.c
> +++ ext/csv/csv.c
> @@ -158,11 +158,11 @@
>pCSV->zRow[n] = '\0';
>bEol = -1;
>  }
>}
>if( bShrink ){
> -pCSV->zRow = realloc( pCSV->zRow, n+1 );
> +pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
>  pCSV->maxRow = n+1;
>}
>return bEol ? pCSV->zRow : 0;
>  }
>
> @@ -608,11 +608,11 @@
>  *pzErr = sqlite3_mprintf("%s", aErrMsg[4]);
>  sqlite3_free(zSql);
>  csvRelease( pCSV );
>  return SQLITE_ERROR;
>}
> -  zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail);
> +  zSql = sqlite3_mprintf("%s\"%s\"%s", zTmp, zCol, zTail);
>  }else{
>zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail);
>  }
>  sqlite3_free(zTmp);
>}
>
> Index: ext/csv/csv1.test
> ===
> --- ext/csv/csv1.test
> +++ ext/csv/csv1.test
> @@ -23,10 +23,11 @@
>  #
>  #   csv-1.*: Creating/destroying csv tables.
>  #   csv-2.*: Linear scans of csv data.
>  #   csv-3.*: Test renaming an csv table.
>  #   csv-4.*: CREATE errors
> +#   csv-5.*: Dirty header and long line.
>  #
>
>  ifcapable !csv {
>finish_test
>return
> @@ -36,10 +37,12 @@
>  set test1csv [file join [file dirname [info script]] test1.csv]
>  # This file is delimited by '|' and has quoted fields.
>  set test2csv [file join [file dirname [info script]] test2.csv]
>  # This file is d

Re: [sqlite] [csv extension] Error while reading long lines

[gwenn]

t;aEscapedQuotes = p1;
 }

   }while( *s );

   pCSV->nCol = nCol;
@@ -402,13 +439,38 @@
   CSV *pCSV = (CSV *)pVtabCursor->pVtab;

   if( i<0 || i>=pCSV->nCol ){
 sqlite3_result_null( ctx );
   }else{
-char *col = pCSV->aCols[i];
+const char *col = pCSV->aCols[i];
 if( !col ){
   sqlite3_result_null( ctx );
+}else if( pCSV->aEscapedQuotes[i] ){
+  char *z;
+
+  int nByte = (int)(strlen(col) - pCSV->aEscapedQuotes[i]);
+  if( nByte>pCSV->db->aLimit[SQLITE_LIMIT_LENGTH] ){
+sqlite3_result_error_toobig( ctx );
+z = 0;
+  }else{
+z = sqlite3_malloc( nByte );
+if( !z ){
+  sqlite3_result_error_nomem( ctx );
+}
+  }
+  if( z ){
+int j,k;
+for(j=0, k=0; col[j]; j++){
+  z[k++] = col[j];
+  if( col[j]=='\"' ){
+/* unescape quote */
+j++;
+  }
+}
+z[k] = 0;
+sqlite3_result_text( ctx, z, k, sqlite3_free );
+  }
 }else{
   sqlite3_result_text( ctx, col, -1, SQLITE_TRANSIENT );
 }
   }

@@ -473,10 +535,11 @@
 /* finalize any prepared statements here */

 csv_close( pCSV );
 if( pCSV->zRow ) sqlite3_free( pCSV->zRow );
 if( pCSV->aCols ) sqlite3_free( pCSV->aCols );
+if( pCSV->aEscapedQuotes ) sqlite3_free( pCSV->aEscapedQuotes );
 sqlite3_free( pCSV );
   }
   return 0;
 }

@@ -539,10 +602,11 @@
 return SQLITE_NOMEM;
   }

   /* intialize virtual table object */
   memset(pCSV, 0, sizeof(CSV)+nDb+nName+nFile+3);
+  pCSV->db = db;
   pCSV->nBusy = 1;
   pCSV->base.pModule = 
   pCSV->cDelim = cDelim;
   pCSV->zDb = (char *)[1];
   pCSV->zName = >zDb[nDb+1];
@@ -608,11 +672,11 @@
 *pzErr = sqlite3_mprintf("%s", aErrMsg[4]);
 sqlite3_free(zSql);
 csvRelease( pCSV );
 return SQLITE_ERROR;
   }
-  zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail);
+  zSql = sqlite3_mprintf("%s\"%s\"%s", zTmp, zCol, zTail);
 }else{
   zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail);
 }
 sqlite3_free(zTmp);
   }

I'll add some testcases...

On Sat, May 8, 2010 at 9:44 PM, gwenn <gwenn.k...@gmail.com> wrote:

> Ok,
> I've just added support to embedded new lines and partial support to
> escaped double-quotes.
> By partial support, I mean they are not unescaped yet...
>
> Index: ext/csv/csv.c
> ===
> --- ext/csv/csv.c
> +++ ext/csv/csv.c
> @@ -120,10 +120,11 @@
>  */
>  static char *csv_getline( CSV *pCSV ){
>int n = 0;
>int bEol = 0;
>int bShrink = 0;
> +  int bQuotedCol = 0;
>
>/* allocate initial row buffer */
>if( pCSV->maxRow < 1 ){
>  pCSV->zRow = sqlite3_malloc( 100 );
>  if( pCSV->zRow ){
> @@ -150,19 +151,32 @@
>pCSV->zRow[n] = '\0';
>bEol = -1;
>break;
>  }
>  /* look for line delimiter */
> -while( pCSV->zRow[n] ){ n++; }
> -if( (n>0) && ((pCSV->zRow[n-1]=='\n') || (pCSV->zRow[n-1]=='\r')) ){
> +while( pCSV->zRow[n] ){
> +  if( pCSV->zRow[n]=='\"' ){
> +if( bQuotedCol ) {
> +  if( pCSV->zRow[n+1]=='\"' ) { /* escaped */
> +n++;
> +  }else{
> +bQuotedCol = 0;
> +  }
> +}else if( n==0 || pCSV->zRow[n-1]==pCSV->cDelim ){
> +  bQuotedCol = 1;
> +}
> +  }
> +  n++;
> +}
> +if( (n>0) && ((pCSV->zRow[n-1]=='\n') || (pCSV->zRow[n-1]=='\r')) &&
> !bQuotedCol ){
>pCSV->zRow[n-1] = '\n'; /* uniform line ending */
>pCSV->zRow[n] = '\0';
>bEol = -1;
>  }
>}
>if( bShrink ){
> -pCSV->zRow = realloc( pCSV->zRow, n+1 );
> +pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
>  pCSV->maxRow = n+1;
>}
>return bEol ? pCSV->zRow : 0;
>  }
>
> @@ -332,17 +346,23 @@
>do{
>  /* if it begins with a quote, assume it's a quoted col */
>  if( *s=='\"' ){
>s++;  /* skip quote */
>pCSV->aCols[nCol] = s; /* save pointer for this col */
> -  /* TBD: handle escaped quotes "" */
>/* find closing quote */
> -  s = strchr(s, '\"');
> -  if( !s ){
> -/* no closing quote */
> -pCSV->eof = -1;
> -return SQLITE_ERROR;
> +  while( 1 ){
> +s = strchr(s, '\"');
> +if( !s ){
> +  /* no closing quote */
> +  pCSV->eof = -1;
> +  return SQLITE_ERROR;
> +}else if ( *(s+1)==

Re: [sqlite] [csv extension] Error while reading long lines

[gwenn]

Here is some tests:
--- csv1.test
+++ csv1.test
@@ -23,10 +23,11 @@
 #
 #   csv-1.*: Creating/destroying csv tables.
 #   csv-2.*: Linear scans of csv data.
 #   csv-3.*: Test renaming an csv table.
 #   csv-4.*: CREATE errors
+#   csv-5.*: Dirty header, long line, escaped quotes, escaped newlines.
 #

 ifcapable !csv {
   finish_test
   return
@@ -36,10 +37,13 @@
 set test1csv [file join [file dirname [info script]] test1.csv]
 # This file is delimited by '|' and has quoted fields.
 set test2csv [file join [file dirname [info script]] test2.csv]
 # This file is delimited by '|'.  It does NOT have quoted fields.
 set test3csv [file join [file dirname [info script]] test3.csv]
+# This file contains a dirty header, one long line, escaped quotes, escaped
+# new lines.
+set test4csv [file join [file dirname [info script]] test4.csv]

 #
 # Test cases csv-1.* test CREATE and DROP table statements.
 #

@@ -249,5 +253,40 @@
   catchsql " CREATE VIRTUAL TABLE t1 USING csv('foo') "
 } {1 {Error opening CSV file: 'foo'}}
 do_test csv-4.1.3 {
   catchsql " CREATE VIRTUAL TABLE t1 USING csv(foo foo) "
 } {1 {Error opening CSV file: 'foo foo'}}
+
+#
+# Test cases csv-5.* test file with dirty header and long line.
+#
+
+do_test csv-5.1.1 {
+  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test4csv') "
+  execsql " CREATE VIRTUAL TABLE t2 USING csv('$test4csv', ',',
USE_HEADER_ROW) "
+} {}
+do_test csv-5.1.2 {
+  execsql {
+SELECT col1 FROM t1 limit 1 offset 1;
+  }
+} {123456789}
+do_test csv-5.1.3 {
+  execsql {
+SELECT * FROM t1 limit 1 offset 3;
+  }
+} {{123456789
+} {
+} {} {123456789
+} {1234\\567'89
+} {123456"789
+} {123""456789
+} 1234\"5678\"9 123456789\" {}}
+do_test csv-5.1.4 {
+  execsql {
+SELECT col1,col2,col3 FROM t1 limit 1 offset 4;
+  }
+} {{} ' {}}
+do_test csv-5.1.5 {
+  execsql {
+SELECT col1 FROM t1 limit 1 offset 5;
+  }
+} {'}

And the test file (test4.csv):
col 1,col.2,col-3,col!4,c...@5,col;6,col%7,col*8,col=9,col'10
123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
"123456789
","
","","123456789
","1234\\567'89
","123456""789
","123""""456789
",1234"5678"9,123456789",""
"",',
'

On Thu, May 13, 2010 at 9:28 PM, gwenn <gwenn.k...@gmail.com> wrote:

> Done!
>
> Index: ext/csv/csv.c
> ===
> --- ext/csv/csv.c
> +++ ext/csv/csv.c
> @@ -60,10 +60,11 @@
>char *zRow;  /* Buffer for current CSV row */
>char cDelim; /* Character to use for delimiting columns
> */
>int nCol;/* Number of columns in current row */
>int maxCol;  /* Size of aCols array */
>char **aCols;/* Array of parsed columns */
> +  int *aEscapedQuotes; /* Number of escaped quotes for each column
> in aCols */
>  };
>
>
>  /*
>  ** An CSV cursor object.
> @@ -120,10 +121,11 @@
>  */
>  static char *csv_getline( CSV *pCSV ){
>int n = 0;
>int bEol = 0;
>int bShrink = 0;
> +  int bQuotedCol = 0;
>
>/* allocate initial row buffer */
>if( pCSV->maxRow < 1 ){
>  pCSV->zRow = sqlite3_malloc( 100 );
>  if( pCSV->zRow ){
> @@ -135,10 +137,13 @@
>/* read until eol */
>while( !bEol ){
>  /* grow row buffer as needed */
>  if( n+100>pCSV->maxRow ){
>int newSize = pCSV->maxRow*2 + 100;
> +  if( newSize>=pCSV->db->aLimit[SQLITE_LIMIT_LENGTH] ){
> +return 0;
> +  }
>char *p = sqlite3_realloc(pCSV->zRow, newSize);
>if( !p ) return 0;
>pCSV->maxRow = newSize;
>pCSV->zRow = p;
>bShrink = -1;
> @@ -150,19 +155,32 @@
>pCSV->zRow[n] = '\0';
>bEol = -1;
>break;
>  }
>  /* look for line delimiter */
> -while( pCSV->zRow[n] ){ n++; }
> -if( (n>0) && ((pCSV->zRow[n-1]=='\n') || (pCSV->zRow[n-1]=='\r')) ){
> +while( pCSV->zRow[n] ){
> +  if( pCSV->zRow[n]=='\"' ){
> +if( bQuotedCol ) {
> +  if( pCSV->zRow[n+1]=='\"' ) { /* escaped */
> +n++;
> +  }else{
> +bQuotedCol = 0;
> +  }
> +}else if

[sqlite] VTab & xRename

[gwenn]

Hello,

The documentation says the xRename function is mandatory:
http://sqlite.org/vtab.html#xrename
"The xRename method is required for every virtual table implementation."

But it seems possible to not specify it:
  static const sqlite3_module fts3aux_module = {
...
 0,   /* xRename   */
...
  };

Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VTab & xRename

[gwenn]

Thanks for your suggestion Jay.

static sqlite3_module csvModule = {
  0,/* iVersion */
  csvCreate,/* xCreate - create a table */
  csvConnect,   /* xConnect - connect to an existing table */
  csvBestIndex, /* xBestIndex - Determine search strategy */
  csvDisconnect,/* xDisconnect - Disconnect from a table */
  csvDestroy,   /* xDestroy - Drop a table */
  csvOpen,  /* xOpen - open a cursor */
  csvClose, /* xClose - close a cursor */
  csvFilter,/* xFilter - configure scan constraints */
  csvNext,  /* xNext - advance a cursor */
  csvEof,   /* xEof */
  csvColumn,/* xColumn - read data */
  csvRowid, /* xRowid - read data */
  0,/* xUpdate - write data */
  0,/* xBegin - begin transaction */
  0,/* xSync - sync transaction */
  0,/* xCommit - commit transaction */
  0,/* xRollback - rollback transaction */
  0,/* xFindFunction - function overloading */
  0 /* xRename - rename the table */
};

sqlite> .load ./csv.sqlext
sqlite> create virtual table test using csv(test1.csv, ',', USE_HEADER_ROW);
sqlite> select * from test;
1|2|3
a|b|c
a|b|c
a|b|c .. z
a|b|c,d
sqlite> alter table test rename to test1;
sqlite> select * from test;
Error: no such table: test
sqlite> select * from test1;
1|2|3
a|b|c
a|b|c
a|b|c .. z
a|b|c,d
sqlite>

So it's seems that SQLite properly handles virtual table rename even
when xRename is not specified by the module.
Regards.

On Tue, Oct 23, 2012 at 10:50 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Tue, Oct 23, 2012 at 10:16:07PM +0200, gwenn scratched on the wall:
>> Hello,
>>
>> The documentation says the xRename function is mandatory:
>> http://sqlite.org/vtab.html#xrename
>> "The xRename method is required for every virtual table implementation."
>>
>> But it seems possible to not specify it:
>>   static const sqlite3_module fts3aux_module = {
>> ...
>>  0,   /* xRename   */
>> ...
>>   };
>
>
>   And when you attempt to rename the table, what happens?
>
>
>   The virtual table interface is advanced, in the sense that there are
>   very few safety nets or double-checks.  It is designed to be used by
>   an intelligent programmer that knows their stuff.  You need to do what
>   the docs say, exactly, or something bad can happen.  That's not to
>   say something bad will happen right away.  The fact that you can assign
>   a NULL function pointer to the xRename() function only means the system
>   is not double-checking your work when you pass in the structure... it
>   does not mean that passing a NULL is allowed.  I strongly suspect that
>   if you do not provide a xRename() function, and someone attempts to
>   rename the table, the whole application will simply crash.  Your fault.
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[gwenn]

If you want, you can verify automatically that all the FK columns have a
type matching the referenced columns by using (and tweaking) an old tool
whose name is 'genfkey' (see http://www.sqlite.org/faq.html#q22 but the
'readme' link is broken).
Regards.



On Thu, Nov 8, 2012 at 6:29 PM, Simon Slavin  wrote:

>
> On 8 Nov 2012, at 5:27pm, stahl...@dbs.uni-hannover.de wrote:
>
> > But inferring the FK's type from the referenced PK would cause
> applications
> > which rely on the FK's type affinity being 'none' to be broken, no?
>
> At this sort of level of bug-compatibility, you have to say "Will not be
> fixed until SQLite4."
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to verify referential integrity of SQLite database

[gwenn]

Hello,
You can give the following tool a try if you want:
https://github.com/gwenn/checkfkey
But I'm not sure that it correctly handles composite.
Regards.

On Thu, Dec 13, 2012 at 4:22 PM, Jean-Christophe Deschamps
<j...@antichoc.net> wrote:
>
>> Jay A. Kreibich wrote:
>> >   I can also see situations when someone might want to run one
>> >   set or the other set of checks.  Breaking it out, so that these
>> >   checks are done by a different PRAGMA (integrity_check_v2 ?) seems
>> >   like a wise idea.
>>
>> Indeed; with a separate PRAGMA fk_integrity_check, it would be possible
>> to run the check even when foreign keys are not currently enabled.
>> This would be a useful thing to do just before enabling foreign keys.
>
>
> Isn't something else than a pragma more appropiate?
>
> SELECT consistency_check() FROM mytable;
>
> would return rows from a specific table where any constraint, unicity or FK
> is violated:
> rowid | constraint_name | diag_code
>
> SELECT consistency_check_all();
>
> would return rows from every table in turn where any constraint, unicity or
> FK is violated:
> table_name | rowid | constraint_name | diag_code
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Timezone is supported by date/time functions but is not documented

[gwenn]

Hello,
SQLite datetime function correctly parses timestring with timezone:

sqlite> select datetime('2013-04-30T18:38:54Z');
2013-04-30 18:38:54
sqlite> select datetime('2013-04-30T20:38:54+02:00');
2013-04-30 18:38:54

But this is not documented:
http://sqlite.org/lang_datefunc.html
http://sqlite.org/datatype3.html#datetime

May I suggest updating these pages accordingly.

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

[gwenn]

Hello,
I've tested the improved ".import" command and it seems that there is
a bug with empty not-quoted field:
$ cat empty.csv
A|B
|
$ ./a.out
SQLite version 3.8.0 2013-06-28 23:55:45
sqlite> .import empty.csv test
empty.csv:2: expected 2 columns but found 1 - filling the rest with NULL
sqlite>

I am not sure, but it seems to be here:
   }else{
csv_append_char(p, c); // FIXME
while( (c = fgetc(p->in))!=EOF && c!=cSep && c!='\n' ){

Regards.

On Thu, Jun 27, 2013 at 1:01 AM, Richard Hipp  wrote:
> On Wed, Jun 26, 2013 at 6:23 PM, RSmith  wrote:
>
>>  I have done ludicrous amounts of testing and evaluating imports for and
>> from CSVs
>>
>
> I made a go at improving the CSV importer for the upcoming SQLite 3.8.0
> release.  Please see the latest trunk check-in.  Your expert feedback would
> certainly be welcomed here.
>
> Note that in the new ".import" command, the table named in the second
> argument need not exist now, and the shell will create it for you
> automatically, giving it column names as determined by the first row of the
> CSV file.  That seemed like it might be a handy feature.
>
> The other changes to the new ".import" are that it issues error messages
> (but tries to continue muddling through) if the input does not conform to
> rfc4180, and it correctly handles quoted data that extends across multiple
> lines or that contains embedded commas.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any tool to create erd from sqlite database?

[gwenn]

Hello,
There is a minimalist one here:
https://github.com/gwenn/sqliterd
It depends on c/go compilers and the graphviz dot command...
Regards.

On Fri, Sep 20, 2013 at 2:26 PM, Jason H <scorp...@yahoo.com> wrote:
> Don't forget about ODBC tools... Just use ta SQLite ODBC driver...
>
>
> 
>  From: dd <durga.d...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Friday, September 20, 2013 8:11 AM
> Subject: [sqlite] Any tool to create erd from sqlite database?
>
>
> I am looking for tool which generates er diagrams from existing database.
>
> Any suggetions?
>
> Thanks in advance.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_set_auxdata & invalid pointer

[gwenn]

Hello,
I am trying to add custom aggregation function support in a golang driver
(scalar functions are ok).
While testing, I got this:
*** glibc detected *** ./6.out: realloc(): invalid pointer:
0x02daa1c5 ***
=== Backtrace: =
/lib/x86_64-linux-gnu/libc.so.6(+0x72656)[0x2b9a7b5da656]
/lib/x86_64-linux-gnu/libc.so.6(realloc+0x312)[0x2b9a7b5e0762]
/home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(+0x30387)[0x2b9a7b2ec387]
/home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(+0x16a5b)[0x2b9a7b2d2a5b]
/home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(+0x234da)[0x2b9a7b2df4da]
/home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(sqlite3_set_auxdata+0xb6)[0x2b9a7b2e2d86]

I just have enough skills to debug with gdb and to find this line:
62056: pVdbeFunc = sqlite3DbRealloc(pCtx->s.db, pVdbeFunc, nMalloc);

Could you please help me find what I am doing wrong?
I just call sqlite3_set_auxdata in my xStep function.
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_set_auxdata & invalid pointer

[gwenn]

I guess that "sqlite3_set_auxdata" cannot be called by the xStep
implementation of an aggregate function.
The doc says:
"The following two functions may be used by scalar SQL functions to
associate metadata with argument values."
I will try with "sqlite3_aggregate_context".
Sorry for the disturbance.

On Sat, Jan 14, 2012 at 10:36 PM, gwenn <gwenn.k...@gmail.com> wrote:
>
> Hello,
> I am trying to add custom aggregation function support in a golang driver 
> (scalar functions are ok).
> While testing, I got this:
> *** glibc detected *** ./6.out: realloc(): invalid pointer: 
> 0x02daa1c5 ***
> === Backtrace: =
> /lib/x86_64-linux-gnu/libc.so.6(+0x72656)[0x2b9a7b5da656]
> /lib/x86_64-linux-gnu/libc.so.6(realloc+0x312)[0x2b9a7b5e0762]
> /home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(+0x30387)[0x2b9a7b2ec387]
> /home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(+0x16a5b)[0x2b9a7b2d2a5b]
> /home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(+0x234da)[0x2b9a7b2df4da]
> /home/gwen/Test/sqlite-autoconf-3070900/.libs/libsqlite3.so.0(sqlite3_set_auxdata+0xb6)[0x2b9a7b2e2d86]
>
> I just have enough skills to debug with gdb and to find this line:
> 62056: pVdbeFunc = sqlite3DbRealloc(pCtx->s.db, pVdbeFunc, nMalloc);
>
> Could you please help me find what I am doing wrong?
> I just call sqlite3_set_auxdata in my xStep function.
> Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tiny correction

[gwenn]

Hello,
It seems that the icuFunctionError can be simplified:

  char zBuf[128];
  sqlite3_snprintf(128, zBuf, "ICU error: %s(): %s", zName, u_errorName(e));
  zBuf[127] = '\0'; // <- useless

In the documentation:
"As long as the buffer size is greater than zero, sqlite3_snprintf()
guarantees that the buffer is always zero-terminated."

Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typo in source code comment

[gwenn]

/*** EXPERIMENTAL ***
**
** Register a function to be invoked when a transaction comments.
** If the invoked function returns non-zero, then the commit becomes a
** rollback.
*/
SQLITE_API void *sqlite3_commit_hook(

:s/comments/commits/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite & JDBC & generated key

[gwenn]

* In JDBC API, there is a method to retreive the generated key during an insert:
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
* With SQLite API, there is: sqlite3_last_insert_rowid.
Let suppose that:
 - the primary key is correctly declared to make it an alias for the rowid,
 - and the connection is not shared.
1) Do you know how to retreive the column name of the primary key (the
table name is not known) ?
2) Do you know if there are other bindings that implement/support this
kind of feature ?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite & JDBC & generated key

[gwenn]

Thanks for your replies.
I will add a tweak to ignore column access by name when running
"SELECT last_insert_rowid();"

On Thu, Apr 5, 2012 at 2:17 AM, Kees Nuyt <k.n...@zonnet.nl> wrote:
> On Wed, 4 Apr 2012 21:08:24 +0200, gwenn <gwenn.k...@gmail.com> wrote:
>
>>  2) Do you know if there are other bindings that implement/support
>>     this kind of feature ?
>
> I almost forgot to mention:
>
>  SELECT last_insert_rowid();
>
> http://www.sqlite.org/lang_corefunc.html
>
> --
> Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Jdbc & Blob & Incremental I/O

[gwenn]

Hi,
I've been trying to support incremental I/O in a Jdbc driver.
By forcing the user to access the rowid before the blob, it's possible
to use only the JDBC API for loading a Blob:
// CREATE TABLE test (data BLOB); INSERT INTO test (data) VALUES
(zeroblob(1024));
ResultSet rs = stmt.executeQuery("SELECT rowid, data FROM test");
rs.getRowId(1);
final Blob blob = rs.getBlob(2);
...
Indeed, with the rowId kept internally by the driver and with the
column index (2), I can retrieve all the data needed by
'sqlite3_blob_open': dbName, tblName, colName.
I will also support this alternative:
PrepareStatement pstmt = c.prepareStatement("SELECT data FROM test
where rowid = :rowid");
pstmt.setRowId(1, ...); -- rowId value kept internally
ResultSet rs = pstmt.executeQuery();
final Blob blob = rs.getBlob(1);

But for update/insert, it doesn't work because the
sqlite3_column_name, sqlite3_column_origin_name,
sqlite3_column_table_name and sqlite3_column_database_name can only be
used with select:
PreparedStatement pstmt = c.prepareStatement("UPDATE test SET data
= :blob WHERE rowid = :rowid");
pstmt.setRowId(2, new RowIdImpl(rowid));
pstmt.setBinaryStream(1, new ByteArrayInputStream(new byte[] {1,
2, 3, 4, 5, 6})); -- fails
pstmt.executeUpdate();

Do you see a way to write a blob incrementally by using only the JDBC API?
(I've checked all the other implementations, but they don't support
reading, nor writing...)

Thanks.

(the driver is here:
https://github.com/gwenn/sqlite-jna/tree/master/src/main/java/org/sqlite/driver)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The upcoming "pi" release of SQLite

[gwenn]

Hello,
It seems that the problem reported here:
http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg97627.html
is not fixed.

SQLite version 3.14.0 2016-07-23 05:22:02
sqlite> select 0x1g;
1
sqlite>

Regards.

On Sat, Jul 23, 2016 at 5:16 PM, Richard Hipp  wrote:
> The next release of SQLite will be the "pi" release - version 3.14.
> It will probably occur within the next two weeks.
>
> Draft change log:  https://www.sqlite.org/draft/releaselog/3_14_0.html
>
> Code snapshot: 
> https://www.sqlite.org/snapshot/sqlite-snapshot-201607230522.tar.gz
>
> Testing and (especially) documentation work is on-going.  Please try
> out the snapshot.  Look over the changes.  Speak up loudly and quickly
> if you have any issues.  If you do not want to post to this mailing
> list, you can send feedback directly to my email address shown below.
>
> Thanks.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Different error messages for the same syntax error

[gwenn]

Hello,

SQLite version 3.13.0 2016-05-18 10:57:30
sqlite> create table test (name text default '');
sqlite> insert into test values ();
Error: near ")": syntax error
sqlite> insert into test values (''), ();
Error: no tables specified

Maybe the parser rule is too permissive:
values(A) ::= values(X) COMMA LP exprlist(Y) RP.
versus
values(A) ::= values(X) COMMA LP nexprlist(Y) RP.

Thanks and regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL parsing logic for triggers

[gwenn]

Hello,
I am just looking for information (I am not asking for any change):
It is for (auto) completion hints.

1) it seems not possible to insert default values in trigger command:

// INSERT
trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).

versus

// The INSERT command /
//
cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.

Is it a choice or an oversight ?

2) It seems possible to use different database names when creating a trigger:

trigger_decl(A) ::= temp(T) TRIGGER ifnotexists(NOERR) nm(B) dbnm(Z)
trigger_time(C) trigger_event(D)
ON fullname(E) foreach_clause when_clause(G). {

Is it to make possible to create temporary trigger by using the
following syntax:
CREATE TABLE test (data);
CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
instead of:
CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;

Or there are other use cases supported ?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL parsing logic for triggers

[gwenn]

On Sun, Oct 9, 2016 at 12:14 PM, Richard Hipp <d...@sqlite.org> wrote:
> On 10/9/16, gwenn <gwenn.k...@gmail.com> wrote:
>> Hello,
>> I am just looking for information (I am not asking for any change):
>> It is for (auto) completion hints.
>>
>> 1) it seems not possible to insert default values in trigger command:
>>
>> // INSERT
>> trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).
>>
>> versus
>>
>> // The INSERT command
>> /
>> //
>> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
>> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
>>
>> Is it a choice or an oversight ?
>
> Deliberate.  Adding DEFAULT VALUES to triggers is just more code to be
> tested and maintained for something that adds no new capability and
> that nobody ever uses.
>
>>
>> Is it to make possible to create temporary trigger by using the
>> following syntax:
>> CREATE TABLE test (data);
>> CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
>> instead of:
>> CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;
>>
>
> Did you try it?  What happened?

Yes I tried and it works as expected (the two statements seem equivalent).
But the reverse does not:
CREATE TEMP TABLE test (data);
CREATE TRIGGER main.trig UPDATE ON temp.test BEGIN ...; END;
Is (temp.[trigger name], [^temp].[table name])  the only working case
when the provided database names are different ?

And I have another question related to hexadecimals.
It seems that they are not properly tokenized in select statements (at
least in the result columns part).
Invalid hexadecimals are not rejected but splitted into the valid part
and an elided 'as' part.
For example:
SELECT 0x1g;
is interpreted as
SELECT 0x1 g;

Invalid hexadecimals are properly rejected where there is no elided
'as' possible.
For example,
INSERT INTO test VALUES (0x1g);

Is it intentional ?

Many thanks.

> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL parsing logic for triggers

[gwenn]

Sorry,
I've just found the answer to the first question here:
http://www.sqlite.org/lang_insert.html
Regards.

On Sun, Oct 9, 2016 at 10:34 AM, gwenn <gwenn.k...@gmail.com> wrote:
> Hello,
> I am just looking for information (I am not asking for any change):
> It is for (auto) completion hints.
>
> 1) it seems not possible to insert default values in trigger command:
>
> // INSERT
> trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).
>
> versus
>
> // The INSERT command 
> /
> //
> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
>
> Is it a choice or an oversight ?
>
> 2) It seems possible to use different database names when creating a trigger:
>
> trigger_decl(A) ::= temp(T) TRIGGER ifnotexists(NOERR) nm(B) dbnm(Z)
> trigger_time(C) trigger_event(D)
> ON fullname(E) foreach_clause when_clause(G). {
>
> Is it to make possible to create temporary trigger by using the
> following syntax:
> CREATE TABLE test (data);
> CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
> instead of:
> CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;
>
> Or there are other use cases supported ?
>
> Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[gwenn]

Sorry,
I mean that there are impacts on SQLite wrappers:
https://github.com/xerial/sqlite-jdbc/commit/42557128d56da563126003180fd8b8e8978ec818#diff-5a06ee6e8dbd2f4087ab8d361df52832
https://github.com/gwenn/gosqlite/commit/ac9891a74d94fb57679407bd36b80a5be218c6d6
https://github.com/gwenn/sqlite-jna/commit/be502a521b5c7f2becd0d10381cd8ccc3bd50234

Before 3.16, these drivers were throwing an error when executing a
SELECT/PRAGMA with a zero column count.
But the new SQLite behaviour seems good to me.

On Sat, Jan 7, 2017 at 3:20 PM, Richard Hipp <d...@sqlite.org> wrote:
> On 1/7/17, gwenn <gwenn.k...@gmail.com> wrote:
>> Hello,
>> You should try executing a PRAGMA with no result such as:
>> PRAGMA table_info('no_such_table');
>>
>> Before 3.16,
>> sqlite3_step returns SQLITE_DONE
>> and
>> sqlite3_column_count returns 0
>>
>> After 3.16,
>> sqlite3_step returns SQLITE_DONE
>> and
>> sqlite3_column_count does not return 0 but 6
>>
>
> Please help us to understand why this is a problem?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[gwenn]

Hello,
You should try executing a PRAGMA with no result such as:
PRAGMA table_info('no_such_table');

Before 3.16,
sqlite3_step returns SQLITE_DONE
and
sqlite3_column_count returns 0

After 3.16,
sqlite3_step returns SQLITE_DONE
and
sqlite3_column_count does not return 0 but 6

Regards.

On Fri, Jan 6, 2017 at 1:50 AM, Richard Hipp  wrote:
> On 1/5/17, Richard Hipp  wrote:
>> But apparently, many people are looking at the result of
>> sqlite3_column_count() and if it is zero, they never bother to
>> sqlite3_finalize() their PRAGMA statements.
>
> Or maybe not.
>
> I just did an analysis of the sqlite3_column_count() changes between
> 3.15.2 and 3.16.0.  All changes involve PRAGMAs that used to return 0
> but now return a positive number.
>
> So if applications were using a zero sqlite3_column_count() return to
> indicate that the pragma returned no rows, that still works.
>
> In the cases where sqlite3_column_count() used to return 0 but now
> returns positive, that would induce the application to call
> sqlite3_step() on the pragma, which would then return SQLITE_DONE.
>
> Everything should still work.
>
> So now I don't really understand what is going wrong.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Extract all SQL commands from the test suite

[gwenn]

Hello,
I would like to extract all SQL commands from sqlite-src/test/*.test.
For example, extract:
PRAGMA cache_size=10;
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(randomblob(2));
BEGIN;
DELETE FROM t1;
INSERT INTO t1 VALUES(randomblob(15000));
...
from 8_3_names.test.

Should I modify the TCL extension ?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] List of subdomains/tools/drivers

[gwenn]

Hello,
Is there a web page listing all the SQLite related fossil repositories ?

https://www.sqlite.org/sqlar/
http://sqlite.org/sqllogictest/
https://www.sqlite.org/android/
http://system.data.sqlite.org/
http://lua.sqlite.org/
...

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA table_info and not nullable rowid alias

[gwenn]

Hello,
PRAGMA table_info reports that a rowid alias is nullable:

sqlite> .headers on
sqlite> CREATE TABLE test (id INTEGER PRIMARY KEY);
sqlite> pragma table_info("test");
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
sqilte> --0|id|INTEGER|1||1 expected
sqlite> INSERT INTO test (id) VALUES (NULL);
sqlite> SELECT rowid, id FROM test;
id|id
1|1

Regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] invalid column constraint ignored

[gwenn]

Hello,
I suppose it is a trade-off to make the parser light and fast.
But invalid column constraints are ignored.

sqlite> create table tbl (data text constraint x);

postgres=# create table tbl (data text constraint x);
ERROR:  syntax error at or near ")" at character 41

sqlite> create table tbl (data text deferrable initially deferred);

postgres=# create table tbl (data text deferrable initially deferred);
ERROR:  misplaced DEFERRABLE clause at character 29

sqlite> create table tbl (data text not null constraint x);

postgres=# create table tbl (data text not null constraint x);
ERROR:  syntax error at or near ")" at character 50

Regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parser.y and SQLITE_OMIT_ATTACH

[gwenn]

Sorry,
I didn't know that VACUUM uses ATTACH.


On Sun, Jun 11, 2017 at 1:03 PM, Richard Hipp <d...@sqlite.org> wrote:
> Have you tried this?  I don't think it will work.  IIRC, VACUUM uses
> ATTACH internally.
>
> On 6/11/17, gwenn <gwenn.k...@gmail.com> wrote:
>> Hello,
>>
>> %ifndef SQLITE_OMIT_VACUUM
>> %ifndef SQLITE_OMIT_ATTACH
>> cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
>> cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
>> %endif  SQLITE_OMIT_ATTACH
>> %endif  SQLITE_OMIT_VACUUM
>>
>> should be
>>
>> %ifndef SQLITE_OMIT_VACUUM
>> cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
>> %ifndef SQLITE_OMIT_ATTACH
>> cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
>> %endif  SQLITE_OMIT_ATTACH
>> %endif  SQLITE_OMIT_VACUUM
>>
>> Regards.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] parser.y and SQLITE_OMIT_ATTACH

[gwenn]

Hello,

%ifndef SQLITE_OMIT_VACUUM
%ifndef SQLITE_OMIT_ATTACH
cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
%endif  SQLITE_OMIT_ATTACH
%endif  SQLITE_OMIT_VACUUM

should be

%ifndef SQLITE_OMIT_VACUUM
cmd ::= VACUUM.{sqlite3Vacuum(pParse,0);}
%ifndef SQLITE_OMIT_ATTACH
cmd ::= VACUUM nm(X).  {sqlite3Vacuum(pParse,);}
%endif  SQLITE_OMIT_ATTACH
%endif  SQLITE_OMIT_VACUUM

Regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] carray module and

[gwenn]

Hi,
I guess there is a typo in the carray documentation:

diff --git a/ext/misc/carray.c b/ext/misc/carray.c
index b39904ae1..32fec3406 100644
--- a/ext/misc/carray.c
+++ b/ext/misc/carray.c
@@ -24,7 +24,7 @@
 **
 **static int aX[] = { 53, 9, 17, 2231, 4, 99 };
 **int i = sqlite3_bind_parameter_index(pStmt, "$ptr");
-**sqlite3_bind_value(pStmt, i, aX, "carray", 0);
+**sqlite3_bind_pointer(pStmt, i, aX, "carray", 0);
 **
 ** There is an optional third parameter to determine the datatype of
 ** the C-language array.  Allowed values of the third parameter are

And it seems that sqlite3_value_type is not specified for a pointer.
Currently, SQLITE_NULL is returned.
Could you confirm that I did not miss anything ?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Import zipped csv file

[gwenn]

Hello,
Is it possible to mix zipfile and csv extensions ?
sqlite> SELECT data FROM zipfile('csv_file.zip');
works.
sqlite> .shell unzip csv_file.zip
sqlite> CREATE VIRTUAL TABLE test USING csv(filename='csv_file.csv');
works.
How to pass the data extracted by zipfile to csv extension ?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typo

[gwenn]

Hi,
There is a typo here:
https://sqlite.org/session/sqlite3changeset_op.html
> If pbIncorrect is not NULL, then *pbIndirect is set to true
Should be pbIndirect instead of pbIncorrect.
Regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typo in Session extension documentation

[gwenn]

Hi,
https://sqlite.org/session/sqlite3changegroup_add_strm.html

int eConflict, /* DATA, MISSING, CONFLICT, CONSTRAINT */
should be
int eConflict, /* DATA, NOTFOUND, CONFLICT, CONSTRAINT, FOREIGN_KEY */

based on https://sqlite.org/session/c_changeset_conflict.html

Regards.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] json path escaping with double quote

[gwenn]

Ok,
I tried to patch this line:

diff --git a/ext/misc/json1.c b/ext/misc/json1.c
index d99d360b2..0bb4e1cee 100644
--- a/ext/misc/json1.c
+++ b/ext/misc/json1.c
@@ -1123,7 +1123,7 @@ static JsonNode *jsonLookupStep(
   u32 iStart, iLabel;
   JsonNode *pNode;
   iStart = jsonParseAddNode(pParse, JSON_OBJECT, 2, 0);
-  iLabel = jsonParseAddNode(pParse, JSON_STRING, i, zPath);
+  iLabel = jsonParseAddNode(pParse, JSON_STRING, nKey, zKey);
   zPath += i;
   pNode = jsonLookupAppend(pParse, zPath, pApnd, pzErr);
   if( pParse->oom ) return 0;

Regards.

On Thu, Jun 13, 2019 at 6:45 PM gwenn  wrote:
>
> Hello,
> With the json1 extension, we can escape special characters like '['
> from being interpreted as an array index by wrapping the path in
> double quotes. But sometimes, it does not work:
>
> sqlite> CREATE TABLE test (data TEXT);
> sqlite> INSERT INTO test (data) VALUES ('{}');
> sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', 
> json(3
> 2));
> sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
> "equity_spot[at_andr]"|32
> sqlite> -- KO: expected equity_spot[at_andr]|32 but got
> "equity_spot[at_andr]"|32
> sqlite> DELETE FROM test;
> sqlite> INSERT INTO test (data) VALUES ('{"equity_spot[at_andr]":34.3}');
> sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', 
> json(3
> 2));
> sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
> equity_spot[at_andr]|32
> sqlite> -- OK: no double quote
>
> I use json_patch as a workaround.
> Is this the expected behaviour ?
> Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] json path escaping with double quote

[gwenn]

Hello,
With the json1 extension, we can escape special characters like '['
from being interpreted as an array index by wrapping the path in
double quotes. But sometimes, it does not work:

sqlite> CREATE TABLE test (data TEXT);
sqlite> INSERT INTO test (data) VALUES ('{}');
sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', json(3
2));
sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
"equity_spot[at_andr]"|32
sqlite> -- KO: expected equity_spot[at_andr]|32 but got
"equity_spot[at_andr]"|32
sqlite> DELETE FROM test;
sqlite> INSERT INTO test (data) VALUES ('{"equity_spot[at_andr]":34.3}');
sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', json(3
2));
sqlite> SELECT json_each.key, json_each.value FROM test, json_each(test.data);
equity_spot[at_andr]|32
sqlite> -- OK: no double quote

I use json_patch as a workaround.
Is this the expected behaviour ?
Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] json path escaping with double quote

[gwenn]

$ make quicktest
is ok with the patch.
I will try to provide some additional test cases.

On Sat, Jun 15, 2019 at 10:37 AM gwenn  wrote:
>
> Ok,
> I tried to patch this line:
>
> diff --git a/ext/misc/json1.c b/ext/misc/json1.c
> index d99d360b2..0bb4e1cee 100644
> --- a/ext/misc/json1.c
> +++ b/ext/misc/json1.c
> @@ -1123,7 +1123,7 @@ static JsonNode *jsonLookupStep(
>u32 iStart, iLabel;
>JsonNode *pNode;
>iStart = jsonParseAddNode(pParse, JSON_OBJECT, 2, 0);
> -  iLabel = jsonParseAddNode(pParse, JSON_STRING, i, zPath);
> +  iLabel = jsonParseAddNode(pParse, JSON_STRING, nKey, zKey);
>zPath += i;
>pNode = jsonLookupAppend(pParse, zPath, pApnd, pzErr);
>if( pParse->oom ) return 0;
>
> Regards.
>
> On Thu, Jun 13, 2019 at 6:45 PM gwenn  wrote:
> >
> > Hello,
> > With the json1 extension, we can escape special characters like '['
> > from being interpreted as an array index by wrapping the path in
> > double quotes. But sometimes, it does not work:
> >
> > sqlite> CREATE TABLE test (data TEXT);
> > sqlite> INSERT INTO test (data) VALUES ('{}');
> > sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', 
> > json(3
> > 2));
> > sqlite> SELECT json_each.key, json_each.value FROM test, 
> > json_each(test.data);
> > "equity_spot[at_andr]"|32
> > sqlite> -- KO: expected equity_spot[at_andr]|32 but got
> > "equity_spot[at_andr]"|32
> > sqlite> DELETE FROM test;
> > sqlite> INSERT INTO test (data) VALUES ('{"equity_spot[at_andr]":34.3}');
> > sqlite> UPDATE test SET data = json_set(data, '$."equity_spot[at_andr]"', 
> > json(3
> > 2));
> > sqlite> SELECT json_each.key, json_each.value FROM test, 
> > json_each(test.data);
> > equity_spot[at_andr]|32
> > sqlite> -- OK: no double quote
> >
> > I use json_patch as a workaround.
> > Is this the expected behaviour ?
> > Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users