/// <summary>
/// Add a column to the table named sourceTable at position columnNumber using the provided columnDefinition
/// </summary>
public void AddColumnToTable(string tableName, int columnNumber, ColumnDefinition columnDefinition)
{
using (SQLiteConnection connection = new SQLiteConnection(this.connectionString))
{
connection.Open();
List<string> existingColumnNames = this.GetColumnNamesAsList(connection, tableName);
if (existingColumnNames.Contains(columnDefinition.Name))
{
throw new ArgumentException(String.Format("Column '{0}' is already present in table '{1}'.", columnDefinition.Name, tableName), "columnDefinition");
}
// if columnNumber would result in the column being inserted at the end of the table, then use the more efficient method to do so.
if (columnNumber >= existingColumnNames.Count)
{
this.AddColumnToEndOfTable(tableName, columnDefinition);
return;
}
// addding a column in a specific location requires
// - creating a new schema
// - creating a new table from that schema
// - copying data to the new table
// - removing the old table
// - renaming the new table to the name of the old one
// clone current schema and insert new column
string newSchema = this.InsertColumnInSchema(connection, tableName, columnNumber, columnDefinition);
// copy the existing table to a new table with the new schema
string destTable = tableName + "NEW";
string sql = "CREATE TABLE " + destTable + " (" + newSchema + ")";
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
command.ExecuteNonQuery();
}
this.CopyAllValuesFromTable(connection, tableName, tableName, destTable);
// remove the existing table and rename the new table
this.DropTable(connection, tableName);
this.RenameTable(connection, destTable, tableName);
}
}
/// <summary>
/// Add a column definition into the provided schema at the given column location
/// </summary>
private string InsertColumnInSchema(SQLiteConnection connection, string tableName, int newColumnNumber, ColumnDefinition newColumn)
{
List<string> columnDefinitions = this.GetColumnDefinitions(connection, tableName);
columnDefinitions.Insert(newColumnNumber, newColumn.ToString());
return String.Join(", ", columnDefinitions);
}
// This method will create a column in a table of type TEXT, where it is added to its end
// It assumes that the value, if not empty, should be treated as the default value for that column
public void AddColumnToEndOfTable(string tableName, ColumnDefinition columnDefinition)
{
this.ExecuteNonQuery("ALTER TABLE " + tableName + " ADD COLUMN " + columnDefinition.ToString());
}