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