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 fo
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
>
> 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 b
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
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
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 g
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<1000
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
s
Gesendet: Freitag, 04. August 2017 21:16
An: Sylvain Pointeau
Cc: SQLite mailing list
Betreff: Re: [sqlite] sequencer
On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote:
> I programmed the currval using a temp table, but the performance
> dropped slightly
>
> sqlite>
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') f
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 ta
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):
>
>
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 S
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 t
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
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
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
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, &stmt, 0);
> >
> > Should this init function re-init
> 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
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 ord
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 ord
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. Belo
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
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(con
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 S
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
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.sq
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
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 t
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')
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 i
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
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
Bet
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
> > thin
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.
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
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
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 sh
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
sequenc
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. S
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 i
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 optimize
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 ar
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 s
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 usi
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-user
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
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 superio
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 ?
Si
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:
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,
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
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 + incr
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 ha
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, "sql
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
56 matches
Mail list logo
