Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

What about invalid and reused MAC addresses and devices with no MAC address at all? What about time resets to the epoch which are not restored, user time changes, daylight saving or leap seconds? It sounds even more probabilistic than the probabilistic methods. Does anyone actually use it? >

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Only if you assume a monotonic clock ... --- 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 J. King >Sent:

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Peter (that is, the other Peter) is being more than a little flippant, yes, and at least a little obscure (I don't get the joke, either), but the substance appears quite serious. His prior message suggested using what I can only assume would be a trivial extension to SQLite to do what you

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Is this a joke? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Well, first you imagine you're back at the office in Santa Clara or Redmond in the early 1990's. Then take a belt of whisky, cross your eyes, and paste from doc to clipboard a few edits and voila: #include "sqlite3ext.h" #include SQLITE_EXTENSION_INIT1 static struct metadata { char const

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Version 1 UUIDs only use a random number (16 bits) in the case of an uninitialized clock sequence (a case which, ideally, should only occur the first time a device generates a UUID). Version 1 UUIDs especially avoid using random numbers; they are also not a shortening of longer input. In

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On 11/24/17 8:58 PM, Jean-Christophe Deschamps wrote: At 23:49 24/11/2017, you wrote: On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote: At 22:38 24/11/2017, you wrote: One proof of the falsehood of your assertion is that we CAN fill a database with some data using UIDs, and we will

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Actually, it is entirely possible to generate two and only two random xUID side by each and have them be duplicates. Such is the nature of randomness. The only way to ensure that there is no collisions is to check whether the xUID is already in use/seen within the domain where it is used.

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

At 00:13 25/11/2017, you wrote: Looks like I do need to use AUTOINCREMENT after all, otherwise the framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY columns are allowed a NULL value on insert. I can't answer about just INTEGER PRIMARY KEY columns, but any table with an

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

At 23:49 24/11/2017, you wrote: On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote: At 22:38 24/11/2017, you wrote: One proof of the falsehood of your assertion is that we CAN fill a database with some data using UIDs, and we will almost certainly not get a collision, while you assertion

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

> sqlite> .load column-meta-data.so Sorry I'm at a loss to find this extension? Google gives me nothing related to SQLite for "isRowId", "column-meta-data.so", "column-meta-data.c", etc. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Apparently you would query pragma_table_info for "INTEGER" PK columns and then ask through a trivial extension function about the other column meta-data: https://www.sqlite.org/c3ref/table_column_metadata.html The basic plot is illustrated below: sqlite> .load column-meta-data.so sqlite> SELECT

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On Fri, Nov 24, 2017 at 3:13 PM, Peter Halasz wrote: > Sorry to steer the conversation back to the topic. > > Looks like I do need to use AUTOINCREMENT after all, otherwise the > framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY > columns are

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Sorry to steer the conversation back to the topic. Looks like I do need to use AUTOINCREMENT after all, otherwise the framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY columns are allowed a NULL value on insert. That's a shame. Is there a way to query a schema to get a

Re: [sqlite] SQLite website A/B comparison

Richard Hipp writes: > Which is the better "timeline" display for the SQLite project status, > in your opinion? > > A: https://sqlite.org/src/timeline > B: https://sqlite.org/b/timeline > I prefer A; having the "Older" link at the top doesn't seem right for some reason.

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On Fri, Nov 24, 2017 at 11:25 AM, Keith Medcalf wrote: > > Actually a UUID or a GUID has a 100% certainty of a collision, not just a > possibility of a collision. Just as all hash algorithms which take > something and generate a shorter "hash" or "checksum" will always

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote: At 22:38 24/11/2017, you wrote: One proof of the falsehood of your assertion is that we CAN fill a database with some data using UIDs, and we will almost certainly not get a collision, while you assertion we will. This is an attempt at

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

At 22:38 24/11/2017, you wrote: One proof of the falsehood of your assertion is that we CAN fill a database with some data using UIDs, and we will almost certainly not get a collision, while you assertion we will. This is an attempt at "proof by example". Keith is perfectly right

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On 11/24/17 2:25 PM, Keith Medcalf wrote: Actually a UUID or a GUID has a 100% certainty of a collision, not just a possibility of a collision. Just as all hash algorithms which take something and generate a shorter "hash" or "checksum" will always have collisions. Without exception and as

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Actually a UUID or a GUID has a 100% certainty of a collision, not just a possibility of a collision. Just as all hash algorithms which take something and generate a shorter "hash" or "checksum" will always have collisions. Without exception and as an absolute 100% certainty. There is no

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

You are missing the point. The rowid is assigned automatically if it is not specified (that is, if it is null on insert). This is the behaviour of the rowid. In all databases and filesystems ever invented anywhere in the multiverse by any carbon (even non-carbon) based lifeform, whether

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

No. The Windows Search Indexer or Windows Defender only processes files when they are "closed" (that is, the act of closing a file adds it to the queue of files to be processed). As long as they are "open" they are not enqueued for processing. There are a few exceptions, but they all

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Obviously, this is a design time factor, but, in my applications, I always use integer IDs if I'm ever going to bring the info that row contains (And other relevant info) to the UI. I have never had a solid reason to use GUIDs or UUIDs or whatever. Any time I'm adding something to a listbox,

Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)

Richard Damon wrote: > > A basic rule is that even if you don't think anyone else might be > accessing a file, you need to allow for a temporary failure to lock a > file for writting, and retry several times with a timeout. > Yes, the Win32 VFS does this. However, the original poster may

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

On 11/24/17 12:20 PM, Nelson, Erik - 2 wrote: Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM but this does not prevent reading and modifying the file from other processes while they are open by SQLite in our process. Does antivirus or Windows randomly modify other applications' files?

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On 11/24/17 3:51 AM, R Smith wrote: On 2017/11/24 5:23 AM, Peter Halasz wrote: As for whether I need to use AUTOINCREMENT, it seemed like a good idea to avoid rowid reuse, but I can avoid using it for the sake of optimization, so probably will. I agree with Keith and has many times

Re: [sqlite] Many ML emails going to GMail's SPAM

I agree - keep the list on email. Simple, convenient. John Gillespie On 22 November 2017 at 19:49, Niall O'Reilly wrote: > On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote: > > > Please, not a forum. The email list is instant, dynamic, and convenient. >

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM >but this does not prevent reading and modifying the file from other >processes while they are open by SQLite in our process. Does antivirus or Windows randomly modify other applications' files? It seems like no program could reliably

[sqlite] Getting an advance list of RowIDs for a query result set

For a complex query you can often get a list of the base table RowIDs very quickly with a simple query and then use an array of those values (along with the carray virtual table) to retrieve sections of data from the complex query almost instantly. I've been doing this for a while but would

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

You might be able to set the timeout to something higher. https://sqlite.org/pragma.html#pragma_busy_timeout https://sqlite.org/c3ref/busy_timeout.html On Fri, Nov 24, 2017 at 3:44 AM, Jiří Matějka wrote: > We need to lock our SQLite datafiles at the filesystem level,

[sqlite] SQLite website A/B comparison

Which is the better "timeline" display for the SQLite project status, in your opinion? A: https://sqlite.org/src/timeline B: https://sqlite.org/b/timeline Since this question is really more about the version-control system than SQLite itself, please reply directly to me rather responding

Re: [sqlite] EXISTS optimisation?

Constantin Emil MARINA wrote: > I am wondering if in SQLITE the EXISTS clause is expanded and optimized in > any way. No. > This is generated by the observation that 2 algebrically equivalent queries, > SELECT WHERE EXISTS () > and > SELECT WHERE id IN (SELECT ...) > produce different

Re: [sqlite] Commit and rollback behaviour during power loss

There is one degenerate case, which has been discussed a few times on this list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that marks a transaction being committed is the deletion of the rollback journal. Deleting a file is a directory level operation, which means there are

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

On 24 November 2017 at 16:44, Jiří Matějka wrote: > We need to lock our SQLite datafiles at the filesystem level, because our > application frequently crashes with "database is locked" error, which is > probably due to other processes opening our files. At least we

Re: [sqlite] Commit and rollback behaviour during power loss

> If Commit returns with SQLITE_OK, then YES, it is handed off to the disk > with some caveats, namely: > > A - We are assuming the Python sqlite3 wrapper you use doesn't do > obfuscation of any sort and directly calls the sqlite3 API and returns the > direct result from those calls. If you

Re: [sqlite] Commit and rollback behaviour during power loss

On 2017/11/24 10:47 AM, Blagovest Buyukliev wrote: Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit()

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On Fri, Nov 24, 2017 at 9:51 AM, R Smith wrote: > I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, but > obviously the speed/size gain with an integer key (especially INTEGER > PRIMARY KEY row-id alias in SQLite) can't be ignored. > > Disclaimer: This is

[sqlite] Need SQLite datafiles to be locked on OS level (Windows)

We need to lock our SQLite datafiles at the filesystem level, because our application frequently crashes with "database is locked" error, which is probably due to other processes opening our files. At least we identified Windows Search indexing service and McAffee antivirus. I have tried

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On 2017/11/24 5:23 AM, Peter Halasz wrote: As for whether I need to use AUTOINCREMENT, it seemed like a good idea to avoid rowid reuse, but I can avoid using it for the sake of optimization, so probably will. I agree with Keith and has many times mentioned this before (apologies to others

[sqlite] Commit and rollback behaviour during power loss

Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() has returned, fsync() has been called on the file and