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
596 def cast_sql_append(sql, expr, type)
597   if type == Time or type == DateTime
598     sql << "datetime("
599     literal_append(sql, expr)
600     sql << ')'
601   elsif type == Date
602     sql << "date("
603     literal_append(sql, expr)
604     sql << ')'
605   else
606     super
607   end
608 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
612 def complex_expression_sql_append(sql, op, args)
613   case op
614   when :"NOT LIKE", :"NOT ILIKE"
615     sql << 'NOT '
616     complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
617   when :^
618     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
619   when :**
620     unless (exp = args[1]).is_a?(Integer)
621       raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
622     end
623     case exp
624     when 0
625       sql << '1'
626     else
627       sql << '('
628       arg = args[0]
629       if exp < 0
630         invert = true
631         exp = exp.abs
632         sql << '(1.0 / ('
633       end
634       (exp - 1).times do 
635         literal_append(sql, arg)
636         sql << " * "
637       end
638       literal_append(sql, arg)
639       sql << ')'
640       if invert
641         sql << "))"
642       end
643     end
644   when :extract
645     part = args[0]
646     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
647     sql << "CAST(strftime(" << format << ', '
648     literal_append(sql, args[1])
649     sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
650   else
651     super
652   end
653 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
657 def constant_sql_append(sql, constant)
658   if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc
659     sql << c
660   else
661     super
662   end
663 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
668 def delete(&block)
669   @opts[:where] ? super : where(1=>1).delete(&block)
670 end
empty?() click to toggle source

Always return false when using VALUES

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
673 def empty?
674   return false if @opts[:values]
675   super
676 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
681 def explain(opts=nil)
682   # Load the PrettyTable class, needed for explain output
683   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
684 
685   ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
686   rows = ds.all
687   Sequel::PrettyTable.string(rows, ds.columns)
688 end
having(*cond) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
691 def having(*cond)
692   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900
693   super
694 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
769 def insert_conflict(opts = :ignore)
770   case opts
771   when Symbol, String
772     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
773       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
774     end
775     clone(:insert_conflict => opts)
776   when Hash
777     clone(:insert_on_conflict => opts)
778   else
779     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
780   end
781 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
788 def insert_ignore
789   insert_conflict(:ignore)
790 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
698 def insert_select(*values)
699   return unless supports_insert_select?
700   # Handle case where query does not return a row
701   server?(:default).with_sql_first(insert_select_sql(*values)) || false
702 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
706 def insert_select_sql(*values)
707   ds = opts[:returning] ? self : returning
708   ds.insert_sql(*values)
709 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
712 def quoted_identifier_append(sql, c)
713   sql << '`' << c.to_s.gsub('`', '``') << '`'
714 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
793 def returning(*values)
794   return super if values.empty?
795   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
796   clone(:returning=>_returning_values(values).freeze)
797 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
720 def select(*cols)
721   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)})
722     super(*cols.map{|c| alias_qualified_column(c)})
723   else
724     super
725   end
726 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
800 def supports_cte?(type=:select)
801   db.sqlite_version >= 30803
802 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
805 def supports_cte_in_subqueries?
806   supports_cte?
807 end
supports_deleting_joins?() click to toggle source

SQLite does not support deleting from a joined dataset

    # File lib/sequel/adapters/shared/sqlite.rb
815 def supports_deleting_joins?
816   false
817 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
810 def supports_derived_column_lists?
811   false
812 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
820 def supports_intersect_except_all?
821   false
822 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
825 def supports_is_true?
826   false
827 end
supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

    # File lib/sequel/adapters/shared/sqlite.rb
830 def supports_modifying_joins?
831   db.sqlite_version >= 33300
832 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
835 def supports_multiple_column_in?
836   false
837 end
supports_returning?(_) click to toggle source

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

    # File lib/sequel/adapters/shared/sqlite.rb
840 def supports_returning?(_)
841   db.sqlite_version >= 33500
842 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
847 def supports_timestamp_timezones?
848   db.use_timestamp_timezones?
849 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE ‘t’.

    # File lib/sequel/adapters/shared/sqlite.rb
852 def supports_where_true?
853   false
854 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
857 def supports_window_clause?
858   db.sqlite_version >= 32800
859 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
870 def supports_window_function_frame_option?(option)
871   db.sqlite_version >= 32800 ? true : super
872 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
865 def supports_window_functions?
866   db.sqlite_version >= 32600
867 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
877 def _returning_values(values)
878   values.map do |v|
879     case v
880     when Symbol
881       _, c, a = split_symbol(v)
882       a ? v : Sequel.as(v, c)
883     when SQL::Identifier, SQL::QualifiedIdentifier
884       Sequel.as(v, unqualified_column_for(v))
885     else
886       v
887     end
888   end
889 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
1049 def _truncate_sql(table)
1050   "DELETE FROM #{table}"
1051 end
aggreate_dataset_use_from_self?() click to toggle source

Use from_self for aggregate dataset using VALUES.

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
892 def aggreate_dataset_use_from_self?
893   super || @opts[:values]
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
905 def alias_qualified_column(col)
906   case col
907   when Symbol
908     t, c, a = split_symbol(col)
909     if t && !a
910       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
911     else
912       col
913     end
914   when SQL::QualifiedIdentifier
915     SQL::AliasedExpression.new(col, col.column)
916   else
917     col
918   end
919 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
897 def as_sql_append(sql, aliaz, column_aliases=nil)
898   raise Error, "sqlite does not support derived column lists" if column_aliases
899   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
900   sql << ' AS '
901   literal_append(sql, aliaz.to_s)
902 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
922 def check_insert_allowed!
923   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
924   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
925 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
929 def default_import_slice
930   500
931 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
934 def default_timestamp_format
935   db.use_timestamp_timezones? ? "'%Y-%m-%d %H:%M:%S.%6N%z'" : super
936 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
944 def insert_conflict_sql(sql)
945   if resolution = @opts[:insert_conflict]
946     sql << " OR " << resolution.to_s.upcase
947   end
948 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
951 def insert_on_conflict_sql(sql)
952   if opts = @opts[:insert_on_conflict]
953     sql << " ON CONFLICT"
954 
955     if target = opts[:constraint] 
956       sql << " ON CONSTRAINT "
957       identifier_append(sql, target)
958     elsif target = opts[:target]
959       sql << ' '
960       identifier_append(sql, Array(target))
961       if conflict_where = opts[:conflict_where]
962         sql << " WHERE "
963         literal_append(sql, conflict_where)
964       end
965     end
966 
967     if values = opts[:update]
968       sql << " DO UPDATE SET "
969       update_sql_values_hash(sql, values)
970       if update_where = opts[:update_where]
971         sql << " WHERE "
972         literal_append(sql, update_where)
973       end
974     else
975       sql << " DO NOTHING"
976     end
977   end
978 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
981 def literal_blob_append(sql, v)
982   sql <<  "X'" << v.unpack("H*").first << "'"
983 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
991 def literal_true
992   @db.integer_booleans ? '1' : "'t'"
993 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
997 def multi_insert_sql_strategy
998   db.sqlite_version >= 30711 ? :values : :union
999 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
1002 def native_function_name(emulated_function)
1003   if emulated_function == :char_length
1004     'length'
1005   else
1006     super
1007   end
1008 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
1011 def requires_emulating_nulls_first?
1012   db.sqlite_version < 33000
1013 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
1018 def select_lock_sql(sql)
1019   super unless @opts[:lock] == :update
1020 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/sqlite.rb
1022 def select_only_offset_sql(sql)
1023   sql << " LIMIT -1 OFFSET "
1024   literal_append(sql, @opts[:offset])
1025 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
1028 def select_values_sql(sql)
1029   sql << "VALUES "
1030   expression_list_append(sql, opts[:values])
1031 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
1034 def supports_cte_in_compounds?
1035   false
1036 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
1039 def supports_filtered_aggregates?
1040   db.sqlite_version >= 33000
1041 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

     # File lib/sequel/adapters/shared/sqlite.rb
1044 def supports_quoted_function_names?
1045   true
1046 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
1054 def update_from_sql(sql)
1055   if(from = @opts[:from][1..-1]).empty?
1056     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1057   else
1058     sql << ' FROM '
1059     source_list_append(sql, from)
1060     select_join_sql(sql)
1061   end
1062 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
1065 def update_table_sql(sql)
1066   sql << ' '
1067   source_list_append(sql, @opts[:from][0..0])
1068 end