Re: [sqlite] Article on AUTOINC vs. UUIDs

On Thu, Nov 30, 2017 at 11:52 AM, Keith Medcalf wrote: > > On Thursday, 30 November, 2017 09:27, J Decker wrote: > > >> UUIDs are nice though if you don't have a natural key available and > >> you are generating keys on multiple systems. Sadly, they are

Re: [sqlite] Simple read-only program very slow

Is there an index on pos where ppos is the left-most field (or the only field) in the index? What is the column affinity of ppos? Of the fiold you are passing as a parameter? Is ppos unique? If you CREATE [UNIQUE] INDEX searchindex ON pos (ppos, pnum); then your query can be satisfied only

Re: [sqlite] Simple read-only program very slow

On Sun, Nov 26, 2017 at 12:02 AM, Clemens Ladisch wrote: > Kevin O'Gorman wrote: > > I wrote a super simple program to the read the file and count how many > > records are already there. I got impatient waiting for it so I killed > > the process and added an output of one

[sqlite] Fix for 32bit compilation on Solaris

Hi, Compilers shipped with Solaris were traditionally compiling 32bit binaries unless specified otherwise. This changed recently, the default is 64bit binaries. So if you want to compile 32bit object, you have to specify -m32. That slightly breaks sqlite configuration script as it expects 32bit

Re: [sqlite] Simple read-only program very slow

On Sun, Nov 26, 2017 at 1:39 AM, Simon Slavin wrote: > > > On 26 Nov 2017, at 3:13am, Kevin O'Gorman wrote: > > > > I've got a database of some 100 million records, and a file of just over > > 300 thousand that I want represented in it. I wanted

Re: [sqlite] Article on AUTOINC vs. UUIDs

On Thursday, 30 November, 2017 09:27, J Decker wrote: >> UUIDs are nice though if you don't have a natural key available and >> you are generating keys on multiple systems. Sadly, they are strings >> in sqlite, and it would be more efficient to store and compare them >> as

Re: [sqlite] Foreign key lint has issues with without rowid

3.21.0 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, November 30, 2017 2:06 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key lint has issues with without rowid On 30 Nov 2017, at

Re: [sqlite] Foreign key lint has issues with without rowid

On 30 Nov 2017, at 7:02pm, David Raymond wrote: > I’m using the CLI’s .lint fkey-indexes command, and it appears to be having > issues with without rowid tables. A low priority thing to look into when > someone's bored. Please tell us which version of SQLite you’re

[sqlite] Foreign key lint has issues with without rowid

I’m using the CLI’s .lint fkey-indexes command, and it appears to be having issues with without rowid tables. A low priority thing to look into when someone's bored. sqlite> create table parent (id integer primary key); sqlite> create table child1 (id integer primary key, parentID int

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

I would LOVE for this to be a Forum. I'd be willing to be a paid member/sponsor to help pay for running the forum software. I prefer XenForo for Forum software personally - it supports clipboard image copy/paste into the forum, which vBulletin doesn't. - Deon -Original Message-

Re: [sqlite] Sqlite and docker performance question

Perhaps the file sync performed by SQLite is more expensive in the docker environment than in the host. That would make sense to me. On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote: > In my last mail i have multiple table creation and index. It seam that the > overtime is for

Re: [sqlite] Article on AUTOINC vs. UUIDs

Thanks, very insightful! On Thu, Nov 30, 2017 at 5:27 PM J Decker wrote: > I would also like to make a note, that many criticisms are 'there's so many > bytes to have to compare', however, because of the highly random nature of > good UUIDs failure occurs quickly, usually

Re: [sqlite] Article on AUTOINC vs. UUIDs

On Thu, Nov 30, 2017 at 7:14 AM, Wout Mertens wrote: > The article is a bit muddled, the only real argument I could find is that > auto-inc makes next keys easy to guess, which is information leakage, which > means that is a potential security problem if that information

Re: [sqlite] Article on AUTOINC vs. UUIDs

For userIds, I generate a unique id from their email address. If they later change the email address, they keep the id anyway. I really like natural keys. Of course, if you want to use that id in URLs, it would be good to use a second unique id that is not used as a foreign key, so that people

Re: [sqlite] Article on AUTOINC vs. UUIDs

On 30 Nov 2017, at 3:52pm, Stephen Chrzanowski wrote: > As one of the security guys here at work say, "Security does not help > convenience". In the debug world, yeah, I agree, looking for 4310 is much > easier than 8af7* but, that should stick to a debug environment.

Re: [sqlite] PLEASE UNSUBSCRIBE

On 30 Nov 2017, at 3:55pm, oɹɹoɯɐɥɔ ǝƃɹoɾ wrote: > I've tried a zillion times via the above link but it does not seem to work. Clicking on that link just worked fine for me. It’s not even HTTPS. You might want to try a different browser, try your smartphone, then

[sqlite] PLEASE UNSUBSCRIBE

> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users I've tried a zillion times via the above link but it does not seem to work. Thanks, -- Jorge. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Article on AUTOINC vs. UUIDs

As one of the security guys here at work say, "Security does not help convenience". In the debug world, yeah, I agree, looking for 4310 is much easier than 8af7* but, that should stick to a debug environment. But to put a twist on this, and to lessen the paranoia of collisions, you could

Re: [sqlite] Article on AUTOINC vs. UUIDs

> if your systems are set up in a sane way, the MAC address alone would prevent collisions, no? > And on the same system, are collisions even possible? Google says "In the case of standard version 1 and 2 UUIDsusing unique MAC addresses from network cards, collisions can occur only when an

Re: [sqlite] Article on AUTOINC vs. UUIDs

There are some minor points, but I agree that it basically boils down to “serial IDs break security-by-obscurity.” That’s true, but…. -j > On Nov 30, 2017, at 9:00 AM, Keith Medcalf wrote: > > > Well, in my opinion the guy is an idiot. The way to avoid the issues

Re: [sqlite] Article on AUTOINC vs. UUIDs

The article is a bit muddled, the only real argument I could find is that auto-inc makes next keys easy to guess, which is information leakage, which means that is a potential security problem if that information were somehow useful. Seems to me that problem can be resolved by having an auto-inc

Re: [sqlite] Article on AUTOINC vs. UUIDs

Well, in my opinion the guy is an idiot. The way to avoid the issues he is describing as the problems with serial IDs (or using the RowID) are simple to avoid: DO NOT DO THOSE THINGS! If you know that disclosing the RowID in a URL is ungood, then DO NOT DO THAT. Sheesh. As my good buddy

Re: [sqlite] Article on AUTOINC vs. UUIDs

I'm going to read it later, but, I'll be going in skeptical. Collision is real, even at 128-bit resolution, but if your code handles the potential of such a thing, then its no different than an autoinc primary key. I'm rather interested in their reasoning. On Thu, Nov 30, 2017 at 9:15 AM, Simon

[sqlite] Article on AUTOINC vs. UUIDs

Thought some of you might enjoy seeing this article. I make no comment on what I think of the reasoning therein. It’s set in the PostgreSQL world, but you could make an external function for SQLite which generates UUIDs.

Re: [sqlite] Sqlite and docker performance question

In my last mail i have multiple table creation and index. It seam that the overtime is for all the create statement. It really looks like a disk pb. But can't identify so much difference in my direct disk access ( with DD ) I will try it on an other host. 2017-11-30 14:59 GMT+01:00 Simon Slavin

Re: [sqlite] Sqlite and docker performance question

OP wrote: > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER, > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER); Is this the first content of a new file ? If so, SQLite has to create the file and write some structure information as well as writing the table. I

Re: [sqlite] Sqlite and docker performance question

my test script is a little bit more complicated but with .timer it give this result. Each statement is longer. So no load time or something like that. ON HOST root@dipsy:/usr/share/kserver4# time sqlite3 dbm.db < dbm.sql Run Time: real 0.027 user 0.00 sys 0.00 Run Time: real 0.020 user

Re: [sqlite] Sqlite and docker performance question

All my other tests give me some equivalent time in host and in container. I have reduced the problem to the creation of the database. The rest of my workload run approximatively in the same time. You're right, there is some variance between each test but the 4 factor stay the same. Sometime it's

Re: [sqlite] Sqlite and docker performance question

In addition to the execution time of the SQL you are also measuring time to load and link the sqlite3 command. A perhaps more realistic test would be to change the f.sql to contain: .timer on CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER, virtual INTEGER, pfid

Re: [sqlite] Sqlite and docker performance question

> Le 30 nov. 2017 à 12:00, Sebastien HEITZMANN <2...@2le.net> a écrit : > > We juste create a sigle table in a new db. In my docker container it take 4 > time more time than in the host system. > > time sqlite3 /tmp/foo.db < f.sql > > on the host machine > real 0m0.216s > > and in the docker

[sqlite] Sqlite and docker performance question

Hi, In our application we use sqlite with great satisfaction. We currently benchmark our application and came up to a strange difference in creating a very simple sqlite db. We juste create a sigle table in a new db. In my docker container it take 4 time more time than in the host system. time