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