public static async Task <List <VVideoHistory> > GetVideoHistoryView(this IDbControl control, int order)
{
var results = new List <VVideoHistory>();
var sql = new StringBuilder();
sql.AppendLine($"SELECT id, tick, count");
sql.AppendLine($"FROM (");
sql.AppendLine($" SELECT ifnull(id, '') id, ifnull(max(tick), 0) tick, count(id) count");
sql.AppendLine($" FROM video_history");
sql.AppendLine($" GROUP BY id");
sql.AppendLine($")");
sql.AppendLine($"ORDER BY {order} desc");
using (var reader = await control.ExecuteReaderAsync(sql.ToString()))
{
while (await reader.ReadAsync())
{
results.Add(new VVideoHistory(
reader.GetString(0), // id
reader.GetInt64(1), // tick
reader.GetInt64(2) // count
));
}
}
return(results);
}
public static async Task <int> InsertOrReplaceVideoHistory(this IDbControl control, params TVideoHistory[] views)
{
var columns = new[] { "id", "tick" };
int result = 0;
foreach (var chunk in views.Chunk(500))
{
var sql = new StringBuilder();
sql.AppendLine($"INSERT OR REPLACE INTO video_history (");
sql.AppendLine(columns.GetString(","));
sql.AppendLine(")");
sql.AppendLine(
chunk
.Select(data => $"SELECT '{data.VideoId}', {data.Tick}")
.GetString(" UNION ALL ")
);
// ﾊﾟﾗﾒｰﾀ設定
result += await control.ExecuteNonQueryAsync(sql.ToString());
}
return(result);
}
public static async Task <int> InsertOrReplaceSetting(this IDbControl control, params TSetting[] settings)
{
var columns = new[] { "key", "value", "description" };
int result = 0;
foreach (var chunk in settings.Chunk(500))
{
var sql = new StringBuilder();
sql.AppendLine($"INSERT OR REPLACE INTO setting (");
sql.AppendLine(columns.GetString(","));
sql.AppendLine(")");
sql.AppendLine(
chunk
.Select(data => $"SELECT {(int)data.Key}, '{data.Value}', '{data.Description}'")
.GetString(" UNION ALL ")
);
// ﾊﾟﾗﾒｰﾀ設定
result += await control.ExecuteNonQueryAsync(sql.ToString());
}
return(result);
}
public static async Task <int> InitializeSetting(this IDbControl control)
{
// 既に登録されている設定情報を取得
var settings1 = await control.GetSetting();
// 未登録時に登録する設定情報
var settings2 = new[]
{
new TSetting(SettingKeys.MailAddress, ""),
new TSetting(SettingKeys.Password, ""),
new TSetting(SettingKeys.Browser, @"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"),
new TSetting(SettingKeys.ComboHistorySort, "0"),
new TSetting(SettingKeys.ComboRankGenre, "0"),
new TSetting(SettingKeys.ComboRankPeriod, "0"),
new TSetting(SettingKeys.ComboVMylistSort, "0"),
};
// 結合する
var settings3 = settings1
.Concat(settings2.Where(s2 => !settings1.Contains(s2)))
.ToArray();
// 全て登録
return(await control.InsertOrReplaceSetting(settings3));
}
private static async Task CreateTemporaryHistory(this IDbControl control)
{
var sql = new StringBuilder();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS temporary_history (");
sql.AppendLine($" id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (id)");
sql.AppendLine($")");
await control.ExecuteNonQueryAsync(sql.ToString());
}
private static async Task CreateSetting(this IDbControl control)
{
var sql = new StringBuilder();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS setting (");
sql.AppendLine($" key INTEGER NOT NULL,");
sql.AppendLine($" value TEXT NOT NULL,");
sql.AppendLine($" description TEXT NOT NULL,");
sql.AppendLine($"PRIMARY KEY (key)");
sql.AppendLine($")");
await control.ExecuteNonQueryAsync(sql.ToString());
}
public static async Task Initialize(this IDbControl control)
{
// 設定情報テーブルの作成
await control.CreateSetting();
// 設定情報テーブルに初期情報を登録する
await control.InitializeSetting();
// 視聴ﾃｰﾌﾞﾙの作成
await control.CreateVideoHistory();
// お気に入りﾃｰﾌﾞﾙの作成
await control.CreateFavorite();
// お気に入り履歴ﾃｰﾌﾞﾙの作成
await control.CreateTemporaryHistory();
}
public static async Task <int> DeleteVideoHistory(this IDbControl control, params string[] views)
{
var columns = new[] { "id", "tick" };
int result = 0;
foreach (var chunk in views.Chunk(500))
{
var sql = new StringBuilder();
sql.AppendLine($"DELETE FROM video_history ");
sql.AppendLine($"WHERE id IN (");
sql.AppendLine(chunk.Select(id => $"'{id}'").GetString(","));
sql.AppendLine($")");
// ﾊﾟﾗﾒｰﾀ設定
result += await control.ExecuteNonQueryAsync(sql.ToString());
}
return(result);
}
public static async Task <List <TVideoHistory> > GetVideoHistory(this IDbControl control)
{
var results = new List <TVideoHistory>();
var sql = new StringBuilder();
sql.AppendLine($"SELECT id, tick");
sql.AppendLine($"FROM video_history");
using (var reader = await control.ExecuteReaderAsync(sql.ToString()))
{
while (await reader.ReadAsync())
{
results.Add(new TVideoHistory(
reader.GetString(0), // id
reader.GetInt64(1) // tick
));
}
}
return(results);
}
public static async Task <TSetting[]> GetSetting(this IDbControl control)
{
var results = new List <TSetting>();
var sql = new StringBuilder();
sql.AppendLine($"SELECT cast(key as text), value");
sql.AppendLine($"FROM setting");
using (var reader = await control.ExecuteReaderAsync(sql.ToString()))
{
while (await reader.ReadAsync())
{
results.Add(new TSetting(
(SettingKeys)Enum.Parse(typeof(SettingKeys), reader.GetString(0)), // key
reader.GetString(1) // value
));
}
}
return(results.ToArray());
}
public static async Task <TFavorite[]> GetFavorite(this IDbControl control)
{
var results = new List <TFavorite>();
var sql = new StringBuilder();
sql.AppendLine($"SELECT id, tick");
sql.AppendLine($"FROM favorite");
using (var reader = await control.ExecuteReaderAsync(sql.ToString()))
{
while (await reader.ReadAsync())
{
results.Add(new TFavorite(
reader.GetString(0), // id
reader.GetInt64(1) // tick
));
}
}
return(results.ToArray());
}
public static async Task <TSetting> GetSetting(this IDbControl control, SettingKeys key)
{
var sql = new StringBuilder();
sql.AppendLine($"SELECT cast(key as text), value");
sql.AppendLine($"FROM setting");
sql.AppendLine($"WHERE key = ?");
using (var reader = await control.ExecuteReaderAsync(sql.ToString(), Sqlite3Util.CreateParameter(DbType.Int32, (int)key)))
{
if (await reader.ReadAsync())
{
return(new TSetting(
(SettingKeys)Enum.Parse(typeof(SettingKeys), reader.GetString(0)), // key
reader.GetString(1) // value
));
}
else
{
return(null);
}
}
}
public static async Task <int> DeleteFavorite(this IDbControl control, params TFavorite[] favorites)
{
int result = 0;
foreach (var chunk in favorites.Chunk(500))
{
var sql = new StringBuilder();
sql.AppendLine($"DELETE FROM favorite (");
sql.AppendLine($"WHERE id IN (");
sql.AppendLine(
chunk
.Select(data => $"'{data.Mylist}'")
.GetString(", ")
);
sql.AppendLine($")");
// ﾊﾟﾗﾒｰﾀ設定
result += await control.ExecuteNonQueryAsync(sql.ToString());
}
return(result);
}
