Module: Sequel::MySQL::DatasetMethods
- Included in:
- JDBC::MySQL::Dataset, Dataset
- Defined in:
- lib/sequel_core/adapters/shared/mysql.rb
Overview
Dataset methods shared by datasets that use MySQL databases.
Constant Summary collapse
- BOOL_TRUE =
'1'.freeze
- BOOL_FALSE =
'0'.freeze
- COMMA_SEPARATOR =
', '.freeze
Instance Method Summary collapse
-
#complex_expression_sql(op, args) ⇒ Object
MySQL specific syntax for LIKE/REGEXP searches, as well as string concatenation.
-
#delete_sql(opts = nil) ⇒ Object
MySQL supports ORDER and LIMIT clauses in DELETE statements.
-
#full_text_search(cols, terms, opts = {}) ⇒ Object
MySQL specific full text search syntax.
-
#having(*cond, &block) ⇒ Object
MySQL allows HAVING clause on ungrouped datasets.
-
#insert_default_values_sql ⇒ Object
MySQL doesn’t use the SQL standard DEFAULT VALUES.
-
#join_table(type, table, expr = nil, table_alias = nil) ⇒ Object
Transforms an CROSS JOIN to an INNER JOIN if the expr is not nil.
-
#join_type_sql(join_type) ⇒ Object
Transforms :natural_inner to NATURAL LEFT JOIN and straight to STRAIGHT_JOIN.
-
#literal(v) ⇒ Object
Override the default boolean values.
-
#multi_insert_sql(columns, values) ⇒ Object
MySQL specific syntax for inserting multiple values at once.
-
#quoted_identifier(c) ⇒ Object
MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.
-
#replace_sql(*values) ⇒ Object
MySQL specific syntax for REPLACE (aka UPSERT, or update if exists, insert if it doesn’t).
-
#update_sql(values, opts = nil) ⇒ Object
MySQL supports ORDER and LIMIT clauses in UPDATE statements.
Instance Method Details
#complex_expression_sql(op, args) ⇒ Object
MySQL specific syntax for LIKE/REGEXP searches, as well as string concatenation.
101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 101 def complex_expression_sql(op, args) case op when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' "(#{literal(args.at(0))} #{'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)}#{[:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE'} #{'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)}#{literal(args.at(1))})" when :'||' if args.length > 1 "CONCAT(#{args.collect{|a| literal(a)}.join(', ')})" else literal(args.at(0)) end else super(op, args) end end |
#delete_sql(opts = nil) ⇒ Object
MySQL supports ORDER and LIMIT clauses in DELETE statements.
117 118 119 120 121 122 123 124 125 126 127 128 129 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 117 def delete_sql(opts = nil) sql = super opts = opts ? @opts.merge(opts) : @opts if order = opts[:order] sql << " ORDER BY #{expression_list(order)}" end if limit = opts[:limit] sql << " LIMIT #{limit}" end sql end |
#full_text_search(cols, terms, opts = {}) ⇒ Object
MySQL specific full text search syntax.
132 133 134 135 136 137 138 139 140 141 142 143 144 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 132 def full_text_search(cols, terms, opts = {}) mode = opts[:boolean] ? " IN BOOLEAN MODE" : "" s = if Array === terms if mode.blank? "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) end |
#having(*cond, &block) ⇒ Object
MySQL allows HAVING clause on ungrouped datasets.
147 148 149 150 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 147 def having(*cond, &block) @opts[:having] = {} x = filter(*cond, &block) end |
#insert_default_values_sql ⇒ Object
MySQL doesn’t use the SQL standard DEFAULT VALUES.
153 154 155 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 153 def insert_default_values_sql "INSERT INTO #{source_list(@opts[:from])} () VALUES ()" end |
#join_table(type, table, expr = nil, table_alias = nil) ⇒ Object
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.
159 160 161 162 163 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 159 def join_table(type, table, expr=nil, table_alias=nil) type = :inner if (type == :cross) && !expr.nil? raise(Sequel::Error, "MySQL doesn't support FULL OUTER JOIN") if type == :full_outer super(type, table, expr, table_alias) end |
#join_type_sql(join_type) ⇒ Object
Transforms :natural_inner to NATURAL LEFT JOIN and straight to STRAIGHT_JOIN.
167 168 169 170 171 172 173 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 167 def join_type_sql(join_type) case join_type when :straight then 'STRAIGHT_JOIN' when :natural_inner then 'NATURAL LEFT JOIN' else super end end |
#literal(v) ⇒ Object
Override the default boolean values.
176 177 178 179 180 181 182 183 184 185 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 176 def literal(v) case v when true BOOL_TRUE when false BOOL_FALSE else super end end |
#multi_insert_sql(columns, values) ⇒ Object
MySQL specific syntax for inserting multiple values at once.
188 189 190 191 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 188 def multi_insert_sql(columns, values) values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR) ["INSERT INTO #{source_list(@opts[:from])} (#{identifier_list(columns)}) VALUES #{values}"] end |
#quoted_identifier(c) ⇒ Object
MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.
194 195 196 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 194 def quoted_identifier(c) "`#{c}`" end |
#replace_sql(*values) ⇒ Object
MySQL specific syntax for REPLACE (aka UPSERT, or update if exists, insert if it doesn’t).
200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 200 def replace_sql(*values) from = source_list(@opts[:from]) 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 "REPLACE INTO #{from} VALUES #{literal(values)}" end when Hash if values.empty? "REPLACE INTO #{from} DEFAULT VALUES" else fl, vl = [], [] values.each {|k, v| fl << literal(k.is_a?(String) ? k.to_sym : k); vl << literal(v)} "REPLACE INTO #{from} (#{fl.join(COMMA_SEPARATOR)}) VALUES (#{vl.join(COMMA_SEPARATOR)})" end when Dataset "REPLACE INTO #{from} #{literal(values)}" else if values.respond_to?(:values) replace_sql(values.values) else "REPLACE INTO #{from} VALUES (#{literal(values)})" end end end end |
#update_sql(values, opts = nil) ⇒ Object
MySQL supports ORDER and LIMIT clauses in UPDATE statements.
240 241 242 243 244 245 246 247 248 249 250 251 252 |
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 240 def update_sql(values, opts = nil) sql = super opts = opts ? @opts.merge(opts) : @opts if order = opts[:order] sql << " ORDER BY #{expression_list(order)}" end if limit = opts[:limit] sql << " LIMIT #{limit}" end sql end |