public void DeleteSubSchema(string subSchemaName, int currentVersionMajor, int currentVersionMinor, string deleteScriptFilePath) { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false); using (ITransaction transaction = database.BeginTransaction(IsolationLevel.Serializable)) { int versionMajor; int versionMinor; bool schemaPresent = GetSubSchemaVersion(subSchemaName, out versionMajor, out versionMinor); if (!schemaPresent) { return; } if (currentVersionMajor == versionMajor && currentVersionMinor == versionMinor) { using (TextReader reader = new SqlScriptPreprocessor(deleteScriptFilePath)) ExecuteBatch(transaction, new InstructionList(reader)); } else { throw new ArgumentException(string.Format("The current version of sub schema '{0}' is {1}.{2}, but the schema deletion script works for version {3}.{4}", subSchemaName, versionMajor, versionMinor, currentVersionMajor, currentVersionMajor)); } using (IDbCommand command = MediaPortal_Basis_Schema.DeleteSubSchemaCommand(transaction, subSchemaName)) command.ExecuteNonQuery(); transaction.Commit(); } }
public bool GetSubSchemaVersion(string subSchemaName, out int versionMajor, out int versionMinor) { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false); versionMajor = 0; versionMinor = 0; int versionMajorParameterIndex; int versionMinorParameterIndex; using (ITransaction transaction = database.BeginTransaction()) { using (IDbCommand command = MediaPortal_Basis_Schema.SelectVersionBySubschemaCommand(transaction, subSchemaName, out versionMajorParameterIndex, out versionMinorParameterIndex)) using (IDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow)) { if (reader.Read()) { // Versions are marked NOT NULL, so it is safe not to check for DBNull versionMajor = reader.GetInt32(versionMajorParameterIndex); versionMinor = reader.GetInt32(versionMinorParameterIndex); return(true); } return(false); } } }
protected bool GetDatabaseVersion(out int versionMajor, out int versionMinor, bool useBackupTable = false) { versionMajor = 0; versionMinor = 0; ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false); if (!database.TableExists(MediaPortal_Basis_Schema.DATABASE_VERSION_TABLE_NAME + (useBackupTable ? BACKUP_TABLE_SUFFIX : ""))) { return(false); } ITransaction transaction = database.BeginTransaction(); try { int versionIndex; IDbCommand command; if (useBackupTable) { command = SelectBackupDatabaseVersionByCommand(transaction, out versionIndex); } else { command = MediaPortal_Basis_Schema.SelectDatabaseVersionByCommand(transaction, out versionIndex); } using (command) using (IDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow)) { if (reader.Read()) { if (Version.TryParse(reader.GetString(versionIndex), out Version version)) { versionMajor = version.Major; versionMinor = version.Minor; } return(true); } return(false); } } finally { transaction.Dispose(); } }
public ICollection <string> GetDatabaseSubSchemas() { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false); using (ITransaction transaction = database.BeginTransaction()) { int nameIndex; using (IDbCommand command = MediaPortal_Basis_Schema.SelectAllSubSchemaNames(transaction, out nameIndex)) using (IDataReader reader = command.ExecuteReader()) { ICollection <string> result = new List <string>(); while (reader.Read()) { result.Add(database.ReadDBValue <string>(reader, nameIndex)); } return(result); } } }
protected bool SetDatabaseVersion(int versionMajor, int versionMinor, bool useBackupTable = false) { Version newVersion = new Version(versionMajor, versionMinor); ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false); if (!database.TableExists(MediaPortal_Basis_Schema.DATABASE_VERSION_TABLE_NAME + (useBackupTable ? BACKUP_TABLE_SUFFIX : ""))) { return(false); } ITransaction transaction = database.BeginTransaction(); try { IDbCommand command; if (useBackupTable) { command = UpdateBackupDatabaseVersionCommand(transaction, newVersion.ToString(2)); } else { command = MediaPortal_Basis_Schema.UpdateDatabaseVersionCommand(transaction, newVersion.ToString(2)); } using (command) { command.ExecuteNonQuery(); } transaction.Commit(); return(true); } catch (Exception e) { ServiceRegistration.Get <ILogger>().Error("DatabaseManager: Error updating database version", e); transaction.Rollback(); throw; } }
public bool UpdateSubSchema(string subSchemaName, int?currentVersionMajor, int?currentVersionMinor, string updateScriptFilePath, int newVersionMajor, int newVersionMinor) { try { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false); int versionMajor; int versionMinor; bool schemaPresent = GetSubSchemaVersion(subSchemaName, out versionMajor, out versionMinor); using (ITransaction transaction = database.BeginTransaction(IsolationLevel.Serializable)) { if (schemaPresent) { if (currentVersionMajor.HasValue && currentVersionMajor.Value == versionMajor && currentVersionMinor.HasValue && currentVersionMinor.Value == versionMinor) { ServiceRegistration.Get <ILogger>().Debug("DatabaseManager: Updating subschema '{0}' from version {1}.{2} to version {3}.{4}...", subSchemaName, versionMajor, versionMinor, newVersionMajor, newVersionMinor); using (TextReader reader = new SqlScriptPreprocessor(updateScriptFilePath)) ExecuteBatch(transaction, new InstructionList(reader)); } else { throw new ArgumentException(string.Format( "The current version of sub schema '{0}' is {1}.{2}, but the schema update script is given for version {3}", subSchemaName, versionMajor, versionMinor, ExplicitVersionToString(currentVersionMajor, currentVersionMinor))); } } else // !schemaPresent { if (!currentVersionMajor.HasValue && !currentVersionMinor.HasValue) { ServiceRegistration.Get <ILogger>().Debug("DatabaseManager: Creating subschema '{0}' version {1}.{2}...", subSchemaName, newVersionMajor, newVersionMinor); using (TextReader reader = new SqlScriptPreprocessor(updateScriptFilePath)) ExecuteBatch(transaction, new InstructionList(reader)); } else { throw new ArgumentException(string.Format( "The sub schema '{0}' is not present yet, but the schema update script is given for version {1}", subSchemaName, ExplicitVersionToString(currentVersionMajor, currentVersionMinor))); } } IDbCommand command; if (schemaPresent) { command = MediaPortal_Basis_Schema.UpdateSubSchemaCommand( transaction, subSchemaName, newVersionMajor, newVersionMinor); } else { command = MediaPortal_Basis_Schema.InsertSubSchemaCommand( transaction, subSchemaName, newVersionMajor, newVersionMinor); } command.ExecuteNonQuery(); transaction.Commit(); } ServiceRegistration.Get <ILogger>().Info("DatabaseManager: Subschema '{0}' present in version {1}.{2}", subSchemaName, newVersionMajor, newVersionMinor); return(true); } catch (Exception e) { ServiceRegistration.Get <ILogger>().Error("DatabaseManager: Error updating subschema '{0}'", e, subSchemaName); throw; } }