Skip to content

Instantly share code, notes, and snippets.

@mohemohe
Last active August 29, 2015 13:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mohemohe/9587862 to your computer and use it in GitHub Desktop.
Save mohemohe/9587862 to your computer and use it in GitHub Desktop.
ある時点よりも前の日付に作成され、かつ自分のアカウントに関連していないテーブルを削除したい感じ
if (IsNotWriting() == true)
{
if ((DataCount = GetTotalRecords("Status")) > LeaveData)
{
using (var sqlc = new SQLiteConnection("Data Source=" + _DBdir + _DBfile))
{
sqlc.Open();
using (SQLiteCommand cmd = sqlc.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Status ORDER BY julianday(CreatedAt)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT CreatedAt FROM Status LIMIT 1 OFFSET " + (DataCount - LeaveData);
TargetDT = cmd.ExecuteScalar().ToString();
DateTime dt = DateTime.Parse(TargetDT);
TargetDT = dt.ToString("yyyy-MM-dd HH:mm:ss");
if (!(System.IO.File.Exists(_DBdir + _DBlock)))
{
cmd.CommandText = "DELETE FROM Status WHERE julianday(CreatedAt) < julianday((datetime('" + TargetDT + "')))";
}
else
{
Console.WriteLine("Krile is running. Skip reduce some tables.");
// 動かないです
cmd.CommandText = "DELETE FROM Status WHERE julianday(CreatedAt) < julianday((datetime('" + TargetDT + "')))) AND NOT (RetweeterId IN(SELECT Id FROM Accounts) OR RetweetOriginalUserId IN(SELECT Id FROM Accounts) OR InReplyToOrRecipientUserId IN(SELECT Id FROM Accounts))";
}
cmd.ExecuteNonQuery();
}
sqlc.Close();
}
Console.WriteLine("Status reduced.");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment