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