Re: [sqlite] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 11:00pm, François wrote:

> I just have a last question: you are telling that opening connection
> at app launch and close it only at app exit (scenario A) may be less
> secure than open/close database at each transaction (scenario B), on a
> data integrity point of view. But you gave the contrary advice at the
> beginning of our exchanges. Why?

I'm not sure what you mean by 'secure'.  Opening and closing database 
connections takes far more time than just doing a transaction.  It's just like 
opening and closing a data file on disk: you don't tend to do it unnecessarily.

> Let's consider a simple example of a database used by only one iOS
> app, which performs many INSERT or UPDATE during a user session.
> Therefore it would be better to minimize data loss risk.

Generally, open a connection when you know you're going to need the database, 
and close it again when you know you're not going to need the database again, 
just as if you were reading or writing a text file.

I think you're doing premature optimization.  Don't worry too much about data 
loss.  You have to put some trust into the platform you're using, especially a 
hugely mass-produced item like an iPhone.  If you were writing for a 
life-critical system used in conditions where electronic devices crash often, 
things would be different.

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

Re: [sqlite] When open / close database on IOS ?

[François]

OK thank you for these new explanations.

I just have a last question: you are telling that opening connection
at app launch and close it only at app exit (scenario A) may be less
secure than open/close database at each transaction (scenario B), on a
data integrity point of view. But you gave the contrary advice at the
beginning of our exchanges. Why?

Let's consider a simple example of a database used by only one iOS
app, which performs many INSERT or UPDATE during a user session.
Therefore it would be better to minimize data loss risk.

Best Regards,

François



On 25 août, 23:36, Simon Slavin  wrote:
> On 25 Aug 2011, at 9:54pm, François wrote:
>
> > On 25 août, 19:29, Simon Slavin  wrote:
> >> When you use sqlite3_open() a database file that routine checks for many 
> >> signs that the database wasn't closed properly -- in other
> >> words that an app which previously had it open crashed rather than using 
> >> sqlite3_close().  It then uses all the clues available in the
> >> database file and any journal files to restore the database to a 'safe' 
> >> situation.
>
> > So we just have to close database in applicationWillterminate and
> > applicationWillResignActive methods because SQLite handles crashes
> > itself. This is amazing!
>
> If your application crashes you can't make it do anything else.  So you have 
> no other option.  Just think of it as if there's a 'rescue utility' built 
> into sqlite3_open().  It's not a perfect utility, and can't rescue from all 
> possible kinds of corruption, but it will handle the results of most kinds of 
> crashes and leave you with a usable database.
>
> > Wow it confuses me. In 3) you told that when app crashes outside a
> > transaction and before database close, there is no data loss.
>
> No.  The recovery after a database has crashed does not make sure there is no 
> data loss.  It tries to make sure the database is restored to a COMMIT point, 
> not half way through a transaction, or in a corrupt state where, for 
> instance, a row has been added to a table but the table's indexes haven't 
> been updated.  The recovery process could, for instance, lose an entire 
> transaction if you were unlucky with your crash's timing.
>
> > And now
> > you tell that until database is closed, data are not flushed to the
> > disk. I was thinking that flush to disk was performed at transaction
> > commit because for example, performing N INSERT requests is much
> > faster within a transaction than within N implicit ones. Please excuse
> > me but can you explain this to me ? I am quite confused now :-)
>
> The only time you can be sure that the SQLite database file on disk perfectly 
> reflects the state of your database is when you don't have any connections 
> open, and have given caches time to empty, and have given your disk hardware 
> time to do what it promised the motherboard it would do.  SQLite may or may 
> not choose to update the database file on disk at any time while you have the 
> connection open.  That's not your problem.
>
> If SQLite waited for a full and proper update of the disk file every time you 
> closed a transaction it would work very slowly indeed.  Nothing actually 
> works like that these days: we're used to computers that tell the user "I did 
> it" but then do a lot of the work afterwards.
>
> >> iPhones make this all very confusing, because working memory isn't very 
> >> different to 'disk' memory, and once apps are started they
> >> normally continue running until the device shuts down because the battery 
> >> runs low.  But these things were designed when things were
> >> different.
>
> > Are you telling that when you commit a transaction, data are "flushed"
> > into a "in-memory cache" version of the database, and when database is
> > closed then this cache is flushed to the database file ?
>
> No.  Sorry but this is more complicated than you have to care about at first 
> glance.  Please think of transactions and connections are two completely 
> different things.  You can write your program, for example, that until you've 
> committed a transaction, /nothing/ is written to disk.  Perhaps all the 
> INSERT and UPDATE commands are just held as text in memory until a SELECT or 
> COMMIT, with no data generated at all.  Certainly if you have two different 
> apps with connections to the same database, app1 could open a transaction, do 
> a thousand INSERTs, then wait for user input for three hours, and app2 won't 
> see any changes until app1 has done a COMMIT.  That's how SQL is designed.
>
> If you want to make sure a SQLite database on disk reflects the changes 
> you've made to it, close all your database connections.  Until then your 
> changes may be in journal files or in the memory associated with the app that 
> made the change.
>
> If you write your software the way the documentation says, it'll work fine.  
> If you want to learn more about the deep considerations behind your 
> 

Re: [sqlite] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 9:54pm, François wrote:

> On 25 août, 19:29, Simon Slavin  wrote:
>> When you use sqlite3_open() a database file that routine checks for many 
>> signs that the database wasn't closed properly -- in other
>> words that an app which previously had it open crashed rather than using 
>> sqlite3_close().  It then uses all the clues available in the
>> database file and any journal files to restore the database to a 'safe' 
>> situation.
> 
> So we just have to close database in applicationWillterminate and
> applicationWillResignActive methods because SQLite handles crashes
> itself. This is amazing!

If your application crashes you can't make it do anything else.  So you have no 
other option.  Just think of it as if there's a 'rescue utility' built into 
sqlite3_open().  It's not a perfect utility, and can't rescue from all possible 
kinds of corruption, but it will handle the results of most kinds of crashes 
and leave you with a usable database.

> Wow it confuses me. In 3) you told that when app crashes outside a
> transaction and before database close, there is no data loss.

No.  The recovery after a database has crashed does not make sure there is no 
data loss.  It tries to make sure the database is restored to a COMMIT point, 
not half way through a transaction, or in a corrupt state where, for instance, 
a row has been added to a table but the table's indexes haven't been updated.  
The recovery process could, for instance, lose an entire transaction if you 
were unlucky with your crash's timing.

> And now
> you tell that until database is closed, data are not flushed to the
> disk. I was thinking that flush to disk was performed at transaction
> commit because for example, performing N INSERT requests is much
> faster within a transaction than within N implicit ones. Please excuse
> me but can you explain this to me ? I am quite confused now :-)

The only time you can be sure that the SQLite database file on disk perfectly 
reflects the state of your database is when you don't have any connections 
open, and have given caches time to empty, and have given your disk hardware 
time to do what it promised the motherboard it would do.  SQLite may or may not 
choose to update the database file on disk at any time while you have the 
connection open.  That's not your problem.

If SQLite waited for a full and proper update of the disk file every time you 
closed a transaction it would work very slowly indeed.  Nothing actually works 
like that these days: we're used to computers that tell the user "I did it" but 
then do a lot of the work afterwards.

>> iPhones make this all very confusing, because working memory isn't very 
>> different to 'disk' memory, and once apps are started they
>> normally continue running until the device shuts down because the battery 
>> runs low.  But these things were designed when things were
>> different.
> 
> Are you telling that when you commit a transaction, data are "flushed"
> into a "in-memory cache" version of the database, and when database is
> closed then this cache is flushed to the database file ?

No.  Sorry but this is more complicated than you have to care about at first 
glance.  Please think of transactions and connections are two completely 
different things.  You can write your program, for example, that until you've 
committed a transaction, /nothing/ is written to disk.  Perhaps all the INSERT 
and UPDATE commands are just held as text in memory until a SELECT or COMMIT, 
with no data generated at all.  Certainly if you have two different apps with 
connections to the same database, app1 could open a transaction, do a thousand 
INSERTs, then wait for user input for three hours, and app2 won't see any 
changes until app1 has done a COMMIT.  That's how SQL is designed.

If you want to make sure a SQLite database on disk reflects the changes you've 
made to it, close all your database connections.  Until then your changes may 
be in journal files or in the memory associated with the app that made the 
change.

If you write your software the way the documentation says, it'll work fine.  If 
you want to learn more about the deep considerations behind your questions, read



>  And that on
> iPhone/iPad, the "memory cache" is in fact the disk ?

It's just that there is no hard disk in an iPhone, just dynamic RAM and Flash 
RAM.  So really everything's in RAM.

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

Re: [sqlite] When open / close database on IOS ?

[François]

On 25 août, 19:29, Simon Slavin  wrote:
> On 25 Aug 2011, at 5:19pm, François wrote:
>
> > 3) So SQLite correctly deals with crashes (this is amazing!) But when
> > app crashes, database connection may be not closed. Could it be a
> > problem? For example, preventing from re-opening or reading database
> > later ?
>
> When you use sqlite3_open() a database file that routine checks for many 
> signs that the database wasn't closed properly -- in other
> words that an app which previously had it open crashed rather than using 
> sqlite3_close().  It then uses all the clues available in the
> database file and any journal files to restore the database to a 'safe' 
> situation.

So we just have to close database in applicationWillterminate and
applicationWillResignActive methods because SQLite handles crashes
itself. This is amazing!

> If you were part way through a transaction, then none of the operations in 
>that transaction will be present in the 'safe' version.

OK. This is quite obvious for me since crash occurs before transaction
commit.

> > 4) As far as I understand, data integrity is handled by database
> > transactions, not by database close: I mean that data are flushed into
> > database file when transactions are committed, not when database is
> > closed. Is this true ? If yes, what is the exact role of the close ?
>
> It is unfortunate that the way computers work seems to tie these things 
> together but conceptually they are completely different.  And
> I'm sorry but for brevity's sake the following leaves out some picky details 
> about how SQLite actually works.
>
> Transactions are about programming and connections between data.  For 
> instance, you might want to INSERT an invoice and UPDATE that
> client's balance and either both of those things must be done or neither.  
> Transactions are about making sure that related changes to
> your data happen 'at the same time' even though, because SQL has no commands 
> which change more than one TABLE, you cannot issue one
> command that makes both changes.  You can use transactions in your software: 
> you can start a transaction, raise an invoice, update a
> client's balance, then read the new balance and notice it's less than zero, 
> and cancel all those changes by issuing ROLLBACK instead of
> COMMIT.  Everything inside SQLite happens in a transaction.  If you fail to 
> do BEGIN yourself before doing UPDATE, SQLite actually does
> BEGIN; UPDATE; COMMIT or nothing could be done.
>

Yes, it a quite traditional transaction definition.

> Closing the database connection is about files on disk.  While your app has a 
> connection to a database the files on the disk may not
> clearly reflect the true state of your data.  Various changes may be partly 
> made.  Two or more different programs may have transactions
> open.  You may be using some form of caching to delay changes so you can make 
> them all at once.  So if you have a SQLite application
> running while you take a complete backup of your hard disk, the data copied 
> with the file may be weird in all sorts of ways.  The
> sqlite3_close() command says "Save all this to disk, so I can forget it for 
> the night and it'll all be there for the morning.".  It
> makes sure that all your change are neatly expressed in files on disk, 
> instead of them being partly on disk and partly in the memory of
> some apps you're running.

Wow it confuses me. In 3) you told that when app crashes outside a
transaction and before database close, there is no data loss. And now
you tell that until database is closed, data are not flushed to the
disk. I was thinking that flush to disk was performed at transaction
commit because for example, performing N INSERT requests is much
faster within a transaction than within N implicit ones. Please excuse
me but can you explain this to me ? I am quite confused now :-)

> iPhones make this all very confusing, because working memory isn't very 
> different to 'disk' memory, and once apps are started they
> normally continue running until the device shuts down because the battery 
> runs low.  But these things were designed when things were
> different.

Are you telling that when you commit a transaction, data are "flushed"
into a "in-memory cache" version of the database, and when database is
closed then this cache is flushed to the database file ? And that on
iPhone/iPad, the "memory cache" is in fact the disk ?

Thank you,

Best Regards,


> Simon.
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 5:19pm, François wrote:

> 3) So SQLite correctly deals with crashes (this is amazing!) But when
> app crashes, database connection may be not closed. Could it be a
> problem? For example, preventing from re-opening or reading database
> later ?

When you use sqlite3_open() a database file that routine checks for many signs 
that the database wasn't closed properly -- in other words that an app which 
previously had it open crashed rather than using sqlite3_close().  It then uses 
all the clues available in the database file and any journal files to restore 
the database to a 'safe' situation.  If you were part way through a 
transaction, then none of the operations in that transaction will be present in 
the 'safe' version.

> 4) As far as I understand, data integrity is handled by database
> transactions, not by database close: I mean that data are flushed into
> database file when transactions are committed, not when database is
> closed. Is this true ? If yes, what is the exact role of the close ?

It is unfortunate that the way computers work seems to tie these things 
together but conceptually they are completely different.  And I'm sorry but for 
brevity's sake the following leaves out some picky details about how SQLite 
actually works.

Transactions are about programming and connections between data.  For instance, 
you might want to INSERT an invoice and UPDATE that client's balance and either 
both of those things must be done or neither.  Transactions are about making 
sure that related changes to your data happen 'at the same time' even though, 
because SQL has no commands which change more than one TABLE, you cannot issue 
one command that makes both changes.  You can use transactions in your 
software: you can start a transaction, raise an invoice, update a client's 
balance, then read the new balance and notice it's less than zero, and cancel 
all those changes by issuing ROLLBACK instead of COMMIT.  Everything inside 
SQLite happens in a transaction.  If you fail to do BEGIN yourself before doing 
UPDATE, SQLite actually does BEGIN; UPDATE; COMMIT or nothing could be done.

Closing the database connection is about files on disk.  While your app has a 
connection to a database the files on the disk may not clearly reflect the true 
state of your data.  Various changes may be partly made.  Two or more different 
programs may have transactions open.  You may be using some form of caching to 
delay changes so you can make them all at once.  So if you have a SQLite 
application running while you take a complete backup of your hard disk, the 
data copied with the file may be weird in all sorts of ways.  The 
sqlite3_close() command says "Save all this to disk, so I can forget it for the 
night and it'll all be there for the morning.".  It makes sure that all your 
change are neatly expressed in files on disk, instead of them being partly on 
disk and partly in the memory of some apps you're running.

iPhones make this all very confusing, because working memory isn't very 
different to 'disk' memory, and once apps are started they normally continue 
running until the device shuts down because the battery runs low.  But these 
things were designed when things were different.

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

Re: [sqlite] When open / close database on IOS ?

[François]

Simon,

Thank you for this so fast answer!

1) OK, this is exactly what I was expecting.

2) OK, you perfectly understood my question.

Let me add two linked questions:

3) So SQLite correctly deals with crashes (this is amazing!) But when
app crashes, database connection may be not closed. Could it be a
problem? For example, preventing from re-opening or reading database
later ?

4) As far as I understand, data integrity is handled by database
transactions, not by database close: I mean that data are flushed into
database file when transactions are committed, not when database is
closed. Is this true ? If yes, what is the exact role of the close ?

Thank you

Best Regards,

François


On 25 août, 17:53, Simon Slavin  wrote:
> On 25 Aug 2011, at 4:32pm, François wrote:
>
> > 1) Do you suggest to open only one connection for all database
> > requests in the application, and to close it when user exits from the
> > application (scenario A) ? Or to use one connection for each
> > transaction (scenario B) ?
>
> One connection for all transactions should be fine.  Unless your application 
> needs database access while it's in the background, the connection should be 
> closed when the application is shifted to the background or, of course, quit. 
>  Open the connection again either when the app is foregrounded, or the first 
> time you need SQLite access and haven't opened the connection yet.
>
> > 2) If scenario A, how to be sure that database is properly closed when
> > user exits ? I mean, using applicationWillTerminate and
> > applicationWillResignActive methods helps but this is not sufficient
> > because there are other cases where user exists. For example, if he
> > kills himself the app or if it crashes.
>
> If the user kills the app properly, using the app row, the app should still 
> receive applicationWillTerminate.  I think the only situation when it does 
> not receive this is when the operating system decides the app has stopped 
> responding to events -- like the 'Force Quit' situation for OS X.  In which 
> case, we are dealing with a crash, so let's consider that part of the 
> question.
>
> I have checked the operation of SQLite on iOS under crashing situations and 
> it seems to correctly deal with this situation, recovering data from journal 
> files and restoring to an uncorrupted state, just as it does on other 
> platforms.  My testing was with a simple database and normal transactions so 
> I didn't try many complicated or unusual situations, but everything I tried 
> worked as expected.
>
> There are some aspects of operation under iOS 5 I can't comment on here, but 
> there will be no real problems if you follow Apple's guidelines for startup 
> and shutdown of iOS apps.
>
> I will also be interested in answers from other programmers.  Writing apps 
> for the iPhone isn't like normal programming !
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 4:32pm, François wrote:

> 1) Do you suggest to open only one connection for all database
> requests in the application, and to close it when user exits from the
> application (scenario A) ? Or to use one connection for each
> transaction (scenario B) ?

One connection for all transactions should be fine.  Unless your application 
needs database access while it's in the background, the connection should be 
closed when the application is shifted to the background or, of course, quit.  
Open the connection again either when the app is foregrounded, or the first 
time you need SQLite access and haven't opened the connection yet.

> 2) If scenario A, how to be sure that database is properly closed when
> user exits ? I mean, using applicationWillTerminate and
> applicationWillResignActive methods helps but this is not sufficient
> because there are other cases where user exists. For example, if he
> kills himself the app or if it crashes.

If the user kills the app properly, using the app row, the app should still 
receive applicationWillTerminate.  I think the only situation when it does not 
receive this is when the operating system decides the app has stopped 
responding to events -- like the 'Force Quit' situation for OS X.  In which 
case, we are dealing with a crash, so let's consider that part of the question.

I have checked the operation of SQLite on iOS under crashing situations and it 
seems to correctly deal with this situation, recovering data from journal files 
and restoring to an uncorrupted state, just as it does on other platforms.  My 
testing was with a simple database and normal transactions so I didn't try many 
complicated or unusual situations, but everything I tried worked as expected.

There are some aspects of operation under iOS 5 I can't comment on here, but 
there will be no real problems if you follow Apple's guidelines for startup and 
shutdown of iOS apps.

I will also be interested in answers from other programmers.  Writing apps for 
the iPhone isn't like normal programming !

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

[sqlite] When open / close database on IOS ?

[François]

Hello,

Just two questions concerning iPhone/iPad apps:

1) Do you suggest to open only one connection for all database
requests in the application, and to close it when user exits from the
application (scenario A) ? Or to use one connection for each
transaction (scenario B) ?

2) If scenario A, how to be sure that database is properly closed when
user exits ? I mean, using applicationWillTerminate and
applicationWillResignActive methods helps but this is not sufficient
because there are other cases where user exists. For example, if he
kills himself the app or if it crashes.

Thank you !

Best Regards,

François
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users