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) { }