Recently we worked with big databases under android, and during that I met query optimization problems when using subqueries.
From my previous experiences with MySQL I thought, the database engine optimization is always the best, and there is no way, that multiple queries from program code is better, but I ended running multiple selects in a loop in this case for the best result.
When you release a newer version of your application, on update the android system identifies from the package name and version number, that the user has an older version installed. During updating not all data of the previous version is overwritten, databases, preferences, previously downloaded/created files on sdcard or internal storage remain unchanged. If there are any incompatibility with the old version of these, is must be taken care of.
First of all, files on the external storage can be lost or corrupted due to many other reasons, so version incompatibility is just one more reason to check those files before using them.
However the internal storage is theoretically not modified by external sources, if there are version incompatibilities, you have to check the state of this files too.
In android it is highly important to use transactions when working with databases.
First, in android database operations - especially writing - are very slow. Batching them into transactions will make them much faster.
Second, the database remains consistent under any circumstances. The database system makes sure to all the operations in a transaction take effect, or on error, rollback all of them.
If you are used to other platforms like PHP+MySQL where the code usually runs on a powerful server, witch is not likely to stop execution "unexpectedly", you can be surprised how much it affects the performance in android.
The android system can kill apps/threads/activities and so interrupt database usage, the battery can discharge or can be removed etc.
The implementation is very simple, using 3 methods in the SQLiteDatabase class:
If you want to include a database with initial data in your apk, you have to insert the database file into the projects assets folder, then programmatically check if the database, and if it does not exists copy the one from the assets.
You will need a function to check if the database exists, fox example:
The most spread method to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system.
We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.
I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.
Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:
Hoever the practice is to store them normally and save the access route (Uri), sometimes it can be handy to store files/images completely in database.
In sqlite database there are only a few data types, so its easy to choose: files can be stored in a text as a ByteArray.
Lets see a sample code, where we download an image from the Internet then store it in the local database:
When I first tried to manage an sqlite database on an android device I was not sure about where I fail in it. Can I even insert the records into the database, or I fail only to read the data from it? So I started to search for possibilities to debug the database lifecycle.
The system stores databases in the /data/data/package_name/databases folder by default.
In a command line using the adb (Android Debug Bridge - found in the android sdk tools library) you can access the databases on a running emulator like below:
