Morfik Database Guide - Appendix A
From Morfikwiki.com
Morfik AppsBuilder automatically creates several objects in a projects default database which are used by the Workspace itself and by the Morfik Framework. If you are experienced in using Firebird or other major relational databases you might want to know what these objects are. It is exactly to satisfy this curiosity of the experienced database professionals that this Appendix has been added to this guide.
The database used as a sample to examine what objects are automatically created by Morfik AppsBuilder, in this Appendix, is the MorfikCMS project database.
Contents |
[edit] Morfik System Fields
Every table that is created within a Morfik project’s default database has hidden system fields which you, normally, do not see. To get a clear view of everything that exists in a regular project database, we will use a Firebird Administration utility. This utility, called IBExpert, has a Personal version which is freely available from the manufacturer’s website. This version can be used by any developer looking to satisfy his/her curiosity about what goes on inside a project’s database.
Table 1 presents the list of four system fields which are automatically created in each table created within a Morfik project database.
All system fields should not be altered or written to by the developer. Their content is automatically created and maintained by the Morfik Framework and manually altering it can cause application misbehavior.
| Table 1 – Morfik system fields | ||
| Field | Type | Size |
| MFK$GUID | CHAR | 36 |
| MFK$CTS | TIMESTAMP | - |
| MFK$LMTS | TIMESTAMP | - |
Each of these system fields has its own special uses and you can use access them directly, once you know they are there.
[edit] MFK$GUID
The MFK$GUID field is used in different moments when the Morfik Framework requires that each record be uniquely identified, such as when handling the caching of blob fields.
This field is specifically required to be returned in any query which will retrieve a picture field for binding with an Image control in a Morfik Form. This is due to the fact that the Morfik Framework only retrieves blobs, such as images, when they are actually needed in order to reduce network traffic and bandwidth consumption.
[edit] MFK$CTS
The MFK$CTS field is a timestamp which records the moment when the record was first inserted into the table. You can consider this the "Creation TimeStamp".
[edit] MFK$LMTS
The MFK$LMTS field is updated every time the record is updated and thus is a timestamp which records the moment when the record was last updated. You can consider this the "Last Modified TimeStamp".
| Note | |
 | Both the MFK$CTS and the MFK$LMTS system fields are updated through system created triggers which are shown in greater detail in the topic Generators and Triggers. |
[edit] Generators And Triggers
Morfik AppsBuilder automatically creates and maintains generators and the triggers to handle system and AutoNumber fields.
[edit] Generators
Each table which has an AutoNumber field will have a generator associated with it. Since Firebird does not automatically associate a generator to a table, this is done implicitly through the generator’s name. Morfik AppsBuilder also creates the necessary trigger code for obtaining a value from the generator and assigning it to the corresponding field.
Figure 1 - Database Objects
Note in the Treeview that can be seen in Figure 1, that our sample database has five tables, five generators and ten triggers. Morfik AppsBuilder creates a generator which follows the TableName_G1 naming convention for each table which has an AutoNumber field.
If a table has more than one AutoNumber field, Morfik AppsBuilder will create additional generators following the naming pattern TableName_Gx where "x" is incremented for each additional AutoNumber field.
The values of the AutoNumber fields are automatically updated through automatically generated triggers as seen the next topic.
[edit] Triggers
Morfik AppsBuilder automatically creates two triggers for each table you create within a project’s default database. Both triggers are always present, regardless of whether the table has an AutoNumber field or not, since they are used to set values for the Morfik system fields.
These triggers are created according to the following naming patterns: TableName_TBI for "Before Insert" triggers and TableName_TBU for "Before Update" triggers. The following code snippet contains the complete source for the creation of the _TBI (Before Insert) trigger for the AccessLog table of the MorfikCMS sample project.
SET SQL DIALECT 3;
SET NAMES UNICODE_FSS;
CREATE GENERATOR "AccessLog_G1";
SET TERM ^ ;
CREATE TRIGGER "AccessLog_TBI" FOR "AccessLog"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.MFK$CTS = GetTime();
NEW.MFK$LMTS = GetTime();
IF ((NEW.MFK$GUID IS NULL) OR (NEW.MFK$GUID='GenerateGUID()')) THEN
NEW.MFK$GUID = GenerateGUID();
NEW."Id" = GEN_ID("AccessLog_G1",1);
END
^
SET TERM ; ^
Notice that this trigger simply initiates the values of the Morfik system fields. If the table has AutoNumber fields then the necessary code to obtain the required value from the associated Generator will be added to the end of this trigger, as seen in this example.
The following code snippet contains the complete source for the creation of the _TBU (Before Update) trigger for the AccessLog table of the MorfikCMS sample project
SET SQL DIALECT 3;
SET NAMES UNICODE_FSS;
SET TERM ^ ;
CREATE TRIGGER "AccessLog_TBU" FOR "AccessLog"
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
NEW.MFK$LMTS = GetTime();
END
^
SET TERM ; ^
Note that this trigger just updates the MFK$LMTS system field with the current time. This field is thus always kept up to date with the time the record was last modified.
These are all the system triggers which are automatically created by Morfik AppsBuilder in a project’s default database.
| Note | |
|
| Since Firebird allows for more than one trigger in each of the six positions (i.e. Before Insert, After Insert, Before Update, After Update, Before Delete, After Delete) the Firebird savvy user can add his/her own triggers without interference with the system generated ones. |
[edit] Indices
Morfik AppsBuilder automatically creates two indices for each table you create within a project’s default database. The automatically created triggers follow the TableName_X1, TableName_X2 naming convention.
The _X1 index is a unique ascending index on the MFK$GUID field, while the _X2 index is an ascending index on the MFK$CTS field.
These are the only Morfik specific indices the used will find in a project’s default database. Other indices will either be directly defined by the user, or implicitly defined through the creation of relationships between tables and their corresponding constraints.
In examining a project’s default database you might find several indices with a RDB$ prefix in their names. These are Firebird system indices which are created to support the Primary and Foreign key definitions and constraints.
[edit] Tables
At this point a Morfik project database does not include any Morfik system tables. Morfik does however use the Firebird table description for its own purposes, storing in it some data in XML format.
The following is an example of what you might expect to find in the table description of a table created by Morfik.
<?xml version="1.0" encoding="windows-1252"?>
<FBTableExtensionBlock>
<mfk:section mfk:name="$TEB_SUBTYPE">
<mfk:section mfk:name="MFK$GUID">1</mfk:section>
<mfk:section mfk:name="Id">1</mfk:section>
<mfk:section mfk:name="Body">1</mfk:section>
<mfk:section mfk:name="IsPublished">1</mfk:section>
</mfk:section>
<mfk:section mfk:name="$TEB_DEFAULTVAL">
<mfk:section mfk:name="MFK$GUID">GenerateGUID()</mfk:section>
<mfk:section mfk:name="IsPublished">1</mfk:section>
<mfk:section mfk:name="SectionId">0</mfk:section>
<mfk:section mfk:name="CreatedBy">0</mfk:section>
<mfk:section mfk:name="ViewCount">0</mfk:section>
</mfk:section>
<mfk:section mfk:name="$TEB_GEN">
<mfk:section mfk:name="MFK$GUID">GUID:1:1</mfk:section>
<mfk:section mfk:name="Id"><TABLENAME>_G1:1:1</mfk:section>
</mfk:section>
<mfk:section mfk:name="$TEB_POSITION">
<mfk:section mfk:name="Id">1</mfk:section>
<mfk:section mfk:name="Title">2</mfk:section>
<mfk:section mfk:name="Summary">3</mfk:section>
<mfk:section mfk:name="Body">4</mfk:section>
<mfk:section mfk:name="DateCreated">5</mfk:section>
<mfk:section mfk:name="DatePublished">6</mfk:section>
<mfk:section mfk:name="IsPublished">7</mfk:section>
<mfk:section mfk:name="SectionId">8</mfk:section>
<mfk:section mfk:name="CreatedBy">9</mfk:section>
<mfk:section mfk:name="ViewCount">10</mfk:section>
</mfk:section>
</FBTableExtensionBlock>
This XML snippet stores information used by the Morfik table designer. We will not go into the specifics of the tags used in this snippet since their definition and usage are still subject to change. It is strongly recommended that the user avoid making any changes to this information directly.
[edit] UDFs
Morfik AppsBuilder makes a large portion of the functions available in the SystemUtilities module available to the database through a large set of UDFs. The following is a list of the functions in the mfkudf library.
| ACOS | ADDMONTH | |||
| ADDYEAR | AGE | |||
| AND_ | ASIN | |||
| ATAN | BIGLRTRIM | |||
| BLOBASPCHAR | BLOBBINCMP | |||
| BLOBLEFT | BLOBLINE | |||
| BLOBMAXSEGMENTLENGTH | BLOBMID | |||
| BLOBRIGHT | BLOBSEGMENTCOUNT | |||
| BLOBSIZE | CEIL | |||
| CHARS | COPY | |||
| COS | COTAN | |||
| CRLF | DATEFORMAT | |||
| DATESERIAL | DAYOFMONTH | |||
| DAYOFWEEK | DAYOFYEAR | |||
| DIFFINDAYS | DIFFINMONTHS | |||
| DIFFINWEEKS | DIFFINYEARS | |||
| DIV_ | DOLLARTODECIMALTEXT | |||
| DOLLARVAL | DOUBLEABS | |||
| EXP | FACTORIAL | |||
| FINDWORD | FIXEDPOINT | |||
| FLOOR | FRAC | |||
| GBPARAM | GDPARAM | |||
| GENERATEGUID | GETDATE | |||
| GETHOUR | GETMINUTE | |||
| GETMONTH | GETQUARTER | |||
| GETSECOND | GETTIME | |||
| GETYEAR | GFPARAM | |||
| GIPARAM | GSPARAM | |||
| HEXADECTOINT | IIF | |||
| INTEGERABS | INTG | |||
| INTTOHEXADEC | ISALPHA | |||
| ISDIGIT | ISDIVISIBLEBY | |||
| ISLEAPYEAR | ISLOWER | |||
| ISUPPER | LEFTCHAR | |||
| LNXP1 | LOG10 | |||
| LOG2 | LOGN | |||
| LONGDAYNAME | LONGMONTHNAME | |||
| LOWCASE | LRTRIM | |||
| LTRIM | MAXDATE | |||
| MAXIMUM | MID | |||
| MINDATE | MINIMUM | |||
| MOD_ | MONTHNAME | |||
| NOT_ | ODD | |||
| ORD | OR_ | |||
| PADLEFT | PADRIGHT | |||
| POS | POWER | |||
| PROPERCASE | RAND | |||
| REPLACESTRING | RIGHTCHAR | |||
| ROUND | ROUNDFLOAT | |||
| RTRIM | SBPARAM | |||
| SDPARAM | SFPARAM | |||
| SHL_ | SHORTDAYNAME | |||
| SHORTMONTHNAME | SHR_ | |||
| SIGN | SIN | |||
| SIPARAM | SPACE | |||
| SQRT | SSPARAM | |||
| STRBLOB | STRINGLENGTH | |||
| STRIPSTRING | STRIPTIME | |||
| SUBSTR | TAN | |||
| TIMESERIAL | TRUNCATE | |||
| UPCASE | XOR_ | |||
| XSASC | XSATAN | |||
| XSCHR | XSCOS | |||
| XSDATESERIAL | XSDAY | |||
| XSDIV | XSEXP | |||
| XSHEX | XSHOUR | |||
| XSIIF | XSINSTR | |||
| XSINT | XSLCASE | |||
| XSLEFT | XSLTRIM | |||
| XSMID | XSMINUTE | |||
| XSMONTH | XSNOW | |||
| XSREPLACE | XSRIGHT | |||
| XSRND | XSRTRIM | |||
| XSSECOND | XSSGN | |||
| XSSIN | XSSPACE | |||
| XSSQR | XSTAN | |||
| XSTIME | XSTIMESERIAL | |||
| XSTRIM | XSYEAR | |||
[edit] Wrapping it up
Morfik AppsBuilder hides away many of the details necessary, when using Firebird, to accomplish simple things such as having self incrementing fields. In the backstage of a Morfik XApp is a Firebird database and though Morfik usually hides most of the complexity of using that database it does allow you to have full access to it. The MorfikCMS project, for example, makes use of three small stored procedures though there is not visible support for accessing stored procedures in the Morfik AppsBuilder Workspace. This combination of initial ease of use with full access to the underlying high octane engine of a full fledged relational database server makes Morfik AppsBuilder a great tool for creating data driven Web applications.
