Module: Sequel::MySQL::DatabaseMethods

Includes:
Database::SplitAlterTable, UnmodifiedIdentifiers::DatabaseMethods
Included in:
JDBC::MySQL::DatabaseMethods, Database, Sequel::Mysql2::Database, Trilogy::Database
Defined in:
lib/sequel/adapters/shared/mysql.rb

Constant Summary collapse

CAST_TYPES = 
{String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}.freeze
COLUMN_DEFINITION_ORDER = 
[:generated, :collate, :null, :default, :unique, :primary_key, :auto_increment, :references].freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#default_charset ⇒ Object

Set the default charset used for CREATE TABLE. You can pass the :charset option to create_table to override this setting.



26
27
28
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 26

def default_charset
  @default_charset
end

#default_collate ⇒ Object

Set the default collation used for CREATE TABLE. You can pass the :collate option to create_table to override this setting.



30
31
32
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 30

def default_collate
  @default_collate
end

#default_engine ⇒ Object

Set the default engine used for CREATE TABLE. You can pass the :engine option to create_table to override this setting.



34
35
36
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 34

def default_engine
  @default_engine
end

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.



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

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

#commit_prepared_transaction(transaction_id, opts = OPTS) ⇒ Object 



42
43
44
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 42

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

#database_type ⇒ Object 



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

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.



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

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).
    order(:CONSTRAINT_NAME, :POSITION_IN_UNIQUE_CONSTRAINT).
    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 ⇒ Object 



76
77
78
79
80
81
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 76

def freeze
  server_version
  mariadb?
  supports_timestamp_usecs?
  super
end

#global_index_namespace? ⇒ Boolean

MySQL namespaces indexes per table.

Returns:

  • (Boolean) 


84
85
86
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 84

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.



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 93

def indexes(table, opts=OPTS)
  indexes = {}
  remove_indexes = []
  m = output_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

#mariadb? ⇒ Boolean

Whether the database is MariaDB and not MySQL

Returns:

  • (Boolean) 


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

def mariadb?
  return @is_mariadb if defined?(@is_mariadb)
  @is_mariadb = !(fetch('SELECT version()').single_value! !~ /mariadb/i)
end

#rename_tables(*renames) ⇒ Object

Renames multiple tables in a single call.

DB.rename_tables [:items, :old_items], [:other_items, :old_other_items]
# RENAME TABLE items TO old_items, other_items TO old_other_items



195
196
197
198
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 195

def rename_tables(*renames)
  execute_ddl(rename_tables_sql(renames))
  renames.each{|from,| remove_cached_schema(from)}
end

#rollback_prepared_transaction(transaction_id, opts = OPTS) ⇒ Object 



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_version ⇒ Object

Get version of MySQL server, used for determined capabilities.



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

def server_version
  @server_version ||= begin
    m = /(\d+)\.(\d+)\.(\d+)/.match(fetch('SELECT version()').single_value!)
    (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) 


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

def supports_create_table_if_not_exists?
  true
end

#supports_generated_columns? ⇒ Boolean

Generated columns are supported in MariaDB 5.2.0+ and MySQL 5.7.6+.

Returns:

  • (Boolean) 


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

def supports_generated_columns?
  server_version >= (mariadb? ? 50200 : 50706)
end

#supports_prepared_transactions? ⇒ Boolean

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

Returns:

  • (Boolean) 


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

def supports_prepared_transactions?
  server_version >= 50000
end

#supports_savepoints? ⇒ Boolean

MySQL 5+ supports savepoints

Returns:

  • (Boolean) 


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

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) 


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

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) 


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

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

MySQL supports transaction isolation levels

Returns:

  • (Boolean) 


171
172
173
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 171

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



179
180
181
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 179

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

#views(opts = OPTS) ⇒ Object

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

Options:

:server

Set the server to use



187
188
189
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 187

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

#with_advisory_lock(lock_id, opts = OPTS) ⇒ Object

Attempt to acquire an exclusive advisory lock with the given lock_id (which will be converted to a string). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.

DB.with_advisory_lock(1357){DB.get(1)}
# SELECT GET_LOCK('1357', 0) LIMIT 1
# SELECT 1 AS v LIMIT 1
# SELECT RELEASE_LOCK('1357') LIMIT 1

Options:

:wait

Do not raise an error, instead, wait until the advisory lock can be acquired.



211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 211

def with_advisory_lock(lock_id, opts=OPTS)
  lock_id = lock_id.to_s
  ds = dataset
  if server = opts[:server]
    ds = ds.server(server)
  end

  # MariaDB doesn't support negative values for infinite wait.  A wait of 34 years
  # should be reasonably similar to infinity for this case.
  timeout = opts[:wait] ? 1073741823 : 0

  synchronize(server) do |c|
    begin
      unless locked = ds.get{GET_LOCK(lock_id, timeout)} == 1
        raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
      end

      yield
    ensure
      ds.get{RELEASE_LOCK(lock_id)} if locked
    end
  end
end