Re: [sqlite] sequencer

[Sylvain Pointeau]

On Sun, Aug 6, 2017 at 6:34 PM, Sylvain Pointeau  wrote:

> Dear all,
>
> you can now find the code on github with a BSD3 license, as well as a
> release providing the dll for windows
>
>
forgot to add the link :-)
https://github.com/spointeau/libsequence


> I provided a cmake file, only tested for mingw (I don't have other
> compiler), I hope other will contribute to support other compilers.
>
> Again thanks for all your support.
>
> 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]

Dear all,

you can now find the code on github with a BSD3 license, as well as a
release providing the dll for windows

I provided a cmake file, only tested for mingw (I don't have other
compiler), I hope other will contribute to support other compilers.

Again thanks for all your support.

Best regards,
Sylvain


On Fri, Aug 4, 2017 at 10:44 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> 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

[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] 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 <sylvain.point...@gmail.com>
Cc: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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] 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] 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] 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] 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] 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] 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] 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

Re: [sqlite] sequencer

[petern]

Sylvain, are you happy with the performance?

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

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

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

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

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






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

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

Re: [sqlite] sequencer

[Sylvain Pointeau]

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

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

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

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

good idea, defaukt to 1

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

yes agree


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


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

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


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


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


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

yes I did it on purpose


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

should I use the mutex lock to ensure atomicity?


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

Re: [sqlite] sequencer

[Nico Williams]

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

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

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

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

Re: [sqlite] sequencer

[Nico Williams]

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

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

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

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

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

I think some type checking should be done.

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

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

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

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

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

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

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

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

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

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

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



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

do {
current_value = ;
;
} while ();

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

Anyways, no change needed here.



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

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

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

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

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

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

Re: [sqlite] sequencer

[petern]

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

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

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



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

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

Re: [sqlite] sequencer

[Sylvain Pointeau]

Hello,

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

Best regards,
Sylvain

---

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

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

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

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

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


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

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

  rc = sqlite3_step(stmt);

  sqlite3_finalize(stmt);

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

  sqlite3_result_int64( context, seq_init_val );
}

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

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

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

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

  rc = sqlite3_step(stmt);

  sqlite3_finalize(stmt);

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

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

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

  rc = sqlite3_step(stmt);

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

  sqlite3_finalize(stmt);

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

  sqlite3_result_int64( context, seq_val );
}


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

Re: [sqlite] sequencer

[Nico Williams]

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

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

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

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

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

Re: [sqlite] sequencer

[Alek Paunov]

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

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

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


CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

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



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


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


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




PostgreSQL-style sequence support would be much more general.

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

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


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


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


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

Re: [sqlite] sequencer

[Nico Williams]

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

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

Re: [sqlite] sequencer

[Sylvain Pointeau]

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

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


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

Re: [sqlite] sequencer

[Hick Gunter]

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

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

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

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

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

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

For field current_value:

 SELECT current FROM my_sequences WHERE name='';

You could also implement a virtual field rewind

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

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


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

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

Le mer. 2 août 2017 à 20:56, Richard Hipp <d...@sqlite.org> a écrit :

> On 8/2/17, Sylvain Pointeau <sylvain.point...@gmail.com> wrote:
> >
> > is it really possible to make an update into that nextval function?
> > I
> don't
> > think so since only one statement can be run at the same time if
> > sqlite
> is
> > in serialized mode.
>
> "serialized" means that multiple threads cannot be making updates at
> the same time.  In this case, the update would be happening in the
> same thread.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

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


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

Re: [sqlite] sequencer

[Sylvain Pointeau]

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

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

> On 8/2/17, Sylvain Pointeau  wrote:
> >
> > is it really possible to make an update into that nextval function? I
> don't
> > think so since only one statement can be run at the same time if sqlite
> is
> > in serialized mode.
>
> "serialized" means that multiple threads cannot be making updates at
> the same time.  In this case, the update would be happening in the
> same thread.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Richard Hipp]

On 8/2/17, Sylvain Pointeau  wrote:
>
> is it really possible to make an update into that nextval function? I don't
> think so since only one statement can be run at the same time if sqlite is
> in serialized mode.

"serialized" means that multiple threads cannot be making updates at
the same time.  In this case, the update would be happening in the
same thread.

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

Re: [sqlite] sequencer

[Nico Williams]

On Wed, Aug 02, 2017 at 06:46:55PM +, Sylvain Pointeau wrote:
> if I do
> 
> insert into mytable(f)
> select nextval("myseq") from T
> 
> is it really possible to make an update into that nextval function? I don't
> think so since only one statement can be run at the same time if sqlite is
> in serialized mode.

Yes, UDFs can run arbitrary SQL statements on the same connection.

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]

if I do

insert into mytable(f)
select nextval("myseq") from T

is it really possible to make an update into that nextval function? I don't
think so since only one statement can be run at the same time if sqlite is
in serialized mode.

Le mer. 2 août 2017 à 20:25, Nico Williams  a écrit :

> On Wed, Aug 02, 2017 at 06:10:52PM +, Sylvain Pointeau wrote:
> > for a general case, I would need to persist the counter into a table
> (for a
> > specified sequencer) and doing the nextval inside a mutex lock
> >
> > Is it possible to insert/ select from a UDF if the statements are
> > serialized? or should I use the virtual tables? (should we store the
> > sequencers in a different DB?)
>
> In SQLite3 all writes in transactions are serialized.  No locks needed
> because there's just one big lock around the entire DB.  I recommend you
> read up on SQLite3's transactions and locking.
>
> A next_serial() UDF would basically be a C-coded (or Perl, or whatever)
> function that uses the SQLite3 API to first run an UPDATE on the
> sequence then a SELECT to get the now-next value, and would return that.
>
> If you use the INSTEAD OF trigger approach, then the same applies,
> except that the triggers will be SQL-coded (which is nice, IMO).
>
> This is all perfectly safe in the current SQLite3 concurrency model
> (just one writer at a time).  I don't think SQLite3's write concurrency
> will ever get better, but I suppose one never knows!
>
> If you were using an RDBMS with higher write concurrency then you'd need
> to be more careful and arrange for synchronization.  Usually such
> RDBMSes provide builtin next_serial()-style functions anyways.
>
> As to your last question, I'd put the sequences table in the same DB,
> unless you need to attach multiple DBs and have all of them share
> sequences, in which case I'd make a DB just for the sequences, or else
> put them in the main DB.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Nico Williams]

On Wed, Aug 02, 2017 at 06:10:52PM +, Sylvain Pointeau wrote:
> for a general case, I would need to persist the counter into a table (for a
> specified sequencer) and doing the nextval inside a mutex lock
> 
> Is it possible to insert/ select from a UDF if the statements are
> serialized? or should I use the virtual tables? (should we store the
> sequencers in a different DB?)

In SQLite3 all writes in transactions are serialized.  No locks needed
because there's just one big lock around the entire DB.  I recommend you
read up on SQLite3's transactions and locking.

A next_serial() UDF would basically be a C-coded (or Perl, or whatever)
function that uses the SQLite3 API to first run an UPDATE on the
sequence then a SELECT to get the now-next value, and would return that.

If you use the INSTEAD OF trigger approach, then the same applies,
except that the triggers will be SQL-coded (which is nice, IMO).

This is all perfectly safe in the current SQLite3 concurrency model
(just one writer at a time).  I don't think SQLite3's write concurrency
will ever get better, but I suppose one never knows!

If you were using an RDBMS with higher write concurrency then you'd need
to be more careful and arrange for synchronization.  Usually such
RDBMSes provide builtin next_serial()-style functions anyways.

As to your last question, I'd put the sequences table in the same DB,
unless you need to attach multiple DBs and have all of them share
sequences, in which case I'd make a DB just for the sequences, or else
put them in the main DB.

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]

Thank you Nico!

for a general case, I would need to persist the counter into a table (for a
specified sequencer) and doing the nextval inside a mutex lock

Is it possible to insert/ select from a UDF if the statements are
serialized? or should I use the virtual tables? (should we store the
sequencers in a different DB?)



Le mer. 2 août 2017 à 19:46, Nico Williams  a écrit :

> On Wed, Aug 02, 2017 at 05:41:38PM +0100, Simon Slavin wrote:
> > On 2 Aug 2017, at 5:35pm, Nico Williams  wrote:
> > > On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote:
> > >> Can someone explain ?
> > >
> > > They make it easy to have N tables with the same rowid namespace, for
> > > example.  So you could do something like:
> > >
> > >  ...
> >
> > Okay, I understand that.  Thanks, Nico.
> >
> > So the reason I didn’t understand the need for sequences is that
> > someone who had been using SQLite for a long time would never come up
> > with the concept.  It’s just not needed.  You’d just create individual
> > tables, each with their own AUTOINC key as normal, then key into them
> > using a VIEW or FOREIGN KEY.  The problem solved by SEQUENCEs never
> > arises.
>
> Sometimes you have external constraints on your schema and so don't have
> the freedom to do what you propose.  I guess it can happen that you've
> never had to experience that, but I have.
>
> > This gives us a problem with Peter’s original question, because it
> > seems unlike that implmenting sequences with SQLite is common enough
> > that we have a ready solution.
>
> Well, if no one's done the UDF thing, maybe OP can do it and maybe open
> source it.  I mean, it's pretty simple.  Alternatively OP can use
> triggers as discussed.
>
> Another thing I often do with SQLite3 is to create VIEWs with INSTEAD OF
> triggers that do the right thing.  Something like:
>
>   CREATE TABLE sequences (...); -- see previous post
>   CREATE TABLE real_thing (...);
>   CREATE VIEW  thing AS
>   SELECT * FROM real_thing;
>   CREATE TRIGGER thing_ins INSTEAD OF INSERT ON thing
>   FOR EACH ROW
>   BEGIN
> UPDATE sequences
> SET last = last + 1
> WHERE name = 'foo';
> INSERT INTO real_thing (...)
> SELECT (SELECT last FROM sequences WHERE name = 'foo'),
>NEW.column_1, ...;
>   END;
>   CREATE TRIGGER thing_upd INSTEAD OF UPDATE ON thing
>   FOR EACH ROW
>   BEGIN
> UPDATE real_thing
> SET ...
> WHERE ...;
>   END;
>   CREATE TRIGGER thing_del INSTEAD OF DELETE ON thing
>   FOR EACH ROW
>   BEGIN
> DELETE FROM real_thing
> WHERE ...;
>   END;
>
> I often basically use VIEWs in SQLite3 as a form of SQL-coded stored
> procedures, with NEW.* / OLD.* being the "function"'s arguments.
>
> This can get very verbose and somewhat tedious though.  I've used SQL to
> generate all of these VIEWs and INSTEAD OF TRIGGERs in order to reduce
> the amount of code to hand-maintain.  (I also do similar things with PG:
> https://github.com/twosigma/postgresql-contrib .)
>
> Since it's not possible to have an INSERT output a result, nor can you
> have a SELECT on such a VIEW have a side-effect, it's not possible to
> write a sequences VIEW that you can then use like this:
>
>   -- Doesn't work because SELECT on VIEWs can't have side-effects (not
>   -- without side-effect-having UDFs in the VIEW's definition):
>   INSERT INTO real_thing (...)
>   SELECT (SELECT next FROM seq WHERE name = 'foo'), ...;
>
> Without a next_serial() UDF one has to resort to the triggers discussed
> earlier.  So there's a limit to what one can do with this technique if
> you also appreciate and want elegance (which I do).
>
> An actual next_serial()-type function would be very nice.
>
> In short, SQLite3 basically has stored procedures.  It's just missing
> syntactic sugar for them.
>
> It really does help to not be afraid of using SQL as a programming
> language.  Many are allergic to SQL as a programming language -- those
> poor souls often end up using ORMs and pay the price for it later.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Nico Williams]

On Wed, Aug 02, 2017 at 05:41:38PM +0100, Simon Slavin wrote:
> On 2 Aug 2017, at 5:35pm, Nico Williams  wrote:
> > On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote:
> >> Can someone explain ?
> > 
> > They make it easy to have N tables with the same rowid namespace, for
> > example.  So you could do something like:
> > 
> >  ...
> 
> Okay, I understand that.  Thanks, Nico.
> 
> So the reason I didn’t understand the need for sequences is that
> someone who had been using SQLite for a long time would never come up
> with the concept.  It’s just not needed.  You’d just create individual
> tables, each with their own AUTOINC key as normal, then key into them
> using a VIEW or FOREIGN KEY.  The problem solved by SEQUENCEs never
> arises.

Sometimes you have external constraints on your schema and so don't have
the freedom to do what you propose.  I guess it can happen that you've
never had to experience that, but I have.

> This gives us a problem with Peter’s original question, because it
> seems unlike that implmenting sequences with SQLite is common enough
> that we have a ready solution.

Well, if no one's done the UDF thing, maybe OP can do it and maybe open
source it.  I mean, it's pretty simple.  Alternatively OP can use
triggers as discussed.

Another thing I often do with SQLite3 is to create VIEWs with INSTEAD OF
triggers that do the right thing.  Something like:

  CREATE TABLE sequences (...); -- see previous post
  CREATE TABLE real_thing (...);
  CREATE VIEW  thing AS
  SELECT * FROM real_thing;
  CREATE TRIGGER thing_ins INSTEAD OF INSERT ON thing
  FOR EACH ROW
  BEGIN
UPDATE sequences
SET last = last + 1
WHERE name = 'foo';
INSERT INTO real_thing (...)
SELECT (SELECT last FROM sequences WHERE name = 'foo'),
   NEW.column_1, ...;
  END;
  CREATE TRIGGER thing_upd INSTEAD OF UPDATE ON thing
  FOR EACH ROW
  BEGIN
UPDATE real_thing
SET ...
WHERE ...;
  END;
  CREATE TRIGGER thing_del INSTEAD OF DELETE ON thing
  FOR EACH ROW
  BEGIN
DELETE FROM real_thing
WHERE ...;
  END;

I often basically use VIEWs in SQLite3 as a form of SQL-coded stored
procedures, with NEW.* / OLD.* being the "function"'s arguments.

This can get very verbose and somewhat tedious though.  I've used SQL to
generate all of these VIEWs and INSTEAD OF TRIGGERs in order to reduce
the amount of code to hand-maintain.  (I also do similar things with PG:
https://github.com/twosigma/postgresql-contrib .)

Since it's not possible to have an INSERT output a result, nor can you
have a SELECT on such a VIEW have a side-effect, it's not possible to
write a sequences VIEW that you can then use like this:

  -- Doesn't work because SELECT on VIEWs can't have side-effects (not
  -- without side-effect-having UDFs in the VIEW's definition):
  INSERT INTO real_thing (...)
  SELECT (SELECT next FROM seq WHERE name = 'foo'), ...;

Without a next_serial() UDF one has to resort to the triggers discussed
earlier.  So there's a limit to what one can do with this technique if
you also appreciate and want elegance (which I do).

An actual next_serial()-type function would be very nice.

In short, SQLite3 basically has stored procedures.  It's just missing
syntactic sugar for them.

It really does help to not be afraid of using SQL as a programming
language.  Many are allergic to SQL as a programming language -- those
poor souls often end up using ORMs and pay the price for it later.

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 Wed, Aug 02, 2017 at 07:48:52PM +0300, Alek Paunov wrote:
> On 2017-08-02 18:23, Sylvain Pointeau wrote:
> ...
> >
> >CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;
> >
> >insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
> >'other info')
> >
> 
> BTW, your request is somewhat related with the brand new union-vtab SQLite
> extension [1] (for optimized union view of identical tables) - if we have
> the basic PostreSQL nextval in SQLite, the following pattern would become
> possible:

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

PostgreSQL-style sequence support would be much more general.

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

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

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

Re: [sqlite] sequencer

[Alek Paunov]

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


CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

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



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


create sequence doc_id start with 40;
create table doc(doc_id integer primary key default nextval('doc_id'), doc);
create table inbox_doc(doc_id integer primary key default 
nextval('doc_id'), doc);


create virtual table temp.doc using unionvtab(
'select doc_id, doc from doc union all select doc_id, doc from 
inbox_doc'
);
select doc from temp.doc where doc_id = 42;

So, maybe nextval is already on the roadmap ;-)

Kind Regards,
Alek

[1] https://sqlite.org/unionvtab.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Simon Slavin]

On 2 Aug 2017, at 5:35pm, Nico Williams  wrote:

> On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote:
>> Can someone explain ?
> 
> Think of sequences as non-deterministic functions.  (They are actually
> deterministic, but using hidden state, so from the engine's perspective
> they are non-deterministic.)
> 
> They make it easy to have N tables with the same rowid namespace, for
> example.  So you could do something like:
> 
>  CREATE SEQUENCE foo START WITH 0 INCREMENT BY 5;
>  CREATE TABLE t1 (rowid integer primary key default (next_serial(foo)), ...);
>  CREATE TABLE t2 (rowid integer primary key default (next_serial(foo)), ...);
>  CREATE TABLE t3 (rowid integer primary key default (next_serial(foo)), ...);

Okay, I understand that.  Thanks, Nico.

So the reason I didn’t understand the need for sequences is that someone who 
had been using SQLite for a long time would never come up with the concept.  
It’s just not needed.  You’d just create individual tables, each with their own 
AUTOINC key as normal, then key into them using a VIEW or FOREIGN KEY.  The 
problem solved by SEQUENCEs never arises.

This gives us a problem with Peter’s original question, because it seems unlike 
that implmenting sequences with SQLite is common enough that we have a ready 
solution.

Simon.
___
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 Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote:
> On 2 Aug 2017, at 4:54pm, Peter Da Silva  
> wrote:
> > Can’t you do the same basic logic then use (SELECT value FROM
> > super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
> 
> Actually, I don’t understand how sequences are superior to normal use
> of an AUTOINC column.  Can someone explain ?

Think of sequences as non-deterministic functions.  (They are actually
deterministic, but using hidden state, so from the engine's perspective
they are non-deterministic.)

They make it easy to have N tables with the same rowid namespace, for
example.  So you could do something like:

  CREATE SEQUENCE foo START WITH 0 INCREMENT BY 5;
  CREATE TABLE t1 (rowid integer primary key default (next_serial(foo)), ...);
  CREATE TABLE t2 (rowid integer primary key default (next_serial(foo)), ...);
  CREATE TABLE t3 (rowid integer primary key default (next_serial(foo)), ...);

and have those three tables share a rowid namespace -- there will be no
collisions between them.  The way one might handle this use case in
SQLite3 is to create one master table with autoincrement and then have
foreign key references onto it from the others; you'd insert into the
master table first then into the referring table using
last_insert_rowid() or a sub-query that returns the same.

There are other workarounds too, as others have mentioned (triggers,
...).

One could always create a UDF that does an UPDATE and SELECT behind the
scenes.  Then one would create a table like so:

  CREATE TABLE sequences (name TEXT PRIMARY KEY,
  start INTEGER DEFAULT (0),
  increment INTEGER DEFAULT (1),
  last INTEGER DEFAULT (0)) WITHOUT ROWID;

and one would create sequences by INSERTing rows into that table, and
the UDF would just do the obvious

  UPDATE sequences SET last = last + 1 WHERE name = _name_argument;

then

  SELECT last FROM sequences WHERE name = _name_argument;

and return that.

Such a UDF probably exists as open source soemwhere, so OP should look
around for it.

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]

your solution is only for one row as opposed to my example creating many
rows

On Wed, 2 Aug 2017 at 18:27, Peter Da Silva 
wrote:

> By “the same thing” I mean:
>
> BEGIN;
> something like the stuff I had in my original post where it’s incrementing
> the sequence;
> your statement where you’re using the sequence, except using something
> like (SELECT value FROM super_sequences WHERE id=’SEQ_1’);
> COMMIT;
>
> On 8/2/17, 11:20 AM, "sqlite-users on behalf of Sylvain Pointeau" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> sylvain.point...@gmail.com> wrote:
>
> On Wed, Aug 2, 2017 at 5:54 PM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
>
> > Can’t you do the same basic logic then use (SELECT value FROM
> > super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
> >
> >
> > insert into mytable (MY_NO, MY_INFO)
> > SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
> > ;
> >
> >
> no because nextval also increment the sequence, as opposed to the
> (SELECT
> value FROM super_sequences WHERE id=’SEQ_1’) where it only reads the
> value
> ___
> 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

[Peter Da Silva]

By “the same thing” I mean:

BEGIN;
something like the stuff I had in my original post where it’s incrementing the 
sequence;
your statement where you’re using the sequence, except using something like 
(SELECT value FROM super_sequences WHERE id=’SEQ_1’);
COMMIT;

On 8/2/17, 11:20 AM, "sqlite-users on behalf of Sylvain Pointeau" 
 wrote:

On Wed, Aug 2, 2017 at 5:54 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Can’t you do the same basic logic then use (SELECT value FROM
> super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
>
> insert into mytable (MY_NO, MY_INFO)
> SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
> ;
>
>
no because nextval also increment the sequence, as opposed to the (SELECT
value FROM super_sequences WHERE id=’SEQ_1’) where it only reads the value
___
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 Wed, Aug 2, 2017 at 5:54 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Can’t you do the same basic logic then use (SELECT value FROM
> super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
>
> insert into mytable (MY_NO, MY_INFO)
> SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
> ;
>
>
no because nextval also increment the sequence, as opposed to the (SELECT
value FROM super_sequences WHERE id=’SEQ_1’) where it only reads the value
___
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 Wed, Aug 2, 2017 at 5:56 PM, Simon Slavin  wrote:

>
>
> On 2 Aug 2017, at 4:54pm, Peter Da Silva 
> wrote:
>
> > Can’t you do the same basic logic then use (SELECT value FROM
> super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
> Actually, I don’t understand how sequences are superior to normal use of
> an AUTOINC column.  Can someone explain ?
>
>
Yes I can.

I need to use special number that I specified the start and increment.

for instance I have a entity with a lower number and upper number, I create
then a sequence like

CREATE SEQUENCE IF NOT EXISTS SEQ_RANGE START WITH 100 INCREMENT BY 100 ;

then I create many entities like:

insert into MYENTITY(LowerNumber,UpperNumber)
select SEQ_RANGE.currval+1, SEQ_RANGE.nextval
from MY_REF_TABLE
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Simon Slavin]

On 2 Aug 2017, at 4:54pm, Peter Da Silva  wrote:

> Can’t you do the same basic logic then use (SELECT value FROM super_sequences 
> WHERE id=’SEQ_1’) instead of SEQ_1.nextval?

Actually, I don’t understand how sequences are superior to normal use of an 
AUTOINC column.  Can someone explain ?

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

Re: [sqlite] sequencer

[Peter Da Silva]

Can’t you do the same basic logic then use (SELECT value FROM super_sequences 
WHERE id=’SEQ_1’) instead of SEQ_1.nextval?

On 8/2/17, 10:48 AM, "sqlite-users on behalf of Sylvain Pointeau" 
 wrote:

On Wed, Aug 2, 2017 at 5:43 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Hence the suggestion to script a transaction. For example, in pseudocode:
>
> BEGIN;
> SELECT value, increment from super_sequences where table = :table and
> column = :column;
> INSERT INTO :table (id, other, fields) VALUES :(value+increment,other,
> values);
> UPDATE super_sequences set value = :(value + increment) WHERE table =
> :table and column = :column;
> COMMIT;
>

ok but it does not work for insert like

insert into mytable (MY_NO, MY_INFO)
SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
;
___
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 Wed, Aug 2, 2017 at 5:43 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Hence the suggestion to script a transaction. For example, in pseudocode:
>
> BEGIN;
> SELECT value, increment from super_sequences where table = :table and
> column = :column;
> INSERT INTO :table (id, other, fields) VALUES :(value+increment,other,
> values);
> UPDATE super_sequences set value = :(value + increment) WHERE table =
> :table and column = :column;
> COMMIT;
>

ok but it does not work for insert like

insert into mytable (MY_NO, MY_INFO)
SELECT  SEQ_1.nextval,  a.INFO  FROM myothertable a
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Simon Slavin]

On 2 Aug 2017, at 4:37pm, Sylvain Pointeau  wrote:

> Yes I am aware of autoinc but this is not what I can use, because I need to
> specify exactly the sequence (as start number and increment). Additionally
> I can have tables having 2 or 3 fields needing a specified sequence number.

Assuming I understand what you’re asking for, Here are four ways to do this.

First is to set up a TRIGGER which works out the next number in the sequence 
for you.  The TRIGGER would work out MAX(keycol), add the increment to it, and 
use the result to set the value on the new row.

Second is to set a DEFAULT as part of the column definition which does the same 
thing.  (This depends on being able to use MAX() as part of DEFAULT.  I’m 
currently thinking that this is probably not allowed.)

Third is to allow SQLite to set its autoinc value as normal, and calculate the 
key value you want from that and store that in a different column.  You can set 
this key value using a TRIGGER or DEFAULT definition.

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

Re: [sqlite] sequencer

[Peter Da Silva]

Hence the suggestion to script a transaction. For example, in pseudocode:

BEGIN;
SELECT value, increment from super_sequences where table = :table and column = 
:column;
INSERT INTO :table (id, other, fields) VALUES :(value+increment,other,values);
UPDATE super_sequences set value = :(value + increment) WHERE table = :table 
and column = :column;
COMMIT;

On 8/2/17, 10:37 AM, "sqlite-users on behalf of Sylvain Pointeau" 
 wrote:

On Wed, Aug 2, 2017 at 5:27 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Have a look at https://sqlite.org/autoinc.html
>

Yes I am aware of autoinc but this is not what I can use, because I need to
specify exactly the sequence (as start number and increment). Additionally
I can have tables having 2 or 3 fields needing a specified sequence number.
___
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 Wed, Aug 2, 2017 at 5:27 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Have a look at https://sqlite.org/autoinc.html
>

Yes I am aware of autoinc but this is not what I can use, because I need to
specify exactly the sequence (as start number and increment). Additionally
I can have tables having 2 or 3 fields needing a specified sequence number.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequencer

[Peter Da Silva]

Have a look at https://sqlite.org/autoinc.html

Also keep in mind that latency for SQLITE is low, since it’s not client-server, 
so you can script a transaction that has any sequence behavior you want with 
similar overhead to having SQLITE implement the sequence for you.

On 8/2/17, 10:23 AM, "sqlite-users on behalf of Sylvain Pointeau" 
 wrote:

Dear all,

I am currently using H2 and I use sequencers like:

CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

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

I would like to move to sqlite, but what would be your advice for the
sequencer values?
Is it possible at all?

Best regards,
Sylvain
___
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] sequencer

[Sylvain Pointeau]

Dear all,

I am currently using H2 and I use sequencers like:

CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;

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

I would like to move to sqlite, but what would be your advice for the
sequencer values?
Is it possible at all?

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