public static void LoadList(DBHelper source, int metaClassId, ArrayList fields)
{
using (IDataReader reader = source.RunTextDataReader(string.Format(CultureInfo.InvariantCulture, "SELECT F.Name, F.Length, T.SqlName, F.AllowNulls, T.Variable, T.DefaultValue FROM MetaClassMetaFieldRelation R JOIN MetaField F ON F.MetaFieldId = R.MetaFieldId JOIN MetaDataType T ON T.DataTypeId = F.DataTypeId WHERE R.MetaClassId = {0} AND F.SystemMetaClassId = 0 ORDER BY F.MetaFieldId", metaClassId)))
{
while (reader.Read())
{
MyMetaField item = Load(reader);
fields.Add(item);
}
}
}
internal void Load(DBHelper dbHelper)
{
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [LanguageId],[Locale],[FriendlyName] FROM [LANGUAGES] WHERE [IsDefault]=1"))
{
if (reader.Read())
{
LanguageId = (int)reader[0];
Locale = reader[1].ToString();
FriendlyName = reader[2].ToString();
}
}
}
internal static string GetPortalParameterValue(DBHelper dbHelper, string name)
{
string result = null;
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [Value] FROM [PortalConfig] WHERE [Key]=@Key"
, DBHelper.MP("@Key", SqlDbType.NVarChar, 100, name)))
{
if (reader.Read())
result = reader[0].ToString();
}
return result;
}
internal static IList<CompanyInfo> LoadList(DBHelper source)
{
List<CompanyInfo> ret = new List<CompanyInfo>();
using (IDataReader reader = source.RunTextDataReader("SELECT [company_id], [domain], [company_name], [db_name] FROM [COMPANIES]"))
{
while (reader.Read())
{
CompanyInfo item = Load(reader);
ret.Add(item);
}
}
return ret;
}
public static ArrayList LoadList(DBHelper source)
{
ArrayList ret = new ArrayList();
using (IDataReader reader = source.RunTextDataReader("SELECT [MetaClassId], [Name], [TableName] FROM [MetaClass] WHERE [IsSystem] = 0 AND [IsAbstract] = 0"))
{
while (reader.Read())
{
MyMetaClass item = Load(reader);
if (item.Name != "AssetsEx")
ret.Add(item);
}
}
return ret;
}
public static IDictionary<int, ListFolder> LoadList(DBHelper source)
{
Dictionary<int, ListFolder> ret = new Dictionary<int, ListFolder>();
using (IDataReader reader = source.RunTextDataReader("SELECT * FROM [LIST_FOLDERS]"))
{
while (reader.Read())
{
ListFolder item = Load(reader);
if (!ret.ContainsKey(item._listFolderId))
ret.Add(item._listFolderId, item);
}
}
// Calculate OutlineLevel, OutlineNumber, HasChildren.
foreach (ListFolder folder in ret.Values)
folder.CalculateOutline(ret);
return ret;
}
internal static IConfigurationParameter[] ListPortalParameters(DBHelper dbHelper)
{
List<IConfigurationParameter> list = new List<IConfigurationParameter>();
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [Key],[Value] FROM [PortalConfig] ORDER BY [Key]"))
{
while (reader.Read())
{
string name = reader[0].ToString();
string value = reader[1].ToString();
if (!name.StartsWith("system.", StringComparison.OrdinalIgnoreCase))
{
ConfigurationParameter item = new ConfigurationParameter(name, value);
list.Add(item);
}
}
}
return list.ToArray();
}
public static User[] GetUsers(DBHelper dbh, int groupId, int alertGroupID)
{
List<User> list = new List<User>();
using (IDataReader reader = dbh.RunTextDataReader("SELECT PrincipalId, Login, Password, FirstName, LastName, Email, Activity, salt, [hash] FROM [USERS] WHERE Login IS NOT NULL AND Password IS NOT NULL AND IsExternal = 0"))
{
while (reader.Read())
{
User item = new User();
item.principalId = (int)reader["PrincipalId"];
item.login = reader["Login"].ToString();
item.password = reader["Password"].ToString();
item.firstName = reader["FirstName"].ToString();
item.lastName = reader["LastName"].ToString();
item.email = reader["Email"].ToString();
if (reader["salt"] != DBNull.Value)
item.salt = reader["salt"].ToString();
if (reader["hash"] != DBNull.Value)
item.hash = reader["hash"].ToString();
item.isActive = (byte)reader["Activity"] == 3;
if (string.Compare(item.login, "alert", StringComparison.OrdinalIgnoreCase) != 0)
item.imGroupId = groupId;
else
item.imGroupId = alertGroupID;
list.Add(item);
}
}
return list.ToArray();
}
private void ValidateDatabaseVersion(DBHelper dbHelper, Version requiredVersion, bool isSource)
{
using (DBTransaction tran = dbHelper.BeginTransaction())
{
Version currentVersion = new Version();
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [Major],[Minor],[Build] FROM [DatabaseVersion]"))
{
if (reader.Read())
{
int major = (int)reader["Major"];
int minor = (int)reader["Minor"];
int build = (int)reader["Build"];
currentVersion = new Version(major, minor, build);
}
}
if (currentVersion != requiredVersion)
{
string databaseType;
string advise;
if (isSource)
{
databaseType = "Source";
if (currentVersion < requiredVersion)
advise = "Update IBN 4.5 to the required version.";
else
advise = "Update IBN 4.7 to the latest version or wait for the next update if you already have the latest version.";
}
else
{
databaseType = "Target";
advise = "Select different database.";
}
throw new IbnConverterException(string.Format("{0} database {1}.{2} has unsupported version {3}. Required version is {4}. {5}"
, databaseType, tran.SqlTran.Connection.DataSource, tran.SqlTran.Connection.Database, currentVersion, requiredVersion, advise)
);
}
}
}
public static IDictionary<int, TTEntry> LoadList(DBHelper source)
{
Dictionary<int, TTEntry> ret = new Dictionary<int, TTEntry>();
using (IDataReader reader = source.RunTextDataReader("SELECT * FROM [TIMESHEETS]"))
{
while (reader.Read())
{
TTEntry item = Load(reader);
if (!ret.ContainsKey(item._timeSheetId))
ret.Add(item._timeSheetId, item);
}
}
foreach (TTEntry entry in ret.Values)
{
string table = null;
string field = null;
switch (entry._objectTypeId)
{
case 3:
table = "PROJECTS";
field = "ProjectId";
break;
case 4:
table = "EVENTS";
field = "EventId";
break;
case 5:
table = "TASKS";
field = "TaskId";
break;
case 6:
table = "TODO";
field = "ToDoId";
break;
case 7:
table = "INCIDENTS";
field = "IncidentId";
break;
case 11:
table = "TIMESHEET_TODO";
field = "TimeSheetToDoId";
break;
case 16:
table = "DOCUMENTS";
field = "DocumentId";
break;
}
if (table != null && field != null)
{
object title = source.RunTextScalar(string.Format(CultureInfo.InvariantCulture, "SELECT [Title] FROM [{0}] WHERE [{1}] = @p1", table, field), DBHelper.MP("@p1", SqlDbType.Int, entry._objectId));
if (title != null && title != DBNull.Value)
{
entry._title = title.ToString();
}
else
{
entry._objectTypeId = -1;
entry._objectId = -1;
entry._title = string.Format(CultureInfo.InvariantCulture, "#{0}", entry._timeSheetId);
}
}
}
return ret;
}
public static IDictionary<int, Project> LoadList(DBHelper source)
{
Dictionary<int, Project> ret = new Dictionary<int, Project>();
using (IDataReader reader = source.RunTextDataReader("SELECT [ProjectId],[Title],[StatusId],[ManagerId],[ExecutiveManagerId] FROM [PROJECTS]"))
{
while (reader.Read())
{
Project item = Load(reader);
if (!ret.ContainsKey(item._projectId))
ret.Add(item._projectId, item);
}
}
foreach (Project project in ret.Values)
{
using (IDataReader reader = source.RunTextDataReader("SELECT [PrincipalId],[IsTeamMember],[IsSponsor],[IsStakeHolder] FROM [PROJECT_MEMBERS] WHERE [ProjectId]=@p1", DBHelper.MP("@p1", SqlDbType.Int, project._projectId)))
{
while (reader.Read())
{
int principalId = (int)reader["PrincipalId"];
if ((bool)reader["IsTeamMember"])
project._teamMembers.Add(principalId);
if ((bool)reader["IsSponsor"])
project._sponsors.Add(principalId);
if ((bool)reader["IsStakeHolder"])
project._stakeholders.Add(principalId);
}
}
}
return ret;
}
private static void UpdateRecord(string tableName, string sourceCondition, string targetCondition, ArrayList skip, DBHelper source, DBHelper target)
{
string colName;
DataTable dt;
bool isIdentity;
ArrayList columns = new ArrayList();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
using (IDataReader reader = source.RunTextDataReader(string.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}] {1}", tableName, sourceCondition)))
{
dt = reader.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
colName = (string)row["ColumnName"];
if (!skip.Contains(colName) && CheckColumn(target, cmd, tableName, colName, out isIdentity))
columns.Add(colName);
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb.Append("SELECT ");
sb2.Append("UPDATE [");
sb2.Append(tableName);
sb2.Append("] SET ");
for (int i = 0; i < columns.Count; i++)
{
colName = (string)columns[i];
if (i > 0)
{
sb.Append(", ");
sb2.Append(", ");
}
sb.Append("[");
sb.Append(colName);
sb.Append("]");
sb2.Append("[");
sb2.Append(colName);
sb2.Append("] = @p");
sb2.Append(i);
}
sb.Append(" FROM [");
sb.Append(tableName);
sb.Append("] ");
sb.Append(sourceCondition);
sb2.Append(" ");
sb2.Append(targetCondition);
string selectCommand = sb.ToString();
string updateCommand = sb2.ToString();
// Read data from source and update target
using (IDataReader reader = source.RunTextDataReader(selectCommand))
{
dt = reader.GetSchemaTable();
cmd.CommandText = updateCommand;
cmd.Parameters.Clear();
if (reader.Read())
{
// Create parameters
for (int i = 0; i < columns.Count; i++)
{
cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@p{0}", i), GetColumnValue((string)columns[i], reader[i], null, null));
if ((Type)dt.Rows[i]["DataType"] == typeof(byte[]))
cmd.Parameters[i].SqlDbType = SqlDbType.Binary;
}
target.RunCmd(cmd);
}
}
}
public static IDictionary<int, TTBlock> LoadList(DBHelper source)
{
Dictionary<int, TTBlock> ret = new Dictionary<int,TTBlock>();
using (IDataReader reader = source.RunTextDataReader("SELECT T.*, P.Title, S.ProjectSpreadSheetId FROM [WeekTimeSheet] T LEFT JOIN [PROJECTS] P ON P.ProjectId = T.ProjectId LEFT JOIN [ProjectSpreadSheet] S ON S.ProjectId = T.ProjectId"))
{
while (reader.Read())
{
TTBlock item = Load(reader);
if (!ret.ContainsKey(item._weekTimeSheetId))
ret.Add(item._weekTimeSheetId, item);
}
}
return ret;
}
private static NameValueCollection LoadList(DBHelper source, string commandText)
{
NameValueCollection ret = new NameValueCollection();
using (IDataReader reader = source.RunTextDataReader(commandText))
{
while (reader.Read())
{
ret.Add(reader["ItemId"].ToString(), reader["ItemName"].ToString());
}
}
return ret;
}
private void CopyTable(string sourceTableName, string targetTableName, NameValueCollection columnsMapping, string selPrefix, string selColumns, string selCondition, bool copyBinary, DBHelper source, DBHelper target, IList<StringCollection> nameLists, IList<Hashtable> valueHashTables, bool insertIdentity, Hashtable identities)
{
SendProgress("Copying data from table {0} to table {1}", sourceTableName, targetTableName);
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
DataTable dt;
Type dataType;
bool isIdentity, hasIdentity = false;
string selectCommand;
string insertCommand;
ArrayList columnTypes = new ArrayList();
ArrayList sourceColumns = new ArrayList();
ArrayList targetColumns = new ArrayList();
ArrayList sourceBinColumns = new ArrayList();
ArrayList targetBinColumns = new ArrayList();
Hashtable binIdentities = new Hashtable();
string sourceIdentityColumn = null, targetIdentityColumn = null;
int identityIndex = -1;
string sourceColumnName, targetColumnName;
// Get column names to be inserted
selectCommand = string.Format(CultureInfo.InvariantCulture, "SELECT {0}{1} FROM [{2}] {3}", selPrefix, selColumns, sourceTableName, selCondition);
using (IDataReader reader = source.RunTextDataReaderSchemaOnly(selectCommand))
{
dt = reader.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
sourceColumnName = (string)row["ColumnName"];
targetColumnName = GetTargetColumn(sourceColumnName, columnsMapping);
dataType = (Type)row["DataType"];
// Check if column exists and is identity
if (CheckColumn(target, cmd, targetTableName, targetColumnName, out isIdentity))
{
if (isIdentity)
{
hasIdentity = true;
sourceIdentityColumn = sourceColumnName;
targetIdentityColumn = targetColumnName;
}
if (dataType == typeof(byte[]))
{
sourceBinColumns.Add(sourceColumnName);
targetBinColumns.Add(targetColumnName);
}
else
{
columnTypes.Add(dataType);
sourceColumns.Add(sourceColumnName);
targetColumns.Add(targetColumnName);
}
}
}
}
// Build select command
sb.Append("SELECT ");
int n = 0;
foreach (string colName in sourceColumns)
{
if (n > 0)
sb.Append(", ");
sb.Append(selPrefix);
sb.Append("[");
sb.Append(colName);
sb.Append("]");
n++;
}
sb.Append(" FROM [");
sb.Append(sourceTableName);
sb.Append("] ");
sb.Append(selCondition);
// Build insert command
sb2.Append("INSERT INTO [");
sb2.Append(targetTableName);
sb2.Append("] (");
int j = n = 0;
foreach (string colName in targetColumns)
{
if (colName == targetIdentityColumn)
identityIndex = j;
if (insertIdentity || identityIndex != j)
{
if (n > 0)
sb2.Append(", ");
sb2.Append("[");
sb2.Append(colName);
sb2.Append("]");
n++;
}
j++;
}
sb2.Append(") VALUES (");
n = 0;
for (int i = 0; i < targetColumns.Count; i++)
{
if (insertIdentity || identityIndex != i)
{
if (n > 0)
sb2.Append(", ");
sb2.Append("@p");
sb2.Append(i);
n++;
}
}
sb2.Append(")");
if (hasIdentity && !insertIdentity)
sb2.Append(" SELECT @@IDENTITY");
selectCommand = sb.ToString();
insertCommand = sb2.ToString();
// Read data from source and insert into target
using (DBTransaction tran = target.BeginTransaction())
{
object identity;
using (IDataReader reader = source.RunTextDataReader(selectCommand))
{
dt = reader.GetSchemaTable();
if (insertIdentity && hasIdentity)
SetInsertIdentity(target, targetTableName, true);
try
{
cmd.CommandText = insertCommand;
while (reader.Read())
{
if (cmd.Parameters.Count == 0)
{
// Create parameters
for (int i = 0; i < targetColumns.Count; i++)
{
SqlParameter p = cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@p{0}", i), GetColumnValue((string)sourceColumns[i], reader[i], nameLists, valueHashTables));
if (columnTypes[i] == typeof(Decimal))
p.DbType = DbType.Decimal;
}
}
else
{
// Set values
for (int i = 0; i < targetColumns.Count; i++)
{
cmd.Parameters[i].Value = GetColumnValue((string)sourceColumns[i], reader[i], nameLists, valueHashTables);
}
}
identity = target.RunCmdScalar(cmd);
if (hasIdentity)
{
object oldIdentity = reader[identityIndex];
if (insertIdentity)
binIdentities[oldIdentity] = oldIdentity;
else
binIdentities[oldIdentity] = identity;
if (!insertIdentity && identities != null)
identities[oldIdentity] = identity;
}
}
}
finally
{
if (insertIdentity && hasIdentity)
SetInsertIdentity(target, targetTableName, false);
}
}
tran.Commit();
}
// Copy binary and image fields
if (copyBinary)
{
if (sourceIdentityColumn != null && sourceBinColumns.Count > 0)
{
SqlParameter paramTextPtr = DBHelper.MP("@ptr", SqlDbType.VarBinary, 16, null);
SqlParameter paramData = new SqlParameter("@data", SqlDbType.VarBinary);
paramData.Value = _buffer;
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@id";
foreach (object id in binIdentities.Keys)
{
paramId.Value = binIdentities[id];
for (int i = 0; i < sourceBinColumns.Count; i++)
{
CopyBinaryData(source, sourceTableName, (string)sourceBinColumns[i], sourceIdentityColumn, id,
target, targetTableName, (string)targetBinColumns[i], targetIdentityColumn,
cmd, paramTextPtr, paramData, paramId);
}
}
}
}
SendProgress("Done");
}
/// <summary>
/// Loads the list types.
/// </summary>
private static void LoadListTypes(DBHelper source)
{
_listTypes.Clear();
//if (_listTypes != null)
// throw new ArgumentException("Invalid call. LoadListTypes must be called once.");
//_listTypes = new Dictionary<int, string>();
using (IDataReader reader = source.RunTextDataReader("SELECT * FROM [LIST_TYPES]"))
{
while (reader.Read())
{
string listTypeName = (string)reader["TypeName"];
int listTypeId = (int)reader["TypeId"];
_listTypes.Add(listTypeId, listTypeName);
}
}
}
internal void LoadExtendedInfo(DBHelper dbHelper)
{
try
{
dbHelper.Database = Database;
DatabaseSize = 8 * Convert.ToInt32(dbHelper.RunTextScalar("SELECT SUM([size]) FROM [sysfiles] WHERE ([status] & 0x42) = 2"), CultureInfo.InvariantCulture) / 1024; // size in Megabytes
using (IDataReader reader = dbHelper.RunTextDataReader("SELECT [State],[Build] FROM [DatabaseVersion] WITH (NOLOCK)"))
{
reader.Read();
DatabaseState = Convert.ToInt32(reader["State"], CultureInfo.InvariantCulture);
DatabaseVersion = Convert.ToInt32(reader["Build"], CultureInfo.InvariantCulture);
}
if (DatabaseState == 6) // Ready
{
_defaultLanguage.Load(dbHelper);
InternalUsersCount = Convert.ToInt32(dbHelper.RunTextScalar("SELECT COUNT(*) FROM [USERS] WHERE [Activity]=3 AND [IsExternal]=0"), CultureInfo.InvariantCulture);
ExternalUsersCount = Convert.ToInt32(dbHelper.RunTextScalar("SELECT COUNT(*) FROM [USERS] WHERE [Activity]=3 AND [IsExternal]=1"), CultureInfo.InvariantCulture);
}
}
finally
{
dbHelper.Database = null;
}
}
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
}
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
}
private static string GetPortalDB(DBHelper dbh, int companyId)
{
using (IDataReader reader = dbh.RunTextDataReader(string.Format(CultureInfo.InvariantCulture, "SELECT db_name FROM COMPANIES WHERE company_id={0}", companyId)))
{
reader.Read();
return reader["db_name"].ToString();
}
}
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);
}
public static IDictionary<int, ListInfo45> LoadList(DBHelper source)
{
//Load all exists list types
LoadListTypes(source);
Dictionary<int, ListInfo45> ret = new Dictionary<int, ListInfo45>();
using (IDataReader reader = source.RunTextDataReader("SELECT * FROM [LISTS]"))
{
while (reader.Read())
{
ListInfo45 item = Load(reader);
if (!ret.ContainsKey(item._listId))
ret.Add(item._listId, item);
}
}
return ret;
}
