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

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.



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

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.



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

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

#database_type ⇒ Object

MySQL uses the :mysql database type



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

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.



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

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

#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.



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 84

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.



102
103
104
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 102

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.



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

def server_version
  m = /(\d+)\.(\d+)\.(\d+)/.match(get(SQL::Function.new(:version)))
  @server_version ||= (m[1].to_i * 10000) + (m[2].to_i * 100) + m[3].to_i
end

#supports_create_table_if_not_exists? ⇒ Boolean

MySQL supports CREATE TABLE IF NOT EXISTS syntax.

Returns:

  • (Boolean) 


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

def supports_create_table_if_not_exists?
  true
end

#supports_prepared_transactions? ⇒ Boolean

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

Returns:

  • (Boolean) 


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

def supports_prepared_transactions?
  server_version >= 50000
end

#supports_savepoints? ⇒ Boolean

MySQL supports savepoints

Returns:

  • (Boolean) 


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

def supports_savepoints?
  server_version >= 50000
end

#supports_savepoints_in_prepared_transactions? ⇒ Boolean

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

Returns:

  • (Boolean) 


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

def supports_savepoints_in_prepared_transactions?
  super && server_version <= 50512
end

#supports_transaction_isolation_levels? ⇒ Boolean

MySQL supports transaction isolation levels

Returns:

  • (Boolean) 


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

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



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

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.



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

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



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

def views(opts={})
  full_tables('VIEW', opts)
end