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

Transcript

1. Testing SQLite
   database in Android
   Aliaksandr Zhukovich
   @Alex_Zhukovich
   

   View Slide

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

   View Slide

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

   View Slide

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
   

   View Slide

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

   View Slide

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
   

   View Slide

7. Database
   

   View Slide

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

   View Slide

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.
   

   View Slide

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.
    

    View Slide

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.
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

22. ContentProvider
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide