REST easy with kbmMW #8 – Database 3

In previous articles we have seen how easy it is to use kbmMW’s ORM to maintain database structures and access and manipulate data.

Next release of kbmMW continues to extend on the ORM with additional features designed to make typical chores easy.

Most often, you want records to disappear from a table the moment you call ORM’s delete methods.

But sometimes, for example when you are referencing the record from other tables, it would be nice to retain the record silently, but keep it out of the result set of most queries.

Some databases supports this the hard way by using referential integrity, which means you will simply not be allowed to delete the record if some other table reference it.

That leaves it entirely up to the developer to manually manage when to show the record and when not to.

kbmMW’s ORM takes a slightly different approach to the problem. What if you define a field in the class that will flag if the record is to be considered deleted or not, and tell kbmMW that it should take that field into account when operating that particular class?

Next version will allow you to do just that. So instead of actually deleting the record, kbmMW will automatically flag it as deleted, AND automatically keep the record out of the queries result sets, regardless if you use higher level methods to operate the data, or lowerlevel kbmMW SQL statements.

The following is a sample class holding images and other stuff. Notice the kbmMW_Table attribute. It now also contains some new settings:

defaultDeleteMethod:mark
deleteMarkProperty:Delete
deleteMarkValue:true

[kbmMW_Table('name:image, defaultDeleteMethod:mark, deleteMarkProperty:Deleted, deleteMarkValue:true')]
TImage = class // this unit is called uData
private
   FID:kbmMWNullable;
   FDescription:kbmMWNullable;
   FPersonID:string;
   FBlob:TMemoryStream;
   FDeleted:boolean;
protected
   procedure SetBlob(AValue:TMemoryStream); virtual;
public
   constructor Create; virtual;
   destructor Destroy; override;

   [kbmMW_Field('name:id, primary:true, generator:shortGuid',ftString,40)]
   [kbmMW_NotNull]
   property ID:kbmMWNullable read FID write FID;

   [kbmMW_Field('name:personid',ftString,40)]
   [kbmMW_NotNull]
   [kbmMW_Null('')]
   property PID:string read FPersonID write FPersonID;

   [kbmMW_Field('name:description',ftString,30)]
   property Description:kbmMWNullable read FDescription write FDescription;

   [kbmMW_Field('name:blob',ftGraphic)]
   [kbmMW_NotNull]
   property Blob:TMemoryStream read FBlob write SetBlob;

   [kbmMW_Field('name:deleted',ftBoolean)]
   property Deleted:boolean read FDeleted write FDeleted;
end;

Default value for defaultDeleteMethod is delete or default which means the same… just delete the record (the usual way).

However when setting it to mark, we tell kbmMW that we want kbmMW to mark the deletion of records by setting the value of a specific field to a specific value. Basically it can be most (non blob) types of field, and the value can also be chosen at will. In our case, we indicates that we want the Deleted boolean property to be the one that indicates if a record is deleted or not.

The moment we use ORM.Delete(…) on an TImage instance, the corresponding record will be marked as deleted, and if we search for TImage instances in the database using ORM, the deleted ones will not be returned to us.

So from the perspective of the developer, the delete seems to operate exactly the same as before, with the big exception, that the records are in fact still available in the table. The ORM just hides the deleted ones from us.

In other situations, you might actually want to delete the record the old fashioned way, but you would like also to have a copy of the deleted record in a backup or audit table.

This can also easily be done in kbmMW by setting defaultDeleteMethod to move.

[kbmMW_Table('name:image, defaultDeleteMethod:move, deleteMoveToTable:uData.TBackupImage')]
TImage = class
...
end;

[kbmMW_Table('name:backupImage')]
TBackupImage=class(TImage)
public
   [kbmMW_Field('name:id, primary:true',ftString,40)]
   [kbmMW_NotNull]
   property ID:kbmMWNullable read FID write FID;
end;

Notice that we also provide a deleteMoveToTable setting, which points to the fully qualified name of the class that should act as the backup/audit table.

Each time we use kbmMW to delete a record, it will first be inserted into the database table named backupImage, and only then be deleted from the image table. All this will run in a single transaction, and if things goes wrong, a rollback will be forced to ensure the consistency between the backup table and the actual table is not violated during the insert/delete operation.

In the above example we have redefined the id field’s attributes, to ensure that there is no generators defined on it. The reason is that using kbmMW’s SQL statements to delete a record will automatically be rewritten to select into and delete. If any generator fields were defined, those would not be populated in such scenario, why kbmMW would raise an exception if generator fields were found. However if you do not use kbmMW’s SQL statements to delete records, its perfectly legal to have additional generator fields in the backup table. Those will then be populated automatically the usual way.

If you really really want to delete a record on a table where the class are using one of these alternative delete methods, you can add an option to make kbmMW just delete the record.

ORM.Delete(o,nil,[mwqoIgnoreDeleteModification]);

Assuming o is an instance of uData.TImage, and o is actually to be found in the table, then the record corresponding to o will be deleted from the table the old fashioned way.

The same option can be given to the Query methods, to allow showing a record that otherwise would not be returned, because it was marked as deleted.

If the move method was used, then you will have to query using the TBackupImage class to get to the deleted (but backed up) records.