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 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) { 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) { 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 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); } }