Persisting Data on Android with SQLite & Content Provider

AnDevCon talk on 12/03/2015

Have you been avoiding SQLite DB and Content Provider because they look complicated? This class will teach you how to persist data with SQLite Database, why write a Content Provider, as well as how to access a Content Provider with a CursorLoader. From the class you will learn:

• How to create the classes to define your database schema, tables and columns
• How to exam your database schema and tables, query the data with SQLite3 (command line) and SQLite Browser (GUI).
• Concepts related to the Content Provider such as Content Authority, Uri, Content Type, and ContentResolver etc.
• How to write your own Content Provider and access a Content Provider
• Some libraries that make it easier to work with SQLite and Content Provider

Link to talk recording: https://youtu.be/9Ias8Tp-yGI

Transcript

1. Persisting Data on Android - with SQLite & Content Provider @margaretmz +MargaretMaynardReid by Margaret Maynard-Reid Dec. 3, 2015
2. About me • Android Developer @General UI • Android Instructor @University of Washington • Co-organizer & Women Techmakers Lead of GDG Seattle • Co-organizer of Seattle (Eastside) Android 2
3. SQLite Db Content Provider query() insert() update() delete() Android UI (Activity/Fragment) CursorLoader ContentResolver • SQLite ◦ Create a Db ◦ Inspect a Db (tools) ◦ Access a Db • Content Provider ◦ Write your own ◦ Access a Provider • Open source libraries ◦ Less boilerplate ◦ Less tedious Topics Covered 3
4. SQLite Databases Create a database
5. How to Create a SQLite Db? First plan your database schema! 1. Define a schema class 2. Extend SQLiteOpenHelper a. override onCreate() to create the Db b. override onUpgrade() to handle new Db version 3. The database gets created 5
6. Step 0. Plan Database Schema Sample app has a simple database with just one table (“version”) that keeps track of Android versions with a few columns. 6
7. Step 1. Define a Schema Class A static class to maintain database schema • Often times named SomeContract.java (if all tables in one class) • Contains constants to define ◦ table name(s) ◦ columns ◦ projection - String array with the columns you want in query result public class VersionContract implements BaseColumns { ... /** * Define the Version table */ public static final class Version { // Define table name public static final String TABLE_NAME = "version"; // Define table columns public static final String ID = BaseColumns._ID; public static final String CODE_NAME = "code_name"; public static final String VERSION_NO = "version_no"; public static final String API_LEVEL = "api_level"; public static final String RELEASE_DATE = "release_date"; public static final String FEATURES = "features"; // Define projection for Version table public static final String[] PROJECTION = new String[] { /*0*/ VersionContract.Version.ID, /*1*/ VersionContract.Version.CODE_NAME, /*2*/ VersionContract.Version.VERSION_NO, /*3*/ VersionContract.Version.API_LEVEL, /*4*/ VersionContract.Version.RELEASE_DATE, /*5*/ VersionContract.Version.FEATURES }; } … } 7
8. Step 2. Extend SQLiteOpenHelper SQLiteOpenHelper is a Helper class that • Provides access to your SQLiteDatabase • Helps handle transactions • Helps handle Db versioning ◦ Upgrades ◦ Downgrades 8
9. Tips on SQLiteOpenHelper • Thin Database Helper ◦ Just deals with the administration of the database ◦ Logic and queries are stored externally ◦ More Reusable • Thick Database Helper ◦ All logic and administration is done from the Helper ◦ Less Reusable 9
10. SQLiteDatabase.execSQL() • SQLiteDatabase.execSQL (String sql) - execute a SQL statement that doesn't return data, for example - create a db table • SQLite supports only NULL, INTEGER, REAL, TEXT and BLOB storage classes (datatypes) // SQL statement to create the Version table private final static String VERSION_TABLE_CREATE = "CREATE TABLE " + VERSION_TABLE_NAME + " (" + VERSION_ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " + VERSION_ROW_CODE_NAME + " TEXT, " + VERSION_ROW_VERSION_NO + " TEXT, " + VERSION_ROW_API_LEVEL + " TEXT, " + VERSION_ROW_RELEASE_DATE + " TEXT, " + VERSION_ROW_FEATURES + " TEXT" + ");"; @Override public void onCreate(SQLiteDatabase db) { // Create Version table db.execSQL(VERSION_TABLE_CREATE); Log.i(LOG_TAG, "Creating table with query: " + VERSION_TABLE_CREATE); } 10
11. Step 3. Create a Database Two options to create a SQLite Database 1. Use SQLiteDatabase directly (Memory) • create() • openDatabase() • openOrCreateDatabase() 2. SQLiteOpenHelper (Internal Storage) ✔ Database file gets created only once when getReadableDatabase() or getWritableDatabase() is called DatabaseHelper mHelper = DatabaseHelper(this); SQLiteDatabase db = mHelper.getReadableDatabase(); 11
12. Watchouts with SQLite Db is only created once! • when db schema changes, ◦ Increment Db version ◦ Manually delete database (clear data or uninstall) if app not yet published • onUpgrade() - be careful when dropping tables 12
13. SQLite Databases Inspect the Database
14. Basic SQL • SELECT – Select data from a table • UPDATE – Update data from a table • DELETE – Deletes data from a table • INSERT – Inserts data into a table in a database • CREATE {DATABASE | TABLE} – Creates a new element • ALTER {DATABASE | TABLE} – Updates an element https://www.sqlite.org/lang.html 14
15. Basic SQL Select • SELECT {projection} • FROM {table} • WHERE {conditions} • ORDER BY {argument} • SELECT * FROM versions • SELECT * FROM version WHERE _id=8 • SELECT _id, code_name FROM version ORDER BY code_name 15
16. SQLite Tools • Sqlite3 command line tool • SQLite Browser – http://sqlitebrowser.org/ • SQLite Administrator – http://sqliteadmin.orbmu2k.de/ • Navicat for SQLite • Apps on Google Play ◦ Access Database on SDCARD ◦ Access via SuperUser on Rooted phone • Facebook Stetho (also inspect network & view hierarchy) 16
17. SQLite Db Location Where is the database located? • On internal storage, associated with your app • Android Studio/Android Device Monitor/File Explorer, • Find db file under data/data/<package name>/databases • Click on Pull to pull the db file to a local location 17
18. Sqlite3 Open command line • adb shell • cd data/data/<package name>/databases • sqlite3 dbname ← invoke sqlite3 on database • .tables ← list all the tables in db • .schema tablename ← print the SQL CREATE statement for an existing table • .exit ← exit sqlite3 Command line shell for SQLite http://www.sqlite.org/cli.html 18
19. SQLite Browser • Download SQLite Browser • Get the db file from emulator adb pull <remote> <local> • Open SQLite Browser and modify db • Copy the updated db file back to emulator adb push <local> <remote> 19
20. Demo 1. Run sample code 2. Locate the db file on Emulator 3. Inspect the db a. Use sqlite3 to access db b. Open db in SQLite Browser 20
21. SQLite Databases Access a Database
22. Database Operations SQLiteDatabase CRUD • SQLiteDatabase.insert() : Create • SQLiteDatabase.query() : Read • SQLiteDatabase.update() : Update • SQLiteDatabase.delete() : Delete 22
23. SQLiteDabase.query() SQLiteDatabase.query() • tables – The tables to run the query against • columns – Columns to include in rows returned • where – Filter the rows returned • groupBy – Filter that groups rows returned • having – Filter row groups to include in the rows returned • orderBy – Orders the rows returned • limit – Limits the number of rows returned DatabaseHelper mHelper = DatabaseHelper(this); SQLiteDatabase db = mHelper.getReadableDatabase(); Cursor c = db.query( VERSION_TABLE_NAME, // table name VersionContract.Version.PROJECTION, // query result columns null, // selection null, // selectionArgs null, // groupBy null, // having null); // orderBy 23
24. What is a Cursor? • A pointer to a current row in a result set • Starts “before” the first entry in the set • Need to call moveToFirst() or moveToNext() Long Form Cursor c = SQLiteDatabase.query(...) c.moveToFirst(); while (c.isAfterLast() == false) { } Short Form Cursor c = SQLiteDatabase.query(...) while (c.moveToNext()) { } 24
25. SQLiteQueryBuilder • Useful for Strict checks, Building Unions, Convenience • buildQueryString() ◦ distinct – Set if rows should be unique ◦ tables – The tables to run the query against ◦ columns – Columns to include in rows returned ◦ where – Filter the rows returned ◦ groupBy – Filter that groups rows returned ◦ having – Filter row groups to include in the rows returned ◦ orderBy – Orders the rows returned ◦ limit – Limits the number of rows returned 25
26. Accessing SQLite Db • SQLiteDatabase.insert(), query(), update() & delete() • Use a cursor to access the Db • Remember to close the Db & cursor • Handle threading - Db operations needs to be performed asynchronously • Handle requery after data change • Refresh UI after requery 26
27. Accessing SQLite Db A few options: • Handle Db access yourself • Use a library (discussed later) • Write a Content Provider and access with a CursorLoader 27
28. Content Providers Write Your Own Content Provider
29. What is a Content Provider? • Provide managed and secured access to data • Encapsulate the data with a consistent, standardized URI-based access • Useful as a cross-process interface for data-sharing amongst running processes • Part of an Android application ◦ Defined in the application manifest as <provider> ◦ Maps to a ContentProvider class in the project 29
30. Why Content Provider? Consider writing your own Content Provider when • You want to share your data • You want to use features in the Android Framework such as: ◦ CursorLoader ◦ Search autosuggest ◦ Copy/paste ◦ SyncAdapter 30
31. Write Your Own Content Provider • Create a class that extends from ContentProvider ◦ Should match the name attribute in the Manifest ◦ Create Content Authority, Path, Content Uri, and Content Type ▪ Add to Provider class If only one table in DB ▪ Add to DbContract class if multiple tables in DB ◦ Use UriMatcher to match Uri pattern to integers ◦ Implement the required methods • Register your ContentProvider in the Manifest 31
32. Content Provider Terminologies Define the following for a Content Provider • Content Authority - Symbolic name of the provider • Content Path - A name that points to a table or file (a path) • Content URI (Authority + Path) - identifies data in a provider • Content ID - The optional id part points to an individual row in a table • Content Type - What is actually stored in your provider 32
33. Content Type A Content Type is a defined MIME type • Type ◦ vnd.android.cursor.dir ▪ Cursor may contain multiple items ◦ vnd.android.cursor.item ▪ Cursor should only contain 1 item • Subtype ◦ Potentially a MIME subtype ◦ Usually something custom to your application Content Type Examples - • text/plain • text/html • audio/mpeg • video/quicktime • vnd.android.cursor.item/phone • vnd.android.cursor.dir/vnd. sometype 33
34. UriMatcher • Convenience class • Matches incoming Uri to integer values ◦ * Matches a string of any valid characters of any length. ◦ # Matches a string of numeric characters of any length. private static final UriMatcher sUriMatcher = createUriMatcher(); private static UriMatcher createUriMatcher() { final UriMatcher uriMatcher = new UriMatcher(UriMatcher. NO_MATCH); final String authority = VersionContract.CONTENT_AUTHORITY; uriMatcher.addURI(authority, VersionContract.PATH_VERSION, VERSION); uriMatcher.addURI(authority, VersionContract.PATH_VERSION + "/#", VERSION_ID); return uriMatcher; } 34
35. Content Provider - Methods Main methods to override: • onCreate() - initialize the provider • query() - return data to caller • insert() - insert data to content provider • update() - update existing data • delete() - delete data from content provider • getType() - return MIME type of the data in provider 35
36. Register Provider in Manifest • Make sure authority & name match what have been defined for the Content Provider • Set android:exported=”false” if you don’t want other apps to access your Provider <application ...> <activity android:name=".MainActivity"> …. </activity> <provider android:authorities="com.mzm.sample.content_provider" android:name=".provider.VersionProvider" android:exported="false"/> </application> 36
37. Content Providers Access a Content Provider
38. Access Content Get permission to the Content Provider if it’s not your own <uses-permission android:name="android.permission.READ_CONTACTS"/> At a minimum, you will need • A Content URI • A Projection • A ContentResolver or CursorLoader • A Cursor • Something to do with the data you retrieved 38
39. Access content w/ ContentResolver android.content.ContentResolver (API 1) • uri - The provider table used in resolution • projection - The columns that should be returned • selection - Criteria for selecting rows • selectionArgs - Replace ? arguments from Selection • sortOrder - The order of the rows returned 39
40. Access content w/ CursorLoader android.content.CursorLoader (API 11) • context - the current context • uri - The provider table used in resolution • projection - The columns that should be returned • selection - Criteria for selecting rows • selectionArgs - Replace ? arguments from Selection • sortOrder - The order of the rows returned 40
41. What is a CursorLoader? An Android Loader built from AsyncTaskLoader • Targets a ContentProvider • Loads data asynchronously • Handles the Cursor lifecycle i.e. automatically rerun the query when data associated with the query changes @Override public Loader<Cursor> onCreateLoader(int i, Bundle bundle) { CursorLoader cursorLoader = new CursorLoader(getActivity(), VersionContract.Version.CONTENT_URI, VersionContract.Version.PROJECTION, null, null, null ); return cursorLoader; } 41
42. Content Providers Access System Content Providers
43. Android System Content Providers http://developer.android.com/reference/android/provider/package-summary.html 43 • Browser – Bookmarks • Calendar – Attendees – Events – Reminders • CallLog • ContactsContract – Name – Phones – Photos etc. • MediaStore ◦ Audio ▪ Albums ▪ Artists ▪ Playlists ◦ Images ◦ Video • Settings • SyncState • UserDictionary • VoicemailContract
44. Content URI Format • General Form – content://authority/path/id • Authority Examples – android.provider 44 ContactsContract AUTHORITY "com.android.contacts" MediaStore AUTHORITY "media" CalendarContract AUTHORITY "com.android.calendar"
45. Content URI Examples • content://media/external/images/media/9134 • content://com.android.contacts/data/123 • content://com.mybooks.access/books/914 • content://com.mybooks. access/books/authors/42 45
46. Access image from MediaStore • Use ContentResolver ContentResolver.query(MediaStore.Images.Media. EXTERNAL_CONTENT_URI, projection, selection, selectionArgs, sortOrder) • Use CursorLoader (preferred) CursorLoader(context, MediaStore.Images.Media. EXTERNAL_CONTENT_URI, projection, selection, selectionArgs, sortOrder) 46
47. SQLite & Content Provider Libraries & Resources
48. Open Source Libraries Library What it does SQLiteAssetHelper • Ship with an existing SQLite db • Works with Content Provider SQLBrite (by Square) • Works with RxJava & RxAndroid • Notifies when data changes Cupboard for Android • Easy filtering of queries • Works with both SQLite & Content Providers greenDao & OrmLite • Object to Relational Mapping • Maps to POJO’s A few libraries as examples here, there are many more... 48
49. Resources Libraries • 10 Android Database Libraries • Search ‘sqlite’ or ‘content providers’ on Android Arsenal Classes & Tutorials • Android SQLite database and content provider Tutorial by Vogella • Udacity class Developing Android Apps Sample code • Google I/O schedule Android app • My sample code for this talk 49
50. Thank you! Please fill out the class feedback How to contact me - @margaretmz +MargaretMaynardReid 50