private static void Prune(Status status) { lock (Database.DbUpdateLock) { using (SQLiteMonTransaction transMon = new SQLiteMonTransaction(FetchDbConn().BeginTransaction())) { status.StatusText = "Pruning episode images..."; // Set image to null for unavailable episodes, except: // - episodes in the downloads list // - the most recent episode for subscriptions or favourites (as this may be used as the programme image) using (SQLiteCommand command = new SQLiteCommand("update episodes set image=null where epid in (select e1.epid from episodes as e1 left outer join downloads on e1.epid=downloads.epid where available=0 and image is not null and downloads.epid is null and ((not (exists(select 1 from subscriptions where subscriptions.progid=e1.progid) or exists(select 1 from favourites where favourites.progid=e1.progid))) or exists(select 1 from episodes as e2 where e1.progid=e2.progid and image is not null and e2.date > e1.date)))", FetchDbConn())) { command.ExecuteNonQuery(); } status.StatusText = "Cleaning up unused images..."; // Remove images which are now no-longer referenced by a programme or episode using (SQLiteCommand command = new SQLiteCommand("delete from images where imgid in (select imgid from images left outer join programmes on imgid=programmes.image left outer join episodes on imgid=episodes.image where programmes.image is null and episodes.image is null)", FetchDbConn())) { command.ExecuteNonQuery(); } transMon.Trans.Commit(); } Settings.LastPrune = DateTime.Now; } }
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(Path.Combine(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 = Database.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); } } // 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, Database.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", ErrorType.UnknownError)); command.ExecuteNonQuery(); } break; case Database.CurrentDbVersion: // Nothing to do, this is the current version. break; } // Set the current database version Settings.DatabaseVersion = Database.CurrentDbVersion; } catch (SQLiteException) { transMon.Trans.Rollback(); throw; } transMon.Trans.Commit(); } } } // Prune the database once a week if (Settings.LastPrune.AddDays(7) < DateTime.Now) { using (Status status = new Status()) { status.ShowDialog(delegate { Prune(status); }); } } // Vacuum the database every three months if (Settings.LastVacuum.AddMonths(3) < DateTime.Now) { using (Status status = new Status()) { status.ShowDialog(delegate { Vacuum(status); }); } } return(true); }
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 } }
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; } // Migrate old (pre 0.26) version databases from www.nerdoftheherd.com -> NerdoftheHerd.com string oldDbPath = Path.Combine(Path.Combine(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 = Database.CurrentDbVersion; } else { // 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 using (SQLiteConnection specConn = new SQLiteConnection("Data Source=" + specDbPath + ";Version=3;New=False;Read Only=True")) { specConn.Open(); UpdateStructure(specConn, Database.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", ErrorType.UnknownError)); command.ExecuteNonQuery(); } break; case Database.CurrentDbVersion: // Nothing to do, this is the current version. break; } // Set the current database version Settings.DatabaseVersion = Database.CurrentDbVersion; } catch (SQLiteException) { transMon.Trans.Rollback(); throw; } transMon.Trans.Commit(); } } // Prune the database once a week if (Settings.LastPrune.AddDays(7) < DateTime.Now) { using (Status status = new Status()) { status.ShowDialog(delegate { Prune(status); }); } } // Vacuum the database every three months if (Settings.LastVacuum.AddMonths(3) < DateTime.Now) { using (Status status = new Status()) { status.ShowDialog(delegate { Vacuum(status); }); } } return true; }
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(Path.Combine(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; } } // 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", 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(); } } } // 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(3) < DateTime.Now) { using (Status status = new Status()) { status.ShowDialog(() => { Vacuum(status); }); } } return true; }
private static void Prune(Status status) { lock (DbUpdateLock) { using (SQLiteMonTransaction transMon = new SQLiteMonTransaction(FetchDbConn().BeginTransaction())) { status.StatusText = "Pruning episode images..."; // Set image to null for unavailable episodes, except: // - episodes in the downloads list // - the most recent episode for subscriptions or favourites (as this may be used as the programme image) using (SQLiteCommand command = new SQLiteCommand("update episodes set image=null where epid in (select e1.epid from episodes as e1 left outer join downloads on e1.epid=downloads.epid where available=0 and image is not null and downloads.epid is null and ((not (exists(select 1 from subscriptions where subscriptions.progid=e1.progid) or exists(select 1 from favourites where favourites.progid=e1.progid))) or exists(select 1 from episodes as e2 where e1.progid=e2.progid and image is not null and e2.date > e1.date)))", FetchDbConn())) { command.ExecuteNonQuery(); } status.StatusText = "Pruning programme data..."; // Remove old programmes which have never had an episode // downloaded and are not subscriptions or favourites using (SQLiteCommand command = new SQLiteCommand( @"delete from programmes where progid in ( select progid from programmes p where lastupdate < date('now', '-1 year') and ( select count(*) from episodes e where e.progid=p.progid and ( autodownload=0 or e.date > date('now', '-1 year') ) ) = 0 and ( select count(*) from episodes e inner join downloads d on e.progid=p.progid and e.epid=d.epid ) = 0 and ( select count(*) from subscriptions s where s.progid=p.progid ) = 0 and ( select count(*) from favourites f where f.progid=p.progid ) = 0 )", FetchDbConn())) { command.ExecuteNonQuery(); } status.StatusText = "Cleaning up unused images..."; // Remove images which are now no-longer referenced by a programme or episode using (SQLiteCommand command = new SQLiteCommand( @"delete from images where imgid in ( select imgid from images left outer join programmes on imgid=programmes.image left outer join episodes on imgid=episodes.image left outer join chapters on imgid=chapters.image where programmes.image is null and episodes.image is null and chapters.image is null )", FetchDbConn())) { command.ExecuteNonQuery(); } transMon.Trans.Commit(); } Settings.LastPrune = DateTime.Now; } }