[sqlite] design problem involving trigger

[Will Parsons]

On Monday, 24 Aug 2015  2:46 AM -0400, Mark Lawrence wrote:
>> It's theoretically possible, but in that case I would be content to
>> force a difference in the title.  It should be possible to have the
>> following:
>> 
>> 'History of Scotland' | -> 'A. Jones'
>> 'History of Scotland' | -> 'T. Smith'
>> 'Manual of DOS'   | NULL
>> 
>> But, an attempt to insert another record 'Manual of DOS' with a NULL
>> author should fail.
>
> You can achieve this using a partial index[1] on the Books.title
> column, which is used only when the author is null. A test script to
> demonstrate:
>
> create table Books(
> id integer primary key,
> title text collate nocase not null,
> author references Authors(id),
> unique(title, author)
> );
>
> create table Authors(
> id integer primary key,
> name text unique not null check(name <> '')
> );
>
> -- **
> create unique index no_author_index on Books(title)
> where author is null;
> -- **
>
> insert into Authors values(1,'A. Jones');
> insert into Authors values(2,'T. Smith');
>
> insert into Books values(1, 'History of Scotland', 1);
> insert into Books values(2, 'History of Scotland', 2);
> insert into Books values(3, 'Manual of DOS', NULL);
>
> select
> b.title, a.name
> from
> Books b
> left join
> Authors a
> on
> a.id = b.author
> ;
> -- titlename  
> -- ---  --
> -- History of Scotland  A. Jones  
> -- History of Scotland  T. Smith  
> -- Manual of DOSNULL  
>
> insert into Books values(4, 'Manual of DOS', NULL);
> -- Error: near line 37: UNIQUE constraint failed: Books.title
>
> [1] https://www.sqlite.org/partialindex.html

Yes, this works and seems to me to be more elegant than my original
idea of replacing a NULL author field with a 0, so thanks to you and
to the others who responded with their suggestions.

-- 
Will

[sqlite] Performance problems on windows

[Richard Hipp]

I don't have any clues.

While most of our work happens on Linux, we do measure performance on
Windows from one release to the next (see for example item 23-b on the
most recent release check-list
https://www.sqlite.org/checklists/private/3081100/index#c23) and it
gets better from one release to the next.  So I don't know why you are
seeing a slowdown.

How do you measure?

On 8/24/15, Jakub Zakrzewski  wrote:
> Hi All,
>
> I finally got a chance to upgrade SQLite for our product from ancient
> 3.7.16.2. Initial tests on Linux were very promising - ranging from 33% to
> even 300% (for one degenerated case) speed improvement. So far so good.
> Problems begun when I have tested it on Windows. Depending on test case the
> new version is up to 0.28x slower! In the course of investigation I have
> managed to improve the performance by adding few missing indexes but this
> has influenced both old and new versions so the relative performance with
> 3.8.11.1 is still like one third worse.
>
> I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0 and the
> results are quite surprising:
> 3.7.16.2 -> 3.7.17.0 : ~16% faster
> 3.7.17.0 -> 3.8.0.0  : ~26% slower
> 3.7.16.2 -> 3.8.0.0  : ~15% slower
> 3.7.16.2 -> 3.8.11.1 : ~28% slower
>
> We use SQLite as backend ("persistent storage") to an implementation of a
> state machine. The queries are rather simple and each of them is too fast to
> measure (SQLite performance timer only has resolution of milliseconds) in
> the profiler it looks like the actual filesystem functions were taking more
> time in new versions but that tells me nothing really.
>
> Is there something I can tweak?
> The page size is set to 4K (NTFS file system), synchonous is OFF,
> journal_mode=truncated;
>
> With 10K objects all versions seem to perform equally: test takes ~45s. For
> 100K objects it's already [m]:[s]
> 3.7.16.2 : ~10:55
> 3.7.17.0 : ~09:30
> 3.8.0.0  : ~12:46
> 3.8.11.1 : ~15:08
>
> I'm out of ideas here. Can someone help me with further investigation?
>
> --
> Gruesse,
> Jakub
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Threading-mode for an encrypted database

[Richard Hipp]

On 8/24/15, Daniel Coles  wrote:
> Hello,
>
> Our application is to use SQLite to store its data.  It uses a
> multi-threaded server, though in most cases SQLite would be accessed by one
> thread at a time.  The application runs on Microsoft Windows.
>
> Our initial SQLite implementation followed our interpretation of the
> guidelines laid out here
> (http://www.sqlite.org/cvstrac/wiki?p=MultiThreading), in that:
> * We use "multi-thread" mode
> * Threads create connections ('sqlite3' objects) to the database when
> needed, and then close them again once an operation is complete
> * The 'sqlite3' objects are not shared between threads
>
> Implementing encryption through the SQLite Encryption Extension (SEE) is
> causing me to question whether this is the correct strategy.  I would like
> to know:
> * Is there any sharing of information between the 'sqlite3' objects?  In
> particular, the in-memory unencrypted database page-caches; are those
> shared?

No, and no.  Unless you turn on shared-cache mode
(https://www.sqlite.org/sharedcache.html) and stop constantly closing
and reopening your database connections, then the answer would be Yes
and Yes.

> * What governs the lifetime of the page-caches?

The page cache is discarded for lots of reasons:  (1) you close the
database connection. (2) another process modifies the database file,
rendering the cache stale. (3) You invoke PRAGMA shrink_memory.  Etc.


> * We do not want multiple application instances to modify the same file at
> once.

SQLite automatically serializes writes.  You do not have to do
anything to make this happen.

> * What happens if an application unexpectedly exits while having a SQLite
> file exclusively locked?  Is the lock automatically released by the
> underlying OS?

Yes.  And any uncommitted changes are rolled back.

>
> My current suspicion is that I will need to change our SQLite threading so
> that we have one persistent 'sqlite3' connection that is shared between
> threads.  Does that sound advisable?
>

That depends on so many things that it is hard to say.  You could
certainly try it and see how it works out!  But I think if you just
avoid constantly opening and closing connections, things will go
better.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQlite database access over wireless network

[Eduardo Morras]

On Mon, 24 Aug 2015 16:43:40 + (UTC)
Mike McWhinney  wrote:

> Hello,
> I am trying to troubleshoot a problem that I'm having with a program
> usingSQLite over a wireless network.? I know that wireless networks
> can havetheir own sets of issues that cause database access problems.
> What is happening is that something that takes 1 or 2 seconds on a
> wirednetwork will take 15-20 seconds over wireless.? 
> 
> I am using System.Data.SQLite, which is based on version SQLite 3.
> 8.8.3 (version 1.0.96.0)with C# Visual Studio. The program is
> installed and runs off a network. The database is also on a network.
> The reads are somewhat slower running on a wired network than when
> run on a local machine.However, when run over wireless it is
> significantly slower.? These are mostly reads onfairly simple
> queries.? 
> 
> Are there any pragmas that may be used to increase the latency or to
> allow thereads to process faster across a network??? Or are there any
> other tips or tricksthat may be used to speed up access? This is a
> multi-user database with about 2-10 usersreading/writing data. Most
> of the time, the client application sits idle.? The SQLite
> connectionsare opened only on demand, then closed after I/O
> operations. However this opening and closingis kept to a minimum.
> ThanksMike

As Simon Slavin and R.Smith tells you, it's not an scenary (WiFi and concurrent 
r/w access) where Sqlite will work well. Each time sqlite needs to create a 
temp file, lock a file, delete a file, etc... it must wait to network and 
remote os. Said that and if you really wants to work with sqlite, don't use a 
direct System.Data.Sqlite connection. Instead, send the queries (text queries) 
to a process who runs sqlite on local, queues the queries, execute them in 
order and return results to each user. It's a medium complex project and some 
corner cases should be consider. I'll try postgres instead.



---   ---
Eduardo Morras 

[sqlite] PRAGMA cache_size and ATTACHED DBs

[R.Smith]

The cache size pragma dictates to (and affects) the connection, not the DB.

So yes.


On 2015-08-24 07:30 PM, jose i cabrera wrote:
>
> Greetings!
>
> When connecting to a DB, and setting a PRAGMA cache_size, will the 
> attached DB also respond/behave the same way/size set by the original 
> connection?
>
> Thanks.
>
> jos?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQlite database access over wireless network

[R.Smith]

On 2015-08-24 06:43 PM, Mike McWhinney wrote:
> Hello,
> I am trying to troubleshoot a problem that I'm having with a program 
> usingSQLite over a wireless network.  I know that wireless networks can 
> havetheir own sets of issues that cause database access problems.
> What is happening is that something that takes 1 or 2 seconds on a 
> wirednetwork will take 15-20 seconds over wireless.
>
> I am using System.Data.SQLite, which is based on version SQLite 3. 8.8.3 
> (version 1.0.96.0)with C# Visual Studio.
> The program is installed and runs off a network. The database is also on a 
> network.
> The reads are somewhat slower running on a wired network than when run on a 
> local machine.However, when run over wireless it is significantly slower.  
> These are mostly reads onfairly simple queries.
>
> Are there any pragmas that may be used to increase the latency or to allow 
> thereads to process faster across a network?   Or are there any other tips or 
> tricksthat may be used to speed up access? This is a multi-user database with 
> about 2-10 usersreading/writing data. Most of the time, the client 
> application sits idle.  The SQLite connectionsare opened only on demand, then 
> closed after I/O operations. However this opening and closingis kept to a 
> minimum.

This is a hard one. To mention the obvious: 2-10 users needing data over 
a network...? Why on Earth would you use SQLite for this? MySQL/MariaDB 
and Postgres (to name just 2) will do that job significantly better 
(with respect to handling the networking anyway) and they cost exactly 
the same amount.

http://www.sqlite.org/whentouse.html

Secondly, the simplicity of the query does not enter into it. What costs 
time is acquiring locks, reading bytes, etc. A lot of that goes on in 
the background while you do what feels like "simple" queries. I would 
suggest using larger cache amounts and the like, but then you close and 
open DB connections (if I understand correct), so will hardly notice a 
benefit for that. (This is where server based DB's as mentioned above 
really shines).

Lastly, the really bad news: Wireless networks are slow. That's how they 
roll - especially when the airspace is congested (as is commonly the 
case). Slower by a factor of 10 (as in your case) is not only possible, 
but rather likely. Improved bandwidth routers or better (read: more 
expensive) routers will be the go-to solution for that.

Sorry for the lack of good news...
Ryan

[sqlite] Proper way to abort

[Jean-Christophe Deschamps]

>
>I have some queries that may take 5-15 seconds to complete. Sometimes 
>the situation changes shortly after starting the query where my 
>program does not need those results anymore and the program wants to 
>abort and begin a different query instead.
>
>My question is: What is the proper way to abort a query, or other 
>operation during execution that will not cause any issues? By issues I 
>mean files not being closed, or memory not being free'd and such 
>because the operation was aborted and did not have finish normall, but 
>I can continue normally after the abort..

Maybe http://www.sqlite.org/c3ref/interrupt.html 

[sqlite] Proper way to abort

[Zsbán Ambrus]

On Mon, Aug 24, 2015 at 6:22 PM, Scott Doctor  wrote:
> I have some queries that may take 5-15 seconds to complete. Sometimes the
> situation changes shortly after starting the query where my program does not
> need those results anymore and the program wants to abort and begin a
> different query instead.
>
> My question is: What is the proper way to abort a query, or other operation
> during execution that will not cause any issues?

You can use the sqlite3_progress_handler function for this, as
documented in "http://sqlite.org/c3ref/progress_handler.html;.

-- Ambrus

[sqlite] SQlite database access over wireless network

[Simon Slavin]

On 24 Aug 2015, at 5:43pm, Mike McWhinney  wrote:

> What is happening is that something that takes 1 or 2 seconds on a 
> wirednetwork will take 15-20 seconds over wireless.  

I did two downloads of a big .zip file earlier today, one via WiFi, the other 
via Ethernet.  They came from the same server and went to the same laptop.

The WiFi download took 50 minutes.
The Ethernet download took  9 minutes.

WiFi is slower than ethernet.

Simon.

[sqlite] Wierd Locking problem

[Luc Andre]

We use straight SQLITE3 PHP Extension sot we don\ not have that much
customization level.

On Mon, Aug 24, 2015 at 5:44 PM, Eduardo Morras  wrote:

> On Mon, 24 Aug 2015 16:03:24 +0200
> Luc Andre  wrote:
>
> > Hi All,
> >
> > Our web servers use a daily updated sqlite database file.
> >
> > The file is opened by multiple (apache/PHP) threads but always in
> > read only mode:
> >
> > $db = new SQLite3(DB_FILE, SQLITE3_OPEN_READONLY);
> >
> > The file itself has no write access.
> >
> > -rw-r--r-- 1 root root 225759232 Aug 24 13:43 db.sqlite
> >
> > But sometimes we get PHP warnings:
> >
> > PHP Notice: SQLite3Stmt::execute(): Unable to execute statement:
> > database is locked
> > SQLite3::prepare(): Unable to prepare statement: 5, database is locked
> > SQLite3::querySingle(): Unable to execute statement: database is
> > locked
> >
> > We can not understand how a read only file can get locked.
>
> Some hints (some of them from documentation):
>
> a) If you use wal mode then you can't open it in read only mode, it' will
> be on read-write mode.
> b) If you have some triggers that modifies, inserts or delete data, you'll
> get a db locked if any attempts to fire when another is running.
> c) If you use wal mode and wal checkpoint isn't on passive mode, you'll
> get a db busy.
> d) Check performance on serialize threading mode (2), instead of
> multi-thread mode (1).
> e) If a journal size limit is set, whichever journal mode, a db lock
> happens.
> f) Some pragmas lock db, update user_version, cache_spill=on,
>
>
> > Any hint / fix ?
> >
> > Regards,
> >
> > Luc
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wierd Locking problem

[Eduardo Morras]

On Mon, 24 Aug 2015 16:03:24 +0200
Luc Andre  wrote:

> Hi All,
> 
> Our web servers use a daily updated sqlite database file.
> 
> The file is opened by multiple (apache/PHP) threads but always in
> read only mode:
> 
> $db = new SQLite3(DB_FILE, SQLITE3_OPEN_READONLY);
> 
> The file itself has no write access.
> 
> -rw-r--r-- 1 root root 225759232 Aug 24 13:43 db.sqlite
> 
> But sometimes we get PHP warnings:
> 
> PHP Notice: SQLite3Stmt::execute(): Unable to execute statement:
> database is locked
> SQLite3::prepare(): Unable to prepare statement: 5, database is locked
> SQLite3::querySingle(): Unable to execute statement: database is
> locked
> 
> We can not understand how a read only file can get locked.

Some hints (some of them from documentation):

a) If you use wal mode then you can't open it in read only mode, it' will be on 
read-write mode.
b) If you have some triggers that modifies, inserts or delete data, you'll get 
a db locked if any attempts to fire when another is running.
c) If you use wal mode and wal checkpoint isn't on passive mode, you'll get a 
db busy.
d) Check performance on serialize threading mode (2), instead of multi-thread 
mode (1).
e) If a journal size limit is set, whichever journal mode, a db lock happens.
f) Some pragmas lock db, update user_version, cache_spill=on, 


> Any hint / fix ?
> 
> Regards,
> 
> Luc
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 

[sqlite] Threading-mode for an encrypted database

[Daniel Coles]

Hello,

Our application is to use SQLite to store its data.  It uses a multi-threaded 
server, though in most cases SQLite would be accessed by one thread at a time.  
The application runs on Microsoft Windows.

Our initial SQLite implementation followed our interpretation of the guidelines 
laid out here (http://www.sqlite.org/cvstrac/wiki?p=MultiThreading), in that:
* We use "multi-thread" mode
* Threads create connections ('sqlite3' objects) to the database when needed, 
and then close them again once an operation is complete
* The 'sqlite3' objects are not shared between threads

Implementing encryption through the SQLite Encryption Extension (SEE) is 
causing me to question whether this is the correct strategy.  I would like to 
know:
* Is there any sharing of information between the 'sqlite3' objects?  In 
particular, the in-memory unencrypted database page-caches; are those shared?
* What governs the lifetime of the page-caches?
* We do not want multiple application instances to modify the same file at 
once.  I presume that the only sensible option that we have is to set the 
locking_mode to exclusive, but my guess is that this will block all other 
'sqlite3' connections within the same application.  Or is the SQLite database 
simply locked to the process?
* What happens if an application unexpectedly exits while having a SQLite file 
exclusively locked?  Is the lock automatically released by the underlying OS?

My current suspicion is that I will need to change our SQLite threading so that 
we have one persistent 'sqlite3' connection that is shared between threads.  
Does that sound advisable?

Sorry that this is a number of questions wrapped up in a single e-mail.  I am 
mostly wanting to verify that my understanding of the issues is correct, or 
whether SQLite is doing clever stuff behind the scenes with multiple 
connections within a single application instance.

Thank you in advance for any help that you're able to offer,

Daniel

[sqlite] Wierd Locking problem

[Simon Slavin]

On 24 Aug 2015, at 3:45pm, Luc Andre  wrote:

> I'm sure no process open the file using the SQLite API in write mode.

You are, but the software has to check for it, and occasionally two of your 
threads do the same check at the same time.

I'm afraid that if you have already set a timeout I don't know why you are 
seeing problems.  I hope someone else can help.

Simon.

[sqlite] Wierd Locking problem

[Luc Andre]

On Mon, Aug 24, 2015 at 4:38 PM, Simon Slavin  wrote:

>
> On 24 Aug 2015, at 3:03pm, Luc Andre  wrote:
>
> > PHP Notice: SQLite3Stmt::execute(): Unable to execute statement: database
> > is locked
> > SQLite3::prepare(): Unable to prepare statement: 5, database is locked
> > SQLite3::querySingle(): Unable to execute statement: database is locked
> >
> > We can not understand how a read only file can get locked.
>
> Although the connection you're using is readonly, another program may have
> the same database file open for writing.  So your readonly thread has to
> check to see that nothing is making changes to the file.  Occasionally two
> of your threads make this check at the same time and argue with one-another
> about who goes first.
>
>
I'm sure no process open the file using the SQLite API in write mode.
If you suspect a cron job that open open the file (as a regular binary
file) in rw mode, I doubt it, but the SQLIte opening should have failed,
and the opening is always fine.


> > Any hint / fix ?
>
> For every connection to the database set a timeout using this function:
>
> 
>
> which will look something like
>
> $dbConnection->busyTimeout(30);
>
> I'm using 5 minutes just to give an unattended program time to survive a
> network problem.  I don't expect any access to every really take 5 minutes.
>
>
We already did a $dbConnection->busyTimeout(500); (500ms is already huge
for our web server latency) with no luck.



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

[sqlite] SQlite database access over wireless network

[Mike McWhinney]

Hello,
I am trying to troubleshoot a problem that I'm having with a program 
usingSQLite over a wireless network.? I know that wireless networks can 
havetheir own sets of issues that cause database access problems.
What is happening is that something that takes 1 or 2 seconds on a wirednetwork 
will take 15-20 seconds over wireless.? 

I am using System.Data.SQLite, which is based on version SQLite 3. 8.8.3 
(version 1.0.96.0)with C# Visual Studio.
The program is installed and runs off a network. The database is also on a 
network.
The reads are somewhat slower running on a wired network than when run on a 
local machine.However, when run over wireless it is significantly slower.? 
These are mostly reads onfairly simple queries.? 

Are there any pragmas that may be used to increase the latency or to allow 
thereads to process faster across a network??? Or are there any other tips or 
tricksthat may be used to speed up access? This is a multi-user database with 
about 2-10 usersreading/writing data. Most of the time, the client application 
sits idle.? The SQLite connectionsare opened only on demand, then closed after 
I/O operations. However this opening and closingis kept to a minimum.
ThanksMike

[sqlite] Wierd Locking problem

[Luc Andre]

Hi All,

Our web servers use a daily updated sqlite database file.

The file is opened by multiple (apache/PHP) threads but always in read only
mode:

$db = new SQLite3(DB_FILE, SQLITE3_OPEN_READONLY);

The file itself has no write access.

-rw-r--r-- 1 root root 225759232 Aug 24 13:43 db.sqlite

But sometimes we get PHP warnings:

PHP Notice: SQLite3Stmt::execute(): Unable to execute statement: database
is locked
SQLite3::prepare(): Unable to prepare statement: 5, database is locked
SQLite3::querySingle(): Unable to execute statement: database is locked

We can not understand how a read only file can get locked.

Any hint / fix ?

Regards,

Luc

[sqlite] Wierd Locking problem

[Simon Slavin]

On 24 Aug 2015, at 3:03pm, Luc Andre  wrote:

> PHP Notice: SQLite3Stmt::execute(): Unable to execute statement: database
> is locked
> SQLite3::prepare(): Unable to prepare statement: 5, database is locked
> SQLite3::querySingle(): Unable to execute statement: database is locked
> 
> We can not understand how a read only file can get locked.

Although the connection you're using is readonly, another program may have the 
same database file open for writing.  So your readonly thread has to check to 
see that nothing is making changes to the file.  Occasionally two of your 
threads make this check at the same time and argue with one-another about who 
goes first.

> Any hint / fix ?

For every connection to the database set a timeout using this function:



which will look something like

$dbConnection->busyTimeout(30);

I'm using 5 minutes just to give an unattended program time to survive a 
network problem.  I don't expect any access to every really take 5 minutes.

Simon.

[sqlite] Performance problems on windows

[Jakub Zakrzewski]

Hi All,

I finally got a chance to upgrade SQLite for our product from ancient 3.7.16.2. 
Initial tests on Linux were very promising - ranging from 33% to even 300% (for 
one degenerated case) speed improvement. So far so good. Problems begun when I 
have tested it on Windows. Depending on test case the new version is up to 
0.28x slower! In the course of investigation I have managed to improve the 
performance by adding few missing indexes but this has influenced both old and 
new versions so the relative performance with 3.8.11.1 is still like one third 
worse.

I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0 and the 
results are quite surprising:
3.7.16.2 -> 3.7.17.0 : ~16% faster
3.7.17.0 -> 3.8.0.0  : ~26% slower
3.7.16.2 -> 3.8.0.0  : ~15% slower
3.7.16.2 -> 3.8.11.1 : ~28% slower

We use SQLite as backend ("persistent storage") to an implementation of a state 
machine. The queries are rather simple and each of them is too fast to measure 
(SQLite performance timer only has resolution of milliseconds) in the profiler 
it looks like the actual filesystem functions were taking more time in new 
versions but that tells me nothing really.

Is there something I can tweak?
The page size is set to 4K (NTFS file system), synchonous is OFF, 
journal_mode=truncated;

With 10K objects all versions seem to perform equally: test takes ~45s. For 
100K objects it's already [m]:[s]
3.7.16.2 : ~10:55
3.7.17.0 : ~09:30
3.8.0.0  : ~12:46
3.8.11.1 : ~15:08

I'm out of ideas here. Can someone help me with further investigation?

--
Gruesse,
Jakub

[sqlite] PRAGMA cache_size and ATTACHED DBs

[jose i cabrera]

thanks.


On 8/24/2015 1:34 PM, R.Smith wrote:
> The cache size pragma dictates to (and affects) the connection, not 
> the DB.
>
> So yes.
>
>
> On 2015-08-24 07:30 PM, jose i cabrera wrote:
>>
>> Greetings!
>>
>> When connecting to a DB, and setting a PRAGMA cache_size, will the 
>> attached DB also respond/behave the same way/size set by the original 
>> connection?
>>
>> Thanks.
>>
>> jos?
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] PRAGMA cache_size and ATTACHED DBs

[jose i cabrera]

Greetings!

When connecting to a DB, and setting a PRAGMA cache_size, will the 
attached DB also respond/behave the same way/size set by the original 
connection?

Thanks.

jos?

[sqlite] Lua inside SQLite

[Dominique Devienne]

On Sun, Aug 23, 2015 at 10:55 PM, Richard Hipp  wrote:

> Regular SQL functions always return scalars in SQLite.
>
> See https://www.sqlite.org/src/artifact/b8fb7befd85b3a9b for an
> example of how to implement table-valued functions.  This is a new
> feature so there is no documentation on it yet.  But the example is
> well-commented.  This capability will be in the next release, so
> you'll have to compile from trunk if you want to use it right away -
> it is not found in 3.8.11.1.
>

The example only uses literals for the function's arguments.
Can columns coming from a join be used as well?

A canonical use-case would be to list values of a JSON-formatted text
columns, like the following made-up SQL:

create table t (id INTEGER PRIMARY KEY, json TEXT);
insert into t values (1, '[1]'), (2, '[]'), (3,'[3,4]);
select id, j.column_value as json_array_entry from t, array_values(t.json)
j;
id | json_array_entry
1 | 1
3 | 3
3 | 4

See also https://docs.oracle.com/database/121/ADXDB/json.htm
and
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns004.htm

Thanks, --DD

[sqlite] design problem involving trigger

[Mark Lawrence]

On Mon Aug 24, 2015 at 08:46:57AM +0200, Mark Lawrence wrote:
> 
> You can achieve this using a partial index[1] on the Books.title
> column, which is used only when the author is null. A test script to

My apologies. It appears from the mailing list archive this was already
mentioned, but I didn't see those messages in my inbox.

-- 
Mark Lawrence

[sqlite] Compile warnings

[David Bennett]

I think we've beaten the philosophy to death and we're largely in agreement.

I'm not sure we actually came up with a firm recommendation as to what to do
about this specific warning in this particular line of code with this
compiler. Ostrich treatment maybe, and rely on the general Sqlite
disclaimer?

Regards
David M Bennett FACS

MD Powerflex Corporation, creators of PFXplus
To contact us, please call +61-3-9548-9114 or go to
www.pfxcorp.com/contact.htm

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
Robison
Sent: Monday, 24 August 2015 8:25 AM
To: davidb at pfxcorp.com; General Discussion of SQLite Database

Subject: Re: [sqlite] Compile warnings

On Sat, Aug 22, 2015 at 8:07 PM, David Bennett  wrote:

> Of course that is the aim, as always.
>
>
>
> In this particular case, maximally portable code (that will compile 
> and execute correctly on all conforming compilers) must (a) ensure 
> that the pointer argument is valid (b) ensure that the length is valid, or
zero.
> Where reasonably possible both should be done statically. If 
> conditional code is introduced then it must be tested with all branches
covered.
>

Agreed, and in C89 NULL was a valid pointer argument in this context as long
as the length was zero. That has nothing to do with this particular thread,
but I referenced it just as a point of "C99 changed the semantics of what is
valid, invalidating previously valid code". Projects that took advantage of
that can either modify their code to accommodate the newer standard or leave
it along claiming it conforms to the intended / desired standard. In that
case, it was easy to change to code to be compatible with both standards and
it was done. In this case (gcc warning about possible argument order error
due to a constant expression) can be equally accommodated, but it's not a
matter of standards compliance. It *is* a matter of one of the (or perhaps
*the*) most used compilers bellyaching about something that is not wrong or
invalid in any way. So is the code modified to suppress the warning, is the
warning disabled globally, locally, or just ignored? I can see the benefits
to making the change and to not making the change.

> As always, it may be the case that one or more compilers may issue 
> warnings for code that is fully compliant with the standard and fully 
> tested. Sadly there may even be compilers that compile the code 
> incorrectly (a compiler bug). The question of how to handle undesired 
> warnings or compiler bugs on code that is known to be correct and 
> compliant is always a judgement call. In my opinion the solution 
> chosen should always be as fine-grained as possible (such as a 
> compiler-specific conditional), but the downside is that the code can 
> become littered with references to specific problems in specific compilers
and thus become harder to work with.
>

I agree completely. Most of us don't have to worry about this too much
because we target one platform (most code is not written with portability in
mind). SQLite is not most projects.


> In my opinion changes that are visible to other compilers should be 
> avoided unless the changed code is an equally valid solution to the 
> problem at hand and/or the problem affects multiple compilers. In this 
> light adding an if-test would be an incorrect choice (and would 
> require an additional set of tests). Suppressing the warning 
> specifically for this compiler would be a preferable solution.
>

Agreed for the most part. Just to be clear: I never said "don't make this
change" (I don't think). I just wanted to bring up the thought that an if
statement *might* degrade performance in a code base that has been carefully
tuned to maximize efficiency.  I wasn't thinking of test cases or such, just
efficiency. That being said, if gcc is generating the warning because a
constant expression has a value of zero, an if statement might actually
increase performance by providing the compiler with the knowledge that it
can completely remove the corresponding memset because the expression will
always be false. In that case, add the if might be the exact right thing to
do.

Again we're to the point of "there is no one universal right solution to
this issue". The only thing I can say is: not all warnings are equally bad,
and I will review warnings that are generated from third party code (such as
SQLite) but I rarely will do anything to try to suppress them. Making my
code right is a hard enough task. I don't need to "fix" third party code (as
long as it passes testing).

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

[sqlite] ESC_BAD_ACCESS when using a background thread

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/24/2015 03:08 AM, Jeff M wrote:
> I've checked all of your suggestions and nothing is amiss.

You ran valgrind and it said everything is fine?  That would be shocking.

> I don't understand how the main thread can run before the
> background task has completed -- and specifically while sqlite_step
> is still executing.

If the database is busy/locked then sqlite_step can relinquish the
mutex, call the busy handler and try again.

> Apparently, sqlite_step() can relinquish control to the main
> thread.

SQLite does not contain a thread scheduler (we'll ignore pragma threads).

You've either got 100% correct code in which case the underlying
issues are to do with concurrency (need to check memory barriers,
marking as volatile etc), or there is a bug in the code related to
object/pointer lifetimes or similar.  With the latter changing things
changes where collateral damage happens, but doesn't actually fix the
issue.

It is almost certainly the second case.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlXbSzwACgkQmOOfHg372QR/XACfV5Iw0umJ1smYygE/Komcemx0
46gAnjumLvNw1/fj2uFpIEdTwYnmhlOd
=HcCt
-END PGP SIGNATURE-

[sqlite] Proper way to abort

[Scott Doctor]

Sheesh, how did I miss that. Guess I need new glasses. Thats 
exactly what I was looking for.


Scott Doctor
scott at scottdoctor.com
--

On 8/24/2015 9:39 AM, Jean-Christophe Deschamps wrote:
>
>>
>> I have some queries that may take 5-15 seconds to complete. 
>> Sometimes the situation changes shortly after starting the 
>> query where my program does not need those results anymore 
>> and the program wants to abort and begin a different query 
>> instead.
>>
>> My question is: What is the proper way to abort a query, or 
>> other operation during execution that will not cause any 
>> issues? By issues I mean files not being closed, or memory 
>> not being free'd and such because the operation was aborted 
>> and did not have finish normall, but I can continue normally 
>> after the abort..
>
> Maybe http://www.sqlite.org/c3ref/interrupt.html
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>
>
>

[sqlite] Compile warnings

[Scott Robison]

On Aug 24, 2015 6:29 AM, "David Bennett"  wrote:
>
> I think we've beaten the philosophy to death and we're largely in
agreement.
>
> I'm not sure we actually came up with a firm recommendation as to what to
do
> about this specific warning in this particular line of code with this
> compiler. Ostrich treatment maybe, and rely on the general Sqlite
> disclaimer?

Well, I haven't inspected the code closely. My discussions have been purely
philosophical. :)

If the warning has to do with a constant zero expression, then I think I'd
probably go ahead and add the if statement at this point, giving the
optimizer the opportunity to completely remove the code fragment, even
though that would likely just change warnings.

If the expression is not constant, I don't have a strong opinion on how to
suppress or if to suppress. Given SQLite's focus on performance (and that
the code is correct either way) I'd probably profile and see if performance
was impacted and make the decision then. If performance was clearly better
with an if statement I'd make the change. If not suppress the warning
conditionally if possible (gcc is a very common choice of compiler and
worth having a clean build) or ignore it if not.

>
> Regards
> David M Bennett FACS
>
> MD Powerflex Corporation, creators of PFXplus
> To contact us, please call +61-3-9548-9114 or go to
> www.pfxcorp.com/contact.htm
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
> Robison
> Sent: Monday, 24 August 2015 8:25 AM
> To: davidb at pfxcorp.com; General Discussion of SQLite Database
> 
> Subject: Re: [sqlite] Compile warnings
>
> On Sat, Aug 22, 2015 at 8:07 PM, David Bennett  wrote:
>
> > Of course that is the aim, as always.
> >
> >
> >
> > In this particular case, maximally portable code (that will compile
> > and execute correctly on all conforming compilers) must (a) ensure
> > that the pointer argument is valid (b) ensure that the length is valid,
or
> zero.
> > Where reasonably possible both should be done statically. If
> > conditional code is introduced then it must be tested with all branches
> covered.
> >
>
> Agreed, and in C89 NULL was a valid pointer argument in this context as
long
> as the length was zero. That has nothing to do with this particular
thread,
> but I referenced it just as a point of "C99 changed the semantics of what
is
> valid, invalidating previously valid code". Projects that took advantage
of
> that can either modify their code to accommodate the newer standard or
leave
> it along claiming it conforms to the intended / desired standard. In that
> case, it was easy to change to code to be compatible with both standards
and
> it was done. In this case (gcc warning about possible argument order error
> due to a constant expression) can be equally accommodated, but it's not a
> matter of standards compliance. It *is* a matter of one of the (or perhaps
> *the*) most used compilers bellyaching about something that is not wrong
or
> invalid in any way. So is the code modified to suppress the warning, is
the
> warning disabled globally, locally, or just ignored? I can see the
benefits
> to making the change and to not making the change.
>
> > As always, it may be the case that one or more compilers may issue
> > warnings for code that is fully compliant with the standard and fully
> > tested. Sadly there may even be compilers that compile the code
> > incorrectly (a compiler bug). The question of how to handle undesired
> > warnings or compiler bugs on code that is known to be correct and
> > compliant is always a judgement call. In my opinion the solution
> > chosen should always be as fine-grained as possible (such as a
> > compiler-specific conditional), but the downside is that the code can
> > become littered with references to specific problems in specific
compilers
> and thus become harder to work with.
> >
>
> I agree completely. Most of us don't have to worry about this too much
> because we target one platform (most code is not written with portability
in
> mind). SQLite is not most projects.
>
>
> > In my opinion changes that are visible to other compilers should be
> > avoided unless the changed code is an equally valid solution to the
> > problem at hand and/or the problem affects multiple compilers. In this
> > light adding an if-test would be an incorrect choice (and would
> > require an additional set of tests). Suppressing the warning
> > specifically for this compiler would be a preferable solution.
> >
>
> Agreed for the most part. Just to be clear: I never said "don't make this
> change" (I don't think). I just wanted to bring up the thought that an if
> statement *might* degrade performance in a code base that has been
carefully
> tuned to maximize efficiency.  I wasn't thinking of test cases or such,
just
> efficiency. That being said, if gcc is generating the warning because a
> constant expression has 

[sqlite] Proper way to abort

[Scott Doctor]

I have some queries that may take 5-15 seconds to complete. 
Sometimes the situation changes shortly after starting the query 
where my program does not need those results anymore and the 
program wants to abort and begin a different query instead.

My question is: What is the proper way to abort a query, or 
other operation during execution that will not cause any issues? 
By issues I mean files not being closed, or memory not being 
free'd and such because the operation was aborted and did not 
have finish normall, but I can continue normally after the abort..


Scott Doctor
scott at scottdoctor.com
--

[sqlite] design problem involving trigger

[Mark Lawrence]

> It's theoretically possible, but in that case I would be content to
> force a difference in the title.  It should be possible to have the
> following:
> 
> 'History of Scotland' | -> 'A. Jones'
> 'History of Scotland' | -> 'T. Smith'
> 'Manual of DOS'   | NULL
> 
> But, an attempt to insert another record 'Manual of DOS' with a NULL
> author should fail.

You can achieve this using a partial index[1] on the Books.title
column, which is used only when the author is null. A test script to
demonstrate:

create table Books(
id integer primary key,
title text collate nocase not null,
author references Authors(id),
unique(title, author)
);

create table Authors(
id integer primary key,
name text unique not null check(name <> '')
);

-- **
create unique index no_author_index on Books(title)
where author is null;
-- **

insert into Authors values(1,'A. Jones');
insert into Authors values(2,'T. Smith');

insert into Books values(1, 'History of Scotland', 1);
insert into Books values(2, 'History of Scotland', 2);
insert into Books values(3, 'Manual of DOS', NULL);

select
b.title, a.name
from
Books b
left join
Authors a
on
a.id = b.author
;
-- titlename  
-- ---  --
-- History of Scotland  A. Jones  
-- History of Scotland  T. Smith  
-- Manual of DOSNULL  

insert into Books values(4, 'Manual of DOS', NULL);
-- Error: near line 37: UNIQUE constraint failed: Books.title

[1] https://www.sqlite.org/partialindex.html

Mark
-- 
Mark Lawrence

[sqlite] ESC_BAD_ACCESS when using a background thread

[Jeff M]

Simon, Roger:

I've checked all of your suggestions and nothing is amiss.

The background thread fetches the image data and caches it by adding the data 
to an NSMutableDictionary.  The main thread checks the dictionary and does the 
lazy-load only if the desired image data is not in the cache.

When the exception occurs on the main thread, execution stops where the 
dictionary is being checked (via objectForKey:), and the lazy-load background 
thread is executing inside of sqlite_step (sqlite_step has not returned when 
the exception occurs on the main thread).

This perplexes me.  I don't understand how the main thread can run before the 
background task has completed -- and specifically while sqlite_step is still 
executing.  Apparently, sqlite_step() can relinquish control to the main 
thread.  I'd appreciate some enlightenment on this implementation issue.

My solution -- The problem goes away if I move the caching step 
(setObject:forKey:) to the main thread (via 
dispatch_async(dispatch_get_main_queue() ).  It was intermittent so I can't be 
certain, but I've not been able to force a crash since making that change.

Jeff


> On Aug 23, 2015, at 7:05 AM, Simon Slavin  wrote:
> 
> Are you checking the values returned by sqlite3_prepare, sqlite3_bind, and 
> sqlite3_step, to make sure they return SQLITE_OK ?

> On Aug 23, 2015, at 7:00 PM, Roger Binns  wrote:
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 08/23/2015 03:31 AM, Jeff M wrote:
>> sqlite3_step();   // occasionally crashes here (showing
>> ESC_BAD_ACCESS on main thread)
> 
> That has three very likely causes.  The first is that your internal
> state gets messed up, and the statement has actually been
> finalized/freed.  ie it is junk memory.  You can add assertions that
> check it is still a known statement pointer by checking this returns it:
> 
>  http://sqlite.org/c3ref/next_stmt.html
> 
> The second is that the memory where you stored the statement pointer
> is what is trashed.
> 
> The final cause is that some other code has memory bugs, causing
> damage to SQLite's data structures.
> 
>> It's not a zombie object issue (tested with NSZombieEnabled).
> 
> Sadly that only checks Objective C objects, and not all memory.
> 
>> Any ideas on how to debug this?
> 
> I used valgrind running the app in the simulator.  (I also configure
> valgrind to never actually reuse memory.)
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
> 
> iEYEARECAAYFAlXaXpEACgkQmOOfHg372QRKZACfWyT6pEyNQ9sEKPbhFQ4pI/5G
> Nh0AniO5ESx9CIbB484/gYqjtfCsGUrM
> =Op+8
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Simon Slavin]

On 24 Aug 2015, at 2:48am, Simon Slavin  wrote:

> My suspicion here is that there's a design fault in ZFS.

To correct myself here, what I meant to write was that there was a fault in the 
implementation of ZFS that Paolo is using, not in the basic design of ZFS 
itself.

Simon.

[sqlite] ATTACH DATABASE statement speed

[Simon Slavin]

On 24 Aug 2015, at 2:32am, Roger Binns  wrote:

> On 08/19/2015 05:56 PM, Paolo Bolzoni wrote:
>> I left running the pragma quick check during the night and finished
>> in 2 hours and 46 minutes, so it is about 8 times slower than in
>> ext4. Zfs is an advanced filesystem plenty of features, but this
>> speed difference is too much I think.
> 
> I use btrfs which like zfs is also a copy on write filesystem.  It is
> possible for the files to get very fragmented which can result in
> dismal performance, even on an SSD even for reads.  Random small
> writes especially aggravate this.  btrfs has an autodefrag option that
> addresses this in the background, and SQLite is specifically mentioned
> as all the browsers use it behind the scenes as do many email clients.

Paolo is using Linux which does not do read-ahead optimization like some 
versions of Windows.  Therefore if he really is using an SSD then fragmentation 
is not an issue.

The other thing that makes me think fragmentation is a red herring is the 
extent to which ZFS is slower.  I have seen fragmentation make something take 
twice as long.  I might even believe, under some weird situation, it makes 
something take 4 times as long.  But not 6 or 8.

My suspicion here is that there's a design fault in ZFS.  I can't call it a bug 
under the rules of this list, since it is giving the right result, just 
ridiculously slowly.  But whatever it is I'm betting that it's not a problem in 
the code for SQLite, it's a problem in the code for ZFS.  And it'll take a ZFS 
expert to solve it, probably on a ZFS mailing list, not this one.

The one possible exception would be if the SQLite database page size and the 
ZFS sector size are causing problems with one-another.  But here too the best 
diagnosis would be done by a ZFS expert.  And anyway, /nothing/ should cause a 
slow-down of a factor of 8.

Simon.

[sqlite] DbFunctions.TruncateTime

[Kevin Benson]

On Sun, Aug 23, 2015 at 10:53 PM, Joe Mistachkin 
wrote:

>
> The LINQ provider for System.Data.SQLite does not provide this function
> directly; however, it may be possible to use one of the core date-time
> related SQL functions to do it?
>
> https://www.sqlite.org/lang_datefunc.html
>


Maybe something involving the "unixepoch" modifier ...via the connection
string in App.config ?

http://stackoverflow.com/questions/30444222/sqlite-and-entityframework-formatexception-on-timestamp

--
   --
  --
 --???--
K e V i N