protected virtual void ProcessAlterTable(TableDefinition tableDefinition)
{
var tableName = tableDefinition.Name;
var tempTableName = tableName + "_temp";
var uid = 0;
while (TableExists(null, tempTableName))
{
tempTableName = tableName + "_temp" + uid++;
}
// What is the cleanest way to do this? Add function to Generator?
var quoter = new SqliteQuoter();
var columnsToTransfer = string.Join(", ", tableDefinition.Columns.Select(c => quoter.QuoteColumnName(c.Name)));
Process(new CreateTableExpression() { TableName = tempTableName, Columns = tableDefinition.Columns.ToList() });
Process(string.Format("INSERT INTO {0} SELECT {1} FROM {2}", quoter.QuoteTableName(tempTableName), columnsToTransfer, quoter.QuoteTableName(tableName)));
Process(new DeleteTableExpression() { TableName = tableName });
Process(new RenameTableExpression() { OldName = tempTableName, NewName = tableName });
foreach (var index in tableDefinition.Indexes)
{
Process(new CreateIndexExpression() { Index = index });
}
}
public FirebirdTableSchema(string tableName, FirebirdProcessor processor)
{
TableName = tableName;
Processor = processor;
Definition = new TableDefinition()
{
Name = tableName,
SchemaName = String.Empty
};
Load();
}
protected virtual IList<TableDefinition> ReadTables()
{
var dtTable = GetTableNamesAndDDL();
var tableDefinitionList = new List<TableDefinition>();
foreach (DataRow dr in dtTable.Rows)
{
var tableDef = new TableDefinition
{
Name = dr["name"].ToString(),
Columns = GetColumnDefinitionsForTableDDL(dr["sql"].ToString())
};
tableDefinitionList.Add(tableDef);
}
return tableDefinitionList;
}
protected virtual TableDefinition ParseCreateTableStatement(SqliteSyntaxReader reader)
{
var table = new TableDefinition();
while (reader.Read() != SqliteSyntaxReader.TokenType.StringToken || reader.ValueToUpper != "TABLE") ;
if (reader.Read() == SqliteSyntaxReader.TokenType.StringToken && reader.ValueToUpper == "IF")
{
reader.Read(); // NOT
reader.Read(); // EXISTS
}
else
{
reader.Rollback();
}
table.Name = ParseIdentifier(reader);
// Find Column List
reader.SkipTillToken(SqliteSyntaxReader.TokenType.ListStart);
// Split the list.
var list = reader.ReadList();
foreach (var columnReader in list)
{
columnReader.SkipWhitespace();
if (columnReader.Read() == SqliteSyntaxReader.TokenType.StringToken)
{
if (columnReader.ValueToUpper == "CONSTRAINT" ||
columnReader.ValueToUpper == "PRIMARY" || columnReader.ValueToUpper == "UNIQUE" ||
columnReader.ValueToUpper == "CHECK" || columnReader.ValueToUpper == "FOREIGN")
{
continue;
}
}
columnReader.Rollback();
var column = ParseColumnDefinition(columnReader);
column.TableName = table.Name;
table.Columns.Add(column);
}
return table;
}
public void TestSchemaTestWriter()
{
var tableDef = new TableDefinition
{
SchemaName = "dbo",
Name = "tableName",
Columns = new List<ColumnDefinition> { new ColumnDefinition() },
Indexes = new List<IndexDefinition> { new IndexDefinition() },
ForeignKeys = new List<ForeignKeyDefinition> { new ForeignKeyDefinition() }
};
var defs = new List<TableDefinition> {tableDef};
var testWriter = new SchemaTestWriter();
var output = GetOutput(testWriter, defs);
string expectedMessage = testWriter.GetMessage(1, 1, 1, 1);
output.ShouldBe(expectedMessage);
}
/// <summary>
/// Writes the Migration Up() and Down()
/// </summary>
/// <param name="context">The context that controls how the column should be generated</param>
/// <param name="table">the table to generate the migration for</param>
/// <param name="migration">The migration index to apply</param>
/// <param name="output">The output stream to append the C# code to</param>
private void WriteToStream(SchemaMigrationContext context, TableDefinition table, int migration, StreamWriter output)
{
WriteMigration(output, context, migration
, () => context.MigrationClassNamer(migration, table)
,() => WriteTable(context, table, output)
, () => WriteDeleteTable(context, table, output));
}
/// <summary>
/// Creates a table and optionally inserts data into the table if <see cref="SchemaMigrationContext.MigrateData"/> is <c>True</c>
/// </summary>
/// <param name="context">The context that controls how the column should be generated</param>
/// <param name="table">the table to generate the migration for</param>
/// <param name="output">The output stream to append the C# code to</param>
private void WriteTable(SchemaMigrationContext context, TableDefinition table, StreamWriter output)
{
if ( context.MigrationRequired(MigrationType.Tables) )
{
output.WriteLine("\t\t\tif (Schema.Table(\"{0}\").Exists()) return;", table.Name);
output.WriteLine("");
output.WriteLine("\t\t\tCreate.Table(\"" + table.Name + "\")");
foreach (var column in table.Columns)
{
WriteColumn(context, column, output, column == table.Columns.Last());
}
if (context.MigrationRequired(MigrationType.Data))
{
WriteInsertData(output, table, context);
}
}
if ( context.MigrationRequired(MigrationType.Indexes) && table.Indexes.Count > 0 )
{
var primaryKey = table.Columns.Where(c => c.IsPrimaryKey);
var primaryKeyName = string.Empty;
if ( primaryKey.Count() > 0 )
{
primaryKeyName = primaryKey.First().PrimaryKeyName;
}
foreach (var index in table.Indexes.Where(i => i.Name != primaryKeyName))
WriteIndex(output, index, context);
}
}
private void WriteInsertData(StreamWriter output, TableDefinition table, SchemaMigrationContext context)
{
output.Write(string.Format("\t\t\tInsert.IntoTable(\"{0}\").DataTable(@\"{1}\\{0}.xml\")",table.Name, context.DataDirectory));
// Check if the column contains an identity column
var identityColumn = ColumnWithIdentitySpecified(table);
if (!string.IsNullOrEmpty(identityColumn))
{
// It does lets add on extra handling for inserting the identity value into the destination database
output.Write(".WithIdentity().OnColumn(\"");
output.Write(identityColumn);
output.Write("\")");
}
// Add any replacement values
foreach (var replacement in MatchingReplacements(context.InsertColumnReplacements, table))
{
output.Write(string.Format(".WithReplacementValue({0}, {1})", FormatValue(replacement.OldValue), FormatValue(replacement.NewValue)));
}
// Check if we need to honour case senstive column names
if ( context.CaseSenstiveColumnNames && context.CaseSenstiveColumns.Count() == 0)
{
// We do update insert statement
output.Write(".WithCaseSensitiveColumnNames()");
}
if ( context.CaseSenstiveColumns.Count() > 0 )
{
foreach (var column in context.CaseSenstiveColumns)
{
// We do update insert statement
output.Write(string.Format(".WithCaseSensitiveColumn(\"{0}\")",column));
}
}
output.Write(";");
}
private void WriteForeignKey(TableDefinition table, StreamWriter writer)
{
foreach (var foreignKey in table.ForeignKeys)
{
writer.WriteLine(string.Format("\t\t\tCreate.ForeignKey(\"{0}\")", foreignKey.Name));
writer.WriteLine(string.Format("\t\t\t\t.FromTable(\"{0}\")", foreignKey.ForeignTable));
foreach (var column in foreignKey.ForeignColumns)
{
writer.WriteLine(string.Format("\t\t\t\t\t.ForeignColumn(\"{0}\")", column));
}
writer.WriteLine(string.Format("\t\t\t\t.ToTable(\"{0}\")", foreignKey.PrimaryTable));
foreach (var column in foreignKey.PrimaryColumns)
{
writer.WriteLine();
writer.Write(string.Format("\t\t\t\t\t.PrimaryColumn(\"{0}\")", column));
}
writer.WriteLine(";");
}
}
private static void WriteDeleteTable(SchemaMigrationContext context, TableDefinition table, StreamWriter output)
{
if (context.MigrationRequired(MigrationType.Indexes) && table.Indexes.Count > 0)
{
var primaryKey = table.Columns.Where(c => c.IsPrimaryKey);
var primaryKeyName = string.Empty;
if (primaryKey.Count() > 0)
{
primaryKeyName = primaryKey.First().PrimaryKeyName;
}
foreach (var index in table.Indexes.Where(i => i.Name != primaryKeyName))
WriteDeleteIndex(output, index);
}
if (context.MigrationRequired(MigrationType.Tables))
{
output.WriteLine("\t\t\tDelete.Table(\"" + table.Name + "\");");
}
}
/// <summary>
/// Searched for replacements that match the <see cref="InsertColumnReplacement.ColumnDataToMatch"/>
/// </summary>
/// <param name="replacements">The replacements to be searched</param>
/// <param name="table">The table to compare the replacements against</param>
/// <returns>Any matching replacements</returns>
private static IEnumerable<InsertColumnReplacement> MatchingReplacements(IEnumerable<InsertColumnReplacement> replacements, TableDefinition table)
{
return
replacements.Where(
possibleReplacement => (table.Columns.Where(
columnDefinition =>
columnDefinition.Type == possibleReplacement.ColumnDataToMatch.Type &&
columnDefinition.IsNullable == possibleReplacement.ColumnDataToMatch.IsNullable).Count() > 0)).ToList();
}
private static string ColumnWithIdentitySpecified(TableDefinition table)
{
var column = table.Columns.Where(c => c.IsIdentity);
if ( column.Count() == 1)
{
return column.First().Name;
}
return string.Empty;
}
protected void WriteDeleteTable(TableDefinition table, StreamWriter output)
{
//Delete.Table("Bar");
output.WriteLine("\t\t\tDelete.Table(\"" + table.Name + "\");");
}
protected void WriteTable(TableDefinition table, StreamWriter output)
{
output.WriteLine("\t\t\tCreate.Table(\"" + table.Name + "\")");
foreach (ColumnDefinition column in table.Columns)
{
WriteColumn(column, output, column == table.Columns.Last());
}
}
private void WriteDeleteForeignKey(TableDefinition table, StreamWriter writer)
{
foreach (var foreignKey in table.ForeignKeys)
{
writer.WriteLine(string.Format("\t\t\tDelete.ForeignKey(\"{0}\");", foreignKey.Name));
}
}
protected virtual IList<TableDefinition> ReadTables()
{
const string query = @"SELECT OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema], t.name AS [Table],
c.[Name] AS ColumnName,
t.object_id AS [TableID],
c.column_id AS [ColumnID],
def.definition AS [DefaultValue],
c.[system_type_id] AS [TypeID],
c.[user_type_id] AS [UserTypeID],
c.[max_length] AS [Length],
c.[precision] AS [Precision],
c.[scale] AS [Scale],
c.[is_identity] AS [IsIdentity],
c.[is_nullable] AS [IsNullable],
CASE WHEN EXISTS(SELECT 1 FROM sys.foreign_key_columns fkc WHERE t.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id) THEN 1 ELSE 0 END AS IsForeignKey,
CASE WHEN EXISTS(select 1 from sys.index_columns ic WHERE t.object_id = ic.object_id AND c.column_id = ic.column_id) THEN 1 ELSE 0 END AS IsIndexed
,CASE WHEN kcu.CONSTRAINT_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey
, CASE WHEN EXISTS(select stc.CONSTRAINT_NAME, skcu.TABLE_NAME, skcu.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS stc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE skcu ON skcu.CONSTRAINT_NAME = stc.CONSTRAINT_NAME WHERE stc.CONSTRAINT_TYPE = 'UNIQUE'
AND skcu.TABLE_NAME = t.name AND skcu.COLUMN_NAME = c.name) THEN 1 ELSE 0 END AS IsUnique
,pk.name AS PrimaryKeyName
FROM sys.all_columns c
JOIN sys.tables t ON c.object_id = t.object_id AND t.type = 'u'
LEFT JOIN sys.default_constraints def ON c.default_object_id = def.object_id
LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND pk.type = 'PK'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON t.name = kcu.TABLE_NAME AND c.name = kcu.COLUMN_NAME AND pk.name = kcu.CONSTRAINT_NAME
ORDER BY t.name, c.name";
DataSet ds = Read(query);
DataTable dt = ds.Tables[0];
IList<TableDefinition> tables = new List<TableDefinition>();
foreach (DataRow dr in dt.Rows)
{
List<TableDefinition> matches = (from t in tables
where t.Name == dr["Table"].ToString()
&& t.SchemaName == dr["Schema"].ToString()
select t).ToList();
TableDefinition tableDef = null;
if (matches.Count > 0) tableDef = matches[0];
// create the table if not found
if (tableDef == null)
{
tableDef = new TableDefinition
{
Name = dr["Table"].ToString(),
SchemaName = dr["Schema"].ToString()
};
tables.Add(tableDef);
}
//find the column
List<ColumnDefinition> cmatches = (from c in tableDef.Columns
where c.Name == dr["ColumnName"].ToString()
select c).ToList();
ColumnDefinition colDef = null;
if (cmatches.Count > 0) colDef = cmatches[0];
if (colDef == null)
{
//need to create and add the column
tableDef.Columns.Add(new ColumnDefinition
{
Name = dr["ColumnName"].ToString(),
CustomType = "", //TODO: set this property
DefaultValue = dr.IsNull("DefaultValue") ? "" : dr["DefaultValue"].ToString(),
IsForeignKey = dr["IsForeignKey"].ToString() == "1",
IsIdentity = dr["IsIdentity"].ToString() == "True",
IsIndexed = dr["IsIndexed"].ToString() == "1",
IsNullable = dr["IsNullable"].ToString() == "True",
IsPrimaryKey = dr["IsPrimaryKey"].ToString() == "1",
IsUnique = dr["IsUnique"].ToString() == "1",
Precision = int.Parse(dr["Precision"].ToString()),
PrimaryKeyName = dr.IsNull("PrimaryKeyName") ? "" : dr["PrimaryKeyName"].ToString(),
Size = int.Parse(dr["Length"].ToString()),
TableName = dr["Table"].ToString(),
Type = GetDbType(int.Parse(dr["TypeID"].ToString())), //TODO: set this property
ModificationType = ColumnModificationType.Create
});
}
}
return tables;
}
public DataExpressionRoot(IMigrationContext migrationContext)
{
_context = migrationContext;
_table = new TableDefinition { SchemaName = "dbo" };
}
