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

Always return false when using VALUES

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

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
679 def having(*cond)
680   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900
681   super
682 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
757 def insert_conflict(opts = :ignore)
758   case opts
759   when Symbol, String
760     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
761       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
762     end
763     clone(:insert_conflict => opts)
764   when Hash
765     clone(:insert_on_conflict => opts)
766   else
767     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
768   end
769 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
776 def insert_ignore
777   insert_conflict(:ignore)
778 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
686 def insert_select(*values)
687   return unless supports_insert_select?
688   # Handle case where query does not return a row
689   server?(:default).with_sql_first(insert_select_sql(*values)) || false
690 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
694 def insert_select_sql(*values)
695   ds = opts[:returning] ? self : returning
696   ds.insert_sql(*values)
697 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
700 def quoted_identifier_append(sql, c)
701   sql << '`' << c.to_s.gsub('`', '``') << '`'
702 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
781 def returning(*values)
782   return super if values.empty?
783   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
784   clone(:returning=>_returning_values(values).freeze)
785 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
708 def select(*cols)
709   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)})
710     super(*cols.map{|c| alias_qualified_column(c)})
711   else
712     super
713   end
714 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
788 def supports_cte?(type=:select)
789   db.sqlite_version >= 30803
790 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
793 def supports_cte_in_subqueries?
794   supports_cte?
795 end
supports_deleting_joins?() click to toggle source

SQLite does not support deleting from a joined dataset

    # File lib/sequel/adapters/shared/sqlite.rb
803 def supports_deleting_joins?
804   false
805 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
798 def supports_derived_column_lists?
799   false
800 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
808 def supports_intersect_except_all?
809   false
810 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
813 def supports_is_true?
814   false
815 end
supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

    # File lib/sequel/adapters/shared/sqlite.rb
818 def supports_modifying_joins?
819   db.sqlite_version >= 33300
820 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
823 def supports_multiple_column_in?
824   false
825 end
supports_returning?(_) click to toggle source

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

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

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
840 def supports_where_true?
841   false
842 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
845 def supports_window_clause?
846   db.sqlite_version >= 32800
847 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
858 def supports_window_function_frame_option?(option)
859   db.sqlite_version >= 32800 ? true : super
860 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
853 def supports_window_functions?
854   db.sqlite_version >= 32600
855 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
865 def _returning_values(values)
866   values.map do |v|
867     case v
868     when Symbol
869       _, c, a = split_symbol(v)
870       a ? v : Sequel.as(v, c)
871     when SQL::Identifier, SQL::QualifiedIdentifier
872       Sequel.as(v, unqualified_column_for(v))
873     else
874       v
875     end
876   end
877 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
1032 def _truncate_sql(table)
1033   "DELETE FROM #{table}"
1034 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
880 def aggreate_dataset_use_from_self?
881   super || @opts[:values]
882 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
893 def alias_qualified_column(col)
894   case col
895   when Symbol
896     t, c, a = split_symbol(col)
897     if t && !a
898       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
899     else
900       col
901     end
902   when SQL::QualifiedIdentifier
903     SQL::AliasedExpression.new(col, col.column)
904   else
905     col
906   end
907 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
885 def as_sql_append(sql, aliaz, column_aliases=nil)
886   raise Error, "sqlite does not support derived column lists" if column_aliases
887   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
888   sql << ' AS '
889   literal_append(sql, aliaz.to_s)
890 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
910 def check_insert_allowed!
911   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
912   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
913 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
917 def default_import_slice
918   500
919 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
927 def insert_conflict_sql(sql)
928   if resolution = @opts[:insert_conflict]
929     sql << " OR " << resolution.to_s.upcase
930   end
931 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
934 def insert_on_conflict_sql(sql)
935   if opts = @opts[:insert_on_conflict]
936     sql << " ON CONFLICT"
937 
938     if target = opts[:constraint] 
939       sql << " ON CONSTRAINT "
940       identifier_append(sql, target)
941     elsif target = opts[:target]
942       sql << ' '
943       identifier_append(sql, Array(target))
944       if conflict_where = opts[:conflict_where]
945         sql << " WHERE "
946         literal_append(sql, conflict_where)
947       end
948     end
949 
950     if values = opts[:update]
951       sql << " DO UPDATE SET "
952       update_sql_values_hash(sql, values)
953       if update_where = opts[:update_where]
954         sql << " WHERE "
955         literal_append(sql, update_where)
956       end
957     else
958       sql << " DO NOTHING"
959     end
960   end
961 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
964 def literal_blob_append(sql, v)
965   sql <<  "X'" << v.unpack("H*").first << "'"
966 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
974 def literal_true
975   @db.integer_booleans ? '1' : "'t'"
976 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
980 def multi_insert_sql_strategy
981   db.sqlite_version >= 30711 ? :values : :union
982 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
985 def native_function_name(emulated_function)
986   if emulated_function == :char_length
987     'length'
988   else
989     super
990   end
991 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
994 def requires_emulating_nulls_first?
995   db.sqlite_version < 33000
996 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
1001 def select_lock_sql(sql)
1002   super unless @opts[:lock] == :update
1003 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/sqlite.rb
1005 def select_only_offset_sql(sql)
1006   sql << " LIMIT -1 OFFSET "
1007   literal_append(sql, @opts[:offset])
1008 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
1011 def select_values_sql(sql)
1012   sql << "VALUES "
1013   expression_list_append(sql, opts[:values])
1014 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
1017 def supports_cte_in_compounds?
1018   false
1019 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
1022 def supports_filtered_aggregates?
1023   db.sqlite_version >= 33000
1024 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

     # File lib/sequel/adapters/shared/sqlite.rb
1027 def supports_quoted_function_names?
1028   true
1029 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
1037 def update_from_sql(sql)
1038   if(from = @opts[:from][1..-1]).empty?
1039     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1040   else
1041     sql << ' FROM '
1042     source_list_append(sql, from)
1043     select_join_sql(sql)
1044   end
1045 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
1048 def update_table_sql(sql)
1049   sql << ' '
1050   source_list_append(sql, @opts[:from][0..0])
1051 end