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 [Link] 10000 a
2 [Link] 20000 b
3 c.mp3 320000 c
4 [Link] 1231200 d
0
[Link]
providers/
MediaProvider/src/com/android/providers/media/
9
The main table
[Link]#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 [Link] 10000 A
2 [Link] 20000 B
3 c.mp3 320000 C
4 [Link] 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 (“[Link]”, 102400,
“image0”);
_id _data _size title …
1 [Link] 10000 A
2 [Link] 20000 B
3 c.mp3 320000 C
4 [Link] 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 [Link] 10000 A
2 [Link] 20000 B
3 c.mp3 320000 C
4 [Link] 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 [Link] 10000 a
2 [Link] 20000 b
3 c.mp3 320000 c
4 [Link] 12312000 d
5 [Link] 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 [Link] 10000 a
2 [Link] 20000 b
3 c.mp3 32000 c
0
5 image0.j 10240 profile
pg 0
_id _data image_id width …
1 [Link] 1 300
b
thumbnails table 3 [Link] 5 600
b
24
ON DELETE
files table
_id _data _size title …
1 [Link] 10000 a
2 [Link] 20000 b
3 c.mp3 32000 c
0
5 image0.j 10240 profile
pg 0
_id _data image_id width …
1 [Link] 1 300
b
thumbnails table 3 [Link] 5 600
b
25
ON DELETE
files table
_id _data _size title …
1 [Link] 10000 a
2 [Link] 20000 b
3 c.mp3 32000 c
0
5 image0.j 10240 profile
pg 0
_id _data image_id width …
1 [Link] NULL
1 300
b
thumbnails table 3 [Link] 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 [Link] 10000 a
2 [Link] 20000 b
5 image0.j 10240 profil
pg 0 e
thumbnails
_id _data image_id width …
1 [Link] 1 300
b
3 [Link] 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 [Link] 10000 a
2 [Link] 20000 b
5 image0.j 10240 profil
pg 0 e
thumbnails
_id _data image_id width …
1 [Link] 1 300
b
3 [Link] 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 [Link] 10000 a imag
e
2 [Link] 20000 b imag
e
3 c.mp3 32000 c audio
0
5 [Link] 10240 profil imag
g 0 e e
36
View
s _id
1
2
_data
[Link]
[Link]
_size
10000
20000
title
a
b
…
5 [Link] 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);
[Link]("SQL query");
40
Android
It helps you to generate SQL statements.
query (SELECT), delete, insert, update
[Link](), [Link]()
[Link]("table", "whereClause",args)
[Link](file)
[Link]("table", null, values)
[Link](...)
[Link]("SQLquery", args)
[Link]("table", null, values)
[Link]("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();
[Link]("columnName1", value1);
[Link]("columnName2", value2);
...
[Link]("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:
[Link]("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 = [Link]("SELECT * FROM students");
[Link]();
do {
int id =
[Link]([Link]("id")); String
email = [Link](
[Link]("email"));
...
48
} while
([Link]());
[Link]();
49
