On Tue, Aug 15, 2006 at 11:47:20AM -0700, Aaron Stone wrote: > On Tue, 2006-08-15 at 12:04 -0500, Jim C. Nasby wrote: > > On Tue, Aug 15, 2006 at 09:39:06AM -0700, Aaron Stone wrote: > [snip] > > > One possible path to making this work is a PHP extension compiled > > > against our libdbmail.so library. Most people would rather see a > > > pure-PHP solution, however, so you're back at porting weDBMail to the > > > current schema... > > > > What about using stored procs as an API into the database? I know that > > raises some cross-database issues, but those aren't insurmountable (and > > probably aren't much worse than dealing with the cross-database stuff in > > C). > > I'm totally open to using stored procedures for certain queries where it > makes sense, but I'm not up for rewriting all 154 queries anytime soon! > Also, we've been able to reuse all of the queries, swapping in just a > handful of specific function names where needed. If we went fully over > to stored procedures, because MySQL and PostgreSQL don't (yet?) use the > same stored language, we'd be right back in DBMail 1.x land with two > totally independent database layers.
As of now they're different, and knowing how poor MySQL is at sticking to standards it wouldn't surprise me if they stayed different. But for simple stored procs that are just replacing single SQL statements it should be pretty easy to generate code that will deal with either. The create syntax will differ, but that will just be a matter of what's being wrapped around the actual SQL. The calling syntax will probably differ as well, but it shouldn't be hard to deal with that at a low level of the database code (you'd want something like a run_stored_proc function that would deal with the different databases). At a former employer, we created a meta-language where we'd define the access methods that were needed, and it would create the stored procedure code for different databases, as well as the C code to call those procedures and handle the results. Actually, the code also contained the schema definition, so it would not only generate DDL files, it was also capable of generating documentation and automatically generating a lot of the access method meta-code, which then was used to generate the stored procs, etc. This was all done in XML/XSLT, mostly because it meant not having to write a parser. Another option that comes to mind is a hybrid approach... I don't know the guts of IMAP, but presumably it's plenty fast for a lot of operations. The key then would be to provide a database API for the operations it wasn't fast at. I suspect that could be substantially less than 154 queries. -- Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
