Create Your Own Code Generator

Lately there has been a lot of talk about tools like Reactor, Arf, ObjectBreeze and Transfer with regards to how they can help reduce repetitive coding tasks and and speed up development time. While, as you may already know, I am a huge fan of these tools, there may be times when you are not able to or may choose not to (for whatever reason) use these tools. This leaves tools like Roobios (and even SQL Surveyor though it is not free) that include some basic code generation to speed up your component development. However, these tools only offer a few basics, and may not write code in the manner you wish to have it. Well, as it turns out, it isn't all that complicated to build your own tool to generate components your way. I put together a basic example and will walk through how I built it (and share the code via the download).First, Credit Where Credit is Due
Much of the code I am going to share was assisted by my studying how tools like Reactor and Arf generated their code. In fact, I do use a couple of simple data type conversion functions from Doug Hughes early version of Reactor, as well as a couple of slightly modified components from Arf. The code itself is generated off of XSL stylesheets, which was inspired by Reactor; I found this the simplest way to make the code generation easily customizable.

As for the generated code, the bean (and optional transfer object) was modeled off the Rooibos bean code, though it seems to be standard bean code. Other portions were modeled from Arf (as this project started as a simple means for me to move a test app I was writing off of Arf since, for reason I won't get into but that have nothing to do with the quality of the tool, I was not going to use it for this particular project). Others still were modeled off of some example code that came with ColdSpring.

A lot of this has to do with the fact that I am a total nOOb; so, for that matter, please forgive any poor OO (or even any misuse of terminology) in my generated code. I am more than happy to receive constructive criticism in that regard. I am still toying with how the code gets outputted as I try to put the generated components to use, so help in that regard would be appreciated.

Building the App
The first thing to do was to pull a list of datasources, a list of tables in that datasource and then the metadata for that particular table. I chose to use the Admin API to pull the datasource list (yes this is CF7 only, but I used XML forms to speed up the process and those are too). The code to pull the datasources was pretty simple (just replace your password in the login line):

<cfset adminAPI = createObject("component","cfide.adminapi.administrator").login("password") />
<cfset datasource = createObject("component","cfide.adminapi.datasource") />
<cfset stDatasources = datasource.getdatasources() />

The form I built, just asks you first to choose a datasource and then choose a table. You will note that, as I am only familiar with pulling MS SQL table metadata, I limited it to MS SQL datasources for the moment (more on that later).

<cfoutput>
<cfform format="xml" action="#CGI.SCRIPT_NAME#" method="post" skin="lightgray">
<cfif len(form.dsn)>
<!--- mssql only obviously, need to move this to the mssql component --->
<cfquery name="qAllTables" datasource="#form.dsn#">       exec sp_tables @table_type="'Table'"    </cfquery>
<cfinput type="hidden" name="dsn" value="#form.dsn#" />
<cfformitem type="html">DSN: #form.dsn# (<a href="#CGI.SCRIPT_NAME#">click to change</a>)</cfformitem>
<cfinput type="text" name="componentPath" label="Component Path:" size="50" required="true" value="#form.componentPath#" />
<cfselect name="table" label="Choose a table" selected="#form.table#">
<cfloop query="qAllTables">
<option value="#qAlltables.table_name#">#qAlltables.table_name#</option>
</cfloop>
</cfselect>
<cfinput type="checkbox" name="generateService" value="1" label="Generate Service" checked="#yesNoFormat(form.generateService)#" />
<cfinput type="checkbox" name="generateLTO" value="1" label="Generate LTO" checked="#yesNoFormat(form.generateLTO)#" />
<cfinput type="checkbox" name="generateColdspringXML" value="1" label="Generate ColdSpring XML" checked="#yesNoFormat(form.generateColdspringXML)#" />
<cfelse>
<cfselect name="dsn" label="Choose a datasource">
<cfloop collection="#stDatasources#" item="ds">
<!--- only mssql for now --->
<cfif stDatasources[ds].driver EQ "MSSQLServer">
<option value="#stDatasources[ds].name#">#stDatasources[ds].name#</option>
</cfif>
</cfloop>
</cfselect>
</cfif>
<cfinput type="submit" name="submitted" value="continue" />
</cfform>
</cfoutput>

That's not all that complicated, is it? The rest of this page just outputs the xsl transformations of the metadata in text areas. The trick is in what we do with the metadata and in our xsl documents. This (for the moment) is all handled by my mssql component (and, yes, I do hope to reorganize the code a bit down the road, but the point here is that this is quick and dirty and easy to do).

The component uses standard MS SQL stored procedures to get the metadata (these are seperated into functions, but I will show them combined for display purposes).

<!--- get table column info --->
<cfstoredproc datasource="#variables.dsn#" procedure="sp_columns">
<cfprocparam value="#variables.table#" cfsqltype="CF_SQL_VARCHAR" />
<cfprocresult name="qTable" />
</cfstoredproc>

<!--- get primary key data --->
<cfstoredproc datasource="#variables.dsn#" procedure="sp_pkeys">
<cfprocparam value="#variables.table#" cfsqltype="CF_SQL_VARCHAR" />
<cfprocresult name="qPrimaryKeys" />
</cfstoredproc>

Much like Reactor does, I then convert the table metadata into a very simple xml format (this isn't a standardized format...I just made it up...I would guess that there is probably a more standardized means of expressing table metadata in xml).

<!--- convert the table data into an xml format --->
<cfxml variable="xmlTable">
<cfoutput>
<root>
<bean name="#listLast(variables.componentPath,'.')#" path="#variables.componentPath#">
<dbtable name="#variables.table#">
<cfloop query="variables.tableMetadata">
<column name="#variables.tableMetadata.column_name#"             type="<cfif variables.tableMetadata.type_name EQ 'char' AND variables.tableMetadata.length EQ 35 AND listFind(variables.primaryKeyList,variables.tableMetadata.column_name)>uuid<cfelse>#translateDataType(variables.tableMetadata.type_name)#</cfif>"             cfSqlType="#translateCfSqlType(variables.tableMetadata.type_name)#"             required="#yesNoFormat(variables.tableMetadata.nullable-1)#"             length="#variables.tableMetadata.length#"             primaryKey="#yesNoFormat(listFind(variables.primaryKeyList,variables.tableMetadata.column_name))#" />
</cfloop>
</dbtable>
</bean>
</root>
</cfoutput>
</cfxml>

You will note that I do use a couple of functions here from Reactor to convert the data types from SQL to ones I can use in CF code; and that's basically it for getting the table metadata...again, nothing overly complicated.

The real work one would have to do to customize the outputted components to their liking is to create xsl doxuments to covert this table xml to CF code. That is pretty basic too.

All I did was build a component the way I liked it, and saved a copy as a text document. The rest is essentially find/replace. First, of course, replace any < and > with &lt; and &gt;. Next, replace the component with <xsl:value-of select="//bean/@path"/> and any references to the bean name with <xsl:value-of select="//bean/@name"/>. I think you get the idea...download the code to see the rest, but its mostly just replacing out values with their xml "mapping" (is that the right term?).

I was even able to add in the code for a basic validate function (based upon Arf's validate). It checks for type and length and whether something is required or not based upon whether it is nullable or not. Lastly, I added (as a last minute addition) some basic ColdSpring wiring code...I am probably not doing this perfectly as I am newer to ColdSpring than to OO concepts, but, like I said earlier, the fun in this is that once it is built it is easy to change and/or expand.

Going Forward
First and foremost, I would like to remove the fact that this will only work for MS SQL. I do a lot of MySQL development on the side myself, and it would be useful to have this available. I have read recently that you can use Java and the Admin API to introspect datasources, and this may be a way of adding support to other databases, but I am not overly familiar with how this is done (although, I believe this is how Arf works...). Secondly, I would like to strengthen the quality of the generated components as I become more familiar with the underlying OO concepts. Lastly, the code is kinda sloppy in places as this was really glorified skunkworks.

Please feel free to take the code and do as you wish with it. If you do take the time to improve upon it, I would appreciate it if you shared the result.

Update - Feb. 5, 2006
I have updated the code generator with MySQL support and some better generated code (IMHO)

Download the attachment.

This is great stuff Brian. Thanks a lot for posting this.

I'll recommend you look at Francis's code also. I found it very, very easy to extend to my needs.

good stuff Brian. How is this licensed?

After Ray's debacle with hornhead I'm strongly urging everyone to adopt some sort of license to remove any ambiguity. Pete Frietag has a great comparison chart here:
http://www.petefreitag.com/item/533.cfm