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.
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.
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.
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:
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 (;).
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.
The TournamentScores database schema has three tables:
SQLite3 has support for the following common datatypes for columns:
Once you’ve determined which columns are necessary for each table, you’re ready to create some tables within your database schema.
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;
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);
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
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
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;
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”.
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
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:
The results from this query are shown below:
PlayerName TotalWeightedScore ------------------------- ----------------- Garry Kasparov 100.0 Bobby Fisher 87.5 Bart Simpson 46.25
goto top
SQLite with TCB
The embedded database used by this distribution of TCB is the command line version of SQLite. To use the tables described above in a TCB program, create a new database file (sqlite.exe chesstournament.db3) and follow the directions above to create and populate database tables in the new database file.
To make the database easily accessible to TCB programs, add a line to the file "data\lstDBs.lst" containing something like: data\chesstournament.db3. The user can now use the makeprc function to create a browse/editor TCB program for each table in the database. This function is available with a single click from either of the TCB programs, prcs\dbmanager2.prc or prcs\sqliteview.prc.
TCB works with SQLite by sending a query to SQLite.exe which, in turn, sends any query results to a disk file. TCB then does whatever TCB is instructed to do with the data returned by the query.
Contact: Fred Pierce, makpiya@aol.com, makpiya@gmail.com, 831.240.9969