public string GenerateCreateDatabase(DatabaseContainer database) { StringBuilder sb = new StringBuilder(); database.Schemas.ForEach(schema => { sb.AppendLine(GenerateCreateSchema(schema)); }); database.Schemas.ForEach(schema => { sb.AppendLine(GenerateDisableForeignKeyContraints(schema)); }); database.Schemas.ForEach(schema => { sb.AppendLine(GenerateDropAllTables(schema)); }); database.Schemas.ForEach(schema => { schema.Tables.ForEach(table => { sb.AppendLine(GenerateCreateTableStatement(schema.Name, table, schema.ForeignKeys)); }); }); List <Constraint> constraints = new List <Constraint>(); database.Schemas.ForEach(schema => { schema.Tables.ForEach(table => { constraints.AddRange(table.Constraints); }); }); sb.AppendLine(GenerateIndexes(constraints)); List <DefaultConstraint> defaultConstraints = new List <DefaultConstraint>(); database.Schemas.ForEach(schema => { schema.Tables.ForEach(table => { defaultConstraints.AddRange(table.DefaultConstraints); }); }); sb.AppendLine(GenerateCreateDefaultValueConstraints(defaultConstraints)); database.Schemas.ForEach(schema => { sb.AppendLine(GenerateForeignKeyConstraints(schema)); }); return(sb.ToString()); }
public virtual DatabaseContainer Clone() { DatabaseContainer container = new DatabaseContainer(this.Name, this.DatabaseType, this.Version); this.Schemas.ForEach(x => { container.Schemas.Add(x.Clone()); }); return(container); }
public string GenerateInsertStatmentsForDatabase(DatabaseContainer database) { TestDataGenerator generator = new TestDataGenerator(new TestDataRepository()); Resolver resolver = new Resolver(); /* Convert the tables to a dependency table list is really just our database with dependency tables instead of table */ List <DependencyTable> convertedTables = resolver.ConvertToDependencyTables(database); string output = string.Empty; //This must be defined like this for the recursive call to work inside the action. It's a .NET quirk. Action <DependencyTable> recursiveGenerateInsert = null; /* * primaryKeys tracks what the auto incremnted primary key is for each table while we're inserting * */ Dictionary <Tuple <string, string>, int> primaryKeys = new Dictionary <Tuple <string, string>, int>(); //key is schema:table /* * fieldTypeIndex tracks the index in the test generator for unique indexes. * So we have a list of unique guids in our test data array and we are inserting into a guid * column that is unique. We can't insert the same quid twice. So we use this index which is keyed by schema:table:column * to track which entry in the test data array we're on. * */ Dictionary <Tuple <string, string, string>, int> fieldTypeIndex = new Dictionary <Tuple <string, string, string>, int>(); // keys is schema:tablecolumn // this iterated the table columns and walks the tree using the RefersTo field on the table to find the next node. // When it gets to the bottom leaf of the tree it generates the insert SQL and then returns recursiveGenerateInsert = (t) => { Dictionary <string, int> explicitForeignKeys = new Dictionary <string, int>(); t.Columns.ForEach(c => { if (c.RefersTo != null) // if there is a table we reference { if (c.RefersTo.Table != t) { recursiveGenerateInsert(c.RefersTo.Table); // iterate all of the columns } else { // this is a hack. When we insert into a table with a foreign key to itself we need a value so // we're just hard coding it to one for now primaryKeys[Tuple.Create <string, string>(c.RefersTo.Table.Schema, c.RefersTo.Table.Name)] = 1; } /* Set the value for the column that is a foreign key explicit by looking for the current primaryKey for the referenced table * */ explicitForeignKeys[c.Name] = primaryKeys[Tuple.Create <string, string>(c.RefersTo.Table.Schema, c.RefersTo.Table.Name)]; } }); var schema = database.Schemas.First(x => x.Name.ToUpper() == t.Schema.ToUpper()); var table = schema.Tables.First(x => x.Name.ToUpper() == t.Name.ToUpper()); int primaryKey = 0; if (primaryKeys.Keys.Any(k => k.Item1 == schema.Name && k.Item2 == table.Name)) // a primary key for this table exists { primaryKey = primaryKeys[Tuple.Create <string, string>(schema.Name, table.Name)] + 1; // increment it and return the new value primaryKeys[Tuple.Create <string, string>(schema.Name, table.Name)] = primaryKey; } else { primaryKey = 1;// this table doesn't exist. Create a dictionary entry and set it to 1 primaryKeys[Tuple.Create <string, string>(schema.Name, table.Name)] = primaryKey; } output += generator.GeneratInsert(schema, table, primaryKey, fieldTypeIndex, explicitForeignKeys); }; convertedTables.ForEach(t => { resolver.ClearValues(t); // clear the RefersTo for all of the tables. // Build the dependency tree for this table resolver.BuildDependsOnList(database.GetForeignKeys(), convertedTables, t); // Iterate this tables columns and any tables those columns reference recursiveGenerateInsert(t); }); return(output); }
public DatabaseContainer GetDatabaseStructure() { Microsoft.Data.SqlClient.SqlConnectionStringBuilder builder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(this._connectionString); DatabaseContainer container = new DatabaseContainer(builder.InitialCatalog, DatabaseContainer.DatabaseTypeMSSQL); using (SqlConnection conn = new SqlConnection(this._connectionString)) { List <Constraint> databaseConstraints = GetIndexesForDatabase(); List <DefaultConstraint> databaseDefaultConstraints = GetDefaultConstraintsForDatabase(); List <ForeignKey> databaseForeignKeys = GetForeignKeysForDatabase(); conn.Open(); SqlCommand command = new SqlCommand(@"SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, columns.IS_NULLABLE, columns.TABLE_SCHEMA as [SCHEMA], sc.is_computed, sc.is_rowguidcol, sc.is_identity, TABLES.TABLE_TYPE as TABLE_TYPE FROM INFORMATION_SCHEMA.COLUMNS AS columns LEFT JOIN INFORMATION_SCHEMA.TABLES on INFORMATION_SCHEMA.TABLES.TABLE_NAME = columns.TABLE_NAME AND INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = columns.table_schema LEFT JOIN sys.columns sc on object_name(sc.object_id) = columns.TABLE_NAME AND OBJECT_SCHEMA_NAME(sc.object_id) = columns.table_schema AND sc.name = COLUMN_NAME WHERE (TABLES.TABLE_TYPE = 'BASE TABLE' OR TABLES.TABLE_TYPE = 'VIEW') AND columns.TABLE_SCHEMA <> 'SYS' AND INFORMATION_SCHEMA.TABLES.TABLE_NAME <> '__EFMigrationsHistory' ORDER BY [SCHEMA],columns.TABLE_NAME,columns.COLUMN_NAME", conn); command.CommandTimeout = 300; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { string table = reader["TABLE_NAME"].ToString(); string schema = reader["SCHEMA"].ToString(); Column column = new Column(); column.Name = reader["COLUMN_NAME"].ToString(); column.DataType = reader["DATA_TYPE"].ToString(); column.NumericPrecision = String.IsNullOrEmpty(reader["NUMERIC_PRECISION"].ToString()) ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]); column.MaxLength = String.IsNullOrEmpty(reader["CHARACTER_MAXIMUM_LENGTH"].ToString()) ? 0 : Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]); column.IsNullable = reader["IS_NULLABLE"].ToString() == "YES" ? true : false; bool isComputed = Convert.ToBoolean(reader["IS_COMPUTED"].ToString()); bool isRowGuidColumn = Convert.ToBoolean(reader["is_rowguidcol"].ToString()); bool isIdentity = Convert.ToBoolean(reader["is_identity"].ToString()); if (isComputed || isRowGuidColumn || isIdentity || column.DataType.ToUpper() == "TIMESTAMP") { column.DatabaseGenerated = true; } var existingSchema = container.Schemas.FirstOrDefault(x => x.Name == schema); if (existingSchema == null) { Schema newSchema = new Schema { Name = schema, ForeignKeys = databaseForeignKeys.Where(f => f.Columns.Any(c => c.ForeignKeySchemaName == schema)).ToList() }; var existingTable = newSchema.Tables.FirstOrDefault(x => x.Name == table); if (existingTable == null) { Table newTable = new Table(); var tableType = reader["TABLE_TYPE"].ToString(); if (tableType.ToUpper() == "VIEW") { newTable.IsView = true; } newTable.Name = table; newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); newTable.Columns.Add(column); newSchema.Tables.Add(newTable); } else { existingTable.Columns.Add(column); } container.Schemas.Add(newSchema); } else { var existingTable = existingSchema.Tables.FirstOrDefault(x => x.Name == table); if (existingTable == null) { Table newTable = new Table(); var tableType = reader["TABLE_TYPE"].ToString(); if (tableType.ToUpper() == "VIEW") { newTable.IsView = true; } newTable.Name = table; newTable.Columns.Add(column); newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); existingSchema.Tables.Add(newTable); } else { existingTable.Columns.Add(column); } } } } return(container); }
public DatabaseContainer GetDatabaseStructure() { Microsoft.Data.SqlClient.SqlConnectionStringBuilder builder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(this._connectionString); DatabaseContainer container = new DatabaseContainer(builder.InitialCatalog, DatabaseContainer.DatabaseTypePostgreSQL); using (NpgsqlConnection conn = new NpgsqlConnection(this._connectionString)) { List <Constraint> databaseConstraints = GetIndexesForDatabase(); //List<DefaultConstraint> databaseDefaultConstraints = GetDefaultConstraintsForDatabase(); List <ForeignKey> databaseForeignKeys = GetForeignKeysForDatabase(); conn.Open(); NpgsqlCommand command = new NpgsqlCommand(@"select ist.TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, IS_IDENTITY, ist.table_schema as Schema, ist.TABLE_TYPE FROM INFORMATION_SCHEMA.Columns isc LEFT JOIN INFORMATION_SCHEMA.Tables ist on ist.table_name = isc.table_name AND ist.TABLE_SCHEMA = isc.TABLE_SCHEMA WHERE ist.table_schema NOT IN ('pg_catalog','information_schema') AND ist.TABLE_NAME <> '__EFMigrationsHistory' AND (ist.TABLE_TYPE = 'BASE TABLE' OR ist.TABLE_TYPE = 'VIEW')", conn); command.CommandTimeout = 300; NpgsqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { string table = reader["TABLE_NAME"].ToString(); string schema = reader["SCHEMA"].ToString(); Column column = new Column(); column.Name = reader["COLUMN_NAME"].ToString(); column.DataType = reader["DATA_TYPE"].ToString(); column.NumericPrecision = String.IsNullOrEmpty(reader["NUMERIC_PRECISION"].ToString()) ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]); column.MaxLength = String.IsNullOrEmpty(reader["CHARACTER_MAXIMUM_LENGTH"].ToString()) ? 0 : Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]); column.IsNullable = reader["IS_NULLABLE"].ToString() == "YES" ? true : false; bool isComputed = false; ///Convert.ToBoolean(reader["IS_COMPUTED"].ToString()); bool isRowGuidColumn = false; // Convert.ToBoolean(reader["is_rowguidcol"].ToString()); bool isIdentity = reader["is_identity"].ToString().ToBoolean(); //if (isComputed || isRowGuidColumn || isIdentity || column.DataType.ToUpper() == "TIMESTAMP") //{ // column.DatabaseGenerated = true; //} var existingSchema = container.Schemas.FirstOrDefault(x => x.Name == schema); if (existingSchema == null) { Schema newSchema = new Schema { Name = schema, ForeignKeys = databaseForeignKeys.Where(f => f.Columns.Any(c => c.ForeignKeySchemaName == schema)).ToList() }; var existingTable = newSchema.Tables.FirstOrDefault(x => x.Name == table); if (existingTable == null) { Table newTable = new Table(); newTable.Name = table; var tableType = reader["TABLE_TYPE"].ToString(); if (tableType.ToUpper() == "VIEW") { newTable.IsView = true; } newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); //newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); newTable.Columns.Add(column); newSchema.Tables.Add(newTable); } else { existingTable.Columns.Add(column); } container.Schemas.Add(newSchema); } else { var existingTable = existingSchema.Tables.FirstOrDefault(x => x.Name == table); if (existingTable == null) { Table newTable = new Table(); newTable.Name = table; var tableType = reader["TABLE_TYPE"].ToString(); if (tableType.ToUpper() == "VIEW") { newTable.IsView = true; } newTable.Columns.Add(column); newTable.Constraints = databaseConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); //newTable.DefaultConstraints = databaseDefaultConstraints.Where(c => c.SchemaName == schema && c.TableName == table).ToList(); existingSchema.Tables.Add(newTable); } else { existingTable.Columns.Add(column); } } } } return(container); }