public OleDBDiffStorage(OleDBDataModel dataModel)
: this(dataModel,
AbstractADODataModel.DEFAULT_PREFERENCE_TABLE,
AbstractADODataModel.DEFAULT_USER_ID_COLUMN,
AbstractADODataModel.DEFAULT_ITEM_ID_COLUMN,
AbstractADODataModel.DEFAULT_PREFERENCE_COLUMN,
DEFAULT_DIFF_TABLE,
DEFAULT_ITEM_A_COLUMN,
DEFAULT_ITEM_B_COLUMN,
DEFAULT_COUNT_COLUMN,
DEFAULT_AVERAGE_DIFF_COLUMN,
DEFAULT_MIN_DIFF_COUNT)
{
}
public OleDBDiffStorage(OleDBDataModel dataModel,
String preferenceTable,
String userIDColumn,
String itemIDColumn,
String preferenceColumn,
String diffsTable,
String itemIDAColumn,
String itemIDBColumn,
String countColumn,
String avgColumn,
int minDiffCount)
:base(dataModel,
// getDiffSQL
"SELECT " + countColumn + ", " + avgColumn + " FROM " + diffsTable +
" WHERE " + itemIDAColumn + "=? AND "+ itemIDBColumn + "=? UNION " +
"SELECT " + countColumn + ", " + avgColumn + " FROM " + diffsTable +
" WHERE " + itemIDAColumn + "=? AND "+ itemIDBColumn + "=?",
// getDiffsSQL
"SELECT " + countColumn + ", " + avgColumn + ", " + itemIDAColumn + " FROM " + diffsTable + ", " +
preferenceTable + " WHERE " + itemIDBColumn + "=? AND " + itemIDAColumn + " = " + itemIDColumn +
" AND " + userIDColumn + "=? ORDER BY " + itemIDAColumn,
// getAverageItemPrefSQL
"SELECT COUNT(1), AVG(" + preferenceColumn + ") FROM " + preferenceTable +
" WHERE " + itemIDColumn + "=?",
// updateDiffSQLs
new String[] {
"UPDATE " + diffsTable + " SET " + avgColumn + " = " + avgColumn + " - (? / " + countColumn +
") WHERE " + itemIDAColumn + "=?",
"UPDATE " + diffsTable + " SET " + avgColumn + " = " + avgColumn + " + (? / " + countColumn +
") WHERE "+ itemIDBColumn + "=?"
},
// removeDiffSQL
new String[] {
"UPDATE " + diffsTable + " SET " + countColumn + " = " + countColumn + "-1, " +
avgColumn + " = " + avgColumn + " * ((" + countColumn + " + 1) / CAST(" + countColumn +
" AS DECIMAL)) + ? / CAST(" + countColumn + " AS DECIMAL) WHERE " + itemIDAColumn + "=?",
"UPDATE " + diffsTable + " SET " + countColumn + " = " + countColumn + "-1, " +
avgColumn + " = " + avgColumn + " * ((" + countColumn + " + 1) / CAST(" + countColumn +
" AS DECIMAL)) - ? / CAST(" + countColumn + " AS DECIMAL) WHERE "+ itemIDBColumn + "=?"
},
// getRecommendableItemsSQL
"SELECT id FROM " +
"(SELECT " + itemIDAColumn + " AS id FROM " + diffsTable + ", " + preferenceTable +
" WHERE "+ itemIDBColumn + " = item_id AND " + userIDColumn + "=? UNION DISTINCT" +
" SELECT "+ itemIDBColumn + " AS id FROM " + diffsTable + ", " + preferenceTable +
" WHERE " + itemIDAColumn + " = item_id AND " + userIDColumn +
"=?) possible_item_ids WHERE id NOT IN (SELECT " + itemIDColumn + " FROM " + preferenceTable +
" WHERE " + userIDColumn + "=?)",
// deleteDiffsSQL
"DELETE FROM " + diffsTable,
// createDiffsSQL
"INSERT INTO " + diffsTable + " (" + itemIDAColumn + ", "+ itemIDBColumn + ", " + avgColumn +
", " + countColumn + ") SELECT prefsA." + itemIDColumn + ", prefsB." + itemIDColumn + ',' +
" AVG(prefsB." + preferenceColumn + " - prefsA." + preferenceColumn + ")," +
" COUNT(1) AS count FROM " + preferenceTable + " prefsA, " + preferenceTable + " prefsB WHERE prefsA." +
userIDColumn + " = prefsB." + userIDColumn + " AND prefsA." + itemIDColumn + " < prefsB." +
itemIDColumn + ' ' + " GROUP BY prefsA." + itemIDColumn +
", prefsB." + itemIDColumn + " HAVING count >=?",
// diffsExistSQL
"SELECT COUNT(1) FROM " + diffsTable,
minDiffCount)
{
}
