public async Task <(long MinDownloadedAt, string[] MediaPath)> GetMediaPath(long downloaded_at)
{
using (var cmd = new MySqlCommand(@"SELECT
m.media_id, mt.media_url
FROM media m
JOIN media_downloaded_at md ON m.media_id = md.media_id
JOIN media_text mt ON m.media_id = mt.media_id
WHERE md.downloaded_at <= @downloaded_at
ORDER BY md.downloaded_at DESC
LIMIT @limit"))
{
cmd.Parameters.AddWithValue("@downloaded_at", downloaded_at);
cmd.Parameters.AddWithValue("@limit", 1000);
var ret = new List <string>();
long minDownloadedAt = long.MaxValue;
await ExecuteReader(cmd, (r) =>
{
long down = r.GetInt64(0);
if (down < minDownloadedAt)
{
minDownloadedAt = down;
}
ret.Add(MediaFolderPath.ThumbPath(down, r.GetString(1)));
}).ConfigureAwait(false);
return(minDownloadedAt, ret.ToArray());
}
}
static async Task Main(string[] args)
{
//多重起動防止
//CheckOldProcess.CheckandExit();
var config = Config.Instance;
var db = new DBHandler();
await db.NullifyPidAll().ConfigureAwait(false);
var child = new ChildProcessHandler();
//子プロセスが複数あったらいるかもしれないけど今は無用(´・ω・`)
//LockerHandler.CheckAndStart();
//画像保存用フォルダはここで作る
MediaFolderPath.MkdirAll();
bool GetMyTweet = false; //後から追加されたアカウントはstreamer側で自分のツイートを取得させる
Stopwatch LoopWatch = new Stopwatch();
while (true)
{
LoopWatch.Restart();
long[] users = await db.SelectNewToken().ConfigureAwait(false);
int NeedProcessCount = (int)(await db.CountToken().ConfigureAwait(false) / config.crawlparent.AccountLimit + 1);
if (users.Length > 0)
{
Console.WriteLine("Assigning {0} tokens", users.Length);
//アカウント数からして必要な個数のtwidownを起動する
while (NeedProcessCount > child.Count)
{
int newpid = child.Start();
if (newpid < 0)
{
await Task.Delay(60000).ConfigureAwait(false); continue;
} //雑すぎるエラー処理
Console.WriteLine("New PID: {0}", newpid);
}
//あとはボコボコ突っ込む
await child.AssignToken(users, GetMyTweet).ConfigureAwait(false);
}
GetMyTweet = true;
//ここでプロセス間通信を監視して返事がなかったら再起動する
do
{
await child.DeleteDead().ConfigureAwait(false);
//LockerHandler.CheckAndStart();
await Task.Delay(10000).ConfigureAwait(false);
} while (LoopWatch.ElapsedMilliseconds < 60000);
LoopWatch.Stop();
}
}
//しばらくツイートがないアカウントのprofile_imageを消す
public async Task RemoveOldProfileImage()
{
DriveInfo drive = new DriveInfo(config.crawl.PictPathProfileImage);
int RemovedCount = 0;
const int BulkUnit = 1000;
const string head = @"DELETE FROM user_updated_at WHERE user_id IN";
string BulkUpdateCmd = BulkCmdStrIn(BulkUnit, head);
//Console.WriteLine("{0} / {1} MB Free.", drive.AvailableFreeSpace >> 20, drive.TotalSize >> 20);
try
{
var Table = new List <(long user_id, string profile_image_url, bool is_default_profile_image)>(BulkUnit);
while (drive.TotalFreeSpace < drive.TotalSize / 16)
{
using (var cmd = new MySqlCommand(@"SELECT
user_id, profile_image_url, is_default_profile_image
FROM user
JOIN user_updated_at USING (user_id)
WHERE profile_image_url IS NOT NULL
ORDER BY updated_at LIMIT @limit;"))
{
cmd.Parameters.AddWithValue("@limit", BulkUnit);
if (!await ExecuteReader(cmd, (r) => Table.Add((r.GetInt64(0), r.GetString(1), r.GetBoolean(2)))))
{
return;
}
}
if (Table.Count < BulkUnit)
{
break;
}
foreach (var row in Table)
{
if (!row.is_default_profile_image)
{
File.Delete(MediaFolderPath.ProfileImagePath(row.user_id, row.is_default_profile_image, row.profile_image_url));
}
}
using (var upcmd = new MySqlCommand(BulkUpdateCmd))
{
for (int n = 0; n < Table.Count; n++)
{
upcmd.Parameters.Add("@" + n.ToString(), DbType.Int64).Value = Table[n].user_id;
}
RemovedCount += await ExecuteNonQuery(upcmd);
}
//Console.WriteLine("{0} Icons removed", RemovedCount);
//Console.WriteLine("{0} / {1} MB Free.", drive.AvailableFreeSpace >> 20, drive.TotalSize >> 20);
Table.Clear();
}
}
catch (Exception e) { Console.WriteLine(e); return; }
Console.WriteLine("{0} Icons removed.", RemovedCount);
}
public async Task RemoveOldMedia()
{
DriveInfo drive = new DriveInfo(config.crawl.PictPaththumb);
int RemovedCountFile = 0;
const int BulkUnit = 1000;
//Console.WriteLine("{0} / {0} MB Free.", drive.AvailableFreeSpace >> 20, drive.TotalSize >> 20);
try
{
var Table = new List <(long media_id, string media_url)>(BulkUnit);
while (drive.TotalFreeSpace < drive.TotalSize / 16)
{
using (MySqlCommand cmd = new MySqlCommand(@"(SELECT
m.media_id, mt.media_url
FROM media_downloaded_at md
JOIN media m on md.media_id = m.media_id
JOIN media_text mt ON m.media_id = mt.media_id
ORDER BY downloaded_at
LIMIT @limit)
ORDER BY media_id;"))
{
cmd.Parameters.AddWithValue("@limit", BulkUnit);
if (!await ExecuteReader(cmd, (r) => Table.Add((r.GetInt64(0), r.GetString(1)))))
{
return;
}
}
if (Table.Count < BulkUnit)
{
break;
}
foreach (var row in Table)
{
File.Delete(MediaFolderPath.ThumbPath(row.media_id, row.media_url));
}
using (var Cmd = new MySqlCommand(BulkCmdStrIn(Table.Count, @"DELETE FROM media_downloaded_at WHERE media_id IN")))
{
for (int i = 0; i < Table.Count; i++)
{
Cmd.Parameters.Add("@" + i.ToString(), DbType.Int64).Value = Table[i].media_id;
}
await ExecuteNonQuery(Cmd).ConfigureAwait(false);
}
RemovedCountFile += Table.Count;
//Console.WriteLine("{0} Media removed", RemovedCountFile);
//Console.WriteLine("{0} / {1} MB Free.", drive.AvailableFreeSpace >> 20, drive.TotalSize >> 20);
Table.Clear();
}
}
catch (Exception e) { Console.WriteLine(e); return; }
Console.WriteLine("{0} Old Media removed.", RemovedCountFile);
}
public async Task <IActionResult> thumb(string FileName)
{
if (!long.TryParse(Path.GetFileNameWithoutExtension(FileName), out long media_id))
{
return(StatusCode(400));
}
//まずは鯖内のファイルを探す 拡張子はリクエストURLを信頼して手を抜く
string localmedia = MediaFolderPath.ThumbPath(media_id, FileName);
if (System.IO.File.Exists(localmedia))
{
return(PhysicalFile(localmedia, GetMime(FileName), true));
}
//鯖内にファイルがなかったのでtwitterから横流しする
var MediaInfo = await DB.SelectThumbUrl(media_id).ConfigureAwait(false);
if (MediaInfo == null)
{
return(StatusCode(404));
}
var ret = await Download(MediaInfo.Value.media_url + (MediaInfo.Value.media_url.IndexOf("twimg.com") >= 0 ? ":thumb" : ""),
MediaInfo.Value.tweet_url).ConfigureAwait(false);
if (RemovedStatusCode(ret.StatusCode))
{
Removed.Enqueue(MediaInfo.Value.source_tweet_id);
}
if (ret.FileBytes != null)
{
//画像の取得に成功したわけだし保存しておきたい
StoreMediaBlock.Post((MediaInfo.Value, ret.FileBytes));
return(File(ret.FileBytes, GetMime(FileName)));
}
else
{
return(StatusCode((int)ret.StatusCode));
}
}
//ツイートが削除されて参照されなくなった画像を消す
public async Task RemoveOrphanMedia()
{
int RemovedCount = 0;
const int BulkUnit = 1000;
try
{
var Table = new List <(long media_id, string media_url)>(BulkUnit);
do
{
Table.Clear(); //ループ判定が後ろにあるのでここでやるしかない
using (MySqlCommand cmd = new MySqlCommand(@"SELECT m.media_id, mt.media_url
FROM media m
LEFT JOIN media_downloaded_at md ON m.media_id = md.media_id
JOIN media_text mt ON m.media_id = mt.media_id
WHERE m.source_tweet_id IS NULL
AND (md.downloaded_at IS NULL OR md.downloaded_at < @downloaded_at)
ORDER BY m.media_id
LIMIT @limit;"))
{
//ダウンロードしたての画像は除く
cmd.Parameters.Add("@downloaded_at", MySqlDbType.Int64).Value = DateTimeOffset.UtcNow.ToUnixTimeSeconds() - 600;
cmd.Parameters.AddWithValue("@limit", BulkUnit);
if (!await ExecuteReader(cmd, (r) => Table.Add((r.GetInt64(0), r.GetString(1)))))
{
return;
}
}
if (Table.Count < 1)
{
break;
}
var DeleteMediaBlock = new ActionBlock <(long media_id, string media_url)>(async(row) =>
{
File.Delete(MediaFolderPath.ThumbPath(row.media_id, row.media_url));
using var DeleteCmd = new MySqlCommand(@"DELETE FROM media WHERE media_id = @media_id");
using var DeleteCmd2 = new MySqlCommand(@"DELETE FROM media_text WHERE media_id = @media_id");
DeleteCmd.Parameters.Add("@media_id", MySqlDbType.Int64).Value = row.media_id;
DeleteCmd2.Parameters.Add("@media_id", MySqlDbType.Int64).Value = row.media_id;
int deleted = await ExecuteNonQuery(new[] { DeleteCmd, DeleteCmd2 }).ConfigureAwait(false);
if (deleted > 0)
{
Interlocked.Add(ref RemovedCount, deleted >> 1);
}
}, new ExecutionDataflowBlockOptions()
{
MaxDegreeOfParallelism = Environment.ProcessorCount
});
foreach (var row in Table)
{
DeleteMediaBlock.Post(row);
}
DeleteMediaBlock.Complete();
await DeleteMediaBlock.Completion.ConfigureAwait(false);
//Console.WriteLine("{0} Orphan Media removed.", RemovedCount);
} while (Table.Count >= BulkUnit);
}
catch (Exception e) { Console.WriteLine(e); return; }
Console.WriteLine("{0} Orphan Media removed.", RemovedCount);
}
/// <summary>
/// ユーザーのアイコンを実際に探す
/// 鯖内 > 横流し の優先度
/// </summary>
/// <param name="FileName">アカウントID.拡張子</param>
/// <returns></returns>
async Task <(int StatusCode, Stream Data)> FindProfileImage(string FileName)
{
//ファイル名の先頭が"_"だったら初期アイコンのURLとみなす
if (FileName.StartsWith('_'))
{
string trimmedname = MediaFolderPath.DefaultProfileImagePath(FileName.Substring(1));
if (System.IO.File.Exists(MediaFolderPath.DefaultProfileImagePath(trimmedname)))
{
return(StatusCodes.Status200OK, System.IO.File.OpenRead(trimmedname));
}
else
{
return(StatusCodes.Status404NotFound, null);
}
}
//↑以外はuser_idとみなす
if (!long.TryParse(Path.GetFileNameWithoutExtension(FileName), out long user_id))
{
return(StatusCodes.Status400BadRequest, null);
}
//まずは鯖内のファイルを探す 拡張子はリクエストURLを信頼して手を抜く
//初期アイコンではないものとして探す
string localmedia = MediaFolderPath.ProfileImagePath(user_id, false, FileName);
if (System.IO.File.Exists(localmedia))
{
return(StatusCodes.Status200OK, System.IO.File.OpenRead(localmedia));
}
var ProfileImageInfo = await DB.SelectProfileImageUrl(user_id).ConfigureAwait(false);
if (ProfileImageInfo == null)
{
return(StatusCodes.Status404NotFound, null);
}
//初期アイコンなら改めて鯖内を探す(通常はここには来ない)
if (ProfileImageInfo.Value.is_default_profile_image)
{
string defaulticon = MediaFolderPath.ProfileImagePath(user_id, true, ProfileImageInfo.Value.profile_image_url);
if (System.IO.File.Exists(defaulticon))
{
return(StatusCodes.Status200OK, System.IO.File.OpenRead(defaulticon));
}
}
//鯖内にファイルがなかったのでtwitterから横流しする
var downloaded = await Download(ProfileImageInfo.Value.profile_image_url, ProfileImageInfo.Value.tweet_url).ConfigureAwait(false);
if (downloaded.FileBytes != null)
{
//画像の取得に成功したわけだし保存しておきたい
//初期アイコンはいろいろ面倒なのでここではやらない
if (!ProfileImageInfo.Value.is_default_profile_image)
{
StoreProfileImageBlock.Post((ProfileImageInfo.Value, downloaded.FileBytes));
}
return(StatusCodes.Status200OK, new MemoryStream(downloaded.FileBytes, false));
}
else
{
return((int)downloaded.StatusCode, null);
}
}
/// <summary>
/// こいつのツイートを全消しする
/// </summary>
/// <param name="user_id"></param>
/// <returns></returns>
public async Task DeleteUser(long user_id)
{
const int DeleteUnit = 1000;
//ツイートを次々消すやつ
var RemoveTweetBlock = new ActionBlock <long>(async(tweet_id) =>
{
Counter.TweetToDelete.Increment();
using (var cmd = new MySqlCommand(@"DELETE FROM tweet WHERE tweet_id = @tweet_id;"))
using (var cmd2 = new MySqlCommand(@"DELETE FROM tweet_text WHERE tweet_id = @tweet_id;"))
{
cmd.Parameters.Add("@tweet_id", MySqlDbType.Int64).Value = tweet_id;
cmd2.Parameters.Add("@tweet_id", MySqlDbType.Int64).Value = tweet_id;
if (await ExecuteNonQuery(new[] { cmd, cmd2 }).ConfigureAwait(false) > 0)
{
Counter.TweetDeleted.Increment();
}
}
}, new ExecutionDataflowBlockOptions()
{
MaxDegreeOfParallelism = Environment.ProcessorCount, BoundedCapacity = DeleteUnit
});
long LastTweetId = 0;
var IdList = new Queue <long>();
//まずはツイートを消す
while (true)
{
IdList.Clear();
using (var cmd = new MySqlCommand(@"SELECT
tweet_id
FROM tweet
WHERE user_id = @user_id AND tweet_id > @tweet_id
ORDER BY tweet_id LIMIT @limit;"))
{
cmd.Parameters.Add("user_id", MySqlDbType.Int64).Value = user_id;
cmd.Parameters.Add("tweet_id", MySqlDbType.Int64).Value = LastTweetId;
cmd.Parameters.Add("limit", MySqlDbType.Int64).Value = (long)DeleteUnit;
await ExecuteReader(cmd, (r) =>
{
long id = r.GetInt64(0);
IdList.Enqueue(id);
LastTweetId = id;
Counter.LastTweetID = id;
}).ConfigureAwait(false);
if (IdList.Count == 0)
{
break;
}
while (IdList.TryDequeue(out long id))
{
await RemoveTweetBlock.SendAsync(id);
LastTweetId = id;
}
}
}
RemoveTweetBlock.Complete();
await RemoveTweetBlock.Completion.ConfigureAwait(false);
//アイコンも忘れずに消す
using (var cmd = new MySqlCommand(@"SELECT
profile_image_url, is_default_profile_image
FROM user
JOIN user_updated_at USING (user_id)
WHERE user_id = @user_id;"))
{
string profile_image_url = null;
bool is_default_profile_image = true;
cmd.Parameters.Add("user_id", MySqlDbType.Int64).Value = user_id;
await ExecuteReader(cmd, (r) => { profile_image_url = r.GetString(0); is_default_profile_image = r.GetBoolean(1); }).ConfigureAwait(false);
if (profile_image_url != null && !is_default_profile_image)
{
File.Delete(MediaFolderPath.ProfileImagePath(user_id, is_default_profile_image, profile_image_url));
}
}
using (var cmd = new MySqlCommand(@"DELETE FROM user WHERE user_id = @user_id;"))
{
cmd.Parameters.Add("user_id", MySqlDbType.Int64).Value = user_id;
await ExecuteNonQuery(cmd).ConfigureAwait(false);
}
}
