1 <?php
2 /**
3 * XAO_DbToXml.php
4 *
5 * This script is pivotal to developers who wish to retrieve content from a
6 * relational database. It's primary function is to convert tabular results sets
7 * to XML format. It does more than that however, see the class doc comments for
8 * information on how to leverage it's awsome power.
9 *
10 * @author Terence Kearns
11 * @version 1.0 alpha
12 * @copyright Terence Kearns 2003
13 * @license LGPL
14 * @package XAO
15 * @link http://xao-php.sourceforge.net
16 */
17
18 /**
19 * Import the basic DomDoc class for inheritance.
20 *
21 * This class is based on DomDoc and hence supports all of it's functionality,
22 * including it's ability to be consumed by another DomDoc based object.
23 *
24 * @import DomDoc
25 */
26 include_once "XAO_DomDoc.php";
27
28 /**
29 * Convert a result table from an SQL query into basic XML.
30 *
31 * Very simple conversion of SQL query results table to XML data which may then
32 * be transformed into a suitable structure. Optionally more complext usage can
33 * include the use of callback functions through the optional arguments to the
34 * constructor. See the constructor method documentation for details. This class
35 * extends DomDoc and therefore inherits all DomDoc capabilities.
36 *
37 * @package XAO
38 */
39
40 class DbToXml extends DomDoc {
41
42 /**
43 * The stub node onto which the result nodes will be grafted.
44 *
45 * This is used by the execute() method to append child nodes to.
46 * Unless a stub node is specified in the constructor, it will be
47 * assigned the instance DomDoc::ndRoot
48 *
49 * @access public
50 * @var node
51 */
52 var $ndStub;
53
54 /**
55 * The name of the result element (root element).
56 *
57 * @access public
58 * @var string
59 */
60 var $strResEl = "result";
61
62 /**
63 * The name of the row element.
64 *
65 * @access public
66 * @var string
67 */
68 var $strRowEl = "row";
69
70 /**
71 * An instance of a PEAR DB result object.
72 *
73 * @access public
74 * @var object
75 */
76 var $objDBResult;
77
78 /**
79 * 2D array containing the rows and colums of the result returned from the
80 * passed DB result object.
81 *
82 * @access private
83 * @var object
84 */
85 var $arrResult = array();
86
87 /**
88 * Field-name/Element-name function mapping.
89 *
90 * This member is populated with the optional second argument to the
91 * constructor function.
92 *
93 * @access private
94 * @var array
95 */
96 var $arrCallBacks;
97
98 /**
99 * Constructor method
100 *
101 * Perform requirements checks and initialised resources.
102 *
103 * @param object An instance of a PEAR DB result object or a 2d
104 * associative array.
105 * @param array An associative array mapping result column names to
106 * methods (call back functions) of the class.
107 * @param object a reference to an existing DOM document
108 * @param object a DOM node in the referenced document where the results
109 * from this calss will be appended.
110 * @return void
111 */
112
113 function DbToXml(&$objDBResult,$arrCallBacks = array(),$objDocRef = null,$ndStub = null) {
114 $this->arrCallBacks = $arrCallBacks;
115
116 // run the parent constructor
117 if(is_object($objDocRef) && is_object($ndStub)) {
118 // check to see if this tree is being
119 // grafted onto an existing node.
120 $this->DomDoc($objDocRef,XAO_DOC_REFERENCE);
121 $this->ndStub = $ndStub;
122 }
123 else {
124 $this->DomDoc($this->strResEl);
125 $this->ndStub = $this->ndRoot;
126 }
127 // populate the 2d associative array
128 // that will be used to build our XML
129 if(is_array($objDBResult)) {
130 $this->arrResult = $objDBResult;
131 }
132 else {
133 if(DB::isError($objDBResult))
134 $this->Throw(
135 "DbToXml: The DB result object you passed to DbToXml "
136 ."returned an error:\n".$objDBResult->getMessage()
137 //.":\n".$objDBResult->getUserinfo()
138 ,$this->arrSetErrFnc(__FUNCTION__,__LINE__)
139 );
140 if(!isset($objDBResult->result))
141 $this->Throw(
142 "DbToXml: The DB result object you passed to DbToXml "
143 ."is not a valid DB query result.",
144 $this->arrSetErrFnc(__FUNCTION__,__LINE__)
145 );
146 // initialise resources
147 $this->objDBResult = $objDBResult;
148 // fetch the result data (list of rows)
149 // into an associative array.
150 while($this->arrResult[] = $this->objDBResult->fetchRow(DB_FETCHMODE_ASSOC));
151 array_pop($this->arrResult);
152 }
153 }
154
155 /**
156 * Convert the RDBMS data into XML elements.
157 *
158 * Run the fetch method on the result object to obtain the result data in a
159 * 2D array. Iterate over this 2D array to first obtain the rows and then the
160 * fields which are created and appended as elements using DOM XML methods.
161 *
162 * @return void
163 */
164 function execute() {
165 // work-around so name can be re-defined
166 // *after* the constructor.
167 if($this->strResEl != "result") $this->ndStub->set_name($this->strResEl);
168 // try to include a record of the SQL
169 // used to generate this fragment.
170 // THE FOLLOWING CODE DOESN'T WORK BECAUSE
171 // SOMETHIMES THE "LAST_QUERY" IS NOT THE
172 // ONE USED.
173 /*
174 if(isset($this->objDBResult->dbh->last_query)) {
175 $elQry = $this->objDoc->create_element("DbToXml_source_sql");
176 $ndQry = $this->ndStub->append_child($elQry);
177 $ndQry->set_content(trim($this->objDBResult->dbh->last_query));
178 }
179 */
180 // the following check is done outside the
181 // array looping to increase performance.
182 // performing the check for each field or
183 // each row is unnecesary if there are no
184 // callbacks. This raises _consistency_
185 // issues when maintaining two separate
186 // blocks of almost identical code.
187 // I would have added support for regular
188 // expressions but that would have proven
189 // too costly for performance.
190 if(count($this->arrCallBacks)) {
191 // iterate through the result list
192 foreach($this->arrResult AS $arrRow) {
193 // add a row element for each row in the
194 // result list
195 $elRow = $this->objDoc->create_element($this->strRowEl);
196 $ndRow = $this->ndStub->append_child($elRow);
197 $this->RowConstructor($this->objDoc,$ndRow);
198 // iterate through the fields in the row
199 foreach($arrRow AS $fieldName => $fieldVal) {
200 // DON'T CREATE EMPTY TAGS!
201 if(strlen($fieldVal) && !is_int($fieldName)) {
202 // add an element for each non-empty field
203 $elField = $this->objDoc->create_element($fieldName);
204 $ndField = $ndRow->append_child($elField);
205 // CHECK FOR CALLBACKS - this check impacts
206 // on performance - let alone execution of
207 // the requested function.
208 if(isset($this->arrCallBacks[$fieldName])) {
209 $funcName = $this->arrCallBacks[$fieldName];
210 $fieldVal = $this->$funcName($this->objDoc,$ndField,$fieldVal);
211 }
212 $ndField->set_content($fieldVal);
213 if(!$ndField->has_child_nodes()) $ndRow->remove_child($ndField);
214 }
215 }
216 $this->RowDestructor($this->objDoc,$ndRow);
217 }
218 }
219 else {
220 foreach($this->arrResult AS $arrRow) {
221 // add a row element for each row in the
222 // result list
223 $elRow = $this->objDoc->create_element($this->strRowEl);
224 $ndRow = $this->ndStub->append_child($elRow);
225 $this->RowConstructor($this->objDoc,$ndRow);
226 // iterate through the fields in the row
227 foreach($arrRow AS $fieldName => $fieldVal) {
228 // DON'T CREATE EMPTY TAGS!
229 if(strlen($fieldVal) && !is_int($fieldName)) {
230 // add an element for each non-empty field
231 $elField = $this->objDoc->create_element($fieldName);
232 $ndField = $ndRow->append_child($elField);
233 $ndField->set_content($fieldVal);
234 }
235 }
236 $this->RowDestructor($this->objDoc,$ndRow);
237 }
238 }
239 }
240
241
242
243 /**
244 * Group records in the output tree by element corresponding to column.
245 *
246 * This is a very handy way to quickly group output elements by a selected
247 * element.
248 *
249 * @param string name of the column used for grouping
250 */
251 function GroupBy($strColName) {
252 // to do
253 }
254
255 /**
256 * Clean up function to flush existing data.
257 *
258 * Calling this function is needed if the constructor is to be called more
259 * than once.
260 *
261 * @return void
262 */
263 function Reset() {
264 $this->arrCallBacks=null;
265 $this->arrResult=array();
266 $this->ndStub=null;
267 // now the constructor will need to be called again before this
268 // object instance can be used further.
269 }
270
271 /**
272 * Row Contructor
273 *
274 * This abstract method may be overwritten by the child class in order to
275 * perform an operation just _prior_ to the execution of a row in the
276 * execute() method of this class
277 *
278 * @param object a reference to the current PHP DOM XML object instance
279 * @param node a reference to the element node representing a row
280 * @access public
281 * @return void
282 */
283 function RowConstructor(&$objDoc,&$ndRow){}
284
285 /**
286 * Row Destructor
287 *
288 * This abstract method may be overwritten by the child class in order to
289 * perform an operation just _after_ the execution of a row in the
290 * execute() method of this class
291 *
292 * @param object a reference to the current PHP DOM XML object instance
293 * @param node a reference to the element node representing a row
294 * @access public
295 * @return void
296 */
297 function RowDestructor(&$objDoc,&$ndRow){}
298
299 /**
300 * unix timestamp date call-back mutator function
301 *
302 * This call-back function is placed here for convenience and can be used to
303 * produce an element with a more convenient schema for representing a date
304 * from a unix timestamp. It also provides a useful example of the call-back
305 * capability of this class. What it does is add a set of attributes to the
306 * element in context which each represent a conventional date component.
307 *
308 * @param object a reference to the current PHP DOM XML object instance
309 * @param node a reference to the element node representing a field
310 * @param string a copy of the text content that would normally be
311 * assigned to this field elemeent.
312 * @access public
313 * @return void
314 */
315 function unixTsToReadable(&$objDoc,&$ndField,$intTs) {
316 $intTs = (integer)$intTs;
317 if($intTs < 0) return;
318 $ndField->set_attribute("unixTS",$intTs);
319 $ndField->set_attribute("ODBCformat",date("Y-m-d H:i:s",$intTs));
320 $ndField->set_attribute("year",date("Y",$intTs));
321 $ndField->set_attribute("month",date("m",$intTs));
322 $ndField->set_attribute("day",date("d",$intTs));
323 $ndField->set_attribute("hour",date("H",$intTs));
324 $ndField->set_attribute("min",date("i",$intTs));
325 }
326
327 /**
328 * ODBC timestamp date call-back mutator function
329 *
330 * This call-back function is placed here for convenience and can be used to
331 * produce create a unix timestamp from an ODBC compliant timestamp. This
332 * unix timestap is then sent to $this->unixTsToReadable where nice
333 * attributes are added :)
334 *
335 * @param object a reference to the current PHP DOM XML object instance
336 * @param node a reference to the element node representing a field
337 * @param string a copy of the text content that would normally be
338 * assigned to this field elemeent.
339 * @access public
340 * @return void
341 */
342 function odbcToReadable(&$objDOM,&$ndField,$odbcTs) {
343 if(trim($odbcTs) == "") return;
344 // example of MS SQL select snippet producing expected format.
345 // CAST(DATEPART(yyyy,myDate) AS varchar(64)) + '-' +
346 // CAST(DATEPART(mm,myDate) AS varchar(64)) + '-' +
347 // CAST(DATEPART(dd,myDate) AS varchar(64)) + ' ' +
348 // CAST(DATEPART(hh,myDate) AS varchar(64)) + ':' +
349 // CAST(DATEPART(n,myDate) AS varchar(64)) AS myODBCDate,
350 $arrTs = explode(" ",$odbcTs);
351 $arrDate = explode("-",$arrTs[0]);
352 $arrTime = explode(":",$arrTs[1]);
353 $year = $arrDate[0];
354 $month = $arrDate[1];
355 $day = $arrDate[2];
356 $hour = $arrTime[0];
357 $min = $arrTime[1];
358 if($year != 1900) {
359 $year = date("Y");
360 $unixTs = mktime($hour,$min,0,$month,$day,$year);
361 if($unixTs != -1) $this->unixTsToReadable($objDOM,$ndField,$unixTs);
362 return $odbcTs;
363 }
364 }
365 }
366
367 ?>
