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 52
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 58
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 63
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 70
def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  ds = metadata_dataset.
    from(Sequel[: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(Sequel[:CONSTRAINT_NAME].as(:name), Sequel[:COLUMN_NAME].as(:column), Sequel[:REFERENCED_TABLE_NAME].as(:table), Sequel[:REFERENCED_COLUMN_NAME].as(: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
freeze() click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 92
def freeze
  server_version
  supports_timestamp_usecs?
  super
end
global_index_namespace?() click to toggle source

MySQL namespaces indexes per table.

# File lib/sequel/adapters/shared/mysql.rb, line 99
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 108
def indexes(table, opts=OPTS)
  indexes = {}
  remove_indexes = []
  m = output_identifier_meth
  im = input_identifier_meth
  schema, table = schema_and_table(table)

  table = Sequel::SQL::Identifier.new(table)
  sql = "SHOW INDEX FROM #{literal(table)}"
  if schema
    schema = Sequel::SQL::Identifier.new(schema)
    sql += " FROM #{literal(schema)}"
  end

  metadata_dataset.with_sql(sql).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 135
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 140
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 148
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 153
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 158
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 164
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 172
def supports_timestamp_usecs?
  return @supports_timestamp_usecs if defined?(@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 178
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 186
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 192
def use(db_name)
  Sequel::Deprecation.deprecate("Database#use", "Create a new Sequel::Database instance instead of using Database#use")
  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 204
def views(opts=OPTS)
  full_tables('VIEW', opts)
end

Private Instance Methods

alter_table_add_column_sql(table, op) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 210
def alter_table_add_column_sql(table, op)
  pos = if after_col = op[:after]
    " AFTER #{quote_identifier(after_col)}"
  elsif op[:first]
    " FIRST"
  end

  sql = if related = op.delete(:table)
    sql = super + "#{pos}, ADD "
    op[:table] = related
    op[:key] ||= primary_key_from_schema(related)
    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}#{pos}"
  end
end
alter_table_add_constraint_sql(table, op) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/mysql.rb, line 254
def alter_table_add_constraint_sql(table, op)
  if op[:type] == :foreign_key
    op[:key] ||= primary_key_from_schema(op[:table])
  end
  super
end
alter_table_change_column_sql(table, op) click to toggle source
# File lib/sequel/adapters/shared/mysql.rb, line 230
def alter_table_change_column_sql(table, op)
  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)}"
end
alter_table_drop_constraint_sql(table, op) click to toggle source
# File lib/sequel/adapters/shared/mysql.rb, line 261
def alter_table_drop_constraint_sql(table, op)
  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
end
alter_table_rename_column_sql(table, op)
alter_table_set_column_default_sql(table, op)
alter_table_set_column_null_sql(table, op)
alter_table_set_column_type_sql(table, op)
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 274
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 329
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 334
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 341
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 350
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 356
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 291
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 302
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 363
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 373
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]
      elsif !(pkc = generator.columns.select{|con| con[:primary_key] == true}).empty?
        pkc.map{|c| c[:name]}
      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 427
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 432
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
index_definition_sql(table_name, index) click to toggle source

Handle MySQL specific index SQL syntax

# File lib/sequel/adapters/shared/mysql.rb, line 438
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 307
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 454
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 459
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 470
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 484
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 504
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 509
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 514
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 519
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 541
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 526
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 553
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 566
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 571
def view_with_check_option_support
  :local if server_version >= 50002
end