public void FindOracleAutoNumberTrigger()
        {
            //arrange
            var schema = new DatabaseSchema(null, SqlType.Oracle);
            var table = schema.AddTable("Test");
            var id = table.AddColumn<int>("Id").AddPrimaryKey();
            id.IsAutoNumber = true;
            table.AddColumn<string>("Name").AddLength(200);
            var databaseTrigger = new DatabaseTrigger
                                  {
                                      Name = "Test_INS_TRG",
                                      TriggerEvent = "INSERT",
                                      TriggerBody = @"BEGIN
  SELECT ""Test_SEQ"".NEXTVAL INTO :NEW.""Id"" FROM DUAL;
END;",
                                      TriggerType = "BEFORE EACH ROW",
                                  };
            table.Triggers.Add(databaseTrigger);
            var databaseSequence = new DatabaseSequence { IncrementBy = 1, MinimumValue = 0, Name = "Test_SEQ" };
            schema.Sequences.Add(databaseSequence);

            //act
            var result = OracleSequenceTrigger.FindTrigger(table);

            //assert
            Assert.IsNotNull(result);
            Assert.IsNotNull(result.DatabaseTrigger);
            Assert.IsNotNull(result.DatabaseSequence);
            Assert.AreEqual(databaseSequence, result.DatabaseSequence);
            Assert.AreEqual(databaseTrigger, result.DatabaseTrigger);
        }
 private void CreateResult(ResultType resultType, DatabaseSequence sequence, string script)
 {
     var result = new CompareResult
         {
             SchemaObjectType = SchemaObjectType.Sequence,
             ResultType = resultType,
             Name = sequence.Name,
             SchemaOwner = sequence.SchemaOwner,
             Script = script
         };
     _results.Add(result);
 }
        public void TestPostgreSqlColumnDefaults()
        {
            //MigrationGenerator does not output default values for columns
            //https://github.com/martinjw/dbschemareader/issues/11

            //arrange
            var gen = new DdlGeneratorFactory(SqlType.PostgreSql).MigrationGenerator();
            gen.IncludeSchema = false;

            var pkSeqName = "Seq_PK_Generator";
            var pkSeq = new DatabaseSequence()
            {
                Name = pkSeqName,
                MinimumValue = 1,
                IncrementBy = 1,
            };

            //gen.AddSequence(pkSeq).Replace(";", " CACHE;");

            var newTable = new DatabaseTable { Name = "TestTable" };

            var idColumn = newTable.AddColumn("Id", DbType.Int64);
            idColumn.AddPrimaryKey("PK_TestTable");
            idColumn.DefaultValue = $"nextval('{pkSeqName}')";
            var summaryColumn = newTable.AddColumn("Summary", DbType.String);
            summaryColumn.Length = 100;

            //act
            var ddl = gen.AddTable(newTable);

            //assert

            //expected
            /*
            CREATE SEQUENCE "Seq_PK_Generator" INCREMENT BY 1 MINVALUE 1 CACHE;

            CREATE TABLE "TestTable"
            (
              "Id" BIGINT NOT NULL, --default value missing
              "Summary" VARCHAR (100)  NOT NULL
            );
            ALTER TABLE "TestTable" ADD CONSTRAINT "PK_TestTable" PRIMARY KEY ("Id");
             */

            Assert.IsTrue(ddl.IndexOf("BIGINT NOT NULL DEFAULT nextval('Seq_PK_Generator'),", StringComparison.OrdinalIgnoreCase) != -1, "default value should be included");
        }
        public static List<DatabaseSequence> Sequences(DataTable dt)
        {
            List<DatabaseSequence> list = new List<DatabaseSequence>();

            var sequenceKeyMap = new SequenceKeyMap(dt);

            foreach (DataRow row in dt.Rows)
            {
                DatabaseSequence seq = new DatabaseSequence();
                seq.Name = row[sequenceKeyMap.Key].ToString();
                seq.SchemaOwner = row[sequenceKeyMap.OwnerKey].ToString();
                if (!string.IsNullOrEmpty(sequenceKeyMap.MinValueKey))
                    seq.MinimumValue = GetNullableDecimal(row[sequenceKeyMap.MinValueKey]);
                if (!string.IsNullOrEmpty(sequenceKeyMap.MaxValueKey))
                    seq.MaximumValue = GetNullableDecimal(row[sequenceKeyMap.MaxValueKey]);
                if (!string.IsNullOrEmpty(sequenceKeyMap.IncrementKey))
                    seq.IncrementBy = GetNullableInt(row[sequenceKeyMap.IncrementKey]) ?? 1;
                list.Add(seq);
            }
            return list;
        }
 public string AddSequence(DatabaseSequence sequence)
 {
     //amazingly SQLServer Denali has the same syntax as Oracle. http://msdn.microsoft.com/en-us/library/ff878091%28v=SQL.110%29.aspx
     var sb = new StringBuilder();
     sb.Append("CREATE SEQUENCE " + SchemaPrefix(sequence.SchemaOwner) + Escape(sequence.Name) +
         " INCREMENT BY " + sequence.IncrementBy);
     //min/max are optional- if they look like defaults, no need to write them out
     if (sequence.MinimumValue.HasValue && sequence.MinimumValue != 0)
     {
         sb.Append(" MINVALUE " + sequence.MinimumValue);
     }
     if (sequence.MaximumValue.HasValue && sequence.MaximumValue != 999999999999999999999999999M)
     {
         sb.Append(" MAXVALUE " + sequence.MaximumValue);
     }
     sb.Append(";");
     return sb.ToString();
 }
 public string DropSequence(DatabaseSequence sequence)
 {
     return "DROP SEQUENCE " + SchemaPrefix(sequence.SchemaOwner) + Escape(sequence.Name) + ";";
 }
 public string AddSequence(DatabaseSequence sequence)
 {
     return _migration.AddSequence(sequence);
 }
 public string DropSequence(DatabaseSequence sequence)
 {
     return _migration.DropSequence(sequence);
 }