Testing SQLite database in Android

Testing SQLite database in Android

Testing SQLite database in Android

Presentation Details:
------------------------------------------------------
Overview of base principle of testing in Android and especially testing SQLite database. Tips for testing and working with SQLite database.
- Local tests
- Instrumentation tests
- Overview base Android components for working with SQLite database
- Example of tests

Project:
https://github.com/AlexZhukovich/mNotes

Blog:
http://alexzh.com

Twitter:
https://twitter.com/alex_zhukovich

If you have some more questions, I will happily answer them

2b0404a5db1a74f01bf3bf94d142e28c?s=128

Alex Zhukovich

February 22, 2017
Tweet

Transcript

  1. 2.

    Android world • Many version of Android OS • Many

    devices from different manufactures 1 February, 2017, https://developer.android.com/about/dashboards/index.html
  2. 4.

    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
  3. 5.

    Instrumentation vs local tests • Local tests • JUnit •

    Robolectric • Mockito • etc • Instrumentation tests • JUnit • Espresso • UI Automator • etc 4
  4. 6.

    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
  5. 9.

    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.
  6. 10.

    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.
  7. 11.

    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.
  8. 12.

    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.
  9. 13.

    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
  10. 14.

    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.
  11. 15.

    SQLite database in Android SQLiteDatabase Insert Query Update Delete Raw

    SQL query 14 ContentProvider Register content provider Insert Query Update Delete Insert many rows
  12. 16.

    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
  13. 17.

    Update database (Upgrade / Downgrade) void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion,

    int newVersion){ … } 16 SQL Import from resources Backup Import Restore
  14. 18.

    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
  15. 19.

    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
  16. 20.

    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
  17. 21.

    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<Map.Entry<String, Object>> valueSet = expectedValues.valueSet(); for (Map.Entry<String, Object> 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
  18. 23.

    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
  19. 24.

    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
  20. 25.

    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
  21. 26.

    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
  22. 27.

    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
  23. 28.

    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
  24. 29.

    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
  25. 30.

    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