Switch branches/tags
Commits on Jun 12, 2008
  1. Refactor default SQL generator to generate HAVING clause before

    Joshua Vickery
    Joshua Vickery committed Jun 12, 2008
    ORDER BY clause in accordance with the SQL-92 spec.
    Remove now unecessary code from mysql.rb adapter.
Commits on Jun 11, 2008
  1. Refactor SQL::Expression subclasses so that methods that don't make s…

    jeremyevans committed Jun 11, 2008
    …ense aren't defined
    This refactor fixes some issues, like being able to do:
    which makes no sense in SQL.
    It adds a few new features.  One is a qualify method for symbols
    that can add a table/schema qualifier for a column/table name:
      :price.qualify(:items) # items.price
    Another are cast_numeric and cast_string, which will cast with
    default types (integer and text) and convert the resulting value
    to a NumericExpression or StringExpression, so operators that
    don't make sense aren't defined (like &).  Example:
      :date.extract(:hour).cast_string + ':00'
      # CAST(extract(hour FROM date) AS text) || ':00'
    Also, cast is now the preferred name for the method, instead of
    cast_as (which is now an alias to cast).
    The ColumnMethods module and ColumnExpr class are going away,
    because they combine wildly different semantics with similar
    syntax. They are being replaced with separate modules (AliasMethods,
    OrderMethods, CastMethods) and classes (OrderedExpression,
    There should be very minimal fallout from this:
    1) Inverting the order of a dataset will get you ASC if you had DESC,
    instead of leaving ASC off.
    2) Plain Strings no longer have asc and desc methods, since ordering
    by a plain string is a noop.  LiteralStrings still have asc and desc
    3) You can no longer abuse the SQL::Function DSL (Symbol#[]) to
    use a table alias with specified columns.  I have no idea if anyone
    used this, but it no longer works.  I doubt anyone needs it, so there
    isn't a good replacement, other than LiteralString.
    The new hierarchy looks like this:
Commits on Jun 10, 2008
  1. Add support for SQL CASE statement via Array#case and Hash#case

    jeremyevans committed Jun 10, 2008
    Some examples:
      {(:price * 10 > :max)=>:price - 10}.case(:price)
      # CASE WHEN price * 10 > max THEN price - 10 ELSE price END
      # sum(CASE WHEN type = 1 THEN 1 ELSE 0 END)
    If you are specifying multiple conditions, use an array to get
    consistent ordering:
      [[:a, :b], [:c, :d]].case(:e)
    Using a hash with multiple conditions does work, but since the
    order is arbitrary, your conditions should be orthogonal if you
    desire deterministic behavior.
  2. Support the SQL EXTRACT function: :date.extract(:year)

    jeremyevans committed Jun 10, 2008
    Stop abusing ColumnExpr to support the SQL CAST function. Just
    because the SQL syntax is the same doesn't mention you should
    use invalid sematics. This comes at a slight cost to backwards
    compatibility according to the specs:
    This is actually a good thing, as the before example is an abuse of
    the SQL::Function class, when the result is not a function call but a
    type specifier.
    Add a SQL::IrregularFunction class to handle EXTRACT and CAST.
    Add ComplexExpressionMethods#extract, for easily extracting parts
    (e.g. month, minute) from dates/times.  Since these parts should
    always be numeric, return the result as a NumericExpression.
Commits on Jun 9, 2008
  1. Delete some file-level RDoc comments, since RDoc stupidly overwrites …

    jeremyevans committed Jun 9, 2008
    …the Sequel module RDoc with them
Commits on Jun 7, 2008
  1. Add :decimal fields to the schema parser

    jeremyevans committed Jun 7, 2008
    Convert numeric fields to BigDecimal in PostgreSQL adapter
Commits on Jun 6, 2008
  1. The expr argument in join table now allows the same argument as filte…

    jeremyevans committed Jun 6, 2008
    …r, so it can take a string or a blockless filter expression
    The following use of join_table now means something different:
      DB[:artists].join_table(:inner, :albums, :artist_id)
        => "INNER JOIN albums ON albums.artist_id ="
        => "INNER JOIN albums ON artist_id"
    Obviously, that is a loss of functionality for that case, but this
    commit allows the following cases:
      DB[:artists].join_table(:inner, :albums, "artist_id =")
      DB[:artists].join_table(:inner, :albums, :number_sold > 1000000)
    That's right, you can now use blockless filters, strings, or anything
    accepted by filter.
    To fix your code that used the previous implementation:
        DB[:artists].join_table(:inner, :albums, :artist_id)
        DB[:artists].join_table(:inner, :albums, :artist_id=>:id)
    Coming soon will be the ability for join_table to take a block that
    gives the table alias arguments, so you can create blockless filters
    that work for arbitrary joins without knowing in advance what table
    aliases will be used.
  2. pvs: removing support for proc filters in join expressions (since Jer…

    Peter Sumskas
    Peter Sumskas committed Jun 6, 2008
    …emy is considering deprecating them).
         added support for using an SQL string as the join expression.
Commits on Jun 5, 2008
  1. Add a :one_to_one option to one_to_many associations, which creates a…

    jeremyevans committed Jun 5, 2008
    … getter and setter similar to many_to_one (a.k.a. has_one)
    The usual use of this is to split one table into multiple tables,
    generally either for security or performance reasons.
    The getter returns a singular matching record, or raises an error if
    multiple records match.
    The setter updates the record given and removes associations with all
    other records.
    The usual many_to_one association methods are either removed if they
    are not needed (remove_$, remove_all_$) or made private if they are
    ($, $_dataset, $_helper, add_$).
    Assume that each song has only one lyric and each lyric has only one
    song, but the table is split because you don't use the lyrics often
    and it performs better to have them in a separate table:
      Song.one_to_many :lyrics, :one_to_one=>true # Note the plural
      song = => 1)
      song.lyric # <Lyric...>, or nil if no association lyric
      lyric = => 2)
      song.lyric = lyric # UPDATE lyrics SET song_id = 1 WHERE id = 2;
                         # UPDATE lyrics SET song_id = NULL WHERE id != 2
                         #   AND song_id = 1;
    Also, this changes the code so that add_ and remove_ for one_to_many
    associations call save instead of save! on the object, and raise an
    error if the object couldn't be saved.  This was the intent when the
    code was originally written.  save! skips validation, it doesn't
    raise an error instead of returning false.
  2. Raise an error if the :if validation option is not a Symbol, Proc, or…

    jeremyevans committed Jun 5, 2008
    … nil
    Add specs for the :if=>proc{...} case
  3. bugfix: specifying an alias for a join on a dataset did not work. Add…

    Peter Sumskas authored and jeremyevans committed Jun 5, 2008
    …ed spec and fixed code.
  4. bugfix: specifying an alias for a join on a dataset did not work. Add…

    Peter Sumskas
    Peter Sumskas committed Jun 5, 2008
    …ed spec and fixed code.
Commits on Jun 4, 2008
  1. Bump version to 2.0.1

    jeremyevans committed Jun 4, 2008
  2. Have the PostgreSQL and MySQL adapters use the Sequel.time_class sett…

    jeremyevans committed Jun 4, 2008
    …ing for datetime/timestamp types
    Add a time type to the schema parser, that is used for database time
    types (which have time but not date).  There isn't really a ruby
    equivalent for this, as the time class holds the date as well, but
    continue to use Time when returning and typecasting since it has
    been used historical and there isn't a better class in ruby's
    standard library.
    Have PostgreSQL money type use BigDecimal instead of Float.
    Support the PostgreSQL time with time zone type.
  3. Make the choice of Time or DateTime optional for typecasting :datetim…

    jeremyevans committed Jun 4, 2008
    …e types, default to Time
    Add Sequel.time_class(=)? methods for getting and setting the time
    class used.
    Add String#to_sequel_time, which uses Sequel.time_class to convert
    the string to the correct time class.
    Refactor Database#typecast_value to use the correct time class.
    The database adapters need to be converted to call :to_sequel_time
    instead of :to_time, but as Time is now the default time class for
    :datetime types, there shouldn't be any breakage unless the user
    sets Sequel.time_class = DateTime.
  4. Make identifier quoting uppercase by default, to work better with the…

    jeremyevans committed Jun 4, 2008
    … SQL standard, override in PostgreSQL (Fixes #232)
    This should fix the Oracle adapter so it doesn't require
    quote_identifiers = false, as well as other adapters that default
    unquoted identifiers to uppercase (which is the SQL standard).
Commits on Jun 3, 2008
  1. Add StringExpression#+, for simple SQL string concatenation

    jeremyevans committed Jun 3, 2008
    Most simply used like this:
      :x.sql_string + :y # SQL: x || y
    This makes SQL string concatenation possible without
    Array#sql_string_join.  Unfortunately, the only way to get a
    StringExpression is via Array#sql_string_join or
    ComplexExpressionMethods#sql_string.  However, if you want
    + to do string concatenation instead of addition by default
    for Symbols, include StringConcatenationMethods in Symbol.
  2. Add support for SQL::StringMethods#ilike, for case insensitive patter…

    jeremyevans committed Jun 3, 2008
    …n matches
    ILIKE is a PostgreSQL extension for case insensitive pattern matching,
    but some form of it is supported in MySQL and possibly other databases.
    Because the blockless filters aren't tied to a particular database,
    I have to pick a default syntax and then customize the SQL generated
    for each database that supports the same semantics.
    Make take an options hash that supports a
    :case_insensitive option, and use this in StringMethods#ilike.
    MySQL does case insensitive matches by default for LIKE, so use LIKE
    BINARY by default and LIKE instead of ILIKE.  While there, refactor
    the pattern match SQL generation and pattern match specs.
    SQLite doesn't support regexp matches and does a case insensitive
    match (at least for ascii), so have #like and #ilike be identical.
    Have SQLite raise an error if a regexp pattern match is attempted.
    While there, add pattern specs and refactor and enable the alter
    table schema specs that were commented out.
  3. Refactor blockless filter support

    jeremyevans committed Jun 3, 2008
    Split ComplexExpression functionality into three subclasses:
    BooleanExpression, NumericExpression, and StringExpression.  Each
    subclass implements certain methods that the other subclasses do not.
    BooleanExpression: &, |, ~
    NumericExpression: +, -, *, /, <, >, <=, >=
    StringExpression: like, <, >, <=, >=
    The methods are defined in the BooleanMethods, NumericMethods,
    StringMethods, and InequalityMethods modules, which are included
    in the above classes as necessary.
    All modules are included in the ComplexExpressionMethods module
    which is included in LiteralString, Symbol, and SQL::Function.
    ComplexExpressionMethods used to be included in SQL::Expression,
    but it only really makes sense for SQL::Function, and would
    have caused problems if it were included in SQL::Expression,
    since ComplexExpression is a subclass of that.
    ComplexExpressionMethods also has three new methods: sql_boolean,
    sql_number, and sql_string, which return the receiver wrapped
    in the appropriate type of expression.  This can be useful if
    other libraries overwrite the operators for classes that Sequel
    defines the SQL for.  For example, I ran into a situation where
    Symbol#/ was defined to make file system path creation easier,
    but it returned the result as a string, which Sequel produced
    the wrong SQL with (quoting it as a string).  The fix was to
    use :symbol.sql_number/10.
Commits on Jun 2, 2008
  1. Allowing validation with :if => block

    Danilo Sato Danilo Sato
    Danilo Sato authored and Danilo Sato committed Jun 2, 2008