SQLite in Android
1
What is a database?
•
relational database: A method of structuring data as
tables associated to each other by shared attributes.
•
a table row corresponds to a unit of data called a
record; a column corresponds to an attribute of that
record
•
relational databases typically use Structured Query
Language (SQL) to define, manage, and search
data
2
Why use a database?
•
powerful: can search, filter, combine data from many sources
•
fast: can search/filter a database very quickly compared to a
file
•
big: scale well up to very large data sizes
•
safe: built-in mechanisms for failure recovery (transactions)
•
multi-user: concurrency features let many users view/edit
data at same time
•
abstract: layer of abstraction between stored data and app(s)
common syntax: database programs use same SQL
commands
3
Relational database
•
A database is a set of tables
•
Each table has a primary key — a column with
unique values to identify a row
•
Tables can be related via foreign keys.
4
Some database
software
•
Oracle
• Microsoft
• SQLServer(powerful)
• Access(simple)
• PostgreSQL
– powerful/complex free open-source database system
• SQLite
– transportable, lightweight free open-source database system
• MySQL
–
simple free open-source database system
–
many servers run “LAMP” (Linux,Apache,MySQL,andPHP)
5
–Wikipedia is run on PHP and MySQL
•
6
Android includes
SQLite
SQLite is a library,
runs in the app’s process
7
Android Media
(Media Content Provider)
•
The Media provider contains meta data
for all available media on both internal
and external storage devices.
SQLite:
metadata:
•
file location
•
size
•
artist
•
albums
raw •
playlists
files •
…
8
The main table
files
A single table to represent all types of media
files: Each row can be an image, audio,
video, or playlist
_id _data _size title …
1 a.jpg 10000 a
2 b.bmp 20000 b
3 c.mp3 320000 c
4 d.avi 1231200 d
0
http://androidxref.com/4.4.3_r1.1/xref/packages/
providers/
MediaProvider/src/com/android/providers/media/
9
The main table
MediaProvider.java#1335
1
Other tables in Media
•
thumbnails,
•
artists,
•
albums,
•
audio_playlists_map (stores members of a playlist)
Rows: Fixed number of
columns Tables: Variable
number of rows
11
SQL
• Structured Query Language (SQL): a
language for searching and updating a
database
–
a standard syntax that is used by all database
software
(with minor incompatibilities)
–
generally case-insensitive
• a declarative language: describes what
data you are seeking, not exactly how
to find it
12
Basic SQL operations
• SELECT
• INSERT
• UPDATE
• DELETE
13
SELECT
• SELECT <list of columns> FROM <table>
WHERE <where clause>
[ORDER BY <column> [ASC or DESC]]
[LIMIT <number>];
•
e.g., SELECT * FROM files WHERE _id=3;
_id _data _size Title …
1 a.jpg 10000 A
2 b.bmp 20000 B
3 c.mp3 320000 C
4 d.avi 1231200 D
0
14
SELECT
• SELECT <list of columns> FROM <table>
WHERE <where clause>
[ORDER BY <column> [ASC or DESC]]
[LIMIT <number>];
•
SELECT _id, _data FROM files
•
SELECT * FROM files; (* means all columns)
• ORDER BY: sort the result by a column
• LIMIT: only get the first n rows in the result
15
INSERT
• INSERT INTO <table> (<list of columns>)
VALUES (<list of values>);
•
e.g., INSERT INTO files (data, size, title)
VALUES (“image0.jpg”, 102400,
“image0”);
_id _data _size title …
1 a.jpg 10000 A
2 b.bmp 20000 B
3 c.mp3 320000 C
4 d.avi 1231200 D
16
INSERT
0
5 image0.j 102400 image
pg 0
17
UPDATE
• UPDATE <table> SET
<column1> = <value1>,
<column2> = <value2>,
…
<columnn> = <valuen>
WHERE <where clause>;
18
UPDATE
• e.g., UPDATE files SET title=“profile”
WHERE _id=5;
_id _data _size Title …
1 a.jpg 10000 A
2 b.bmp 20000 B
3 c.mp3 320000 C
4 d.avi 1231200 D
0
5 image0.j 102400 Profile
pg
19
DELETE
• DELETE FROM <table>
WHERE <where clause>;
•
e.g., DELETE FROM files
WHERE _id=4;
_id _data _size title
1 a.jpg 10000 a
2 b.bmp 20000 b
3 c.mp3 320000 c
4 d.avi 12312000 d
5 image0.jp 102400 profile
20
Related data
thumbnail file
_id
_data
_size
_id
titile
_data
…
image_id
21
Related data
thumbnail
_id
_data
image_id
?
width
…
22
Foreign
keys
If thumbnails.image_id is declared to be a
foreign key of files._id,
SQLite will enforce Referential Integrity:
When a row in files is removed or its _id is
changed, SQLite can set the affected foreign
keys in thumbnails to NULL, or remove the
affected rows, etc.
23
Foreign
files table keys
_id _data _size title …
1 a.jpg 10000 a
2 b.bmp 20000 b
3 c.mp3 32000 c
0
5 image0.j 10240 profile
pg 0
_id _data image_id width …
1 1.thum 1 300
b
thumbnails table 3 5.thum 5 600
b
24
ON DELETE
files table
_id _data _size title …
1 a.jpg 10000 a
2 b.bmp 20000 b
3 c.mp3 32000 c
0
5 image0.j 10240 profile
pg 0
_id _data image_id width …
1 1.thum 1 300
b
thumbnails table 3 5.thum 5 600
b
25
ON DELETE
files table
_id _data _size title …
1 a.jpg 10000 a
2 b.bmp 20000 b
3 c.mp3 32000 c
0
5 image0.j 10240 profile
pg 0
_id _data image_id width …
1 1.thum NULL
1 300
b
thumbnails table 3 5.thum 5 600
b
26
Join — query multiple
related tables
• Inner join
• Outer join
If multiple tables have the same column
name, use <table>.<col> to
distinguish them
27
Inner
Join
• Inner join (JOIN) — only returns rows matching the
condition
•
SELECT … FROM files
JOIN thumbnails
ON files._id=thumbnails.image_id
WHERE …
•
Equivalent to
•
SELECT … FROM files, thumbnails
WHERe
files._id=thumbnails.image_id
28
Inner
AND (…)
Join
29
Inner
files
_id _data Join
_size title …
1 a.jpg 10000 a
2 b.bmp 20000 b
5 image0.j 10240 profil
pg 0 e
thumbnails
_id _data image_id width …
1 1.thum 1 300
b
3 5.thum 5 600
b
JOIN ON files._id=thumbnails.image_id
files._id title … thumbnails._id Widt …
30
Inner
1
5
a
profil
Join
1
3
h
300
600
e
31
Outer
Join
Left outer join (LEFT [OUTER] JOIN)
— returns all rows in the left table,
fill NULL to the right table if no
matching rows.
Right outer join — returns all rows in
the right table, fill NULL to the left
table if no matching rows. (not
supported by SQLite)
Full outer join — records from both
sides are included, fill NULL to “the
other table” if no match. (not
supported by SQLite)
32
Left Outer
Join
• Left outer join (LEFT [OUTER] JOIN) — returns all
rows in the left table, fill NULL to the right table if
no matching rows.
• SELECT … FROM files
LEFT OUTER JOIN thumbnails
ON
files._id=thumbnails.image_id
WHERE …
33
Left Outer
files
_id Join
_data _size title …
1 a.jpg 10000 a
2 b.bmp 20000 b
5 image0.j 10240 profil
pg 0 e
thumbnails
_id _data image_id width …
1 1.thum 1 300
b
3 5.thum 5 600
b
JOIN ON files._id=thumbnails.image_id
files._id title … thumbnails._id width …
1 a 1 300
34
Left Outer
2
5 Join
b
profil
e
NULL
3
NULL
600
35
View
s
A view is a virtual table based on other tables or
views
CREATE VIEW <view name> AS
SELECT ….;
_id _data _size title type …
1 a.jpg 10000 a imag
e
2 b.bmp 20000 b imag
e
3 c.mp3 32000 c audio
0
5 image0.jp 10240 profil imag
g 0 e e
36
View
s _id
1
2
_data
a.jpg
b.bmp
_size
10000
20000
title
a
b
…
5 image0.jp 10240 profil
g 0 e
37
Views in
<view>
audio_met
a
<table
>
images files
select only images
audio
albums video
38
Views in
CREATE VIEW audio_meta AS
SELECT _id, <audio-related columns>,
FROM files
WHERE media_type =<MEDIA_TYPE_AUDIO>;
CREATE VIEW IF NOT EXISTS audio AS
SELECT * FROM audio_meta
LEFT OUTER JOIN artists ON
audio_meta.artist_id=artists.artist_id
LEFT OUTER JOIN albums ON
audio_meta.album_id=albums.album_id;
39
Android
A class to use SQLite.
SQLiteDatabase db = openOrCreateDatabase( "name",
MODE_PRIVATE, null);
db.execSQL("SQL query");
40
Android
It helps you to generate SQL statements.
query (SELECT), delete, insert, update
db.beginTransaction(), db.endTransaction()
db.delete("table", "whereClause",args)
db.deleteDatabase(file)
db.insert("table", null, values)
db.query(...)
db.rawQuery("SQLquery", args)
db.replace("table", null, values)
db.update("table", values, "whereClause", args)
41
Avoid using
user-provided
SQL injection:
• statement =
"SELECT * FROM users WHERE name =\'" +
userName + "\';"
• If the user provides userName = "' OR
'1'='1 " Statement becomes:
•
SELECT * FROM users
WHERE name =‘’ OR
‘1’=‘1’;
42
Avoid using
user-provided
— always true.
43
Avoid using
user-provided
Use ContentValues and arguments for user-
provided
input.
44
ContentValues
ContentValues cvalues = new
ContentValues();
cvalues.put("columnName1", value1);
cvalues.put("columnName2", value2);
...
db.insert("tableName", null, cvalues);
• ContentValues can be optionally used as a
level of abstraction for statements like
INSERT, UPDATE, REPLACE
45
Compare to raw
statements…
–
Contrast with:
db.execSQL("INSERT INTO tableName ("
+ columnName1 + ", " + columnName2
+ ") VALUES (" + value1 + ", " + value2 + ")");
ContentValues allows you to use cleaner Java
syntax rather than raw SQL syntax for some
common operations.
46
Arguments
query(String table, String[]
columns, String selection, String[]
selectionArgs,
String groupBy, String having, String orderBy)
•
selection: a where clause that can contain “?”
•
type=? and date=?
•
selectionArgs:
•
[“image”, “10/1/2016”]
47
Cursor: result of a
query
Cursor lets you iterate through row results one at a time
———
Cursor cursor = db.rawQuery("SELECT * FROM students");
cursor.moveToFirst();
do {
int id =
cursor.getInt(cursor.getColumnIndex("id")); String
email = cursor.getString(
cursor.getColumnIndex("email"));
...
48
} while
(cursor.moveToNext());
cursor.close();
49
