private void SaveTenant(DbFactory dbFactory, string alias, TenantStatus status, string newAlias = null, string whereCondition = null)
{
using (var connection = dbFactory.OpenConnection())
{
if (newAlias == null)
{
newAlias = alias;
}
else if (newAlias != alias)
{
newAlias = GetUniqAlias(connection, newAlias);
}
var commandText = string.Format(
"update tenants_tenants " +
"set " +
" status={0}, " +
" alias = '{1}', " +
" last_modified='{2}', " +
" statuschanged='{2}' " +
"where alias = '{3}'",
status.ToString("d"),
newAlias,
DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"),
alias);
if (!string.IsNullOrEmpty(whereCondition))
{
commandText += (" and " + whereCondition);
}
connection.CreateCommand(commandText).WithTimeout(120).ExecuteNonQuery();
}
}
public override void RunJob()
{
Logger.DebugFormat("begin restore data for module {0}", _module.ModuleName);
SetStepsCount(_module.Tables.Count(t => !IgnoredTables.Contains(t.Name)));
using (var connection = _factory.OpenConnection())
{
foreach (var table in _module.GetTablesOrdered().Where(t => !IgnoredTables.Contains(t.Name) && t.InsertMethod != InsertMethod.None))
{
Logger.DebugFormat("begin restore table {0}", table.Name);
var transactionsCommited = 0;
var rowsInserted = 0;
ActionInvoker.Try(
state =>
RestoreTable(connection.Fix(), (TableInfo)state, ref transactionsCommited,
ref rowsInserted), table, 5,
onAttemptFailure: error => _columnMapper.Rollback(),
onFailure: error => { throw ThrowHelper.CantRestoreTable(table.Name, error); });
SetStepCompleted();
Logger.DebugFormat("{0} rows inserted for table {1}", rowsInserted, table.Name);
}
}
Logger.DebugFormat("end restore data for module {0}", _module.ModuleName);
}
private string GetTenantAlias(DbFactory dbFactory)
{
using (var connection = dbFactory.OpenConnection())
{
var commandText = "select alias from tenants_tenants where id = " + TenantId;
return(connection.CreateCommand(commandText).WithTimeout(120).ExecuteScalar <string>());
}
}
private string GetTenantAlias(DbFactory dbFactory)
{
using var connection = dbFactory.OpenConnection();
var command = connection.CreateCommand();
command.CommandText = "select alias from tenants_tenants where id = " + TenantId;
return((string)command.WithTimeout(120).ExecuteScalar());
}
private void DoBackupModule(IDataWriteOperator writer, DbFactory dbFactory, IModuleSpecifics module)
{
Logger.Debug("begin saving data for module {0}", module.ModuleName);
var tablesToProcess = module.Tables.Where(t => !IgnoredTables.Contains(t.Name) && t.InsertMethod != InsertMethod.None).ToList();
int tablesCount = tablesToProcess.Count;
int tablesProcessed = 0;
using (var connection = dbFactory.OpenConnection())
{
foreach (var table in tablesToProcess)
{
Logger.Debug("begin load table {0}", table.Name);
using (var data = new DataTable(table.Name))
{
ActionInvoker.Try(
state =>
{
data.Clear();
var t = (TableInfo)state;
var dataAdapter = dbFactory.CreateDataAdapter();
dataAdapter.SelectCommand = module.CreateSelectCommand(connection.Fix(), TenantId, t).WithTimeout(600);
((DbDataAdapter)dataAdapter).Fill(data);
},
table,
maxAttempts: 5,
onFailure: error => { throw ThrowHelper.CantBackupTable(table.Name, error); },
onAttemptFailure: error => Logger.Warn("backup attempt failure: {0}", error));
foreach (var col in data.Columns.Cast <DataColumn>().Where(col => col.DataType == typeof(DateTime)))
{
col.DateTimeMode = DataSetDateTime.Unspecified;
}
module.PrepareData(data);
Logger.Debug("end load table {0}", table.Name);
Logger.Debug("begin saving table {0}", table.Name);
var tmp = Path.GetTempFileName();
using (var file = File.OpenWrite(tmp))
{
data.WriteXml(file, XmlWriteMode.WriteSchema);
data.Clear();
}
writer.WriteEntry(KeyHelper.GetTableZipKey(module, data.TableName), tmp);
File.Delete(tmp);
Logger.Debug("end saving table {0}", table.Name);
}
SetCurrentStepProgress((int)((++tablesProcessed * 100) / (double)tablesCount));
}
}
Logger.Debug("end saving data for module {0}", module.ModuleName);
}
private List <object[]> GetDataWithPrimary(string t, List <string> columns, string primary, int start, int step)
{
using var connection = DbFactory.OpenConnection();
var command = connection.CreateCommand();
var selects = string.Join(',', columns);
command.CommandText = $"select {selects} from {t} where {primary} BETWEEN {start} and {start + step} ";
return(ExecuteList(command));
}
private List <object[]> GetData(string t, List <string> columns, int offset)
{
using var connection = DbFactory.OpenConnection();
var command = connection.CreateCommand();
var selects = string.Join(',', columns);
command.CommandText = $"select {selects} from {t} LIMIT {offset}, {Limit}";
return(ExecuteList(command));
}
private void SetTenantActive(int tenantId)
{
using var connection = DbFactory.OpenConnection();
var commandText = string.Format(
"update tenants_tenants " +
"set " +
" status={0}, " +
" last_modified='{1}', " +
" statuschanged='{1}' " +
"where id = '{2}'",
(int)TenantStatus.Active,
DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"),
tenantId);
connection.CreateCommand().WithTimeout(120).ExecuteNonQuery();
}
private int SelectCount(string t)
{
try
{
using var connection = DbFactory.OpenConnection();
using var analyzeCommand = connection.CreateCommand();
analyzeCommand.CommandText = $"analyze table {t}";
analyzeCommand.ExecuteNonQuery();
using var command = connection.CreateCommand();
command.CommandText = "select TABLE_ROWS from INFORMATION_SCHEMA.TABLES where TABLE_NAME = '" + t + "'";
return(int.Parse(command.ExecuteScalar().ToString()));
}
catch (Exception e)
{
Logger.Error(e);
throw;
}
}
private void DoBackupModule(IDataWriteOperator writer, DbFactory dbFactory, IModuleSpecifics module)
{
InvokeInfo("begin saving data for module {0}", module.ModuleName);
int tablesCount = module.Tables.Count();
int tablesProcessed = 0;
using (var connection = dbFactory.OpenConnection(module.ConnectionStringName))
{
foreach (var table in module.Tables)
{
InvokeInfo("begin saving table {0}", table.Name);
using (var data = new DataTable(table.Name))
{
ActionInvoker.Try(
state =>
{
data.Clear();
var t = (TableInfo)state;
var dataAdapter = dbFactory.CreateDataAdapter(module.ConnectionStringName);
dataAdapter.SelectCommand = module.CreateSelectCommand(connection.Fix(), Tenant.TenantId, t).WithTimeout(600);
((DbDataAdapter)dataAdapter).Fill(data);
},
table,
maxAttempts: 5,
onFailure: error => { throw ThrowHelper.CantBackupTable(table.Name, error); },
onAttemptFailure: error => InvokeWarning("backup attempt failure: {0}", error));
foreach (var col in data.Columns.Cast<DataColumn>().Where(col => col.DataType == typeof(DateTime)))
{
col.DateTimeMode = DataSetDateTime.Unspecified;
}
var stream = writer.BeginWriteEntry(KeyHelper.GetTableZipKey(module, data.TableName));
data.WriteXml(stream, XmlWriteMode.WriteSchema);
writer.EndWriteEntry();
data.Clear();
}
SetStepProgress((int)((++tablesProcessed*100)/(double)tablesCount));
}
}
InvokeInfo("end saving data for module {0}", module.ModuleName);
}
private void DoDeleteModule(DbFactory dbFactory, IModuleSpecifics module)
{
InvokeInfo("begin delete data for module ({0})", module.ModuleName);
int tablesCount = module.Tables.Count();
int tablesProcessed = 0;
using (var connection = dbFactory.OpenConnection(module.ConnectionStringName))
{
foreach (var table in module.GetTablesOrdered().Reverse().Where(t => !IgnoredTables.Contains(t.Name)))
{
ActionInvoker.Try(state =>
{
var t = (TableInfo)state;
module.CreateDeleteCommand(connection.Fix(), Tenant.TenantId, t).WithTimeout(120).ExecuteNonQuery();
}, table, 5, onFailure: error => { throw ThrowHelper.CantDeleteTable(table.Name, error); });
SetStepProgress((int)((++tablesProcessed*100)/(double)tablesCount));
}
}
InvokeInfo("end delete data for module ({0})", module.ModuleName);
}
private void DoBackupModule(IDataWriteOperator writer, DbFactory dbFactory, IModuleSpecifics module)
{
InvokeInfo("begin saving data for module {0}", module.ModuleName);
int tablesCount = module.Tables.Count();
int tablesProcessed = 0;
using (var connection = dbFactory.OpenConnection(module.ConnectionStringName))
{
foreach (var table in module.Tables)
{
InvokeInfo("begin saving table {0}", table.Name);
using (var data = new DataTable(table.Name))
{
ActionInvoker.Try(
state =>
{
data.Clear();
var t = (TableInfo)state;
var dataAdapter = dbFactory.CreateDataAdapter(module.ConnectionStringName);
dataAdapter.SelectCommand = module.CreateSelectCommand(connection.Fix(), Tenant.TenantId, t).WithTimeout(600);
((DbDataAdapter)dataAdapter).Fill(data);
},
table,
maxAttempts: 5,
onFailure: error => { throw ThrowHelper.CantBackupTable(table.Name, error); },
onAttemptFailure: error => InvokeWarning("backup attempt failure: {0}", error));
foreach (var col in data.Columns.Cast <DataColumn>().Where(col => col.DataType == typeof(DateTime)))
{
col.DateTimeMode = DataSetDateTime.Unspecified;
}
var stream = writer.BeginWriteEntry(KeyHelper.GetTableZipKey(module, data.TableName));
data.WriteXml(stream, XmlWriteMode.WriteSchema);
writer.EndWriteEntry();
data.Clear();
}
SetStepProgress((int)((++tablesProcessed * 100) / (double)tablesCount));
}
}
InvokeInfo("end saving data for module {0}", module.ModuleName);
}
private void DoDeleteModule(DbFactory dbFactory, IModuleSpecifics module)
{
InvokeInfo("begin delete data for module ({0})", module.ModuleName);
int tablesCount = module.Tables.Count();
int tablesProcessed = 0;
using (var connection = dbFactory.OpenConnection(module.ConnectionStringName))
{
foreach (var table in module.GetTablesOrdered().Reverse().Where(t => !IgnoredTables.Contains(t.Name)))
{
ActionInvoker.Try(state =>
{
var t = (TableInfo)state;
module.CreateDeleteCommand(connection.Fix(), Tenant.TenantId, t).WithTimeout(120).ExecuteNonQuery();
}, table, 5, onFailure: error => { throw ThrowHelper.CantDeleteTable(table.Name, error); });
SetStepProgress((int)((++tablesProcessed * 100) / (double)tablesCount));
}
}
InvokeInfo("end delete data for module ({0})", module.ModuleName);
}
private List <IGrouping <string, BackupFileInfo> > GetFilesGroup(DbFactory dbFactory)
{
var files = GetFilesToProcess().ToList();
var exclude = new List <string>();
using (var db = dbFactory.OpenConnection())
using (var command = db.CreateCommand())
{
command.CommandText = "select storage_path from backup_backup where tenant_id = " + TenantId + " and storage_type = 0 and storage_path is not null";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
exclude.Add(reader.GetString(0));
}
}
}
files = files.Where(f => !exclude.Any(e => f.Path.Contains(string.Format("/file_{0}/", e)))).ToList();
return(files.GroupBy(file => file.Module).ToList());
}
private void DumpTableScheme(string t, string dir)
{
try
{
Logger.DebugFormat("dump table scheme start {0}", t);
using (var connection = DbFactory.OpenConnection())
{
var command = connection.CreateCommand();
command.CommandText = string.Format("SHOW CREATE TABLE `{0}`", t);
var createScheme = ExecuteList(command);
var creates = new StringBuilder();
creates.AppendFormat("DROP TABLE IF EXISTS `{0}`;", t);
creates.AppendLine();
creates.Append(createScheme
.Select(r => Convert.ToString(r[1]))
.FirstOrDefault());
creates.Append(";");
var path = Path.Combine(dir, t);
using (var stream = File.OpenWrite(path))
{
var bytes = Encoding.UTF8.GetBytes(creates.ToString());
stream.Write(bytes, 0, bytes.Length);
}
SetStepCompleted();
}
Logger.DebugFormat("dump table scheme stop {0}", t);
}
catch (Exception e)
{
Logger.Error(e);
throw;
}
}
private void SaveTenant(DbFactory dbFactory, TenantStatus status, string newAlias = null, string whereCondition = null)
{
using (var connection = dbFactory.OpenConnection("core"))
{
if (newAlias == null)
{
newAlias = Tenant.TenantAlias;
}
else if (newAlias != Tenant.TenantAlias)
{
newAlias = GetUniqAlias(connection, newAlias);
}
var commandText = string.Format(
"update tenants_tenants " +
"set " +
" status={0}, " +
" alias = '{1}', " +
" last_modified='{2}', " +
" statuschanged='{2}' " +
"where alias = '{3}'",
status.ToString("d"),
newAlias,
DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"),
Tenant.TenantAlias);
if (!string.IsNullOrEmpty(whereCondition))
commandText += (" and " + whereCondition);
connection.CreateCommand(commandText).WithTimeout(120).ExecuteNonQuery();
}
}
private static void SetTenantActive(DbFactory dbFactory, int tenantId)
{
using (var connection = dbFactory.OpenConnection("core"))
{
var commandText = string.Format(
"update tenants_tenants " +
"set " +
" status={0}, " +
" last_modified='{1}', " +
" statuschanged='{1}' " +
"where id = '{2}'",
(int)TenantStatus.Active,
DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"),
tenantId);
connection.CreateCommand(commandText).WithTimeout(120).ExecuteNonQuery();
}
}
private void DumpTableData(string t, string dir, int count)
{
try
{
if (count == 0)
{
Logger.DebugFormat("dump table data stop {0}", t);
SetStepCompleted(2);
return;
}
Logger.DebugFormat("dump table data start {0}", t);
var searchWithPrimary = false;
string primaryIndex;
var primaryIndexStep = 0;
var primaryIndexStart = 0;
List <string> columns;
using (var connection = DbFactory.OpenConnection())
{
var command = connection.CreateCommand();
command.CommandText = string.Format("SHOW COLUMNS FROM `{0}`", t);
columns = ExecuteList(command).Select(r => "`" + Convert.ToString(r[0]) + "`").ToList();
if (command.CommandText.Contains("tenants_quota") || command.CommandText.Contains("webstudio_settings"))
{
}
}
using (var connection = DbFactory.OpenConnection())
{
var command = connection.CreateCommand();
command.CommandText = string.Format("select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_SCHEMA = '{0}' and TABLE_NAME = '{1}' and COLUMN_KEY = 'PRI' and DATA_TYPE = 'int'", connection.Database, t);
primaryIndex = ExecuteList(command).ConvertAll(r => Convert.ToString(r[0])).FirstOrDefault();
}
using (var connection = DbFactory.OpenConnection())
{
var command = connection.CreateCommand();
command.CommandText = string.Format("SHOW INDEXES FROM {0} WHERE COLUMN_NAME='{1}' AND seq_in_index=1", t, primaryIndex);
var isLeft = ExecuteList(command);
searchWithPrimary = isLeft.Count == 1;
}
if (searchWithPrimary)
{
using var connection = DbFactory.OpenConnection();
var command = connection.CreateCommand();
command.CommandText = string.Format("select max({1}), min({1}) from {0}", t, primaryIndex);
var minMax = ExecuteList(command).ConvertAll(r => new Tuple <int, int>(Convert.ToInt32(r[0]), Convert.ToInt32(r[1]))).FirstOrDefault();
primaryIndexStart = minMax.Item2;
primaryIndexStep = (minMax.Item1 - minMax.Item2) / count;
if (primaryIndexStep < Limit)
{
primaryIndexStep = Limit;
}
}
var path = Path.Combine(dir, t);
var offset = 0;
do
{
List <object[]> result;
if (searchWithPrimary)
{
result = GetDataWithPrimary(t, columns, primaryIndex, primaryIndexStart, primaryIndexStep);
primaryIndexStart += primaryIndexStep;
}
else
{
result = GetData(t, columns, offset);
}
offset += Limit;
var resultCount = result.Count;
if (resultCount == 0)
{
break;
}
SaveToFile(path, t, columns, result);
if (resultCount < Limit)
{
break;
}
} while (true);
SetStepCompleted();
Logger.DebugFormat("dump table data stop {0}", t);
}
catch (Exception e)
{
Logger.Error(e);
throw;
}
}
private void DoBackupStorage(IDataWriteOperator writer, DbFactory dbFactory)
{
Logger.Debug("begin backup storage");
var files = GetFilesToProcess();
var exclude = new List <string>();
using (var db = dbFactory.OpenConnection())
using (var command = db.CreateCommand())
{
command.CommandText = "select storage_path from backup_backup where tenant_id = " + TenantId + " and storage_type = 0 and storage_path is not null";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
exclude.Add(reader.GetString(0));
}
}
}
files = files.Where(f => !exclude.Any(e => f.Path.Contains(string.Format("/file_{0}/", e))));
var fileGroups = files.GroupBy(file => file.Module).ToList();
var groupsProcessed = 0;
foreach (var group in fileGroups)
{
var storage = StorageFactory.GetStorage(ConfigPath, TenantId.ToString(), group.Key);
foreach (var file in group)
{
ActionInvoker.Try(state =>
{
var f = (BackupFileInfo)state;
using (var fileStream = storage.GetReadStream(f.Domain, f.Path))
{
var tmp = Path.GetTempFileName();
try
{
using (var tmpFile = File.OpenWrite(tmp))
{
fileStream.CopyTo(tmpFile);
}
writer.WriteEntry(KeyHelper.GetFileZipKey(file), tmp);
}
finally
{
if (File.Exists(tmp))
{
File.Delete(tmp);
}
}
}
}, file, 5, error => Logger.Warn("can't backup file ({0}:{1}): {2}", file.Module, file.Path, error));
}
SetCurrentStepProgress((int)(++groupsProcessed * 100 / (double)fileGroups.Count));
}
if (fileGroups.Count == 0)
{
SetStepCompleted();
}
var restoreInfoXml = new XElement(
"storage_restore",
fileGroups
.SelectMany(group => group.Select(file => (object)file.ToXElement()))
.ToArray());
var tmpPath = Path.GetTempFileName();
using (var tmpFile = File.OpenWrite(tmpPath))
{
restoreInfoXml.WriteTo(tmpFile);
}
writer.WriteEntry(KeyHelper.GetStorageRestoreInfoZipKey(), tmpPath);
File.Delete(tmpPath);
Logger.Debug("end backup storage");
}
private void DoDump(IDataWriteOperator writer)
{
var tmp = Path.GetTempFileName();
File.AppendAllText(tmp, true.ToString());
writer.WriteEntry(KeyHelper.GetDumpKey(), tmp);
List <string> tables;
var files = new List <BackupFileInfo>();
using (var connection = DbFactory.OpenConnection())
{
var command = connection.CreateCommand();
command.CommandText = "show tables";
tables = ExecuteList(command).Select(r => Convert.ToString(r[0])).ToList();
}
/* using (var dbManager = new DbManager("default", 100000))
* {
* tables = dbManager.ExecuteList("show tables;").Select(r => Convert.ToString(r[0])).ToList();
* }*/
var stepscount = tables.Count * 4; // (schema + data) * (dump + zip)
if (ProcessStorage)
{
var tenants = TenantManager.GetTenants(false).Select(r => r.TenantId);
foreach (var t in tenants)
{
files.AddRange(GetFiles(t));
}
stepscount += files.Count * 2 + 1;
Logger.Debug("files:" + files.Count);
}
SetStepsCount(stepscount);
var excluded = ModuleProvider.AllModules.Where(r => IgnoredModules.Contains(r.ModuleName)).SelectMany(r => r.Tables).Select(r => r.Name).ToList();
excluded.AddRange(IgnoredTables);
excluded.Add("res_");
var dir = Path.GetDirectoryName(BackupFilePath);
var subDir = Path.Combine(dir, Path.GetFileNameWithoutExtension(BackupFilePath));
var schemeDir = Path.Combine(subDir, KeyHelper.GetDatabaseSchema());
var dataDir = Path.Combine(subDir, KeyHelper.GetDatabaseData());
if (!Directory.Exists(schemeDir))
{
Directory.CreateDirectory(schemeDir);
}
if (!Directory.Exists(dataDir))
{
Directory.CreateDirectory(dataDir);
}
var dict = tables.ToDictionary(t => t, SelectCount);
tables.Sort((pair1, pair2) => dict[pair1].CompareTo(dict[pair2]));
for (var i = 0; i < tables.Count; i += TasksLimit)
{
var tasks = new List <Task>(TasksLimit * 2);
for (var j = 0; j < TasksLimit && i + j < tables.Count; j++)
{
var t = tables[i + j];
tasks.Add(Task.Run(() => DumpTableScheme(t, schemeDir)));
if (!excluded.Any(t.StartsWith))
{
tasks.Add(Task.Run(() => DumpTableData(t, dataDir, dict[t])));
}
else
{
SetStepCompleted(2);
}
}
Task.WaitAll(tasks.ToArray());
ArchiveDir(writer, subDir);
}
Logger.DebugFormat("dir remove start {0}", subDir);
Directory.Delete(subDir, true);
Logger.DebugFormat("dir remove end {0}", subDir);
if (ProcessStorage)
{
DoDumpStorage(writer, files);
}
}