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

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
655 def having(*cond)
656   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
657   super
658 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
718 def insert_conflict(opts = :ignore)
719   case opts
720   when Symbol, String
721     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
722       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
723     end
724     clone(:insert_conflict => opts)
725   when Hash
726     clone(:insert_on_conflict => opts)
727   else
728     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
729   end
730 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
737 def insert_ignore
738   insert_conflict(:ignore)
739 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
661 def quoted_identifier_append(sql, c)
662   sql << '`' << c.to_s.gsub('`', '``') << '`'
663 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
669 def select(*cols)
670   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)})
671     super(*cols.map{|c| alias_qualified_column(c)})
672   else
673     super
674   end
675 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
742 def supports_cte?(type=:select)
743   db.sqlite_version >= 30803
744 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
747 def supports_cte_in_subqueries?
748   supports_cte?
749 end
supports_deleting_joins?() click to toggle source

SQLite does not support deleting from a joined dataset

    # File lib/sequel/adapters/shared/sqlite.rb
757 def supports_deleting_joins?
758   false
759 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
752 def supports_derived_column_lists?
753   false
754 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
762 def supports_intersect_except_all?
763   false
764 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
767 def supports_is_true?
768   false
769 end
supports_modifying_joins?() click to toggle source

SQLite 3.33.0 supports modifying joined datasets

    # File lib/sequel/adapters/shared/sqlite.rb
772 def supports_modifying_joins?
773   db.sqlite_version >= 33300
774 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
777 def supports_multiple_column_in?
778   false
779 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
784 def supports_timestamp_timezones?
785   db.use_timestamp_timezones?
786 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
789 def supports_where_true?
790   false
791 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
794 def supports_window_clause?
795   db.sqlite_version >= 32800
796 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
807 def supports_window_function_frame_option?(option)
808   db.sqlite_version >= 32800 ? true : super
809 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
802 def supports_window_functions?
803   db.sqlite_version >= 32600
804 end

Private Instance Methods

_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
961 def _truncate_sql(table)
962   "DELETE FROM #{table}"
963 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
822 def alias_qualified_column(col)
823   case col
824   when Symbol
825     t, c, a = split_symbol(col)
826     if t && !a
827       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
828     else
829       col
830     end
831   when SQL::QualifiedIdentifier
832     SQL::AliasedExpression.new(col, col.column)
833   else
834     col
835   end
836 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
814 def as_sql_append(sql, aliaz, column_aliases=nil)
815   raise Error, "sqlite does not support derived column lists" if column_aliases
816   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
817   sql << ' AS '
818   literal_append(sql, aliaz.to_s)
819 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
839 def check_insert_allowed!
840   raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group]
841   raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset?
842 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
846 def default_import_slice
847   500
848 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
856 def insert_conflict_sql(sql)
857   if resolution = @opts[:insert_conflict]
858     sql << " OR " << resolution.to_s.upcase
859   end
860 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
863 def insert_on_conflict_sql(sql)
864   if opts = @opts[:insert_on_conflict]
865     sql << " ON CONFLICT"
866 
867     if target = opts[:constraint] 
868       sql << " ON CONSTRAINT "
869       identifier_append(sql, target)
870     elsif target = opts[:target]
871       sql << ' '
872       identifier_append(sql, Array(target))
873       if conflict_where = opts[:conflict_where]
874         sql << " WHERE "
875         literal_append(sql, conflict_where)
876       end
877     end
878 
879     if values = opts[:update]
880       sql << " DO UPDATE SET "
881       update_sql_values_hash(sql, values)
882       if update_where = opts[:update_where]
883         sql << " WHERE "
884         literal_append(sql, update_where)
885       end
886     else
887       sql << " DO NOTHING"
888     end
889   end
890 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
893 def literal_blob_append(sql, v)
894   sql <<  "X'" << v.unpack("H*").first << "'"
895 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
903 def literal_true
904   @db.integer_booleans ? '1' : "'t'"
905 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
909 def multi_insert_sql_strategy
910   db.sqlite_version >= 30711 ? :values : :union
911 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
914 def native_function_name(emulated_function)
915   if emulated_function == :char_length
916     'length'
917   else
918     super
919   end
920 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
923 def requires_emulating_nulls_first?
924   db.sqlite_version < 33000
925 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
930 def select_lock_sql(sql)
931   super unless @opts[:lock] == :update
932 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
934 def select_only_offset_sql(sql)
935   sql << " LIMIT -1 OFFSET "
936   literal_append(sql, @opts[:offset])
937 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
940 def select_values_sql(sql)
941   sql << "VALUES "
942   expression_list_append(sql, opts[:values])
943 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
946 def supports_cte_in_compounds?
947   false
948 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
951 def supports_filtered_aggregates?
952   db.sqlite_version >= 33000
953 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

    # File lib/sequel/adapters/shared/sqlite.rb
956 def supports_quoted_function_names?
957   true
958 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
966 def update_from_sql(sql)
967   if(from = @opts[:from][1..-1]).empty?
968     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
969   else
970     sql << ' FROM '
971     source_list_append(sql, from)
972     select_join_sql(sql)
973   end
974 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
977 def update_table_sql(sql)
978   sql << ' '
979   source_list_append(sql, @opts[:from][0..0])
980 end