BatCave: Queries and Templates

Behind the Scenes - Queries and Templates

The Basic Data Views section showed us different default views of the database data.  How were these views defined?  What data was used to display this information?  Step into the Admin office to learn more about the Queries and Templates used here.

For every data view we saw in the aforementioned section, there was an entry in the Queries table.  This entry defines several key pieces of information used in the data display.

As show in the image above, we have queries that have names like Jobs_Current, Jobs_Error, Jobs_Recent or Slots_Active. The second half of the query name is used to define the query on the second row of the navigation bar.


The three templates defined in each query are referenced in the Templates table.  Each record in the Templates table consists of the following information:

A Closer Look - The Jobs_Current Query and Templates

Let's take a closer look at one of the default views, the Current Jobs query.  As a refresher, let's see what this might look like on the screen.

Now let's look in detail at the Jobs_Current query

Field Contents Comments
queryid 21 Used internally.  Generally not important to the user.
query Jobs_Current The query name, specified as Section_Name.
phpscript main.php This is the default page, main.php.
ldescript "Display a summary of jobs that are currently running or have errors." A long description of this query.
header_template job_pxSummaryHeader Contains the title and then a table definition with the column definitions of the rows to be displayed.
row_template job_pxSummaryRow Contains instructions for displaying every row of data
footer_template job_pxSummaryFooter Contains the table close tag (</table>).
sql SELECT jid,user,host,port,djid, filename, title, priority, 
UNIX_TIMESTAMP(spooltime) AS spooltime, 
UNIX_TIMESTAMP(starttime) AS starttime, 
UNIX_TIMESTAMP(statetime) AS statetime, 
UNIX_TIMESTAMP(donetime) AS donetime, 
UNIX_TIMESTAMP(deletetime) AS deletetime, 
numTasks, error, done,
whereclause WHERE numTasks AND (NOT deletetime) AND (NOT donetime)
orderby  ORDER BY spooltime DESC
visibility navbar This query is visible on the navigation bar.
collect jid,user,host,port,priority,title Used for job control.
rank 10  

OK, now let's have a look at the templates referred to in this query



templateID 65 Not generally useful to user.  Used in editing pages.
templateName job_pxSummaryHeader Template name in Section_Name format
templateCode <!-- Table Title -->
<div align="center">
<table class="titletable">
  <tr >
    <td width="100%"><h5 align="center">Alfred Jobs ($numrows)</h5></td>

<!-- Table Data-->
<table class="basictable" >
    <th> &nbsp; </th>
    <th align="left"><% sortURL($orderby,"user",$dir,"OWNER") %></th>
    <th><% sortURL($orderby,"spooltime",$dir,"SPOOLED") %></th>
    <th align="center"><% sortURL($orderby,"numTasks",$dir,"TASKS") %></th>
    <th><% sortURL($orderby,"priority",$dir,"PRI") %></th>
    <th align="left"><% sortURL($orderby,"title",$dir,"TITLE") %></th>




templateID 66 Not generally useful to user.  Used in editing pages.
templateName job_pxSummaryRow Template name in Section_Name format
templateCode <tr>
  <% pxPercentPieColumn($record[jid], $record[numTasks], $record[done], $record[error], $record[donetime], $record[deletetime]) %>
  <td align="left"><% dispatcherJobLink($record[user],$record[host],$record[port])%></td>
  <td align="center" class="nobreak"><% date('j M H:i',$record[spooltime]) %></td>
  <td align="center"><% pxElapsedTime($record[spooltime],$record[donetime], $record[deletetime])%></td>
  <td align="center"><% pxTaskCounts($record[jid],$record[numTasks],$record[done],$record[error]) %> </td>
  <td align="center">$record[priority]</td>
  <td align="left" width="60%"><a href="<% pxJobDetailLink($record[jid]) %>">$record[title]</a>&nbsp; &nbsp; $record[dispatchnotes]</td>




templateID 64 Not generally useful to user.  Used in editing pages.
templateName job_pxSummaryFooter Template name in Section_Name format
templateCode </table>


A Closer Look at the Row Template

The majority of the work in the SQL query is performed by the row template.  The row template has the responsibility of formatting each row of data.  Generally a row template is table-record tags <tr></tr>, with some number of table-descriptor tags, one for each colum of data.  Let's look at the row template:


The whole template is enclosed within the table record tags.  Now, lets look at one of the columns.  First lets start with one of the simple columns, the priority field:

<td align="center">$record[priority]</td>

When the inner loop on main.php operates it collects each row into an array variable called $record.  Each field that was selected the the SQL statement will be available as a value within that array.  The priority data is retrieved here with the $record[priority] syntax.  So, this whole entry here puts the priority value, as returned directly from the database, into one column in the returned table.

Now, let's look at one of the more complicated columns.  

<td align="center" class="nobreak"><% date('j M H:i',$record[spooltime]) %></td>

This is an example of calling a PHP function, directly from the template.  The tags <% %> are used to specify a PHP function, which will be used to format the data.  In this particular example the array $record[spooltime] contains a timestamp of when the job was spooled.  The date() function in PHP is used to format a date, using a format string to define how the date will display.

Let's look at one more column, which uses a custom function rather than a default PHP function:

<td align="left"><% dispatcherJobLink($record[user],$record[host],$record[port])%></td>

The dispatcherJobLink() function is part of the PHP code in the BatCave.  The function resides in batcave/funcs/utilityfuncs.php.  The function takes as its input the user name, the dispatcher host, and the port from the Job table.  The function creates special links that link off to the dispatcher, or back to the main query, but limit the row data to the specified user.


Pixar Animation Studios
(510) 752-3000 (voice)   (510) 752-3151 (fax)
Copyright © 1996- Pixar. All rights reserved.
RenderMan® is a registered trademark of Pixar.