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