Transcription
SQLite in Android1
What is a database? relational database: A method of structuring data astables 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 QueryLanguage (SQL) to define, manage, and search data2
Why use a database? powerful: can search, filter, combine data from many sources fast: can search/filter a database very quickly compared to afilebig: scale well up to very large data sizes safe: built-in mechanisms for failure recovery (transactions) multi-user: concurrency features let many users view/editdata at same time abstract: layer of abstraction between stored data and app(s)common syntax: database programs use same SQLcommands3
Relational database A database is a set of tables Each table has a primary key — a column with uniquevalues to identify a row Tables can be related via foreign keys.4
Some database software Oracle Microsoft SQLServer(powerful)Access(simple)PostgreSQL– powerful/complex SQLite– transportable, free open-source database systemlightweight free open-source database systemMySQL– simplefree open-source database system– many servers run “LAMP” (Linux,Apache,MySQL,andPHP)–Wikipediais run on PHP and MySQL 5
Android includes SQLiteSQLite is a library,runs in the app’s process6
Android Media Manager(Media Content Provider) The Media provider contains meta data for allavailable media on both internal and externalstorage devices.SQLite:metadata: file location size artist albumsraw files playlists 7
The main table in Media:filesA single table to represent all types of media files:Each row can be an image, audio, video, or 0032000012312000titleabcd http://androidxref.com/4.4.3 /android/providers/media/MediaProvider.java#13358
Other tables in Media thumbnails,artists,albums,audio playlists map (stores members of a playlist)Rows: Fixed number of columnsTables: Variable number of rows9
SQL Structured Query Language (SQL): a languagefor searching and updating a database–astandard syntax that is used by all database software(with minor incompatibilities)– generally case-insensitivea declarative language: describes what datayou are seeking, not exactly how to find it10
Basic SQL operations SELECT INSERT UPDATE DELETE11
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 001231200012titleabcd
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 result13
INSERT INSERT INTO table ( list of columns )VALUES ( list of values ); e.g., INSERT INTO files (data, size, title)VALUES (“image0.jpg”, 102400, image0
UPDATE UPDATE table SET column1 value1 , column2 value2 , columnn valuen WHERE where clause ;15
UPDATE e.g., UPDATE files SET title “profile”WHERE id 00200003200001231200010240016titleabcdprofile
DELETE DELETE FROM table WHERE where clause ; e.g., DELETE FROM filesWHERE id 00200003200001231200010240017titleabcdprofile
Related data across tablesthumbnailiddatafileimage ididwidthdata sizetitile 18
Related data across tablesthumbnailiddata?image idwidth 19
Foreign keysIf thumbnails.image id is declared to be aforeign key of files. id,SQLite will enforce Referential Integrity:When a row in files is removed or its id ischanged, SQLite can set the affected foreign keysin thumbnails to NULL, or remove the affectedrows, etc.20
Foreign keysfiles tableid1235dataa.jpgb.bmpc.mp3image0.jpgthumbnails 21 data image id1.thumb15.thumb5width300600
ON DELETE CASCADEfiles tableid1235dataa.jpgb.bmpc.mp3image0.jpgthumbnails 22 data image id1.thumb15.thumb5width300600
ON DELETE SET NULLfiles tableid1235dataa.jpgb.bmpc.mp3image0.jpgthumbnails 23 data image id1.thumb NULL15.thumb5width300600
Join — query multiplerelated tables Inner join Outer joinIf multiple tables have the same column name,use table . col to distinguish them24
Inner Join Inner join (JOIN) — only returns rows matching the condition SELECT FROM filesJOIN thumbnailsON files. id thumbnails.image idWHERE Equivalent to SELECT FROM files, thumbnailsWHERe files. id thumbnails.image idAND ( )25
Inner pg 102400titleabprofile thumbnailsid13data image id1.thumb15.thumb5width300600 JOIN ON files. id thumbnails.image idfiles. id title1a5profile thumbnails. id width1300360026
Outer JoinLeft outer join (LEFT [OUTER] JOIN) —returns all rows in the left table, fill NULLto the right table if no matching rows.Right outer join — returns all rows in theright table, fill NULL to the left table if nomatching rows. (not supported by SQLite)Full outer join — records from both sidesare included, fill NULL to “the other table”if no match. (not supported by SQLite)27
Left Outer Join Left outer join (LEFT [OUTER] JOIN) — returns allrows in the left table, fill NULL to the right table if nomatching rows. SELECT FROM filesLEFT OUTER JOIN thumbnailsON files. id thumbnails.image idWHERE 28
filesid125Left Outer Joindatasizea.jpg10000b.bmp20000image0.jpg 102400titleabprofile thumbnailsid13data image id1.thumb15.thumb5width300600 JOIN ON files. id thumbnails.image idfiles. id title1a2b5profile thumbnails. id width1300NULLNULL360029
ViewsA view is a virtual table based on other tables or viewsCREATE VIEW view name ASSELECT ageimageaudioimage 102400profile30
Views in Media Provider view table audiovideoimagesaudio metaartistsfiles31albums
Views in Media ProviderCREATE VIEW audio meta ASSELECT id, audio-related columns ,FROM filesWHERE media type MEDIA TYPE AUDIO ;CREATE VIEW IF NOT EXISTS audio ASSELECT * FROM audio metaLEFT OUTER JOIN artists ONaudio meta.artist id artists.artist idLEFT OUTER JOIN albums ONaudio meta.album id albums.album id;32
Android SQLiteDatabaseA class to use SQLite.SQLiteDatabase db openOrCreateDatabase( "name",MODE PRIVATE, null);db.execSQL("SQL query");33
Android SQLiteDatabaseIt helps you to generate SQL statements.query (SELECT), delete, insert, updatedb.beginTransaction(), db.endTransaction()db.delete("table", t("table", null, values)db.query(.)db.rawQuery("SQLquery", args)db.replace("table", null, values)db.update("table", values, "whereClause", args)34
Avoid using user-providedinput as part of a raw querySQL injection: statement "SELECT * FROM users WHERE name \'" userName "\';" If the user provides userName " ' OR '1' '1 "Statement becomes: SELECT * FROM usersWHERE name ‘’ OR ‘1’ ‘1’;— always true.35
Avoid using user-providedinput as part of a raw queryUse ContentValues and arguments for user-providedinput.36
ContentValuesContentValues cvalues new ContentValues();cvalues.put("columnName1", value1);cvalues.put("columnName2", value2);.db.insert("tableName", null, cvalues); ContentValues can be optionally used as a level ofabstraction for statements like INSERT, UPDATE,REPLACE37
Compare to rawstatements – Contrastwith:db.execSQL("INSERT INTO tableName (" columnName1 ", " columnName2 ") VALUES (" value1 ", " value2 ")");ContentValues allows you to use cleaner Java syntaxrather than raw SQL syntax for some common operations.38
Argumentsquery(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”]39
Cursor: result of a queryCursor 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 } while (cursor.moveToNext());cursor.close();40
Android SQLiteDatabase 34 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)
