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