module Sequel::MySQL::DatabaseMethods

  1. lib/sequel/adapters/shared/mysql.rb

Constants

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  
DATABASE_ERROR_REGEXPS = { /Duplicate entry .+ for key/ => UniqueConstraintViolation, /foreign key constraint fails/ => ForeignKeyConstraintViolation, /cannot be null/ => NotNullConstraintViolation, /Deadlock found when trying to get lock; try restarting transaction/ => SerializationFailure, /CONSTRAINT .+ failed for/ => CheckConstraintViolation, /\A(Statement aborted because lock\(s\) could not be acquired immediately and NOWAIT is set\.|Lock wait timeout exceeded; try restarting transaction)/ => DatabaseLockTimeout, }.freeze  

Attributes

default_charset [RW]

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

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

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

Public Instance methods

cast_type_literal(type)

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

[show source] 
   # File lib/sequel/adapters/shared/mysql.rb
38 def cast_type_literal(type)
39   CAST_TYPES[type] || super
40 end
commit_prepared_transaction(transaction_id, opts=OPTS)
[show source] 
   # File lib/sequel/adapters/shared/mysql.rb
42 def commit_prepared_transaction(transaction_id, opts=OPTS)
43   run("XA COMMIT #{literal(transaction_id)}", opts)
44 end
database_type()
[show source] 
   # File lib/sequel/adapters/shared/mysql.rb
46 def database_type
47   :mysql
48 end
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.

[show source] 
   # File lib/sequel/adapters/shared/mysql.rb
53 def foreign_key_list(table, opts=OPTS)
54   m = output_identifier_meth
55   im = input_identifier_meth
56   ds = metadata_dataset.
57     from(Sequel[:INFORMATION_SCHEMA][:KEY_COLUMN_USAGE]).
58     where(:TABLE_NAME=>im.call(table), :TABLE_SCHEMA=>Sequel.function(:DATABASE)).
59     exclude(:CONSTRAINT_NAME=>'PRIMARY').
60     exclude(:REFERENCED_TABLE_NAME=>nil).
61     order(:CONSTRAINT_NAME, :POSITION_IN_UNIQUE_CONSTRAINT).
62     select(Sequel[:CONSTRAINT_NAME].as(:name), Sequel[:COLUMN_NAME].as(:column), Sequel[:REFERENCED_TABLE_NAME].as(:table), Sequel[:REFERENCED_COLUMN_NAME].as(:key))
63   
64   h = {}
65   ds.each do |row|
66     if r = h[row[:name]]
67       r[:columns] << m.call(row[:column])
68       r[:key] << m.call(row[:key])
69     else
70       h[row[:name]] = {:name=>m.call(row[:name]), :columns=>[m.call(row[:column])], :table=>m.call(row[:table]), :key=>[m.call(row[:key])]}
71     end
72   end
73   h.values
74 end
freeze()
[show source] 
   # File lib/sequel/adapters/shared/mysql.rb
76 def freeze
77   server_version
78   mariadb?
79   supports_timestamp_usecs?
80   super
81 end
global_index_namespace?()

MySQL namespaces indexes per table.

[show source] 
   # File lib/sequel/adapters/shared/mysql.rb
84 def global_index_namespace?
85   false
86 end
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.

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
 93 def indexes(table, opts=OPTS)
 94   indexes = {}
 95   remove_indexes = []
 96   m = output_identifier_meth
 97   schema, table = schema_and_table(table)
 98 
 99   table = Sequel::SQL::Identifier.new(table)
100   sql = "SHOW INDEX FROM #{literal(table)}"
101   if schema
102     schema = Sequel::SQL::Identifier.new(schema)
103     sql += " FROM #{literal(schema)}"
104   end
105 
106   metadata_dataset.with_sql(sql).each do |r|
107     name = r[:Key_name]
108     next if name == 'PRIMARY'
109     name = m.call(name)
110     remove_indexes << name if r[:Sub_part] && ! opts[:partial]
111     i = indexes[name] ||= {:columns=>[], :unique=>r[:Non_unique] != 1}
112     i[:columns] << m.call(r[:Column_name])
113   end
114   indexes.reject{|k,v| remove_indexes.include?(k)}
115 end
mariadb?()

Whether the database is MariaDB and not MySQL

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
122 def mariadb?
123   return @is_mariadb if defined?(@is_mariadb)
124   @is_mariadb = !(fetch('SELECT version()').single_value! !~ /mariadb/i)
125 end
rename_tables(*renames)

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
[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
195 def rename_tables(*renames)
196   execute_ddl(rename_tables_sql(renames))
197   renames.each{|from,| remove_cached_schema(from)}
198 end
rollback_prepared_transaction(transaction_id, opts=OPTS)
[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
117 def rollback_prepared_transaction(transaction_id, opts=OPTS)
118   run("XA ROLLBACK #{literal(transaction_id)}", opts)
119 end
server_version()

Get version of MySQL server, used for determined capabilities.

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
128 def server_version
129   @server_version ||= begin
130     m = /(\d+)\.(\d+)\.(\d+)/.match(fetch('SELECT version()').single_value!)
131     (m[1].to_i * 10000) + (m[2].to_i * 100) + m[3].to_i
132   end
133 end
supports_create_table_if_not_exists?()

MySQL supports CREATE TABLE IF NOT EXISTS syntax.

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
136 def supports_create_table_if_not_exists?
137   true
138 end
supports_generated_columns?()

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

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
141 def supports_generated_columns?
142   server_version >= (mariadb? ? 50200 : 50706)
143 end
supports_prepared_transactions?()

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

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
146 def supports_prepared_transactions?
147   server_version >= 50000
148 end
supports_savepoints?()

MySQL 5+ supports savepoints

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
151 def supports_savepoints?
152   server_version >= 50000
153 end
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

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
157 def supports_savepoints_in_prepared_transactions?
158   super && (server_version <= 50512 || server_version >= 50523)
159 end
supports_timestamp_usecs?()

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.

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
165 def supports_timestamp_usecs?
166   return @supports_timestamp_usecs if defined?(@supports_timestamp_usecs)
167   @supports_timestamp_usecs = server_version >= 50605 && typecast_value_boolean(opts[:fractional_seconds])
168 end
supports_transaction_isolation_levels?()

MySQL supports transaction isolation levels

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
171 def supports_transaction_isolation_levels?
172   true
173 end
tables(opts=OPTS)

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

Options:

:server

Set the server to use

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
179 def tables(opts=OPTS)
180   full_tables('BASE TABLE', opts)
181 end
views(opts=OPTS)

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

Options:

:server

Set the server to use

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
187 def views(opts=OPTS)
188   full_tables('VIEW', opts)
189 end
with_advisory_lock(lock_id, opts=OPTS)

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.

[show source] 
    # File lib/sequel/adapters/shared/mysql.rb
211 def with_advisory_lock(lock_id, opts=OPTS)
212   lock_id = lock_id.to_s
213   ds = dataset
214   if server = opts[:server]
215     ds = ds.server(server)
216   end
217 
218   # MariaDB doesn't support negative values for infinite wait.  A wait of 34 years
219   # should be reasonably similar to infinity for this case.
220   timeout = opts[:wait] ? 1073741823 : 0
221 
222   synchronize(server) do |c|
223     begin
224       unless locked = ds.get{GET_LOCK(lock_id, timeout)} == 1
225         raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
226       end
227 
228       yield
229     ensure
230       ds.get{RELEASE_LOCK(lock_id)} if locked
231     end
232   end
233 end