Beispiel #1
0
        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());
        }
Beispiel #2
0
        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);
        }
Beispiel #4
0
        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);
        }