Module: Sequel::SQLite::DatasetMethods

Included in:
Amalgalite::Dataset, DataObjects::SQLite::Dataset, JDBC::SQLite::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/sqlite.rb

Overview

Instance methods for datasets that connect to an SQLite database

Constant Summary collapse

SELECT_CLAUSE_METHODS = 
Dataset.clause_methods(:select, %w'distinct columns from join where group having compounds order limit')
CONSTANT_MAP = 
{:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql(op, args) ⇒ Object

SQLite does not support pattern matching via regular expressions. SQLite is case insensitive (depending on pragma), so use LIKE for ILIKE.



295
296
297
298
299
300
301
302
303
304
305
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 295

def complex_expression_sql(op, args)
  case op
  when :~, :'!~', :'~*', :'!~*'
    raise Error, "SQLite does not support pattern matching via regular expressions"
  when :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
    # SQLite is case insensitive for ASCII, and non case sensitive for other character sets
    "#{'NOT ' if [:'NOT LIKE', :'NOT ILIKE'].include?(op)}(#{literal(args.at(0))} LIKE #{literal(args.at(1))})"
  else
    super(op, args)
  end
end

#constant_sql(constant) ⇒ Object

MSSQL doesn’t support the SQL standard CURRENT_DATE or CURRENT_TIME



308
309
310
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 308

def constant_sql(constant)
  CONSTANT_MAP[constant] || super
end

#delete ⇒ Object

SQLite performs a TRUNCATE style DELETE if no filter is specified. Since we want to always return the count of records, add a condition that is always true and then delete.



315
316
317
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 315

def delete
  @opts[:where] ? super : filter(1=>1).delete
end

#explain ⇒ Object

Return an array of strings specifying a query explanation for a SELECT of the current dataset.



321
322
323
324
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 321

def explain
  db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}").
    map{|x| "#{x[:addr]}|#{x[:opcode]}|#{(1..5).map{|i| x[:"p#{i}"]}.join('|')}|#{x[:comment]}"}
end

#having(*cond, &block) ⇒ Object

HAVING requires GROUP BY on SQLite

Raises:



327
328
329
330
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 327

def having(*cond, &block)
  raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
  super
end

#quoted_identifier(c) ⇒ Object

SQLite uses the nonstandard ‘ (backtick) for quoting identifiers.



333
334
335
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 333

def quoted_identifier(c)
  "`#{c}`"
end

#supports_intersect_except_all? ⇒ Boolean

SQLite does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


338
339
340
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 338

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

SQLite does not support IS TRUE

Returns:

  • (Boolean) 


343
344
345
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 343

def supports_is_true?
  false
end

#supports_multiple_column_in? ⇒ Boolean

SQLite does not support multiple columns for the IN/NOT IN operators

Returns:

  • (Boolean) 


348
349
350
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 348

def supports_multiple_column_in?
  false
end

#supports_timestamp_timezones? ⇒ Boolean

SQLite supports timezones in literal timestamps, since it stores them as text.

Returns:

  • (Boolean) 


354
355
356
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 354

def supports_timestamp_timezones?
  true
end