>That's a bit hard for us to do. 140+ tables = 140+ CFC's just for data access.
It's hard because of how time consuming it would be to create, or to maintain? As far as creation goes, we had a CFML generator that created the basics, and we simply tweaked from there. As far as maintenance goes... It's a slight pain, but the 100-odd CFCs we have are easier to maintain than the other 1000-odd files our software has. At least we're maintaining moderately standardised code (well: it's stanadrdised in places ;-) Another part of our software is designed to allow the user to create their own "objects" (read: DB tables and CRUD functions in a CFC); this uses *one* set of CRUD functions for all eventualities. Basically the object creation UI creates an XML file with the DB structure in it, and the various get(), add(), etc functions read that to work out which arguments to expect, their type, where to put them and how (ie: which DB platform). You get the picture (and my former co-worker Dave will be reading this and cringing @ my description of his fine work). The bottom line is that there's only *one* set of functions for all DB tables, using this model. >We also have the added complication that there's a lot of conditional logic to build either the SQL or the result sets - things that should be in joins (or in the T-SQL for SQLServer) but are wrapped up in a legacy db design (no varchars!). eg: We started out trying to adhere to a "lowest common denominator" idea for the DB interaction. Stuff like "MySQL doesn't do subqueries, so we'll not use them. We've kind of stepped back from this now, and decided given we've got the capability to have alternate code depending on the specific platform, we might as well hone that code to be as performant as possible; even if the code for MS-SQL, MySQl or Oracle uis *completely* different. It's no hardship, really. >so what I'm curious about are ideas that would allow us to use diff db's without resorting to re-writing nearly 2000 queries for each db vendor. We mostly support those three DB platforms I mentioned above. Theer's some vagaries to each, but for most of the querying we need to do (selecting columns from table(s)), there's a lot of common ground. And we simply have a switch statement based on a dbplatform variable to determine which block fo code to run. It's not a very complicated approach, but it's worked for us thusfar. Adam ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
