Module: Sequel::SQLite::DatasetMethods

Included in:
Amalgalite::Dataset, DataObjects::SQLite::Dataset, JDBC::SQLite::Dataset, Dataset, Sequel::Swift::SQLite::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')
COMMA_SEPARATOR = 
', '.freeze
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

#boolean_constant_sql(constant) ⇒ Object

Ugly hack. Really, SQLite uses 0 for false and 1 for true but then you can’t differentiate between integers and booleans. In filters, SQL::BooleanConstants are used more, while in other places the ruby true/false values are used more, so use 1/0 for SQL::BooleanConstants. The correct fix for this would require separate literalization paths for filters compared to other values, but that’s more work than I want to do right now.



349
350
351
352
353
354
355
356
357
358
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 349

def boolean_constant_sql(constant)
  case constant
  when true
    '1'
  when false
    '0'
  else
    super
  end
end

#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.



363
364
365
366
367
368
369
370
371
372
373
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 363

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



376
377
378
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 376

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.



383
384
385
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 383

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.



389
390
391
392
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 389

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) ⇒ Object

HAVING requires GROUP BY on SQLite

Raises:



395
396
397
398
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 395

def having(*cond)
  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.



401
402
403
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 401

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

#supports_intersect_except_all? ⇒ Boolean

SQLite does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


406
407
408
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 406

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

SQLite does not support IS TRUE

Returns:

  • (Boolean) 


411
412
413
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 411

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) 


416
417
418
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 416

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) 


422
423
424
 
# File 'lib/sequel/adapters/shared/sqlite.rb', line 422

def supports_timestamp_timezones?
  true
end