require_relative "spec_helper"

[nil, :postgres].each do |database_type|
  describe "DB#{"(#{database_type})" if database_type}" do
    before do
      if database_type == :postgres
        @db = Sequel.mock(:host=>"postgres")
        @db.extension :identifier_mangling
        @db.quote_identifiers = false
      else
        @db = Sequel.mock
      end
    end

    def expected_sqls(sqls, adapter_sqls=Sequel::OPTS)
      adapter_sqls.fetch(@db.database_type, sqls)
    end
      
    describe "#create_table" do
      it "should accept the table name" do
        @db.create_table(:cats){}.must_be_nil
        @db.sqls.must_equal ['CREATE TABLE cats ()']
      end

      it "should accept an identifier table name" do
        @db.create_table(Sequel[:cats]){}.must_be_nil
        @db.sqls.must_equal ['CREATE TABLE cats ()']
      end

      it "should accept a qualified table name" do
        @db.create_table(Sequel[:s][:cats]){}.must_be_nil
        @db.sqls.must_equal ['CREATE TABLE s.cats ()']
      end

      it "should accept a multi-level qualified table name" do
        @db.create_table(Sequel[:s][:t][:cats]){}.must_be_nil
        @db.sqls.must_equal ['CREATE TABLE s.t.cats ()']
      end

      with_symbol_splitting "should accept the table name with splittable symbols" do
        @db.create_table(:cats__cats) {}
        @db.sqls.must_equal ['CREATE TABLE cats.cats ()']
      end

      it "should accept the table name in multiple formats" do
        @db.create_table(Sequel[:cats][:cats]) {}
        @db.create_table("cats__cats1") {}
        @db.create_table(Sequel.identifier(:cats__cats2)) {}
        @db.create_table(Sequel.qualify(:cats3, :cats)) {}
        @db.sqls.must_equal ['CREATE TABLE cats.cats ()', 'CREATE TABLE cats__cats1 ()', 'CREATE TABLE cats__cats2 ()', 'CREATE TABLE cats3.cats ()']
      end

      it "should raise an error if the table name argument is not valid" do
        proc{@db.create_table(1) {}}.must_raise(Sequel::Error)
        proc{@db.create_table(Sequel.as(:cats, :c)) {}}.must_raise(Sequel::Error)
      end

      it "should remove cached schema entry" do
        @db.instance_variable_set(:@schemas, {'cats'=>[]})
        @db.create_table(:cats){Integer :a}
        @db.instance_variable_get(:@schemas).must_be :empty?
      end
      
      it "should accept multiple columns" do
        @db.create_table(:cats) do
          column :id, :integer
          column :name, :text
        end
        @db.sqls.must_equal ['CREATE TABLE cats (id integer, name text)']
      end
      
      it "should accept method calls as data types" do
        @db.create_table(:cats) do
          integer :id
          text :name
        end
        @db.sqls.must_equal ['CREATE TABLE cats (id integer, name text)']
      end
      
      it "should transform types given as ruby classes to database-specific types" do
        @db.create_table(:cats) do
          String :a
          Integer :b
          Fixnum :c
          Bignum :d
          Float :e
          BigDecimal :f
          Date :g
          DateTime :h
          Time :i
          Numeric :j
          File :k
          TrueClass :l
          FalseClass :m
          column :n, Integer
          primary_key :o, :type=>String
          foreign_key :p, :f, :type=>Date
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (o varchar(255) PRIMARY KEY AUTOINCREMENT, a varchar(255), b integer, c integer, d bigint, e double precision, f numeric, g date, h timestamp, i timestamp, j numeric, k blob, l boolean, m boolean, n integer, p date REFERENCES f)'],
          :postgres => [
            "CREATE TABLE cats (o text GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, a text, b integer, c integer, d bigint, e double precision, f numeric, g date, h timestamp, i timestamp, j numeric, k bytea, l boolean, m boolean, n integer, p date REFERENCES f)"
          ]
        )
      end

      it "should transform types given as ruby classes to database-specific types" do
        @db.default_string_column_size = 50
        @db.create_table(:cats) do
          String :a
          String :a2, :size=>13
          String :a3, :fixed=>true
          String :a4, :size=>13, :fixed=>true
          String :a5, :text=>true
          varchar :a6
          varchar :a7, :size=>13
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (a varchar(50), a2 varchar(13), a3 char(50), a4 char(13), a5 text, a6 varchar(50), a7 varchar(13))'],
          :postgres => ['CREATE TABLE cats (a text, a2 varchar(13), a3 char(50), a4 char(13), a5 text, a6 varchar(50), a7 varchar(13))']
        )
      end

      it "should allow the use of modifiers with ruby class types" do
        @db.create_table(:cats) do
          String :a, :size=>50
          String :b, :text=>true
          String :c, :fixed=>true, :size=>40
          Time :d, :only_time=>true
          BigDecimal :e, :size=>[11,2]
        end
        @db.sqls.must_equal ['CREATE TABLE cats (a varchar(50), b text, c char(40), d time, e numeric(11, 2))']
      end

      it "should use clob type for String columns if database uses clob for text" do
        @db.extend(Module.new{private; def uses_clob_for_text?; true; end})
        @db.create_table(:cats) do
          String :b, :text=>true
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (b clob)'],
          :postgres => ['CREATE TABLE cats (b text)']
        )
      end

      it "should allow the use of modifiers with ruby class types" do
        c = Class.new
        def c.name; 'Fixnum'; end
        @db.create_table(:cats) do
          column :a, c
        end
        @db.sqls.must_equal ['CREATE TABLE cats (a integer)']
      end

      it "should raise an error if you use a ruby class that isn't handled" do
        proc{@db.create_table(:cats){column :a, Class}}.must_raise(Sequel::Error)
      end

      it "should accept primary key definition" do
        @db.create_table(:cats) do
          primary_key :id
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id integer PRIMARY KEY AUTOINCREMENT)'],
          :postgres => ['CREATE TABLE cats (id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY)']
        )

        @db.create_table(:cats) do
          primary_key :id, :serial, :auto_increment => false
        end
        @db.sqls.must_equal ['CREATE TABLE cats (id serial PRIMARY KEY)']

        @db.create_table(:cats) do
          primary_key :id, :type => :serial, :auto_increment => false
        end
        @db.sqls.must_equal ['CREATE TABLE cats (id serial PRIMARY KEY)']

        @db.create_table(:cats) do
          Integer :a
          primary_key :id
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id integer PRIMARY KEY AUTOINCREMENT, a integer)'],
          :postgres => ['CREATE TABLE cats (id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, a integer)']
        )

        @db.create_table(:cats) do
          Integer :a
          primary_key :id, :Bignum
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id bigint PRIMARY KEY AUTOINCREMENT, a integer)'],
          :postgres => ['CREATE TABLE cats (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, a integer)']
        )

        @db.create_table(:cats) do
          Integer :a
          primary_key :id, :keep_order=>true
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (a integer, id integer PRIMARY KEY AUTOINCREMENT)'],
          :postgres => ['CREATE TABLE cats (a integer, id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY)']
        )
      end

      it "should allow naming primary key constraint with :primary_key_constraint_name option" do
        @db.create_table(:cats) do
          primary_key :id, :primary_key_constraint_name=>:foo
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id integer CONSTRAINT foo PRIMARY KEY AUTOINCREMENT)'],
          :postgres => ['CREATE TABLE cats (id integer GENERATED BY DEFAULT AS IDENTITY CONSTRAINT foo PRIMARY KEY)']
        )
      end

      it "should automatically set primary key column NOT NULL if database doesn't do it automatically" do
        def @db.can_add_primary_key_constraint_on_nullable_columns?; false end
        @db.create_table(:cats) do
          primary_key :id
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id integer NOT NULL PRIMARY KEY AUTOINCREMENT)'],
          :postgres => ['CREATE TABLE cats (id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY)']
        )
      end

      it "should automatically set primary key column NOT NULL when adding constraint if database doesn't do it automatically" do
        def @db.can_add_primary_key_constraint_on_nullable_columns?; false end
        @db.create_table(:cats) do
          String :id
          primary_key [:id]
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id varchar(255) NOT NULL, PRIMARY KEY (id))'],
          :postgres => ['CREATE TABLE cats (id text NOT NULL, PRIMARY KEY (id))']
        )
      end

      it "should handle case where the primary key column cannot be found when adding NOT NULL constraint if database doesn't do it automatically" do
        def @db.can_add_primary_key_constraint_on_nullable_columns?; false end
        @db.create_table(:cats) do
          String Sequel[:id]
          primary_key [:id]
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id varchar(255), PRIMARY KEY (id))'],
          :postgres => ['CREATE TABLE cats (id text, PRIMARY KEY (id))']
        )
      end

      it "should handling splitting named column constraints into table constraints if unsupported" do
        def @db.supports_named_column_constraints?; false end
        @db.create_table(:cats) do
          primary_key :id, :primary_key_constraint_name=>:foo
          foreign_key :cat_id, :cats, :unique=>true, :unique_constraint_name=>:bar, :foreign_key_constraint_name=>:baz, :deferrable=>true, :key=>:foo_id, :on_delete=>:cascade, :on_update=>:restrict
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id integer AUTOINCREMENT, cat_id integer, CONSTRAINT foo PRIMARY KEY (id), CONSTRAINT baz FOREIGN KEY (cat_id) REFERENCES cats(foo_id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED, CONSTRAINT bar UNIQUE (cat_id))'],
          :postgres => [
            "CREATE TABLE cats (id integer GENERATED BY DEFAULT AS IDENTITY, cat_id integer, CONSTRAINT foo PRIMARY KEY (id), CONSTRAINT baz FOREIGN KEY (cat_id) REFERENCES cats(foo_id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED, CONSTRAINT bar UNIQUE (cat_id))"
          ]
        )
      end

      it "should handling splitting named column constraints into table constraints if unsupported using :primary_key and :unique options with hash values" do
        def @db.supports_named_column_constraints?; false end
        @db.create_table(:cats) do
          primary_key :id, :primary_key=>{:name=>:foo, :deferrable=>true}
          foreign_key :cat_id, :cats, :unique=>{:name=>:bar, :deferrable=>true}, :foreign_key_constraint_name=>:baz, :deferrable=>true, :key=>:foo_id, :on_delete=>:cascade, :on_update=>:restrict
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (id integer AUTOINCREMENT, cat_id integer, CONSTRAINT foo PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT baz FOREIGN KEY (cat_id) REFERENCES cats(foo_id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED, CONSTRAINT bar UNIQUE (cat_id) DEFERRABLE INITIALLY DEFERRED)'],
          :postgres => [
            'CREATE TABLE cats (id integer GENERATED BY DEFAULT AS IDENTITY, cat_id integer, CONSTRAINT foo PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT baz FOREIGN KEY (cat_id) REFERENCES cats(foo_id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED, CONSTRAINT bar UNIQUE (cat_id) DEFERRABLE INITIALLY DEFERRED)'
          ]
        )
      end

      it "should accept and literalize default values" do
        @db.create_table(:cats) do
          integer :id, :default => 123
          text :name, :default => "abc'def"
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer DEFAULT 123, name text DEFAULT 'abc''def')"]
      end
      
      it "should accept not null definition" do
        @db.create_table(:cats) do
          integer :id
          text :name, :null => false
          text :name2, :allow_null => false
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer, name text NOT NULL, name2 text NOT NULL)"]
      end
      
      it "should accept null definition" do
        @db.create_table(:cats) do
          integer :id
          text :name, :null => true
          text :name2, :allow_null => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer, name text NULL, name2 text NULL)"]
      end
      
      it "should accept unique definition" do
        @db.create_table(:cats) do
          integer :id
          text :name, :unique => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer, name text UNIQUE)"]
      end
      
      it "should allow naming unique constraint with :unique_constraint_name option" do
        @db.create_table(:cats) do
          text :name, :unique => true, :unique_constraint_name=>:foo
        end
        @db.sqls.must_equal ["CREATE TABLE cats (name text CONSTRAINT foo UNIQUE)"]
      end
      
      it "should handle not deferred unique constraints" do
        @db.create_table(:cats) do
          integer :id
          text :name
          unique :name, :deferrable=>false
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer, name text, UNIQUE (name) NOT DEFERRABLE)"]
      end
      
      it "should handle deferred unique constraints" do
        @db.create_table(:cats) do
          integer :id
          text :name
          unique :name, :deferrable=>true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer, name text, UNIQUE (name) DEFERRABLE INITIALLY DEFERRED)"]
      end
      
      it "should handle deferred initially immediate unique constraints" do
        @db.create_table(:cats) do
          integer :id
          text :name
          unique :name, :deferrable=>:immediate
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer, name text, UNIQUE (name) DEFERRABLE INITIALLY IMMEDIATE)"]
      end
      
      it "should handle deferred unique column constraints" do
        @db.create_table(:cats) do
          integer :id, :unique=>true, :unique_deferrable=>true
          integer :i, :unique=>true, :unique_deferrable=>:immediate
          integer :j, :unique=>true, :unique_deferrable=>false
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer UNIQUE DEFERRABLE INITIALLY DEFERRED, i integer UNIQUE DEFERRABLE INITIALLY IMMEDIATE, j integer UNIQUE NOT DEFERRABLE)"]
      end
      
      it "should handle deferred primary key column constraints" do
        @db.create_table(:cats) do
          integer :id, :primary_key=>true, :primary_key_deferrable=>true
          integer :i, :primary_key=>true, :primary_key_deferrable=>:immediate
          integer :j, :primary_key=>true, :primary_key_deferrable=>false
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, i integer PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE, j integer PRIMARY KEY NOT DEFERRABLE)"]
      end
      
      it "should handle :unique option with hash value for options for unique constraint" do
        @db.create_table(:cats) do
          integer :id, :unique=>{:name=>:u1, :deferrable=>true}
          integer :i, :unique=>{:name=>:u2, :deferrable=>:immediate}
          integer :j, :unique=>{:deferrable=>false}
          integer :k, :unique=>{:name=>:u3}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer CONSTRAINT u1 UNIQUE DEFERRABLE INITIALLY DEFERRED, i integer CONSTRAINT u2 UNIQUE DEFERRABLE INITIALLY IMMEDIATE, j integer UNIQUE NOT DEFERRABLE, k integer CONSTRAINT u3 UNIQUE)"]
      end
      
      it "should handle :primary_key option with hash value for options for primary key constraint" do
        @db.create_table(:cats) do
          integer :id, :primary_key=>{:name=>:p1, :deferrable=>true}
          integer :i, :primary_key=>{:name=>:p2, :deferrable=>:immediate}
          integer :j, :primary_key=>{:deferrable=>false}
          integer :k, :primary_key=>{:name=>:p3}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer CONSTRAINT p1 PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, i integer CONSTRAINT p2 PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE, j integer PRIMARY KEY NOT DEFERRABLE, k integer CONSTRAINT p3 PRIMARY KEY)"]
      end
      
      it "should accept unsigned definition" do
        @db.create_table(:cats) do
          integer :value, :unsigned => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (value integer UNSIGNED)"]
      end
      
      it "should accept [SET|ENUM](...) types" do
        @db.create_table(:cats) do
          set :color, :elements => ['black', 'tricolor', 'grey']
        end
        @db.sqls.must_equal ["CREATE TABLE cats (color set('black', 'tricolor', 'grey'))"]
      end
      
      it "should accept varchar size" do
        @db.create_table(:cats) do
          varchar :name
        end
        @db.sqls.must_equal ["CREATE TABLE cats (name varchar(255))"]
        @db.create_table(:cats) do
          varchar :name, :size => 51
        end
        @db.sqls.must_equal ["CREATE TABLE cats (name varchar(51))"]
      end
      
      it "should use double precision for double type" do
        @db.create_table(:cats) do
          double :name
        end
        @db.sqls.must_equal ["CREATE TABLE cats (name double precision)"]
      end

      it "should accept foreign keys without options" do
        @db.create_table(:cats) do
          foreign_key :project_id
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer)"]
      end

      it "should accept foreign keys with options" do
        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects)"]
      end

      it "should accept foreign keys with separate table argument" do
        @db.create_table(:cats) do
          foreign_key :project_id, :projects, :default=>3
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer DEFAULT 3 REFERENCES projects)"]
      end
      
      it "should allowing naming foreign key constraint with :foreign_key_constraint_name option" do
        @db.create_table(:cats) do
          foreign_key :project_id, :projects, :foreign_key_constraint_name=>:foo
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer CONSTRAINT foo REFERENCES projects)"]
      end
      
      it "should raise an error if the table argument to foreign_key isn't a hash, symbol, or nil" do
        proc{@db.create_table(:cats){foreign_key :project_id, Object.new, :default=>3}}.must_raise(Sequel::Error)
      end
      
      it "should accept foreign keys with arbitrary keys" do
        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :key => :id
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects(id))"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :key => :zzz
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects(zzz))"]
      end
      
      it "should accept foreign keys with ON DELETE clause" do
        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :restrict
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE RESTRICT)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :cascade
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE CASCADE)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :no_action
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE NO ACTION)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :set_null
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE SET NULL)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :set_default
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE SET DEFAULT)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => 'NO ACTION FOO'
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE NO ACTION FOO)"]
      end

      it "should accept foreign keys with ON UPDATE clause" do
        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_update => :restrict
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON UPDATE RESTRICT)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_update => :cascade
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON UPDATE CASCADE)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_update => :no_action
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON UPDATE NO ACTION)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_update => :set_null
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON UPDATE SET NULL)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_update => :set_default
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON UPDATE SET DEFAULT)"]

        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_update => 'SET DEFAULT FOO'
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON UPDATE SET DEFAULT FOO)"]
      end
      
      it "should accept foreign keys with deferrable option" do
        @db.create_table(:cats) do
          foreign_key :project_id, :projects, :deferrable=>true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects DEFERRABLE INITIALLY DEFERRED)"]
      end

      it "should accept collation" do
        @db.create_table(:cats) do
          String :name, :collate => :utf8_bin
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (name varchar(255) COLLATE utf8_bin)'],
          :postgres => ['CREATE TABLE cats (name text COLLATE utf8_bin)']
        )
      end

      it "should accept collation as a String, treated literally" do
        @db.create_table(:cats) do
          String :name, :collate => '"utf8_bin"'
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats (name varchar(255) COLLATE "utf8_bin")'],
          :postgres => ['CREATE TABLE cats (name text COLLATE "utf8_bin")']
        )
      end

      it "should accept inline index definition" do
        @db.create_table(:cats) do
          integer :id, :index => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX cats_id_index ON cats (id)"]
      end
      
      it "should accept inline index definition for qualified table" do
        @db.create_table(Sequel[:sch][:cats]) do
          integer :id, :index => true
        end
        @db.sqls.must_equal ["CREATE TABLE sch.cats (id integer)", "CREATE INDEX sch_cats_id_index ON sch.cats (id)"]
      end
      
      it "should accept inline index definition with a hash of options" do
        @db.create_table(:cats) do
          integer :id, :index => {:unique=>true}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE UNIQUE INDEX cats_id_index ON cats (id)"]
      end
      
      it "should accept inline index definition for foreign keys" do
        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :cascade, :index => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE CASCADE)",
          "CREATE INDEX cats_project_id_index ON cats (project_id)"]
      end
      
      it "should accept inline index definition for foreign keys with a hash of options" do
        @db.create_table(:cats) do
          foreign_key :project_id, :table => :projects, :on_delete => :cascade, :index => {:unique=>true}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (project_id integer REFERENCES projects ON DELETE CASCADE)",
          "CREATE UNIQUE INDEX cats_project_id_index ON cats (project_id)"]
      end
      
      it "should accept index definitions" do
        @db.create_table(:cats) do
          integer :id
          index :id
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX cats_id_index ON cats (id)"]
      end

      it "should accept unique constraint definitions" do
        @db.create_table(:cats) do
          text :name
          unique :name
        end
        @db.sqls.must_equal ["CREATE TABLE cats (name text, UNIQUE (name))"]
      end

      it "should accept partial index definitions" do
        def @db.supports_partial_indexes?() true end
        @db.create_table(:cats) do
          integer :id
          index :id, :where=>proc{id > 1}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX cats_id_index ON cats (id) WHERE (id > 1)"]
      end

      it "should not raise on index error for unsupported index definitions if ignore_index_errors is used" do
        @db.create_table(:cats, :ignore_index_errors=>true) do
          text :name
          full_text_index :name
        end
      end

      if database_type == :postgres
        it "should allow full-text index definitions" do
          @db.create_table(:cats) do
            text :name
            full_text_index :name
          end
          @db.sqls.must_equal ["CREATE TABLE cats (name text)",
            "CREATE INDEX cats_name_index ON cats USING gin (to_tsvector('simple'::regconfig, (COALESCE(name, ''))))"]
        end

        it "should allow spatial index definitions" do
          @db.create_table(:cats) do
            point :geom
            spatial_index :geom
          end
          @db.sqls.must_equal ["CREATE TABLE cats (geom point)",
            "CREATE INDEX cats_geom_index ON cats USING gist (geom)"]
        end

        it "should allow partial index definitions" do
          @db.create_table(:cats) do
            text :name
            index :name, :where => {:something => true}
          end
          @db.sqls.must_equal ["CREATE TABLE cats (name text)",
            "CREATE INDEX cats_name_index ON cats (name) WHERE (something IS TRUE)"]
        end

        it "should allow index definitions with type" do
          @db.create_table(:cats) do
            text :name
            index :name, :type => :hash
          end
          @db.sqls.must_equal ["CREATE TABLE cats (name text)",
            "CREATE INDEX cats_name_index ON cats USING hash (name)"]
        end
      else
        it "should raise on full-text index definitions" do
          proc {
            @db.create_table(:cats) do
              text :name
              full_text_index :name
            end
          }.must_raise(Sequel::Error)
        end

        it "should raise on spatial index definitions" do
          proc {
            @db.create_table(:cats) do
              point :geom
              spatial_index :geom
            end
          }.must_raise(Sequel::Error)
        end

        it "should raise on partial index definitions" do
          proc {
            @db.create_table(:cats) do
              text :name
              index :name, :where => {:something => true}
            end
          }.must_raise(Sequel::Error)
        end

        it "should raise index definitions with type" do
          proc {
            @db.create_table(:cats) do
              text :name
              index :name, :type => :hash
            end
          }.must_raise(Sequel::Error)
        end
      end

      it "should ignore errors if the database raises an error on an index creation statement and the :ignore_index_errors option is used" do
        @db.define_singleton_method(:execute_ddl){|*a| raise Sequel::DatabaseError if /blah/ =~ a.first; super(*a)}
        lambda{@db.create_table(:cats){Integer :id; index :blah; index :id}}.must_raise(Sequel::DatabaseError)
        @db.sqls.must_equal ['CREATE TABLE cats (id integer)']
        @db.create_table(:cats, :ignore_index_errors=>true){Integer :id; index :blah; index :id}
        @db.sqls.must_equal ['CREATE TABLE cats (id integer)', 'CREATE INDEX cats_id_index ON cats (id)']
      end

      it "should not use savepoints around index creation if running inside a transaction if :ignore_index_errors option is used" do
        @db.define_singleton_method(:execute_ddl){|*a| super(*a); raise Sequel::DatabaseError if /blah/ =~ a.first}
        @db.transaction{@db.create_table(:cats, :ignore_index_errors=>true){Integer :id; index :blah; index :id}}
        @db.sqls.must_equal expected_sqls(
          ["BEGIN",
            "CREATE TABLE cats (id integer)",
            "CREATE INDEX cats_blah_index ON cats (blah)",
            "CREATE INDEX cats_id_index ON cats (id)",
            "COMMIT"],
          :postgres => [
            "BEGIN",
            "CREATE TABLE cats (id integer)",
            "SAVEPOINT autopoint_1",
            "CREATE INDEX cats_blah_index ON cats (blah)",
            "ROLLBACK TO SAVEPOINT autopoint_1",
            "SAVEPOINT autopoint_1",
            "CREATE INDEX cats_id_index ON cats (id)",
            "RELEASE SAVEPOINT autopoint_1",
            "COMMIT"
          ]
        )
      end

      it "should use savepoints around index creation if running inside a transaction if :ignore_index_errors option is used and transactional schema modifications are supported" do
        @db.define_singleton_method(:supports_transactional_ddl?){true}
        @db.define_singleton_method(:execute_ddl){|*a| super(*a); raise Sequel::DatabaseError if /blah/ =~ a.first}
        @db.transaction{@db.create_table(:cats, :ignore_index_errors=>true){Integer :id; index :blah; index :id}}
        @db.sqls.must_equal ["BEGIN", "CREATE TABLE cats (id integer)", "SAVEPOINT autopoint_1", "CREATE INDEX cats_blah_index ON cats (blah)", "ROLLBACK TO SAVEPOINT autopoint_1", "SAVEPOINT autopoint_1", "CREATE INDEX cats_id_index ON cats (id)", "RELEASE SAVEPOINT autopoint_1", "COMMIT"]
      end

      it "should accept multiple index definitions" do
        @db.create_table(:cats) do
          integer :id
          index :id
          index :name
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX cats_id_index ON cats (id)", "CREATE INDEX cats_name_index ON cats (name)"]
      end
      
      it "should accept functional indexes" do
        @db.create_table(:cats) do
          integer :id
          index Sequel.function(:lower, :name)
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX cats_lower_name__index ON cats (lower(name))"]
      end
      
      it "should accept indexes with identifiers" do
        @db.create_table(:cats) do
          integer :id
          index Sequel.identifier(:lower__name)
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX cats_lower__name_index ON cats (lower__name)"]
      end
      
      it "should accept custom index names" do
        @db.create_table(:cats) do
          integer :id
          index :id, :name => 'abc'
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE INDEX abc ON cats (id)"]
      end

      it "should accept unique index definitions" do
        @db.create_table(:cats) do
          integer :id
          index :id, :unique => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE UNIQUE INDEX cats_id_index ON cats (id)"]
      end
      
      it "should accept composite index definitions" do
        @db.create_table(:cats) do
          integer :id
          index [:id, :name], :unique => true
        end
        @db.sqls.must_equal ["CREATE TABLE cats (id integer)", "CREATE UNIQUE INDEX cats_id_name_index ON cats (id, name)"]
      end
      
      it "should accept unnamed constraint definitions with blocks" do
        @db.create_table(:cats) do
          integer :score
          check{(x > 0) & (y < 1)}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (score integer, CHECK ((x > 0) AND (y < 1)))"]
      end

      it "should accept unnamed constraint definitions with function calls" do
        @db.create_table(:cats) do
          integer :score
          check{f(x)}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (score integer, CHECK (f(x)))"]
      end

      it "should accept unnamed constraint definitions" do
        @db.create_table(:cats) do
          check 'price < ?', 100
        end
        @db.sqls.must_equal ["CREATE TABLE cats (CHECK (price < 100))"]
      end

      it "should accept arrays of pairs constraints" do
        @db.create_table(:cats) do
          check [[:price, 100]]
        end
        @db.sqls.must_equal ["CREATE TABLE cats (CHECK (price = 100))"]
      end

      it "should accept hash constraints" do
        @db.create_table(:cats) do
          check :price=>100
        end
        @db.sqls.must_equal ["CREATE TABLE cats (CHECK (price = 100))"]
      end

      it "should accept array constraints" do
        @db.create_table(:cats) do
          check [Sequel.expr(:x) > 0, Sequel.expr(:y) < 1]
        end
        @db.sqls.must_equal ["CREATE TABLE cats (CHECK ((x > 0) AND (y < 1)))"]
      end

      it "should accept expression constraints" do
        @db.create_table(:cats) do
          check Sequel.&(Sequel.expr(:x) > 0, Sequel.expr(:y) < 1)
        end
        @db.sqls.must_equal ["CREATE TABLE cats (CHECK ((x > 0) AND (y < 1)))"]
      end

      it "should accept named constraint definitions" do
        @db.create_table(:cats) do
          integer :score
          constraint :valid_score, 'score <= 100'
        end
        @db.sqls.must_equal ["CREATE TABLE cats (score integer, CONSTRAINT valid_score CHECK (score <= 100))"]
      end

      it "should accept named constraint definitions with options" do
        @db.create_table(:cats) do
          integer :score
          constraint({:name=>:valid_score, :deferrable=>true}, 'score <= 100')
        end
        @db.sqls.must_equal ["CREATE TABLE cats (score integer, CONSTRAINT valid_score CHECK (score <= 100) DEFERRABLE INITIALLY DEFERRED)"]
      end

      it "should accept named constraint definitions with block" do
        @db.create_table(:cats) do
          constraint(:blah_blah){(x.sql_number > 0) & (y.sql_number < 1)}
        end
        @db.sqls.must_equal ["CREATE TABLE cats (CONSTRAINT blah_blah CHECK ((x > 0) AND (y < 1)))"]
      end

      it "should raise an error if an invalid constraint type is used" do
        proc{@db.create_table(:cats){unique [:a, :b], :type=>:bb}}.must_raise(Sequel::Error)
      end

      it "should accept composite primary keys" do
        @db.create_table(:cats) do
          integer :a
          integer :b
          primary_key [:a, :b]
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, PRIMARY KEY (a, b))"]
      end

      it "should accept named composite primary keys" do
        @db.create_table(:cats) do
          integer :a
          integer :b
          primary_key [:a, :b], :name => :cpk
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, CONSTRAINT cpk PRIMARY KEY (a, b))"]
      end

      it "should accept composite foreign keys" do
        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc)"]
      end

      it "should accept named composite foreign keys" do
        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :name => :cfk
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, CONSTRAINT cfk FOREIGN KEY (a, b) REFERENCES abc)"]
      end

      it "should accept composite foreign keys with arbitrary keys" do
        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :key => [:real_a, :real_b]
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc(real_a, real_b))"]

        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :key => [:z, :x]
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc(z, x))"]
      end

      it "should accept composite foreign keys with on delete and on update clauses" do
        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :on_delete => :cascade
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc ON DELETE CASCADE)"]

        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :on_update => :no_action
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc ON UPDATE NO ACTION)"]

        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :on_delete => :restrict, :on_update => :set_default
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc ON DELETE RESTRICT ON UPDATE SET DEFAULT)"]

        @db.create_table(:cats) do
          integer :a
          integer :b
          foreign_key [:a, :b], :abc, :key => [:x, :y], :on_delete => :set_null, :on_update => :set_null
        end
        @db.sqls.must_equal ["CREATE TABLE cats (a integer, b integer, FOREIGN KEY (a, b) REFERENCES abc(x, y) ON DELETE SET NULL ON UPDATE SET NULL)"]
      end

      it "should accept an :as option to create a table from the results of a dataset" do
        @db.create_table(:cats, :as=>@db[:a])
        @db.sqls.must_equal ['CREATE TABLE cats AS SELECT * FROM a']
      end

      it "should accept an :as option to create a table from a SELECT string" do
        @db.create_table(:cats, :as=>'SELECT * FROM a')
        @db.sqls.must_equal ['CREATE TABLE cats AS SELECT * FROM a']
      end

      it "should raise an Error if both a block and an :as argument are given" do
        proc{@db.create_table(:cats, :as=>@db[:a]){}}.must_raise(Sequel::Error)
      end
    end

    describe "#create_table!" do
      it "should create the table if it does not exist" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.create_table!(:cats){|*a|}.must_be_nil
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats ()'],
          :postgres => ['DROP TABLE IF EXISTS cats', 'CREATE TABLE cats ()']
        )
      end
      
      it "should drop the table before creating it if it already exists" do
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.create_table!(:cats){|*a|}
        @db.sqls.must_equal expected_sqls(
          ['DROP TABLE cats',
            'CREATE TABLE cats ()'],
          :postgres => [
            'DROP TABLE IF EXISTS cats',
            'CREATE TABLE cats ()'
          ]
        )
      end
      
      it "should use IF EXISTS if the database supports it" do
        @db.define_singleton_method(:supports_drop_table_if_exists?){true}
        @db.create_table!(:cats){|*a|}
        @db.sqls.must_equal ['DROP TABLE IF EXISTS cats', 'CREATE TABLE cats ()']
      end
    end

    describe "#create_table?" do
      it "should not create the table if the table already exists" do
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.create_table?(:cats){|*a|}.must_be_nil
        @db.sqls.must_equal expected_sqls(
          [],
          :postgres => ['CREATE TABLE IF NOT EXISTS cats ()']
        )
      end
      
      it "should create the table if the table doesn't already exist" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.create_table?(:cats){|*a|}
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats ()'],
          :postgres => ['CREATE TABLE IF NOT EXISTS cats ()']
        )
      end
      
      it "should use IF NOT EXISTS if the database supports that" do
        @db.define_singleton_method(:supports_create_table_if_not_exists?){true}
        @db.create_table?(:cats){|*a|}
        @db.sqls.must_equal ['CREATE TABLE IF NOT EXISTS cats ()']
      end
      
      it "should not use IF NOT EXISTS if the indexes are created" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.define_singleton_method(:supports_create_table_if_not_exists?){true}
        @db.create_table?(:cats){|*a| Integer :a, :index=>true}
        @db.sqls.must_equal ['CREATE TABLE cats (a integer)', 'CREATE INDEX cats_a_index ON cats (a)']

        @db.singleton_class.send(:alias_method, :table_exists?, :table_exists?)
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.create_table?(:cats){|*a| Integer :a, :index=>true}
        @db.sqls.must_equal []
      end
    end

    describe "#create_join_table" do
      it "should take a hash with foreign keys and table name values" do
        @db.create_join_table(:cat_id=>:cats, :dog_id=>:dogs).must_be_nil
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']
      end
      
      it "should be able to have values be a hash of options" do
        @db.create_join_table(:cat_id=>{:table=>:cats, :null=>true}, :dog_id=>{:table=>:dogs, :default=>0})
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NULL REFERENCES cats, dog_id integer DEFAULT 0 NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']
      end
      
      it "should be able to pass a second hash of table options" do
        @db.create_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :temp=>true)
        @db.sqls.must_equal ['CREATE TEMPORARY TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']
      end
      
      it "should recognize :name option in table options" do
        @db.create_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :name=>:f)
        @db.sqls.must_equal ['CREATE TABLE f (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX f_dog_id_cat_id_index ON f (dog_id, cat_id)']
      end
      
      it "should recognize :index_options option in table options" do
        @db.create_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :index_options=>{:name=>:foo_index})
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX foo_index ON cats_dogs (dog_id, cat_id)']
      end
      
      it "should recognize :no_index option in table options" do
        @db.create_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :no_index=>true)
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))']
      end
      
      it "should recognize :no_primary_key option in table options" do
        @db.create_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :no_primary_key=>true)
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs)', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']
      end
      
      it "should raise an error if the hash doesn't have 2 entries with table names" do
        proc{@db.create_join_table({})}.must_raise(Sequel::Error)
        proc{@db.create_join_table({:cat_id=>:cats})}.must_raise(Sequel::Error)
        proc{@db.create_join_table({:cat_id=>:cats, :human_id=>:humans, :dog_id=>:dog})}.must_raise(Sequel::Error)
        proc{@db.create_join_table({:cat_id=>:cats, :dog_id=>{}})}.must_raise(Sequel::Error)
      end
    end
      
    describe "#create_join_table?" do
      it "should create the table if it does not already exist" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.create_join_table?(:cat_id=>:cats, :dog_id=>:dogs).must_be_nil
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']
      end

      it "should not create the table if it already exists" do
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.create_join_table?(:cat_id=>:cats, :dog_id=>:dogs)
        @db.sqls.must_equal []
      end

      it "should not use IF NOT EXISTS" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.define_singleton_method(:supports_create_table_if_not_exists?){true}
        @db.create_join_table?(:cat_id=>:cats, :dog_id=>:dogs)
        @db.sqls.must_equal ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']

        @db.singleton_class.send(:alias_method, :table_exists?, :table_exists?)
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.create_join_table?(:cat_id=>:cats, :dog_id=>:dogs)
        @db.sqls.must_equal []
      end

      it "should not use IF NOT EXISTS if no_index is used" do
        @db.define_singleton_method(:supports_create_table_if_not_exists?){true}
        @db.create_join_table?({:cat_id=>:cats, :dog_id=>:dogs}, :no_index=>true)
        @db.sqls.must_equal ['CREATE TABLE IF NOT EXISTS cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))']
      end
    end
      
    describe "#create_join_table!" do
      it "should drop the table first if it already exists" do
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.create_join_table!(:cat_id=>:cats, :dog_id=>:dogs).must_be_nil
        @db.sqls.must_equal expected_sqls(
          ['DROP TABLE cats_dogs',
            'CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))',
            'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)'],
          :postgres => [
            'DROP TABLE IF EXISTS cats_dogs',
            'CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))',
            'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)'
          ]
        )
      end

      it "should not drop the table if it doesn't exists" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.create_join_table!(:cat_id=>:cats, :dog_id=>:dogs)
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))',
          'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)'],
          :postgres => [
            'DROP TABLE IF EXISTS cats_dogs',
            'CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))',
            'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)'
          ]
        )
      end

      it "should use IF EXISTS if the database supports it" do
        @db.define_singleton_method(:supports_drop_table_if_exists?){true}
        @db.create_join_table!(:cat_id=>:cats, :dog_id=>:dogs)
        @db.sqls.must_equal ['DROP TABLE IF EXISTS cats_dogs', 'CREATE TABLE cats_dogs (cat_id integer NOT NULL REFERENCES cats, dog_id integer NOT NULL REFERENCES dogs, PRIMARY KEY (cat_id, dog_id))', 'CREATE INDEX cats_dogs_dog_id_cat_id_index ON cats_dogs (dog_id, cat_id)']
      end
    end
      
    describe "#drop_join_table" do
      it "should take a hash with foreign keys and table name values and drop the table" do
        @db.drop_join_table(:cat_id=>:cats, :dog_id=>:dogs).must_be_nil
        @db.sqls.must_equal ['DROP TABLE cats_dogs']
      end
      
      it "should be able to have values be a hash of options" do
        @db.drop_join_table(:cat_id=>{:table=>:cats, :null=>true}, :dog_id=>{:table=>:dogs, :default=>0})
        @db.sqls.must_equal ['DROP TABLE cats_dogs']
      end

      it "should respect a second hash of table options" do
        @db.drop_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :cascade=>true)
        @db.sqls.must_equal ['DROP TABLE cats_dogs CASCADE']
      end

      it "should respect :name option for table name" do
        @db.drop_join_table({:cat_id=>:cats, :dog_id=>:dogs}, :name=>:f)
        @db.sqls.must_equal ['DROP TABLE f']
      end
      
      it "should raise an error if the hash doesn't have 2 entries with table names" do
        proc{@db.drop_join_table({})}.must_raise(Sequel::Error)
        proc{@db.drop_join_table({:cat_id=>:cats})}.must_raise(Sequel::Error)
        proc{@db.drop_join_table({:cat_id=>:cats, :human_id=>:humans, :dog_id=>:dog})}.must_raise(Sequel::Error)
        proc{@db.drop_join_table({:cat_id=>:cats, :dog_id=>{}})}.must_raise(Sequel::Error)
      end
    end

    describe "#drop_table" do
      it "should generate a DROP TABLE statement" do
        @db.drop_table(:cats).must_be_nil
        @db.sqls.must_equal ['DROP TABLE cats']
      end

      it "should drop multiple tables at once" do
        @db.drop_table :cats, :dogs
        @db.sqls.must_equal ['DROP TABLE cats', 'DROP TABLE dogs']
      end

      it "should take an options hash and support the :cascade option" do
        @db.drop_table :cats, :dogs, :cascade=>true
        @db.sqls.must_equal ['DROP TABLE cats CASCADE', 'DROP TABLE dogs CASCADE']
      end
    end

    describe "#drop_table?" do
      it "should drop the table if it exists" do
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.drop_table?(:cats).must_be_nil
        @db.sqls.must_equal expected_sqls(
          ["DROP TABLE cats"],
          :postgres => ["DROP TABLE IF EXISTS cats"]
        )
      end
      
      it "should do nothing if the table does not exist" do
        @db.define_singleton_method(:table_exists?){|a| false}
        @db.drop_table?(:cats)
        @db.sqls.must_equal expected_sqls(
          [],
          :postgres => ["DROP TABLE IF EXISTS cats"]
        )
      end
      
      it "should operate on multiple tables at once" do
        @db.define_singleton_method(:table_exists?){|a| a == :cats}
        @db.drop_table? :cats, :dogs
        @db.sqls.must_equal expected_sqls(
          ['DROP TABLE cats'],
          :postgres => ["DROP TABLE IF EXISTS cats", "DROP TABLE IF EXISTS dogs"]
        )
      end

      it "should take an options hash and support the :cascade option" do
        @db.define_singleton_method(:table_exists?){|a| true}
        @db.drop_table? :cats, :dogs, :cascade=>true
        @db.sqls.must_equal expected_sqls(
          ['DROP TABLE cats CASCADE', 'DROP TABLE dogs CASCADE'],
          :postgres => ["DROP TABLE IF EXISTS cats CASCADE", "DROP TABLE IF EXISTS dogs CASCADE"]
        )
      end

      it "should use IF NOT EXISTS if the database supports that" do
        @db.define_singleton_method(:supports_drop_table_if_exists?){true}
        @db.drop_table? :cats, :dogs
        @db.sqls.must_equal ['DROP TABLE IF EXISTS cats', 'DROP TABLE IF EXISTS dogs']
      end

      it "should use IF NOT EXISTS with CASCADE if the database supports that" do
        @db.define_singleton_method(:supports_drop_table_if_exists?){true}
        @db.drop_table? :cats, :dogs, :cascade=>true
        @db.sqls.must_equal ['DROP TABLE IF EXISTS cats CASCADE', 'DROP TABLE IF EXISTS dogs CASCADE']
      end
    end

    describe "#alter_table" do
      it "should allow adding not null constraint via set_column_allow_null with false argument" do
        @db.alter_table(:cats) do
          set_column_allow_null :score, false
        end.must_be_nil
        @db.sqls.must_equal ["ALTER TABLE cats ALTER COLUMN score SET NOT NULL"]
      end
      
      it "should allow removing not null constraint via set_column_allow_null with true argument" do
        @db.alter_table(:cats) do
          set_column_allow_null :score, true
        end
        @db.sqls.must_equal ["ALTER TABLE cats ALTER COLUMN score DROP NOT NULL"]
      end

      it "should allow adding not null constraint via set_column_not_null" do
        @db.alter_table(:cats) do
          set_column_not_null :score
        end
        @db.sqls.must_equal ["ALTER TABLE cats ALTER COLUMN score SET NOT NULL"]
      end
      
      it "should allow removing not null constraint via set_column_allow_null without argument" do
        @db.alter_table(:cats) do
          set_column_allow_null :score
        end
        @db.sqls.must_equal ["ALTER TABLE cats ALTER COLUMN score DROP NOT NULL"]
      end

      it "should support add_column" do
        @db.alter_table(:cats) do
          add_column :score, :integer
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD COLUMN score integer"]
      end

      it "should support add_constraint" do
        @db.alter_table(:cats) do
          add_constraint :valid_score, 'score <= 100'
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD CONSTRAINT valid_score CHECK (score <= 100)"]
      end

      it "should support add_constraint with options" do
        @db.alter_table(:cats) do
          add_constraint({:name=>:valid_score, :deferrable=>true}, 'score <= 100')
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD CONSTRAINT valid_score CHECK (score <= 100) DEFERRABLE INITIALLY DEFERRED"]
      end

      it "should support add_constraint with block" do
        @db.alter_table(:cats) do
          add_constraint(:blah_blah){(x.sql_number > 0) & (y.sql_number < 1)}
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD CONSTRAINT blah_blah CHECK ((x > 0) AND (y < 1))"]
      end

      it "should support add_unique_constraint" do
        @db.alter_table(:cats) do
          add_unique_constraint [:a, :b]
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD UNIQUE (a, b)"]

        @db.alter_table(:cats) do
          add_unique_constraint [:a, :b], :name => :ab_uniq
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD CONSTRAINT ab_uniq UNIQUE (a, b)"]
      end

      it "should support add_foreign_key" do
        @db.alter_table(:cats) do
          add_foreign_key :node_id, :nodes
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD COLUMN node_id integer REFERENCES nodes"]
      end

      it "should support add_foreign_key with :index option" do
        @db.alter_table(:cats) do
          add_foreign_key :node_id, :nodes, :index=>true
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD COLUMN node_id integer REFERENCES nodes", "CREATE INDEX cats_node_id_index ON cats (node_id)"]
      end

      it "should support add_foreign_key with composite foreign keys" do
        @db.alter_table(:cats) do
          add_foreign_key [:node_id, :prop_id], :nodes_props
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD FOREIGN KEY (node_id, prop_id) REFERENCES nodes_props"]

        @db.alter_table(:cats) do
          add_foreign_key [:node_id, :prop_id], :nodes_props, :name => :cfk
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD CONSTRAINT cfk FOREIGN KEY (node_id, prop_id) REFERENCES nodes_props"]

        @db.alter_table(:cats) do
          add_foreign_key [:node_id, :prop_id], :nodes_props, :key => [:nid, :pid]
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD FOREIGN KEY (node_id, prop_id) REFERENCES nodes_props(nid, pid)"]

        @db.alter_table(:cats) do
          add_foreign_key [:node_id, :prop_id], :nodes_props, :on_delete => :restrict, :on_update => :cascade
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD FOREIGN KEY (node_id, prop_id) REFERENCES nodes_props ON DELETE RESTRICT ON UPDATE CASCADE"]
      end

      it "should support add_column with :index=>true option" do
        @db.alter_table(:cats) do
          add_column :name, Integer, :index=>true
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD COLUMN name integer", "CREATE INDEX cats_name_index ON cats (name)"]
      end

      it "should support add_column with :index=>hash option" do
        @db.alter_table(:cats) do
          add_column :name, Integer, :index=>{:name=>:foo}
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD COLUMN name integer", "CREATE INDEX foo ON cats (name)"]
      end

      it "should support add_index" do
        @db.alter_table(:cats) do
          add_index :name
        end
        @db.sqls.must_equal ["CREATE INDEX cats_name_index ON cats (name)"]
      end

      it "should ignore errors if the database raises an error on an add_index call and the :ignore_errors option is used" do
        @db.define_singleton_method(:execute_ddl){|*a| raise Sequel::DatabaseError}
        lambda{@db.add_index(:cats, :id)}.must_raise(Sequel::DatabaseError)
        @db.add_index(:cats, :id, :ignore_errors=>true)
        @db.sqls.must_equal []
      end

      it "should support add_primary_key" do
        @db.alter_table(:cats) do
          add_primary_key :id
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats ADD COLUMN id integer PRIMARY KEY AUTOINCREMENT"],
          :postgres => ['ALTER TABLE cats ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY']
        )
      end

      it "should support add_primary_key with composite primary keys" do
        @db.alter_table(:cats) do
          add_primary_key [:id, :type]
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD PRIMARY KEY (id, type)"]

        @db.alter_table(:cats) do
          add_primary_key [:id, :type], :name => :cpk
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD CONSTRAINT cpk PRIMARY KEY (id, type)"]
      end

      it "should set primary key column NOT NULL when using add_primary_key if database doesn't handle it" do
        def @db.can_add_primary_key_constraint_on_nullable_columns?; false end
        @db.alter_table(:cats) do
          add_primary_key :id
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats ADD COLUMN id integer NOT NULL PRIMARY KEY AUTOINCREMENT"],
          :postgres => ['ALTER TABLE cats ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY']
        )
      end

      it "should set primary key column NOT NULL when adding primary key constraint if database doesn't handle it" do
        def @db.can_add_primary_key_constraint_on_nullable_columns?; false end
        @db.alter_table(:cats) do
          add_primary_key [:id, :type]
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats ALTER COLUMN id SET NOT NULL",
            "ALTER TABLE cats ALTER COLUMN type SET NOT NULL",
            "ALTER TABLE cats ADD PRIMARY KEY (id, type)"],
          :postgres => ["ALTER TABLE cats ALTER COLUMN id SET NOT NULL, ALTER COLUMN type SET NOT NULL, ADD PRIMARY KEY (id, type)"]
        )
      end

      it "should support drop_column" do
        @db.alter_table(:cats) do
          drop_column :score
        end
        @db.sqls.must_equal ["ALTER TABLE cats DROP COLUMN score"]
      end

      it "should support drop_column with :cascade=>true option" do
        @db.alter_table(:cats) do
          drop_column :score, :cascade=>true
        end
        @db.sqls.must_equal ["ALTER TABLE cats DROP COLUMN score CASCADE"]
      end

      it "should support drop_constraint" do
        @db.alter_table(:cats) do
          drop_constraint :valid_score
        end
        @db.sqls.must_equal ["ALTER TABLE cats DROP CONSTRAINT valid_score"]
      end

      it "should support drop_constraint with :cascade=>true option" do
        @db.alter_table(:cats) do
          drop_constraint :valid_score, :cascade=>true
        end
        @db.sqls.must_equal ["ALTER TABLE cats DROP CONSTRAINT valid_score CASCADE"]
      end

      it "should support drop_foreign_key" do
        def @db.foreign_key_list(table_name)
          [{:name=>:cats_node_id_fkey, :columns=>[:node_id]}] 
        end
        @db.alter_table(:cats) do
          drop_foreign_key :node_id
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats DROP CONSTRAINT cats_node_id_fkey",
            "ALTER TABLE cats DROP COLUMN node_id"],
          :postgres => ['ALTER TABLE cats DROP CONSTRAINT cats_node_id_fkey, DROP COLUMN node_id']
        )
      end

      it "should support drop_foreign_key with :foreign_key_constraint_name option" do
        @db.alter_table(:cats) do
          drop_foreign_key :node_id, :foreign_key_constraint_name=>:foo
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats DROP CONSTRAINT foo",
            "ALTER TABLE cats DROP COLUMN node_id"],
          :postgres => ['ALTER TABLE cats DROP CONSTRAINT foo, DROP COLUMN node_id']
        )
      end

      it "should support drop_foreign_key with :name option" do
        @db.alter_table(:cats) do
          drop_foreign_key :node_id, :name=>:foo
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats DROP CONSTRAINT foo",
            "ALTER TABLE cats DROP COLUMN node_id"],
          :postgres => ['ALTER TABLE cats DROP CONSTRAINT foo, DROP COLUMN node_id']
        )
      end

      it "should support drop_foreign_key with composite foreign keys" do
        def @db.foreign_key_list(table_name)
          [{:name=>:cats_node_id_prop_id_fkey, :columns=>[:node_id, :prop_id]}] 
        end
        @db.alter_table(:cats) do
          drop_foreign_key [:node_id, :prop_id]
        end
        @db.sqls.must_equal ["ALTER TABLE cats DROP CONSTRAINT cats_node_id_prop_id_fkey"]

        @db.alter_table(:cats) do
          drop_foreign_key [:node_id, :prop_id], :name => :cfk
        end
        @db.sqls.must_equal ["ALTER TABLE cats DROP CONSTRAINT cfk"]
      end

      it "should have drop_foreign_key raise Error if no name is found" do
        def @db.foreign_key_list(table_name)
          [{:name=>:cats_node_id_fkey, :columns=>[:foo_id]}] 
        end
        lambda{@db.alter_table(:cats){drop_foreign_key :node_id}}.must_raise(Sequel::Error)
      end

      it "should have drop_foreign_key raise Error if multiple foreign keys found" do
        def @db.foreign_key_list(table_name)
          [{:name=>:cats_node_id_fkey, :columns=>[:node_id]}, {:name=>:cats_node_id_fkey2, :columns=>[:node_id]}] 
        end
        lambda{@db.alter_table(:cats){drop_foreign_key :node_id}}.must_raise(Sequel::Error)
      end

      it "should support drop_index" do
        @db.alter_table(:cats) do
          drop_index :name
        end
        @db.sqls.must_equal ["DROP INDEX cats_name_index"]
      end

      it "should support drop_index with a given name" do
        @db.alter_table(:cats) do
          drop_index :name, :name=>:blah_blah
        end
        @db.sqls.must_equal ["DROP INDEX blah_blah"]
      end

      it "should support rename_column" do
        @db.alter_table(:cats) do
          rename_column :name, :old_name
        end
        @db.sqls.must_equal ["ALTER TABLE cats RENAME COLUMN name TO old_name"]
      end

      it "should support set_column_default" do
        @db.alter_table(:cats) do
          set_column_default :score, 3
        end
        @db.sqls.must_equal ["ALTER TABLE cats ALTER COLUMN score SET DEFAULT 3"]
      end

      it "should support set_column_type" do
        @db.alter_table(:cats) do
          set_column_type :score, :real
        end
        @db.sqls.must_equal ["ALTER TABLE cats ALTER COLUMN score TYPE real"]
      end

      it "should support set_column_type with options" do
        @db.alter_table(:cats) do
          set_column_type :score, :integer, :unsigned=>true
          set_column_type :score, :varchar, :size=>30
          set_column_type :score, :enum, :elements=>['a', 'b']
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats ALTER COLUMN score TYPE integer UNSIGNED",
            "ALTER TABLE cats ALTER COLUMN score TYPE varchar(30)",
            "ALTER TABLE cats ALTER COLUMN score TYPE enum('a', 'b')"],
          :postgres => [
            "ALTER TABLE cats ALTER COLUMN score TYPE integer UNSIGNED, ALTER COLUMN score TYPE varchar(30), ALTER COLUMN score TYPE enum('a', 'b')"
          ]
        )
      end

      it "should combine operations into a single query if the database supports it" do
        @db.define_singleton_method(:supports_combining_alter_table_ops?){true}
        @db.alter_table(:cats) do
          add_column :a, Integer
          drop_column :b
          set_column_not_null :c
          rename_column :d, :e
          set_column_default :f, 'g'
          set_column_type :h, Integer
          add_constraint(:i){a > 1}
          drop_constraint :j
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats ADD COLUMN a integer, DROP COLUMN b, ALTER COLUMN c SET NOT NULL, RENAME COLUMN d TO e, ALTER COLUMN f SET DEFAULT 'g', ALTER COLUMN h TYPE integer, ADD CONSTRAINT i CHECK (a > 1), DROP CONSTRAINT j"],
          :postgres => [
            "ALTER TABLE cats ADD COLUMN a integer, DROP COLUMN b, ALTER COLUMN c SET NOT NULL",
            "ALTER TABLE cats RENAME COLUMN d TO e",
            "ALTER TABLE cats ALTER COLUMN f SET DEFAULT 'g', ALTER COLUMN h TYPE integer, ADD CONSTRAINT i CHECK (a > 1), DROP CONSTRAINT j"
          ]
        )
      end
      
      it "should combine operations into consecutive groups of combinable operations if the database supports combining operations" do
        @db.define_singleton_method(:supports_combining_alter_table_ops?){true}
        @db.alter_table(:cats) do
          add_column :a, Integer
          drop_column :b
          set_column_not_null :c
          rename_column :d, :e
          add_index :e
          set_column_default :f, 'g'
          set_column_type :h, Integer
          add_constraint(:i){a > 1}
          drop_constraint :j
        end
        @db.sqls.must_equal expected_sqls(
          ["ALTER TABLE cats ADD COLUMN a integer, DROP COLUMN b, ALTER COLUMN c SET NOT NULL, RENAME COLUMN d TO e",
            "CREATE INDEX cats_e_index ON cats (e)",
            "ALTER TABLE cats ALTER COLUMN f SET DEFAULT 'g', ALTER COLUMN h TYPE integer, ADD CONSTRAINT i CHECK (a > 1), DROP CONSTRAINT j"],
          :postgres => [
            'ALTER TABLE cats ADD COLUMN a integer, DROP COLUMN b, ALTER COLUMN c SET NOT NULL',
            'ALTER TABLE cats RENAME COLUMN d TO e',
            'CREATE INDEX cats_e_index ON cats (e)',
            "ALTER TABLE cats ALTER COLUMN f SET DEFAULT 'g', ALTER COLUMN h TYPE integer, ADD CONSTRAINT i CHECK (a > 1), DROP CONSTRAINT j"
          ]
        )
      end
      
      it "should handle operations that don't emit SQL when combining" do
        @db.define_singleton_method(:supports_combining_alter_table_ops?){true}
        @db.define_singleton_method(:combinable_alter_table_op?){|op| super(op) && (op[:op] != :rename_column || op[:name] == :d2)}
        @db.define_singleton_method(:alter_table_op_sql){|t, op| super(t, op) unless op[:op] == :rename_column}
        @db.alter_table(:cats) do
          rename_column :d, :e
          add_column :a, Integer
          drop_column :b
          set_column_not_null :c
          rename_column :d2, :e2
          add_index :e
          set_column_default :f, 'g'
          set_column_type :h, Integer
          add_constraint(:i){a > 1}
          drop_constraint :j
        end
        @db.sqls.must_equal ["ALTER TABLE cats ADD COLUMN a integer, DROP COLUMN b, ALTER COLUMN c SET NOT NULL",
          "CREATE INDEX cats_e_index ON cats (e)",
          "ALTER TABLE cats ALTER COLUMN f SET DEFAULT 'g', ALTER COLUMN h TYPE integer, ADD CONSTRAINT i CHECK (a > 1), DROP CONSTRAINT j"]
      end
    end

    describe "#create_table" do
      it "should construct proper SQL" do
        @db.create_table :test do
          primary_key :id, :integer, :null => false
          column :name, :text
          index :name, :unique => true
        end
        @db.sqls.must_equal expected_sqls(
          ['CREATE TABLE test (id integer NOT NULL PRIMARY KEY AUTOINCREMENT, name text)',
            'CREATE UNIQUE INDEX test_name_index ON test (name)'],
          :postgres => [
            'CREATE TABLE test (id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, name text)',
            'CREATE UNIQUE INDEX test_name_index ON test (name)']
        )
      end
      
      it "should create a temporary table" do
        @db.create_table :test_tmp, :temp => true do
          primary_key :id, :integer, :null => false
          column :name, :text
          index :name, :unique => true
        end
        
        @db.sqls.must_equal expected_sqls(
          ['CREATE TEMPORARY TABLE test_tmp (id integer NOT NULL PRIMARY KEY AUTOINCREMENT, name text)',
            'CREATE UNIQUE INDEX test_tmp_name_index ON test_tmp (name)'],
          :postgres => [
            'CREATE TEMPORARY TABLE test_tmp (id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, name text)',
            'CREATE UNIQUE INDEX test_tmp_name_index ON test_tmp (name)'
          ]
        )
      end
      
      it "should create a schema-qualified temporary table" do
        @db.create_table Sequel[:sch][:test_tmp], :temp => true do
          primary_key :id, :integer, :null => false
          column :name, :text
          index :name, :unique => true
        end
        
        @db.sqls.must_equal expected_sqls(
          ['CREATE TEMPORARY TABLE sch.test_tmp (id integer NOT NULL PRIMARY KEY AUTOINCREMENT, name text)',
            'CREATE UNIQUE INDEX sch_test_tmp_name_index ON sch.test_tmp (name)'],
          :postgres => [
            'CREATE TEMPORARY TABLE sch.test_tmp (id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, name text)',
            'CREATE UNIQUE INDEX sch_test_tmp_name_index ON sch.test_tmp (name)'
          ]
        )
      end
    end

    describe "#alter_table" do
      it "should construct proper SQL" do
        @db.alter_table :xyz do
          add_column :aaa, :text, :null => false, :unique => true
          drop_column :bbb
          rename_column :ccc, :ddd
          set_column_type :eee, :integer
          set_column_default :hhh, 'abcd'
          add_index :fff, :unique => true
          drop_index :ggg
        end
        
        @db.sqls.must_equal expected_sqls(
          ['ALTER TABLE xyz ADD COLUMN aaa text NOT NULL UNIQUE',
            'ALTER TABLE xyz DROP COLUMN bbb',
            'ALTER TABLE xyz RENAME COLUMN ccc TO ddd',
            'ALTER TABLE xyz ALTER COLUMN eee TYPE integer',
            "ALTER TABLE xyz ALTER COLUMN hhh SET DEFAULT 'abcd'",
            'CREATE UNIQUE INDEX xyz_fff_index ON xyz (fff)',
            'DROP INDEX xyz_ggg_index'],
          :postgres => [
            'ALTER TABLE xyz ADD COLUMN aaa text NOT NULL UNIQUE, DROP COLUMN bbb',
            'ALTER TABLE xyz RENAME COLUMN ccc TO ddd',
            "ALTER TABLE xyz ALTER COLUMN eee TYPE integer, ALTER COLUMN hhh SET DEFAULT 'abcd'",
            'CREATE UNIQUE INDEX xyz_fff_index ON xyz (fff)',
            'DROP INDEX xyz_ggg_index'
          ]
        )
      end
    end

    describe "#add_column" do
      it "should construct proper SQL" do
        db = Sequel.mock
        db.add_column(:test, :name, :text, :unique => true).must_be_nil
        db.sqls.must_equal ['ALTER TABLE test ADD COLUMN name text UNIQUE']
      end
    end

    describe "#drop_column" do
      it "should construct proper SQL" do
        @db.drop_column(:test, :name).must_be_nil
        @db.sqls.must_equal ['ALTER TABLE test DROP COLUMN name']
      end
      
      it "should use CASCADE for :cascade=>true option" do
        @db.drop_column :test, :name, :cascade=>true
        @db.sqls.must_equal ['ALTER TABLE test DROP COLUMN name CASCADE']
      end
    end

    describe "#rename_column" do
      it "should construct proper SQL" do
        @db.rename_column(:test, :abc, :def).must_be_nil
        @db.sqls.must_equal ['ALTER TABLE test RENAME COLUMN abc TO def']
      end
    end

    describe "#set_column_type" do
      it "should construct proper SQL" do
        @db.set_column_type(:test, :name, :integer).must_be_nil
        @db.sqls.must_equal ['ALTER TABLE test ALTER COLUMN name TYPE integer']
      end
    end

    describe "#set_column_default" do
      it "should construct proper SQL" do
        @db.set_column_default(:test, :name, 'zyx').must_be_nil
        @db.sqls.must_equal ["ALTER TABLE test ALTER COLUMN name SET DEFAULT 'zyx'"]
      end
    end

    describe "#add_index" do
      it "should construct proper SQL" do
        @db.add_index(:test, :name, :unique => true).must_be_nil
        @db.sqls.must_equal ['CREATE UNIQUE INDEX test_name_index ON test (name)']
      end
      
      it "should accept multiple columns" do
        @db.add_index :test, [:one, :two]
        @db.sqls.must_equal ['CREATE INDEX test_one_two_index ON test (one, two)']
      end
    end

    describe "#drop_index" do
      it "should construct proper SQL" do
        @db.drop_index(:test, :name).must_be_nil
        @db.sqls.must_equal ['DROP INDEX test_name_index']
      end
    end

    describe "#drop_table" do
      it "should construct proper SQL" do
        @db.drop_table(:test).must_be_nil
        @db.sqls.must_equal ['DROP TABLE test']
      end
      
      it "should accept multiple table names" do
        @db.drop_table :a, :bb, :ccc
        @db.sqls.must_equal ['DROP TABLE a', 'DROP TABLE bb', 'DROP TABLE ccc']
      end
    end

    describe "#rename_table" do
      it "should construct proper SQL" do
        @db.rename_table(:abc, :xyz).must_be_nil
        @db.sqls.must_equal ['ALTER TABLE abc RENAME TO xyz']
      end
    end

    describe "#create_view" do
      it "should construct proper SQL with raw SQL" do
        @db.create_view(:test, "SELECT * FROM xyz").must_be_nil
        @db.sqls.must_equal ['CREATE VIEW test AS SELECT * FROM xyz']
        @db.create_view Sequel.identifier(:test), "SELECT * FROM xyz"
        @db.sqls.must_equal ['CREATE VIEW test AS SELECT * FROM xyz']
      end
      
      it "should construct proper SQL with dataset" do
        @db.create_view :test, @db[:items].select(:a, :b).order(:c)
        @db.sqls.must_equal ['CREATE VIEW test AS SELECT a, b FROM items ORDER BY c']
      end

      it "should handle :columns option" do
        @db.create_view :test, @db[:items].select(:a, :b).order(:c), :columns=>[:d, :e]
        @db.sqls.must_equal ['CREATE VIEW test (d, e) AS SELECT a, b FROM items ORDER BY c']
        @db.create_view :test, @db[:items].select(:a, :b).order(:c), :columns=>%w'd e'
        @db.sqls.must_equal ['CREATE VIEW test (d, e) AS SELECT a, b FROM items ORDER BY c']
        @db.create_view :test, @db[:items].select(:a, :b).order(:c), :columns=>[Sequel.identifier('d'), Sequel.lit('e')]
        @db.sqls.must_equal ['CREATE VIEW test (d, e) AS SELECT a, b FROM items ORDER BY c']
      end

      it "should handle :check option" do
        @db.create_view :test, @db[:items].select(:a, :b).order(:c), :check=>true
        @db.sqls.must_equal ['CREATE VIEW test AS SELECT a, b FROM items ORDER BY c WITH CHECK OPTION']
        @db.create_view :test, @db[:items].select(:a, :b).order(:c), :check=>:local
        @db.sqls.must_equal ['CREATE VIEW test AS SELECT a, b FROM items ORDER BY c WITH LOCAL CHECK OPTION']
      end

      with_symbol_splitting "should handle create_or_replace_view with splittable symbols" do
        @db.create_or_replace_view :sch__test, "SELECT * FROM xyz"
        @db.sqls.must_equal expected_sqls(
          ['DROP VIEW sch.test',
            'CREATE VIEW sch.test AS SELECT * FROM xyz'],
          :postgres => ['CREATE OR REPLACE VIEW sch.test AS SELECT * FROM xyz']
        )
      end

      it "should handle create_or_replace_view" do
        @db.create_or_replace_view :test, @db[:items].select(:a, :b).order(:c)
        @db.sqls.must_equal expected_sqls(
          ['DROP VIEW test',
          'CREATE VIEW test AS SELECT a, b FROM items ORDER BY c'],
          :postgres => ['CREATE OR REPLACE VIEW test AS SELECT a, b FROM items ORDER BY c']
        )
        @db.create_or_replace_view Sequel.identifier(:test), @db[:items].select(:a, :b).order(:c)
        @db.sqls.must_equal expected_sqls(
          ['DROP VIEW test',
          'CREATE VIEW test AS SELECT a, b FROM items ORDER BY c'],
          :postgres => ['CREATE OR REPLACE VIEW test AS SELECT a, b FROM items ORDER BY c']
        )
      end

      it "should handle create_or_replace_view when DROP VIEW raises a database error" do
        def @db.drop_view(*) super; raise Sequel::DatabaseError end
        @db.create_or_replace_view :test, @db[:items].select(:a, :b).order(:c)
        @db.sqls.must_equal expected_sqls(
          ['DROP VIEW test',
            'CREATE VIEW test AS SELECT a, b FROM items ORDER BY c'],
          :postgres => ['CREATE OR REPLACE VIEW test AS SELECT a, b FROM items ORDER BY c']
        )
      end

      # No need to test on postgres, since it doesn't call drop_view
      unless database_type == :postgres
        it "should raise in create_or_replace_view when DROP VIEW raises a disconnect error" do
          def @db.drop_view(*) super; raise Sequel::DatabaseDisconnectError end
          proc{@db.create_or_replace_view :test, @db[:items].select(:a, :b).order(:c)}.must_raise Sequel::DatabaseDisconnectError
          @db.sqls.must_equal ['DROP VIEW test']
        end

        it "should raise in create_or_replace_view when DROP VIEW raises a connect error" do
          def @db.drop_view(*) super; raise Sequel::DatabaseConnectionError end
          proc{@db.create_or_replace_view :test, @db[:items].select(:a, :b).order(:c)}.must_raise Sequel::DatabaseConnectionError
          @db.sqls.must_equal ['DROP VIEW test']
        end
      end

      it "should use CREATE OR REPLACE VIEW if such syntax is supported" do
        def @db.supports_create_or_replace_view?() true end
        @db.create_or_replace_view :test, @db[:items]
        @db.sqls.must_equal ['CREATE OR REPLACE VIEW test AS SELECT * FROM items']
      end
    end

    describe "#drop_view" do
      with_symbol_splitting "should construct proper SQL for splittable symbols" do
        @db.drop_view(:sch__test).must_be_nil
        @db.sqls.must_equal ['DROP VIEW sch.test']
      end

      it "should construct proper SQL" do
        @db.drop_view :test
        @db.drop_view Sequel.identifier(:test)
        @db.drop_view Sequel.qualify(:sch, :test)
        @db.sqls.must_equal ['DROP VIEW test', 'DROP VIEW test', 'DROP VIEW sch.test']
      end

      it "should drop multiple views at once" do
        @db.drop_view :cats, :dogs
        @db.sqls.must_equal ['DROP VIEW cats', 'DROP VIEW dogs']
      end

      it "should support the :cascade option" do
        @db.drop_view :cats, :dogs, :cascade=>true
        @db.sqls.must_equal ['DROP VIEW cats CASCADE', 'DROP VIEW dogs CASCADE']
      end

      it "should support the :if_exists option" do
        @db.drop_view :cats, :dogs, :if_exists=>true
        @db.sqls.must_equal ['DROP VIEW IF EXISTS cats', 'DROP VIEW IF EXISTS dogs']
      end
    end

    describe "#alter_table_sql" do
      it "should raise error for an invalid op" do
        proc {Sequel.mock.send(:alter_table_sql, :mau, :op => :blah)}.must_raise(Sequel::Error)
      end
    end
  end
end

describe "Schema Parser" do
  before do
    @sqls = []
    @db = Sequel::Database.new
  end

  it "should raise an error if there are no columns" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      []
    end
    proc{@db.schema(:x)}.must_raise(Sequel::Error)
  end

  it "should cache data by default" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[:a, {}]]
    end
    @db.schema(:x).must_be_same_as(@db.schema(:x))
  end

  it "should not cache data if :reload=>true is given" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[:a, {}]]
    end
    @db.schema(:x).wont_be_same_as(@db.schema(:x, :reload=>true))
  end

  it "should not cache schema metadata if cache_schema is false" do
    @db.cache_schema = false
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[:a, {}]]
    end
    @db.schema(:x).wont_be_same_as(@db.schema(:x))
  end

  it "should freeze string values in resulting hash" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[:a, {:oid=>1, :db_type=>'integer'.dup, :default=>"'a'".dup, :ruby_default=>'a'.dup}]]
    end
    c = @db.schema(:x)[0][1]
    c[:db_type].frozen?.must_equal true
    c[:default].frozen?.must_equal true
    c[:ruby_default].frozen?.must_equal true
  end

  it "should provide options if given a table name" do
    c = nil
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      c = [t, opts]
      [[:a, {:db_type=>t.to_s}]]
    end
    @db.schema(:x)
    c.must_equal ["x", {}]
    @db.schema(Sequel[:s][:x])
    c.must_equal ["x", {:schema=>"s"}]
    ds = @db[Sequel[:s][:y]]
    @db.schema(ds)
    c.must_equal ["y", {:schema=>"s", :dataset=>ds}]
  end

  it "should raise error if asked to schema parse a dataset not involving a single table" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[:a, {:db_type=>t.to_s}]]
    end
    proc{@db.schema(@db.from)}.must_raise Sequel::Error
    proc{@db.schema(@db.from(:x, :y))}.must_raise Sequel::Error
    proc{@db.schema(@db.from(:x).cross_join(:y))}.must_raise Sequel::Error
  end

  with_symbol_splitting "should provide options if given a table name with splittable symbols" do
    c = nil
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      c = [t, opts]
      [[:a, {:db_type=>t.to_s}]]
    end
    @db.schema(:s__x)
    c.must_equal ["x", {:schema=>"s"}]
    ds = @db[:s__y]
    @db.schema(ds)
    c.must_equal ["y", {:schema=>"s", :dataset=>ds}]
  end

  it "should parse the schema correctly for a single table" do
    sqls = @sqls
    proc{@db.schema(:x)}.must_raise(Sequel::Error)
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      sqls << t
      [[:a, {:db_type=>t.to_s}]]
    end
    @db.schema(:x).must_equal [[:a, {:db_type=>"x", :ruby_default=>nil}]]
    @sqls.must_equal ['x']
    @db.schema(:x).must_equal [[:a, {:db_type=>"x", :ruby_default=>nil}]]
    @sqls.must_equal ['x']
    @db.schema(:x, :reload=>true).must_equal [[:a, {:db_type=>"x", :ruby_default=>nil}]]
    @sqls.must_equal ['x', 'x']
  end

  it "should dedup :db_type strings" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[:a, {:db_type=>t.to_s.dup}], [:b, {:db_type=>t.to_s.dup}]]
    end
    sch = @db.schema(:x)
    sch.must_equal [[:a, {:db_type=>"x", :ruby_default=>nil}], [:b, {:db_type=>"x", :ruby_default=>nil}]]
    sch[0][1][:db_type].must_be_same_as(sch[1][1][:db_type])
  end if RUBY_VERSION >= '2.5'

  it "should set :auto_increment to true by default if unset and a single integer primary key is used" do
    @db.define_singleton_method(:schema_parse_table){|*| [[:a, {:primary_key=>true, :db_type=>'integer'}]]}
    @db.schema(:x).first.last[:auto_increment].must_equal true
  end

  it "should not set :auto_increment if already set" do
    @db.define_singleton_method(:schema_parse_table){|*| [[:a, {:primary_key=>true, :db_type=>'integer', :auto_increment=>false}]]}
    @db.schema(:x).first.last[:auto_increment].must_equal false
  end

  it "should set :auto_increment to false by default if unset and a single nonintegery primary key is used" do
    @db.define_singleton_method(:schema_parse_table){|*| [[:a, {:primary_key=>true, :db_type=>'varchar'}]]}
    @db.schema(:x).first.last[:auto_increment].must_equal false
  end

  it "should set :auto_increment to false by default if unset and a composite primary key" do
    @db.define_singleton_method(:schema_parse_table){|*| [[:a, {:primary_key=>true, :db_type=>'integer'}], [:b, {:primary_key=>true, :db_type=>'integer'}]]}
    @db.schema(:x).first.last[:auto_increment].must_equal false
    @db.schema(:x).last.last[:auto_increment].must_equal false
  end

  it "should set :auto_increment to true by default if set and not the first column" do
    @db.define_singleton_method(:schema_parse_table){|*| [[:b, {}], [:a, {:primary_key=>true, :db_type=>'integer'}]]}
    @db.schema(:x).last.last[:auto_increment].must_equal true
  end

  it "should convert various types of table name arguments" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[t, opts]]
    end
    s1 = @db.schema(:x)
    s1.must_equal [['x', {:ruby_default=>nil}]]
    @db.schema(:x).object_id.must_equal s1.object_id
    @db.schema(Sequel.identifier(:x)).object_id.must_equal s1.object_id

    s2 = @db.schema(Sequel[:x][:y])
    s2.must_equal [['y', {:schema=>'x', :ruby_default=>nil}]]
    @db.schema(Sequel[:x][:y]).object_id.must_equal s2.object_id
    @db.schema(Sequel.qualify(:x, :y)).object_id.must_equal s2.object_id

    s2 = @db.schema(Sequel.qualify(:v, Sequel[:x][:y]))
    s2.must_equal [['y', {:schema=>'x', :ruby_default=>nil, :information_schema_schema=>Sequel.identifier('v')}]]
    @db.schema(Sequel.qualify(:v, Sequel[:x][:y])).object_id.must_equal s2.object_id
    @db.schema(Sequel.qualify(Sequel[:v][:x], :y)).object_id.must_equal s2.object_id

    s2 = @db.schema(Sequel.qualify(Sequel[:u][:v], Sequel[:x][:y]))
    s2.must_equal [['y', {:schema=>'x', :ruby_default=>nil, :information_schema_schema=>Sequel.qualify('u', 'v')}]]
    @db.schema(Sequel.qualify(Sequel[:u][:v], Sequel[:x][:y])).object_id.must_equal s2.object_id
    @db.schema(Sequel.qualify(Sequel.qualify(:u, :v), Sequel.qualify(:x, :y))).object_id.must_equal s2.object_id
  end

  with_symbol_splitting "should convert splittable symbol arguments" do
    @db.define_singleton_method(:schema_parse_table) do |t, opts|
      [[t, opts]]
    end
    s1 = @db.schema(:x)
    s1.must_equal [['x', {:ruby_default=>nil}]]
    @db.schema(:x).object_id.must_equal s1.object_id
    @db.schema(Sequel.identifier(:x)).object_id.must_equal s1.object_id

    s2 = @db.schema(:x__y)
    s2.must_equal [['y', {:schema=>'x', :ruby_default=>nil}]]
    @db.schema(:x__y).object_id.must_equal s2.object_id
    @db.schema(Sequel.qualify(:x, :y)).object_id.must_equal s2.object_id

    s2 = @db.schema(Sequel.qualify(:v, :x__y))
    s2.must_equal [['y', {:schema=>'x', :ruby_default=>nil, :information_schema_schema=>Sequel.identifier('v')}]]
    @db.schema(Sequel.qualify(:v, :x__y)).object_id.must_equal s2.object_id
    @db.schema(Sequel.qualify(:v__x, :y)).object_id.must_equal s2.object_id

    s2 = @db.schema(Sequel.qualify(:u__v, :x__y))
    s2.must_equal [['y', {:schema=>'x', :ruby_default=>nil, :information_schema_schema=>Sequel.qualify('u', 'v')}]]
    @db.schema(Sequel.qualify(:u__v, :x__y)).object_id.must_equal s2.object_id
    @db.schema(Sequel.qualify(Sequel.qualify(:u, :v), Sequel.qualify(:x, :y))).object_id.must_equal s2.object_id
  end

  it "should correctly parse all supported data types" do
    sm = Module.new do
      def schema_parse_table(t, opts)
        [[:x, {:db_type=>t.to_s, :type=>schema_column_type(t.to_s)}]]
      end
    end
    @db.extend(sm)
    @db.schema(:tinyint).first.last[:type].must_equal :integer
    @db.schema(:int).first.last[:type].must_equal :integer
    @db.schema(:integer).first.last[:type].must_equal :integer
    @db.schema(:bigint).first.last[:type].must_equal :integer
    @db.schema(:smallint).first.last[:type].must_equal :integer
    @db.schema(:character).first.last[:type].must_equal :string
    @db.schema(:"character varying").first.last[:type].must_equal :string
    @db.schema(:varchar).first.last[:type].must_equal :string
    @db.schema(:"varchar(255)").first.last[:type].must_equal :string
    @db.schema(:"varchar(255)").first.last[:max_length].must_equal 255
    @db.schema(:text).first.last[:type].must_equal :string
    @db.schema(:date).first.last[:type].must_equal :date
    @db.schema(:datetime).first.last[:type].must_equal :datetime
    @db.schema(:"datetime(6)").first.last[:type].must_equal :datetime
    @db.schema(:smalldatetime).first.last[:type].must_equal :datetime
    @db.schema(:timestamp).first.last[:type].must_equal :datetime
    @db.schema(:"timestamp with time zone").first.last[:type].must_equal :datetime
    @db.schema(:"timestamp without time zone").first.last[:type].must_equal :datetime
    @db.schema(:"timestamp(6)").first.last[:type].must_equal :datetime
    @db.schema(:"timestamp(6) with time zone").first.last[:type].must_equal :datetime
    @db.schema(:time).first.last[:type].must_equal :time
    @db.schema(:"time with time zone").first.last[:type].must_equal :time
    @db.schema(:"time without time zone").first.last[:type].must_equal :time
    @db.schema(:bool).first.last[:type].must_equal :boolean
    @db.schema(:boolean).first.last[:type].must_equal :boolean
    @db.schema(:real).first.last[:type].must_equal :float
    @db.schema(:float).first.last[:type].must_equal :float
    @db.schema(:"float unsigned").first.last[:type].must_equal :float
    @db.schema(:double).first.last[:type].must_equal :float
    @db.schema(:"double(1,2)").first.last[:type].must_equal :float
    @db.schema(:"double(1,2) unsigned").first.last[:type].must_equal :float
    @db.schema(:"double precision").first.last[:type].must_equal :float
    @db.schema(:number).first.last[:type].must_equal :decimal
    @db.schema(:numeric).first.last[:type].must_equal :decimal
    @db.schema(:decimal).first.last[:type].must_equal :decimal
    @db.schema(:"number(10,0)").first.last[:type].must_equal :integer
    @db.schema(:"numeric(10, 10)").first.last[:type].must_equal :decimal
    @db.schema(:"decimal(10,1)").first.last[:type].must_equal :decimal
    @db.schema(:bytea).first.last[:type].must_equal :blob
    @db.schema(:blob).first.last[:type].must_equal :blob
    @db.schema(:image).first.last[:type].must_equal :blob
    @db.schema(:nchar).first.last[:type].must_equal :string
    @db.schema(:nvarchar).first.last[:type].must_equal :string
    @db.schema(:ntext).first.last[:type].must_equal :string
    @db.schema(:clob).first.last[:type].must_equal :string
    @db.schema(:ntext).first.last[:type].must_equal :string
    @db.schema(:smalldatetime).first.last[:type].must_equal :datetime
    @db.schema(:binary).first.last[:type].must_equal :blob
    @db.schema(:varbinary).first.last[:type].must_equal :blob
    @db.schema(:enum).first.last[:type].must_equal :enum

    @db = Sequel.mock(:host=>'postgres')
    @db.extend(sm)
    @db.schema(:interval).first.last[:type].must_equal :interval
    @db.schema(:citext).first.last[:type].must_equal :string

    @db = Sequel.mock(:host=>'mysql')
    @db.extend(sm)
    @db.schema(:set).first.last[:type].must_equal :set
    @db.schema(:mediumint).first.last[:type].must_equal :integer
    @db.schema(:mediumtext).first.last[:type].must_equal :string
  end
end