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
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

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
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

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
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

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
670 def delete(&block)
671   @opts[:where] ? super : where(1=>1).delete(&block)
672 end

empty?() click to toggle source

Always return false when using VALUES

Calls superclass method

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

explain(opts=nil) click to toggle source

Return a string specifying a query explanation for a SELECT of the current dataset. Options:

:query_plan: Use EXPLAIN QUERY PLAN instead of EXPLAIN if true.

    # 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   keyword = (opts && opts[:query_plan]) ? "EXPLAIN QUERY PLAN" : "EXPLAIN"
688   ds = db.send(:metadata_dataset).clone(:sql=>"#{keyword} #{select_sql}".freeze)
689   rows = ds.all
690   Sequel::PrettyTable.string(rows, ds.columns)
691 end

having(*cond) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method

    # File lib/sequel/adapters/shared/sqlite.rb
694 def having(*cond)
695   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900
696   super
697 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
772 def insert_conflict(opts = :ignore)
773   case opts
774   when Symbol, String
775     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
776       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
777     end
778     clone(:insert_conflict => opts)
779   when Hash
780     clone(:insert_on_conflict => opts)
781   else
782     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
783   end
784 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
791 def insert_ignore
792   insert_conflict(:ignore)
793 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
701 def insert_select(*values)
702   return unless supports_insert_select?
703   # Handle case where query does not return a row
704   server?(:default).with_sql_first(insert_select_sql(*values)) || false
705 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
709 def insert_select_sql(*values)
710   ds = opts[:returning] ? self : returning
711   ds.insert_sql(*values)
712 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
715 def quoted_identifier_append(sql, c)
716   sql << '`' << c.to_s.gsub('`', '``') << '`'
717 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
796 def returning(*values)
797   return super if values.empty?
798   raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
799   clone(:returning=>_returning_values(values).freeze)
800 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
723 def select(*cols)
724   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)})
725     super(*cols.map{|c| alias_qualified_column(c)})
726   else
727     super
728   end
729 end

supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
803 def supports_cte?(type=:select)
804   db.sqlite_version >= 30803
805 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
808 def supports_cte_in_subqueries?
809   supports_cte?
810 end

supports_deleting_joins?() click to toggle source

SQLite does not support deleting from a joined dataset

    # File lib/sequel/adapters/shared/sqlite.rb
818 def supports_deleting_joins?
819   false
820 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
813 def supports_derived_column_lists?
814   false
815 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
823 def supports_intersect_except_all?
824   false
825 end

supports_is_true?() click to toggle source

SQLite does not support IS TRUE

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

supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

    # File lib/sequel/adapters/shared/sqlite.rb
833 def supports_modifying_joins?
834   db.sqlite_version >= 33300
835 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
838 def supports_multiple_column_in?
839   false
840 end

supports_returning?(_) click to toggle source

SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.

    # File lib/sequel/adapters/shared/sqlite.rb
843 def supports_returning?(_)
844   db.sqlite_version >= 33500
845 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
850 def supports_timestamp_timezones?
851   db.use_timestamp_timezones?
852 end

supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

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

supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
860 def supports_window_clause?
861   db.sqlite_version >= 32800
862 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
873 def supports_window_function_frame_option?(option)
874   db.sqlite_version >= 32800 ? true : super
875 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
868 def supports_window_functions?
869   db.sqlite_version >= 32600
870 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
880 def _returning_values(values)
881   values.map do |v|
882     case v
883     when Symbol
884       _, c, a = split_symbol(v)
885       a ? v : Sequel.as(v, c)
886     when SQL::Identifier, SQL::QualifiedIdentifier
887       Sequel.as(v, unqualified_column_for(v))
888     else
889       v
890     end
891   end
892 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
1052 def _truncate_sql(table)
1053   "DELETE FROM #{table}"
1054 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
895 def aggreate_dataset_use_from_self?
896   super || @opts[:values]
897 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
908 def alias_qualified_column(col)
909   case col
910   when Symbol
911     t, c, a = split_symbol(col)
912     if t && !a
913       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
914     else
915       col
916     end
917   when SQL::QualifiedIdentifier
918     SQL::AliasedExpression.new(col, col.column)
919   else
920     col
921   end
922 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
900 def as_sql_append(sql, aliaz, column_aliases=nil)
901   raise Error, "sqlite does not support derived column lists" if column_aliases
902   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
903   sql << ' AS '
904   literal_append(sql, aliaz.to_s)
905 end

check_delete_allowed!()

Alias for: check_insert_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
925 def check_insert_allowed!
926   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
927   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
928 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
932 def default_import_slice
933   500
934 end

default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

Calls superclass method

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

identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

insert_conflict_sql(sql) click to toggle source

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
947 def insert_conflict_sql(sql)
948   if resolution = @opts[:insert_conflict]
949     sql << " OR " << resolution.to_s.upcase
950   end
951 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
954 def insert_on_conflict_sql(sql)
955   if opts = @opts[:insert_on_conflict]
956     sql << " ON CONFLICT"
957 
958     if target = opts[:constraint] 
959       sql << " ON CONSTRAINT "
960       identifier_append(sql, target)
961     elsif target = opts[:target]
962       sql << ' '
963       identifier_append(sql, Array(target))
964       if conflict_where = opts[:conflict_where]
965         sql << " WHERE "
966         literal_append(sql, conflict_where)
967       end
968     end
969 
970     if values = opts[:update]
971       sql << " DO UPDATE SET "
972       update_sql_values_hash(sql, values)
973       if update_where = opts[:update_where]
974         sql << " WHERE "
975         literal_append(sql, update_where)
976       end
977     else
978       sql << " DO NOTHING"
979     end
980   end
981 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
984 def literal_blob_append(sql, v)
985   sql <<  "X'" << v.unpack("H*").first << "'"
986 end

literal_false() click to toggle source

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

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

literal_true() click to toggle source

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

    # File lib/sequel/adapters/shared/sqlite.rb
994 def literal_true
995   @db.integer_booleans ? '1' : "'t'"
996 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
1000 def multi_insert_sql_strategy
1001   db.sqlite_version >= 30711 ? :values : :union
1002 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
1005 def native_function_name(emulated_function)
1006   if emulated_function == :char_length
1007     'length'
1008   else
1009     super
1010   end
1011 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
1014 def requires_emulating_nulls_first?
1015   db.sqlite_version < 33000
1016 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
1021 def select_lock_sql(sql)
1022   super unless @opts[:lock] == :update
1023 end

select_only_offset_sql(sql) click to toggle source

     # File lib/sequel/adapters/shared/sqlite.rb
1025 def select_only_offset_sql(sql)
1026   sql << " LIMIT -1 OFFSET "
1027   literal_append(sql, @opts[:offset])
1028 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
1031 def select_values_sql(sql)
1032   sql << "VALUES "
1033   expression_list_append(sql, opts[:values])
1034 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
1037 def supports_cte_in_compounds?
1038   false
1039 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
1042 def supports_filtered_aggregates?
1043   db.sqlite_version >= 33000
1044 end

supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

     # File lib/sequel/adapters/shared/sqlite.rb
1047 def supports_quoted_function_names?
1048   true
1049 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
1057 def update_from_sql(sql)
1058   if(from = @opts[:from][1..-1]).empty?
1059     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
1060   else
1061     sql << ' FROM '
1062     source_list_append(sql, from)
1063     select_join_sql(sql)
1064   end
1065 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
1068 def update_table_sql(sql)
1069   sql << ' '
1070   source_list_append(sql, @opts[:from][0..0])
1071 end