/// <summary>
/// Initializes static members of the <see cref="TempFile"/> class.
/// Any file names still listed in the database are attempted to be deleted.
/// </summary>
static TempFile()
{
lock (notInUse)
{
using (SQLiteCommand command = new SQLiteCommand("select filepath from tempfiles", FetchDbConn()))
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int filePathOrdinal = reader.GetOrdinal("filepath");
while (reader.Read())
{
notInUse.Add(reader.GetString(filePathOrdinal));
}
}
}
DeleteFiles();
}
/// <summary>
/// Initializes static members of the <see cref="TempFile"/> class and cleans up
/// any left-over files from previous sessions. Any file names still listed in
/// the database are added to the notInUse list and are attempted to be deleted.
/// </summary>
static TempFile()
{
lock (notInUse)
{
using (SQLiteCommand command = new SQLiteCommand("select filepath from tempfiles", FetchDbConn()))
{
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int filePathOrdinal = reader.GetOrdinal("filepath");
while (reader.Read())
{
notInUse.Add(reader.GetString(filePathOrdinal));
}
}
}
}
DeleteFiles();
}
protected static string GetValue(string propertyName)
{
if (string.IsNullOrEmpty(propertyName))
{
throw new ArgumentException("Property name for value must be specified", "propertyName");
}
using (SQLiteCommand command = new SQLiteCommand("select value from settings where property=@property", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@property", propertyName));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return(null);
}
return(reader.GetString(reader.GetOrdinal("value")));
}
}
}
public DownloadHandler(int epid)
{
using (SQLiteCommand command = new SQLiteCommand("select pr.progid, pluginid, pr.image as progimg, ep.duration, ep.image as epimg, pr.extid as progextid, ep.extid as epextid from episodes as ep, programmes as pr where ep.epid=@epid and ep.progid=pr.progid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
throw new DataNotFoundException(epid, "Episode does not exist");
}
this.pluginId = new Guid(reader.GetString(reader.GetOrdinal("pluginid")));
this.progExtId = reader.GetString(reader.GetOrdinal("progextid"));
this.episodeExtId = reader.GetString(reader.GetOrdinal("epextid"));
this.progInfo = new Model.Programme(reader.GetInt32(reader.GetOrdinal("progid")));
this.episodeInfo = new Model.Episode(epid);
this.providerProgInfo = new Provider.ProgrammeInfo(this.progInfo);
if (reader.IsDBNull(reader.GetOrdinal("progimg")))
{
this.providerProgInfo.Image = null;
}
else
{
this.providerProgInfo.Image = RetrieveImage(reader.GetInt32(reader.GetOrdinal("progimg")));
}
this.providerEpisodeInfo = new Provider.EpisodeInfo(this.episodeInfo);
if (reader.IsDBNull(reader.GetOrdinal("duration")))
{
this.providerEpisodeInfo.Duration = null;
}
else
{
this.providerEpisodeInfo.Duration = reader.GetInt32(reader.GetOrdinal("duration"));
}
if (reader.IsDBNull(reader.GetOrdinal("epimg")))
{
this.providerEpisodeInfo.Image = null;
}
else
{
this.providerEpisodeInfo.Image = RetrieveImage(reader.GetInt32(reader.GetOrdinal("epimg")));
}
using (SQLiteCommand extCommand = new SQLiteCommand("select name, value from episodeext where epid=@epid", FetchDbConn()))
{
extCommand.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader extReader = new SQLiteMonDataReader(extCommand.ExecuteReader()))
{
while (extReader.Read())
{
this.providerEpisodeInfo.ExtInfo.Add(extReader.GetString(extReader.GetOrdinal("name")), extReader.GetString(extReader.GetOrdinal("value")));
}
}
}
}
}
}
private static void UpdateStructure(SQLiteConnection specConn, SQLiteConnection updateConn)
{
using (SQLiteCommand specCommand = new SQLiteCommand("select name, sql from sqlite_master where type='table'", specConn))
{
using (SQLiteCommand checkUpdateCmd = new SQLiteCommand("select sql from sqlite_master where type='table' and name=@name", updateConn))
{
SQLiteParameter nameParam = new SQLiteParameter("@name");
checkUpdateCmd.Parameters.Add(nameParam);
using (SQLiteMonDataReader specReader = new SQLiteMonDataReader(specCommand.ExecuteReader()))
{
int nameOrd = specReader.GetOrdinal("name");
int sqlOrd = specReader.GetOrdinal("sql");
while (specReader.Read())
{
string specName = specReader.GetString(nameOrd);
string specSql = specReader.GetString(sqlOrd);
nameParam.Value = specName;
UpdateType updateReqd;
using (SQLiteMonDataReader checkUpdateRdr = new SQLiteMonDataReader(checkUpdateCmd.ExecuteReader()))
{
if (!checkUpdateRdr.Read())
{
// The table doesn't exist
updateReqd = UpdateType.Create;
}
else
{
if (specSql == checkUpdateRdr.GetString(checkUpdateRdr.GetOrdinal("sql")))
{
// The table does not require an update
updateReqd = UpdateType.None;
}
else
{
// The structure of the table doesn't match, so update it
updateReqd = UpdateType.Update;
}
}
}
if (updateReqd == UpdateType.Create)
{
// Create the table
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
else if (updateReqd == UpdateType.Update)
{
// Fetch a list of common column names for transferring the data
string columnNames = ColNames(specConn, updateConn, specName);
// Rename the existing table to table_name_old
using (SQLiteCommand updateCommand = new SQLiteCommand("alter table [" + specName + "] rename to [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Create the new table with the correct structure
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Copy across the data (discarding rows which violate any new constraints)
if (!string.IsNullOrEmpty(columnNames))
{
using (SQLiteCommand updateCommand = new SQLiteCommand("insert or ignore into [" + specName + "] (" + columnNames + ") select " + columnNames + " from [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
// Delete the old table
using (SQLiteCommand updateCommand = new SQLiteCommand("drop table [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
}
}
}
}
}
public DownloadHandler(int epid)
{
using (SQLiteCommand command = new SQLiteCommand("select pr.progid, pluginid, pr.image as progimg, ep.duration, ep.image as epimg, pr.extid as progextid, ep.extid as epextid from episodes as ep, programmes as pr where ep.epid=@epid and ep.progid=pr.progid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
throw new DataNotFoundException(epid, "Episode does not exist");
}
this.pluginId = new Guid(reader.GetString(reader.GetOrdinal("pluginid")));
this.progExtId = reader.GetString(reader.GetOrdinal("progextid"));
this.episodeExtId = reader.GetString(reader.GetOrdinal("epextid"));
this.progInfo = new Model.Programme(reader.GetInt32(reader.GetOrdinal("progid")));
this.episodeInfo = new Model.Episode(epid);
this.providerProgInfo = new ProgrammeInfo(this.progInfo);
if (reader.IsDBNull(reader.GetOrdinal("progimg")))
{
this.providerProgInfo.Image = null;
}
else
{
this.providerProgInfo.Image = Database.RetrieveImage(reader.GetInt32(reader.GetOrdinal("progimg")));
}
this.providerEpisodeInfo = new EpisodeInfo(this.episodeInfo);
if (reader.IsDBNull(reader.GetOrdinal("duration")))
{
this.providerEpisodeInfo.Duration = null;
}
else
{
this.providerEpisodeInfo.Duration = reader.GetInt32(reader.GetOrdinal("duration"));
}
if (reader.IsDBNull(reader.GetOrdinal("epimg")))
{
this.providerEpisodeInfo.Image = null;
}
else
{
this.providerEpisodeInfo.Image = Database.RetrieveImage(reader.GetInt32(reader.GetOrdinal("epimg")));
}
using (SQLiteCommand extCommand = new SQLiteCommand("select name, value from episodeext where epid=@epid", FetchDbConn()))
{
extCommand.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader extReader = new SQLiteMonDataReader(extCommand.ExecuteReader()))
{
while (extReader.Read())
{
this.providerEpisodeInfo.ExtInfo.Add(extReader.GetString(extReader.GetOrdinal("name")), extReader.GetString(extReader.GetOrdinal("value")));
}
}
}
}
}
}
private static void UpdateStructure(SQLiteConnection specConn, SQLiteConnection updateConn)
{
using (SQLiteCommand specCommand = new SQLiteCommand("select name, sql from sqlite_master where type='table'", specConn))
{
using (SQLiteCommand checkUpdateCmd = new SQLiteCommand("select sql from sqlite_master where type='table' and name=@name", updateConn))
{
SQLiteParameter nameParam = new SQLiteParameter("@name");
checkUpdateCmd.Parameters.Add(nameParam);
using (SQLiteMonDataReader specReader = new SQLiteMonDataReader(specCommand.ExecuteReader()))
{
int nameOrd = specReader.GetOrdinal("name");
int sqlOrd = specReader.GetOrdinal("sql");
while (specReader.Read())
{
string specName = specReader.GetString(nameOrd);
string specSql = specReader.GetString(sqlOrd);
nameParam.Value = specName;
UpdateType updateReqd;
using (SQLiteMonDataReader checkUpdateRdr = new SQLiteMonDataReader(checkUpdateCmd.ExecuteReader()))
{
if (!checkUpdateRdr.Read())
{
// The table doesn't exist
updateReqd = UpdateType.Create;
}
else
{
if (specSql == checkUpdateRdr.GetString(checkUpdateRdr.GetOrdinal("sql")))
{
// The table does not require an update
updateReqd = UpdateType.None;
}
else
{
// The structure of the table doesn't match, so update it
updateReqd = UpdateType.Update;
}
}
}
if (updateReqd == UpdateType.Create)
{
// Create the table
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
else if (updateReqd == UpdateType.Update)
{
// Fetch a list of common column names for transferring the data
string columnNames = ColNames(specConn, updateConn, specName);
// Rename the existing table to table_name_old
using (SQLiteCommand updateCommand = new SQLiteCommand("alter table [" + specName + "] rename to [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Create the new table with the correct structure
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Copy across the data (discarding rows which violate any new constraints)
if (!string.IsNullOrEmpty(columnNames))
{
using (SQLiteCommand updateCommand = new SQLiteCommand("insert or ignore into [" + specName + "] (" + columnNames + ") select " + columnNames + " from [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
// Delete the old table
using (SQLiteCommand updateCommand = new SQLiteCommand("drop table [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
}
}
}
}
}
protected static string GetValue(string propertyName)
{
if (string.IsNullOrEmpty(propertyName))
{
throw new ArgumentException("Property name for value must be specified", "propertyName");
}
using (SQLiteCommand command = new SQLiteCommand("select value from settings where property=@property", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@property", propertyName));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return null;
}
return reader.GetString(reader.GetOrdinal("value"));
}
}
}
public static bool Startup()
{
const string DbFileName = "store.db";
string specDbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DbFileName);
string appDbPath = Path.Combine(FileUtils.GetAppDataFolder(), DbFileName);
// Ensure that the template database exists
if (!File.Exists(specDbPath))
{
MessageBox.Show("The Radio Downloader template database was not found at '" + specDbPath + "'." + Environment.NewLine + Environment.NewLine + "Try repairing the Radio Downloader installation or installing the latest version from nerdoftheherd.com", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
using (SQLiteConnection specConn = new SQLiteConnection("Data Source=" + specDbPath + ";Version=3;New=False;Read Only=True"))
{
specConn.Open();
using (SQLiteCommand command = new SQLiteCommand("pragma integrity_check(1)", specConn))
{
string result = (string)command.ExecuteScalar();
if (result.ToUpperInvariant() != "OK")
{
MessageBox.Show("The Radio Downloader template database at '" + specDbPath + "' appears to be corrupted." + Environment.NewLine + Environment.NewLine + "Try repairing the Radio Downloader installation or installing the latest version from nerdoftheherd.com", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
}
// Migrate old (pre 0.26) version databases from www.nerdoftheherd.com -> NerdoftheHerd.com
string oldDbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "www.nerdoftheherd.com", Application.ProductName, DbFileName);
if (File.Exists(oldDbPath) && !File.Exists(appDbPath))
{
File.Move(oldDbPath, appDbPath);
}
// Test if there is an existing application database
if (!File.Exists(appDbPath))
{
// Start with a copy of the template database
File.Copy(specDbPath, appDbPath);
// Set the current database version in the new database
Settings.DatabaseVersion = CurrentDbVersion;
}
else
{
using (SQLiteCommand command = new SQLiteCommand("pragma integrity_check(1)", FetchDbConn()))
{
string result = (string)command.ExecuteScalar();
if (result.ToUpperInvariant() != "OK")
{
if (MessageBox.Show("Unfortunately Radio Downloader cannot start because your database has become corrupted." + Environment.NewLine + Environment.NewLine + "Would you like to view some help about resolving this issue?", Application.ProductName, MessageBoxButtons.YesNo, MessageBoxIcon.Stop) == DialogResult.Yes)
{
OsUtils.LaunchUrl(new Uri("https://nerdoftheherd.com/tools/radiodld/help/corrupt-database"), "corruptdb");
}
return(false);
}
}
// Disable foreign keys so we can check them afterwards instead
using (SQLiteCommand command = new SQLiteCommand("pragma foreign_keys = off", FetchDbConn()))
{
command.ExecuteNonQuery();
}
// Start a transaction so we can roll back a half-completed upgrade on error
using (SQLiteMonTransaction transMon = new SQLiteMonTransaction(FetchDbConn().BeginTransaction()))
{
try
{
// Perform a check and automatic update of the database table structure
UpdateStructure(specConn, FetchDbConn());
// Perform any updates required which were not handled by UpdateStructure
switch (Settings.DatabaseVersion)
{
case 4:
// Clear error details previously serialised as XML
using (SQLiteCommand command = new SQLiteCommand("update downloads set errordetails=null where errortype=@errortype", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("errortype", Provider.ErrorType.UnknownError));
command.ExecuteNonQuery();
}
break;
case CurrentDbVersion:
// Nothing to do, this is the current version.
break;
}
// Set the current database version
Settings.DatabaseVersion = CurrentDbVersion;
}
catch (SQLiteException)
{
transMon.Trans.Rollback();
throw;
}
transMon.Trans.Commit();
}
// Re-enable foreign keys now all upgrades are completed
using (SQLiteCommand command = new SQLiteCommand("pragma foreign_keys = on", FetchDbConn()))
{
command.ExecuteNonQuery();
}
}
}
// Cleanup data which violates foreign key constraints
using (SQLiteCommand command = new SQLiteCommand("pragma foreign_key_check", FetchDbConn()))
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
while (reader.Read())
{
string table = reader.GetString(0);
int rowid = reader.GetInt32(1);
if (reader.GetString(2) == "images")
{
using (SQLiteCommand nullCmd = new SQLiteCommand("update " + table + " set image=null where rowid=" + rowid, FetchDbConn()))
{
nullCmd.ExecuteNonQuery();
}
}
else
{
using (SQLiteCommand deleteCmd = new SQLiteCommand("delete from " + table + " where rowid=" + rowid, FetchDbConn()))
{
deleteCmd.ExecuteNonQuery();
}
}
}
}
// Prune the database once a week
if (Settings.LastPrune.AddDays(7) < DateTime.Now)
{
using (Status status = new Status())
{
status.ShowDialog(() =>
{
Prune(status);
});
}
}
// Vacuum the database every three months
if (Settings.LastVacuum.AddMonths(VacuumMonths) < DateTime.Now)
{
using (Status status = new Status())
{
status.ShowDialog(() =>
{
Vacuum(status);
});
}
}
return(true);
}