Android – Limitations of SQLite
SQLite support in Android is certainly very crucial for data driven enterprise applications. But SQLite for Android comes up with certain restrictions and limitations which is important for us to know as an Android developer. Here is the list which I found –
Non-Integer Primary key handling
SQLite supports non-integer primary key but here is a catch you have to use NON NULL constraints too because SQLite allows null as a value to be hold by the non-integer primary key column and thus permits multiple rows in a table having null in primary key column.
Referential Integrity Support
By default, SQLite does not enforce foreign key constraints so even if you have use foreign key in your database design insert statements will not fail for entering wrong data. To enable referential integrity support in recent versions of Android, use
pragma foreign_key = true
Alter statement for multiple columns
Alter statement is very useful in sql queries for changing the definition of Tables without dropping and recreating but unfortunately SQLite for Android support only one column per Alter statement. So if you have to alter single table with N columns then you have to write
N Alter statements.
Read also –
- How To Use BottomSheet In Android
- How To Display PDF In Android
- How To Use BottomSheetDialog In Android
- Use Of Material Design Snackbar In Android Apps
- Handling exif data for captured images in android
SQLite doesn’t use static typing
Any information in SQLite depeneds on the type of value inserted, not on the definition of the Table as per CREATE Table statement. To be more precise you can insert text inside an INTEGER column which is as per design of SQLite. So you have to very careful while inserting values than creating table.
Raw query flaws
In most cases where table join needed and if no ORM library is in use then Android developers end up writing raw sql queries using execSQL and rawQuery which accept bindArgs but developers ignores it. And the query looks like –
db.execSQL("delete from people where name='"+person+"' and added_date < "+ String.valueOf(System.currentTimeMillis() - HISTORY));
Now lets assume if the person parameter contains the value
Note that execSQL method ignores any text that appears after a semicolon. After the execution of above query the code will delete all the records for Robert instead just a few historical records. This kind of error can be avoided simply by using bindArgs.
SQLite Database managing strategies
There are two basic strategies for managing database from an Application:
- Get it and keep it
- Get it when you need it.
The first strategy is the simpler and a reasonable option as an open database is about few KBs of memory. An application with multiple activities, only one or few of which uses the database, then holding the database means that the memory it occupies cannot be re-purposed. Meanwhile if the application’s process terminated because it has been in the background for too long and several other new application started – the database, any connections and everything else that is part of application’s memory space are gone.
And if the code forgets to explicitly close the db connection, the garbage collector will collect the instance that will generate an error message – close() was never explicitly called on database.
To avoid this it better to open the database inside the Application instance variable or in a static class variable.
Few of the points I have referred from Enterprise Android book.