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