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

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
608 def having(*cond)
609   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
610   super
611 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
671 def insert_conflict(opts = :ignore)
672   case opts
673   when Symbol, String
674     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
675       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
676     end
677     clone(:insert_conflict => opts)
678   when Hash
679     clone(:insert_on_conflict => opts)
680   else
681     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
682   end
683 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
690 def insert_ignore
691   insert_conflict(:ignore)
692 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
614 def quoted_identifier_append(sql, c)
615   sql << '`' << c.to_s.gsub('`', '``') << '`'
616 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
622 def select(*cols)
623   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)})
624     super(*cols.map{|c| alias_qualified_column(c)})
625   else
626     super
627   end
628 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
695 def supports_cte?(type=:select)
696   db.sqlite_version >= 30803
697 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
700 def supports_cte_in_subqueries?
701   supports_cte?
702 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
705 def supports_derived_column_lists?
706   false
707 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
710 def supports_intersect_except_all?
711   false
712 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
715 def supports_is_true?
716   false
717 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
720 def supports_multiple_column_in?
721   false
722 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
727 def supports_timestamp_timezones?
728   db.use_timestamp_timezones?
729 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
732 def supports_where_true?
733   false
734 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
737 def supports_window_clause?
738   db.sqlite_version >= 32800
739 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
750 def supports_window_function_frame_option?(option)
751   db.sqlite_version >= 32800 ? true : super
752 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
745 def supports_window_functions?
746   db.sqlite_version >= 32600
747 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
892 def _truncate_sql(table)
893   "DELETE FROM #{table}"
894 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
765 def alias_qualified_column(col)
766   case col
767   when Symbol
768     t, c, a = split_symbol(col)
769     if t && !a
770       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
771     else
772       col
773     end
774   when SQL::QualifiedIdentifier
775     SQL::AliasedExpression.new(col, col.column)
776   else
777     col
778   end
779 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
757 def as_sql_append(sql, aliaz, column_aliases=nil)
758   raise Error, "sqlite does not support derived column lists" if column_aliases
759   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
760   sql << ' AS '
761   literal_append(sql, aliaz.to_s)
762 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
782 def default_import_slice
783   500
784 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
792 def insert_conflict_sql(sql)
793   if resolution = @opts[:insert_conflict]
794     sql << " OR " << resolution.to_s.upcase
795   end
796 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
799 def insert_on_conflict_sql(sql)
800   if opts = @opts[:insert_on_conflict]
801     sql << " ON CONFLICT"
802 
803     if target = opts[:constraint] 
804       sql << " ON CONSTRAINT "
805       identifier_append(sql, target)
806     elsif target = opts[:target]
807       sql << ' '
808       identifier_append(sql, Array(target))
809       if conflict_where = opts[:conflict_where]
810         sql << " WHERE "
811         literal_append(sql, conflict_where)
812       end
813     end
814 
815     if values = opts[:update]
816       sql << " DO UPDATE SET "
817       update_sql_values_hash(sql, values)
818       if update_where = opts[:update_where]
819         sql << " WHERE "
820         literal_append(sql, update_where)
821       end
822     else
823       sql << " DO NOTHING"
824     end
825   end
826 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
829 def literal_blob_append(sql, v)
830   sql <<  "X'" << v.unpack("H*").first << "'"
831 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
839 def literal_true
840   @db.integer_booleans ? '1' : "'t'"
841 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
845 def multi_insert_sql_strategy
846   db.sqlite_version >= 30711 ? :values : :union
847 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
850 def native_function_name(emulated_function)
851   if emulated_function == :char_length
852     'length'
853   else
854     super
855   end
856 end
requires_emulating_nulls_first?() click to toggle source

SQLite does not natively support NULLS FIRST/LAST.

    # File lib/sequel/adapters/shared/sqlite.rb
859 def requires_emulating_nulls_first?
860   true
861 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
866 def select_lock_sql(sql)
867   super unless @opts[:lock] == :update
868 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
870 def select_only_offset_sql(sql)
871   sql << " LIMIT -1 OFFSET "
872   literal_append(sql, @opts[:offset])
873 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
876 def select_values_sql(sql)
877   sql << "VALUES "
878   expression_list_append(sql, opts[:values])
879 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
882 def supports_cte_in_compounds?
883   false
884 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

    # File lib/sequel/adapters/shared/sqlite.rb
887 def supports_quoted_function_names?
888   true
889 end