[sqlite] AUTOINC vs. UUIDs

Just to chime in here: I think using UUIDs for primary keys is an absolutely terrible idea, instead. First off, how are you going to generate them? How will you avoid collisions? Why should it be necessary to using that much storage space for a primary key and what's the reason for not using

[sqlite] AUTOINC vs. UUIDs

On Wed, 20 May 2015 12:36:43 -0700, Scott Doctor wrote: > Given a field that is a primary key with auto-increment, does sqlite > store an integer that gets incremented, or does it look at the last row > and increment its value? The autoincrement clause causes an entry in the sqlite_sequence

[sqlite] AUTOINC vs. UUIDs

On 2015-05-20 09:36 PM, Scott Doctor wrote: > > Given a field that is a primary key with auto-increment, does sqlite > store an integer that gets incremented, or does it look at the last > row and increment its value? > SQLite stores an Integer that gets incremented if the PK is declared

[sqlite] AUTOINC vs. UUIDs

On Wed, May 20, 2015 at 7:20 PM, R.Smith wrote: > > On 2015-05-21 01:52 AM, Peter Aronson wrote: > >> Now you're just getting silly. What if the application sets all rowids, >> everywhere to 1? The fact is, the chance of collision on a UUID is pretty >> astronomically low as long as a decent

[sqlite] AUTOINC vs. UUIDs

On 20 May 2015, at 8:52pm, Kees Nuyt wrote: > The autoincrement clause causes an entry in the sqlite_sequence > table. It's interesting that SQLite uses tables inside the user database for private purposes like this. A certain wall is broken when the designers choose this option. SQLite

[sqlite] Limit size of write-ahead log file

On 5/19/15, Kees Nuyt wrote: > Did you consider > PRAGMA wal_autocheckpoint=N; and > PRAGMA database.wal_checkpoint(); ? > > http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint Thank you for your reply. Yes, but that will not always limit the size of the wal file. I believe

[sqlite] AUTOINC vs. UUIDs

On Wed, May 20, 2015 at 8:05 PM, Simon Slavin wrote: > Posting this not because I agree with it but because the subject has come > up here a couple of times. > > < > https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/ > > > > "Today, I?ll talk about

[sqlite] AUTOINC vs. UUIDs

> On May 20, 2015, at 8:05 PM, Simon Slavin wrote: > > "Today, I?ll talk about why we stopped using serial integers for our primary > keys, and why we?re now extensively using Universally Unique IDs (or UUIDs) > almost everywhere.? Argh? seriously? tl;dr: don?t.

[sqlite] AUTOINC vs. UUIDs

Posting this not because I agree with it but because the subject has come up here a couple of times. "Today, I?ll talk about why we stopped using serial integers for our primary keys, and why

[sqlite] index broken by insert

Dear Richard, Simon, SQLiters, I hope you did not take my comment to mean that my stupid solution outsmarts your smart solution. I wanted to say that my work is not delayed and that I am trying within the tools/knowledge I have to solve my problems. Substantially, I would like to understand if

[sqlite] AUTOINC vs. UUIDs

On the other hand, perhaps both tablets implement the same PRNG with the same seed. You will then have the same HUID's generated on both and have the exact same problem. Addressing the problem using Hope and Pray is not a very robust solution. It would be much better to solve the problem

[sqlite] AUTOINC vs. UUIDs

All relational database engines store configuration data within the users' database. Many of them just hide it behind varying layers of logically imposed complication. For example, you could simulate SQL Servers' obfuscation by simply changing the name of the primary database alias from

[sqlite] AUTOINC vs. UUIDs

You misunderstood. If an application running on a tablet for 2 users add rows to a local database with an auto increment key, each would get a key based on their database an let's say for grins they both start with a new clean database. User 1 gets key 1 and user 2 gets key 1. Now they push

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Sorry, scratch that. T2 was not a virtual table when I ran this query. My bad. Told you I was a noob. Eric -Original Message- From: Eric Hill Sent: Wednesday, May 20, 2015 12:32 PM To: 'General Discussion of SQLite Database' Subject: RE: [sqlite] Virtual Table query - why isn't

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

Eric had said: >> But then what about a query like this: >> >>SELECT * FROM T1 >>LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = T1.b ) AND >> ( T2.c = T1.c ); >> >> xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in >> that order. To which

[sqlite] AUTOINC vs. UUIDs

Often times people will use GUIDs as primary keys when different systems need to generate rows and then merge together. Like an application that works with offline clients that push the data to a server when the connect. However there are other ways of accomplishing the same thing. Sent from

[sqlite] AUTOINC vs. UUIDs

On 5/20/15, Scott Doctor wrote: > > Given a field that is a primary key with auto-increment, does sqlite > store an integer that gets incremented, or does it look at the last row > and increment its value? https://www.sqlite.org/autoinc.html -- D. Richard Hipp drh at sqlite.org

[sqlite] AUTOINC vs. UUIDs

I can see the issue. For instance, an invoice ID related to inventory, where the invoice ID is the PK number. Two salesmen on their iPads take an order where their local database assigns the same PK number. When merged you have two invoices with the same ID. Seems a classic problem with non

[sqlite] Creating an index seems to break table logic

It has been an extremely strange week for database corruption and extremely complicated bugs. I'm going to have to move "try PRAGMA integrity_check" up a few notches in future. Although, as Richard comments, one of the reports was of corruption that integrity_check didn't spot. Simon.

[sqlite] AUTOINC vs. UUIDs

Hmmm, 9,223,372,036,854,775,807. Disregarding the negative numbers because,... well... If your computer can process one billion completed, finalized, transactions per second (I want one of those computers), the pool of numbers will deplete in a mere: 9223372036854775807 / 10 ) /

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

-Urspr?ngliche Nachricht- Von: Eric Hill [mailto:Eric.Hill at jmp.com] Gesendet: Dienstag, 19. Mai 2015 22:44 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? Hey, Gunter, ... But then what about a query like this:

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

On Tue, 19 May 2015 20:44:17 + Eric Hill wrote: > But then what about a query like this: > > SELECT * FROM T1 > LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = > T1.b ) AND ( T2.c = T1.c ); > > xBestIndex will get called here for T1 with 3 constraints, c, b, and > a,

[sqlite] AUTOINC vs. UUIDs

Given a field that is a primary key with auto-increment, does sqlite store an integer that gets incremented, or does it look at the last row and increment its value? Scott Doctor scott at scottdoctor.com On 5/20/2015 11:05 AM, Simon Slavin wrote: > Posting this not because I

[sqlite] Re : cannot read sqlite 3.x DB-File with SQLIte Browser 3.6 (can't either with 2.0b1)

Salut Regiany, Nous sommes d?sol?s , ce forum a contributeurs et lecteurs anglais. Essayez d'utiliser google translate ou un autre service de traducteur pour communiquer, s'il vous pla?t! As to the question: Do you mean the language for the forum? Or some kind of SQLite system? A menu

[sqlite] Creating an index seems to break table logic

On 5/20/15, Simon Slavin wrote: > It has been an extremely strange week for database corruption and extremely > complicated bugs. I'm going to have to move "try PRAGMA integrity_check" up > a few notches in future. Although, as Richard comments, one of the reports > was of corruption that

[sqlite] Re : index broken by insert

On 5/20/15, REGIANY Lucie wrote: > J ai cr?? une table > CREATE TABLE table 1 (idsas (5) PRIMARY KEY NOT NULL); I don't see how that would work. Sqlite complains about syntax error for that. If "table 1" is meant to be the name of the table, you have to double quote it, because it contains a

[sqlite] Creating an index seems to break table logic

The trouble ticket is here: https://www.sqlite.org/src/tktview/f2ad7de056ab1dc9200d5c364952ac29d7fb035f On 5/20/15, Richard Hipp wrote: > On 5/19/15, Adam Podstawczy?ski wrote: >> sqlite> select * from list_of_numbers where astart < 7169319380 and aend >> > >> 7169319380; >> >> Now, the above

[sqlite] Creating an index seems to break table logic

On 5/19/15, Adam Podstawczy?ski wrote: > sqlite> select * from list_of_numbers where astart < 7169319380 and aend > > 7169319380; > > Now, the above query is expected to return one record only ? and it does: > > But when I add index to the very same table: > > CREATE INDEX startingnumber ON

[sqlite] Re : index broken by insert

Bonsoir J ai cr?? une base Sqlite3 dbtest1.db J ai cr?? une table CREATE TABLE table 1 (idsas (5) PRIMARY KEY NOT NULL); Je veux inporter un gros fichier 1 lignes : siren.txt sans ent?te ni nom de colonnes avec s?parateur de ; et un champ avec 4000 caracteres. Je n y arrive pas pouvez

[sqlite] Re : cannot read sqlite 3.x DB-File with SQLIte Browser 3.6 (can't either with 2.0b1)

Bonjour, je me suis trompee sur la langue (francais) mais je n ai pas eu le choix dans le menu deroulant a l inscription. Comment puis je faire? Cordialement Envoy? depuis mon mobile Huawei Message original Objet : Re: [sqlite] cannot read sqlite 3.x DB-File with SQLIte

[sqlite] Creating an index seems to break table logic

On 05/20/2015 12:39 AM, Adam Podstawczy?ski wrote: > Hi all, > > I have this schema: > > > CREATE TABLE list_of_numbers ( >nn TEXT, >astart INT, >aend INT, >alength INT, >usesflag TEXT, >blength INT, >coolflag NUM, >alphaid > ); Thanks for the report. Are you able

[sqlite] Creating an index seems to break table logic

On 2015-05-19 07:39 PM, Adam Podstawczy?ski wrote: > Hi all, > > I have this schema: > > > CREATE TABLE list_of_numbers ( >nn TEXT, >astart INT, >aend INT, >alength INT, >usesflag TEXT, >blength INT, >coolflag NUM, >alphaid > ); > > Some example data: > > nn

[sqlite] Creating an index seems to break table logic

Thank you for responding Simon, Please see my last response to this thread where I copied more data from the console. I have to obfuscate the data somewhat unfortunately before public posting? but I preserved all structure and types as in original data. Thanks, -- adam > On 19 May 2015, at