/// <summary>
/// Applies majority on facts and normalizes their score
/// </summary>
public static void CalculateFactScores(MySqlUtils sqlUtils, string category, TopicType topicType)
{
string factScoreUpdate1 = string.Format(
"UPDATE {0} sf, {3} t1 SET sf.Score = IFNULL((SELECT SUM(us.Belief) FROM {1} us, {2} im " +
"WHERE sf.ItemID=im.ItemID AND im.UserId=us.UserId AND sf.Category='{4}'), 0) " +
"WHERE sf.TopicID=t1.TopicId AND t1.TopicType={5}",
TableConstants.ScoredFacts, TableConstants.UserScores,
TableConstants.ItemsMentions, TableConstants.Topics,
category, (int)topicType);
string factScoreUpdate2 = string.Format(
"UPDATE {0} sf, (SELECT SUM(sf1.Score) AS TopicScore, sf1.TopicId " +
"FROM {0} sf1, {1} t WHERE sf1.Category = '{2}' AND sf1.TopicId=t.TopicId AND t.TopicType={3} " +
"GROUP BY sf1.TopicId) cs " +
"SET sf.Score = sf.Score / cs.TopicScore " +
"WHERE sf.TopicId = cs.TopicId AND sf.Category='{2}' AND cs.TopicScore <> 0",
TableConstants.ScoredFacts, TableConstants.Topics, category, (int)topicType);
sqlUtils.ExecuteNonQuery(factScoreUpdate1);
sqlUtils.ExecuteNonQuery(factScoreUpdate2);
}
public static void NewNormalizedScoreCalculation(MySqlUtils sqlUtils)
{
object obj = sqlUtils.ExecuteScalar(string.Format(
"select max(NumOfFacts) from {0}", TableConstants.UserScores));
int maxFactor = (int)obj;
int minFactor = (int)sqlUtils.ExecuteScalar(string.Format(
"select min(NumOfFacts) from {0}", TableConstants.UserScores));
// this works but not converges
sqlUtils.ExecuteNonQuery(string.Format(
"UPDATE {0} us " +
"SET us.Belief=((1-{3})*us.Belief + " +
"{3}*(SELECT COUNT(*) FROM {1} rk, {2} im " +
"WHERE rk.FactId=im.ItemId AND im.UserID=us.UserId)/" +
"us.NumOfFacts*(us.NumOfFacts-{4})/({5}-{4})), us.Version=us.Version+1",
TableConstants.UserScores,
TableConstants.RepKeyResults,
TableConstants.ItemsMentions,
0.2, minFactor, maxFactor));
}
public static void PrepareDb(MySqlUtils sqlUtils, string category, TopicType topicType)
{
///////////////////////////////////////////////
// reinitiate the Users Scores table
// for each user from table of Users create
// posititve and negative rows
sqlUtils.ExecuteNonQuery(string.Format(
"CREATE TABLE IF NOT EXISTS {0} ( " +
"UserID int(11) unsigned NOT NULL PRIMARY KEY, " +
"Belief double NOT NULL, " +
"Version int(11) NULL, " +
"NumOfFacts int(11) NOT NULL " +
// ", FOREIGN KEY usUserID_fkey (UserID) REFERENCES Users (UserID) ON DELETE CASCADE" +
") ENGINE = MyISAM",
TableConstants.UserScores));
// new users creation in the userscores table
sqlUtils.ExecuteNonQuery(string.Format(
"INSERT INTO {0} (UserId, Belief, Version, NumOfFacts) " +
"(SELECT u.UserID as UserId, {2} as Belief, 1 as Version, " +
"0 as NumOfFacts FROM {1} u " +
"WHERE u.UserID NOT IN (SELECT us.UserId FROM {0} us))",
TableConstants.UserScores, TableConstants.Users, BeliefInitialValue,
TableConstants.ItemsMentions));
// update users number of facts
sqlUtils.ExecuteNonQuery(string.Format(
"UPDATE {0} us, (SELECT im.UserID, COUNT(*) as NumOfItems FROM {1} im GROUP BY im.UserID) s " +
"SET us.NumOfFacts = s.NumOfItems, Version=1 " +
"WHERE s.UserID=us.UserID",
TableConstants.UserScores, TableConstants.ItemsMentions));
////////////////////////////////////////////////////
// creation and initiallization of scored facts table
sqlUtils.ExecuteNonQuery(string.Format(
"CREATE TABLE IF NOT EXISTS {0} ( " +
"ItemID INT(11) unsigned NOT NULL, " +
"TopicID INT(11) unsigned NOT NULL, " +
"Factor int(11) NOT NULL, " +
"Score DOUBLE NOT NULL, " +
"Category varchar(70) COLLATE utf8_bin NOT NULL, " +
"Correctness TINYINT(1) NULL, " +
//"FactName varchar(100) COLLATE utf8_bin NULL, " +
//"FactValue varchar(500) COLLATE utf8_bin NOT NULL, " +
"PRIMARY KEY(ItemID), " +
//"FOREIGN KEY sfItemID_fkey (ItemID) REFERENCES Items (ItemID) ON DELETE CASCADE, " +
"FOREIGN KEY sfItemID_fkey (ItemID) REFERENCES Items (id) ON DELETE CASCADE, " +
"FOREIGN KEY sfTopicID_fkey (TopicID) REFERENCES Topics (TopicID) ON DELETE CASCADE " +
") ENGINE = MyISAM",
TableConstants.ScoredFacts));
// insert into ScoredFacts new facts
sqlUtils.ExecuteNonQuery(String.Format(
"INSERT INTO {0} (ItemId, TopicId, Category, Factor, Score) " +
"SELECT i.ItemId, i.TopicId, '{3}' AS Category, 0 AS Factor, 0 AS Score " +
"FROM {1} t, {2} i WHERE t.TopicId=i.TopicId AND t.Category = '{3}' AND t.TopicType={4} " +
"AND i.ItemId NOT IN (SELECT ItemID FROM {0})",
TableConstants.ScoredFacts, TableConstants.Topics, TableConstants.Items,
category, (int)topicType));
// update all facts Factor
sqlUtils.ExecuteNonQuery(String.Format(
"UPDATE {0} sf, (SELECT im.ItemId, COUNT(im.ID) as Factor FROM {1} im, {2} t " +
"WHERE t.TopicId=im.TopicId AND t.TopicType={3} GROUP BY im.ItemId) s " +
"SET sf.Factor = s.Factor WHERE sf.ItemId = s.ItemId",
TableConstants.ScoredFacts, TableConstants.ItemsMentions,
TableConstants.Topics, (int)topicType));
}
public static void NewScoreCalculation(MySqlUtils sqlUtils)
{
// this works but not converges
sqlUtils.ExecuteNonQuery(string.Format(
"UPDATE {0} us " +
"SET us.Belief=((1-{3})*us.Belief + " +
"{3}*(SELECT COUNT(*) FROM {1} rk, {2} im " +
"WHERE rk.FactId=im.ItemId AND im.UserID=us.UserId)/" +
//"us.Version/us.NumOfFacts)/(1+1/us.Version), us.Version=us.Version+1",
"us.NumOfFacts/us.version), us.Version=us.Version+1",
TableConstants.UserScores,
TableConstants.RepKeyResults,
TableConstants.ItemsMentions,
0.2));
}