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;
}
if (index.IndexSize == 0)
indicesQuery.Add(string.Format("{0}( {1} )", type, "`" + string.Join("`, `", index.Fields) + "`"));
else
indicesQuery.Add(string.Format("{0}( {1} )", type, "`" + string.Join("`, `", index.Fields) + "`"+"("+index.IndexSize+")"));
}
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.ErrorFormat("[MySQLDataLoader] CreateTable: {0}", e.ToString());
}
}
public override void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions)
{
if (TableExists(table))
{
MainConsole.Instance.WarnFormat("[SQLite]: Trying to create a table '{0}' that already exists.", table);
return;
}
IndexDefinition primary = null;
foreach (IndexDefinition index in indexDefinitions)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
List<string> columnDefinition = new List<string>();
bool has_auto_increment = false;
foreach (ColumnDefinition column in columns)
{
has_auto_increment |= column.Type.auto_increment;
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 ()))
};
try {
ExecuteNonQuery (cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create table (" + table + "), " + e);
}
CloseReaderCommand(cmd);
if (indexDefinitions.Length >= 1 && (primary == null || indexDefinitions.Length >= 2))
{
columnDefinition = new List<string>(primary != null ? indexDefinitions.Length : indexDefinitions.Length - 1);
// reusing existing variable for laziness
uint i = 0;
foreach (IndexDefinition index in indexDefinitions)
{
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 + " ON " + table + "(" +
string.Join(", ", index.Fields) + ")");
}
foreach (string query in columnDefinition)
{
cmd = new SqliteCommand
{
CommandText = query
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create index (" + query + "), " + e);
}
CloseReaderCommand(cmd);
}
}
}
public override void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions,
Dictionary<string, string> renameColumns)
{
if (!TableExists(table))
{
MainConsole.Instance.WarnFormat ("[SQLite]: Trying to update a table {0} that does not exist.", table);
return;
}
List<ColumnDefinition> oldColumns = ExtractColumnsFromTable(table);
Dictionary<string, ColumnDefinition> sameColumns = new Dictionary<string, ColumnDefinition>();
foreach (ColumnDefinition column in oldColumns)
{
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);
}
}
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 + ");"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create temporary table, " + e);
}
CloseReaderCommand(cmd);
cmd = new SqliteCommand
{
CommandText =
"INSERT INTO " + table + "__temp SELECT " + renamedTempTableColumn + " from " + table +
";"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Copy to tempory table, " + e);
}
CloseReaderCommand(cmd);
cmd = new SqliteCommand
{
CommandText = "drop table " + table
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Drop table, " + e);
}
CloseReaderCommand(cmd);
List<string> newTableColumnDefinition = new List<string>(columns.Length);
IndexDefinition primary = null;
foreach (IndexDefinition index in indexDefinitions)
{
if (index.Type == IndexType.Primary)
{
primary = index;
break;
}
}
bool has_auto_increment = false;
foreach (ColumnDefinition column in columns)
{
has_auto_increment |= column.Type.auto_increment;
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()))
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create new table, " + e);
}
CloseReaderCommand(cmd);
if (indexDefinitions.Length >= 1 && (primary == null || indexDefinitions.Length >= 2))
{
newTableColumnDefinition = new List<string>(primary != null ? indexDefinitions.Length : indexDefinitions.Length - 1);
// reusing existing variable for laziness
uint i = 0;
foreach (IndexDefinition index in indexDefinitions)
{
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 + " ON " + table + "(" +
string.Join(", ", index.Fields) + ")");
}
foreach (string query in newTableColumnDefinition)
{
cmd = new SqliteCommand
{
CommandText = query
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Create new index, " + e);
}
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;"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Copy to new table, " + e);
}
CloseReaderCommand(cmd);
cmd = new SqliteCommand
{
CommandText = "drop table " + table + "__temp"
};
try {
ExecuteNonQuery(cmd);
} catch (SqliteException e) {
MainConsole.Instance.Error ("[Sqlite]: Drop temporary table, " + e);
}
CloseReaderCommand(cmd);
}
protected abstract void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions,
IndexDefinition[] indexDefinitions);
protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions,
IndexDefinition[] indexDefinitions)
{
var cmd = new SqliteCommand
{
CommandText =
string.Format("insert into {0} select * from {1}", destinationTableName,
sourceTableName)
};
ExecuteNonQuery(cmd);
CloseReaderCommand(cmd);
}
protected void AddSchema(string table, ColumnDefinition[] definitions)
{
AddSchema(table, definitions, new IndexDefinition[0]);
}
public bool VerifyTableExists(string tableName, ColumnDefinition[] columnDefinitions,
IndexDefinition[] indexDefinitions)
{
if (!TableExists(tableName))
{
MainConsole.Instance.Warn("[DataMigrator]: Issue finding table " + tableName +
" when verifying 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 verifying table " + tableName + " column " +
columnDefinition.Name +
" when verifying tables exist, problem with new column definitions");
return false;
}
}
foreach (ColumnDefinition columnDefinition in extractedColumns)
{
if (!newColumns.Contains(columnDefinition))
{
ColumnDefinition thisDef =
newColumns.FirstOrDefault(
extractedDefinition => extractedDefinition.Name.ToLower() == columnDefinition.Name.ToLower());
//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 verifying table " + tableName + " column " +
columnDefinition.Name +
" when verifying 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 verifying table " + tableName + " index " +
indexDefinition.Type.ToString() + " (" +
string.Join(", ", indexDefinition.Fields) +
") when verifying 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 verifying table " + tableName + " index " +
indexDefinition.Type.ToString() + " (" +
string.Join(", ", indexDefinition.Fields) +
") when verifying tables exist");
return false;
}
}
}
return true;
}
public abstract void CreateTable(string table, ColumnDefinition[] columns, 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 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);
}
}
}
public void CopyTableToTable(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions, IndexDefinition[] indexDefinitions)
{
if (!TableExists(sourceTableName))
{
throw new MigrationOperationException("Cannot copy table to new name, source table does not exist: " +
sourceTableName);
}
if (TableExists(destinationTableName))
{
this.DropTable(destinationTableName);
if (TableExists(destinationTableName))
throw new MigrationOperationException(
"Cannot copy table to new name, table with same name already exists: " + destinationTableName);
}
if (!VerifyTableExists(sourceTableName, columnDefinitions, indexDefinitions))
{
throw new MigrationOperationException(
"Cannot copy table to new name, source table does not match columnDefinitions: " +
destinationTableName);
}
EnsureTableExists(destinationTableName, columnDefinitions, indexDefinitions, null);
CopyAllDataBetweenMatchingTables(sourceTableName, destinationTableName, columnDefinitions, indexDefinitions);
}
static void RestoreTempTableToReal(IDataConnector genericData, string tablename,
ColumnDefinition[] columnDefinitions, IndexDefinition[] indexDefinitions)
{
genericData.CopyTableToTable(GetTempTableNameFromTableName(GetTempTableNameFromTableName(tablename)),
tablename, columnDefinitions, indexDefinitions);
}
static void CopyTableToTempVersion(IDataConnector genericData, string tablename,
ColumnDefinition[] columnDefinitions, IndexDefinition[] indexDefinitions)
{
genericData.CopyTableToTable(tablename, GetTempTableNameFromTableName(tablename), columnDefinitions,
indexDefinitions);
}
protected void AddSchema(string table, ColumnDefinition[] definitions, IndexDefinition[] indexes)
{
Schema.Add(new SchemaDefinition(table, definitions, indexes));
}
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>();
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);
}
}
try
{
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>());
}
}
catch (Exception e)
{
MainConsole.Instance.ErrorFormat("[MySQLDataLoader] UpdateTable: {0}", 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 abstract void UpdateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions,
Dictionary<string, string> renameColumns);
protected override void CopyAllDataBetweenMatchingTables(string sourceTableName, string destinationTableName,
ColumnDefinition[] columnDefinitions,
IndexDefinition[] indexDefinitions)
{
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)
{
MainConsole.Instance.ErrorFormat("[MySQLDataLoader] CopyAllDataBetweenMatchingTables", e.ToString());
}
}
/// <summary>
/// Defines a schema with indices
/// </summary>
/// <param name="schemaName">Name of schema</param>
/// <param name="columns">Columns in schema</param>
/// <param name="indices">Indices in schema</param>
public SchemaDefinition(string schemaName, ColumnDefinition[] columns, IndexDefinition[] indices)
{
m_name = schemaName;
m_columns = columns;
m_indices = indices;
}