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 |
EXRACT_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 << EXRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE 439: else 440: super 441: end 442: 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 456 456: def delete 457: @opts[:where] ? super : filter(1=>1).delete 458: 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 462 462: def explain 463: db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}"). 464: map{|x| "#{x[:addr]}|#{x[:opcode]}|#{(1..5).map{|i| x[:"p#{i}"]}.join('|')}|#{x[:comment]}"} 465: 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 482 482: def select(*cols) 483: 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)}) 484: super(*cols.map{|c| alias_qualified_column(c)}) 485: else 486: super 487: end 488: 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 508 508: def supports_timestamp_timezones? 509: db.use_timestamp_timezones? 510: end