[S07.03-ConflictResolutionPolicy]Test for ON CONFLICT REPLACE not found

[kaushiknsanji]

In this exercise S07.03-Exercise-ConflictResolutionPolicy, we are required to 'Add a UNIQUE constraint on the date column to replace on conflict'.

So for this, I looked up the sqlite documentation and wrote this way, but did not give attention to 'replace on conflict' in the TODO -

/**
 * Called when the database is created for the first time. This is where the creation of
 * tables and the initial population of the tables should happen.
 *
 * @param sqLiteDatabase The database.
 */
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {

	/*
	 * This String will contain a simple SQL statement that will create a table that will
	 * cache our weather data.
	 */
	final String SQL_CREATE_WEATHER_TABLE =

			"CREATE TABLE " + WeatherEntry.TABLE_NAME + " (" +

			/*
			 * WeatherEntry did not explicitly declare a column called "_ID". However,
			 * WeatherEntry implements the interface, "BaseColumns", which does have a field
			 * named "_ID". We use that here to designate our table's primary key.
			 */
			WeatherEntry._ID               + " INTEGER PRIMARY KEY AUTOINCREMENT, " +

			WeatherEntry.COLUMN_DATE       + " INTEGER NOT NULL, "                 +

			WeatherEntry.COLUMN_WEATHER_ID + " INTEGER NOT NULL, "                 +

			WeatherEntry.COLUMN_MIN_TEMP   + " REAL NOT NULL, "                    +
			WeatherEntry.COLUMN_MAX_TEMP   + " REAL NOT NULL, "                    +

			WeatherEntry.COLUMN_HUMIDITY   + " REAL NOT NULL, "                    +
			WeatherEntry.COLUMN_PRESSURE   + " REAL NOT NULL, "                    +

			WeatherEntry.COLUMN_WIND_SPEED + " REAL NOT NULL, "                    +
			WeatherEntry.COLUMN_DEGREES    + " REAL NOT NULL, " +
					"CONSTRAINT date_unique UNIQUE (" + WeatherEntry.COLUMN_DATE + ") " +
					");";

		  //COMPLETED (1) Add a UNIQUE constraint on the date column to replace on conflict

	/*
	 * After we've spelled out our SQLite table creation statement above, we actually execute
	 * that SQL with the execSQL method of our SQLite database object.
	 */
	sqLiteDatabase.execSQL(SQL_CREATE_WEATHER_TABLE);
}

While in the Solution code, its given as below -

/**
 * Called when the database is created for the first time. This is where the creation of
 * tables and the initial population of the tables should happen.
 *
 * @param sqLiteDatabase The database.
 */
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {

	/*
	 * This String will contain a simple SQL statement that will create a table that will
	 * cache our weather data.
	 */
	final String SQL_CREATE_WEATHER_TABLE =

			"CREATE TABLE " + WeatherEntry.TABLE_NAME + " (" +

			/*
			 * WeatherEntry did not explicitly declare a column called "_ID". However,
			 * WeatherEntry implements the interface, "BaseColumns", which does have a field
			 * named "_ID". We use that here to designate our table's primary key.
			 */
			WeatherEntry._ID               + " INTEGER PRIMARY KEY AUTOINCREMENT, " +

			WeatherEntry.COLUMN_DATE       + " INTEGER NOT NULL, "                 +

			WeatherEntry.COLUMN_WEATHER_ID + " INTEGER NOT NULL,"                  +

			WeatherEntry.COLUMN_MIN_TEMP   + " REAL NOT NULL, "                    +
			WeatherEntry.COLUMN_MAX_TEMP   + " REAL NOT NULL, "                    +

			WeatherEntry.COLUMN_HUMIDITY   + " REAL NOT NULL, "                    +
			WeatherEntry.COLUMN_PRESSURE   + " REAL NOT NULL, "                    +

			WeatherEntry.COLUMN_WIND_SPEED + " REAL NOT NULL, "                    +
			WeatherEntry.COLUMN_DEGREES    + " REAL NOT NULL, "                    +

		  //COMPLETED (1) Add a UNIQUE constraint on the date column to replace on conflict
			/*
			 * To ensure this table can only contain one weather entry per date, we declare
			 * the date column to be unique. We also specify "ON CONFLICT REPLACE". This tells
			 * SQLite that if we have a weather entry for a certain date and we attempt to
			 * insert another weather entry with that date, we replace the old weather entry.
			 */
			" UNIQUE (" + WeatherEntry.COLUMN_DATE + ") ON CONFLICT REPLACE);";

	/*
	 * After we've spelled out our SQLite table creation statement above, we actually execute
	 * that SQL with the execSQL method of our SQLite database object.
	 */
	sqLiteDatabase.execSQL(SQL_CREATE_WEATHER_TABLE);
}

The Only difference here is that I did not mention the keywords ON CONFLICT REPLACE, after defining that the COLUMN_DATE column should have a UNIQUE constraint. To my surprise, the given test method testDuplicateDateInsertBehaviorShouldReplace() in the test code TestSunshineDatabase.java that tests this, ran without failure.

@Test
public void testDuplicateDateInsertBehaviorShouldReplace() {

	/* Obtain weather values from TestUtilities */
	ContentValues testWeatherValues = TestUtilities.createTestWeatherContentValues();

	/*
	 * Get the original weather ID of the testWeatherValues to ensure we use a different
	 * weather ID for our next insert.
	 */
	long originalWeatherId = testWeatherValues.getAsLong(REFLECTED_COLUMN_WEATHER_ID);

	/* Insert the ContentValues with old weather ID into database */
	database.insert(
			WeatherContract.WeatherEntry.TABLE_NAME,
			null,
			testWeatherValues);

	/*
	 * We don't really care what this ID is, just that it is different than the original and
	 * that we can use it to verify our "new" weather entry has been made.
	 */
	long newWeatherId = originalWeatherId + 1;

	testWeatherValues.put(REFLECTED_COLUMN_WEATHER_ID, newWeatherId);

	/* Insert the ContentValues with new weather ID into database */
	database.insert(
			WeatherContract.WeatherEntry.TABLE_NAME,
			null,
			testWeatherValues);

	/* Query for a weather record with our new weather ID */
	Cursor newWeatherIdCursor = database.query(
			REFLECTED_TABLE_NAME,
			new String[]{REFLECTED_COLUMN_DATE},
			null,
			null,
			null,
			null,
			null);

	String recordWithNewIdNotFound =
			"New record did not overwrite the previous record for the same date.";
	assertTrue(recordWithNewIdNotFound,
			newWeatherIdCursor.getCount() == 1);

	/* Always close the cursor after you're done with it */
	newWeatherIdCursor.close();
}

In the testDuplicateDateInsertBehaviorShouldReplace(), it is clearly seen that we are inserting a second record with incremented value of REFLECTED_COLUMN_WEATHER_ID which will be 322 (original value being 321, defined in TestUtilities class). Post insert, we check the count of records in the table by firing a query and calling getCount() on the Cursor. If this results in 1 record (when we had inserted two records with the same date), then this test case passes, which in turn means that the UNIQUE constraint on COLUMN_DATE is mentioned. So ideally, this method has checked for only duplicate records but did not check if 'Replace on Conflict' is working. Hence even my solution to the TODO worked without the ON CONFLICT REPLACE!

How to Fix this?

To test this 'Replace on Conflict', we need to mention the REFLECTED_COLUMN_WEATHER_ID as part of the projection in the Cursor#query() method. Then, post the line where assert for duplicate records, we need to the move the cursor to its first position Cursor#moveToPosition(0), so that we can read the record in our assert statement. This is done as below -

Look for the comments that say 'EDIT :: Kaushik Sanji :'

@Test
public void testDuplicateDateInsertBehaviorShouldReplace() {

	/* Obtain weather values from TestUtilities */
	ContentValues testWeatherValues = TestUtilities.createTestWeatherContentValues();

	/*
	 * Get the original weather ID of the testWeatherValues to ensure we use a different
	 * weather ID for our next insert.
	 */
	long originalWeatherId = testWeatherValues.getAsLong(REFLECTED_COLUMN_WEATHER_ID);

	/* Insert the ContentValues with old weather ID into database */
	database.insert(
			WeatherContract.WeatherEntry.TABLE_NAME,
			null,
			testWeatherValues);

	/*
	 * We don't really care what this ID is, just that it is different than the original and
	 * that we can use it to verify our "new" weather entry has been made.
	 */
	long newWeatherId = originalWeatherId + 1;

	testWeatherValues.put(REFLECTED_COLUMN_WEATHER_ID, newWeatherId);

	/* Insert the ContentValues with new weather ID into database */
	database.insert(
			WeatherContract.WeatherEntry.TABLE_NAME,
			null,
			testWeatherValues);

	/* Query for a weather record with our new weather ID */
	Cursor newWeatherIdCursor = database.query(
			REFLECTED_TABLE_NAME,
			//EDIT :: Kaushik Sanji : Added 'REFLECTED_COLUMN_WEATHER_ID' to the projection
			new String[]{REFLECTED_COLUMN_WEATHER_ID, REFLECTED_COLUMN_DATE},
			null,
			null,
			null,
			null,
			null);

	String recordWithNewIdNotFound =
			"New record did not overwrite the previous record for the same date.";
	assertTrue(recordWithNewIdNotFound,
			newWeatherIdCursor.getCount() == 1);

	//EDIT :: Kaushik Sanji : Code to test the conflict on replace: START
	//Moving to the first record to validate the conflict replace condition
	newWeatherIdCursor.moveToPosition(0);

	String recordDidNotReplaceOnConflict
			= "New record for the same date did not replace.";
	assertTrue(recordDidNotReplaceOnConflict,
			newWeatherId == newWeatherIdCursor.getLong(newWeatherIdCursor.getColumnIndex(REFLECTED_COLUMN_WEATHER_ID)));
	//EDIT :: Kaushik Sanji : Code to test the conflict on replace: END

	/* Always close the cursor after you're done with it */
	newWeatherIdCursor.close();
}

As seen above, we are comparing the value of REFLECTED_COLUMN_WEATHER_ID of the newly inserted record with the incremented value previously stored in a variable. If they are NOT same then ON CONFLICT REPLACE was NOT mentioned along with the UNIQUE constraint on COLUMN_DATE and this assert will fail as expected for such a case.

Please update the change requested.