• res/layout-port-notouch
• res/layout-port-qwerty-640x480
• res/layout-port-qwerty
• res/layout-port-640x480
• res/layout-port
• res/layout-land-notouch-qwerty-640x480
• res/layout-land-notouch-qwerty
• res/layout-land-notouch-640x480
• res/layout-land-notouch
• res/layout-land-qwerty-640x480
• res/layout-land-qwerty
• res/layout-land-640x480
• res/layout-land
Here, we take advantage of the fact that specific matches take precedence over “unspecified” values. So, a device with a QWERTY keyboard will choose a resource with qwerty in the directory over a resource that does not specify its keyboard type. Combine that with the “most matches wins” rule, we see that res/layout-port will only match devices with 480×320 screens, no QWERTY keyboard, and a touch-screen in portrait orientation.
We could refine this even further, to only cover the specific devices we are targeting (the T-Mobile G1, the Fictional One, and the Fictional Two), plus take advantage of res/layout being the overall default:
• res/layout-port-notouch-640x480
• res/layout-port-notouch
• res/layout-land-notouch-640x480
• res/layout-land-notouch
• res/layout-land
• res/layout
Here, 640x480 differentiates the Fictional One from the other two devices, while notouch differentiates the Fictional Two from the T-Mobile G1.
CHAPTER 20
Managing and Accessing Local Databases
SQLite[20] is a very popular embedded database, as it combines a clean SQL interface with a very small memory footprint and decent speed. Moreover, it is public domain, so everyone can use it. Lots of firms (Adobe, Apple, Google, Sun, Symbian) and open-source projects (Mozilla, PHP, Python) all ship products with SQLite.
For Android, SQLite is “baked into” the Android runtime, so every Android application can create SQLite databases. Since SQLite uses a SQL interface, it is fairly straightforward to use for people with experience in other SQL-based databases. However, its native API is not JDBC, and JDBC might be too much overhead for a memory-limited device like a phone, anyway. Hence, Android programmers have a different API to learn — the good news is that it is not very difficult.
This chapter will cover the basics of SQLite use in the context of working on Android. It by no means is a thorough coverage of SQLite as a whole. If you want to learn more about SQLite and how to use it in environments other than Android, a fine book is The Definitive Guide to SQLite[21] by Mike Owens (Apress, 2006).
Activities will typically access a database via a content provider or service. Therefore, this chapter does not have a full example. You will find a full example of a content provider that accesses a database in Chapter 28.
A Quick SQLite Primer
SQLite, as the name suggests, uses a dialect of SQL for queries (SELECT), data manipulation (INSERT, et al), and data definition (CREATE TABLE, et al). SQLite has a few places where it deviates from the SQL-92 standard, no different than most SQL databases. The good news is that SQLite is so space-efficient that the Android runtime can include all of SQLite, not some arbitrary subset to trim it down to size.
The biggest difference from other SQL databases you will encounter is probably the data typing. While you can specify the data types for columns in a CREATE TABLE statement, and while SQLite will use those as a hint, that is as far as it goes. You can put whatever data you want in whatever column you want. Put a string in an INTEGER column? Sure! No problem! Vice versa? Works too! SQLite refers to this as “manifest typing,” as described in the documentation:[22]
In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column.
In addition, there is a handful of standard SQL features not supported in SQLite, notably FOREIGN KEY constraints, nested transactions, RIGHT OUTER JOIN and FULL OUTER JOIN, and some flavors of ALTER TABLE.
Beyond that, though, you get a full SQL system, complete with triggers, transactions, and the like. Stock SQL statements, like SELECT, work pretty much as you might expect.
If you are used to working with a major database, like Oracle, you may look upon SQLite as being a “toy” database. Please bear in mind that Oracle and SQLite are meant to solve different problems, and that you will not likely be seeing a full copy of Oracle on a phone any time soon.
Start at the Beginning
No databases are automatically supplied to you by Android. If you want to use SQLite, you have to create your own database, then populate it with your own tables, indexes, and data.
To create and open a database, your best option is to craft a subclass of SQLiteOpenHelper. This class wraps up the logic to create and upgrade a database, per your specifications, as needed by your application. Your subclass of SQLiteOpenHelper will need three methods:
• The constructor, chaining upward to the SQLiteOpenHelper constructor. This takes the Context (e.g., an Activity), the name of the database, an optional cursor factory (typically, just pass null), and an integer representing the version of the database schema you are using.
• onCreate(), which passes you a SQLiteDatabase object that you need to populate with tables and initial data, as appropriate.
• onUpgrade(), which passes you a SQLiteDatabase object and the old and new version numbers, so you can figure out how best to convert the database from the old schema to the new one. The simplest, albeit least friendly, approach is to simply drop the old tables and create new ones. This is covered in greater detail in Chapter 28.
The rest of this chapter will discuss how you actually create tables, insert data, drop tables, etc., and will show sample code from a SQLiteOpenHelper subclass.
To use your SQLiteOpenHelper subclass, create an instance and ask it to getReadableDatabase() or getWriteableDatabase(), depending upon whether or not you will be changing its contents:
db = (new DatabaseHelper(getContext())).getWritableDatabase();
return (db == null) ? false : true;
This will return a SQLiteDatabase instance, which you can then use to query the database or modify its data.
When you are done with the database (e.g., your activity is being closed), simply call close() on the SQLiteDatabase to release your connection.
Setting the Table
For creating your tables and indexes, you will need to call execSQL() on your SQLiteDatabase, providing the DDL statement you wish to apply against the database. Barring a database error, this method returns nothing.