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

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
659 def having(*cond)
660   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
661   super
662 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
737 def insert_conflict(opts = :ignore)
738   case opts
739   when Symbol, String
740     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
741       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
742     end
743     clone(:insert_conflict => opts)
744   when Hash
745     clone(:insert_on_conflict => opts)
746   else
747     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
748   end
749 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
756 def insert_ignore
757   insert_conflict(:ignore)
758 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
666 def insert_select(*values)
667   return unless supports_insert_select?
668   # Handle case where query does not return a row
669   server?(:default).with_sql_first(insert_select_sql(*values)) || false
670 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
674 def insert_select_sql(*values)
675   ds = opts[:returning] ? self : returning
676   ds.insert_sql(*values)
677 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
680 def quoted_identifier_append(sql, c)
681   sql << '`' << c.to_s.gsub('`', '``') << '`'
682 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
761 def returning(*values)
762   return super if values.empty?
763   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
764   clone(:returning=>_returning_values(values).freeze)
765 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
688 def select(*cols)
689   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)})
690     super(*cols.map{|c| alias_qualified_column(c)})
691   else
692     super
693   end
694 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

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

SQLite does not support deleting from a joined dataset

    # File lib/sequel/adapters/shared/sqlite.rb
783 def supports_deleting_joins?
784   false
785 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
778 def supports_derived_column_lists?
779   false
780 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
788 def supports_intersect_except_all?
789   false
790 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
793 def supports_is_true?
794   false
795 end
supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

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

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

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

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
820 def supports_where_true?
821   false
822 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
825 def supports_window_clause?
826   db.sqlite_version >= 32800
827 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
838 def supports_window_function_frame_option?(option)
839   db.sqlite_version >= 32800 ? true : super
840 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
833 def supports_window_functions?
834   db.sqlite_version >= 32600
835 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
845 def _returning_values(values)
846   values.map do |v|
847     case v
848     when Symbol
849       _, c, a = split_symbol(v)
850       a ? v : Sequel.as(v, c)
851     when SQL::Identifier, SQL::QualifiedIdentifier
852       Sequel.as(v, unqualified_column_for(v))
853     else
854       v
855     end
856   end
857 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
1007 def _truncate_sql(table)
1008   "DELETE FROM #{table}"
1009 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
868 def alias_qualified_column(col)
869   case col
870   when Symbol
871     t, c, a = split_symbol(col)
872     if t && !a
873       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
874     else
875       col
876     end
877   when SQL::QualifiedIdentifier
878     SQL::AliasedExpression.new(col, col.column)
879   else
880     col
881   end
882 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
860 def as_sql_append(sql, aliaz, column_aliases=nil)
861   raise Error, "sqlite does not support derived column lists" if column_aliases
862   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
863   sql << ' AS '
864   literal_append(sql, aliaz.to_s)
865 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
885 def check_insert_allowed!
886   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
887   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
888 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
892 def default_import_slice
893   500
894 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

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

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

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

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

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

SQLite supports quoted function names.

     # File lib/sequel/adapters/shared/sqlite.rb
1002 def supports_quoted_function_names?
1003   true
1004 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
1012 def update_from_sql(sql)
1013   if(from = @opts[:from][1..-1]).empty?
1014     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1015   else
1016     sql << ' FROM '
1017     source_list_append(sql, from)
1018     select_join_sql(sql)
1019   end
1020 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
1023 def update_table_sql(sql)
1024   sql << ' '
1025   source_list_append(sql, @opts[:from][0..0])
1026 end