Testing SQLite database in Android Aliaksandr Zhukovich @Alex_Zhukovich 

Android world • Many version of Android OS • Many devices from different manufactures 1 February, 2017, https://developer.android.com/about/dashboards/index.html 

SQLite https://developer.android.com/reference/android/database/sqlite/package-summary.html 2 SQLite 3.4 API 1 SQLite 3.5 API 3 SQLite 3.6 API 8 SQLite 3.7 API 11 SQLite 3.8 API 21 SQLite 3.9 API 24 

Database • Create (SQL query, import from resources) • Update • Upgrade (SQL query, import from resources) • Downgrade (SQL query, import from resources) • Delete /data/data/your.app.package/databases/database_name 3 

Instrumentation vs local tests • Local tests • JUnit • Robolectric • Mockito • etc • Instrumentation tests • JUnit • Espresso • UI Automator • etc 4 

Init Instrumentation vs local tests • Instrumentation tests • Context Context context = InstrumentationRegistry.getTargetContext(); • Local tests • Context Context context = RuntimeEnvironment.application; • Register ContentProvider ProviderInfo providerInfo = new ProviderInfo(); providerInfo.authority = NotesContract.CONTENT_AUTHORITY; Robolectric.buildContentProvider(NotesProvider.class) .create(providerInfo); 5 

Database 

Work with SQLite in Android Cursor ContentValues 7 SQLiteOpenHelper SQLiteDatabase CursorLoader ContentProvider 

Cursor 8 The Cursor is an interface provides access to the result set returned by a database query. • boolean moveToFirst() Move the cursor to the first row. • boolean moveToPosition(int position) Move the cursor to an absolute position. • int getCount() Returns the numbers of rows in the cursor. • String getString(int columnIndex) Returns the value of the requested column as a String. • void close() Closes the Cursor, releasing all of its resources and making it completely invalid. 

ContentValues 9 The ContentValues is a class which is used to store a set of values. • void clear() Removes all values. • boolean containsKey(String key) Returns true if this object has the named value. • String getAsString(String key) Gets a value and converts it to a String. • void put(String key, String value) Adds a value to the set. 

SQLiteOpenHelper 10 The SQLiteOpenHelper is used to manage the SQLite database file for a process. • void close() Close any open database object. • SQLiteDatabase getReadableDatabase() Create and/or open a database. • SQLiteDatabase getWritableDatabase() Create and/or open a database that will be used for reading and writing. • void onCreate(SQLiteDatabase db) Called when the database is created for the first time. • void onUpgrade( … ) Called when the database needs to be upgraded. 

SQLiteDatabase 11 The SQLiteDatabase is a class which contains methods for interacting with database. • void execSQL(String sql) Execute a single SQL statement. • boolean isOpen() Returns true if the database is currently open. • Cursor query( … ) Query the given URL, returning a Cursor over the result set. • long insert( … ) Convenience method for inserting a row into the database. 

CursorLoader 12 The CursorLoader is a member of Android’s loader framework designed to handle cursor. • void cancelLoadInBackground() Called on the main thread to abort a load in progress. • Cursor loadInBackground() Called on a worker thread to perform the actual load and to return the result of the load operation. • void onStartLoading() Starts an asynchronous load of the contacts list data. • void onStopLoading() Must be called from the UI thread 

ContentProvider 13 The ContentProvider is a component needed to share data between multiple applications. • Uri insert( … ) Implement this to handle requests to insert a new row. • int update( … ) Implement this to handle requests to update one or more rows. • Cursor query( … ) Implement this to handle query requests from clients. • int delete( … ) Implement this to handle requests to delete one or more rows. 

SQLite database in Android SQLiteDatabase Insert Query Update Delete Raw SQL query 14 ContentProvider Register content provider Insert Query Update Delete Insert many rows 

Create database final static String CREATE_NOTES_TABLE = "CREATE TABLE " + NoteEntry.TABLE_NAME + " (" + NoteEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + NoteEntry.COLUMN_NOTE_CREATION + " INTEGER NOT NULL," + NoteEntry.COLUMN_NOTE_TITLE + " TEXT NOT NULL," + NoteEntry.COLUMN_NOTE_DESCRIPTION + " TEXT," + NoteEntry.COLUMN_NOTE_MODIFICATION + " INTEGER NOT NULL);"; sqLiteDatabase.execSQL(CREATE_NOTES_TABLE); @Test public void shouldCreateDatabase() { mDbHelper = new NotesDbHelper(mContext); mDatabase = mDbHelper.getWritableDatabase(); assertTrue(mDatabase.isOpen()); } 15 

Update database (Upgrade / Downgrade) void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion){ … } 16 SQL Import from resources Backup Import Restore 

Testing Upgrade database @Test public void shouldTestUpgradeDatabaseFromVersion1To2() { final int oldVersion = 1; final int newVersion = 2; DatabaseUtils.deleteTable(mDatabase, NotesContract.NoteEntry.TABLE_NAME); mDatabase.execSQL(NotesDbHelper.SQL_BASE_CREATE_NOTES_TABLE); DatabaseUtils.insertNote(mDatabase, TestDataUtils.getTestNoteSchemeV1()); … mDbHelper.onUpgrade(mDatabase, oldVersion, newVersion); mDatabase.close(); mDatabase = mDbHelper.getReadableDatabase(); cursor = mDatabase.query(NoteEntry.TABLE_NAME, null, null, null, null, null, null); assertTrue(cursor.moveToFirst()); TestDataUtils.verifyCurrentRecord(valuesV1, cursor); } 17 

Insert record @Test public void shouldInsertNote() { ContentValues noteTestValue = TestDataUtils.getTestNote(); long noteId = mDatabase.insert( NoteEntry.TABLE_NAME, // table to query null, // null column hack values // values ); assertTrue(noteId != -1); Cursor cursor = mDatabase.query( NoteEntry.TABLE_NAME, // table to query null, // columns null, // columns for the "where" clause null, // values for the "where" clause null, // column to group by null, // column to filter by row group null // sort order ); assertTrue(cursor.moveToFirst()); verifyCurrentRecord(noteTestValue, cursor); } 18 

Update record @Test public void shouldTestNotesTable() { … final int updatedRows = 1; final ContentValues updatedNoteValues = getUpdatedNote(noteTestValue); long numUpdatedRows = mDatabase.update( NoteEntry.TABLE_NAME, // table to query updatedNoteValues, //values NoteEntry._ID + " = ?", // columns for the "where" clause new String[]{String.valueOf(newNoteId)} // values for the "where" clause ); assertEquals(updatedRows, numUpdatedRows); cursor = mDatabase.query(NoteEntry.TABLE_NAME, null, null, null, null, null, null); assertTrue(cursor.moveToFirst()); verifyCurrentRecord(updatedNoteValues, cursor) } 19 

Verification of data Cursor cursor = mDatabase.query( NoteEntry.TABLE_NAME, // table to query null, // columns null, // columns for the "where" clause null, // values for the "where" clause null, // column to group by null, // column to filter by row group null // sort order ); public static void verifyCurrentRecord(ContentValues expectedValues, Cursor valueCursor) { Set> valueSet = expectedValues.valueSet(); for (Map.Entry entry : valueSet) { String columnName = entry.getKey(); int idx = valueCursor.getColumnIndex(columnName); assertFalse("Column '" + columnName + "' not found. ", idx == -1); String expectedValue = entry.getValue().toString(); assertEquals("Value '" + entry.getValue().toString() + "' did not match the expected value '" + expectedValue, expectedValue, valueCursor.getString(idx)); } } 20 

ContentProvider 

Register ContentProvider ContentProvider should be register in AndroidManifest.xml @Test public void shouldContentProviderRegistered() { PackageManager pm = mContext.getPackageManager(); ComponentName componentName = new ComponentName(mContext.getPackageName(), NotesProvider.class.getName()); try { ProviderInfo providerInfo = pm.getProviderInfo(componentName, 0); assertEquals(providerInfo.authority, NotesContract.CONTENT_AUTHORITY); } catch (PackageManager.NameNotFoundException ex) { fail("Error: NotesProvider not registered at " + mContext.getPackageName()); } } 22 

GetType Content URI: PREFIX://AUTHORITY/PATH/[ID] content://com.alexzh.mnotes/notes content://com.alexzh.mnotes/notes/6 @Test public void shouldVerifyGetType() { String type = mContext.getContentResolver().getType(NoteEntry.CONTENT_URI); assertEquals(NoteEntry.CONTENT_TYPE, type); type = mContext.getContentResolver().getType(NoteEntry.buildNoteUri(NOTE_ID)); assertEquals(NoteEntry.CONTENT_ITEM_TYPE, type); } 23 

Query @Test public void shouldVerifyNotesQuery() { ContentValues noteValues = TestDataUtils.getTestNote(); DatabaseUtils.insertNote(mDatabase, noteValues); Cursor cursor = mContext.getContentResolver().query( NoteEntry.CONTENT_URI, // uri null, //projection null, // columns for the "where" clause null, // values for the "where" clause null // sort ); assertNotNull(cursor); assertTrue(cursor.moveToFirst()); verifyCurrentRecord(noteValues, cursor); } 24 

Insert @Test public void shouldVerifyInsertNote() { ContentValues noteValues = TestDataUtils.getTestNote(); Uri noteUri = mContext.getContentResolver().insert(NoteEntry.CONTENT_URI, noteValues); assertNotNull(noteUri); assertEquals(TestDataUtils.NOTE_TEST_ID, ContentUris.parseId(noteUri)); Cursor cursor = mContext.getContentResolver().query( NoteEntry.CONTENT_URI, // uri null, //projection null, // columns for the "where" clause null, // values for the "where" clause null // sort ); assertNotNull(cursor); assertTrue(cursor.moveToFirst()); verifyCurrentRecord(noteValues, cursor); } 25 

Update @Test public void shouldVerifyUpdateNote() { ContentValues noteValues = TestDataUtils.getTestNote(); long noteId = DatabaseUtils.insertNote(mDatabase, noteValues); noteValues.put(NoteEntry.COLUMN_NOTE_TITLE, "Updated note"); mContext.getContentResolver().update( NoteEntry.CONTENT_URI, // uri noteValues, // values NoteEntry._ID + " = ?", // columns for the "where" clause new String[] {String.valueOf(noteId)} // values for the "where" clause ); Cursor cursor = mContext.getContentResolver().query( NoteEntry.CONTENT_URI, // uri null, //projection null, // columns for the "where" clause null, // values for the "where" clause null // sort ); assertNotNull(cursor); assertTrue(cursor.moveToFirst()); verifyCurrentRecord(noteValues, cursor); } 26 

Delete @Test public void shouldVerifyDeleteNote() { ContentValues noteValues = TestDataUtils.getTestNote(); long noteId = DatabaseUtils.insertNote(mDatabase, noteValues); mContext.getContentResolver().delete( NoteEntry.CONTENT_URI, // uri NoteEntry._ID + " = ?", // columns for the "where" clause new String[] {String.valueOf(noteId)} // values for the "where" clause ); Cursor cursor = mContext.getContentResolver().query( NoteEntry.CONTENT_URI, // uri null, //projection null, // columns for the "where" clause null, // values for the "where" clause null // sort ); assertNotNull(cursor); assertFalse(cursor.moveToFirst()); } 27 

Insert many rows @Test public void shouldVerifyBulkInsert() { ContentValues[] notesValues = TestDataUtils.getTestNotes(); int countInsertedNotes = mContext.getContentResolver().bulkInsert( NoteEntry.CONTENT_URI, // uri notesValues // values ); assertEquals(notesValues.length, countInsertedNotes); Cursor cursor = mContext.getContentResolver().query( NoteEntry.CONTENT_URI, // uri null, //projection null, // columns for the "where" clause null, // values for the "where" clause null // sort ); assertNotNull(cursor); assertTrue(cursor.moveToFirst()); verifyCurrentRecord(notesValues[0], cursor); … } 28 

Resources 29 • Best Practices for Testing https://developer.android.com/training/testing/ • Saving Data in SQL Databases https://developer.android.com/training/basics/data-storage/databases.html • SQLite https://www.sqlite.org/ • Robolectric http://robolectric.org/ • Project https://github.com/AlexZhukovich/mNotes @Alex_Zhukovich 

Feedback 22 http://bit.ly/feedbackLodQATestingSQLite @Alex_Zhukovich