لطفا منتظر باشید ...
Hack 72. 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 6-15 shows the input and output
displayed when the roundtrip from user to database to user is
complete.
Figure 6-15. MozSQL, showing the results of an SQL query
6.16.1. 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.
6.16.2. 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="/image/library/english/10055_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=" 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:///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:///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
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:///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:///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>
6.16.3. 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
