Re: [sqlite] LSM1 extension

[Charles Leifer]

Right -- my question is still unanswered as to why the code was merged in.
I have tremendous respect for this project and everyone involved in it, so
please understand I'm not trying to call into question your decisions --
I'd just like clarification as to why this was included now?

On Fri, Aug 4, 2017 at 10:48 PM, Richard Hipp  wrote:

> On 8/4/17, Charles Leifer  wrote:
> >
> > My excitement quickly turned to disappointment as I realized that the
> > extension is unusable for all practical purposes:
> >
>
> Yes, it needs work.  That is why it is unannounced.
>
> --
> 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] LSM1 extension

[Richard Hipp]

On 8/4/17, Charles Leifer  wrote:
>
> My excitement quickly turned to disappointment as I realized that the
> extension is unusable for all practical purposes:
>

Yes, it needs work.  That is why it is unannounced.

-- 
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] LSM1 extension

[Charles Leifer]

Hi,

I was pleased to see that 3.20 contained the source for the LSM1 virtual
table, even if not included in the official announcements.

My excitement quickly turned to disappointment as I realized that the
extension is unusable for all practical purposes:

- Single-key equality lookups are broken (unless you hand-craft the
encoding of the key according to the encodeKey rules).
- Everything else is a full-database scan.

I suppose I can understand releasing the code without all the xBestIndex
optimizations, but I was really quite surprised to see single-key lookups
weren't working as I'd have expected.

I don't want to be presumptuous, but I'm having a hard time understanding
why this code is included alongside extensions like FTS5 and RTREE. Because
it wasn't in the release notes are we to ignore it? That doesn't make sense
to me because, given the wonderful tools we have for things like version
control and all that, why not just hold off on merging it until it's
actually ready for release?

Looking forward to hearing your thoughts on the issue,

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

[sqlite] Fixed: Checkin d8637bad produces bad amalgamation

[Keith Medcalf]

Thanks Dan, that fixed it.  Also, the current head of trunk is just fine as 
well...


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

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy
>Sent: Friday, 4 August, 2017 02:26
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Checkin d8637bad produces bad amalgamation
>
>On 08/04/2017 03:50 AM, Keith Medcalf wrote:
>> Check-in d8637bad produces a bad amalgamation output.
>
>Thanks for reporting this. Should be fixed now.
>
>Dan.
>
>
>
>>
>> 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
>
>
>___
>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]

>
> You're welcome.  Thanks for posting this on github.  Make sure there's a
> license, preferably a nice and friendly one such as a BSD license, or
> else put it in the public domain like SQLite3 is -- but it's your code,
> so you do what you like with it.
>

I will put a BSD license and it will be on github this week end

very funny, look what I found:
http://docs.oracle.com/cd/E17076_05/html/bdb-sql/sequencesupport.html
___
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 10:09:04PM +0200, Sylvain Pointeau wrote:
> On Fri, Aug 4, 2017 at 10:01 PM, Nico Williams 
> wrote:
> > The main issue is that you can't tell when a transaction has begun or
> > ended, so you can't tell when curr_val() should raise an error.  You can
> > only tell that next_val() has been called on this connection, and that's
> > not the same semantics as H2's.
> 
> actually the temp table is better in case of rollback.

Right.

> however normally it should be executed under an autonomous transaction,
> a rollback should not affect the sequence.

Correct.

> > I would just not bother with curr_val() for now.
> 
> I need it, but the current implementation should be enough.

OK.

> => I would really really like if a real implementation of the sequence
> could be done in sqlite, behaving like oracle.
> (very fast, reliable etc)
> 
> Thanks for all your help!

You're welcome.  Thanks for posting this on github.  Make sure there's a
license, preferably a nice and friendly one such as a BSD license, or
else put it in the public domain like SQLite3 is -- but it's your code,
so you do what you like with it.

Cheers,

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

[petern]

Dominique.  strcmp alone won't care if zPType is code segment or data
segment.   As is, this release won't need the dynamic linker to perfectly
consolidate constants.  Much ado.  [You are also free to clone three very
tiny API functions substituting integer/etc PType if you're in the mood to
ship a custom build to your customers.]

SQLITE_API void *sqlite3_value_pointer(sqlite3_value *pVal, const char
*zPType){
  Mem *p = (Mem*)pVal;
  if( (p->flags&(MEM_TypeMask|MEM_Term|MEM_Subtype)) ==
 (MEM_Null|MEM_Term|MEM_Subtype)
   && zPType!=0
   && p->eSubtype=='p'
   && strcmp(p->u.zPType, zPType)==0
  ){
return (void*)p->z;
  }else{
return 0;
  }
}

On Fri, Aug 4, 2017 at 6:37 AM, Dominique Devienne 
wrote:

> On Fri, Aug 4, 2017 at 11:37 AM, Ulrich Telle  wrote:
>
> > Regarding the development of SQLite extensions (UDFs) and using the
> > pointer-binding interface to communicate between different extensions
>
> (AFAIK one of the reasons to introduce the new pointer-binding interface)
> > the advice to use string literals is certainly good.
> >
>
> I think on the contrary they are more meant to communicate across different
> UDFs of the *same* extension, and not across extensions.
>
> Typical use (IMHO) of the pointer-binding interface is to pass around a
> pointer to an extension-defined struct from one function to another,
> from an inner "parsing" function (e.g. taking JSON text and parsing it into
> a compact and efficient binary representation) whose result is
> used by another UDF of the same extension that is aware of the custom
> struct of the "parsed representation" of the JSON and can therefore
> do more efficient processing. In that context, pointer equality of string
> literals can be used and relied upon w/o even a strcmp(), and both
> UTFs are "tightly coupled". The "type-pointer" is simply a "poor man's type
> system" to guarantee the hard-cast in the receiving UDF is "safe".
>
> I'm not even sure you can guarantee uniqueness of string literals across
> dynamically loaded shared libraries, maybe the runtime linker
> merges equivalent read-only "static" text from different libs, but that
> seems very platform-dependent and not something to rely on.
>
> It's also probably unusual IMHO for different extensions to share headers
> to have a common understanding of a custom struct
> to share at runtime via SQLite pointer-binding in UDFs across these
> extensions. Not impossible, but seem rather unlikely to me.
>
> When you look at it from this lens, then enforcing string literals for the
> type-pointer makes sense. My $0.02, FWIW. --DD
>
> PS: Yes, like others I'd love it if DRH went further into this "custom
> type" route, even persistent ones longer term with a format change,
>   since pointer-binding with type-string-pointer is going in that
> direction, at least at runtime-only, and only for "temporaries" across
> UDFs,
>   but I'm afraid we're a long way from that, and I'm not holding my breadth
> :)
> ___
> 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]

On Fri, Aug 4, 2017 at 10:01 PM, Nico Williams 
wrote:

> On Fri, Aug 04, 2017 at 09:55:03PM +0200, Sylvain Pointeau wrote:
> > On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams 
> wrote:
> > > In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce()
> > > (WIN32).  But here, a global in combination with CREATE TEMP TABLE IF
> > > NOT EXISTS is probably good enough.
> >
> > I wonder if a memory structure would not be better?
>
> Doesn't matter.  You already have a solution.
>
> The main issue is that you can't tell when a transaction has begun or
> ended, so you can't tell when curr_val() should raise an error.  You can
> only tell that next_val() has been called on this connection, and that's
> not the same semantics as H2's.
>
>
actually the temp table is better in case of rollback.
however normally it should be executed under an autonomous transaction,
a rollback should not affect the sequence.


> I would just not bother with curr_val() for now.
>

I need it, but the current implementation should be enough.

=> I would really really like if a real implementation of the sequence
could be done in sqlite, behaving like oracle.
(very fast, reliable etc)

Thanks for all your help!
___
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 09:55:03PM +0200, Sylvain Pointeau wrote:
> On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams  wrote:
> > In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce()
> > (WIN32).  But here, a global in combination with CREATE TEMP TABLE IF
> > NOT EXISTS is probably good enough.
> 
> I wonder if a memory structure would not be better?

Doesn't matter.  You already have a solution.

The main issue is that you can't tell when a transaction has begun or
ended, so you can't tell when curr_val() should raise an error.  You can
only tell that next_val() has been called on this connection, and that's
not the same semantics as H2's.

I would just not bother with curr_val() for now.
___
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 Fri, Aug 4, 2017 at 9:16 PM, Nico Williams  wrote:

> On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote:
> > I programmed the currval using a temp table, but the performance dropped
> > slightly
> >
> > sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
> > i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s
> > eq1') from T;
> > Run Time: real 25.837 user 23.446950 sys 0.171601
> >
> > I create the temp table only once with the help of a global variable. I
> > believe it is safe (per session), am I right?
>
> In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce()
> (WIN32).  But here, a global in combination with CREATE TEMP TABLE IF
> NOT EXISTS is probably good enough.
>

I wonder if a memory structure would not be better?

kind of a fixed sized array where I would iterate to find the seq_name,
otherwise I insert it.
(or a real map but then c++ would be better in this case)

what do you think?
___
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 07:20:00PM +, Hick Gunter wrote:
> A "temp table" would only be visible in the session that creates it
> and would not live past the end of the session. Sequences should be
> persistent...

This is about the H2 curr_val() semantics -- that it only works if
you've already done a next_val() in the same tx.
___
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 "temp table" would only be visible in the session that creates it and would 
not live past the end of the session. Sequences should be persistent...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Nico Williams
Gesendet: Freitag, 04. August 2017 21:16
An: Sylvain Pointeau 
Cc: SQLite mailing list 
Betreff: Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote:
> I programmed the currval using a temp table, but the performance
> dropped slightly
>
> sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
> i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s
> eq1') from T;
> Run Time: real 25.837 user 23.446950 sys 0.171601
>
> I create the temp table only once with the help of a global variable.
> I believe it is safe (per session), am I right?

In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce() (WIN32). 
 But here, a global in combination with CREATE TEMP TABLE IF NOT EXISTS is 
probably good enough.
___
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

Re: [sqlite] sequencer

[Nico Williams]

On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote:
> I programmed the currval using a temp table, but the performance dropped
> slightly
> 
> sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
> i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s
> eq1') from T;
> Run Time: real 25.837 user 23.446950 sys 0.171601
> 
> I create the temp table only once with the help of a global variable. I
> believe it is safe (per session), am I right?

In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce()
(WIN32).  But here, a global in combination with CREATE TEMP TABLE IF
NOT EXISTS is probably good enough.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Sylvain Pointeau]

I programmed the currval using a temp table, but the performance dropped
slightly

sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s
eq1') from T;
Run Time: real 25.837 user 23.446950 sys 0.171601

I create the temp table only once with the help of a global variable. I
believe it is safe (per session), am I right?

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

int is_temp_table_created = 0;

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, " \
  " SEQ_INIT INTEGER NOT NULL, " \
  " SEQ_INC INTEGER NOT NULL CHECK (SEQ_INC<>0) " \
  " )", 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_init, seq_inc) values (?, ?, ?, ?)", -1, , 0);

  sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
  sqlite3_bind_int64(stmt, 2, seq_init_val-seq_inc_val);
  sqlite3_bind_int64(stmt, 3, seq_init_val);
  sqlite3_bind_int64(stmt, 4, 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;
  int update_row_count = 0;
  sqlite3_stmt *stmt;
  long nextval = 0;

  sqlite3 *db = sqlite3_context_db_handle(context);
  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) {
nextval = 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;
  }

  if( is_temp_table_created == 0 ) {

rc = sqlite3_exec(db, "CREATE TEMPORARY TABLE IF NOT EXISTS
TEMP_SP_SEQ_CURRVAL ( " \
" SEQ_NAME TEXT PRIMARY KEY, " \
" CURRVAL INTEGER " \
" )", 0, 0, 0);

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

is_temp_table_created = 1;
  }

  sqlite3_prepare_v2(db, "update TEMP_SP_SEQ_CURRVAL set currval = ? where
seq_name = ?", -1, , 0);

  sqlite3_bind_int64(stmt, 1, nextval);
  sqlite3_bind_text(stmt, 2, 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;
  }

  update_row_count = sqlite3_changes(db);

  if (update_row_count == 0) {
// update not done, value has to be inserted

sqlite3_prepare_v2(db, "insert into TEMP_SP_SEQ_CURRVAL (seq_name,
currval) values (?,?)", -1, , 0);

sqlite3_bind_text(stmt, 1, seq_name, -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt, 2, nextval);

rc = sqlite3_step(stmt);

sqlite3_finalize(stmt);

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

  sqlite3_result_int64( context, nextval );
}

void sp_seq_currval(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int rc = 0;
  sqlite3_stmt *stmt;
  long currval = 0;

  sqlite3 *db = sqlite3_context_db_handle(context);
  const unsigned char* seq_name = sqlite3_value_text(argv[0]);

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

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

  rc = sqlite3_step(stmt);

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

  sqlite3_finalize(stmt);

  if (rc != SQLITE_ROW) {
sqlite3_result_error(context, "currval is not yet defined in this
session for this sequence", -1);
return;
  }

  sqlite3_result_int64( context, currval );
}


int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
 SQLITE_EXTENSION_INIT2(pApi)
 sqlite3_create_function(db, 

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 07:05:06PM +, Peter Da Silva wrote:
> Step 2 seems rather expensive, even if you’re filtering out dead blocks in 
> the process.

It's no more expensive than WAL checkpointing is today.

You could always do what LMDB does to reuse free blocks in a DB and
avoid having to checkpoint-and-rename-into-place, but that greatly
complicates locking because readers have to lock the transaction that
they are reading at.

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 01:59:07PM -0500, Nico Williams wrote:
> The checkpoint process would look like this:
> 
>  - make a new file in the same directory
>  - copy the DB to the new file

The copy would basically be copying all the live data as a single
transaction on the new DB/WAL file.  At the end there should be at most
two transactions in the file: one for the schema, one for the data.

>  - rename the new file into place
>  - write the "closed, renamed" marker into the old file (which is still
>open)
> 
> This works on POSIX, and if you use the right CreateFileEx() options, it
> works on WIN32.

I'm referring here to readers that have the first file open...
continuing to be able to read from it as long as they retain the open
file handle, even after that file is deleted by the rename.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Peter Da Silva]

On 8/4/17, 1:59 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> The checkpoint process would look like this:
>   - make a new file in the same directory
>   - copy the DB to the new file
>   - rename the new file into place
>   - write the "closed, renamed" marker into the old file (which is still open)

Step 2 seems rather expensive, even if you’re filtering out dead blocks in the 
process.


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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 06:49:42PM +, Peter Da Silva wrote:
> On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" 
>  n...@cryptonector.com> wrote:
> > SQLite3's WAL is already log-structured.  The main DB file isn't.
> > So SQLite3 is a hybrid.  But it doesn't have to be a hybrid.
> 
> One issue I see with this is you’ll have to retain the old WALs as
> long as they have any live data, or the checkpoint operation will have
> to copy all the unmodified data in the log to the new WAL, or you’ll
> have to keep a non-log structure containing all the relatively static
> data that hasn’t been modified in the last “N” checkpoints.

The checkpoint process would look like this:

 - make a new file in the same directory
 - copy the DB to the new file
 - rename the new file into place
 - write the "closed, renamed" marker into the old file (which is still
   open)

This works on POSIX, and if you use the right CreateFileEx() options, it
works on WIN32.

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Peter Da Silva]

On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> SQLite3's WAL is already log-structured.  The main DB file isn't.  So SQLite3 
> is a hybrid.  But it doesn't have to be a hybrid.

One issue I see with this is you’ll have to retain the old WALs as long as they 
have any live data, or the checkpoint operation will have to copy all the 
unmodified data in the log to the new WAL, or you’ll have to keep a non-log 
structure containing all the relatively static data that hasn’t been modified 
in the last “N” checkpoints.

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 06:43:08PM +, Peter Da Silva wrote:
> On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" wrote:
> > [...]
> 
> A log-structured database, like a log-structured file system?

Yes.  Filesystems and databases are each other's dual.

SQLite3's WAL is already log-structured.  The main DB file isn't.  So
SQLite3 is a hybrid.  But it doesn't have to be a hybrid.

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Peter Da Silva]

On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> Imagine a mode where there is only a WAL, and to checkpoint is to write a new 
> WAL with only live contents and... rename(2) into place.  Such a mode would 
> a) be a 100% Copy-on-Write (CoW) mode, whereas currently WAL is only CoW 
> until a checkpoint operation comes along, b) have better read concurrency.  A 
> special marker could be used to denote "this WAL is closed and replaced with 
> a checkpointed one", that way readers only have to stat/re-open when they see 
> this.

A log-structured database, like a log-structured file system?

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 11:53:05AM -0500, Nico Williams wrote:
> WAL mode still ends up having no read concurrency when it's time to
> checkpoint the WAL.  The same would happen with this concept.  I don't
> think this works well.

Speaking of which... and I know I've mentioned this before and I risk
being a broken record...

Imagine a mode where there is only a WAL, and to checkpoint is to write
a new WAL with only live contents and... rename(2) into place.  Such a
mode would a) be a 100% Copy-on-Write (CoW) mode, whereas currently WAL
is only CoW until a checkpoint operation comes along, b) have better
read concurrency.  A special marker could be used to denote "this WAL is
closed and replaced with a checkpointed one", that way readers only have
to stat/re-open when they see this.

It probably wouldn't take much to write such a thing.

One nice thing about this is that block-level replication would be easy
and fast because all writes would be append-writes: just send missing
blocks until the file ID (st_dev, st_ino, and inode gen number) changes,
then send the whole new file.  (Not that block-level replication is an
end-all-be-all.  But that for some use cases it's very nice.  Logical
replication is better for other use-cases.)

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 08:04:38PM +0200, Sylvain Pointeau wrote:
> On Fri, Aug 4, 2017 at 6:24 PM, Nico Williams  wrote:
> > On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote:
> > > Now I would like to implement the seq_currval:
> 
> we could implement it like (in pseudo code):
> 
> var currvalues = map[string,number]
> 
> function seq_nextval(seq_name) {
>   update sequence set val = val + inc where name = seq_name
>   nextval = select val from sequence where name = seq_name
>   currvalues[seq_name] = nextval
>   return nextval
> }
> 
> function seq_currval(seq_name) {
>   if currvalues[seq_name] is not set => raise error
>   return currvalues[seq_name]
> }

Sure, but the currvalues[] map has to be store... in the DB handle.  You
could use a TEMP TABLE, but you'd have to make sure to DELETE all its
rows when a transaction ends.

> however I noticed that because I am retrieving the params as int64:
> '10a' becomes 10,
> 'abc' becomes 0
> null becomes 0
> 
> and I think that is ok, so the SEQ_INIT INTEGER NOT NULL can never be null
> and the SEQ_INC can never be 0 due to the check

Sure.

Nico
-- 
___
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 Fri, Aug 4, 2017 at 6:24 PM, Nico Williams  wrote:

> On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote:
> > Please find below the last source code, I removed the check on the table
> > (NOT NULL on both sql_val and seq_inc)
>
> Yeah, I saw.  I think this is another argument for SQLite3 needing a
> strict-type mode!
>
> > Now I would like to implement the seq_currval:
> >
> > Oracle raises an exception because seq_nextval must be called at least
> one
> > time for the session
>
> I'm not sure how to implement this.  Maybe with the sessions extension.
>
> But I don't think this is an important semantic in the context of
> SQLite3 -- after all, it's "lite".
>

we could implement it like (in pseudo code):

var currvalues = map[string,number]

function seq_nextval(seq_name) {
  update sequence set val = val + inc where name = seq_name
  nextval = select val from sequence where name = seq_name
  currvalues[seq_name] = nextval
  return nextval
}

function seq_currval(seq_name) {
  if currvalues[seq_name] is not set => raise error
  return currvalues[seq_name]
}


>
> > H2 returns init minus seq (making nextval returning the init value) =>
> this
> > is also the trick I used so the update is simpler so more efficient
>
> I don't follow.  What's H2?
>
>
H2 is a java embedded database, similar to sqlite. I recently moved most of
my projects (in java/kotlin) to sqlite, one is still using H2, mainly due
to the sequence.


> > do you agree that we must use a variable mapping sequenceName => last
> > nextval
> >
> > should I use a very simple structure (fixed sized array and O(n) access)?
> > or use a map? but then I would rather prefer to switch to C++, unless you
> > have a better idea?
>
> I don't understand this.
>

see the pseudo code above, how to implement the map[string,number] in C? a
fixed sized array?


>
> > #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 PRIMARY KEY, " \
> >   " SEQ_VAL INTEGER, " \
> >   " SEQ_INC INTEGER " \
> >   " )", 0, 0, 0);
> >
> >   if( rc != SQLITE_OK ) {
> > sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> > return;
> >   }
>
> You could still check the types here...  It won't kill performance --
> after all, this is only at sequence init time.
>
> Also, you could keep the CHECK constraints on the name and increment
> columns, and not on the current columns.  Not sure it's worth it.
>
>
ok I did it

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

however I noticed that because I am retrieving the params as int64:
'10a' becomes 10,
'abc' becomes 0
null becomes 0

and I think that is ok, so the SEQ_INIT INTEGER NOT NULL can never be null
and the SEQ_INC can never be 0 due to the check

The rest looks good.
>
> Also, I saw D. R. Hipp's discussion of increased write concurrency.
> Ooops!  (Also, great news!)  Maybe you should change the code for the
> next value to a loop like I described earlier (get the current value,
> update to add 1 where the value hasn't changed, repeat until the update
> statement updates one row, not zero).
>

I did not really understand your logic here.

Best regards,
Sylvain
___
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 Fri, Aug 04, 2017 at 10:55:57AM -0500, Bob Friesenhahn wrote:
> Sqlite does not really have a way to know if a module in the current
> directory (the directory which just happened to be current when the request
> was made) should be trusted.  To be secure, sqlite should insist that the
> load request be something other than a bare module name because then the
> responsibility is put on the user of sqlite.

You can always load an absolute path.  That said, using relative paths
and depending on the caller's run-path is not bad per-se -- just
something to be aware of.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 08:50:05AM +0200, Luc DAVID wrote:
> sqlite has WAL mode for better concurrency and this could maybe be used to
> extend the number of writters:
> 
> Do you think it would be possible to create a
> MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is
> currently running in order to allow more writers?
> 
> The sqlite engine would then take care of dealing with all the WAL files
> when reading data, backup...etc

WAL mode still ends up having no read concurrency when it's time to
checkpoint the WAL.  The same would happen with this concept.  I don't
think this works well.

Other approaches will work better.  One would be to have a server
process, with the SQLite3 API transparently using IPC to talk to it.  Or
else a single-process DB (connections from only one process allowed at
any time) where internal thread synchronization could be used to manage
locks (on rows/pages and a log used to ultimately put together a final
transaction in the background so that other threads can keep making
progress).

Doing this sort of thing with multiple processes gets tricky fast,
requiring shared memory, mutexes on shared memory, ...  IPC to a server
process makes everything much simpler, but there is an IPC performance
penalty.  The server process model works very well for PostgreSQL
though...

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 12:58:43PM +0100, Simon Slavin wrote:
> The problem you’re trying to fix is one of the big problems with
> distributed databases.  Nobody has found a good solution for it yet.

It's impossible to solve for the eventually-consistent types.  You just
have to explicitly handle... eventual conflicts... eventually.

For the rest, higher write concurrency is not the end of the world, but
you have to be aware of it and you have to be careful.  The RDBMS can
only do so much to protect you automatically -- the more it does, the
worse it will perform, but the less it does the more you have to be
aware and code defensively.

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Nico Williams]

On Fri, Aug 04, 2017 at 09:33:31AM +0200, Eric Grange wrote:
> The main problem with multiple writers would be in conflict resolution,
> locking and deadlocks.

There is plenty in the literature about this.

You have to code more defensively, you may need things like "FOR
UPDATE", etc.

> Imagine a simple accounting transactions "update accounts set value = value
> + 1 where ..." if run at the same time from 2 threads (or close enough),
> then if you do not have conflict resolution in place you may end up
> increase value by only 1

I write that like so:

  SELECT value
  FROM accounts
  WHERE ...; -- store this in a variable

  UPDATE accounts
  SET value = value + 1
  WHERE ... AND value = :value_read_earlier;

This is how one is supposed to get atomicity in LDAP too, incidentally.

So, if you were creating a SEQUENCE kind of thing (see the "sequencer"
thread this week too) then I'd return 1 + value_read_earlier.

This approach works well and is portable!  But it does require that you
think about concurrency :(

> 1) alice starts transaction
> 2) bob starts transaction
> 3) alice reads value, sees 10
> 4) bob reads value, sees 10
> 5) alice writes 10+1
> 6) bob writes 10+1 ooops

Yeah, "don't do that"; see above.

> And once you have more than one query, things become even more complicated.

Things are already this complicated if you use PostgreSQL, SQL Server,
Oracle, ...

There's nothing new here, except that plenty of code wirtten with
SQLite3 in mind may need to be modified to be safe with higher write
concurrency enabled (so it probably shouldn't be enabled by default).

> Even among full relational DBs, few manage these correctly. IME only
> PostgreSQL and Firebird handle these correctly by default, for Oracle or
> MSSQL you have to use special locking modes and transaction options with
> significant performance penalties.

Yes, you have to be aware of varying synchronization/locking features
(e.g., "FOR UPDATE") and write concurrency semantics.

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 06:06:55PM +0200, Sylvain Pointeau wrote:
> Please find below the last source code, I removed the check on the table
> (NOT NULL on both sql_val and seq_inc)

Yeah, I saw.  I think this is another argument for SQLite3 needing a
strict-type mode!

(I very much prefer strong typing, though for EAV schemas it's very
nice to have a duck-typed V column.  But if CHECK constraints kill
performance, then strong typing via CHECK constraints is not viable.
I know, CHECK constraints don't get you strong typing in VIEWs and
queries in general, but it's good enough for my purposes.)

> I find that the performance is not that bad now.

Excellent.

> Now I would like to implement the seq_currval:
> 
> Oracle raises an exception because seq_nextval must be called at least one
> time for the session

I'm not sure how to implement this.  Maybe with the sessions extension.

But I don't think this is an important semantic in the context of
SQLite3 -- after all, it's "lite".

> H2 returns init minus seq (making nextval returning the init value) => this
> is also the trick I used so the update is simpler so more efficient

I don't follow.  What's H2?

> do you agree that we must use a variable mapping sequenceName => last
> nextval
>
> should I use a very simple structure (fixed sized array and O(n) access)?
> or use a map? but then I would rather prefer to switch to C++, unless you
> have a better idea?

I don't understand this.

> #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 PRIMARY KEY, " \
>   " SEQ_VAL INTEGER, " \
>   " SEQ_INC INTEGER " \
>   " )", 0, 0, 0);
> 
>   if( rc != SQLITE_OK ) {
> sqlite3_result_error(context, sqlite3_errmsg(db), -1);
> return;
>   }

You could still check the types here...  It won't kill performance --
after all, this is only at sequence init time.

Also, you could keep the CHECK constraints on the name and increment
columns, and not on the current columns.  Not sure it's worth it.

The rest looks good.

Also, I saw D. R. Hipp's discussion of increased write concurrency.
Ooops!  (Also, great news!)  Maybe you should change the code for the
next value to a loop like I described earlier (get the current value,
update to add 1 where the value hasn't changed, repeat until the update
statement updates one row, not zero).

Nico
-- 
___
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 Fri, Aug 4, 2017 at 6:03 PM, petern  wrote:

> About the H2 test.  That tester actually uses 10e6 is 10 x 10^6, 10 million
> rows.  When I wrote you I forgot I bumped it up to 10 million since 1
> million was only half a second on the native case.  Give that a try on H2
> and you'll see 22s goes to above 200s.
> >
> > CREATE SEQUENCE IF NOT EXISTS SEQ_TEST START WITH 1;
> > CREATE TABLE SEQ_TEST(seq_num INT UNIQUE NOT NULL);
> >
> > insert into SEQ_TEST(seq_num)
> > select seq_test.nextval from SYSTEM_RANGE(1, *100*)
> > ;
>

no, the H2 test case was 1 million. (my computer might be terribly slower
than yours)
and I also used 1 million for my sqlite test case, so I was consistent.
___
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 Fri, Aug 04, 2017 at 08:20:10AM -0700, J Decker wrote:
> On Fri, Aug 4, 2017 at 8:11 AM, Nico Williams  wrote:
> > No, see, the ".so"/".dll" suffix is used in all cases, and it varies by
> > platform, so it's best if SQLite3 adds it so you can keep your code more
> > portable.  While the "lib" prefix is only ever needed if you want a
> > link-edit to find the thing as -l -- which you almost never ever
> > want when  is a loadable module.
> 
> so does the lib prefix.  You cannot package a .so in an android .apk.
>  but you can include lib.so.

Really?!  Oy.  But still, that would just mean that when building for an
Android platform SQLite3 *must* add the "lib" prefix, not that it should
try it on every Unix-like platform.

> At which point, because I apparently missed that too.  if one is using
> CMake, you get a lib.so without setting additional options.

So?

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

Re: [sqlite] sequencer

[Sylvain Pointeau]

Please find below the last source code, I removed the check on the table
(NOT NULL on both sql_val and seq_inc)

sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('seq1') from T;
Run Time: real 18.829 user 16.146103 sys 0.140401

I find that the performance is not that bad now.

Because of the performance (but I did not measure), I won't do the check on
the parameters, but I did put the same name for the seq_init for 2 or 3
parameters.

Now I would like to implement the seq_currval:

Oracle raises an exception because seq_nextval must be called at least one
time for the session
H2 returns init minus seq (making nextval returning the init value) => this
is also the trick I used so the update is simpler so more efficient

do you agree that we must use a variable mapping sequenceName => last
nextval
should I use a very simple structure (fixed sized array and O(n) access)?
or use a map? but then I would rather prefer to switch to C++, unless you
have a better idea?

#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 PRIMARY KEY, " \
  " SEQ_VAL INTEGER, " \
  " SEQ_INC INTEGER " \
  " )", 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-seq_inc_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", 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

Re: [sqlite] sequencer

[petern]

About the H2 test.  That tester actually uses 10e6 is 10 x 10^6, 10 million
rows.  When I wrote you I forgot I bumped it up to 10 million since 1
million was only half a second on the native case.  Give that a try on H2
and you'll see 22s goes to above 200s.

On Fri, Aug 4, 2017 at 4:19 AM, Sylvain Pointeau  wrote:

> On Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau <
> sylvain.point...@gmail.com
> > wrote:
>
> > On Fri, Aug 4, 2017 at 7:41 AM, petern 
> > wrote:
> >
> >> 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.
> >>
> >
> >
> for information, I tested it with H2
>
> CREATE SEQUENCE IF NOT EXISTS SEQ_TEST START WITH 1;
> CREATE TABLE SEQ_TEST(seq_num INT UNIQUE NOT NULL);
>
> insert into SEQ_TEST(seq_num)
> select seq_test.nextval from SYSTEM_RANGE(1, 100)
> ;
>
> [2017-08-04 13:17:00] 100 rows affected in 22s 349ms
> ___
> 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()

[Bob Friesenhahn]

On Fri, 4 Aug 2017, Peter Da Silva wrote:


On 8/4/17, 8:29 AM, "sqlite-users on behalf of Bob Friesenhahn" 
 
wrote:

Lazy programmers who request such things are of the same ilk which use 
programming practices resulting in SQL injection attacks.  Sqlite should not 
promote such practices.


Then require a fully qualified path and extension, and don’t have a search path 
for DLLs at all.

Otherwise you’re just haggling over where to draw the line.


The operating system (insert operating system used here) has an 
operating-system specific algorithm it uses when it searches for 
shared libraries which were specified using only the file name. 
Whether 'lib' at the front of the file name is significant to its 
searching behavior depends on the operating system used.


I have not checked what sqlite actualy does, but for security, it 
should be doing its own 'stat' to find the existing module, and then 
open it via an explicit path in order to defeat any operating-system 
specific behavior.


If sqlite were to simply issue load requests via dlopen() (or 
equivalent) with various permutations, then it would become subject to 
the varying behavior of different systems.  For example, a program 
which uses sqlite as part of a directory indexer which is indexing a 
directory which contains uploads from untrusted users could be 
compromised.


Sqlite does not really have a way to know if a module in the current 
directory (the directory which just happened to be current when the 
request was made) should be trusted.  To be secure, sqlite should 
insist that the load request be something other than a bare module 
name because then the responsibility is put on the user of sqlite.


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] Suggestion: Pre-release Snapshots versioning

[Richard Hipp]

On 8/4/17, jose isaias cabrera  wrote:
>
> Right now, when I went to a machine that I had upgraded with a
> snapshot, I saw that the version was 3.20.0.  But when I compared the DLL
> file size and date, they were different.  It would be nice for pre-releases
> to have something to distinguish them with the new one.

That "something" is the "source-id".  You can access the source-id
from C-code using

SQLITE_SOURCE_ID   (https://sqlite.org/c3ref/c_source_id.html)

or

sqlite3_sourceid()  (https://sqlite.org/c3ref/libversion.html)

Or you can access the information from SQL using

sqlite_source_id()  (https://sqlite.org/lang_corefunc.html#sqlite_source_id)

The 3.20.0 release version has a source-id of

"2017-08-01 13:24:15
9501e22dfeebdcefa783575e47c60b514d7c2e0cad73b2a496c0bc4b680900a8"

The snapshots have an earlier date.  You can trace the version of
SQLite you are running back to a particular source-code repository
check-in using the hash.  Let $HASH be some prefix of the hash shown
at the end of the source-id.  (8 characters is usually plenty.)  Then
you can find the check-in, in context, by visiting
"sqlite3.org/src/timeline?c=$HASM".  For example:

https://sqlite.org/src/timeline?c=9501e22d

-- 
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] Feature request: check for 'lib' prefix for load_extension()

[J Decker]

On Fri, Aug 4, 2017 at 8:11 AM, Nico Williams  wrote:

> On Fri, Aug 04, 2017 at 10:17:33AM +0200, Dominique Devienne wrote:
> > On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams 
> wrote:
> > > 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.
> >
> > While you're technically correct, Matt's request seems completely
> > reasonable to me.
>
> Not if it's a result of being mistaken.  Now that OP knows about object
> naming, he can reconsider and restate his request.
>
> > If SQLite wasn't doing *any* changes to the filename, not adding the
> > extension for example, you may have had a point, but since it does,
> > trying with the lib prefix on Linux, which is undeniably a common
>
> No, see, the ".so"/".dll" suffix is used in all cases, and it varies by
> platform, so it's best if SQLite3 adds it so you can keep your code more
> portable.  While the "lib" prefix is only ever needed if you want a
> link-edit to find the thing as -l -- which you almost never ever
> want when  is a loadable module.
>

so does the lib prefix.  You cannot package a .so in an android .apk.
 but you can include lib.so.



>
> > naming convention on Linux, seems like a worthwhile addition. [...]
>
> You didn't understand.
>
At which point, because I apparently missed that too.  if one is using
CMake, you get a lib.so without setting additional options.


>
> > I really don't see what's controversial with Matt's request :)
>
> a) it's borne of a mistake
> b) it's not necessary
> c) it's more code and more docs
> d) it's more likely to lead to accidents
>
>
a) It's born of 'I want to load 'myExtension'" and sqlite already does
substitutions.
b) maybe.
c) so?  it's not like a whole path is specified that you'd get
/usr/lib/myextension  and then try lib/usr/lib/myextension.so
d) and more likely to cause scripting configuration issues;   There's no
.if or .goto commands to be able to handle error conditions.  So you have
to maintain at least 2 scripts instead of just 1.  Leading to more
accidents in updating one and not the other.


> > It's not like load-extension is a performance-critical operation, that
> > trying an extra load is that expensive.
>
> This is true, but also irrelevant :)
>
> > And the security consideration that an "attacker" could make it load
> > his own library instead, but using a different name tried before the
> > actual one is moot IMHO, since extension loading is by nature unsafe.
> >
> > In short, I support Matt's request and I hope DRH considers it seriously.
> > FWIW :). --DD
>
> What problems does this solve?  None.
>
> Wrong, it adds the ability to code a single script to execute.


> 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] Feature request: check for 'lib' prefix for load_extension()

[J Decker]

On Fri, Aug 4, 2017 at 6:29 AM, Bob Friesenhahn <
bfrie...@simple.dallas.tx.us> wrote:

> On Fri, 4 Aug 2017, Dominique Devienne wrote:
>
>>
>> I really don't see what's controversial with Matt's request :)
>>
>> It's not like load-extension is a performance-critical operation, that
>> trying an extra load is that expensive.
>> And the security consideration that an "attacker" could make it load his
>> own library instead, but using a
>> different name tried before the actual one is moot IMHO, since extension
>> loading is by nature unsafe.
>>
>> In short, I support Matt's request and I hope DRH considers it seriously.
>> FWIW :). --DD
>>
>
> It is true that sqlite normally only needs to load an extension once per
> invocation.  However, loading an extension incurs a cost in that
> several/many 'stat' operations on the filesystem are necessary in order to
> find the module unless the full path to it was given (use 'strace',
> 'truss', or 'dtruss' to see this in action).  The security implications can
> be severe on some popular operating systems.
>
> As I mentioned before, two very popular desktop OSs (Microsoft Windows and
> Apple's OS X) have a defined pattern in that they will search the current
> directory for a module by default.


And then search the whole path.  certainly not a cost savings, don't see
the point.


> Hopefully it should sink in that if one requests loading the extension
> while the process current directory is currently in a potentially 'hostile'
> directory that this may lead to the compromise of the account of the user
> ID executing sqlite because arbitrary binary code can be injected.
>

And how does this help avoid that?  export LD_LIBRARY_PATH=. and it is the
same behavior as windows...


>
> Lazy programmers who request such things are of the same ilk which use
> programming practices resulting in SQL injection attacks.  Sqlite should
> not promote such practices.
>
> Probably the extension is not in a well known place, so a path of possible
places is still going to be checked.


> 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] Feature request: check for 'lib' prefix for load_extension()

[Nico Williams]

On Fri, Aug 04, 2017 at 10:17:33AM +0200, Dominique Devienne wrote:
> On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams  wrote:
> > 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.
> 
> While you're technically correct, Matt's request seems completely
> reasonable to me.

Not if it's a result of being mistaken.  Now that OP knows about object
naming, he can reconsider and restate his request.

> If SQLite wasn't doing *any* changes to the filename, not adding the
> extension for example, you may have had a point, but since it does,
> trying with the lib prefix on Linux, which is undeniably a common

No, see, the ".so"/".dll" suffix is used in all cases, and it varies by
platform, so it's best if SQLite3 adds it so you can keep your code more
portable.  While the "lib" prefix is only ever needed if you want a
link-edit to find the thing as -l -- which you almost never ever
want when  is a loadable module.

> naming convention on Linux, seems like a worthwhile addition. [...]

You didn't understand.

> I really don't see what's controversial with Matt's request :)

a) it's borne of a mistake
b) it's not necessary
c) it's more code and more docs
d) it's more likely to lead to accidents

> It's not like load-extension is a performance-critical operation, that
> trying an extra load is that expensive.

This is true, but also irrelevant :)

> And the security consideration that an "attacker" could make it load
> his own library instead, but using a different name tried before the
> actual one is moot IMHO, since extension loading is by nature unsafe.
> 
> In short, I support Matt's request and I hope DRH considers it seriously.
> FWIW :). --DD

What problems does this solve?  None.

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

[sqlite] Suggestion: Pre-release Snapshots versioning

[jose isaias cabrera]

Greetings!

Thanks for this wonderful tool! Dr. Hipp and team, thanks.

A suggestion I have is that the pre-release snapshots have some type of 
versioning.  Right now, when I went to a machine that I had upgraded with a 
snapshot, I saw that the version was 3.20.0.  But when I compared the DLL file 
size and date, they were different.  It would be nice for pre-releases to have 
something to distinguish them with the new one.  I suggest “3.20.0 beta release 
0” or “3.20.0 beta 201707251512”.  I know that this may damage some folks 
programming, but it will save time for some of us that use multiple machines 
for testing and also to distinguish between the regular releases and the 
pre-releases.  Thanks.

josé
___
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 Fri, Aug 04, 2017 at 06:05:53AM +, Hick Gunter wrote:
> >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> >Auftrag von Nico Williams
> >But loadable modules are almost never meant to be used that way.
> >They're usually meant to be used only through dlopen() and friends.
> 
> Which other method apart from dlopen() would you recommend for
> dynamically loaded extensions?

There's only two methods for loading an object dynamically: because you
demanded it at link-edit-time, or because you used dlopen() or similar.

There are no others, full stop.

(You could write your own run-time loader, but you'd still be
implementing a dlopen().)

> We are using virtual tables to interface with diverse data stores and
> make them queryable with SQL. The general interactive shell needs
> access to a certain subset of functions, and some speciality tools
> have dedicated loadable extensions that should not be accessible
> outside of that scope.

So?

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 03:35:36AM +, Sylvain Pointeau wrote:
> Le ven. 4 août 2017 à 02:42, Nico Williams  a écrit :
> > >   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

I think it would be too easy to reset sequences, but that's not
something one wants to do often.

> > Anyways, no change needed here.
> 
> should I use the mutex lock to ensure atomicity?

No.  It can't help.  As I said, no change is needed here.

> > Nice!  Did you test it?
> 
> yes! I tested it with mingw compiler (and cmake), and the sqlite3 shell to
> test the seq functions.
> 
> I will do the code update and will probably create a github repository

Excellent!

> thanks a lot for your review.

Thank you for taking this on!

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Olivier Mascia]

> Le 4 août 2017 à 14:15, Richard Hipp  a écrit :
> 
> Another alternative is the newer server-process-edition branch
> (https://sqlite.org/src/timeline?n=all=server-process-edition) which
> you can read about here:
> https://sqlite.org/src/artifact/0c6bc6f55191b690

Looks certainly promising!

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


___
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

[Dominique Devienne]

On Fri, Aug 4, 2017 at 11:37 AM, Ulrich Telle  wrote:

> Regarding the development of SQLite extensions (UDFs) and using the
> pointer-binding interface to communicate between different extensions

(AFAIK one of the reasons to introduce the new pointer-binding interface)
> the advice to use string literals is certainly good.
>

I think on the contrary they are more meant to communicate across different
UDFs of the *same* extension, and not across extensions.

Typical use (IMHO) of the pointer-binding interface is to pass around a
pointer to an extension-defined struct from one function to another,
from an inner "parsing" function (e.g. taking JSON text and parsing it into
a compact and efficient binary representation) whose result is
used by another UDF of the same extension that is aware of the custom
struct of the "parsed representation" of the JSON and can therefore
do more efficient processing. In that context, pointer equality of string
literals can be used and relied upon w/o even a strcmp(), and both
UTFs are "tightly coupled". The "type-pointer" is simply a "poor man's type
system" to guarantee the hard-cast in the receiving UDF is "safe".

I'm not even sure you can guarantee uniqueness of string literals across
dynamically loaded shared libraries, maybe the runtime linker
merges equivalent read-only "static" text from different libs, but that
seems very platform-dependent and not something to rely on.

It's also probably unusual IMHO for different extensions to share headers
to have a common understanding of a custom struct
to share at runtime via SQLite pointer-binding in UDFs across these
extensions. Not impossible, but seem rather unlikely to me.

When you look at it from this lens, then enforcing string literals for the
type-pointer makes sense. My $0.02, FWIW. --DD

PS: Yes, like others I'd love it if DRH went further into this "custom
type" route, even persistent ones longer term with a format change,
  since pointer-binding with type-string-pointer is going in that
direction, at least at runtime-only, and only for "temporaries" across UDFs,
  but I'm afraid we're a long way from that, and I'm not holding my breadth
:)
___
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()

[Peter Da Silva]

On 8/4/17, 8:29 AM, "sqlite-users on behalf of Bob Friesenhahn" 
 wrote:
> Lazy programmers who request such things are of the same ilk which use 
> programming practices resulting in SQL injection attacks.  Sqlite should not 
> promote such practices.

Then require a fully qualified path and extension, and don’t have a search path 
for DLLs at all.
 
Otherwise you’re just haggling over where to draw the line.

___
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 Fri, 4 Aug 2017, Dominique Devienne wrote:


I really don't see what's controversial with Matt's request :)

It's not like load-extension is a performance-critical operation, that
trying an extra load is that expensive.
And the security consideration that an "attacker" could make it load his
own library instead, but using a
different name tried before the actual one is moot IMHO, since extension
loading is by nature unsafe.

In short, I support Matt's request and I hope DRH considers it seriously.
FWIW :). --DD


It is true that sqlite normally only needs to load an extension once 
per invocation.  However, loading an extension incurs a cost in that 
several/many 'stat' operations on the filesystem are necessary in 
order to find the module unless the full path to it was given (use 
'strace', 'truss', or 'dtruss' to see this in action).  The security 
implications can be severe on some popular operating systems.


As I mentioned before, two very popular desktop OSs (Microsoft Windows 
and Apple's OS X) have a defined pattern in that they will search the 
current directory for a module by default.  Hopefully it should sink 
in that if one requests loading the extension while the process 
current directory is currently in a potentially 'hostile' directory 
that this may lead to the compromise of the account of the user ID 
executing sqlite because arbitrary binary code can be injected.


Lazy programmers who request such things are of the same ilk which use 
programming practices resulting in SQL injection attacks.  Sqlite 
should not promote such practices.


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

[Sylvain Pointeau]

> On Fri, Aug 4, 2017 at 7:41 AM, petern 
>> wrote:
>>
>>> 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.
>>>
>>

> I am sorry I fail to see how to improve it, I would be really interested
> to have advises from the experts!
>

HURR ! I found something !

if I remove the checks on the SP_SEQUENCE table, it is much faster

sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE
i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('seq1') from T;
Run Time: real 18.735 user 16.614106 sys 0.109201

and I have another idea to select on the rowid at the update.

Best regards,
Sylvain
___
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 Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau  wrote:

> On Fri, Aug 4, 2017 at 7:41 AM, petern 
> wrote:
>
>> 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.
>>
>
> Hi Peter,
>
> I am not using the sequence as default, but I use it explicitely like
> insert into T (T_ID) values (seq_nextval('seq'))
>
> I generally create few hundred rows in my app, so I won't see any
> performance issue,
>
> Nevertheless, I am surprised to see so much difference between my UDF and
> the trigger implementation.
>
> Does anyone have an idea how to improve it? (using the same approach ->
> the sequence is in a table
>


I am sorry I fail to see how to improve it, I would be really interested to
have advises from the experts!

Furthermore, is it still useful to make a github repository since this is
slow?

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

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Richard Hipp]

On 8/4/17, Luc DAVID  wrote:
> Hello,
>
> I was thinking about a possible solution for sqlite "only single writer
> is allowed at the same time" and database lock.
>
> sqlite has WAL mode for better concurrency and this could maybe be used
> to extend the number of writters:

The begin-concurrent branch
(https://sqlite.org/src/timeline?r=begin-concurrent=all) allows you
to say:

 BEGIN CONCURRENT;
 -- various database reads and updates
 COMMIT;

And to do that simultaneously in two or more database connections, and
have them all work.  Except, the concurrent transactions may not
overlap.  That is to say, content written by one may not be read or
written by another.  If the transactions do overlap, the second one to
try to COMMIT will get an SQLITE_BUSY_SNAPSHOT error and will be
forced to abandon its transaction and start over.

The begin-concurrent branch is in production use in high-stress
environments.  We have not merged that branch to trunk (yet) because
it currently imposes extra overhead on all applications, even
applications that do not use BEGIN CONCURRENT.

Another alternative is the newer server-process-edition branch
(https://sqlite.org/src/timeline?n=all=server-process-edition) which
you can read about here:
https://sqlite.org/src/artifact/0c6bc6f55191b690

-- 
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] Thinking about a way to extend the number of writers in WAL mode

[Simon Slavin]

On 4 Aug 2017, at 11:43am, Luc DAVID  wrote:

> sqlite was not designed for this kind of access but It would be great to have 
> a higher level of concurrency

The problem with these things is that you have SQLite trying to read the minds 
of he programmer and user.  Consider two operations being done by different 
computers at the same time:

UPDATE contacts SET phone = REPLACE (phone, '444', '555')

INSERT INTO contacts VALUES ('Charles', '444 1234')

The first one is done because an entire phone exchange got renumbered to make 
way for a new exchange.  The second is a new contact.  But the two operations 
were in overlapping transactions.  When they’re both finished should the new 
contact have '444' or '555' ?

Here’s another scenario.  Suppose you have an invoice file so you can invoice 
those contacts, and the key of the invoice file is the rowid of the contact 
file.  Two users each want to create an invoice for a new contact at the same 
time.  The software running on their computers needs to insert the new contact, 
then find the rowid of the new contact so it can create an invoice for it.  How 
can you arrange this when the transactions can overlap ?

The problem you’re trying to fix is one of the big problems with distributed 
databases.  Nobody has found a good solution for it yet.

Simon.
___
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 Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau  wrote:

> On Fri, Aug 4, 2017 at 7:41 AM, petern 
> wrote:
>
>> 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.
>>
>
>
for information, I tested it with H2

CREATE SEQUENCE IF NOT EXISTS SEQ_TEST START WITH 1;
CREATE TABLE SEQ_TEST(seq_num INT UNIQUE NOT NULL);

insert into SEQ_TEST(seq_num)
select seq_test.nextval from SYSTEM_RANGE(1, 100)
;

[2017-08-04 13:17:00] 100 rows affected in 22s 349ms
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

[Luc DAVID]

Yes this would be the case if no one closes the write transactions 
before reading.


Would a possible solution be to use "read uncommited" or to include a 
kind of timestamp or autoInc identifier used internally by sqlite engine 
? even if I am not sure it would be enough to avoid conflicts...


sqlite was not designed for this kind of access but It would be great to 
have a higher level of concurrency


Luc



Le 04/08/2017 à 09:33, Eric Grange a écrit :

The main problem with multiple writers would be in conflict resolution,
locking and deadlocks.

Imagine a simple accounting transactions "update accounts set value = value
+ 1 where ..." if run at the same time from 2 threads (or close enough),
then if you do not have conflict resolution in place you may end up
increase value by only 1

1) alice starts transaction
2) bob starts transaction
3) alice reads value, sees 10
4) bob reads value, sees 10
5) alice writes 10+1
6) bob writes 10+1 ooops

And once you have more than one query, things become even more complicated.

Even among full relational DBs, few manage these correctly. IME only
PostgreSQL and Firebird handle these correctly by default, for Oracle or
MSSQL you have to use special locking modes and transaction options with
significant performance penalties.

Eric

On Fri, Aug 4, 2017 at 8:50 AM, Luc DAVID  wrote:


Hello,

I was thinking about a possible solution for sqlite "only single writer is
allowed at the same time" and database lock.

sqlite has WAL mode for better concurrency and this could maybe be used to
extend the number of writters:

Do you think it would be possible to create a
MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is
currently running in order to allow more writers?

The sqlite engine would then take care of dealing with all the WAL files
when reading data, backup...etc

The maximum of allowed writers could be set by a pragma or another mean
(when opening the db)

It seems a simply way to boost sqlite concurrency.

Am I wrong on this point ?

Best Regards

Luc

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


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


___
sqlite-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,

> I agree that it's impossible to enforce "static strings" or "strings that 
> live long enough" with the C/C++ type system.
> 
> You chose to force key management down the throat of your users,

Hm, quite a tough expression ... the advantage is that the wrapper does not 
depend on the pointer type being a string literal or static string. For the 
purpose - to allow pointer binding to communicate with existing SQLite 
extension - I think this is a legitimate approach.

Regarding the development of SQLite extensions (UDFs) and using the 
pointer-binding interface to communicate between different extensions (AFAIK 
one of the reasons to introduce the new pointer-binding interface) the advice 
to use string literals is certainly good.

Although my wrapper allows to implement UDFs, it is not meant (and not well 
suited) to implement stand-alone SQLite extensions. It is meant for end-user 
applications where the developer has access to all information, database 
related or not, anyway.

> with two problems :
> 
> - All the potential memory leaks, thread races, etc that may occur if your 
> key management has subtle concurrency bugs.

Using the same prepared statement object in different threads isn't a good idea 
anyway ... if it works at all. If the application is carefully designed in 
respect to SQLite database access I don't see a high risk.

> - Making your users think they can build *stuff* on top of this API, *stuff* 
> that are explicitly
> out of scope of the pointer API as described by the design rationale of 
> SQLite pointers API.

Sorry, since SQLite has no means to absolutely enforce the use of string 
literals for the pointer type parameter, developers can build *stuff* on top of 
the pointer-binding interface anyway. According to the explanation from Richard 
Hipp a main reason for the design decision was to avoid a runtime penalty for 
applications not using the pointer-binding interface at all.

> Plus memory problems if your users think they can feed your API with 
> arbitrary strings built at runtime.

My "users" are software developers. Why should they want to feed the API with 
arbitrary strings built at runtime? I'm pretty sure that in most cases the type 
strings will be string literals, but the API will not fail if a string variable 
is used.

> I think the problem, assuming there is one, is not in the SQLite API.

I ran into the problem that the pointer type string I tried to bind to the new 
interface was a temporary object. And that doesn't work with the current SQLite 
API. I learned that the new SQLite pointer-binding API was designed as it is on 
purpose. I resolved the issue for my wrapper classes. And that's it.

If a malicious developer wants to do evil, he can do so even with the SQLite 
API directly. He doesn't need my wrapper for that.

Regards,

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

Re: [sqlite] Checkin d8637bad produces bad amalgamation

[Dan Kennedy]

On 08/04/2017 03:50 AM, Keith Medcalf wrote:

Check-in d8637bad produces a bad amalgamation output.


Thanks for reporting this. Should be fixed now.

Dan.





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



___
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()

[Dominique Devienne]

On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams  wrote:

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


While you're technically correct, Matt's request seems completely
reasonable to me.
If SQLite wasn't doing *any* changes to the filename, not adding the
extension for example,
you may have had a point, but since it does, trying with the lib prefix on
Linux, which is
undeniably a common naming convention on Linux, seems like a worthwhile
addition. It is
true after all most build system will be default use that "ld-based" naming
convention, and
that there's no distinction for a shared lib meant to be loaded explicitly
via dlopen/LoadLibrary
or one that's implicitly linked with an executable. If fact, you can have
the same shared lib
SQLite "extension" used both ways, explicit-loading by sqlite3.exe, and
implicit-loading by
custom-app.exe.

I really don't see what's controversial with Matt's request :)

It's not like load-extension is a performance-critical operation, that
trying an extra load is that expensive.
And the security consideration that an "attacker" could make it load his
own library instead, but using a
different name tried before the actual one is moot IMHO, since extension
loading is by nature unsafe.

In short, I support Matt's request and I hope DRH considers it seriously.
FWIW :). --DD
___
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é]

I agree that it's impossible to enforce "static strings" or "strings that live 
long enough" with the C/C++ type system.

You chose to force key management down the throat of your users, with two 
problems :

- All the potential memory leaks, thread races, etc that may occur if your key 
management has subtle concurrency bugs.
- Making your users think they can build *stuff* on top of this API, *stuff* 
that are explicitly out of scope of the pointer API as described by the design 
rationale of SQLite pointers API. Plus memory problems if your users think they 
can feed your API with arbitrary strings built at runtime.

I think the problem, assuming there is one, is not in the SQLite API.

Gwendal


> Le 4 août 2017 à 08:36, Ulrich Telle  a écrit :
> 
> Gwendal,
> 
>> 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.
> 
> I could do that, of course. But it wouldn't solve the issue. It would push 
> the problem just one level up.
> 
>> // pointerType should be a static string
>> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, char 
>> *pointerType, void(*DeletePointer)(void*))
> 
> That doesn't enforce a static string. The signature would have to be at least:
> 
> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const char* 
> pointerType, void(*DeletePointer)(void*))
> 
> However, if not called with a string literal, it would still easily fail to 
> work. Simplified example:
> 
> void* ptr = ...;
> char* pType = malloc(10);
> strcpy(pType, "carray");
> // ...
> stmt.Bind(1, ptr, pType, NULL);
> // ...
> free(pType);
> 
> Yes, I know, this is an artificial example. No one would implement it this 
> way in practice. It just demonstrates that even with a method signature 
> asking for a const char* it is not guaranteed to work. It would most probably 
> fail at runtime.
> 
> Additionally, wxWidgets supports various implicit string conversions. So - 
> just as SQLite itself - my wrapper methods can't detect whether a static 
> string or string literal was passed or whether the compiler constructed a 
> temporary string object. And the compiler will not issue error messages, 
> often not even warnings.
> 
> As mentioned in an earlier post, for my wrapper classes I decided to 
> implement the necessary housekeeping. Regarding the use of SQLite and my 
> wrapper classes I just quote a sentence which can be found in all SQLite 
> sources:
> 
> "May you do good and not evil."
> 
> 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] Thinking about a way to extend the number of writers in WAL mode

[Eric Grange]

The main problem with multiple writers would be in conflict resolution,
locking and deadlocks.

Imagine a simple accounting transactions "update accounts set value = value
+ 1 where ..." if run at the same time from 2 threads (or close enough),
then if you do not have conflict resolution in place you may end up
increase value by only 1

1) alice starts transaction
2) bob starts transaction
3) alice reads value, sees 10
4) bob reads value, sees 10
5) alice writes 10+1
6) bob writes 10+1 ooops

And once you have more than one query, things become even more complicated.

Even among full relational DBs, few manage these correctly. IME only
PostgreSQL and Firebird handle these correctly by default, for Oracle or
MSSQL you have to use special locking modes and transaction options with
significant performance penalties.

Eric

On Fri, Aug 4, 2017 at 8:50 AM, Luc DAVID  wrote:

> Hello,
>
> I was thinking about a possible solution for sqlite "only single writer is
> allowed at the same time" and database lock.
>
> sqlite has WAL mode for better concurrency and this could maybe be used to
> extend the number of writters:
>
> Do you think it would be possible to create a
> MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is
> currently running in order to allow more writers?
>
> The sqlite engine would then take care of dealing with all the WAL files
> when reading data, backup...etc
>
> The maximum of allowed writers could be set by a pragma or another mean
> (when opening the db)
>
> It seems a simply way to boost sqlite concurrency.
>
> Am I wrong on this point ?
>
> Best Regards
>
> Luc
>
> ___
> 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]

On Fri, Aug 4, 2017 at 7:41 AM, petern  wrote:

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

Hi Peter,

I am not using the sequence as default, but I use it explicitely like
insert into T (T_ID) values (seq_nextval('seq'))

I generally create few hundred rows in my app, so I won't see any
performance issue,

Nevertheless, I am surprised to see so much difference between my UDF and
the trigger implementation.

Does anyone have an idea how to improve it? (using the same approach -> the
sequence is in a table)

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

[sqlite] Thinking about a way to extend the number of writers in WAL mode

[Luc DAVID]

Hello,

I was thinking about a possible solution for sqlite "only single writer 
is allowed at the same time" and database lock.


sqlite has WAL mode for better concurrency and this could maybe be used 
to extend the number of writters:


Do you think it would be possible to create a 
MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is 
currently running in order to allow more writers?


The sqlite engine would then take care of dealing with all the WAL files 
when reading data, backup...etc


The maximum of allowed writers could be set by a pragma or another mean 
(when opening the db)


It seems a simply way to boost sqlite concurrency.

Am I wrong on this point ?

Best Regards

Luc

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

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

I could do that, of course. But it wouldn't solve the issue. It would push the 
problem just one level up.

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

That doesn't enforce a static string. The signature would have to be at least:

void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const char* 
pointerType, void(*DeletePointer)(void*))

However, if not called with a string literal, it would still easily fail to 
work. Simplified example:

void* ptr = ...;
char* pType = malloc(10);
strcpy(pType, "carray");
// ...
stmt.Bind(1, ptr, pType, NULL);
// ...
free(pType);

Yes, I know, this is an artificial example. No one would implement it this way 
in practice. It just demonstrates that even with a method signature asking for 
a const char* it is not guaranteed to work. It would most probably fail at 
runtime.

Additionally, wxWidgets supports various implicit string conversions. So - just 
as SQLite itself - my wrapper methods can't detect whether a static string or 
string literal was passed or whether the compiler constructed a temporary 
string object. And the compiler will not issue error messages, often not even 
warnings.

As mentioned in an earlier post, for my wrapper classes I decided to implement 
the necessary housekeeping. Regarding the use of SQLite and my wrapper classes 
I just quote a sentence which can be found in all SQLite sources:

"May you do good and not evil."

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

[Hick Gunter]

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von 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.
>

Which other method apart from dlopen() would you recommend for dynamically 
loaded extensions?

We are using virtual tables to interface with diverse data stores and make them 
queryable with SQL. The general interactive shell needs access to a certain 
subset of functions, and some speciality tools have dedicated loadable 
extensions that should not be accessible outside of that scope.


___
 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