public void CopyTableToTable(string sourceTableName, string destinationTableName, ColumnDefinition[] columnDefinitions)
{
if (!TableExists(sourceTableName))
{
throw new MigrationOperationException("Cannot copy table to new name, source table does not exist: " + sourceTableName);
}
if (TableExists(destinationTableName))
{
throw new MigrationOperationException("Cannot copy table to new name, table with same name already exists: " + destinationTableName);
}
if (!VerifyTableExists(sourceTableName, columnDefinitions))
{
throw new MigrationOperationException("Cannot copy table to new name, source table does not match columnDefinitions: " + destinationTableName);
}
EnsureTableExists(destinationTableName, columnDefinitions);
CopyAllDataBetweenMatchingTables(sourceTableName, destinationTableName, columnDefinitions);
}
public override void UpdateTable(string table, ColumnDefinition[] columns,
Dictionary<string, string> renameColumns)
{
table = table.ToLower();
if (!TableExists(table))
{
throw new DataManagerException("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> removedColumns = new Dictionary<string, ColumnDefinition>();
Dictionary<string, ColumnDefinition> modifiedColumns = new Dictionary<string, ColumnDefinition>();
#if (!ISWIN)
Dictionary<string, ColumnDefinition> addedColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in columns)
{
if (!oldColumns.Contains(column)) addedColumns.Add(column.Name.ToLower(), column);
}
foreach (ColumnDefinition column in oldColumns)
{
if (!columns.Contains(column))
{
if (addedColumns.ContainsKey(column.Name.ToLower()))
{
if (column.Name.ToLower() != addedColumns[column.Name.ToLower()].Name.ToLower() || column.Type != addedColumns[column.Name.ToLower()].Type)
modifiedColumns.Add(column.Name.ToLower(), addedColumns[column.Name.ToLower()]);
addedColumns.Remove(column.Name.ToLower());
}
else
removedColumns.Add(column.Name.ToLower(), column);
}
}
#else
Dictionary<string, ColumnDefinition> addedColumns =
columns.Where(column => !oldColumns.Contains(column)).ToDictionary(column => column.Name.ToLower());
foreach (ColumnDefinition column in oldColumns.Where(column => !columns.Contains(column)))
{
if (addedColumns.ContainsKey(column.Name.ToLower()))
{
if (column.Name.ToLower() != addedColumns[column.Name.ToLower()].Name.ToLower() ||
column.Type != addedColumns[column.Name.ToLower()].Type)
modifiedColumns.Add(column.Name.ToLower(), addedColumns[column.Name.ToLower()]);
addedColumns.Remove(column.Name.ToLower());
}
else
removedColumns.Add(column.Name.ToLower(), column);
}
#endif
try
{
#if (!ISWIN)
foreach (ColumnDefinition column in addedColumns.Values)
{
string addedColumnsQuery = "add `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) + " ";
string query = string.Format("alter table " + table + " " + addedColumnsQuery);
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (ColumnDefinition column in modifiedColumns.Values)
{
string modifiedColumnsQuery = "modify column `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) + " ";
string query = string.Format("alter table " + table + " " + modifiedColumnsQuery);
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (ColumnDefinition column in removedColumns.Values)
{
string droppedColumnsQuery = "drop `" + column.Name + "` ";
string query = string.Format("alter table " + table + " " + droppedColumnsQuery);
ExecuteNonQuery(query, new Dictionary<string, object>());
}
#else
foreach (string query in addedColumns.Values.Select(column => "add `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) +
" ").Select(addedColumnsQuery => string.Format("alter table " + table + " " + addedColumnsQuery)))
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (string query in modifiedColumns.Values.Select(column => "modify column `" + column.Name + "` " +
GetColumnTypeStringSymbol(column.Type) + " ").Select(modifiedColumnsQuery => string.Format("alter table " + table + " " + modifiedColumnsQuery)))
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (string query in removedColumns.Values.Select(column => "drop `" + column.Name + "` ").Select(droppedColumnsQuery => string.Format("alter table " + table + " " + droppedColumnsQuery)))
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
#endif
}
catch (Exception e)
{
MainConsole.Instance.Error("[MySQLDataLoader] UpdateTable", e);
}
}
protected abstract void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName, ColumnDefinition[] columnDefinitions, IndexDefinition[] indexDefinitions);
public void EnsureTableExists(string tableName, ColumnDefinition[] columnDefinitions, IndexDefinition[] indexDefinitions, Dictionary<string, string> renameColumns)
{
if (TableExists(tableName))
{
if (!VerifyTableExists(tableName, columnDefinitions, indexDefinitions))
{
//throw new MigrationOperationException("Cannot create, table with same name and different columns already exists. This should be fixed in a migration: " + tableName);
UpdateTable(tableName, columnDefinitions, indexDefinitions, renameColumns);
}
return;
}
CreateTable(tableName, columnDefinitions, indexDefinitions);
}
public override void UpdateTable(string table, ColumnDefinition[] columns,
Dictionary<string, string> renameColumns)
{
if (!TableExists(table))
{
throw new DataManagerException("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> sameColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in oldColumns)
{
#if (!ISWIN)
foreach (ColumnDefinition innercolumn in columns)
{
if (innercolumn.Name.ToLower() == column.Name.ToLower() || renameColumns.ContainsKey(column.Name) && renameColumns[column.Name].ToLower() == innercolumn.Name.ToLower())
{
sameColumns.Add(column.Name, column);
break;
}
}
#else
if (columns.Any(innercolumn => innercolumn.Name.ToLower() == column.Name.ToLower() ||
renameColumns.ContainsKey(column.Name) &&
renameColumns[column.Name].ToLower() == innercolumn.Name.ToLower()))
{
sameColumns.Add(column.Name, column);
}
#endif
}
string renamedTempTableColumnDefinition = string.Empty;
string renamedTempTableColumn = string.Empty;
foreach (ColumnDefinition column in oldColumns)
{
if (renamedTempTableColumnDefinition != string.Empty)
{
renamedTempTableColumnDefinition += ", ";
renamedTempTableColumn += ", ";
}
renamedTempTableColumn += column.Name;
renamedTempTableColumnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type);
}
string query = "CREATE TABLE " + table + "__temp(" + renamedTempTableColumnDefinition + ");";
var cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
query = "INSERT INTO " + table + "__temp SELECT " + renamedTempTableColumn + " from " + table + ";";
cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
query = "drop table " + table;
cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
string newTableColumnDefinition = string.Empty;
#if (!ISWIN)
List<ColumnDefinition> primaryColumns = new List<ColumnDefinition>();
foreach (ColumnDefinition column in columns)
{
if (column.IsPrimary) primaryColumns.Add(column);
}
#else
List<ColumnDefinition> primaryColumns = columns.Where(column => column.IsPrimary).ToList();
#endif
bool multiplePrimary = primaryColumns.Count > 1;
foreach (ColumnDefinition column in columns)
{
if (newTableColumnDefinition != string.Empty)
{
newTableColumnDefinition += ", ";
}
newTableColumnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type) +
((column.IsPrimary && !multiplePrimary) ? " PRIMARY KEY" : string.Empty);
}
string multiplePrimaryString = string.Empty;
if (multiplePrimary)
{
string listOfPrimaryNamesString = string.Empty;
foreach (ColumnDefinition column in primaryColumns)
{
if (listOfPrimaryNamesString != string.Empty)
{
listOfPrimaryNamesString += ", ";
}
listOfPrimaryNamesString += column.Name;
}
multiplePrimaryString = string.Format(", PRIMARY KEY ({0}) ", listOfPrimaryNamesString);
}
query = string.Format("create table " + table + " ( {0} {1}) ", newTableColumnDefinition,
multiplePrimaryString);
cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
string InsertFromTempTableColumnDefinition = string.Empty;
string InsertIntoFromTempTableColumnDefinition = string.Empty;
foreach (ColumnDefinition column in sameColumns.Values)
{
if (InsertFromTempTableColumnDefinition != string.Empty)
{
InsertFromTempTableColumnDefinition += ", ";
}
if (InsertIntoFromTempTableColumnDefinition != string.Empty)
{
InsertIntoFromTempTableColumnDefinition += ", ";
}
if (renameColumns.ContainsKey(column.Name))
InsertIntoFromTempTableColumnDefinition += renameColumns[column.Name];
else
InsertIntoFromTempTableColumnDefinition += column.Name;
InsertFromTempTableColumnDefinition += column.Name;
}
query = "INSERT INTO " + table + " (" + InsertIntoFromTempTableColumnDefinition + ") SELECT " +
InsertFromTempTableColumnDefinition + " from " + table + "__temp;";
cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
query = "drop table " + table + "__temp";
cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions)
{
var cmd = new SQLiteCommand
{
CommandText =
string.Format("insert into {0} select * from {1}", destinationTableName,
sourceTableName)
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
public override void UpdateTable(string table, ColumnDefinition[] columns,
Dictionary<string, string> renameColumns)
{
if (TableExists(table))
{
throw new DataManagerException("Trying to create a table with name of one that already exists.");
}
string columnDefinition = string.Empty;
var primaryColumns = (from cd in columns where cd.IsPrimary select cd);
bool multiplePrimary = primaryColumns.Count() > 1;
foreach (ColumnDefinition column in columns)
{
if (columnDefinition != string.Empty)
{
columnDefinition += ", ";
}
columnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type) +
((column.IsPrimary && !multiplePrimary) ? " PRIMARY KEY" : string.Empty);
}
string multiplePrimaryString = string.Empty;
if (multiplePrimary)
{
string listOfPrimaryNamesString = string.Empty;
foreach (ColumnDefinition column in primaryColumns)
{
if (listOfPrimaryNamesString != string.Empty)
{
listOfPrimaryNamesString += ", ";
}
listOfPrimaryNamesString += column.Name;
}
multiplePrimaryString = string.Format(", PRIMARY KEY ({0}) ", listOfPrimaryNamesString);
}
string query = string.Format("create table " + table + " ( {0} {1}) ", columnDefinition,
multiplePrimaryString);
SqlConnection dbcon = GetLockedConnection();
SqlCommand dbcommand = dbcon.CreateCommand();
dbcommand.CommandText = query;
dbcommand.ExecuteNonQuery();
CloseDatabase(dbcon);
}
private void RestoreTempTableToReal(IDataConnector genericData, string tablename, ColumnDefinition[] columnDefinitions)
{
genericData.CopyTableToTable(GetTempTableNameFromTableName(GetTempTableNameFromTableName(tablename)), tablename, columnDefinitions);
}
public override void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indices)
{
if (TableExists(table))
{
throw new DataManagerException("Trying to create a table with name of one that already exists.");
}
IndexDefinition primary = null;
foreach (IndexDefinition index in indices)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
List<string> columnDefinition = new List<string>();
bool has_auto_increment = false;
foreach (ColumnDefinition column in columns)
{
if (column.Type.auto_increment)
{
has_auto_increment = true;
}
columnDefinition.Add(column.Name + " " + GetColumnTypeStringSymbol(column.Type));
}
if (!has_auto_increment && primary != null && primary.Fields.Length > 0)
{
columnDefinition.Add("PRIMARY KEY (" + string.Join(", ", primary.Fields) + ")");
}
var cmd = new SQLiteCommand {
CommandText = string.Format("create table " + table + " ({0})", string.Join(", ", columnDefinition.ToArray()))
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
if (indices.Length >= 1 && (primary == null || indices.Length >= 2))
{
columnDefinition = new List<string>(primary != null ? indices.Length : indices.Length - 1); // reusing existing variable for laziness
uint i = 0;
foreach (IndexDefinition index in indices)
{
if (index.Type == IndexType.Primary || index.Fields.Length < 1)
{
continue;
}
i++;
columnDefinition.Add("CREATE " + (index.Type == IndexType.Unique ? "UNIQUE " : string.Empty) + "INDEX idx_" + table + "_" + i.ToString() + " ON " + table + "(" + string.Join(", ", index.Fields) + ")");
}
foreach (string query in columnDefinition)
{
cmd = new SQLiteCommand
{
CommandText = query
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
}
}
protected void AddSchema(string table, ColumnDefinition[] definitions, IndexDefinition[] indexes)
{
schema.Add(new SchemaDefinition(table, definitions, indexes));
}
protected void AddSchema(string table, ColumnDefinition[] definitions)
{
AddSchema(table, definitions, new IndexDefinition[0]);
}
public override void UpdateTable(string table, ColumnDefinition[] columns)
{
if (!TableExists(table))
{
throw new DataManagerException("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> sameColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in oldColumns)
{
if (columns.Contains(column))
{
sameColumns.Add(column.Name, column);
}
}
string columnDefinition = string.Empty;
/*var primaryColumns = (from cd in columns where cd.IsPrimary == true select cd);
bool multiplePrimary = primaryColumns.Count() > 1;*/
string renamedTempTableColumnDefinition = string.Empty;
string renamedTempTableColumn = string.Empty;
foreach (ColumnDefinition column in oldColumns)
{
if (renamedTempTableColumnDefinition != string.Empty)
{
renamedTempTableColumnDefinition += ", ";
renamedTempTableColumn += ", ";
}
renamedTempTableColumn += column.Name;
renamedTempTableColumnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type);
}
string query = "CREATE TABLE " + table + "__temp(" + renamedTempTableColumnDefinition + ");";
var cmd = new SqliteCommand();
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
query = "INSERT INTO " + table + "__temp SELECT " + renamedTempTableColumn + " from " + table + ";";
cmd = new SqliteCommand();
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
query = "drop table " + table;
cmd = new SqliteCommand();
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
string newTableColumnDefinition = string.Empty;
List<ColumnDefinition> primaryColumns = new List<ColumnDefinition>();
foreach (ColumnDefinition column in columns)
{
if (column.IsPrimary) primaryColumns.Add(column);
}
bool multiplePrimary = primaryColumns.Count > 1;
foreach (ColumnDefinition column in columns)
{
if (newTableColumnDefinition != string.Empty)
{
newTableColumnDefinition += ", ";
}
newTableColumnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type) + ((column.IsPrimary && !multiplePrimary) ? " PRIMARY KEY" : string.Empty);
}
string multiplePrimaryString = string.Empty;
if (multiplePrimary)
{
string listOfPrimaryNamesString = string.Empty;
foreach (ColumnDefinition column in primaryColumns)
{
if (listOfPrimaryNamesString != string.Empty)
{
listOfPrimaryNamesString += ", ";
}
listOfPrimaryNamesString += column.Name;
}
multiplePrimaryString = string.Format(", PRIMARY KEY ({0}) ", listOfPrimaryNamesString);
}
query = string.Format("create table " + table + " ( {0} {1}) ", newTableColumnDefinition, multiplePrimaryString);
cmd = new SqliteCommand();
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
string InsertFromTempTableColumnDefinition = string.Empty;
foreach (ColumnDefinition column in sameColumns.Values)
{
if (InsertFromTempTableColumnDefinition != string.Empty)
{
InsertFromTempTableColumnDefinition += ", ";
}
InsertFromTempTableColumnDefinition += column.Name;
}
query = "INSERT INTO " + table + " (" + InsertFromTempTableColumnDefinition + ") SELECT " + InsertFromTempTableColumnDefinition + " from " + table + "__temp;";
cmd = new SqliteCommand();
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
query = "drop table " + table + "__temp";
cmd = new SqliteCommand();
cmd.CommandText = query;
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
public override void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indices, Dictionary<string, string> renameColumns)
{
table = table.ToLower();
if (!TableExists(table))
{
throw new DataManagerException("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> removedColumns = new Dictionary<string, ColumnDefinition>();
Dictionary<string, ColumnDefinition> modifiedColumns = new Dictionary<string, ColumnDefinition>();
#if (!ISWIN)
Dictionary<string, ColumnDefinition> addedColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in columns)
{
if (!oldColumns.Contains(column)) addedColumns.Add(column.Name.ToLower(), column);
}
foreach (ColumnDefinition column in oldColumns)
{
if (!columns.Contains(column))
{
if (addedColumns.ContainsKey(column.Name.ToLower()))
{
if (column.Name.ToLower() != addedColumns[column.Name.ToLower()].Name.ToLower() || column.Type != addedColumns[column.Name.ToLower()].Type)
{
modifiedColumns.Add(column.Name.ToLower(), addedColumns[column.Name.ToLower()]);
}
addedColumns.Remove(column.Name.ToLower());
}
else
{
removedColumns.Add(column.Name.ToLower(), column);
}
}
}
#else
Dictionary<string, ColumnDefinition> addedColumns = columns.Where(column => !oldColumns.Contains(column)).ToDictionary(column => column.Name.ToLower());
foreach (ColumnDefinition column in oldColumns.Where(column => !columns.Contains(column)))
{
if (addedColumns.ContainsKey(column.Name.ToLower()))
{
if (column.Name.ToLower() != addedColumns[column.Name.ToLower()].Name.ToLower() || column.Type != addedColumns[column.Name.ToLower()].Type)
{
modifiedColumns.Add(column.Name.ToLower(), addedColumns[column.Name.ToLower()]);
}
addedColumns.Remove(column.Name.ToLower());
}
else{
removedColumns.Add(column.Name.ToLower(), column);
}
}
#endif
try
{
#if (!ISWIN)
foreach (ColumnDefinition column in addedColumns.Values)
{
string addedColumnsQuery = "add `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) + " ";
string query = string.Format("alter table " + table + " " + addedColumnsQuery);
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (ColumnDefinition column in modifiedColumns.Values)
{
string modifiedColumnsQuery = "modify column `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) + " ";
string query = string.Format("alter table " + table + " " + modifiedColumnsQuery);
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (ColumnDefinition column in removedColumns.Values)
{
string droppedColumnsQuery = "drop `" + column.Name + "` ";
string query = string.Format("alter table " + table + " " + droppedColumnsQuery);
ExecuteNonQuery(query, new Dictionary<string, object>());
}
#else
foreach (string query in addedColumns.Values.Select(column => "add `" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type) +
" ").Select(addedColumnsQuery => string.Format("alter table " + table + " " + addedColumnsQuery)))
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (string query in modifiedColumns.Values.Select(column => "modify column `" + column.Name + "` " +
GetColumnTypeStringSymbol(column.Type) + " ").Select(modifiedColumnsQuery => string.Format("alter table " + table + " " + modifiedColumnsQuery)))
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
foreach (string query in removedColumns.Values.Select(column => "drop `" + column.Name + "` ").Select(droppedColumnsQuery => string.Format("alter table " + table + " " + droppedColumnsQuery)))
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
#endif
}
catch (Exception e)
{
MainConsole.Instance.Error("[MySQLDataLoader] UpdateTable", e);
}
Dictionary<string, IndexDefinition> oldIndicesDict = ExtractIndicesFromTable(table);
List<string> removeIndices = new List<string>();
List<string> oldIndexNames = new List<string>(oldIndicesDict.Count);
List<IndexDefinition> oldIndices = new List<IndexDefinition>(oldIndicesDict.Count);
List<IndexDefinition> newIndices = new List<IndexDefinition>();
foreach (KeyValuePair<string, IndexDefinition> oldIndex in oldIndicesDict)
{
oldIndexNames.Add(oldIndex.Key);
oldIndices.Add(oldIndex.Value);
}
int i=0;
foreach(IndexDefinition oldIndex in oldIndices){
bool found = false;
foreach (IndexDefinition newIndex in indices)
{
if (oldIndex.Equals(newIndex))
{
found = true;
break;
}
}
if (!found)
{
removeIndices.Add(oldIndexNames[i]);
}
++i;
}
foreach (IndexDefinition newIndex in indices)
{
bool found = false;
foreach (IndexDefinition oldIndex in oldIndices)
{
if (oldIndex.Equals(newIndex))
{
found = true;
break;
}
}
if (!found)
{
newIndices.Add(newIndex);
}
}
foreach (string oldIndex in removeIndices)
{
ExecuteNonQuery(string.Format("ALTER TABLE `{0}` DROP INDEX `{1}`", table, oldIndex), new Dictionary<string, object>());
}
foreach (IndexDefinition newIndex in newIndices)
{
ExecuteNonQuery(string.Format("ALTER TABLE `{0}` ADD {1} (`{2}`)", table, newIndex.Type == IndexType.Primary ? "PRIMARY KEY" : (newIndex.Type == IndexType.Unique ? "UNIQUE" : "INDEX"), string.Join("`, `", newIndex.Fields)), new Dictionary<string,object>());
}
}
public override void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indices)
{
table = table.ToLower();
if (TableExists(table))
{
throw new DataManagerException("Trying to create a table with name of one that already exists.");
}
IndexDefinition primary = null;
foreach (IndexDefinition index in indices)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
List<string> columnDefinition = new List<string>();
foreach (ColumnDefinition column in columns)
{
columnDefinition.Add("`" + column.Name + "` " + GetColumnTypeStringSymbol(column.Type));
}
if (primary != null && primary.Fields.Length > 0)
{
columnDefinition.Add("PRIMARY KEY (`" + string.Join("`, `", primary.Fields) + "`)");
}
List<string> indicesQuery = new List<string>(indices.Length);
foreach (IndexDefinition index in indices)
{
string type = "KEY";
switch (index.Type)
{
case IndexType.Primary:
continue;
case IndexType.Unique:
type = "UNIQUE";
break;
case IndexType.Index:
default:
type = "KEY";
break;
}
indicesQuery.Add(string.Format("{0}( {1} )", type, "`" + string.Join("`, `", index.Fields) + "`"));
}
string query = string.Format("create table " + table + " ( {0} {1}) ", string.Join(", ", columnDefinition.ToArray()), indicesQuery.Count > 0 ? ", " + string.Join(", ", indicesQuery.ToArray()) : string.Empty);
try
{
ExecuteNonQuery(query, new Dictionary<string, object>());
}
catch (Exception e)
{
MainConsole.Instance.Error("[MySQLDataLoader] CreateTable", e);
}
}
public override void UpdateTable(string table, ColumnDefinition[] columns)
{
table = table.ToLower();
if (!TableExists(table))
{
throw new DataManagerException("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> addedColumns = new Dictionary<string, ColumnDefinition>();
Dictionary<string, ColumnDefinition> removedColumns = new Dictionary<string, ColumnDefinition>();
Dictionary<string, ColumnDefinition> modifiedColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in columns)
{
if (!oldColumns.Contains(column))
{
addedColumns.Add(column.Name, column);
}
}
foreach (ColumnDefinition column in oldColumns)
{
if (!columns.Contains(column))
{
if (addedColumns.ContainsKey(column.Name))
{
modifiedColumns.Add(column.Name, addedColumns[column.Name]);
addedColumns.Remove(column.Name);
}
else
removedColumns.Add(column.Name, column);
}
}
string columnDefinition = string.Empty;
/*var primaryColumns = (from cd in columns where cd.IsPrimary == true select cd);
bool multiplePrimary = primaryColumns.Count() > 1;*/
string addedColumnsQuery = "";
string modifiedColumnsQuery = "";
string droppedColumnsQuery = "";
MySqlConnection dbcon = GetLockedConnection();
foreach (ColumnDefinition column in addedColumns.Values)
{
addedColumnsQuery = "add " + column.Name + " " + GetColumnTypeStringSymbol(column.Type) + " ";
string query = string.Format("alter table " + table + " " + addedColumnsQuery);
MySqlCommand dbcommand = dbcon.CreateCommand();
dbcommand.CommandText = query;
try
{
dbcommand.ExecuteNonQuery();
}
catch
{
}
}
foreach (ColumnDefinition column in modifiedColumns.Values)
{
modifiedColumnsQuery = "modify column " + column.Name + " " + GetColumnTypeStringSymbol(column.Type) + " ";
string query = string.Format("alter table " + table + " " + modifiedColumnsQuery);
MySqlCommand dbcommand = dbcon.CreateCommand();
dbcommand.CommandText = query;
try
{
dbcommand.ExecuteNonQuery();
}
catch
{
}
}
foreach (ColumnDefinition column in removedColumns.Values)
{
droppedColumnsQuery = "drop " + column.Name + " ";
string query = string.Format("alter table " + table + " " + droppedColumnsQuery);
MySqlCommand dbcommand = dbcon.CreateCommand();
dbcommand.CommandText = query;
try
{
dbcommand.ExecuteNonQuery();
}
catch
{
}
}
CloseDatabase(dbcon);
}
public override void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indices, Dictionary<string, string> renameColumns)
{
if (TableExists(table))
{
throw new DataManagerException("Trying to create a table with name of one that already exists.");
}
string columnDefinition = string.Empty;
foreach (ColumnDefinition column in columns)
{
if (columnDefinition != string.Empty)
{
columnDefinition += ", ";
}
columnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type);
}
string query = string.Format("create table " + table + " ( {0} {1}) ", columnDefinition, string.Empty);
SqlConnection dbcon = GetLockedConnection();
SqlCommand dbcommand = dbcon.CreateCommand();
dbcommand.CommandText = query;
dbcommand.ExecuteNonQuery();
CloseDatabase(dbcon);
}
private void CopyTableToTempVersion(IDataConnector genericData, string tablename, ColumnDefinition[] columnDefinitions)
{
genericData.CopyTableToTable(tablename, GetTempTableNameFromTableName(tablename), columnDefinitions);
}
public override void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indices, Dictionary<string, string> renameColumns)
{
if (!TableExists(table))
{
throw new DataManagerException("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> sameColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in oldColumns)
{
#if (!ISWIN)
foreach (ColumnDefinition innercolumn in columns)
{
if (innercolumn.Name.ToLower() == column.Name.ToLower() || renameColumns.ContainsKey(column.Name) && renameColumns[column.Name].ToLower() == innercolumn.Name.ToLower())
{
sameColumns.Add(column.Name, column);
break;
}
}
#else
if (columns.Any(innercolumn => innercolumn.Name.ToLower() == column.Name.ToLower() ||
renameColumns.ContainsKey(column.Name) &&
renameColumns[column.Name].ToLower() == innercolumn.Name.ToLower()))
{
sameColumns.Add(column.Name, column);
}
#endif
}
string renamedTempTableColumnDefinition = string.Empty;
string renamedTempTableColumn = string.Empty;
foreach (ColumnDefinition column in oldColumns)
{
if (renamedTempTableColumnDefinition != string.Empty)
{
renamedTempTableColumnDefinition += ", ";
renamedTempTableColumn += ", ";
}
renamedTempTableColumn += column.Name;
renamedTempTableColumnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type);
}
var cmd = new SQLiteCommand {
CommandText = "CREATE TABLE " + table + "__temp(" + renamedTempTableColumnDefinition + ");"
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
cmd = new SQLiteCommand {
CommandText = "INSERT INTO " + table + "__temp SELECT " + renamedTempTableColumn + " from " + table + ";"
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
cmd = new SQLiteCommand {
CommandText = "drop table " + table
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
List<string> newTableColumnDefinition = new List<string>(columns.Length);
IndexDefinition primary = null;
foreach (IndexDefinition index in indices)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
bool has_auto_increment = false;
foreach (ColumnDefinition column in columns)
{
if (column.Type.auto_increment)
{
has_auto_increment = true;
}
newTableColumnDefinition.Add(column.Name + " " + GetColumnTypeStringSymbol(column.Type));
}
if (!has_auto_increment && primary != null && primary.Fields.Length > 0){
newTableColumnDefinition.Add("PRIMARY KEY (" + string.Join(", ", primary.Fields) + ")");
}
cmd = new SQLiteCommand {
CommandText = string.Format("create table " + table + " ({0}) ", string.Join(", ", newTableColumnDefinition.ToArray()))
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
if (indices.Length >= 1 && (primary == null || indices.Length >= 2))
{
newTableColumnDefinition = new List<string>(primary != null ? indices.Length : indices.Length - 1); // reusing existing variable for laziness
uint i = 0;
foreach (IndexDefinition index in indices)
{
if (index.Type == IndexType.Primary || index.Fields.Length < 1)
{
continue;
}
i++;
newTableColumnDefinition.Add("CREATE " + (index.Type == IndexType.Unique ? "UNIQUE " : string.Empty) + "INDEX idx_" + table + "_" + i.ToString() + " ON " + table + "(" + string.Join(", ", index.Fields) + ")");
}
foreach (string query in newTableColumnDefinition)
{
cmd = new SQLiteCommand
{
CommandText = query
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
}
string InsertFromTempTableColumnDefinition = string.Empty;
string InsertIntoFromTempTableColumnDefinition = string.Empty;
foreach (ColumnDefinition column in sameColumns.Values)
{
if (InsertFromTempTableColumnDefinition != string.Empty)
{
InsertFromTempTableColumnDefinition += ", ";
}
if (InsertIntoFromTempTableColumnDefinition != string.Empty)
{
InsertIntoFromTempTableColumnDefinition += ", ";
}
if (renameColumns.ContainsKey(column.Name))
InsertIntoFromTempTableColumnDefinition += renameColumns[column.Name];
else
InsertIntoFromTempTableColumnDefinition += column.Name;
InsertFromTempTableColumnDefinition += column.Name;
}
cmd = new SQLiteCommand {
CommandText = "INSERT INTO " + table + " (" + InsertIntoFromTempTableColumnDefinition + ") SELECT " + InsertFromTempTableColumnDefinition + " from " + table + "__temp;"
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
cmd = new SQLiteCommand {
CommandText = "drop table " + table + "__temp"
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
protected void AddSchema(string table, ColumnDefinition[] definitions)
{
schema.Add(new Rec<string, ColumnDefinition[]>(table, definitions));
}
public override void CreateTable(string table, ColumnDefinition[] columns)
{
table = table.ToLower ();
if (TableExists (table))
{
throw new DataManagerException ("Trying to create a table with name of one that already exists.");
}
string columnDefinition = string.Empty;
var primaryColumns = (from cd in columns where cd.IsPrimary select cd);
bool multiplePrimary = primaryColumns.Count () > 1;
foreach (ColumnDefinition column in columns)
{
if (columnDefinition != string.Empty)
{
columnDefinition += ", ";
}
columnDefinition += "`" + column.Name + "` " + GetColumnTypeStringSymbol (column.Type) + ((column.IsPrimary && !multiplePrimary) ? " PRIMARY KEY" : string.Empty);
}
string multiplePrimaryString = string.Empty;
if (multiplePrimary)
{
string listOfPrimaryNamesString = string.Empty;
foreach (ColumnDefinition column in primaryColumns)
{
if (listOfPrimaryNamesString != string.Empty)
{
listOfPrimaryNamesString += ", ";
}
listOfPrimaryNamesString += "`" + column.Name + "`";
}
multiplePrimaryString = string.Format (", PRIMARY KEY ({0}) ", listOfPrimaryNamesString);
}
string query = string.Format ("create table " + table + " ( {0} {1}) ", columnDefinition, multiplePrimaryString);
try
{
ExecuteNonQuery (query, new Dictionary<string, object> ());
}
catch(Exception e)
{
m_log.Debug("[MySQLDataLoader] CreateTable", e);
}
}
protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions)
{
SqlConnection dbcon = GetLockedConnection();
SqlCommand dbcommand = dbcon.CreateCommand();
dbcommand.CommandText = string.Format("insert into {0} select * from {1}", destinationTableName,
sourceTableName);
dbcommand.ExecuteNonQuery();
CloseDatabase(dbcon);
}
public override void UpdateTable(string table, ColumnDefinition[] columns)
{
table = table.ToLower ();
if (!TableExists (table))
{
throw new DataManagerException ("Trying to update a table with name of one that does not exist.");
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable (table);
Dictionary<string, ColumnDefinition> removedColumns = new Dictionary<string, ColumnDefinition> ();
Dictionary<string, ColumnDefinition> modifiedColumns = new Dictionary<string, ColumnDefinition> ();
Dictionary<string, ColumnDefinition> addedColumns = columns.Where (column => !oldColumns.Contains (column)).ToDictionary (column => column.Name);
foreach (ColumnDefinition column in oldColumns.Where (column => !columns.Contains (column)))
{
if (addedColumns.ContainsKey (column.Name))
{
modifiedColumns.Add (column.Name, addedColumns[column.Name]);
addedColumns.Remove (column.Name);
}
else
removedColumns.Add (column.Name, column);
}
try
{
foreach (ColumnDefinition column in addedColumns.Values)
{
string addedColumnsQuery = "add `" + column.Name + "` " + GetColumnTypeStringSymbol (column.Type) + " ";
string query = string.Format ("alter table " + table + " " + addedColumnsQuery);
ExecuteNonQuery (query, new Dictionary<string, object> ());
}
foreach (ColumnDefinition column in modifiedColumns.Values)
{
string modifiedColumnsQuery = "modify column `" + column.Name + "` " + GetColumnTypeStringSymbol (column.Type) + " ";
string query = string.Format ("alter table " + table + " " + modifiedColumnsQuery);
ExecuteNonQuery (query, new Dictionary<string, object> ());
}
foreach (ColumnDefinition column in removedColumns.Values)
{
string droppedColumnsQuery = "drop `" + column.Name + "` ";
string query = string.Format ("alter table " + table + " " + droppedColumnsQuery);
ExecuteNonQuery (query, new Dictionary<string, object> ());
}
}
catch(Exception e)
{
m_log.Debug("[MySQLDataLoader] UpdateTable", e);
}
}
public override void CreateTable(string table, ColumnDefinition[] columns)
{
if (TableExists(table))
{
throw new DataManagerException("Trying to create a table with name of one that already exists.");
}
string columnDefinition = string.Empty;
var primaryColumns = (from cd in columns where cd.IsPrimary select cd);
bool multiplePrimary = primaryColumns.Count() > 1;
foreach (ColumnDefinition column in columns)
{
if (columnDefinition != string.Empty)
{
columnDefinition += ", ";
}
columnDefinition += column.Name + " " + GetColumnTypeStringSymbol(column.Type) +
((column.IsPrimary && !multiplePrimary) ? " PRIMARY KEY" : string.Empty);
}
string multiplePrimaryString = string.Empty;
if (multiplePrimary)
{
string listOfPrimaryNamesString = string.Empty;
foreach (ColumnDefinition column in primaryColumns)
{
if (listOfPrimaryNamesString != string.Empty)
{
listOfPrimaryNamesString += ", ";
}
listOfPrimaryNamesString += column.Name;
}
multiplePrimaryString = string.Format(", PRIMARY KEY ({0}) ", listOfPrimaryNamesString);
}
string query = string.Format("create table " + table + " ( {0} {1}) ", columnDefinition,
multiplePrimaryString);
var cmd = new SQLiteCommand {CommandText = query};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName, ColumnDefinition[] columnDefinitions)
{
sourceTableName = sourceTableName.ToLower ();
destinationTableName = destinationTableName.ToLower ();
try
{
ExecuteNonQuery (string.Format ("insert into {0} select * from {1}", destinationTableName,
sourceTableName), new Dictionary<string, object> ());
}
catch(Exception e)
{
m_log.Debug("[MySQLDataLoader] CopyAllDataBetweenMatchingTables", e);
}
}
public bool VerifyTableExists(string tableName, ColumnDefinition[] columnDefinitions, IndexDefinition[] indexDefinitions)
{
if (!TableExists(tableName))
{
MainConsole.Instance.Warn("[DataMigrator]: Issue finding table " + tableName + " when verifing tables exist!");
return false;
}
List<ColumnDefinition> extractedColumns = ExtractColumnsFromTable(tableName);
List<ColumnDefinition> newColumns = new List<ColumnDefinition>(columnDefinitions);
foreach (ColumnDefinition columnDefinition in columnDefinitions)
{
if (!extractedColumns.Contains(columnDefinition))
{
ColumnDefinition thisDef = null;
foreach (ColumnDefinition extractedDefinition in extractedColumns)
{
if (extractedDefinition.Name.ToLower() == columnDefinition.Name.ToLower())
{
thisDef = extractedDefinition;
break;
}
}
//Check to see whether the two tables have the same type, but under different names
if (thisDef != null)
{
if (GetColumnTypeStringSymbol(thisDef.Type) == GetColumnTypeStringSymbol(columnDefinition.Type))
{
continue; //They are the same type, let them go on through
}
else
{
MainConsole.Instance.Warn("Mismatched Column Type on " + tableName + "." + thisDef.Name + ": " + GetColumnTypeStringSymbol(thisDef.Type) + ", " + GetColumnTypeStringSymbol(columnDefinition.Type));
}
}
MainConsole.Instance.Warn("[DataMigrator]: Issue verifing table " + tableName + " column " + columnDefinition.Name + " when verifing tables exist, problem with new column definitions");
return false;
}
}
foreach (ColumnDefinition columnDefinition in extractedColumns)
{
if (!newColumns.Contains(columnDefinition))
{
#if (!ISWIN)
ColumnDefinition thisDef = null;
foreach (ColumnDefinition extractedDefinition in newColumns)
{
if (extractedDefinition.Name.ToLower() == columnDefinition.Name.ToLower())
{
thisDef = extractedDefinition;
break;
}
}
#else
ColumnDefinition thisDef = newColumns.FirstOrDefault(extractedDefinition => extractedDefinition.Name.ToLower() == columnDefinition.Name.ToLower());
#endif
//Check to see whether the two tables have the same type, but under different names
if (thisDef != null)
{
if (GetColumnTypeStringSymbol(thisDef.Type) == GetColumnTypeStringSymbol(columnDefinition.Type))
{
continue; //They are the same type, let them go on through
}
}
MainConsole.Instance.Warn("[DataMigrator]: Issue verifing table " + tableName + " column " + columnDefinition.Name + " when verifing tables exist, problem with old column definitions");
return false;
}
}
Dictionary<string, IndexDefinition> ei = ExtractIndicesFromTable(tableName);
List<IndexDefinition> extractedIndices = new List<IndexDefinition>(ei.Count);
foreach(KeyValuePair<string, IndexDefinition> kvp in ei){
extractedIndices.Add(kvp.Value);
}
List<IndexDefinition> newIndices = new List<IndexDefinition>(indexDefinitions);
foreach (IndexDefinition indexDefinition in indexDefinitions)
{
if (!extractedIndices.Contains(indexDefinition))
{
IndexDefinition thisDef = null;
foreach (IndexDefinition extractedDefinition in extractedIndices)
{
if (extractedDefinition.Equals(indexDefinition))
{
thisDef = extractedDefinition;
break;
}
}
if (thisDef == null)
{
MainConsole.Instance.Warn("[DataMigrator]: Issue verifing table " + tableName + " index " + indexDefinition.Type.ToString() + " (" + string.Join(", ", indexDefinition.Fields) + ") when verifing tables exist");
return false;
}
}
}
foreach (IndexDefinition indexDefinition in extractedIndices)
{
if (!newIndices.Contains(indexDefinition))
{
IndexDefinition thisDef = null;
foreach (IndexDefinition extractedDefinition in newIndices)
{
if (extractedDefinition.Equals(indexDefinition))
{
thisDef = extractedDefinition;
break;
}
}
if (thisDef == null)
{
MainConsole.Instance.Warn("[DataMigrator]: Issue verifing table " + tableName + " index " + indexDefinition.Type.ToString() + " (" + string.Join(", ", indexDefinition.Fields) + ") when verifing tables exist");
return false;
}
}
}
return true;
}
public bool VerifyTableExists(string tableName, ColumnDefinition[] columnDefinitions)
{
if (!TableExists(tableName))
{
OpenSim.Framework.Console.MainConsole.Instance.Output("[DataMigrator]: Issue finding table " + tableName + " when verifing tables exist!", "Warn");
return false;
}
List<ColumnDefinition> extractedColumns = ExtractColumnsFromTable(tableName);
foreach (ColumnDefinition columnDefinition in columnDefinitions)
{
if (!extractedColumns.Contains(columnDefinition))
{
ColumnDefinition thisDef = null;
//Check to see whether the two tables have the same type, but under different names
foreach (ColumnDefinition extractedDefinition in extractedColumns)
{
if (extractedDefinition.Name == columnDefinition.Name)
{
thisDef = extractedDefinition;
break;
}
}
if (thisDef != null)
{
if (GetColumnTypeStringSymbol(thisDef.Type) == GetColumnTypeStringSymbol(columnDefinition.Type))
continue; //They are the same type, let them go on through
}
OpenSim.Framework.Console.MainConsole.Instance.Output("[DataMigrator]: Issue verifing table " + tableName + " column " + columnDefinition.Name + " when verifing tables exist!", "Warn");
return false;
}
}
return true;
}
public abstract void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions, Dictionary<string, string> renameColumns);
public abstract void UpdateTable(string table, ColumnDefinition[] columns);
public abstract void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions);
public bool VerifyTableExists(string tableName, ColumnDefinition[] columnDefinitions)
{
if (!TableExists(tableName))
{
return false;
}
List<ColumnDefinition> extractedColumns = ExtractColumnsFromTable(tableName);
foreach (ColumnDefinition columnDefinition in columnDefinitions)
{
if (!extractedColumns.Contains(columnDefinition))
{
return false;
}
}
return true;
}