Module Sequel::SQLite::DatasetMethods
In: lib/sequel/adapters/shared/sqlite.rb

Instance methods for datasets that connect to an SQLite database

Methods

Included Modules

Dataset::Replace

Constants

CONSTANT_MAP = {:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}
EMULATED_FUNCTION_MAP = {:char_length=>'length'.freeze}
EXTRACT_MAP = {:year=>"'%Y'", :month=>"'%m'", :day=>"'%d'", :hour=>"'%H'", :minute=>"'%M'", :second=>"'%f'"}
NOT_SPACE = Dataset::NOT_SPACE
COMMA = Dataset::COMMA
PAREN_CLOSE = Dataset::PAREN_CLOSE
AS = Dataset::AS
APOS = Dataset::APOS
EXTRACT_OPEN = "CAST(strftime(".freeze
EXTRACT_CLOSE = ') AS '.freeze
NUMERIC = 'NUMERIC'.freeze
INTEGER = 'INTEGER'.freeze
BACKTICK = '`'.freeze
BACKTICK_RE = /`/.freeze
DOUBLE_BACKTICK = '``'.freeze
BLOB_START = "X'".freeze
HSTAR = "H*".freeze
DATE_OPEN = "date(".freeze
DATETIME_OPEN = "datetime(".freeze
ONLY_OFFSET = " LIMIT -1 OFFSET ".freeze
OR = " OR ".freeze
SELECT_VALUES = "VALUES ".freeze

Public Instance methods

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 543
543:       def cast_sql_append(sql, expr, type)
544:         if type == Time or type == DateTime
545:           sql << DATETIME_OPEN
546:           literal_append(sql, expr)
547:           sql << PAREN_CLOSE
548:         elsif type == Date
549:           sql << DATE_OPEN
550:           literal_append(sql, expr)
551:           sql << PAREN_CLOSE
552:         else
553:           super
554:         end
555:       end

SQLite doesn‘t support a NOT LIKE b, you need to use NOT (a LIKE b). It doesn‘t support xor or the extract function natively, so those have to be emulated.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 559
559:       def complex_expression_sql_append(sql, op, args)
560:         case op
561:         when "NOT LIKE""NOT LIKE", "NOT ILIKE""NOT ILIKE"
562:           sql << NOT_SPACE
563:           complex_expression_sql_append(sql, (op == "NOT ILIKE""NOT ILIKE" ? :ILIKE : :LIKE), args)
564:         when :^
565:           complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)}
566:         when :**
567:           unless (exp = args[1]).is_a?(Integer)
568:             raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}")
569:           end
570:           case exp
571:           when 0
572:             sql << '1'
573:           else
574:             sql << '('
575:             arg = args.at(0)
576:             if exp < 0
577:               invert = true
578:               exp = exp.abs
579:               sql << '(1.0 / ('
580:             end
581:             (exp - 1).times do 
582:               literal_append(sql, arg)
583:               sql << " * "
584:             end
585:             literal_append(sql, arg)
586:             sql << PAREN_CLOSE
587:             if invert
588:               sql << "))"
589:             end
590:           end
591:         when :extract
592:           part = args.at(0)
593:           raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
594:           sql << EXTRACT_OPEN << format << COMMA
595:           literal_append(sql, args.at(1))
596:           sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE
597:         else
598:           super
599:         end
600:       end

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

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 604
604:       def constant_sql_append(sql, constant)
605:         if c = CONSTANT_MAP[constant]
606:           sql << c
607:         else
608:           super
609:         end
610:       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.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 615
615:       def delete
616:         @opts[:where] ? super : where(1=>1).delete
617:       end

Return an array of strings specifying a query explanation for a SELECT of the current dataset. Currently, the options are ignore, but it accepts options to be compatible with other adapters.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 622
622:       def explain(opts=nil)
623:         # Load the PrettyTable class, needed for explain output
624:         Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
625: 
626:         ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}")
627:         rows = ds.all
628:         Sequel::PrettyTable.string(rows, ds.columns)
629:       end

HAVING requires GROUP BY on SQLite

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 632
632:       def having(*cond)
633:         raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
634:         super
635:       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.

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)

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 666
666:       def insert_conflict(resolution = :ignore)
667:         clone(:insert_conflict => resolution)
668:       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)

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 675
675:       def insert_ignore
676:         insert_conflict(:ignore)
677:       end

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

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 638
638:       def quoted_identifier_append(sql, c)
639:         sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK
640:       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.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 646
646:       def select(*cols)
647:         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)})
648:           super(*cols.map{|c| alias_qualified_column(c)})
649:         else
650:           super
651:         end
652:       end

SQLite 3.8.3+ supports common table expressions.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 680
680:       def supports_cte?(type=:select)
681:         db.sqlite_version >= 30803
682:       end

SQLite does not support table aliases with column aliases

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 685
685:       def supports_derived_column_lists?
686:         false
687:       end

SQLite does not support INTERSECT ALL or EXCEPT ALL

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 690
690:       def supports_intersect_except_all?
691:         false
692:       end

SQLite does not support IS TRUE

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 695
695:       def supports_is_true?
696:         false
697:       end

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

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 700
700:       def supports_multiple_column_in?
701:         false
702:       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.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 707
707:       def supports_timestamp_timezones?
708:         db.use_timestamp_timezones?
709:       end

SQLite cannot use WHERE ‘t’.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 712
712:       def supports_where_true?
713:         false
714:       end

[Validate]