MAP524/DPS924 Lecture 6

SQLite

This is a semi-structured data store from your application on the phone. To store and retrieve data in the database you use SQL-like query strings, but SQLite isn't nearly as powerful as a typical SQL server. Only the very simplest parts of SQL are supported.

There's a lot of good material describing SQLite use on Android on this website.

Command-line

Often the easiest way to create an empty database, insert test data, and test your app's usage of SQLite is the command-line tool sqlite3. On Linux it should be installed by default, on other platforms you can download and install it yourself.

# Create the database
sqlite3 employee.db
-- Now you're inside the sqlite shell, not bash. Press Ctrl+D on an empty line to quit.
-- Create a table:
create table names (ids integer primary key, name text, pay integer);
-- Show your tables
.tables
-- Insert some data
insert into names (name,pay) values('john', 10000);
insert into names (name,pay) values('mary', 20000);
insert into names (name,pay) values('sam', 30000);
-- Display your data
select * from names;
-- Show your databases
.databases
-- Quit
.quit

Test data

Eventually you'll write tests for your app with your test data, but during development a really handy website is [1]. You can get data in many formats including CSV which can be imported into sqlite like this:

• Get some data from generatedata.com
  • filename is names.txt
• Open database

sqlite3 employee.db

• Set your deliminator

.separator ","

• Import your data

.import names.txt names

• Display your data

select * from names;

• Quit

.quit

Using a pre-built DB file

If you want your app to come with some pre-built data in the database then it's probably easiest to follow the example above to create the file, add it to your resources, and copy it into the correct place the first time your app starts. For example:

try 
{
    String destPath = "/data/data/" + context.getPackageName() + "/databases/";
    
    File destPathFile =  new File(destPath);
    if (!destPathFile.exists())
        destPathFile.mkdirs();
    
    File destFile = new File(destPath + DB_FILE_NAME);
    if (!destFile.exists())
    {
        Log.d(TAG, "First run, copying default database");
        copyFile(context.getAssets().open(DB_FILE_NAME),
                 new FileOutputStream(destPath + "/" + DB_FILE_NAME));
    }
} 
catch (FileNotFoundException e) { e.printStackTrace(); } 
catch (IOException e) { e.printStackTrace(); }

dbOpenHelper = new DatabaseOpenHelper(context);

Make sure this code runs when your app starts up and don't forget to put your db file in the assets folder.