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
507 def cast_sql_append(sql, expr, type)
508   if type == Time or type == DateTime
509     sql << "datetime("
510     literal_append(sql, expr)
511     sql << ')'
512   elsif type == Date
513     sql << "date("
514     literal_append(sql, expr)
515     sql << ')'
516   else
517     super
518   end
519 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
523 def complex_expression_sql_append(sql, op, args)
524   case op
525   when :"NOT LIKE", :"NOT ILIKE"
526     sql << 'NOT '
527     complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
528   when :^
529     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
530   when :**
531     unless (exp = args[1]).is_a?(Integer)
532       raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
533     end
534     case exp
535     when 0
536       sql << '1'
537     else
538       sql << '('
539       arg = args[0]
540       if exp < 0
541         invert = true
542         exp = exp.abs
543         sql << '(1.0 / ('
544       end
545       (exp - 1).times do 
546         literal_append(sql, arg)
547         sql << " * "
548       end
549       literal_append(sql, arg)
550       sql << ')'
551       if invert
552         sql << "))"
553       end
554     end
555   when :extract
556     part = args[0]
557     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
558     sql << "CAST(strftime(" << format << ', '
559     literal_append(sql, args[1])
560     sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
561   else
562     super
563   end
564 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
568 def constant_sql_append(sql, constant)
569   if c = CONSTANT_MAP[constant]
570     sql << c
571   else
572     super
573   end
574 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
579 def delete
580   @opts[:where] ? super : where(1=>1).delete
581 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
586 def explain(opts=nil)
587   # Load the PrettyTable class, needed for explain output
588   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
589 
590   ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
591   rows = ds.all
592   Sequel::PrettyTable.string(rows, ds.columns)
593 end
having(*cond) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
596 def having(*cond)
597   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
598   super
599 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
630 def insert_conflict(resolution = :ignore)
631   unless INSERT_CONFLICT_RESOLUTIONS.include?(resolution.to_s.upcase)
632     raise Error, "Invalid value passed to Dataset#insert_conflict: #{resolution.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
633   end
634   clone(:insert_conflict => resolution)
635 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
642 def insert_ignore
643   insert_conflict(:ignore)
644 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
602 def quoted_identifier_append(sql, c)
603   sql << '`' << c.to_s.gsub('`', '``') << '`'
604 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
610 def select(*cols)
611   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)})
612     super(*cols.map{|c| alias_qualified_column(c)})
613   else
614     super
615   end
616 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
647 def supports_cte?(type=:select)
648   db.sqlite_version >= 30803
649 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
652 def supports_cte_in_subqueries?
653   supports_cte?
654 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
657 def supports_derived_column_lists?
658   false
659 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
662 def supports_intersect_except_all?
663   false
664 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
667 def supports_is_true?
668   false
669 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
672 def supports_multiple_column_in?
673   false
674 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
679 def supports_timestamp_timezones?
680   db.use_timestamp_timezones?
681 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
684 def supports_where_true?
685   false
686 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
791 def _truncate_sql(table)
792   "DELETE FROM #{table}"
793 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
699 def alias_qualified_column(col)
700   case col
701   when Symbol
702     t, c, a = split_symbol(col)
703     if t && !a
704       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
705     else
706       col
707     end
708   when SQL::QualifiedIdentifier
709     SQL::AliasedExpression.new(col, col.column)
710   else
711     col
712   end
713 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
691 def as_sql_append(sql, aliaz, column_aliases=nil)
692   raise Error, "sqlite does not support derived column lists" if column_aliases
693   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
694   sql << ' AS '
695   literal_append(sql, aliaz.to_s)
696 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
716 def default_import_slice
717   500
718 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
726 def insert_conflict_sql(sql)
727   if resolution = @opts[:insert_conflict]
728     sql << " OR " << resolution.to_s.upcase
729   end
730 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
733 def literal_blob_append(sql, v)
734   sql <<  "X'" << v.unpack("H*").first << "'"
735 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
743 def literal_true
744   @db.integer_booleans ? '1' : "'t'"
745 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
749 def multi_insert_sql_strategy
750   db.sqlite_version >= 30711 ? :values : :union
751 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
754 def native_function_name(emulated_function)
755   if emulated_function == :char_length
756     'length'
757   else
758     super
759   end
760 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
765 def select_lock_sql(sql)
766   super unless @opts[:lock] == :update
767 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
769 def select_only_offset_sql(sql)
770   sql << " LIMIT -1 OFFSET "
771   literal_append(sql, @opts[:offset])
772 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
775 def select_values_sql(sql)
776   sql << "VALUES "
777   expression_list_append(sql, opts[:values])
778 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
781 def supports_cte_in_compounds?
782   false
783 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

    # File lib/sequel/adapters/shared/sqlite.rb
786 def supports_quoted_function_names?
787   true
788 end