Module: Sequel::MySQL::DatabaseMethods

Included in:
DataObjects::MySQL::DatabaseMethods, JDBC::MySQL::DatabaseMethods, Database, Sequel::Mysql2::Database, Swift::MySQL::DatabaseMethods
Defined in:
lib/sequel/adapters/shared/mysql.rb

Overview

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

Constant Summary collapse

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

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.



40
41
42
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 40

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

#commit_prepared_transaction(transaction_id) ⇒ Object

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



46
47
48
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 46

def commit_prepared_transaction(transaction_id)
  run("XA COMMIT #{literal(transaction_id)}")
end

#database_type ⇒ Object

MySQL uses the :mysql database type



51
52
53
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 51

def database_type
  :mysql
end

#foreign_key_list(table, opts = {}) ⇒ Object

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



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 58

def foreign_key_list(table, opts={})
  m = output_identifier_meth
  im = input_identifier_meth
  ds = metadata_dataset.
    from(:INFORMATION_SCHEMA__KEY_COLUMN_USAGE).
    where(:TABLE_NAME=>im.call(table)).
    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) 


81
82
83
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 81

def global_index_namespace?
  false
end

#indexes(table, 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.



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 90

def indexes(table, 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) ⇒ Object

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



108
109
110
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 108

def rollback_prepared_transaction(transaction_id)
  run("XA ROLLBACK #{literal(transaction_id)}")
end

#server_version ⇒ Object

Get version of MySQL server, used for determined capabilities.



113
114
115
116
117
118
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 113

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) 


121
122
123
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 121

def supports_create_table_if_not_exists?
  true
end

#supports_prepared_transactions? ⇒ Boolean

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

Returns:

  • (Boolean) 


126
127
128
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 126

def supports_prepared_transactions?
  server_version >= 50000
end

#supports_savepoints? ⇒ Boolean

MySQL supports savepoints

Returns:

  • (Boolean) 


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

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) 


137
138
139
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 137

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

#supports_transaction_isolation_levels? ⇒ Boolean

MySQL supports transaction isolation levels

Returns:

  • (Boolean) 


142
143
144
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 142

def supports_transaction_isolation_levels?
  true
end

#tables(opts = {}) ⇒ Object

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

Options:

  • :server - Set the server to use



150
151
152
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 150

def tables(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.



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

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

#views(opts = {}) ⇒ Object

Return an array of symbols specifying view 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 views(opts={})
  full_tables('VIEW', opts)
end