// IMPORTANT: additional steps must be done before and after calling this method
// before you must:
// 1) set PRAGMA foreign_keys=off
// 2) begin a transaction
// after you must:
// 1) commit the transaction
// 2) set PRAGMA foreign_keys back to what it was beofore
// see https://www.sqlite.org/lang_altertable.html#otheralter for more details on the procedure for rebuilding a table.
public static void RebuildTable(CruiseDatastore db, ITableDefinition tableDef, IEnumerable <KeyValuePair <string, string> > customFieldMaps = null)
{
var tableName = tableDef.TableName;
var tempTableName = "new_" + tableName;
var createNewTable = tableDef.GetCreateTable(tempTableName);
db.Execute(createNewTable);
var fieldIntersectArray = ListFieldsIntersect(db, tableName, tempTableName);
var fieldListFrom = new List <string>(fieldIntersectArray);
var fieldListTo = new List <string>(fieldIntersectArray);
if (customFieldMaps != null)
{
foreach (var map in customFieldMaps)
{
var i = fieldListTo.FindIndex(x => string.Compare(x, map.Key, true) is 0);
if (i > 0)
{
fieldListTo[i] = map.Value;
}
else
{
fieldListTo.Add(map.Key);
fieldListFrom.Add(map.Value);
}
}
}
db.Execute($"INSERT INTO main.{tempTableName} ( {fieldListTo.Aggregate((a, b) => a + ", " + b)} ) SELECT {fieldListFrom.Aggregate((a, b) => a + ", " + b)} FROM main.{tableName};");
db.Execute($"DROP TABLE main.{tableName};");
db.Execute($"ALTER TABLE {tempTableName} RENAME TO {tableName}");
var createIndexes = tableDef.CreateIndexes;
if (createIndexes != null)
{
db.Execute(createIndexes);
}
var triggers = tableDef.CreateTriggers;
if (triggers != null)
{
foreach (var trigger in triggers)
{
db.Execute(trigger);
}
}
}
