Module | Sequel::SQLite::DatasetMethods |
In: |
lib/sequel/adapters/shared/sqlite.rb
|
Instance methods for datasets that connect to an SQLite database
SELECT_CLAUSE_METHODS | = | Dataset.clause_methods(:select, %w'select distinct columns from join where group having compounds order limit') |
CONSTANT_MAP | = | {:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".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 |
SQLite does not support pattern matching via regular expressions. SQLite is case insensitive (depending on pragma), so use LIKE for ILIKE.
# File lib/sequel/adapters/shared/sqlite.rb, line 435 435: def complex_expression_sql_append(sql, op, args) 436: case op 437: when :~, '!~''!~', '~*''~*', '!~*''!~*' 438: raise Error, "SQLite does not support pattern matching via regular expressions" 439: when :ILIKE 440: super(sql, :LIKE, args.map{|a| SQL::Function.new(:upper, a)}) 441: when "NOT LIKE""NOT LIKE", "NOT ILIKE""NOT ILIKE" 442: sql << NOT_SPACE 443: complex_expression_sql_append(sql, (op == "NOT ILIKE""NOT ILIKE" ? :ILIKE : :LIKE), args) 444: when :^ 445: sql << complex_expression_arg_pairs(args) do |a, b| 446: a = literal(a) 447: b = literal(b) 448: "((~(#{a} & #{b})) & (#{a} | #{b}))" 449: end 450: when :extract 451: part = args.at(0) 452: raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 453: sql << EXTRACT_OPEN << format << COMMA 454: literal_append(sql, args.at(1)) 455: sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE 456: else 457: super 458: end 459: end
SQLite has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.
# File lib/sequel/adapters/shared/sqlite.rb, line 463 463: def constant_sql_append(sql, constant) 464: if c = CONSTANT_MAP[constant] 465: sql << c 466: else 467: super 468: end 469: 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.
# File lib/sequel/adapters/shared/sqlite.rb, line 481 481: def explain(opts=nil) 482: # Load the PrettyTable class, needed for explain output 483: Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 484: 485: ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 486: rows = ds.all 487: Sequel::PrettyTable.string(rows, ds.columns) 488: 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.
# File lib/sequel/adapters/shared/sqlite.rb, line 505 505: def select(*cols) 506: 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)}) 507: super(*cols.map{|c| alias_qualified_column(c)}) 508: else 509: super 510: end 511: 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.
# File lib/sequel/adapters/shared/sqlite.rb, line 531 531: def supports_timestamp_timezones? 532: db.use_timestamp_timezones? 533: end