SQLite

tenderlaSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

78 views

SQLite

Command line sqlite3


The command line sqlite3 is not installed on all devices


To install, get sqlite3 from web page, and


$
adb

push sqlite3 /
sdcard
/

$
adb

shell

$
su

# mount
-
o
remount,rw

-
t yaffs2 /dev/block/mtdblock3
/system

#
dd

if=/
sdcard
/sqlite3 of=/system/bin/sqlite3

#
chmod

4755 /system/bin/sqlite3

# mount
-
o
remount,ro

-
t yaffs2 /dev/block/mtdblock3
/system


Found Things


Record a thing (e.g., audio, picture, text,
video, web link, etc.) at a location


When someone is near the location, perhaps
play the thing.


Allow things that are played to be selected in
a sophisticate way


Include various information about the things


Cache things and get things from the web


Things database

id

type

filename

File

state

lat

long

Recorded
date

Recorded

time

author

score

Last played

Tag

1

Tag 2

1

pic

1.jpeg

here

75..

36..

5/2/2010

1:01

stephan

100

5/3/2010

tree

bird

Look in database and get thing from nearby and that matched other criteria


For now, we will just use near by lat and long

SQLite


SQLite

is a scaled down version of SQL


Data is in tables with rows and columns


Each row is a data entry.


SQL allows data to be stored and retrieved


Basic things (see
http://www.w3schools.com/sql/default.asp


Create database


Create table in the database


Insert data into the table


Get data from table (or tables), via query


There’s other stuff, but this is enough for us

Create database and table


SQLiteDatabase

myDatabase
;


myDatabase

=
openOrCreateDatabase
( “/
mnt
/
sdcard
/
things.db
",
SQLiteDatabase.CREATE_IF_NECESSARY
, null);


Makes a database called
things.db


If no path, then db is in /data/data
/<
appname
>/
databases


here we use a specific path so we can examine the db at the command line


Is database already exists, it does not make it



Make table


Must specify the column name and type (e.g., integer)


Also, there must be a primary column (a key) such that each entry in this column is
unique


One way to do this is to include a column that is an integer that
autoincrements




myDatabase.execSQL
("CREATE TABLE IF NOT EXISTS things" +


"( id INTEGER PRIMARY KEY AUTOINCREMENT," +


" type VARCHAR(40), " +


" filename VARCHAR(40), " +


" lat INTEGER, " +


" long INTEGER);");



Name of table

Key is named id and is integer

Type is audio,
pic
,
tex
, etc.

Filename of the thing

Location of the thing

If “IF NOT EXIST” is
missing, then this will crash
if the table already exists

Try it


Make function
createDatabase


Call
createDatabase

from
onCreate


Run
adb


Change to the databases directory


ls


Run sqlite3
things.db

“.dump”

Add data

long
newId

=
myDatabase.insert
("things", null, values);

Name of table

Set of pairs (column name, data for that column)

Id of entry

ContentValues

values = new
ContentValues
();


values.put
("type", "audio");


values.put
("filename", "1.mp4");


values.put
("lat","
75001200");


values.put
("long","
36000000
");

Try it


Make function
insertData
()

ContentValues

values = new
ContentValues
();


values.put
("type", "audio");


values.put
("filename", "1.mp4");


values.put
("lat","75001200");


values.put
("long","36000000");

long
newId

=
myDatabase.insert
("things", null, values);

Log.d
(“
SQLPlay”,”insert

returned id “+
newId
);



Run


adb

sqllite3
things.db

“.dump”


Add three buttons


Record


Stop


Play


Allow record audio, location, internet, camera

Next filename


We will save the recorded data with name id.mp4, where id is
the id from the database


So, to determine the filename, we need to get the next id


Or, insert a partial row, get the id and update the row

id

type

filename

File

state

lat

long

Recorded
date

Recorded

time

author

score

Last played

Tag

1

Tag 2

1

audio

1.mp4

here

75..

36..

5/2/2010

1:01

stephan

100

5/3/2010

tree

bird

2

audio

null

here

76

24

5/3/2010

2:02

stephan

99

5/4/2010

sing

song

id

type

filename

File

state

lat

long

Recorded
date

Recorded

time

author

score

Last played

Tag

1

Tag 2

1

audio

1.mp4

here

75..

36..

5/2/2010

1:01

stephan

100

5/3/2010

tree

bird

2

audio

2.mp4

here

76

24

5/3/2010

2:02

stephan

99

5/4/2010

sing

song


ContentValues

values = new
ContentValues
();


values.put
("type", "audio
");


values.put
("
lat“, 36000000);


values.put
("
long“,74000000);


long
newId

=
myDatabase.insert
("things", null, values);


Log.d
(“
SQLPlay","
newId
="+
newId
);


String
filename =
newId
+".mp4
";



ContentValues

values2 = new
ContentValues
();


values2.put("filename",
Long.toString
(
newId
)+".mp4
");




String
wheres
[] = new String[1];


wheres
[0] =
Long.toString
(
newId
);


myDatabase.update
("things", values2, "id=?",
wheres
);

Make a new column with the filename empty (null)

Make file name

Make filename column (value pairs like insert)

update

location


From previous lectures on location


Add class
attribtues


LocationManager

locationManager
;


String provider
;


Make
setUpLocation

and call from
onCreate
, after
createDatabase




public void
setUpLocation
() {


Criteria
criteria

= new Criteria();


criteria.setAccuracy
(
Criteria.ACCURACY_FINE
);


criteria.setPowerRequirement
(
Criteria.NO_REQUIREMENT
);



locationManager

= (
LocationManager
)
getSystemService
(
Context.LOCATION_SERVICE
);


provider =
locationManager.getBestProvider
(criteria, true);


Location
location

=
locationManager.getLastKnownLocation
(provider);


String
locInfo

=
String.format
("Initial loc = (%f, %f) @ (%f meters up)",
location.getLatitude
(),
location.getLongitude
(),
location.getAltitude
() );


Log.d
("
SQLPlay","starting

loc: "+
locInfo
);




}

Get location

Location when not moving/indoors


Get
gps

lock since last reboot. That way, .
getLastKnownLocation

doesn’t crash


Add a random number to location


Location
location

=
locationManager.getLastKnownLocation
(provider);


Random generator = new Random();


int

random1 =
generator.nextInt
( 1000000 );


int

random2 =
generator.nextInt
( 1000000 );


int

latitude = (
int
)(
location.getLatitude
()*1E6+random1);


int

longitude = (
int
)(
location.getLongitude
()*1E6+random2);


Put this into
getNextfileName

function


rm

things.db


Run and make a few files. Dump database

Record sound


From previous project, cut record sound and
paste into
recordSound

function


Cut and paste
stopRecording

into
stopRecordingButton.onClick


Make buttons nice



Run on device


Make a few entries


adb

-
d pull /data/data/
com.SQLPlay
/files c:
\
audio

adb

-
d pull /data/data/
com.SQLPlay
/databases c:
\
audio

Play sound


Get location (done)


Get database entry that is nearest to current
location


Android query


SQL SELECT


Table = “things”


Columns: array of the columns to be returned


E.g., null, returns all columns


E.g., String columns[] = “filename”; returns the filename


{selection
selectionArgs
} is also know as WHERE (in SQL, one types …. WHERE
~ {selection
selectionArgs
}


E.g., selection = “type=?”; String
selectionArgs
[] = {“audio”}


E.g., selection = “lat<?”; String
selectionArgs
[] = {“90000000”}


E.g., selection = “lat<? AND type=?”; String
selectionArgs
[] = {“90000000”, “audio”}



Groupby

and having are for grouping (we set as null)


Orderby

is to order the replies (we set as null)


Limit controls how many to return (we set as null)


If
orderBy

is used, then limit can be used to get the first or first few


E.g.,
orderBy

= “cost”; limit = “1”; returns the row with the highest cost

public Cursor query (String table, String[] columns, String selection, String[]
selectionArgs
, String
groupBy
, String having, String
orderBy
, String limit)

Cursor c =
myDatabase.query

(“things”, null , null, null, null, null, null, null);

cursor


Cursor is used to get the results, which might be
emtpy

or have several
rows, where each row has one or more columns


You can scroll through the cursor


Also, you should close the cursor when done


Perhaps use
startManagingCursor
(c);


c.moveToFirst
();


while (
c.isAfterLast
() == false)


{



String row = new String();



for (
int

i
=0;
i
<
c.getColumnCount
();
i
++)




row +=
c.getString
(
i
) + " ";



Log.d
("
SQLPlay",row
);



c.moveToNext
();


}


c.close
();

Try it


Make function
playAudio

and call this function from
playButton.onClick


Make function,
getfilename


In
playAudio
, call
getFilename


In
getFilename
, put

Cursor c =
myDatabase.query

(“things”, null , null, null, null, null, null, null);


c.moveToFirst
();


while (
c.isAfterLast
() == false)


{



String row = new String();



for (
int

i
=0;
i
<
c.getColumnCount
();
i
++)



row +=
c.getString
(
i
) + " ";



Log.d
("
SQLPlay",row
);



c.moveToNext
();


}


c.close
();

Run, select play, and look at log.

String selection = “lat<?”

String
selectionArgs
[] = {“90000000”};

Try other selections

String columns[] = “max(lat)”

Everything else null

String selection = “lat BETWEEN ? AND ?”

String
selectionArgs
[] = {“0”, “90000000”};

String selection = “lat IN ”

String
selectionArgs
[] = {“
somevalue
”};

Exact match

(maybe
somevalue

can be a set?)

String columns[] = “min(abs(lat
-
70000000))”

Everything else null

nearest


But how to get a nearest point?


I don’t think it is possible with this type of query



rawQuery


Same as
SQLite

queries.


Android’s query is easy, but very limited.
rawQuery

gives the full power of
SQLite


If you know SQL, then you may as well use
rawQuery

SQL SELECT


SELECT
column_name
(s)


FROM
table_name


WHERE
column_name

operator value



SELECT * FROM things WHERE lat < 90000000


Try in sqlite3


In shell, # sqlite3
things.db

“SELECT * from things” ….


Smallest lat


SELECT * FROM things WHERE lat in (SELECT min(lat) )


Nearest lat to 4000000


SELECT * FROM things WHERE abs(lat
-
40000000) in (SELECT
min(abs(lat
-
40000000) ) );



Nearest to La, Lo


SELECT * FROM things WHERE abs(lat
-

La)+abs(long
-

Lo) in
(SELECT min(abs(lat
-

La)+abs(long
-

Lo) ) );


Don’t forget spaces,
-
Lo is an invalid string is Lo<0



Back to code


Cursor c =
myDatabase.rawQuery
(…);



String la = latitude;


String lo = longitude;


String ex = “abs(lat


la) + abs(long


lo)”;


String Q = “SELECT * FROM things WHERE ” +
ex + “ IN ( SELECT min( “ + ex + “) FROM
things) ; “;


Cursor c =
myDatabase.rawQuery
(Q, []);


File name



String Q = “SELECT filename FROM things
WHERE ” + ex + “ IN ( SELECT min( “ + ex + “)
FROM things) ; “;


Cursor c =
myDatabase.rawQuery
(Q, []);


String filename =
c.getString
(0);


Log.d
(“
SQLPlay”,”filename
:”+filename);


Return filename;


Try it


in
getFilename

function


Location
location

=
locationManager.getLastKnownLocation
(provider);


Random generator = new Random();


int

random1 =
generator.nextInt
( 1000000 );


int

random2 =
generator.nextInt
( 1000000 );


int

latitude = (
int
)(
location.getLatitude
()*1E6+random1);


int

longitude = (
int
)(
location.getLongitude
()*1E6+random2);

String la = latitude;

String lo = longitude;

String ex = “abs(lat


la) + abs(long


lo)”;

String Q = “SELECT filename FROM things WHERE ” + ex + “ IN ( SELECT min( “ + ex + “) FROM things) ; “;

Cursor c =
myDatabase.rawQuery
(Q, []);

String filename =
c.getString
(0);

Log.d
(“
SQLPlay”,”filename
:”+filename);

r
eturn filename;

finally


In play, paste code to play audio from previous
lecture