module Sequel::SQLite::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- INSERT_CONFLICT_RESOLUTIONS
The allowed values for
insert_conflict
Public Instance Methods
# File lib/sequel/adapters/shared/sqlite.rb 507 def cast_sql_append(sql, expr, type) 508 if type == Time or type == DateTime 509 sql << "datetime(" 510 literal_append(sql, expr) 511 sql << ')' 512 elsif type == Date 513 sql << "date(" 514 literal_append(sql, expr) 515 sql << ')' 516 else 517 super 518 end 519 end
SQLite
doesn't support a NOT LIKE b, you need to use NOT (a LIKE b). It doesn't support xor, power, or the extract function natively, so those have to be emulated.
# File lib/sequel/adapters/shared/sqlite.rb 523 def complex_expression_sql_append(sql, op, args) 524 case op 525 when :"NOT LIKE", :"NOT ILIKE" 526 sql << 'NOT ' 527 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 528 when :^ 529 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 530 when :** 531 unless (exp = args[1]).is_a?(Integer) 532 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 533 end 534 case exp 535 when 0 536 sql << '1' 537 else 538 sql << '(' 539 arg = args[0] 540 if exp < 0 541 invert = true 542 exp = exp.abs 543 sql << '(1.0 / (' 544 end 545 (exp - 1).times do 546 literal_append(sql, arg) 547 sql << " * " 548 end 549 literal_append(sql, arg) 550 sql << ')' 551 if invert 552 sql << "))" 553 end 554 end 555 when :extract 556 part = args[0] 557 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 558 sql << "CAST(strftime(" << format << ', ' 559 literal_append(sql, args[1]) 560 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 561 else 562 super 563 end 564 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 568 def constant_sql_append(sql, constant) 569 if c = CONSTANT_MAP[constant] 570 sql << c 571 else 572 super 573 end 574 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 579 def delete 580 @opts[:where] ? super : where(1=>1).delete 581 end
Return an array of strings specifying a query explanation for a SELECT of the current dataset. Currently, the options are ignored, but it accepts options to be compatible with other adapters.
# File lib/sequel/adapters/shared/sqlite.rb 586 def explain(opts=nil) 587 # Load the PrettyTable class, needed for explain output 588 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 589 590 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 591 rows = ds.all 592 Sequel::PrettyTable.string(rows, ds.columns) 593 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 596 def having(*cond) 597 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 598 super 599 end
Handle uniqueness violations when inserting, by using a specified resolution algorithm. With no options, uses INSERT OR REPLACE. SQLite
supports the following conflict resolution algoriths: ROLLBACK, ABORT, FAIL, IGNORE and REPLACE.
Examples:
DB[:table].insert_conflict.insert(a: 1, b: 2) # INSERT OR IGNORE INTO TABLE (a, b) VALUES (1, 2) DB[:table].insert_conflict(:replace).insert(a: 1, b: 2) # INSERT OR REPLACE INTO TABLE (a, b) VALUES (1, 2)
# File lib/sequel/adapters/shared/sqlite.rb 630 def insert_conflict(resolution = :ignore) 631 unless INSERT_CONFLICT_RESOLUTIONS.include?(resolution.to_s.upcase) 632 raise Error, "Invalid value passed to Dataset#insert_conflict: #{resolution.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 633 end 634 clone(:insert_conflict => resolution) 635 end
Ignore uniqueness/exclusion violations when inserting, using INSERT OR IGNORE. Exists mostly for compatibility to MySQL's insert_ignore. Example:
DB[:table].insert_ignore.insert(a: 1, b: 2) # INSERT OR IGNORE INTO TABLE (a, b) VALUES (1, 2)
# File lib/sequel/adapters/shared/sqlite.rb 642 def insert_ignore 643 insert_conflict(:ignore) 644 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 602 def quoted_identifier_append(sql, c) 603 sql << '`' << c.to_s.gsub('`', '``') << '`' 604 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 610 def select(*cols) 611 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)}) 612 super(*cols.map{|c| alias_qualified_column(c)}) 613 else 614 super 615 end 616 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 647 def supports_cte?(type=:select) 648 db.sqlite_version >= 30803 649 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 652 def supports_cte_in_subqueries? 653 supports_cte? 654 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 657 def supports_derived_column_lists? 658 false 659 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 662 def supports_intersect_except_all? 663 false 664 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 667 def supports_is_true? 668 false 669 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 672 def supports_multiple_column_in? 673 false 674 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 679 def supports_timestamp_timezones? 680 db.use_timestamp_timezones? 681 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 684 def supports_where_true? 685 false 686 end
Private Instance Methods
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 791 def _truncate_sql(table) 792 "DELETE FROM #{table}" 793 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 699 def alias_qualified_column(col) 700 case col 701 when Symbol 702 t, c, a = split_symbol(col) 703 if t && !a 704 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 705 else 706 col 707 end 708 when SQL::QualifiedIdentifier 709 SQL::AliasedExpression.new(col, col.column) 710 else 711 col 712 end 713 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 691 def as_sql_append(sql, aliaz, column_aliases=nil) 692 raise Error, "sqlite does not support derived column lists" if column_aliases 693 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 694 sql << ' AS ' 695 literal_append(sql, aliaz.to_s) 696 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 716 def default_import_slice 717 500 718 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 721 def identifier_list(columns) 722 columns.map{|i| quote_identifier(i)}.join(', ') 723 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 726 def insert_conflict_sql(sql) 727 if resolution = @opts[:insert_conflict] 728 sql << " OR " << resolution.to_s.upcase 729 end 730 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 733 def literal_blob_append(sql, v) 734 sql << "X'" << v.unpack("H*").first << "'" 735 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 738 def literal_false 739 @db.integer_booleans ? '0' : "'f'" 740 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 743 def literal_true 744 @db.integer_booleans ? '1' : "'t'" 745 end
SQLite
only supporting multiple rows in the VALUES clause starting in 3.7.11. On older versions, fallback to using a UNION.
# File lib/sequel/adapters/shared/sqlite.rb 749 def multi_insert_sql_strategy 750 db.sqlite_version >= 30711 ? :values : :union 751 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 754 def native_function_name(emulated_function) 755 if emulated_function == :char_length 756 'length' 757 else 758 super 759 end 760 end
SQLite
does not support FOR UPDATE, but silently ignore it instead of raising an error for compatibility with other databases.
# File lib/sequel/adapters/shared/sqlite.rb 765 def select_lock_sql(sql) 766 super unless @opts[:lock] == :update 767 end
# File lib/sequel/adapters/shared/sqlite.rb 769 def select_only_offset_sql(sql) 770 sql << " LIMIT -1 OFFSET " 771 literal_append(sql, @opts[:offset]) 772 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 775 def select_values_sql(sql) 776 sql << "VALUES " 777 expression_list_append(sql, opts[:values]) 778 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 781 def supports_cte_in_compounds? 782 false 783 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 786 def supports_quoted_function_names? 787 true 788 end