Module: Sequel::MySQL::DatabaseMethods

Overview

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

Constant Summary

AUTO_INCREMENT =
'AUTO_INCREMENT'.freeze
CAST_TYPES =
{String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}
COLUMN_DEFINITION_ORDER =
[:collate, :null, :default, :unique, :primary_key, :auto_increment, :references]
PRIMARY =
'PRIMARY'.freeze
MYSQL_TIMESTAMP_RE =
/\ACURRENT_(?:DATE|TIMESTAMP)?\z/

Instance Method Summary collapse

Methods included from Database::ResetIdentifierMangling

extended

Instance Method Details

#cast_type_literal(type) ⇒ Object

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



49
50
51
# File 'lib/sequel/adapters/shared/mysql.rb', line 49

def cast_type_literal(type)
  CAST_TYPES[type] || super
end

#commit_prepared_transaction(transaction_id, opts = OPTS) ⇒ Object

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



55
56
57
# File 'lib/sequel/adapters/shared/mysql.rb', line 55

def commit_prepared_transaction(transaction_id, opts=OPTS)
  run("XA COMMIT #{literal(transaction_id)}", opts)
end

#database_typeObject

MySQL uses the :mysql database type



60
61
62
# File 'lib/sequel/adapters/shared/mysql.rb', line 60

def database_type
  :mysql
end

#foreign_key_list(table, opts = OPTS) ⇒ Object

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



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
# File 'lib/sequel/adapters/shared/mysql.rb', line 67

def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  im = input_identifier_meth
  ds = .
    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?Boolean

MySQL namespaces indexes per table.

Returns:

  • (Boolean)


90
91
92
# File 'lib/sequel/adapters/shared/mysql.rb', line 90

def global_index_namespace?
  false
end

#indexes(table, opts = OPTS) ⇒ Object

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.



99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
# File 'lib/sequel/adapters/shared/mysql.rb', line 99

def indexes(table, opts=OPTS)
  indexes = {}
  remove_indexes = []
  m = output_identifier_meth
  im = input_identifier_meth
  .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) ⇒ Object

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



117
118
119
# File 'lib/sequel/adapters/shared/mysql.rb', line 117

def rollback_prepared_transaction(transaction_id, opts=OPTS)
  run("XA ROLLBACK #{literal(transaction_id)}", opts)
end

#server_versionObject

Get version of MySQL server, used for determined capabilities.



122
123
124
125
126
127
# File 'lib/sequel/adapters/shared/mysql.rb', line 122

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?Boolean

MySQL supports CREATE TABLE IF NOT EXISTS syntax.

Returns:

  • (Boolean)


130
131
132
# File 'lib/sequel/adapters/shared/mysql.rb', line 130

def supports_create_table_if_not_exists?
  true
end

#supports_prepared_transactions?Boolean

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

Returns:

  • (Boolean)


135
136
137
# File 'lib/sequel/adapters/shared/mysql.rb', line 135

def supports_prepared_transactions?
  server_version >= 50000
end

#supports_savepoints?Boolean

MySQL 5+ supports savepoints

Returns:

  • (Boolean)


140
141
142
# File 'lib/sequel/adapters/shared/mysql.rb', line 140

def supports_savepoints?
  server_version >= 50000
end

#supports_savepoints_in_prepared_transactions?Boolean

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

Returns:

  • (Boolean)


146
147
148
# File 'lib/sequel/adapters/shared/mysql.rb', line 146

def supports_savepoints_in_prepared_transactions?
  super && (server_version <= 50512 || server_version >= 50523)
end

#supports_timestamp_usecs?Boolean

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.

Returns:

  • (Boolean)


154
155
156
# File 'lib/sequel/adapters/shared/mysql.rb', line 154

def supports_timestamp_usecs?
  @supports_timestamp_usecs ||= server_version >= 50605 && typecast_value_boolean(opts[:fractional_seconds])
end

#supports_transaction_isolation_levels?Boolean

MySQL supports transaction isolation levels

Returns:

  • (Boolean)


159
160
161
# File 'lib/sequel/adapters/shared/mysql.rb', line 159

def supports_transaction_isolation_levels?
  true
end

#tables(opts = OPTS) ⇒ Object

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

Options:

:server

Set the server to use



167
168
169
# File 'lib/sequel/adapters/shared/mysql.rb', line 167

def tables(opts=OPTS)
  full_tables('BASE TABLE', opts)
end

#use(db_name) ⇒ Object

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



173
174
175
176
177
178
# File 'lib/sequel/adapters/shared/mysql.rb', line 173

def use(db_name)
  disconnect
  @opts[:database] = db_name if self << "USE #{db_name}"
  @schemas = {}
  self
end

#views(opts = OPTS) ⇒ Object

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

Options:

:server

Set the server to use



184
185
186
# File 'lib/sequel/adapters/shared/mysql.rb', line 184

def views(opts=OPTS)
  full_tables('VIEW', opts)
end