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;
}
}
