Android: Sqlite Version

Going back to that db corruption issue I wrote about a few weeks ago. Had a lot of questions on this one. Some of the posts I found talk about it being fixed in Version 3.5.4 (2007-12-14). But that might just be one cause of the file corruption.  3.* versions and Ice Cream Sandwich run on 3.7.4. 4.1/4.2 upgrade to 3.7.11. There are probably multiple causes of the corruption and a backup/restore strategy should be used if appropriate.

List is at:  http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android

SQLite 3.7.4:

15-4.0.3-Ice Cream Sandwich
14-4.0-Ice Cream Sandwich
13-3.2-Honeycomb
12-3.1-Honeycomb
11-3.0-Honeycomb

Android: Database corruption issue

I recently ran into an issue where all the rows of all the tables in a user's database were deleted. For this app, there is some security code that would do this, but it would also delete temp files and backup data files. No exceptions were thrown and no app crashes were reported.Those files were still there which indicated that the service for wiping the db didn't run, but I wasn't able to duplicate it and there were no other instances of this happening.

The other day I was running some backend tests and after the test run, went to restore the previous version of the DB to rerun the test and got this error : android.database.sqlite.SQLiteDatabaseCorruptException



12-29 10:22:39.190: D/(14156): Here
12-29 10:22:39.200: I/SqliteDatabaseCpp(14156): sqlite returned: error code = 11, msg = database corruption at line 46978 of [8609a15dfa], db=/data/data/com.myapp.android/databases/db_file
12-29 10:22:39.200: I/SqliteDatabaseCpp(14156): sqlite returned: error code = 11, msg = database corruption at line 46978 of [8609a15dfa], db=/data/data/com.myapp.android/databases/db_file

/data/data/com.myapp.android/databases/db_file
12-29 10:22:39.200: E/DefaultDatabaseErrorHandler(14156): Corruption reported by sqlite on database: /data/data/com.myapp.android/databases/db_file
12-29 10:22:39.200: E/DefaultDatabaseErrorHandler(14156): deleting the database file: /data/data/com.myapp.android/databases/db_file

12-29 10:22:39.220: E/SQLiteDatabase(14156): android.database.sqlite.SQLiteDatabaseCorruptException: error code 11: database disk image is malformed
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:112)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1737)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1610)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at com.myapp.android.database.DBHelper.createRecord(DBHelper.java:80)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at com.myapp.android.contentproviders.SynchLogProvider.insert(SynchLogProvider.java:117)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.content.ContentProvider$Transport.insert(ContentProvider.java:203)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:153)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at android.os.Binder.execTransact(Binder.java:339)
12-29 10:22:39.220: E/SQLiteDatabase(14156): at dalvik.system.NativeStart.run(Native Method)
12-29 10:22:39.220: E/DatabaseUtils(14156): Writing exception to parcel

The database was completely wiped. Turns out that if a Sqlite database is corrupted, Android automatically deletes the database file and recreates it. This is a known Android bug/feature.

I didn't get this error until I tried to restore the db backup file and Android even thought that a desktop backup of the file was corrupted (although I was able to open it up in a third party tool on the desktop.) Likely the cause was the backup (simply a file copy) occurred while a service was updating the db.  We use the backup all the time and this was the first time that it had occurred. (The user issue was caused by something else corrupting the db file not the backup)

http://code.google.com/p/android/issues/detail?id=10127

http://stackoverflow.com/questions/2960015/android-database-disk-image-is-malformed

http://www.sqlite.org/lockingv3.html#how_to_corrupt

Solution appears to be to have a backup and restore routine so that data can be recovered if necessary.

Android Content Provider Limiting rows returned

To limit the number of rows returned by a content provider, try this code
Product obj = new Product();

        Cursor cursor = getContentResolver().query(
                ProviderHelper.determineURI(obj), null, null, null, " _ID DESC LIMIT 5000");

This will return the last 5000 rows in descending order.Limit needs to be last and don't forget the column name you want to descend.

Android Cursor code example

Processing cursors is a very common task that developers do when creating android apps. Here's some sample loops for processing cursors. Pay attention to log files to make sure that there are no null pointer exceptions when developing your cursor processing.

Cursor cur = dB.rawQuery("SELECT firstname, salary FROM demos " +
           "where salary>100,000 LIMIT 10", null);

if (cur != null ) {

    if  (cur.moveToFirst()) {

        do {
            String firstName = cur.getString(cur.getColumnIndex("firstname"));

            int salary = cur.getInt(cur.getColumnIndex("salary"));

            results.add("" + firstName + ",salary: " + salary);
        } while (cur.moveToNext());

    }
}
cur.close();

Organizing Resource Files

For very complex projects, I like to try to group the layout files together so that activity files, fragment 

files, adapter files and include files are all together. Developers working on an activity or fragment can immediately find the file that they need to change.  I do this by placing a key at the front of a file. Doesn't matter what the key is just as long as it makes sense. I typically make the activity files "views" and prepend a vi_ to their file names.Why? Eclipse will display the files alphabetically and since they files are not modified frequently, I want them at the bottom of the list and out of the way. Same goes for adapter template files.  

Here's what I use:

1, Container/Activity Files

Name container files the same as activity where possible. In this case I’ve named them vi_{ACTIVITYNAME}_container_portrait.xml and vi_{ACTIVITYNAME}_container.xml (for horizontal mode) I named them vi_* so that they end up towards the bottom of the directory since we won’t be touching them much and they’re more out of the way then.

2.       Fragments

In the example above, I named the fragment xmls, {activityname}.xml but we might want to call them fr_{ACTIVITYNAME}_top.xml, fr_{ACTIVITYNAME}_bottom.xml etc

3.       Include files for things like common headers I call inc_{FILEPURPOSE}.xml for example inc_topnav.xml

4.       Adapter list item files (I don’t see any in the screen shot) I would call li_{purpose}.xml  Purpose is generally related to the adapter name. For example, an adapter that shows a list of products would be called ProductsAdapter.java  with the list item being li_productsadapter.xml

Android Market Share now a dominating 68%

IDC reports that Android market share of the smart phone market has increased to 68%. Nearly 105 million Android phones shipped in last quarter, twice as many as a year ago. Apple also grew their shipments to 26 million.  Take a stab at Apple's market share.Where do you think it is? Way down at 17% of the global market.  Open platform is certainly the way to go.  Apple should have learned that way back in 1980s when it lost it's enormous advantage in personal computers to IBM compatible.

Android: Business models Some Analysis of Downloads

A potential client contacted me the other day to talk about an idea that they have. It's a great idea and wanted to bounce around some business model ideas.  The question that they had was the following:

So, we are trying to figure out for Super App Idea, the kind of download traffic we might get if we were free vs $1 vs $2?  Do you have a feel for that?  I am sure it might be a little different on iPhone as opposed to Android as well.  Do you have any sense of conversion rates  as well?  

I did a quick look for a few apps that had a free version and a paid version. I decided to use a pill tracking system since that was an app I downloaded to test the other day.  The first app was a free pill box app (https://play.google.com/store/apps/details?id=com.mobilepills.pillbox&hl=en) that received between 10,000 and 50,000 downloads.  The first comment was put in on September 2010 so it's been around about 2 years. We'll assume that the first comment was put in right around launch date. So conservatively it is generating about 5000 downloads a year.  Note these downloads don't include downloads from third party distributors like Amazon. At a high end, they've received 25,000 downloads per year

The second one I looked at was a pay to use app that has similar functionality.   This app (https://play.google.com/store/apps/details?id=com.sartuga.android.pillboxalert) received between 1000 and 5000 downloads since their first comment on November 8, 2009. They are selling their app at $1.99. So taking the conservative 1000 downloads, that means that they've received about 300 downloads a year or revenue of about $600 per year. At a high end, they've received 1600 downloads per year or revenue of about $3200 per year.

Based on this example, free apps will get about ten times the downloads as paid apps.

I'm going to create a few more blog entries in the near future taking a look at a couple of ways to potentially increase the downloads if you have a paid application.