public static CreateTableExpression GetCreateTableWithAutoIncrementExpression()
 {
     var expression = new CreateTableExpression { TableName = TestTableName1 };
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, IsIdentity = true, Type = DbType.Int32 });
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32 });
     return expression;
 }

 public static CreateTableExpression GetCreateTableExpression()
 {
     CreateTableExpression expression = new CreateTableExpression() { TableName = TestTableName1, };
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, Type = DbType.String });
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32 });
     return expression;
 }

        public void ErrorIsReturnedWhenTableNameIsEmptyString()
        {
            var expression = new CreateTableExpression { TableName = String.Empty };

            var errors = ValidationHelper.CollectErrors(expression);
            errors.ShouldContain(ErrorMessages.TableNameCannotBeNullOrEmpty);
        }

        public void CanMigrateTableWithIdentityData()
        {
            // Arrange
              var create = new CreateTableExpression
              {
              TableName = "Foo"
             ,
              Columns = new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32, IsIdentity = true} }
              };
              var context = GetDefaultContext();

              var insert = new InsertDataExpression()
              {
              TableName = "Foo"
              };
              insert.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 1)});
              insert.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 100) });
              insert.WithIdentity = true;

              // Act
              MigrateTable(context, create, insert);

              // Assert
              context.MigrationIndex.ShouldBe(1);
        }

        public void ErrorIsNotReturnedWhenTableNameIsSet()
        {
            var expression = new CreateTableExpression { TableName = "table1" };

            var errors = ValidationHelper.CollectErrors(expression);
            Assert.That(errors.Count, Is.EqualTo(0));
        }

 public static CreateTableExpression GetCreateTableWithNullableColumn()
 {
     var expression = new CreateTableExpression { TableName = TestTableName1 };
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, IsNullable = true, Type = DbType.String });
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32 });
     return expression;
 }

        public string Generate(CreateTableExpression expression)
        {
            IList<ColumnDefinition> columns = expression.Columns;
            string result = "";
            int total = columns.Count - 1;

            //if more than one column is a primary key or the primary key is given a name, then it needs to be added separately
            IList<ColumnDefinition> primaryKeyColumns = GetPrimaryKeyColumns(columns);
            bool addPrimaryKeySeparately = false;
            if (primaryKeyColumns.Count > 1 || (primaryKeyColumns.Count == 1 && !string.IsNullOrEmpty(primaryKeyColumns[0].PrimaryKeyName)))
            {
                if(CanSeperatePrimaryKeyAndIdentity || primaryKeyColumns.All(x => !x.IsIdentity))
                {
                    addPrimaryKeySeparately = true;
                    foreach (ColumnDefinition column in primaryKeyColumns)
                    {
                        column.IsPrimaryKey = false;
                    }
                }
            }

            for (int i = 0; i < columns.Count; i++)
            {
                result += Generate(columns[i]);

                if (i != total)
                    result += ", ";
            }

            if (addPrimaryKeySeparately)
                result = AddPrimaryKeyConstraint(expression.TableName, primaryKeyColumns, result);

            return result;
        }

 public static CreateTableExpression GetCreateTableWithNamedMultiColumnPrimaryKeyExpression()
 {
     var expression = new CreateTableExpression { TableName = TestTableName1 };
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, IsPrimaryKey = true, PrimaryKeyName = "TestKey", Type = DbType.String });
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32, IsPrimaryKey = true });
     return expression;
 }

 public static CreateTableExpression GetCreateTableWithDefaultValue()
 {
     CreateTableExpression expression = new CreateTableExpression() { TableName = TestTableName1, };
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, Type = DbType.String, DefaultValue = "Default", TableName = TestTableName1 });
     expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32, DefaultValue = 0, TableName = TestTableName1 });
     return expression;
 }

        private CreateTableExpression GetCreateTableExpression(string tableName)
        {
            var columnName1 = "ColumnName1";

            var column1 = new ColumnDefinition { Name = columnName1, Type = DbType.String };

            var expression = new CreateTableExpression { TableName = tableName };
            expression.Columns.Add(column1);
            return expression;
        }

        public dynamic CreateTableMigration(CreateTableMigration createTableMigration)
        {
            var create_table_expr = new CreateTableExpression
                                        {
                                            TableName = createTableMigration.TableName,
                                            SchemaName = createTableMigration.SchemaName,
                                        };
            create_table_expr.Columns = createTableMigration.Colunns.Select(build_column_definition).ToList();

            return create_table_expr;
        }

        public void CanCreateTableWithDateTimeOffsetColumn()
        {
            var expression = new CreateTableExpression {TableName = "TestTable1"};
            expression.Columns.Add(new ColumnDefinition {TableName = "TestTable1", Name = "TestColumn1", Type = DbType.DateTimeOffset});
            expression.Columns.Add(new ColumnDefinition {TableName = "TestTable1", Name = "TestColumn2", Type = DbType.DateTime2});
            expression.Columns.Add(new ColumnDefinition {TableName = "TestTable1", Name = "TestColumn3", Type = DbType.Date});
            expression.Columns.Add(new ColumnDefinition { TableName = "TestTable1", Name = "TestColumn4", Type = DbType.Time });

            var result = Generator.Generate(expression);
            result.ShouldBe("CREATE TABLE [dbo].[TestTable1] ([TestColumn1] DATETIMEOFFSET NOT NULL, [TestColumn2] DATETIME2 NOT NULL, [TestColumn3] DATE NOT NULL, [TestColumn4] TIME NOT NULL)");
        }

        public override string Generate(CreateTableExpression expression)
        {
            string FederatedOn = "";
             if (!(string.IsNullOrEmpty(expression.Federation.DistributionName) || string.IsNullOrEmpty(expression.FederationColumn)))
             {
            FederatedOn = string.Format(FederateTable, expression.Federation.DistributionName,
                                         expression.FederationColumn);
             }

             return Wrap(string.Format("{0} {1}", base.Generate(expression), FederatedOn));
        }

        public override string Generate(CreateTableExpression expression)
        {
            var descriptionStatements = DescriptionGenerator.GenerateDescriptionStatements(expression);
            var createTableStatement = string.Format("CREATE TABLE {0}.{1}", Quoter.QuoteSchemaName(expression.SchemaName), base.Generate(expression));
            var descriptionStatementsArray = descriptionStatements as string[] ?? descriptionStatements.ToArray();

            if (!descriptionStatementsArray.Any())
                return createTableStatement;

            return ComposeStatements(createTableStatement, descriptionStatementsArray);
        }

        public void CanCreateTableExpression()
        {
            CreateTableExpression expression = new CreateTableExpression { TableName = tableName };
            expression.Columns.Add(column.Object);

            using (command)
            {
                processor.Process(expression);
                command.CommandText = string.Format("SELECT name FROM sqlite_master WHERE type='table' and name='{0}'", tableName);
                command.ExecuteReader().Read().ShouldBeTrue();
            }
        }

        /// <summary>
        /// Outputs a create table string
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public override string Generate(CreateTableExpression expression)
        {
            if (string.IsNullOrEmpty(expression.TableName)) throw new ArgumentNullException("expression", "expression.TableName cannot be empty");
            if (expression.Columns.Count == 0) throw new ArgumentException("You must specifiy at least one column");

            string quotedTableName = Quoter.QuoteTableName(expression.TableName);

            string errors = ValidateAdditionalFeatureCompatibility(expression.Columns.SelectMany(x => x.AdditionalFeatures));
            if (!string.IsNullOrEmpty(errors)) return errors;

            return string.Format(CreateTable, quotedTableName, Column.Generate(expression.Columns, quotedTableName));
        }

        public void CanCreateTableWithDateTimeOffsetColumn() 
        {
            var tableName = "TestTable1";
            var expression = new CreateTableExpression { TableName = tableName };
            expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn1", Type = DbType.DateTimeOffset });
            expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn2", Type = DbType.DateTime2 });
            expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn3", Type = DbType.Date });
            expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn4", Type = DbType.Time });

            var result = Generator.Generate(expression);
            result.ShouldBe(string.Format("CREATE TABLE \"public\".\"{0}\" (\"TestColumn1\" timestamptz NOT NULL, \"TestColumn2\" timestamp NOT NULL, \"TestColumn3\" date NOT NULL, \"TestColumn4\" time NOT NULL);", tableName));
        }

 public override string Generate(CreateTableExpression expression)
 {
     var createStatement = new StringBuilder();
     var tableName = Quoter.QuoteTableName(expression.TableName);
     createStatement.Append(string.Format("CREATE TABLE {0}.{1} ({2})", Quoter.QuoteSchemaName(expression.SchemaName), tableName, Column.Generate(expression.Columns, tableName)));
     var descriptionStatement = DescriptionGenerator.GenerateDescriptionStatements(expression);
     if (descriptionStatement != null && descriptionStatement.Any())
     {
         createStatement.Append(";");
         createStatement.Append(string.Join(";", descriptionStatement.ToArray()));
     }
     return createStatement.ToString();
 }

        public void CanMigrateTable()
        {
            // Arrange
             var create = new CreateTableExpression
                         {
                            TableName = "Foo"
                            ,Columns = new List<ColumnDefinition> {new ColumnDefinition {Name = "Id", Type = DbType.Int32}}
                         };
             var context = GetDefaultContext();

             // Act
             MigrateTable(context, create);

             // Assert
             context.MigrationIndex.ShouldBe(1);
        }

        public void CanReadLongViewDefinition()
        {
            // Arrange
             var create = new CreateTableExpression
             {
            TableName = "Foo",
            Columns =
               new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32 } }
             };

             IList<ViewDefinition> views;

             var createSql = new StringBuilder();
             createSql.Append("CREATE VIEW FooView As SELECT Id,");
             createSql.Append("'");
             createSql.Append(new string('A', 3000));
             createSql.Append("'");
             createSql.Append(" As LongText1,");
             createSql.Append("'");
             createSql.Append(new string('B', 3000));
             createSql.Append("'");
             createSql.Append(" As LongText2");
             createSql.Append(" FROM Foo");

             // Act
             using (var connection = new SqlConnection(ConnectionString))
             {
            var processor = new SqlServerProcessor(connection, new SqlServer2005Generator(), new DebugAnnouncer(), new ProcessorOptions());

            processor.Process(create);

            Assert.IsTrue(processor.TableExists(string.Empty, create.TableName), "SqlServer");

            processor.Execute(createSql.ToString());

            var dumper = new SqlServerSchemaDumper(processor, new DebugAnnouncer());
            views = dumper.ReadViewSchema();

            processor.CommitTransaction();
             }

             // Assert

             Assert.AreEqual(1, views.Count);
        }