public SchemaBuilder AlterTable(string name, Action<AlterTableCommand> table)
{
var alterTable = new AlterTableCommand(String.Concat(_formatPrefix(_featurePrefix), name));
table(alterTable);
Run(alterTable);
return this;
}
public static void AddColumnFor <TColumn, TRecord>(this AlterTableCommand self, Expression <Func <TRecord, object> > expression, Action <AddColumnCommand> column = null)
{
#pragma warning disable CS0618 // We need enum support here
var property = ObjectExtensions.PropertyName(expression);
#pragma warning restore CS0618 // Type or member is obsolete
self.AddColumn <TColumn>(property, column);
}
public static void AddColumnForForeignKey <T>(this AlterTableCommand self, Action <AddColumnCommand> column = null, string columnNameOverride = null)
{
var recordType = typeof(T);
var columnName = columnNameOverride.HasValue() ? columnNameOverride : recordType.Name + "_Id";
self.AddColumn <int>(columnName, column);
}
public SchemaBuilder AlterTable(string name, Action<AlterTableCommand> table)
{
var alterTable = new AlterTableCommand(FormatTable(name));
table(alterTable);
Execute(_builder.CreateSql(alterTable));
return this;
}
//we need to do the work around. http://www.sqlite.org/faq.html#q11
public IEnumerable <string> CreateSqlStatements(AlterTableCommand command)
{
string fullTableName = command.Name;
string tempTableName = fullTableName + "_" + Guid.NewGuid().ToString("N");
yield return(StatementUtil.CreateTempTable(tempTableName, fullTableName));
//modify all the references to the old table to go to the new table
//drop the old table
yield return(StatementUtil.DropTable(fullTableName));
//create newTable and insert the data, add and drop indices
foreach (string statement in CreateNewTableCopyInDataAndCreateIndices(command, tempTableName))
{
yield return(statement);
}
//change them all back!
//drop the tempTable
yield return(StatementUtil.DropTable(tempTableName));
}
public void AlterTableAdapterIgnoresEmptyOrNullIndices()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256));";
var originalIndices = new string[0];
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
var input = new AlterTableCommand("TEST_TestTable");
input.DropColumn("name");
input.DropColumn("last");
input.CreateIndex("fun", "id");
var result = adapter.AlterTableStatements(input);
var expectedFinal = new[]
{
//this line expects the guid to be added
"CREATE TEMPORARY TABLE TEST_TestTable_ AS SELECT * FROM TEST_TestTable;",
"DROP TABLE TEST_TestTable;",
"CREATE TABLE TEST_TestTable (id INTEGER primary key autoincrement);",
//this line expects the guid to be added
"INSERT INTO TEST_TestTable (id) SELECT id FROM TEST_TestTable_;",
"CREATE INDEX fun ON TEST_TestTable (id);",
//this line expects the guid to be added
"DROP TABLE TEST_TestTable_;",
};
VerifyYourStatementsAreValid(expectedFinal, result);
}
private IEnumerable <string> CreateNewTableCopyInDataAndCreateIndices(AlterTableCommand command, string tempTable)
{
string fullTableName = command.Name;
//we'll use this to create the insert into later
string[] originalColumns = CreateTableNode.ColumnDefinitions.Select(i => i.ColumnName).ToArray();
CreateTableNode = IncorporateAlterationsInCreateNode(command, CreateTableNode);
var visitor = new TreeStringOutputVisitor();
yield return(CreateTableNode.Accept(visitor).ToString());
string[] finalSetOfColumns =
originalColumns.Where(i => command.TableCommands.OfType <DropColumnCommand>().All(j => j.ColumnName != i))
.ToArray();
// this can only be the ones in the ORIGINAL table that we want to copy, i.e. don't copy in deleted columns
yield return(StatementUtil.InsertInto(fullTableName, finalSetOfColumns, tempTable));
//let's figure out our final indexes
IncorporateAlterationsInIndexNodes(CreateIndexNodes, fullTableName, command.TableCommands, finalSetOfColumns);
var indexNodeVisitor = new TreeStringOutputVisitor();
foreach (CreateIndexNode indexNode in CreateIndexNodes)
{
yield return(indexNode.Accept(indexNodeVisitor).ToString());
}
}
/// <summary>
/// returns null if something if we don't have ones we can do natively
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public IEnumerable <string> CreateSqlStatements(AlterTableCommand command)
{
if (!command.TableCommands.All(i => i is AddColumnCommand || i is AddIndexCommand || i is DropIndexCommand))
{
// we have non Native Alterations to make
yield break;
}
var visitor = new TreeStringOutputVisitor();
foreach (var alterCommand in command.TableCommands)
{
if (alterCommand is AddColumnCommand)
{
var addColumn = alterCommand as AddColumnCommand;
yield return
(StatementUtil.AddColumn(command.Name,
addColumn.CreateColumnDefNode().Accept(visitor).ToString()));
}
else if (alterCommand is AddIndexCommand)
{
var addIndexCommand = alterCommand as AddIndexCommand;
yield return(StatementUtil.AddIndex(addIndexCommand.IndexName, addIndexCommand.TableName, addIndexCommand.ColumnNames));
}
else if (alterCommand is DropIndexCommand)
{
var dropIndexCommand = alterCommand as DropIndexCommand;
yield return(StatementUtil.DropIndex(dropIndexCommand.IndexName));
}
}
}
protected override IExistingTable CreateItem(string name)
{
AlterTableCommand alterTableCommand = new AlterTableCommand(_migrateCommand, name);
_migrateCommand.Add(alterTableCommand);
return(new ExistingTable(alterTableCommand));
}
public override IASTNode VisitAlterTable(SqlServerCommandParser.AlterTableContext context)
{
AlterTableCommand result = new AlterTableCommand((SimpleTableSegment)Visit(context.tableName()));
foreach (var alterDefinitionClauseContext in context.alterDefinitionClause())
{
foreach (var alterDefinitionSegment in ((CollectionValue <IAlterDefinitionSegment>)Visit(alterDefinitionClauseContext)).GetValue())
{
if (alterDefinitionSegment is AddColumnDefinitionSegment addColumnDefinitionSegment)
{
result.AddColumnDefinitions.Add(addColumnDefinitionSegment);
}
else if (alterDefinitionSegment is ModifyColumnDefinitionSegment modifyColumnDefinitionSegment)
{
result.ModifyColumnDefinitions.Add(modifyColumnDefinitionSegment);
}
else if (alterDefinitionSegment is DropColumnDefinitionSegment dropColumnDefinitionSegment)
{
result.DropColumnDefinitions.Add(dropColumnDefinitionSegment);
}
else if (alterDefinitionSegment is ConstraintDefinitionSegment constraintDefinitionSegment)
{
result.AddConstraintDefinitions.Add(constraintDefinitionSegment);
}
}
}
return(result);
}
public SchemaBuilder AlterTable(string name, Action <AlterTableCommand> table)
{
var alterTable = new AlterTableCommand(FormatTable(name));
table(alterTable);
Execute(_builder.CreateSql(alterTable));
return(this);
}
public static void CreateIndexForSingleColumn <TRecord>(this AlterTableCommand self, Expression <Func <TRecord, object> > expression, string indexNameOverride = null)
{
var property = ReflectOn <TRecord> .NameOf(expression);
var indexName = indexNameOverride.HasValue() ? indexNameOverride : CreateIndexKeyName <TRecord>(expression);
self.CreateIndex(indexName, new[] { property });
}
public SchemaBuilder AlterTable(string name, Action <AlterTableCommand> table)
{
var alterTable = new AlterTableCommand(String.Concat(_formatPrefix(_featurePrefix), name));
table(alterTable);
Run(alterTable);
return(this);
}
internal ExistingTable(AlterTableCommand command)
{
_command = command;
_columns = new ColumnCollection(command);
_indexes = new IndexesCollection(command);
_uniqueConstraints = new UniqueConstraintCollection(command);
_foreignKeyConstraints = new ForeignKeyCollection(command);
}
public override void Visit(AlterTableCommand command)
{
if (ExecuteCustomInterpreter(command))
{
return;
}
if (command.TableCommands.Count == 0)
{
return;
}
// drop columns
foreach (var dropColumn in command.TableCommands.OfType <DropColumnCommand>())
{
var builder = new StringBuilder();
Visit(builder, dropColumn);
RunPendingStatements();
}
// add columns
foreach (var addColumn in command.TableCommands.OfType <AddColumnCommand>())
{
var builder = new StringBuilder();
Visit(builder, addColumn);
RunPendingStatements();
}
// alter columns
foreach (var alterColumn in command.TableCommands.OfType <AlterColumnCommand>())
{
var builder = new StringBuilder();
Visit(builder, alterColumn);
RunPendingStatements();
}
// add index
foreach (var addIndex in command.TableCommands.OfType <AddIndexCommand>())
{
var builder = new StringBuilder();
Visit(builder, addIndex);
RunPendingStatements();
}
// drop index
foreach (var dropIndex in command.TableCommands.OfType <DropIndexCommand>())
{
var builder = new StringBuilder();
Visit(builder, dropIndex);
RunPendingStatements();
}
}
public static AlterTableCommand AddColumn <TModel, TProperty>(
this AlterTableCommand command,
Expression <Func <TModel, TProperty> > propertySelector,
Action <CreateColumnCommand> column = null,
bool isPrefixWithModelType = false)
{
var columnName = GetColumnName(propertySelector, isPrefixWithModelType);
var propertyType = GetPropertyType <TProperty>();
var dbType = SchemaUtils.ToDbType(propertyType);
command.AddColumn(columnName, dbType, column);
return(command);
}
public IEnumerable <string> AlterTableStatements(AlterTableCommand command)
{
var native = new NativeAlterTableProcessor();
var result = native.CreateSqlStatements(command);
if (!result.Any())
{
var simulated = new SimulatedAlterTableProcessor(CreateTableNode, CreateIndexNodes);
result = simulated.CreateSqlStatements(command);
}
return(result);
}
public void HandleAlterTables()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256), "
+ "CONSTRAINT TEST_FK FOREIGN KEY (id) REFERENCES FAKE_TABLE (id) DEFERRABLE INITIALLY DEFERRED,"
+ "CONSTRAINT KEEP_FK FOREIGN KEY (temp, id) REFERENCES FAKE_TABLE (temp, id) DEFERRABLE INITIALLY DEFERRED);";
var originalIndices = new[]
{
"CREATE INDEX some_index ON TEST_TestTable (id)",
"CREATE INDEX name_index ON TEST_TestTable (name)",
"CREATE INDEX this_should_be_gone ON TEST_TestTable (name, last)",
};
var input = new AlterTableCommand("TEST_TestTable");
input.CreateIndex("funny_index", "id", "name");
input.DropIndex("some_index");
input.AddColumn("add_column", "TINYINT");
input.DropColumn("last");
//no, this doesn't make sense; no, I don't care
input.AlterColumn("name", c => c.WithDefault(0).WithType("INTEGER"));
input.DropForeignKey("TEST_FK");
input.CreateForeignKey("TEST_FK2", new [] { "id", "last" }, "SOME_OTHERTABLE", new [] { "something", "else" });
var expectedFinal = new[]
{
//this line expects the guid to be added
"CREATE TEMPORARY TABLE TEST_TestTable_ AS SELECT * FROM TEST_TestTable;",
"DROP TABLE TEST_TestTable;",
"CREATE TABLE TEST_TestTable (id INTEGER primary key autoincrement, name INTEGER default 0, add_column TINYINT default NULL, " +
"CONSTRAINT KEEP_FK FOREIGN KEY (temp, id) REFERENCES FAKE_TABLE (temp, id) DEFERRABLE INITIALLY DEFERRED, " +
"CONSTRAINT TEST_FK2 FOREIGN KEY (id, last) REFERENCES SOME_OTHERTABLE (something, else) DEFERRABLE INITIALLY DEFERRED);",
//this line expects the guid to be added
"INSERT INTO TEST_TestTable (id, name) SELECT id, name FROM TEST_TestTable_;",
"CREATE INDEX name_index ON TEST_TestTable (name);",
"CREATE INDEX funny_index ON TEST_TestTable (id, name);",
//this line expects the guid to be added
"DROP TABLE TEST_TestTable_;",
}.Select(LowerAndWhitespaceFreeString).ToArray();
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
var output = adapter.AlterTableStatements(input).Select(LowerAndWhitespaceFreeString).ToArray();
VerifyYourStatementsAreValid(expectedFinal, output);
}
public virtual IEnumerable <string> Run(AlterTableCommand command)
{
if (command.TableCommands.Count == 0)
{
yield break;
}
var commands = new List <string>();
// drop columns
foreach (var dropColumn in command.TableCommands.OfType <DropColumnCommand>())
{
var builder = new StringBuilder();
Run(builder, dropColumn);
}
// add columns
foreach (var addColumn in command.TableCommands.OfType <AddColumnCommand>())
{
var builder = new StringBuilder();
Run(builder, addColumn);
yield return(builder.ToString());
}
// alter columns
foreach (var alterColumn in command.TableCommands.OfType <AlterColumnCommand>())
{
var builder = new StringBuilder();
Run(builder, alterColumn);
yield return(builder.ToString());
}
// add index
foreach (var addIndex in command.TableCommands.OfType <AddIndexCommand>())
{
var builder = new StringBuilder();
Run(builder, addIndex);
yield return(builder.ToString());
}
// drop index
foreach (var dropIndex in command.TableCommands.OfType <DropIndexCommand>())
{
var builder = new StringBuilder();
Run(builder, dropIndex);
yield return(builder.ToString());
}
}
public void DropMissingIndexShouldFail()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256));";
var originalIndices = new string[0];
var input = new AlterTableCommand("TEST_TestTable");
input.DropIndex("fake_index");
input.DropColumn("name");
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
Assert.Throws <InvalidIndexException>(() => adapter.AlterTableStatements(input).ToArray());
}
public void AlterMissingColumnShouldFail()
{
//var origin
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256));";
var originalIndices = new string[0];
var input = new AlterTableCommand("TEST_TestTable");
input.AlterColumn("fake_column", i => i.WithType("fake"));
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
Assert.Throws <InvalidColumnException <AlterColumnCommand> >(() => adapter.AlterTableStatements(input).ToArray());
}
protected override IExistingTable CreateItem(string name)
{
AlterTableCommand alterTableCommand;
MigrateCommand migrateCommand;
if ((migrateCommand = _command as MigrateCommand) != null)
{
alterTableCommand = new AlterTableCommand(migrateCommand, name);
}
else
{
alterTableCommand = new AlterTableCommand((AlterSchemaCommand)_command, name);
}
_command.Add(alterTableCommand);
return(new ExistingTable(alterTableCommand));
}
public SchemaBuilder AlterTable(string name, Action <AlterTableCommand> table)
{
try
{
var alterTable = new AlterTableCommand(Prefix(name), _dialect, _tablePrefix);
table(alterTable);
Execute(_builder.CreateSql(alterTable));
}
catch
{
if (ThrowOnError)
{
throw;
}
}
return(this);
}
public void FirstAlterTableTest()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256))";
var originalIndices = new[]
{
"CREATE INDEX some_index ON TEST_TestTable (id)",
"CREATE INDEX name_index ON TEST_TestTable (name)",
"CREATE INDEX this_should_be_gone ON TEST_TestTable (name, last)",
};
var input = new AlterTableCommand("TEST_TestTable");
input.CreateIndex("funny_index", "id", "name");
input.DropIndex("some_index");
input.AddColumn("add_column", "TINYINT", command => command.WithType("TINYINT"));
input.DropColumn("last");
//no, this doesn't make sense; no, I don't care
input.AlterColumn("name", c => c.WithDefault(0).WithType("INTEGER"));
var expectedFinal = new[]
{
//this line expects the guid to be added
"CREATE TEMPORARY TABLE TEST_TestTable_ AS SELECT * FROM TEST_TestTable;",
"DROP TABLE TEST_TestTable;",
"CREATE TABLE TEST_TestTable (id INTEGER primary key autoincrement, name INTEGER default 0, add_column TINYINT default NULL);",
//this line expects the guid to be added
"INSERT INTO TEST_TestTable (id, name) SELECT id, name FROM TEST_TestTable_;",
"CREATE INDEX name_index ON TEST_TestTable (name);",
"CREATE INDEX funny_index ON TEST_TestTable (id, name);",
//this line expects the guid to be added
"DROP TABLE TEST_TestTable_;",
};
var adapter = new AlterTableAdapter(originalTable, originalIndices);
var output = adapter.AlterTableStatements(input).Select(LowerAndWhitespaceFreeString).ToArray();
VerifyYourStatementsAreValid(expectedFinal, output);
}
public override void Visit(AlterTableCommand command)
{
_output.WriteLine("Altering table {0}", command.Name);
}
public AlterForeignKeyCommand(AlterTableCommand parent, string constraintName)
: base(parent)
{
_constraintName = constraintName;
}
public AlterColumnCommand(AlterTableCommand parent, string columnName)
: base(parent)
{
_columnName = columnName;
}
public override void Visit(AlterTableCommand command) {
_output.WriteLine("Altering table {0}", command.Name);
}
public abstract void Visit(AlterTableCommand command);
public AlterIndexCommand(AlterTableCommand parent, string indexName)
: base(parent)
{
_indexName = indexName;
}
public abstract void Visit(AlterTableCommand command);
public virtual IEnumerable<string> Run(AlterTableCommand command)
{
if (command.TableCommands.Count == 0)
{
yield break;
}
var commands = new List<string>();
// drop columns
foreach (var dropColumn in command.TableCommands.OfType<DropColumnCommand>())
{
var builder = new StringBuilder();
Run(builder, dropColumn);
}
// add columns
foreach (var addColumn in command.TableCommands.OfType<AddColumnCommand>())
{
var builder = new StringBuilder();
Run(builder, addColumn);
yield return builder.ToString();
}
// alter columns
foreach (var alterColumn in command.TableCommands.OfType<AlterColumnCommand>())
{
var builder = new StringBuilder();
Run(builder, alterColumn);
yield return builder.ToString();
}
// add index
foreach (var addIndex in command.TableCommands.OfType<AddIndexCommand>())
{
var builder = new StringBuilder();
Run(builder, addIndex);
yield return builder.ToString();
}
// drop index
foreach (var dropIndex in command.TableCommands.OfType<DropIndexCommand>())
{
var builder = new StringBuilder();
Run(builder, dropIndex);
yield return builder.ToString();
}
}
public AlterUniqueConstraintCommand(AlterTableCommand command, string constraintName)
: base(command)
{
_constraintName = constraintName;
}
public ForeignKeyCollection(AlterTableCommand command)
{
_command = command;
}
public UniqueConstraintCollection(AlterTableCommand command)
{
_command = command;
}
private CreateTableNode IncorporateAlterationsInCreateNode(AlterTableCommand command,
CreateTableNode createTableNode)
{
foreach (TableCommand alterCommand in command.TableCommands)
{
if (alterCommand is AddColumnCommand)
{
var addColumn = alterCommand as AddColumnCommand;
createTableNode.ColumnDefinitions.Add(addColumn.CreateColumnDefNode());
}
else if (alterCommand is DropColumnCommand)
{
var dropColumn = alterCommand as DropColumnCommand;
ColumnDefNode result =
createTableNode.ColumnDefinitions.FirstOrDefault(i => i.ColumnName == dropColumn.ColumnName);
if (result == null)
{
//bad!!
throw new InvalidColumnException <DropColumnCommand>(String.Format("Altering column {0} failed. No such column exists on table {1}.", dropColumn.ColumnName, dropColumn.TableName), dropColumn);
}
else
{
//remove our column
createTableNode.ColumnDefinitions.Remove(result);
}
}
else if (alterCommand is AlterColumnCommand)
{
var alterColumn = alterCommand as AlterColumnCommand;
ColumnDefNode columnDef =
createTableNode.ColumnDefinitions.FirstOrDefault(i => i.ColumnName == alterColumn.ColumnName);
if (columnDef == null)
{
//throw!!!!
throw new InvalidColumnException <AlterColumnCommand>(String.Format("Altering column {0} failed. No such column exists on table {1}.", alterColumn.ColumnName, alterColumn.TableName), alterColumn);
}
//modify the type name
if (!String.IsNullOrEmpty(alterColumn.DbType))
{
columnDef.TypeNameNode = SQLiteParseVisitor.ParseString <TypeNameNode>(alterColumn.DbType,
i => i.type_name());
}
if (alterColumn.Default != null)
{
//modify the default
DefaultConstraintNode defaultConstraint =
columnDef.ColumnConstraints.OfType <DefaultConstraintNode>().FirstOrDefault();
if (defaultConstraint == null)
{
//we'll create our own
defaultConstraint = new DefaultConstraintNode
{
Value = DbUtils.ConvertToSqlValue(alterColumn.Default)
};
//and add it!
columnDef.ColumnConstraints.Add(defaultConstraint);
}
else
{
//we modify the one that exists
defaultConstraint.Value = DbUtils.ConvertToSqlValue(alterColumn.Default);
}
}
}
else if (alterCommand is CreateForeignKeyCommand)
{
var foreignKeyCommand = alterCommand as CreateForeignKeyCommand;
var keyNode = new TableConstraintForeignKeyNode
{
FieldNames = foreignKeyCommand.SrcColumns,
ConstraintName = foreignKeyCommand.Name,
ForeignKeyClauseNode = new ForeignKeyClauseNode
{
TableName = foreignKeyCommand.DestTable,
FieldList = foreignKeyCommand.DestColumns,
ForeignDeferrable = new ForeignDeferrableNode().SetToTrulyDeferrable()
}
};
createTableNode.TableConstraints.Add(keyNode);
}
else if (alterCommand is DropForeignKeyCommand)
{
var foreignKeyCommand = alterCommand as DropForeignKeyCommand;
TableConstraintForeignKeyNode foreignKeyDrop = createTableNode.TableConstraints
.OfType <TableConstraintForeignKeyNode>()
.FirstOrDefault(n => n.ConstraintName == foreignKeyCommand.Name);
if (foreignKeyDrop == null)
{
throw new InvalidForeignKeyException(String.Format("No foreign key {0} exists.", foreignKeyCommand.Name), foreignKeyCommand);
}
createTableNode.TableConstraints.Remove(foreignKeyDrop);
}
}
return(createTableNode);
}
public AlterUniqueConstraintCommand(AlterTableCommand command, string constraintName) : base(command)
{
_constraintName = constraintName;
}
public AlterColumnCommand(AlterTableCommand parent, string columnName) : base(parent)
{
_columnName = columnName;
}
public void Visit(AlterTableCommand command) {
}
public void Visit(AlterTableCommand command)
{
}
