How many AUTOINCREMENT tables are in your schema?

classic Classic list List threaded Threaded
43 messages Options
123
Reply | Threaded
Open this post in threaded view
|

How many AUTOINCREMENT tables are in your schema?

Richard Hipp-3
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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Scott Robison-2
0

On Mar 16, 2018 9:37 AM, "Richard Hipp" <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Paul
In reply to this post by Richard Hipp-3
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" <[hidden email]>:


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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

R Smith-2
In reply to this post by Richard Hipp-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Jay Kreibich
In reply to this post by Richard Hipp-3

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 <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Keith Medcalf
In reply to this post by Richard Hipp-3

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-
>[hidden email]] 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
>[hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Chris Locke-3
In reply to this post by R Smith-2
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Simon Slavin-3
In reply to this post by Paul
0

When needed I use a declared INTEGER PRIMARY KEY.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

petern
In reply to this post by Richard Hipp-3
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 <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Doug Currie-2
In reply to this post by Richard Hipp-3
0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Will Parsons
In reply to this post by Richard Hipp-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Donald Shepherd
In reply to this post by Doug Currie-2
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 <[hidden email]> wrote:

> 0
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

R Smith-2
In reply to this post by petern
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Richard Hipp-3
On 3/16/18, R Smith <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Norman Dunbar
In reply to this post by Will Parsons
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Jean-Christophe Deschamps-3
In reply to this post by Richard Hipp-3

>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

curmudgeon
In reply to this post by Richard Hipp-3
0 in my first sqlite3 DB



________________________________
From: sqlite-users <[hidden email]> on behalf of Jean-Christophe Deschamps <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> on
> behalf of Jean-Christophe Deschamps <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Stephen Chrzanowski
In reply to this post by Richard Hipp-3
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 <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How many AUTOINCREMENT tables are in your schema?

Luuk
In reply to this post by Richard Hipp-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123