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