Module Sequel::SQLite::DatasetMethods
In: lib/sequel/adapters/shared/sqlite.rb

Instance methods for datasets that connect to an SQLite database

Methods

Constants

SELECT_CLAUSE_METHODS = Dataset.clause_methods(:select, %w'distinct columns from join where group having compounds order limit')
COMMA_SEPARATOR = ', '.freeze
CONSTANT_MAP = {:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}

Public Instance methods

Ugly hack. Really, SQLite uses 0 for false and 1 for true but then you can‘t differentiate between integers and booleans. In filters, SQL::BooleanConstants are used more, while in other places the ruby true/false values are used more, so use 1/0 for SQL::BooleanConstants. The correct fix for this would require separate literalization paths for filters compared to other values, but that‘s more work than I want to do right now.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 349
349:       def boolean_constant_sql(constant)
350:         case constant
351:         when true
352:           '1'
353:         when false
354:           '0'
355:         else
356:           super
357:         end
358:       end

SQLite does not support pattern matching via regular expressions. SQLite is case insensitive (depending on pragma), so use LIKE for ILIKE.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 363
363:       def complex_expression_sql(op, args)
364:         case op
365:         when :~, '!~''!~', '~*''~*', '!~*''!~*'
366:           raise Error, "SQLite does not support pattern matching via regular expressions"
367:         when :LIKE, 'NOT LIKE''NOT LIKE', :ILIKE, 'NOT ILIKE''NOT ILIKE'
368:           # SQLite is case insensitive for ASCII, and non case sensitive for other character sets
369:           "#{'NOT ' if [:'NOT LIKE', :'NOT ILIKE'].include?(op)}(#{literal(args.at(0))} LIKE #{literal(args.at(1))})"
370:         else
371:           super(op, args)
372:         end
373:       end

MSSQL doesn‘t support the SQL standard CURRENT_DATE or CURRENT_TIME

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 376
376:       def constant_sql(constant)
377:         CONSTANT_MAP[constant] || super
378:       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.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 383
383:       def delete
384:         @opts[:where] ? super : filter(1=>1).delete
385:       end

Return an array of strings specifying a query explanation for a SELECT of the current dataset.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 389
389:       def explain
390:         db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}").
391:           map{|x| "#{x[:addr]}|#{x[:opcode]}|#{(1..5).map{|i| x[:"p#{i}"]}.join('|')}|#{x[:comment]}"}
392:       end

HAVING requires GROUP BY on SQLite

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 395
395:       def having(*cond)
396:         raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group]
397:         super
398:       end

SQLite uses the nonstandard ` (backtick) for quoting identifiers.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 401
401:       def quoted_identifier(c)
402:         "`#{c}`"
403:       end

SQLite does not support INTERSECT ALL or EXCEPT ALL

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 406
406:       def supports_intersect_except_all?
407:         false
408:       end

SQLite does not support IS TRUE

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 411
411:       def supports_is_true?
412:         false
413:       end

SQLite does not support multiple columns for the IN/NOT IN operators

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 416
416:       def supports_multiple_column_in?
417:         false
418:       end

SQLite supports timezones in literal timestamps, since it stores them as text.

[Source]

     # File lib/sequel/adapters/shared/sqlite.rb, line 422
422:       def supports_timestamp_timezones?
423:         true
424:       end

[Validate]