<commit_before>= Schema modification methods

Here's a brief description of the most common schema modification methods:

== +create_table+

+create_table+ is the most common schema modification method, and it's used for adding new tables
to the schema.  You provide it with the name of the table as a symbol, as well a block:

  create_table(:artists) do
    primary_key :id
    String :name
  end

Not that if you want a primary key for the table, you need to specify it, Sequel does not create one
by default.

=== Column types

Most method calls inside the create_table block will create columns, since +method_missing+ calls +column+.
Columns are generally created by specifying the column type as the method
name, followed by the column name symbol to use, and after that any options that should be used.
If the method is a ruby class name that Sequel recognizes, Sequel will transform it into the appropriate
type for the given database.  So while you specified +String+, Sequel will actually use +varchar+ or
+text+ depending on the underlying database.  Here's a list of all of ruby classes that Sequel will
convert to database types:

  create_table(:columns_types) do       # common database type used
    Integer :a0                         # integer
    String :a1                          # varchar(255)
    String :a2, :size=>50               # varchar(50)
    String :a3, :fixed=>true            # char(255)
    String :a4, :fixed=>true, :size=>50 # char(50)
    String :a5, :text=>true             # text
    File :b,                            # blob
    Fixnum :c                           # integer
    Bignum :d                           # bigint
    Float :e                            # double precision
    BigDecimal :f                       # numeric
    BigDecimal :f2, :size=>10           # numeric(10)
    BigDecimal :f3, :size=>[10, 2]      # numeric(10, 2)
    Date :g                             # date
    DateTime :h                         # timestamp
    Time :i                             # timestamp
    Time :i2, :only_time=>true          # time
    Numeric :j                          # numeric
    TrueClass :k                        # boolean
    FalseClass :l                       # boolean
  end

Note that in addition to the ruby class name, Sequel also pays attention to the column options when
determining which database type to use.  Also note that for boolean columns, you can use either
TrueClass or FalseClass, they are treated the same way (ruby doesn't have a Boolean class).

Also note that this conversion is only done if you use a supported ruby class name.  In all other
cases, Sequel uses the type specified verbatim:

  create_table(:columns_types) do  # database type used
    string :a1                     # string
    datetime :a2                   # datetime
    blob :a3                       # blob
    inet :a4                       # inet
  end

In addition to specifying the types as methods, you can use the +column+ method and specify the types
as the second argument, either as ruby classes, symbols, or strings:

  create_table(:columns_types) do  # database type used
    column :a1, :string            # string
    column :a2, String             # varchar(255)
    column :a3, 'string'           # string
    column :a4, :datetime          # datetime
    column :a5, DateTime           # timestamp
    column :a6, 'timestamp(6)'     # timestamp(6)
  end

=== Column options

When using the type name as method, the third argument is an options hash, and when using the +column+
method, the fourth argument is the options hash.  The following options are supported:

:default :: The default value for the column.
:index :: Create an index on this column. If given a hash, use the hash as the
          options for the index.
:null :: Mark the column as allowing NULL values (if true),
         or not allowing NULL values (if false).  If unspecified, will default
         to whatever the database default is.
:unique :: Mark the column as unique, generally has the same effect as
           creating a unique index on the column.

=== Other methods

In addition to the +column+ method and other methods that create columns, there are a other methods that can be used:

==== +primary_key+

You've seen this one used already.  It's used to create an autoincrementing integer primary key column.

  create_table(:a0){primary_key :id}

If you want an autoincrementing 64-bit integer:

  create_table(:a0){primary_key :id, :type=>Bignum}

If you want to create a primary key column that doesn't use an autoincrementing integer, you should
not use this method.  Instead, you should use the :primary_key option to the +column+ method or type
method:

  create_table(:a1){Integer :id, :primary_key=>true} # Non autoincrementing integer primary key
  create_table(:a2){String :name, :primary_key=>true} # varchar(255) primary key

If you want to create a composite primary key, you should call the +primary_key+ method with an
array of column symbols:

  create_table(:items) do
    Integer :group_id
    Integer :position
    primary_key [:group_id, :position]
  end 

If provided with an array, +primary_key+ does not create a column, it just sets up the primary key constraint.

==== +foreign_key+

+foreign_key+ is used to create a foreign key column that references a column in another table (or the same table).
It takes the column name as the first argument, the table it references as the second argument, and an options hash
as it's third argument.  A simple example is:

  create_table(:albums) do
    primary_key :id
    foreign_key :artist_id, :artists
    String :name
  end

+foreign_key+ accepts some specific options:

:deferrable :: Makes the foreign key constraint checks deferrable, so they aren't checked
               until the end of the transaction.
:key :: For foreign key columns, the column in the associated table
        that this column references.  Unnecessary if this column
        references the primary key of the associated table, at least
        on most databases.
:on_delete :: Specify the behavior of this foreign key column when the row with the primary key
              it references is deleted , can be :restrict, :cascade, :set_null, or :set_default.
              You can also use a string, which is used literally.
:on_update :: Specify the behavior of this foreign key column when the row with the primary key
              it references modifies the value of the primary key.  Takes the same options as
              :on_delete.

Like +primary_key+, if you provide +foreign_key+ with an array of symbols, it will not create a
column, but create a foreign key constraint:

  create_table(:artists) do
    String :name
    String :location
    primary_key [:name, :location]
  end
  create_table(:albums) do
    String :artist_name
    String :artist_location
    String :name
    foreign_key [:artist_name, :artist_location], :artists
  end

==== +index+

+index+ creates indexes on the table.  For single columns, calling index is the same as using the<commit_msg>Document foreign_key :name option in schema_modification.rdoc<commit_after>= Schema modification methods

Here's a brief description of the most common schema modification methods:

== +create_table+

+create_table+ is the most common schema modification method, and it's used for adding new tables
to the schema.  You provide it with the name of the table as a symbol, as well a block:

  create_table(:artists) do
    primary_key :id
    String :name
  end

Not that if you want a primary key for the table, you need to specify it, Sequel does not create one
by default.

=== Column types

Most method calls inside the create_table block will create columns, since +method_missing+ calls +column+.
Columns are generally created by specifying the column type as the method
name, followed by the column name symbol to use, and after that any options that should be used.
If the method is a ruby class name that Sequel recognizes, Sequel will transform it into the appropriate
type for the given database.  So while you specified +String+, Sequel will actually use +varchar+ or
+text+ depending on the underlying database.  Here's a list of all of ruby classes that Sequel will
convert to database types:

  create_table(:columns_types) do       # common database type used
    Integer :a0                         # integer
    String :a1                          # varchar(255)
    String :a2, :size=>50               # varchar(50)
    String :a3, :fixed=>true            # char(255)
    String :a4, :fixed=>true, :size=>50 # char(50)
    String :a5, :text=>true             # text
    File :b,                            # blob
    Fixnum :c                           # integer
    Bignum :d                           # bigint
    Float :e                            # double precision
    BigDecimal :f                       # numeric
    BigDecimal :f2, :size=>10           # numeric(10)
    BigDecimal :f3, :size=>[10, 2]      # numeric(10, 2)
    Date :g                             # date
    DateTime :h                         # timestamp
    Time :i                             # timestamp
    Time :i2, :only_time=>true          # time
    Numeric :j                          # numeric
    TrueClass :k                        # boolean
    FalseClass :l                       # boolean
  end

Note that in addition to the ruby class name, Sequel also pays attention to the column options when
determining which database type to use.  Also note that for boolean columns, you can use either
TrueClass or FalseClass, they are treated the same way (ruby doesn't have a Boolean class).

Also note that this conversion is only done if you use a supported ruby class name.  In all other
cases, Sequel uses the type specified verbatim:

  create_table(:columns_types) do  # database type used
    string :a1                     # string
    datetime :a2                   # datetime
    blob :a3                       # blob
    inet :a4                       # inet
  end

In addition to specifying the types as methods, you can use the +column+ method and specify the types
as the second argument, either as ruby classes, symbols, or strings:

  create_table(:columns_types) do  # database type used
    column :a1, :string            # string
    column :a2, String             # varchar(255)
    column :a3, 'string'           # string
    column :a4, :datetime          # datetime
    column :a5, DateTime           # timestamp
    column :a6, 'timestamp(6)'     # timestamp(6)
  end

=== Column options

When using the type name as method, the third argument is an options hash, and when using the +column+
method, the fourth argument is the options hash.  The following options are supported:

:default :: The default value for the column.
:index :: Create an index on this column. If given a hash, use the hash as the
          options for the index.
:null :: Mark the column as allowing NULL values (if true),
         or not allowing NULL values (if false).  If unspecified, will default
         to whatever the database default is.
:unique :: Mark the column as unique, generally has the same effect as
           creating a unique index on the column.

=== Other methods

In addition to the +column+ method and other methods that create columns, there are a other methods that can be used:

==== +primary_key+

You've seen this one used already.  It's used to create an autoincrementing integer primary key column.

  create_table(:a0){primary_key :id}

If you want an autoincrementing 64-bit integer:

  create_table(:a0){primary_key :id, :type=>Bignum}

If you want to create a primary key column that doesn't use an autoincrementing integer, you should
not use this method.  Instead, you should use the :primary_key option to the +column+ method or type
method:

  create_table(:a1){Integer :id, :primary_key=>true} # Non autoincrementing integer primary key
  create_table(:a2){String :name, :primary_key=>true} # varchar(255) primary key

If you want to create a composite primary key, you should call the +primary_key+ method with an
array of column symbols:

  create_table(:items) do
    Integer :group_id
    Integer :position
    primary_key [:group_id, :position]
  end 

If provided with an array, +primary_key+ does not create a column, it just sets up the primary key constraint.

==== +foreign_key+

+foreign_key+ is used to create a foreign key column that references a column in another table (or the same table).
It takes the column name as the first argument, the table it references as the second argument, and an options hash
as it's third argument.  A simple example is:

  create_table(:albums) do
    primary_key :id
    foreign_key :artist_id, :artists
    String :name
  end

+foreign_key+ accepts some specific options:

:deferrable :: Makes the foreign key constraint checks deferrable, so they aren't checked
               until the end of the transaction.
:key :: For foreign key columns, the column in the associated table
        that this column references.  Unnecessary if this column
        references the primary key of the associated table, at least
        on most databases.
:on_delete :: Specify the behavior of this foreign key column when the row with the primary key
              it references is deleted , can be :restrict, :cascade, :set_null, or :set_default.
              You can also use a string, which is used literally.
:on_update :: Specify the behavior of this foreign key column when the row with the primary key
              it references modifies the value of the primary key.  Takes the same options as
              :on_delete.

Like +primary_key+, if you provide +foreign_key+ with an array of symbols, it will not create a
column, but create a foreign key constraint:

  create_table(:artists) do
    String :name
    String :location
    primary_key [:name, :location]
  end
  create_table(:albums) do
    String :artist_name
    String :artist_location
    String :name
    foreign_key [:artist_name, :artist_location], :artists
  end

When using an array of symbols, you can also provide a :name option to name the constraint:

  create_table(:albums) do
    String :artist_name
    String :artist_location
    String :name
    foreign_key [:artist_name, :artist_location], :artists, :name=>'albums_artist_name_location_fkey'
  end

If you want to add a foreign key for a single column with a named constraint, you must use
the array form with a single symbol:

  create_table(:albums) do
    primary_key :id
    Integer :artist_id
    String :name
    foreign_key [:artist_id], :artists, :name=>'albums_artist_id_fkey'
  end

==== +index+

+index+ creates indexes on the table.  For single columns, calling index is the same as using the
{'fraction_non_alphanumeric': 0.06087015268206999, 'fraction_numerical': 0.007701661937576003, 'mean_word_length': 2.707237665915352, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2461078', 'n_tokens_mistral': 3965, 'n_tokens_neox': 3802, 'n_words': 1906}