Module: Sequel::MySQL::DatabaseMethods

Extended by:
Database::ResetIdentifierMangling
Includes:
Database::SplitAlterTable
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

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

- (Object) cast_type_literal(type)

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



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

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

- (Object) commit_prepared_transaction(transaction_id, opts = OPTS)

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



53
54
55
# 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

- (Object) database_type

MySQL uses the :mysql database type



58
59
60
# File 'lib/sequel/adapters/shared/mysql.rb', line 58

def database_type
  :mysql
end

- (Object) foreign_key_list(table, opts = OPTS)

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



65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
# 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 = .
    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

- (Boolean) global_index_namespace?

MySQL namespaces indexes per table.



88
89
90
# File 'lib/sequel/adapters/shared/mysql.rb', line 88

def global_index_namespace?
  false
end

- (Object) indexes(table, opts = OPTS)

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.



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
# 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
  .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

- (Object) rollback_prepared_transaction(transaction_id, opts = OPTS)

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



115
116
117
# 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

- (Object) server_version

Get version of MySQL server, used for determined capabilities.



120
121
122
123
124
125
# 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

- (Boolean) supports_create_table_if_not_exists?

MySQL supports CREATE TABLE IF NOT EXISTS syntax.



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

def supports_create_table_if_not_exists?
  true
end

- (Boolean) supports_prepared_transactions?

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



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

def supports_prepared_transactions?
  server_version >= 50000
end

- (Boolean) supports_savepoints?

MySQL supports savepoints



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

def supports_savepoints?
  server_version >= 50000
end

- (Boolean) supports_savepoints_in_prepared_transactions?

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



144
145
146
# File 'lib/sequel/adapters/shared/mysql.rb', line 144

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

- (Boolean) supports_transaction_isolation_levels?

MySQL supports transaction isolation levels



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

def supports_transaction_isolation_levels?
  true
end

- (Object) tables(opts = OPTS)

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

Options:

  • :server - Set the server to use



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

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

- (Object) use(db_name)

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



163
164
165
166
167
168
# File 'lib/sequel/adapters/shared/mysql.rb', line 163

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

- (Object) views(opts = OPTS)

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

Options:

  • :server - Set the server to use



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

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