Re: [sqlite] File handles for database on OS X keeps growing.

[Simon Slavin]

On 20 May 2017, at 1:34am, Map Pin Support  
wrote:

> I have an app which makes thousands of individual inserts into a SQLite3 
> database.
> I use FMDB as a Cocoa wrapper for SQLite.

This is not normal for SQLite.  I don’t think you can get much help here.  You 
need help from the people who support FMDB.

My only idea is to make sure you’re using an up-to-date version of FMDB.

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

Re: [sqlite] File handles for database on OS X keeps growing.

[Jens Alfke]

> On May 19, 2017, at 5:34 PM, Map Pin Support 
>  wrote:
> 
> What I am seeing is the app crash as a result of “too many files open”, 
> however I believe I am opening the database file only once:

It’s been a while since I’ve used FMDB, but IIRC it opens a pool of database 
connections to dispatch requests to. That’s a pretty common technique. It 
sounds like something is going wrong with that pool and it’s opening more and 
more connections.

This is not an issue related to SQLite itself. You’ll have better luck asking 
on an FMDB forum/mailing list (if there is one?) or perhaps filing an issue 
against FMDB on Github.

Or if you feel like investigating the issue yourself, set breakpoints on 
sqlite3_open and sqlite3_open_v2, and by looking at the backtrace and the code 
you may get an idea of why they’re being called so much.

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

[sqlite] File handles for database on OS X keeps growing.

[Map Pin Support]

I have an app which makes thousands of individual inserts into a SQLite3 
database.
I use FMDB as a Cocoa wrapper for SQLite.

Sqlite v 3.18.0
FMDB v 2.6.2

What I am seeing is the app crash as a result of “too many files open”, however 
I believe I am opening the database file only once:

if (![db openWithFlags:SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE]) {
NSLog(@"Could not open db.");
}


In OSX, if I launch Activity Monitor , double-click my app and look at Open 
files and ports, I see a steadily increasing number of references to my .sqlite 
file

(I have trimmed the output here, but the numbers count up from 15 to 4725)

cwd
/Users/nparker/Library/Containers/com.map-pin.Dapper/Data
txt
/private/var/folders/0n/14xdqy691_jdjrntkjg349qcgn/T/AppTranslocation/87ADEFD1-5363-48A2-89EE-7090093B38A4/d/Dapper-4.app/Contents/MacOS/Dapper
txt
/System/Library/Frameworks/ServiceManagement.framework/Versions/A/ServiceManagement
txt
/Library/Frameworks/iTunesLibrary.framework/Versions/A/iTunesLibrary
txt
/usr/lib/libsqlite3.dylib
txt
/usr/lib/libSystem.B.dylib
txt
/System/Library/Frameworks/IOKit.framework/Versions/A/IOKit
txt
/usr/lib/system/libdispatch.dylib
txt
/usr/lib/system/libdyld.dylib
txt
/usr/lib/system/liblaunch.dylib
txt
/usr/lib/system/libmacho.dylib
txt
/usr/lib/system/libquarantine.dylib
txt
/usr/lib/system/libsystem_c.dylib
txt
/usr/lib/system/libsystem_configuration.dylib
txt
/usr/lib/system/libsystem_coreservices.dylib
txt
/usr/lib/system/libsystem_dnssd.dylib
txt
/usr/lib/system/libsystem_info.dylib
txt
/usr/lib/system/libsystem_kernel.dylib
txt
/usr/lib/system/libsystem_m.dylib
txt
/usr/lib/system/libsystem_malloc.dylib
txt
/usr/lib/system/libsystem_network.dylib
txt
/usr/lib/system/libsystem_networkextension.dylib
txt
/usr/lib/system/libsystem_platform.dylib
txt
/usr/lib/system/libsystem_pthread.dylib
txt
/usr/lib/system/libsystem_sandbox.dylib
txt
/usr/lib/system/libsystem_secinit.dylib
txt
/usr/lib/system/libsystem_symptoms.dylib
txt
/usr/lib/system/libsystem_trace.dylib
txt
/usr/lib/system/libxpc.dylib
txt
/usr/lib/system/libkxld.dylib
txt
/System/Library/Frameworks/CFNetwork.framework/Versions/A/CFNetwork
txt
/usr/lib/libnetwork.dylib
txt
/System/Library/Frameworks/CoreDisplay.framework/Versions/A/CoreDisplay
txt
/usr/share/icu/icudt57l.dat
txt
/private/var/db/mds/messages/501/se_SecurityMessages
txt
/Users/testuser/Library/Containers/com.map-pin.Dapper/Data/Library/Caches/com.map-pin.Dapper/Cache.db-shm
txt
/System/Library/CoreServices/SystemAppearance.bundle/Contents/Resources/VibrantLightAppearance.car
txt
/System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vImage.framework/Versions/A/Libraries/libCGInterfaces.dylib
txt
/System/Library/Fonts/SFNSText.ttf
txt
/System/Library/Frameworks/Security.framework/Versions/A/PlugIns/csparser.bundle/Contents/MacOS/csparser
txt
/System/Library/CoreServices/SystemAppearance.bundle/Contents/Resources/FunctionRowAppearance.car
txt
/System/Library/CoreServices/SystemAppearance.bundle/Contents/Resources/SystemAppearance.car
txt
/System/Library/Frameworks/Carbon.framework/Versions/A/Frameworks/HIToolbox.framework/Versions/A/Resources/Extras2.rsrc
txt
/System/Library/Keyboard 
Layouts/AppleKeyboardLayouts.bundle/Contents/Resources/AppleKeyboardLayouts-L.dat
txt
/usr/lib/dyld
txt
/System/Library/CoreServices/SystemAppearance.bundle/Contents/Resources/Assets.car
txt
/System/Library/Fonts/Helvetica.dfont
txt
/System/Library/Frameworks/OpenGL.framework/Versions/A/Resources/GLEngine.bundle/GLEngine
txt
/System/Library/Extensions/AppleIntelBDWGraphicsGLDriver.bundle/Contents/MacOS/AppleIntelBDWGraphicsGLDriver
txt
/System/Library/Frameworks/OpenGL.framework/Versions/A/Resources/GLRendererFloat.bundle/GLRendererFloat
txt
/private/var/folders/0n/14xdqy691_jdjrntkjg349qcgn/C/com.map-pin.Dapper/com.map-pin.Dapper/com.apple.metal/libraries.data
txt
/private/var/folders/0n/14xdqy691_jdjrntkjg349qcgn/C/com.map-pin.Dapper/com.map-pin.Dapper/com.apple.metal/Intel(R)
 Iris(TM) Graphics 6100/functions.data
txt
/System/Library/Extensions/AppleIntelBDWGraphicsMTLDriver.bundle/Contents/MacOS/AppleIntelBDWGraphicsMTLDriver
txt
/private/var/folders/0n/14xdqy691_jdjrntkjg349qcgn/0/com.apple.LaunchServices-175-v2.csstore
txt
/private/var/db/dyld/dyld_shared_cache_x86_64h
0
/dev/null
1
/dev/null
2
/dev/null
3
/System/Library/Frameworks/Carbon.framework/Versions/A/Frameworks/HIToolbox.framework/Versions/A/Resources/Extras2.rsrc
4
/System/Library/Frameworks/CoreImage.framework/ci_kernels.metallib
5
/Users/testuser/Library/Containers/com.map-pin.Dapper/Data/Library/Caches/com.map-pin.Dapper/Cache.db
6
/Users/testuser/Library/Containers/com.map-pin.Dapper/Data/Library/Caches/com.map-pin.Dapper/Cache.db-wal
7
/Users/testuser/Library/Containers/com.map-pin.Dapper/Data/Library/Caches/com.map-pin.Dapper/Cache.db-shm
8
9
/Users/testuser/Library/Containers/com.map-pin.Dapper/Data/Library/Application 

Re: [sqlite] SQLite in memory

[Wout Mertens]

Note that, as I understand it, if you use only a single connection for the
CherryPi server, all the threads on the server will be running the queries
sequentially. Try using a database connection per thread?

On Thu, May 18, 2017, 8:47 PM Gabriele Lanaro 
wrote:

> Thanks everyone for all the tips! This is all very useful.
>
> We are using SQLite’s FTS5 feature to search a large number of text files.
> There are 50M records in total but they are split across 1000 smaller
> databases of 50K records each. Each DB is 250MB in size.
>
> I am trying to test query performance and for that I am issuing queries for
> same term over and over on a random subset of the databases.
>
> Each query will execute on its own thread (I am using Python’s CherryPy
> server) and the connection to each DB will be kept alive (multiple requests
> will certainly hit the same connection). I am using PRAGMA
> cache_size=256MB, so it should cache the entire DB in memory.
>
> If I only use a single database then I would expect that the query could be
> served entirely from cached the copy of DB. Since we have 100 of those, I
> could reasonably expect that there will be some reading from disk.
>
> But for a smaller number of DBs, say 10, I would expect the performance
> similar to in-memory.
>
> The schema is roughly constituted of two tables, one is a full text index,
> fts5_table, while the other is called datatable.
>
> The query in question is a full text query on the full text index joined
> with another table.
>
> *SELECT* fts5.column, datatable.column2 *FROM* fts5_table, datatable
> *WHERE*
>  fts5_table *MATCH* ‘term’ *AND* datatable.id = fts5_column.rowid *ORDER
> BY*
>  rank *ASC* *LIMIT* 10;
>
> The total number 'term' matches in the documents is about 3 documents
> (given the orderby query, this means that all of those documents will need
> to be ranked).
>
> As I increase the number of threads I reach a peak performance at 4
> threads, which is less than the number of available cores (after that, the
> performance degrades). If I make my application use more threads (i.e. more
> requests get processed in paralle) the performance linearly degrades.
>
> All I am getting is ~40 requests per seconds (when we have 100 users making
> queries in parallel). But I believe SQLite is capable of doing much more. I
> am running on a Red Hat Linux on an Intel machine with 8-cores, 16-threads
> and 64GB of system memory. Disks are SSD.
>
> Thanks,
>
> Gabriele
>
>
> On Thu, May 18, 2017 at 7:51 AM, Eduardo Morras  wrote:
>
> > On Wed, 17 May 2017 22:18:19 -0700
> > Gabriele Lanaro  wrote:
> >
> > > Hi, I'm trying to assess if the performance of my application is
> > > dependent on disk access from sqlite.
> > >
> > > To rule this out I wanted to make sure that the SQLite DB is
> > > completely accessed from memory and there are no disk accesses.
> > >
> > > Is it possible to obtain this effect by using pragmas such as
> > > cache_size?
> > >
> > > Another solution is to copy the existing db to a :memory: db but I'd
> > > like to achieve the same effect without doing so (because it will
> > > require substantial modification of the application). For the sake of
> > > argument, let's image that using :memory: db is not an option.
> > >
> > > Also using a ramdisk is not an option because I don't have root
> > > access to the machine.
> >
> > What OS are you using?
> >
> > You can next tips to make the app less dependant on disk I/O access:
> >
> > a) change where store temporal tables (mat views, subqueries, temp
> > tables) and indices (transient, to use ram always (pragma
> > temp_store=2),
> >
> > b) increase cache size, the more, the better (os disk cache is shared
> > with other processes and is slower), if cache is equal or bigger than
> > your db, it'll be fit in ram,
> >
> > c) set config SQLITE_CONFIG_STMTJRNL_SPILL=-1 if you use statement
> > journal files (check http://www.sqlite.org/tempfiles.html#stmtjrnl),
> >
> > d) use wal mode to avoid *-shm files, and set wal_checkpointing,
> >
> > e) use mmap_size pragma to minimize I/O (check
> > http://www.sqlite.org/mmap.html it has disadvanteges too)
> >
> >
> > > Thanks,
> > >
> > > Gabriele
> >
> > HTH
> >
> > ---   ---
> > Eduardo Morras 
> >
> ___
> 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] auntondex with unique and integer primary key

[David Raymond]

This is the only reason I can think of for the redundancy and was actually 
thinking of it earlier.

Consider the basic "select count(*) from ...;" If you've got a lot of fields, 
or if they're large fields, then the fanout of your records means you may have 
to get a whole bunch of pages to find how many records you have. An index on 
just the rowid is as compact as possible and would provide the fastest possible 
answer to the basic count query.

Hmm, it would also provide quick checking of foreign keys if it's a parent 
table. Might also be useful in some joins as a quick way to see if there's a 
match etc.

So yeah, there are some possible reasons for it. The "unique" bit is redundant, 
but if you want that extra index for one of the above mentioned reasons, then 
it means you can create it right there for 1 extra word. Of course without a 
comment in the create statement no one's gonna understand why you put it there, 
and everything will function just fine without it.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Friday, May 19, 2017 3:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] auntondex with unique and integer primary key


Perhaps there is a reason that you want a separate unique index.  Maybe the 
table has 15000 columns and from time to time you just need to be able to scan 
the used RowIDs without incurring the penalty of wafting to and fro all over 
the disk.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[Paul Sanderson]

Ahh -

I always let SQLite decide what index to use as I assume that it knows
best. I have never used "indexed by" to force the use of a specific index -
I see the issue with backward compatibility now.

Thanks Richard

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] auntondex with unique and integer primary key

[Keith Medcalf]

No.  

You asked for the extra index to be created in the table specification.  It is 
not the job of the database engine to correct your errors (it is not even 
possible to know if it is an error).  

If you declared that you want an extra UNIQUE index on an INTEGER PRIMARY KEY 
(which is already unique), then how is the software to know that you do not 
know what you are doing -vs- that you DO know what you are doing and need that 
extra UNIQUE index?  

Perhaps there is a reason that you want a separate unique index.  Maybe the 
table has 15000 columns and from time to time you just need to be able to scan 
the used RowIDs without incurring the penalty of wafting to and fro all over 
the disk.

So, the real optimization opportunity rests with the database designer who 
should not create non-required indexes.  For example, one may declare:

create table x(rowid unique integer primary key, value text not null collate 
nocase unique);
create index pk_x on x as (rowid);
create index fk_x on x as (value);
create unique index ak_x on x (value);

Although you have declared a multiplicity of redundant indexes, there is no way 
to tell whether you did so because (a) you intended to do so for some 
particular reason (ie, requirement); or, (b) you simply do not know what you 
are doing (ie, made an error).  Computer software is supposed to do what it is 
told to do.  Exactly.  With no arguments and without interpretation of meaning. 
 And if it does not understand, then it should spit up one of the most useful 
error messages ever devised:  "Who?", "What?", "Where?"; and, "How?"

One of the very few exceptions to this is the PL/1 Level F Optimizing 
Compilers.  If you sputter nonsence it will try to put a "meaningful spin" on 
your sputterings -- one of the very first implementations of "Plug and Pray" 
(and, unless you really knew what you were doing, working with about the same 
success).

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Friday, 19 May, 2017 11:22
> To: General Discussion of SQLite Database
> Subject: [sqlite] auntondex with unique and integer primary key
> 
> Is the autoindex associated when using unique with an integer primary key
> definition redundant?
> 
> I have seen a number of DBs/tables created in the following form:
> 
> Create table test(id integer unique primary key);
> 
> Insert into test values (1);
> 
> Insert into test values (2);
> 
> Insert into test values (3);
> 
> The table is created and populated as expected, but an
> sqlite_autoindex_test_1 is also created with content that mirrors exactly
> the rowid/id.
> 
> Is the autoindex redundant and is this an opportunity for optimisation?
> 
> 
> Using 3.18.0
> 
> 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



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

Re: [sqlite] NOT NULL integer primary key

[Keith Medcalf]

On Thursday, 18 May, 2017 10:17, Paul Sanderson  
wrote:

> Create table test (id integer not null primary key, data text);
> insert into test values (null, 'row1');
> select * from test;
> 1, row1
 
> I know that if you provide a NULL value to a column define as integer
> primary key that SQLite will provide a rowid, but should the not null
> constraint be obeyed?

Specifying NOT NULL on an INTEGER PRIMARY KEY (which is an alias for the RowID) 
is a redundant redundancy.  The RowID cannot be null and must have a value.  
Trying to insert a null value is how you get one generated for you.  

It should probably be an error to specify "NULL" or "NOT NULL" (or anything 
else other than perhaps AUTOINCREMENT) with an INTEGER PRIMARY KEY (RowID 
alias).  However, rather than do so the redundant redundancies and the 
illogical declarations (such as INTEGER PRIMARY KEY NULL or COLLATE clauses) 
are merely silently ignored.




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

Re: [sqlite] auntondex with unique and integer primary key

[Richard Hipp]

On 5/19/17, Paul Sanderson  wrote:
>
> Yes Unique is redundant in the create statement, but it would be a small
> optimisation, unless I am missing something, for SQLite to detect this and
> not create the autoindex to start with.
>

That would be great, if we had caught the problem 15 years ago.  But
now, we have to keep the databases backwards compatible, and omitting
the index would generate an incompatible database file.

-- 
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] auntondex with unique and integer primary key

[Paul Sanderson]

I think you guys might be missing my point :)

We know the integer primary key is an alias for the rowid - but as that
table is created we also get a completely redundant index, a second b-tree,
which is essentially a one to one mapping of rowids 1=1, 2=2, 3=3 etc.

The index takes up space that is not required and when updating the table
and will also need to be updated when an insert etc takes places using up
cycles and disk I/O.

Yes Unique is redundant in the create statement, but it would be a small
optimisation, unless I am missing something, for SQLite to detect this and
not create the autoindex to start with.

Just a thought



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 19 May 2017 at 19:29, Joseph L. Casale  wrote:

> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On
> > Behalf Of Paul Sanderson
> > Sent: Friday, May 19, 2017 12:08 PM
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] auntondex with unique and integer primary key
>
> > I just thought it might be an area for optimisation as a redundant index
> is
> > built.
>
> According to the docs, it's only a pointer and not a duplicate when
> specified exactly as 'INTEGER PRIMARY KEY'. The semantics change
> when you add AUTOINCREMENT to it.
> ___
> 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] auntondex with unique and integer primary key

[Joseph L. Casale]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Paul Sanderson
> Sent: Friday, May 19, 2017 12:08 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] auntondex with unique and integer primary key

> I just thought it might be an area for optimisation as a redundant index is
> built.

According to the docs, it's only a pointer and not a duplicate when
specified exactly as 'INTEGER PRIMARY KEY'. The semantics change
when you add AUTOINCREMENT to it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[Paul Sanderson]

Thanks Simon

I am aware that a PK must be unique :)

It's not me that's declaring it as unique - I get to look at thousands of
databases that other people create and it is these where I have noticed it
(Chrome and Skype are two).

I just thought it might be an area for optimisation as a redundant index is
built.




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 19 May 2017 at 18:49, Simon Slavin  wrote:

>
> On 19 May 2017, at 6:21pm, Paul Sanderson 
> wrote:
>
> > Is the autoindex associated when using unique with an integer primary key
> > definition redundant?
> >
> > I have seen a number of DBs/tables created in the following form:
> >
> > Create table test(id integer unique primary key);
> > Insert into test values (1);
> > Insert into test values (2);
> > Insert into test values (3);
> >
> > The table is created and populated as expected, but an
> > sqlite_autoindex_test_1 is also created with content that mirrors exactly
> > the rowid/id.
> >
> > Is the autoindex redundant and is this an opportunity for optimisation?
>
> There’s no point in declaring the primary key as unique.  A primary key
> has to be unique.  SQLite will enforce uniqueness whether you tell it to or
> not.
>
> sqlite> Create table test1(id integer primary key);
> sqlite> Create table test2(id integer unique primary key);
> sqlite> Create table test3(id integer primary key unique);
> sqlite> PRAGMA index_list(test1);
> sqlite> PRAGMA index_list(test2);
> 0|sqlite_autoindex_test2_1|1|u|0
> sqlite> PRAGMA index_list(test3);
> 0|sqlite_autoindex_test3_1|1|u|0
> sqlite>
>
> It appears that SQLite does not notice that you have declared a primary
> key as unique.  It’s really this that’s causing the problem.
>
> Simon.
> ___
> 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] auntondex with unique and integer primary key

[J. King]

On May 19, 2017 1:21:49 PM EDT, Paul Sanderson  
wrote:
>Is the autoindex associated when using unique with an integer primary
>key
>definition redundant?
>
>I have seen a number of DBs/tables created in the following form:
>
>Create table test(id integer unique primary key);
>
>Insert into test values (1);
>
>Insert into test values (2);
>
>Insert into test values (3);
>
>The table is created and populated as expected, but an
>sqlite_autoindex_test_1 is also created with content that mirrors
>exactly
>the rowid/id.
>
>Is the autoindex redundant and is this an opportunity for optimisation?
>
>
>Using 3.18.0
>
>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

A primary key is by definition unique, so the unique constraint is needless 
duplication. Note, too, that the primary key is only an alias for the rowid 
when using the formulation "integer primary key" exactly. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auntondex with unique and integer primary key

[Simon Slavin]

On 19 May 2017, at 6:21pm, Paul Sanderson  wrote:

> Is the autoindex associated when using unique with an integer primary key
> definition redundant?
> 
> I have seen a number of DBs/tables created in the following form:
> 
> Create table test(id integer unique primary key);
> Insert into test values (1);
> Insert into test values (2);
> Insert into test values (3);
> 
> The table is created and populated as expected, but an
> sqlite_autoindex_test_1 is also created with content that mirrors exactly
> the rowid/id.
> 
> Is the autoindex redundant and is this an opportunity for optimisation?

There’s no point in declaring the primary key as unique.  A primary key has to 
be unique.  SQLite will enforce uniqueness whether you tell it to or not.

sqlite> Create table test1(id integer primary key);
sqlite> Create table test2(id integer unique primary key);
sqlite> Create table test3(id integer primary key unique);
sqlite> PRAGMA index_list(test1);
sqlite> PRAGMA index_list(test2);
0|sqlite_autoindex_test2_1|1|u|0
sqlite> PRAGMA index_list(test3);
0|sqlite_autoindex_test3_1|1|u|0
sqlite> 

It appears that SQLite does not notice that you have declared a primary key as 
unique.  It’s really this that’s causing the problem.

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

Re: [sqlite] auntondex with unique and integer primary key

[Joseph L. Casale]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Paul Sanderson
> Sent: Friday, May 19, 2017 11:22 AM
> To: General Discussion of SQLite Database  us...@mailinglists.sqlite.org>
> Subject: [sqlite] auntondex with unique and integer primary key

> Is the autoindex redundant and is this an opportunity for optimisation?

See https://sqlite.org/autoinc.html and https://www.sqlite.org/rowidtable.html
for the nuances and rational for one versus the other.

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

Re: [sqlite] Thank you for your work

[Richard Hipp]

On 5/19/17, Claudio Bantaloukas  wrote:
>
> Lo and behold, the latest version handled these queries without issue,
> halved the time it took to run some other queries and has apparently not
> introduced any new issues.
>

It's always nice to hear that we did something right :-)

-- 
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] auntondex with unique and integer primary key

[Paul Sanderson]

Is the autoindex associated when using unique with an integer primary key
definition redundant?

I have seen a number of DBs/tables created in the following form:

Create table test(id integer unique primary key);

Insert into test values (1);

Insert into test values (2);

Insert into test values (3);

The table is created and populated as expected, but an
sqlite_autoindex_test_1 is also created with content that mirrors exactly
the rowid/id.

Is the autoindex redundant and is this an opportunity for optimisation?


Using 3.18.0

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

[sqlite] Thank you for your work

[Claudio Bantaloukas]

I had recently been tasked with verifying why our previous (and ancient) 
version of SQLite was having difficulty parsing some long and complicated 
queries our software was throwing at it. Before embarking in the full quest, I 
tried a more recent version to see whether things had improved.

Lo and behold, the latest version handled these queries without issue, halved 
the time it took to run some other queries and has apparently not introduced 
any new issues.

I'd like to thank the SQLite developers for their great work in maintaining 
this library. You guys rock!

We are currently using the SQLite library at the Cambridge Crystallographic 
Data Centre, a non-profit organisation that caters to the crystallographic 
community as the home of small molecule crystallography data and as a provider 
of software for pharmaceutical discovery, materials development, research and 
education.

Kind Regards,
Claudio Bantaloukas

LEGAL NOTICE
Unless expressly stated otherwise, information contained in this
message is confidential. If this message is not intended for you,
please inform postmas...@ccdc.cam.ac.uk and delete the message.
The Cambridge Crystallographic Data Centre is a company Limited
by Guarantee and a Registered Charity.
Registered in England No. 2155347 Registered Charity No. 800579
Registered office 12 Union Road, Cambridge CB2 1EZ.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 bug in SQLite 3.18.0

[Krzysztof Małysa]

When SQLIte is compiled with sanitizers they report invalid readouts and
undefined-behavior:
```

sqlite3.c:188900:5: runtime error: index 11 out of bounds for type 'char
[8]'
SUMMARY: AddressSanitizer: undefined-behavior sqlite3.c:188900:5 in
sqlite3.c:189142:30: runtime error: index 12 out of bounds for type 'char
[8]'
SUMMARY: AddressSanitizer: undefined-behavior sqlite3.c:189142:30 in
sqlite3.c:184801:14: runtime error: null pointer passed as argument 1,
which is declared to never be null
/usr/include/string.h:62:62: note: nonnull attribute specified here
SUMMARY: AddressSanitizer: undefined-behavior sqlite3.c:184801:14 in
```
Compilation command:
```
$ clang 4.c sqlite3.c -o 4 -pthread -ldl -lm -DSQLITE_ENABLE_FTS5
-fsanitize=address -fsanitize=undefined
```


Additional information:
```
$ clang -v
clang version 3.8.0-2ubuntu4 (tags/RELEASE_380/final)
Target: x86_64-pc-linux-gnu
Thread model: posix
InstalledDir: /usr/bin
Found candidate GCC installation: /usr/bin/../lib/gcc/i686-linux-gnu/5.4.0
Found candidate GCC installation: /usr/bin/../lib/gcc/i686-linux-gnu/6.0.0
Found candidate GCC installation: /usr/bin/../lib/gcc/x86_64-linux-gnu/5.4.0
Found candidate GCC installation: /usr/bin/../lib/gcc/x86_64-linux-gnu/6.0.0
Found candidate GCC installation: /usr/lib/gcc/i686-linux-gnu/5.4.0
Found candidate GCC installation: /usr/lib/gcc/i686-linux-gnu/6.0.0
Found candidate GCC installation: /usr/lib/gcc/x86_64-linux-gnu/5.4.0
Found candidate GCC installation: /usr/lib/gcc/x86_64-linux-gnu/6.0.0
Selected GCC installation: /usr/bin/../lib/gcc/x86_64-linux-gnu/5.4.0
Candidate multilib: .;@m64
Candidate multilib: 32;@m32
Candidate multilib: x32;@mx32
Selected multilib: .;@m64


$ uname -a
Linux quasarHP 4.4.0-77-generic #98-Ubuntu SMP Wed Apr 26 08:34:02 UTC 2017
x86_64 x86_64 x86_64 GNU/Linux
```


This behaviour appeared in the earlier versions of SQLite, for sure in


Hope this helps,
Krzysztof Małysa
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users