module Sequel::SQLite::DatasetMethods

Constants

CONSTANT_MAP
EXTRACT_MAP
INSERT_CONFLICT_RESOLUTIONS

The allowed values for #insert_conflict

Public Instance Methods

cast_sql_append(sql, expr, type) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 506
def cast_sql_append(sql, expr, type)
  if type == Time or type == DateTime
    sql << "datetime("
    literal_append(sql, expr)
    sql << ')'
  elsif type == Date
    sql << "date("
    literal_append(sql, expr)
    sql << ')'
  else
    super
  end
end
complex_expression_sql_append(sql, op, args) click to toggle source

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

Calls superclass method
# 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 '
    complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
  when :^
    complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
  when :**
    unless (exp = args[1]).is_a?(Integer)
      raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
    end
    case exp
    when 0
      sql << '1'
    else
      sql << '('
      arg = args[0]
      if exp < 0
        invert = true
        exp = exp.abs
        sql << '(1.0 / ('
      end
      (exp - 1).times do 
        literal_append(sql, arg)
        sql << " * "
      end
      literal_append(sql, arg)
      sql << ')'
      if invert
        sql << "))"
      end
    end
  when :extract
    part = args[0]
    raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
    sql << "CAST(strftime(" << format << ', '
    literal_append(sql, args[1])
    sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
  else
    super
  end
end
constant_sql_append(sql, constant) click to toggle source

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

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 567
def constant_sql_append(sql, constant)
  if c = CONSTANT_MAP[constant]
    sql << c
  else
    super
  end
end
delete() click to toggle source

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.

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 578
def delete
  @opts[:where] ? super : where(1=>1).delete
end
explain(opts=nil) click to toggle source

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

# File lib/sequel/adapters/shared/sqlite.rb, line 585
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) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 595
def having(*cond)
  raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
  super
end
insert_conflict(resolution = :ignore) click to toggle source

Handle uniqueness violations when inserting, by using a specified resolution algorithm. With no options, uses INSERT OR REPLACE. SQLite supports the following conflict resolution algoriths: ROLLBACK, ABORT, FAIL, IGNORE and REPLACE.

Examples:

DB[:table].insert_conflict.insert(a: 1, b: 2)
# INSERT OR IGNORE INTO TABLE (a, b) VALUES (1, 2)

DB[:table].insert_conflict(:replace).insert(a: 1, b: 2)
# INSERT OR REPLACE INTO TABLE (a, b) VALUES (1, 2)
# File lib/sequel/adapters/shared/sqlite.rb, line 629
def insert_conflict(resolution = :ignore)
  unless INSERT_CONFLICT_RESOLUTIONS.include?(resolution.to_s.upcase)
    raise Error, "Invalid value passed to Dataset#insert_conflict: #{resolution.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
  end
  clone(:insert_conflict => resolution)
end
insert_ignore() click to toggle source

Ignore uniqueness/exclusion violations when inserting, using INSERT OR IGNORE. Exists mostly for compatibility to MySQL's insert_ignore. Example:

DB[:table].insert_ignore.insert(a: 1, b: 2)
# INSERT OR IGNORE INTO TABLE (a, b) VALUES (1, 2)
# File lib/sequel/adapters/shared/sqlite.rb, line 641
def insert_ignore
  insert_conflict(:ignore)
end
quoted_identifier_append(sql, c) click to toggle source

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

# File lib/sequel/adapters/shared/sqlite.rb, line 601
def quoted_identifier_append(sql, c)
  sql << '`' << c.to_s.gsub('`', '``') << '`'
end
select(*cols) click to toggle source

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.

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 609
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_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

# File lib/sequel/adapters/shared/sqlite.rb, line 646
def supports_cte?(type=:select)
  db.sqlite_version >= 30803
end
supports_cte_in_subqueries?() click to toggle source

SQLite supports CTEs in subqueries if it supports CTEs.

# File lib/sequel/adapters/shared/sqlite.rb, line 651
def supports_cte_in_subqueries?
  supports_cte?
end
supports_derived_column_lists?() click to toggle source

SQLite does not support table aliases with column aliases

# File lib/sequel/adapters/shared/sqlite.rb, line 656
def supports_derived_column_lists?
  false
end
supports_intersect_except_all?() click to toggle source

SQLite does not support INTERSECT ALL or EXCEPT ALL

# File lib/sequel/adapters/shared/sqlite.rb, line 661
def supports_intersect_except_all?
  false
end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

# File lib/sequel/adapters/shared/sqlite.rb, line 666
def supports_is_true?
  false
end
supports_multiple_column_in?() click to toggle source

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

# File lib/sequel/adapters/shared/sqlite.rb, line 671
def supports_multiple_column_in?
  false
end
supports_timestamp_timezones?() click to toggle source

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.

# File lib/sequel/adapters/shared/sqlite.rb, line 678
def supports_timestamp_timezones?
  db.use_timestamp_timezones?
end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

# File lib/sequel/adapters/shared/sqlite.rb, line 683
def supports_where_true?
  false
end

Private Instance Methods

_truncate_sql(table) click to toggle source

SQLite treats a DELETE with no WHERE clause as a TRUNCATE

# File lib/sequel/adapters/shared/sqlite.rb, line 790
def _truncate_sql(table)
  "DELETE FROM #{table}"
end
alias_qualified_column(col) click to toggle source

If col is a qualified column, alias it to the same as the column name

# File lib/sequel/adapters/shared/sqlite.rb, line 698
def alias_qualified_column(col)
  case col
  when Symbol
    t, c, a = split_symbol(col)
    if t && !a
      alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
    else
      col
    end
  when SQL::QualifiedIdentifier
    SQL::AliasedExpression.new(col, col.column)
  else
    col
  end
end
as_sql_append(sql, aliaz, column_aliases=nil) click to toggle source

SQLite uses string literals instead of identifiers in AS clauses.

# File lib/sequel/adapters/shared/sqlite.rb, line 690
def as_sql_append(sql, aliaz, column_aliases=nil)
  raise Error, "sqlite does not support derived column lists" if column_aliases
  aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
  sql << ' AS '
  literal_append(sql, aliaz.to_s)
end
default_import_slice() click to toggle source

SQLite supports a maximum of 500 rows in a VALUES clause.

# File lib/sequel/adapters/shared/sqlite.rb, line 715
def default_import_slice
  500
end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

# File lib/sequel/adapters/shared/sqlite.rb, line 720
def identifier_list(columns)
  columns.map{|i| quote_identifier(i)}.join(', ')
end
insert_conflict_sql(sql) click to toggle source

Add OR clauses to SQLite INSERT statements

# File lib/sequel/adapters/shared/sqlite.rb, line 725
def insert_conflict_sql(sql)
  if resolution = @opts[:insert_conflict]
    sql << " OR " << resolution.to_s.upcase
  end
end
literal_blob_append(sql, v) click to toggle source

SQLite uses a preceding X for hex escaping strings

# File lib/sequel/adapters/shared/sqlite.rb, line 732
def literal_blob_append(sql, v)
  sql <<  "X'" << v.unpack("H*").first << "'"
end
literal_false() click to toggle source

Respect the database integer_booleans setting, using 0 or 'f'.

# File lib/sequel/adapters/shared/sqlite.rb, line 737
def literal_false
  @db.integer_booleans ? '0' : "'f'"
end
literal_true() click to toggle source

Respect the database integer_booleans setting, using 1 or 't'.

# File lib/sequel/adapters/shared/sqlite.rb, line 742
def literal_true
  @db.integer_booleans ? '1' : "'t'"
end
multi_insert_sql_strategy() click to toggle source

SQLite only supporting multiple rows in the VALUES clause starting in 3.7.11. On older versions, fallback to using a UNION.

# File lib/sequel/adapters/shared/sqlite.rb, line 748
def multi_insert_sql_strategy
  db.sqlite_version >= 30711 ? :values : :union
end
native_function_name(emulated_function) click to toggle source

Emulate the char_length function with length

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 753
def native_function_name(emulated_function)
  if emulated_function == :char_length
    'length'
  else
    super
  end
end
select_lock_sql(sql) click to toggle source

SQLite does not support FOR UPDATE, but silently ignore it instead of raising an error for compatibility with other databases.

Calls superclass method
# File lib/sequel/adapters/shared/sqlite.rb, line 764
def select_lock_sql(sql)
  super unless @opts[:lock] == :update
end
select_only_offset_sql(sql) click to toggle source
# File lib/sequel/adapters/shared/sqlite.rb, line 768
def select_only_offset_sql(sql)
  sql << " LIMIT -1 OFFSET "
  literal_append(sql, @opts[:offset])
end
select_values_sql(sql) click to toggle source

Support VALUES clause instead of the SELECT clause to return rows.

# File lib/sequel/adapters/shared/sqlite.rb, line 774
def select_values_sql(sql)
  sql << "VALUES "
  expression_list_append(sql, opts[:values])
end
supports_cte_in_compounds?() click to toggle source

SQLite does not support CTEs directly inside UNION/INTERSECT/EXCEPT.

# File lib/sequel/adapters/shared/sqlite.rb, line 780
def supports_cte_in_compounds?
  false
end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

# File lib/sequel/adapters/shared/sqlite.rb, line 785
def supports_quoted_function_names?
  true
end