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