Android - Sqlite

DDL

final HashSet<String> tableNameHashSet = new HashSet<String>();
tableNameHashSet.add(Contract.ParentTableEntry.TABLE_NAME);
tableNameHashSet.add(Contract.ChildTableEntry.TABLE_NAME);

mContext.deleteDatabase(myDBDbHelper.DATABASE_NAME);
SQLiteDatabase db = new myDBDbHelper(this.mContext).getWritableDatabase();
assertEquals(true, db.isOpen());

// have we created the tables we want?
Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
assertTrue("Error: This means that the database has not been created correctly", c.moveToFirst());

// verify that the tables have been created
do {
	tableNameHashSet.remove(c.getString(0));
} while( c.moveToNext() );

// if this fails, it means that your database doesn't contain both the location entry
// and weather entry tables
assertTrue("Error: Your database was created without both the tables", tableNameHashSet.isEmpty());

// now, do our tables contain the correct columns?
c = db.rawQuery("PRAGMA table_info(" + Contract.ChildTableEntry.TABLE_NAME + ")", null);

assertTrue("Error: This means that we were unable to query the database for table information.", c.moveToFirst());

// Build a HashSet of all of the column names we want to look for
final HashSet<String> childTableColumnHashSet = new HashSet<String>();
childTableColumnHashSet .add(Contract.ChildTableEntry._ID);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_NAME);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_COORD_LAT);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_COORD_LONG);
childTableColumnHashSet .add(Contract.ChildTableEntry.COLUMN_LOCATION_SETTING);

int columnNameIndex = c.getColumnIndex("name");
do {
	String columnName = c.getString(columnNameIndex);
	childTableColumnHashSet .remove(columnName);
} while(c.moveToNext());

// if this fails, it means that your database doesn't contain all of the required location
// entry columns
assertTrue("Error: The database doesn't contain all of the required child entry columns", childTableColumnHashSet .isEmpty());
db.close();

// Delete the database
mContext.deleteDatabase(myDBDbHelper.DATABASE_NAME);

DML

• ContentValues store the value of a column row in a key pair format. The key = The static field of the contract, the value = the value
• See also Android - Cursor

// First step: Get reference to writable database
// If there's an error in those massive SQL table creation Strings,
// errors will be thrown here when you try to get a writable database.
WeatherDbHelper dbHelper = new myDBDbHelper(mContext);
SQLiteDatabase db = dbHelper.getWritableDatabase();

// Second Step: Create ContentValues of what you want to insert
// (you can use the createNorthPoleLocationValues if you wish)
ContentValues testValues = new ContentValues();
testValues.put(Contract.ChildTableEntry.COLUMN_CITY_NAME, "North Pole");
testValues.put(Contract.ChildTableEntry.COLUMN_COORD_LAT, 64.7488);
testValues.put(Contract.ChildTableEntry.COLUMN_COORD_LONG, -147.353);

// Third Step: Insert ContentValues into database and get a row ID back
long childTableRowId;
childTableRowId = db.insert(Contract.ChildTableEntry.TABLE_NAME, null, testValues);

// Verify we got a row back. If the value = -1, the insert has failed ! 
assertTrue(childTableRowId != -1);

// Data's inserted.  

// Verification of the insertion the round trip.

// Fourth Step: Query the database and receive a Cursor back
Cursor cursor = db.query(
		Contract.ChildTableEntry.TABLE_NAME,  // Table to Query
		null, // all columns
		null, // Columns for the "where" clause
		null, // Values for the "where" clause
		null, // columns to group by
		null, // columns to filter by row groups
		null // sort order
);

// Move the cursor to a valid database row and check to see if we got any records back
// from the query
assertTrue( "Error: No Records returned from  query", cursor.moveToFirst() );

// Fifth Step: Validate data in resulting Cursor with the original ContentValues
// (you can use the validateCurrentRecord function in TestUtilities to validate the
// query if you like)
Set<Map.Entry<String, Object>> valueSet = testValues.valueSet();
for (Map.Entry<String, Object> entry : valueSet) {
	String columnName = entry.getKey();
	int idx = cursor.getColumnIndex(columnName);
	assertFalse("Column '" + columnName + "' not found. " + error, idx == -1);
	String expectedValue = entry.getValue().toString();
	assertEquals("Value '" + entry.getValue().toString() +
			"' did not match the expected value '" +
			expectedValue + "'. " + error, expectedValue, cursor.getString(idx));
}

// Move the cursor to demonstrate that there is only one record in the database
assertFalse( "Error: More than one record returned from location query",
		cursor.moveToNext() );

// Sixth Step: Close Cursor and Database
cursor.close();
db.close();

Example: Database Test (Create DB, Insert, Query, ….) See TestDb.java