Re: [sqlite] sqlite3_prepare16_v3 and prepFlags

[Nico Williams]

On Wed, Aug 02, 2017 at 11:01:07PM +0100, Bart Smissaert wrote:
> Using 3.20.0 now on Windows and wonder when exactly I should use the
> SQLITE_PREPARE_PERSISTENT
> 
> flag
> instead of a zero. I have tried both options
> with a plain select statement producing some 10 rows. Both worked and
> SQLITE_PREPARE_PERSISTENT
> 
> seemed
> a bit faster. Are there any clear guidelines
> when to use either option?
> Note I am not using FTS3, FTS5 or the R-Tree extension.

I thought the docs were clear: if you'll be executing the statement
repeatedly, then use SQLITE_PREPARE_PERSISTENT.

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

[sqlite] sqlite3_prepare16_v3 and prepFlags

[Bart Smissaert]

Using 3.20.0 now on Windows and wonder when exactly I should use the
SQLITE_PREPARE_PERSISTENT

flag
instead of a zero. I have tried both options
with a plain select statement producing some 10 rows. Both worked and
SQLITE_PREPARE_PERSISTENT

seemed
a bit faster. Are there any clear guidelines
when to use either option?
Note I am not using FTS3, FTS5 or the R-Tree extension.

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

Re: [sqlite] Pointers in 3.20.0 are great! Dreaming of better

[Nico Williams]

Another thing is that with serializers the shell could automatically
serialize on output if desired.

Also, the callbacks could be attached to a "type", and perhaps that way
you could preserve the APIs you already added for pointer values.
___
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

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

[Matt Chambers]

load_extension() has the very sensible behavior of:
> So for example, if "samplelib" cannot be loaded, then names like
> "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried
> also.

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



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Feature-request-check-for-lib-prefix-for-load-extension-tp96658.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users