public static void MoneyTransactionsTemplatesTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.MoneyTransactionTemplatess
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "TransactionType", FieldType = TableMigrationFieldType.Integer
});
t.Fields.Add(new TableMigrationField {
FieldName = "Amount", FieldType = TableMigrationFieldType.Integer
});
t.Fields.Add(new TableMigrationField {
FieldName = "Active", FieldType = TableMigrationFieldType.Bit
});
t.Fields.Add(new TableMigrationField {
FieldName = "Auto", FieldType = TableMigrationFieldType.Bit
});
t.Fields.Add(new TableMigrationField {
FieldName = "Name", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "FromDate", FieldType = TableMigrationFieldType.Date
});
t.Fields.Add(new TableMigrationField {
FieldName = "ToDate", FieldType = TableMigrationFieldType.Date, IsNullAble = true
});
db.ExecurteCreateTable(t.ToString());
}
private async Task RunMigration(IDbMigration migration, DatabaseVersionModel databaseVersion)
{
// Check Actual DatabaseVersion against the migration version
// Don't run unless this Migrations Migration has not been run
if (databaseVersion.IsMigrationComplete == false)
{
await migration.RunOrderedMigration(MigrationStep.Migrate, _dbProvider).ConfigureAwait(false);
switch (_systemRole)
{
case SystemRole.Server:
await migration.RunOrderedMigration(MigrationStep.ServerMigrate, _dbProvider).ConfigureAwait(false);
break;
case SystemRole.Client:
await migration.RunOrderedMigration(MigrationStep.ClientMigrate, _dbProvider).ConfigureAwait(false);
break;
}
// Update the database version to show the migration has been run
databaseVersion.IsMigrationComplete = true;
await _dbProvider.Query <DatabaseVersionModel>()
.Where(dbv => dbv.VersionNumber == migration.MigrationVersion)
.Update(databaseVersion)
.ConfigureAwait(false);
}
}
public static void SQuickTasksDataTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.QuickTasks
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "Name", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "Description", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "Active", FieldType = TableMigrationFieldType.Bit
});
t.Fields.Add(new TableMigrationField {
FieldName = "CreationDate", FieldType = TableMigrationFieldType.Date
});
db.ExecurteCreateTable(t.ToString());
}
/// <summary>
/// Initializes a new instance of the <see cref="OpenIZ.Mobile.Core.Configuration.Data.DataMigrator"/> class.
/// </summary>
/// <param name="configuration">Configuration.</param>
public DataMigrator()
{
this.m_tracer = Tracer.GetTracer(this.GetType());
this.m_migrations = new List <IDbMigration> ();
this.m_tracer.TraceInfo("Scanning for data migrations...");
// Scan for migrations
foreach (var dbm in typeof(DataMigrator).GetTypeInfo().Assembly.DefinedTypes)
{
try {
if (dbm.AsType() == typeof(DataMigrator) ||
!typeof(IDbMigration).GetTypeInfo().IsAssignableFrom(dbm))
{
continue;
}
IDbMigration migration = Activator.CreateInstance(dbm.AsType()) as IDbMigration;
if (migration != null)
{
this.m_tracer.TraceVerbose("Found data migrator {0}...", migration.Id);
this.m_migrations.Add(migration);
}
} catch {
}
}
}
public static void DiaryTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.DiaryData
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "title", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "description", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "date", FieldType = TableMigrationFieldType.Date
});
t.Fields.Add(new TableMigrationField {
FieldName = "CreationDate", FieldType = TableMigrationFieldType.Date
});
t.Fields.Add(new TableMigrationField {
FieldName = "Shared", FieldType = TableMigrationFieldType.Bit
});
t.Fields.Add(new TableMigrationField {
FieldName = "RepeatEvery", FieldType = TableMigrationFieldType.Integer, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "UserId", FieldType = TableMigrationFieldType.Integer
});
db.ExecurteCreateTable(t.ToString());
}
private async Task RunBeforeMigration(IDbMigration migration, DatabaseVersionModel databaseVersion)
{
// Check Actual DatabaseVersion against the migration version
// Don't run unless this Migrations BeforeMigration has not been run
if (databaseVersion.IsBeforeMigrationComplete == false)
{
// Before Migrate
await migration.RunOrderedMigration(MigrationStep.Setup, _dbProvider).ConfigureAwait(false);
if (_systemRole == SystemRole.Server)
{
await migration.RunOrderedMigration(MigrationStep.ServerSetup, _dbProvider).ConfigureAwait(false);
}
if (_systemRole == SystemRole.Client)
{
await migration.RunOrderedMigration(MigrationStep.ClientSetup, _dbProvider).ConfigureAwait(false);
}
// Update the database version to show the before migration has been run
databaseVersion.IsBeforeMigrationComplete = true;
await _dbProvider.Query <DatabaseVersionModel>()
.Where(dbv => dbv.VersionNumber == migration.MigrationVersion)
.Update(databaseVersion)
.ConfigureAwait(false);
}
}
public static void UsersTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.Users
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "UserName", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "DisplayName", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "Password", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 12
});
t.Fields.Add(new TableMigrationField {
FieldName = "AllowedClientPagePermissions", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "EmailAdress", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
db.ExecurteCreateTable(t.ToString());
}
public static void WorkHoursDataTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.WorkHoursData
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "Month", FieldType = TableMigrationFieldType.Date
});
t.Fields.Add(new TableMigrationField {
FieldName = "Active", FieldType = TableMigrationFieldType.Bit
});
t.Fields.Add(new TableMigrationField {
FieldName = "CurrentMonthTotal", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "CurrentShiftStart", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "CurrentShiftEnd", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
db.ExecurteCreateTable(t.ToString());
}
/// <summary>
/// 重命名 Sequence
/// </summary>
public static void RenameSequence(this IDbMigration migration, string name, string newName,
bool suppressTransaction = false)
{
migration.AddOperation(new SqlOperation($"exec sp_rename '{name}', '{newName}'")
{
SuppressTransaction = suppressTransaction
});
}
/// <summary>
/// 删除指定 Sequence
/// </summary>
public static void DropSequence(this IDbMigration migration, string sequenceName,
bool suppressTransaction = false)
{
migration.AddOperation(
new SqlOperation($"DROP SEQUENCE {sequenceName}")
{
SuppressTransaction = suppressTransaction
});
}
/// <summary>
/// 创建一个 No Cache 的 Sequence
/// </summary>
public static void CreateSequence(this IDbMigration migration, string sequenceName,
bool suppressTransaction = false)
{
migration.AddOperation(
new SqlOperation($"CREATE SEQUENCE {sequenceName} AS bigint START WITH 1 INCREMENT BY 1 NO CACHE")
{
SuppressTransaction = suppressTransaction
});
}
/// <summary>
/// 为指定列添加默认值约束
/// </summary>
public static void AddDefaultContraint(this IDbMigration migration, string tableName, string colName,
string defaultValue, bool suppressTransaction = false)
{
migration.AddOperation(
new SqlOperation($"ALTER TABLE {tableName} ADD DEFAULT {defaultValue} FOR {colName};")
{
SuppressTransaction = suppressTransaction
});
}
private static void AddSqlOperation(this IDbMigration migration, string sql, bool suppressTransaction = false, object anonymousArguments = null)
{
var operation = new SqlOperation(sql, anonymousArguments)
{
SuppressTransaction = suppressTransaction
};
migration.AddOperation(operation);
}
public async Task Run(SystemRole systemRole, IDbMigration migration)
{
_systemRole = systemRole;
var databaseVersion = await GetMigrationInformationAsync(migration).ConfigureAwait(false);
await RunBeforeMigration(migration, databaseVersion).ConfigureAwait(false);
await RunMigration(migration, databaseVersion).ConfigureAwait(false);
await RunAfterMigration(migration, databaseVersion).ConfigureAwait(false);
}
public static void DeleteDefaultConstraint(this IDbMigration migration, string tableName, string colName, bool suppressTransaction = false)
{
var sql = new SqlOperation(
$"DECLARE @SQL varchar(1000) " +
$"SET @SQL='ALTER TABLE {tableName} DROP CONSTRAINT ['+(SELECT name FROM sys.default_constraints WHERE parent_object_id = object_id('{tableName}') AND col_name(parent_object_id, parent_column_id) = '{colName}')+']'; " +
$"PRINT @SQL; " +
$"EXEC(@SQL);")
{
SuppressTransaction = suppressTransaction
};
migration.AddOperation(sql);
}
public static void SqlFileOrResource(this IDbMigration migration, string fileName, Assembly assembly = null, string location = null)
{
Guard.NotEmpty(fileName, nameof(fileName));
var tokenizer = new SqlFileTokenizer(fileName, assembly ?? Assembly.GetExecutingAssembly(), location);
foreach (var cmd in tokenizer.Tokenize())
{
if (cmd.HasValue())
{
migration.AddSqlOperation(cmd);
}
}
}
public static void PermissionGroupsTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.PermissionGroups
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "Name", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
db.ExecurteCreateTable(t.ToString());
}
public static async Task RunOrderedMigration(this IDbMigration migration, MigrationStep key, IDbProvider dbProvider)
{
if (!migration.Migration.ContainsKey(key))
{
return;
}
var database = new Database(dbProvider.DatabaseName, dbProvider.Dialect);
migration.Migration[key].Invoke(database, dbProvider);
var dbCommand = database.ToString();
if (!string.IsNullOrWhiteSpace(dbCommand))
{
await dbProvider.ExecuteNonQuery(dbCommand);
}
}
public static void LottoRowsTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.LottoRows
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "N1", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "N2", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "N3", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "N4", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "N5", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "N6", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "S", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "PoleKey", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 250, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "PoleDestinationDate", FieldType = TableMigrationFieldType.Date
});
t.Fields.Add(new TableMigrationField {
FieldName = "CreationDate", FieldType = TableMigrationFieldType.Date
});
t.Fields.Add(new TableMigrationField {
FieldName = "WinsData", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
db.ExecurteCreateTable(t.ToString());
}
public static void GoogleAPICredentialsTable(IDbMigration db)
{
var t = new TableMigration
{
TableName = SynnDataProvider.TableNames.GoogleAPICredentials
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "userid", FieldType = TableMigrationFieldType.Integer
});
t.Fields.Add(new TableMigrationField {
FieldName = "Credentials", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
db.ExecurteCreateTable(t.ToString());
}
public static void DeleteDefaultConstraint(this IDbMigration migration, string tableName, string colName, bool suppressTransaction = false)
{
// see https://stackoverflow.com/questions/17894906/ef-migration-for-changing-data-type-of-columns
// (function name there with typo: DeleteDefaultContraint)
var sql = new SqlOperation(String.Format(@"DECLARE @SQL varchar(1000)
SET @SQL='ALTER TABLE {0} DROP CONSTRAINT ['+(SELECT name
FROM sys.default_constraints
WHERE parent_object_id = object_id('{0}')
AND col_name(parent_object_id, parent_column_id) = '{1}')+']';
PRINT @SQL;
EXEC(@SQL);", tableName, colName))
{
SuppressTransaction = suppressTransaction
};
migration.AddOperation(sql);
}
public static void ShiftsDataTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.ShiftsData
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "DaylyShift", FieldType = TableMigrationFieldType.Integer
});
t.Fields.Add(new TableMigrationField {
FieldName = "Date", FieldType = TableMigrationFieldType.Date
});
db.ExecurteCreateTable(t.ToString());
}
public static void UserPreferencesTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.UserPreferences
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "pdata", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "UserId", FieldType = TableMigrationFieldType.Integer
});
db.ExecurteCreateTable(t.ToString());
}
public static void WeddingItemsTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.WeddingItems
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "GuestName", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "Payment", FieldType = TableMigrationFieldType.Integer
});
db.ExecurteCreateTable(t.ToString());
}
public static void UserDictionaryTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.UserDictionary
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "dKey", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "Value", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
db.ExecurteCreateTable(t.ToString());
}
public static void MigrationItemsTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.MigrationItems
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "Name", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 500, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "Date", FieldType = TableMigrationFieldType.Date
});
db.ExecurteCreateTable(t.ToString());
}
public static void ThemeItemsTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.ThemeItems
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "ElementIdentifier", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "CssAttribute", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "CssValue", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = false
});
db.ExecurteCreateTable(t.ToString());
}
public static void DevTasksTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.DevTasks
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "Status", FieldType = TableMigrationFieldType.Integer, IsNullAble = false
});
t.Fields.Add(new TableMigrationField {
FieldName = "Name", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 500, IsNullAble = true
});
t.Fields.Add(new TableMigrationField {
FieldName = "Description", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000, IsNullAble = true
});
db.ExecurteCreateTable(t.ToString());
}
public static void LogTable(IDbMigration db)
{
var t = new TableMigration
{
HasIdentity = true,
TableName = SynnDataProvider.TableNames.Log
};
t.Fields = new List <TableMigrationField>();
t.Fields.Add(new TableMigrationField {
FieldName = "Message", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "Trace", FieldType = TableMigrationFieldType.NVarchar, FieldLLenght = 4000
});
t.Fields.Add(new TableMigrationField {
FieldName = "Date", FieldType = TableMigrationFieldType.Date
});
db.ExecurteCreateTable(t.ToString());
}
private async Task <DatabaseVersionModel> GetMigrationInformationAsync(IDbMigration migration)
{
var databaseVersions = await _dbProvider.Query <DatabaseVersionModel>()
.Where(dbv => dbv.VersionNumber == migration.MigrationVersion)
.OrderBy(v => v.VersionNumber, OrderDirection.Descending)
.Select()
.ConfigureAwait(false);
var databaseVersion = databaseVersions.FirstOrDefault();
if (databaseVersion == null)
{
databaseVersion = new DatabaseVersionModel
{
VersionNumber = migration.MigrationVersion,
MigrationDate = DateTime.UtcNow
};
await _dbProvider.Create(databaseVersion).ConfigureAwait(false);
}
return(databaseVersion);
}
