private void PersistColumnBatch(ICollection <string> values, string tableName, string columnName) { string replacedName; string output; MySqlCommand cmd; StringBuilder insertValues = new StringBuilder($"INSERT INTO {tableName}({columnName}) VALUES"); int i = 1; int j = 0; try { conn.Open(); MySqlTransaction transaction = conn.BeginTransaction(); foreach (string s in values) { output = $"Writing value to column {tableName}.{columnName} ({i++} / {values.Count})."; PrintUtils.PrintRow(output, 0, 0); replacedName = s.Replace("'", "''"); insertValues.Append($"('{replacedName}')"); if (values.Count == 1 || (j > 0 && (j % BatchQueryLimit == 0 || j == values.Count - 1))) { insertValues.Append(";"); cmd = new MySqlCommand(insertValues.ToString(), conn); cmd.ExecuteNonQuery(); transaction.Commit(); transaction = conn.BeginTransaction(); insertValues = new StringBuilder($"INSERT INTO {tableName}({columnName}) VALUES"); } else { insertValues.Append(","); } j++; } } catch (Exception e) { PrintUtils.Report(e); } conn.Close(); }
public bool IsConnected() { bool isOpen = false; try { conn.Open(); isOpen = conn.State == ConnectionState.Open; } catch (Exception e) { PrintUtils.Report(e); } conn.Close(); return(isOpen); }
private void Clean(string tableName, string idColumn) { try { Console.WriteLine("Cleaning database..."); conn.Open(); int currentId = 1; int deleted = 0; string checkMin = $"SELECT MIN({idColumn}) FROM {tableName}"; MySqlCommand minCmd = new MySqlCommand(checkMin, conn); MySqlDataReader rdr = minCmd.ExecuteReader(); while (rdr.Read() && rdr[0] != DBNull.Value) { currentId = (int)rdr[0]; } rdr.Close(); string checkExists = $"SELECT {idColumn} FROM {tableName} WHERE {idColumn} = {currentId}"; string deleteBatch = $"DELETE FROM {tableName} LIMIT {BatchQueryLimit}"; string resetInc = $"ALTER TABLE {tableName} AUTO_INCREMENT = 1"; string output; MySqlCommand existsCmd = new MySqlCommand(checkExists, conn); MySqlCommand deleteCmd = new MySqlCommand(deleteBatch, conn); rdr = existsCmd.ExecuteReader(); deleteCmd.CommandTimeout = TimeOut; while (rdr.Read()) { rdr.Close(); deleteCmd.ExecuteNonQuery(); currentId += BatchQueryLimit; deleted += BatchQueryLimit; checkExists = $"SELECT {idColumn} FROM {tableName} WHERE {idColumn} = {currentId}"; existsCmd = new MySqlCommand(checkExists, conn); rdr = existsCmd.ExecuteReader(); output = $"Deleted {deleted} rows."; PrintUtils.PrintRow(output, 0, 0); } rdr.Close(); MySqlCommand resetCmd = new MySqlCommand(resetInc, conn); resetCmd.ExecuteNonQuery(); output = $"Database cleaned."; PrintUtils.PrintRow(output, 0, 0); } catch (Exception e) { PrintUtils.Report(e); } conn.Close(); }
public void PersistSeriesTags(IDictionary <string, HashSet <string> > index) { int i = 1; string output; MySqlCommand cmd; MySqlDataReader rdr; MySqlTransaction transaction = null; PersistColumnBatch(index.Keys.ToList(), "series", "name"); Dictionary <string, int> seriesDict = new Dictionary <string, int>(); Dictionary <string, int> tagDict = new Dictionary <string, int>(); string querySeries = "SELECT name, id FROM series"; string queryTags = "SELECT name, id FROM tags"; output = $"Preparing ID dictionaries."; PrintUtils.PrintRow(output, 0, 0); try { conn.Open(); cmd = new MySqlCommand(queryTags, conn); rdr = cmd.ExecuteReader(); while (rdr.Read()) { tagDict.Add((string)rdr[0], (int)rdr[1]); } rdr.Close(); cmd = new MySqlCommand(querySeries, conn); rdr = cmd.ExecuteReader(); while (rdr.Read()) { seriesDict.Add((string)rdr[0], (int)rdr[1]); } rdr.Close(); StringBuilder insertSeriesTags = new StringBuilder("INSERT INTO series_tags(tag_id, series_id) VALUES"); int j = 0; int k = 0; transaction = conn.BeginTransaction(); foreach (string seriesName in index.Keys) { int seriesId = seriesDict[seriesName]; output = $"Writing series ({i++} / {index.Keys.Count})."; PrintUtils.PrintRow(output, 0, 0); foreach (string tagName in index[seriesName]) { int tagId = tagDict[tagName]; insertSeriesTags.Append($"({tagId}, {seriesId}),"); j++; } if (j >= BatchQueryLimit || k == index.Keys.Count - 1) { insertSeriesTags.Remove(insertSeriesTags.Length - 1, 1); insertSeriesTags.Append(";"); cmd = new MySqlCommand(insertSeriesTags.ToString(), conn); cmd.CommandTimeout = TimeOut; cmd.ExecuteNonQuery(); transaction.Commit(); transaction = conn.BeginTransaction(); insertSeriesTags = new StringBuilder("INSERT INTO series_tags(tag_id, series_id) VALUES"); j = 0; } k++; } } catch (Exception e) { PrintUtils.Report(e); if (transaction != null) { transaction.Rollback(); } } conn.Close(); }
private void CountPosts(string tableName, string idColumn, string countColumn, string countQuery) { try { string output = "Computing post counts..."; int tagsOffset = 0; PrintUtils.PrintRow(output, 0, 0); conn.Open(); while (true) { string getTagIDs = $"SELECT {idColumn} FROM {tableName} LIMIT {BatchQueryLimit} OFFSET {tagsOffset}"; StringBuilder sb = new StringBuilder(); List <int> ids = new List <int>(); MySqlCommand cmd = new MySqlCommand(getTagIDs, conn); cmd.CommandTimeout = TimeOut; MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { ids.Add((int)rdr[0]); sb.Append((int)rdr[0] + ","); } if (!rdr.HasRows) { break; } rdr.Close(); sb.Remove(sb.Length - 1, 1); string countQueryUpdated = countQuery.Replace("%", sb.ToString()); cmd = new MySqlCommand(countQueryUpdated, conn); cmd.CommandTimeout = TimeOut; string updateCount; var idCounts = new Dictionary <int, long>(); rdr = cmd.ExecuteReader(); while (rdr.Read()) { idCounts.Add((int)rdr[0], (long)rdr[1]); } rdr.Close(); foreach (int id in ids) { if (!idCounts.ContainsKey(id)) { idCounts.Add(id, 0); } } foreach (var pair in idCounts) { output = $"Updating {idColumn} {pair.Key} with {countColumn} = {pair.Value}."; PrintUtils.PrintRow(output, 0, 0); updateCount = $"UPDATE {tableName} SET {countColumn} = {pair.Value} WHERE {idColumn} = {pair.Key}"; cmd = new MySqlCommand(updateCount, conn); cmd.ExecuteNonQuery(); } tagsOffset += BatchQueryLimit; output = $"Processed {BatchQueryLimit} posts ({tagsOffset} done)."; PrintUtils.PrintRow(output, 0, 0); } } catch (Exception e) { PrintUtils.Report(e); } conn.Close(); }
static int Main(string[] args) { string usageString = "Usage: LobitaDownloader index | backup | recover | clean>"; try { Resources.SystemLogger = new Logger("syslogs"); XmlConfigManager config = new XmlConfigManager(Resources.ProductionDirectory, Resources.ConfigFile); DbIndexPersistence persistence = new DbIndexPersistence(config); XmlIndexBackup backup = new XmlIndexBackup(config); IndexBuilder indexBuilder = new IndexBuilder(persistence, backup, config); switch (args[0]) { case "index": if (CheckConnections(persistence, backup)) { indexBuilder.BuildIndex(); } else { return(-1); } break; case "backup": if (CheckConnections(persistence, backup)) { indexBuilder.BackupRestore(); } else { return(-1); } break; case "recover": if (CheckConnections(persistence, backup)) { indexBuilder.Recover(); } else { return(-1); } break; case "clean": if (CheckConnections(persistence, backup)) { indexBuilder.CleanUp(); } else { return(-1); } break; default: Console.WriteLine(usageString); return(-1); } } catch (Exception e) { PrintUtils.Report(e); return(-1); } Resources.SystemLogger.Log("Application terminated successfully."); return(0); }