Defining Primary Table Columns

You must define one row for each database field (column) to be included in your generated db_object class.

Standard Class Column Definitions

Here we see the first few columns of the film definition:

We won't go into the details regarding these grid columns since they are identical to the columns you use when defining a PHP class using the php_class model. Since a db_object is just a specialised PHP class, these columns are used in the same way. If you are unfamiliar with the php_class model please follow this link: defining php classes. One of the notable differences is that the first column offers help so if you double-click on this column you can select fields from the primary table. Also, the last column (not shown above) is the description column. This is just used for documentation purposes.

db_object Specific Definition Columns

Turn your attention to the following screen shot where we show the first column above together with the other columns of interest to your object definition.

Let's review these grid columns in detail.

DB Data Type

This column should normally coincide with the definition of the field type within the SQL database. Your object may contain columns that are not actually in the database. For all such fields you must set the DB Data Type to "derived". It is up to you to assign these derived fields. This could be done in the after_retrieve_object method for example.

DB Column Parms

Certain database columns have special significance or must follow certain rules. This grid column is entered in json format and is used to define special properties of the column. These properties will trigger certain code to be generated within the db_object class. These special properties include:

primary

If the current column is part of the primary key of the object, indicate a number which reflects the field's position within the primary key. So, if the primary key is not a compound key, simply add primary:1 to the primary key column. For compound keys, primary:2 would be added to the column that corresponds to the second component of the primary key, etc..

auto_increment

Set this to "always" if the primary key is to be automatically derived for every insert operation. Set this to if_zero if the primary key is to be only derived in cases where the supplied primary key is zero. Only set this value if auto_increment is also set within the database.

locked

This property is normally only set for objects that use compound keys. The db_object class supports a retrieve_next_key method (to implement Next and Previous) requests that are used to get the row with the next higher or lower key value. Sometimes, you want to put a logical lock on certain fields so that next does not cause the key value to change. For example, let's say your object is used to maintain performance reviews for employees grouped by department. Suppose the primary key for the object is a compound key consisting of department_id + employee_id.  This might be a case where your would want to lock the department_id field to prevent supervisors from seeing reviews in other departments.

To use this feature, the initial department_id would normally be assigned programmatically to match the supervisor's own department. This would typically be done within the object's assign_defaults method. If  the supervisor requests the next performance review record this would be returned as long as it was for the same department_id. When viewing the very last performance review within the department, the Next method would return an "end of data" condition (even though the table may have more performance reviews for other departments).

Essentially, the locked feature is used to restrict access to certain records by placing a logical window on the data rows that can be accessed.

*label

The *label property is normally set within the Shared Parms section so that the same field label value is used within the object as well as on the forms. Nevertheless, if for some reason you want the object label to be different from the form label, you can set the *label property in the DB Column Parms section.

log

The log property is used in conjunction with writing audit trail logs. Recall that the default log behaviour is set by assigning the Default Log Option parameter. Sometimes you may want to override this default setting for certain fields. This can be done by using the log property.

UI Control

The UI Control indicates the default HTML control to be used when the property is placed on a form. This default can be overridden within the form if required.

UI Parms vs. Shared Parms

As mentioned earlier, HTML 5 offers extensive validation capabilities however they suffer from the fact that they are not strictly enforceable since that are implemented within the browser and hackers can easily post requests that bypass the browser altogether. To thwart such hackers, the db_object makes it easy to replicate browser validation within the object itself simply by placing certain UI validation within the Shared Parms section.  As an example, suppose your object includes a number column that is required and must be an integer from 1 to 10.  This can be easily done with modern browsers by defining a form field such as:

:rating,type:number,required:true,min:1,max:10

By placing these properties within the Shared Parms section (rather than the UI Parms section) you indicate that you want these settings to be redundantly implemented within the generated db_object in order to ensure that these rules are enforced even in situations where the object is not accessed via the intended browser forms.

UI Parms

All properties of an HTML input control can be specified within the UI Parms section. These become the default properties of the control when the database field is placed on a form provided the form references the db_object in the Field Definitions property.  In addition to native HTML property, the form model supports dozens of supplemental properties that start with asterisk (*). For example *trim:r can be used to automatically perform right-trim handling on the field. To see the complete set of properties supported refer to the drop-down on the Property column when using the html_field model.

Shared Parms

The following parameters can be specified as part of the Shared Parms section. These constraints will be implemented both on the forms and within the db_object itself:

• *case
• list
• min
• max
• minlength
• maxlength
• pattern
• readonly
• required
• step
• *select
• *trim

Validators

Validators are PHP classes defined within the classes/field_validator folder. These inherit from the field_validator_base class. Generally speaking, whenever you encounter a situation where specialised field validation is required you should consider creating a validator to handle the validation. By encapsulating the validation withing a validator class you can share this same validation for other fields that may require the same validation. If you define the validation within the pseudo site system, it can be shared across all of your websites.

Formatter

Formatters are PHP classes defined within the classes/field_formatter folder. These inherit from the field_formatter_base class. As with validators, whenever you encounter a situation where a field needs to be formatted in a certain way you should consider creating a formatter to handle the formatting. This will allow you to share the formatter throughout your site and, if defined within system, within other websites as well.

Auto Assign Field

Certain database fields can be assigned automatically by the db_object. For example, if your database table contains a "last_update_timestamp" field this can be assigned automatically to the current timestamp value whenever a record is updated. Here is the list of Auto Assign values:

Auto-Assign Value	Assignment
date	The date at which the record was updated or inserted.
time	The time at which the record was updated or inserted.
datetime	The combined date and time values.
user	The user who performed the update or insert operation. This value is only populated for pages that require the user to log in.
ip	The IP address of the user who inserted or last updated the record.
browser	Details of the browser (user agent) that was used when modifying the record.
service	The name of the page that was used to modify this record.
action	The last action that was used on the record. This will be insert if the record has never been updated otherwise it will be update.
row_num	This value only applies to child records maintained using the db_object_child model. The field will be assigned the relative row number of the record (starting from 1). For example, if there are three child siblings, these would be assigned 1, 2 and 3, respectively. For more information, refer to the db_object_child help.

Auto Assign Method

Indicate whether the Auto Assign Field applies only to insert operations, only to update operations or both.

Defining Dependent Tables

As described earlier, a db_object can include any number of secondary (dependent) tables. These are usually related to the primary table on a 1:1 or 1:many basis. These dependent tables must be encapsulated using the db_object_child model. Having done so, you reference the generated classes in the Dependent Tables grid as shown here.

The first column indicates the name of the array field that should be used to represent the dependent entity within the db_object class. The second column is the name of the class generated by the db_object_child model and the third column is the name of the flexgrid that will be used to maintain the values when the dependent table values are placed on a form.

Continue reading to learn about Adding constraints to your object.