mysql.rb in sequel-3.2.0

- old
+ new

@@ -1,23 +1,15 @@
-Sequel.require %w'unsupported savepoint_transactions', 'adapters/utils'
-
 module Sequel
-  class Database
-    # Keep default column_references_sql for add_foreign_key support
-    alias default_column_references_sql column_references_sql
-  end
   module MySQL
     class << self
       # Set the default options used for CREATE TABLE
       attr_accessor :default_charset, :default_collate, :default_engine
     end
 
     # Methods shared by Database instances that connect to MySQL,
     # currently supported by the native and JDBC adapters.
     module DatabaseMethods
-      include Sequel::Database::SavepointTransactions
-    
       AUTO_INCREMENT = 'AUTO_INCREMENT'.freeze
       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
@@ -66,10 +58,15 @@
       def tables(opts={})
         m = output_identifier_meth
         metadata_dataset.with_sql('SHOW TABLES').server(opts[:server]).map{|r| m.call(r.values.first)}
       end
       
+      # MySQL supports savepoints
+      def supports_savepoints?
+        true
+      end
+
       # Changes the database in use by issuing a USE statement.  I would be
       # very careful if I used this.
       def use(db_name)
         disconnect
         @opts[:database] = db_name if self << "USE #{db_name}"
@@ -85,23 +82,23 @@
         case op[:op]
         when :add_column
           if related = op.delete(:table)
             sql = super(table, op)
             op[:table] = related
-            [sql, "ALTER TABLE #{quote_schema_table(table)} ADD FOREIGN KEY (#{quote_identifier(op[:name])})#{default_column_references_sql(op)}"]
+            [sql, "ALTER TABLE #{quote_schema_table(table)} ADD FOREIGN KEY (#{quote_identifier(op[:name])})#{column_references_sql(op)}"]
           else
             super(table, op)
           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]}
           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)}"
+          default = o == :set_column_default ? op[:default] : old_opts[:ruby_default]
+          "ALTER TABLE #{quote_schema_table(table)} CHANGE COLUMN #{quote_identifier(op[:name])} #{column_definition_sql(op.merge(: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
@@ -117,13 +114,13 @@
       def column_definition_sql(column)
         column.delete(:default) if column[:type] == File || (column[:type] == String && column[:text] == true)
         super
       end
       
-      # Handle MySQL specific syntax for column references
-      def column_references_sql(column)
-        "#{", FOREIGN KEY (#{quote_identifier(column[:name])})" unless column[:type] == :check}#{super(column)}"
+      # MySQL doesn't handle references as column constraints, it must use a separate table constraint
+      def column_references_column_constraint_sql(column)
+        "#{", FOREIGN KEY (#{quote_identifier(column[:name])})" unless column[:type] == :check}#{column_references_sql(column)}"
       end
       
       # Use MySQL specific syntax for engine type and character encoding
       def create_table_sql(name, generator, options = {})
         engine = options.include?(:engine) ? options[:engine] : Sequel::MySQL.default_engine
@@ -197,16 +194,15 @@
       end
     end
   
     # Dataset methods shared by datasets that use MySQL databases.
     module DatasetMethods
-      include Dataset::UnsupportedIntersectExcept
-
       BOOL_TRUE = '1'.freeze
       BOOL_FALSE = '0'.freeze
       TIMESTAMP_FORMAT = "'%Y-%m-%d %H:%M:%S'".freeze
       COMMA_SEPARATOR = ', '.freeze
+      SELECT_CLAUSE_ORDER = %w'distinct columns from join where group having compounds order limit'.freeze
       
       # MySQL specific syntax for LIKE/REGEXP searches, as well as
       # string concatenation.
       def complex_expression_sql(op, args)
         case op
@@ -229,16 +225,10 @@
         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
-
       # Adds full text filter
       def full_text_search(cols, terms, opts = {})
         filter(full_text_sql(cols, terms, opts))
       end
       
@@ -311,19 +301,15 @@
       # ON DUPLICATE KEY UPDATE value=VALUES(value)
       #
       def on_duplicate_key_update(*args)
         clone(:on_duplicate_key_update => args)
       end
-      
+
       # MySQL specific syntax for inserting multiple values at once.
       def multi_insert_sql(columns, values)
-        if update_cols = opts[:on_duplicate_key_update]
-          update_cols = columns if update_cols.empty?
-          update_string = update_cols.map{|c| "#{quote_identifier(c)}=VALUES(#{quote_identifier(c)})"}.join(COMMA_SEPARATOR)
-        end
         values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)
-        ["#{insert_sql_base}#{source_list(@opts[:from])} (#{identifier_list(columns)}) VALUES #{values}#{" ON DUPLICATE KEY UPDATE #{update_string}" if update_string}"]
+        ["#{insert_sql_base}#{source_list(@opts[:from])} (#{identifier_list(columns)}) VALUES #{values}#{insert_sql_suffix}"]
       end
       
       # MySQL uses the nonstandard ` (backtick) for quoting identifiers.
       def quoted_identifier(c)
         "`#{c}`"
@@ -363,10 +349,20 @@
             end
           end
         end
       end
       
+      #  does not support DISTINCT ON
+      def supports_distinct_on?
+        false
+      end
+
+      # MySQL does not support INTERSECT or EXCEPT
+      def supports_intersect_except?
+        false
+      end
+
       # MySQL supports ORDER and LIMIT clauses in UPDATE statements.
       def update_sql(values)
         sql = super
         sql << " ORDER BY #{expression_list(opts[:order])}" if opts[:order]
         sql << " LIMIT #{opts[:limit]}" if opts[:limit]
@@ -378,10 +374,15 @@
       # MySQL supports INSERT IGNORE INTO
       def insert_sql_base
         "INSERT #{'IGNORE ' if opts[:insert_ignore]}INTO "
       end
 
+      # MySQL supports INSERT ... ON DUPLICATE KEY UPDATE
+      def insert_sql_suffix
+        on_duplicate_key_update_sql if opts[:on_duplicate_key_update]
+      end
+
       # MySQL doesn't use the SQL standard DEFAULT VALUES.
       def insert_default_values_sql
         "#{insert_sql_base}#{source_list(@opts[:from])} () VALUES ()"
       end
 
@@ -401,9 +402,33 @@
       end
 
       # Use 1 for true on MySQL
       def literal_true
         BOOL_TRUE
+      end
+      
+      # MySQL specific syntax for ON DUPLICATE KEY UPDATE
+      def on_duplicate_key_update_sql
+        if update_cols = opts[:on_duplicate_key_update]
+          update_vals = nil
+
+          if update_cols.empty?
+            update_cols = columns
+          elsif update_cols.last.is_a?(Hash)
+            update_vals = update_cols.last
+            update_cols = update_cols[0..-2]
+          end
+
+          updating = update_cols.map{|c| "#{quote_identifier(c)}=VALUES(#{quote_identifier(c)})" }
+          updating += update_vals.map{|c,v| "#{quote_identifier(c)}=#{literal(v)}" } if update_vals
+
+          " ON DUPLICATE KEY UPDATE #{updating.join(COMMA_SEPARATOR)}"
+        end
+      end
+      
+      # MySQL does not support the SQL WITH clause
+      def select_clause_order
+        SELECT_CLAUSE_ORDER
       end
     end
   end
 end