SQLite

What is SQLite?

     SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

     SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.

SQLite in Android:

       SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration.

You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.

Access to an SQLite database involves accessing the filesystem. This can be slow. Therefore it is recommended to perform database operations asynchronously, for example inside the AsyncTask class.

If your application creates a database, this database is by default saved in the directoryDATA/data/APP_NAME/databases/FILENAME.

The parts of the above directory are constructed based on the following rules. DATA is the path which theEnvironment.getDataDirectory() method returns. APP_NAME is your application name. FILENAME is the name you specify in your application code for the database.

SQLite Architecture:

   android.database.sqlite package contains the SQLite specific classes.

1) SQLiteOpenHelper.

2)SQLiteDatabase.

SQLiteOpenHelper:

          To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.

In this class you need to override the onCreate() and onUpgrade() methods.

onCreate() is called by the framework, if the database does not exists.

onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.

Both methods receive an SQLiteDatabase object as parameter which represents the database.

SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode.

The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

It is best practice to create a separate class per table. This class defines static onCreate() and onUpgrade()methods. These methods are called in the corresponding methods of SQLiteOpenHelper. This way your implementation of SQLiteOpenHelper will stay readable, even if you have several tables.

SQLiteDatabase:

  SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.

More specifically SQLiteDatabase provides the insert(), update() and delete() methods.

In addition it provides the execSQL() method, which allows to execute an SQL statement directly.

The object ContentValues allows to define key/values. The "key" represents the table column identifier and the "value" represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries.

Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .

rawQuery() directly accepts an SQL select statement as input.

query() provides a structured interface for specifying the SQL query.

SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

SQLite is at the heart of Android’s database support. This database was developed with embedded environments in mind – and is used not only by Android but also by Apple’s iOS and Blackberry’s system as well as lots of other systems with low memory footprint and comparatively little CPU horsepower.

Why SQLite in the first place?

Of course there is a reason why SQLite is so dominant in the embedded and also the mobile world. The main reasons are

• Low memory consumption
• Ease of use
• Free availability

SQLite in Android consumes very little memory

While SQLite’s memory footprint starts at about 50 kilobyte it’s remains low even for bigger projects with more complex data structures (at about a few hundred kilobytes). Keep in mind: In the mobile world the memory per process as well as total usage of memoryis limited compared to desktop systems. Gladly SQLite should not add too much burden to the memory consumption of your app.

SQLite is easy to use

SQLite is a serverless system. I will detail what this means in the next section, but it makes handling of the database that much easier. No need for configuration files or complicated commands. You definitely do do not want these on mobile systems. Those systems must run out of the box without forcing the user to manually configure anything or forcing the developers to consider additional constraints.

SQLite’s source code is released under the public domain

SQLite has a huge commercial backing by the likes of Google, Adobe, Mozilla or Bloomberg. And it is used in many, many products and open source projects. The project is maintained actively so one can expect further imrpovements as well as optimizations in the future. Android for example uses ever newer versions in its SDKs to make use of these improvements.

SQLite is not like any other database

Though SQLite offers quite an impressive feature set given its size, it differs in many aspects from a conventional database system:

• SQLite is serverless
• SQLite stores data in one database file
• SQLite offers only a few data types
• SQLite uses manifest typing instead of static types
• SQLite has no fixed column length
• SQLite uses cross-platform database files

I will delve into each of these points a bit deeper – and add another one that’s only relevant if you want to support older Adroid versions.

SQLite is serverless

There is no SQLite process running at all. You use SQLite more like a library which helps you to access the database files. You do not need to configure the database in any way. No port configuration, no adding of users, no managing of access levels, no tablespace setup and what not. You simply create the database files when you need it. I will cover how to create a database in the next part of this tutorial series.

All data is stored in one single database file

SQLite uses one file to store all the contents of your database. This file contains the main data, as well as indices, triggers and any meta data needed by SQLite itself. Newer versions add a journal file which is used during transactions.

SQLite offers fewer datatypes

The following table shows all types supported by SQLite. If you use other types (like varchar) in your CREATE TABLE statement SQLite maps them as closely as possible to any of these types.

SQLite datatypes

Type	Meaning
NULL	The null value
INTEGER	Any number which is no floating point number
REAL	Floating-point numbers (8-Byte IEEE 754 – i.e. double precision)
TEXT	Any String and also single characters (UTF-8, UTF-16BE or UTF-16LE)
BLOB	A binary blob of data

The biggest problem here is the missing datetime type. The best thing to do is to store dates as Strings in the ISO 8601 format. The string to represent the 28th of March 2013 (the day of publishing this post) would be “2013-03-28“. Together with the publishing time it would look like this: “2013-03-27T07:58“. Stored this way SQLite offers some date/time functions to add days, change to the start of the month and things like that.Note: In contrast to ISO 8601 SQLite doesn’t offer any timezone support.

Also missing is a boolean type. Booleans have to be represented as numbers (with 0 being false and 1 being true).

Although a blob type is listed in the table above, you shouldn’t use it on Android. If you need to store binary data (e.g. media-files) store them on the file system and simply put the filename in the database. More on SQLite types can be found on the SQLite project page.

SQLite doesn’t use static typing

Any type information in SQLite is dependent on the value inserted, not on the data definition of the CREATE TABLE statement. Let’s say you create a column as an INTEGERcolumn. Then you might still end up with TEXT entries in this column. That’s perfectly legal in SQLite – but to my knowledge in no other relational database management system.

This reliance on the value is called manifest typing – something in between static and dynamic typing. In Mike Owens’ book on SQLite you can find a very good and much more detailed explanation of SQLite’s typing.

SQLite has no fixed column length

If you look at the table above you see that there is only a definition for text, but not for varchar(xyz), where you can limit the column to an arbitrary length. In SQLite any TEXT value is simply as long as it is. SQLite adds no restrictions. Which might be pretty bad. To enforce a restriction, you have to do this in your code. SQLite won’t help you. On the other hand you will not get into any trouble if Strings get too long or numbers too large. Well, you will not get any SQLExceptions – though it might break your code in other ways or destroy your UI!

SQLite’s database files are cross-platform

You can take a file from a device put it on your laptop and start using it as if you created it on your laptop from the outset.

It might come handy to pull the database file from the device (or your emulator) and run queries from within your development machine. Especially if you want to use tools with a graphical user interface. One of the best know is the SQLite Manager extension for Firefox which you might prefer to sqlite3 in some cases (see screenshot).

SQLite Manager showing the results of an SELECT statetment

Also you sometimes might want to prepare the database on your development machine and put a database onto your device which contains the needed set of data like a very large dataset to test for performance or a defined database for starting automated tests.

Thanks to SQLite’s cross platform file format it is also possible to deliver a prefilled database with your app to your users. Jeff Gilfelt has written a library to help you with it. You can find his Android SQLite Asset hHelper library on github.

SQLite offers a special kind of table for fast text searches

To help developers create fast text searches SQLite offers also a special kind of database table. The so called FTS3/FTS4 tables. FTS stands for “full text search”. You have to create special tables for it to work and use slightly different SELECT statements (and rarely special INSERT statements) to use them efficiently. But if you do so, you gain tremendous performance improvements for text only search. I will cover FTS in an extra blog post.

Older versions of SQLite do not support referential integrity

The version of SQLite integrated into older versions of Android (3.4 in the early days, later on 3.5.9) doesn’t support referential integrity. This changed with Android 2.2. Thus this problem should fade away pretty soon and is only relevant if you want to support API level 7 or lower. In this case this limitation forces you to take special care when using foreign keys within tables. Since databases on Android are usually way less complex than those of enterprise projects this might not be as bad a problem as it sounds. But still, you have to be careful. Of course being careful is never wrong 

For more information on how SQLite differs from other database go to the SQLite website.

Of course what is not different from other SQL Database systems is the use of SQL to create tables, and query and update them. And of course SQLite is relational – that is, you deal with tables which store your data and the results of your queries also take the form of tables.

Where are those database files on Android?

As I have mentioned, a database in SQLite is more or less simply a file accessed through the SQLite API. In Android these files are by default stored within the

/data/data/<package-name>/databases

directory. Thus if your package is called com.grokkingandroid.android and your database is called “sample.db” the actual file would be/data/data/com.grokkingandroid.android/databases/sample.db.

Keep security in mind

As usual in Android the access rights of the database file determine who can use your database. If you follow the standard way presented in the following posts of this series, your database file will be located within the private directory of your app. This means that your app owns the database file and no one else can access it. Even using the other less common ways to create the database you can only grant access to the file. Thus others can access all of your database or nothing. There is no middle ground.

Still: You should never rely on data being safe from prying eyes in the database. Any sensitive data should be encrypted. Very sensitive data should not be stored on the device at all. Keep in mind that if the device gets lost, any misbehaving finder of the device can gain access to the database file as well as to your app. On a rooted device all files can be read. Apps like SQLite Editor make it easy to read even sensitive data – if they are not encrypted:

SQLite Editor showing the two databases of the contacts app

In cases where data privacy is of utmost importance, you have to revert to secured services or force the user to enter a secret every time before encrypting and storing the data or reading and decrypting them respectively.

Android differs from the standard Java way

Apart from SQLite’s own peculiarities there is also the way Android deals with this database. First of all SQLite is an integral part of Android. Every app developer can rely on SQLite being present on an Android system. Though which version of SQLite is dependent of the SDK which the device uses. – which of course is a good thing, since SQLite is developed actively and future Android versions should make use of those improvements.

The biggest Android-speciality of course is how Android treats the database. Android doesn’t use JDBC. And so also no JDBC driver for SQLite. This means that you are stuck with using SQLite the Android way or using another database which you have to include in the download of your app (though I see no need for any other database). It also means that you have to learn a new way to deal with databases. Any prior JDBC-knowledge is of no use in the Android world. The rest of this series will be about the special API Android provides to deal with SQLite in your JAVA-code.

Lessons learned

You have seen why Google chose SQLite as the underlying database for Android’s apps. It has many advantages, most of all it’s low memory footprint and it’s ease of use.

Furthermore you learned about how SQLite differs from most other relational database systems and what implications this might have.

With this knowledge you are well prepared to start using SQLite. In the next installments of this series I’m going to show you how to create the database, how to insert, update and delete data and how to query those records. I’m also going to post about SQLite’s full text searches feature and how to use it.