Generating Your Components with ColdFusion 8
So, in my case, the first thing I need is a list of tables, which requires a simple tag call. However, I could not find a way to limit the results to only user tables, so I then filter the results within my generator to ones not indicated as a system table, as in the snippet below:
<cfdbinfo datasource="#variables.dsn#" name="qAllTables" type="tables" />
<cfloop query="qAllTables">
<cfif qAllTables.table_type NEQ "SYSTEM TABLE">
<cfset objTable = createObject("component","cfcgenerator.com.cf.model.datasource.table.table").init(qAllTables.table_name,qAllTables.table_type) />
<cfset arrayAppend(arrReturn,objTable) />
</cfif>
</cfloop>
Once you have chosen your table, I need to get the details about that table including the columns, column types and primary keys which is now a single line of code:
<cfdbinfo datasource="#variables.dsn#" name="qTable" type="columns" table="#variables.table#" />
This is certainly quite a bit easier than the complex queries that make up the various rdbms components in my generator (most ORMs include something similar). The query returns most of the information you would need to build a system based upon metadata including the column name data type, size, primary keys, default values and foreign keys. While the code is about as straightforward as you can get, you may be wondering why I added the "most" qualifier - as in, what limitations exist in the
Limitations
Named domains/custom data types - I know very little about the SQL side of this other than that I have a number of datasources that use these in MS SQL. Currently the type_name column will come up with the domain rather than the actual data type - this means that the data type is not reliable for generating things like the ColdFusion or queryparam data types because the domain doesn't necessarily indicate anything about the underlying data type.
Identity fields not identified - This one is pretty straightforward, there is currently no method to determine if a column is an identity.
No standard Microsoft Access datasources - Running the cfdbinfo tag against a standard Microsoft Access datasource received the some error about an optional feature not being implemented back from the driver. However, it does appear to work just fine on MS Access with Unicode datasources. I don't consider this a key limitation because I really don't recommend using MS Access as a database for any real application (though I know some people use it for prototyping, but I still don't like it even for that).
The Generator Implementation
This implementation has been tested against MS Access with Unicode and Apache Derby Embedded datasources. If you encounter issues, please discuss them on the Illudium mailing list.
I will start this section by saying that this information is based upon a beta release candidate and not on the final version and it may well be that the final version of CF8 will remove these limitations. However what follows are the limitations as I found them in the current version:
First and foremost, if you are not running ColdFusion 8, the generator will still work and you will not see a difference as the component that includes cfdbinfo is only utilized when it is supported. However, because of the limitations listed above, more specifically the first two, I decided to leave the currently functioning database support for MySQL, MSSQL and Oracle in place for those datasource types even when CF8 is installed. However, if you are running CF8 and you have a datasource that is not one of the previously supported types, the generator will use the CF8 component for that datasource. While the issues I have discussed means that you may need to do some corrections to the data types and required values, I figured limited support for a RDBMS was better than no support - plus, I am hoping that these issues may be resolved down the road in which case, you will get the same support across all data sources. The translateCfSqlType() and translateDataType() functions need to be updated to include types from other RDBMS in the long run as well.
