Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Perry]

On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
> 
> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
> 
>> WHERE x IN (?1,?2,?3,?4...,?1000 )
> 
> People really do this ?  Sheesh.

It's a pretty common pattern. Sure, queries that are likely to use very large 
IN groups ought to be using carray or intarray but not everyone knows they 
exist :)

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

Re: [sqlite] DEF CON (wasL A license plate of NULL)

[Scott Perry]

On Aug 12, 2019, at 1:14 PM, Keith Medcalf  wrote:
> The only interesting thing is CVE-2015-7036, but I don't know if that was so 
> much an SQLite3 issue, as it was an issue in the use of the tokenizer by 
> Apple.  In either case, Apple fixed their bugs and SQLite3 was hardened 
> against some inappropriate (unintended by the application developer) uses of 
> the fts3_tokenizer() function.

Generally speaking, the issue was the availability of a built-in function 
accessible from the query language that took a function pointer as a parameter. 

Specifically, the vector was that an attacker with the ability to execute 
arbitrary SQL could cause execution to jump to the address of their choosing 
via the second parameter to fts3_tokenizer. Using established ROP techniques 
this could be used to gain control of the process.

The attack is even more interesting when combined with Check Point Research's 
recent publication (search for "SELECT code_execution FROM * USING SQLite;"), 
which explains how to gain control of a process from a database file by 
replacing all of its tables with views containing malicious queries.

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

Re: [sqlite] Backing up a SQLite database without the CLI

[Scott Perry]

`ATTACH` and iterating over all tables with `INSERT INTO SELECT` is how `VACUUM 
INTO` is implemented (see src/vacuum.c).

A less complicated way to back up the database might be to run `BEGIN` followed 
by `PRAGMA user_version` to acquire a read lock, after which you can safely 
copy the database (and wal, if appropriate) files directly.

Scott

On Mar 18, 2019, at 08:21, Jonathan Moules  wrote:
> 
> Hi Simon,
> 
> Thanks for your thoughts. Sorry, I should have been clearer: I have no way of 
> knowing if there are other open connections to the file - there may be as 
> it's a web-application. So I'll assume there are connections.
> 
> At this point I'm starting to think that the best option is to create a new 
> database with the requisite structure and copy the data across via an ATTACH 
> (there are only two tables and one will almost always be empty at this point).
> 
> Any other thoughts welcome though!
> Cheers,
> Jonathan
> 
> On 2019-03-18 13:37, Simon Slavin wrote:
>> On 18 Mar 2019, at 1:10pm, Jonathan Moules  
>> wrote:
>> 
>>> I was wondering if there was a good way of backing up an SQLite database if 
>>> you do *not* have access to the SQLite command line tool (which I know has 
>>> .backup - https://stackoverflow.com/a/25684912). [snip]
>>> I've considered simply running "PRAGMA wal_checkpointer;" and then copying 
>>> the file immediately after that, but that still seems prone to error.
>> Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
>> then open it again.  This does not take significantly more time, and it 
>> ensures that you will copy the right thing no matter what caching and 
>> optimization your tools are trying to do.
>> 
>> In more general terms ...
>> 
>> Are you trying to backup while the database is being modified using SQLite 
>> function calls ?
>> 
>> If not, then the data is just a single file.  Assuming all programs using 
>> SQLite calls closed their connections properly, just copy the file using any 
>> file copy commands, or file copy primatives in your favourite programming 
>> language.  In PHP I'd use the built-in copy command:
>> 
>> 
>> 
>> There may be a journal file there and you can copy that too, but just the 
>> database file is enough for a backup for emergency purposes.
>> 
>> If you're trying to copy a file while connections still have it open then 
>> you should use SQLite API calls to do it.  The obvious ones are in the 
>> SQLite Online Backup API, which is the set of calls underlying the '.backup' 
>> command you mentioned.  You can find documentation for this here:
>> 
>> 
>> 
>> Unfortunately I don't think the PHP sqlite3 tools give access to this API.
>> 
>> Hope that helps.  Don't hesitate to get back to us if we can help.
>> ___
>> 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

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

Re: [sqlite] Corruption of database when renaming a table

[Scott Perry]

The behaviour of ALTER TABLE changed in 3.25.0 and breaks compatibility with 
many existing applications. Your statements (corrected for use of single 
quotes, as Ryan mentioned) work for me when `PRAGMA legacy_alter_table=1;`.

More info on SQLite's website: https://www.sqlite.org/lang_altertable.html

Scott

> On Mar 15, 2019, at 06:29, tjerzyko  wrote:
> 
> I'm having corruption problem with a certain database file. You can download 
> it here:
> https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
> It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
> I open it with sqlite3.exe and run the following script:
> 
> begin;
> alter table fs_params rename to fs_params_20291_27910;
> CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
> drop table 'fs_params_20291_27910';
> alter table file_locks rename to file_locks_27446_24559;
> CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME 
> TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
> drop table 'file_locks_27446_24559';
> CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
> alter table cam_in_file rename to cam_in_file_22705_10035;
> end;
> 
> It throws the following error:
> Error: near line 9: error in trigger T_FILE_BDELETE: no such table: 
> main.file_locks_27446_24559
> 
> Probably my script is incorrect, but another thing worries me more. I open 
> the database again and:
> 
> e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> .tables
> AUDIO_IN_FILE  FS_ATTRIBUTES  REC_RECORDING_MODE
> AUDIO_IN_FRAGMENT  FS_PARAMS  REC_WORKING_MODE
> CAM_IN_FILEINTEGRITY_TEST STORAGE_BLOCK
> CAM_IN_FRAGMENTLOOP_FRAGMENT  T_SCHEDULE
> FILE   REC_LATEST_CONNECTION
> FILE_LOCKS REC_LIST_TIMESTAMP
> sqlite> select * from CAM_IN_FILE;
> Error: no such table: CAM_IN_FILE
> sqlite>
> 
> The table exists, but not quite. The database seems to be corrupted.
> 
> When I tried the same procedure on database created with newer SQLite 
> version, there were no such problems.
> 
> Tomasz Jerzykowski
> 
> 
> ___
> 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] SEE Temp Files

[Scott Perry]

> On Jan 29, 2019, at 10:12, Jens Alfke  wrote:
> 
>> On Jan 28, 2019, at 3:35 PM, Richard Hipp  wrote:
>> 
>> On the other
>> hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
>> held in memory, then intermediate results may be written to swap space
>> when the device gets under memory pressure. 
> 
> Mobile OSs don’t swap. (iOS for certain; I don’t have confirmation of this 
> for Android, but our local Android dev believes it’s so.)

While iOS 12 does not swap memory in the traditional sense, there are 
conditions under which it may write an idle application's memory to disk.

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

Re: [sqlite] -wal and -shm files left behind by libsqlite3.dylib

[Scott Perry]

This is expected behaviour. As Richard guessed, the system libsqlite3.dylib is 
built with SQLITE_ENABLE_PERSIST_WAL. For Darwin systems, the compromise of 
keeping the extra 4MiB (max) file around is generally worth the reduced I/O 
overhead that results from creating, resizing, and unlinking files.

If you need, you can still override this behaviour using the 
SQLITE_FCNTL_PERSIST_WAL opcode to the sqlite3_file_control() interface.

Scott


On Jan 28, 2019, at 10:32, Carsten Müncheberg  
wrote:
> 
> When loading and using /usr/lib/libsqlite3.dylib (3.19.3) which is shipped
> with MacOS 10.13.6 I noticed that the -wal and -shm files are not deleted
> when closing the last connection to a database. I tested this with the
> sqlite3 command line tool. It does not happen when I compile and link
> SQLite myself.
> 
> Is this expected behavior or some kind of bug?
> 
> Carsten

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

Re: [sqlite] CoreData - when database gets closed

[Scott Perry]

On Jan 22, 2019, at 9:02 AM, Simon Slavin  wrote:
> 
> On 22 Jan 2019, at 3:45pm, Maldonado-Salazar, Carlos 
>  wrote:
> 
>> Is there a way to know when CoreData closes sqlite files?. I’m using 
>> CoreData in an iOS app and I set file attributes for sqlite file to be 
>> NSFileProtectionCompleteUnlessOpen which throws away the key to encrypt the 
>> file when it’s closed, denying access to it.
> 
> I don't think I've seen one.  Technically, CoreData might hold its file open 
> after your application has quit, for example if it's doing lazy writing.  So 
> there'd be nothing to notify

A persistent store coordinator will close its handle when the store is removed. 
This happens naturally when the coordinator is deallocated, or you can do it 
manually using -[NSPersistentStoreCoordinator removePersistentStore:error:].

One common problem people run into is unexpected object lifetime extension when 
the coordinator gets added to a autorelease pool higher up in the call stack. 
In those conditions you can make the lifetime semantics of the coordinator more 
precise through explicit use of @autoreleasepool blocks.

> Using NSFileProtectionCompleteUnlessOpen or NSFileProtectionComplete should 
> do a good job of keeping your data secure.

I'd recommend using NSFileProtectionComplete over 
NSFileProtectionCompleteUnlessOpen. The former is simpler and self-securing 
(the filesystem will return an error if Core Data attempts to read or write 
data when the device is locked).

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

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Scott Perry]

On Dec 11, 2018, at 04:01, Daniel Alm  wrote:
> 
> Hi,
> 
> For the past half year we’ve been receiving reports from users who had 
> restored their SQLite-based databases from a Time Machine backup. Afterwards, 
> they would receive "database disk image is malformed” errors. The app also 
> backs up the user’s data “manually” to a ZIP file every week; those backups 
> seem to be working fine. We also haven’t received reports from other backup 
> tools causing issues. I have also suspected a bug in Time Machine, but it is 
> striking that the issues did seem to start occurring after an update to the 
> app (luckily, in fact, with the same update that also introduced the “manual” 
> backups).

Time Machine achieves eventual consistency by restarting when it detects that a 
file has changed since the backup was started. It does not have special 
provisions for SQLite database files.

Even if the scheduled backup never runs, the conditions under which a database 
would be captured in an utterly inconsistent state should be vanishingly rare. 
It would be most useful if you could share a representative database with 
Richard for analysis.

> Changes that we made to our setup in the update that coincided with the 
> errors occurring:
> - Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1 in 
> another update; no improvement).
> - Used memory mapping for read accesses via “PRAGMA mmap_size = 1073741824;” 
> (we have since reverted to “PRAGMA mmap_size = 0;” after reading 
> http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html
>  
> ;
>  no improvement).
> - Using a secondary database via [ATTACH 
> DATABASE](https://www.sqlite.org/lang_attach.html 
> ) (although this also seems to occur 
> for users without such a database).
> 
> At this point, I am at a loss, especially given that SQLite should be fairly 
> robust against database corruption. While our app is running in the 
> background all the time, it is not very write-heavy (~ one transaction per 
> minute taking just a few milliseconds). Also, the app had been running fine 
> before the update for a long time without any reports of this issue. I might 
> be doing something wrong or have changed anything else, but I don’t know 
> what; if you have any ideas, let me know.
> 
> Any suggestions on what could be the culprit or what else I could try besides 
> downgrading all the way to SQLite 3.21 would be appreciated.

Out of curiosity, why aren't you using the SQLite that comes with the OS?

Scott

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

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Scott Perry]

On Nov 26, 2018, at 14:16, Simon Slavin  wrote:
> 
> On 26 Nov 2018, at 9:09pm, Scott Perry  wrote:
> 
>> For Bill's purposes—investigating a copied, non-corrupt database—it would 
>> probably be easiest to just convert from the Cocoa epoch to the Unix epoch 
>> by updating all the columns that currently store Cocoa timestamps. Something 
>> like:
>> 
>>   UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;
> 
> If you want to access your date in that form but leave the database usable by 
> Apple's libraries you can create a view which has a new column which modifies 
> the date in the above way.  As long as your VIEW's name does not clash with 
> one Apple wants to use, Apple's utilities should not stop working just 
> because you created a new view in a Core Data database.  I have previously 
> done this without problems but I may have just been lucky.
> 
> Come to think of that, Scott, you're in a better position to confirm that 
> than I am.


Local experimentation is one of the greatest learning tools, but I don't 
recommend making any customizations to Core Data stores that will ever be used 
on someone else's device as the framework is not especially sympathetic to 
meddling.

With that disclaimer out of the way, as a general rule Core Data namespaces all 
of its resources by prefixing them with Z. Migrations (which occur when 
updating the store to a new model version, or on first use after an operating 
system update) may destroy schema customizations.

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

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Scott Perry]

On Nov 20, 2018, at 12:41 PM, Jens Alfke  wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman  
> wrote:
> 
>> The timestamp from iOS systems is not compliant with ISO 8601/Unix or other 
>> common timestamps.  It appears apple has their start date offset 31 years.
> 
> Yes, the ‘epoch’ in Apple’s own APIs (CoreFoundation, Foundation) is 
> 1/1/2001, expressed as a double. But of course the POSIX APIs on Apple 
> platforms use the regular Unix epoch of 1/1/1970 as integer.

That database looks like it was produced by Core Data, which does not use 
SQLite's timestamp-specific features since NSDate and friends are backed by 
doubles.

For Bill's purposes—investigating a copied, non-corrupt database—it would 
probably be easiest to just convert from the Cocoa epoch to the Unix epoch by 
updating all the columns that currently store Cocoa timestamps. Something like:

UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;

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

Re: [sqlite] Regarding CoC

[Scott Perry]

I have to admit I was a lot more excited about the concept of SQLite having a 
Code of Conduct until I actually read it. Regardless of the fact that I seem to 
fail a great many of its provisions, it seems fairly deaf—if not 
antagonistic—to the issues of our times that have created demand for such 
documents.

Scott

On Oct 19, 2018, at 7:11 AM, Richard Hipp  wrote:
> 
> On 10/19/18, Mantas Gridinas  wrote:
>> 
>> I found code of conduct in documentation and I was wondering if it were
>> true. Checking the version history it appears to have been added on
>> 2018-02-22.
>> 
> 
> Yes.  Clients were encouraging me to have a code of conduct.  (Having
> a CoC seems to be a trendy thing nowadays.)  So I looked around and
> came up with what you found, submitted the idea to the whole staff,
> and everybody approved.
> 
> -- 
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Scott Perry]

On Feb 15, 2017, at 11:46 AM, Simon Slavin  wrote:
> On 15 Feb 2017, at 7:39pm, Richard Hipp  wrote:
>> Are you saying it is the one built into MacOS - not one you obtained
>> from https://www.sqlite.org/download.html?  If so, can you provide me
>> with details of what OS version you are running, please?
> 
> Answered privately because it’s a non-public release of macOS.  Sorry, folks, 
> shouldn’t have posted in public.

In the future it would be most appreciated if problems with Apple's version of 
SQLite were reported by Radar , doubly so for 
pre-release versions of operating systems where the issues can be fixed before 
the final release.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Article about pointer abuse in SQLite

[Scott Perry]

On Mar 21, 2016, at 1:48 PM, Scott Perry  wrote:
> 
> On Mar 21, 2016, at 3:17 AM, Klaas Van B.  wrote:
>> 
>>>> On 3/19/16, James K. Lowden  wrote:
>> 
>>>> ... If the correctness of the code is
>>>> subject to change by the compiler's interpretation of the language, how
>>>> is the programmer to prevent it?
>> 
>>> On Sat, 19 Mar 2016 15:50:43 -0400 Richard Hipp  wrote:
>> 
>>> ... But subsequent revisions of the
>>> C-language standards changed that.  How does one write code that will
>>> comply with language standards that keep changing out from under you?
>> 
>> It's like trying to live according to the law while they're changing the 
>> constitution.
> 
> This is a false dichotomy. Compilers allow you to choose your standard; 
> --std=c11 means something very specific (and unchanging) about the behaviour 
> you can expect to be defined.

(and yes, I did mean to say false equivalence in my response)

[sqlite] Article about pointer abuse in SQLite

[Scott Perry]

On Mar 21, 2016, at 3:17 AM, Klaas Van B.  wrote:
> 
>>> On 3/19/16, James K. Lowden  wrote:
> 
>>> ... If the correctness of the code is
>>> subject to change by the compiler's interpretation of the language, how
>>> is the programmer to prevent it?
> 
>> On Sat, 19 Mar 2016 15:50:43 -0400 Richard Hipp  wrote:
> 
>> ... But subsequent revisions of the
>> C-language standards changed that.  How does one write code that will
>> comply with language standards that keep changing out from under you?
> 
> It's like trying to live according to the law while they're changing the 
> constitution.

This is a false dichotomy. Compilers allow you to choose your standard; 
--std=c11 means something very specific (and unchanging) about the behaviour 
you can expect to be defined.

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

[Scott Perry]

The SQLite built into OS X does not support cache sharing for performance 
reasons?, which is probably why your results are statistically identical and 
the OP's results are wildly different.

You can verify this by checking the return value of 
sqlite3_enable_shared_cache; on OS X it returns SQLITE_MISUSE.

---
? On multicore systems, memory barriers are extremely expensive; not sharing 
caches allows them to run lock-free

On Dec 20, 2015, at 7:05 AM, E.Pasma  wrote:
> 
> 20 dec 2015, 14:29, sanhua.zh:
> 
>> Here is the test result for selecting 100,000 items in original test case.
>> 
>> 
>> shared cache mode
>> 2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480
>> 2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449
>> 2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768
>> 2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169
>> 
>> 
>> without shared cache mode
>> 2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914
>> 2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914
>> 2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964
>> 2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958
>> 
>> 
>> May be your python code does not run the same thing as mine, I am poor in 
>> python so that I could not figure it out.
> Indeed, I had changed the query to just scan the table in the database and 
> not return all  rows. Now I chaged that and also set the corresponding number 
> of rows. Still cache sharing does not make such a mega differencr. Below are 
> the timings.
> I have no clu now for your mega difference.
> SQLite version? 3.8 here
> hardware? I have (only) Mac OS X 10.5.8 with a 1.22 GHz PowerPC
> 
> Timings for default mode:
> $ python3 larry3.py [B
> sqlite version 3.8.3.1
> cache sharing 0 cache size 2000 rowcount 10
> after split 0.0037369728088378906
> after start 0.8668131828308105
> steps 10 connect+fetch 4.39 connect 0.0
> steps 10 connect+fetch 4.52 connect 0.0
> steps 10 connect+fetch 4.62 connect 0.0
> steps 10 connect+fetch 4.51 connect 0.0
> elapsed 5.21
> 
> Timings in shared cache mode:
> sqlite version 3.8.3.1
> cache sharing 1 cache size 2000 rowcount 10
> after split 0.0035581588745117188
> after start 0.7083160877227783
> steps 10 connect+fetch 6.4 connect 0.0
> steps 10 connect+fetch 6.17 connect 0.0
> steps 10 connect+fetch 6.56 connect 0.0
> steps 10 connect+fetch 6.46 connect 0.0
> elapsed 6.85
> 
> Python script:
> 
> import random, os, time, threading
> import sqlite3 as sqlite
> print ("sqlite version", sqlite.sqlite_version)
> 
> TESTDB='larry.tmp'
> SHARED=0
> SIZE=2000
> ROWCOUNT=10
> print('cache sharing', SHARED, 'cache size', SIZE, "rowcount", ROWCOUNT)
> sqlite.enable_shared_cache(SHARED)
> 
> def connect():
>con= sqlite.Connection (TESTDB, isolation_level=None, check_same_thread=0)
>con.execute ("pragma cache_size=%i"%(SIZE,))
>return con
> 
> def e_str (e):
>" format exception as string "
>return "%s: %s" % (e.__class__.__name__, e)
> 
> class Testthread (threading.Thread):
>"""
>execute query in a thread
>"""
>def __init__ (self, qq, con = None):
>self.con = con
>self.qq = qq
>self.out = "thr%i.out" % id (self)
>open (self.out, 'w').close ()
>os.remove (self.out)
>threading.Thread.__init__ (
>self,
>target=self.__target,
>)
>def start (self):
>threading.Thread.start (self)
>for retry in range (5):
>time.sleep (2 ** retry * .05)
>if os.access (self.out, os.R_OK):
>break
>else:
>print("Testthread: spoolfile does not appear")
>time.sleep (.10) # for the SQL to start
>def __target (self):
>subt0=time.time()
>if not self.con:
>self.con = connect ()
>dt1=round(time.time()-subt0,2)
>f = open (self.out, 'w')
>try:
>try:
>n=0
>for q in self.qq.split (';'):
>i=None
>for i in self.con.execute(q):
>n+=1
>continue
>f.write (str(i)+'\n') # write last line only
>dt2=round(time.time()-subt0,2)
>print("steps", n, "connect+fetch", dt2, "connect", dt1)
>except Exception as e:
>f.write (e_str (e) + '\n')
>finally:
>f.close()
>self.con.close ()
>def join (self, timeout=None):
>if timeout is not None:
>threading.Thread.join (self, timeout)
>else:
>timeout = 7.5 # respond to keyboard interrupts
>while self.isAlive ():
>threading.Thread.join (self, timeout)
>return self
>def get_result (self):
>try:
>return open (self.out, 'r').read ().strip ()
>except IOError as e:
>return None
> 
> def main ():

[sqlite] SQLite take lower performance while using shared cache on iOS/Mac

[Scott Perry]

Using a shared cache will result in lower memory usage and may result in lower 
IO, but it isn't likely to speed up your program since it also requires more 
locks to guarantee safety between competing database connections.

SQLite on Apple's platforms is built with SQLITE_THREADSAFE=2, so under normal 
use there is minimal locking overhead.

Unless you absolutely need to conserve every last byte of memory?and since 
you're writing Objective-C that's almost certainly not the case?you should not 
be using a shared cache.

On Dec 16, 2015, at 10:58 PM, sanhua.zh  wrote:
> 
> I try to use shared cache to optimize my code. 
> Sincesqlite3_enable_shared_cache is deprecated on iOS/Mac, I 
> usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag to open shared cache mode.
> 4 threads select is running in my code, while each thread has its own sqlite 
> connection and do the same thing - select all 10 item from ?test? table.
> Strange thing happened.
> Each thread ends up within 0.09 seconds without SQLITE_OPEN_SHAREDCACHE, but 
> with SQLITE_OPEN_SHAREDCACHE ends up in 14 second, which is much slower.
> As sqlite.org said, shared cache mode can reduce the memory and IO, which 
> leads to better performance.
> Any one can tell me whether I write the wrong code or using shared cache mode 
> in an incorrect scene ?
> 
> 
> Here is my code mixed by C and Objective-C, but it will not stop your reading 
> and understanding:
> 
> 
> #import Foundation/Foundation.h
> #import sqlite3.h
> #import sys/time.h
> 
> 
> double now()
> {
>  timeval now;
>  gettimeofday(now, nullptr);
>  return now.tv_sec+now.tv_usec/100.0;
> }
> 
> 
> void showResultCode(int resultCode)
> {
>  if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) {
>NSLog(@"unexperted result %d", resultCode);
>  }
> }
> 
> 
> void SQLiteLog(void* userInfo, int ret, const char* msg)
> {
>  NSLog(@"ret=%d, msg=%s", ret, msg);
> }
> 
> 
> void write(const char* path)
> {
>  sqlite3* handle;
> 
> 
>  showResultCode(sqlite3_open(path, handle));
> 
> 
>  showResultCode(sqlite3_exec(handle, "PRAGMA synchronous=FULL", nullptr, 
> nullptr, nullptr));
>  showResultCode(sqlite3_exec(handle, "PRAGMA journal_mode=WAL", nullptr, 
> nullptr, nullptr));
>  showResultCode(sqlite3_exec(handle, "drop table if exists test;", nullptr, 
> nullptr, nullptr));
>  showResultCode(sqlite3_exec(handle, "create table if not exists test(id 
> integer);", nullptr, nullptr, nullptr));
> 
> 
>  sqlite3_stmt* stmt = nullptr;
>  showResultCode(sqlite3_exec(handle, "BEGIN", nullptr, nullptr, nullptr));
>  for (int i = 0; i  100; i++) {
>showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert 
> into test values(%d);", i].UTF8String, -1, stmt, nullptr));
>showResultCode(sqlite3_step(stmt));
>showResultCode(sqlite3_finalize(stmt));
>  }
>  showResultCode(sqlite3_exec(handle, "COMMIT", nullptr, nullptr, nullptr));
>  showResultCode(sqlite3_close(handle));
> }
> 
> 
> 
> 
> void read(const char* path)
> {
>  sqlite3* handle;
> 
> 
>  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE | 
> SQLITE_OPEN_SHAREDCACHE, nullptr));
> //  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE, 
> nullptr));
>  sqlite3_stmt* stmt;
>  showResultCode(sqlite3_prepare(handle, "select * from test;", -1, stmt, 
> nullptr));
> 
> 
>  double start = now();
>  int integer = 0;
>  while (sqlite3_step(stmt)!=SQLITE_DONE) {
>integer = sqlite3_column_int(stmt, 0);
>  }
>  NSLog(@"%d", integer);
>  showResultCode(sqlite3_finalize(stmt));
>  double end = now();
>  NSLog(@"cost %f", end-start);
> 
> 
>  showResultCode(sqlite3_close(handle));
> }
> 
> 
> int main(int argc, char * argv[])
> {
>  sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL);
>  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
> 
> 
>  const char* path = "/Users/sanhuazhang/Desktop/test.db";
> 
> 
>  write(path);
>  for (int i = 0; i  4; i++) {
>dispatch_queue_t queue = dispatch_queue_create([NSString 
> stringWithFormat:@"queue%d", i].UTF8String, DISPATCH_QUEUE_CONCURRENT);
>dispatch_async(queue, ^{
>  read(path);
>});
>  }
> 
> 
>  sleep(1);
>  return 1;
> }
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite database becomes corrupt on iOS

[Scott Perry]

On Aug 19, 2015, at 11:34 PM, Simon Slavin  wrote:
> 
> 
> On 20 Aug 2015, at 6:22am, Scott Perry  wrote:
> 
>> That said, it's possible to corrupt a database by forcing the device to 
>> power off (by holding the power and home buttons) while SQLite's writes are 
>> in flight. This is equivalent to the conditions of a kernel panic.
> 
> This is true only if your storage subsystem (usually hard disk plus it's 
> driver) does not work properly, and a couple of bad coincidences happen.  In 
> almost all situations, when SQLite next accesses the database it will notice 
> a journal file still exists for it and use the information in the journal 
> file to uncorrupt the database.
> 
> The resulting database might or might not reflect the last transaction's 
> changes.  It depends on precisely when power was lost.  But it should not be 
> corrupt, and it should reflect all changes up to the end of a transaction.

If the NAND controller is interrupted by power loss or kernel panic, it is 
possible for a write to be partially applied to disk, a condition that SQLite 
cannot defend against.

These conditions are extremely rare, especially in the wild, but at scale every 
possible error is a certainty.

[sqlite] SQLite database becomes corrupt on iOS

[Scott Perry]

On Aug 14, 2015, at 10:13 AM, Simon Slavin  wrote:
> On 14 Aug 2015, at 5:16pm, Random Coder  wrote:
>> I've run into other issues
>> that lead me to believe the OS is caching file writes until the app
>> exits in some situations regardless of various sync calls, but I never
>> did have time to track down if I was just fooling myself, or if the OS
>> was indeed doing things to "help" me out.
> 
> The hardware in iDevices varies (obviously) between the many devices which 
> have been produced over the years.  But I believe that you're right in that 
> it's not possible for the OS to tell when data has /really/ been written to 
> permanent storage.  Such writing takes a lot of power and it makes sense that 
> a device would want to do it infrequently.

If the device runs out of power normally, your writes are guaranteed. However, 
your app will get no notice. It's equivalent to getting jetsamed during 
low-memory conditions.

That said, it's possible to corrupt a database by forcing the device to power 
off (by holding the power and home buttons) while SQLite's writes are in 
flight. This is equivalent to the conditions of a kernel panic.

Using the system-provided SQLite helps, the unix VFS is modified to be 
(somewhat) more robust on Apple's platforms.

[sqlite] Copying an open DB file (iOS)

[Scott Perry]

On May 8, 2015, at 4:47 AM, Simon Slavin  wrote:
> On 8 May 2015, at 8:09am, Jeff M  wrote:
> 
>> For a restore, the current DB is closed, then a backup file is copied in and 
>> opened.
> 
>> I've been using this scheme for years without a problem.  But, should I 
>> flush, or close, or lock before the copy?
> 
> You need to close your connection, take the copy, then reopen your 
> connection.  Just like you do when restoring a backup.  While there is a 
> connection to the database some data from it might be in an associated 
> journal file.  Only by closing all connections can you be sure that the 
> database is up-to-date, consistent with itself, and can be opened without 
> SQLite thinking it needs to repair something.
> 
> That will cover you for all operations, journal types, journal settings, and 
> platforms.  For some settings and situations it might be possible to get away 
> with something less than closing the connection, but then you would need to 
> be careful about what you were doing, whereas the above instructions are 
> simple and will definitely work every time.

If you're using wal mode journalling (which I'd recommend for running on iOS 
devices), you'll want to execute `PRAGMA wal_checkpoint(TRUNCATE);` before 
closing your last connection to ensure you don't leave any data behind in the 
journal when you move the database file.

Alternately, you could store the database in a directory and treat it as a 
bundle. That would guarantee that you never lose track of any associated files.

[sqlite] Integrating sqlite with Core Data and iCloud

[Scott Perry]

There are a lot of people who go on this journey, and I don't recommend it. If 
you want to take advantage of iCloud, it's best to use Core Data directly, 
perhaps with an API shim of your own making that makes it easier and more 
suitable to your needs.

On Apr 25, 2015, at 12:41 PM, Jeff M  wrote:
> On Apr 24, 2015, at 2:44 AM, Simon Slavin  wrote:
>> On 24 Apr 2015, at 6:59am, Jeff M  wrote:
>> 
>>> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
>>> Data.  Core Data then takes care of the iCloud issues.
>> 
>> I imagine you'd do that by writing a VFS which used Core Data for storage.  
>> Core Data could store your data in any of the formats it has drivers for, 
>> including plaintext files and SQLite.  And Core Data could store your data 
>> in any medium it has drivers for, including local storage and iCloud.
>> 
>> The result might be slow and inefficient, since you're building a DBMS 
>> (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite).
>> 
>> If you use Core Data the resulting file wouldn't look like a normal SQLite 
>> database.  Core Data stores objects.  It doesn't store the rows and columns 
>> you refer to with SQL commands.  Maybe your objects would be table rows.
>> 
>> Simon.
> 
> Originally, I wanted to map my tables, rows, and columns to similarly-named 
> Core Data entities and attributes so I could continue to use the SQL 
> language.  I was hoping to hook into the SQL parser to get the benefits of 
> where.c, but I realize now that's impractical.

This is possible, but at each step of the way there are reasons to not do it. 
You'd need to:

Create tooling that generates Core Data models from SQL schemas. This means you 
should probably give up on the ability to make schema changes at runtime. This 
code will also need to produce a shim that provides Core Data object access to 
SQLite when it needs row data.

Write a vdbe replacement (this has been done before, but I can't find a link at 
the moment) and write a replacement that converts parsed queries or vm 
operations into Core Data requests (which will then be converted back into 
SQLite). Here you will either have to give up certain queries that aren't 
supported by the Core Data request language (further limiting functionality), 
or write sufficient compatibility code (regressing performance).

If your goal is to use iCloud as the backend, this means you'll likely have to 
deal with data conflicts, probably at your shim layer. Constantly-connected 
database systems don't have to deal with this problem because they are always 
online, and its likely that the right conflict resolution decisions will vary 
based on your use case.

> Your suggestion of working at the file system level is interesting, but my 
> objects would be disk blocks.  I could use a simple Core Data model: one 
> entity (representing the entire database file) and create one object per 
> block (each having a binary attribute containing one block of data).  It 
> would be easy to map each file system read() and write() to the corresponding 
> objects.  Using Core Data as a memory array would earn me the Kludge of The 
> Year Award.  But, I see data corruption in my future.

I can't recommend this approach either, due to the conflict resolution issues I 
mention above. If you're using Core Data as a vfs layer and somehow conflict 
resolution leaves you with pages from two different forks of the database, it's 
likely that your database will be irrecoverably corrupted. You'll also lose out 
on a lot of useful features such as model migration, meaning that you'll have 
to manually orchestrate your own schema changes.

> Can you point me to some sample source code (outside of SQLite itself) that 
> implements sqlite3_vfs_register()?
> 
> Jeff
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] static malloc_zone_t* _sqliteZone_

[Scott Perry]

Good eye, thanks for reporting this.

Pointers on the stack or in static storage are pointer-aligned by default on 
all of Apple's platforms.

On Mar 3, 2015, at 7:27 PM, Andy Rahn  wrote:
> 
> Hi SQLite users;
> 
> I have a question about _sqliteZone_ in mem1.c.  I notice that the
> address of this static variable is used in a call to
> OSAtomicCompareAndSwapPtrBarrier on MacOS and iOS.  That system call
> is declared in OSAtomic.h, which includes a note about the pointer
> alignment of its arguments:
> 
>> * WARNING: all addresses passed to these functions must be "naturally 
>> aligned",
>> * i.e.  * int32_t pointers must be 32-bit aligned (low 2 bits of
>> * address are zeroes), and int64_t pointers must be 64-bit 
>> aligned
>> * (low 3 bits of address are zeroes.)
> 
> I wonder, therefore, if it might be prudent to declare _sqliteZone_
> with the alignment attribute, so that the compiler is sure to put it
> at a 32 / 64 bit aligned address space? e .g.
> 
> static __attribute__((aligned(8))) malloc_zone_t* _sqliteZone_;
> 
> and also, because this local variable is used in that same function:
> 
> __attribute__((aligned(8))) malloc_zone_t* newzone =
> malloc_create_zone(4096, 0);
> 
> I see that attribute is used one other place, so this may be an
> important nuance.  On a 32-bit architecture, it would be safe to use
> aligned(4) instead of aligned(8) but I'm not sure anyone will care
> about the (possible) 4-byte savings.
> 
> - Andy
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users