module Sequel::MySQL::DatabaseMethods

Methods shared by Database instances that connect to MySQL, currently supported by the native and JDBC adapters.

Constants

AUTO_INCREMENT
CAST_TYPES
COLUMN_DEFINITION_ORDER
DATABASE_ERROR_REGEXPS
MYSQL_TIMESTAMP_RE
PRIMARY

Public Instance Methods

cast_type_literal(type) click to toggle source

MySQL's cast rules are restrictive in that you can't just cast to any possible database type.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 47
def cast_type_literal(type)
  CAST_TYPES[type] || super
end
commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source

Commit an existing prepared transaction with the given transaction identifier string.

# File lib/sequel/adapters/shared/mysql.rb, line 53
def commit_prepared_transaction(transaction_id, opts=OPTS)
  run("XA COMMIT #{literal(transaction_id)}", opts)
end
database_type() click to toggle source

MySQL uses the :mysql database type

# File lib/sequel/adapters/shared/mysql.rb, line 58
def database_type
  :mysql
end
foreign_key_list(table, opts=OPTS) click to toggle source

Use the Information Schema's KEY_COLUMN_USAGE table to get basic information on foreign key columns, but include the constraint name.

# File lib/sequel/adapters/shared/mysql.rb, line 65
def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  ds = metadata_dataset.
    from(:INFORMATION_SCHEMA__KEY_COLUMN_USAGE).
    where(:TABLE_NAME=>im.call(table), :TABLE_SCHEMA=>Sequel.function(:DATABASE)).
    exclude(:CONSTRAINT_NAME=>'PRIMARY').
    exclude(:REFERENCED_TABLE_NAME=>nil).
    select(:CONSTRAINT_NAME___name, :COLUMN_NAME___column, :REFERENCED_TABLE_NAME___table, :REFERENCED_COLUMN_NAME___key)
  
  h = {}
  ds.each do |row|
    if r = h[row[:name]]
      r[:columns] << m.call(row[:column])
      r[:key] << m.call(row[:key])
    else
      h[row[:name]] = {:name=>m.call(row[:name]), :columns=>[m.call(row[:column])], :table=>m.call(row[:table]), :key=>[m.call(row[:key])]}
    end
  end
  h.values
end
global_index_namespace?() click to toggle source

MySQL namespaces indexes per table.

# File lib/sequel/adapters/shared/mysql.rb, line 88
def global_index_namespace?
  false
end
indexes(table, opts=OPTS) click to toggle source

Use SHOW INDEX FROM to get the index information for the table.

By default partial indexes are not included, you can use the option :partial to override this.

# File lib/sequel/adapters/shared/mysql.rb, line 97
def indexes(table, opts=OPTS)
  indexes = {}
  remove_indexes = []
  m = output_identifier_meth
  im = input_identifier_meth
  metadata_dataset.with_sql("SHOW INDEX FROM ?", SQL::Identifier.new(im.call(table))).each do |r|
    name = r[:Key_name]
    next if name == PRIMARY
    name = m.call(name)
    remove_indexes << name if r[:Sub_part] && ! opts[:partial]
    i = indexes[name] ||= {:columns=>[], :unique=>r[:Non_unique] != 1}
    i[:columns] << m.call(r[:Column_name])
  end
  indexes.reject{|k,v| remove_indexes.include?(k)}
end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source

Rollback an existing prepared transaction with the given transaction identifier string.

# File lib/sequel/adapters/shared/mysql.rb, line 115
def rollback_prepared_transaction(transaction_id, opts=OPTS)
  run("XA ROLLBACK #{literal(transaction_id)}", opts)
end
server_version() click to toggle source

Get version of MySQL server, used for determined capabilities.

# File lib/sequel/adapters/shared/mysql.rb, line 120
def server_version
  @server_version ||= begin
    m = /(\d+)\.(\d+)\.(\d+)/.match(get(SQL::Function.new(:version)))
    (m[1].to_i * 10000) + (m[2].to_i * 100) + m[3].to_i
  end
end
supports_create_table_if_not_exists?() click to toggle source

MySQL supports CREATE TABLE IF NOT EXISTS syntax.

# File lib/sequel/adapters/shared/mysql.rb, line 128
def supports_create_table_if_not_exists?
  true
end
supports_prepared_transactions?() click to toggle source

MySQL 5+ supports prepared transactions (two-phase commit) using XA

# File lib/sequel/adapters/shared/mysql.rb, line 133
def supports_prepared_transactions?
  server_version >= 50000
end
supports_savepoints?() click to toggle source

MySQL 5+ supports savepoints

# File lib/sequel/adapters/shared/mysql.rb, line 138
def supports_savepoints?
  server_version >= 50000
end
supports_savepoints_in_prepared_transactions?() click to toggle source

MySQL doesn't support savepoints inside prepared transactions in from 5.5.12 to 5.5.23, see bugs.mysql.com/bug.php?id=64374

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 144
def supports_savepoints_in_prepared_transactions?
  super && (server_version <= 50512 || server_version >= 50523)
end
supports_timestamp_usecs?() click to toggle source

Support fractional timestamps on MySQL 5.6.5+ if the :fractional_seconds Database option is used. Technically, MySQL 5.6.4+ supports them, but automatic initialization of datetime values wasn't supported to 5.6.5+, and this is related to that.

# File lib/sequel/adapters/shared/mysql.rb, line 152
def supports_timestamp_usecs?
  @supports_timestamp_usecs ||= server_version >= 50605 && typecast_value_boolean(opts[:fractional_seconds])
end
supports_transaction_isolation_levels?() click to toggle source

MySQL supports transaction isolation levels

# File lib/sequel/adapters/shared/mysql.rb, line 157
def supports_transaction_isolation_levels?
  true
end
tables(opts=OPTS) click to toggle source

Return an array of symbols specifying table names in the current database.

Options:

:server

Set the server to use

# File lib/sequel/adapters/shared/mysql.rb, line 165
def tables(opts=OPTS)
  full_tables('BASE TABLE', opts)
end
use(db_name) click to toggle source

Changes the database in use by issuing a USE statement. I would be very careful if I used this.

# File lib/sequel/adapters/shared/mysql.rb, line 171
def use(db_name)
  disconnect
  @opts[:database] = db_name if self << "USE #{db_name}"
  @schemas = {}
  self
end
views(opts=OPTS) click to toggle source

Return an array of symbols specifying view names in the current database.

Options:

:server

Set the server to use

# File lib/sequel/adapters/shared/mysql.rb, line 182
def views(opts=OPTS)
  full_tables('VIEW', opts)
end

Private Instance Methods

alter_table_op_sql(table, op) click to toggle source

Use MySQL specific syntax for some alter table operations.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 189
def alter_table_op_sql(table, op)
  case op[:op]
  when :add_column
    if related = op.delete(:table)
      sql = super
      op[:table] = related
      op[:key] ||= primary_key_from_schema(related)
      sql << ", ADD "
      if constraint_name = op.delete(:foreign_key_constraint_name)
        sql << "CONSTRAINT #{quote_identifier(constraint_name)} "
      end
      sql << "FOREIGN KEY (#{quote_identifier(op[:name])})#{column_references_sql(op)}"
    else
      super
    end
  when :rename_column, :set_column_type, :set_column_null, :set_column_default
    o = op[:op]
    opts = schema(table).find{|x| x.first == op[:name]}
    opts = opts ? opts.last.dup : {}
    opts[:name] = o == :rename_column ? op[:new_name] : op[:name]
    opts[:type] = o == :set_column_type ? op[:type] : opts[:db_type]
    opts[:null] = o == :set_column_null ? op[:null] : opts[:allow_null]
    opts[:default] = o == :set_column_default ? op[:default] : opts[:ruby_default]
    opts.delete(:default) if opts[:default] == nil
    opts.delete(:primary_key)
    unless op[:type] || opts[:type]
      raise Error, "cannot determine database type to use for CHANGE COLUMN operation"
    end
    opts = op.merge(opts)
    if op.has_key?(:auto_increment)
      opts[:auto_increment] = op[:auto_increment]
    end
    "CHANGE COLUMN #{quote_identifier(op[:name])} #{column_definition_sql(opts)}"
  when :drop_constraint
    case op[:type]
    when :primary_key
      "DROP PRIMARY KEY"
    when :foreign_key
      name = op[:name] || foreign_key_name(table, op[:columns])
      "DROP FOREIGN KEY #{quote_identifier(name)}"
    when :unique
      "DROP INDEX #{quote_identifier(op[:name])}"
    end
  when :add_constraint
    if op[:type] == :foreign_key
      op[:key] ||= primary_key_from_schema(op[:table])
    end
    super
  else
    super
  end
end
alter_table_sql(table, op) click to toggle source

MySQL server requires table names when dropping indexes.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 243
def alter_table_sql(table, op)
  case op[:op]
  when :drop_index
    "#{drop_index_sql(table, op)} ON #{quote_schema_table(table)}"
  when :drop_constraint
    if op[:type] == :primary_key
      if (pk = primary_key_from_schema(table)).length == 1
        return [alter_table_sql(table, {:op=>:rename_column, :name=>pk.first, :new_name=>pk.first, :auto_increment=>false}), super]
      end
    end
    super
  else
    super
  end
end
auto_increment_sql() click to toggle source

Use MySQL specific AUTO_INCREMENT text.

# File lib/sequel/adapters/shared/mysql.rb, line 298
def auto_increment_sql
  AUTO_INCREMENT
end
begin_new_transaction(conn, opts) click to toggle source

MySQL needs to set transaction isolation before begining a transaction

# File lib/sequel/adapters/shared/mysql.rb, line 303
def begin_new_transaction(conn, opts)
  set_transaction_isolation(conn, opts)
  log_connection_execute(conn, begin_transaction_sql)
end
begin_transaction(conn, opts=OPTS) click to toggle source

Use XA START to start a new prepared transaction if the :prepare option is given.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 310
def begin_transaction(conn, opts=OPTS)
  if (s = opts[:prepare]) && savepoint_level(conn) == 1
    log_connection_execute(conn, "XA START #{literal(s)}")
  else
    super
  end
end
column_definition_order() click to toggle source

The order of the column definition, as an array of symbols.

# File lib/sequel/adapters/shared/mysql.rb, line 319
def column_definition_order
  COLUMN_DEFINITION_ORDER
end
column_definition_sql(column) click to toggle source

MySQL doesn't allow default values on text columns, so ignore if it the generic text type is used

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 325
def column_definition_sql(column)
  column.delete(:default) if column[:type] == File || (column[:type] == String && column[:text] == true)
  super
end
column_schema_normalize_default(default, type) click to toggle source

Handle MySQL specific default format.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 260
def column_schema_normalize_default(default, type)
  if column_schema_default_string_type?(type)
    return if [:date, :datetime, :time].include?(type) && MYSQL_TIMESTAMP_RE.match(default)
    default = "'#{default.gsub("'", "''").gsub('\\', '\\\\')}'"
  end
  super(default, type)
end
combinable_alter_table_op?(op) click to toggle source

Don't allow combining adding foreign key operations with other operations, since in some cases adding a foreign key constraint in the same query as other operations results in MySQL error 150.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 271
def combinable_alter_table_op?(op)
  super && !(op[:op] == :add_constraint && op[:type] == :foreign_key) && !(op[:op] == :drop_constraint && op[:type] == :primary_key)
end
commit_transaction(conn, opts=OPTS) click to toggle source

Prepare the XA transaction for a two-phase commit if the :prepare option is given.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 332
def commit_transaction(conn, opts=OPTS)
  if (s = opts[:prepare]) && savepoint_level(conn) <= 1
    log_connection_execute(conn, "XA END #{literal(s)}")
    log_connection_execute(conn, "XA PREPARE #{literal(s)}")
  else
    super
  end
end
create_table_sql(name, generator, options = OPTS) click to toggle source

Use MySQL specific syntax for engine type and character encoding

# File lib/sequel/adapters/shared/mysql.rb, line 342
def create_table_sql(name, generator, options = OPTS)
  engine = options.fetch(:engine, Sequel::MySQL.default_engine)
  charset = options.fetch(:charset, Sequel::MySQL.default_charset)
  collate = options.fetch(:collate, Sequel::MySQL.default_collate)
  generator.constraints.sort_by{|c| (c[:type] == :primary_key) ? -1 : 1}

  # Proc for figuring out the primary key for a given table.
  key_proc = lambda do |t|
    if t == name 
      if pk = generator.primary_key_name
        [pk]
      elsif !(pkc = generator.constraints.select{|con| con[:type] == :primary_key}).empty?
        pkc.first[:columns]
      end
    else
      primary_key_from_schema(t)
    end
  end

  # Manually set the keys, since MySQL requires one, it doesn't use the primary
  # key if none are specified.
  generator.constraints.each do |c|
    if c[:type] == :foreign_key
      c[:key] ||= key_proc.call(c[:table])
    end
  end

  # Split column constraints into table constraints in some cases:
  # foreign key - Always
  # unique, primary_key - Only if constraint has a name
  generator.columns.each do |c|
    if t = c.delete(:table)
      same_table = t == name
      key = c[:key] || key_proc.call(t)

      if same_table && !key.nil?
        generator.constraints.unshift(:type=>:unique, :columns=>Array(key))
      end

      generator.foreign_key([c[:name]], t, c.merge(:name=>c[:foreign_key_constraint_name], :type=>:foreign_key, :key=>key))
    end
  end

  "#{super}#{" ENGINE=#{engine}" if engine}#{" DEFAULT CHARSET=#{charset}" if charset}#{" DEFAULT COLLATE=#{collate}" if collate}"
end
database_error_regexps() click to toggle source
# File lib/sequel/adapters/shared/mysql.rb, line 394
def database_error_regexps
  DATABASE_ERROR_REGEXPS
end
full_tables(type, opts) click to toggle source

Backbone of the tables and views support using SHOW FULL TABLES.

# File lib/sequel/adapters/shared/mysql.rb, line 399
def full_tables(type, opts)
  m = output_identifier_meth
  metadata_dataset.with_sql('SHOW FULL TABLES').server(opts[:server]).map{|r| m.call(r.values.first) if r.delete(:Table_type) == type}.compact
end
identifier_input_method_default() click to toggle source

MySQL folds unquoted identifiers to lowercase, so it shouldn't need to upcase identifiers on input.

# File lib/sequel/adapters/shared/mysql.rb, line 405
def identifier_input_method_default
  nil
end
identifier_output_method_default() click to toggle source

MySQL folds unquoted identifiers to lowercase, so it shouldn't need to upcase identifiers on output.

# File lib/sequel/adapters/shared/mysql.rb, line 410
def identifier_output_method_default
  nil
end
index_definition_sql(table_name, index) click to toggle source

Handle MySQL specific index SQL syntax

# File lib/sequel/adapters/shared/mysql.rb, line 415
def index_definition_sql(table_name, index)
  index_name = quote_identifier(index[:name] || default_index_name(table_name, index[:columns]))
  raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes?
  index_type = case index[:type]
  when :full_text
    "FULLTEXT "
  when :spatial
    "SPATIAL "
  else
    using = " USING #{index[:type]}" unless index[:type] == nil
    "UNIQUE " if index[:unique]
  end
  "CREATE #{index_type}INDEX #{index_name}#{using} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}"
end
mysql_connection_setting_sqls() click to toggle source

The SQL queries to execute on initial connection

# File lib/sequel/adapters/shared/mysql.rb, line 276
def mysql_connection_setting_sqls
  sqls = []
  
  if wait_timeout = opts.fetch(:timeout, 2147483)
    # Increase timeout so mysql server doesn't disconnect us
    # Value used by default is maximum allowed value on Windows.
    sqls << "SET @@wait_timeout = #{wait_timeout}"
  end

  # By default, MySQL 'where id is null' selects the last inserted id
  sqls <<  "SET SQL_AUTO_IS_NULL=0" unless opts[:auto_is_null]

  # If the user has specified one or more sql modes, enable them
  if sql_mode = opts[:sql_mode]
    sql_mode = Array(sql_mode).join(',').upcase
    sqls <<  "SET sql_mode = '#{sql_mode}'"
  end

  sqls
end
primary_key_from_schema(table) click to toggle source

Parse the schema for the given table to get an array of primary key columns

# File lib/sequel/adapters/shared/mysql.rb, line 431
def primary_key_from_schema(table)
  schema(table).select{|a| a[1][:primary_key]}.map{|a| a[0]}
end
rollback_transaction(conn, opts=OPTS) click to toggle source

Rollback the currently open XA transaction

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 436
def rollback_transaction(conn, opts=OPTS)
  if (s = opts[:prepare]) && savepoint_level(conn) <= 1
    log_connection_execute(conn, "XA END #{literal(s)}")
    log_connection_execute(conn, "XA PREPARE #{literal(s)}")
    log_connection_execute(conn, "XA ROLLBACK #{literal(s)}")
  else
    super
  end
end
schema_column_type(db_type) click to toggle source

Recognize MySQL set type.

Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 447
def schema_column_type(db_type)
  case db_type
  when /\Aset/io
    :set
  when /\Amediumint/io
    :integer
  when /\Amediumtext/io
    :string
  else
    super
  end
end
schema_parse_table(table_name, opts) click to toggle source

Use the MySQL specific DESCRIBE syntax to get a table description.

# File lib/sequel/adapters/shared/mysql.rb, line 461
def schema_parse_table(table_name, opts)
  m = output_identifier_meth(opts[:dataset])
  im = input_identifier_meth(opts[:dataset])
  table = SQL::Identifier.new(im.call(table_name))
  table = SQL::QualifiedIdentifier.new(im.call(opts[:schema]), table) if opts[:schema]
  metadata_dataset.with_sql("DESCRIBE ?", table).map do |row|
    extra = row.delete(:Extra)
    if row[:primary_key] = row.delete(:Key) == 'PRI'
      row[:auto_increment] = !!(extra.to_s =~ /auto_increment/io)
    end
    row[:allow_null] = row.delete(:Null) == 'YES'
    row[:default] = row.delete(:Default)
    row[:db_type] = row.delete(:Type)
    row[:type] = schema_column_type(row[:db_type])
    [m.call(row.delete(:Field)), row]
  end
end
split_alter_table_op?(op) click to toggle source

Split DROP INDEX ops on MySQL 5.6+, as dropping them in the same statement as dropping a related foreign key causes an error.

# File lib/sequel/adapters/shared/mysql.rb, line 481
def split_alter_table_op?(op)
  server_version >= 50600 && (op[:op] == :drop_index || (op[:op] == :drop_constraint && op[:type] == :unique))
end
supports_combining_alter_table_ops?() click to toggle source

MySQL can combine multiple alter table ops into a single query.

# File lib/sequel/adapters/shared/mysql.rb, line 486
def supports_combining_alter_table_ops?
  true
end
supports_create_or_replace_view?() click to toggle source

MySQL supports CREATE OR REPLACE VIEW.

# File lib/sequel/adapters/shared/mysql.rb, line 491
def supports_create_or_replace_view?
  true
end
supports_named_column_constraints?() click to toggle source

MySQL does not support named column constraints.

# File lib/sequel/adapters/shared/mysql.rb, line 496
def supports_named_column_constraints?
  false
end
type_literal_generic_datetime(column) click to toggle source

MySQL has both datetime and timestamp classes, most people are going to want datetime

# File lib/sequel/adapters/shared/mysql.rb, line 518
def type_literal_generic_datetime(column)
  if supports_timestamp_usecs?
    :'datetime(6)'
  elsif column[:default] == Sequel::CURRENT_TIMESTAMP
    :timestamp
  else
    :datetime
  end
end
type_literal_generic_file(column) click to toggle source

Respect the :size option if given to produce tinyblob, mediumblob, and longblob if :tiny, :medium, or :long is given.

# File lib/sequel/adapters/shared/mysql.rb, line 503
def type_literal_generic_file(column)
  case column[:size]
  when :tiny    # < 2^8 bytes
    :tinyblob
  when :medium  # < 2^24 bytes
    :mediumblob
  when :long    # < 2^32 bytes
    :longblob
  else          # 2^16 bytes
    :blob
  end
end
type_literal_generic_time(column) click to toggle source

MySQL has both datetime and timestamp classes, most people are going to want datetime.

# File lib/sequel/adapters/shared/mysql.rb, line 530
def type_literal_generic_time(column)
  if column[:only_time]
    if supports_timestamp_usecs?
      :'time(6)'
    else
      :time
    end
  else
    type_literal_generic_datetime(column)
  end
end
type_literal_generic_trueclass(column) click to toggle source

MySQL doesn't have a true boolean class, so it uses tinyint(1)

# File lib/sequel/adapters/shared/mysql.rb, line 543
def type_literal_generic_trueclass(column)
  :'tinyint(1)'
end
view_with_check_option_support() click to toggle source

MySQL 5.0.2+ supports views with check option.

# File lib/sequel/adapters/shared/mysql.rb, line 548
def view_with_check_option_support
  :local if server_version >= 50002
end