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

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
670 def having(*cond)
671   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900
672   super
673 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
748 def insert_conflict(opts = :ignore)
749   case opts
750   when Symbol, String
751     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
752       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
753     end
754     clone(:insert_conflict => opts)
755   when Hash
756     clone(:insert_on_conflict => opts)
757   else
758     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
759   end
760 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
767 def insert_ignore
768   insert_conflict(:ignore)
769 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
677 def insert_select(*values)
678   return unless supports_insert_select?
679   # Handle case where query does not return a row
680   server?(:default).with_sql_first(insert_select_sql(*values)) || false
681 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
685 def insert_select_sql(*values)
686   ds = opts[:returning] ? self : returning
687   ds.insert_sql(*values)
688 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
691 def quoted_identifier_append(sql, c)
692   sql << '`' << c.to_s.gsub('`', '``') << '`'
693 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
772 def returning(*values)
773   return super if values.empty?
774   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
775   clone(:returning=>_returning_values(values).freeze)
776 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
699 def select(*cols)
700   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)})
701     super(*cols.map{|c| alias_qualified_column(c)})
702   else
703     super
704   end
705 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

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

SQLite does not support deleting from a joined dataset

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

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
804 def supports_is_true?
805   false
806 end
supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

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

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

    # File lib/sequel/adapters/shared/sqlite.rb
819 def supports_returning?(_)
820   db.sqlite_version >= 33500
821 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
826 def supports_timestamp_timezones?
827   db.use_timestamp_timezones?
828 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
831 def supports_where_true?
832   false
833 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
836 def supports_window_clause?
837   db.sqlite_version >= 32800
838 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
849 def supports_window_function_frame_option?(option)
850   db.sqlite_version >= 32800 ? true : super
851 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
844 def supports_window_functions?
845   db.sqlite_version >= 32600
846 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
856 def _returning_values(values)
857   values.map do |v|
858     case v
859     when Symbol
860       _, c, a = split_symbol(v)
861       a ? v : Sequel.as(v, c)
862     when SQL::Identifier, SQL::QualifiedIdentifier
863       Sequel.as(v, unqualified_column_for(v))
864     else
865       v
866     end
867   end
868 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
1018 def _truncate_sql(table)
1019   "DELETE FROM #{table}"
1020 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
879 def alias_qualified_column(col)
880   case col
881   when Symbol
882     t, c, a = split_symbol(col)
883     if t && !a
884       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
885     else
886       col
887     end
888   when SQL::QualifiedIdentifier
889     SQL::AliasedExpression.new(col, col.column)
890   else
891     col
892   end
893 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
871 def as_sql_append(sql, aliaz, column_aliases=nil)
872   raise Error, "sqlite does not support derived column lists" if column_aliases
873   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
874   sql << ' AS '
875   literal_append(sql, aliaz.to_s)
876 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
896 def check_insert_allowed!
897   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
898   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
899 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
903 def default_import_slice
904   500
905 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

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

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

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

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

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

SQLite supports quoted function names.

     # File lib/sequel/adapters/shared/sqlite.rb
1013 def supports_quoted_function_names?
1014   true
1015 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
1023 def update_from_sql(sql)
1024   if(from = @opts[:from][1..-1]).empty?
1025     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1026   else
1027     sql << ' FROM '
1028     source_list_append(sql, from)
1029     select_join_sql(sql)
1030   end
1031 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
1034 def update_table_sql(sql)
1035   sql << ' '
1036   source_list_append(sql, @opts[:from][0..0])
1037 end