Circuit Maker - Fusebox Smart Query

This page describes the new and improved query system that was developed in the Spring of 2007 for use at Campus Life Tech Support located in Illinois State University. This system integrates query naming, organization, debugging, and control into the Fusebox Framework.

This new system can be seen in the Mailfwd Application and in the Employee Circuit of NOPS (under the tag folder). Circuit Maker generates code for applications that uses the new method described below in Section 2.

1 The Old Method
2 The New Method: Fusebox Smart Query

The Old Method

Overview

This method handles SELECT, INSERT, UPDATE, and DELETE queries in a similar fashion. All queries are stored in files that exist in the model circuits. All query files are named in a similar fashion...

qrySelectEmployee.php
qryInsertEmployee.php
qryUpdateEmployee.php
qryDeleteEmployee.php

It is assumed that 'Employee' is the table that is being queried in each case above. In most cases, these query files would be located in a model circuit called 'Employee' The resultant redundancy/repetition (m_employee.qrySelectEmployee) is fixed in Fusebox Smart Query. For SELECT, INSERT, UPDATE, and DELETE queries, all the query executions, error checking, and message pushing are handled directly by each file.

INSERT, UPDATE, and DELETE queries

INSERT, UPDATE, and DELETE queries work in a similar fashion. See the code below...

qryDeleteEmployee.php

 <?
 $query = "DELETE FROM announcement WHERE announcement_id = '" . $attributes['announcement_id'] . "'";
 $rsDeleteAnnouncement = $db->query($query);
        
 if($rsDeleteAnnouncement) {
        $errors->pushError("Successfully deleted Announcement. ", 0);
 }
 else {
        $errors->pushError("Unsuccessfully deleted Announcement. " . $contactInfo, 100);
 }
 ?>

This query would be called in the control circuit in a manner that the reader would expect

circuit.xml.php

<do action="m_employee.qryDeleteAnnouncement" />

Code similar to the above example would be used for INSERT and UPDATE statements as well. The only differences would be the error message that is pushed and the actual query statement.

SELECT Queries

The SELECT files operate in a very different fashion. There are far more SELECT queries in an application than INSERT, UPDATE, and DELETE queries. Therefore, the organization inside the SELECT file is more structured. Inside a SELECT file there is a switch statement that separates all the queries. This means that a variable must be used to tell the switch statement which query to run. The $qryAction variable was created for this purpose. This variable is set in the control circuit via the 'set' Fusebox tag. Once the switch statement knows which query to run, it assigns $query to equal the query statement and runs the query at the bottom of the file. The results are stored in a customized variable. This variable includes the name of the primary table that is used in the FROM clause of the query. For example, if we were querying from the Employee table, then our results would be stored in a variable called $rsSelectEmployee. This name is hard coded into each SELECT file in every model circuit.

There is a problem with this method. What if the developer wanted to run two queries from the same model circuit in one control fuseaction? The first query result would be overwritten by the second query result. So, the $qryVar variable was introduced to allow the developer to manually override the resultset name. This variable is set in Fusebox in the control circuit right before the do action tag is executed for the SELECT query. So if $qryVar equaled 'Emp' and the developer ran the SELECT query, then the results of the query would be stored in $Emp. The sample code below shows this process from start to finish.

control circuit

<fuseaction name="list">
   <set name="qryAction" value="byId" />
   <do action="m_employee.qrySelectEmployee" />

   <set name="qryVar" value="EmployeeList"
   <set name="qryAction" value="all" />

   <do action="m_employee.qrySelectEmployee" />

   <do action="v_employee.dspEmployeeInfo.php contentvariable="content['main']" />
</fuseaction>

qrySelectEmployee.php

<?
$qryWhat="";
$qryWhere="";

switch($qryAction)
{
  case "all":
    $qryWhat ="*";
    $qryWhere = "";     
    break;

  case "byId":
    $qryWhat ="*";
    $qryWhere = "WHERE employee_id = '" . (int) $attributes['announcement_id'] . "'";
    break;
}

$query = "SELECT $qryWhat FROM employee AS e $qryWhere";
 
if (isset($qryVar) && $qryVar != "") {
  ${$qryVar} = $db->query($query);
}
else {
  $rsSelectAnnouncement = $db->query($query);
}

unset($qryWhat, $qryWhere, $qryAction, $qryVar, $query);
?>

dspEmployeeInfo.php

Single employee
<? 
foreach($rsSelectEmployee->data as $EmployeeById)
{
  //code
} 
?>

Employee List
<? 
foreach($EmployeeList->data as $list)
{
  //code
} 
?>

Confused? Good. On to the new method.

The New Method: Fusebox Smart Query

Overview

This new method, referred to as FSQ (Fusebox Smart Query), revolves around sub-circuits. All queries are organized into sub-circuits. This is very important. Every table in the database must have a corresponding model circuit in the application. In each of these model circuits, there will be four sub-circuits, named 'select', 'insert', 'update', and 'delete'. For example, the circuit and sub-circuits for an "employee" table would be:

fusebox.xml.php

<circuit alias="m" path="_model/" parent=""/>                        
  <circuit alias="m_employee"     path="_model/employee/"     parent="m" />
    <circuit alias="m_employee_select"     path="_model/employee/select/"     parent="m_employee" />
    <circuit alias="m_employee_insert"     path="_model/employee/insert/"     parent="m_employee" />
    <circuit alias="m_employee_update"     path="_model/employee/update/"     parent="m_employee" />
    <circuit alias="m_employee_delete"     path="_model/employee/delete/"     parent="m_employee" />

file structure

-application/
   -control/
   -model/
       -employee/
           -select/
               byId.php
               bySemesterId.php
               ...
               circuit.xml.php
           -insert/
               record.php
               circuit.xml.php
           -update/
               byId.php
               circuit.xml.php
           -delete/
               byId.php
               circuit.xml.php
        (additional act files)
        circuit.xml.php
  -view/

INSERT, UPDATE, and DELETE Queries

First, let's discuss the INSERT, UPDATE, and DELETE queries. These query circuits are not automated. This means that it is up to the developer to write the query, query execution code, and error messages for each file in the INSERT, UPDATE, and DELETE sub-circuits. These queries therefore work a lot like the old method, except that the sub-circuit name tells you whether it is insert, update, or delete rather than the fuseaction name...

<do action="m_employee_insert.record" />

At the bare minimum the delete sub-circuit will have a 'byId.php' query file, the update sub-circuit will have a 'byId.php' query file, and the insert sub-circuit will have a 'record.php' query file. If a fuseaction is called "byId", you can assume it performs its action based on the primary key of the circuit's table (in our example, the employee.employee_id field).

SELECT Queries

In contrast, the SELECT queries behave very differently from the old method. It behaves differently because we have introduced a new model circuit called '_query'. This circuit is important because it is used by all 'select' sub-circuits to run their queries. Every single 'select' query that the web application executes is piped to this circuit and executed, and the results are put into a uniquely named variable. But, we are getting ahead of ourselves...

To add a new SELECT query to the application, the follow questions must be answered...

What should the query be named?
Where should the query go?

The developer must first correctly name the query. Most of the time, this is very easy. If the employee table is being queried for an employee by the employee's id, then the query would be named 'byEmployeeId.php'. If the developer wanted all the employees for a semester, then the query would be named 'bySemesterId.php'. If multiple tables need to be joined, then the developer must find the least common denominator of the query by working up through the JOIN statements and finding the first limiting condition. If this name gets out of control (extremely long), the developer can use the '_' character to insert a name that does not follow the above conditions. See the example below...

//before
<do action="m_employee_select.byEmployeeIdbyNationalitybyEmailAddress" />
//will look for a file named 'byEmployeeIdbyNationalitybyEmailAddress.php' 
 
//after
<do action="m_employee_select.byId_CustomList" />
//will look for a file named 'byId_CustomList'

The developer must correctly place the query in the application. Most of the time, this is very easy. If the employee table is the only table being queried, then the query is placed in the select sub-circuit under m_employee (m_employee_select). If multiple tables are joined, then the developer must figure out which table is the most important and place the query in that table's 'select' sub-circuit.

Of course, the developer must add the new fuseaction to the circuit.xml.php in the proper select sub-circuit.

Ok! Our 'select' fuseaction has been named and correctly placed in the application. Now we can fill the file with the query. We will assume that the query is named 'byId.php'. Only two things go in this file, see below...

byId.php

<?
$qryWhat = "*";
$qryWhere = "WHERE employee_id = '" . (int) $attributes['employee_id'] . "'
    ORDER BY employee_last_name";
?>

That's all! The _query model circuit will run the query and store the results automatically. To use the new query, the just call it from a control fuseaction as usual. For example:

<do action="m_employee_select.byId" />

Automation is Awesome

Overview

When this query is executed, the sub-circuit circuit.xml.php file enters the picture. This is a very important file as it contains a prefuseaction and a postfuseaction. The prefuseaction is designed for debugging purposes and the postfuseaction is designed for query processing. The developer does not need to touch the postfuseaction.

The Prefuseaction

The prefuseaction in the 'select' sub-circuit is powerful. This fuseaction controls all aspects of the query. Below is what the prefuseaction contains...

 <prefuseaction>
     <set name="short_name" value="e"/>

     <set name="rsDefault" value="Employee"/>
  
     <set name="show_query" value="true"/>
     <set name="show_resultSet" value="false"/>
     <set name="run_query" value="true" /> 
 </prefuseaction>

$short_name and $rsDefault are both optional variables. These variables are only required when the table name is more than one word. When the table name is more than one word, then the automated query process doesn't know what the table alias should be or where the beginning of each word in the table is. When this situation occurs the developer must manually override the automation by assigning these variables values.

$show_query, $show_resultSet, and $run_query all are used for debugging purposes. All these variables can be set to true or false and do exactly what their names describe. It is worth mentioning that setting these variables in the prefuseaction will affect all the queries in the sub-circuit. They can also be set in an individual fuseaction instead, in which case they would affect only that query.

The Postfuseaction

The 'select' sub-circuit postfuseaction runs the query and stores the results in a variable. Result set variables ($rs) are automatically named in the following manner...

$rsSelect[table name]_[fuseaction name]

so...

$rsSelectEmployee_byId

..is how a developer would access the results from the byId.php query. This is accomplished by have the postfuseaction store the fuseaction and the circuit name of where a SELECT query is located. Then it is directed to global SELECT query execution code in the '_query' circuit. See the code below...

<postfuseaction>
   <set name="query_circuit" value="{$myFusebox['thisCircuit']}"/>
   <set name="query_name" value="{$myFusebox['thisFuseaction']}"/>
   <do action="_query.runQuery"/>
</postfuseaction>

This result set name is automatically generated from the model circuit ('m_employee_select') and the sub-circuit fuseaction for each query ('byId'). Using this automation helps prevent the results of multiple select queries from overwriting each other as would happen in the old method. Developers can run multiple SELECT queries from the select sub circuit in the employee circuit in a control fuseaction without overwriting anything.

If by chance the developer wants to run two 'byId' queries in the same control fuseaction, the _query.runQuery fuseaction will not overwrite the old result set. Instead, it will warn the user (via the Errors Class). To get around this, a manual override of the result set variable name is provided. Simply set the variable 'rsCustom' in the control circuit before calling the second query. This query will then be stored in the variable named as the value of 'rsCustom'. See below...

...
<do action="m_employee_select.byId" /> <!-- results stored in $rsSelectEmployee_byId -->
 
<set name="rsCustom" value="AnotherEmployee" />
<do action="m_employee_select.byId" /> <!-- results stored in $rsSelectAnotherEmployee_byId -->

...

Circuit Maker - Fusebox Smart Query

Contents

The Old Method

Overview

INSERT, UPDATE, and DELETE queries

SELECT Queries

The New Method: Fusebox Smart Query

Overview

INSERT, UPDATE, and DELETE Queries

SELECT Queries

Automation is Awesome

Overview

The Prefuseaction

The Postfuseaction