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} |
EMULATED_FUNCTION_MAP | = | {:char_length=>'length'.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 501 501: def cast_sql_append(sql, expr, type) 502: if type == Time or type == DateTime 503: sql << DATETIME_OPEN 504: literal_append(sql, expr) 505: sql << PAREN_CLOSE 506: elsif type == Date 507: sql << DATE_OPEN 508: literal_append(sql, expr) 509: sql << PAREN_CLOSE 510: else 511: super 512: end 513: end
SQLite doesn‘t support a NOT LIKE b, you need to use NOT (a LIKE b). It doesn‘t support xor or the extract function natively, so those have to be emulated.
# File lib/sequel/adapters/shared/sqlite.rb, line 517 517: def complex_expression_sql_append(sql, op, args) 518: case op 519: when "NOT LIKE""NOT LIKE", "NOT ILIKE""NOT ILIKE" 520: sql << NOT_SPACE 521: complex_expression_sql_append(sql, (op == "NOT ILIKE""NOT ILIKE" ? :ILIKE : :LIKE), args) 522: when :^ 523: sql << complex_expression_arg_pairs(args) do |a, b| 524: a = literal(a) 525: b = literal(b) 526: "((~(#{a} & #{b})) & (#{a} | #{b}))" 527: end 528: when :extract 529: part = args.at(0) 530: raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 531: sql << EXTRACT_OPEN << format << COMMA 532: literal_append(sql, args.at(1)) 533: sql << EXTRACT_CLOSE << (part == :second ? NUMERIC : INTEGER) << PAREN_CLOSE 534: else 535: super 536: end 537: 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 541 541: def constant_sql_append(sql, constant) 542: if c = CONSTANT_MAP[constant] 543: sql << c 544: else 545: super 546: end 547: 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 559 559: def explain(opts=nil) 560: # Load the PrettyTable class, needed for explain output 561: Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 562: 563: ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 564: rows = ds.all 565: Sequel::PrettyTable.string(rows, ds.columns) 566: 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 583 583: def select(*cols) 584: 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)}) 585: super(*cols.map{|c| alias_qualified_column(c)}) 586: else 587: super 588: end 589: 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 609 609: def supports_timestamp_timezones? 610: db.use_timestamp_timezones? 611: end