Connect SQL to XUL





Connect SQL to XUL

Use Mozilla technologies to send and retrieve SQL queries.

One of the most powerful uses of the Mozilla technologies is when they are combined with other web technologies to produce a complete client/server solution. This hack combines Mozilla's XML interface language XUL with RDF, PHP, and a MySQL database. This is an advanced hack, and you need to be comfortable with extensions and packages before the whole thing will work for you. Get comfortable with those technologies first.

In a nutshell, we will enter an SQL query in a Firefox form. This will send the request to a PHP script running on a remote server. The PHP script will run a query against the MySQL database, converting the output into an RDF document. This document will be returned to Firefox, where XUL will use the RDF to display the results of the query. Figure shows the input and output displayed when the roundtrip from user to database to user is complete.

MozSQL, showing the results of an SQL query


Get Ready for Dynamic XUL

Every part of the Firefox interface is created using XUL [Hack #68] . We use it to create entire application interfaces. Whether it is bookmarks or toolbars, menus or buttons, in Firefox, XUL is ultimately responsible.

Most XUL can be created upfront and hardcoded. However, some XUL needs to be built dynamically; bookmarks are a good example of XUL that can't be hardcoded. To this end, XUL supports templates [Hack #70] : dynamic portions of a XUL document that are populated at runtime, based on some external XML data.

The XML data that Firefox is most concerned with is RDF. To recap, RDF is concerned with making statements about things. Each statement is comprised of three pieces of information: the thing we're making the statement about, a property of the thing, and the value of that property. In RDF parlance, this is called a triple, and the thing, property, and values are called subject, predicate, and object, respectively. For example, Firefox Hacks (the thing) has an author (the property) of Nigel McFarlane (the value). In RDF, we would write this statement roughly as follows (but this is just illustrative):

<rdf>
  <Description about="FirefoxHacks">
    <author>Nigel McFarlane</author>
  </Description>
</rdf>

This is an RDF triple, which an XUL template uses to build XUL dynamically. When the RDF changes, so does the XUL document.

Enough of concepts; start by creating a mozsql chrome package [Hack #86], including modifying installed-chrome.txt, and create the new package's standard contents.rdf file. We'll use a straightforward directory structure, not a JAR file. There's no extensions, overlays, themes, locales, or skins; we just want the chrome://mozsql URL to be working so that our XUL code can run in a fully secure environment.

Make an XUL Template

Here is the XUL document we will use to create and then render the RDF returned by our PHP script. This goes inside the mozsql chrome package that you need to create to run XUL content as chrome. Call it mozsql.xul, and put it in the content subdirectory of the package:

<?xml version="1.0"?>
<?xml-stylesheet href="chrome://mozsql/content/global.css" type="text/css"?>
<page id="MozSQL"
      xmlns="http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul">

  <script type="application/x-javascript" src="chrome://mozsql/content/
   mozsql.js"/>

  <description id="lblTitle" value="MozSQL :: A Firefox, PHP and MySQL
   Hack"/>
  <hbox>
    <textbox id="tbQueryString" multiline="true" value="SELECT * from USER"/>
    <vbox>
      <button id="cmdQuery" label="Query" tooltiptext="Execute SQL Query"
              oncommand="doQuery( );"/>
    </vbox>
  </hbox>

  <vbox id="vbResults" datasources="rdf:null" ref="urn:mozsql">

The next bit of code, which immediately follows the preceding set, deals with the XUL template. Only a part of the document is dynamic; the remainder (above) can be static:

    <template>
      <rule>
        <conditions>
          <content uri="?uri"/>
          <member container="?uri" child="?row"/>
          <triple subject="?row"
                  predicate="http://www.example.com/mozsql#value"
                  object="?value"/>
        </conditions>
        <action>
          <hbox col="?value" uri="?row" class="hbCell">
            <label value="?value" crop="end"/>
          </hbox>
        </action>
      </rule>
    </template>
  </vbox>
</page>

The conditions tag looks for the RDF statements that match the values we're interested in. The action tag is the XUL produced when our RDF statement matches the condition.

We've also created some pretty CSS style information for this document. It is decorative, not functional. Call it global.css and put it next to the mozsql.xul (it's not a theme or skin):

@namespace url("http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul");

#MozSQL    {
    background-color: -moz-Dialog;
    color: -moz-DialogText;
    font: message-box;
    padding: 30px;
}
#lblTitle    {
    font-size: 150%;
    padding-bottom: 20px;
}
#tbQueryString    {
    width: 500px;
    height: 75px;
}
#cmdQuery    {
    height: 30px;
    width: 75px;
}
.hbCell > label        {
    border-right: 1px solid #999999;
    width: 75px;
    padding: 3px;
}
vbox hbox[id^="rdf"] > label    {
    max-width: 15px;
}
vbox > hbox[col="0"]    {
    margin-top: 20px;
    background-color: #E4E4E4;
    border-bottom: 1px solid #999999;
    font-weight: bold;
}

And finally, here's the included script that adds a datasource to the template and fills it with data from the server. Call it mozsql.js and put it in the same folder as the other files:

function doQuery( )        {
  //the template root
  var dsElem = document.getElementById( "vbResults" );

  // the HTTP GET request
  var tbQuery = document.getElementById( "tbQueryString" );
  var dsURL = "http://www.example.com/MozSQL/mozsql.php?query=" +tbQuery.value;

  // Quick hack to prevent composites, comment out to aggregate results
  dsElem.setAttribute( "datasources", dsURL );
    
  // Create, Init and add a CompositeDatasource to the dsElem 
  var cd = "@mozilla.org/rdf/datasource;1?name=composite-datasource";
  var dsCom = Components.classes[cd].createInstance( );

  var dsItem = dsCom.QueryInterface(
       Components.interfaces.nsIRDFCompositeDataSource );
  dsElem.database.AddDataSource( dsItem );   

  // Now get the RDF service 
  var = rs "@mozilla.org/rdf/rdf--service;1";
  var rdfSvc = Components.classes[rs].getService( );
  var rdfItem = rdfSvc.QueryInterface( Components.interfaces.nslRDFService );

  // Run the HTTP GET request and add the server-supplied response data
  var ds = rdfItem.GetDataSource( dsURL );
  dsItem.AddDataSource( ds );
}

When the button is clicked, the tbQueryString string is retrieved from the text box and appended to our remote URL under the HTTP GET variable query. Next, we get the element that holds the template root (the <vbox id="vbResults"> element) and add the remote URL to it.

The remainder of the code is the RDF content generated by the server. We use the following PHP script to create an instance of an RDF datasource, add it to our <vbox> element, and then call the RDF service to handle this and call GetdataSource( dsURL ); to retrieve the remote document from the PHP script. This script is stored on the www.example.com server (change this to suit yourself) in the file MozSQL/mozsql.php:

<?php
  define( 'HOSTNAME', 'localhost' );
  define( 'DATABASE', 'mysql' );

  // Mozilla expects RDF to be returned as XML
  header( 'Content-Type: application/xml' );

  if ( !isset( $_GET ["query"] ) )
    exit;
  $query = addslashes( $_GET ['query'] );        
  $username = 'username';
  $password = 'password';

  @ $conn = mysql_pconnect(HOSTNAME, $username, $password);
  if ( !$conn )    {
    exit;
  }
  mysql_select_db( DATABASE, $conn );
  $results = mysql_query( $query ) or die( mysql_error( ) );
  if ( !$results  ||  mysql_num_rows( $results ) < 1 )
    exit;
  // Write out the RDF preamble
  echo "<?xml version=\"1.0\"?>\n".
     "<rdf:RDF xmlns:rdf=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#\""
       ." xmlns:sql=\"http://www.example.com/mozsql#\">\n".
     "<rdf:Seq rdf:about=\"urn:mozsql\">\n".
       "<rdf:li>\n";
    
  $row = mysql_fetch_array( $results, MYSQL_ASSOC );
  $row_keys = array_keys( $row );    
  $row_count = mysql_num_rows( $results );
  $col_count = count( $row_keys );
  mysql_data_seek( $results, 0 );                // Reset results var
    
  for( $i = 0; $i < $row_count; $i++ )        {  // Iterate over each row
    $row = mysql_fetch_array( $results );
    echo "<rdf:Seq sql:value=\"" .$i ."\">\n";
    for( $j = 0; $j < $col_count; $j++ )  { // Iterate over each column in row
      echo "<rdf:li rdf:resource=\"urn:row" .$i .":col" .$j ."\"/>\n";
    }
      echo "</rdf:Seq>\n";
  } 
  echo "</rdf:li>\n</rdf:Seq>\n";
  mysql_data_seek( $results, 0 );
  // Write out Column headers (keys) first
  for( $j = 0; $j < $col_count; $j++ )          {      
    echo "<rdf:Description rdf:about=\"urn:row0:col" .$j 
          ."\" sql:value=\"" .$row_keys[ $j ] ."\"/>\n";
  }
  for( $i = 0; $i < $row_count; $i++ )     {      // Write remainder items out
    $row = mysql_fetch_array( $results );
    for( $j = 0; $j < $col_count; $j++ )        {
      echo "<rdf:Description rdf:about=\"urn:row" 
             .$i .":col" .$j ."\" sql:value=\"" .$row[ $j ] ."\"/>\n";
    }
  } 
  echo "</rdf:RDF>";        // Close root RDF element
  mysql_free_result( $results );
?>

You, of course, will need to set your own host, database, username, and password. Finally, here is an example of the RDF we expect the PHP script to produce:

<?xml version="1.0"?>
<rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#
         xmlns:sql="http://www.example.com/mozsql#">
    <rdf:Seq rdf:about="urn:mozsql">
        <rdf:li>
      <!-- This is called an 'anonymous resource', 
                as it has no explicit rdf:uri -->
            <rdf:Seq sql:value="1">
       <!-- This ListItem points to the item with the same URI,
                     this is called an 'assertion' or 'arc' -->
                <rdf:li rdf:resource="urn:row1:col1"/>
                <rdf:li rdf:resource="urn:row1:col2"/>
            </rdf:Seq>
       <!-- Next row of data -->
            <rdf:Seq sql:value="2">
                <rdf:li rdf:resource="urn:row2:col1"/>
                <rdf:li rdf:resource="urn:row2:col2"/>
            </rdf:Seq>
        </rdf:li>
    </rdf:Seq>

    <!-- These predicates hold the actual data (rdf object) -->
    <rdf:Description rdf:about="urn:row1:col1" sql:value="Col1"/>
    <rdf:Description rdf:about="urn:row1:col2" sql:value="Col2"/>
    <rdf:Description rdf:about="urn:row2:col1" sql:value="Col1"/>
    <rdf:Description rdf:about="urn:row2:col2" sql:value="Col2"/>
</rdf:RDF>

Weigh It Up

This is a rough-and-ready example of what you can achieve by combining XUL and other web technologies alongside remote RDF. With the addition of other XUL elements, such as a drop-down list to select databases and other text boxes for usernames and passwords, it doesn't take much imagination to see that this isn't far off from a Mozilla-based MySQL frontend management utility.

One of the coolest things about RDF is a feature called aggregation, where multiple RDF sources are combined as if they were one. Comment out the bolded line in the previous JavaScript lists and see what happens; XUL templates can do operations similar to SQL's JOIN or UNION. Imagine now, instead of adding just a single remote URL, we add another, but this time to a different host, perhaps a Windows server running Microsoft SQL. Now, without that bold line, the remote data from these two hosts is aggregated, so we can effectively query and combine data from both hosts in real time! The cost of proprietary software to achieve this type of solution can leave the bank manager running for cover.

Neil Stansbury


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows