# File lib/sequel/adapters/shared/mysql.rb, line 414 def primary_key_from_schema(table) schema(table).select{|a| a[1][:primary_key]}.map{|a| a[0]} end
Methods shared by Database instances that connect to MySQL, currently supported by the native and JDBC adapters.
MySQL's cast rules are restrictive in that you can't just cast to any possible database type.
# File lib/sequel/adapters/shared/mysql.rb, line 44 def cast_type_literal(type) CAST_TYPES[type] || super end
Commit an existing prepared transaction with the given transaction identifier string.
# File lib/sequel/adapters/shared/mysql.rb, line 50 def commit_prepared_transaction(transaction_id) run("XA COMMIT #{literal(transaction_id)}") end
MySQL uses the :mysql database type
# File lib/sequel/adapters/shared/mysql.rb, line 55 def database_type :mysql end
Use the Information Schema's KEY_COLUMN_USAGE table to get basic information on foreign key columns, but include the constraint name.
# File lib/sequel/adapters/shared/mysql.rb, line 62 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), :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
MySQL namespaces indexes per table.
# File lib/sequel/adapters/shared/mysql.rb, line 85 def global_index_namespace? false end
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.
# File lib/sequel/adapters/shared/mysql.rb, line 94 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 an existing prepared transaction with the given transaction identifier string.
# File lib/sequel/adapters/shared/mysql.rb, line 112 def rollback_prepared_transaction(transaction_id) run("XA ROLLBACK #{literal(transaction_id)}") end
Get version of MySQL server, used for determined capabilities.
# File lib/sequel/adapters/shared/mysql.rb, line 117 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
MySQL supports CREATE TABLE IF NOT EXISTS syntax.
# File lib/sequel/adapters/shared/mysql.rb, line 125 def supports_create_table_if_not_exists? true end
MySQL supports prepared transactions (two-phase commit) using XA
# File lib/sequel/adapters/shared/mysql.rb, line 130 def supports_prepared_transactions? server_version >= 50000 end
MySQL supports savepoints
# File lib/sequel/adapters/shared/mysql.rb, line 135 def supports_savepoints? server_version >= 50000 end
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
# File lib/sequel/adapters/shared/mysql.rb, line 141 def supports_savepoints_in_prepared_transactions? super && (server_version <= 50512 || server_version >= 50523) end
MySQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mysql.rb, line 146 def supports_transaction_isolation_levels? true end
Return an array of symbols specifying table names in the current database.
Options:
:server - Set the server to use
# File lib/sequel/adapters/shared/mysql.rb, line 154 def tables(opts={}) full_tables('BASE TABLE', opts) end
Changes the database in use by issuing a USE statement. I would be very careful if I used this.
# File lib/sequel/adapters/shared/mysql.rb, line 160 def use(db_name) disconnect @opts[:database] = db_name if self << "USE #{db_name}" @schemas = {} self end
Return an array of symbols specifying view names in the current database.
Options:
:server - Set the server to use
# File lib/sequel/adapters/shared/mysql.rb, line 171 def views(opts={}) full_tables('VIEW', opts) end
Use MySQL specific syntax for some alter table operations.
# File lib/sequel/adapters/shared/mysql.rb, line 178 def alter_table_op_sql(table, op) case op[:op] when :add_column if related = op.delete(:table) sql = super op[:table] = related op[:key] ||= primary_key_from_schema(related) sql << ", ADD FOREIGN KEY (#{quote_identifier(op[:name])})#{column_references_sql(op)}" else super end when :rename_column, :set_column_type, :set_column_null, :set_column_default o = op[:op] opts = schema(table).find{|x| x.first == op[:name]} opts = opts ? opts.last.dup : {} opts[:name] = o == :rename_column ? op[:new_name] : op[:name] opts[:type] = o == :set_column_type ? op[:type] : opts[:db_type] opts[:null] = o == :set_column_null ? op[:null] : opts[:allow_null] opts[:default] = o == :set_column_default ? op[:default] : opts[:ruby_default] opts.delete(:default) if opts[:default] == nil opts.delete(:primary_key) unless op[:type] || opts[:type] raise Error, "cannot determine database type to use for CHANGE COLUMN operation" end opts = op.merge(opts) opts.delete(:auto_increment) if op[:auto_increment] == false "CHANGE COLUMN #{quote_identifier(op[:name])} #{column_definition_sql(opts)}" when :drop_constraint case op[:type] when :primary_key "DROP PRIMARY KEY" when :foreign_key name = op[:name] || foreign_key_name(table, op[:columns]) "DROP FOREIGN KEY #{quote_identifier(name)}" when :unique "DROP INDEX #{quote_identifier(op[:name])}" end when :add_constraint if op[:type] == :foreign_key op[:key] ||= primary_key_from_schema(op[:table]) end super else super end end
MySQL server requires table names when dropping indexes.
# File lib/sequel/adapters/shared/mysql.rb, line 226 def alter_table_sql(table, op) case op[:op] when :drop_index "#{drop_index_sql(table, op)} ON #{quote_schema_table(table)}" when :drop_constraint if op[:type] == :primary_key if (pk = primary_key_from_schema(table)).length == 1 return [alter_table_sql(table, {:op=>:rename_column, :name=>pk.first, :new_name=>pk.first, :auto_increment=>false}), super] end end super else super end end
Use MySQL specific AUTO_INCREMENT text.
# File lib/sequel/adapters/shared/mysql.rb, line 279 def auto_increment_sql AUTO_INCREMENT end
MySQL needs to set transaction isolation before begining a transaction
# File lib/sequel/adapters/shared/mysql.rb, line 284 def begin_new_transaction(conn, opts) set_transaction_isolation(conn, opts) log_connection_execute(conn, begin_transaction_sql) end
Use XA START to start a new prepared transaction if the :prepare option is given.
# File lib/sequel/adapters/shared/mysql.rb, line 291 def begin_transaction(conn, opts={}) if (s = opts[:prepare]) && (th = _trans(conn))[:savepoint_level] == 0 log_connection_execute(conn, "XA START #{literal(s)}") th[:savepoint_level] += 1 else super end end
The order of the column definition, as an array of symbols.
# File lib/sequel/adapters/shared/mysql.rb, line 301 def column_definition_order COLUMN_DEFINITION_ORDER end
MySQL doesn't allow default values on text columns, so ignore if it the generic text type is used
# File lib/sequel/adapters/shared/mysql.rb, line 307 def column_definition_sql(column) column.delete(:default) if column[:type] == File || (column[:type] == String && column[:text] == true) super end
Handle MySQL specific default format.
# File lib/sequel/adapters/shared/mysql.rb, line 243 def column_schema_normalize_default(default, type) if column_schema_default_string_type?(type) return if [:date, :datetime, :time].include?(type) && MYSQL_TIMESTAMP_RE.match(default) default = "'#{default.gsub("'", "''").gsub('\\', '\\\\')}'" end super(default, type) end
Don't allow combining adding foreign key operations with other operations, since in some cases adding a foreign key constraint in the same query as other operations results in MySQL error 150.
# File lib/sequel/adapters/shared/mysql.rb, line 254 def combinable_alter_table_op?(op) super && !(op[:op] == :add_constraint && op[:type] == :foreign_key) && !(op[:op] == :drop_constraint && op[:type] == :primary_key) end
Prepare the XA transaction for a two-phase commit if the :prepare option is given.
# File lib/sequel/adapters/shared/mysql.rb, line 314 def commit_transaction(conn, opts={}) if (s = opts[:prepare]) && _trans(conn)[:savepoint_level] <= 1 log_connection_execute(conn, "XA END #{literal(s)}") log_connection_execute(conn, "XA PREPARE #{literal(s)}") else super end end
Use MySQL specific syntax for engine type and character encoding
# File lib/sequel/adapters/shared/mysql.rb, line 324 def create_table_sql(name, generator, options = {}) engine = options.fetch(:engine, Sequel::MySQL.default_engine) charset = options.fetch(:charset, Sequel::MySQL.default_charset) collate = options.fetch(:collate, Sequel::MySQL.default_collate) generator.constraints.sort_by{|c| (c[:type] == :primary_key) ? -1 : 1} # Proc for figuring out the primary key for a given table. key_proc = lambda do |t| if t == name if pk = generator.primary_key_name [pk] elsif !(pkc = generator.constraints.select{|con| con[:type] == :primary_key}).empty? pkc.first[:columns] end else primary_key_from_schema(t) end end # Manually set the keys, since MySQL requires one, it doesn't use the primary # key if none are specified. generator.constraints.each do |c| if c[:type] == :foreign_key c[:key] ||= key_proc.call(c[:table]) end end # Split column constraints into table constraints in some cases: # * foreign key - Always # * unique, primary_key - Only if constraint has a name generator.columns.each do |c| if t = c.delete(:table) same_table = t == name k = c[:key] key ||= key_proc.call(t) if same_table && !k.nil? generator.constraints.unshift(:type=>:unique, :columns=>Array(k)) end generator.foreign_key([c[:name]], t, c.merge(:name=>c[:foreign_key_constraint_name], :type=>:foreign_key, :key=>key)) end end "#{super}#{" ENGINE=#{engine}" if engine}#{" DEFAULT CHARSET=#{charset}" if charset}#{" DEFAULT COLLATE=#{collate}" if collate}" end
# File lib/sequel/adapters/shared/mysql.rb, line 378 def database_error_regexps DATABASE_ERROR_REGEXPS end
Backbone of the tables and views support using SHOW FULL TABLES.
# File lib/sequel/adapters/shared/mysql.rb, line 383 def full_tables(type, opts) m = output_identifier_meth metadata_dataset.with_sql('SHOW FULL TABLES').server(opts[:server]).map{|r| m.call(r.values.first) if r.delete(:Table_type) == type}.compact end
MySQL folds unquoted identifiers to lowercase, so it shouldn't need to upcase identifiers on input.
# File lib/sequel/adapters/shared/mysql.rb, line 389 def identifier_input_method_default nil end
MySQL folds unquoted identifiers to lowercase, so it shouldn't need to upcase identifiers on output.
# File lib/sequel/adapters/shared/mysql.rb, line 394 def identifier_output_method_default nil end
Handle MySQL specific index SQL syntax
# File lib/sequel/adapters/shared/mysql.rb, line 399 def index_definition_sql(table_name, index) index_name = quote_identifier(index[:name] || default_index_name(table_name, index[:columns])) index_type = case index[:type] when :full_text "FULLTEXT " when :spatial "SPATIAL " else using = " USING #{index[:type]}" unless index[:type] == nil "UNIQUE " if index[:unique] end "CREATE #{index_type}INDEX #{index_name}#{using} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}" end
The SQL queries to execute on initial connection
# File lib/sequel/adapters/shared/mysql.rb, line 259 def mysql_connection_setting_sqls sqls = [] # Increase timeout so mysql server doesn't disconnect us # Value used by default is maximum allowed value on Windows. sqls << "SET @@wait_timeout = #{opts[:timeout] || 2147483}" # By default, MySQL 'where id is null' selects the last inserted id sqls << "SET SQL_AUTO_IS_NULL=0" unless opts[:auto_is_null] # If the user has specified one or more sql modes, enable them if sql_mode = opts[:sql_mode] sql_mode = Array(sql_mode).join(',').upcase sqls << "SET sql_mode = '#{sql_mode}'" end sqls end
Parse the schema for the given table to get an array of primary key columns
# File lib/sequel/adapters/shared/mysql.rb, line 414 def primary_key_from_schema(table) schema(table).select{|a| a[1][:primary_key]}.map{|a| a[0]} end
Rollback the currently open XA transaction
# File lib/sequel/adapters/shared/mysql.rb, line 419 def rollback_transaction(conn, opts={}) if (s = opts[:prepare]) && _trans(conn)[:savepoint_level] <= 1 log_connection_execute(conn, "XA END #{literal(s)}") log_connection_execute(conn, "XA PREPARE #{literal(s)}") log_connection_execute(conn, "XA ROLLBACK #{literal(s)}") else super end end
Recognize MySQL set type.
# File lib/sequel/adapters/shared/mysql.rb, line 430 def schema_column_type(db_type) case db_type when /\Aset/o :set else super end end
Use the MySQL specific DESCRIBE syntax to get a table description.
# File lib/sequel/adapters/shared/mysql.rb, line 440 def schema_parse_table(table_name, opts) m = output_identifier_meth(opts[:dataset]) im = input_identifier_meth(opts[:dataset]) table = SQL::Identifier.new(im.call(table_name)) table = SQL::QualifiedIdentifier.new(im.call(opts[:schema]), table) if opts[:schema] metadata_dataset.with_sql("DESCRIBE ?", table).map do |row| row[:auto_increment] = true if row.delete(:Extra).to_s =~ /auto_increment/o row[:allow_null] = row.delete(:Null) == 'YES' row[:default] = row.delete(:Default) row[:primary_key] = row.delete(:Key) == 'PRI' row[:default] = nil if blank_object?(row[:default]) row[:db_type] = row.delete(:Type) row[:type] = schema_column_type(row[:db_type]) [m.call(row.delete(:Field)), row] end end
MySQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/mysql.rb, line 458 def supports_combining_alter_table_ops? true end
MySQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/mysql.rb, line 463 def supports_create_or_replace_view? true end
MySQL does not support named column constraints.
# File lib/sequel/adapters/shared/mysql.rb, line 468 def supports_named_column_constraints? false end
MySQL has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mysql.rb, line 490 def type_literal_generic_datetime(column) if column[:default] == Sequel::CURRENT_TIMESTAMP :timestamp else :datetime end end
Respect the :size option if given to produce tinyblob, mediumblob, and longblob if :tiny, :medium, or :long is given.
# File lib/sequel/adapters/shared/mysql.rb, line 475 def type_literal_generic_file(column) case column[:size] when :tiny # < 2^8 bytes :tinyblob when :medium # < 2^24 bytes :mediumblob when :long # < 2^32 bytes :longblob else # 2^16 bytes :blob end end
MySQL has both datetime and timestamp classes, most people are going to want datetime
# File lib/sequel/adapters/shared/mysql.rb, line 500 def type_literal_generic_time(column) column[:only_time] ? :time : type_literal_generic_datetime(column) end
MySQL doesn't have a true boolean class, so it uses tinyint(1)
# File lib/sequel/adapters/shared/mysql.rb, line 505 def type_literal_generic_trueclass(column) :'tinyint(1)' end