260 lines
10 KiB
C#
260 lines
10 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using DTS.Common.Utilities.Logging;
|
|
|
|
// ReSharper disable once CheckNamespace
|
|
namespace DTS.Common.Storage
|
|
{
|
|
/// <summary>
|
|
/// this class is used for migration from 52 to 53 in the database
|
|
/// it handles moving the users from the old structures to the new ones
|
|
/// http://fogbugz/fogbugz/default.asp?9749
|
|
/// </summary>
|
|
public class UserMigrationHelper
|
|
{
|
|
public string UserName { get; }
|
|
public string DisplayName { get; }
|
|
public string Password { get; }
|
|
public short Role { get; }
|
|
public DateTime LastModified { get; }
|
|
public string LastModifiedBy { get; }
|
|
public bool LocalOnly { get; }
|
|
public Dictionary<string, short> Permissions { get; }
|
|
public Dictionary<string, bool> Visibility { get; }
|
|
public int[] TagIds { get; }
|
|
|
|
public UserMigrationHelper(DataRow row)
|
|
{
|
|
UserName = Convert.ToString(row["UserName"]);
|
|
DisplayName = Convert.ToString(row["DisplayName"]);
|
|
Password = Convert.ToString(row["Password"]);
|
|
Permissions = GetPermissionsFromString(Convert.ToString(row["IUIItemPermissions"]));
|
|
Visibility = GetVisibilityFromString(Convert.ToString(row["IUIItemVisibility"]));
|
|
Role = Convert.ToInt16(row["Role"]);
|
|
LastModified = Convert.ToDateTime(row["LastModified"]);
|
|
LastModifiedBy = Convert.ToString(row["LastModifiedBy"]);
|
|
LocalOnly = Convert.ToBoolean(row["LocalOnly"]);
|
|
var tagObject = row["UserTags"];
|
|
if (!DBNull.Value.Equals(tagObject))
|
|
{
|
|
var tags = new List<int>(GetTags((byte[])row["UserTags"]).Distinct());
|
|
tags.Remove(0);
|
|
TagIds = tags.ToArray();
|
|
}
|
|
else
|
|
{
|
|
TagIds = new int[0];
|
|
}
|
|
}
|
|
|
|
private static Dictionary<string, short> GetPermissionsFromString(string sPermissions)
|
|
{
|
|
var lookup = new Dictionary<string, short>();
|
|
var tokens = sPermissions.Split(',');
|
|
foreach (var token in tokens)
|
|
{
|
|
var subtokens = token.Split('=');
|
|
if (2 != subtokens.Length) { continue; }
|
|
lookup[subtokens[0]] = Convert.ToInt16(subtokens[1]);
|
|
}
|
|
return lookup;
|
|
}
|
|
|
|
private static Dictionary<string, bool> GetVisibilityFromString(string sVisibilities)
|
|
{
|
|
var lookup = new Dictionary<string, bool>();
|
|
var tokens = sVisibilities.Split(',');
|
|
foreach (var token in tokens)
|
|
{
|
|
var subtokens = token.Split('=');
|
|
if (2 != subtokens.Length) { continue; }
|
|
lookup[subtokens[0]] = Convert.ToBoolean(Convert.ToInt32(subtokens[1]));
|
|
}
|
|
return lookup;
|
|
}
|
|
|
|
private static int[] GetTags(byte[] bytes)
|
|
{
|
|
if (!bytes.Any()) return new int[0];
|
|
var tags = new int[bytes.Length / sizeof(int)];
|
|
try
|
|
{
|
|
Buffer.BlockCopy(bytes, 0, tags, 0, bytes.Length);
|
|
return tags;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
APILogger.Log(ex);
|
|
}
|
|
return new int[0];
|
|
}
|
|
|
|
// ReSharper disable once InconsistentNaming
|
|
public void Commit(Dictionary<string, long> IUIItemNameToID)
|
|
{
|
|
int id;
|
|
using (var cmd = DbOperations.GetCommand())
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = "INSERT INTO [DataPROUsers] ([UserName],[DisplayName],[Password],[Role],[LastModified],[LastModifiedBy],[LocalOnly]) VALUES (@1,@2,@3,@4,@5,@6,@7)";
|
|
DbOperations.CreateParam(cmd, "@1", SqlDbType.NVarChar, UserName);
|
|
DbOperations.CreateParam(cmd, "@2", SqlDbType.NVarChar, DisplayName);
|
|
DbOperations.CreateParam(cmd, "@3", SqlDbType.NVarChar, Password);
|
|
DbOperations.CreateParam(cmd, "@4", SqlDbType.SmallInt, Role);
|
|
DbOperations.CreateParam(cmd, "@5", SqlDbType.DateTime, LastModified);
|
|
DbOperations.CreateParam(cmd, "@6", SqlDbType.NVarChar, LastModifiedBy);
|
|
DbOperations.CreateParam(cmd, "@7", SqlDbType.Bit, LocalOnly);
|
|
|
|
//DbOperations.Connection.ExecuteCommand(cmd);
|
|
if (DbOperations._usingMSSQL)
|
|
{
|
|
DbOperations.Connection.ExecuteCommand(cmd);
|
|
}
|
|
else
|
|
{
|
|
DbOperations.Connection.ExecuteSQLiteCommand(cmd);
|
|
}
|
|
|
|
using (var nestedCmd = DbOperations.GetCommand())
|
|
{
|
|
try
|
|
{
|
|
nestedCmd.CommandText = "SELECT [ID] FROM [DataPROUsers] WHERE [UserName]=@1";
|
|
DbOperations.CreateParam(nestedCmd, "@1", SqlDbType.NVarChar, UserName);
|
|
using (var ds = DbOperations.Connection.QueryDataSet(nestedCmd))
|
|
{
|
|
var dr = ds.Tables[0].Rows[0];
|
|
id = Convert.ToInt32(dr["ID"]);
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
nestedCmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
if (Permissions.Any())
|
|
{
|
|
IDbCommand cmd = null;
|
|
StringBuilder sb = null;
|
|
var i = 0;
|
|
using (var e = Permissions.GetEnumerator())
|
|
{
|
|
while (e.MoveNext())
|
|
{
|
|
if (null == cmd)
|
|
{
|
|
cmd = DbOperations.GetCommand();
|
|
sb = new StringBuilder();
|
|
sb.Append(DbOperations.BEGIN_STATEMENT);
|
|
}
|
|
|
|
var permission = e.Current.Value;
|
|
var visible = !Visibility.ContainsKey(e.Current.Key) || Visibility[e.Current.Key];
|
|
|
|
sb.AppendFormat(
|
|
"INSERT INTO UserUIItemSettings (UserID, UIItemID, Permission, Visible) VALUES (@{0}_1, @{0}_2, @{0}_3, @{0}_4);",
|
|
i);
|
|
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_1", i), SqlDbType.Int, id);
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_2", i), SqlDbType.BigInt,
|
|
IUIItemNameToID[e.Current.Key]);
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_3", i), SqlDbType.SmallInt, permission);
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_4", i), SqlDbType.BigInt, visible);
|
|
|
|
if (0 == i % 25 && 0 != i)
|
|
{
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
cmd.CommandText = sb.ToString();
|
|
//DbOperations.Connection.ExecuteCommand(cmd);
|
|
if (DbOperations._usingMSSQL)
|
|
{
|
|
DbOperations.Connection.ExecuteCommand(cmd);
|
|
}
|
|
else
|
|
{
|
|
DbOperations.Connection.ExecuteSQLiteCommand(cmd);
|
|
}
|
|
|
|
cmd.Connection.Dispose();
|
|
cmd.Dispose();
|
|
sb = null;
|
|
cmd = null;
|
|
}
|
|
|
|
i++;
|
|
}
|
|
}
|
|
|
|
if (null != cmd)
|
|
{
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
cmd.CommandText = sb.ToString();
|
|
//DbOperations.Connection.ExecuteCommand(cmd);
|
|
if (DbOperations._usingMSSQL)
|
|
{
|
|
DbOperations.Connection.ExecuteCommand(cmd);
|
|
}
|
|
else
|
|
{
|
|
DbOperations.Connection.ExecuteSQLiteCommand(cmd);
|
|
}
|
|
cmd.Connection.Dispose();
|
|
cmd.Dispose();
|
|
}
|
|
}
|
|
|
|
// ReSharper disable once InvertIf
|
|
if (TagIds.Any())
|
|
{
|
|
IDbCommand cmd = null;
|
|
StringBuilder sb = null;
|
|
var i = 0;
|
|
foreach (var tag in TagIds)
|
|
{
|
|
if (null == cmd)
|
|
{
|
|
cmd = DbOperations.GetSQLCommand();
|
|
sb = new StringBuilder();
|
|
sb.Append(DbOperations.BEGIN_STATEMENT);
|
|
}
|
|
|
|
sb.Append(string.Format("INSERT INTO [TagAssignments] ([ObjectID],[ObjectType],[TagID]) VALUES (@{0}_1,@{0}_2,@{0}_3);", i));
|
|
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_1", i), SqlDbType.Int, id);
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_2", i), SqlDbType.SmallInt, 0);
|
|
DbOperations.CreateParam(cmd, string.Format("@{0}_3", i), SqlDbType.Int, tag);
|
|
i++;
|
|
}
|
|
|
|
if (null == sb)
|
|
{
|
|
return;
|
|
}
|
|
sb.Append(DbOperations.COMMIT_STATEMENT);
|
|
cmd.CommandText = sb.ToString();
|
|
//DbOperations.Connection.ExecuteCommand(cmd);
|
|
if (DbOperations._usingMSSQL)
|
|
{
|
|
DbOperations.Connection.ExecuteCommand(cmd);
|
|
}
|
|
else
|
|
{
|
|
DbOperations.Connection.ExecuteSQLiteCommand(cmd);
|
|
}
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
}
|