BatCave: Digging Deeper - Extending the Cave


The data mining features of the cave have been designed to be easily extendable by the customer. We've seen a couple of examples of extending the view in the previous section.  In this section we're going to delve into the more advanced features for customizing. 


Adding a Custom Query from the SQL Expert View

In the Queries and Templates section, we learned that at the heart of every data view is a query, and we looked at the details of one of the standard queries. We also briefly looked at using the SQL Expert View to change the SQL where clause to modify an existing query.  Let's take another look at that interface.

As we learned earlier, we could easily change the where clause to affect the returned data, and by clicking on View Data, we would see the data affected by the new where clause.  At this point we could save it to our browser FAVORITES folder.  But, what if we wanted now to make this available to the entire facility?  That's where the other button comes in.  First of all, if you are not already viewing the Jobs queries, click on the Jobs link, followed by the Jobs_Current link.  Make sure that Show SQL Expert has been checked in the controls at the top of the view. Add AND title like '%mtor%'  to the where clause and click on the View Data button. When you do, you should see a same view as shown in the expert view above..  

Now, click on Create Custom Query.  If your system administrator has turned on password protection for this directory, you will get a login prompt.  If not, you will now see the Query editor. Let's take a look at this page now in sections.

All of the settings from the current query, including your where clause customizations have been transferred into the Query editor.  At this point, you really only need to change a single parameter, and that is the query title.  Query titles are always specified as Section_QueryName.    The section defines the navigation link, while the query name defines the name you will see on the second line of the navigation bar. To add this query to an existing section, simply give it a new name, like Jobs_MtorJobs,  and the Jobs navbar will change to reflect the new query.  That would produce a navigation bar which looks like this:

OK, that seemed to work.  But I'm not really happy with this change.  Why is my MtorJobs entry right next to current?  How can I change this.  Clearly clicking on Create Custom Query again wouldn't help. 


Editing Existing Queries

If you look closely at the navigation bar in the example 2 images above, where we were adding the new custom query, you will see that we were in the admin section.  Clicking on Admin and then on Queries in the navigation bar will take us to the query editor page.  A list of all of the current queries, alphabetical by section will be presented.

And if we look a little farther down that page, we'll see our MtorJobs query, in the Jobs section.

 

Editing the MtorJobs Query

Clicking on the query name, will take us back into the query editor, with all of the data from the selected query filled in.  Let's take a look at some of the other editor fields.  

One of the common desired changes is to change the position of the query in the navigation bar.  How is that accomplished?  Looking again at the navigation bar which is generated with our new query in place we see:

The query names on the second line of the navigation bar are sorted twice.  First by rank, which is defined in the query table, and secondly by name alphabetically.  So, let's have a closer look at the MtorJobs query.

We'll see that the current rank parameter for this query is 10.  This was because this query was based upon the Jobs_Current query.  Remember this query was created by modifying the whereclause on the Jobs_Current query, and clicking on Create Custom Query.  At this point the queries are identical, except for the query name.   The out-of-box defaults for the Jobs queries are Jobs_Current:10, Jobs_Error:8, Jobs_Recent:5, and all other jobs queries are zero.  By setting the rank value of our query to 0, and clicking on Update Query, we can see the new rank affect the sorting order on the navigation bar. 

MtorJobs, being at the same rank with LongRunning and LongWaiting, is then sorted alphabetically, and is placed last on the navigation bar. 

 In the edit window above, you see two other fields from the Queries table, namely visibility and collect. The visibility parameter determines whether or not this query will be displayed in the navigation bar.  Currently if the value is navbar the query will be visible.  If it is anything else, it will be ignored for the navigation bar, but can be used in query links in data output.

The collect values should not be changed for a Jobs_ query.  These values are collected in jobs queries and are used when controlling processes.

 

Creating a New Navigational Section

The Bat Cave is not limited to the query sections as shown on the first line of the navigation bar.  What if we were working on a special new project called Razzle!, and we want to create a series of queries that are specific only to that Razzle production?  Simple, as a start, let's re-edit this query name from Jobs_MtorJobs to Razzle_Dazzle.  This probably isn't really the query we would use, but let's give it a try anyway.  Simply changing the name now results in the following navigational changes.

Creating additional queries with this section name allows you to build a whole set of queries which are tailored to a specific interest area.


Changing the Data Display

What you see in the query display is based upon two factors. (1) what data is being retrieved by the query, and (2) the HTML templates used in the data display.  These values are also defined in the query table, and are edited in the same fashion in which we've changed the query name and rank values.  So, let's continue to look at our query, which is now named Razzle_Dazzle!

For now let's concentrate on the top four parameters.  This includes the PHP script which is invoked when this query is selected, and the three HTML templates, header, row and footer. In most cases, using the default main.php page for data display is fine, and requires no additional development.  Because the three header templates already exist, you'll see links on the right of the entry widget which will take you to template editor for these specific templates.  Because we don't want to change the existing template, let's just enter new template names.  For the three template entries we will enter razzle_header, razzle_row, and razzle_footer and click update.

If we were to try to run this query right now, we would get a display with only the default header and footer, and no data.  That is because the three templates are not yet defined.  So, let's get on to making new templates.

 

Inserting New HTML Templates

In the navigation bar, click on ADMIN, and then Templates.  Very similar to the Queries editor, this takes you to the Templates editor, and you are presented with a list of Templates, alphabetically separated by section.

Instead of editing an existing template, we are going to edit a new template from scratch.  Click on the link Insert New Template. This brings up the template editor, but all data is blank.

Your screen should look that the portion above.  For right now we are going to create a trivially simple template which simply displays a couple columns of the Job data.  We're not even going to include headers in this first pass, just to keep it easy.  So, according to what we entered in the Razzle_Dazzle query, we need to create razzle_header, razzle_row and razzle_footer templates.  Let's start with the header template first.

and click the Insert Template button at the bottom of the templateCode entry box.  Great, that's the first part.  Now, let's do the footer template.  

and again insert this template.  As you can see, we've created just the basic HTML table entries for our header and footer.  Now, on to the interesting part.  When a query is run, it will return zero or more rows of data which match the SQL query specified in the Query editor. Generally (but not always), you will want to display each row returned from the SQL query as a row in the HTML row template.  If you look at the SQL query in the edit screen above,  you can see the fields which have been chosen in the SQL query.  Lets display three of these fields of data in this example, the jid (job ID field), user and title fields.  Each one of these fields will be in a separate column in the output display. 

As the data is returned from the SQL server, it is contained in an array variable called $record.    The values which we want to display are $record[jid], $record[user] and $record[title].  So, let's create the row_template now.  

and insert the new template.  OK, let's see what we get now.  We should be now looking at the Template list in the template editor.  We should see our three razzle entries defined.


 On the navigation bar, if we click on RAZZLE and then on the Dazzle query, we should see the following!

 

Editing the Razzle Template

Not too bad for our first attempt.  But, the data is mashed up against the left margin, and we don't have any column headers to help us understand what data we're seeing.  But we can fix that with a simple change to the header field.

As we had seen in previous pages, it might be nice to add sorting to the column headers.  Any PHP function can be embedded in the HTML template by enclosing it in <% %> characters.  We can cause the columns to having sorting links by using the sortURL function which is defined in the utilityfuncs.php file.  This function is called like this:

sortURL($current, $new, $currentdir, $title, $extra="")

For our case test case here, modify the razzle_header to be:

<table width="80%" align="center">
<tr>
  <th><% sortURL($orderby,"jid",$dir,"jid") %></</th>
  <th><% sortURL($orderby,"user",$dir,"user name") %></th>
  <th><% sortURL($orderby,"title",$dir,"job title") %></th>
</tr>

Now, all three column are sortable. 

 

Linking the data to other pages

OK, so we've got three columns of data, but we can't from this display get to any other information on these jobs.  There already is a template which gives a more detailed view of a specified job.  How could we link to that from either the jid column or the title column?

Well, the easy way to do this is to use the pxJobLinkInfo function, which is also defined in the utilityfuncs.php file.  Instead, just to make it more fun, let's write our own version of this function.  In the top level directory of the batcave, (where main.php exists), if there is a file called sitefuncs.php, it will be sourced.  This is a great place for customers to extend the available functions by adding their own.  If this file doesn't exist, then create it.  In the file putting the following PHP code

<?php

  function razzle_joblink($jid, $title) {
   return "<a href=\"main.php?query=Jobs_Detail&jid=$jid\">$title</a>";
  }

?>

Be absolutely certain that there are no spaces, or empty lines past the close ?>  characters.  Exta spaces here would cause all of your cookie settings to stop working! 

As you can see from the code, we have a function called razzle_joblink, which we pass the jid number, and a title for the link.  So, to make use of this new function, we are going to modify the razzle_row template.

And now when we view our Razzle Dazzle query, we see the following:

And now clicking on the jid is a link to a page which displays job details.  Let's give one more example of extending that.  Let's say this time, rather than just being able to click on the jid link, we want a link after the title, which reads (Job Details).  Simple. 

As you would expect, clicking on (Job Details) on any of the rows will take you to the same page as clicking on the jid link.  


Summary

The Bat Cave components are designed to be extended.  In fact, that is how we developed much of the Bat Cave.  The core structure was set in place, and we used the Queries and Template editors to develop the rest of the displays.  The table below lists a quick summary of what functionality can be extended, and where this would be accomplished

Functionality What To Change Where to Change It
New navigational query Add new query Queries Editor (Admin -> Queries)
New navigational section Add new query (new section name) Queries Editor (Admin -> Queries) - Add new query of format Section_Name
What data is returned from a query Change the SQL clause  Queries Editor (Admin -> Queries)
Limit the data this is returned from a query Change the SQL whereclause  Queries Editor (Admin -> Queries)
Which templates are used to display query data Change the SQL templates Queries Editor (Admin -> Queries)
Change how data is displayed using new HTML templates Create a new HTML Template Templates Editor (Admin -> Templates)
Create different types of display like special links or percentage meters Create new PHP functions which are called by HTML templates Put site PHP functions in sitefuncs.php, located in same directory as main.php
Use a different PHP page to display data which doesn't lend itself to main.php limitations. Edit the phpscript parameter in the Queries editor Queries Editor (Admin -> Queries)
Change which database is being used for templates Edit the $templates entries.  Located in either global.php, or if exists, global_overides.php will override any settings in global.php

 

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