…and eager association loading
…ruby running on *nix Before, the only real way to connect to MSSQL from a C based ruby on *nix was to use the ODBC adapter with unixodbc and freetds. I've heard it's a big pain to set up, and never attempted to do so myself. Fortunately, tiny_tds was released recently and makes it simple. Thanks to metaskills from the tiny_tds project for making changes to tiny_tds to better support Sequel.
Surprisingly, nobody caught this error until now, and I'm pretty sure it's been in the README since before 1.3. The code and SQL aren't wrong, but the explanation is. The assumption is the order and limit settings are applied before the aggregation, but that's not how SQL operates. If you want the behavior described, you have to use a subselect, which is a bit complicated for the first example, so just simplify the code.
Amalgalite is a ruby library that embeds SQLite in a ruby extension without requiring a separate SQLite installation. This adapter is full featured and passes all integration tests, and all but one of the SQLite adapter tests (I could fix that failing test, but I don't think it's worth it). The only major issue with the amalgalite adapter is that it is currently pretty slow (10 times slower in the integration tests, 40 times slower in the adapter specs). This could have something to do with the fact that the adapter reloads the entire schema whenever there is a possibility that cached information could be stale. I didn't want to do that, but otherwise amalgalite can give you weird NoMethodErrors. As I don't use this in production, I'm not planning on working on the performance issues.
Most of this is using the block argument provided by filter instead of sql_string or sql_number. This also moves the column references via symbols documentation from the dataset filtering rdoc to the README. Add info on the new database independent migrations to the schema rdoc.
….9: , <, <=, >, >= This sizeable commit is necessary due to the fact that Sequel was violating one of it's own principles, which is that it does not override methods defined by ruby, it only adds methods not already defined. Ruby 1.9 adds Symbol# and includes Comparable in Symbol, which define <, <=, >, and >=. If you are using 1.9, code like this will stop working: dataset.filter(:number > 1) dataset.filter(:number >= 2) dataset.filter(:name < 'M') dataset.filter(:name <= 'I') dataset.filter(:is_bool[:x]) This obviously breaks backwards compatibility for 1.9 users, but it's necessary as we can't allow Sequel to completely change the meaning of existing methods on core classes. Thankfully, the fix is fairly easy, if a little verbose: dataset.filter(:number.sql_number > 1) dataset.filter(:number.sql_number >= 2) dataset.filter(:name.sql_string < 'M') dataset.filter(:name.sql_string <= 'I') dataset.filter(:is_bool.sql_function(:x)) Code like this won't break: dataset.filter(:number - 1 > 0) Because :number - 1 yields a Sequel::SQL::ComplexExpression, which defines #>. Therefore, you can change the following: dataset.filter(:number > 1) to: dataset.filter(:number - 1 > 0) But I think using .sql_number is more clear. In order to find existing uses of the above issues, here are a couple commands you can run (from the root of your project): # Find :symbol egrep -nr ':['\''"]?[a-zA-Z_0-9]*['\''"]?\[' * # Find :symbol (<|>|<=|>=) egrep -nr '[^:]:['\''"]?[a-zA-Z_0-9]*['\''"]? *[<>]=?' * All of the documentation was changed to reflect these new conventions. Note that if you are using ruby 1.8, you do not have to make the change immediately. However, if you ever plan to move to ruby 1.9, it's better to make the change sooner than later. In order to implement this change the Sequel::SQL::*Methods modules were reorganized so that they don't include other modules. This means that if you were used to just including Sequel::SQL::GenericExpressionMethods in a class to make it act like a Sequel expression, you now need to include all modules separately. Since Sequel::SQL::SpecificExpressionMethods and Sequel::SQL::GenericExpressionMethods were modules whose sole purpose was to include other modules, those modules have been removed. Personally, I think making symbols more like strings was a bad design decision, and hopefully one that will be removed in a later version. However, as ruby 1.9.1 is already at the release candidate 2 stage, I'm guessing it is here to stay.
The DataObjects support in this commit is fairly simple, and mostly ported from the JDBC support for these databases. There is no bound variable, prepared statement, or stored procedure support yet, and I have no plans to add it. There is a potential performance advantage to using DataObjects as it handles a lot more in C (such as typecasting). Most adapter specs and integration tests pass with this, though a few fail (similar to the situation with JDBC). This commit makes some other minor changes: JDBC databases are now extended with their subadapter code before Database#initialize is called, as this is required in certain cases. I didn't notice any problems on JDBC before, but when copying over the JDBC code to the DataObjects adapter, there were problems when the subadapter support was added after the Database#initialize was called (don't remember exactly what, though). Database.uri is now aliased explicitly via a real method, so it is easier to subclass. Sequel::PostgreSQL.client_min_messages is now set in the shared adapter instead of the native postgres adapter because it has an effect when using the DataObjects adapter. There shouldn't be any negative reprecussions when doing this. The SQLite Dataset#delete implementation was changed so that instead of counting first and deleting inside of a transaction, it just adds a condition that is always true. This makes it do a regular delete instead of truncate style delete. I didn't test the performance implications, if they are negative and significant, please let me know. The new code is certainly cleaner. Database#initialize now uses @opts ||= opts instead of @opts = opts, to allow subclasses to extend the database before calling super. Special DataObjects support was added to Database.connect, similar to the existing JDBC support. I'm tempted to refactor this, as it is ugly, but there's not yet a need. Database and Dataset #execute_insert were added. Database#execute_insert just calls #execute_dui by default, and Dataset#execute_insert calls Database#execute_insert. This will be used to refactor some of the adapters and remove some duplication, since returning the primary key when inserting is going to be significantly different than returning the number of affected rows on most if not all databases. The add_foreign_key integration test is no longer skipped if MySQL is used, now that MySQL supports add_foreign_key.