lib/sequel/adapters/shared/mysql.rb in sequel-2.12.0 vs lib/sequel/adapters/shared/mysql.rb in sequel-3.0.0
- old
+ new
@@ -13,18 +13,41 @@
# Methods shared by Database instances that connect to MySQL,
# currently supported by the native and JDBC adapters.
module DatabaseMethods
AUTO_INCREMENT = 'AUTO_INCREMENT'.freeze
- NOT_NULL = Sequel::Database::NOT_NULL
- NULL = Sequel::Database::NULL
- PRIMARY_KEY = Sequel::Database::PRIMARY_KEY
- TYPES = Sequel::Database::TYPES.merge(DateTime=>'datetime', \
- TrueClass=>'tinyint', FalseClass=>'tinyint')
- UNIQUE = Sequel::Database::UNIQUE
- UNSIGNED = Sequel::Database::UNSIGNED
+ CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}
+ PRIMARY = 'PRIMARY'.freeze
+ # 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
+ end
+
+ # MySQL uses the :mysql database type
+ def database_type
+ :mysql
+ end
+
+ # Return a hash containing index information. Hash keys are index name symbols.
+ # Values are subhashes with two keys, :columns and :unique. The value of :columns
+ # is an array of symbols of column names. The value of :unique is true or false
+ # depending on if the index is unique.
+ def indexes(table)
+ 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
+ i = indexes[m.call(name)] ||= {:columns=>[], :unique=>r[:Non_unique] != 1}
+ i[:columns] << m.call(r[:Column_name])
+ end
+ indexes
+ 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
end
@@ -32,14 +55,12 @@
# Return an array of symbols specifying table names in the current database.
#
# Options:
# * :server - Set the server to use
def tables(opts={})
- ds = self['SHOW TABLES'].server(opts[:server])
- ds.identifier_output_method = nil
- ds2 = dataset
- ds.map{|r| ds2.send(:output_identifier, r.values.first)}
+ m = output_identifier_meth
+ metadata_dataset.with_sql('SHOW TABLES').server(opts[:server]).map{|r| m.call(r.values.first)}
end
# Changes the database in use by issuing a USE statement. I would be
# very careful if I used this.
def use(db_name)
@@ -61,14 +82,19 @@
op[:table] = related
[sql, "ALTER TABLE #{quote_schema_table(table)} ADD FOREIGN KEY (#{quote_identifier(op[:name])})#{default_column_references_sql(op)}"]
else
super(table, op)
end
- when :rename_column
- "ALTER TABLE #{quote_schema_table(table)} CHANGE COLUMN #{quote_identifier(op[:name])} #{quote_identifier(op[:new_name])} #{type_literal(op)}"
- when :set_column_type
- "ALTER TABLE #{quote_schema_table(table)} CHANGE COLUMN #{quote_identifier(op[:name])} #{quote_identifier(op[:name])} #{type_literal(op)}"
+ when :rename_column, :set_column_type, :set_column_null, :set_column_default
+ o = op[:op]
+ opts = schema(table).find{|x| x.first == op[:name]}
+ old_opts = opts ? opts.last : {}
+ name = o == :rename_column ? op[:new_name] : op[:name]
+ type = o == :set_column_type ? op[:type] : old_opts[:db_type]
+ null = o == :set_column_null ? op[:null] : old_opts[:allow_null]
+ default = o == :set_column_default ? op[:default] : (old_opts[:default].lit if old_opts[:default])
+ "ALTER TABLE #{quote_schema_table(table)} CHANGE COLUMN #{quote_identifier(op[:name])} #{column_definition_sql(:name=>name, :type=>type, :null=>null, :default=>default)}"
when :drop_index
"#{drop_index_sql(table, op)} ON #{quote_schema_table(table)}"
else
super(table, op)
end
@@ -83,17 +109,15 @@
def column_references_sql(column)
"#{", FOREIGN KEY (#{quote_identifier(column[:name])})" unless column[:type] == :check}#{super(column)}"
end
# Use MySQL specific syntax for engine type and character encoding
- def create_table_sql_list(name, columns, indexes = nil, options = {})
- options[:engine] = Sequel::MySQL.default_engine unless options.include?(:engine)
- options[:charset] = Sequel::MySQL.default_charset unless options.include?(:charset)
- options[:collate] = Sequel::MySQL.default_collate unless options.include?(:collate)
- sql = ["CREATE TABLE #{quote_schema_table(name)} (#{column_list_sql(columns)})#{" ENGINE=#{options[:engine]}" if options[:engine]}#{" DEFAULT CHARSET=#{options[:charset]}" if options[:charset]}#{" DEFAULT COLLATE=#{options[:collate]}" if options[:collate]}"]
- sql.concat(index_list_sql_list(name, indexes)) if indexes && !indexes.empty?
- sql
+ def create_table_sql(name, generator, options = {})
+ engine = options.include?(:engine) ? options[:engine] : Sequel::MySQL.default_engine
+ charset = options.include?(:charset) ? options[:charset] : Sequel::MySQL.default_charset
+ collate = options.include?(:collate) ? options[:collate] : Sequel::MySQL.default_collate
+ "#{super}#{" ENGINE=#{engine}" if engine}#{" DEFAULT CHARSET=#{charset}" if charset}#{" DEFAULT COLLATE=#{collate}" if collate}"
end
# MySQL folds unquoted identifiers to lowercase, so it shouldn't need to upcase identifiers on input.
def identifier_input_method_default
nil
@@ -114,51 +138,62 @@
"SPATIAL "
else
using = " USING #{index[:type]}" unless index[:type] == nil
"UNIQUE " if index[:unique]
end
- "CREATE #{index_type}INDEX #{index_name} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}#{using}"
+ "CREATE #{index_type}INDEX #{index_name}#{using} ON #{quote_schema_table(table_name)} #{literal(index[:columns])}"
end
+ # MySQL treats integer primary keys as autoincrementing.
+ def schema_autoincrementing_primary_key?(schema)
+ super and schema[:db_type] =~ /int/io
+ end
+
# Use the MySQL specific DESCRIBE syntax to get a table description.
def schema_parse_table(table_name, opts)
- ds = self["DESCRIBE ?", SQL::Identifier.new(table_name)]
- ds.identifier_output_method = nil
- ds2 = dataset
- ds.map do |row|
+ m = output_identifier_meth
+ im = input_identifier_meth
+ metadata_dataset.with_sql("DESCRIBE ?", SQL::Identifier.new(im.call(table_name))).map do |row|
row.delete(:Extra)
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])
- [ds2.send(:output_identifier, row.delete(:Field)), row]
+ [m.call(row.delete(:Field)), row]
end
end
- # Override the standard type conversions with MySQL specific ones
- def type_literal_base(column)
- TYPES[column[:type]]
+ # MySQL has both datetime and timestamp classes, most people are going
+ # to want datetime
+ def type_literal_generic_datetime(column)
+ :datetime
end
+
+ # MySQL has both datetime and timestamp classes, most people are going
+ # to want datetime
+ def type_literal_generic_time(column)
+ column[:only_time] ? :time : :datetime
+ end
+
+ # MySQL doesn't have a true boolean class, so it uses tinyint
+ # MySQL doesn't have a true boolean class, so it uses tinyint
+ def type_literal_generic_trueclass(column)
+ :tinyint
+ end
end
# Dataset methods shared by datasets that use MySQL databases.
module DatasetMethods
include Dataset::UnsupportedIntersectExcept
BOOL_TRUE = '1'.freeze
BOOL_FALSE = '0'.freeze
- CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}
TIMESTAMP_FORMAT = "'%Y-%m-%d %H:%M:%S'".freeze
COMMA_SEPARATOR = ', '.freeze
- # MySQL can't use the varchar type in a cast.
- def cast_sql(expr, type)
- "CAST(#{literal(expr)} AS #{CAST_TYPES[type] || db.send(:type_literal_base, :type=>type)})"
- end
-
# MySQL specific syntax for LIKE/REGEXP searches, as well as
# string concatenation.
def complex_expression_sql(op, args)
case op
when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
@@ -173,49 +208,32 @@
super(op, args)
end
end
# MySQL supports ORDER and LIMIT clauses in DELETE statements.
- def delete_sql(opts = (defarg=true;nil))
- if defarg
- sql = super()
- opts = @opts
- else
- sql = super
- opts = opts ? @opts.merge(opts) : @opts
- end
-
- if order = opts[:order]
- sql << " ORDER BY #{expression_list(order)}"
- end
- if limit = opts[:limit]
- sql << " LIMIT #{limit}"
- end
-
+ def delete_sql
+ sql = super
+ sql << " ORDER BY #{expression_list(opts[:order])}" if opts[:order]
+ sql << " LIMIT #{opts[:limit]}" if opts[:limit]
sql
end
# MySQL doesn't support DISTINCT ON
def distinct(*columns)
raise(Error, "DISTINCT ON not supported by MySQL") unless columns.empty?
super
end
- # MySQL specific full text search syntax.
+ # Adds full text filter
def full_text_search(cols, terms, opts = {})
- mode = opts[:boolean] ? " IN BOOLEAN MODE" : ""
- s = if Array === terms
- if mode.empty?
- "MATCH #{literal(Array(cols))} AGAINST #{literal(terms)}"
- else
- "MATCH #{literal(Array(cols))} AGAINST (#{literal(terms)[1...-1]}#{mode})"
- end
- else
- "MATCH #{literal(Array(cols))} AGAINST (#{literal(terms)}#{mode})"
- end
- filter(s)
+ filter(full_text_sql(cols, terms, opts))
end
+
+ # MySQL specific full text search syntax.
+ def full_text_sql(cols, term, opts = {})
+ "MATCH #{literal(Array(cols))} AGAINST (#{literal(Array(term).join(' '))}#{" IN BOOLEAN MODE" if opts[:boolean]})"
+ end
# MySQL allows HAVING clause on ungrouped datasets.
def having(*cond, &block)
_filter(:having, *cond, &block)
end
@@ -309,15 +327,10 @@
if values.empty?
"REPLACE INTO #{from} DEFAULT VALUES"
else
values = values[0] if values.size == 1
- # if hash or array with keys we need to transform the values
- if @transform && (values.is_a?(Hash) || (values.is_a?(Array) && values.keys))
- values = transform_save(values)
- end
-
case values
when Array
if values.empty?
"REPLACE INTO #{from} DEFAULT VALUES"
else
@@ -342,25 +355,13 @@
end
end
end
# MySQL supports ORDER and LIMIT clauses in UPDATE statements.
- def update_sql(values, opts = (defarg=true;nil))
- if defarg
- sql = super(values)
- opts = @opts
- else
- sql = super
- opts = opts ? @opts.merge(opts) : @opts
- end
-
- if order = opts[:order]
- sql << " ORDER BY #{expression_list(order)}"
- end
- if limit = opts[:limit]
- sql << " LIMIT #{limit}"
- end
-
+ def update_sql(values)
+ sql = super
+ sql << " ORDER BY #{expression_list(opts[:order])}" if opts[:order]
+ sql << " LIMIT #{opts[:limit]}" if opts[:limit]
sql
end
private