protected internal static Bitmap RetrieveImage(int imgid) { using (SQLiteCommand command = new SQLiteCommand("select image from images where imgid=@imgid", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@imgid", imgid)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { if (!reader.Read()) { return(null); } // Get the size of the image data by passing nothing to getbytes int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0); byte[] content = new byte[dataLength]; reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength); using (MemoryStream contentStream = new MemoryStream(content)) { using (Bitmap streamBitmap = new Bitmap(contentStream)) { return(new Bitmap(streamBitmap)); } } } } }
private static void ResumeDownloadsAsync() { List <int> epids = new List <int>(); lock (downloads) { using (SQLiteCommand command = new SQLiteCommand("select episodes.epid from downloads, episodes where downloads.epid=episodes.epid and status=@statuswait order by date", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@statuswait", Model.Download.DownloadStatus.Waiting)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { int epidOrdinal = reader.GetOrdinal("epid"); while (reader.Read()) { epids.Add(reader.GetInt32(epidOrdinal)); } } } if (epids.Count > 0) { AddDownloads(epids.ToArray()); } } RetryErrored(); }
private static void RetryErrored() { List <int> epids = new List <int>(); using (SQLiteCommand command = new SQLiteCommand("select epid from downloads where status=@statuserr and errortime < datetime('now', '-' || (1 << errorcount) || ' hours')", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@statuserr", Model.Download.DownloadStatus.Errored)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { int epidOrdinal = reader.GetOrdinal("epid"); while (reader.Read()) { epids.Add(reader.GetInt32(epidOrdinal)); } } } foreach (int epid in epids) { Model.Download.ResetAsync(epid, true); } ThreadPool.QueueUserWorkItem(delegate { // Wait for 30 minutes, and check again Thread.Sleep(1800000); RetryErrored(); }); }
protected internal static Bitmap RetrieveImage(int imgid) { using (SQLiteCommand command = new SQLiteCommand("select image from images where imgid=@imgid", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@imgid", imgid)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { if (!reader.Read()) { return null; } // Get the size of the image data by passing nothing to getbytes int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0); byte[] content = new byte[dataLength]; reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength); using (MemoryStream contentStream = new MemoryStream(content)) { using (Bitmap streamBitmap = new Bitmap(contentStream)) { return new Bitmap(streamBitmap); } } } } }
/// <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(); }
protected internal static int?StoreImage(Image image) { if (image == null) { return(null); } // Convert the image into a byte array byte[] imageAsBytes = null; using (MemoryStream memstream = new MemoryStream()) { image.Save(memstream, System.Drawing.Imaging.ImageFormat.Png); imageAsBytes = memstream.ToArray(); } lock (DbUpdateLock) { using (SQLiteCommand command = new SQLiteCommand("select imgid from images where image=@image", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { if (reader.Read()) { return(reader.GetInt32(reader.GetOrdinal("imgid"))); } } } using (SQLiteCommand command = new SQLiteCommand("insert into images (image) values (@image)", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes)); command.ExecuteNonQuery(); } using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", FetchDbConn())) { return((int)(long)command.ExecuteScalar()); } } }
/// <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"))); } } }
protected internal static int?StoreImage(CompressedImage image) { if (image == null) { return(null); } byte[] imageAsBytes = image.GetBytes(); lock (DbUpdateLock) { using (SQLiteCommand command = new SQLiteCommand("select imgid from images where image=@image", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { if (reader.Read()) { return(reader.GetInt32(reader.GetOrdinal("imgid"))); } } } using (SQLiteCommand command = new SQLiteCommand("insert into images (image) values (@image)", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes)); command.ExecuteNonQuery(); } using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", FetchDbConn())) { return((int)(long)command.ExecuteScalar()); } } }
public ErrorReporting(Exception uncaughtException) : this(InvariantMessage(uncaughtException), InvariantStackTrace(uncaughtException)) { try { if (ReferenceEquals(uncaughtException.GetType(), typeof(System.Data.SQLite.SQLiteException))) { // Add extra information to the exception to help debug sqlite concurrency uncaughtException = SQLiteMonDataReader.AddReadersInfo(uncaughtException); uncaughtException = SQLiteMonTransaction.AddTransactionsInfo(uncaughtException); } this.fields.Add("Exception.ToString()", uncaughtException.ToString()); // Set up a list of types which do not need to be serialized List <Type> notSerialize = new List <Type>(new Type[] { typeof(string), typeof(int), typeof(float), typeof(double), typeof(bool) }); // Store the type of the exception and get a list of its properties to loop through Type exceptionType = uncaughtException.GetType(); PropertyInfo[] baseExceptionProperties = typeof(Exception).GetProperties(); bool extraProperty = false; bool overloadedProp = false; foreach (PropertyInfo thisExpProperty in exceptionType.GetProperties()) { extraProperty = true; overloadedProp = false; // Check if this property exists in the base exception class: if not then add it to the report foreach (PropertyInfo baseProperty in baseExceptionProperties) { if (thisExpProperty.Name == baseProperty.Name) { extraProperty = false; break; } } // Test to see if this property is overloaded foreach (PropertyInfo overloadChkProp in exceptionType.GetProperties()) { if (!overloadChkProp.Equals(thisExpProperty)) { if (overloadChkProp.Name == thisExpProperty.Name) { overloadedProp = true; break; } } } if (extraProperty) { string fieldName = "Exception." + thisExpProperty.Name; object propertyValue = thisExpProperty.GetValue(uncaughtException, null); if (overloadedProp) { string typeName = propertyValue.GetType().ToString(); int dotPos = typeName.LastIndexOf(".", StringComparison.Ordinal); if (dotPos >= 0) { typeName = typeName.Substring(dotPos + 1); } fieldName += ":" + typeName; } if (propertyValue != null && !string.IsNullOrEmpty(propertyValue.ToString())) { if (propertyValue.GetType() == typeof(Provider.ErrorType)) { // ErrorType is always set to UnknownError on DownloadExceptions continue; } if (!notSerialize.Contains(propertyValue.GetType())) { if (propertyValue.GetType().IsSerializable) { // Attempt to serialize the object as an XML string try { StringWriter valueStringWriter = new StringWriter(CultureInfo.InvariantCulture); XmlSerializer valueSerializer = new XmlSerializer(propertyValue.GetType()); valueSerializer.Serialize(valueStringWriter, propertyValue); this.fields.Add(fieldName, valueStringWriter.ToString()); continue; } catch (NotSupportedException) { // Not possible to serialize - do nothing & fall through to the ToString code } catch (InvalidOperationException) { // Problem serializing the object - do nothing & fall through to the ToString code } } } this.fields.Add(fieldName, propertyValue.ToString()); } } } if (uncaughtException.Data != null) { foreach (DictionaryEntry dataEntry in uncaughtException.Data) { if (ReferenceEquals(dataEntry.Key.GetType(), typeof(string)) && ReferenceEquals(dataEntry.Value.GetType(), typeof(string))) { this.fields[(string)dataEntry.Key] = (string)dataEntry.Value; } } } } catch { // No way of reporting errors that have happened here, so try to continue } }
protected internal static int? StoreImage(Image image) { if (image == null) { return null; } // Convert the image into a byte array byte[] imageAsBytes = null; using (MemoryStream memstream = new MemoryStream()) { image.Save(memstream, System.Drawing.Imaging.ImageFormat.Png); imageAsBytes = memstream.ToArray(); } lock (DbUpdateLock) { using (SQLiteCommand command = new SQLiteCommand("select imgid from images where image=@image", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { if (reader.Read()) { return reader.GetInt32(reader.GetOrdinal("imgid")); } } } using (SQLiteCommand command = new SQLiteCommand("insert into images (image) values (@image)", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes)); command.ExecuteNonQuery(); } using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", FetchDbConn())) { return (int)(long)command.ExecuteScalar(); } } }
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")); } } }
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(); } } } } } } }
private static void ResumeDownloadsAsync() { List<int> epids = new List<int>(); lock (downloads) { using (SQLiteCommand command = new SQLiteCommand("select episodes.epid from downloads, episodes where downloads.epid=episodes.epid and status=@statuswait order by date", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@statuswait", Model.Download.DownloadStatus.Waiting)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { int epidOrdinal = reader.GetOrdinal("epid"); while (reader.Read()) { epids.Add(reader.GetInt32(epidOrdinal)); } } } if (epids.Count > 0) { AddDownloads(epids.ToArray()); } } RetryErrored(); }
private static void RetryErrored() { List<int> epids = new List<int>(); using (SQLiteCommand command = new SQLiteCommand("select epid from downloads where status=@statuserr and errortime < datetime('now', '-' || (1 << errorcount) || ' hours')", FetchDbConn())) { command.Parameters.Add(new SQLiteParameter("@statuserr", Model.Download.DownloadStatus.Errored)); using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader())) { int epidOrdinal = reader.GetOrdinal("epid"); while (reader.Read()) { epids.Add(reader.GetInt32(epidOrdinal)); } } } foreach (int epid in epids) { Model.Download.ResetAsync(epid, true); } ThreadPool.QueueUserWorkItem(delegate { // Wait for 30 minutes, and check again Thread.Sleep(1800000); RetryErrored(); }); }
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"))); } } } } } }
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); }