module Sequel::SQLite::DatasetMethods

  1. lib/sequel/adapters/shared/sqlite.rb
Parent: SQLite

Instance methods for datasets that connect to an SQLite database

Included modules

  1. Dataset::Replace

Constants

APOS = Dataset::APOS  
AS = Dataset::AS  
BACKTICK = '`'.freeze  
BACKTICK_RE = /`/.freeze  
BLOB_START = "X'".freeze  
COMMA = Dataset::COMMA  
CONSTANT_MAP = {:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}  
DATETIME_OPEN = "datetime(".freeze  
DATE_OPEN = "date(".freeze  
DOUBLE_BACKTICK = '``'.freeze  
EMULATED_FUNCTION_MAP = {:char_length=>'length'.freeze}  
EXTRACT_CLOSE = ') AS '.freeze  
EXTRACT_MAP = {:year=>"'%Y'", :month=>"'%m'", :day=>"'%d'", :hour=>"'%H'", :minute=>"'%M'", :second=>"'%f'"}  
EXTRACT_OPEN = "CAST(strftime(".freeze  
HSTAR = "H*".freeze  
INTEGER = 'INTEGER'.freeze  
NOT_SPACE = Dataset::NOT_SPACE  
NUMERIC = 'NUMERIC'.freeze  
PAREN_CLOSE = Dataset::PAREN_CLOSE  
SELECT_CLAUSE_METHODS = Dataset.clause_methods(:select, %w'select distinct columns from join where group having compounds order limit')  

Public Instance methods

cast_sql_append (sql, expr, type)
[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 506
def cast_sql_append(sql, expr, type)
  if type == Time or type == DateTime
    sql << DATETIME_OPEN
    literal_append(sql, expr)
    sql << PAREN_CLOSE
  elsif type == Date
    sql << DATE_OPEN
    literal_append(sql, expr)
    sql << PAREN_CLOSE
  else
    super
  end
end
complex_expression_sql_append (sql, op, args)

SQLite doesn't support a NOT LIKE b, you need to use NOT (a LIKE b). It doesn't support xor or the extract function natively, so those have to be emulated.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 522
def complex_expression_sql_append(sql, op, args)
  case op
  when :"NOT LIKE", :"NOT ILIKE"
    sql << NOT_SPACE
    complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
  when :^
    sql << complex_expression_arg_pairs(args) do |a, b|
      a = literal(a)
      b = literal(b)
      "((~(#{a} & #{b})) & (#{a} | #{b}))"
    end
  when :extract
    part = args.at(0)
    raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
    sql << EXTRACT_OPEN << format << COMMA
    literal_append(sql, args.at(1))
    sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE
  else
    super
  end
end
constant_sql_append (sql, constant)

SQLite has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 546
def constant_sql_append(sql, constant)
  if c = CONSTANT_MAP[constant]
    sql << c
  else
    super
  end
end
delete ()

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.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 557
def delete
  @opts[:where] ? super : where(1=>1).delete
end
explain (opts=nil)

Return an array of strings specifying a query explanation for a SELECT of the current dataset. Currently, the options are ignore, but it accepts options to be compatible with other adapters.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 564
def explain(opts=nil)
  # Load the PrettyTable class, needed for explain output
  Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)

  ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
  rows = ds.all
  Sequel::PrettyTable.string(rows, ds.columns)
end
having (*cond)

HAVING requires GROUP BY on SQLite

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 574
def having(*cond)
  raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
  super
end
quoted_identifier_append (sql, c)

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

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 580
def quoted_identifier_append(sql, c)
  sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK
end
select (*cols)

When a qualified column is selected on SQLite and the qualifier is a subselect, the column name used is the full qualified name (including the qualifier) instead of just the column name. To get correct column names, you must use an alias.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 588
def select(*cols)
  if ((f = @opts[:from]) && f.any?{|t| t.is_a?(Dataset) || (t.is_a?(SQL::AliasedExpression) && t.expression.is_a?(Dataset))}) || ((j = @opts[:join]) && j.any?{|t| t.table.is_a?(Dataset)})
    super(*cols.map{|c| alias_qualified_column(c)})
  else
    super
  end
end
supports_intersect_except_all? ()

SQLite does not support INTERSECT ALL or EXCEPT ALL

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 597
def supports_intersect_except_all?
  false
end
supports_is_true? ()

SQLite does not support IS TRUE

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 602
def supports_is_true?
  false
end
supports_multiple_column_in? ()

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

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 607
def supports_multiple_column_in?
  false
end
supports_timestamp_timezones? ()

SQLite supports timezones in literal timestamps, since it stores them as text. But using timezones in timestamps breaks SQLite datetime functions, so we allow the user to override the default per database.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 614
def supports_timestamp_timezones?
  db.use_timestamp_timezones?
end
supports_where_true? ()

SQLite cannot use WHERE 't'.

[show source] 
# File lib/sequel/adapters/shared/sqlite.rb, line 619
def supports_where_true?
  false
end