Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Eduardo Morras]

On Fri, 16 Mar 2018 11:37:24 -0400
Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite
> faster.
> 
> How many tables in your schema(s) use AUTOINCREMENT?

Within all my projects 4 (very old projects)

I don't use it on current projects.

> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
> 
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement |
> wc -l
> 
> Private email to me is fine.  Thanks for participating in this survey!
> -- 
> D. Richard Hipp
> d...@sqlite.org


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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[jungle Boogie]

On 16 March 2018 at 08:37, Richard Hipp  wrote:
> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Chris Locke]

I see - thanks Paul.  I misunderstood.  Thanks for your detailed
explanation.


Chris

On Wed, Mar 21, 2018 at 9:13 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> Actually it is totally different Chris
>
> >  I read that - but my point was more that some people seem to think that
> an
> > int primary key can be auto incrementing, it can't.
>
> an INT primary key cannot be autoincrementing
>
> An INTEGER primary key and an INTEGER primary key autoincrement work in
> essentially the same way. i.e. if you insert a row and do not specifically
> assign a value to the pk (i.e. you assign NULL) the value assigned will
> usually be one more than last pk used.
>
> if you have an INT primary key and add a new row with no value assigned to
> the PK then null will be stored (all null values are treated as unique in
> SQLite and so as far as the PK is concerned all rows are different).
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (id int primary key, data text);
> sqlite> insert into test (data) values('row 1');
> sqlite> insert into test (data) values('row 2');
> sqlite> select id, data from test;
>   |row 1
>   |row 2
>
> of course the rowid is still there hidden behind the scenes and you can
> access it with
>
> sqlite> select rowid, id, data from test;
> 1|  |row 1
> 2|  |row 2
>
> but if you want to use the rowid as the PK then you should probably use an
> INTEGER pk so it becomes an alias for the rowid in the first place.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 20 March 2018 at 16:44, Chris Locke  wrote:
>
> > >  some people seem to think that an int primary key can be auto
> > incrementing, it can't
> >
> > But it works in the same way  sort of.  Its auto incrementing, with
> the
> > caveat that if the last row is deleted, the previous number will be used
> > again.  Depending on the database schema, this may or may not cause
> issues.
> >
> >
> > Thanks,
> > Chris
> >
> >
> > On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> > sandersonforens...@gmail.com> wrote:
> >
> > >  I read that - but my point was more that some people seem to think
> that
> > an
> > > int primary key can be auto incrementing, it can't.
> > >
> > >
> > > SQLite version 3.18.0 2017-03-28 18:48:43
> > > Enter ".help" for usage hints.
> > > Connected to a transient in-memory database.
> > > Use ".open FILENAME" to reopen on a persistent database.
> > > sqlite> create table test (id integer primary key autoincrement);
> > > sqlite> create table test2 (id int primary key autoincrement);
> > > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > > sqlite>
> > >
> > > Paul
> > > www.sandersonforensics.com
> > > skype: r3scue193
> > > twitter: @sandersonforens
> > > Tel +44 (0)1326 572786
> > > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > > Forensic-Toolkit
> > > -Forensic Toolkit for SQLite
> > > email from a work address for a fully functional demo licence
> > >
> > > On 20 March 2018 at 08:48, R Smith  wrote:
> > >
> > > >
> > > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > > >
> > > >> Autoincrement can ONLY be used with an integer primary key
> > > >>
> > > >
> > > > I think Peter's shouting is more about the inability to distinguish
> via
> > > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> > both
> > > > of which are of course integer and can be auto-incrementing, but only
> > one
> > > > of which is an alias for rowid.
> > > >
> > > >
> > > >
> > > > ___
> > > > 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
> >
> ___
> 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] How many AUTOINCREMENT tables are in your schema?

[Dominique Devienne]

On Thu, Mar 22, 2018 at 3:22 PM, Richard Hipp  wrote:

> On 3/22/18, Dominique Devienne  wrote:
> >
> > Hi Richard. Is 8.d from https://www.sqlite.org/draft/
> releaselog/current.html
> > the result of this inquiry?
>
> No.  I just happened to notice the inefficiency while I was working on 8d.
>
> > And is there a chance the "some kind of indexed lookup" you mention above
> > is likely to land in the future?
>
> That is still unclear, but probably not.
>
> > If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables,
> are
> > we really full-scanning
> > up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your
> > insights into this. --DD
>
> No.  You are only doing a full table scan on the 117 entries of the
> sqlite_sequence table.  And as all 117 entries probably all fit on the
> same page, adding an index probably won't speed things up any.
>

What I meant was that each row-insert incurs a sqlite_sequence full-scan,
and thus inserting 1M rows logically incurs the multiplication of both
cardinality.

I already guessed sqlite_sequence is cached, since "extremely hot", but
that's
still a lot of rows even if cached, even more so since sqlite_sequence also
needs
to be *written* to, not just read from, to record the new max rowid.


> Another idea is to implement an in-memory cache so that if you do
> multiple inserts into an AUTOINCREMENT table, it only does the scan of
> sqlite_sequence once.
>
> In private communications with some groups that have a lot of
> AUTOINCREMENT tables and who performance sensitive and who heavily
> instrument their code, nobody has noticed any performance issues
> associated with scanning the sqlite_sequence table.  For that reason,
> we probably are not going to take any action on this right now.  But I
> will hold open the possibility of enhancing the sqlite_sequence lookup
> at some point in the future if somebody (especially a client) notices
> the potential for a performance benefit.


Thanks, this is reassuring. Many thanks for the quick update. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Richard Hipp]

On 3/22/18, Dominique Devienne  wrote:
>
> Hi Richard. Is 8.d from https://www.sqlite.org/draft/releaselog/current.html
> the result of this inquiry?

No.  I just happened to notice the inefficiency while I was working on 8d.

> And is there a chance the "some kind of indexed lookup" you mention above
> is likely to land in the future?

That is still unclear, but probably not.

>
> If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables, are
> we really full-scanning
> up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your
> insights into this. --DD

No.  You are only doing a full table scan on the 117 entries of the
sqlite_sequence table.  And as all 117 entries probably all fit on the
same page, adding an index probably won't speed things up any.

Another idea is to implement an in-memory cache so that if you do
multiple inserts into an AUTOINCREMENT table, it only does the scan of
sqlite_sequence once.

In private communications with some groups that have a lot of
AUTOINCREMENT tables and who performance sensitive and who heavily
instrument their code, nobody has noticed any performance issues
associated with scanning the sqlite_sequence table.  For that reason,
we probably are not going to take any action on this right now.  But I
will hold open the possibility of enhancing the sqlite_sequence lookup
at some point in the future if somebody (especially a client) notices
the potential for a performance benefit.

-- 
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] How many AUTOINCREMENT tables are in your schema?

[Dominique Devienne]

On Sat, Mar 17, 2018 at 1:28 AM, Richard Hipp  wrote:

> On 3/16/18, R Smith  wrote:
> > It's interesting to fathom what hypothesis is being tested with this
> pole...
>
> INSERT operations on a table with AUTOINCREMENT do a full-table scan
> against the sqlite_sequence table.  I'm wondering if it is worthing
> adding extra logic to do some kind of indexed lookup.  For a schema
> with just a few AUTOINCREMENT tables, there is really no point in
> trying to use an index.  But if you have hundreds of AUTOINCREMENT
> tables, some kind of index might be worthwhile.
>

Hi Richard. Is 8.d from https://www.sqlite.org/draft/releaselog/current.html
the result of this inquiry?
And is there a chance the "some kind of indexed lookup" you mention above
is likely to land in the future?

I'm obviously asking because we (my employer) is the heaviest (publicly
reported) user of AUTOINCREMENT,
and I wonder if we're not incurring unbeknownst until now insert penalties
from those full scans.

What's the threshold you estimate (or measured) in AUTOINCREMENT table
count for the full-scan O(N)
to be slower than an hypothetical indexed-lookup O(log N) of
sqlite_sequence?

If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables, are
we really full-scanning
up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your
insights into this. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Toby Dickenson]

0 in all schemas

On 21 March 2018 at 09:22, R Smith  wrote:
>
> On 2018/03/21 11:13 AM, Paul Sanderson wrote:
>>
>> Actually it is totally different Chris
>
>
> Indeed, and thank you for highlighting this.
>
> I'm so used to putting down CREATE TABLE t (id INTEGER PRIMARY KEY, f2,
> f3,... ) and seeing the id increment automatically when needed, I lost sight
> of the fact that this HAS to be the rowid alias to work (i.e. INTEGER and
> not INT).
>
> This little fact probably lends slightly more weight to Peter's request for
> a way to tell INTEGER PRIMARY KEY apart from INT PRIMARY KEY in a way that
> doesn't require a schema parse.
>
>
>
> ___
> 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] How many AUTOINCREMENT tables are in your schema?

[R Smith]

On 2018/03/21 11:13 AM, Paul Sanderson wrote:

Actually it is totally different Chris


Indeed, and thank you for highlighting this.

I'm so used to putting down CREATE TABLE t (id INTEGER PRIMARY KEY, f2, 
f3,... ) and seeing the id increment automatically when needed, I lost 
sight of the fact that this HAS to be the rowid alias to work (i.e. 
INTEGER and not INT).


This little fact probably lends slightly more weight to Peter's request 
for a way to tell INTEGER PRIMARY KEY apart from INT PRIMARY KEY in a 
way that doesn't require a schema parse.



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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Paul Sanderson]

Actually it is totally different Chris

>  I read that - but my point was more that some people seem to think that
an
> int primary key can be auto incrementing, it can't.

an INT primary key cannot be autoincrementing

An INTEGER primary key and an INTEGER primary key autoincrement work in
essentially the same way. i.e. if you insert a row and do not specifically
assign a value to the pk (i.e. you assign NULL) the value assigned will
usually be one more than last pk used.

if you have an INT primary key and add a new row with no value assigned to
the PK then null will be stored (all null values are treated as unique in
SQLite and so as far as the PK is concerned all rows are different).

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (id int primary key, data text);
sqlite> insert into test (data) values('row 1');
sqlite> insert into test (data) values('row 2');
sqlite> select id, data from test;
  |row 1
  |row 2

of course the rowid is still there hidden behind the scenes and you can
access it with

sqlite> select rowid, id, data from test;
1|  |row 1
2|  |row 2

but if you want to use the rowid as the PK then you should probably use an
INTEGER pk so it becomes an alias for the rowid in the first place.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 20 March 2018 at 16:44, Chris Locke  wrote:

> >  some people seem to think that an int primary key can be auto
> incrementing, it can't
>
> But it works in the same way  sort of.  Its auto incrementing, with the
> caveat that if the last row is deleted, the previous number will be used
> again.  Depending on the database schema, this may or may not cause issues.
>
>
> Thanks,
> Chris
>
>
> On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
> >  I read that - but my point was more that some people seem to think that
> an
> > int primary key can be auto incrementing, it can't.
> >
> >
> > SQLite version 3.18.0 2017-03-28 18:48:43
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table test (id integer primary key autoincrement);
> > sqlite> create table test2 (id int primary key autoincrement);
> > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > sqlite>
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > Forensic-Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 20 March 2018 at 08:48, R Smith  wrote:
> >
> > >
> > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > >
> > >> Autoincrement can ONLY be used with an integer primary key
> > >>
> > >
> > > I think Peter's shouting is more about the inability to distinguish via
> > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> both
> > > of which are of course integer and can be auto-incrementing, but only
> one
> > > of which is an alias for rowid.
> > >
> > >
> > >
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Darko Volaric]

0

I roll my own.


> On Mar 16, 2018, at 4:37 PM, Richard Hipp  wrote:
> 
> This is a survey, the results of which will help us to make SQLite faster.
> 
> How many tables in your schema(s) use AUTOINCREMENT?
> 
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
> 
>   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
> 
> Private email to me is fine.  Thanks for participating in this survey!
> -- 
> 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] How many AUTOINCREMENT tables are in your schema?

[petern]

Regarding SQLite "next_val()", the following works with or without "NOT
NULL":

CREATE TABLE t(rowid INTEGER PRIMARY KEY NOT NULL);
INSERT INTO t VALUES (NULL),(NULL);
SELECT * FROM t;
--rowid
--1
--2
DELETE FROM t WHERE rowid=1;
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
--rowid
--2
--3

But these do not work at all:

CREATE TABLE t(rowid INT PRIMARY KEY);
INSERT INTO t VALUES (NULL),(NULL);
SELECT * FROM t;
--rowid
--
--

CREATE TABLE t(rowid INT PRIMARY KEY NOT NULL);
INSERT INTO t VALUES (NULL),(NULL);
--Error: NOT NULL constraint failed: t.rowid

CREATE TABLE t(rowid INTEGER PRIMARY KEY) WITHOUT ROWID;
INSERT INTO t VALUES (NULL),(NULL);
--Error: NOT NULL constraint failed: t.rowid


Peter


On Tue, Mar 20, 2018 at 9:44 AM, Chris Locke 
wrote:

> >  some people seem to think that an int primary key can be auto
> incrementing, it can't
>
> But it works in the same way  sort of.  Its auto incrementing, with the
> caveat that if the last row is deleted, the previous number will be used
> again.  Depending on the database schema, this may or may not cause issues.
>
>
> Thanks,
> Chris
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Chris Locke]

>  some people seem to think that an int primary key can be auto
incrementing, it can't

But it works in the same way  sort of.  Its auto incrementing, with the
caveat that if the last row is deleted, the previous number will be used
again.  Depending on the database schema, this may or may not cause issues.


Thanks,
Chris


On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

>  I read that - but my point was more that some people seem to think that an
> int primary key can be auto incrementing, it can't.
>
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test (id integer primary key autoincrement);
> sqlite> create table test2 (id int primary key autoincrement);
> Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> sqlite>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 20 March 2018 at 08:48, R Smith  wrote:
>
> >
> > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> >
> >> Autoincrement can ONLY be used with an integer primary key
> >>
> >
> > I think Peter's shouting is more about the inability to distinguish via
> > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY, both
> > of which are of course integer and can be auto-incrementing, but only one
> > of which is an alias for rowid.
> >
> >
> >
> > ___
> > 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] How many AUTOINCREMENT tables are in your schema?

[David Raymond]

select name
from sqlite_master
where
type = 'table'
and exists (
  select 1
  from pragma_table_info(sqlite_master.name)
  where pk > 0
)
and not exists (
  select 1
  from pragma_index_list(sqlite_master.name)
  where origin = 'pk'
)
order by name;

Tables which have a primary key, but no index created by a primary key clause.
I  that covers only integer primary key tables, but am not sure for 
things like extensions, virtual tables, etc.


SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .timer off

sqlite> .eqp off

sqlite> .mode column

sqlite> create table ipk (id integer primary key, foo, bar);

sqlite> create table intpk (id int primary key, foo, bar);

sqlite> create table nopk (id integer, foo, bar);

sqlite> create table cpk (id1 integer, id2 integer, foo, bar, primary key (id1, 
id2));

sqlite> create table wri (id integer primary key, foo, bar) without rowid;

sqlite> create table ipkd (id integer primary key desc, foo, bar);

sqlite> select name
   ...> from sqlite_master
   ...> where
   ...> type = 'table'
   ...> and exists (
   ...>   select 1 from pragma_table_info(sqlite_master.name)
   ...>   where pk > 0
   ...> )
   ...> and not exists (
   ...>   select 1
   ...>   from pragma_index_list(sqlite_master.name)
   ...>   where origin = 'pk'
   ...> )
   ...> order by name;
name
--
ipk

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Halasz
Sent: Tuesday, March 20, 2018 2:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

When needed I use a declared INTEGER PRIMARY KEY.
>
>
MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY
TO INCLUDE A SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY

YES I AM SHOUTING
___
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] How many AUTOINCREMENT tables are in your schema?

[Paul Sanderson]

 I read that - but my point was more that some people seem to think that an
int primary key can be auto incrementing, it can't.


SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (id integer primary key autoincrement);
sqlite> create table test2 (id int primary key autoincrement);
Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
sqlite>

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 20 March 2018 at 08:48, R Smith  wrote:

>
> On 2018/03/20 10:24 AM, Paul Sanderson wrote:
>
>> Autoincrement can ONLY be used with an integer primary key
>>
>
> I think Peter's shouting is more about the inability to distinguish via
> SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY, both
> of which are of course integer and can be auto-incrementing, but only one
> of which is an alias for rowid.
>
>
>
> ___
> 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] How many AUTOINCREMENT tables are in your schema?

[R Smith]

On 2018/03/20 10:24 AM, Paul Sanderson wrote:

Autoincrement can ONLY be used with an integer primary key


I think Peter's shouting is more about the inability to distinguish via 
SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY, 
both of which are of course integer and can be auto-incrementing, but 
only one of which is an alias for rowid.



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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Paul Sanderson]

Autoincrement can ONLY be used with an integer primary key

https://sqlite.org/autoinc.html

On Tue, 20 Mar 2018 at 06:50, Peter Halasz  wrote:

> When needed I use a declared INTEGER PRIMARY KEY.
> >
> >
> MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY
> TO INCLUDE A SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY
>
> YES I AM SHOUTING
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Peter Halasz]

When needed I use a declared INTEGER PRIMARY KEY.
>
>
MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY
TO INCLUDE A SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Jim Dodgen]

0

On Friday, March 16, 2018, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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
>


-- 

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Dominique Devienne]

On Fri, Mar 16, 2018 at 4:37 PM, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
>

sqlite3 schema-v.db  '.schema --indent' | grep -i autoincrement | wc -l
28
sqlite3 schema-i.db  '.schema --indent' | grep -i autoincrement | wc -l
117
sqlite3 schema-p.db  '.schema --indent' | grep -i autoincrement | wc -l
55
sqlite3 schema-g.db '.schema --indent' | grep -i autoincrement | wc -l
14

The 4 different main "data" schemas used by my employer's commercial
offering.
There are hundreds/thousands of those DBs at all our client sites. Note
that I'm
not directly associated to the design of those schemas, but I know the
SQLite-assigned
auto-inc'd rowid PK is heavily depended on, in the very SQLite-heavy
application-suite.

PS: And that new tables, for new data types, are regularly added each
release, so these
numbers will only increase, until an unlikely redesign to rely on Guid PKs
is made for example.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Wout Mertens]

1 very important one. I use it to insert events into a queue with a version
and the version has to monotonously increase.

However, if I had to maintain the known max manually, that wouldn't really
be a problem.

On Mon, Mar 19, 2018, 3:52 AM Rowan Worth,  wrote:

> 5/10
> 1/11
> 5/10
>
> Always in conjunction with INTEGER PRIMARY KEY fwiw.
>
>
> Also the following command is perhaps more portable:
>
> sqlite3 yourfile.db .schema | grep -ic autoincrement
>
> The sqlite3 shell on my system is too old to understand .schema --indent
> and doesn't output anything so there's always zero lines to count :)
>
> -Rowan
>
>
> On 16 March 2018 at 23:37, Richard Hipp  wrote:
>
> > This is a survey, the results of which will help us to make SQLite
> faster.
> >
> > How many tables in your schema(s) use AUTOINCREMENT?
> >
> > I just need a single integer, the count of uses of the AUTOINCREMENT
> > in your overall schema.  You might compute this using:
> >
> >sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
> >
> > Private email to me is fine.  Thanks for participating in this survey!
> > --
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Rowan Worth]

5/10
1/11
5/10

Always in conjunction with INTEGER PRIMARY KEY fwiw.


Also the following command is perhaps more portable:

sqlite3 yourfile.db .schema | grep -ic autoincrement

The sqlite3 shell on my system is too old to understand .schema --indent
and doesn't output anything so there's always zero lines to count :)

-Rowan


On 16 March 2018 at 23:37, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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] How many AUTOINCREMENT tables are in your schema?

[John G]

0

(Out of 3 databases. )

On 16 March 2018 at 15:37, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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] How many AUTOINCREMENT tables are in your schema?

[Cecil Westerhof]

2018-03-16 16:37 GMT+01:00 Richard Hipp :

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>

​A lot less as I thought:
0
5
0
0
0
0
0
0
0
2
0
2
0
0
0
1
0
0
0

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Luuk]

0


C:\Users\Luuk\AppData\Roaming\Thunderbird\Profiles\.default>sqlite3
global-messages-db.sqlite ".schema --indent" | findstr /i "autoincrement"

C:\Users\Luuk\AppData\Roaming\Thunderbird\Profiles\.default>


On 16-3-2018 16:37, Richard Hipp wrote:
> '.schema --indent'

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Stephen Chrzanowski]

Every table I create that is considered a primary resource, I use
auto-increment on exactly one field.  Any table I create that uses
auto-increment is used for internal use within the database, or, an
"object" to point an item in a tStringList to the database row.  This
integer is NEVER given outright to the user to manage.

On Fri, Mar 16, 2018 at 11:37 AM, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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] How many AUTOINCREMENT tables are in your schema?

[Rob Willett]

We have 17 in one database and 1 in another.

Rob

On 17 Mar 2018, at 11:43, x wrote:


0 in my first sqlite3 DB




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on 
behalf of Jean-Christophe Deschamps <j...@antichoc.net>

Sent: Saturday, March 17, 2018 9:04:22 AM
To: SQLite mailing list
Subject: Re: [sqlite] How many AUTOINCREMENT tables are in your 
schema?




How many tables in your schema(s) use AUTOINCREMENT?


Maybe a dozen uses in DB designs I made for my own use. Zero or very
few in designs made for others.

My use case of autoincrement is certainly somehow peculiar.

For some of DBs I use daily for managing my own business (I'm
self-employed) I didn't feel the need to write ad-hoc applications and
I only use a third-party SQLite manager (SQlite Expert is open 24/7).
Of course such use demands real care and a lot of fancy constraints,
triggers, add-on functions, external procedures, etc to be workable.
This isn't scalable nor usable by anyone else.

Several of my tables are best viewed/edited as LIFOs: the more recent
entries on top. For instance a table of orders, where you prefer 
recent

entries to be on top of the table when viewed by "natural" (ID) order.
To achieve that effect I use autoincrement and triggers which negate
the rowid alias at insert. The sqlite-sequence entry is handy to make
new row get an unused ID which, once negated, will show first when
viewing the table, albeit there is no more any non-negative ID in the
actual table.

I wouldn't have the use of autoincrement if my DB manager had a
settable ORDER BY clause for basic table viewing/editing.


___
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] How many AUTOINCREMENT tables are in your schema?

[x]

0 in my first sqlite3 DB




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Jean-Christophe Deschamps <j...@antichoc.net>
Sent: Saturday, March 17, 2018 9:04:22 AM
To: SQLite mailing list
Subject: Re: [sqlite] How many AUTOINCREMENT tables are in your schema?


>How many tables in your schema(s) use AUTOINCREMENT?

Maybe a dozen uses in DB designs I made for my own use. Zero or very
few in designs made for others.

My use case of autoincrement is certainly somehow peculiar.

For some of DBs I use daily for managing my own business (I'm
self-employed) I didn't feel the need to write ad-hoc applications and
I only use a third-party SQLite manager (SQlite Expert is open 24/7).
Of course such use demands real care and a lot of fancy constraints,
triggers, add-on functions, external procedures, etc to be workable.
This isn't scalable nor usable by anyone else.

Several of my tables are best viewed/edited as LIFOs: the more recent
entries on top. For instance a table of orders, where you prefer recent
entries to be on top of the table when viewed by "natural" (ID) order.
To achieve that effect I use autoincrement and triggers which negate
the rowid alias at insert. The sqlite-sequence entry is handy to make
new row get an unused ID which, once negated, will show first when
viewing the table, albeit there is no more any non-negative ID in the
actual table.

I wouldn't have the use of autoincrement if my DB manager had a
settable ORDER BY clause for basic table viewing/editing.


___
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] How many AUTOINCREMENT tables are in your schema?

[Jean-Christophe Deschamps]

How many tables in your schema(s) use AUTOINCREMENT?


Maybe a dozen uses in DB designs I made for my own use. Zero or very 
few in designs made for others.


My use case of autoincrement is certainly somehow peculiar.

For some of DBs I use daily for managing my own business (I'm 
self-employed) I didn't feel the need to write ad-hoc applications and 
I only use a third-party SQLite manager (SQlite Expert is open 24/7). 
Of course such use demands real care and a lot of fancy constraints, 
triggers, add-on functions, external procedures, etc to be workable. 
This isn't scalable nor usable by anyone else.


Several of my tables are best viewed/edited as LIFOs: the more recent 
entries on top. For instance a table of orders, where you prefer recent 
entries to be on top of the table when viewed by "natural" (ID) order. 
To achieve that effect I use autoincrement and triggers which negate 
the rowid alias at insert. The sqlite-sequence entry is handy to make 
new row get an unused ID which, once negated, will show first when 
viewing the table, albeit there is no more any non-negative ID in the 
actual table.


I wouldn't have the use of autoincrement if my DB manager had a 
settable ORDER BY clause for basic table viewing/editing.



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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Norman Dunbar]

5.

5 tables in my first SQLite database.


Cheers,
Norm.
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Richard Hipp]

On 3/16/18, R Smith  wrote:
> It's interesting to fathom what hypothesis is being tested with this pole...

INSERT operations on a table with AUTOINCREMENT do a full-table scan
against the sqlite_sequence table.  I'm wondering if it is worthing
adding extra logic to do some kind of indexed lookup.  For a schema
with just a few AUTOINCREMENT tables, there is really no point in
trying to use an index.  But if you have hundreds of AUTOINCREMENT
tables, some kind of index might be worthwhile.


-- 
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] How many AUTOINCREMENT tables are in your schema?

[R Smith]

It's interesting to fathom what hypothesis is being tested with this pole...

But it seems (from the on-forum replies anyway) that there are two very 
clear AUTOINCREMENT factions: Those who use it Everywhere, and those who 
use it Nowhere - which already is somewhat surprising to me.




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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Will Parsons]

On Friday, 16 Mar 2018 11:37 AM -0400, Richard Hipp wrote:
> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?

0

-- 
Will

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[petern]

0 AUTOINCREMENT columns.   A per column overload-able nextValue() interface
could have its uses though.

On Fri, Mar 16, 2018 at 8:37 AM, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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] How many AUTOINCREMENT tables are in your schema?

[Donald Shepherd]

100s - we use it as part of the definition of some dynamically created
tables that are dependent on the shape of the data we are receiving so can
end up with a large number of them.
On Sat, 17 Mar 2018 at 5:57 am, Doug Currie  wrote:

> 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] How many AUTOINCREMENT tables are in your schema?

[Doug Currie]

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Simon Slavin]

0

When needed I use a declared INTEGER PRIMARY KEY.

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Chris Locke]

0, across approx 20 databases, ranging from small 3 table schemas, to a
couple of ERP systems using 120+ tables.

Thanks,
Chris


On Fri, Mar 16, 2018 at 4:09 PM, R Smith  wrote:

> Across 8 production systems and about 120 SQLite DBs for us - Not a single
> AUTOINCREMENT - so   0 .
>
> I have to confess though, there are less critical places where we use the
> ability of SQLite to insert and automatically incremented INT primary keys
> (so Non-AUTOINCREMENT keys), in case that is relevant to the knowledge you
> seek.
>
>
>
> On 2018/03/16 5:37 PM, Richard Hipp wrote:
>
>> This is a survey, the results of which will help us to make SQLite faster.
>>
>> How many tables in your schema(s) use AUTOINCREMENT?
>>
>> I just need a single integer, the count of uses of the AUTOINCREMENT
>> in your overall schema.  You might compute this using:
>>
>> sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>>
>> Private email to me is fine.  Thanks for participating in this survey!
>>
>
> ___
> 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] How many AUTOINCREMENT tables are in your schema?

[Keith Medcalf]

Typically none, though in very rare cases there may be one or two out of a 
dozen or so tables. 

Generally speaking, I have found no particular advantage in most circumstances 
to having "integer primary key" with the "AUTOINCREMENT" property (that is, 
guaranteed ascending unique assignment larger than any "integer primary key" 
ever previously inserted in that table).  

The simpler "integer primary key" without AUTOINCREMENT still guarantees that 
the "integer primary key" is unique and is sufficient for the common use case 
of assigning a unique alternate key to the tuple in the table (which will 
become the primary key) to be used to enforce referential integrity.  That is, 
the proper key is contained in the row data and is an alternate key to the row 
and the "integer primary key" is simply used as a simple shorter "alias" 
candidate key for the tuple.

This also applies there the database is designed using a "mastertable" (a table 
that contains nothing except references to its multiple child tables which 
contain the data, but no actual data assigned any column of the "mastertable" 
itself), a design common if implementing a "network extended" type hierarchical 
model on top of a relational database, for example.

The exception where AUTOINCREMENT is useful is where the rowid alias can itself 
be "overloaded" with meaning rather than strictly an "internal use" only 
candidate key and perpetual "uniqueness" is a contraint of the assigned 
overloaded meaning.  That said, however, such tables often end up being parents 
in a relationship (not children) and therefore generally the persistence 
requirement is met by simply not deleting the parent -- which often ends up 
being part of the overloading requirement and therefore rendering AUTOINCREMENT 
moot -- that is, if the "integer primary key" is overloaded with meaning -- as 
in JobNumber or DocumentNumber or somesuch -- then once assigned, removing the 
tuple is prohibited by external (ie, Business) requirements.

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

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
>Sent: Friday, 16 March, 2018 09:37
>To: General Discussion of SQLite Database
>Subject: [sqlite] How many AUTOINCREMENT tables are in your schema?
>
>This is a survey, the results of which will help us to make SQLite
>faster.
>
>How many tables in your schema(s) use AUTOINCREMENT?
>
>I just need a single integer, the count of uses of the AUTOINCREMENT
>in your overall schema.  You might compute this using:
>
>   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement |
>wc -l
>
>Private email to me is fine.  Thanks for participating in this
>survey!
>--
>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] How many AUTOINCREMENT tables are in your schema?

[Jay Kreibich]

Pretty much every table of every database, with the obvious exceptions like 
virtual tables.

  -j


> On Mar 16, 2018, at 10:37 AM, Richard Hipp  wrote:
> 
> This is a survey, the results of which will help us to make SQLite faster.
> 
> How many tables in your schema(s) use AUTOINCREMENT?
> 
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
> 
>   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
> 
> Private email to me is fine.  Thanks for participating in this survey!
> -- 
> 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] How many AUTOINCREMENT tables are in your schema?

[R Smith]

Across 8 production systems and about 120 SQLite DBs for us - Not a 
single AUTOINCREMENT - so   0 .


I have to confess though, there are less critical places where we use 
the ability of SQLite to insert and automatically incremented INT 
primary keys (so Non-AUTOINCREMENT keys), in case that is relevant to 
the knowledge you seek.




On 2018/03/16 5:37 PM, Richard Hipp wrote:

This is a survey, the results of which will help us to make SQLite faster.

How many tables in your schema(s) use AUTOINCREMENT?

I just need a single integer, the count of uses of the AUTOINCREMENT
in your overall schema.  You might compute this using:

sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l

Private email to me is fine.  Thanks for participating in this survey!


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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Paul]

0



In all of the few dozens of schemas. Rowid logic is almost perfect. In 
extremely rare cases, when
primary key id is exposed outside of database, a custom table that keeps last 
allocated id is used.


16 March 2018, 17:37:31, by "Richard Hipp" :


This is a survey, the results of which will help us to make SQLite faster.

How many tables in your schema(s) use AUTOINCREMENT?

I just need a single integer, the count of uses of the AUTOINCREMENT
in your overall schema.  You might compute this using:

   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l

Private email to me is fine.  Thanks for participating in this survey!
-- 
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] How many AUTOINCREMENT tables are in your schema?

[Scott Robison]

0

On Mar 16, 2018 9:37 AM, "Richard Hipp"  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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