Re: [sqlite] sequencer

[petern]

Sylvain, are you happy with the performance?

Maybe you are using it differently but, from my tests, the DEFAULT clause
is ignored for PRIMARY KEY columns.   I had to use an ordinary column with
UNIQUE constraint to test your extension.  Below is a tester for 1 million
rows which completes in about 186 seconds.  The same million row test with
PRIMARY KEY column (and ignored DEFAULT) completes in about 5 seconds.

-
sqlite> .load sqlite-ext/libseqvalue.so
sqlite> DROP TABLE IF EXISTS sp_sequence;SELECT
seq_init_inc("seqtest",1,2);DROP TABLE IF EXISTS seqtest;CREATE TABLE
seqtest(rowid INT DEFAULT(seq_nextval('seqtest')) UNIQUE NOT NULL,payload
INT);
"seq_init_inc(""seqtest"",1,2)"
1
Run Time: real 0.394 user 0.168000 sys 0.044000
sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<10e6)
INSERT INTO seqtest(payload) SELECT i from T;
Run Time: real 190.775 user 186.208000 sys 0.616000
sqlite> select * from seqtest limit 10;
rowid,payload
3,1
5,2
7,3
9,4
11,5
13,6
15,7
17,8
19,9
21,10
Run Time: real 0.001 user 0.00 sys 0.00

Your idea peaked my interest because I used H2 in the past.  H2 has many
features but is very slow compared to SQLite for large tables.  There is
also a learning curve to move from thinking in H2 to thinking in SQLite.
For example, in this case, unless you can get a PRIMARY KEY column to work
with your sequence function, INSERTs will be a bottleneck.

FYI.  below is a reference implementation using INSTEAD OF TRIGGER to
compute next rowid for the same million row test. Compare 15 seconds in
user time with 186 seconds using DEFAULT sequence function.

sqlite> DROP TABLE IF EXISTS seqtest;CREATE TABLE seqtest(rowid INTEGER
PRIMARY KEY,payload int);DROP TRIGGER IF EXISTS seqtest;CREATE VIEW
seqtest_v AS SELECT * FROM seqtest;CREATE TRIGGER seqtest INSTEAD OF INSERT
ON seqtest_v BEGIN INSERT INTO seqtest(rowid,payload) VALUES ((SELECT
ifnull(max(rowid)+2,1) FROM seqtest),NEW.payload); END;
Run Time: real 0.189 user 0.00 sys 0.00
sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<10e6)
INSERT INTO seqtest_v(payload) SELECT i from T;Run Time: real 23.461 user
15.012000 sys 0.38
sqlite> select * from seqtest limit 10;
rowid,payload
1,1
3,2
5,3
7,4
9,5
11,6
13,7
15,8
17,9
19,10
Run Time: real 0.000 user 0.00 sys 0.00






On Thu, Aug 3, 2017 at 3:35 PM, Sylvain Pointeau  wrote:

> Hello,
>
> please find below my implementation of a sequence, I am open for any
> critic!
>
> Best regards,
> Sylvain
>
> ---
>
> #include "sqlite3ext.h"
> SQLITE_EXTENSION_INIT1
>
> void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value **argv)
> {
>   int rc = 0;
>   sqlite3_stmt *stmt;
>   sqlite3 *db = sqlite3_context_db_handle(context);
>
>   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
>   long seq_init_val = sqlite3_value_int64(argv[1]);
>   long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);
>
>   rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS SP_SEQUENCE ( " \
>   " SEQ_NAME TEXT NOT NULL PRIMARY KEY, " \
>   " SEQ_VAL INTEGER NOT NULL, " \
>   " SEQ_INC INTEGER NOT NULL " \
>   " )", 0, 0, 0);
>
>   if( rc != SQLITE_OK ) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>
>   sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
> seq_val, seq_inc) values (?, ?, ?)", -1, , 0);
>
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>   sqlite3_bind_int64(stmt, 2, seq_init_val);
>   sqlite3_bind_int64(stmt, 3, seq_inc_val);
>
>   rc = sqlite3_step(stmt);
>
>   sqlite3_finalize(stmt);
>
>   if (rc != SQLITE_DONE) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>   sqlite3_result_int64( context, seq_init_val );
> }
>
> void sp_seq_nextval(sqlite3_context *context, int argc, sqlite3_value
> **argv) {
>   int rc = 0;
>   sqlite3_stmt *stmt;
>   sqlite3 *db = sqlite3_context_db_handle(context);
>   long seq_val = 0;
>
>   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
>
>   sqlite3_prepare_v2(db, "update sp_sequence set seq_val = seq_val +
> seq_inc where seq_name = ?", -1, , 0);
>
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>
>   rc = sqlite3_step(stmt);
>
>   sqlite3_finalize(stmt);
>
>   if (rc != SQLITE_DONE) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>   sqlite3_prepare_v2(db, "select seq_val from sp_sequence where seq_name =
> ?", -1, , 0);
>
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>
>   rc = sqlite3_step(stmt);
>
>   if( rc == SQLITE_ROW) {
> seq_val = sqlite3_column_int64(stmt, 0);
>   }
>
>   sqlite3_finalize(stmt);
>
>   if (rc != SQLITE_ROW) {
> if( rc == SQLITE_DONE ) sqlite3_result_error(context, "sequence name
> does not exist", -1);
> else sqlite3_result_error(context, sqlite3_errmsg(db), 

Re: [sqlite] Problem with the new pointer-passing interface

[Gwendal Roué]

But... why don't you simply ask your users for a static string as well??? C++ 
makes it trivial to support this requirement of the C API.

// pointerType should be a static string
void wxSQLite3Statement::Bind(int paramIndex, void* pointer, char 
*pointerType, void(*DeletePointer)(void*))

Gwendal

> Le 3 août 2017 à 17:30, Ulrich Telle  a écrit :
> 
> Richard,
> 
>> Can you please provide more details on how having a dynamic string for
>> the pointer type would be helpful?  What is it that you are trying to
>> do that string constant will not work?  Please be as specific as
>> possible, so that I might better understand your problem.
> 
> I maintain a C++ wrapper library for SQLite especially for developers of 
> wxWidgets based applications. This wrapper library offers access to almost 
> all SQLite features (that make sense for an end-user application) through the 
> use of C++ classes. That is, the wrapper classes encapsulate all calls to the 
> SQlite C API.
> 
> With the release of SQLite 3.20.0 the new pointer-passing interface was 
> introduced, and I found it quite useful to support extensions like carray. 
> Therefore, I implemented a pointer bind method for the prepared SQL statement 
> classs. This method makes internally a call to function sqlite3_bind_pointer. 
> The signature and implementation of the method looks like this:
> 
> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const wxString& 
> pointerType, void(*DeletePointer)(void*))
> {
>  CheckStmt();
>  const char* localPointerType = m_stmt->MakePointerTypeCopy(pointerType);
>  int rc = sqlite3_bind_pointer(m_stmt->m_stmt, paramIndex, pointer, 
> localPointerType, DeletePointer);
> }
> 
> The member variable m_stmt is a reference counted reference object to a 
> prepared SQL statement (sqlite3_stmt). This makes it possible to pass around 
> the SQL statement object and to clean up the SQLite data structures when the 
> last reference to the statement is deleted. This reference object now 
> includes a dynamic array holding pointer type string duplicates until the 
> reference object itself goes out of scope.
> 
> However, in my first implementation I converted the pointer type string 
> parameter (wxString object) to a local char* variable. Since this local 
> variable was destroyed after leaving the method, the select on the carray 
> table failed, since the pointer type string was void.
> 
> Now, I create a copy of the pointer type string in a data structure that is 
> kept alive until the SQL statement object is deleted. The carray extension 
> now works flawlessly in the context of my wrapper.
> 
> For a C++ wrapper you could argue that using the SQLite API directly is 
> feasible. However, for SQLite wrappers for other languages like Python or 
> Lua, this might not work out.
> 
> Regards,
> 
> Ulrich
> ___
> 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] sequencer

[Sylvain Pointeau]

Le ven. 4 août 2017 à 02:42, Nico Williams  a écrit :

> On Fri, Aug 04, 2017 at 12:35:56AM +0200, Sylvain Pointeau wrote:
> > void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value
> **argv) {
> >   int rc = 0;
> >   sqlite3_stmt *stmt;
> >   sqlite3 *db = sqlite3_context_db_handle(context);
>
> If you use sqlite3_create_function*() with nArg == -1 then you can have
> just one UDF from sp_seq_init().  You'd have to check argc here though.
>

yes this is a good idea, I will do that as well as testing the arguments as
also Petern mentioned.

>
> >   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
> >   long seq_init_val = sqlite3_value_int64(argv[1]);
>
> seq_init_val could be optional and default to 0 or maybe 1.
>

good idea, defaukt to 1

>
> >   long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);
>
> I think some type checking should be done.
>

yes agree


> You could just take the argv[] values and bind them directly to the
> insert below, and use CHECK constraints on the SP_SEQUENCE table.


> Or you could use sqlite3_value_type() to check that each argument is of
> the expected value.
>
> I like the idea of using a CHECK constraint.  It simplifies the C code
> by having SQLite3 do more of the work.
>

I like also the check constraint, I am just wondering if the error, if any,
would descriptive enough. I will test it at least..


> >   rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS SP_SEQUENCE ( " \
> >   " SEQ_NAME TEXT NOT NULL PRIMARY KEY, " \
> >   " SEQ_VAL INTEGER NOT NULL, " \
> >   " SEQ_INC INTEGER NOT NULL " \
> >   " )", 0, 0, 0);
>
> You might want to add WITHOUT ROWID here (unless you want this to work
> with older versions of SQLite3).  You could make this conditional on a C
> pre-processor macro.


it is a good idea, however it does not seem critical


> >   if( rc != SQLITE_OK ) {
> > sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> > return;
> >   }
> >
> >
> >   sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
> > seq_val, seq_inc) values (?, ?, ?)", -1, , 0);
>
> Should this init function re-initialize sequences if called more than
> once for the same sequence name?
>

yes I did it on purpose


> Because that's what INSERT OR REPLACE will do (since on conflict it will
> delete the old row and insert a new one).  If you don't mean to
> re-initialize then use INSERT OR IGNORE.
>
> (When I want INSERT OR UPDATE I just run two statements:
>
> UPDATE .. WHERE ..;
> INSERT OR IGNORE ..;)
>
> >   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
> >   sqlite3_bind_int64(stmt, 2, seq_init_val);
> >   sqlite3_bind_int64(stmt, 3, seq_inc_val);
> >
> >   rc = sqlite3_step(stmt);
> >
> >   sqlite3_finalize(stmt);
> >
> >   if (rc != SQLITE_DONE) {
> > sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> > return;
> >   }
> >
> >   sqlite3_result_int64( context, seq_init_val );
> > }
> >
> > void sp_seq_nextval(sqlite3_context *context, int argc, sqlite3_value
> > **argv) {
> >   int rc = 0;
> >   sqlite3_stmt *stmt;
> >   sqlite3 *db = sqlite3_context_db_handle(context);
> >   long seq_val = 0;
> >
> >   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
> >
> >   sqlite3_prepare_v2(db, "update sp_sequence set seq_val = seq_val +
> > seq_inc where seq_name = ?", -1, , 0);
> >
> >   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
> >
> >   rc = sqlite3_step(stmt);
> >
> >   sqlite3_finalize(stmt);
> >
> >   if (rc != SQLITE_DONE) {
> > sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> > return;
> >   }
> >
> >   sqlite3_prepare_v2(db, "select seq_val from sp_sequence where seq_name
> =
> > ?", -1, , 0);
> >
> >   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>
> 
>
> If ever SQLite3 got higher write concurrency (it almost certainly won't,
> but derivatives might), then a simple way to ensure atomicity here might
> be to do the SELECT first then an UPDATE with a WHERE seq_val =
> :the_value_just_read, and loop as necessary.
>
> do {
> current_value = ;
>   current value hasn't changed>;
> } while ();
>
> Though if one is going to have higher write concurrency then one ought
> to have SELECT .. FOR UPDATE.
>
> Anyways, no change needed here.
>

should I use the mutex lock to ensure atomicity?


> 
>
> >   rc = sqlite3_step(stmt);
> >
> >   if( rc == SQLITE_ROW) {
> > seq_val = sqlite3_column_int64(stmt, 0);
> >   }
> >
> >   sqlite3_finalize(stmt);
> >
> >   if (rc != SQLITE_ROW) {
> > if( rc == SQLITE_DONE ) sqlite3_result_error(context, "sequence name
> > does not exist", -1);
> > else sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> > return;
> >   }
> >
> >   sqlite3_result_int64( context, seq_val );
> > }
> >
> >
> > int sqlite3_extension_init(
> >   sqlite3 *db,
> >   char **pzErrMsg,
> >   const sqlite3_api_routines *pApi
> > ){
> >  

Re: [sqlite] sequencer

[Nico Williams]

On Thu, Aug 03, 2017 at 07:42:12PM -0500, Nico Williams wrote:
> On Fri, Aug 04, 2017 at 12:35:56AM +0200, Sylvain Pointeau wrote:
> I think some type checking should be done.
> 
> You could just take the argv[] values and bind them directly to the
> insert below, and use CHECK constraints on the SP_SEQUENCE table.

This seems best, actually, partly because you can't hide this table and
with CHECK constraints you get to make sure that users use the right
types in these columns.

I do wish SQLite3 had a strict typing mode where the declared type of a
column is automatically made into a CHECK constraint.

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

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

[Nico Williams]

On Tue, Aug 01, 2017 at 10:56:47AM -0700, Matt Chambers wrote:
> load_extension() has the very sensible behavior of:
> > So for example, if "samplelib" cannot be loaded, then names like
> > "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried
> > also.
> 
> I would like to see that extended to include "libsamplelib.so" since that is
> the default naming scheme on many *nix platforms. This simple change would
> allow me to use the same base library name for my extension on both Windows
> and Linux. Otherwise I have to modify my build system to override its
> default behavior of adding the lib prefix on Linux.

You're mistaken.

lib.so is NOT "the default naming scheme on many *nix platforms".

lib.so is the naming scheme when you want the link-editor (e.g.,
ld(1)) to find a library using -l.

But loadable modules are almost never meant to be used that way.
They're usually meant to be used only through dlopen() and friends.

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

Re: [sqlite] sequencer

[Nico Williams]

On Fri, Aug 04, 2017 at 12:35:56AM +0200, Sylvain Pointeau wrote:
> void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value **argv) {
>   int rc = 0;
>   sqlite3_stmt *stmt;
>   sqlite3 *db = sqlite3_context_db_handle(context);

If you use sqlite3_create_function*() with nArg == -1 then you can have
just one UDF from sp_seq_init().  You'd have to check argc here though.

>   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
>   long seq_init_val = sqlite3_value_int64(argv[1]);

seq_init_val could be optional and default to 0 or maybe 1.

>   long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);

I think some type checking should be done.

You could just take the argv[] values and bind them directly to the
insert below, and use CHECK constraints on the SP_SEQUENCE table.

Or you could use sqlite3_value_type() to check that each argument is of
the expected value.

I like the idea of using a CHECK constraint.  It simplifies the C code
by having SQLite3 do more of the work.

>   rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS SP_SEQUENCE ( " \
>   " SEQ_NAME TEXT NOT NULL PRIMARY KEY, " \
>   " SEQ_VAL INTEGER NOT NULL, " \
>   " SEQ_INC INTEGER NOT NULL " \
>   " )", 0, 0, 0);

You might want to add WITHOUT ROWID here (unless you want this to work
with older versions of SQLite3).  You could make this conditional on a C
pre-processor macro.

>   if( rc != SQLITE_OK ) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
> 
> 
>   sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
> seq_val, seq_inc) values (?, ?, ?)", -1, , 0);

Should this init function re-initialize sequences if called more than
once for the same sequence name?

Because that's what INSERT OR REPLACE will do (since on conflict it will
delete the old row and insert a new one).  If you don't mean to
re-initialize then use INSERT OR IGNORE.

(When I want INSERT OR UPDATE I just run two statements:

UPDATE .. WHERE ..;
INSERT OR IGNORE ..;)

>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>   sqlite3_bind_int64(stmt, 2, seq_init_val);
>   sqlite3_bind_int64(stmt, 3, seq_inc_val);
> 
>   rc = sqlite3_step(stmt);
> 
>   sqlite3_finalize(stmt);
> 
>   if (rc != SQLITE_DONE) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
> 
>   sqlite3_result_int64( context, seq_init_val );
> }
> 
> void sp_seq_nextval(sqlite3_context *context, int argc, sqlite3_value
> **argv) {
>   int rc = 0;
>   sqlite3_stmt *stmt;
>   sqlite3 *db = sqlite3_context_db_handle(context);
>   long seq_val = 0;
> 
>   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
> 
>   sqlite3_prepare_v2(db, "update sp_sequence set seq_val = seq_val +
> seq_inc where seq_name = ?", -1, , 0);
> 
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
> 
>   rc = sqlite3_step(stmt);
> 
>   sqlite3_finalize(stmt);
> 
>   if (rc != SQLITE_DONE) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
> 
>   sqlite3_prepare_v2(db, "select seq_val from sp_sequence where seq_name =
> ?", -1, , 0);
> 
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);



If ever SQLite3 got higher write concurrency (it almost certainly won't,
but derivatives might), then a simple way to ensure atomicity here might
be to do the SELECT first then an UPDATE with a WHERE seq_val =
:the_value_just_read, and loop as necessary.

do {
current_value = ;
;
} while ();

Though if one is going to have higher write concurrency then one ought
to have SELECT .. FOR UPDATE.

Anyways, no change needed here.



>   rc = sqlite3_step(stmt);
> 
>   if( rc == SQLITE_ROW) {
> seq_val = sqlite3_column_int64(stmt, 0);
>   }
> 
>   sqlite3_finalize(stmt);
> 
>   if (rc != SQLITE_ROW) {
> if( rc == SQLITE_DONE ) sqlite3_result_error(context, "sequence name
> does not exist", -1);
> else sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
> 
>   sqlite3_result_int64( context, seq_val );
> }
> 
> 
> int sqlite3_extension_init(
>   sqlite3 *db,
>   char **pzErrMsg,
>   const sqlite3_api_routines *pApi
> ){
>  SQLITE_EXTENSION_INIT2(pApi)

I'd create just one UDF using sp_seq_init(), make the narg -1, and check
the argc counts in sp_seq_init().

>  sqlite3_create_function(db, "seq_init_inc", 3, SQLITE_UTF8, 0,
> sp_seq_init, 0, 0);
>  sqlite3_create_function(db, "seq_init", 2, SQLITE_UTF8, 0, sp_seq_init, 0,
> 0);

If you made seq_next() have nArg == -1 then you could have a default
sequence for the no-arguments case...  You don't have to -- you could
say that's just creeping featuritis!

>  sqlite3_create_function(db, "seq_nextval", 1, SQLITE_UTF8, 0,
> sp_seq_nextval, 0, 0);
>  return 0;
> }

Nice!  Did you test it?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

Re: [sqlite] sequencer

[petern]

Neat.  For production, you might want to check the type on function
arguments before using them.

Is it working fairly fast on inserts?   That is, I presume, if this is
intended for bypassing restrictions on the DEFAULT clause (expr) of
column-constraint in a CREATE TABLE statement:

https://www.sqlite.org/lang_createtable.html



On Thu, Aug 3, 2017 at 3:35 PM, Sylvain Pointeau  wrote:

> Hello,
>
> please find below my implementation of a sequence, I am open for any
> critic!
>
> Best regards,
> Sylvain
>
> ---
>
> #include "sqlite3ext.h"
> SQLITE_EXTENSION_INIT1
>
> void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value **argv)
> {
>   int rc = 0;
>   sqlite3_stmt *stmt;
>   sqlite3 *db = sqlite3_context_db_handle(context);
>
>   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
>   long seq_init_val = sqlite3_value_int64(argv[1]);
>   long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);
>
>   rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS SP_SEQUENCE ( " \
>   " SEQ_NAME TEXT NOT NULL PRIMARY KEY, " \
>   " SEQ_VAL INTEGER NOT NULL, " \
>   " SEQ_INC INTEGER NOT NULL " \
>   " )", 0, 0, 0);
>
>   if( rc != SQLITE_OK ) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>
>   sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
> seq_val, seq_inc) values (?, ?, ?)", -1, , 0);
>
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>   sqlite3_bind_int64(stmt, 2, seq_init_val);
>   sqlite3_bind_int64(stmt, 3, seq_inc_val);
>
>   rc = sqlite3_step(stmt);
>
>   sqlite3_finalize(stmt);
>
>   if (rc != SQLITE_DONE) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>   sqlite3_result_int64( context, seq_init_val );
> }
>
> void sp_seq_nextval(sqlite3_context *context, int argc, sqlite3_value
> **argv) {
>   int rc = 0;
>   sqlite3_stmt *stmt;
>   sqlite3 *db = sqlite3_context_db_handle(context);
>   long seq_val = 0;
>
>   const unsigned char* seq_name = sqlite3_value_text(argv[0]);
>
>   sqlite3_prepare_v2(db, "update sp_sequence set seq_val = seq_val +
> seq_inc where seq_name = ?", -1, , 0);
>
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>
>   rc = sqlite3_step(stmt);
>
>   sqlite3_finalize(stmt);
>
>   if (rc != SQLITE_DONE) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>   sqlite3_prepare_v2(db, "select seq_val from sp_sequence where seq_name =
> ?", -1, , 0);
>
>   sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
>
>   rc = sqlite3_step(stmt);
>
>   if( rc == SQLITE_ROW) {
> seq_val = sqlite3_column_int64(stmt, 0);
>   }
>
>   sqlite3_finalize(stmt);
>
>   if (rc != SQLITE_ROW) {
> if( rc == SQLITE_DONE ) sqlite3_result_error(context, "sequence name
> does not exist", -1);
> else sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }
>
>   sqlite3_result_int64( context, seq_val );
> }
>
>
> int sqlite3_extension_init(
>   sqlite3 *db,
>   char **pzErrMsg,
>   const sqlite3_api_routines *pApi
> ){
>  SQLITE_EXTENSION_INIT2(pApi)
>  sqlite3_create_function(db, "seq_init_inc", 3, SQLITE_UTF8, 0,
> sp_seq_init, 0, 0);
>  sqlite3_create_function(db, "seq_init", 2, SQLITE_UTF8, 0, sp_seq_init, 0,
> 0);
>  sqlite3_create_function(db, "seq_nextval", 1, SQLITE_UTF8, 0,
> sp_seq_nextval, 0, 0);
>  return 0;
> }
> ___
> 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] Feature request: check for 'lib' prefix for load_extension()

[J Decker]

On Thu, Aug 3, 2017 at 10:42 AM, Bob Friesenhahn <
bfrie...@simple.dallas.tx.us> wrote:

> On Tue, 1 Aug 2017, Matt Chambers wrote:
>
> load_extension() has the very sensible behavior of:
>>
>>> So for example, if "samplelib" cannot be loaded, then names like
>>> "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried
>>> also.
>>>
>>
>> I would like to see that extended to include "libsamplelib.so" since that
>> is
>> the default naming scheme on many *nix platforms. This simple change would
>> allow me to use the same base library name for my extension on both
>> Windows
>> and Linux. Otherwise I have to modify my build system to override its
>> default behavior of adding the lib prefix on Linux.
>>
>
> These conveniences tend to lessen the security of sqlite since this is
> arbitrary executable code capable of doing anything the user is able to do
> (e.g. delete all files or add a virus).  If the user is willing to be
> precise, then there is less risk of a compromised module/library from being
> introduced.
>
>
then it shouldn't try any alternatives.  OR it should always be
'lib.so' which would also be acceptable.



> It should be obvious that calling sqlite3_load_extension() without an
> absolute path, or other safeguards, exposes the program to accidentally
> loading a file from whatever happens to be the current directory (perhaps a
> writeable directory that an attacker was able to write into).
>
> Apple's OS X and Microsoft Windows always try to load from the current
> directory.
>
> Bob
> --
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.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] sequencer

[Sylvain Pointeau]

Hello,

please find below my implementation of a sequence, I am open for any critic!

Best regards,
Sylvain

---

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value **argv) {
  int rc = 0;
  sqlite3_stmt *stmt;
  sqlite3 *db = sqlite3_context_db_handle(context);

  const unsigned char* seq_name = sqlite3_value_text(argv[0]);
  long seq_init_val = sqlite3_value_int64(argv[1]);
  long seq_inc_val = argc < 3 ? 1 : sqlite3_value_int64(argv[2]);

  rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS SP_SEQUENCE ( " \
  " SEQ_NAME TEXT NOT NULL PRIMARY KEY, " \
  " SEQ_VAL INTEGER NOT NULL, " \
  " SEQ_INC INTEGER NOT NULL " \
  " )", 0, 0, 0);

  if( rc != SQLITE_OK ) {
sqlite3_result_error(context, sqlite3_errmsg(db), -1);
return;
  }


  sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name,
seq_val, seq_inc) values (?, ?, ?)", -1, , 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
  sqlite3_bind_int64(stmt, 2, seq_init_val);
  sqlite3_bind_int64(stmt, 3, seq_inc_val);

  rc = sqlite3_step(stmt);

  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
sqlite3_result_error(context, sqlite3_errmsg(db), -1);
return;
  }

  sqlite3_result_int64( context, seq_init_val );
}

void sp_seq_nextval(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int rc = 0;
  sqlite3_stmt *stmt;
  sqlite3 *db = sqlite3_context_db_handle(context);
  long seq_val = 0;

  const unsigned char* seq_name = sqlite3_value_text(argv[0]);

  sqlite3_prepare_v2(db, "update sp_sequence set seq_val = seq_val +
seq_inc where seq_name = ?", -1, , 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);

  rc = sqlite3_step(stmt);

  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
sqlite3_result_error(context, sqlite3_errmsg(db), -1);
return;
  }

  sqlite3_prepare_v2(db, "select seq_val from sp_sequence where seq_name =
?", -1, , 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);

  rc = sqlite3_step(stmt);

  if( rc == SQLITE_ROW) {
seq_val = sqlite3_column_int64(stmt, 0);
  }

  sqlite3_finalize(stmt);

  if (rc != SQLITE_ROW) {
if( rc == SQLITE_DONE ) sqlite3_result_error(context, "sequence name
does not exist", -1);
else sqlite3_result_error(context, sqlite3_errmsg(db), -1);
return;
  }

  sqlite3_result_int64( context, seq_val );
}


int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
 SQLITE_EXTENSION_INIT2(pApi)
 sqlite3_create_function(db, "seq_init_inc", 3, SQLITE_UTF8, 0,
sp_seq_init, 0, 0);
 sqlite3_create_function(db, "seq_init", 2, SQLITE_UTF8, 0, sp_seq_init, 0,
0);
 sqlite3_create_function(db, "seq_nextval", 1, SQLITE_UTF8, 0,
sp_seq_nextval, 0, 0);
 return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Checkin d8637bad produces bad amalgamation

[Keith Medcalf]

Check-in d8637bad produces a bad amalgamation output.

Index: tool/mksqlite3c-noext.tcl
==
--- tool/mksqlite3c-noext.tcl
+++ tool/mksqlite3c-noext.tcl
@@ -26,11 +26,11 @@
 # from in this file.  The version number is needed to generate the header
 # comment of the amalgamation.
 #
 set addstatic 1
 set linemacros 0
-set useapicall 0
+set useapicall 1
 for {set i 0} {$i<[llength $argv]} {incr i} {
   set x [lindex $argv $i]
   if {[regexp {^-+nostatic$} $x]} {
 set addstatic 0
   } elseif {[regexp {^-+linemacros} $x]} {

Index: tool/mksqlite3c.tcl
==
--- tool/mksqlite3c.tcl
+++ tool/mksqlite3c.tcl
@@ -26,11 +26,11 @@
 # from in this file.  The version number is needed to generate the header
 # comment of the amalgamation.
 #
 set addstatic 1
 set linemacros 0
-set useapicall 0
+set useapicall 1
 for {set i 0} {$i<[llength $argv]} {incr i} {
   set x [lindex $argv $i]
   if {[regexp {^-+nostatic$} $x]} {
 set addstatic 0
   } elseif {[regexp {^-+linemacros} $x]} {

Index: tool/mksqlite3h.tcl
==
--- tool/mksqlite3h.tcl
+++ tool/mksqlite3h.tcl
@@ -36,11 +36,11 @@
 #
 set TOP [lindex $argv 0]

 # Enable use of SQLITE_APICALL macros at the right points?
 #
-set useapicall 0
+set useapicall 1

 if {[lsearch -regexp [lrange $argv 1 end] {^-+useapicall}] != -1} {
   set useapicall 1
 }


Is applied so that the SQLITE_APICALL defines are added to the amalgamation
and RBU is included in the amalgamation

then the output sqlite3.c file contains entries such as (from _SQLITE3RBU_H_):

SQLITE_API SQLITE_API sqlite3rbu *SQLITE_APICALL sqlite3rbu_open(
  const char *zTarget,
  const char *zRbu,
  const char *zState
);

for all the function definitions.

The actual definition of the function is fine however.

ISSUE:

If SQLITE_API is defined as "_declspec(export)" this does not create a problem 
since the multiple declarations of the same attribute are not syntax violations,
however
If SQLITE_API is defined as "static" this causes the compiler to vomit because 
"static static void* function(...bunchOfArgs)" is invalid syntax.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Problem with the new pointer-passing interface

[Nico Williams]

On Thu, Aug 03, 2017 at 08:33:31PM +0200, Ulrich Telle wrote:
> > Rhetorical: Why not use the pointer value itself then instead of the
> > contents of the string?  After all, the string should just be a .text
> > section constant string...
> 
> The SQLite developer team chose a string representation for the
> pointer type to make naming collisions less likely.

So, that was rhetorical :)

> > Now, suppose that you're building FFI bindings for SQLite3 for some
> > programming language, e.g., Python.  So you have a string from the
> > run-time for this language, but you can't ensure that it will have the
> > necessary lifetime.  So now you have to make a copy.  It's a bit of a
> > pain.  SQLite3 could have made a copy itself.
> 
> I would have preferred that, too, but I learned that this would have
> imposed a runtime penalty even for applications not using the new
> pointer interface. 

That seems unlikely.  Basically the connection has to have an array/list
of types, which can be empty/null when the pointer passing interface is
not used.

> > On the flip side, it's possible that you have to do some conversions
> > anyways because SQLite3 deals with C strings and the target language
> > run-time deals with counted-codeunit strings or whatever.
> 
> The conversion is not the problem. The problem is that SQLite requires
> that the pointer type string has a life span at least as long as the
> prepared statement referring to it. And this requires that the
> application takes care of that.

I meant that the author of some bindings for SQLite3 might have to
perform conversions at their layer.  If you have to do that, then
allocating your own copy (and keeping track of it so you can release it
atexit() or whatever) is not that big a deal, which is why I concluded
with this:

> > So even if SQLite3 made a copy, the FFI bindings might have to make
> > their own copy anyways.  Thus: who cares :)
> 
> The problem is to keep the copy alive long enough. However, the
> problem is not that big. It can be solved in less than 50 lines of
> code.

It's easier for SQLite3 to do that since it has a suitable structure for
that: the connection associated with the statement.

Whereas for an FFI bindings of SQLite3 it can be much harder, since now
you'll need a list/table of these strings to free atexit() or when the
DLL is unloded, or whatever is appropriate.

> > Still, for many cases it will be easier to write code to this API if
> > SQLite3 makes its own copy.
> 
> Yes, but Richard Hipp made it quite clear that the latter is unlikely
> to happen.

Maybe I've made a strong enough argument that he might revisit that
decision.

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

Re: [sqlite] command shell .timeout

[Roman Fleysher]

Thank you, Richard.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Thursday, August 03, 2017 3:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] command shell .timeout

On 8/3/17, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
--
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] command shell .timeout

[Richard Hipp]

On 8/3/17, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
-- 
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] command shell .timeout

[Roman Fleysher]

Dear SQLiters,

I am using sqlit3 command shell. It has ".timeout" command. What is the 
difference between:

.timeout MS
PRAGMA busy_timeout = milliseconds;

I am getting "database is locked" when accessing the same file from multiple 
concurrent shells and trying to set timeouts to avoid this.

(By the way, PRAGMA busy_timeout = milliseconds; prints new timeout to screen. 
I would expect printing only if new value is not provided.)

Thank you

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

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

[Brian Clifford]

Thanks for looking into this, I see in the original example I passed in a
nested call of 28 replaces() this was the cutoff point where it would fail,
the following line has about 36 replaces() this fails for me:

replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(Transcript,
',',''),';',''),':',''),'[',''),']',''),'{',''),'}',''),'(',
''),')',''),'\',''),'/',''),'"',''),'_',''),'+',''),'=',''),
'£',''),'$',''),'€',''),'%',''),'^',''),'&',''),'*',''),'@',
''),'#',''),'”',''),'“',''),'…',''),'¬',''),'¦',''),'`',''),
'~',''),'|',''),'.',''),'!',''),'?',''),'
','')

I get the parser stack overflow exception at the point where I create the
index:

"CREATE INDEX RecordingsNoPunctuationPart1Idx ON Recordings({0});"

Or in FULL:

"CREATE INDEX RecordingsNoPunctuationPart1Idx ON Recordings(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(Transcript,
',',''),';',''),':',''),'[',''),']',''),'{',''),'}',''),'(',
''),')',''),'\',''),'/',''),'"',''),'_',''),'+',''),'=',''),
'£',''),'$',''),'€',''),'%',''),'^',''),'&',''),'*',''),'@',
''),'#',''),'”',''),'“',''),'…',''),'¬',''),'¦',''),'`',''),
'~',''),'|',''),'.',''),'!',''),'?',''),'
',''));"


*Brian Clifford*
Software Developer

On 3 August 2017 at 11:38, Richard Hipp  wrote:

> I am unable to reproduce the problem.  Do you have any additional
> hints on how to make this fail?
>
> The depth of the stack in the push-down automaton used by the parser
> is deliberately limited to 100 levels.  This is a feature, not a bug.
> See https://www.sqlite.org/compile.html#yystackdepth for additional
> information.
>
> On 8/3/17, Brian Clifford  wrote:
> > Hi,
> >
> > I've subscribed to the sqllite mailing list but am still awaiting
> > authorization.
> >
> > I'd like to report a bug I found with sqlite where I'm creating a
> function
> > index which consists of approx 36 nested calls to the replace() function
> > (to remove punctuation)
> >
> > I get a parser stackoverflow exception after about 28 nested calls.
> >
> > Below is the pseudo-code I use for creating my table and the function
> index
> > ---
> >
> > string _ReplacePunctuationFunctionPart =
> > "replace(replace(replace(replace(replace(replace(
> replace(replace(replace(replace(replace(replace(replace(replace(replace(
> replace(replace(replace(replace(replace(replace(replace(replace(replace(
> replace(replace(replace(Transcript,',',''),';',''),':'
> ,''),'[',''),']',''),'{',''),'}',''),'(',''),')',''),'\','')
> ,'/',''),'"',''),'_',''),'+',''),'=',''),'£',''),'$',''),'€'
> ,''),'%',''),'^',''),'&',''),'*',''),'@',''),'#',''),'”',''),'“',''),'
> > ','')"
> >
> >
> >  "CREATE TABLE \"Recordings\" (\"Id\" INTEGER PRIMARY KEY AUTOINCREMENT
> NOT
> > NULL, \"Transcript\" TEXT NOT NULL COLLATE NOCASE, \"Mp3FileName\" TEXT
> NOT
> > NULL UNIQUE, \"Label\" TEXT);";
> >  "CREATE UNIQUE INDEX RecordingsMp3Index ON Recordings(Mp3FileName);";
> >  "CREATE INDEX RecordingsTextIndex ON Recordings(Transcript);";
> >  string.Format("CREATE INDEX RecordingsNoPunctuationIdx ON
> > Recordings({0});", _ReplacePunctuationFunctionPart);
> >
> >   SELECT 
> >
> >  string.Format("SELECT Id, {0} as 'TranscriptNoPunctuation' FROM
> > Recordings;", _ReplacePunctuationFunctionPart);
> >
> > --
> >
> > regards,
> >
> > *Brian Clifford*
> > Software Developer
> >
> > --
> >
> >
> > 
> >
> > www.thinksmartbox.com
> >
> > Facebook   Twitter
> >   LinkedIn
> > 
> YouTube
> > 
> >
> > Smartbox Assistive Technology, Ysobel House, Enigma Commercial Centre,
> > Sandys
> > Road, Malvern, WR14 1JJ
> >
> > Tel: +44 (0) 1684 578868
> > ___
> > 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
>

-- 




www.thinksmartbox.com

Facebook   Twitter 
  LinkedIn 
  YouTube 


Smartbox Assistive Technology, Ysobel House, Enigma Commercial Centre, Sandys 
Road, Malvern, WR14 1JJ

Tel: +44 (0) 1684 578868

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Nico,

> Rhetorical: Why not use the pointer value itself then instead of the
> contents of the string?  After all, the string should just be a .text
> section constant string...

The SQLite developer team chose a string representation for the pointer type to 
make naming collisions less likely.

> Now, suppose that you're building FFI bindings for SQLite3 for some
> programming language, e.g., Python.  So you have a string from the
> run-time for this language, but you can't ensure that it will have the
> necessary lifetime.  So now you have to make a copy.  It's a bit of a
> pain.  SQLite3 could have made a copy itself.

I would have preferred that, too, but I learned that this would have imposed a 
runtime penalty even for applications not using the new pointer interface. 

> On the flip side, it's possible that you have to do some conversions
> anyways because SQLite3 deals with C strings and the target language
> run-time deals with counted-codeunit strings or whatever.

The conversion is not the problem. The problem is that SQLite requires that the 
pointer type string has a life span at least as long as the prepared statement 
referring to it. And this requires that the application takes care of that.

> So even if SQLite3 made a copy, the FFI bindings might have to make
> their own copy anyways.  Thus: who cares :)

The problem is to keep the copy alive long enough. However, the problem is not 
that big. It can be solved in less than 50 lines of code.

> Still, for many cases it will be easier to write code to this API if
> SQLite3 makes its own copy.

Yes, but Richard Hipp made it quite clear that the latter is unlikely to happen.

Regards,

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

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

[Bob Friesenhahn]

On Tue, 1 Aug 2017, Matt Chambers wrote:


load_extension() has the very sensible behavior of:

So for example, if "samplelib" cannot be loaded, then names like
"samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried
also.


I would like to see that extended to include "libsamplelib.so" since that is
the default naming scheme on many *nix platforms. This simple change would
allow me to use the same base library name for my extension on both Windows
and Linux. Otherwise I have to modify my build system to override its
default behavior of adding the lib prefix on Linux.


These conveniences tend to lessen the security of sqlite since this is 
arbitrary executable code capable of doing anything the user is able 
to do (e.g. delete all files or add a virus).  If the user is willing 
to be precise, then there is less risk of a compromised module/library 
from being introduced.


It should be obvious that calling sqlite3_load_extension() without an 
absolute path, or other safeguards, exposes the program to 
accidentally loading a file from whatever happens to be the current 
directory (perhaps a writeable directory that an attacker was able to 
write into).


Apple's OS X and Microsoft Windows always try to load from the current 
directory.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Nico Williams]

On Thu, Aug 03, 2017 at 06:59:44PM +0300, Alek Paunov wrote:
> On 2017-08-02 20:24, Nico Williams wrote:
> >I've implemented "inheritance" with triggers to map DMLs on "derived"
> >tables onto "base" tables.  That works and is much more general.  If you
> >need a rowid, however, the triggers have to do more work, first acquring
> >the rowid from the base table, then setting it on the derived table
> >rows, and this can get tricky.
> 
> Yes. I meant exactly that usecase - where many logically "subclass" tables
> share common "address" (rowid) space, so that, any object (or the rest of
> the DB) could "reference" objects in the whole hierarchy (like unified value
> references in script languages data-models).

It's so darned helpful, that one might expect SQL to support this.  But
no :(

PG's INHERIT fails to inherit PRIMARY KEY and UNIQUE constraints!
Inheriting those would have to also share the same namespaces, else
INHERIT would be useless still.

FOREIGN KEYs generally compile into triggers internally.  INHERIT could
as well, though there is more scope for space optimization here, as the
trigger-based scheme I've used ends up creating a fair bit of
duplication.  It's "just a matter of code" :)

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

Re: [sqlite] sequencer

[Alek Paunov]

On 2017-08-02 20:24, Nico Williams wrote:

On Wed, Aug 02, 2017 at 07:48:52PM +0300, Alek Paunov wrote:

On 2017-08-02 18:23, Sylvain Pointeau wrote:
...


CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
'other info')



BTW, your request is somewhat related with the brand new union-vtab SQLite
extension [1] (for optimized union view of identical tables) - if we have
the basic PostreSQL nextval in SQLite, the following pattern would become
possible:


I find the union vtab thing mostly not useful because it requires
constraining the tables to be union'ed to have distinct ranges of
rowids.  This is of too narrow utility.


Indeed - My assertion was simply wrong because of the ranges 
requirement. Query in the VTable DDL also was totally not what the 
implementation expects - sorry for the noise :-(.




PostgreSQL-style sequence support would be much more general.

If the union vtab thing is aiming to make it easier to implement
something like table inheritance, I'll warn you right off that
PostgreSQL's INHERIT is utterly useless -- do not copy it.

I've implemented "inheritance" with triggers to map DMLs on "derived"
tables onto "base" tables.  That works and is much more general.  If you
need a rowid, however, the triggers have to do more work, first acquring
the rowid from the base table, then setting it on the derived table
rows, and this can get tricky.


Yes. I meant exactly that usecase - where many logically "subclass" 
tables share common "address" (rowid) space, so that, any object (or the 
rest of the DB) could "reference" objects in the whole hierarchy (like 
unified value references in script languages data-models).


Of course, even without sequence build-in, both your suggestions: main 
"object" table with triggers on every subclass table or simple UDF 
next_serial are enough to build such representation.


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

Re: [sqlite] Problem with the new pointer-passing interface

[Nico Williams]

On Thu, Aug 03, 2017 at 12:33:05PM +0300, Paul wrote:
> To me it seems like that string is very tightly coupled with the
> actual pointer being bound.  I think it's a good idea, in case you
> cannot make it a literal or static, to keep it with an object whose
> pointer you bind.

Rhetorical: Why not use the pointer value itself then instead of the
contents of the string?  After all, the string should just be a .text
section constant string...

Now, suppose that you're building FFI bindings for SQLite3 for some
programming language, e.g., Python.  So you have a string from the
run-time for this language, but you can't ensure that it will have the
necessary lifetime.  So now you have to make a copy.  It's a bit of a
pain.  SQLite3 could have made a copy itself.

On the flip side, it's possible that you have to do some conversions
anyways because SQLite3 deals with C strings and the target language
run-time deals with counted-codeunit strings or whatever.

So even if SQLite3 made a copy, the FFI bindings might have to make
their own copy anyways.  Thus: who cares :)

Still, for many cases it will be easier to write code to this API if
SQLite3 makes its own copy.

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Richard,

> Can you please provide more details on how having a dynamic string for
> the pointer type would be helpful?  What is it that you are trying to
> do that string constant will not work?  Please be as specific as
> possible, so that I might better understand your problem.

I maintain a C++ wrapper library for SQLite especially for developers of 
wxWidgets based applications. This wrapper library offers access to almost all 
SQLite features (that make sense for an end-user application) through the use 
of C++ classes. That is, the wrapper classes encapsulate all calls to the 
SQlite C API.

With the release of SQLite 3.20.0 the new pointer-passing interface was 
introduced, and I found it quite useful to support extensions like carray. 
Therefore, I implemented a pointer bind method for the prepared SQL statement 
classs. This method makes internally a call to function sqlite3_bind_pointer. 
The signature and implementation of the method looks like this:

void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const wxString& 
pointerType, void(*DeletePointer)(void*))
{
  CheckStmt();
  const char* localPointerType = m_stmt->MakePointerTypeCopy(pointerType);
  int rc = sqlite3_bind_pointer(m_stmt->m_stmt, paramIndex, pointer, 
localPointerType, DeletePointer);
}

The member variable m_stmt is a reference counted reference object to a 
prepared SQL statement (sqlite3_stmt). This makes it possible to pass around 
the SQL statement object and to clean up the SQLite data structures when the 
last reference to the statement is deleted. This reference object now includes 
a dynamic array holding pointer type string duplicates until the reference 
object itself goes out of scope.

However, in my first implementation I converted the pointer type string 
parameter (wxString object) to a local char* variable. Since this local 
variable was destroyed after leaving the method, the select on the carray table 
failed, since the pointer type string was void.

Now, I create a copy of the pointer type string in a data structure that is 
kept alive until the SQL statement object is deleted. The carray extension now 
works flawlessly in the context of my wrapper.

For a C++ wrapper you could argue that using the SQLite API directly is 
feasible. However, for SQLite wrappers for other languages like Python or Lua, 
this might not work out.

Regards,

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

Re: [sqlite] sequencer

[Nico Williams]

On Thu, Aug 03, 2017 at 12:25:00PM +0200, Sylvain Pointeau wrote:
> On Thu, 3 Aug 2017 at 08:04, Hick Gunter  wrote:
> > A sequence is very easily implemented as a virtual table that keeps the
> > current values in a separate table my_sequences (name text primary key,
> > initial integer, current integer, increment integer).
> >
> > (...) Or whatever else tickles your fancy. Just think table <=> class,
> > virtual field <=> method
> 
> What do you all recommend? UDF or Virtual tables?

I recommend a UDF because that's how it works in PG, so your code will
be more portable that way.  That said, the two approaches are basically
equivalent.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Richard,

> On 8/3/17, Ulrich Telle  wrote:
> >
> > The description of the new pointer-passing interface gives the impression
> > that restricting the pointer type parameter to static strings or literals
> > prevents misuse of the new feature. And that is definitely not the case. It
> > might be a hurdle for unsophisticated developers, but not for the
> > experienced ones.
> 
> The documentation has now been adjusted to try to make it clear that
> the static string requirement simply makes misuse of the interface
> more difficult, not impossible.

Thanks. Certainly a valid point to not impose a runtime penalty on applications 
not using the pointer interface.

As said I solved the issue for my SQLite wrapper. The solution only affects the 
calls to the pointer interface (like binding a pointer to a SQL parameter).

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 9:16 AM, "sqlite-users on behalf of Ulrich Telle"wrote:
> > The description of the new pointer-passing interface gives the
> > impression that restricting the pointer type parameter to static
> > strings or literals prevents misuse of the new feature.
> 
> The term I used was “deters”.

I know, but the SQLite documentation uses "prevents":

" ... Thus, the requirement that pointer types be static strings helps to 
prevent misuse of the pointer-passing interfaces."

> > And that is definitely not the case. It might be a hurdle for
> > unsophisticated developers, but not for the experienced ones.
> 
> What experienced, non-malicious developers would read the rationale
> and then go ahead and implement an extension that opened up the
> possibility of a pointer-based exploit from SQL by allowing types
> generated from SQL strings? 

No one, hopefully. At least I'm not going to do that. I just wanted to point 
out that there are legitimate scenarios (like the wrapper I maintain) in which 
the restriction makes it harder to implement support for useful extensions like 
carray.

I learned that the restriction was imposed on purpose, and since I believe - as 
naive as I am - to be a rather experienced developer, I was able to overcome it 
for my use case. That is, I can now live with the restriction.

Regards,

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

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

[Simon Slavin]

On 3 Aug 2017, at 3:33pm, petern  wrote:

> The deeply nested string replace pattern comes up frequently.  A
> suggestion.  Why not a built in aggregate replace function?

Two more suggestions:

Create a function which does exactly what you want.  Call it 
removePunctuation(p1).  Feed it a string and it removes everything which isn’t 
alphanumeric.  Or however it is you define punctuation.

Create a function called removeTheseCharacters(p1, p2).  Feed it p1 which is a 
string, and p2 which is another string make up of any number of characters.  
function returns a value which is p1 with all the characters in p2 removed.

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

Re: [sqlite] Problem with the new pointer-passing interface

[Richard Hipp]

On 8/3/17, Ulrich Telle  wrote:
>
> Right. And therefore I think that the restriction to static strings or
> literals for the pointer type parameter just makes life a bit harder for
> honest developers of wrappers or the like, but prevents nothing.
>

Can you please provide more details on how having a dynamic string for
the pointer type would be helpful?  What is it that you are trying to
do that string constant will not work?  Please be as specific as
possible, so that I might better understand your 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

Re: [sqlite] Problem with the new pointer-passing interface

[Richard Hipp]

On 8/3/17, Ulrich Telle  wrote:
>
> The description of the new pointer-passing interface gives the impression
> that restricting the pointer type parameter to static strings or literals
> prevents misuse of the new feature. And that is definitely not the case. It
> might be a hurdle for unsophisticated developers, but not for the
> experienced ones.
>

The documentation has now been adjusted to try to make it clear that
the static string requirement simply makes misuse of the interface
more difficult, not impossible.

-- 
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

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

[petern]

The deeply nested string replace pattern comes up frequently.  A
suggestion.  Why not a built in aggregate replace function?

As such, the example here would be more readable and not limited by stack
depth as the expression:

SELECT replace(Transcript,column1,column2) FROM (VALUES
(',',''),(';',''),...)




On Thu, Aug 3, 2017 at 3:38 AM, Richard Hipp  wrote:

> I am unable to reproduce the problem.  Do you have any additional
> hints on how to make this fail?
>
> The depth of the stack in the push-down automaton used by the parser
> is deliberately limited to 100 levels.  This is a feature, not a bug.
> See https://www.sqlite.org/compile.html#yystackdepth for additional
> information.
>
> On 8/3/17, Brian Clifford  wrote:
> > Hi,
> >
> > I've subscribed to the sqllite mailing list but am still awaiting
> > authorization.
> >
> > I'd like to report a bug I found with sqlite where I'm creating a
> function
> > index which consists of approx 36 nested calls to the replace() function
> > (to remove punctuation)
> >
> > I get a parser stackoverflow exception after about 28 nested calls.
> >
> > Below is the pseudo-code I use for creating my table and the function
> index
> > ---
> >
> > string _ReplacePunctuationFunctionPart =
> > "replace(replace(replace(replace(replace(replace(
> replace(replace(replace(replace(replace(replace(replace(replace(replace(
> replace(replace(replace(replace(replace(replace(replace(replace(replace(
> replace(replace(replace(Transcript,',',''),';',''),':'
> ,''),'[',''),']',''),'{',''),'}',''),'(',''),')',''),'\','')
> ,'/',''),'"',''),'_',''),'+',''),'=',''),'£',''),'$',''),'€'
> ,''),'%',''),'^',''),'&',''),'*',''),'@',''),'#',''),'”',''),'“',''),'
> > ','')"
> >
> >
> >  "CREATE TABLE \"Recordings\" (\"Id\" INTEGER PRIMARY KEY AUTOINCREMENT
> NOT
> > NULL, \"Transcript\" TEXT NOT NULL COLLATE NOCASE, \"Mp3FileName\" TEXT
> NOT
> > NULL UNIQUE, \"Label\" TEXT);";
> >  "CREATE UNIQUE INDEX RecordingsMp3Index ON Recordings(Mp3FileName);";
> >  "CREATE INDEX RecordingsTextIndex ON Recordings(Transcript);";
> >  string.Format("CREATE INDEX RecordingsNoPunctuationIdx ON
> > Recordings({0});", _ReplacePunctuationFunctionPart);
> >
> >   SELECT 
> >
> >  string.Format("SELECT Id, {0} as 'TranscriptNoPunctuation' FROM
> > Recordings;", _ReplacePunctuationFunctionPart);
> >
> > --
> >
> > regards,
> >
> > *Brian Clifford*
> > Software Developer
> >
> > --
> >
> >
> > 
> >
> > www.thinksmartbox.com
> >
> > Facebook   Twitter
> >   LinkedIn
> > 
> YouTube
> > 
> >
> > Smartbox Assistive Technology, Ysobel House, Enigma Commercial Centre,
> > Sandys
> > Road, Malvern, WR14 1JJ
> >
> > Tel: +44 (0) 1684 578868
> > ___
> > 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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 9:16 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> The description of the new pointer-passing interface gives the impression 
> that restricting the pointer type parameter to static strings or literals 
> prevents misuse of the new feature.

The term I used was “deters”.

> And that is definitely not the case. It might be a hurdle for unsophisticated 
> developers, but not for the experienced ones.

What experienced, non-malicious developers would read the rationale and then go 
ahead and implement an extension that opened up the possibility of a 
pointer-based exploit from SQL by allowing types generated from SQL strings? 

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

Re: [sqlite] Cut off paragraph in pointer documentation

[Richard Hipp]

On 8/3/17, J. King  wrote:
> The fifth paragraph of
>  ends after two
> words, "Note that".
>

Thank you.  Should be fixed now.

-- 
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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 8:56 AM, "sqlite-users on behalf of Ulrich Telle" wrote:
> > Nevertheless, I think the pointer type string restriction gives a false 
> > sense of security.
> 
> You described creating a malicious extension that would give you the ability 
> to probe pointers by forging strings.

The description of the new pointer-passing interface gives the impression that 
restricting the pointer type parameter to static strings or literals prevents 
misuse of the new feature. And that is definitely not the case. It might be a 
hurdle for unsophisticated developers, but not for the experienced ones.

> From a security point of view, once you have the ability to create
> and deploy a malicious extension into a target host, you’ve already
> got full local code execution access to that host anyway.

Right. And therefore I think that the restriction to static strings or literals 
for the pointer type parameter just makes life a bit harder for honest 
developers of wrappers or the like, but prevents nothing.

Without UDFs I think it is impossible to get access to pointers in pure SQL, so 
if someone wants to do malicious things he has to convince a user to load a 
malicious extension.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 8:56 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> Nevertheless, I think the pointer type string restriction gives a false sense 
> of security.

You described creating a malicious extension that would give you the ability to 
probe pointers by forging strings.

From a security point of view, once you have the ability to create and deploy a 
malicious extension into a target host, you’ve already got full local code 
execution access to that host anyway.


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

[sqlite] Cut off paragraph in pointer documentation

[J. King]

The fifth paragraph of 
 ends after two 
words, "Note that".


--
J. King

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 8:27 AM, "sqlite-users on behalf of Ulrich Telle" wrote:
> > I really don't think that the latter is true. To overcome the restriction
> > is extremly simple, if you don't care about memory leaks. Just do [...]
> 
> If you’re creating a malicious extension, sure. But if you’re creating
> an exploit this seems like an odd way to go about it. It’d be simpler
> to implement something like “SELECT root_shell_on_port(1337);”

I have no intention at all to create malicious extensions. I just develop and 
maintain a SQLite wrapper and stumbled across this restriction with static 
pointer type strings, when I started to add support for the new pointer-passing 
interface and tested it with the carray extension.

In the meantime I already managed to adjust my wrapper to get along with the 
restriction.

Nevertheless, I think the pointer type string restriction gives a false sense 
of security.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Richard,

> As the desire for dynamic pointer type strings seems to be a recurring
> theme, I have added lots of extra documentation that attempts to
> explain why the pointer-passing interfaces deliberately use a constant
> static string for the pointer type:
> 
>https://sqlite.org/bindptr.html#ptrtyp

As I already wrote in my response to Gwendal, I admittedly haven't read that 
page carefully enough. Sorry.

Nevertheless, as I explained in the above mentioned post this restriction 
doesn't prevent a bad guy from developing a SQL UDF that can construct 
arbitrary pointers from SQL. That is, IMHO the restriction gives a false sense 
of security. Exposing pointers in SQL is potentially dangerous, although there 
are certainly useful applications.

BTW, for my SQLite wrapper I already implemented the required housekeeping 
(which even avoids allocating duplicates of already registered pointer types) 
in less than 40 lines of code in the meantime.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Gwendal Roué]

> Le 3 août 2017 à 15:27, Ulrich Telle  a écrit :
> 
> Thanks for the pointer. I have to admit that I referred to 
> http://sqlite.org/c3ref/bind_blob.html 
> .

No offense :-) The SQLite documentation has organically grown, and information 
is often scattered across several pages. It takes time and experience... even 
for brand new features.

Now I lack the expertise to answer your concerns, but am reading this 
conversation with interest.

Gwendal

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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 8:27 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> I really don't think that the latter is true. To overcome the restriction is 
> extremly simple, if you don't care about memory leaks. Just do [...]

If you’re creating a malicious extension, sure. But if you’re creating an 
exploit this seems like an odd way to go about it. It’d be simpler to implement 
something like “SELECT root_shell_on_port(1337);”

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Gwendal,

> > Le 3 août 2017 à 14:27, Peter Da Silva  a 
> > écrit :
> > 
> > On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" 
> >  > ulrich.te...@gmx.de> wrote:
> >> IMHO it would be better if the function sqlite3_bind_pointer would make a 
> >> copy of the type string and would thus be independent of the life span of 
> >> the type parameter.
> > 
> > I believe that this was a deliberate choice to deter using temporary 
> > strings for type names.
> 
> The reference (https://www.sqlite.org/bindptr.html) says:

Thanks for the pointer. I have to admit that I referred to 
http://sqlite.org/c3ref/bind_blob.html.

> > Because pointer types must be static strings, and because string
> > values in SQLite are dynamic strings, that means that SQL values
> > cannot be used as a pointer type. This prevents misguided
> > developers from creating a new SQL function that can manufacture
> > pointer values directly from SQL. Such a function, if possible to
> > write, would undermine the security of the pointer-passing APIs.
> > Thus, the requirement that pointer types be static strings helps
> > to prevent misuse of the pointer-passing interfaces.

I really don't think that the latter is true. To overcome the restriction is 
extremly simple, if you don't care about memory leaks. Just do

/* Assume that char* ptrType holds the pointer type string */
/* Create a copy of the pointer type string on the heap
char* ptrTypeCopy = strdup(ptrType)
sqlite3_bind_pointer(pStmt, argIndex, ptr, ptrTypeCopy, NULL);

The function sqlite3_bind_pointer has no means to detect whether the passed 
pointer type ptrTypeCopy is indeed a static variable or a string literal. So at 
the expense of a memory leak it is a no-brainer to overcome the restriction. 
That is, developing SQL UDFs that are able to construct pointers from SQL data 
is not prevented by this restriction.

Conclusion: Beware of loadable extensions that you haven't written (or at least 
compiled) yourself.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Richard Hipp]

As the desire for dynamic pointer type strings seems to be a recurring
theme, I have added lots of extra documentation that attempts to
explain why the pointer-passing interfaces deliberately use a constant
static string for the pointer type:

   https://sqlite.org/bindptr.html#ptrtyp

-- 
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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" wrote:
> > IMHO it would be better if the function sqlite3_bind_pointer would make a 
> > copy of the type string
> > and would thus be independent of the life span of the type parameter.
> 
> I believe that this was a deliberate choice to deter using temporary strings 
> for type names.

Well, the comment regarding the type parameter ("The T parameter should be a 
static string, preferably a string literal.") just tells that the type string 
"should" be static. It doesn't warn that it doesn't work if the life time of 
the type string is shorter than that of the statement to which the pointer is 
bound. At least the description should be enhanced.

If one uses the SQLite3 API directly, this is most probably not a big deal, 
since most likely string literals will be used to specify the pointer type. But 
as soon as one tries to implement a SQLite wrapper (may it be for C++ or other 
languages like Python etc), this restriction makes things cumbersome. String 
parameters may not be guaranteed to live as long as needed, and it might be 
necessary to convert them to const char* before passing them on to SQLite. Then 
you have to implement some sort of housekeeping (or have to live with memory 
leaks, if you allocate a copy yourself and never free it). Since SQLite already 
does housekeeping regarding the pointer itself, I assume that it wouldn't be 
too difficult to extend this to make a copy of the type string and free it, 
when the pointer value goes out of scope and its destructor is invoked.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Gwendal Roué]

> Le 3 août 2017 à 14:27, Peter Da Silva  a 
> écrit :
> 
> On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" 
>  ulrich.te...@gmx.de> wrote:
>> IMHO it would be better if the function sqlite3_bind_pointer would make a 
>> copy of the type string and would thus be independent of the life span of 
>> the type parameter.
> 
> I believe that this was a deliberate choice to deter using temporary strings 
> for type names.

The reference (https://www.sqlite.org/bindptr.html) says:

> Because pointer types must be static strings, and because string values in 
> SQLite are dynamic strings, that means that SQL values cannot be used as a 
> pointer type. This prevents misguided developers from creating a new SQL 
> function that can manufacture pointer values directly from SQL. Such a 
> function, if possible to write, would undermine the security of the 
> pointer-passing APIs. Thus, the requirement that pointer types be static 
> strings helps to prevent misuse of the pointer-passing interfaces.


Gwendal Roué

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

Re: [sqlite] Problem with the new pointer-passing interface

[Peter Da Silva]

On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" 
 
wrote:
> IMHO it would be better if the function sqlite3_bind_pointer would make a 
> copy of the type string and would thus be independent of the life span of the 
> type parameter.

I believe that this was a deliberate choice to deter using temporary strings 
for type names.
 

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

Re: [sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

[Richard Hipp]

I am unable to reproduce the problem.  Do you have any additional
hints on how to make this fail?

The depth of the stack in the push-down automaton used by the parser
is deliberately limited to 100 levels.  This is a feature, not a bug.
See https://www.sqlite.org/compile.html#yystackdepth for additional
information.

On 8/3/17, Brian Clifford  wrote:
> Hi,
>
> I've subscribed to the sqllite mailing list but am still awaiting
> authorization.
>
> I'd like to report a bug I found with sqlite where I'm creating a function
> index which consists of approx 36 nested calls to the replace() function
> (to remove punctuation)
>
> I get a parser stackoverflow exception after about 28 nested calls.
>
> Below is the pseudo-code I use for creating my table and the function index
> ---
>
> string _ReplacePunctuationFunctionPart =
> "replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Transcript,',',''),';',''),':',''),'[',''),']',''),'{',''),'}',''),'(',''),')',''),'\',''),'/',''),'"',''),'_',''),'+',''),'=',''),'£',''),'$',''),'€',''),'%',''),'^',''),'&',''),'*',''),'@',''),'#',''),'”',''),'“',''),'
> ','')"
>
>
>  "CREATE TABLE \"Recordings\" (\"Id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT
> NULL, \"Transcript\" TEXT NOT NULL COLLATE NOCASE, \"Mp3FileName\" TEXT NOT
> NULL UNIQUE, \"Label\" TEXT);";
>  "CREATE UNIQUE INDEX RecordingsMp3Index ON Recordings(Mp3FileName);";
>  "CREATE INDEX RecordingsTextIndex ON Recordings(Transcript);";
>  string.Format("CREATE INDEX RecordingsNoPunctuationIdx ON
> Recordings({0});", _ReplacePunctuationFunctionPart);
>
>   SELECT 
>
>  string.Format("SELECT Id, {0} as 'TranscriptNoPunctuation' FROM
> Recordings;", _ReplacePunctuationFunctionPart);
>
> --
>
> regards,
>
> *Brian Clifford*
> Software Developer
>
> --
>
>
> 
>
> www.thinksmartbox.com
>
> Facebook   Twitter
>   LinkedIn
>   YouTube
> 
>
> Smartbox Assistive Technology, Ysobel House, Enigma Commercial Centre,
> Sandys
> Road, Malvern, WR14 1JJ
>
> Tel: +44 (0) 1684 578868
> ___
> 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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi Paul,

> To me it seems like that string is very tightly coupled with the actual 
> pointer being bound.

No, not really. The type string is tightly coupled with the extension module 
which uses the pointer. The type string allows the extension module to check 
whether the pointer is really meant to be handled by the module.

For the carray module the pointer just pints to a C++ array of integers, 
doubles or strings. So there is no simple way to attach the type string to this 
data structure.

> I think it's a good idea, in case you cannot make it a literal or static, to 
> keep it with
> an object whose pointer you bind.

The problem is that my component just provides a thin wrapper for SQLite for 
applications based on the wxWidgets library. In most cases the wrapper just 
passes given parameters on to the underlying SQLite functions. That is, the 
wrapper itself doesn't know anything about internals of extension modules and 
pointer objects they might be able to handle.

I could introduce a wrapper object for pointers that additionally contains a 
type string, but then I would have to keep track of wrapper objects, since only 
the raw pointer is passed on to the extension module. In fact, this would not 
solve the problem.

IMHO it should be rather simple to adjust the SQLite functions of the new 
pointer-passing interface to make a copy of the type string on binding a 
pointer and releasing the copy when the pointer value goes out of scope.

Regards,  

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

Re: [sqlite] sequencer

[Sylvain Pointeau]

On Thu, 3 Aug 2017 at 08:04, Hick Gunter  wrote:

> A sequence is very easily implemented as a virtual table that keeps the
> current values in a separate table my_sequences (name text primary key,
> initial integer, current integer, increment integer).
>
> (...) Or whatever else tickles your fancy. Just think table <=> class,
> virtual field <=> method
>


What do you all recommend? UDF or Virtual tables?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: Nested function call to replace() throws parser stack overflow exception

[Brian Clifford]

Hi,

I've subscribed to the sqllite mailing list but am still awaiting
authorization.

I'd like to report a bug I found with sqlite where I'm creating a function
index which consists of approx 36 nested calls to the replace() function
(to remove punctuation)

I get a parser stackoverflow exception after about 28 nested calls.

Below is the pseudo-code I use for creating my table and the function index
---

string _ReplacePunctuationFunctionPart =
"replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Transcript,',',''),';',''),':',''),'[',''),']',''),'{',''),'}',''),'(',''),')',''),'\',''),'/',''),'"',''),'_',''),'+',''),'=',''),'£',''),'$',''),'€',''),'%',''),'^',''),'&',''),'*',''),'@',''),'#',''),'”',''),'“',''),'
','')"


 "CREATE TABLE \"Recordings\" (\"Id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL, \"Transcript\" TEXT NOT NULL COLLATE NOCASE, \"Mp3FileName\" TEXT NOT
NULL UNIQUE, \"Label\" TEXT);";
 "CREATE UNIQUE INDEX RecordingsMp3Index ON Recordings(Mp3FileName);";
 "CREATE INDEX RecordingsTextIndex ON Recordings(Transcript);";
 string.Format("CREATE INDEX RecordingsNoPunctuationIdx ON
Recordings({0});", _ReplacePunctuationFunctionPart);

  SELECT 

 string.Format("SELECT Id, {0} as 'TranscriptNoPunctuation' FROM
Recordings;", _ReplacePunctuationFunctionPart);

--

regards,

*Brian Clifford*
Software Developer

-- 




www.thinksmartbox.com

Facebook   Twitter 
  LinkedIn 
  YouTube 


Smartbox Assistive Technology, Ysobel House, Enigma Commercial Centre, Sandys 
Road, Malvern, WR14 1JJ

Tel: +44 (0) 1684 578868
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi Clemens,

> Ulrich Telle wrote:
> > In the comment of the sqlite3_bind_pointer() routine I found this note:
> >
> > The T parameter should be a static string, preferably a string literal.
> >
> > In my case this is quite cumbersome, since in my wrapper I have to extract
> > the value of parameter T from a temporary string object.
> 
>  says:
> | The "pointer type" string which is the last parameter to each of the
> | pointer-passing interfaces should be a distinct, application-specific
> | string literal that appears directly in the API call. The pointer type
> | should not be a parameter passed in from a higher-level function.
> 
> If you think that you really need to get this string from a higher-level
> function, offer a mechanism to 'register' this string, i.e., make
> a permanent copy that can be referenced by later calls.

Yes, I know I will have to do that to make things work, but it is really 
cumbersome, since I need a global object holding these 'registered' strings and 
have to take care that memory is cleaned up at some point in time. Doing it 
within SQLite would be much easier, since SQLite has to call the destructor 
function for the pointer (if it was given). So SQLite 'knows' when the pointer 
value goes out of scope ... and freeing the type string when checking whether 
the destructor function for the pointer has to be called or not, would probably 
be just one statement more in the SQLite source.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Paul]

To me it seems like that string is very tightly coupled with the actual pointer 
being bound.
I think it's a good idea, in case you cannot make it a literal or static, to 
keep it with
an object whose pointer you bind.


  Hi,  
 
 I came across a problem with the new pointer-passing interface, when I 
tried 
 to incorporate the carray extension into my wxSQLite3 wrapper for SQLite3. 
 
 
 In the comment of the sqlite3_bind_pointer() routine I found this note:  
 
 The T parameter should be a static string, preferably a string literal.  
 
 This comment is a bit misleading, since it doesn't become clear that the 
 string pointer for T must be valid as long as the associated statement is 
 active.  
 
 In my case this is quite cumbersome, since in my wrapper I have to extract 
 the value of parameter T from a temporary string object. That is, shortly 
after 
 having called sqlite3_bind_pointer the temporary string goes out of scope. 
 The effect is that later on the function sqlite3_value_pointer usually 
can't see 
 the correct type value anymore. Therefore sqlite3_value_pointer returns a 
 NULL pointer ... and the carray table is empty.  
 
 IMHO it would be better if the function sqlite3_bind_pointer would make a 
 copy of the type string and would thus be independent of the life span of 
the 
 type parameter.  
 
 Regards,  
 
 Ulrich  
 -- 
 E-Mail privat:  ulrich.te...@gmx.de
 World Wide Web: http://www.telle-online.de
 
 
 ___
 sqlite-users mailing list
 
sqlite-users@mailinglists.sqlite.orghttp://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] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi,

I came across a problem with the new pointer-passing interface, when I 
tried to incorporate the carray extension into my wxSQLite3 wrapper for 
SQLite3.


In the comment of the sqlite3_bind_pointer() routine I found this note:

The T parameter should be a static string, preferably a string literal.

This comment is a bit misleading, since it doesn't become clear that the 
string pointer for T must be valid as long as the associated statement 
is active.


In my case this is quite cumbersome, since in my wrapper I have to 
extract the value of parameter T from a temporary string object. That 
is, shortly after having called sqlite3_bind_pointer the temporary 
string goes out of scope. The effect is that later on the function 
sqlite3_value_pointer usually can't see the correct type value anymore. 
Therefore sqlite3_value_pointer returns a NULL pointer ... and the 
carray table is empty.


IMHO it would be better if the function sqlite3_bind_pointer would make 
a copy of the type string and would thus be independent of the life span 
of the type parameter.


Regards,

Ulrich

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

Re: [sqlite] Problem with the new pointer-passing interface

[Clemens Ladisch]

Ulrich Telle wrote:
> In the comment of the sqlite3_bind_pointer() routine I found this note:
>
> The T parameter should be a static string, preferably a string literal.
>
> In my case this is quite cumbersome, since in my wrapper I have to extract
> the value of parameter T from a temporary string object.

 says:
| The "pointer type" string which is the last parameter to each of the
| pointer-passing interfaces should be a distinct, application-specific
| string literal that appears directly in the API call. The pointer type
| should not be a parameter passed in from a higher-level function.

If you think that you really need to get this string from a higher-level
function, offer a mechanism to 'register' this string, i.e., make
a permanent copy that can be referenced by later calls.


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

[sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi,  

I came across a problem with the new pointer-passing interface, when I tried 
to incorporate the carray extension into my wxSQLite3 wrapper for SQLite3.  

In the comment of the sqlite3_bind_pointer() routine I found this note:  

The T parameter should be a static string, preferably a string literal.  

This comment is a bit misleading, since it doesn't become clear that the 
string pointer for T must be valid as long as the associated statement is 
active.  

In my case this is quite cumbersome, since in my wrapper I have to extract 
the value of parameter T from a temporary string object. That is, shortly after 
having called sqlite3_bind_pointer the temporary string goes out of scope. 
The effect is that later on the function sqlite3_value_pointer usually can't 
see 
the correct type value anymore. Therefore sqlite3_value_pointer returns a 
NULL pointer ... and the carray table is empty.  

IMHO it would be better if the function sqlite3_bind_pointer would make a 
copy of the type string and would thus be independent of the life span of the 
type parameter.  

Regards,  

Ulrich  
-- 
E-Mail privat:  ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de


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

Re: [sqlite] sqlite3_prepare16_v3 and prepFlags

[Bart Smissaert]

Not quite clear as point 9 in the release notes of 3.20.0 seems to me to
suggest that this only applied if you are
using FTS3, FTS5 or the R-Tree extension.

RBS

On Wed, Aug 2, 2017 at 11:09 PM, Nico Williams 
wrote:

> On Wed, Aug 02, 2017 at 11:01:07PM +0100, Bart Smissaert wrote:
> > Using 3.20.0 now on Windows and wonder when exactly I should use the
> > SQLITE_PREPARE_PERSISTENT
> >  sqlitepreparepersistent>
> > flag
> > instead of a zero. I have tried both options
> > with a plain select statement producing some 10 rows. Both worked and
> > SQLITE_PREPARE_PERSISTENT
> >  sqlitepreparepersistent>
> > seemed
> > a bit faster. Are there any clear guidelines
> > when to use either option?
> > Note I am not using FTS3, FTS5 or the R-Tree extension.
>
> I thought the docs were clear: if you'll be executing the statement
> repeatedly, then use SQLITE_PREPARE_PERSISTENT.
>
> Nico
> --
> ___
> 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] sequencer

[Hick Gunter]

A sequence is very easily implemented as a virtual table that keeps the current 
values in a separate table my_sequences (name text primary key, initial 
integer, current integer, increment integer).

CREATE VIRTUAL TABLE seq_1 USING sequence ([[,]]); -- 
defaults 1, 1

The xCreate/xConnect function just needs to store its name and set a DDL like 
CREATE TABLE X (current_value integer, next_value integer);

The xColumn function just needs to run one of two statements:

For field next_value run the following statement and fall through to 
current_value:

UPDATE my_sequences SET current = current + increment WHERE 
name='';

For field current_value:

 SELECT current FROM my_sequences WHERE name='';

You could also implement a virtual field rewind

UPDATE my_sequences SET current = initial WHERE name='sequence_name';

Or whatever else tickles your fancy. Just think table <=> class, virtual field 
<=> method


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Sylvain Pointeau
Gesendet: Mittwoch, 02. August 2017 21:07
An: SQLite mailing list 
Betreff: Re: [sqlite] sequencer

ok thank you for the confirmation, I will try implementing it in a dll using UD 
functions and put it on github.

Le mer. 2 août 2017 à 20:56, Richard Hipp  a écrit :

> On 8/2/17, Sylvain Pointeau  wrote:
> >
> > is it really possible to make an update into that nextval function?
> > I
> don't
> > think so since only one statement can be run at the same time if
> > sqlite
> is
> > in serialized mode.
>
> "serialized" means that multiple threads cannot be making updates at
> the same time.  In this case, the update would be happening in the
> same thread.
>
> --
> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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