Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Testing SQLite database in Android

Alex Zhukovich
February 22, 2017

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

Alex Zhukovich

February 22, 2017
Tweet

More Decks by Alex Zhukovich

Other Decks in Technology

Transcript

  1. Testing SQLite
    database in Android
    Aliaksandr Zhukovich
    @Alex_Zhukovich

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

  8. 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 full-size slide

  9. 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 full-size slide

  10. 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 full-size slide

  11. 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 full-size slide

  12. 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 full-size slide

  13. 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 full-size slide

  14. 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 full-size slide

  15. 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 full-size slide

  16. Update database (Upgrade / Downgrade)
    void onUpgrade(SQLiteDatabase sqLiteDatabase,
    int oldVersion,
    int newVersion){

    }
    16
    SQL
    Import
    from
    resources
    Backup
    Import
    Restore

    View full-size slide

  17. 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 full-size slide

  18. 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 full-size slide

  19. 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 full-size slide

  20. 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 full-size slide

  21. ContentProvider

    View full-size slide

  22. 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 full-size slide

  23. 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 full-size slide

  24. 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 full-size slide

  25. 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 full-size slide

  26. 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 full-size slide

  27. 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 full-size slide

  28. 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 full-size slide

  29. 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 full-size slide

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

    View full-size slide