DataMgr and Logical Deletes

I have to start with a confession. In at least one area, DataMgr may have made my life worse.

In general, a record can be deleted in one of two ways. The record can be deleted from the table (a hard delete) or it can simply be marked as deleted (a logical delete). With a hard delete, you don't have to make sure that the record doesn't show up in any queries, but you also can't easily get it back. With a logical delete, you can get the record back, but you have to make sure not to retrieve it in any query.

Jennifer Livit once made an impassioned argument against hard deletes. Her point about the dangers of a hard delete are valuable. In fact, I relied on logical deletes for some time. Until, that is, I created DataMgr.

DataMgr has a method called deleteRecord, which (and this may not surprise you) deletes a record. Now, since DataMgr is limited in its knowledge of how you use the database table (it knows basically what the database knows), it does a hard delete.

OK. No problem. Just because I use DataMgr and it has a deleteRecord method, doesn't mean I have to use it. I can still do a logical delete in my component and make sure to tell each call to getRecords() for that table not to retrieve deleted records. The thing is, that isn't what happened. Using deleteRecord was so easy.

So, with DataMgr 2.0, I am correcting a mistake. DataMgr 2 will now support logical deletes. In order to use logical deletes, however, you must use loadXML to tell DataMgr which column you want to use to indicate the deletion. It will set that field appropriately upon deletion and it will also keep from retrieving records with getRecords that are marked as having been deleted.

This is done by way of the new "Special" attribute for a field. DataMgr can use boolean fields or data fields to indicate a deletion. With a boolean field, DataMgr will treat a value of true as a deletion. With a date field, DataMgr will treat any non-NULL value as a deletion.

Here is the XML for a table with a boolean deletion field:

<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="isDeleted" CF_DataType="CF_SQL_BIT" Special="DeletionMark" />
    </table>
</tables>

Here is the XML for a table with a date deletion field:

<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="DateDeleted" CF_DataType="CF_SQL_BIT" Special="DeletionMark" />
    </table>
</tables>

In either case, the names of the fields do not matter, only the special attribute. If the field is not CF_SQL_BIT or CF_SQL_DATE, however, DataMgr will ignore the Special="DeletionMark".

Use loadXml to pass this XML into DataMgr. You can see the CFC docs for syntax or review the "Synchronizing Database Structure" presentation for more on using loadXml.

DataMgr 2 is still in beta, but I am using it on production site, so feel free to download it and try it out. It is free for any use.