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 |
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 418 418: def complex_expression_sql_append(sql, op, args) 419: case op 420: when :~, '!~''!~', '~*''~*', '!~*''!~*' 421: raise Error, "SQLite does not support pattern matching via regular expressions" 422: when :ILIKE 423: super(sql, :LIKE, args.map{|a| SQL::Function.new(:upper, a)}) 424: when "NOT LIKE""NOT LIKE", "NOT ILIKE""NOT ILIKE" 425: sql << NOT_SPACE 426: complex_expression_sql_append(sql, (op == "NOT ILIKE""NOT ILIKE" ? :ILIKE : :LIKE), args) 427: when :^ 428: sql << complex_expression_arg_pairs(args) do |a, b| 429: a = literal(a) 430: b = literal(b) 431: "((~(#{a} & #{b})) & (#{a} | #{b}))" 432: end 433: when :extract 434: part = args.at(0) 435: raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 436: sql << EXTRACT_OPEN << format << COMMA 437: literal_append(sql, args.at(1)) 438: sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE 439: else 440: super 441: end 442: 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 446 446: def constant_sql_append(sql, constant) 447: if c = CONSTANT_MAP[constant] 448: sql << c 449: else 450: super 451: end 452: 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.
# File lib/sequel/adapters/shared/sqlite.rb, line 457 457: def delete 458: @opts[:where] ? super : filter(1=>1).delete 459: end
Return an array of strings specifying a query explanation for a SELECT of the current dataset.
# File lib/sequel/adapters/shared/sqlite.rb, line 463 463: def explain 464: db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}"). 465: map{|x| "#{x[:addr]}|#{x[:opcode]}|#{(1..5).map{|i| x[:"p#{i}"]}.join('|')}|#{x[:comment]}"} 466: 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 483 483: def select(*cols) 484: 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)}) 485: super(*cols.map{|c| alias_qualified_column(c)}) 486: else 487: super 488: end 489: 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 509 509: def supports_timestamp_timezones? 510: db.use_timestamp_timezones? 511: end