mysql.rb in sequel-3.34.0

- old
+ new

@@ -49,10 +49,35 @@
       # MySQL uses the :mysql database type
       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.
+      def foreign_key_list(table, opts={})
+        m = output_identifier_meth
+        im = input_identifier_meth
+        ds = metadata_dataset.
+          from(:INFORMATION_SCHEMA__KEY_COLUMN_USAGE).
+          where(:TABLE_NAME=>im.call(table)).
+          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
+
       # 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.
@@ -405,18 +430,24 @@
       CAST_BITCOMP_OPEN = "CAST(~".freeze
       CAST_BITCOMP_CLOSE = " AS SIGNED INTEGER)".freeze
       STRAIGHT_JOIN = 'STRAIGHT_JOIN'.freeze
       NATURAL_LEFT_JOIN = 'NATURAL LEFT JOIN'.freeze
       BACKTICK = '`'.freeze
+      BACKTICK_RE = /`/.freeze
+      DOUBLE_BACKTICK = '``'.freeze
       EMPTY_COLUMNS = " ()".freeze
       EMPTY_VALUES = " VALUES ()".freeze
       IGNORE = " IGNORE".freeze
       REPLACE = 'REPLACE'.freeze
       ON_DUPLICATE_KEY_UPDATE = " ON DUPLICATE KEY UPDATE ".freeze
       EQ_VALUES = '=VALUES('.freeze
       EQ = '='.freeze
       WITH_ROLLUP = ' WITH ROLLUP'.freeze
+      MATCH_AGAINST = ["(MATCH ".freeze, " AGAINST (".freeze, "))".freeze].freeze
+      MATCH_AGAINST_BOOLEAN = ["(MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE))".freeze].freeze
+      EXPLAIN = 'EXPLAIN '.freeze
+      EXPLAIN_EXTENDED = 'EXPLAIN EXTENDED '.freeze
       
       # MySQL specific syntax for LIKE/REGEXP searches, as well as
       # string concatenation.
       def complex_expression_sql_append(sql, op, args)
         case op
@@ -464,10 +495,21 @@
       #   # SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10
       def calc_found_rows
         clone(:calc_found_rows => true)
       end
       
+      # Return the results of an EXPLAIN query as a string. Options:
+      # :extended :: Use EXPLAIN EXPTENDED instead of EXPLAIN if true.
+      def explain(opts={})
+        # Load the PrettyTable class, needed for explain output
+        Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
+
+        ds = db.send(:metadata_dataset).with_sql((opts[:extended] ? EXPLAIN_EXTENDED : EXPLAIN) + select_sql).naked
+        rows = ds.all
+        Sequel::PrettyTable.string(rows, ds.columns)
+      end
+
       # Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.
       def for_share
         lock_style(:share)
       end
 
@@ -477,11 +519,11 @@
       end
       
       # MySQL specific full text search syntax.
       def full_text_sql(cols, terms, opts = {})
         terms = terms.join(' ') if terms.is_a?(Array)
-        SQL::PlaceholderLiteralString.new("MATCH ? AGAINST (?#{" IN BOOLEAN MODE" if opts[:boolean]})", [Array(cols), terms], true)
+        SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
       end
 
       # MySQL allows HAVING clause on ungrouped datasets.
       def having(*cond, &block)
         _filter(:having, *cond, &block)
@@ -550,18 +592,28 @@
         [insert_sql(columns, sql)]
       end
       
       # MySQL uses the nonstandard ` (backtick) for quoting identifiers.
       def quoted_identifier_append(sql, c)
-        sql << BACKTICK << c.to_s << BACKTICK
+        sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK
       end
       
+      # Execute a REPLACE statement on the database.
+      def replace(*values)
+        execute_insert(replace_sql(*values))
+      end
+
       # MySQL specific syntax for REPLACE (aka UPSERT, or update if exists,
       # insert if it doesn't).
       def replace_sql(*values)
         clone(:replace=>true).insert_sql(*values)
       end
+
+      # Replace multiple rows in a single query.
+      def multi_replace(*values)
+        clone(:replace=>true).multi_insert(*values)
+      end
       
       # MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation,
       # though the rows returned cannot be made deterministic through ordering.
       def supports_distinct_on?
         true
@@ -728,19 +780,10 @@
       # MySQL does not support the SQL WITH clause for SELECT statements
       def select_clause_methods
         SELECT_CLAUSE_METHODS
       end
       
-      # MySQL supports ROLLUP via nonstandard SQL syntax
-      def select_group_sql(sql)
-        if group = @opts[:group]
-          sql << GROUP_BY
-          expression_list_append(sql, group)
-          sql << WITH_ROLLUP if @opts[:group_options] == :rollup
-        end
-      end
-
       # Support FOR SHARE locking when using the :share lock style.
       def select_lock_sql(sql)
         @opts[:lock] == :share ? (sql << FOR_SHARE) : super
       end
 
@@ -750,9 +793,14 @@
       end
 
       # MySQL supports the ORDER BY and LIMIT clauses for UPDATE statements
       def update_clause_methods
         UPDATE_CLAUSE_METHODS
+      end
+
+      # MySQL uses WITH ROLLUP syntax.
+      def uses_with_rollup?
+        true
       end
     end
   end
 end