lib/sequel/adapters/shared/mysql.rb in sequel-4.49.0 vs lib/sequel/adapters/shared/mysql.rb in sequel-5.0.0

- old
+ new

@@ -1,121 +1,50 @@ # frozen-string-literal: true -Sequel.require %w'replace split_alter_table unmodified_identifiers', 'adapters/utils' +require_relative '../utils/replace' +require_relative '../utils/split_alter_table' +require_relative '../utils/unmodified_identifiers' module Sequel module MySQL Sequel::Database.set_shared_adapter_scheme(:mysql, self) def self.mock_adapter_setup(db) - db.instance_eval do + db.instance_exec do @server_version = 50617 end end - # SEQUEL5: Remove - @convert_tinyint_to_bool = true - @default_charset = nil - @default_collate = nil - @default_engine = nil - class << self - def convert_tinyint_to_bool - Sequel::Deprecation.deprecate("Sequel::MySQL.convert_tinyint_to_bool", "Call this method on the Database instance") - @convert_tinyint_to_bool - end - def convert_tinyint_to_bool=(v) - Sequel::Deprecation.deprecate("Sequel::MySQL.convert_tinyint_to_bool=", "Call this method on the Database instance") - @convert_tinyint_to_bool = v - end - - def default_charset - Sequel::Deprecation.deprecate("Sequel::MySQL.default_charset", "Call this method on the Database instance") - @default_charset - end - def default_charset=(v) - Sequel::Deprecation.deprecate("Sequel::MySQL.default_charset=", "Call this method on the Database instance") - @default_charset = v - end - - def default_collate - Sequel::Deprecation.deprecate("Sequel::MySQL.default_collate", "Call this method on the Database instance") - @default_collate - end - def default_collate=(v) - Sequel::Deprecation.deprecate("Sequel::MySQL.default_collate=", "Call this method on the Database instance") - @default_collate = v - end - - def default_engine - Sequel::Deprecation.deprecate("Sequel::MySQL.default_engine", "Call this method on the Database instance") - @default_engine - end - def default_engine=(v) - Sequel::Deprecation.deprecate("Sequel::MySQL.default_engine=", "Call this method on the Database instance") - @default_engine = v - end - end - - # Methods shared by Database instances that connect to MySQL, - # currently supported by the native and JDBC adapters. module DatabaseMethods include UnmodifiedIdentifiers::DatabaseMethods include Sequel::Database::SplitAlterTable - AUTO_INCREMENT = 'AUTO_INCREMENT'.freeze - Sequel::Deprecation.deprecate_constant(self, :AUTO_INCREMENT) - PRIMARY = 'PRIMARY'.freeze - Sequel::Deprecation.deprecate_constant(self, :PRIMARY) - MYSQL_TIMESTAMP_RE = /\ACURRENT_(?:DATE|TIMESTAMP)?\z/ - Sequel::Deprecation.deprecate_constant(self, :MYSQL_TIMESTAMP_RE) - - CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}#.freeze # SEQUEL5 - COLUMN_DEFINITION_ORDER = [:collate, :null, :default, :unique, :primary_key, :auto_increment, :references]#.freeze # SEQUEL5 - + CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}.freeze + COLUMN_DEFINITION_ORDER = [:collate, :null, :default, :unique, :primary_key, :auto_increment, :references].freeze # Set the default charset used for CREATE TABLE. You can pass the # :charset option to create_table to override this setting. - #attr_accessor :default_charset # SEQUEL5 + attr_accessor :default_charset # Set the default collation used for CREATE TABLE. You can pass the # :collate option to create_table to override this setting. - #attr_accessor :default_collate # SEQUEL5 + attr_accessor :default_collate # Set the default engine used for CREATE TABLE. You can pass the # :engine option to create_table to override this setting. - #attr_accessor :default_engine # SEQUEL5 + attr_accessor :default_engine - # SEQUEL5: Remove - attr_writer :default_charset - def default_charset - v = @default_charset - v.nil? ? Sequel::MySQL.instance_variable_get(:@default_charset) : v - end - attr_writer :default_collate - def default_collate - v = @default_collate - v.nil? ? Sequel::MySQL.instance_variable_get(:@default_collate) : v - end - attr_writer :default_engine - def default_engine - v = @default_engine - v.nil? ? Sequel::MySQL.instance_variable_get(:@default_engine) : v - end - # 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 - # Commit an existing prepared transaction with the given transaction - # identifier string. def commit_prepared_transaction(transaction_id, opts=OPTS) run("XA COMMIT #{literal(transaction_id)}", opts) end - # MySQL uses the :mysql database type def database_type :mysql end # Use the Information Schema's KEY_COLUMN_USAGE table to get @@ -161,11 +90,10 @@ # option :partial to override this. def indexes(table, opts=OPTS) indexes = {} remove_indexes = [] m = output_identifier_meth - im = input_identifier_meth schema, table = schema_and_table(table) table = Sequel::SQL::Identifier.new(table) sql = "SHOW INDEX FROM #{literal(table)}" if schema @@ -182,12 +110,10 @@ i[:columns] << m.call(r[:Column_name]) end indexes.reject{|k,v| remove_indexes.include?(k)} end - # Rollback an existing prepared transaction with the given transaction - # identifier string. def rollback_prepared_transaction(transaction_id, opts=OPTS) run("XA ROLLBACK #{literal(transaction_id)}", opts) end # Get version of MySQL server, used for determined capabilities. @@ -239,20 +165,10 @@ # :server :: Set the server to use def tables(opts=OPTS) full_tables('BASE TABLE', opts) end - # Changes the database in use by issuing a USE statement. I would be - # very careful if I used this. - def use(db_name) - Sequel::Deprecation.deprecate("Database#use", "Create a new Sequel::Database instance instead of using Database#use") - disconnect - @opts[:database] = db_name if self << "USE #{db_name}" - @schemas = {} - self - end - # Return an array of symbols specifying view names in the current database. # # Options: # :server :: Set the server to use def views(opts=OPTS) @@ -322,11 +238,10 @@ when :unique "DROP INDEX #{quote_identifier(op[:name])}" 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)}" when :drop_constraint @@ -377,11 +292,10 @@ end sqls end - # Use MySQL specific AUTO_INCREMENT text. def auto_increment_sql 'AUTO_INCREMENT' end # MySQL needs to set transaction isolation before begining a transaction @@ -398,11 +312,10 @@ else super end end - # The order of the column definition, as an array of symbols. def column_definition_order COLUMN_DEFINITION_ORDER end # MySQL doesn't allow default values on text columns, so ignore if it the @@ -486,11 +399,10 @@ def full_tables(type, opts) m = output_identifier_meth metadata_dataset.with_sql('SHOW FULL TABLES').server(opts[:server]).map{|r| m.call(r.values.first) if r.delete(:Table_type) == type}.compact end - # Handle MySQL specific index SQL syntax def index_definition_sql(table_name, index) index_name = quote_identifier(index[:name] || default_index_name(table_name, index[:columns])) raise Error, "Partial indexes are not supported for this database" if index[:where] && !supports_partial_indexes? index_type = case index[:type] when :full_text @@ -518,11 +430,10 @@ else super end end - # Recognize MySQL set type. def schema_column_type(db_type) case db_type when /\Aset/io :set when /\Amediumint/io @@ -627,103 +538,10 @@ end end # Dataset methods shared by datasets that use MySQL databases. module DatasetMethods - BOOL_TRUE = '1'.freeze - Sequel::Deprecation.deprecate_constant(self, :BOOL_TRUE) - BOOL_FALSE = '0'.freeze - Sequel::Deprecation.deprecate_constant(self, :BOOL_FALSE) - COMMA_SEPARATOR = ', '.freeze - Sequel::Deprecation.deprecate_constant(self, :COMMA_SEPARATOR) - FOR_SHARE = ' LOCK IN SHARE MODE'.freeze - Sequel::Deprecation.deprecate_constant(self, :FOR_SHARE) - SQL_CALC_FOUND_ROWS = ' SQL_CALC_FOUND_ROWS'.freeze - Sequel::Deprecation.deprecate_constant(self, :SQL_CALC_FOUND_ROWS) - APOS = "'".freeze - Sequel::Deprecation.deprecate_constant(self, :APOS) - APOS_RE = /'/.freeze - Sequel::Deprecation.deprecate_constant(self, :APOS_RE) - DOUBLE_APOS = "''".freeze - Sequel::Deprecation.deprecate_constant(self, :DOUBLE_APOS) - SPACE = ' '.freeze - Sequel::Deprecation.deprecate_constant(self, :SPACE) - PAREN_CLOSE = ')'.freeze - Sequel::Deprecation.deprecate_constant(self, :PAREN_CLOSE) - PAREN_OPEN = '('.freeze - Sequel::Deprecation.deprecate_constant(self, :PAREN_OPEN) - NOT_SPACE = 'NOT '.freeze - Sequel::Deprecation.deprecate_constant(self, :NOT_SPACE) - FROM = ' FROM '.freeze - Sequel::Deprecation.deprecate_constant(self, :FROM) - COMMA = ', '.freeze - Sequel::Deprecation.deprecate_constant(self, :COMMA) - LIMIT = " LIMIT ".freeze - Sequel::Deprecation.deprecate_constant(self, :LIMIT) - GROUP_BY = " GROUP BY ".freeze - Sequel::Deprecation.deprecate_constant(self, :GROUP_BY) - ESCAPE = " ESCAPE ".freeze - Sequel::Deprecation.deprecate_constant(self, :ESCAPE) - BACKSLASH = "\\".freeze - Sequel::Deprecation.deprecate_constant(self, :BACKSLASH) - REGEXP = 'REGEXP'.freeze - Sequel::Deprecation.deprecate_constant(self, :REGEXP) - LIKE = 'LIKE'.freeze - Sequel::Deprecation.deprecate_constant(self, :LIKE) - BINARY = 'BINARY '.freeze - Sequel::Deprecation.deprecate_constant(self, :BINARY) - CONCAT = "CONCAT".freeze - Sequel::Deprecation.deprecate_constant(self, :CONCAT) - CAST_BITCOMP_OPEN = "CAST(~".freeze - Sequel::Deprecation.deprecate_constant(self, :CAST_BITCOMP_OPEN) - CAST_BITCOMP_CLOSE = " AS SIGNED INTEGER)".freeze - Sequel::Deprecation.deprecate_constant(self, :CAST_BITCOMP_CLOSE) - STRAIGHT_JOIN = 'STRAIGHT_JOIN'.freeze - Sequel::Deprecation.deprecate_constant(self, :STRAIGHT_JOIN) - NATURAL_LEFT_JOIN = 'NATURAL LEFT JOIN'.freeze - Sequel::Deprecation.deprecate_constant(self, :NATURAL_LEFT_JOIN) - BACKTICK = '`'.freeze - Sequel::Deprecation.deprecate_constant(self, :BACKTICK) - BACKTICK_RE = /`/.freeze - Sequel::Deprecation.deprecate_constant(self, :BACKTICK_RE) - DOUBLE_BACKTICK = '``'.freeze - Sequel::Deprecation.deprecate_constant(self, :DOUBLE_BACKTICK) - EMPTY_COLUMNS = " ()".freeze - Sequel::Deprecation.deprecate_constant(self, :EMPTY_COLUMNS) - EMPTY_VALUES = " VALUES ()".freeze - Sequel::Deprecation.deprecate_constant(self, :EMPTY_VALUES) - IGNORE = " IGNORE".freeze - Sequel::Deprecation.deprecate_constant(self, :IGNORE) - ON_DUPLICATE_KEY_UPDATE = " ON DUPLICATE KEY UPDATE ".freeze - Sequel::Deprecation.deprecate_constant(self, :ON_DUPLICATE_KEY_UPDATE) - EQ_VALUES = '=VALUES('.freeze - Sequel::Deprecation.deprecate_constant(self, :EQ_VALUES) - EQ = '='.freeze - Sequel::Deprecation.deprecate_constant(self, :EQ) - WITH_ROLLUP = ' WITH ROLLUP'.freeze - Sequel::Deprecation.deprecate_constant(self, :WITH_ROLLUP) - EXPLAIN = 'EXPLAIN '.freeze - Sequel::Deprecation.deprecate_constant(self, :EXPLAIN) - EXPLAIN_EXTENDED = 'EXPLAIN EXTENDED '.freeze - Sequel::Deprecation.deprecate_constant(self, :EXPLAIN_EXTENDED) - BACKSLASH_RE = /\\/.freeze - Sequel::Deprecation.deprecate_constant(self, :BACKSLASH_RE) - QUAD_BACKSLASH = "\\\\\\\\".freeze - Sequel::Deprecation.deprecate_constant(self, :QUAD_BACKSLASH) - BLOB_START = "0x".freeze - Sequel::Deprecation.deprecate_constant(self, :BLOB_START) - EMPTY_BLOB = "''".freeze - Sequel::Deprecation.deprecate_constant(self, :EMPTY_BLOB) - HSTAR = "H*".freeze - Sequel::Deprecation.deprecate_constant(self, :HSTAR) - CURRENT_TIMESTAMP_56 = 'CURRENT_TIMESTAMP(6)'.freeze - Sequel::Deprecation.deprecate_constant(self, :CURRENT_TIMESTAMP_56) - ONLY_OFFSET = ",18446744073709551615".freeze - Sequel::Deprecation.deprecate_constant(self, :ONLY_OFFSET) - NON_SQL_OPTIONS = (Dataset::NON_SQL_OPTIONS + [:insert_ignore, :update_ignore, :on_duplicate_key_update]).freeze - Sequel::Deprecation.deprecate_constant(self, :NON_SQL_OPTIONS) - 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, :insert, %w'insert ignore into columns values on_duplicate_key_update') @@ -731,12 +549,10 @@ Dataset.def_sql_method(self, :update, %w'update ignore table set where order limit') include Sequel::Dataset::Replace include UnmodifiedIdentifiers::DatasetMethods - # MySQL specific syntax for LIKE/REGEXP searches, as well as - # string concatenation. def complex_expression_sql_append(sql, op, args) case op when :IN, :"NOT IN" ds = args[1] if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] @@ -800,14 +616,14 @@ end # Sets up the select methods to delete from if deleting from a # joined dataset: # - # DB[:a].join(:b, :a_id=>:id).delete + # DB[:a].join(:b, a_id: :id).delete # # DELETE a FROM a INNER JOIN b ON (b.a_id = a.id) # - # DB[:a].join(:b, :a_id=>:id).delete_from(:a, :b).delete + # DB[:a].join(:b, a_id: :id).delete_from(:a, :b).delete # # DELETE a, b FROM a INNER JOIN b ON (b.a_id = a.id) def delete_from(*tables) clone(:delete_from=>tables) end @@ -836,41 +652,25 @@ def full_text_sql(cols, terms, opts = OPTS) terms = terms.join(' ') if terms.is_a?(Array) SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) end - # Transforms an CROSS JOIN to an INNER JOIN if the expr is not nil. - # Raises an error on use of :full_outer type, since MySQL doesn't support it. - def join_table(type, table, expr=nil, opts=OPTS, &block) - if (type == :cross) && !expr.nil? - Sequel::Deprecation.deprecate(":cross join type with conditions being converted to INNER JOIN on MySQL", "Use :inner join type instead") - type = :inner - end - raise(Sequel::Error, "MySQL doesn't support FULL OUTER JOIN or NATURAL FULL JOIN") if type == :full_outer || type == :natural_full - super(type, table, expr, opts, &block) - end - - # Transforms :natural_inner to NATURAL LEFT JOIN and straight to - # STRAIGHT_JOIN. + # Transforms :straight to STRAIGHT_JOIN. def join_type_sql(join_type) - case join_type - when :straight + if join_type == :straight 'STRAIGHT_JOIN' - when :natural_inner - Sequel::Deprecation.deprecate(":natural_inner join type being converted to NATURAL LEFT JOIN on MySQL", "Use :natural_left join type for NATURAL LEFT JOIN, or :natural join type for NATURAL JOIN") - 'NATURAL LEFT JOIN' else super end end # Sets up the insert methods to use INSERT IGNORE. # Useful if you have a unique key and want to just skip # inserting rows that violate the unique key restriction. # # dataset.insert_ignore.multi_insert( - # [{:name => 'a', :value => 1}, {:name => 'b', :value => 2}] + # [{name: 'a', value: 1}, {name: 'b', value: 2}] # ) # # INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2) def insert_ignore clone(:insert_ignore=>true) end @@ -884,25 +684,25 @@ # # Useful if you have a unique key and want to update # inserting rows that violate the unique key restriction. # # dataset.on_duplicate_key_update.multi_insert( - # [{:name => 'a', :value => 1}, {:name => 'b', :value => 2}] + # [{name: 'a', value: 1}, {name: 'b', value: 2}] # ) # # INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2) # # ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value) # # dataset.on_duplicate_key_update(:value).multi_insert( - # [{:name => 'a', :value => 1}, {:name => 'b', :value => 2}] + # [{name: 'a', value: 1}, {name: 'b', value: 2}] # ) # # INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2) # # ON DUPLICATE KEY UPDATE value=VALUES(value) # # dataset.on_duplicate_key_update( - # :value => Sequel.lit('value + VALUES(value)') + # value: Sequel.lit('value + VALUES(value)') # ).multi_insert( - # [{:name => 'a', :value => 1}, {:name => 'b', :value => 2}] + # [{name: 'a', value: 1}, {name: 'b', value: 2}] # ) # # INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2) # # ON DUPLICATE KEY UPDATE value=value + VALUES(value) def on_duplicate_key_update(*args) clone(:on_duplicate_key_update => args) @@ -943,32 +743,31 @@ def supports_modifying_joins? true end # MySQL's DISTINCT ON emulation using GROUP BY does not respect the - # queries ORDER BY clause. + # query's 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. + # Check the database setting for whether fractional timestamps + # are suppported. def supports_timestamp_usecs? db.supports_timestamp_usecs? 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. # - # dataset.update_ignore.update({:name => 'a', :value => 1}) + # dataset.update_ignore.update(name: 'a', value: 1) # # UPDATE IGNORE tablename SET name = 'a', value = 1 def update_ignore clone(:update_ignore=>true) end @@ -1103,10 +902,10 @@ # Use 1 for true on MySQL def literal_true '1' end - # MySQL supports multiple rows in INSERT. + # MySQL supports multiple rows in VALUES in INSERT. def multi_insert_sql_strategy :values end def non_sql_option?(key)