Tour Sections

• Introduction
• Site Structure
• Page Assembly
• URL Handlers
• Multi-View Strings
• Authentication
• Database
• Error Handling
• Validation
• Config Data
• Logging
• EDP
• User Interface
• Formatters
• Images
• API Utilities
• Developer Tools

Online Reference

Tags:

fwpActn_unlock

Types:

fwp_adaptorMySQL
fwp_rcrdData
fwp_recordData

Configs:

tableModel_{tableName}

ToolKit Description

Database Interface

The PageBlocks framework includes a database query abstraction interface which integrates error management, record locking, field validation, and query string generation for common database actions. These routines are structured as shells around the core LDML inline actions, and thus are called actionShells. These routines are available as methods of a subclassable custom type which can be used to represent data models.

The code originated from refactoring the database actions common to typical data table admin pages, so the capabilities are more or less centered around forms processing rather than low-level data model or object relational mapping (that will be a chore for a future release). Nevertheless, depending on the complexity of the data being worked with, the database interface can do quite a few time saving chores in most applications.

The API is written with tiered separation of logic and query code. Therefore, multiple database engines can be used by adding a query translation layer which implements the specific syntax for a given database server. An older API from PageBlocks 4 supports FileMaker Pro with many of the same capabilities. Database statements for INSERT (add), UPDATE, DELETE, and SELECT (search) are all automatically written in response to form inputs using a tableModel_ input-to-field mapper for each table.

A built-in pessimistic record locking system prevents two users from loading the same record into their browsers for editing. This prevents unsyncronized updates in multi-administrator environments. Message delivery to users during the locking process is automatically handled through the error management system, and all data is returned so that it can at least be displayed if the developer chooses to provide that.

Database Abstraction API

The PageBlocks 5 framework includes an n-tiered database API. It's a bit of a hybrid of many systems. It has qualities of both a db abstraction system and a DMO system. It has evolved to meet the needs I've had in my applications to date, and will likely continue to become or be replaced by more sophisticated systems.

Data tables are defined by configuration files which serve many purposes once loaded and parsed into what are called table adaptors. The config file looks like this:

# filename = tblDefn_fwpuserauth.cnfg
#
{{serverType___    [$fw_gDbServerTypes->find:'auth'] }}
{{databaseName___  [$fw_gDatabases->find:'auth'] }}
{{tableName___     [$fw_gTables->find:'userauth'] }}
{{keyfield___      rcrdNo }}
{{lockField___     rcrdLockID }}
{{tableModel___
#
#inputName      fieldName           datatype   validation codes
#------------   -----------------   ---------   --------------------------
rcrdNo           rcrdNo              string
rcrdCreated      rcrdCreated         string
rcrdCreatedBy        rcrdCreatedBy       string
rcrdModified      rcrdModified        string
rcrdModifiedBy        rcrdModifiedBy      string
m_rApv          rcrdApproved        string
rcrdLockID        rcrdLockID          string
rcrdLockTime        rcrdLockTime        string
rcrdLockOwnr       rcrdLockOwner       string
#
m_rSessTime     sessionTime         string
m_rSessVars     sessionVars         string
m_rSessKprs     sessionKeepers      string
m_rSessProf     sessionProfile      string
#
m_uNameF        userNameFirst       string     req, aspc, label=First Name
m_uNameL        userNameLast        string     req, aspc, label=Last Name
m_uOrg          userOrg             string
m_uPhone        userPhone           string     n
m_uEmail        userEmail           string     req, email
m_uPw           userPswd            string
m_uHint         userHint            string     req
#
m_uIns          userLogins          integer
m_uLastIn       userLastLogin       string
m_uTries        userAttempts        integer
m_uLokTime      userLockTime        string
m_uPwNew        userPswdNew         string
m_uPwDate       userPswdCreated     string
m_uPwHist       userPswdHistory     string
m_uHosts        userHosts           string
#
# these are inputs that are part of the model, but not part of the table
# these are usually interim form inputs used to collect pieces of what
# ultimately is inserted into a single field
#
i_uPhoneA       -                   -           req, n, len=3
i_uPhoneP       -                   -           req, n, len=3
i_uPhoneN       -                   -           req, n, len=4
i_uPhoneX       -                   -           n
}}

This file serves to map form inputs to table fields. The form input names are also the working Lasso variable names for each field which are used instead of field tags. This allows the table field name to change without changing the display code or even the logic code in many cases.

This file also serves to define field data types. During table reading/writing there's a chunk of code that maps the db's field types to Lasso data types and performs type casting in both directions. This is also used to modify queries to handle situations like databases that don't accept numeric types being quoted.

Finally, this config file serves to define basic input validation needs. This system centralizes validation rules so that they are always applied uniformly. You'll notice the file includes the actual table fields and also any interim inputs which may be used to capture data before being munged into a single value ultimately headed to the table (such as a multi-field input for a phone number which is ultimately converted to a single value before being stored).

This config table is loaded and stored as a global variable object, so parsing happens only once.

The next component of the database API is the query object. The object mostly correlates to a table, but it is more of a query interface than a strict representation of a single table. The query type can be used as a subclass to provide the foundation for a more specific object (with query methods ready to go), or as an instance.

A query object is created like this:

var:'myObject' = (fwp_recordData:'fwpuserauth');

where fwpuserauth is the name of table (which you'll notice correlates to the file name of the sample config file above). If it is necessary to abstract the code in the application from the actual table names, then the system allows for a simple map like this to be defined:

var:'fw_gTables' = (map:
   'userauth'        = 'fwpuserauth_rev19',
   'userprivileges'  = 'fwpuserprivileges_rev15',
   'userfilters'     = 'fwpuserfilters_rev16',
   'userprofile'     = 'fwpuserprofile_rev17',
   'reference'       = 'fwpreference_rev4',
   'features'        = 'fwpdbcomponents_rev2');

where the key is the abstracted name, and the value is the real table name. Now, a table query object can be created like this:

var:'myObject' = (fwp_recordData:'userauth');

The presence of the $fw_gTables var is tested. If a matching key is found, the value is used as the table name. If one is not found, the parameter is assumed to be a table name itself. The ->onCreate tag will do a lazy load of the table adaptor. If the config file has not yet been loaded, it will be loaded on demand at this point, so there is no need for the developer to verify that a table config has already been loaded.

Now that we have our object in the form of $myObject we can use any of these member tags:

->sql                 passes a complete query string through
->getRecordWithLock   loads a record, locks it if not already locked, 
                      then creates the $m_ model vars
->updateUsingLock     updates a previously locked record
->updateUsingKeyVal   updates by any key pair, but will abandon 
                      if a lock is detected
->update              updates by any key pair, ignores lock fields
->deleteUsingLock     deletes a previously locked record
->deleteUsingKeyVal   deletes by any key pair, but will abandon 
                      if a lock is detected
->delete              deletes by any key pair, ignores lock fields
->select              builds select query using multiple automated
                      options including op codes
->add                 builds insert query using automation options
->duplicate           duplicates an existing record

Insert and update queries are automatically constructed from form submissions, and the process has a security filter to accept only those inputs which are defined as a part of the original form. This provides the security of hand-coded queries and the productivity of automated queries. The various ->update options and ->add will automatically process a validation rules file and will abandon the query if the validation fails.

Each query method has several options to allow the developer maximum flexibility in the query. Individual components of the query can be automated or hand coded. The ->sql method provides the ultimate in hand coding freedom, and still benefits from the built-in logging and error handling.

The ->select method offers flexibility in the returned record format. If a single record is returned, the fields are returned as instance vars to the object. If the -withMakeVars option is specified, the field are returned as individual page vars. For multiple records, the default is to create a named inline. With the -withRecordsMaps or -withRecordsArrays parameter, the records are returned in the instance var ->'records' as an array of maps or an array of arrays which can be iterated. In all cases, the fields are referred to by their inputName from the tableModel mapper.

The final component of the database API is what are called adaptors. A database adaptor is a ctype which provides the SQL-specific syntax for a given database server. The fwp_recordData table query object is written to govern logic, but is ignorant of exact query syntax. Connectors are singleton ctypes loaded at startup, and all are written to the same interface to integrate with the table query ctype automatically.

The adaptor layer allows PageBlocks applications to be written using any database that Lasso can connect to, and in fact allows multiple databases within the same application (logging can be MySQL while the app primarily uses Oracle or whatever). Personally, I haven't used anything but MySQL, so, I can't say it's perfect yet for Postgres, MS SQL Server and Oracle, but the n-tier style layering is in place. Many PageBlocks application queries will be perfectly abstracted and not need rewritten to switch databases, but of course if the developer created any db-language specific queries, they will likely need updated.

The database API doesn't automatically handle relational queries yet (but I'm close to having that worked out), but using the parameter options for the ->select method, the developer can actually craft relational queries using the table query object so that related table results are returned within the object. Related updates, inserts, and deletes aren't yet in the scope of what the code can handle. Still, I find the vast majority of my application queries (especially for admin systems) are handled by this API (which is nice because of the logging, error handling, and validation integration).

One more thing. If the ctype is subclassed, the developer can of course create object-specific instance vars and methods. If a method called ->validate is included, the query object will automatically call that method just after it has done its own validation of the basic input rules found in the {file_tableModel} mapper. This is where the developer can include business rules or other processing requirements that manipulate or verify input data before it is stored in the model or saved to the database.