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
555 def cast_sql_append(sql, expr, type)
556   if type == Time or type == DateTime
557     sql << "datetime("
558     literal_append(sql, expr)
559     sql << ')'
560   elsif type == Date
561     sql << "date("
562     literal_append(sql, expr)
563     sql << ')'
564   else
565     super
566   end
567 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
571 def complex_expression_sql_append(sql, op, args)
572   case op
573   when :"NOT LIKE", :"NOT ILIKE"
574     sql << 'NOT '
575     complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
576   when :^
577     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
578   when :**
579     unless (exp = args[1]).is_a?(Integer)
580       raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
581     end
582     case exp
583     when 0
584       sql << '1'
585     else
586       sql << '('
587       arg = args[0]
588       if exp < 0
589         invert = true
590         exp = exp.abs
591         sql << '(1.0 / ('
592       end
593       (exp - 1).times do 
594         literal_append(sql, arg)
595         sql << " * "
596       end
597       literal_append(sql, arg)
598       sql << ')'
599       if invert
600         sql << "))"
601       end
602     end
603   when :extract
604     part = args[0]
605     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
606     sql << "CAST(strftime(" << format << ', '
607     literal_append(sql, args[1])
608     sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
609   else
610     super
611   end
612 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
616 def constant_sql_append(sql, constant)
617   if c = CONSTANT_MAP[constant]
618     sql << c
619   else
620     super
621   end
622 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
627 def delete
628   @opts[:where] ? super : where(1=>1).delete
629 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
634 def explain(opts=nil)
635   # Load the PrettyTable class, needed for explain output
636   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
637 
638   ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
639   rows = ds.all
640   Sequel::PrettyTable.string(rows, ds.columns)
641 end
having(*cond) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
644 def having(*cond)
645   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
646   super
647 end
insert_conflict(opts = :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.

On SQLite 3.24.0+, you can pass a hash to use an ON CONFLICT clause. With out :update option, uses ON CONFLICT DO NOTHING. Options:

:conflict_where

The index filter, when using a partial index to determine uniqueness.

:target

The column name or expression to handle uniqueness violations on.

:update

A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.

:update_where

A WHERE condition to use for the update.

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)

DB[:table].insert_conflict({}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING

DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO NOTHING

DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING

DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO UPDATE SET b = excluded.b

DB[:table].insert_conflict(target: :a,
  update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[:table][:status_id] => 1}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
    # File lib/sequel/adapters/shared/sqlite.rb
707 def insert_conflict(opts = :ignore)
708   case opts
709   when Symbol, String
710     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
711       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
712     end
713     clone(:insert_conflict => opts)
714   when Hash
715     clone(:insert_on_conflict => opts)
716   else
717     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
718   end
719 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
726 def insert_ignore
727   insert_conflict(:ignore)
728 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
650 def quoted_identifier_append(sql, c)
651   sql << '`' << c.to_s.gsub('`', '``') << '`'
652 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
658 def select(*cols)
659   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)})
660     super(*cols.map{|c| alias_qualified_column(c)})
661   else
662     super
663   end
664 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
731 def supports_cte?(type=:select)
732   db.sqlite_version >= 30803
733 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
736 def supports_cte_in_subqueries?
737   supports_cte?
738 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
741 def supports_derived_column_lists?
742   false
743 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
746 def supports_intersect_except_all?
747   false
748 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
751 def supports_is_true?
752   false
753 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
756 def supports_multiple_column_in?
757   false
758 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
763 def supports_timestamp_timezones?
764   db.use_timestamp_timezones?
765 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
768 def supports_where_true?
769   false
770 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
773 def supports_window_clause?
774   db.sqlite_version >= 32800
775 end
supports_window_function_frame_option?(option) click to toggle source

SQLite 3.28.0+ supports all window frame options that Sequel supports

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
786 def supports_window_function_frame_option?(option)
787   db.sqlite_version >= 32800 ? true : super
788 end
supports_window_functions?() click to toggle source

SQLite 3.25+ supports window functions. However, support is only enabled on SQLite 3.26.0+ because internal Sequel usage of window functions to implement eager loading of limited associations triggers an SQLite crash bug in versions 3.25.0-3.25.3.

    # File lib/sequel/adapters/shared/sqlite.rb
781 def supports_window_functions?
782   db.sqlite_version >= 32600
783 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
933 def _truncate_sql(table)
934   "DELETE FROM #{table}"
935 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
801 def alias_qualified_column(col)
802   case col
803   when Symbol
804     t, c, a = split_symbol(col)
805     if t && !a
806       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
807     else
808       col
809     end
810   when SQL::QualifiedIdentifier
811     SQL::AliasedExpression.new(col, col.column)
812   else
813     col
814   end
815 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
793 def as_sql_append(sql, aliaz, column_aliases=nil)
794   raise Error, "sqlite does not support derived column lists" if column_aliases
795   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
796   sql << ' AS '
797   literal_append(sql, aliaz.to_s)
798 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
818 def default_import_slice
819   500
820 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
828 def insert_conflict_sql(sql)
829   if resolution = @opts[:insert_conflict]
830     sql << " OR " << resolution.to_s.upcase
831   end
832 end
insert_on_conflict_sql(sql) click to toggle source

Add ON CONFLICT clause if it should be used

    # File lib/sequel/adapters/shared/sqlite.rb
835 def insert_on_conflict_sql(sql)
836   if opts = @opts[:insert_on_conflict]
837     sql << " ON CONFLICT"
838 
839     if target = opts[:constraint] 
840       sql << " ON CONSTRAINT "
841       identifier_append(sql, target)
842     elsif target = opts[:target]
843       sql << ' '
844       identifier_append(sql, Array(target))
845       if conflict_where = opts[:conflict_where]
846         sql << " WHERE "
847         literal_append(sql, conflict_where)
848       end
849     end
850 
851     if values = opts[:update]
852       sql << " DO UPDATE SET "
853       update_sql_values_hash(sql, values)
854       if update_where = opts[:update_where]
855         sql << " WHERE "
856         literal_append(sql, update_where)
857       end
858     else
859       sql << " DO NOTHING"
860     end
861   end
862 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
865 def literal_blob_append(sql, v)
866   sql <<  "X'" << v.unpack("H*").first << "'"
867 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
875 def literal_true
876   @db.integer_booleans ? '1' : "'t'"
877 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
881 def multi_insert_sql_strategy
882   db.sqlite_version >= 30711 ? :values : :union
883 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
886 def native_function_name(emulated_function)
887   if emulated_function == :char_length
888     'length'
889   else
890     super
891   end
892 end
requires_emulating_nulls_first?() click to toggle source

SQLite supports NULLS FIRST/LAST natively in 3.30+.

    # File lib/sequel/adapters/shared/sqlite.rb
895 def requires_emulating_nulls_first?
896   db.sqlite_version < 33000
897 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
902 def select_lock_sql(sql)
903   super unless @opts[:lock] == :update
904 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
906 def select_only_offset_sql(sql)
907   sql << " LIMIT -1 OFFSET "
908   literal_append(sql, @opts[:offset])
909 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
912 def select_values_sql(sql)
913   sql << "VALUES "
914   expression_list_append(sql, opts[:values])
915 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
918 def supports_cte_in_compounds?
919   false
920 end
supports_filtered_aggregates?() click to toggle source

SQLite 3.30 supports the FILTER clause for aggregate functions.

    # File lib/sequel/adapters/shared/sqlite.rb
923 def supports_filtered_aggregates?
924   db.sqlite_version >= 33000
925 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

    # File lib/sequel/adapters/shared/sqlite.rb
928 def supports_quoted_function_names?
929   true
930 end