# File lib/sequel/adapters/shared/mysql.rb, line 417 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 47 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 53 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 58 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 65 def foreign_key_list(table, opts=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 88 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 97 def indexes(table, opts=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 115 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 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
MySQL supports CREATE TABLE IF NOT EXISTS syntax.
# File lib/sequel/adapters/shared/mysql.rb, line 128 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 133 def supports_prepared_transactions? server_version >= 50000 end
MySQL supports savepoints
# File lib/sequel/adapters/shared/mysql.rb, line 138 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 144 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 149 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 157 def tables(opts=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 163 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 174 def views(opts=OPTS) full_tables('VIEW', opts) end
Use MySQL specific syntax for some alter table operations.
# File lib/sequel/adapters/shared/mysql.rb, line 181 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 229 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 282 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 287 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 294 def begin_transaction(conn, opts=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 304 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 310 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 246 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 257 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 317 def commit_transaction(conn, opts=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 327 def create_table_sql(name, generator, options = OPTS) 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 381 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 386 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 392 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 397 def identifier_output_method_default nil end
Handle MySQL specific index SQL syntax
# File lib/sequel/adapters/shared/mysql.rb, line 402 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 262 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 417 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 422 def rollback_transaction(conn, opts=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 433 def schema_column_type(db_type) case db_type when /\Aset/o :set when /\Amediumint/o :integer when /\Amediumtext/o :string else super end end
Use the MySQL specific DESCRIBE syntax to get a table description.
# File lib/sequel/adapters/shared/mysql.rb, line 447 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 465 def supports_combining_alter_table_ops? true end
MySQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/mysql.rb, line 470 def supports_create_or_replace_view? true end
MySQL does not support named column constraints.
# File lib/sequel/adapters/shared/mysql.rb, line 475 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 497 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 482 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 507 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 512 def type_literal_generic_trueclass(column) :'tinyint(1)' end