public TwoEstimate(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier, category)
{
m_ruleExecuter += new RuleExecuterDelegate(TwoEstimateMethod);
m_ruleInitializer += new RuleExecuterDelegate(internalInit);
Id = TwoEstimateStr;
}
public static void PrintTableSize(string tableName, MySqlUtils sqlUtils)
{
Logger.DebugWriteLine(string.Format(
"Table {0} has {1} elements.",
tableName,
sqlUtils.ExecuteScalar(string.Format("SELECT COUNT(*) FROM {0}", tableName))));
}
public Majority(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier, category)
{
m_ruleInitializer += new RuleExecuterDelegate(internalInit);
m_ruleExecuter += new RuleExecuterDelegate(MajorityFunc);
Id = "MajorityRule";
PrerequisiteRules = new List<Rule>(0);
}
public RepairKeySample(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier, category)
{
m_ruleExecuter += new RuleExecuterDelegate(SampleWithJoin);
m_ruleInitializer += new RuleExecuterDelegate(internalInit);
Id = "RepairKeySample";
PrerequisiteRules = new List<DCF.Lib.Rule>();
}
public Cosine(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier, category)
{
m_ruleExecuter += new RuleExecuterDelegate(CosineMethod);
m_ruleInitializer += new RuleExecuterDelegate(internalInit);
Id = CosineStr;
InvolvedTables = new List<string>(new string[] { TableConstants.UserScores });
AffectedTables = new List<string>();
}
public void init()
{
m_sqlUtils = new MySqlNativeClientUtils(DBUsername, DBPassword, DBName, HostName);
SqlUtils.Connect();
Logger.TraceWriteLine(string.Format("Connected to {0}/{1}@{2} - {3}", DBUsername, DBPassword, DBName, HostName));
m_supplier = new XmlRuleSupplier(SqlUtils);
m_supplier.parseFile(m_filename);
m_cleansingManager = new CleansingManager(m_supplier);
}
public XmlSqlRepairKeyStatement(string stmnt, MySqlUtils utils,
string idFields, string keyFields, string probField, string targetTable)
: base(stmnt, utils)
{
m_idFields = idFields.Split(',');
for (int i = 0; i < m_idFields.Length; i++) m_idFields[i] = m_idFields[i].Trim();
m_keyFields = keyFields;
m_probField = probField;
m_targetTableName = targetTable;
}
public static void PrintDbTable(string tableName, MySqlUtils sqlUtils)
{
if (Logger.ShowTableContents)
{
DataSet ds = new DataSet();
sqlUtils.ExecuteQuery("select * from " + tableName, ds);
ds.Tables[0].TableName = tableName;
Logger.PrintTable(ds.Tables[0]);
}
}
public MultipleAnswersSample(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier)
{
m_ruleExecuter += new RuleExecuterDelegate(SampleWithJoin);
m_ruleInitializer += new RuleExecuterDelegate(internalInit);
Id = "MultipleAnswersSample";
InvolvedTables = new List<string>();
AffectedTables = new List<string>();
PrerequisiteRules = new List<DCF.Lib.Rule>();
Category = category;
}
public void SetQuestionText(MySqlUtils sqlUtils,
string title, string questionText, string paramTableName,
string paramColNames, string queryBody, TopicType topicType)
{
m_sqlUtils = sqlUtils;
m_queryBody = queryBody;
Text = title;
m_topicType = topicType;
DataSet ds = new DataSet();
sqlUtils.ExecuteQuery(string.Format("SELECT {0} FROM {1}", paramColNames, paramTableName), ds);
m_paramNames = paramColNames.Split(new char[] { ' ', ',', ';' }, StringSplitOptions.RemoveEmptyEntries);
m_tableName = paramTableName;
int curPosition = 0;
while (curPosition < questionText.Length)
{
int startParamIndex = questionText.IndexOf('{', curPosition);
if (startParamIndex < 0) // if there is no more paraeters in the string
{
string labelText = questionText.Substring(curPosition);
_flowLayoutPanelQueryText.Controls.Add(new Label() { Text = labelText, AutoSize = true,
Font = m_labelInfo.Font, ForeColor = m_labelInfo.ForeColor,
Margin = m_labelInfo.Margins, BackColor = Color.Transparent });
break;
}
if (startParamIndex > curPosition) // if there is a need in Label
{
string labelText = questionText.Substring(curPosition, startParamIndex - curPosition);
_flowLayoutPanelQueryText.Controls.Add(new Label() { Text = labelText, AutoSize = true,
Font = m_labelInfo.Font, ForeColor = m_labelInfo.ForeColor,
Margin = m_labelInfo.Margins, BackColor = Color.Transparent });
}
int endParamIndex = questionText.IndexOf('}', startParamIndex);
curPosition = endParamIndex + 1;
int curParamInd = int.Parse(questionText.Substring(startParamIndex + 1, endParamIndex - startParamIndex - 1));
BBBNOVA.BNComboBox cb = new BBBNOVA.BNComboBox()
{
Size = m_comboBoxInfo.Size,
Font = m_comboBoxInfo.Font,
ForeColor = Color.White,
BackColor = Color.Black,
DataSource = ds.Tables[0],
DisplayMember = m_paramNames[curParamInd],
Margin = m_comboBoxInfo.Margins,
DropDownWidth = 300
};
cb.SelectedValueChanged += new EventHandler(comboBox_SelectedValueChanged);
m_cbList.Add(cb);
_flowLayoutPanelQueryText.Controls.Add(cb);
}
}
public DanielConvergence(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier)
: base(sqlUtils, ruleSupplier)
{
this.AffectedTables = new List<string>();
this.Id = "DanielConvergence";
this.InvolvedTables = new List<string>() { TableConstants.UserScores };
this.PrerequisiteRules = new List<DCF.Lib.Rule>();
this.RuleType = RuleTypeEnum.System;
this.m_ruleExecuter += new RuleExecuterDelegate(CosineConverged_m_ruleExecuter);
this.m_ruleInitializer += new RuleExecuterDelegate(CosineConverged_m_ruleInitializer);
// initialize the threshold
m_ConvergenceThreshold = CleaningConfiguration.Instance.ConversionDelta;
// initialize convergence count - number of good samples in a row that need to be good in order to decide converence
m_ConvergenceSamplesCount = CleaningConfiguration.Instance.ConversionSamplesCount;
}
/// <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 object CalculateQuality(MySqlUtils sqlUtils)
{
try
{
string innerSelect =
"select a.* from scoredfacts a, " +
"(SELECT topicid, max(Score) as score FROM scoredfacts group by topicid) b " +
"where a.topicid = b.topicid and a.score = b.score group by a.topicid";
string qualityMeasurement = string.Format(
"select " +
"(select count(*) from ({0}) d, correctfacts c where c.itemid=d.itemid) / " +
"(select count(*) from ({0}) e) as Quality", innerSelect
);
object qualityRes = sqlUtils.ExecuteScalar(qualityMeasurement);
Logger.TraceWriteLine(string.Format("The quality of the run is {0}", qualityRes.ToString()));
return qualityRes;
}
catch (Exception)
{
Logger.TraceWriteLine("No correct facts");
}
return 0;
}
private void MainFormMySql_Load(object sender, EventArgs e)
{
using (LoginForm lf = new LoginForm())
{
lf.UserName = ConfigurationManager.AppSettings[UsernameSettingName];
lf.Password = ConfigurationManager.AppSettings[PasswordSettingName];
DialogResult dr = lf.ShowDialog(this);
if (dr == DialogResult.Cancel)
{
Application.Exit();
}
string password = lf.Password;
string username = lf.UserName;
m_sqlUtils = new MySqlNativeClientUtils(username, password,
ConfigurationManager.AppSettings[DbNameSettingName],
ConfigurationManager.AppSettings[HostNameSettingName]);
}
SqlUtils.Connect();
SqlUtils.ExecuteQuery("SELECT * FROM QueryForms", m_dsQueryForms);
_lboxQuestions.DataSource = m_dsQueryForms.Tables[0];
_lboxQuestions.DisplayMember = "FormName";
_lboxQuestions_SelectedIndexChanged(null, null);
}
public SingleIterationSingleAnswersRule(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier, category)
{
}
public XmlRuleSupplier(MySqlUtils utils)
{
SqlUtils = utils;
}
public XmlMySqlRule(MySqlUtils utils, IRuleSupplier ruleSupplier)
: base(utils, ruleSupplier)
{
m_ruleExecuter += new RuleExecuterDelegate(XmlMySqlRuleExecuter);
m_ruleInitializer += new RuleExecuterDelegate(XmlMySqlRuleInitializer);
}
/// <summary>
/// Initializes the cleaning process
/// </summary>
/// <remarks>
/// Connects to the database and creates <see cref="CleansingManager"/>
/// </remarks>
public void InitFlow()
{
m_sqlUtils = new MySqlNativeClientUtils(DBUsername, DBPassword, DBName, HostName);
SqlUtils.Connect();
Logger.TraceWriteLine(string.Format("Connected to {0}/{1}@{2} - {3}", DBUsername, DBPassword, DBName, HostName));
// TODO Demo m_cleansingManager = new CleansingManager(new PaperRuleProvider(SqlUtils));
m_cleansingManager = new CleansingManager(new OfflineCleaningRuleProvider(SqlUtils));
// remove previous run data
try
{
Logger.TraceWriteLine("Truncating table " + TableConstants.ScoredFacts);
SqlUtils.TruncateTable(TableConstants.ScoredFacts);
Logger.TraceWriteLine("Truncating table " + TableConstants.UserScores);
SqlUtils.TruncateTable(TableConstants.UserScores);
}
catch (MySqlException)
{
Logger.TraceWriteLine(string.Format("Tables {0} and {1} do not exit",
TableConstants.ScoredFacts, TableConstants.UserScores));
}
}
private void Dispose(bool disposing)
{
if (disposing)
{
if (m_sqlUtils != null)
{
m_sqlUtils.Dispose();
m_sqlUtils = null;
}
}
}
public XmlSqlStatement(string statement, MySqlUtils utils)
{
SqlStatement = statement;
SqlUtils = utils;
}
/// <summary>
/// generates ItemsMEntions and Users table
/// </summary>
/// <remarks>
/// The algorithm of User activity emulation is as follows:
/// 1) create a User with probability p to give correct answer. (p is choosen according to users profiles)
/// 2) draw a topic at random from topics that where not choosen by this user
/// 3.0) choose a possible item for this topic with probability p from Correct Facts table and
/// with probability of (1-p) from Items fact that is not correct
/// 3.1) for topic with multiple answers choose randomly n from the number possible correct answers and
/// run (3.0) n times for current topic
/// 4) add the new row to ItemsMentions table
/// </remarks>
public void SimulateUsersActivity(
DataTable dtTopics, DataTable dtItems,
DataTable dtCorrectFacts, DataTable dtItemsMentions, DataTable dtUsers,
MySqlUtils sqlUtils)
{
Logger.DebugWriteLine("Simulating users activity...");
Logger.DebugIndent();
Random userRnd = new Random();
Random iterationRnd = new Random();
Random confidenceRnd = new Random();
List<Pair<double, double>> userProfile = m_initSection.GetUserProfiles();
long currentFactsNum = 0;
int topicsCount = (from r in dtItems.AsEnumerable() select r.Field<uint>("TopicId")).Distinct().Count();
Dictionary<int, int> topicId2CorrectCount = CreateTopicId2CorrectCountMap(dtCorrectFacts);
int correctFactsCount = dtCorrectFacts.Rows.Count;
int numberOfUsers = (int)m_initSection.NumberOfFacts / topicsCount + 10;
List<User> listOfUsers = new List<User>(numberOfUsers);
int currentUser = 0;
Dictionary<int, int> restrictedTopics =
DrawRestrictedTopics(m_initSection.GetTopicsVariabilityProfiles(), topicsCount);
// stats
int[] answerStats = new int[2] { 0, 0 };
// first populate the users
for (int i = 0; i < numberOfUsers; i++)
{
CreateUser(userRnd, userProfile, listOfUsers, topicsCount);
}
PopulateUsersTable(listOfUsers, dtUsers);
sqlUtils.RePopulateExistingTable(dtUsers); // add users into DB
// now populate items mentions
while (currentFactsNum < m_initSection.NumberOfFacts)
{
int fixedUser = currentUser + numberOfUsers;
User user = listOfUsers[currentUser++ % numberOfUsers];
// get a topic and move it to used
int topicIndex;
Logger.Assert(user.GetNextTopic(out topicIndex), "User is exhausted");
DataRow topicRow = dtTopics.Rows[topicIndex];
int topicId = (int)topicRow.Field<uint>("TopicId");
// define iteration for the current topic type
int iterationCount = 1;
if (TopicType.MultipleChoiseAnswer == (TopicType)topicRow.Field<UInt16>("TopicType"))
{
iterationCount = iterationRnd.Next(
topicId2CorrectCount[topicId]);
}
int [] answers = new int[2] {0, 0};
for (int i = 0; i < iterationCount; i++)
{
double randomNumber = confidenceRnd.NextDouble();
if (randomNumber <= user.Confidence)
{
answers[0]++; // correct
}
else
{
answers[1]++; // incorrect
}
}
if (answers[0] > 0) // correct answers
PopulateCorrectAnswers(dtCorrectFacts, dtItemsMentions, user, topicId, answers[0]);
if (answers[1] > 0) // incorrect answers
populateIncorrectAnswers(dtItems, dtItemsMentions, user, topicId,
topicId2CorrectCount[topicId], answers[1], restrictedTopics);
currentFactsNum += answers[0] + answers[1];
// stats
answerStats[0] += answers[0];
answerStats[1] += answers[1];
if (dtItemsMentions.Rows.Count > LimitOfRows)
{
sqlUtils.PopulateTableToDb(dtItemsMentions);
dtItemsMentions.Rows.Clear();
}
}
sqlUtils.PopulateTableToDb(dtItemsMentions); // populate the rest of the items mention
Logger.TraceWriteLine(string.Format(
"Database contain {0} facts on {1} topics with {2} correct and {3} incorrect facts - DB correct ratio is {4}",
currentFactsNum, topicsCount, answerStats[0], answerStats[1], answerStats[0] * 1.0 / currentFactsNum));
Logger.DebugUnindent();
Logger.DebugWriteLine("Done!");
}
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 PaperRuleProvider(MySqlUtils utils)
{
m_sqlUstils = utils;
}
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));
}
protected MySqlRule(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier)
: base(ruleSupplier)
{
SqlUtils = sqlUtils;
}
public OfflineCleaningRuleProvider(MySqlUtils utils)
{
m_sqlUstils = utils;
}
public XmlSqlEventStatement(string statement, MySqlUtils utils, Rule.RuleStoppingDelegate raiseEvent)
: base(statement, utils)
{
SqlUtils = utils;
m_event = raiseEvent;
}
public DcfRule(MySqlUtils sqlUtils, IRuleSupplier ruleSupplier, string category)
: base(sqlUtils, ruleSupplier)
{
Category = category;
}
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));
}
