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_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
757 def supports_intersect_except_all?
758   false
759 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
762 def supports_is_true?
763   false
764 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
767 def supports_multiple_column_in?
768   false
769 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
774 def supports_timestamp_timezones?
775   db.use_timestamp_timezones?
776 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
779 def supports_where_true?
780   false
781 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
784 def supports_window_clause?
785   db.sqlite_version >= 32800
786 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
797 def supports_window_function_frame_option?(option)
798   db.sqlite_version >= 32800 ? true : super
799 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
792 def supports_window_functions?
793   db.sqlite_version >= 32600
794 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
944 def _truncate_sql(table)
945   "DELETE FROM #{table}"
946 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
812 def alias_qualified_column(col)
813   case col
814   when Symbol
815     t, c, a = split_symbol(col)
816     if t && !a
817       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
818     else
819       col
820     end
821   when SQL::QualifiedIdentifier
822     SQL::AliasedExpression.new(col, col.column)
823   else
824     col
825   end
826 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
804 def as_sql_append(sql, aliaz, column_aliases=nil)
805   raise Error, "sqlite does not support derived column lists" if column_aliases
806   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
807   sql << ' AS '
808   literal_append(sql, aliaz.to_s)
809 end
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
829 def default_import_slice
830   500
831 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
839 def insert_conflict_sql(sql)
840   if resolution = @opts[:insert_conflict]
841     sql << " OR " << resolution.to_s.upcase
842   end
843 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
846 def insert_on_conflict_sql(sql)
847   if opts = @opts[:insert_on_conflict]
848     sql << " ON CONFLICT"
849 
850     if target = opts[:constraint] 
851       sql << " ON CONSTRAINT "
852       identifier_append(sql, target)
853     elsif target = opts[:target]
854       sql << ' '
855       identifier_append(sql, Array(target))
856       if conflict_where = opts[:conflict_where]
857         sql << " WHERE "
858         literal_append(sql, conflict_where)
859       end
860     end
861 
862     if values = opts[:update]
863       sql << " DO UPDATE SET "
864       update_sql_values_hash(sql, values)
865       if update_where = opts[:update_where]
866         sql << " WHERE "
867         literal_append(sql, update_where)
868       end
869     else
870       sql << " DO NOTHING"
871     end
872   end
873 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
876 def literal_blob_append(sql, v)
877   sql <<  "X'" << v.unpack("H*").first << "'"
878 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
886 def literal_true
887   @db.integer_booleans ? '1' : "'t'"
888 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
892 def multi_insert_sql_strategy
893   db.sqlite_version >= 30711 ? :values : :union
894 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
897 def native_function_name(emulated_function)
898   if emulated_function == :char_length
899     'length'
900   else
901     super
902   end
903 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
906 def requires_emulating_nulls_first?
907   db.sqlite_version < 33000
908 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
913 def select_lock_sql(sql)
914   super unless @opts[:lock] == :update
915 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
917 def select_only_offset_sql(sql)
918   sql << " LIMIT -1 OFFSET "
919   literal_append(sql, @opts[:offset])
920 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
923 def select_values_sql(sql)
924   sql << "VALUES "
925   expression_list_append(sql, opts[:values])
926 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
929 def supports_cte_in_compounds?
930   false
931 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
934 def supports_filtered_aggregates?
935   db.sqlite_version >= 33000
936 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

    # File lib/sequel/adapters/shared/sqlite.rb
939 def supports_quoted_function_names?
940   true
941 end