private static async Task CreateTable(SQLiteControl command)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_temporary (");
sql.AppendLine($" video_id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (video_id)");
sql.AppendLine($")");
await command.ExecuteNonQueryAsync(sql.ToString());
sql.Clear();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_history (");
sql.AppendLine($" video_id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (video_id, tick)");
sql.AppendLine($")");
await command.ExecuteNonQueryAsync(sql.ToString());
sql.Clear();
sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_favorite (");
sql.AppendLine($" mylist_id TEXT NOT NULL,");
sql.AppendLine($" tick INTEGER NOT NULL,");
sql.AppendLine($"PRIMARY KEY (mylist_id)");
sql.AppendLine($")");
await command.ExecuteNonQueryAsync(sql.ToString());
}
public static async Task DeleteNicoHistory(this SQLiteControl command, string videoid)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"DELETE FROM t_nico_history WHERE video_id = ?");
var parameters = new[]
{
Sqlite3Util.CreateParameter(DbType.String, videoid),
};
await command.ExecuteNonQueryAsync(sql.ToString(), parameters);
}
private static async Task CreateView(SQLiteControl command)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"CREATE VIEW IF NOT EXISTS v_nico_history AS");
sql.AppendLine($"SELECT");
sql.AppendLine($" video_id,");
sql.AppendLine($" MAX(tick) tick,");
sql.AppendLine($" COUNT(video_id) cnt");
sql.AppendLine($"FROM");
sql.AppendLine($" t_nico_history");
sql.AppendLine($"GROUP BY");
sql.AppendLine($" video_id");
await command.ExecuteNonQueryAsync(sql.ToString());
}
public static async Task MergeNicoHistory(this SQLiteControl command, string videoid)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"INSERT OR REPLACE INTO t_nico_history (");
sql.AppendLine($" video_id, tick");
sql.AppendLine($")");
sql.AppendLine($"VALUES (");
sql.AppendLine($" ?, ?");
sql.AppendLine($")");
var parameters = new[]
{
Sqlite3Util.CreateParameter(DbType.String, videoid),
Sqlite3Util.CreateParameter(DbType.Int64, DateTime.Now.Ticks),
};
await command.ExecuteNonQueryAsync(sql.ToString(), parameters);
}
public static async Task MergeNicoFavorite(this SQLiteControl command, string mylistid, DateTime date)
{
var sql = new StringBuilder();
sql.Clear();
sql.AppendLine($"INSERT OR REPLACE INTO t_nico_favorite (");
sql.AppendLine($" mylist_id, tick");
sql.AppendLine($")");
sql.AppendLine($"VALUES (");
sql.AppendLine($" ?, ?");
sql.AppendLine($")");
var parameters = new[]
{
Sqlite3Util.CreateParameter(DbType.String, mylistid),
Sqlite3Util.CreateParameter(DbType.Int64, date.Ticks),
};
await command.ExecuteNonQueryAsync(sql.ToString(), parameters);
}