public void CreateTables(DBHelper target) { StringBuilder sb = new StringBuilder(); StringBuilder sb2 = new StringBuilder(); StringBuilder sb3 = new StringBuilder(); target.RunText(string.Format(CultureInfo.InvariantCulture, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[{0}]", TableName)); target.RunText(string.Format(CultureInfo.InvariantCulture, "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[{0}]", TableNameHistory)); sb.Append(string.Format(CultureInfo.InvariantCulture, "CREATE TABLE [dbo].[{0}] ([ObjectId] [int] NOT NULL, [CreatorId] [int] NULL, [Created] [datetime] NULL, [ModifierId] [int] NULL, [Modified] [datetime] NULL", TableName)); sb2.Append(string.Format(CultureInfo.InvariantCulture, "CREATE TABLE [dbo].[{0}] ([Id] [int] IDENTITY (1, 1) NOT NULL, [ObjectId] [int] NOT NULL, [ModifierId] [int] NULL, [Modified] [datetime] NULL", TableNameHistory)); sb3.Append(string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [dbo].[{0}] ADD ", TableName)); string column; int defaultCount = 0; foreach (MyMetaField field in Fields) { column = string.Format(CultureInfo.InvariantCulture, ", [{0}] [{1}] {2}", field.Name, field.SqlName, field.Variable ? ("(" + field.Length + ") ") : ""); sb.Append(column); sb2.Append(column); if (!field.AllowNulls) sb.Append("NOT "); sb.Append("NULL"); sb2.Append("NULL"); if (!field.AllowNulls) { if (defaultCount > 0) sb3.Append(", "); sb3.Append(string.Format(CultureInfo.InvariantCulture, "CONSTRAINT [DF__{0}__{1}] DEFAULT ({2}) FOR [{1}]", TableName, field.Name, (field.DefaultValue.Length > 0 ? field.DefaultValue : "''"))); defaultCount++; } } sb.Append(") ON [PRIMARY]"); sb2.Append(") ON [PRIMARY]"); target.RunText(sb.ToString()); target.RunText(sb2.ToString()); target.RunText(string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([ObjectId]) ON [PRIMARY]", TableName)); target.RunText(string.Format(CultureInfo.InvariantCulture, "ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]", TableNameHistory)); if (defaultCount > 0) target.RunText(sb3.ToString()); target.RunSP("mdpsp_sys_CreateMetaClassHistoryTrigger", DBHelper.MP("@MetaClassId", SqlDbType.Int, MetaClassId)); //if(!Name.StartsWith("ListsEx_")) target.RunSP("mdpsp_sys_CreateMetaClassProcedure", DBHelper.MP("@MetaClassId", SqlDbType.Int, MetaClassId)); }
internal static void SetPortalParameterValue(DBHelper dbHelper, string name, string value) { if (value == null) { dbHelper.RunText("DELETE FROM [PortalConfig] WHERE [Key]=@Key" , DBHelper.MP("@Key", SqlDbType.NVarChar, 100, name) ); } else { dbHelper.RunText("IF EXISTS (SELECT 1 FROM [PortalConfig] WHERE [Key]=@Key) UPDATE [PortalConfig] SET [Value]=@Value WHERE [Key]=@Key ELSE INSERT INTO [PortalConfig] ([Key],[Value]) VALUES (@Key,@Value)" , DBHelper.MP("@Key", SqlDbType.NVarChar, 100, name) , DBHelper.MP("@Value", SqlDbType.NText, value) ); } }
public static void SaveRolePrincipals(DBHelper target, string tableName, int objectId, int roleId, IEnumerable<int> principals) { foreach (int principalId in principals) { target.RunText(string.Concat("INSERT INTO [", tableName, "] ([ObjectId],[PrincipalId],[RoleId]) VALUES (@p1,@p2,@p3)") , DBHelper.MP("@p1", SqlDbType.Int, objectId) , DBHelper.MP("@p2", SqlDbType.Int, principalId) , DBHelper.MP("@p3", SqlDbType.Int, roleId) ); } }
public void Save(DBHelper target) { target.RunText( "INSERT INTO [cls_Project] ([ProjectId],[Title],[StatusId]) VALUES (@p1,@p2,@p3)" , DBHelper.MP("@p1", SqlDbType.Int, _projectId) , DBHelper.MP("@p2", SqlDbType.NVarChar, 100, _title) , DBHelper.MP("@p3", SqlDbType.Int, _statusId) ); SaveProjectRoles(target, "cls_Project_Role_Principal", _projectId); _blockTypeInstanceId = target.RunTextInteger( "INSERT INTO [cls_TimeTrackingBlockTypeInstance] ([Title],[BlockTypeId],[ProjectId]) VALUES (@p1,@p2,@p3) SELECT @retval = SCOPE_IDENTITY()" , DBHelper.MP("@p1", SqlDbType.NVarChar, 100, _title) , DBHelper.MP("@p2", SqlDbType.Int, ProjectTTBlockTypeId) , DBHelper.MP("@p3", SqlDbType.Int, _projectId) ); SaveProjectRoles(target, "cls_TimeTrackingBlockTypeInstance_Role_Principal", _blockTypeInstanceId); }
private static void SaveRolePrincipals(DBHelper target, int objectId, int roleId, IEnumerable<int> principals) { foreach (int principalId in principals) { target.RunText("INSERT INTO [cls_TimeTrackingBlock_Role_Principal] ([ObjectId],[PrincipalId],[RoleId]) VALUES (@p1,@p2,@p3)" , DBHelper.MP("@p1", SqlDbType.Int, objectId) , DBHelper.MP("@p2", SqlDbType.Int, principalId) , DBHelper.MP("@p3", SqlDbType.Int, roleId) ); } }
public void Save(DBHelper target, Project project) { _blockTypeInstanceId = project != null ? project.BlockTypeInstanceId : NonProjectTTBlockTypeInstanceId; _blockId = target.RunTextInteger("INSERT INTO [cls_TimeTrackingBlock] ([Modified],[ModifierId],[Card],[Title],[BlockTypeInstanceId],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[OwnerId],[mc_StateId],[mc_StateMachineId],[StartDate],[ProjectId],[AreFinancesRegistered],[IsRejected]) VALUES (@p1,@p2,@p3,@p5,@p6,@p7,@p7,@p7,@p7,@p7,@p7,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14) SELECT @retval = SCOPE_IDENTITY()" , DBHelper.MP("@p1", SqlDbType.DateTime, _lastSavedDate) , DBHelper.MP("@p2", SqlDbType.Int, _lastEditorId) , DBHelper.MP("@p3", SqlDbType.NVarChar, 50, "TimeTrackingBlockDescr") , DBHelper.MP("@p5", SqlDbType.NVarChar, 100, _title) , DBHelper.MP("@p6", SqlDbType.Int, _blockTypeInstanceId) , DBHelper.MP("@p7", SqlDbType.Float, 0F) , DBHelper.MP("@p8", SqlDbType.Int, _userId) , DBHelper.MP("@p9", SqlDbType.Int, ConvertState(_statusId, _projectId > 0, ref _isRejected)) , DBHelper.MP("@p10", SqlDbType.Int, TTBlockStateMachineId) , DBHelper.MP("@p11", SqlDbType.DateTime, _startDate) , DBHelper.MP("@p12", SqlDbType.Int, _projectId > 0 ? (object)_projectId : DBNull.Value) , DBHelper.MP("@p13", SqlDbType.Bit, _areFinancesRegistered) , DBHelper.MP("@p14", SqlDbType.Bit, _isRejected) ); if (!string.IsNullOrEmpty(_managerComments)) { target.RunText("INSERT INTO [cls_TimeTrackingBlockDescr] ([TimeTrackingBlockId],[Description]) VALUES (@p1,@p2)" , DBHelper.MP("@p1", SqlDbType.Int, _blockId) , DBHelper.MP("@p2", SqlDbType.NText, _managerComments) ); } // Save role principals Helper.SaveRolePrincipals(target, "cls_TimeTrackingBlock_Role_Principal", _blockId, 6, _userId); // Owner if (project != null) project.SaveProjectRoles(target, "cls_TimeTrackingBlock_Role_Principal", _blockId); }
private static void HashPasswords(DBHelper dbHelper, string tableName, string keyColumnName, string passwordColumnName) { Dictionary<string, string> passwords = new Dictionary<string, string>(); // Read passwords from database string selectCommand = string.Format(CultureInfo.InvariantCulture, "SELECT [{0}],[{1}] FROM [{2}] WHERE [salt] IS NULL OR [hash] IS NULL", keyColumnName, passwordColumnName, tableName); using (IDataReader reader = dbHelper.RunTextDataReader(selectCommand)) { while (reader.Read()) { passwords.Add(reader[0].ToString(), reader[1].ToString()); } } RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider(); HashAlgorithm hashAlgorithm = MD5.Create(); byte[] saltBytes = new byte[5]; StringBuilder builder = new StringBuilder(); foreach (string key in passwords.Keys) { string password = passwords[key]; rng.GetBytes(saltBytes); string salt = System.Convert.ToBase64String(saltBytes); string hash = System.Convert.ToBase64String(hashAlgorithm.ComputeHash(Encoding.Unicode.GetBytes(string.Concat(password, "$", salt)))); builder.Append("UPDATE ["); builder.Append(tableName); builder.Append("] SET [salt]='"); builder.Append(salt); builder.Append("',[hash]='"); builder.Append(hash); builder.Append("' WHERE ["); builder.Append(keyColumnName); builder.Append("]='"); builder.Append(key); builder.Append("'"); builder.AppendLine(); } string updateCommand = builder.ToString(); dbHelper.RunText(updateCommand); }
private static void ConvertLists(DBHelper source, DBHelper target) { #region Copy LIST_FOLDERS to cls_ListFolder IDictionary<int, ListFolder> folders = ListFolder.LoadList(source); ListFolder.SaveList(target, folders); #endregion //load list45 IDictionary<int, ListInfo45> lists45 = ListInfo45.LoadList(source); #region LIST_TYPES [4.5] -> ListType enum[4.7] //Get metaType for ListType meta field MD47.Meta.Management.MetaFieldType mfTypeListType47 = Mediachase.Ibn.Lists.ListInfo.GetAssignedMetaClass().Fields["ListType"].GetMetaType(); // Delete old values foreach (MD47.Meta.MetaEnumItem enumItem47 in mfTypeListType47.EnumItems) { MD47.Meta.Management.MetaEnum.RemoveItem(mfTypeListType47, enumItem47.Handle); } //create listTypes in 47 int orderId = 1; foreach (string listTypeName45 in ListInfo45.ListTypes.Values) { MD47.Meta.Management.MetaEnum.AddItem(mfTypeListType47, listTypeName45, orderId++); } #endregion Dictionary<int, int> listId45to47 = new Dictionary<int, int>(); #region List[4.5] -> ListInfo[4.7] foreach (ListInfo45 listInfo45 in lists45.Values) { Mediachase.Ibn.Lists.ListInfo listInfo47 = Mediachase.Ibn.Lists.ListManager.CreateList(listInfo45.FolderId, listInfo45.Title); listInfo47.Properties["ListType"].Value = ListInfo45.ListTypes[listInfo45.TypeId]; listInfo47.Description = listInfo45.Description; listInfo47.Created = listInfo45.CreationDate; listInfo47.CreatorId = listInfo45.CreatorId; listInfo47.Status = listInfo45.StatusId; listInfo47.Save(); //List_{0}[4.5] -> List_{0}[4.6] MD47.Meta.Management.MetaClass mcList47 = Mediachase.Ibn.Lists.ListManager.GetListMetaClass(listInfo47); MD45.Configurator.MetaClass mcList45 = MD45.Configurator.MetaClass.Load(string.Format(CultureInfo.InvariantCulture, "ListsEx_{0}", listInfo45.ListId)); foreach (MD45.Configurator.MetaField mfFix in mcList45.MetaFields) { MD45.Configurator.MetaDictionary dic = mfFix.Dictionary; } MetadataPlusToMetadataConverter metaDataConverter = new MetadataPlusToMetadataConverter(mcList45, mcList47); metaDataConverter.CopyObjects(null); metaDataConverter.CopyViewPreferences(); Mediachase.Ibn.Lists.ListManager.CreateDefaultForm(mcList47); listId45to47.Add(listInfo45.ListId, listInfo47.PrimaryKeyId.Value); } #endregion #region Convert list templates foreach (MD45.Configurator.MetaClass mcList45 in MD45.Configurator.MetaClass.GetList("Mediachase.IBN40.ListTemplate", true)) { string templateName = string.IsNullOrEmpty(mcList45.FriendlyName) ? mcList45.Name : mcList45.FriendlyName; Mediachase.Ibn.Lists.ListInfo mcListInfo47 = Mediachase.Ibn.Lists.ListManager.CreateTemplate(templateName); mcListInfo47.Description = mcList45.Description; mcListInfo47.Save(); //ListTemplate{0}[4.5] -> List_{0}[4.7] MD47.Meta.Management.MetaClass mcList47 = Mediachase.Ibn.Lists.ListManager.GetListMetaClass(mcListInfo47); foreach (MD45.Configurator.MetaField mfFix in mcList45.MetaFields) { MD45.Configurator.MetaDictionary dic = mfFix.Dictionary; } MetadataPlusToMetadataConverter metaDataConverter = new MetadataPlusToMetadataConverter(mcList45, mcList47); metaDataConverter.CopyObjects(null); metaDataConverter.CopyViewPreferences(); } #endregion #region Copy access restrictions. target.RunText("DELETE [LISTINFO_ACCESS]"); using (IDataReader reader = source.RunTextDataReader("SELECT [ListId],[PrincipalId],[AllowLevel] FROM [LIST_ACCESS]")) { while (reader.Read()) { int listId45 = (int)reader["ListId"]; int principalId = (int)reader["PrincipalId"]; byte allowLevel = (byte)reader["AllowLevel"]; if (listId45to47.ContainsKey(listId45)) { int listId47 = listId45to47[listId45]; target.RunText( "INSERT INTO [LISTINFO_ACCESS] ([ListId],[PrincipalId],[AllowLevel]) VALUES (@1,@2,@3)" , DBHelper.MP("@1", SqlDbType.Int, listId47) , DBHelper.MP("@2", SqlDbType.Int, principalId) , DBHelper.MP("@3", SqlDbType.TinyInt, allowLevel) ); } } } #endregion }
private static void ConvertTimesheets(DBHelper source, DBHelper target) { IDictionary<int, Project> projects = Project.LoadList(source); #region Copy Projects to cls_Project foreach (Project project in projects.Values) { project.Save(target); } #endregion #region Copy WeekTimeSheet to cls_TimeTrackingBlock IDictionary<int, TTBlock> blocks = TTBlock.LoadList(source); string ttGlobalBlockTitle = (string)target.RunTextScalar("SELECT [Title] FROM [cls_TimeTrackingBlockTypeInstance] WHERE [ProjectId] IS NULL"); foreach (TTBlock block in blocks.Values) { Project project = null; if (block.ProjectId < 0) block.Title = ttGlobalBlockTitle; else project = projects[block.ProjectId]; block.Save(target, project); } #endregion #region Copy Timesheets to cls_TimeTrackingEntry IDictionary<int, TTEntry> entries = TTEntry.LoadList(source); foreach (TTEntry entry in entries.Values) { TTBlock block = blocks[entry.WeekTimeSheetId]; entry.BlockTypeInstanceId = block.BlockTypeInstanceId; entry.OwnerId = block.OwnerId; entry.ParentBlockId = block.BlockId; entry.Save(target); } #endregion target.RunSP("TimeTrackingBlockRecalculateDayAll"); target.RunText("UPDATE ActualFinances SET ObjectTypeId = 3, ObjectId = I.ProjectId FROM ActualFinances F JOIN cls_TimeTrackingBlock B ON (F.BlockId = B.TimeTrackingBlockId) JOIN cls_TimeTrackingBlockTypeInstance I ON (I.TimeTrackingBlockTypeInstanceId = B.BlockTypeInstanceId) WHERE F.BlockId IS NOT NULL AND F.ObjectTypeId = 14"); target.RunText("UPDATE cls_TimeTrackingEntry SET Rate = ISNULL(M.Rate, 0) FROM cls_TimeTrackingEntry E JOIN cls_TimeTrackingBlock B ON E.ParentBlockId = B.TimeTrackingBlockId JOIN cls_TimeTrackingBlockTypeInstance I ON E.BlockTypeInstanceId = I.TimeTrackingBlockTypeInstanceId LEFT JOIN PROJECT_MEMBERS M ON (I.ProjectId = M.ProjectId AND E.OwnerId = M.PrincipalId AND M.IsTeamMember = 1) WHERE B.AreFinancesRegistered = 1 AND E.Rate IS NULL"); target.RunSP("TimeTrackingRecalculateAllObjects"); }
public void Convert(int sourceCompanyId, int targetCompanyId) { bool convertAll = true; DBHelper source = new DBHelper(_sourceConnectionString); DBHelper target = new DBHelper(_targetConnectionString); source.CommandTimeout = target.CommandTimeout = _sqlCommandTimeout; #region Validate databases' versions ValidateDatabaseVersion(source, RequiredSourceVersion, true); ValidateDatabaseVersion(target, RequiredTargetVersion, false); string sourcePortalDatabase = GetPortalDB(source, sourceCompanyId); string targetPortalDatabase = GetPortalDB(target, targetCompanyId); source.Database = sourcePortalDatabase; target.Database = targetPortalDatabase; ValidateDatabaseVersion(source, RequiredSourceVersion, true); ValidateDatabaseVersion(target, RequiredTargetVersion, false); source.Database = null; target.Database = null; #endregion #region Declare variables XmlDocument doc = new XmlDocument(); string tableName, selectCommand; List<StringCollection> nameLists = new List<StringCollection>(); List<Hashtable> valueHashTables = new List<Hashtable>(); StringCollection namesCompanyId = new StringCollection(); StringCollection namesImGroupId = new StringCollection(); StringCollection namesUserId = new StringCollection(); StringCollection namesChatId = new StringCollection(); Hashtable valuesCompanyId = new Hashtable(); Hashtable valuesImGroupId = new Hashtable(); Hashtable valuesUserId = new Hashtable(); Hashtable valuesChatId = new Hashtable(); nameLists.Add(namesCompanyId); nameLists.Add(namesImGroupId); nameLists.Add(namesUserId); nameLists.Add(namesChatId); valueHashTables.Add(valuesCompanyId); valueHashTables.Add(valuesImGroupId); valueHashTables.Add(valuesUserId); valueHashTables.Add(valuesChatId); namesCompanyId.Add("company_id"); namesImGroupId.Add("imgroup_id"); namesImGroupId.Add("dep_imgroup_id"); namesImGroupId.Add("imgroupid"); namesUserId.Add("user_id"); namesUserId.Add("from_user_id"); namesUserId.Add("to_user_id"); namesUserId.Add("owner_id"); namesUserId.Add("cont_user_id"); namesUserId.Add("originalid"); namesChatId.Add("chat_id"); #endregion if (convertAll) { #region Convert Main DB SendProgress("*** Converting main database ***"); SendProgress("Deleting target company data"); target.RunSP("ASP_EmptyTables", DBHelper.MP("@company_id", SqlDbType.Int, targetCompanyId), DBHelper.MP("@delete_trial_requests", SqlDbType.Bit, true)); SendProgress("Done"); doc.Load("ConvertMain.xml"); foreach (XmlNode table in doc.SelectNodes("/tables/table")) { bool copyBinary = true; { XmlAttribute attr = table.Attributes["copyBinary"]; if (attr != null) copyBinary = bool.Parse(attr.Value); } tableName = table.Attributes["name"].Value; switch (tableName) { case "COMPANIES": valuesCompanyId[sourceCompanyId] = targetCompanyId; ArrayList skip = new ArrayList(); skip.Add("company_id"); skip.Add("domain"); skip.Add("db_name"); skip.Add("app_id"); UpdateRecord(tableName, string.Format(CultureInfo.InvariantCulture, "WHERE [company_id] = {0}", sourceCompanyId), string.Format(CultureInfo.InvariantCulture, "WHERE [company_id] = {0}", targetCompanyId), skip, source, target); break; case "IMGROUPS": selectCommand = string.Format(CultureInfo.InvariantCulture, "WHERE company_id={1}", tableName, sourceCompanyId); CopyTable(tableName, "", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, valuesImGroupId); break; case "USER": selectCommand = string.Format(CultureInfo.InvariantCulture, "U JOIN IMGROUPS G ON (G.imgroup_id = U.imgroup_id) WHERE G.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "U.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, valuesUserId); break; case "CHATS": selectCommand = string.Format(CultureInfo.InvariantCulture, "C JOIN [USER] U ON (U.user_id = C.owner_id) JOIN IMGROUPS G ON (G.imgroup_id = U.imgroup_id) WHERE G.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "C.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, valuesChatId); break; case "BINARY_DATA": selectCommand = string.Format(CultureInfo.InvariantCulture, "WHERE [fid] IN (SELECT D2.file_id FROM [FILE] D2 JOIN [USER] U1 ON (U1.user_id = D2.from_user_id) JOIN IMGROUPS G1 ON (G1.imgroup_id = U1.imgroup_id) JOIN [USER] U2 ON (U2.user_id = D2.to_user_id) JOIN IMGROUPS G2 ON (G2.imgroup_id = U2.imgroup_id) WHERE G1.company_id = {0} AND G2.company_id = {0})", sourceCompanyId); CopyTable(tableName, "", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, null); break; case "CONTACT_LIST": selectCommand = string.Format(CultureInfo.InvariantCulture, "D2 JOIN [USER] U1 ON (U1.user_id = D2.user_id) JOIN IMGROUPS G1 ON (G1.imgroup_id = U1.imgroup_id) JOIN [USER] U2 ON (U2.user_id = D2.cont_user_id) JOIN IMGROUPS G2 ON (G2.imgroup_id = U2.imgroup_id) WHERE G1.company_id = {1} AND G2.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "D2.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, null); break; case "DEPENDENCES": selectCommand = string.Format(CultureInfo.InvariantCulture, "D JOIN IMGROUPS G1 ON (G1.imgroup_id = D.imgroup_id) JOIN IMGROUPS G2 ON (G2.imgroup_id = D.DEP_IMGROUP_ID) WHERE G1.company_id = {1} AND G2.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "D.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, null); break; case "CHAT_USERS": selectCommand = string.Format(CultureInfo.InvariantCulture, "D2 JOIN [USER] U1 ON (U1.user_id = D2.user_id) JOIN IMGROUPS G1 ON (G1.imgroup_id = U1.imgroup_id) JOIN [USER] U2 ON (U2.user_id = D2.from_user_id) JOIN IMGROUPS G2 ON (G2.imgroup_id = U2.imgroup_id) WHERE G1.company_id = {1} AND G2.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "D2.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, null); break; case "AUTH_LIST": case "FILE": case "USER_MESS": selectCommand = string.Format(CultureInfo.InvariantCulture, "D2 JOIN [USER] U1 ON (U1.user_id = D2.from_user_id) JOIN IMGROUPS G1 ON (G1.imgroup_id = U1.imgroup_id) JOIN [USER] U2 ON (U2.user_id = D2.to_user_id) JOIN IMGROUPS G2 ON (G2.imgroup_id = U2.imgroup_id) WHERE G1.company_id = {1} AND G2.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "D2.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, null); break; case "PORTAL_SESSIONS": case "SESSIONS": case "STATUS_LOG": case "CHAT_MESS": selectCommand = string.Format(CultureInfo.InvariantCulture, "D1 JOIN [USER] U ON (U.user_id = D1.user_id) JOIN IMGROUPS G ON (G.imgroup_id = U.imgroup_id) WHERE G.company_id = {1}", tableName, sourceCompanyId); CopyTable(tableName, "D1.", "*", selectCommand, copyBinary, source, target, nameLists, valueHashTables, false, null); break; } } SendProgress("Hashing passwords..."); HashPasswords(target, "USER", "USER_ID", "PASSWORD"); SendProgress("Done"); SendProgress("*** Done ***"); #endregion } #region Convert portal DB SendProgress("*** Converting portal database ***"); source.Database = sourcePortalDatabase; target.Database = targetPortalDatabase; if (convertAll) { SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; doc.Load("ConvertPortal.xml"); XmlNodeList tablesCopy = doc.SelectNodes("/tables/table"); #region Clear tables in reversed order ClearTables(tablesCopy, target); #endregion #region Load calendars for imported projects Hashtable calendars = new Hashtable(); using (IDataReader reader = source.RunTextDataReader("SELECT CalendarId, ProjectId FROM [CALENDARS] WHERE ProjectId IS NOT NULL")) { while (reader.Read()) { calendars[reader[0]] = reader[1]; } } #endregion #region Copy tables CopyPortalTables(1, 1, tablesCopy, source, target, nameLists, valueHashTables); #endregion #region Update ProjectId column for CALENDARS SendProgress("Updating project calendars"); foreach (object key in calendars.Keys) { object projectId = calendars[key]; target.RunTextDataReader("UPDATE [CALENDARS] SET ProjectId=@pid WHERE CalendarId=@cid" , DBHelper.MP("@pid", SqlDbType.Int, projectId) , DBHelper.MP("@cid", SqlDbType.Int, key) ); } SendProgress("Done"); #endregion #region Copy meta data SendProgress("* Copying metadata *"); // Load meta classes ArrayList metaClasses = MyMetaClass.LoadList(source); StringBuilder sbMetaClassesToDelete = new StringBuilder(); // Load meta fields, create and copy tables foreach (MyMetaClass mc in metaClasses) { if (mc.Name.StartsWith("ListsEx_", StringComparison.Ordinal) || mc.Name.StartsWith("ListTemplate_", StringComparison.Ordinal)) { if(sbMetaClassesToDelete.Length > 0) sbMetaClassesToDelete.Append(","); sbMetaClassesToDelete.Append(mc.MetaClassId.ToString(CultureInfo.InvariantCulture)); } else { mc.LoadFields(source); SendProgress("Creating tables for MetaData class: {0}", mc.Name); mc.CreateTables(target); SendProgress("Done"); selectCommand = "";//string.Format("SELECT * FROM [{0}]", mc.TableName); CopyTable(mc.TableName, "", "*", selectCommand, true, source, target, null, null, true, null); CopyTable(mc.TableNameHistory, "", "*", selectCommand, true, source, target, null, null, true, null); } } string metaClassesToDelete = sbMetaClassesToDelete.ToString(); if (!string.IsNullOrEmpty(metaClassesToDelete)) { target.RunText(string.Concat("DELETE FROM [MetaClassMetaFieldRelation] WHERE [MetaClassId] IN (", metaClassesToDelete, ")")); target.RunText(string.Concat("DELETE FROM [MetaClass] WHERE [MetaClassId] IN (", metaClassesToDelete, ")")); } string defaultLocale = (string)source.RunTextScalar("SELECT [Locale] FROM [LANGUAGES] WHERE [IsDefault] = 1"); using (DBTransaction tran = target.BeginTransaction()) { //target.RunText("DROP TABLE [OrganizationsEx]"); //target.RunText("DROP TABLE [OrganizationsEx_History]"); Mediachase.MetaDataPlus.Configurator.MetaInstaller.RestoreFromFile(tran.SqlTran, string.Format(CultureInfo.InvariantCulture, "metadata_{0}.xml", defaultLocale)); tran.Commit(); } SendProgress("* Done *"); #endregion #region Copy GROUPS to cls_Principal using (IDataReader reader = source.RunTextDataReader("SELECT [PrincipalId], [GroupName] FROM [GROUPS] WHERE [PrincipalId] > 9")) { while (reader.Read()) { int principalId = (int)reader["PrincipalId"]; string groupName = reader["GroupName"].ToString(); target.RunText( "INSERT INTO [cls_Principal] ([PrincipalId],[Card],[Name],[Activity]) VALUES (@1,N'Department',@2,3)" , DBHelper.MP("@1", SqlDbType.Int, principalId) , DBHelper.MP("@2", SqlDbType.NVarChar, 100, groupName) ); } } #endregion #region Copy USERS to cls_Principal using (IDataReader reader = source.RunTextDataReader("SELECT [PrincipalId], [FirstName], [LastName], [Activity] FROM [USERS]")) { while (reader.Read()) { int principalId = (int)reader["PrincipalId"]; string firstName = reader["FirstName"].ToString(); string lastName = reader["LastName"].ToString(); byte activity = (byte)reader["Activity"]; target.RunText( "INSERT INTO [cls_Principal] ([PrincipalId],[Card],[Name],[Activity]) VALUES (@1,N'User',@2,@3)" , DBHelper.MP("@1", SqlDbType.Int, principalId) , DBHelper.MP("@2", SqlDbType.NVarChar, 100, lastName + ", " + firstName) , DBHelper.MP("@3", SqlDbType.Int, activity) ); } } #endregion SendProgress("Hashing passwords..."); HashPasswords(target, "USERS", "PrincipalId", "Password"); SendProgress("Done"); // Update ExpectedAssignDate for issues target.RunText("SET DATEFIRST 7 UPDATE [INCIDENTS] SET [ExpectedAssignDate] = [dbo].GetFinishDateByDuration(B.CalendarId, I.CreationDate, B.ExpectedAssignTime) FROM [INCIDENTS] I JOIN [IncidentBox] B ON (B.IncidentBoxId = I.IncidentBoxId)"); #region Convert timesheets SendProgress("* Converting timesheets *"); using (DBTransaction tran = target.BeginTransaction()) { ConvertTimesheets(source, target); tran.Commit(); } SendProgress("* Done *"); #endregion } string installationDirectoryPath = @"..\"; // TODO: get from registry Mediachase.Ibn.GlobalContext.Current = new GlobalContext(System.IO.Path.Combine(installationDirectoryPath, @"Web\portal\Apps")); #region Convert lists SendProgress("* Converting lists *"); using (MD47.DataContext dataContext47 = new MD47.DataContext(string.Empty)) { // Initialize metadata MD47.DataContext.Current = dataContext47; dataContext47.SqlContext.CommandTimeout = _sqlCommandTimeout; //MD45.MetaDataContext.Current.ConnectionString = sourcePortalConnectionString; SqlTransaction previousSourceTransaction = MD45.MetaDataContext.Current.Transaction; SqlTransaction previousTargetTransaction = MD47.DataContext.Current.SqlContext.Transaction; SqlTransaction previousDatabaseTransaction = Mediachase.IBN.Database.DbContext.Current.Transaction; using (DBTransaction tranSource = source.BeginTransaction()) using (DBTransaction tran = target.BeginTransaction()) { MD45.MetaDataContext.Current.Transaction = tranSource.SqlTran; MD47.DataContext.Current.SqlContext.Transaction = tran.SqlTran; Mediachase.IBN.Database.DbContext.Current.Transaction = tran.SqlTran; try { ConvertLists(source, target); tran.Commit(); } finally { MD45.MetaDataContext.Current.Transaction = previousSourceTransaction; MD47.DataContext.Current.SqlContext.Transaction = previousTargetTransaction; Mediachase.IBN.Database.DbContext.Current.Transaction = previousDatabaseTransaction; } } } SendProgress("* Done *"); #endregion #region Convert contacts and organizations SendProgress("* Converting contacts and organizations *"); ClientsConverter clientsConverter = new ClientsConverter(_sqlCommandTimeout); try { clientsConverter.Warning += new EventHandler<ClientsConverterEventArgs>(OnClientsConverterWarning); clientsConverter.Convert(source, target); } finally { clientsConverter.Warning -= OnClientsConverterWarning; } SendProgress("* Done *"); #endregion target.RunText("DELETE [HISTORY] WHERE [ObjectTypeId] = 21 OR [ObjectTypeId] = 22"); target.RunText("DELETE FROM [USER_SETTINGS] WHERE [Value] LIKE '%org%' OR [Value] LIKE '%vcard%' OR [Key] LIKE '%org%' OR [Key] LIKE '%vcard%'"); SendProgress("*** Done ***"); #endregion }
internal static void SetInsertIdentity(DBHelper dbh, string tableName, bool on) { dbh.RunText(string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT [{0}] {1}", tableName, on ? "ON" : "OFF")); }
public void Save(DBHelper target) { int objectTypeId = _objectTypeId; int objectId = _objectId; if (objectTypeId == 11) { objectTypeId = -1; objectId = -1; } _entryId = target.RunTextInteger("INSERT INTO [cls_TimeTrackingEntry] ([Card],[Title],[BlockTypeInstanceId],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[OwnerId],[ParentBlockId],[ObjectTypeId],[ObjectId],[TotalApproved],[Rate]) VALUES (@p1,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19) SELECT @retval = SCOPE_IDENTITY()" , DBHelper.MP("@p1", SqlDbType.NVarChar, 50, "TimeTrackingEntryDescr") , DBHelper.MP("@p5", SqlDbType.NVarChar, 100, _title) , DBHelper.MP("@p6", SqlDbType.Int, _blockTypeInstanceId) , DBHelper.MP("@p7", SqlDbType.Float, _day1) , DBHelper.MP("@p8", SqlDbType.Float, _day2) , DBHelper.MP("@p9", SqlDbType.Float, _day3) , DBHelper.MP("@p10", SqlDbType.Float, _day4) , DBHelper.MP("@p11", SqlDbType.Float, _day5) , DBHelper.MP("@p12", SqlDbType.Float, _day6) , DBHelper.MP("@p13", SqlDbType.Float, _day7) , DBHelper.MP("@p14", SqlDbType.Int, _ownerId) , DBHelper.MP("@p15", SqlDbType.Int, _parentBlockId) , DBHelper.MP("@p16", SqlDbType.Int, objectTypeId > 0 ? (object)objectTypeId : DBNull.Value) , DBHelper.MP("@p17", SqlDbType.Int, objectId > 0 ? (object)objectId : DBNull.Value) , DBHelper.MP("@p18", SqlDbType.Float, _totalApproved) , DBHelper.MP("@p19", SqlDbType.Money, _rate) ); if (!string.IsNullOrEmpty(_userComments)) { target.RunText("INSERT INTO [cls_TimeTrackingEntryDescr] ([TimeTrackingEntryId],[Comment]) VALUES (@TimeTrackingEntryId,@Comment)" , DBHelper.MP("@TimeTrackingEntryId", SqlDbType.Int, _entryId) , DBHelper.MP("@Comment", SqlDbType.NText, _userComments) ); } if (_actualId > 0) { target.RunText("UPDATE [ActualFinances] SET [BlockId]=@BlockId, [OwnerId]=@OwnerId, [TotalApproved]=@TotalApproved WHERE [ActualFinancesId]=@ActualFinancesId" , DBHelper.MP("@BlockId", SqlDbType.Int, _parentBlockId) , DBHelper.MP("@ActualFinancesId", SqlDbType.Int, _actualId) , DBHelper.MP("@OwnerId", SqlDbType.Int, _ownerId) , DBHelper.MP("@TotalApproved", SqlDbType.Float, _totalApproved) ); } }
public void Save(DBHelper target) { target.RunText("INSERT INTO [cls_ListFolder] ([ListFolderId],[Title],[Created],[Modified],[CreatorId],[ModifierId],[OutlineLevel],[OutlineNumber],[HasChildren],[ParentId],[ProjectId]) VALUES (@p1,@p2,@p3,@p3,@p4,@p4,@p5,@p6,@p7,@p8,@p9)" , DBHelper.MP("@p1", SqlDbType.Int, _listFolderId) , DBHelper.MP("@p2", SqlDbType.NVarChar, 100, _name) , DBHelper.MP("@p3", SqlDbType.DateTime, _creationDate) , DBHelper.MP("@p4", SqlDbType.Int, _creatorId) , DBHelper.MP("@p5", SqlDbType.Int, _outlineLevel) , DBHelper.MP("@p6", SqlDbType.NVarChar, 250, _outlineNumber) , DBHelper.MP("@p7", SqlDbType.Bit, _hasChildren) , DBHelper.MP("@p8", SqlDbType.Int, _parentFolderId == 0 ? DBNull.Value : (object)_parentFolderId) , DBHelper.MP("@p9", SqlDbType.Int, _projectId == null ? DBNull.Value : (object)_projectId) ); if (_isPrivate) { target.RunText("INSERT INTO [cls_ListFolder_Role_Principal] ([ObjectId],[PrincipalId],[RoleId]) VALUES (@p1,@p2,@p3)" , DBHelper.MP("@p1", SqlDbType.Int, _listFolderId) , DBHelper.MP("@p2", SqlDbType.Int, _creatorId) , DBHelper.MP("@p3", SqlDbType.Int, 1) // 1 = Owner ); } }