On Fri, Nov 14, 2008 at 7:42 AM, Jeremy Evans <[EMAIL PROTECTED]> wrote: > > 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.
I think Jeremy has a point - we should chose a DB that will suit our need. However, it would be useful if Sequel or some mix-in offered some of the functionality discussed. In case any DB newbies stumble across this thread and are swayed by Jeremy's MySQL opinion, here is another: It is worth reminding that beauty is in the eye of the beholder ;) Specifically. If your circumstances dictate your use-case requires a DB that is capable of 'working out of the box' (i.e. no lipstick), then Jeremy is correct MySQL is a pig with fleas - but at least it works. PostreSQL won't (as of September 2008) in _some_ cases considered relevant by the Transaction Processing Council (www.tpc.org): http://www.monetdb.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html Ironically, by following Jeremy's maxim (use a DB that works for you), you'd get out there and hug the MySQL pig :) Of course if PostgreSQL works for you, and if you don't mind a DB that doesn't function by default outside your immediate needs (i.e. provides no head room), then you'd consider the PostgreSQL pig as the next best. As Jeremy says you might be able to spend days and $'s, hire a DB-guru to paste lipstick on it, and it might work - any reports of lipstickful postgreSQL being able to process the TPC-H benchmarks? So, it might be the case that PostgreSQL can be coaxed to function, but if your use case requires a DB that functions by default, in the TPC-H use cases, then PostgreSQL is definitely not for you. Yes, it would be nice to have a MonetDB adapter for Sequel... it's currently third on my list of projects. An interesting performance comparison would be after tuning all three DB's. Hiring such a <vendor> DB-guru is currently outside of my, and many other's, feasible set. Jeremy, I can't post every time you assume your use-case is everyone else's.... could you please consider this possibility? Otherwise the Sequel group gets tagged as steering some into a (currently) dead end - postgreSQL ;) Cheers Mark > 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 -~----------~----~----~----~------~----~------~--~---
