mysql.rb in sequel-5.8.0

- old
+ new

@@ -56,10 +56,11 @@
         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).
+          order(:CONSTRAINT_NAME, :POSITION_IN_UNIQUE_CONSTRAINT).
           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]]
@@ -405,10 +406,11 @@
         /Duplicate entry .+ for key/ => UniqueConstraintViolation,
         /foreign key constraint fails/ => ForeignKeyConstraintViolation,
         /cannot be null/ => NotNullConstraintViolation,
         /Deadlock found when trying to get lock; try restarting transaction/ => SerializationFailure,
         /CONSTRAINT .+ failed for/ => CheckConstraintViolation,
+        /\AStatement aborted because lock\(s\) could not be acquired immediately and NOWAIT is set\./ => DatabaseLockTimeout,
       }.freeze
       def database_error_regexps
         DATABASE_ERROR_REGEXPS
       end
 
@@ -559,14 +561,14 @@
     # Dataset methods shared by datasets that use MySQL databases.
     module DatasetMethods
       MATCH_AGAINST = ["MATCH ".freeze, " AGAINST (".freeze, ")".freeze].freeze
       MATCH_AGAINST_BOOLEAN = ["MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE)".freeze].freeze
 
-      Dataset.def_sql_method(self, :delete, %w'delete from where order limit')
+      Dataset.def_sql_method(self, :delete, %w'with delete from where order limit')
       Dataset.def_sql_method(self, :insert, %w'insert ignore into columns values on_duplicate_key_update')
       Dataset.def_sql_method(self, :select, %w'with select distinct calc_found_rows columns from join where group having compounds order limit lock')
-      Dataset.def_sql_method(self, :update, %w'update ignore table set where order limit')
+      Dataset.def_sql_method(self, :update, %w'with update ignore table set where order limit')
 
       include Sequel::Dataset::Replace
       include UnmodifiedIdentifiers::DatasetMethods
 
       def complex_expression_sql_append(sql, op, args)
@@ -577,10 +579,16 @@
             super(sql, op, [args[0], ds.from_self])
           else
             super
           end
         when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
+          if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
+            func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
+            func = ~func if op == :'!~'
+            return literal_append(sql, func)
+          end
+
           sql << '('
           literal_append(sql, args[0])
           sql << ' '
           sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
           sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
@@ -649,11 +657,11 @@
       # :extended :: Use EXPLAIN EXPTENDED instead of EXPLAIN if true.
       def explain(opts=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
+        ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? '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.
@@ -729,12 +737,20 @@
       # MySQL uses the nonstandard ` (backtick) for quoting identifiers.
       def quoted_identifier_append(sql, c)
         sql << '`' << c.to_s.gsub('`', '``') << '`'
       end
 
+      # MariaDB 10.2+ and MySQL 8+ support CTEs
       def supports_cte?(type=:select)
-        type == :select && db.mariadb? && db.server_version >= 100200
+        if db.mariadb?
+          type == :select && db.server_version >= 100200
+        else
+          case type
+          when :select, :update, :delete
+            db.server_version >= 80000
+          end
+        end
       end
 
       # MySQL does not support derived column lists
       def supports_derived_column_lists?
         false
@@ -764,10 +780,15 @@
       # MySQL supports modifying joined datasets
       def supports_modifying_joins?
         true
       end
 
+      # MySQL 8+ supports NOWAIT.
+      def supports_nowait?
+        !db.mariadb? && db.server_version >= 80000
+      end
+
       # MySQL's DISTINCT ON emulation using GROUP BY does not respect the
       # query's ORDER BY clause.
       def supports_ordered_distinct_on?
         false
       end
@@ -775,18 +796,24 @@
       # MySQL supports pattern matching via regular expressions
       def supports_regexp?
         true
       end
 
+      # MySQL 8+ supports SKIP LOCKED.
+      def supports_skip_locked?
+        !db.mariadb? && db.server_version >= 80000
+      end
+
       # Check the database setting for whether fractional timestamps
       # are suppported.
       def supports_timestamp_usecs?
         db.supports_timestamp_usecs?
       end
 
+      # MariaDB 10.2+ and MySQL 8+ support window functions
       def supports_window_functions?
-        db.mariadb? && db.server_version >= 100200
+        db.server_version >= (db.mariadb? ? 100200 : 80000)
       end
 
       # Sets up the update methods to use UPDATE IGNORE.
       # Useful if you have a unique key and want to just skip
       # updating rows that violate the unique key restriction.
@@ -944,11 +971,29 @@
         literal_append(sql, @opts[:offset])
         sql << ",18446744073709551615"
       end
   
       # Support FOR SHARE locking when using the :share lock style.
+      # Use SKIP LOCKED if skipping locked rows.
       def select_lock_sql(sql)
-        @opts[:lock] == :share ? (sql << ' LOCK IN SHARE MODE') : super
+        lock = @opts[:lock]
+        if lock == :share
+          if !db.mariadb? && db.server_version >= 80000
+            sql << ' FOR SHARE'
+          else
+            sql << ' LOCK IN SHARE MODE'
+          end
+        else
+          super
+        end
+
+        if lock
+          if @opts[:skip_locked]
+            sql << " SKIP LOCKED"
+          elsif @opts[:nowait]
+            sql << " NOWAIT"
+          end
+        end
       end
 
       # MySQL specific SQL_CALC_FOUND_ROWS option
       def select_calc_found_rows_sql(sql)
         sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]