ANN: Sequel 2.0.0 Released

Sequel is a lightweight database access toolkit for Ruby.

* Sequel provides thread safety, connection pooling and a concise DSL
  for constructing database queries and table schemas.
* Sequel also includes a lightweight but comprehensive ORM layer for
  mapping records to Ruby objects and handling associated records.
* Sequel makes it easy to deal with multiple records without having
  to break your teeth on SQL.
* Sequel currently has adapters for ADO, DB2, DBI, Informix, JDBC,
  MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.

Sequel 2.0.0 has been released and should be available on the gem
mirrors.  2.0.0 has the following exciting new features:

Blockless Filter Expressions
----------------------------

Before 2.0.0, in order to specify complex SQL expressions, you
either had to resort to writing the SQL yourself in a string or
using an expression inside a block that was parsed by ParseTree.
Because ParseTree was required, only ruby 1.8.* was supported, and
supporting other ruby versions (ruby 1.9, JRuby, Rubinius) would
never be possible.

With 2.0.0, you no longer need to use a block to write complex SQL
expressions. The basics of the blockless filters are the usual
arithmetic, inequality, and binary operators:

  + = addition
  - = subtraction
  * = multiplication
  / = division
  > = greater than
  < = less than
  >= = greater than or equal to
  <= = less than or equal to
  ~ = negation
  & = AND
  | = OR

You can use these operators on Symbols, LiteralStrings, and other
Sequel::SQL::Expressions.  Note that there is no equal operator or
not equal operator, to specify those, you use a Hash.

Here are some examples:

  # Ruby code => SQL WHERE clause
  :active => active
  ~:active => NOT active
  ~~:active => active
  ~~~:active => NOT active
  :is_true[] => is_true()
  ~:is_true[] => NOT is_true()
  :x > 100 => (x > 100)
  :x < 100.01 => (x < 100.01)
  :x <= 0 => (x <= 0)
  :x >= 1 => (x >= 1)
  ~(:x > 100) => (x <= 100)
  {:x => 100} => (x = 100)
  {:x => 'a'} => (x = 'a')
  {:x => nil} => (x IS NULL)
  ~{:x => 100} => (x != 100)
  ~{:x => 'a'} => (x != 'a')
  ~{:x => nil} => (x IS NOT NULL)
  {:x => /a/} => (x ~ 'blah') # Default, MySQL different
  ~{:x => /a/} => (x !~ 'blah') # Default, MySQL different
  :x.like('a') =>  (x LIKE 'a')
  ~:x.like('a') =>  (x NOT LIKE 'a')
  :x.like(/a/) => (x ~ 'a') # Default, MySQL different
  ~:x.like('a', /b/) => ((x NOT LIKE 'a') AND (x !~ 'b')) # Default
  ~{:x => 1..5} => ((x < 1) OR (x > 5))
  ~{:x => DB[:items].select(:i)} => (x NOT IN (SELECT i FROM items))
  ~{:x => [1,2,3]} => (x NOT IN (1, 2, 3))
  :x + 1 > 100 => ((x + 1) > 100)
  (:x * :y) < 100.01 => ((x * y) < 100.01)
  (:x - :y/2) >= 100 => ((x - (y / 2)) >= 100)
  (((:x - :y)/(:x + :y))*:z) <= 100 => ((((x - y) / (x + y)) * z) <=
100)
  ~((((:x - :y)/(:x + :y))*:z) <= 100) => ((((x - y) / (x + y)) * z) >
100)
  :x & :y => (x AND y)
  :x & :y & :z => ((x AND y) AND z)
  :x & {:y => :z} => (x AND (y = z))
  {:y => :z} & :x => ((y = z) AND x)
  {:x => :a} & {:y => :z} => ((x = a) AND (y = z))
  (:x > 200) & (:y < 200) => ((x > 200) AND (y < 200))
  :x | :y => (x OR y)
  :x | :y | :z => ((x OR y) OR z)
  :x | {:y => :z} => (x OR (y = z))
  {:y => :z} | :x => ((y = z) OR x)
  {:x => :a} | {:y => :z} => ((x = a) OR (y = z))
  (:x > 200) | (:y < 200) => ((x > 200) OR (y < 200))
  (:x | :y) & :z => ((x OR y) AND z)
  :x | (:y & :z) => (x OR (y AND z))
  (:x & :w) | (:y & :z) => ((x AND w) OR (y AND z))
  ~((:x | :y) & :z) => ((NOT x AND NOT y) OR NOT z)
  ~((:x & :w) | (:y & :z)) => ((NOT x OR NOT w) AND (NOT y OR NOT z))
  ~((:x > 200) | (:y & :z)) => ((x <= 200) AND (NOT y OR NOT z))
  ~('x'.lit + 1 > 100) => ((x + 1) <= 100)
  'x'.lit.like(/a/) => (x ~ 'a') # (x ~ \'a\')

None of these require blocks, you can use any directly in a call to
filter:

  DB[:items].filter((:price * :tax) - :discount > 100)
  # => SELECT * FROM items WHERE (((price * tax) - discount) > 100)
  DB[:items].filter(:active & ~:archived)
  # => SELECT * FROM items WHERE (active AND NOT archived)

SQL String Concatenation
------------------------

Sequel now has support for expressing SQL string concatenation in an
easy way:

  [:name, :title].sql_string_join(" - ")
  # SQL:  name || ' - ' || title

You can use this in selecting columns, creating filters, ordering
datasets, and possibly elsewhere.

Schema Reflection Support/Typecasting on Assignment
---------------------------------------------------

When used with PostgreSQL, MySQL, or SQLite, Sequel now has the
ability to get information from the database's schema in regards
to column types:

  DB.schema(:artist)
  => [[:id, {:type=>:integer, :db_type=>"integer", :max_chars=>0
     :numeric_precision=>32, :allow_null=>false,
     :default=>"nextval('artist_id_seq'::regclass)"}], [:name,
     {:type=>:string, :default=>nil, :db_type=>"text",
     :numeric_precision=>0, :allow_null=>true, :max_chars=>0}]]

Models now use this information to typecast values on attribute
assignment. For example, if you have an integer column named number
and a text (e.g. varchar) column named title:

  1.5.1:
    model.number = '1'
    model.number # => '1'
    model.title = 1
    model.title # => 1
  2.0.0:
    model.number = '1'
    model.number # => 1
    model.title = 1
    model.title # => '1'

Typecasting can be turned off on a global, per class, and per object
basis:

  Sequel::Model.typecast_on_assignment = false # Global
  Album.typecast_on_assignment = false # Per Class
  Album.new.typecast_on_assignment = false # Per Object

Typecasting is somewhat strict, it does not allow obviously bogus
data to be used:

  model.number = 'a' # Raises error

This is in contrast to how some other ORMs handle the situation:

  model.number = 'a'
  model.number # => 0

If Sequel is being used with a web framework and you want to display
friendly error messages to the user, you should probably turn
typecasting off and set up the necessary validations in your models.

Model Association Improvements
------------------------------

Associations can now be eagerly loaded even if they have a block,
though the block should not rely on being evaluated in the context
of an instance. This allows you filter on associations when eagerly
loading:

  Artist.one_to_many :albums_with_10_tracks, :class=>:Album do |ds|
    ds.filter(:num_tracks => 10)
  end
  Artist.filter(:name.like('A%)).eager(:albums_with_10_tracks).all
  # SELECT * FROM artists WHERE (name LIKE 'A%')
  # SELECT albums.* FROM albums WHERE ((artist_id IN (...)) AND
  #   (num_tracks = 10))

Associations now have a remove_all_ method for removing all
associated objects in a single query:

  Artist.many_to_many :albums
  Artist[1].remove_all_albums
  # DELETE FROM albums_artists WHERE artist_id = 1

  Artist.one_to_many :albums
  Artist[1].remove_all_albums
  # UPDATE albums SET artist_id = NULL WHERE artist_id = 1

All associations can specify a :select option to change which columns
are selected.  Previously only many to many associations suppported
this.

The SQL used when eagerly loading through eager_graph can be
modified via the :graph_join_type, :graph_conditions, and
:graph_join_conditions options.

:graph_join_type changes the join type from the default of
:left_outer.  This can be useful if you do not want any
albums that don't have an artist in the result set:

  Album.many_to_one :artist, :graph_join_type=>:inner
  Album.eager_graph(:artist).sql
  # SELECT ... FROM albums INNER JOIN artists ...

:graph_conditions adds conditions on the join to the table you are
joining, the eager_graph equivalent of an association block argument
in eager. It takes either a hash or an array where all elements
are arrays of length two, similar to join_table, where key symbols
specify columns in the joined table and value symbols specify
columns in the last joined or primary table:

  Album.many_to_one :artist, :graph_conditions=>{:active=>true}
  Album.eager_graph(:artist).sql
  # SELECT ... FROM albums LEFT OUTER JOIN artists ON ((artists.id =
  #   albums.artist_id) AND (artists.active = 't'))

:graph_join_table_conditions exists for many to many associations only,
and operates the same as :graph_conditions, except it specifies a
condition on the many to many join table instead of the associated
model's table. This is necessary if the join table is also model
table with other columns on which you may want to filter:

  Album.many_to_many :genres, :join_table=>:ag, \
    :graph_join_table_conditions=>{:active=>true}
  Album.eager_graph(:genres).sql
  # SELECT ... FROM albums LEFT OUTER JOIN ag ON ((ag.album_id =
      albums.id) AND (ag.active = 't')) LEFT OUTER JOIN genres ON
      (genres.id = ag.genre_id)

Other Small Improvements
------------------------

* Dataset#invert returns a dataset that matches all records not
  matching the current filter.
* Dataset#unfiltered returns a dataset that has any filters removed.
* Dataset#last_page? and Dataset#first_page? for paginated datasets.
* The sequel command line tool now support an -E or --echo argument
  that logs all SQL to the standard output.  It also can take a path
  to a yaml file with database connection options, in addition to a
  database URL.
* Databases can now have multiple SQL loggers, so you can log to the
  standard output as well as a file.
* SQL identifiers (columns and tables) are now quoted by default (you
  can turn this off via Sequel.quote_identifiers = false if need be).
* Sequel.connect now takes an optional block that will disconnect the
  database when the block finishes.
* AlterTableGenerator now has add_primary_key and add_foreign_key
  methods.
* Running the specs without ParseTree installed skips the specs that
  require ParseTree.
* You can use an array of arrays instead of a hash when specifying
  conditions, which may be necessary in certain situations where
  you would be using the same hash key more than once.
* Almost all documentation for Sequel was updated for 2.0.0, so if you
  found Sequel documentation lacking before, check out the new RDoc
  pages.
* There have been many minor refactoring improvements, the code
  should now be easier to read and follow.
* Sequel now has no external dependencies.
* Sequel::Models now have before_validation and after_validation
  hooks.
* Sequel::Model hooks that return false cause the methods that call
  them (such as save) to return false.
* Sequel::Models can now load their schema on first instantiation,
  instead of when they are created, via
  Sequel::Model.lazy_load_schema=.  This is helpful for certain
  web frameworks that reload all models on every request.
* Hook methods that use blocks can now include an optional tag,
  which allows them to work well with web frameworks that load source
  files every time they are modified.

The PostgreSQL adapter has been rewritten and now supports ruby-pg.
There have also been improvements in the following adapters: DBI,
MySQL, SQLite, Oracle, and MSSQL.

All of the methods that have been deprecated in 1.5.0 have now been
removed.  If you are want to upgrade to Sequel 2.0.0 from version 1.4.0
or previous, upgrade to 1.5.1 first, fix all of the deprecation
warnings that show up, and then upgrade to 2.0.0.

There were some backwards incompatible changes made in 2.0.0 beyond the
removal of deprecated methods.  These are:

* Inflector is no longer used, the inflection methods were moved
  directly into String (where they belong because inflections only
  make sense for strings).  So to override singularization
  or pluralization rules, use String.inflections instead of
  Inflector.inflections.
* MySQL tinyints are now returned as boolean values instead of
  integers.  MySQL doesn't have a boolean type, and usually it
  is recommended to use tinyint for a boolean column.
* You can no longer pass an array to Dataset#order or Dataset#select,
  you need to pass each argument separately (the * operator is your
  friend).
* You must use '?' instead of '(?)' when interpolating an array
  argument into a string (e.g. filter('x IN ?', [1,2,3]))
* You must pass an explicit table alias argument to join_table and
  related methods, you can no longer include the table alias
  inside the table argument.
* sqlite:// URLs now operate the same as file:// URLs (2 slashes
  for a relative path, 3 for an absolute path).

If you have any questions, please post on the Google Group.

Thanks,
Jeremy

* {Source code}[http://github.com/jeremyevans/sequel]
* {Bug tracking}[http://code.google.com/p/ruby-sequel/issues/list]
* {Google group}[http://groups.google.com/group/sequel-talk]
* {RDoc}[http://sequel.rubyforge.org]
-- 
Posted via http://www.ruby-forum.com/.