Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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,

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

> 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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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 =

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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:

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

ok thank you for the confirmation, I will try implementing it in a dll using UD functions and put it on github. Le mer. 2 août 2017 à 20:56, Richard Hipp a écrit : > On 8/2/17, Sylvain Pointeau wrote: > > > > is it really possible to make an update

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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 >

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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,

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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,

Re: [sqlite] sequencer

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,

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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 +

Re: [sqlite] sequencer

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

Re: [sqlite] sequencer

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

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