protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { //optimization: 1) do not include "deleted" rows, 2) backup mail only for the last 30 days switch (table.Name) { case "mail_mailbox": return string.Format("where t.tenant = {0} and t.is_removed <> 1", tenantId); //condition on chain_date because of Bug 18855 - transfer mail only for the last 30 days case "mail_mail": return string.Format("inner join mail_mailbox t1 on t1.id = t.id_mailbox " + "where t.tenant = {0} and t1.is_removed <> 1 and t.chain_date > '{1}'", tenantId, DateTime.UtcNow.Subtract(TimeSpan.FromDays(30)).ToString("yyyy-MM-dd HH:mm:ss")); case "mail_attachment": case "mail_tag_mail": return string.Format("inner join mail_mail as t1 on t1.id = t.id_mail " + "inner join mail_mailbox as t2 on t2.id = t1.id_mailbox " + "where t1.tenant = {0} and t2.is_removed <> 1 and t1.chain_date > '{1}'", tenantId, DateTime.UtcNow.Subtract(TimeSpan.FromDays(30)).ToString("yyyy-MM-dd HH:mm:ss")); case "mail_chain": return string.Format("inner join mail_mailbox t1 on t1.id = t.id_mailbox " + "where t.tenant = {0} and t1.is_removed <> 1", tenantId); default: return base.GetSelectCommandConditionText(tenantId, table); } }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, ref object value) { //we insert tenant as suspended so it can't be accessed before restore operation is finished if (table.Name.Equals("tenants_tenants", StringComparison.InvariantCultureIgnoreCase) && columnName.Equals("status", StringComparison.InvariantCultureIgnoreCase)) { value = (int)TenantStatus.Restoring; return true; } if (table.Name.Equals("tenants_quotarow", StringComparison.InvariantCultureIgnoreCase) && columnName.Equals("last_modified", StringComparison.InvariantCultureIgnoreCase)) { value = DateTime.UtcNow; return true; } if ((table.Name == "core_user" || table.Name == "core_group") && columnName == "last_modified") { value = DateTime.UtcNow.AddMinutes(2); return true; } return base.TryPrepareValue(connection, columnMapper, table, columnName, ref value); }
protected override bool TryPrepareRow(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, DataRowInfo row, out Dictionary<string, object> preparedRow) { if (table.Name == "tenants_tenants" && string.IsNullOrEmpty(Convert.ToString(row["payment_id"]))) { var oldTenantID = Convert.ToInt32(row["id"]); row["payment_id"] = Core.CoreContext.Configuration.GetKey(oldTenantID); } return base.TryPrepareRow(connection, columnMapper, table, row, out preparedRow); }
protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (table.Name == "calendar_calendar_item" || table.Name == "calendar_calendar_user") return "inner join calendar_calendars as t1 on t1.id = t.calendar_id where t1.tenant = " + tenantId; if (table.Name == "calendar_event_item" || table.Name == "calendar_event_user") return "inner join calendar_events as t1 on t1.id = t.event_id where t1.tenant = " + tenantId; return base.GetSelectCommandConditionText(tenantId, table); }
public IDbCommand CreateInsertCommand(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, DataRowInfo row) { if (table.InsertMethod == InsertMethod.None) return null; Dictionary<string, object> valuesForInsert; if (!TryPrepareRow(connection, columnMapper, table, row, out valuesForInsert)) return null; var insertCommantText = string.Format("{0} into {1}({2}) values({3});", table.InsertMethod != InsertMethod.Ignore ? table.InsertMethod.ToString().ToLower() : "insert ignore", table.Name, string.Join(",", valuesForInsert.Keys), string.Join(",", valuesForInsert.Keys.Select(x => "@" + x))); IDbCommand command = connection.CreateCommand(insertCommantText); foreach (var parameter in valuesForInsert) { command.AddParameter(parameter.Key, parameter.Value); } return command; }
protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (table.Name == "forum_answer_variant") return "inner join forum_answer as t1 on t1.id = t.answer_id where t1.TenantID = " + tenantId; if (table.Name == "forum_variant") return "inner join forum_question as t1 on t1.id = t.question_id where t1.TenantID = " + tenantId; if (table.Name == "forum_topic_tag") return "inner join forum_topic as t1 on t1.id = t.topic_id where t1.TenantID = " + tenantId; return base.GetSelectCommandConditionText(tenantId, table); }
protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (table.Name == "files_folder_tree") return "inner join files_folder as t1 on t1.id = t.folder_id where t1.tenant_id = " + tenantId; return base.GetSelectCommandConditionText(tenantId, table); }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, IEnumerable<RelationInfo> relations, ref object value) { relations = relations.ToList(); if (relations.All(x => x.ChildTable == "mail_chain" && x.ChildColumn == "tags")) { var mappedTags = new List<string>(); foreach (var tag in value.ToString().Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x))) { object tagId; if (tag > 0) { tagId = columnMapper.GetMapping("mail_tag", "id", tag); } else { tagId = columnMapper.GetMapping("crm_tag", "id", -tag); if (tagId != null) { tagId = -Convert.ToInt32(tagId); } } if (tagId != null) { mappedTags.Add(tagId.ToString()); } } value = string.Join(",", mappedTags.ToArray()); return true; } return base.TryPrepareValue(connection, columnMapper, table, columnName, relations, ref value); }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, IEnumerable<RelationInfo> relations, ref object value) { relations = relations.ToList(); if (relations.All(x => x.ChildTable == "forum_attachment" && x.ChildColumn == "path")) { value = PreparePath(columnMapper, "\\", Convert.ToString(value)); return value != null; } return base.TryPrepareValue(connection, columnMapper, table, columnName, relations, ref value); }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, ref object value) { if (table.Name == "core_usergroup" && columnName == "last_modified") { value = DateTime.UtcNow; return true; } return base.TryPrepareValue(connection, columnMapper, table, columnName, ref value); }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, IEnumerable<RelationInfo> relations, ref object value) { if (table.Name == "projects_tasks_order" && columnName == "task_order") { value = Regex.Replace( Convert.ToString(value), @"(?<=""tasks"":\[(\d+,)*)\d+,?", match => { var mappedId = Convert.ToString(columnMapper.GetMapping("projects_tasks", "id", match.Value.TrimEnd(','))); return !string.IsNullOrEmpty(mappedId) && match.Value.EndsWith(",") ? mappedId + "," : mappedId; }, RegexOptions.Compiled); value = Regex.Replace( Convert.ToString(value), @"(?<=""milestones"":\[(\d+,)*)\d+,?", match => { var mappedId = Convert.ToString(columnMapper.GetMapping("projects_milestones", "id", match.Value.TrimEnd(','))); return !string.IsNullOrEmpty(mappedId) && match.Value.EndsWith(",") ? mappedId + "," : mappedId; }, RegexOptions.Compiled); return true; } return base.TryPrepareValue(connection, columnMapper, table, columnName, relations, ref value); }
protected virtual bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, IEnumerable<RelationInfo> relations, ref object value) { return TryPrepareValue(connection, columnMapper, relations.Single(), ref value); }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, IEnumerable<RelationInfo> relations, ref object value) { var relationList = relations.ToList(); if (relationList.All(x => x.ChildTable == "files_security" && x.ChildColumn == "subject")) { //note: value could be ShareForEveryoneID and in that case result should be always false var strVal = Convert.ToString(value); if (Helpers.IsEmptyOrSystemUser(strVal) || Helpers.IsEmptyOrSystemGroup(strVal)) return true; foreach (var relation in relationList) { var mapping = columnMapper.GetMapping(relation.ParentTable, relation.ParentColumn, value); if (mapping != null) { value = mapping; return true; } } return false; } return base.TryPrepareValue(connection, columnMapper, table, columnName, relationList, ref value); }
protected virtual bool TryPrepareRow(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, DataRowInfo row, out Dictionary<string, object> preparedRow) { preparedRow = new Dictionary<string, object>(); var parentRelations = TableRelations .Where(x => x.FitsForRow(row) && x.Importance != RelationImportance.Low) .GroupBy(x => x.ChildColumn) .ToDictionary(x => x.Key); foreach (var columnName in row.ColumnNames) { if (table.IdType == IdType.Autoincrement && columnName.Equals(table.IdColumn, StringComparison.OrdinalIgnoreCase)) continue; var val = row[columnName]; if (!parentRelations.ContainsKey(columnName)) { if (!TryPrepareValue(connection, columnMapper, table, columnName, ref val)) return false; } else { if (!TryPrepareValue(connection, columnMapper, table, columnName, parentRelations[columnName], ref val)) return false; } preparedRow.Add(columnName, val); } return true; }
protected virtual bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, ref object value) { if (columnName.Equals(table.TenantColumn, StringComparison.OrdinalIgnoreCase)) { int tenantMapping = columnMapper.GetTenantMapping(); if (tenantMapping < 1) return false; value = tenantMapping; return true; } if (table.UserIDColumns.Any(x => columnName.Equals(x, StringComparison.OrdinalIgnoreCase))) { var strVal = Convert.ToString(value); string userMapping = columnMapper.GetUserMapping(strVal); if (userMapping == null) return Helpers.IsEmptyOrSystemUser(strVal); value = userMapping; return true; } var mapping = columnMapper.GetMapping(table.Name, columnName, value); if (mapping != null) value = mapping; return true; }
protected virtual string GetDeleteCommandConditionText(int tenantId, TableInfo table) { return GetSelectCommandConditionText(tenantId, table); }
protected virtual string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (!table.HasTenantColumn()) throw ThrowHelper.CantDetectTenant(table.Name); return string.Format("where t.{0} = {1}", table.TenantColumn, tenantId); }
private IEnumerable<DataRowInfo> GetRows(TableInfo table, Stream xmlStream) { if (xmlStream == null) return Enumerable.Empty<DataRowInfo>(); var rows = DataRowInfoReader.ReadFromStream(xmlStream); var selfRelation = _module.TableRelations.SingleOrDefault(x => x.ChildTable == table.Name && x.IsSelfRelation()); if (selfRelation != null) { rows = rows .ToTree(x => x[selfRelation.ParentColumn], x => x[selfRelation.ChildColumn]) .SelectMany(x => OrderNode(x)); } return rows; }
protected override bool TryPrepareRow(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, DataRowInfo row, out Dictionary<string, object> preparedRow) { if (row.TableName == "files_thirdparty_id_mapping") { //todo: think... preparedRow = new Dictionary<string, object>(); object folderId = null; var sboxId = Regex.Replace(row[1].ToString(), @"(?<=sbox-)\d+", match => { folderId = columnMapper.GetMapping("files_thirdparty_account", "id", match.Value); return Convert.ToString(folderId); }, RegexOptions.Compiled); if (folderId == null) return false; var hashBytes = MD5.Create().ComputeHash(Encoding.UTF8.GetBytes(sboxId)); var hashedId = BitConverter.ToString(hashBytes).Replace("-", "").ToLower(); preparedRow.Add("hash_id", hashedId); preparedRow.Add("id", sboxId); preparedRow.Add("tenant_id", columnMapper.GetTenantMapping()); columnMapper.SetMapping("files_thirdparty_id_mapping", "hash_id", row["hash_id"], hashedId); return true; } return base.TryPrepareRow(connection, columnMapper, table, row, out preparedRow); }
public IDbCommand CreateSelectCommand(IDbConnection connection, int tenantId, TableInfo table) { return connection.CreateCommand(string.Format("select t.* from {0} as t {1};", table.Name, GetSelectCommandConditionText(tenantId, table))); }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, ref object value) { if (table.Name == "files_file" && columnName == "id") { //In `files_file` possible multiple rows with the same value of the column `id`. //If such row was not inserted before current iteration then we need to create value for the `id` first. var fileId = columnMapper.GetMapping(table.Name, columnName, value); if (fileId == null) { fileId = connection .CreateCommand("select max(id) from files_file;") .WithTimeout(120) .ExecuteScalar<int>() + 1; columnMapper.SetMapping(table.Name, columnName, value, fileId); } value = fileId; return true; } return base.TryPrepareValue(connection, columnMapper, table, columnName, ref value); }
protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (table.Name == "projects_project_tag" || table.Name == "projects_following_project_participant") return "inner join projects_projects as t1 on t1.id = t.project_id where t1.tenant_id = " + tenantId; return base.GetSelectCommandConditionText(tenantId, table); }
protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (table.Name == "feed_users") return "inner join core_user t1 on t1.id = t.user_id where t1.tenant = " + tenantId; return base.GetSelectCommandConditionText(tenantId, table); }
private void RestoreTable(IDbConnection connection, TableInfo tableInfo, ref int transactionsCommited, ref int rowsInserted) { using (var stream = _reader.GetEntry(KeyHelper.GetTableZipKey(_module, tableInfo.Name))) { var lowImportanceRelations = _module .TableRelations .Where(r => string.Equals(r.ParentTable, tableInfo.Name, StringComparison.InvariantCultureIgnoreCase)) .Where(r => r.Importance == RelationImportance.Low && !r.IsSelfRelation()) .Select(r => Tuple.Create(r, _module.Tables.Single(t => t.Name == r.ChildTable))) .ToList(); foreach (IEnumerable<DataRowInfo> rows in GetRows(tableInfo, stream).Skip(transactionsCommited * TransactionLength).MakeParts(TransactionLength)) { using (var transaction = connection.BeginTransaction()) { int rowsSuccess = 0; foreach (DataRowInfo row in rows) { if (_replaceDate) { foreach (var column in tableInfo.DateColumns) { _columnMapper.SetDateMapping(tableInfo.Name, column, row[column.Key]); } } object oldIdValue = null; object newIdValue = null; if (tableInfo.HasIdColumn()) { oldIdValue = row[tableInfo.IdColumn]; newIdValue = _columnMapper.GetMapping(tableInfo.Name, tableInfo.IdColumn, oldIdValue); if (newIdValue == null) { if (tableInfo.IdType == IdType.Guid) { newIdValue = Guid.NewGuid().ToString("D"); } else if (tableInfo.IdType == IdType.Integer) { newIdValue = connection .CreateCommand(string.Format("select max({0}) from {1};", tableInfo.IdColumn, tableInfo.Name)) .WithTimeout(120) .ExecuteScalar<int>() + 1; } } if (newIdValue != null) { _columnMapper.SetMapping(tableInfo.Name, tableInfo.IdColumn, oldIdValue, newIdValue); } } var insertCommand = _module.CreateInsertCommand(connection, _columnMapper, tableInfo, row); if (insertCommand == null) { WarnCantInsertRow(row); _columnMapper.Rollback(); continue; } insertCommand.WithTimeout(120).ExecuteNonQuery(); rowsSuccess++; if (tableInfo.HasIdColumn() && tableInfo.IdType == IdType.Autoincrement) { var lastIdCommand = _factory.CreateLastInsertIdCommand(_module.ConnectionStringName); lastIdCommand.Connection = connection; newIdValue = Convert.ToInt32(lastIdCommand.ExecuteScalar()); _columnMapper.SetMapping(tableInfo.Name, tableInfo.IdColumn, oldIdValue, newIdValue); } _columnMapper.Commit(); foreach (Tuple<RelationInfo, TableInfo> relation in lowImportanceRelations) { if (!relation.Item2.HasTenantColumn()) { InvokeWarning("Table {0} does not contain tenant id column. Can't apply low importance relations on such tables.", relation.Item2.Name); continue; } object oldValue = row[relation.Item1.ParentColumn]; object newValue = _columnMapper.GetMapping(relation.Item1.ParentTable, relation.Item1.ParentColumn, oldValue); connection.CreateCommand(string.Format("update {0} set {1} = {2} where {1} = {3} and {4} = {5}", relation.Item1.ChildTable, relation.Item1.ChildColumn, newValue is string ? "'" + newValue + "'" : newValue, oldValue is string ? "'" + oldValue + "'" : oldValue, relation.Item2.TenantColumn, _columnMapper.GetTenantMapping())).WithTimeout(120).ExecuteNonQuery(); } } transaction.Commit(); transactionsCommited++; rowsInserted += rowsSuccess; } } } }
protected override bool TryPrepareValue(IDbConnection connection, ColumnMapper columnMapper, TableInfo table, string columnName, IEnumerable<RelationInfo> relations, ref object value) { var relationList = relations.ToList(); if (relationList.All(x => x.ChildTable == "core_subscription" && x.ChildColumn == "object" && x.ParentTable.StartsWith("projects_"))) { var valParts = Convert.ToString(value).Split('_'); var projectId = columnMapper.GetMapping("projects_projects", "id", valParts[2]); if (projectId == null) return false; var firstRelation = relationList.First(x => x.ParentTable != "projects_projects"); var entityId = columnMapper.GetMapping(firstRelation.ParentTable, firstRelation.ParentColumn, valParts[1]); if (entityId == null) return false; value = string.Format("{0}_{1}_{2}", valParts[0], entityId, projectId); return true; } if (relationList.All(x => x.ChildTable == "core_subscription" && x.ChildColumn == "recipient") || relationList.All(x => x.ChildTable == "core_subscriptionmethod" && x.ChildColumn == "recipient") || relationList.All(x => x.ChildTable == "core_acl" && x.ChildColumn == "subject")) { var strVal = Convert.ToString(value); if (Helpers.IsEmptyOrSystemUser(strVal) || Helpers.IsEmptyOrSystemGroup(strVal)) return true; foreach (var relation in relationList) { var mapping = columnMapper.GetMapping(relation.ParentTable, relation.ParentColumn, value); if (mapping != null) { value = mapping; return true; } } return false; } return base.TryPrepareValue(connection, columnMapper, table, columnName, relationList, ref value); }
protected override string GetSelectCommandConditionText(int tenantId, TableInfo table) { if (table.Name == "crm_entity_contact") return "inner join crm_contact as t1 on t1.id = t.contact_id where t1.tenant_id = " + tenantId; if (table.Name == "crm_entity_tag") return "inner join crm_tag as t1 on t1.id = t.tag_id where t1.tenant_id = " + tenantId; return base.GetSelectCommandConditionText(tenantId, table); }
public IDbCommand CreateDeleteCommand(IDbConnection connection, int tenantId, TableInfo table) { var commandText = string.Format("delete t.* from {0} as t {1};", table.Name, GetDeleteCommandConditionText(tenantId, table)); return connection.CreateCommand(commandText); }
protected override string GetDeleteCommandConditionText(int tenantId, TableInfo table) { //delete all rows regardless of whether there is is_removed = 1 or not return base.GetSelectCommandConditionText(tenantId, table); }