Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 16 Feb 2018, at 1:39am, Jens Alfke  wrote:

> On Feb 15, 2018, at 4:53 PM, Simon Slavin  wrote:
> 
>> Given the way the documentation is arranged, and the lack of any mention to 
>> the contrary,  I will assume sqlite3_shutdown() is paired with 
>> sqlite_initialize(). 
> 
> There’s a sqlite_initialize function?? O_o



However, if you open a connection and you haven't already called it, it's 
called automatically.  So almost nobody cares about it.

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

Re: [sqlite] More sqlite header questions

[Jens Alfke]

> On Feb 15, 2018, at 4:53 PM, Simon Slavin  wrote:
> 
> Given the way the documentation is arranged, and the lack of any mention to 
> the contrary,  I will assume sqlite3_shutdown() is paired with 
> sqlite_initialize(). 

There’s a sqlite_initialize function?? O_o

—Jens

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

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 15 Feb 2018, at 11:10pm, Jens Alfke  wrote:

>> On Feb 15, 2018, at 10:09 AM, Simon Slavin  wrote:
>> 
>>> 1) I can't be the only programmer who learned to make paired calls ("If you 
>>> initialise something, it needs deinitializing, if you allocate something, 
>>> deallocate it.").
> 
> When a process exits, its resources are automatically cleaned up by the 
> kernel — memory and address space freed, file handles and sockets closed, 
> etc. So it’s generally unnecessary to do redundant cleanup like 
> sqlite3_shutdown* on quit, and it can actually slow things down**, especially 
> if freeing a zillion heap blocks that have been paged out or are no longer in 
> CPU caches.
> 
> —Jens
> 
> * This doesn’t apply to sqlite3_close() on a writeable database, since SQLite 
> may need to update data in the file before closing it.

Your footnote is an example of the reason I do paired calls.  I didn't write 
the SQLite API.  I don't know whether sqlite3_shutdown() does anything.  For 
all I know, sqlite3_shutdown() is the thing that deletes dead journal and 
shared memory files.  Maybe it slows things down too much to do that on 
sqlite3_close().  I don't know.

Given the way the documentation is arranged, and the lack of any mention to the 
contrary,  I will assume sqlite3_shutdown() is paired with sqlite_initialize(). 
 One of the Developer team tells me it'd not needed except for cases where your 
process isn't about to quit ?  Fine.  But that should really be in the 
documentation so people who don't read this list know it.  Because the name 
sqlite3_shutdown(), and the documentation for it, implies you should call it 
when sqlite3_initialize() has been called and you don't need SQLite any more.

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

Re: [sqlite] More sqlite header questions

[Jens Alfke]

> On Feb 15, 2018, at 10:09 AM, Simon Slavin  wrote:
> 
> 1) I can't be the only programmer who learned to make paired calls ("If you 
> initialise something, it needs deinitializing, if you allocate something, 
> deallocate it.").

When a process exits, its resources are automatically cleaned up by the kernel 
— memory and address space freed, file handles and sockets closed, etc. So it’s 
generally unnecessary to do redundant cleanup like sqlite3_shutdown* on quit, 
and it can actually slow things down**, especially if freeing a zillion heap 
blocks that have been paged out or are no longer in CPU caches.

—Jens

* This doesn’t apply to sqlite3_close() on a writeable database, since SQLite 
may need to update data in the file before closing it.
**  Case in point: One of the optimizations added to MacOS about five years ago 
was ‘fast termination’, wherein quitting an app will terminate it instantly 
with a simple call to exit(0) if the app code doesn’t have any special cleanup 
it needs to do.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 15 Feb 2018, at 5:30pm, Dan Kennedy  wrote:

> (B) is an understandably common misconception. sqlite3_shutdown() frees 
> resources that were allocated by sqlite3_initialize() or 
> sqlite3_auto_extension() and must be called after all SQLite connections have 
> been closed. These resources are either trivial or non-existent on almost all 
> platforms - so in practice sqlite3_shutdown() is only useful on embedded 
> systems that do not free such resources automatically when a process exits, 
> or in other obscure circumstances.
> 
> Don't call sqlite3_shutdown()!

Thanks for your informative correction.  Is there a chance of amending the 
documentation with the above text ?  I have two problems with the existing 
documentation:

1) I can't be the only programmer who learned to make paired calls ("If you 
initialise something, it needs deinitializing, if you allocate something, 
deallocate it.").

2) I thought that sqlite3_shutdown() called sqlite3_close() on any open 
connections.  In other words, that if keeping track of your connections was 
difficult it might be simpler to just call sqlite3_shutdown().

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

Re: [sqlite] More sqlite header questions

[Dan Kennedy]

On 02/14/2018 04:03 AM, Simon Slavin wrote:

On 13 Feb 2018, at 8:22pm, Chris Brody  wrote:


Thanks Simon for the quick response.

You're welcome.


Can you clarify the following:
- Does this imply that a SQLite database may be left in some kind of
unrecoverable, corrupted, or otherwise invalid state in case an
application would terminate without calling sqlite3_close() on all
open database connections?

Your program is meant to do one of two things:

A) Close all database connections.
B) Call sqlite3_shutdown(), which will close everything and release all memory 
in a correct and orderly fashion.



(B) is an understandably common misconception. sqlite3_shutdown() frees 
resources that were allocated by sqlite3_initialize() or 
sqlite3_auto_extension() and must be called after all SQLite connections 
have been closed. These resources are either trivial or non-existent on 
almost all platforms - so in practice sqlite3_shutdown() is only useful 
on embedded systems that do not free such resources automatically when a 
process exits, or in other obscure circumstances.


Don't call sqlite3_shutdown()!

  http://sqlite.org/c3ref/initialize.html

Dan.







If you do not do either of these things, it /might/ be possible to find that 
your database files are left in an inconsistent state.  But even if it does 
happen, the next time the database is opened using the SQLite API, SQLite will 
figure out what happened and restore the database to valid state.

Rather than unexpected termination you should be more worried about a program 
crashing or losing power in the middle of a SQLite call.  But SQLite was 
written to cope with this, too.


- If yes, what can a programmer do to protect the data in case an
application is abruptly terminated for any reason?

If there was anything, the fix would already be built into SQLite, or it would be 
prominently listed in the "how to use SQLite" pages.

If you think you have a corrupted database, reopen it using the SQLite API, 
then (after using it if you want) close it properly.  This is always the answer 
unless you want to forensically investigate the cause of corruption.


- Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
mitigate this kind of possible corruption?

I'm gonna let one of the developer team answer this.  I suspect that the answer 
depends on your operating system, and your storage device and its driver.

Durability is the foe of execution time.  It would be possible to make SQLite 
one third as subject to corruption -- at the cost of every command that reads 
or writes the database taking nine times as long.


I think this is especially important for mobile apps which may be
terminated without notice, especially when using hybrid app frameworks
such as Cordova/PhoneGap.

No mobile OS I'm aware of will allow termination of a program while it's in the 
middle of a SQLite API call, unless some other part of the application is hung 
and refusing to terminate.  This is part of the design of mobile operating 
systems which are designed to expect unpredictable backgrounding and 
termination.

I can go into great detail about how iOS warns a program about backgrounding 
and termination, so that it can close in a graceful manner.  I assume Android 
does something similar.


However, the SQLite library goes through heroic measures
[...]

I am sure that this was at the cost of many heroic programmer hours.

Not to mention the proportion of SQLite's source code which is devoted to 
detecting and fixing corruption rather than doing mundane database work.

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] More sqlite header questions

[Simon Slavin]

On 13 Feb 2018, at 9:17pm, Richard Hipp  wrote:

> On 2/13/18, Jens Alfke  wrote:
> 
>> On iOS (can’t speak for Android) apps do get notice that they’re going to be
>> terminated.
> 
> I am told that that notification is best-effort and is not guaranteed
> to occur, nor is it guaranteed to be delivered to the application
> prior to the application being forcibly terminated.

The precise way this is done has changed from iOS version 6 to iOS version 10.  
The documentation states (and has always stated) that your app will get a 
backgrounding notification and a termination notification where possible.

One problem comes if your application does not acknowledge a backgrounding 
notification.  If this happens, then your application may not later get 
notified that it is being terminated.  So the protocol breaks down once either 
party isn't following it.

Another problem occurs if the phone receives a call at a time when the battery 
is running low.  Low battery means that the phone has little power to devote to 
background processing, since by law handling the phone call takes priority.  
This is probably the situation that Apple was talking about in Dr Hipp's post.

The most obvious problem happens if the phone runs out of power.  However 
current versions of iOS shut the device down (giving appropriate notifications) 
before the battery is truly out of power.  So they're not in much hurry.

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

Re: [sqlite] More sqlite header questions

[Richard Hipp]

On 2/13/18, Jens Alfke  wrote:
>
> On iOS (can’t speak for Android) apps do get notice that they’re going to be
> terminated.

I am told that that notification is best-effort and is not guaranteed
to occur, nor is it guaranteed to be delivered to the application
prior to the application being forcibly terminated.
-- 
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] More sqlite header questions

[Jens Alfke]

> On Feb 13, 2018, at 12:22 PM, Chris Brody  wrote:
> 
> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

On iOS (can’t speak for Android) apps do get notice that they’re going to be 
terminated. That’s no different for hybrid frameworks; the JS code must be 
calling a SQLite API backed by native code, and that native code can listen for 
the appropriate notifications and close databases.

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

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 13 Feb 2018, at 8:22pm, Chris Brody  wrote:

> Thanks Simon for the quick response.

You're welcome.

> Can you clarify the following:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

Your program is meant to do one of two things:

A) Close all database connections.
B) Call sqlite3_shutdown(), which will close everything and release all memory 
in a correct and orderly fashion.



If you do not do either of these things, it /might/ be possible to find that 
your database files are left in an inconsistent state.  But even if it does 
happen, the next time the database is opened using the SQLite API, SQLite will 
figure out what happened and restore the database to valid state.

Rather than unexpected termination you should be more worried about a program 
crashing or losing power in the middle of a SQLite call.  But SQLite was 
written to cope with this, too.

> - If yes, what can a programmer do to protect the data in case an
> application is abruptly terminated for any reason?

If there was anything, the fix would already be built into SQLite, or it would 
be prominently listed in the "how to use SQLite" pages.

If you think you have a corrupted database, reopen it using the SQLite API, 
then (after using it if you want) close it properly.  This is always the answer 
unless you want to forensically investigate the cause of corruption. 

> - Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
> mitigate this kind of possible corruption?

I'm gonna let one of the developer team answer this.  I suspect that the answer 
depends on your operating system, and your storage device and its driver.

Durability is the foe of execution time.  It would be possible to make SQLite 
one third as subject to corruption -- at the cost of every command that reads 
or writes the database taking nine times as long.

> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

No mobile OS I'm aware of will allow termination of a program while it's in the 
middle of a SQLite API call, unless some other part of the application is hung 
and refusing to terminate.  This is part of the design of mobile operating 
systems which are designed to expect unpredictable backgrounding and 
termination.

I can go into great detail about how iOS warns a program about backgrounding 
and termination, so that it can close in a graceful manner.  I assume Android 
does something similar.

>> However, the SQLite library goes through heroic measures
>> [...]
> 
> I am sure that this was at the cost of many heroic programmer hours.

Not to mention the proportion of SQLite's source code which is devoted to 
detecting and fixing corruption rather than doing mundane database work.

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

Re: [sqlite] More sqlite header questions

[Richard Hipp]

On 2/13/18, Chris Brody  wrote:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

No.  The database might be left in a weird state, but it will
automatically recover the next time you or any other process opens the
database.

Unless, that is, you try to get clever and delete the *-journal or
*-wal file or do something else that interferes with the automatic
recovery process.  If you delete the information that SQLite needs to
recover then it won't, and the database will be left in its weird
state, which is now considered corrupt.

Do not do any of the bad things listed under
https://www.sqlite.org/howtocorrupt.html and you will be fine.

-- 
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] More sqlite header questions

[Chris Brody]

On Tue, Feb 13, 2018 at 2:03 PM, Simon Slavin  wrote:
> [...]
> There are two possibilities:
>
> A) The SQLite API was used correctly, including being allowed to close all 
> files it opened.
> B) Any other situation.
>
> If (A) happened, you can predict things about the database header and you can 
> rely on SQLite documentation about the format of files it uses.  Otherwise 
> you can't.

Thanks Simon for the quick response. Can you clarify the following:
- Does this imply that a SQLite database may be left in some kind of
unrecoverable, corrupted, or otherwise invalid state in case an
application would terminate without calling sqlite3_close() on all
open database connections?
- If yes, what can a programmer do to protect the data in case an
application is abruptly terminated for any reason?
- Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
mitigate this kind of possible corruption?

I think this is especially important for mobile apps which may be
terminated without notice, especially when using hybrid app frameworks
such as Cordova/PhoneGap.

> However, the SQLite library goes through heroic measures
> [...]

I am sure that this was at the cost of many heroic programmer hours.

> The SQLite header is less than 100 bytes long.  It all fits within one sector 
> / page of a storage device i.e. the entire header is written in one 
> operation.  If you ever discover an inconsistent header there's a bug in 
> SQLite.  The contents of the header are listed in
>
> 

Thanks for the clarification.

>> - Any recommended explanations or resources to understand how it may
>> be possible to obtain the correct sqlite database information (such as
>> WAL or other journal mode, actual database size, number of pages,
>> page/cache size, etc.)?
>
> PRAGMAs are available for retrieving all this information.  See
>
> 
> 
> 
>
> The size of the database is page_count * page_size.  Other PRAGMAs on the 
> same page provide other information which might be covered in your "etc.".

Makes sense ... assuming that the database is not corrupted beyond the
heroic repair mechanism:)

> If you have any other questions, please do not hesitate to ask them here.

Will do. Looking forward to the requested clarification. Thanks for
the answers so far.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] More sqlite header questions

[Simon Slavin]

On 13 Feb 2018, at 5:49pm, Chris Brody  wrote:

> I still have the following questions:

Chris,

There are two possibilities:

A) The SQLite API was used correctly, including being allowed to close all 
files it opened.
B) Any other situation.

If (A) happened, you can predict things about the database header and you can 
rely on SQLite documentation about the format of files it uses.  Otherwise you 
can't.

However, the SQLite library goes through heroic measures, every time it opens a 
database, to detect whether the database was closed correctly and, if it sees 
any problem, to rescue an uncorrupt version of the database.  All you need to 
do is open the SQLite database using the SQLite API, and SQLite will take any 
measures necessary to remedy the results of any power-loss or crash, including 
returning an error message if it can't arrive at an uncorrupt version of the 
database.

> 3. rogue code writes data to the file descriptor in use by sqlite

As is true for any library which handles files, nothing can be done about 
something external overwriting the file.  However, there is no way within the 
SQLite API to list the a file handles that a SQLite connection is using.  
Anything trying to mess with SQLite's file handles would have to arrive at the 
handle using random numbers, or at the operating system level.

> My understanding is that iOS applications with shared databases can be
> especially sensitive to sqlite headers

This is not true as long as the iOS applications are using the standard SQLite 
API to access its databases.  There is nothing strange about iOS in this 
regard, it's just another version of Unix and works the same as other versions 
of Unix / Linux.

The Apple reference you provided refer to obsolete versions of iOS.  The 
current version of iOS is version 10, which no longer makes assumptions about 
SQLite database.  From the Apple page you referred to:

"This has since been resolved in iOS 8.2."

The SQLCipher page seems to indicate that SQLCipher is not compatible with a 
particular, rarely-used, feature of iOS 8.  The text in the page you supply 
states clearly that the use SQLCipher makes of this Apple feature is not 
properly supported.  This has nothing to do with SQLite.  A comment near the 
foot of the page you referred to says

"We consider this issue resolved. Thanks @sjlombardo and team!"

> - Any recommended explanations or resources that explain how iOS
> handles shared sqlite databases (in more detail, with simpler terms)?

iOS does not handle shared SQLite databases itself.  The 'sharing' is done by 
the same SQLite API used on other platforms.  iOS would not even know that a 
database is being shared.  Details on how SQLite handles shared databases can 
be found at



> - Any recommended explanations or resources to understand how and when
> sqlite header may be in inconsistent or otherwise incorrect state?

The SQLite header is less than 100 bytes long.  It all fits within one sector / 
page of a storage device i.e. the entire header is written in one operation.  
If you ever discover an inconsistent header there's a bug in SQLite.  The 
contents of the header are listed in



> - Any recommended explanations or resources to understand how it may
> be possible to obtain the correct sqlite database information (such as
> WAL or other journal mode, actual database size, number of pages,
> page/cache size, etc.)?

PRAGMAs are available for retrieving all this information.  See





The size of the database is page_count * page_size.  Other PRAGMAs on the same 
page provide other information which might be covered in your "etc.".

If you have any other questions, please do not hesitate to ask them here.

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

[sqlite] More sqlite header questions

[Chris Brody]

Thanks to DRH for the quick answer. So I would infer that the sqlite
file header would normally be in a consistent state, and only be in an
inconsistent or otherwise incorrect state in the following cases:
1. modification (write) is in progress (until the sqlite3 code has a
chance to finish the modification)
2. application crashed or otherwise terminated without finishing the
modification (until the application or some other sqlite program opens
the database again)
3. rogue code writes data to the file descriptor in use by sqlite
4. other causes described in http://www.sqlite.org/howtocorrupt.html
such as rogue code, rogue process, rogue script, OS bug, dishonest
hardware, etc.

My understanding is that iOS applications with shared databases can be
especially sensitive to sqlite headers as discussed in:
- https://developer.apple.com/library/content/technotes/tn2408/_index.html
- note referenced by SQLCipher project, not sure if I can understand
it 100%
-  https://github.com/sqlcipher/sqlcipher/issues/255#issuecomment-355063368
- discovery that iOS checks header of shared SQLite databases, with
special handling of sqlite databases in WAL mode

I still have the following questions:
- Am I correct to say "that the sqlite file header would normally be
in a consistent state"?
- Am I missing anything or otherwise mistaken here?
- How likely would the header continue to indicate that the database
is an sqlite database in case 1 or 2 above?
- How likely would the header continue to indicate whether the
database is in WAL or any other journal mode in case 1 or 2 above?
- Any recommended explanations or resources that explain how iOS
handles shared sqlite databases (in more detail, with simpler terms)?
- Any recommended explanations or resources to understand how and when
sqlite header may be in inconsistent or otherwise incorrect state?
- Any recommended explanations or resources to understand how it may
be possible to obtain the correct sqlite database information (such as
WAL or other journal mode, actual database size, number of pages,
page/cache size, etc.)?

On Tue, Feb 13, 2018 at 9:10 AM, Richard Hipp  wrote:
>
> On 2/13/18, Chris Brody  wrote:
> > I was wondering what would happen if there would be an application crash,
> > system crash, or power failure while SQLite is updating the file header?
> >
> > Did I miss an explanation somewhere?
>
> The content is replicated either in the rollback-journal or in the
> write-head log (depending on whether or not you are in WAL mode) and
> will be recovered automatically when the database is first opened
> after power has been restored.  See
> https://www.sqlite.org/atomiccommit.html for further information.
> --
> 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