Apps closing global SQLiteDatabase

[hardya]

New to Android and Java (not to OOP (prev C++))

I have in mind by one of the various suggested means (static application member, singleton etc) to provide access to a global SQLiteDatabase and to use SQLiteOpenHelper to open / create / access cached.

My question is actually about closing the db. I think have seen almost as many opinions on this as there are people and each seems very certain of their view.

I think some are recommending close each time you move away from an activity. Does one do this for safety of data or for saving memory or because its the only place to do it. If one does this there seem to be different opinions where you do it. Some say you must close the db in onDestroy. However as far as I can see if you go to another view the first view may not have its onDestroy called til later (when the system needs more space) and then the db may be closed while the 2nd view is performing some action on it.

The alternative I see is to only close the db when the application is finished with but I cannot see a completely certain way to do this.

The fact that (it seems to me) so many people are so firm about conflicting opinions suggests this area of the API is either poorly designed or poorly documented.

On the other hand I may have completely misunderstood the whole thing. Any clarification would be greatly appreciated.

 

[Rukbat]

Remember the environment you're working in. Android can kill an app any time. So open the db, do what you need, close the db. It may waste a few microseconds, but it's better than corrupting the database. (The normal "keep traffic down" or "someone else may want that record" don't apply in Android for a database for your own app.)

 

[hardya]

So if I understand correctly what you are saying is there is no rule/recommendation where you should or should not close the database except that you should close it again at the very very earliest opportunity every time you do any small thing with it, right?

From what you say, I assume that the documented table showing when an activity is or is not killable is simply an ideal but that it is not at all to be expected so to speak, but assume the process may be killed at any moment with no calls after the onCreate?

so say handling an event you want to act on the db. You would open it in that event handling (not in onCreate) start a transaction execute eg 1, 2, 3 then commit then close.

If the process was killed while handling the event in the middle of the transaction I would have thought the db could roll it back on the next open but your suggesting it will be corrupted?

 

[Rukbat]

So if I understand correctly what you are saying is there is no rule/recommendation where you should or should not close the database except that you should close it again at the very very earliest opportunity every time you do any small thing with it, right?

There are no rules. Ask 10 experienced programmers and you'll get 30 ways to do something. If the program is fast, efficient and works, and it's easy for the user to learn and use, it was written "correctly". Then best program, following all the "rules" (Apple used to have a huge set of book with programming rules, promissing that if you followed all of them they'd "never" break your program, proved to be a lie as soon as they stopped using the 68000 CPU.)

From what you say, I assume that the documented table showing when an activity is or is not killable is simply an ideal but that it is not at all to be expected so to speak, but assume the process may be killed at any moment with no calls after the onCreate?

All programs must have code to account for all eventualities, no matter how improbable - even if it's just a try/catch block around the entire program. Since Android can kill any program at any time, the program had better be written with that in mind.

so say handling an event you want to act on the db. You would open it in that event handling (not in onCreate) start a transaction execute eg 1, 2, 3 then commit then close.

I'd open it no sooner than I needed to start the transaction. I'd close it as the next line after the commit.

If the process was killed while handling the event in the middle of the transaction I would have thought the db could roll it back on the next open but your suggesting it will be corrupted?

Since the commit was never executed, nothing actually happened to the database. Once the commmit is executed, the app can be killed and there's no corruption.

Open the database, start a transaction, change a table. If that transaction doesn't throw an error, commit it. start another transaction. Set up to change a dependent table. Your app is now killed. Oops. You can do that in a laptop, you can't do it in Android. Linux, Mac and Windows don't kill apps at - from the app's viewpoint - random.

It's always better to err on the side of caution than to get comments that your app killed someone's contact list. No one is going to comment that your app could be 3ms faster.

 

[hardya]

Thank you so much I really appreciate you taking the time to respond.

I have one last thing on my mind. If I retreive a sqldb ref from the helper (getwritabledb) and the db is now open. If I execute a sql to retrieve a cursor (many rows) which I use in the creation of an adapter which is used in my list view, I am assuming the cursor must remain valid while the listview is visible and I am concerned that closing the db will invalidate the cursor.

If I want to carry out inserts or updates from this position, ought I in your view to execute them, immediately close the db then reopen it and recreate (with a newly created cursor) and reset the adapter?

One thing I am also a little confused, what are the actual consequences if after a commit you delay closing the db and the app is killed before the close? Is the db corrupt? Can the db not be opened again?

Thank you again I appreciate your input.