public void TestAllAvailableTables()
{
foreach (Assembly assembly in new [] { typeof(GS.GameServer).Assembly, typeof(DataObject).Assembly })
{
// Walk through each type in the assembly
foreach (Type type in assembly.GetTypes())
{
if (!type.IsClass || type.IsAbstract)
{
continue;
}
var attrib = type.GetCustomAttributes <DataTable>(false);
if (attrib.Any())
{
Assert.DoesNotThrow(() => {
var dth = new DataTableHandler(type);
Database.CheckOrCreateTableImpl(dth);
}, "Registering All Projects Tables should not throw Exceptions... (Failed on Type {0})", type.FullName);
Database.RegisterDataObject(type);
var selectall = typeof(IObjectDatabase).GetMethod("SelectAllObjects", Array.Empty <Type>()).MakeGenericMethod(type);
object objs = null;
Assert.DoesNotThrow(() => { objs = selectall.Invoke(Database, Array.Empty <object>()); }, "Registered tables should not Throw Exception on Select All... (Failed on Type {0})", type);
Assert.IsNotNull(objs);
}
}
}
}
/// <summary>
/// Get Database Column Definition for ElementBinding
/// </summary>
/// <param name="bind">ElementBinding for Column Definition</param>
/// <param name="table">DataTableHanlder for Special cases</param>
/// <returns>Column Definitnion string.</returns>
protected virtual string GetColumnDefinition(ElementBinding bind, DataTableHandler table)
{
string type = GetDatabaseType(bind, table);
string defaultDef = null;
// Check for Default Value depending on Constraints and Type
if (bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement)
{
defaultDef = "NOT NULL PRIMARY KEY AUTOINCREMENT";
}
else if (bind.DataElement != null && bind.DataElement.AllowDbNull)
{
defaultDef = "DEFAULT NULL";
}
else if (bind.ValueType == typeof(DateTime))
{
defaultDef = "NOT NULL DEFAULT '2000-01-01 00:00:00'";
}
else if (bind.ValueType == typeof(string))
{
defaultDef = "NOT NULL DEFAULT ''";
}
else
{
defaultDef = "NOT NULL DEFAULT 0";
}
// Force Case Insensitive Text Field to Match MySQL Behavior
if (bind.ValueType == typeof(string))
{
defaultDef = string.Format("{0} {1}", defaultDef, "COLLATE NOCASE");
}
return(string.Format("`{0}` {1} {2}", bind.ColumnName, type, defaultDef));
}
static void RunOp()
{
PageCounter counter = new PageCounter();
if (operationMode == 1)
{
DataTableHandler dteHandler = new DataTableHandler();
DateTime Start = new DateTime(2015, 7, 01);
DateTime End = new DateTime(2015, 7, 02);
DataTable PDFs = SQLHandler.getListOfPDFs(Start, End);
counter.CountPagesFromDB(PDFs);
Console.WriteLine("Enter the path and name of the file");
string fullPath = Console.ReadLine();
dteHandler.loadExcel(PDFs, fullPath);
Console.WriteLine("File Written");
}
else if (operationMode == 2)
{
Console.WriteLine("Enter the fullpath of a pdf file: ");
string fullFilePath = Console.ReadLine();
Console.WriteLine("Page count for file: " + counter.CountPages(fullFilePath));
Console.ReadKey();
}
}
/// <summary>
/// Get Database Column Definition for ElementBinding
/// </summary>
/// <param name="bind">ElementBinding for Column Definition</param>
/// <param name="table">DataTableHanlder for Special cases</param>
/// <returns>Column Definitnion string.</returns>
protected virtual string GetColumnDefinition(ElementBinding bind, DataTableHandler table)
{
string type = GetDatabaseType(bind, table);
string defaultDef = null;
Type[] numberTypes =
{
typeof(int), typeof(byte), typeof(bool), typeof(long), typeof(short),
typeof(ushort), typeof(ulong), typeof(uint), typeof(double)
};
// Check for Default Value depending on Constraints and Type
if (bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement)
{
defaultDef = "NOT NULL AUTO_INCREMENT";
}
else if (bind.DataElement != null && bind.DataElement.AllowDbNull)
{
defaultDef = "DEFAULT NULL";
}
else if (bind.ValueType == typeof(DateTime))
{
defaultDef = "NOT NULL DEFAULT '2000-01-01 00:00:00'";
}
else if (numberTypes.Contains(bind.ValueType))
{
defaultDef = "NOT NULL DEFAULT 0";
}
else
{
defaultDef = "NOT NULL";
}
return(string.Format("`{0}` {1} {2}", bind.ColumnName, type, defaultDef));
}
public void TestWrongDataObject()
{
Assert.Throws(typeof(ArgumentException), () => {
var dth = new DataTableHandler(typeof(AttributesUtils));
Database.CheckOrCreateTableImpl(dth);
}, "Registering a wrong DataObject should throw Argument Exception");
}
/// <summary>
/// Create a New Table from DataTableHandler Definition
/// </summary>
/// <param name="table">DataTableHandler Definition to Create in Database</param>
protected void CreateTable(DataTableHandler table)
{
ExecuteNonQueryImpl(GetTableDefinition(table));
foreach (var commands in GetIndexesDefinition(table))
{
ExecuteNonQueryImpl(commands);
}
}
/// <summary>
/// Check for Table Existence, Create or Alter accordingly
/// </summary>
/// <param name="table">Table Handler</param>
public override void CheckOrCreateTableImpl(DataTableHandler table)
{
var currentTableColumns = new List <TableRowBindind>();
try
{
ExecuteSelectImpl(
string.Format("PRAGMA TABLE_INFO(`{0}`)", table.TableName),
reader =>
{
while (reader.Read())
{
var column = reader.GetString(1);
var colType = reader.GetString(2);
var allowNull = !reader.GetBoolean(3);
var primary = reader.GetInt64(5) > 0;
currentTableColumns.Add(new TableRowBindind(column, colType, allowNull, primary));
if (log.IsDebugEnabled)
{
log.DebugFormat("CheckOrCreateTable: Found Column {0} in existing table {1}", column, table.TableName);
}
}
if (log.IsDebugEnabled)
{
log.DebugFormat("CheckOrCreateTable: {0} columns existing in table {1}", currentTableColumns.Count, table.TableName);
}
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("CheckOrCreateTable: ", e);
}
}
// Create Table or Alter Table
if (currentTableColumns.Any())
{
AlterTable(currentTableColumns, table);
}
else
{
if (log.IsWarnEnabled)
{
log.WarnFormat("Table {0} doesn't exist, creating it...", table.TableName);
}
CreateTable(table);
}
}
/// <summary>
/// Check for Table Existence, Create or Alter accordingly
/// </summary>
/// <param name="table">Table Handler</param>
public override void CheckOrCreateTableImpl(DataTableHandler table)
{
var currentTableColumns = new List <TableRowBindind>();
try
{
ExecuteSelectImpl(string.Format("DESCRIBE `{0}`", table.TableName),
new[] { Array.Empty <QueryParameter>() },
reader =>
{
while (reader.Read())
{
var column = reader.GetString(0);
var colType = reader.GetString(1);
var allowNull = reader.GetString(2).ToLower() == "yes";
var primary = reader.GetString(3).ToLower() == "pri";
currentTableColumns.Add(new TableRowBindind(column, colType, allowNull, primary));
if (log.IsDebugEnabled)
{
log.DebugFormat("CheckOrCreateTable: Found Column {0} in existing table {1}", column, table.TableName);
}
}
if (log.IsDebugEnabled)
{
log.DebugFormat("CheckOrCreateTable: {0} columns existing in table {1}", currentTableColumns.Count, table.TableName);
}
});
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("CheckOrCreateTable: ", e);
}
}
// Create Table or Alter Table
if (currentTableColumns.Any())
{
AlterTable(currentTableColumns, table);
}
else
{
if (log.IsWarnEnabled)
{
log.WarnFormat("Table {0} doesn't exist, creating it...", table.TableName);
}
CreateTable(table);
}
}
/// <summary>
/// Finds an object in the database by primary key.
/// </summary>
/// <param name="objectType">the type of object to retrieve</param>
/// <param name="key">the value of the primary key to search for</param>
/// <returns>a <see cref="DataObject" /> instance representing a row with the given primary key value; null if the key value does not exist</returns>
protected override TObject FindObjectByKeyImpl <TObject>(object key)
{
MemberInfo[] members = typeof(TObject).GetMembers();
var ret = (TObject)Activator.CreateInstance(typeof(TObject));
string tableName = ret.TableName;
DataTableHandler dth = TableDatasets[tableName];
string whereClause = null;
if (dth.UsesPreCaching)
{
DataObject obj = dth.GetPreCachedObject(key);
if (obj != null)
{
return(obj as TObject);
}
}
// Escape PK value
key = Escape(key.ToString());
for (int i = 0; i < members.Length; i++)
{
object[] keyAttrib = members[i].GetCustomAttributes(typeof(PrimaryKey), true);
if (keyAttrib.Length > 0)
{
whereClause = "`" + members[i].Name + "` = '" + key + "'";
break;
}
}
if (whereClause == null)
{
whereClause = "`" + ret.TableName + "_ID` = '" + key + "'";
}
var objs = SelectObjectsImpl <TObject>(whereClause, Transaction.IsolationLevel.DEFAULT);
if (objs.Count > 0)
{
dth.SetPreCachedObject(key, objs[0]);
return(objs[0]);
}
return(null);
}
/// <summary>
/// Helper Method to build Table Indexes Definition String
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected IEnumerable <string> GetIndexesDefinition(DataTableHandler table)
{
// Indexes and Constraints
var uniqueFields = table.Table.Constraints.OfType <UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey)
.Select(cstrnt => string.Format("CREATE UNIQUE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", cstrnt.ConstraintName,
string.Join(", ", cstrnt.Columns.Select(col => string.Format("`{0}`", col.ColumnName))),
table.TableName));
var indexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary <string, DataColumn[]>;
var indexesFields = indexes == null ? new string[] { }
: indexes.Select(index => string.Format("CREATE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", index.Key,
string.Join(", ", index.Value.Select(col => string.Format("`{0}`", col.ColumnName))),
table.TableName));
return(uniqueFields.Concat(indexesFields));
}
/// <summary>
/// Helper Method to build Table Definition String
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected string GetTableDefinition(DataTableHandler table)
{
var columnDef = table.FieldElementBindings
.Select(bind => GetColumnDefinition(bind, table));
var primaryFields = new string[] {};
if (!table.FieldElementBindings.Any(bind => bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement))
{
primaryFields = new [] { string.Format("PRIMARY KEY ({0})",
string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName)))) }
}
;
// Create Table First
return(string.Format("CREATE TABLE IF NOT EXISTS `{0}` ({1})", table.TableName,
string.Join(", \n", columnDef.Concat(primaryFields))));
}
public PsaMovesetHandler(PsaFile psaFile)
{
PsaFile = psaFile;
AttributesHandler = new AttributesParser(PsaFile);
DataTableHandler = new DataTableHandler(PsaFile);
ExternalDataHandler = new ExternalDataHandler(PsaFile);
int dataSectionLocation = DataTableHandler.GetDataTableEntryByName("data").Location;
string movesetBaseName = GetMovesetBaseName();
int numberOfSpecialActions = (PsaFile.DataSection[dataSectionLocation + 10] - PsaFile.DataSection[dataSectionLocation + 9]) / 4;
int codeBlockDataStartLocation = 2014 + numberOfSpecialActions * 2;
PsaCommandHandler psaCommandHandler = new PsaCommandHandler(psaFile, dataSectionLocation, codeBlockDataStartLocation);
CodeBlocksHandler codeBlocksHandler = new CodeBlocksHandler(psaFile, dataSectionLocation, psaCommandHandler);
ActionsHandler = new ActionsHandler(PsaFile, dataSectionLocation, codeBlocksHandler, psaCommandHandler);
SubActionsHandler = new SubActionsHandler(PsaFile, dataSectionLocation, codeBlocksHandler, psaCommandHandler);
SubRoutinesHandler = new SubRoutinesHandler(PsaFile, dataSectionLocation, ActionsHandler, SubActionsHandler, psaCommandHandler);
ActionOverridesHandler = new ActionOverridesHandler(PsaFile, dataSectionLocation, ActionsHandler, psaCommandHandler);
ArticlesHandler = new ArticlesHandler(PsaFile, dataSectionLocation, movesetBaseName, psaCommandHandler);
CharacterParamsHandler = new CharacterParamsHandler(PsaFile, dataSectionLocation, movesetBaseName, psaCommandHandler);
MiscHandler = new MiscHandler(PsaFile, dataSectionLocation, movesetBaseName, numberOfSpecialActions);
}
/// <summary>
/// Create a New Table from DataTableHandler Definition
/// </summary>
/// <param name="table">DataTableHandler Definition to Create in Database</param>
protected void CreateTable(DataTableHandler table)
{
var columnDef = table.FieldElementBindings
.Select(bind => GetColumnDefinition(bind, table));
var primaryFields = string.Format("PRIMARY KEY ({0})",
string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName))));
var uniqueFields = table.Table.Constraints.OfType <UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey)
.Select(cstrnt => string.Format("UNIQUE KEY `{0}` ({1})", cstrnt.ConstraintName,
string.Join(", ", cstrnt.Columns.Select(col => string.Format("`{0}`", col.ColumnName)))));
var indexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary <string, DataColumn[]>;
var indexesFields = indexes == null?Array.Empty <string>()
: indexes.Select(index => string.Format("KEY `{0}` ({1})", index.Key,
string.Join(", ", index.Value.Select(col => string.Format("`{0}`", col.ColumnName)))));
var command = string.Format("CREATE TABLE IF NOT EXISTS `{0}` ({1})", table.TableName,
string.Join(", \n", columnDef.Concat(new [] { primaryFields }).Concat(uniqueFields).Concat(indexesFields)));
ExecuteNonQueryImpl(command);
}
/// <summary>
/// 未发货订单
/// </summary>
/// <param name="context"></param>
public void GetPendingList(HttpContext context)
{
var pageSize = 5;
var pageIndex = 1;
if (!string.IsNullOrEmpty(context.Request["limit"]))
{
pageSize = int.Parse(context.Request["limit"]);
}
if (!string.IsNullOrEmpty(context.Request["offset"]))
{
pageIndex = int.Parse(context.Request["offset"]);
}
object RowCount = null;
var user = (context.Session["USER_SESSION"] as UserBase);
if (user.UA01024 != 42 && user.UA01024 != 43 && user.UA01024 != 44 && user.UA01024 != 47)
{
context.Response.Write("0");
return;
}
string where = user.UA01024 == 43 ? "and OA01013 = '" + user.UA01001 + "'" : "and UA01013 = '" + user.UA01013 + "'";
if (user.UA01013 == "全区域")
{
where = string.Empty;
}
DataTable dt = new OrderBLL().GetPengdingList(pageIndex, pageSize, where, ref RowCount);
var list = new DataTableHandler().ToList <Pending>(dt);
Hashtable tab = new Hashtable();
tab["total"] = RowCount;
tab["rows"] = list;
var json = JsonConvert.SerializeObject(tab);
context.Response.Write(json);
}
/// <summary>
/// Check if Table Indexes Need Alteration
/// </summary>
/// <param name="table">DataTableHandler to Implement</param>
protected void CheckTableIndexAlteration(DataTableHandler table)
{
// Query Existing Indexes
var currentIndexes = new List <Tuple <string, string> >();
try
{
ExecuteSelectImpl("SELECT name, sql FROM sqlite_master WHERE type == 'index' AND sql is not null AND tbl_name == @tableName",
new QueryParameter("@tableName", table.TableName),
reader =>
{
while (reader.Read())
{
currentIndexes.Add(new Tuple <string, string>(reader.GetString(0), reader.GetString(1)));
}
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("CheckTableIndexAlteration: ", e);
}
throw;
}
var sortedIndexes = currentIndexes.Select(ind => {
var unique = ind.Item2.Trim().StartsWith("CREATE UNIQUE", StringComparison.OrdinalIgnoreCase);
var columns = ind.Item2.Substring(ind.Item2.IndexOf('(')).Split(',').Select(sp => sp.Trim('`', '(', ')', ' '));
return(new { KeyName = ind.Item1, Unique = unique, Columns = columns.ToArray() });
}).ToArray();
if (log.IsDebugEnabled)
{
log.DebugFormat("CheckTableIndexAlteration: {0} Indexes existing in table {1}", sortedIndexes.Length, table.TableName);
}
var tableIndexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary <string, DataColumn[]>;
var alterQueries = new List <string>();
// Check for Index Removal
foreach (var existing in sortedIndexes)
{
if (log.IsDebugEnabled)
{
log.DebugFormat("CheckTableIndexAlteration: Found Index `{0}` (Unique:{1}) on ({2}) in existing table {3}", existing.KeyName, existing.Unique, string.Join(", ", existing.Columns), table.TableName);
}
DataColumn[] realindex;
if (tableIndexes.TryGetValue(existing.KeyName, out realindex))
{
// Check for index modifications
if (realindex.Length != existing.Columns.Length ||
!realindex.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
alterQueries.Add(string.Format("DROP INDEX `{0}`", existing.KeyName));
alterQueries.Add(string.Format("CREATE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", existing.KeyName, string.Join(", ", realindex.Select(col => string.Format("`{0}`", col))), table.TableName));
}
}
else
{
// Check for Unique
var realunique = table.Table.Constraints.OfType <UniqueConstraint>().FirstOrDefault(cstrnt => !cstrnt.IsPrimaryKey && cstrnt.ConstraintName.Equals(existing.KeyName, StringComparison.OrdinalIgnoreCase));
if (realunique == null)
{
alterQueries.Add(string.Format("DROP INDEX `{0}`", existing.KeyName));
}
else if (realunique.Columns.Length != existing.Columns.Length ||
!realunique.Columns.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
alterQueries.Add(string.Format("DROP INDEX `{0}`", existing.KeyName));
alterQueries.Add(string.Format("CREATE UNIQUE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", existing.KeyName, string.Join(", ", realunique.Columns.Select(col => string.Format("`{0}`", col))), table.TableName));
}
}
}
// Missing Indexes
foreach (var missing in tableIndexes.Where(kv => sortedIndexes.All(c => !c.KeyName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase))))
{
alterQueries.Add(string.Format("CREATE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", missing.Key, string.Join(", ", missing.Value.Select(col => string.Format("`{0}`", col))), table.TableName));
}
foreach (var missing in table.Table.Constraints.OfType <UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey && sortedIndexes.All(c => !c.KeyName.Equals(cstrnt.ConstraintName, StringComparison.OrdinalIgnoreCase))))
{
alterQueries.Add(string.Format("CREATE UNIQUE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", missing.ConstraintName, string.Join(", ", missing.Columns.Select(col => string.Format("`{0}`", col))), table.TableName));
}
if (!alterQueries.Any())
{
return;
}
if (log.IsDebugEnabled)
{
log.DebugFormat("Altering Table Indexes {0} this could take a few minutes...", table.TableName);
}
foreach (var query in alterQueries)
{
ExecuteNonQueryImpl(query);
}
}
/// <summary>
/// Check if this Table need Alteration
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected bool CheckTableAlteration(IEnumerable <TableRowBindind> currentColumns, DataTableHandler table)
{
// Check for Any differences in Columns
if (table.FieldElementBindings
.Any(bind => {
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
// Check Null
if ((bind.DataElement != null && bind.DataElement.AllowDbNull) != column.AllowDbNull)
{
return(true);
}
// Check Type
if (!GetDatabaseType(bind, table).Equals(column.ColumnType, StringComparison.OrdinalIgnoreCase))
{
return(true);
}
// Field are identical
return(false);
}
// Field missing
return(true);
}))
{
return(true);
}
// Check for Any Difference in Primary Keys
if (table.Table.PrimaryKey.Length != currentColumns.Count(col => col.Primary) ||
table.Table.PrimaryKey.Any(pk => {
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(pk.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null && column.Primary)
{
return(false);
}
return(true);
}))
{
return(true);
}
// No Alteration Needed
return(false);
}
/// <summary>
/// Check for Table Existence, Create or Alter accordingly
/// </summary>
/// <param name="table">Table Handler</param>
public override void CheckOrCreateTableImpl(DataTableHandler table)
{
var currentTableColumns = new List<TableRowBindind>();
try
{
ExecuteSelectImpl(string.Format("PRAGMA TABLE_INFO(`{0}`)", table.TableName),
reader =>
{
while (reader.Read())
{
var column = reader.GetString(1);
var colType = reader.GetString(2);
var allowNull = !reader.GetBoolean(3);
var primary = reader.GetInt64(5) > 0;
currentTableColumns.Add(new TableRowBindind(column, colType, allowNull, primary));
if (log.IsDebugEnabled)
log.DebugFormat("CheckOrCreateTable: Found Column {0} in existing table {1}", column, table.TableName);
}
if (log.IsDebugEnabled)
log.DebugFormat("CheckOrCreateTable: {0} columns existing in table {1}", currentTableColumns.Count, table.TableName);
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
log.Debug("CheckOrCreateTable: ", e);
}
// Create Table or Alter Table
if (currentTableColumns.Any())
{
AlterTable(currentTableColumns, table);
}
else
{
if (log.IsWarnEnabled)
log.WarnFormat("Table {0} doesn't exist, creating it...", table.TableName);
CreateTable(table);
}
}
/// <summary>
/// Helper Method to build Table Definition String
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected string GetTableDefinition(DataTableHandler table)
{
var columnDef = table.FieldElementBindings
.Select(bind => GetColumnDefinition(bind, table));
var primaryFields = new string[]{};
if (!table.FieldElementBindings.Any(bind => bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement))
primaryFields = new [] { string.Format("PRIMARY KEY ({0})",
string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName)))) };
// Create Table First
return string.Format("CREATE TABLE IF NOT EXISTS `{0}` ({1})", table.TableName,
string.Join(", \n", columnDef.Concat(primaryFields)));
}
/// <summary>
/// Helper Method to build Table Indexes Definition String
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected IEnumerable<string> GetIndexesDefinition(DataTableHandler table)
{
// Indexes and Constraints
var uniqueFields = table.Table.Constraints.OfType<UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey)
.Select(cstrnt => string.Format("CREATE UNIQUE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", cstrnt.ConstraintName,
string.Join(", ", cstrnt.Columns.Select(col => string.Format("`{0}`", col.ColumnName))),
table.TableName));
var indexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary<string, DataColumn[]>;
var indexesFields = indexes == null ? new string[] { }
: indexes.Select(index => string.Format("CREATE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", index.Key,
string.Join(", ", index.Value.Select(col => string.Format("`{0}`", col.ColumnName))),
table.TableName));
return uniqueFields.Concat(indexesFields);
}
/// <summary>
/// Alter an Existing Table to Match DataTableHandler Definition
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected void AlterTable(IEnumerable <TableRowBindind> currentColumns, DataTableHandler table)
{
var columnDefs = new List <string>();
var alteredColumn = new List <string>();
// Check for Missing Column or Wrong Type
foreach (var binding in table.FieldElementBindings)
{
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(binding.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
// Check Null && Type
if ((binding.DataElement != null && binding.DataElement.AllowDbNull) != column.AllowDbNull ||
!GetDatabaseType(binding, table).Equals(column.ColumnType, StringComparison.OrdinalIgnoreCase))
{
columnDefs.Add(string.Format("CHANGE `{1}` {0}", GetColumnDefinition(binding, table), binding.ColumnName));
alteredColumn.Add(binding.ColumnName);
}
continue;
}
columnDefs.Add(string.Format("ADD {0}", GetColumnDefinition(binding, table)));
}
// Check for Indexes
var indexes = new List <Tuple <bool, string, string> >();
try
{
ExecuteSelectImpl(string.Format("SHOW INDEX FROM `{0}`", table.TableName),
reader =>
{
while (reader.Read())
{
var unique = reader.GetInt64(1) < 1;
var indexname = reader.GetString(2);
var column = reader.GetString(4);
indexes.Add(new Tuple <bool, string, string>(unique, indexname, column));
if (log.IsDebugEnabled)
{
log.DebugFormat("AlterTable: Found Index `{0}` (Unique:{1}) on `{2}` in existing table {3}", indexname, unique, column, table.TableName);
}
}
if (log.IsDebugEnabled)
{
log.DebugFormat("AlterTable: {0} Indexes existing in table {1}", indexes.Count, table.TableName);
}
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("AlterTable: ", e);
}
}
// Sort Indexes
var existingIndexes = indexes.GroupBy(ind => new { KeyName = ind.Item2, Unique = ind.Item1 })
.Select(grp => new { grp.Key.KeyName, grp.Key.Unique, Columns = grp.Select(i => i.Item3).ToArray() }).ToArray();
var havePrimaryIndex = existingIndexes.FirstOrDefault(ind => ind.KeyName.Equals("PRIMARY"));
var currentPrimaryColumn = new string[] { };
if (havePrimaryIndex != null)
{
currentPrimaryColumn = havePrimaryIndex.Columns;
}
// Check for Any Difference in Primary Keys
if (table.Table.PrimaryKey.Length != currentPrimaryColumn.Length ||
table.Table.PrimaryKey.Any(pk => {
var column = currentPrimaryColumn.FirstOrDefault(col => col.Equals(pk.ColumnName, StringComparison.OrdinalIgnoreCase));
return(column == null);
}))
{
// Allow to edit Auto increment key if not previously modified
foreach (var oldkeys in currentColumns.Where(col => col.Primary && !alteredColumn.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("MODIFY `{0}` {1} {2}", oldkeys.ColumnName, oldkeys.ColumnType, oldkeys.AllowDbNull ? "DEFAULT NULL" : "NOT NULL"));
}
if (currentPrimaryColumn.Any())
{
columnDefs.Add("DROP PRIMARY KEY");
}
columnDefs.Add(string.Format("ADD PRIMARY KEY ({0})", string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName)))));
}
var tableIndexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary <string, DataColumn[]>;
// Check for Index Removal
foreach (var existing in existingIndexes.Where(ind => !ind.KeyName.Equals("PRIMARY")))
{
DataColumn[] realindex;
if (tableIndexes.TryGetValue(existing.KeyName, out realindex))
{
// Check for index modifications
if (realindex.Length != existing.Columns.Length ||
!realindex.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
columnDefs.Add(string.Format("ADD KEY `{0}` ({1})", existing.KeyName, string.Join(", ", realindex.Select(col => string.Format("`{0}`", col)))));
}
}
else
{
// Check for Unique
var realunique = table.Table.Constraints.OfType <UniqueConstraint>().FirstOrDefault(cstrnt => !cstrnt.IsPrimaryKey && cstrnt.ConstraintName.Equals(existing.KeyName, StringComparison.OrdinalIgnoreCase));
if (realunique == null)
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
}
else if (realunique.Columns.Length != existing.Columns.Length ||
!realunique.Columns.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
columnDefs.Add(string.Format("ADD UNIQUE KEY `{0}` ({1})", existing.KeyName, string.Join(", ", realunique.Columns.Select(col => string.Format("`{0}`", col)))));
}
}
}
// Missing Indexes
foreach (var missing in tableIndexes.Where(kv => existingIndexes.All(c => !c.KeyName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("ADD KEY `{0}` ({1})", missing.Key, string.Join(", ", missing.Value.Select(col => string.Format("`{0}`", col)))));
}
foreach (var missing in table.Table.Constraints.OfType <UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey && existingIndexes.All(c => !c.KeyName.Equals(cstrnt.ConstraintName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("ADD UNIQUE KEY `{0}` ({1})", missing.ConstraintName, string.Join(", ", missing.Columns.Select(col => string.Format("`{0}`", col)))));
}
if (!columnDefs.Any())
{
return;
}
if (log.IsInfoEnabled)
{
log.InfoFormat("Altering Table {0} this could take a few minutes...", table.TableName);
}
ExecuteNonQueryImpl(string.Format("ALTER TABLE `{0}` {1}", table.TableName, string.Join(", \n", columnDefs)));
}
/// <summary>
/// Alter an Existing Table to Match DataTableHandler Definition
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected void AlterTable(IEnumerable<TableRowBindind> currentColumns, DataTableHandler table)
{
var columnDefs = new List<string>();
var alteredColumn = new List<string>();
// Check for Missing Column or Wrong Type
foreach (var binding in table.FieldElementBindings)
{
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(binding.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
// Check Null && Type
if ((binding.DataElement != null && binding.DataElement.AllowDbNull) != column.AllowDbNull
|| !GetDatabaseType(binding, table).Equals(column.ColumnType, StringComparison.OrdinalIgnoreCase))
{
columnDefs.Add(string.Format("CHANGE `{1}` {0}", GetColumnDefinition(binding, table), binding.ColumnName));
alteredColumn.Add(binding.ColumnName);
}
continue;
}
columnDefs.Add(string.Format("ADD {0}", GetColumnDefinition(binding, table)));
}
// Check for Indexes
var indexes = new List<Tuple<bool, string, string>>();
try
{
ExecuteSelectImpl(string.Format("SHOW INDEX FROM `{0}`", table.TableName),
reader =>
{
while (reader.Read())
{
var unique = reader.GetInt64(1) < 1;
var indexname = reader.GetString(2);
var column = reader.GetString(4);
indexes.Add(new Tuple<bool, string, string>(unique, indexname, column));
if (log.IsDebugEnabled)
log.DebugFormat("AlterTable: Found Index `{0}` (Unique:{1}) on `{2}` in existing table {3}", indexname, unique, column, table.TableName);
}
if (log.IsDebugEnabled)
log.DebugFormat("AlterTable: {0} Indexes existing in table {1}", indexes.Count, table.TableName);
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
log.Debug("AlterTable: ", e);
}
// Sort Indexes
var existingIndexes = indexes.GroupBy(ind => new { KeyName = ind.Item2, Unique = ind.Item1 })
.Select(grp => new { grp.Key.KeyName, grp.Key.Unique, Columns = grp.Select(i => i.Item3).ToArray() }).ToArray();
var havePrimaryIndex = existingIndexes.FirstOrDefault(ind => ind.KeyName.Equals("PRIMARY"));
var currentPrimaryColumn = new string[] { };
if (havePrimaryIndex != null)
currentPrimaryColumn = havePrimaryIndex.Columns;
// Check for Any Difference in Primary Keys
if (table.Table.PrimaryKey.Length != currentPrimaryColumn.Length
|| table.Table.PrimaryKey.Any(pk => {
var column = currentPrimaryColumn.FirstOrDefault(col => col.Equals(pk.ColumnName, StringComparison.OrdinalIgnoreCase));
return column == null;
}))
{
// Allow to edit Auto increment key if not previously modified
foreach(var oldkeys in currentColumns.Where(col => col.Primary && !alteredColumn.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
columnDefs.Add(string.Format("MODIFY `{0}` {1} {2}", oldkeys.ColumnName, oldkeys.ColumnType, oldkeys.AllowDbNull ? "DEFAULT NULL" : "NOT NULL"));
if (currentPrimaryColumn.Any())
columnDefs.Add("DROP PRIMARY KEY");
columnDefs.Add(string.Format("ADD PRIMARY KEY ({0})", string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName)))));
}
var tableIndexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary<string, DataColumn[]>;
// Check for Index Removal
foreach (var existing in existingIndexes.Where(ind => !ind.KeyName.Equals("PRIMARY")))
{
DataColumn[] realindex;
if(tableIndexes.TryGetValue(existing.KeyName, out realindex))
{
// Check for index modifications
if (realindex.Length != existing.Columns.Length
|| !realindex.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
columnDefs.Add(string.Format("ADD KEY `{0}` ({1})", existing.KeyName, string.Join(", ", realindex.Select(col => string.Format("`{0}`", col)))));
}
}
else
{
// Check for Unique
var realunique = table.Table.Constraints.OfType<UniqueConstraint>().FirstOrDefault(cstrnt => !cstrnt.IsPrimaryKey && cstrnt.ConstraintName.Equals(existing.KeyName, StringComparison.OrdinalIgnoreCase));
if (realunique == null)
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
else if (realunique.Columns.Length != existing.Columns.Length
|| !realunique.Columns.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
columnDefs.Add(string.Format("ADD UNIQUE KEY `{0}` ({1})", existing.KeyName, string.Join(", ", realunique.Columns.Select(col => string.Format("`{0}`", col)))));
}
}
}
// Missing Indexes
foreach (var missing in tableIndexes.Where(kv => existingIndexes.All(c => !c.KeyName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase))))
columnDefs.Add(string.Format("ADD KEY `{0}` ({1})", missing.Key, string.Join(", ", missing.Value.Select(col => string.Format("`{0}`", col)))));
foreach (var missing in table.Table.Constraints.OfType<UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey && existingIndexes.All(c => !c.KeyName.Equals(cstrnt.ConstraintName, StringComparison.OrdinalIgnoreCase))))
columnDefs.Add(string.Format("ADD UNIQUE KEY `{0}` ({1})", missing.ConstraintName, string.Join(", ", missing.Columns.Select(col => string.Format("`{0}`", col)))));
if (!columnDefs.Any())
return;
if (log.IsInfoEnabled)
log.InfoFormat("Altering Table {0} this could take a few minutes...", table.TableName);
ExecuteNonQueryImpl(string.Format("ALTER TABLE `{0}` {1}", table.TableName, string.Join(", \n", columnDefs)));
}
/// <summary>
/// Check the XML Package Given for Replace or Insert Apply
/// </summary>
/// <param name="xml">FileInfo for XML Package</param>
/// <param name="replace">Enforce Replace Mode</param>
/// <returns>True if success, False if any errors</returns>
private bool CheckXMLPackageAndApply(FileInfo xml, bool replace)
{
var packageName = string.Format("{0}{1}{2}", xml.Directory.Name, Path.DirectorySeparatorChar, xml.Name);
if (log.IsInfoEnabled)
{
log.InfoFormat("Auto Loading XML File {0} into Database (Mode:{1})", packageName, replace ? "Replace" : "Insert");
}
var result = true;
try
{
// Load the XML File
var xmlTable = LoaderUnloaderXML.LoadXMLTableFromFile(xml);
if (xmlTable.Length > 0)
{
// Guess Object Type
var xmlType = xmlTable.First().GetType();
var tableHandler = new DataTableHandler(xmlType);
// Find unique Fields
var uniqueMember = DatabaseUtils.GetUniqueMembers(xmlType);
// Get all object "Method" Through Reflection
var classMethod = GameServer.Database.GetType().GetMethod("SelectAllObjects", Type.EmptyTypes);
var genericMethod = classMethod.MakeGenericMethod(xmlType);
var existingObjects = ((IEnumerable)genericMethod.Invoke(GameServer.Database, new object[] { })).Cast <DataObject>().ToArray();
// Store Object to Alter
var toDelete = new ConcurrentBag <DataObject>();
var toAdd = new ConcurrentBag <DataObject>();
// Check if an Object already exists
xmlTable.AsParallel().ForAll(obj => {
// Check if Exists Compare Unique and Non-Generated Primary Keys
var exists = existingObjects
.FirstOrDefault(entry => uniqueMember
.Any(constraint => constraint
.All(bind => bind.ValueType == typeof(string)
? bind.GetValue(entry).ToString().Equals(bind.GetValue(obj).ToString(), StringComparison.OrdinalIgnoreCase)
: bind.GetValue(entry) == bind.GetValue(obj))));
if (exists != null)
{
if (replace)
{
// Delete First
toDelete.Add(exists);
toAdd.Add(obj);
}
// Silently ignore duplicate inserts only...
}
else
{
toAdd.Add(obj);
}
});
// Delete First
foreach (var obj in toDelete)
{
obj.AllowDelete = true;
}
GameServer.Database.DeleteObject(toDelete);
// Then Insert
var previousAllowAdd = toAdd.Select(obj => obj.AllowAdd).ToArray();
foreach (var obj in toAdd)
{
obj.AllowAdd = true;
}
GameServer.Database.AddObject(toAdd);
// Reset Allow Add Flag
var current = 0;
foreach (var obj in toAdd)
{
obj.AllowAdd = previousAllowAdd[current];
current++;
}
}
else
{
if (log.IsWarnEnabled)
{
log.WarnFormat("XML Package {0} Found Empty, may be a parsing Error...", packageName);
}
result = false;
}
}
catch (Exception e)
{
if (log.IsErrorEnabled)
{
log.ErrorFormat("Error While Loading XML Package {0} into Database (Mode:{1}) - {2}", packageName, replace ? "Replace" : "Insert", e);
}
result = false;
}
return(result);
}
/// <summary>
/// Alter an Existing Table to Match DataTableHandler Definition
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected void AlterTable(IEnumerable<TableRowBindind> currentColumns, DataTableHandler table)
{
// If Column are not modified Alter Table is not needed...
if (!CheckTableAlteration(currentColumns, table))
{
// Table not Altered check for Indexes and return
CheckTableIndexAlteration(table);
return;
}
if (log.IsInfoEnabled)
log.InfoFormat("Altering Table {0} this could take a few minutes...", table.TableName);
var currentIndexes = new List<string>();
try
{
ExecuteSelectImpl("SELECT name FROM sqlite_master WHERE type == 'index' AND sql is not null AND tbl_name == @tableName",
new QueryParameter("@tableName", table.TableName),
reader =>
{
while (reader.Read())
currentIndexes.Add(reader.GetString(0));
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
log.Debug("AlterTableImpl: ", e);
if (log.IsWarnEnabled)
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, no modifications...", table.TableName);
throw;
}
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using(var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
// Delete Indexes
foreach(var index in currentIndexes)
{
using (var command = new SQLiteCommand(string.Format("DROP INDEX `{0}`", index), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
}
// Rename Table
using (var command = new SQLiteCommand(string.Format("ALTER TABLE `{0}` RENAME TO `{0}_bkp`", table.TableName), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Create New Table
using (var command = new SQLiteCommand(GetTableDefinition(table), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Create Indexes
foreach (var index in GetIndexesDefinition(table))
{
using (var command = new SQLiteCommand(index, conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
}
// Copy Data
var columns = table.FieldElementBindings.Where(bind => currentColumns.Any(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase)))
.Select(bind => string.Format("`{0}`", bind.ColumnName));
using (var command = new SQLiteCommand(string.Format("INSERT INTO `{0}` ({1}) SELECT {1} FROM `{0}_bkp`", table.TableName, string.Join(", ", columns)), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Drop Renamed Table
using (var command = new SQLiteCommand(string.Format("DROP TABLE `{0}_bkp`", table.TableName), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
if (log.IsInfoEnabled)
log.InfoFormat("AlterTableImpl: Table {0} Altered...", table.TableName);
}
catch (Exception e)
{
tran.Rollback();
if (log.IsDebugEnabled)
log.Debug("AlterTableImpl: ", e);
if (log.IsWarnEnabled)
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, rollback...\n{1}", table.TableName, e);
}
}
}
}
/// <summary>
/// Check if Table Indexes Need Alteration
/// </summary>
/// <param name="table">DataTableHandler to Implement</param>
protected void CheckTableIndexAlteration(DataTableHandler table)
{
// Query Existing Indexes
var currentIndexes = new List<Tuple<string, string>>();
try
{
ExecuteSelectImpl("SELECT name, sql FROM sqlite_master WHERE type == 'index' AND sql is not null AND tbl_name == @tableName",
new QueryParameter("@tableName", table.TableName),
reader =>
{
while (reader.Read())
currentIndexes.Add(new Tuple<string, string>(reader.GetString(0), reader.GetString(1)));
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
log.Debug("CheckTableIndexAlteration: ", e);
throw;
}
var sortedIndexes = currentIndexes.Select(ind => {
var unique = ind.Item2.Trim().StartsWith("CREATE UNIQUE", StringComparison.OrdinalIgnoreCase);
var columns = ind.Item2.Substring(ind.Item2.IndexOf('(')).Split(',').Select(sp => sp.Trim('`', '(', ')', ' '));
return new { KeyName = ind.Item1, Unique = unique, Columns = columns.ToArray() };
}).ToArray();
if (log.IsDebugEnabled)
log.DebugFormat("CheckTableIndexAlteration: {0} Indexes existing in table {1}", sortedIndexes.Length, table.TableName);
var tableIndexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary<string, DataColumn[]>;
var alterQueries = new List<string>();
// Check for Index Removal
foreach (var existing in sortedIndexes)
{
if (log.IsDebugEnabled)
log.DebugFormat("CheckTableIndexAlteration: Found Index `{0}` (Unique:{1}) on ({2}) in existing table {3}", existing.KeyName, existing.Unique, string.Join(", ", existing.Columns), table.TableName);
DataColumn[] realindex;
if(tableIndexes.TryGetValue(existing.KeyName, out realindex))
{
// Check for index modifications
if (realindex.Length != existing.Columns.Length
|| !realindex.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
alterQueries.Add(string.Format("DROP INDEX `{0}`", existing.KeyName));
alterQueries.Add(string.Format("CREATE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", existing.KeyName, string.Join(", ", realindex.Select(col => string.Format("`{0}`", col))), table.TableName));
}
}
else
{
// Check for Unique
var realunique = table.Table.Constraints.OfType<UniqueConstraint>().FirstOrDefault(cstrnt => !cstrnt.IsPrimaryKey && cstrnt.ConstraintName.Equals(existing.KeyName, StringComparison.OrdinalIgnoreCase));
if (realunique == null)
alterQueries.Add(string.Format("DROP INDEX `{0}`", existing.KeyName));
else if (realunique.Columns.Length != existing.Columns.Length
|| !realunique.Columns.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
alterQueries.Add(string.Format("DROP INDEX `{0}`", existing.KeyName));
alterQueries.Add(string.Format("CREATE UNIQUE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", existing.KeyName, string.Join(", ", realunique.Columns.Select(col => string.Format("`{0}`", col))), table.TableName));
}
}
}
// Missing Indexes
foreach (var missing in tableIndexes.Where(kv => sortedIndexes.All(c => !c.KeyName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase))))
alterQueries.Add(string.Format("CREATE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", missing.Key, string.Join(", ", missing.Value.Select(col => string.Format("`{0}`", col))), table.TableName));
foreach (var missing in table.Table.Constraints.OfType<UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey && sortedIndexes.All(c => !c.KeyName.Equals(cstrnt.ConstraintName, StringComparison.OrdinalIgnoreCase))))
alterQueries.Add(string.Format("CREATE UNIQUE INDEX IF NOT EXISTS `{0}` ON `{2}` ({1})", missing.ConstraintName, string.Join(", ", missing.Columns.Select(col => string.Format("`{0}`", col))), table.TableName));
if (!alterQueries.Any())
return;
if (log.IsDebugEnabled)
log.DebugFormat("Altering Table Indexes {0} this could take a few minutes...", table.TableName);
foreach (var query in alterQueries)
ExecuteNonQueryImpl(query);
}
/// <summary>
/// Create a New Table from DataTableHandler Definition
/// </summary>
/// <param name="table">DataTableHandler Definition to Create in Database</param>
protected void CreateTable(DataTableHandler table)
{
var columnDef = table.FieldElementBindings
.Select(bind => GetColumnDefinition(bind, table));
var primaryFields = string.Format("PRIMARY KEY ({0})",
string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName))));
var uniqueFields = table.Table.Constraints.OfType<UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey)
.Select(cstrnt => string.Format("UNIQUE KEY `{0}` ({1})", cstrnt.ConstraintName,
string.Join(", ", cstrnt.Columns.Select(col => string.Format("`{0}`", col.ColumnName)))));
var indexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary<string, DataColumn[]>;
var indexesFields = indexes == null ? new string[] { }
: indexes.Select(index => string.Format("KEY `{0}` ({1})", index.Key,
string.Join(", ", index.Value.Select(col => string.Format("`{0}`", col.ColumnName)))));
var command = string.Format("CREATE TABLE IF NOT EXISTS `{0}` ({1})", table.TableName,
string.Join(", \n", columnDef.Concat(new [] { primaryFields }).Concat(uniqueFields).Concat(indexesFields)));
ExecuteNonQueryImpl(command);
}
/// <summary>
/// Alter an Existing Table to Match DataTableHandler Definition
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected void AlterTable(IEnumerable <TableRowBindind> currentColumns, DataTableHandler table)
{
var columnDefs = new List <string>();
var alteredColumn = new List <string>();
// Check for Missing Column or Wrong Type
foreach (var binding in table.FieldElementBindings)
{
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(binding.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
// Check Null && Type
if ((binding.DataElement != null && binding.DataElement.AllowDbNull) != column.AllowDbNull ||
!GetDatabaseType(binding, table).Equals(column.ColumnType, StringComparison.OrdinalIgnoreCase))
{
columnDefs.Add(string.Format("CHANGE `{1}` {0}", GetColumnDefinition(binding, table), binding.ColumnName));
alteredColumn.Add(binding.ColumnName);
}
continue;
}
columnDefs.Add(string.Format("ADD {0}", GetColumnDefinition(binding, table)));
}
// Check for Indexes
var indexes = new List <Tuple <bool, string, string> >();
try
{
ExecuteSelectImpl(string.Format("SHOW INDEX FROM `{0}`", table.TableName),
new[] { Array.Empty <QueryParameter>() },
reader =>
{
while (reader.Read())
{
var unique = reader.GetInt64(1) < 1;
var indexname = reader.GetString(2);
var column = reader.GetString(4);
indexes.Add(new Tuple <bool, string, string>(unique, indexname, column));
if (log.IsDebugEnabled)
{
log.DebugFormat("AlterTable: Found Index `{0}` (Unique:{1}) on `{2}` in existing table {3}", indexname, unique, column, table.TableName);
}
}
if (log.IsDebugEnabled)
{
log.DebugFormat("AlterTable: {0} Indexes existing in table {1}", indexes.Count, table.TableName);
}
});
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("AlterTable: ", e);
}
}
// Sort Indexes
var existingIndexes = indexes.GroupBy(ind => new { KeyName = ind.Item2, Unique = ind.Item1 })
.Select(grp => new { grp.Key.KeyName, grp.Key.Unique, Columns = grp.Select(i => i.Item3).ToArray() }).ToArray();
var havePrimaryIndex = existingIndexes.FirstOrDefault(ind => ind.KeyName.Equals("PRIMARY"));
var currentPrimaryColumn = Array.Empty <string>();
if (havePrimaryIndex != null)
{
currentPrimaryColumn = havePrimaryIndex.Columns;
}
// Check for Any Difference in Primary Keys
if (table.Table.PrimaryKey.Length != currentPrimaryColumn.Length ||
table.Table.PrimaryKey.Any(pk => {
var column = currentPrimaryColumn.FirstOrDefault(col => col.Equals(pk.ColumnName, StringComparison.OrdinalIgnoreCase));
return(column == null);
}))
{
// Allow to edit Auto increment key if not previously modified
foreach (var oldkeys in currentColumns.Where(col => col.Primary && !alteredColumn.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("MODIFY `{0}` {1} {2}", oldkeys.ColumnName, oldkeys.ColumnType, oldkeys.AllowDbNull ? "DEFAULT NULL" : "NOT NULL"));
}
if (currentPrimaryColumn.Any())
{
columnDefs.Add("DROP PRIMARY KEY");
}
columnDefs.Add(string.Format("ADD PRIMARY KEY ({0})", string.Join(", ", table.Table.PrimaryKey.Select(pk => string.Format("`{0}`", pk.ColumnName)))));
}
var tableIndexes = table.Table.ExtendedProperties["INDEXES"] as Dictionary <string, DataColumn[]>;
// Check for Index Removal
foreach (var existing in existingIndexes.Where(ind => !ind.KeyName.Equals("PRIMARY")))
{
DataColumn[] realindex;
if (tableIndexes.TryGetValue(existing.KeyName, out realindex))
{
// Check for index modifications
if (realindex.Length != existing.Columns.Length ||
!realindex.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
columnDefs.Add(string.Format("ADD KEY `{0}` ({1})", existing.KeyName, string.Join(", ", realindex.Select(col => string.Format("`{0}`", col)))));
}
}
else
{
// Check for Unique
var realunique = table.Table.Constraints.OfType <UniqueConstraint>().FirstOrDefault(cstrnt => !cstrnt.IsPrimaryKey && cstrnt.ConstraintName.Equals(existing.KeyName, StringComparison.OrdinalIgnoreCase));
if (realunique == null)
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
}
else if (realunique.Columns.Length != existing.Columns.Length ||
!realunique.Columns.All(col => existing.Columns.Any(c => c.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("DROP KEY `{0}`", existing.KeyName));
columnDefs.Add(string.Format("ADD UNIQUE KEY `{0}` ({1})", existing.KeyName, string.Join(", ", realunique.Columns.Select(col => string.Format("`{0}`", col)))));
}
}
}
// Missing Indexes
foreach (var missing in tableIndexes.Where(kv => existingIndexes.All(c => !c.KeyName.Equals(kv.Key, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("ADD KEY `{0}` ({1})", missing.Key, string.Join(", ", missing.Value.Select(col => string.Format("`{0}`", col)))));
}
foreach (var missing in table.Table.Constraints.OfType <UniqueConstraint>().Where(cstrnt => !cstrnt.IsPrimaryKey && existingIndexes.All(c => !c.KeyName.Equals(cstrnt.ConstraintName, StringComparison.OrdinalIgnoreCase))))
{
columnDefs.Add(string.Format("ADD UNIQUE KEY `{0}` ({1})", missing.ConstraintName, string.Join(", ", missing.Columns.Select(col => string.Format("`{0}`", col)))));
}
if (!columnDefs.Any())
{
return;
}
if (log.IsInfoEnabled)
{
log.InfoFormat("Altering Table {0} this could take a few minutes...", table.TableName);
}
using (var conn = new MySqlConnection(ConnectionString))
{
using (var cmd = conn.CreateCommand())
{
conn.Open();
using (var tran = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
try
{
cmd.Transaction = tran;
// Update Null Values
var nullToNonNull = table.FieldElementBindings.Join(currentColumns, bind => bind.ColumnName, col => col.ColumnName, (bind, col) => new { bind, col }, StringComparer.OrdinalIgnoreCase)
.Where(match => match.bind.DataElement != null && match.bind.DataElement.AllowDbNull == false && match.col.AllowDbNull == true)
.Select(match => match.bind)
.ToArray();
if (nullToNonNull.Any())
{
cmd.CommandText = string.Format("UPDATE `{0}` SET {1} WHERE {2}"
, table.TableName
, string.Join(", ", nullToNonNull.Select(bind => string.Format("`{0}` = IFNULL(`{0}`, {1})", bind.ColumnName, bind.ValueType == typeof(DateTime)
? "'2000-01-01 00:00:00'"
: bind.ValueType == typeof(string)
? "''"
: "0")))
, string.Join(" OR ", nullToNonNull.Select(bind => string.Format("`{0}` IS NULL", bind.ColumnName)))
);
cmd.ExecuteNonQuery();
}
// Alter Table
cmd.CommandText = string.Format("ALTER TABLE `{0}` {1}", table.TableName, string.Join(", \n", columnDefs));
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
if (log.IsDebugEnabled)
{
log.Debug("AlterTableImpl: ", e);
}
if (log.IsWarnEnabled)
{
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, rollback...\n{1}", table.TableName, e);
}
throw;
}
}
}
}
}
/// <summary>
/// Convert a Table ElementBinding to Database Type string (Upper)
/// </summary>
/// <param name="bind">ElementBindind to Convert</param>
/// <param name="table">DataTableHandler for Special cases</param>
/// <returns>Database Type string ToUpper</returns>
protected virtual string GetDatabaseType(ElementBinding bind, DataTableHandler table)
{
string type = null;
// Check Value Type
if (bind.ValueType == typeof(char))
{
type = "UNSIGNED SMALLINT(5)";
}
else if (bind.ValueType == typeof(sbyte))
{
// override to prevent byte conversion
type = "SMALLINT(3)";
}
else if (bind.ValueType == typeof(short))
{
type = "SMALLINT(6)";
}
else if (bind.ValueType == typeof(int))
{
type = "INT(11)";
}
else if (bind.ValueType == typeof(long))
{
type = "BIGINT(20)";
}
else if (bind.ValueType == typeof(byte))
{
type = "UNSIGNED TINYINT(3)";
}
else if (bind.ValueType == typeof(ushort))
{
type = "UNSIGNED SMALLINT(5)";
}
else if (bind.ValueType == typeof(uint))
{
type = "UNSIGNED INT(10)";
}
else if (bind.ValueType == typeof(ulong))
{
type = "UNSIGNED BIGINT(20)";
}
else if (bind.ValueType == typeof(bool))
{
type = "TINYINT(1)";
}
else if (bind.ValueType == typeof(DateTime))
{
type = "DATETIME";
}
else if (bind.ValueType == typeof(float))
{
type = "FLOAT";
}
else if (bind.ValueType == typeof(double))
{
type = "DOUBLE";
}
else if (bind.ValueType == typeof(string))
{
if (bind.DataElement != null && bind.DataElement.Varchar > 0)
{
type = string.Format("VARCHAR({0})", bind.DataElement.Varchar);
}
else if (table.Table.PrimaryKey.Any(key => key.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase))
|| table.Table.Constraints.OfType<UniqueConstraint>().Any(cstrnt => cstrnt.Columns.Any(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase)))
|| (table.Table.ExtendedProperties["INDEXES"] != null && (table.Table.ExtendedProperties["INDEXES"] as Dictionary<string, DataColumn[]>)
.Any(kv => kv.Value.Any(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase)))))
{
// If is in Primary Key Constraint or Unique Constraint or Index row, cast to Varchar.
type = "VARCHAR(255)";
}
else
{
type = "TEXT";
}
}
else
{
type = "BLOB";
}
if (bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement)
type = "INTEGER";
return type;
}
/// <summary>
/// Alter an Existing Table to Match DataTableHandler Definition
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected void AlterTable(IEnumerable <TableRowBindind> currentColumns, DataTableHandler table)
{
// If Column are not modified Alter Table is not needed...
if (!CheckTableAlteration(currentColumns, table))
{
// Table not Altered check for Indexes and return
CheckTableIndexAlteration(table);
return;
}
if (log.IsInfoEnabled)
{
log.InfoFormat("Altering Table {0} this could take a few minutes...", table.TableName);
}
var currentIndexes = new List <string>();
try
{
ExecuteSelectImpl("SELECT name FROM sqlite_master WHERE type == 'index' AND sql is not null AND tbl_name == @tableName",
new QueryParameter("@tableName", table.TableName),
reader =>
{
while (reader.Read())
{
currentIndexes.Add(reader.GetString(0));
}
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("AlterTableImpl: ", e);
}
if (log.IsWarnEnabled)
{
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, no modifications...", table.TableName);
}
throw;
}
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
// Delete Indexes
foreach (var index in currentIndexes)
{
using (var command = new SQLiteCommand(string.Format("DROP INDEX `{0}`", index), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
}
// Rename Table
using (var command = new SQLiteCommand(string.Format("ALTER TABLE `{0}` RENAME TO `{0}_bkp`", table.TableName), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Create New Table
using (var command = new SQLiteCommand(GetTableDefinition(table), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Create Indexes
foreach (var index in GetIndexesDefinition(table))
{
using (var command = new SQLiteCommand(index, conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
}
// Copy Data, Convert Null to Default when needed...
var matchingColumns = table.FieldElementBindings.Join(currentColumns, bind => bind.ColumnName, col => col.ColumnName, (bind, col) => new { bind, col }, StringComparer.OrdinalIgnoreCase);
var columns = matchingColumns.Select(match => {
if (match.bind.DataElement != null && match.bind.DataElement.AllowDbNull == false && match.col.AllowDbNull == true)
{
if (match.bind.ValueType == typeof(DateTime))
{
return new { Target = match.bind.ColumnName, Source = string.Format("IFNULL(`{0}`, {1})", match.bind.ColumnName, "'2000-01-01 00:00:00'") }
}
;
if (match.bind.ValueType == typeof(string))
{
return new { Target = match.bind.ColumnName, Source = string.Format("IFNULL(`{0}`, {1})", match.bind.ColumnName, "''") }
}
;
return(new { Target = match.bind.ColumnName, Source = string.Format("IFNULL(`{0}`, {1})", match.bind.ColumnName, "0") });
}
return(new { Target = match.bind.ColumnName, Source = string.Format("`{0}`", match.bind.ColumnName) });
});
using (var command = new SQLiteCommand(string.Format("INSERT INTO `{0}` ({1}) SELECT {2} FROM `{0}_bkp`", table.TableName, string.Join(", ", columns.Select(c => c.Target)), string.Join(", ", columns.Select(c => c.Source))), conn))
{
if (log.IsDebugEnabled)
{
log.DebugFormat("AlterTableImpl, Insert/Select: {0}", command.CommandText);
}
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Drop Renamed Table
using (var command = new SQLiteCommand(string.Format("DROP TABLE `{0}_bkp`", table.TableName), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
if (log.IsInfoEnabled)
{
log.InfoFormat("AlterTableImpl: Table {0} Altered...", table.TableName);
}
}
catch (Exception e)
{
tran.Rollback();
if (log.IsDebugEnabled)
{
log.Debug("AlterTableImpl: ", e);
}
if (log.IsWarnEnabled)
{
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, rollback...\n{1}", table.TableName, e);
}
throw;
}
}
}
}
/// <summary>
/// Check if this Table need Alteration
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected bool CheckTableAlteration(IEnumerable<TableRowBindind> currentColumns, DataTableHandler table)
{
// Check for Any differences in Columns
if (table.FieldElementBindings
.Any(bind => {
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null)
{
// Check Null
if ((bind.DataElement != null && bind.DataElement.AllowDbNull) != column.AllowDbNull)
return true;
// Check Type
if (!GetDatabaseType(bind, table).Equals(column.ColumnType, StringComparison.OrdinalIgnoreCase))
return true;
// Field are identical
return false;
}
// Field missing
return true;
}))
return true;
// Check for Any Difference in Primary Keys
if (table.Table.PrimaryKey.Length != currentColumns.Count(col => col.Primary)
|| table.Table.PrimaryKey.Any(pk => {
var column = currentColumns.FirstOrDefault(col => col.ColumnName.Equals(pk.ColumnName, StringComparison.OrdinalIgnoreCase));
if (column != null && column.Primary)
return false;
return true;
}))
return true;
// No Alteration Needed
return false;
}
/// <summary>
/// Get Database Column Definition for ElementBinding
/// </summary>
/// <param name="bind">ElementBinding for Column Definition</param>
/// <param name="table">DataTableHanlder for Special cases</param>
/// <returns>Column Definitnion string.</returns>
protected virtual string GetColumnDefinition(ElementBinding bind, DataTableHandler table)
{
string type = GetDatabaseType(bind, table);
string defaultDef = null;
// Check for Default Value depending on Constraints and Type
if (bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement)
{
defaultDef = "NOT NULL PRIMARY KEY AUTOINCREMENT";
}
else if (bind.DataElement != null && bind.DataElement.AllowDbNull)
{
defaultDef = "DEFAULT NULL";
}
else if (bind.ValueType == typeof(DateTime))
{
defaultDef = "NOT NULL DEFAULT '2000-01-01 00:00:00'";
}
else
{
defaultDef = "NOT NULL";
}
// Force Case Insensitive Text Field to Match MySQL Behavior
if (bind.ValueType == typeof(string))
defaultDef = string.Format("{0} {1}", defaultDef, "COLLATE NOCASE");
return string.Format("`{0}` {1} {2}", bind.ColumnName, type, defaultDef);
}
/// <summary>
/// Create a New Table from DataTableHandler Definition
/// </summary>
/// <param name="table">DataTableHandler Definition to Create in Database</param>
protected void CreateTable(DataTableHandler table)
{
ExecuteNonQueryImpl(GetTableDefinition(table));
foreach (var commands in GetIndexesDefinition(table))
ExecuteNonQueryImpl(commands);
}
public void TestMultiIndexesDataObject()
{
var dth = new DataTableHandler(typeof(TestTableWithMultiIndexes));
Assert.DoesNotThrow(() => Database.CheckOrCreateTableImpl(dth), "Registering Test Table with Overlapping Indexes should not Throw exceptions.");
}
/// <summary>
/// Alter an Existing Table to Match DataTableHandler Definition
/// </summary>
/// <param name="currentColumns">Current Existing Columns</param>
/// <param name="table">DataTableHandler to Implement</param>
protected void AlterTable(IEnumerable <TableRowBindind> currentColumns, DataTableHandler table)
{
// If Column are not modified Alter Table is not needed...
if (!CheckTableAlteration(currentColumns, table))
{
// Table not Altered check for Indexes and return
CheckTableIndexAlteration(table);
return;
}
if (log.IsInfoEnabled)
{
log.InfoFormat("Altering Table {0} this could take a few minutes...", table.TableName);
}
var currentIndexes = new List <string>();
try
{
ExecuteSelectImpl("SELECT name FROM sqlite_master WHERE type == 'index' AND sql is not null AND tbl_name == @tableName",
new QueryParameter("@tableName", table.TableName),
reader =>
{
while (reader.Read())
{
currentIndexes.Add(reader.GetString(0));
}
}, IsolationLevel.DEFAULT);
}
catch (Exception e)
{
if (log.IsDebugEnabled)
{
log.Debug("AlterTableImpl: ", e);
}
if (log.IsWarnEnabled)
{
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, no modifications...", table.TableName);
}
throw;
}
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
// Delete Indexes
foreach (var index in currentIndexes)
{
using (var command = new SQLiteCommand(string.Format("DROP INDEX `{0}`", index), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
}
// Rename Table
using (var command = new SQLiteCommand(string.Format("ALTER TABLE `{0}` RENAME TO `{0}_bkp`", table.TableName), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Create New Table
using (var command = new SQLiteCommand(GetTableDefinition(table), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Create Indexes
foreach (var index in GetIndexesDefinition(table))
{
using (var command = new SQLiteCommand(index, conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
}
// Copy Data
var columns = table.FieldElementBindings.Where(bind => currentColumns.Any(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase)))
.Select(bind => string.Format("`{0}`", bind.ColumnName));
using (var command = new SQLiteCommand(string.Format("INSERT INTO `{0}` ({1}) SELECT {1} FROM `{0}_bkp`", table.TableName, string.Join(", ", columns)), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
// Drop Renamed Table
using (var command = new SQLiteCommand(string.Format("DROP TABLE `{0}_bkp`", table.TableName), conn))
{
command.Transaction = tran;
command.ExecuteNonQuery();
}
tran.Commit();
if (log.IsInfoEnabled)
{
log.InfoFormat("AlterTableImpl: Table {0} Altered...", table.TableName);
}
}
catch (Exception e)
{
tran.Rollback();
if (log.IsDebugEnabled)
{
log.Debug("AlterTableImpl: ", e);
}
if (log.IsWarnEnabled)
{
log.WarnFormat("AlterTableImpl: Error While Altering Table {0}, rollback...\n{1}", table.TableName, e);
}
}
}
}
}
/// <summary>
/// Convert a Table ElementBinding to Database Type string (Upper)
/// </summary>
/// <param name="bind">ElementBindind to Convert</param>
/// <param name="table">DataTableHandler for Special cases</param>
/// <returns>Database Type string ToUpper</returns>
protected virtual string GetDatabaseType(ElementBinding bind, DataTableHandler table)
{
string type = null;
// Check Value Type
if (bind.ValueType == typeof(char))
{
type = "SMALLINT(5) UNSIGNED";
}
else if (bind.ValueType == typeof(DateTime))
{
type = "DATETIME";
}
else if (bind.ValueType == typeof(sbyte))
{
type = "TINYINT(3)";
}
else if (bind.ValueType == typeof(short))
{
type = "SMALLINT(6)";
}
else if (bind.ValueType == typeof(int))
{
type = "INT(11)";
}
else if (bind.ValueType == typeof(long))
{
type = "BIGINT(20)";
}
else if (bind.ValueType == typeof(byte))
{
type = "TINYINT(3) UNSIGNED";
}
else if (bind.ValueType == typeof(ushort))
{
type = "SMALLINT(5) UNSIGNED";
}
else if (bind.ValueType == typeof(uint))
{
type = "INT(10) UNSIGNED";
}
else if (bind.ValueType == typeof(ulong))
{
type = "BIGINT(20) UNSIGNED";
}
else if (bind.ValueType == typeof(float))
{
// Float Value have less precision than C# Single.
type = "DOUBLE";
}
else if (bind.ValueType == typeof(double))
{
type = "DOUBLE";
}
else if (bind.ValueType == typeof(bool))
{
type = "TINYINT(1)";
}
else if (bind.ValueType == typeof(string))
{
if (bind.DataElement != null && bind.DataElement.Varchar > 0)
{
type = string.Format("VARCHAR({0})", bind.DataElement.Varchar);
}
else if (table.Table.PrimaryKey.Any(key => key.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase)) ||
table.Table.Constraints.OfType <UniqueConstraint>().Any(cstrnt => cstrnt.Columns.Any(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase))) ||
(table.Table.ExtendedProperties["INDEXES"] != null && (table.Table.ExtendedProperties["INDEXES"] as Dictionary <string, DataColumn[]>)
.Any(kv => kv.Value.Any(col => col.ColumnName.Equals(bind.ColumnName, StringComparison.OrdinalIgnoreCase)))))
{
// If is in Primary Key Constraint or Unique Constraint or Index row, cast to Varchar.
type = "VARCHAR(255)";
}
else
{
type = "TEXT";
}
}
else
{
type = "BLOB";
}
if (bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement)
{
if (bind.ValueType == typeof(ulong) || bind.ValueType == typeof(long))
{
type = "BIGINT(20)";
}
else
{
type = "INT(11)";
}
}
return(type);
}
/// <summary>
/// Get Database Column Definition for ElementBinding
/// </summary>
/// <param name="bind">ElementBinding for Column Definition</param>
/// <param name="table">DataTableHanlder for Special cases</param>
/// <returns>Column Definitnion string.</returns>
protected virtual string GetColumnDefinition(ElementBinding bind, DataTableHandler table)
{
string type = GetDatabaseType(bind, table);
string defaultDef = null;
// Check for Default Value depending on Constraints and Type
if (bind.PrimaryKey != null && bind.PrimaryKey.AutoIncrement)
{
defaultDef = "NOT NULL AUTO_INCREMENT";
}
else if (bind.DataElement != null && bind.DataElement.AllowDbNull)
{
defaultDef = "DEFAULT NULL";
}
else if (bind.ValueType == typeof(DateTime))
{
defaultDef = "NOT NULL DEFAULT '2000-01-01 00:00:00'";
}
else
{
defaultDef = "NOT NULL";
}
return string.Format("`{0}` {1} {2}", bind.ColumnName, type, defaultDef);
}
