I Hate Suffix Data types

Lotusscript has been around a long time, and it has some nuances that can drive a person crazy. In particular ME. This is even more true when attempting to process data from an external database with long legacy roots.  Taking a page from BASIC, Lotusscript provides a convenient way to implicitly type variables called the data type suffix.  I really don't know what they are any more, and I don't care.  I would rather explicitly declare my variables anyway.  Fortunately Lotusscript allows me to force that.  Just put Option Declare in the (Options) section of the script, and you are on your way. Usually...

I was happily porting a Domino database which retrieved certain data points from a PRMS database to get that data from an Epicore CMS database. This all resides on a state of the art Power System running IBM i as the operating system.  But Epicore has a long tradition with IBM going back at least o the AS/400 days and a time where RPG only allowed 8 character field names. This led to many creative abreviations, and the frequent use of special characters like # to mean number.  So I am trying to retrieve an invoice number from and A/R Transaction file and the field name is something like ARINV#. Makes sense if you only have 8 characters to work with.  Enter Lotusscript and the Lotus connectors extension.  It is easy enough to read the record and pull out most of the data I want into a Notes document like so:

doc.customer = rec.arcust(0)
doc.name = rec.arcnam(0)
doc.invoice = rec.arinv#(0)

And there I have a problem.  Even though I have told Lotusscript to force explicit definitions, Lotus connector extension (*lclsx) has declared ARINV as in integer because of the data type suffix at the end of the variable name.  Not only does it now think a character variable is really an integer (CMS defines this field as a character), but it thinks the variable name is really ARINV not ARINV#. Charming.

Now the fix looks ok for this field

Dim arinvno As LCField
Set arinvno = rec.Lookup("arinv#")
doc.invoice = arinvno.Text(0)

But you have to remember that doesn't work for all data types.  If it were an integer I would have had to type:

Dim arinvno As LCField
Set arinvno = rec.Lookup("arinv#")
doc.invoice = arinvno.GetInt(1)

And now we are so far from consistent code that if you don't do it every day, or you don't have a reference manual laying around you are just plain out of luck.  Where the heck did the 1 come from?  Turns out that the Get<Type> functions use a 1 based index where nearly everything else in Lotusscript uses a 0 based index. You gotta love that.

SOAPBOX OFF

Selecting From a List

Building a Yii Model from an existing database file is very easy.  The tutorial here tells how to build the model and quickly add some CRUD functions.  That is fine if you have a simple flat file, but most useful databases have relationships between their files.  Unfortunately, while the pieces covering how to handle this are easily searchable, you will have to go all over the web to put all the pieces together.  Here are some of the things I wanted to do:

• populate a drop down box with the available values for the field
• sometimes I want that drop down box to be loaded based on the value in another field
• I have a Values List that consists of a header and details, sometimes I want the drop down populated from a given values list. For example I want to display a list of customer statuses that are stored in my values list tables.
• In some places I want to translate from my foreign key to a description or name.  That is I want to display the customer name instead of the customer id that is stored in my file.
• In a Grid View, I want to display, sort, and filter by the customer name instead of the customer id stored in my table.
• I want to be able to select multiple values from a list using check boxes, and have those values configurable from a table (Many to Many relationship)

Lets Get Started.

Placing a Drop Down List

If you have created your CRUD Functions with Gii as directed in the above tutorial, you will have several types of components generated in your views directory.  User input forms, however are consolidated into a single file _form.php.  This will be generated as a CActiveForm widget with Ajax Validation disabled, and populated with a row for each attribute in your model.  These rows will look like this:

<div class="row">
    <?php echo $form->labelEx($model,'country_id'); ?>
    <?php echo $form->textField($model,'country_id',array(
        'size'=>60,'maxlength'=>255)
    ); ?>
    <?php echo $form->error($model,'country_id'); ?>
</div>

This does several things for us.  The labelEx function retrieves the label text from the model.  It is also smart enough to be decorated with an asterisk (*) when entry is required for the field.  The textField inserts a text box control on into the form that is 60 characters wide, and will allow 255 characters to be entered.  And error displays any validation errors that occur for this field when the form is posted.

The problem here is that country_id is a foreign key, and no one will know to enter 3 for the USA, or 7 for Canada.  The solution is to use a drop down list that shows the names of the countries instead of the text box.  To do that we must swap out the following code with a drop down list control.

<?php echo $form->textField($model, 'country_id', array(
    'size'=>60,'maxlength'=>255));?>

Is replaced by:

<?php $list = CHtml::listData(Country::model()->findAll(), 
     'id', 'name'); ?>
<?php echo $form->dropDownList($model, 'country_id', $list,
     array('empty'=>'(Select a Country)')); ?>

First we build the list data, then we load it into a drop down list.  Notice that Country in the listData function is the class name of the Country model. listData builds an array of key=>value pairs which are loaded from the array of models provided in the first parameter.  The second parameter 'id' names the model attribute used to populate the key, and the third parameter names the attribute used to populate the value. There is an optional fourth parameter that is used to provide group names for the list.  dropDownList has four parameters as well.  The first two are the same as the first two parameters for textField.  The third parameter is the array formatted by listData, and the fourth is an array of html options for the drop down. list.  There is a special option which specifies some text for the empty value of the drop down list, and that is 'empty'.  I have set the empty value to '(Select a Country)'.

So that is how to replace a foreign key with a more meaningful drop down box on an entry form.  In future posts I will discuss the remaining points listed above.

Yii Active Record Events

I am skipping ahead a bit, but I just worked out an important detail that I want to capture. CActiveRecord pre-defines some events that you can use to save yourself some time and effort. The documentation tells that they are there, but doesn't really tell how to use them. These events are:

onBeforeValidation - in CModel - executes just before the validation rules are processed - can be used to stop save
onAfterValidation - in CModel - executes just after the validation rules are processed - can be used to stop save, but it is harder.
onBeforeSave - executes after validation but before the record is saved - can be used to stop save
onAfterSave - executes after the record is saved
onBeforeDelete - executes before the record is deleted, can be used to stop deletion
onAfterDelete - executes after the record is deleted
onBeforeFind - executes before the find command is executed
onAfterFind - executes after each found record is instantiated
onAfterConstruct - executes after a new model is constructed

I'm not going to go through all  these to tell you what they are useful for. I will leave that to you. But I will show you how to enable one of them (the rest will work the same way), and give you a bit of code that I am using it for.

I was asked to put some audit fields on each of my files to track create date and time, create user, last changed date and time, and last change user. Ordinarily it would take the same bit of code in each of my models to populate these audit fields. That code isn't all that complex. In fact here it is:

   if ($this->isNewRecord) {
      $this->create_user = Yii::app()->user->name;
      $this->create_time = new CDbExpression('current_timestamp');
   } else {
      $this->change_user = Yii::app()->user->name;
      $this->change_time = new CDbExpression('current_timestamp');
   }

This is not a large piece of code, but if things change, and they decide that they want the name of the function that called it or something else, I have to go back and change a lot of code. Instead, I can wrap this in an event handler and call it when the BeforeSave event is raised. So, I created a class in Components named Audit which has a single static method:

class Audit
{
   public static function beforeSaveHandler($event)
   {
      $model = $event->sender;
      if ($model->isNewRecord) {
         $model->create_user = Yii::app()->user->name;
         $model->create_time = new CDbExpression('current_timestamp');
      } else {
         $model->change_user = Yii::app()->user->name;
         $model->change_time = new CDbExpression('current_timestamp');
      }
   }
}

I still have to attach the handler to each model, but that is easy. It is attached in the init() method of the model. If you don't have one, just create it. It will override the init() method from CActiveRecord.

   public function init()
   { 
      parent::init();
      $this->onBeforeSave = array(new Audit, 'beforeSaveHandler');
   }

You do not really have to call the CActiveRecord::init() function because it does nothing, but I do it anyway just in case the developers of Yii put something there in the future.

So there you have it, a simple beforeSave event handler that populates audit fields in a table. You can add as many event handlers as you want to that init() function. I'm not sure what order they will be executed in, I suspect in the order that they are added. But, I wouldn't count on that as it could change. I also wouldn't write dependencies into the handlers as this will likely add unnecessary complexity to your application.

Let me know what you think.

Yii - Not a bad PHP Framework

I've been working on a little PHP project lately, and to make things a bit easier on myself I decided to use an MVC framework.  In the past I have worked a little with Zend Framework, and it is certainly a full featured framework, backed by Zend, the PHP company.  But in my experience it has a very steep learning curve.  In addition the many parts have great documentation if used in isolation, but the documentation of how the parts work together within an MVC framework is a bit lacking, and in addition I never did figure out how to get dates from the database to a form and back without jumping through a whole lot of hoops.  I am sure that there is an easy way to do it, but I spent hours searching the internet, and found nothing.

I have also looked at Code Igniter.  As much as Zend Framework is an industrial strength framework, Code Igniter is on the opposite side of the MVC framework ledger. About as light as a framework can be yet still be called a framework.  I want a framework to provide at least some assistance.  I don't want to have to completely define my own way to do things.

So I heard about Yii and decided to try it.  Yii occupies the space somewhere between Code Igniter and Zend Framework.  It is fairly responsive, yet contains database classes, form classes and several helpers, validators and filters built in.  It also features a slick code generator called Gii which you can configure with your own templates.  So far in my project I have had several things I wanted to do that were a bit unclear, or uncovered in the documentation.  However, I found that the community around Yii is extremely helpful, and most of my questions already had answers either in the Yii forums or on StackOverflow.

Over the next couple of weeks I will post more of my findings about Yii here on my blog.