Dear all, First let me say what a breath of fresh air Sequel is after long struggles to make ActiveRecord do what I want.
There's a particular set of features which I'm interesting in adding, but thought I would see if anyone has existing plans / ideas in this direction. In short, what I would like is the ability to perform update, delete and insert on simple joined datasets. Essentially the ability to treat them as an updatable view, even if the underlying db engine doesn't support such things. Sequel::Model is already pretty excellent for allowing a model to be based on a joined dataset, in a read-only way. And I think it may be possible with a bit more work to extent this to support for update, delete etc for certain kinds of simple joined datasets. My suggestions for implementing this would be: - MySQL and other DBMSes support multi-table update and delete statements; they use the same join clause syntax as for selects, and it should be possible to generate these statements from a joined dataset. Exceptions may need to be raised if incompatible constructs like group_by and calculated columns are used in the dataset. - One obstacle may be that, at present, datasets don't always keep a track of which physical table each of their columns is drawn from. In the short-term, it could be a requirement that you have to specify updates using :table__column syntax, but that does break the abstraction of a joined dataset as a 'view' just a little bit -- in the longer term it would be nice to see Sequel use a more structured approach to column metadata than the optional double-underscore symbol thing. - Inserts into multi-table datasets may prove a little more subtle. In the case of auto-generated primary keys and so forth these imply a dependency graph would need to be sorted into a correct linear order, and would require some code to piece together the different INSERTs. Multi-table insert would also require stricter restrictions on the kinds of datasets allowed - eg in the case of a where clause, it coudl raise an exception, or possibly it could allow a simple where clause of the form "where column = 'value' and other = 'other'", automatically setting the relevant columns to their required values for the new rows. - Just talking selects for a minute - when building further abstractions ontop of joined datasets, it would be nice if Sequel could re-write subqueries in the join clause, as direct joins. eg select * from foo join (select * from bar) as x -> select * from foo join bar select * from foo join (select * from bar join baz) as x -> select * from foo join (bar join baz) Noting that the order of joins doesn't matter in the case of inner joins, and that even in the case of outer joins, most DMBSes support bracketed join expressions within the join clause. But MySQL for one finds it very hard to optimise subqueries used in the join clause (despite the fact that it's a trivial rewriting exercise, sigh. I think it's because it needs to execute the subquery in order to get the column list for it in order to plan the outer query). I mentioned Class Table Inheritance in the subject, which was one (although not the only one) of the motivating goals for this. Class Table Inheritance is when you model the extra columns required by a subclass with a separate table, whose primary key is a foreign key to the superclass/base table. This is a lot cleaner and more normalised from a relational modelling perspective, than the Single Table Inheritance approach, and especially useful when you have a common base class for a lot of different parts of your model. Of course you can map this kind of schema to aggregation instead of inheritance, which is what I'm doing at the moment - but this feels quite artificial when you do genuinely want "is_a" not "has_a", adds a lot of boilerplate to the CRUD methods, and makes it harder to take advantage of polymorphic dispatch within your Ruby models. I've already been able to implement this pattern in a read-only way, using set_dataset together with set_model / set_row_filter. With update/insert/delete of joined datasets this could become a genuine fully-featured model class. Would welcome your thoughts on all this :) -Matthew --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en -~----------~----~----~----~------~----~------~--~---
