SQLite in Android

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 theISO 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 theSQLite project page.

SQLite doesn’t use static typing

Any type information in SQLite is dependent on thevalueinserted,not on the data definition of theCREATE TABLEstatement. Let’s say you create a column as anINTEGERcolumn. Then you might still end up withTEXTentries 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. InMike Owens’ book on SQLiteyou 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 anySQLExceptions– 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).

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 Gilfelthas written a library to help you with it. You can find hisAndroid SQLite Asset helper libraryon 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 theSQLite 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 courseSQLite 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 calledcom.grokkingandroid.androidand 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:

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.

Disclaimer: This post contains a link with my Amazon.com referral ID. For each sale this provides me with a small commission. Thank you for your support!

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

SQLite datatypes

老外写的太好了，不忍心翻译，大家看原文吧。 http://www.grokkingandroid.com/sqlite-in-android/ 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 memoryper processas well astotal 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 offersquite an impressive feature setgiven 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.

TYPE

MEANING