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 |
DATE_OPEN | = | "date(".freeze |
DATETIME_OPEN | = | "datetime(".freeze |
# File lib/sequel/adapters/shared/sqlite.rb, line 434 434: def cast_sql_append(sql, expr, type) 435: if type == Time or type == DateTime 436: sql << DATETIME_OPEN 437: literal_append(sql, expr) 438: sql << PAREN_CLOSE 439: elsif type == Date 440: sql << DATE_OPEN 441: literal_append(sql, expr) 442: sql << PAREN_CLOSE 443: else 444: super 445: end 446: end
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 451 451: def complex_expression_sql_append(sql, op, args) 452: case op 453: when :~, '!~''!~', '~*''~*', '!~*''!~*' 454: raise Error, "SQLite does not support pattern matching via regular expressions" 455: when :ILIKE 456: super(sql, :LIKE, args.map{|a| SQL::Function.new(:upper, a)}) 457: when "NOT LIKE""NOT LIKE", "NOT ILIKE""NOT ILIKE" 458: sql << NOT_SPACE 459: complex_expression_sql_append(sql, (op == "NOT ILIKE""NOT ILIKE" ? :ILIKE : :LIKE), args) 460: when :^ 461: sql << complex_expression_arg_pairs(args) do |a, b| 462: a = literal(a) 463: b = literal(b) 464: "((~(#{a} & #{b})) & (#{a} | #{b}))" 465: end 466: when :extract 467: part = args.at(0) 468: raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 469: sql << EXTRACT_OPEN << format << COMMA 470: literal_append(sql, args.at(1)) 471: sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE 472: else 473: super 474: end 475: 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 479 479: def constant_sql_append(sql, constant) 480: if c = CONSTANT_MAP[constant] 481: sql << c 482: else 483: super 484: end 485: 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 497 497: def explain(opts=nil) 498: # Load the PrettyTable class, needed for explain output 499: Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 500: 501: ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 502: rows = ds.all 503: Sequel::PrettyTable.string(rows, ds.columns) 504: 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 521 521: def select(*cols) 522: 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)}) 523: super(*cols.map{|c| alias_qualified_column(c)}) 524: else 525: super 526: end 527: 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 547 547: def supports_timestamp_timezones? 548: db.use_timestamp_timezones? 549: end