# File lib/sequel/adapters/shared/mysql.rb, line 454 def primary_key_from_schema(table) schema(table).select{|a| a[1][:primary_key]}.map{|a| a[0]} end
module Sequel::MySQL::DatabaseMethods
Methods shared by Database instances that connect to MySQL, currently supported by the native and JDBC adapters.
Constants
- AUTO_INCREMENT
- CAST_TYPES
- COLUMN_DEFINITION_ORDER
- DATABASE_ERROR_REGEXPS
- MYSQL_TIMESTAMP_RE
- PRIMARY
Public Instance Methods
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 52 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 58 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 63 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 70 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). 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
# File lib/sequel/adapters/shared/mysql.rb, line 92 def freeze server_version supports_timestamp_usecs? super end
MySQL namespaces indexes per table.
# File lib/sequel/adapters/shared/mysql.rb, line 99 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 108 def indexes(table, opts=OPTS) indexes = {} remove_indexes = [] m = output_identifier_meth im = input_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
Rollback an existing prepared transaction with the given transaction identifier string.
# File lib/sequel/adapters/shared/mysql.rb, line 135 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 140 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 148 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 153 def supports_prepared_transactions? server_version >= 50000 end
MySQL 5+ supports savepoints
# File lib/sequel/adapters/shared/mysql.rb, line 158 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 164 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 172 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
MySQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mysql.rb, line 178 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 186 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 192 def use(db_name) Sequel::Deprecation.deprecate("Database#use", "Create a new Sequel::Database instance instead of using Database#use") 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 204 def views(opts=OPTS) full_tables('VIEW', opts) end
Private Instance Methods
# File lib/sequel/adapters/shared/mysql.rb, line 210 def alter_table_add_column_sql(table, op) pos = if after_col = op[:after] " AFTER #{quote_identifier(after_col)}" elsif op[:first] " FIRST" end sql = if related = op.delete(:table) sql = super + "#{pos}, ADD " op[:table] = related op[:key] ||= primary_key_from_schema(related) 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}#{pos}" end end
# File lib/sequel/adapters/shared/mysql.rb, line 254 def alter_table_add_constraint_sql(table, op) if op[:type] == :foreign_key op[:key] ||= primary_key_from_schema(op[:table]) end super end
# File lib/sequel/adapters/shared/mysql.rb, line 230 def alter_table_change_column_sql(table, op) 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)}" end
# File lib/sequel/adapters/shared/mysql.rb, line 261 def alter_table_drop_constraint_sql(table, op) 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 end
MySQL server requires table names when dropping indexes.
# File lib/sequel/adapters/shared/mysql.rb, line 274 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 329 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 334 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 341 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 350 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 356 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 291 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 302 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 363 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 373 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] elsif !(pkc = generator.columns.select{|con| con[:primary_key] == true}).empty? pkc.map{|c| c[:name]} 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 427 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 432 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
Handle MySQL specific index SQL syntax
# File lib/sequel/adapters/shared/mysql.rb, line 438 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 307 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
Rollback the currently open XA transaction
# File lib/sequel/adapters/shared/mysql.rb, line 459 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 470 def schema_column_type(db_type) case db_type when /\Aset/io :set when /\Amediumint/io :integer when /\Amediumtext/io :string else super end end
Use the MySQL specific DESCRIBE syntax to get a table description.
# File lib/sequel/adapters/shared/mysql.rb, line 484 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/io) 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 504 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 509 def supports_combining_alter_table_ops? true end
MySQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/mysql.rb, line 514 def supports_create_or_replace_view? true end
MySQL does not support named column constraints.
# File lib/sequel/adapters/shared/mysql.rb, line 519 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 541 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 526 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 553 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 566 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 571 def view_with_check_option_support :local if server_version >= 50002 end