module Sequel::SQLite::DatasetMethods

Constants

CONSTANT_MAP
EXTRACT_MAP
INSERT_CONFLICT_RESOLUTIONS: The allowed values for insert_conflict

Public Instance Methods

Source

    # File lib/sequel/adapters/shared/sqlite.rb
598 def cast_sql_append(sql, expr, type)
599   if type == Time or type == DateTime
600     sql << "datetime("
601     literal_append(sql, expr)
602     sql << ')'
603   elsif type == Date
604     sql << "date("
605     literal_append(sql, expr)
606     sql << ')'
607   else
608     super
609   end
610 end

Calls superclass method

complex_expression_sql_append (sql, op, args)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
614 def complex_expression_sql_append(sql, op, args)
615   case op
616   when :"NOT LIKE", :"NOT ILIKE"
617     sql << 'NOT '
618     complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
619   when :^
620     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
621   when :**
622     unless (exp = args[1]).is_a?(Integer)
623       raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
624     end
625     case exp
626     when 0
627       sql << '1'
628     else
629       sql << '('
630       arg = args[0]
631       if exp < 0
632         invert = true
633         exp = exp.abs
634         sql << '(1.0 / ('
635       end
636       (exp - 1).times do 
637         literal_append(sql, arg)
638         sql << " * "
639       end
640       literal_append(sql, arg)
641       sql << ')'
642       if invert
643         sql << "))"
644       end
645     end
646   when :extract
647     part = args[0]
648     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
649     sql << "CAST(strftime(" << format << ', '
650     literal_append(sql, args[1])
651     sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
652   else
653     super
654   end
655 end

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

constant_sql_append (sql, constant)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
659 def constant_sql_append(sql, constant)
660   if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc
661     sql << c
662   else
663     super
664   end
665 end

SQLite has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.

Calls superclass method

delete (&block)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
670 def delete(&block)
671   @opts[:where] ? super : where(1=>1).delete(&block)
672 end

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

empty? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
675 def empty?
676   return false if @opts[:values]
677   super
678 end

Always return false when using VALUES

Calls superclass method

explain (opts=nil)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
683 def explain(opts=nil)
684   # Load the PrettyTable class, needed for explain output
685   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
686 
687   ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
688   rows = ds.all
689   Sequel::PrettyTable.string(rows, ds.columns)
690 end

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.

having (*cond)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
693 def having(*cond)
694   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900
695   super
696 end

HAVING requires GROUP BY on SQLite

Calls superclass method

insert_conflict (opts = :ignore)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
771 def insert_conflict(opts = :ignore)
772   case opts
773   when Symbol, String
774     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
775       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
776     end
777     clone(:insert_conflict => opts)
778   when Hash
779     clone(:insert_on_conflict => opts)
780   else
781     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
782   end
783 end

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)

insert_ignore ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
790 def insert_ignore
791   insert_conflict(:ignore)
792 end

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)

insert_select (*values)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
700 def insert_select(*values)
701   return unless supports_insert_select?
702   # Handle case where query does not return a row
703   server?(:default).with_sql_first(insert_select_sql(*values)) || false
704 end

Support insert select for associations, so that the model code can use returning instead of a separate query.

insert_select_sql (*values)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
708 def insert_select_sql(*values)
709   ds = opts[:returning] ? self : returning
710   ds.insert_sql(*values)
711 end

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

quoted_identifier_append (sql, c)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
714 def quoted_identifier_append(sql, c)
715   sql << '`' << c.to_s.gsub('`', '``') << '`'
716 end

SQLite uses the nonstandard ‘ (backtick) for quoting identifiers.

returning (*values)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
795 def returning(*values)
796   return super if values.empty?
797   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
798   clone(:returning=>_returning_values(values).freeze)
799 end

Automatically add aliases to RETURNING values to work around SQLite bug.

Calls superclass method

select (*cols)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
722 def select(*cols)
723   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)})
724     super(*cols.map{|c| alias_qualified_column(c)})
725   else
726     super
727   end
728 end

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

supports_cte? (type=:select)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
802 def supports_cte?(type=:select)
803   db.sqlite_version >= 30803
804 end

SQLite 3.8.3+ supports common table expressions.

supports_cte_in_subqueries? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
807 def supports_cte_in_subqueries?
808   supports_cte?
809 end

SQLite supports CTEs in subqueries if it supports CTEs.

supports_deleting_joins? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
817 def supports_deleting_joins?
818   false
819 end

SQLite does not support deleting from a joined dataset

supports_derived_column_lists? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
812 def supports_derived_column_lists?
813   false
814 end

SQLite does not support table aliases with column aliases

supports_intersect_except_all? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
822 def supports_intersect_except_all?
823   false
824 end

SQLite does not support INTERSECT ALL or EXCEPT ALL

supports_is_true? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
827 def supports_is_true?
828   false
829 end

SQLite does not support IS TRUE

supports_modifying_joins? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
832 def supports_modifying_joins?
833   db.sqlite_version >= 33300
834 end

SQLite 3.33.0 supports modifying joined datasets

supports_multiple_column_in? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
837 def supports_multiple_column_in?
838   false
839 end

SQLite does not support multiple columns for the IN/NOT IN operators

supports_returning? (_)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
842 def supports_returning?(_)
843   db.sqlite_version >= 33500
844 end

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

supports_timestamp_timezones? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
849 def supports_timestamp_timezones?
850   db.use_timestamp_timezones?
851 end

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.

supports_where_true? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
854 def supports_where_true?
855   false
856 end

SQLite cannot use WHERE ‘t’.

supports_window_clause? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
859 def supports_window_clause?
860   db.sqlite_version >= 32800
861 end

SQLite 3.28+ supports the WINDOW clause.

supports_window_function_frame_option? (option)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
872 def supports_window_function_frame_option?(option)
873   db.sqlite_version >= 32800 ? true : super
874 end

SQLite 3.28.0+ supports all window frame options that Sequel supports

Calls superclass method

supports_window_functions? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
867 def supports_window_functions?
868   db.sqlite_version >= 32600
869 end

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.

Private Instance Methods

_returning_values (values)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
879 def _returning_values(values)
880   values.map do |v|
881     case v
882     when Symbol
883       _, c, a = split_symbol(v)
884       a ? v : Sequel.as(v, c)
885     when SQL::Identifier, SQL::QualifiedIdentifier
886       Sequel.as(v, unqualified_column_for(v))
887     else
888       v
889     end
890   end
891 end

Add aliases to symbols and identifiers to work around SQLite bug.

_truncate_sql (table)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1051 def _truncate_sql(table)
1052   "DELETE FROM #{table}"
1053 end

SQLite treats a DELETE with no WHERE clause as a TRUNCATE

aggreate_dataset_use_from_self? ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
894 def aggreate_dataset_use_from_self?
895   super || @opts[:values]
896 end

Use from_self for aggregate dataset using VALUES.

Calls superclass method

alias_qualified_column (col)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
907 def alias_qualified_column(col)
908   case col
909   when Symbol
910     t, c, a = split_symbol(col)
911     if t && !a
912       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
913     else
914       col
915     end
916   when SQL::QualifiedIdentifier
917     SQL::AliasedExpression.new(col, col.column)
918   else
919     col
920   end
921 end

If col is a qualified column, alias it to the same as the column name

as_sql_append (sql, aliaz, column_aliases=nil)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
899 def as_sql_append(sql, aliaz, column_aliases=nil)
900   raise Error, "sqlite does not support derived column lists" if column_aliases
901   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
902   sql << ' AS '
903   literal_append(sql, aliaz.to_s)
904 end

SQLite uses string literals instead of identifiers in AS clauses.

check_delete_allowed! ()

Alias for: check_insert_allowed!

check_insert_allowed! ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
924 def check_insert_allowed!
925   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
926   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
927 end

Raise an InvalidOperation exception if insert is not allowed for this dataset.

Also aliased as: check_delete_allowed!

default_import_slice ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
931 def default_import_slice
932   500
933 end

SQLite supports a maximum of 500 rows in a VALUES clause.

default_timestamp_format ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
936 def default_timestamp_format
937   db.use_timestamp_timezones? ? "'%Y-%m-%d %H:%M:%S.%6N%z'" : super
938 end

The strftime format to use when literalizing the time.

Calls superclass method

identifier_list (columns)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
941 def identifier_list(columns)
942   columns.map{|i| quote_identifier(i)}.join(', ')
943 end

SQL fragment specifying a list of identifiers

insert_conflict_sql (sql)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
946 def insert_conflict_sql(sql)
947   if resolution = @opts[:insert_conflict]
948     sql << " OR " << resolution.to_s.upcase
949   end
950 end

Add OR clauses to SQLite INSERT statements

insert_on_conflict_sql (sql)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
953 def insert_on_conflict_sql(sql)
954   if opts = @opts[:insert_on_conflict]
955     sql << " ON CONFLICT"
956 
957     if target = opts[:constraint] 
958       sql << " ON CONSTRAINT "
959       identifier_append(sql, target)
960     elsif target = opts[:target]
961       sql << ' '
962       identifier_append(sql, Array(target))
963       if conflict_where = opts[:conflict_where]
964         sql << " WHERE "
965         literal_append(sql, conflict_where)
966       end
967     end
968 
969     if values = opts[:update]
970       sql << " DO UPDATE SET "
971       update_sql_values_hash(sql, values)
972       if update_where = opts[:update_where]
973         sql << " WHERE "
974         literal_append(sql, update_where)
975       end
976     else
977       sql << " DO NOTHING"
978     end
979   end
980 end

Add ON CONFLICT clause if it should be used

literal_blob_append (sql, v)

Source

    # File lib/sequel/adapters/shared/sqlite.rb
983 def literal_blob_append(sql, v)
984   sql <<  "X'" << v.unpack("H*").first << "'"
985 end

SQLite uses a preceding X for hex escaping strings

literal_false ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
988 def literal_false
989   @db.integer_booleans ? '0' : "'f'"
990 end

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

literal_true ()

Source

    # File lib/sequel/adapters/shared/sqlite.rb
993 def literal_true
994   @db.integer_booleans ? '1' : "'t'"
995 end

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

multi_insert_sql_strategy ()

Source

     # File lib/sequel/adapters/shared/sqlite.rb
 999 def multi_insert_sql_strategy
1000   db.sqlite_version >= 30711 ? :values : :union
1001 end

SQLite only supporting multiple rows in the VALUES clause starting in 3.7.11. On older versions, fallback to using a UNION.

native_function_name (emulated_function)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1004 def native_function_name(emulated_function)
1005   if emulated_function == :char_length
1006     'length'
1007   else
1008     super
1009   end
1010 end

Emulate the char_length function with length

Calls superclass method

requires_emulating_nulls_first? ()

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1013 def requires_emulating_nulls_first?
1014   db.sqlite_version < 33000
1015 end

SQLite supports NULLS FIRST/LAST natively in 3.30+.

select_lock_sql (sql)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1020 def select_lock_sql(sql)
1021   super unless @opts[:lock] == :update
1022 end

SQLite does not support FOR UPDATE, but silently ignore it instead of raising an error for compatibility with other databases.

Calls superclass method

select_only_offset_sql (sql)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1024 def select_only_offset_sql(sql)
1025   sql << " LIMIT -1 OFFSET "
1026   literal_append(sql, @opts[:offset])
1027 end

select_values_sql (sql)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1030 def select_values_sql(sql)
1031   sql << "VALUES "
1032   expression_list_append(sql, opts[:values])
1033 end

Support VALUES clause instead of the SELECT clause to return rows.

supports_cte_in_compounds? ()

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1036 def supports_cte_in_compounds?
1037   false
1038 end

SQLite does not support CTEs directly inside UNION/INTERSECT/EXCEPT.

supports_filtered_aggregates? ()

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1041 def supports_filtered_aggregates?
1042   db.sqlite_version >= 33000
1043 end

SQLite 3.30 supports the FILTER clause for aggregate functions.

supports_quoted_function_names? ()

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1046 def supports_quoted_function_names?
1047   true
1048 end

SQLite supports quoted function names.

update_from_sql (sql)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1056 def update_from_sql(sql)
1057   if(from = @opts[:from][1..-1]).empty?
1058     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1059   else
1060     sql << ' FROM '
1061     source_list_append(sql, from)
1062     select_join_sql(sql)
1063   end
1064 end

Use FROM to specify additional tables in an update query

update_table_sql (sql)

Source

     # File lib/sequel/adapters/shared/sqlite.rb
1067 def update_table_sql(sql)
1068   sql << ' '
1069   source_list_append(sql, @opts[:from][0..0])
1070 end

Only include the primary table in the main update clause