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 424 424: def complex_expression_sql_append(sql, op, args) 425: case op 426: when :~, '!~''!~', '~*''~*', '!~*''!~*' 427: raise Error, "SQLite does not support pattern matching via regular expressions" 428: when :ILIKE 429: super(sql, :LIKE, args.map{|a| SQL::Function.new(:upper, a)}) 430: when "NOT LIKE""NOT LIKE", "NOT ILIKE""NOT ILIKE" 431: sql << NOT_SPACE 432: complex_expression_sql_append(sql, (op == "NOT ILIKE""NOT ILIKE" ? :ILIKE : :LIKE), args) 433: when :^ 434: sql << complex_expression_arg_pairs(args) do |a, b| 435: a = literal(a) 436: b = literal(b) 437: "((~(#{a} & #{b})) & (#{a} | #{b}))" 438: end 439: when :extract 440: part = args.at(0) 441: raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 442: sql << EXTRACT_OPEN << format << COMMA 443: literal_append(sql, args.at(1)) 444: sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE 445: else 446: super 447: end 448: 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 452 452: def constant_sql_append(sql, constant) 453: if c = CONSTANT_MAP[constant] 454: sql << c 455: else 456: super 457: end 458: 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 463 463: def delete 464: @opts[:where] ? super : filter(1=>1).delete 465: 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 469 469: def explain 470: db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}"). 471: map{|x| "#{x[:addr]}|#{x[:opcode]}|#{(1..5).map{|i| x[:"p#{i}"]}.join('|')}|#{x[:comment]}"} 472: 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 489 489: def select(*cols) 490: 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)}) 491: super(*cols.map{|c| alias_qualified_column(c)}) 492: else 493: super 494: end 495: 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 515 515: def supports_timestamp_timezones? 516: db.use_timestamp_timezones? 517: end