On Nov 13, 8:23 am, Matthew <[EMAIL PROTECTED]> wrote: > Dear all, > > First let me say what a breath of fresh air Sequel is after long > struggles to make ActiveRecord do what I want.
Thanks. I think one of the reasons for that is the desire to keep things as simple as possible. > 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. I don't have any need or desire for this, but I'm not opposed to it in principle. I feel the complexity factor is likely to be too high, though. > 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. PostgreSQL doesn't appear to support a JOIN in a delete, though it does in an update. Sequel would know if it is doing a group by, but it's not going to know if a column is calculated or not. > - 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. That is correct, datasets do not, and actually they cannot, unless they were changed to introspect the database. Consider the following: DB[:a].select(:b).join(:c, :id=>:c_id) Sequel has no way of knowing if b refers to a column in a or c, or even a table to be added/joined to the dataset later. > - 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. How do you expect Sequel to know enough to make the right decisions? > - 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 doubt this would have significant benefits, except maybe on MySQL because its optimizer is so bad. Really, it would be better to switch to a database that sucks less. > 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. The best way to handle CTI in Sequel is probably using associations, before or after model callbacks (when a subclass object is saved, save the superclass associated object as well), and intelligent accessors (def blah=(x); superclass_object.blah = x; end). With some metaprogramming it shouldn't be too difficult. > 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 :) It's a ton of work and a huge amount of complexity for what will be of very little use to the average user. I won't even consider the idea further until I see some diffs. Really, if you want an updateable view, use an updateable view (e.g. PostgreSQL RULEs). If your database doesn't support what you want, use a database that does. There's only so much lipstick you can put on a pig after all, and it doesn't change the pig's pig-ness, even if it makes the pig nicer to look at. Jeremy --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
