lib/sequel/adapters/shared/mysql.rb in sequel-3.38.0 vs lib/sequel/adapters/shared/mysql.rb in sequel-3.39.0
- old
+ new
@@ -31,10 +31,11 @@
module DatabaseMethods
AUTO_INCREMENT = 'AUTO_INCREMENT'.freeze
CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}
COLUMN_DEFINITION_ORDER = [:collate, :null, :default, :unique, :primary_key, :auto_increment, :references]
PRIMARY = 'PRIMARY'.freeze
+ MYSQL_TIMESTAMP_RE = /\ACURRENT_(?:DATE|TIMESTAMP)?\z/
# MySQL's cast rules are restrictive in that you can't just cast to any possible
# database type.
def cast_type_literal(type)
CAST_TYPES[type] || super
@@ -108,12 +109,14 @@
run("XA ROLLBACK #{literal(transaction_id)}")
end
# Get version of MySQL server, used for determined capabilities.
def server_version
- m = /(\d+)\.(\d+)\.(\d+)/.match(get(SQL::Function.new(:version)))
- @server_version ||= (m[1].to_i * 10000) + (m[2].to_i * 100) + m[3].to_i
+ @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.
def supports_create_table_if_not_exists?
true
@@ -165,57 +168,110 @@
full_tables('VIEW', opts)
end
private
- # Use MySQL specific syntax for rename column, set column type, and
- # drop index cases.
- def alter_table_sql(table, op)
+ # Preprocess the array of operations. If it looks like some operations depend
+ # on results of earlier operations and may require reloading the schema to
+ # work correctly, split those operations into separate lists, and between each
+ # list, remove the cached schema so that the later operations deal with the
+ # then current table schema.
+ def apply_alter_table(name, ops)
+ modified_columns = []
+ op_groups = [[]]
+ ops.each do |op|
+ case op[:op]
+ when :add_column, :set_column_type, :set_column_null, :set_column_default
+ if modified_columns.include?(op[:name])
+ op_groups << []
+ else
+ modified_columns << op[:name]
+ end
+ when :rename_column
+ if modified_columns.include?(op[:name]) || modified_columns.include?(op[:new_name])
+ op_groups << []
+ end
+ modified_columns << op[:name] unless modified_columns.include?(op[:name])
+ modified_columns << op[:new_name] unless modified_columns.include?(op[:new_name])
+ end
+ op_groups.last << op
+ end
+
+ op_groups.each do |ops|
+ next if ops.empty?
+ alter_table_sql_list(name, ops).each{|sql| execute_ddl(sql)}
+ remove_cached_schema(name)
+ end
+ end
+
+ # Use MySQL specific syntax for some alter table operations.
+ def alter_table_op_sql(table, op)
case op[:op]
when :add_column
if related = op.delete(:table)
- sql = super(table, op)
+ sql = super
op[:table] = related
op[:key] ||= primary_key_from_schema(related)
- [sql, "ALTER TABLE #{quote_schema_table(table)} ADD FOREIGN KEY (#{quote_identifier(op[:name])})#{column_references_sql(op)}"]
+ sql << ", ADD FOREIGN KEY (#{quote_identifier(op[:name])})#{column_references_sql(op)}"
else
- super(table, op)
+ 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
- "ALTER TABLE #{quote_schema_table(table)} CHANGE COLUMN #{quote_identifier(op[:name])} #{column_definition_sql(op.merge(opts))}"
- when :drop_index
- "#{drop_index_sql(table, op)} ON #{quote_schema_table(table)}"
+ "CHANGE COLUMN #{quote_identifier(op[:name])} #{column_definition_sql(op.merge(opts))}"
when :drop_constraint
type = case op[:type]
when :primary_key
- return "ALTER TABLE #{quote_schema_table(table)} DROP PRIMARY KEY"
+ "DROP PRIMARY KEY"
when :foreign_key
- 'FOREIGN KEY'
+ "DROP FOREIGN KEY #{quote_identifier(op[:name])}"
when :unique
- 'INDEX'
- else
- raise(Error, "must specify constraint type via :type=>(:foreign_key|:primary_key|:unique) when dropping constraints on MySQL")
+ "DROP INDEX #{quote_identifier(op[:name])}"
end
- "ALTER TABLE #{quote_schema_table(table)} DROP #{type} #{quote_identifier(op[:name])}"
when :add_constraint
if op[:type] == :foreign_key
op[:key] ||= primary_key_from_schema(op[:table])
end
- super(table, op)
+ super
else
- super(table, op)
+ super
end
end
+ # MySQL server requires table names when dropping indexes.
+ def alter_table_sql(table, op)
+ case op[:op]
+ when :drop_index
+ "#{drop_index_sql(table, op)} ON #{quote_schema_table(table)}"
+ else
+ super
+ end
+ end
+
+ # Handle MySQL specific default format.
+ 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.
+ def combinable_alter_table_op?(op)
+ super && !(op[:op] == :add_constraint && op[:type] == :foreign_key)
+ end
+
# The SQL queries to execute on initial connection
def mysql_connection_setting_sqls
sqls = []
# Increase timeout so mysql server doesn't disconnect us
@@ -367,10 +423,20 @@
else
super
end
end
+ # Recognize MySQL set type.
+ def schema_column_type(db_type)
+ case db_type
+ when /\Aset/io
+ :set
+ else
+ super
+ end
+ end
+
# Use the MySQL specific DESCRIBE syntax to get a table description.
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))
@@ -385,10 +451,15 @@
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.
+ def supports_combining_alter_table_ops?
+ true
+ end
+
# Respect the :size option if given to produce
# tinyblob, mediumblob, and longblob if :tiny,
# :medium, or :long is given.
def type_literal_generic_file(column)
case column[:size]
@@ -665,9 +736,14 @@
# queries ORDER BY clause.
def supports_ordered_distinct_on?
false
end
+ # MySQL supports pattern matching via regular expressions
+ def supports_regexp?
+ true
+ end
+
# MySQL does support fractional timestamps in literal timestamps, but it
# ignores them. Also, using them seems to cause problems on 1.9. Since
# they are ignored anyway, not using them is probably best.
def supports_timestamp_usecs?
false