Android applications can store application data in SQLite databases. In this tutorial, you learn how SQLite databases are designed and manipulated.
Here we begin by designing and using a simple SQLite database to manage chess tournament scores. This tutorial is meant as a brief overview of how SQLite databases work. This knowledge will then be used in future development tutorials to implement database-driven Android applications.
Android applications can create and manipulate their own private SQLite relational databases. Developers can also inspect and modify databases on a given Android emulator or device using the sqlite3 command-line tool provided as part of the Android SDK tool called Android Debug Bridge (adb).
In this tutorial, we assume that you have some understanding of relational databases, in theory, but require a bit of a refresher course before you use them within your Android applications. This particular tutorial does not require any tools; it’s more a theoretical exercise.
However, if you are planning to develop Android applications which rely upon SQLite databases, you will need to install the tools necessary for Android development, such as the Android SDK and the Eclipse IDE. Check out the many other helpful Android tutorials available here on Mobiletuts+ to help you work through these tasks.
What is SQLite?
SQLite is a lightweight relational database engine. SQLite is fast and has a small footprint, making it perfect for Android devices. Instead of the heavyweight server-based databases like Oracle and Microsoft SQL Server, each SQLite database is stored within a single file on disk. Android applications can choose to store private application data in a SQLite database.
Note: If you’re familiar with SQL, then SQLite will be very easy to pick up. SQLite is basically a stripped-down SQL database engine for embedded devices. For specific information about SQLite and its features, functionality, and limitations, see the SQLite online documentation.
A Quick Review of Database Fundamentals
A database is simply a structured way of storing data in a persistent fashion. Data is stored in tables. A table has columns with different datatypes. Each row in a table represents a data record. You may find it helpful to think of a table like an Excel spreadsheet. For an object oriented programming perspective, each table in a database often represents an object (represented by a class). Each table column represents a class attribute. Each record in a table represents a specific instance of that object.
Let’s look at a quick example. Let’s say you have a company database with a table called Employee. The Employee table might have five typed columns: EmployeeID (number), FirstName (string), LastName (string), Title (string) and Salary (number). You could then add a record to the data base for an employee named John Doe and a separate record for an employee named Anne Droid.
Data within a database is meant to be inspected and manipulated. Data within a table can be:
- Added (using the INSERT command)
- Modified (using the UPDATE command)
- Removed (using the DELETE command)
You can search for specific data within a database using what is called a query. A query (using the SELECT command) may involve one table, or multiple tables. To create a query, you must specify the tables, data columns, and data values of interest using SQL command language. Each SQL command is terminated with a semicolon (;).
The Chess Tournament Database
The best way to truly understand how SQLite databases function is to work through a simple example, so let’s do so. Let’s pretend that we have an application that keeps track of player scores from a casual chess tournament. Player scores are recorded and then, at the end of a series of matches, the winner is determined. Each player’s overall tournament score is calculated from their performance on:
- Four warm-up heats (weight: 10% of overall score each)
- One semi-final (weight: 25% of overall score)
- One final (weight: 35% of overall score)
Note: For the tournament, player scores could be based upon a formula that factors in the time it took to win the game and the type and number of pieces left on the board at the end of the game. This way, a strong player will receive a high score for losing few powerful pieces and winning the game quickly. Perhaps style and attitude are included by the judges to encourage fun, light play. How scores are calculated is really not important to how we define our database; we just store them in the database. For simplicity, we will assume that scores are based on a scale of 0–100.
Designing the Database Schema
A database schema is simply the definition of the structure of the database in terms of tables, data columns and such. The schema for our tournament database is quite simple:
The TournamentScores database schema has three tables:
- The Players table contains player information.
- The Games table contains information about each game and how much it counts toward the player’s overall tournament score.
- The GameResults table contains all players’ game scores.
SQLite3 has support for the following common datatypes for columns:
- INTEGER (signed integers)
- REAL (floating point values)
- TEXT (UTF-8 or UTF-16 string; encoded using database encoding)
- BLOB (data chunk)
Once you’ve determined which columns are necessary for each table, you’re ready to create some tables within your database schema.
Working with Tables
Let’s begin by creating the Players table. This table requires a unique player id to reference each player. We can make this the primary key (to uniquely identify a record in this table) and set its autoincrement attribute. Autoincrement means that each time a new player record is added, the record will get a new, unique player id. We also want to store the first and last name of each player-no nulls allowed.
Here we can use the CREATE TABLE SQL statement to generate the Players table:
CREATE TABLE Players ( id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT NOT NULL, lname TEXT NOT NULL );
The Games table is very similar. We need a unique game id to reference each game. We also want a friendly name for each game and a weight value for how much the game counts towards the player’s final tournament score (as a percentage). Here’s the SQL statement to create the Games table:
CREATE TABLE Games ( id INTEGER PRIMARY KEY AUTOINCREMENT, gamename TEXT, weight REAL DEFAULT .10 CHECK (weight<=1));
You can also delete tables using the DROP TABLE statement. For example, to delete the Games table, use the following SQL command:
DROP TABLE Games;
Populating Tables with Data Records
Before we move on, let’s add some data to these tables. To add a record to the Players table, you need to specify the column names and the values in order. For example, the following SQL statement uses the INSERT command to add a record for chess player Bobby Fisher:
INSERT into Players (fname, lname) VALUES ('Bobby', 'Fisher');
While we’re add it, we’ll add two more players: Bart Simpson (a very pitiful chess player) and Garry Kasparov (perhaps the best chess player ever). At the same time, we need to add a bunch of records to the Games table. First we add the semi-final, which counts for 25 percent of the player’s tournament score:
INSERT into Games (gamename, weight) VALUES ('Semi-Final', .25);
Then we add a couple warm-up heats, which use the default weight of 10 percent:
INSERT into Games (gamename) VALUES ('Warm-up Heat 1');
Finally, we add a final worth 35 percent of the total tournament score:
INSERT into Games (gamename, weight) VALUES ('Final', .35);
Querying Tables for Results with SELECT
How do we know the data we’ve added is in the table? Well, that’s easy. We simply query for all rows in a table using a SELECT statement:
SELECT * FROM Games;
This returns all records in the Games table:
id gamename weight ----- --------------- ------ 1 Semi-Final 0.25 2 Warm-up Heat 1 0.1 3 Warm-up Heat 2 0.1 4 Warm-up Heat 3 0.1 5 Warm-up Heat 4 0.1 6 Final 0.35
Using Column Aliases and Calculated Columns
We can also create our own columns and alias them. For example, we can create a column alias called PlayerName that is a calculated column: It’s the player’s first and last names concatenated using the || operator, separated by a space:
SELECT fname||' '|| lname AS PlayerName, id FROM Players;
This query produces the following results:
PlayerName id ------------ -- Bobby Fisher 1 Bart Simpsen 2 Garry Kasparov 3
Altering Data in Tables
Bart’s (player id 2) last name is spelled incorrectly. To update the Players table to reflect the correct spelling, you can use the UPDATE command:
UPDATE Players SET lname='Simpson' WHERE playerid=2;
You can delete rows from a table using the DELETE function. For example, to delete the record we just updated:
DELETE FROM Players WHERE playerid=2;
You can delete all rows in a table by not specifying the WHERE clause:
DELETE FROM Players;
Using Foreign Keys and Composite Keys
Now that we have our Players and Games all set up, let’s create the GameResults table. This is a more complicated table. The GameResults table pairs up player ids from the Players table with game ids from the Games table and then lists the score that the player earned for the specific game. Columns, which link to other tables in this way, are often called foreign keys. We want unique player-game pairings, so we create a composite primary key from the player and game foreign keys, to uniquely identify a GameResults record. Lastly, we enforce that the scores are whole numbers between 0 and 100.
CREATE TABLE GameResults ( playerid INTEGER REFERENCES Players(id), gameid INTEGER REFERENCES Games(id), score INTEGER CHECK (score<=100 AND score>=0), PRIMARY KEY (playerid, gameid));
(Note: SQLite does not enforce foreign key constraints, but you can set them up anyway and enforce the constraints by creating triggers.)
Now it’s time to insert some data to the GameResults table. Let’s say Bobby Fisher (player id 1) received a score of 82 points on the semi-final (game id 1). You could use the following SQL command to insert the appropriate record into the GameResults table:
INSERT into GameResults (playerid, gameid, score) VALUES (1,1,82);
Now let’s assume the tournament is played and the scores are added to the GameResults table. Bobby is a good player, Bart is a terrible player, and Garry always plays a perfect game. Once the records have been added to the GameResults table, we can perform a SELECT * command to list all records in the table, or we can specify columns explicitly like this:
SELECT playerid, gameid, score FROM GameResults;
Here are the results from this query:
playerid gameid score ---------- ---------- ----- 1 1 82 1 2 88 1 3 78 1 4 90 1 5 85 1 6 94 2 1 10 2 2 60 2 3 50 2 4 55 2 5 45 2 6 65 3 6 100 3 5 100 3 4 100 3 3 100 3 2 100 3 1 100
As you can see, this listing is not particularly “human-readable”.
Querying Across Multiple Tables Using JOIN
Wouldn’t it be more helpful if the names of the players and Games were shown instead of their numeric ids? Querying and combining data in SELECT statements is often handled by performing a JOIN with multiple table sources; there are different kinds of JOINS. When you work with multiple tables, you need to specify which table that a column belongs to (especially when columns are named the same, such as with all these different id columns). You can refer to columns by their column name or by their table name, then a dot (.), and then the column name.
Let’s relist the player scores again, only this time, include the name of the game and the name of the player. Also, we limit our results only to the score for the Final (game id 6):
SELECT Players.fname||' '|| Players.lname AS PlayerName, Games.gamename, GameResults.score FROM GameResults JOIN Players ON (GameResults.playerid=Players.id) JOIN Games ON (GameResults.gameid=Games.id) WHERE gameid=6;
which gives us the following results (you could leave off the WHERE to get all Games):
PlayerName gamename score ------------------ -------------- ----- Bobby Fisher Final 94 Bart Simpson Final 65 Garry Kasparov Final 100
Executing More Complex Queries
The sky’s the limit when it comes to the queries you could perform here. For our example, the most important query is the one that tells us who won the tournament.
Here is the final query for calculating the tournament rankings:
SELECT Players.fname||' '|| Players.lname AS PlayerName, SUM((Games.weight*GameResults.score)) AS TotalWeightedScore FROM GameResults JOIN Players ON (GameResults.playerid=Players.id) JOIN Games ON (GameResults.gameid=Games.id) GROUP BY GameResults.playerid ORDER BY TotalWeightedScore DESC;
This query gathers information from several different tables using JOINs and calculates the tournament results in a readable way. So let’s look at what this query does:
- Each player’s full name is displayed as the first result column (calculated from their first and last name)
- Each player’s tournament score is displayed as the second result column (calculated from the weighted scores of all games played by a given player)
- The result records are listed in descending order based upon the tournament scores (first place, second place, etc.)
The results from this query are shown below:
PlayerName TotalWeightedScore ------------------------- ----------------- Garry Kasparov 100.0 Bobby Fisher 87.5 Bart Simpson 46.25
This concludes our exploration of a simple SQLite database example: a chess tournament database. Hopefully you’ve reacquainted yourself with relational database concepts like tables, records, and queries and familiarized yourself with many of the commonly used SQLite commands. Finally, you’ve walked through the design and usage of an example database. Now that you’ve got a handle on how SQLite databases work, you’re ready to use them within your Android applications (the topic of our next tutorial in this series).
About the Authors
Mobile developers Lauren Darcey and Shane Conder have coauthored several books on Android development: an in-depth programming book entitled Android Wireless Application Development and Sams TeachYourself Android Application Development in 24 Hours. When not writing, they spend their time developing mobile software at their company and providing consulting services. They can be reached at via email to [email protected], via their blog at androidbook.blogspot.com, and on Twitter @androidwireless.