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

HAVING requires GROUP BY on SQLite

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

SQLite 3.8.3+ supports common table expressions.

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

SQLite does not support deleting from a joined dataset

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

SQLite does not support IS TRUE

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

SQLite 3.33.0 supports modifying joined datasets

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

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

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

SQLite cannot use WHERE 't'.

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

SQLite 3.28+ supports the WINDOW clause.

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

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

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

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

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

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

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

SQLite supports quoted function names.

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