SQLite Command line sqlite 3 The command line

  • Slides: 27
Download presentation
SQLite

SQLite

Command line sqlite 3 • The command line sqlite 3 is not installed on

Command line sqlite 3 • The command line sqlite 3 is not installed on all devices • To install, get sqlite 3 from web page, and • $ adb push sqlite 3 /sdcard/ $ adb shell $ su # mount -o remount, rw -t yaffs 2 /dev/block/mtdblock 3 /system # dd if=/sdcard/sqlite 3 of=/system/bin/sqlite 3 # chmod 4755 /system/bin/sqlite 3 # mount -o remount, ro -t yaffs 2 /dev/block/mtdblock 3 /system

Found Things • Record a thing (e. g. , audio, picture, text, video, web

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

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

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. w 3 schools. 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 • • SQLite. Database my. Database; my. Database = open.

Create database and table • • SQLite. Database my. Database; my. Database = open. Or. Create. Database( “/mnt/sdcard/things. db", SQLite. Database. CREATE_IF_NECESSARY, null); – – • Makes a database called things. db If no path, then db is in /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 If “IF NOT EXIST” is missing, then this will crash if the table already exists Name of table my. Database. exec. SQL("CREATE TABLE IF NOT EXISTS things" + "( id INTEGER PRIMARY KEY AUTOINCREMENT, " + " type VARCHAR(40), " + " filename VARCHAR(40), " + " lat INTEGER, " + " long INTEGER); "); Key is named id and is integer Type is audio, pic, tex, etc. Filename of the thing Location of the thing

Try it • • • Make function create. Database Call create. Database from on.

Try it • • • Make function create. Database Call create. Database from on. Create Run adb Change to the databases directory ls Run sqlite 3 things. db “. dump”

Add data Id of entry Name of table long new. Id = my. Database.

Add data Id of entry Name of table long new. Id = my. Database. insert("things", null, values); Set of pairs (column name, data for that column) Content. Values values = new Content. Values(); values. put("type", "audio"); values. put("filename", "1. mp 4"); values. put("lat", "75001200"); values. put("long", "36000000");

Try it • Make function insert. Data() Content. Values values = new Content. Values();

Try it • Make function insert. Data() Content. Values values = new Content. Values(); values. put("type", "audio"); values. put("filename", "1. mp 4"); values. put("lat", "75001200"); values. put("long", "36000000"); long new. Id = my. Database. insert("things", null, values); Log. d(“SQLPlay”, ”insert returned id “+new. Id); • Run • adb sqllite 3 things. db “. dump”

 • Add three buttons – Record – Stop – Play • Allow record

• Add three buttons – Record – Stop – Play • Allow record audio, location, internet, camera

Next filename • We will save the recorded data with name id. mp 4,

Next filename • We will save the recorded data with name id. mp 4, 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. mp 4 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. mp 4 here 75. . 36. . 5/2/2010 1: 01 stephan 100 5/3/2010 tree bird 2 audio 2. mp 4 here 76 24 5/3/2010 2: 02 stephan 99 5/4/2010 sing song

Content. Values values = new Content. Values(); values. put("type", "audio"); values. put("lat“, 36000000); values.

Content. Values values = new Content. Values(); values. put("type", "audio"); values. put("lat“, 36000000); values. put("long“, 74000000); long new. Id = my. Database. insert("things", null, values); Log. d(“SQLPlay", "new. Id="+new. Id); String filename = new. Id+". mp 4"; Content. Values values 2 = new Content. Values(); values 2. put("filename", Long. to. String(new. Id)+". mp 4"); String wheres[] = new String[1]; wheres[0] = Long. to. String(new. Id); my. Database. update("things", values 2, "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 – Location. Manager

location • From previous lectures on location • Add class attribtues – Location. Manager location. Manager; – String provider; • Make set. Up. Location and call from on. Create, after create. Database public void set. Up. Location() { Criteria criteria = new Criteria(); criteria. set. Accuracy(Criteria. ACCURACY_FINE); criteria. set. Power. Requirement(Criteria. NO_REQUIREMENT); location. Manager = (Location. Manager)get. System. Service(Context. LOCATION_SERVICE); provider = location. Manager. get. Best. Provider(criteria, true); Location location = location. Manager. get. Last. Known. Location(provider); String loc. Info = String. format("Initial loc = (%f, %f) @ (%f meters up)", location. get. Latitude(), location. get. Longitude(), location. get. Altitude() ); Log. d("SQLPlay", "starting loc: "+loc. Info); } Get location

Location when not moving/indoors • Get gps lock since last reboot. That way, .

Location when not moving/indoors • Get gps lock since last reboot. That way, . get. Last. Known. Location doesn’t crash • Add a random number to location Location location = location. Manager. get. Last. Known. Location(provider); Random generator = new Random(); int random 1 = generator. next. Int( 1000000 ); int random 2 = generator. next. Int( 1000000 ); int latitude = (int)(location. get. Latitude()*1 E 6+random 1); int longitude = (int)(location. get. Longitude()*1 E 6+random 2); • Put this into get. Nextfile. Name function • rm things. db • Run and make a few files. Dump database

Record sound • From previous project, cut record sound and paste into record. Sound

Record sound • From previous project, cut record sound and paste into record. Sound function • Cut and paste stop. Recording into stop. Recording. Button. Click – Make buttons nice • Run on device • Make a few entries adb -d pull /data/com. SQLPlay/files c: audio adb -d pull /data/com. SQLPlay/databases c: audio

Play sound • Get location (done) • Get database entry that is nearest to

Play sound • Get location (done) • Get database entry that is nearest to current location – Android query – SQL SELECT

public Cursor query (String table, String[] columns, String selection, String[] selection. Args, String group.

public Cursor query (String table, String[] columns, String selection, String[] selection. Args, String group. By, String having, String order. By, String limit) • • 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. Args} is also know as WHERE (in SQL, one types …. WHERE ~ {selection. Args} – E. g. , selection = “type=? ”; String selection. Args[] = {“audio”} – E. g. , selection = “lat<? ”; String selection. Args[] = {“ 90000000”} – E. g. , selection = “lat<? AND type=? ”; String selection. Args[] = {“ 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 order. By is used, then limit can be used to get the first or first few – E. g. , order. By = “cost”; limit = “ 1”; returns the row with the highest cost Cursor c = my. Database. query (“things”, null, null, null);

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

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 start. Managing. Cursor(c); c. move. To. First(); while (c. is. After. Last() == false) { String row = new String(); for (int i=0; i<c. get. Column. Count(); i++) row += c. get. String(i) + " "; Log. d("SQLPlay", row); c. move. To. Next(); } c. close();

 • • Try it Make function play. Audio and call this function from

• • Try it Make function play. Audio and call this function from play. Button. Click Make function, getfilename In play. Audio, call get. Filename In get. Filename, put Cursor c = my. Database. query (“things”, null, null, null); c. move. To. First(); while (c. is. After. Last() == false) { String row = new String(); for (int i=0; i<c. get. Column. Count(); i++) row += c. get. String(i) + " "; Log. d("SQLPlay", row); c. move. To. Next(); } c. close(); Run, select play, and look at log.

Try other selections String selection = “lat<? ” String selection. Args[] = {“ 90000000”};

Try other selections String selection = “lat<? ” String selection. Args[] = {“ 90000000”}; String selection = “lat BETWEEN ? AND ? ” String selection. Args[] = {“ 0”, “ 90000000”}; String selection = “lat IN ” String selection. Args[] = {“somevalue”}; String columns[] = “max(lat)” Everything else null String columns[] = “min(abs(lat-70000000))” Everything else null Exact match (maybe somevalue can be a set? )

nearest • But how to get a nearest point? – I don’t think it

nearest • But how to get a nearest point? – I don’t think it is possible with this type of query • raw. Query – Same as SQLite queries. – Android’s query is easy, but very limited. raw. Query gives the full power of SQLite – If you know SQL, then you may as well use raw. Query

SQL SELECT • SELECT column_name(s) • FROM table_name • WHERE column_name operator value •

SQL SELECT • SELECT column_name(s) • FROM table_name • WHERE column_name operator value • SELECT * FROM things WHERE lat < 90000000 • Try in sqlite 3 • In shell, # sqlite 3 things. db “SELECT * from things” ….

 • Smallest lat – SELECT * FROM things WHERE lat in (SELECT min(lat)

• 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 = my. Database. raw. Query(…); String la =

Back to code • Cursor c = my. Database. raw. Query(…); 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 = my. Database. raw. Query(Q, []); • •

File name • String Q = “SELECT filename FROM things WHERE ” + ex

File name • String Q = “SELECT filename FROM things WHERE ” + ex + “ IN ( SELECT min( “ + ex + “) FROM things) ; “; • Cursor c = my. Database. raw. Query(Q, []); • String filename = c. get. String(0); • Log. d(“SQLPlay”, ”filename: ”+filename); • Return filename;

Try it • in get. Filename function Location location = location. Manager. get. Last.

Try it • in get. Filename function Location location = location. Manager. get. Last. Known. Location(provider); Random generator = new Random(); int random 1 = generator. next. Int( 1000000 ); int random 2 = generator. next. Int( 1000000 ); int latitude = (int)(location. get. Latitude()*1 E 6+random 1); int longitude = (int)(location. get. Longitude()*1 E 6+random 2); 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 = my. Database. raw. Query(Q, []); String filename = c. get. String(0); Log. d(“SQLPlay”, ”filename: ”+filename); return filename;

finally • In play, paste code to play audio from previous lecture

finally • In play, paste code to play audio from previous lecture