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
533 def cast_sql_append(sql, expr, type)
534   if type == Time or type == DateTime
535     sql << "datetime("
536     literal_append(sql, expr)
537     sql << ')'
538   elsif type == Date
539     sql << "date("
540     literal_append(sql, expr)
541     sql << ')'
542   else
543     super
544   end
545 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
549 def complex_expression_sql_append(sql, op, args)
550   case op
551   when :"NOT LIKE", :"NOT ILIKE"
552     sql << 'NOT '
553     complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args)
554   when :^
555     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
556   when :**
557     unless (exp = args[1]).is_a?(Integer)
558       raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
559     end
560     case exp
561     when 0
562       sql << '1'
563     else
564       sql << '('
565       arg = args[0]
566       if exp < 0
567         invert = true
568         exp = exp.abs
569         sql << '(1.0 / ('
570       end
571       (exp - 1).times do 
572         literal_append(sql, arg)
573         sql << " * "
574       end
575       literal_append(sql, arg)
576       sql << ')'
577       if invert
578         sql << "))"
579       end
580     end
581   when :extract
582     part = args[0]
583     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
584     sql << "CAST(strftime(" << format << ', '
585     literal_append(sql, args[1])
586     sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')'
587   else
588     super
589   end
590 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
594 def constant_sql_append(sql, constant)
595   if c = CONSTANT_MAP[constant]
596     sql << c
597   else
598     super
599   end
600 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
605 def delete
606   @opts[:where] ? super : where(1=>1).delete
607 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
612 def explain(opts=nil)
613   # Load the PrettyTable class, needed for explain output
614   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
615 
616   ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
617   rows = ds.all
618   Sequel::PrettyTable.string(rows, ds.columns)
619 end
having(*cond) click to toggle source

HAVING requires GROUP BY on SQLite

Calls superclass method
    # File lib/sequel/adapters/shared/sqlite.rb
622 def having(*cond)
623   raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
624   super
625 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
685 def insert_conflict(opts = :ignore)
686   case opts
687   when Symbol, String
688     unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase)
689       raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}.  The allowed values are: :rollback, :abort, :fail, :ignore, or :replace"
690     end
691     clone(:insert_conflict => opts)
692   when Hash
693     clone(:insert_on_conflict => opts)
694   else
695     raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash"
696   end
697 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
704 def insert_ignore
705   insert_conflict(:ignore)
706 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
628 def quoted_identifier_append(sql, c)
629   sql << '`' << c.to_s.gsub('`', '``') << '`'
630 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
636 def select(*cols)
637   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)})
638     super(*cols.map{|c| alias_qualified_column(c)})
639   else
640     super
641   end
642 end
supports_cte?(type=:select) click to toggle source

SQLite 3.8.3+ supports common table expressions.

    # File lib/sequel/adapters/shared/sqlite.rb
709 def supports_cte?(type=:select)
710   db.sqlite_version >= 30803
711 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
714 def supports_cte_in_subqueries?
715   supports_cte?
716 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
719 def supports_derived_column_lists?
720   false
721 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
724 def supports_intersect_except_all?
725   false
726 end
supports_is_true?() click to toggle source

SQLite does not support IS TRUE

    # File lib/sequel/adapters/shared/sqlite.rb
729 def supports_is_true?
730   false
731 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
734 def supports_multiple_column_in?
735   false
736 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
741 def supports_timestamp_timezones?
742   db.use_timestamp_timezones?
743 end
supports_where_true?() click to toggle source

SQLite cannot use WHERE 't'.

    # File lib/sequel/adapters/shared/sqlite.rb
746 def supports_where_true?
747   false
748 end
supports_window_clause?() click to toggle source

SQLite 3.28+ supports the WINDOW clause.

    # File lib/sequel/adapters/shared/sqlite.rb
751 def supports_window_clause?
752   db.sqlite_version >= 32800
753 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
764 def supports_window_function_frame_option?(option)
765   db.sqlite_version >= 32800 ? true : super
766 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
759 def supports_window_functions?
760   db.sqlite_version >= 32600
761 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
906 def _truncate_sql(table)
907   "DELETE FROM #{table}"
908 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
779 def alias_qualified_column(col)
780   case col
781   when Symbol
782     t, c, a = split_symbol(col)
783     if t && !a
784       alias_qualified_column(SQL::QualifiedIdentifier.new(t, c))
785     else
786       col
787     end
788   when SQL::QualifiedIdentifier
789     SQL::AliasedExpression.new(col, col.column)
790   else
791     col
792   end
793 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
771 def as_sql_append(sql, aliaz, column_aliases=nil)
772   raise Error, "sqlite does not support derived column lists" if column_aliases
773   aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier)
774   sql << ' AS '
775   literal_append(sql, aliaz.to_s)
776 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
796 def default_import_slice
797   500
798 end
identifier_list(columns) click to toggle source

SQL fragment specifying a list of identifiers

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

Add OR clauses to SQLite INSERT statements

    # File lib/sequel/adapters/shared/sqlite.rb
806 def insert_conflict_sql(sql)
807   if resolution = @opts[:insert_conflict]
808     sql << " OR " << resolution.to_s.upcase
809   end
810 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
813 def insert_on_conflict_sql(sql)
814   if opts = @opts[:insert_on_conflict]
815     sql << " ON CONFLICT"
816 
817     if target = opts[:constraint] 
818       sql << " ON CONSTRAINT "
819       identifier_append(sql, target)
820     elsif target = opts[:target]
821       sql << ' '
822       identifier_append(sql, Array(target))
823       if conflict_where = opts[:conflict_where]
824         sql << " WHERE "
825         literal_append(sql, conflict_where)
826       end
827     end
828 
829     if values = opts[:update]
830       sql << " DO UPDATE SET "
831       update_sql_values_hash(sql, values)
832       if update_where = opts[:update_where]
833         sql << " WHERE "
834         literal_append(sql, update_where)
835       end
836     else
837       sql << " DO NOTHING"
838     end
839   end
840 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
843 def literal_blob_append(sql, v)
844   sql <<  "X'" << v.unpack("H*").first << "'"
845 end
literal_false() click to toggle source

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

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

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

    # File lib/sequel/adapters/shared/sqlite.rb
853 def literal_true
854   @db.integer_booleans ? '1' : "'t'"
855 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
859 def multi_insert_sql_strategy
860   db.sqlite_version >= 30711 ? :values : :union
861 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
864 def native_function_name(emulated_function)
865   if emulated_function == :char_length
866     'length'
867   else
868     super
869   end
870 end
requires_emulating_nulls_first?() click to toggle source

SQLite does not natively support NULLS FIRST/LAST.

    # File lib/sequel/adapters/shared/sqlite.rb
873 def requires_emulating_nulls_first?
874   true
875 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
880 def select_lock_sql(sql)
881   super unless @opts[:lock] == :update
882 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/sqlite.rb
884 def select_only_offset_sql(sql)
885   sql << " LIMIT -1 OFFSET "
886   literal_append(sql, @opts[:offset])
887 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
890 def select_values_sql(sql)
891   sql << "VALUES "
892   expression_list_append(sql, opts[:values])
893 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
896 def supports_cte_in_compounds?
897   false
898 end
supports_quoted_function_names?() click to toggle source

SQLite supports quoted function names.

    # File lib/sequel/adapters/shared/sqlite.rb
901 def supports_quoted_function_names?
902   true
903 end