Make Your Database Match Your Program

The installation instructions for most ColdFusion programs include extra steps for creating the tables in the database and more for adding columns for upgrades. It is possible, however, to have your application take these steps automatically.

The DataMgr components can be used to install the needed tables your program needs on any database supported by DataMgr (currently MS Access, MS SQL Server, MySQL and PostGreSQL - but adding a new database is easy). The ability to add needed columns to existing tables is a new feature that has just been added to DataMgr (as is support for PostGreSQL).

In earlier posts, I have covered an introduction to DataMgr and inserts using DataMgr.

To instantiate a Data Manager component, you need to pass in the datasource in the init() method.

<cfset Application.DataMgr = CreateObject("component","com.sebtools.DataMgr_PostGreSQL").init("mydsn")>

You can then pass the DataMgr into your component and use it to create tables and columns as needed. The loadXML() method of the DataMgr will load the required table structure into the DataMgr using an XML format it defines for that purpose and optionally creates tables and columns as needed.

<cfset variables.DataMgr.loadXML(xmlstr,true,true)>

The first argument defines the XML for the table or tables needed by the component. The second argument (which defaults to false) indicates whether or not to create any of the tables that don't exist. The third argument (also defaults to false) indicates whether or not to add needed columns to existing tables.

With this code your component will automatically create any tables and columns that it needs. You can also load an existing table into the DataMgr by using the loadTable() method (which takes the name of the table as its only argument).

If the table is loaded, you can get the XML to create the table using the new getXML() method which returns the XML for a table (or for all tables loaded in the DataMgr component if none is given). You can then put that XML in your component to create the table when the component is loaded later.

This should make it easy to install tables as needed and update them with the new requirements of your program.

You can download the DataMgr components (for free) from http://www.bryantwebconsulting.com/cfcs/.

Give it a try and let me know how it works!

Good luck!