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
576 def cast_sql_append(sql, expr, type)
577   if type == Time or type == DateTime
578     sql << "datetime("
579     literal_append(sql, expr)
580     sql << ')'
581   elsif type == Date
582     sql << "date("
583     literal_append(sql, expr)
584     sql << ')'
585   else
586     super
587   end
588 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
592 def complex_expression_sql_append(sql, op, args)
593   case op
594   when :"NOT LIKE", :"NOT ILIKE"
595     sql << 'NOT '
596     complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
597   when :^
598     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
599   when :**
600     unless (exp = args[1]).is_a?(Integer)
601       raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
602     end
603     case exp
604     when 0
605       sql << '1'
606     else
607       sql << '('
608       arg = args[0]
609       if exp < 0
610         invert = true
611         exp = exp.abs
612         sql << '(1.0 / ('
613       end
614       (exp - 1).times do 
615         literal_append(sql, arg)
616         sql << " * "
617       end
618       literal_append(sql, arg)
619       sql << ')'
620       if invert
621         sql << "))"
622       end
623     end
624   when :extract
625     part = args[0]
626     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
627     sql << "CAST(strftime(" << format << ', '
628     literal_append(sql, args[1])
629     sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
630   else
631     super
632   end
633 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
637 def constant_sql_append(sql, constant)
638   if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc
639     sql << c
640   else
641     super
642   end
643 end
delete(&block) 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
648 def delete(&block)
649   @opts[:where] ? super : where(1=>1).delete(&block)
650 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
655 def explain(opts=nil)
656   # Load the PrettyTable class, needed for explain output
657   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
658 
659   ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
660   rows = ds.all
661   Sequel::PrettyTable.string(rows, ds.columns)
662 end
having(*cond) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
665 def having(*cond)
666   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
667   super
668 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
743 def insert_conflict(opts = :ignore)
744   case opts
745   when Symbol, String
746     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
747       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
748     end
749     clone(:insert_conflict => opts)
750   when Hash
751     clone(:insert_on_conflict => opts)
752   else
753     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
754   end
755 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
762 def insert_ignore
763   insert_conflict(:ignore)
764 end
insert_select(*values) click to toggle source

Support insert select for associations, so that the model code can use returning instead of a separate query.

    # File lib/sequel/adapters/shared/sqlite.rb
672 def insert_select(*values)
673   return unless supports_insert_select?
674   # Handle case where query does not return a row
675   server?(:default).with_sql_first(insert_select_sql(*values)) || false
676 end
insert_select_sql(*values) click to toggle source

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

    # File lib/sequel/adapters/shared/sqlite.rb
680 def insert_select_sql(*values)
681   ds = opts[:returning] ? self : returning
682   ds.insert_sql(*values)
683 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
686 def quoted_identifier_append(sql, c)
687   sql << '`' << c.to_s.gsub('`', '``') << '`'
688 end
returning(*values) click to toggle source

Automatically add aliases to RETURNING values to work around SQLite bug.

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
767 def returning(*values)
768   return super if values.empty?
769   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
770   clone(:returning=>_returning_values(values).freeze)
771 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
694 def select(*cols)
695   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)})
696     super(*cols.map{|c| alias_qualified_column(c)})
697   else
698     super
699   end
700 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
774 def supports_cte?(type=:select)
775   db.sqlite_version >= 30803
776 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
779 def supports_cte_in_subqueries?
780   supports_cte?
781 end
supports_deleting_joins?() click to toggle source

SQLite does not support deleting from a joined dataset

    # File lib/sequel/adapters/shared/sqlite.rb
789 def supports_deleting_joins?
790   false
791 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
784 def supports_derived_column_lists?
785   false
786 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
794 def supports_intersect_except_all?
795   false
796 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
799 def supports_is_true?
800   false
801 end
supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

    # File lib/sequel/adapters/shared/sqlite.rb
804 def supports_modifying_joins?
805   db.sqlite_version >= 33300
806 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
809 def supports_multiple_column_in?
810   false
811 end
supports_returning?(_) click to toggle source

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

    # File lib/sequel/adapters/shared/sqlite.rb
814 def supports_returning?(_)
815   db.sqlite_version >= 33500
816 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
821 def supports_timestamp_timezones?
822   db.use_timestamp_timezones?
823 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE ‘t’.

    # File lib/sequel/adapters/shared/sqlite.rb
826 def supports_where_true?
827   false
828 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
831 def supports_window_clause?
832   db.sqlite_version >= 32800
833 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
844 def supports_window_function_frame_option?(option)
845   db.sqlite_version >= 32800 ? true : super
846 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
839 def supports_window_functions?
840   db.sqlite_version >= 32600
841 end

Private Instance Methods

_returning_values(values) click to toggle source

Add aliases to symbols and identifiers to work around SQLite bug.

    # File lib/sequel/adapters/shared/sqlite.rb
851 def _returning_values(values)
852   values.map do |v|
853     case v
854     when Symbol
855       _, c, a = split_symbol(v)
856       a ? v : Sequel.as(v, c)
857     when SQL::Identifier, SQL::QualifiedIdentifier
858       Sequel.as(v, unqualified_column_for(v))
859     else
860       v
861     end
862   end
863 end
_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
1013 def _truncate_sql(table)
1014   "DELETE FROM #{table}"
1015 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
874 def alias_qualified_column(col)
875   case col
876   when Symbol
877     t, c, a = split_symbol(col)
878     if t && !a
879       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
880     else
881       col
882     end
883   when SQL::QualifiedIdentifier
884     SQL::AliasedExpression.new(col, col.column)
885   else
886     col
887   end
888 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
866 def as_sql_append(sql, aliaz, column_aliases=nil)
867   raise Error, "sqlite does not support derived column lists" if column_aliases
868   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
869   sql << ' AS '
870   literal_append(sql, aliaz.to_s)
871 end
check_delete_allowed!()
check_insert_allowed!() click to toggle source

Raise an InvalidOperation exception if insert is not allowed for this dataset.

    # File lib/sequel/adapters/shared/sqlite.rb
891 def check_insert_allowed!
892   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
893   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
894 end
Also aliased as: check_delete_allowed!
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
898 def default_import_slice
899   500
900 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
908 def insert_conflict_sql(sql)
909   if resolution = @opts[:insert_conflict]
910     sql << " OR " << resolution.to_s.upcase
911   end
912 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
915 def insert_on_conflict_sql(sql)
916   if opts = @opts[:insert_on_conflict]
917     sql << " ON CONFLICT"
918 
919     if target = opts[:constraint] 
920       sql << " ON CONSTRAINT "
921       identifier_append(sql, target)
922     elsif target = opts[:target]
923       sql << ' '
924       identifier_append(sql, Array(target))
925       if conflict_where = opts[:conflict_where]
926         sql << " WHERE "
927         literal_append(sql, conflict_where)
928       end
929     end
930 
931     if values = opts[:update]
932       sql << " DO UPDATE SET "
933       update_sql_values_hash(sql, values)
934       if update_where = opts[:update_where]
935         sql << " WHERE "
936         literal_append(sql, update_where)
937       end
938     else
939       sql << " DO NOTHING"
940     end
941   end
942 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
945 def literal_blob_append(sql, v)
946   sql <<  "X'" << v.unpack("H*").first << "'"
947 end
literal_false() click to toggle source

Respect the database integer_booleans setting, using 0 or ‘f’.

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

Respect the database integer_booleans setting, using 1 or ‘t’.

    # File lib/sequel/adapters/shared/sqlite.rb
955 def literal_true
956   @db.integer_booleans ? '1' : "'t'"
957 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
961 def multi_insert_sql_strategy
962   db.sqlite_version >= 30711 ? :values : :union
963 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
966 def native_function_name(emulated_function)
967   if emulated_function == :char_length
968     'length'
969   else
970     super
971   end
972 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
975 def requires_emulating_nulls_first?
976   db.sqlite_version < 33000
977 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
982 def select_lock_sql(sql)
983   super unless @opts[:lock] == :update
984 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
986 def select_only_offset_sql(sql)
987   sql << " LIMIT -1 OFFSET "
988   literal_append(sql, @opts[:offset])
989 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
992 def select_values_sql(sql)
993   sql << "VALUES "
994   expression_list_append(sql, opts[:values])
995 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
 998 def supports_cte_in_compounds?
 999   false
1000 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
1003 def supports_filtered_aggregates?
1004   db.sqlite_version >= 33000
1005 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

     # File lib/sequel/adapters/shared/sqlite.rb
1008 def supports_quoted_function_names?
1009   true
1010 end
update_from_sql(sql) click to toggle source

Use FROM to specify additional tables in an update query

     # File lib/sequel/adapters/shared/sqlite.rb
1018 def update_from_sql(sql)
1019   if(from = @opts[:from][1..-1]).empty?
1020     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1021   else
1022     sql << ' FROM '
1023     source_list_append(sql, from)
1024     select_join_sql(sql)
1025   end
1026 end
update_table_sql(sql) click to toggle source

Only include the primary table in the main update clause

     # File lib/sequel/adapters/shared/sqlite.rb
1029 def update_table_sql(sql)
1030   sql << ' '
1031   source_list_append(sql, @opts[:from][0..0])
1032 end