Permalink
Commits on Jul 2, 2009
  1. Bump version to 3.2.0

    jeremyevans committed Jul 2, 2009
Commits on Jun 29, 2009
  1. n the STI plugin, don't overwrite the STI field if it is already set

    jeremyevans committed Jun 29, 2009
    This allows the following to work as expected:
    
      class Person < Sequel::Model
        plugin :single_table_inheritance, :kind
      end
      class Manager < Person
      end
      Person.create(:kind => "Manager")
  2. Add support for Common Table Expressions, which use the SQL WITH clause

    jeremyevans committed Jun 29, 2009
    CTEs are supported by MSSQL 2005+, DB2 7+, Firebird 2.1+, Oracle 9+,
    and PostgreSQL 8.4+.  They allow you to specify inline views that
    the SELECT query can reference, and also support a recursive mode
    that allows tables in the WITH clause to reference themselves. This
    allows things not normally possible in standard SQL, like loading all
    descendents in a tree structure in a single query.
    
    The standard with clause takes an alias and a dataset:
    
      ds = DB[:vw]
      ds.with(:vw, DB[:table].filter{col < 1})
      # WITH vw AS (SELECT * FROM table WHERE col < 1) SELECT * FROM vw
    
    The recursive with clause usage takes an alias, a nonrecursive
    dataset, and a recursive dataset:
    
      ds.with_recursive(:vw,
        DB[:tree].filter(:id=>1),
        DB[:tree].join(:vw, :id=>:parent_id).
                  select(:vw__id, :vw__parent_id))
      # WITH RECURSIVE vw AS (SELECT * FROM "tree"
      #     WHERE ("id" = 1)
      #     UNION ALL
      #     SELECT "vw"."id", "vw"."parent_id"
      #     FROM "tree"
      #     INNER JOIN "vw" ON ("vw"."id" = "tree"."parent_id"))
      # SELECT * FROM "vw"
    
    I've only tested this on an PostgreSQL 8.4 release candidate, but
    based on my research it should support MSSQL, DB2, and Firebird.
    Oracle apparently uses a different method for recursive queries.
    
    This commit contains some fixes for the window functions committed
    previously.  Like now, it actually works on ruby 1.9.  The
    Dataset#supports_window_functions? method is now defined and
    defaults to false, and the adapters that support window
    functions override it.  If you attempt to use a window function
    on an unsupported dataset, an Error will be raised.
    
    Sequel's default is to support common table expressions, so the
    databases that don't support CTEs have been modified to not use the
    with clause if they previously used the default clauses.
    
    The MSSQL adapter was already using the :with option for setting
    NOLOCK on the query, so I changed the setting to use :table_options.
    
    Because the clauses the PostgreSQL adapter will use depends on the
    server version, I changed the code for getting the server version
    to use static SQL in order to avoid infinite recursion.
    
    PostgreSQL dataset's explain and analyze methods were broken since
    3.0, this commit fixes them and adds a simple spec.
    
    In order to implement the with_recursive method easily, the compound
    dataset methods (union, except, and intersect) now take an options
    hash instead of a true/false flag.  The previous true/false flag is
    still supported for backwards compatibility.  You can now specify it
    with the :all option, and there is now a :from_self option that you
    can set to false if you don't want to return a from_self dataset.
    
    This commit adds the qualify and with dataset methods to
    Sequel::Model.
  3. Add SQL::WindowFunction, expand virtual row blocks to support them an…

    jeremyevans committed Jun 29, 2009
    …d other constructions
    
    This adds two new classes, SQL::WindowFunction and SQL::Window.
    Window is separated from WindowFunction because some databases (at
    least PostgreSQL) allow you to specify windows separately.
    
    VirtualRow's method_missing now considers if a block was given to
    the method.  If no block was used, the old behavior is used.  If
    a block is given, the following new criteria are used:
    
    * If there are no arguments, an SQL::Function with the name of
      method used, and no arguments.
    * If the first argument is :*, an SQL::Function is created with a
      single wildcard argument (*).
    * If the first argument is :distinct, an SQL::Function is created
      with the keyword DISTINCT prefacing all remaining arguments.
    * If the first argument is :over, the second argument if provided
      should be a hash of options to pass to SQL::Window.  The options
      hash can also contain :*=>true to use a wildcard argument as the
      function argument, or :args=>... to specify an array of arguments
      to use as the function arguments.
    
    So you can now use virtual row blocks for the following things:
    
      # Functions
      ds.select{version{}} # SELECT version()
      ds.select{count(:*){}} # SELECT count(*)
      ds.select{count(:distinct, col1){}} # SELECT count(DISTINCT col1)
      # Window Functions
      ds.select{rank(:over){}} # SELECT rank() OVER ()
      ds.select{count(:over, :*=>true){}} # SELECT count(*) OVER ()
      ds.select{sum(:over, :args=>col1, :partition=>col2, :order=>col3){}}
      # SELECT sum(col1) OVER (PARTITION BY col2 ORDER BY col3)
    
    PostgreSQL can now use the Dataset#window method, which creates named
    windows, which window functions can refer to using the :window
    option.
    
    Support for Window and WindowFunction was added to Dataset#qualify,
    and support for PlaceholderLiteralString was also added.
  4. Add Model#autoincrementing_primary_key, for when the autoincrementing…

    jeremyevans committed Jun 29, 2009
    … key isn't the same as the primary key
    
    This allows Sequel to work correctly when used on MySQL MyISAM tables
    where only part of a composite primary key is auto incrementing.  The
    user still needs to override the autoincrementing_primary_key method
    manually, though.
    
    This also adds better specs for Model#save.
Commits on Jun 23, 2009
  1. Add Dataset#ungraphed, to remove the splitting of results into subhas…

    jeremyevans committed Jun 23, 2009
    …hes or associated records
    
    This is mostly useful when using eager_graph as a shortcut to setup
    joins that would be tedious to create manually, but where you really
    want to do some calculation or reporting and you don't want any
    post-processing of the results.
    
    It also works for plain datasets, where you can use it to have graph
    automatically alias columns for you, but still want results in a
    single hash instead of subhashes per table.
  2. Support :opclass option for PostgreSQL indexes

    jeremyevans committed Jun 23, 2009
    Currently, this only supports a single operator class that is used
    for all columns.  It may be expanded in the future to take a hash
    or array so you can specify per column opclasses.
  3. Make parsing of server's version more reliable for PostgreSQL

    jeremyevans committed Jun 23, 2009
    This should allow it to work with versions like 8.4 and 8.4rc1.
    It doesn't currently support beta versions, as I'm not sure
    what PostgreSQL uses for those.
Commits on Jun 22, 2009
  1. Add Dataset#qualify, which is qualify_to with a first_source default

    jeremyevans committed Jun 22, 2009
    I shouldn't have separated qualify_to and qualify_to_first_source
    originally, since almost always you are going to want to qualify to
    the first source, and qualify_to_first_source is just too long.
  2. Fix changing a column's name, type, or null status on MySQL when colu…

    jeremyevans committed Jun 22, 2009
    …mn has a string default
    
    This large commit pulls in the database default to ruby default
    convertor from the schema_dumper plugin and has schema parser
    add a :ruby_default entry to all column schemas using the
    default convertor.
    
    This is necessary to make changing a column column's name, type,
    or null status on MySQL when the column has a string default.  This
    is because MySQL doesn't quote string defaults in DESCRIBE TABLE.
    
    I don't really like this course of action, as the default conversion
    code is very ugly, but there doesn't appear to be a better way to
    fix things.
    
    This does have the advantage of supplying defaults as ruby objects
    for all parsed schemas, which I think could have many potential
    uses.  For example, you could use it in a after_initialize method
    in a model to automatically set defaults, so that Sequel::Model
    operated more like ActiveRecord.
Commits on Jun 18, 2009
  1. Fix the DO and JDBC adapters when used with shared adapters that supp…

    jeremyevans committed Jun 18, 2009
    …ort savepoints
    
    These broke because the shared adapters no longer required a module
    that overrode the transaction methods added by the main adapter.
    The solution is to punt and just call super if the shared adapter
    supports savepoints.
  2. Fix thread-safety issue in stored procedure code

    jeremyevans committed Jun 18, 2009
    The stored procedure code wasn't thread safe before.  Trying
    to run the same stored procedure in separate threads could
    previously result in the arguments to the procedure getting
    clobbered.
    
    Now, when you call a stored procedure, it clones itself and
    passes the arguments to the clone to run, which should be
    thread-safe.
    
    I was originally planning on moving the stored procedure
    support out of adapters/utils, but considering it requires
    specific adapter database support and is currently only
    supported on MySQL and JDBC, I'm planning on leaving it in
    adapter/utils for now.
  3. Remove SavepointTransactions module, integrate into Database code

    jeremyevans committed Jun 18, 2009
    This also adds a bunch of specs for the savepoint code, so you don't
    have to run the adapter specs/integration tests to test the
    savepoint code any more.
  4. Add supports_distinct_on? method

    jeremyevans committed Jun 18, 2009
    This removes the override of distinct in the Oracle and MySQL
    shared adapters and has the standard distinct method raise
    an exception if DISTINCT ON is used but it isn't supported.
  5. Remove SQLStandardDateFormat, replace with requires_sql_standard_date…

    jeremyevans committed Jun 18, 2009
    …times? method
    
    One change here is that before, ADO used SQL standard datetimes,
    which was a mistake as ADO can be used to connect to any database.
    It doesn't appear to be required on Microsoft SQL Server (the default
    when using ADO), so I'm not sure why it was used in the first place.
    If MSSQL does require it, it should be added to the shared adapter
    and not the ADO adapter.
  6. Remove UnsupportedIsTrue module, replace with supports_is_true? method

    jeremyevans committed Jun 18, 2009
    This is a fairly straightforward removal of the UnsupportedIsTrue
    module.  Now the default complex_expression_sql code checks the
    supports_is_true method and operates based on that.
    
    Because the unsupported.rb file is no longer needed, it has been
    removed from the repository and lines requiring it have been removed
    as well.
    
    While here, have complex_expression_sql raise InvalidOperation
    exceptions instead of generic Error exceptions.
  7. Remove UnsupportedIntersectExcept(All)? modules, replace with methods

    jeremyevans committed Jun 18, 2009
    This is the first in a series of commits that gets rid of the
    adapters/utils directory.  It removes the modules that overrode the
    methods in the dataset subclasses with two methods,
    supports_intersect_except? and supports_intersect_except_all?,
    and modifies the standard intersect and except methods to use
    those methods to determine whether to raise an error.
    
    This approach isn't as fast as the previous approach, but it allows
    for more reflection support and is simpler to implement and use.
Commits on Jun 17, 2009
Commits on Jun 15, 2009
  1. Fix bin/sequel using a YAML file on 1.9

    jeremyevans committed Jun 15, 2009
    We were iterating over a hash (or hash-like) data structure while
    both inserting and deleting records.  It's amazing it used to work in
    1.8.
  2. Allow single_threaded setting to be specified in connection string

    jeremyevans committed Jun 15, 2009
    You can now specify the single threaded section directly in the
    connection string:
    
       postgres:///database?single_threaded=t
       postgres:///database?single_threaded=f
    
    This breaks backwards compatibility slightly as before any value
    other than nil or false would cause the single threaded setting
    to be set.  Now, it uses the database's typecasting code to
    determine whether the value should be considered true or false.
    
    This is unlikely to have a significant effect, as Sequel defaults
    to thread-safe behavior, and you only needed to specify the
    single_threaded setting to turn the setting on.  However, I think
    this new behavior is closer to expectations.
  3. Allow connection pool options to be specified in connection string

    jeremyevans committed Jun 15, 2009
    You can now specify the connection pool options directly in the
    connection string:
    
       postgres:///database?max_connections=10&pool_timeout=20
    
    An error will now be raised immediately if max_connections is not
    positive.
    
    This breaks backwards compatibility slightly as before you were
    allowed to specify a Float for the pool_timeout.  If you do so
    now, it gets explicitly converted to an Integer.
  4. Handle :user and :password options in the JDBC adapter

    jeremyevans committed Jun 15, 2009
    This will allow users to connect using JDBC adapters that require
    the username and password be specified as separate arguments instead
    of being specified as part of the connection string.  The Oracle
    Thin adapter appears to be one of these.
  5. Fix warnings when using the ODBC adapter

    jeremyevans committed Jun 15, 2009
    Before, the ODBC adapter gave warnings whenever you used it to
    do anything except select rows.  This is because it expected users
    to drop statements manually.
    
    This is a backwards incompatible change, but should make using the
    ODBC adapter directly much easier for users.
Commits on Jun 12, 2009
  1. Added documentation for opening databases

    Michael Lang committed Jun 12, 2009
  2. Added documentation for opening databases

    Michael Lang committed Jun 12, 2009
Commits on Jun 11, 2009
  1. Majorly increase MySQL select performance using the native adapter

    jeremyevans committed Jun 11, 2009
    Instead of using a default proc that returns its argument, check
    for the existence of a type proc and if there isn't one, just
    assign the value.  Instead of an 18% performance increase as in the
    previous commit, this yields around a 75% performance increase.
    
    Also, the initial commit message in this series of performance
    patches was wrong.  These recent benchmarks were done with selecting
    1000 rows with 10 string columns, not 10 integer columns.  Integer
    columns are probably significantly slower due to having to convert
    the values.
  2. Significant increase in performance in the JDBC adapter

    jeremyevans committed Jun 11, 2009
    Using the same type of benchmark as in recent commits, this is
    results in about a 10% increase in JDBC select performance.
    
    JDBC performance could probably be way faster than it is
    currently, but I'm not sure how to structure it like the
    PostgreSQL and MySQL native adapters.  If you are a JRuby guru
    and have knowledge of JDBC, I'd appreciate your help.
  3. Slight increase in select performance in native SQLite adapter

    jeremyevans committed Jun 11, 2009
    Similar to the MySQL commit recently added.  Doesn't provide nearly
    the performance increase, as all it really saves is an Array#[]
    call per field per row.
    
    Using the same type of benchmark as in the previous MySQL commit,
    this is results in about a 2% increase in SQLite select performance.
  4. Increase MySQL select performance using the native adapter

    jeremyevans committed Jun 11, 2009
    This optimizes the inner loop in the native MySQL adapter's
    fetch_rows.  It eliminates some redundant calls and uses a
    separate proc for tinyint so that the convert_type call is no
    longer necessary.
    
    I've benchmarked this and it is roughly an 18% performance
    increase when selecting 1000 rows with 10 integer columns.
Commits on Jun 8, 2009