On Thu, 1 May 2008 09:24:44 -0400 "Ken Rickard" <agentrickard@gmail.com> wrote:
Now, can Drupal learn from and exploit the value of stored procedures -- possibly, given the requirement restrictions that Larry mentions. In fact, we need people with such experience to be active developers on the project.
In my humble experience it would be hard to write an abstraction layer that let you write stored procedures across several DB. Joshua mentioned Catalyst. I don't know what does he meant when he wrote it can manage stored procedures. The best I can think is it offer standard methods to wrap stored procedures and then you'd have to write custom one for every DB you support. Not all stored procedures are functions and not all return records. I don't know if all the DB Drupal is planning to support provide DB functions that return records but if they do... it could be reasonable to write custom db functions that return the most important objects in Drupal, so people will be able to write SQL similarly to select one.a,two.b,two.c from someobject(id) as one join othertable as two on one.id=two.id But this really doesn't look to belong to the DB abstraction layer and if you put it in the object layer you risk to make it dependent on the DB you chose that is not a good help to isolate the DB layer and make it's API DB agnostic. One thing I've to fight with when I've to code sp is that db_query prepare and execute the query. I'd prefer this functions to be separated for debug purposes. It seems that D7 is going to support prepared statements but splitting "prepare" and "execute" in db_query would have been nice even before. Then to avoid a lot of boilerplate code you could wrap prepare and execute again in db_query but people could use the "prepare" (or cast and escape) part for their purpose independently. I think the path that compete the DB layer would be to let developer provide easier different functions for different DB without resorting to checking db_type. Stored procedures are great for encapsulation, coherency, transaction management, access control... but I think it is hard to offer a flexible framework for dev that is based on sp and I don't think they are suited for Drupal audience. I use stored procedures with Drupal. I think there is nothing missing in Drupal that could help to use stored procedures across several DB. You write different versions of your stored procedures in .install, wrap them in functions, put them in different files that are loaded according to which DB you're using and you're done. The largest portion of the job is to write different versions of sp for different DB, there is no shortcut. Support for transaction may be easier, provide a more agnostic API and be quite useful, I'd say there are several part of core where transaction could be used proficiently to make the overall more solid. Something that would be harder to plan would be to make transactions extend across hooks. eg. it would be nice to make the overall process of creating/updating a user transactional so that if a contrib module add proprieties to a user the user never get half created. I don't think that db that don't support transactionq will suffer from supporting transactions at this level, they just won't offer that level of coherency. -- Ivan Sergio Borgonovo http://www.webthatworks.it