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