public override string DropIndex(DatabaseTable databaseTable, DatabaseIndex index)
 {
     return string.Format(CultureInfo.InvariantCulture,
         "DROP INDEX {0}{1};",
         SchemaPrefix(index.SchemaOwner),
         Escape(index.Name));
 }

        public void TriggerWithNoQuotes()
        {
            //arrange
            var osr = new OracleSchemaReader(ConnectionStrings.OracleHr, "System.Data.OracleClient");
            var dt = new DatabaseTable();
            dt
                .AddColumn("ID")
                .AddPrimaryKey()
                .AddColumn("NAME");
            dt.Triggers.Add(new DatabaseTrigger
            {
                //with spaces, line breaks
                TriggerBody = @"BEGIN
    SELECT MY_SEQ.NEXTVAL
    INTO :NEW.ID
    FROM DUAL;
END;"
            });

            //act
            osr.PostProcessing(dt);

            //assert
            Assert.IsTrue(dt.HasAutoNumberColumn);
        }

 /// <summary>
 /// Initializes a new instance of the <see cref="ProcedureGenerator"/> class.
 /// </summary>
 /// <param name="table">The table.</param>
 public ProcedureGenerator(DatabaseTable table)
     : base(table)
 {
     SqlWriter = new SqlWriter(table, SqlType.Oracle);
     SqlWriter.InStoredProcedure = true;
     PackageName = "PACK_" + TableName;
 }

 protected TableGeneratorBase(DatabaseTable table)
 {
     Table = table;
     TableName = table.Name;
     IncludeSchema = true;
     IncludeDefaultValues = true;
 }

        public DatabaseTable Load(string tableName, CancellationToken ct)
        {
            if (ct.IsCancellationRequested) return new DatabaseTable();
            ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.ReadingSchema, SchemaObjectType.Tables, tableName, null, null);

            var schemaOwner = _schemaReader.Owner;
            DatabaseTable table;
            using (var ds = _schemaReader.Table(tableName))
            {
                if (ds == null) return null;
                if (ds.Tables.Count == 0) return null;
                ReaderEventArgs.RaiseEvent(ReaderProgress, this, ProgressType.Processing, SchemaObjectType.Tables, tableName, null, null);

                table = _databaseSchema.FindTableByName(tableName, schemaOwner);
                if (table == null)
                {
                    table = new DatabaseTable();
                    _databaseSchema.Tables.Add(table);
                }
                table.Name = tableName;
                table.SchemaOwner = schemaOwner;
                //columns must be done first as it is updated by the others
                schemaOwner = AddColumns(schemaOwner, tableName, table, ds);
                AddConstraints(ds, table);
                AddOthers(schemaOwner, tableName, table, ds);

                _schemaReader.PostProcessing(table);
            }
            return table;
        }

        public override string AlterColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
        {
            var sb = new StringBuilder();
            var defaultName = "DF_" + databaseTable.Name + "_" + databaseColumn.Name;
            if (originalColumn != null)
            {
                if (originalColumn.DefaultValue != null)
                {
                    //have to drop default contraint
                    var df = FindDefaultConstraint(databaseTable, databaseColumn.Name);
                    if (df != null)
                    {
                        defaultName = df.Name;
                        sb.AppendLine("ALTER TABLE " + TableName(databaseTable)
                                      + " DROP CONSTRAINT " + Escape(defaultName) + ";");
                    }
                }
            }
            //we could check if any of the properties are changed here
            sb.AppendLine(base.AlterColumn(databaseTable, databaseColumn, originalColumn));
            if (databaseColumn.DefaultValue != null)
            {
                //add default contraint
                sb.AppendLine("ALTER TABLE " + TableName(databaseTable) +
                    " ADD CONSTRAINT " + Escape(defaultName) +
                    " DEFAULT " + databaseColumn.DefaultValue +
                    " FOR " + Escape(databaseColumn.Name) + ";");
            }

            return sb.ToString();
        }

        public void Execute(DatabaseTable databaseTable, DatabaseTable compareTable)
        {
            var firstTriggers = databaseTable.Triggers;
            var secondTriggers = compareTable.Triggers;
            foreach (var trigger in firstTriggers)
            {
                var indexName = trigger.Name;
                var match = secondTriggers.FirstOrDefault(c => c.Name == indexName);
                if (match == null)
                {
                    CreateResult(ResultType.Delete, databaseTable, indexName, 
                        _writer.DropTrigger(databaseTable, trigger));
                    continue;
                }
                if (trigger.TriggerBody != match.TriggerBody || 
                    trigger.TriggerType != match.TriggerType || 
                    trigger.TriggerEvent != match.TriggerEvent)
                {
                    CreateResult(ResultType.Change, databaseTable, indexName,
                        _writer.DropTrigger(databaseTable, trigger) + Environment.NewLine +
                        _writer.AddTrigger(databaseTable, match));
                }
            }

            foreach (var trigger in secondTriggers)
            {
                var indexName = trigger.Name;
                var firstConstraint = firstTriggers.FirstOrDefault(c => c.Name == indexName);
                if (firstConstraint == null)
                {
                    CreateResult(ResultType.Add, databaseTable, indexName, 
                        _writer.AddTrigger(databaseTable, trigger));
                }
            }
        }

        public void WithBidirectionalDepndencyTopologicalSort()
        {
            //arrange
            var schema = new DatabaseSchema(null, null);

            var orders = new DatabaseTable();
            orders.Name = "countries";
            var productsFk = new DatabaseConstraint();
            productsFk.ConstraintType = ConstraintType.ForeignKey;
            productsFk.RefersToTable = "capitalcities";
            orders.AddConstraint(productsFk);
            schema.Tables.Add(orders);

            var products = new DatabaseTable();
            products.Name = "capitalcities";
            var categoriesFk = new DatabaseConstraint();
            categoriesFk.ConstraintType = ConstraintType.ForeignKey;
            categoriesFk.RefersToTable = "countries";
            products.AddConstraint(categoriesFk);
            schema.Tables.Add(products);

            //a country has one capital city
            //a capital city is in one country
            //But bidirectional foreign keys is terrible database design - you really only need one direction.
            //(you have to save the country with a null capital, then the capital, then update the country again).
            //Topological sorts don't support cycles, so we should just get back the original list

            //act
            var sortedTables = SchemaTablesSorter.TopologicalSort(schema);

            //assert
            Assert.AreEqual(2, sortedTables.Count());
            //non-deterministic order
        }

        /// <summary>
        /// Finds the trigger that uses a sequence for autonumbering. May return NULL.
        /// </summary>
        /// <param name="databaseTable">The database table.</param>
        /// <returns></returns>
        public static OracleSequenceTrigger FindTrigger(DatabaseTable databaseTable)
        {
            var triggers = databaseTable.Triggers;
            if (triggers.Count == 0) return null;
            var pk = databaseTable.PrimaryKeyColumn ?? databaseTable.Columns.Find(x => x.IsAutoNumber);
            if (pk == null) return null;
            //the trigger body will look something like "SELECT MYSEQ.NEXTVAL INTO :NEW.ID FROM DUAL;"
            var pattern = ".NEXTVAL\\s+?INTO\\s+?:NEW.\"?" + pk.Name;
            var regex = new Regex(pattern, RegexOptions.IgnoreCase);

            foreach (var databaseTrigger in databaseTable.Triggers)
            {
                var body = databaseTrigger.TriggerBody;
                var match = regex.Match(body);
                if (!match.Success) continue;
                var result = new OracleSequenceTrigger(databaseTrigger);

                //let's write the sequence if we can find it
                var seqName = ParseSequenceName(body, match.Index);
                if (seqName == null) return result;
                result.SequenceName = seqName;
                if (databaseTable.DatabaseSchema != null)
                {
                    result.DatabaseSequence = databaseTable.DatabaseSchema.Sequences
                        .FirstOrDefault(x => seqName.Equals(x.Name, StringComparison.OrdinalIgnoreCase));
                }
                return result;
            }

            return null;
        }

        public bool RunSprocs(DirectoryInfo directory, SqlType dialect, DatabaseTable table)
        {
            if (table == null)
            {
                Message = "No table";
                return false;
            }

            var gen = new DdlGeneratorFactory(dialect).ProcedureGenerator(table);
            if (gen == null)
            {
                //there is no sproc provider (SQLite)
                Message = @"There is no sproc generator";
                return false;
            }
            var path = Path.Combine(directory.FullName, table.Name + "_sprocs.sql");
            try
            {
                gen.WriteToScript(path);
                Message = @"Wrote " + path;
                return true;
            }
            catch (Exception exception)
            {
                Message =
                    @"An error occurred while creating the script.\n" + exception.Message;
            }
            return false;
        }

        private static IList<DatabaseTable> Convert(DataTable dataTable)
        {
            var list = new List<DatabaseTable>();
            if ((dataTable == null) || (dataTable.Columns.Count == 0) || (dataTable.Rows.Count == 0))
            {
                return list;
            }

            const string schemaKey = "SchemaOwner";
            const string tableKey = "TableName";
            const string descKey = "ColumnDescription";
            const string columnKey = "ColumnName";

            foreach (DataRow row in dataTable.Rows)
            {
                var schema = row[schemaKey].ToString();
                var name = row[tableKey].ToString();
                var col = row[columnKey].ToString();
                var desc = row[descKey].ToString();
                var table = list.FirstOrDefault(t => t.SchemaOwner == schema && t.Name == name);
                if (table == null)
                {
                    table = new DatabaseTable();
                    table.Name = name;
                    table.SchemaOwner = schema;
                    list.Add(table);
                }
                table.AddColumn(col).Description = desc;
            }
            return list;
        }

 public virtual string AddColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
 {
     var tableGenerator = CreateTableGenerator(databaseTable);
     var addColumn = tableGenerator.WriteColumn(databaseColumn).Trim();
     if (string.IsNullOrEmpty(databaseColumn.DefaultValue) && !databaseColumn.Nullable)
     {
         var dt = databaseColumn.DataType;
         if (dt == null || dt.IsString)
         {
             addColumn += " DEFAULT ''"; //empty string
         }
         else if (dt.IsNumeric)
         {
             addColumn += " DEFAULT 0";
         }
         else if (dt.IsDateTime)
         {
             addColumn += " DEFAULT CURRENT_TIMESTAMP";
         }
         //make sure the NOT NULL is AFTER the default
         addColumn = addColumn.Replace(" NOT NULL ", " ") + " NOT NULL";
     }
     return string.Format(CultureInfo.InvariantCulture,
         "ALTER TABLE {0} ADD {1}",
         TableName(databaseTable),
         addColumn) + LineEnding();
 }

 public OverrideWriter(ClassBuilder classBuilder, DatabaseTable table, INamer namer)
 {
     _cb = classBuilder;
     _table = table;
     _namer = namer;
     NetName = table.NetName;
 }

 public override string DropColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn)
 {
     return string.Format(CultureInfo.InvariantCulture,
         "ALTER TABLE {0} DROP COLUMN {1} CASCADE;",
         TableName(databaseTable),
         Escape(databaseColumn.Name));
 }

        private void AddConstraints(DataSet ds, DatabaseTable table)
        {
            if (ds.Tables.Contains(_schemaReader.PrimaryKeysCollectionName))
            {
                var converter = new SchemaConstraintConverter(ds.Tables[_schemaReader.PrimaryKeysCollectionName],
                    ConstraintType.PrimaryKey);
                var pkConstraints = converter.Constraints();
                PrimaryKeyLogic.AddPrimaryKey(table, pkConstraints);
            }
            if (ds.Tables.Contains(_schemaReader.ForeignKeysCollectionName))
            {
                var converter = new SchemaConstraintConverter(ds.Tables[_schemaReader.ForeignKeysCollectionName],
                    ConstraintType.ForeignKey);
                table.AddConstraints(converter.Constraints());
            }
            if (ds.Tables.Contains(_schemaReader.ForeignKeyColumnsCollectionName))
            {
                var fkConverter = new ForeignKeyColumnConverter(ds.Tables[_schemaReader.ForeignKeyColumnsCollectionName]);
                fkConverter.AddForeignKeyColumns(table.ForeignKeys);
            }

            if (ds.Tables.Contains(_schemaReader.UniqueKeysCollectionName))
            {
                var converter = new SchemaConstraintConverter(ds.Tables[_schemaReader.UniqueKeysCollectionName],
                    ConstraintType.UniqueKey);
                table.AddConstraints(converter.Constraints());
            }
        }

 private void ComparePrimaryKey(DatabaseTable databaseTable, DatabaseTable match)
 {
     if (databaseTable.PrimaryKey == null && match.PrimaryKey == null)
     {
         //no primary key before or after. Oh dear.
         Trace.TraceWarning("-- NB: " + databaseTable.Name + " has no primary key!");
         return;
     }
     if (databaseTable.PrimaryKey == null)
     {
         //forgot to put pk on it
         CreateResult(ResultType.Add, databaseTable, match.PrimaryKey.Name,
             _writer.AddConstraint(databaseTable, match.PrimaryKey));
     }
     else if (match.PrimaryKey == null)
     {
         //why oh why would you want to drop the primary key?
         CreateResult(ResultType.Change, databaseTable, databaseTable.PrimaryKey.Name,
             _writer.DropConstraint(databaseTable, databaseTable.PrimaryKey) + Environment.NewLine +
             "-- NB: " + databaseTable.Name + " has no primary key!");
     }
     else if (!ConstraintColumnsEqual(databaseTable.PrimaryKey, match.PrimaryKey))
     {
         CreateResult(ResultType.Change, databaseTable, databaseTable.PrimaryKey.Name,
             _writer.DropConstraint(databaseTable, databaseTable.PrimaryKey) + Environment.NewLine +
             _writer.AddConstraint(match, match.PrimaryKey));
     }
 }

        private static IList<DatabaseTable> Convert(DataTable dataTable)
        {
            var list = new List<DatabaseTable>();
            if ((dataTable == null) || (dataTable.Columns.Count == 0) || (dataTable.Rows.Count == 0))
            {
                return list;
            }

            const string schemaKey = "SchemaOwner";
            const string tableKey = "TableName";
            const string descKey = "TableDescription";

            foreach (DataRow row in dataTable.Rows)
            {
                var schema = row[schemaKey].ToString();
                var name = row[tableKey].ToString();
                var desc = row[descKey].ToString();
                var table = new DatabaseTable();
                table.Name = name;
                table.SchemaOwner = schema;
                table.Description = desc;
                list.Add(table);
            }
            return list;
        }

        /// <summary>
        /// Converts the "Tables" DataTable into <see cref="DatabaseTable"/> objects.
        /// </summary>
        /// <remarks>
        /// Note the SqlServer DataTable includes views, which we explicitly remove. 
        /// </remarks>
        public static List<DatabaseTable> Tables(DataTable dt)
        {
            List<DatabaseTable> list = new List<DatabaseTable>();

            TableKeyMap keyMap = new TableKeyMap(dt);

            foreach (DataRow row in dt.Rows)
            {
                string type = row[keyMap.TypeKey].ToString();
                //Sql server has base tables and views. Oracle has system and user
                if (IsNotTable(type)) continue;
                DatabaseTable t = new DatabaseTable();
                t.Name = row[keyMap.TableName].ToString();
                //exclude Oracle bin tables
                if (t.Name.StartsWith("BIN$", StringComparison.OrdinalIgnoreCase)) continue;
                if (!string.IsNullOrEmpty(keyMap.OwnerKey))
                {
                    t.SchemaOwner = row[keyMap.OwnerKey].ToString();
                    //Db2 system tables creeping in
                    if (keyMap.IsDb2 && t.SchemaOwner.Equals("SYSTOOLS", StringComparison.OrdinalIgnoreCase)) continue;
                }
                list.Add(t);
            }
            return list;
        }

 public void Execute(DatabaseTable baseTable, DatabaseTable compareTable)
 {
     ComparePrimaryKey(baseTable, compareTable);
     Compare(baseTable, baseTable.UniqueKeys, compareTable.UniqueKeys);
     Compare(baseTable, baseTable.CheckConstraints, compareTable.CheckConstraints);
     Compare(baseTable, baseTable.ForeignKeys, compareTable.ForeignKeys);
 }

 public SqlWriterCommonTest(SqlType sqlType, DatabaseTable table, DbProviderFactory factory, string connectionString)
 {
     _connectionString = connectionString;
     _factory = factory;
     _table = table;
     _sqlType = sqlType;
 }