I've written in the past about using DataMgr to make sure that the tables and columns you need exist. I have been asked a few times this week about making sure that those newly created tables are automatically loaded with data.

If you don't know how to have DataMgr create the tables and columns you need, you can watch the "Synchronize Database Structure" presentation now.

To review, in order to have DataMgr create tables and columns in DataMgr, pass XML to the loadXML() method of DataMgr (you can view the CFC doc for syntax).

For example, the following XML:

<tables>
    <table name="Examples">
        <field ColumnName="SampleID" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
        <field ColumnName="SampleName" CF_DataType="CF_SQL_VARCHAR" Length="50" />
        <field ColumnName="SampleDescription" CF_DataType="CF_SQL_LONGVARCHAR" />
        <field ColumnName="MyDate" CF_DataType="CF_SQL_DATE" />
    </table>
</tables>

This would create the "Examples" table with the fields as detailed above.

In order to create this table with two rows of data, you could use the following XML.

<tables>
    <table name="Examples">
        <field ColumnName="SampleID" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
        <field ColumnName="SampleName" CF_DataType="CF_SQL_VARCHAR" Length="50" />
        <field ColumnName="SampleDescription" CF_DataType="CF_SQL_LONGVARCHAR" />
        <field ColumnName="MyDate" CF_DataType="CF_SQL_DATE" />
    </table>
    <data table="Examples">
        <row SampleName="Bob" SampleDescription="This is the description of Bob." />
        <row SampleName="Coca-Cola" SampleDescription="Taste tests show this isn't as popular as Pepsi, but ads make people think it tastes better." />
    </data>
</tables>

DataMgr will also let you use column names that are not normally valid in most databases. For example, you could have a column named "Sample Name" (with a space). The above XML format will not, of course, support that column name. DataMgr can support it with a slightly more verbose syntax.

<tables>
    <table name="Examples">
        <field ColumnName="SampleID" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
        <field ColumnName="Sample Name" CF_DataType="CF_SQL_VARCHAR" Length="50" />
        <field ColumnName="SampleDescription" CF_DataType="CF_SQL_LONGVARCHAR" />
        <field ColumnName="MyDate" CF_DataType="CF_SQL_DATE" />
    </table>
    <data table="Examples">
        <row>
            <field name="Sample Name" value="Bob" />
            <field name="SampleDescription" value="This is the description of Bob." />
        </row>
        <row>
            <field name="Sample Name" value="Coca-Cola" />
            <field name="SampleDescription" value="Taste tests show this isn't as popular as Pepsi, but ads make people think it tastes better." />
        </row>
    </data>
</tables>

It is also possible that you might have some data that you want to ensure will always be available in the table (even if it isn't empty). To do that, you can use the "permanentRows" attribute:

<tables>
    <table name="categories">
        <field ColumnName="CatID" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
        <field ColumnName="CatName" CF_DataType="CF_SQL_VARCHAR" Length="50" />
    </table>
    <data table="categories" permanentRows="true">
        <row CatName="Coats" />
        <row CatName="Shoes" />
    </data>
</tables>

DataMgr will use this data to ensure that the categories of "Coats" and "Shoes" always exist (but without adding duplicates).

You may also want to include relational data (products for Coats and Shoes, for example):

<tables>
    <table name="categories">
        <field ColumnName="CatID" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
        <field ColumnName="CatName" CF_DataType="CF_SQL_VARCHAR" Length="50" />
    </table>
    <table name="products">
         <field ColumnName="Prod" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
         <field ColumnName="CatID" CF_DataType="CF_SQL_INTEGER" />
        <field ColumnName="ProdName" CF_DataType="CF_SQL_VARCHAR" Length="50" />
    </table>
    <data table="categories" permanentRows="true">
        <row CatName="Coats" />
        <row CatName="Shoes" />
    </data>
    <data table="products">
        <row ProdName="Air Jordan's">
            <field name="CatID" reltable="categories" relfield="CatID" CatName="Shoes" />
        </row>
        <row ProdName="The Ambassador">
            <field name="CatID" reltable="categories" relfield="CatID" CatName="Coats" />
        </row>
    </data>
</tables>

In the above example, I related the data by one field: "CatName", but I could have used multiple fields if I wanted. If the CatName field was an invalid field name, like "Cat Name", the above syntax would fail. So, the final example covers that unfortunate situation:

<tables>
    <table name="categories">
        <field ColumnName="CatID" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
        <field ColumnName="Cat Name" CF_DataType="CF_SQL_VARCHAR" Length="50" />
    </table>
    <table name="products">
         <field ColumnName="Prod" CF_DataType="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
         <field ColumnName="CatID" CF_DataType="CF_SQL_INTEGER" />
        <field ColumnName="ProdName" CF_DataType="CF_SQL_VARCHAR" Length="50" />
    </table>
    <data table="categories">
        <row>
            <field name="Cat Name" value="Coats" />
        </row>
        <row>
            <field name="Cat Name" value="Shoes" />
        </row>
    </data>
    <data table="products">
        <row ProdName="Air Jordan's">
            <field name="CatID" reltable="categories" relfield="CatID">
                <relfield name="Cat Name" value="Shoes" />
            </field>
        </row>
        <row ProdName="The Ambassador">
            <field name="CatID" reltable="categories" relfield="CatID">
                <relfield name="Cat Name" value="Coats" />
            </field>
        </row>
    </data>
</tables>

Keep in mind, that the first example will cover most situations. The options are available, however, to have DataMgr ensure that the table structure and data that you need for your application are available.

I know that many of my recent entries have been covering features new to the upcoming DataMgr 2 . This isn't one of them. You can use this functionality right now.

Feel free to download DataMgr as use it for any purpose. 

On the CFCDev list, Barry Beattle just asked several questions trying to understand a badly-written Fusebox 3 app. One of his last questions was "what pain do these actually solve?".

To me, this seems like a really good question. I frequently see discussions on whether frameworks have any value or which frameworks are "best". The first discussion seems to produce a lot of heat with the non-believers saying "That doesn't seem to solve any problem that I have". The second discussion seems to generally resolve with "It depends.".

[More]

Now that I have the DataMgr 2.0 Beta fairly stable, I would like to expand database support. The first new database I want to add is Oracle.

Unfortunately, I don't have access to any hosting with Oracle. Not having much experience with Oracle (I only used it once on a small project years ago), I don't know if testing on the free Oracle Database 10g Express Edition would be sufficient to establish Oracle support. 

Does anyone know if that would work or does anyone have the ability to give me temporary access to ColdFusion development on an Oracle database?

While I am on the topic, I would also love to add support for other databases as well if I could get access to run some ColdFusion code against the database (Informix, Sybase, DB2, etc).

So if you think you could get me temporary development access to Oracle or another database that I don't yet support, let me know. You can either comment on this blog, contact me via my site, or email me (steve at my domain name).

The other day, I needed to run test an interaction with some software for a client. It wasn't practical to install it on my machine, but the computer on which it was installed is two hours away. The testing was too complicated to have my client perform it.

What I really needed to do was get on her computer from my house. This required her to be able to set up the connection.

This seemed like an ideal situation for Fog Creek Copilot. I had tested it before, but this would be my first time to use it with a client (and one stretched thin and temporarily overwhelmed with technology).

I signed up and then gave her the instructions to download the program. After a brief bit of confusion because I tend not to enunciate well (she heard a different URL than what I intended to say), she was able to download the program and enter in the code.

Simply put, the program worked exactly as I hoped. If you run into the same situation, I highly recommend Fog Creek Copilot (no, I am not getting any compensation for this review). 

I have been a big fan of QuerySim since Hal Helms introduced the idea years ago (it was later made into a UDF). It is a great way to prototype an application. What I don't like, however, is that I have to type in the fake data and then later remove what I have done.

I understand the idea of "booster rocket" code that I have to throw away, but I would ideally like to avoid it. Even so, I have always liked it and used it extensively.

While working on a recent project, I got to the prototyping stage and thought "There has to be a better way.". I didn't want to type in the simulated date any more.

I found a QuerySim Generator . This seems like a neat idea, but still takes about as much work as typing in values for QuerySim and still produces throw-away code.

Then it hit me. DataMgr! I have been using this tool since I created it in December of 2004. I define my database structure using XML and then use DataMgr to have the application make sure that it has the structure it needs (Peter Bell points out that the Ruby on Rails folks call this ActiveSchema ).

What I really wanted was a way to use the exact same CFC code to work with simulated data. Then my front-end code wouldn't have to change at all once I hooked up my application to an actual database - my CFC might not need to change either.

The resulting work led to a new DataMgr component for a database type of "Sim". DataMgr_Sim.cfc acts just like every other DataMgr component. The only difference is that no real database is involved. All of the data is simulated.

This simulated data can't be added, deleted, or updated (though it may act like it can). It will, however, produce appropriate simulated data for the column types that you are using.

You can also limit the number of rows returned by DataMgr_Sim.cfc in the XML (using the "simrows" attribute) or using the "rows" argument of the init method (only relevant for DataMgr_Sim.cfc). The simulated database will also use seed data instead of randomly generated data for any table in which that is provided.

Brian Kotek's article on QuerySim has some good ideas on using QuerySim.

DataMgr_Sim.cfc is only available as part of DataMgr 2.0 (currently in Beta). Feel free to try it out (I am currently using it on several production sites). 

After using DataMgr 2 in production for a few weeks I think it is ready for beta. I am really excited about the new features. Even over using DataMgr 1.0, they have already saved me a lot of time.

You can download DataMgr 2.0 Beta and try it out. You can also download an upcoming version of CodeCop as an example implementation of DataMgr 2.0. Look in /sys/CodeCop.cfc for a thorough, working example of loadXML(). 

Here is a brief overview of the new features in DataMgr 2.0:

Relationships

DataMgr 2.0 has several built-in relationships (for lack of a better term). These relationships are not exactly the same as relationships in your database (and I would welcome a better term). Instead these relationships allow you to define some columns for DataMgr in a table that are not actual columns in the table in the database, but that DataMgr will use as columns. Instead of getting the data from the table, DataMgr will query another table for the data.

For example, you can have the category name for a product automatically included in the recordset for a product (using a "label" relationship).

You could also use a "list" relationship to get a list of values from another table (perhaps the primary key values from a many-to-many relationship - which could then be used in a checkbox).

Other types include: avg,count,max,min. The data from these relationships will be included in the recordset returned from getRecords(). You can also be pass them in as data to getRecords() to filter the results. With the exception of the aggregate types (avg,count,max,min), you can also pass them into saveRecord() to update the data.

All relationships must be configured in loadXML().

Special Types 

DataMgr can provide a number of different actions on special fields. All of which must be configured in loadXML().

Special Types:

• CreationDate
• Only used with CF_SQL_DATE
• DataMgr will automatically put the current date/time when inserting a record if no value is passed in for this field.
• DeletionMark
• Only used with CF_SQL_BIT or CF_SQL_DATE
• DataMgr will only retrieve records where this field is 0 (zero) for CF_SQL_BIT or NULL for either type.
• If a table has a DeletionMark field, DataMgr will use it (setting it to true for CF_SQL_BIT or to the current date for CF_SQL_DATE) instead of deleting a record when calling deleteRecord().
• Sorter
• Only used with CF_SQL_INTEGER
• If no orderby argument is passed to getRecords(), this field will be the order by field.
• When inserting a record, DataMgr will automatically assign this field to the next highest value (one higher than the highest value in the table) if no other value is passed in.
  

Username/Password

The init() method of DataMgr now accepts arguments for username and password. If both of these arguments are passed in, DataMgr will use them for the username and password attributes of cfquery.

Seed Data

Seed Data is not a new feature of DataMgr 2.0, but it is one that hasn't been covered and takes on increased importance in DataMgr 2.0. DataMgr can load data into a database table when it creates it or it can make sure that some data always exists in a table.

Simulated Database

With DataMgr 2.0 comes a new database type. The database type "Sim" acts much like QuerySim in that it creates simulated data. Unlike QuerySim, however, it creates the data without you needing to type it. The simulated database will use seed data if any is given, otherwise it will generate random data.

The simulated database will also take advantage of relationships. It can include generated data and seeded data in one recordset if that is required by the relationship.

The "Sim" database is ideal for creating a prototype of an application. When you are ready to make your application functional, simply replace the call to DataMgr_Sim with a call to the DataMgr for your database.

The number of rows returned for generated data will be determined by the rows argument of the init() method or by a simrows attribute of the table tag in loadXML().

Logging 

You can log all changes to data done using DataMgr. The startLogging() method will cause DataMgr to store a record for every insert, update or delete. By default this will be done in the "datamgrLogs" table.

You can also use the stopLogging() method to stop logging in DataMgr.

getRecords() 

As a small change, getRecord() has new arguments. The orderBy arguments will order the records (superceding any "Sorter" field). The maxrows argument will limit the number of records returned. The fieldlist argument will limit the fields returned by getRecords. This can include relationship fields.

Synchronizer 

Although technically not a new feature, the DataSynch.cfc is also available to synchronize databases.

/database_synchronization.htm

More details on these features - and how to use them - can be found in the word doc that is included with the DataMgr 2.0 Beta zip file .  Additionally, I plan to do more blog entries and/or presentations about individual features.

While the new version has been working great for me, I expect that you may find improvements that can be made to the API or implementation. If you do, let me know .

Download DataMgr 2.0 Beta. 

Find out more about DataMgr. 

Instead of passing in multiple email settings, I use Mailer.cfc to send email from components.

I recently needed to keep track of every email sent by a client site (but not for the whole server). So, I added logging to Mailer.cfc.

Realizing that I had yet to make it a 1.0 release (an dI have been using Mailer since December 2004), this seemed like a good time.

I have already written an introduction to Mailer.cfc and I have done some presentations on Mailer.cfc, so I will cover the logging briefly here.

Like DataMgr, Mailer.cfc is part of the com.sebtools package (and like DataMgr it is no longer required that it be located there). It does not, however, require DataMgr or any other component. Unless, that is, you want to do logging of email sent by Mailer.cfc.

I chose to introduce the dependency on DataMgr for logging for the same basic reasons that I wrote DataMgr in the first place. It made adding the feature trivial to write (about 55 lines), but very flexible (records only the data available). It ensures that Mailer.cfc can work with any database supported by DataMgr (currently Access, MS SQL, MySQL, PostGreSQL - but adding more is easy and I plan to add support for more in the near future).

In order to have Mailer.cfc log the email that it sends, you need only call the startLogging() method. This method takes only one argument which is the DataMgr component that you want to use.

In order to stop Mailer.cfc from logging the email that it sends, call the stopLogging() method.

Feel free to download Mailer.cfc . It is free and open source.

A few years ago, I was working on my first non-Fusebox site (by client requirement). I handled layouts (with some modification) by calling the requested file from Application.cfm and then outputting the results in the site layout.

I created a file for decisions about which files should get which layout and I made some exceptions for some pop-up pages.

This seemed to work well, overall. I had a centralized layout file (similar to what I would have had in Fusebox 3). I could make universal changes to the layout in one place. I was able to integrate it with a Fusebox 2 shopping cart that we had chosen for the site.

I recently added some new functionality. Due to time constraints, I subcontracted out the work. I got back an AJAX tool to be used in the site administration. I tested it out and it worked as specified. I copied it into the site and tried it out. It looked right, but the AJAX would never return any results.

The reason, of course, is that the AJAX results were being returned with the layout applied to them. OK. No problem. I went in to the configuration page and made another exception for the AJAX data results. Of course, because I had written the site so long ago it took me a while to figure out the problem.

The site uses a slightly different layout for the home page than for the other pages. So, I create a copy of the home page to test changes. Each time, I have to remember to make an exception for the home page so that it gets the home page layout instead of the default one.

None of these issues are major, but they are still problems.

The problems all stem from a very basic issue: The decision of which layout to use on a page is being made outside of the page itself. If the page made th decision of which layout to apply then it would keep the layout when I copied the home page. If the page made the decision of which layout to use, then the AJAX data pages would have worked, never having had any layout applied to them.

This seems like a lot of work, but it doesn't have to be. If you use cfinclude for headers and footers, you just need to do the include from within your page rather than from Application.cfm and OnRequestEnd.cfm (or Application.cfc).