Difference between revisions of "MAP524/DPS924 Lecture 6"
(Created page with '= 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 n…') |
(→Command-line) |
||
Line 8: | Line 8: | ||
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. | 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. | ||
+ | |||
+ | <pre># 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</pre> | ||
+ | |||
+ | == Test data == | ||
+ | |||
+ | Eventually you'll write tests for your app with your test data, but during development a really handy website is [http://generatedata.com/]. 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 | ||
+ | <pre>sqlite3 employee.db</pre> | ||
+ | * Set your deliminator | ||
+ | <pre>.separator ","</pre> | ||
+ | * Import your data | ||
+ | <pre>.import names.txt names</pre> | ||
+ | * Display your data | ||
+ | <pre>select * from names;</pre> | ||
+ | * Quit | ||
+ | <pre>.quit</pre> |
Revision as of 16:02, 18 July 2015
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