# File lib/sequel/adapters/shared/mysql.rb, line 431 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, opts=OPTS) run("XA COMMIT #{literal(transaction_id)}", opts) 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, opts=OPTS) run("XA ROLLBACK #{literal(transaction_id)}", opts) 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 5+ 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 5+ 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
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.
# File lib/sequel/adapters/shared/mysql.rb, line 152 def supports_timestamp_usecs? @supports_timestamp_usecs ||= server_version >= 50605 && typecast_value_boolean(opts[:fractional_seconds]) end
MySQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mysql.rb, line 157 def supports_transaction_isolation_levels? true end
Return an array of symbols specifying table names in the current database.
Options:
Set the server to use
# File lib/sequel/adapters/shared/mysql.rb, line 165 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 171 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:
Set the server to use
# File lib/sequel/adapters/shared/mysql.rb, line 182 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 189 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 " if constraint_name = op.delete(:foreign_key_constraint_name) sql << "CONSTRAINT #{quote_identifier(constraint_name)} " end sql << "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) if op.has_key?(:auto_increment) opts[:auto_increment] = op[:auto_increment] end "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 243 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 298 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 303 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 310 def begin_transaction(conn, opts=OPTS) if (s = opts[:prepare]) && savepoint_level(conn) == 1 log_connection_execute(conn, "XA START #{literal(s)}") else super end end
The order of the column definition, as an array of symbols.
# File lib/sequel/adapters/shared/mysql.rb, line 319 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 325 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 260 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 271 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 332 def commit_transaction(conn, opts=OPTS) if (s = opts[:prepare]) && savepoint_level(conn) <= 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 342 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 key = c[:key] || key_proc.call(t) if same_table && !key.nil? generator.constraints.unshift(:type=>:unique, :columns=>Array(key)) 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 394 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 399 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 405 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 410 def identifier_output_method_default nil end
Handle MySQL specific index SQL syntax
# File lib/sequel/adapters/shared/mysql.rb, line 415 def index_definition_sql(table_name, index) index_name = quote_identifier(index[:name] || default_index_name(table_name, index[:columns])) raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes? 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 276 def mysql_connection_setting_sqls sqls = [] if wait_timeout = opts.fetch(:timeout, 2147483) # Increase timeout so mysql server doesn't disconnect us # Value used by default is maximum allowed value on Windows. sqls << "SET @@wait_timeout = #{wait_timeout}" end # 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 431 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 436 def rollback_transaction(conn, opts=OPTS) if (s = opts[:prepare]) && savepoint_level(conn) <= 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 447 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 461 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| extra = row.delete(:Extra) if row[:primary_key] = row.delete(:Key) == 'PRI' row[:auto_increment] = !!(extra.to_s =~ /auto_increment/o) end row[:allow_null] = row.delete(:Null) == 'YES' row[:default] = row.delete(:Default) row[:db_type] = row.delete(:Type) row[:type] = schema_column_type(row[:db_type]) [m.call(row.delete(:Field)), row] end end
Split DROP INDEX ops on MySQL 5.6+, as dropping them in the same statement as dropping a related foreign key causes an error.
# File lib/sequel/adapters/shared/mysql.rb, line 481 def split_alter_table_op?(op) server_version >= 50600 && (op[:op] == :drop_index || (op[:op] == :drop_constraint && op[:type] == :unique)) end
MySQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/mysql.rb, line 486 def supports_combining_alter_table_ops? true end
MySQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/mysql.rb, line 491 def supports_create_or_replace_view? true end
MySQL does not support named column constraints.
# File lib/sequel/adapters/shared/mysql.rb, line 496 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 518 def type_literal_generic_datetime(column) if supports_timestamp_usecs? :'datetime(6)' elsif 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 503 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 530 def type_literal_generic_time(column) if column[:only_time] if supports_timestamp_usecs? :'time(6)' else :time end else type_literal_generic_datetime(column) end end
MySQL doesn't have a true boolean class, so it uses tinyint(1)
# File lib/sequel/adapters/shared/mysql.rb, line 543 def type_literal_generic_trueclass(column) :'tinyint(1)' end
MySQL 5.0.2+ supports views with check option.
# File lib/sequel/adapters/shared/mysql.rb, line 548 def view_with_check_option_support :local if server_version >= 50002 end