Android & SQLite – 1MB limit, IOException and general woes.

Android & SQLite – 1MB limit, IOException and general woes.

This long weekend I worked on an app: Scrabble For Anagrams. The UI took about 8 hours. The engine to generate and perform rapid lookups of character permutations took about 8 hours. After 2 days of hard work I believed the app was working great and was nearly ready to go on the market. The dictionary was converted into a SQLite DB outside of the android application and was included as an asset.

However, my original dictionary (found online) contained only 10K words and I pretty rapidly found some word combinations that were valid but weren’t in the dictionary – clearly unacceptable. No problem! I found a freely available 400K word dictionary, generated the new DB and placed it in the asset folder. Build, run and bam! IOException.

It took an addition 6 hours to get back to where I was with the new database! Android has a 1MB limitation on reading files within the Asset folder (due to dynamic decompression). I had unknowingly stayed within that limitation in the past, however my new database was > 3MB and hence would not work.

I tried compression and using the GZIP Input stream, I could not get it to work. I tried changing the extension of my DB to .mp3 so that automatic decompression would not occur when building the APK. I still could not get it to work!

This was my final approach:

Dump your existing SQLite database into a series of sql commands

 sqlite dictionary.db .dump > output.sql

Split the resulting file up into 900K chunks:

 split -b900k output.sql dump.

and rename the chunks dump.1 to dump.4. On the android side of things, I performed 4 distinct transactions of separate SQL commands, to rebuild the database on the phone:

db = openOrCreateDatabase(
		"dictionary.db"
		, SQLiteDatabase.OPEN_READWRITE
		, null
		);

for(int i=1;i<5;i++)
{
	progress = ((i-1)*25);
	db.beginTransaction();
	String line;
	Log.e("Uploading", Integer.toString(i));
	try {
		
		InputStream is = myContext.getAssets().open("dump."+Integer.toString(i));
		BufferedReader bufferedReader =  new BufferedReader(new InputStreamReader(is)); 
		while ((line = bufferedReader.readLine()) != null)
		{
		//	Log.e("Line", line);
			db.execSQL(line);
		}
		db.setTransactionSuccessful();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	
	}
	 finally
	 {
		//Cursor cur = mydb.rawQuery(query, null);
			
			db.endTransaction();
	}
}
	db.close();

Unfortunately with the large dictionary it takes a few seconds to build the database when you first run the application, however I threaded the function so the user can interact with the UI while it happens. The query times per combination of characters are also up to 10 seconds on an old phone – however this is within the realms of acceptability.

3 thoughts on “Android & SQLite – 1MB limit, IOException and general woes.

  1. Can we copy our sqlite database browser file apart from Assets foder. Will it free the limitation of 1 mb size

Leave a Reply

Your email address will not be published. Required fields are marked *