root/fResult.php

Revision 562, 17.0 kB (checked in by wbond, 1 year ago)

BackwardsCompatibilityBreak - removed fORMSchema::enableSmartCaching(), fORM::enableSchemaCaching() now provides equivalent functionality. fSchema::setCacheFile() changed to fSchema::enableCaching() and now requires an fCache object. fSchema::flushInfo() renamed to fSchema::clearCache(). Fixed tickets #139, #144, #173 and #185. Added Oracle support and the fCache class. Added a whole slew of new database tests.

LineHide Line Numbers
1 <?php
2 /**
3  * Representation of a result from a query against the fDatabase class
4  *
5  * @copyright  Copyright (c) 2007-2009 Will Bond
6  * @author     Will Bond [wb] <will@flourishlib.com>
7  * @license    http://flourishlib.com/license
8  *
9  * @package    Flourish
10  * @link       http://flourishlib.com/fResult
11  *
12  * @version    1.0.0b3
13  * @changes    1.0.0b3  Added support for Oracle, various bug fixes [wb, 2009-05-04]
14  * @changes    1.0.0b2  Updated for new fCore API [wb, 2009-02-16]
15  * @changes    1.0.0b   The initial implementation [wb, 2007-09-25]
16  */
17 class fResult implements Iterator
18 {
19     /**
20     * Composes text using fText if loaded
21     *
22     * @param  string  $message    The message to compose
23     * @param  mixed   $component  A string or number to insert into the message
24     * @param  mixed   ...
25     * @return string  The composed and possible translated message
26     */
27     static protected function compose($message)
28     {
29         $args = array_slice(func_get_args(), 1);
30        
31         if (class_exists('fText', FALSE)) {
32             return call_user_func_array(
33                 array('fText', 'compose'),
34                 array($message, $args)
35             );
36         } else {
37             return vsprintf($message, $args);
38         }
39     }
40    
41    
42     /**
43     * The number of rows affected by an `INSERT`, `UPDATE`, `DELETE`, etc
44     *
45     * @var integer
46     */
47     private $affected_rows = 0;
48    
49     /**
50     * The auto incremented value from the query
51     *
52     * @var integer
53     */
54     private $auto_incremented_value = NULL;
55    
56     /**
57     * The character set to transcode from for MSSQL queries
58     *
59     * @var string
60     */
61     private $character_set = NULL;
62    
63     /**
64     * The current row of the result set
65     *
66     * @var array
67     */
68     private $current_row = NULL;
69    
70     /**
71     * The php extension used for database interaction
72     *
73     * @var string
74     */
75     private $extension = NULL;
76    
77     /**
78     * The position of the pointer in the result set
79     *
80     * @var integer
81     */
82     private $pointer;
83    
84     /**
85     * The result resource or array
86     *
87     * @var mixed
88     */
89     private $result = NULL;
90    
91     /**
92     * The number of rows returned by a select
93     *
94     * @var integer
95     */
96     private $returned_rows = 0;
97    
98     /**
99     * The SQL query
100     *
101     * @var string
102     */
103     private $sql = '';
104    
105     /**
106     * The type of the database
107     *
108     * @var string
109     */
110     private $type = NULL;
111    
112     /**
113     * The SQL from before translation - only applicable to translated queries
114     *
115     * @var string
116     */
117     private $untranslated_sql = NULL;
118    
119    
120     /**
121     * Sets the PHP extension the query occured through
122     *
123     * @internal
124      *
125     * @param  string $type           The type of database: `'mssql'`, `'mysql'`, `'oracle'`, `'postgresql'`, `'sqlite'`
126     * @param  string $extension      The database extension used: `'array'`, `'mssql'`, `'mysql'`, `'mysqli'`, `'pgsql'`, `'sqlite'`
127     * @param  string $character_set  MSSQL only: the character set to transcode from since MSSQL doesn't do UTF-8
128     * @return fResult
129     */
130     public function __construct($type, $extension, $character_set=NULL)
131     {
132         $valid_types = array('mssql', 'mysql', 'oracle', 'postgresql', 'sqlite');
133         if (!in_array($type, $valid_types)) {
134             throw new fProgrammerException(
135                 'The database type specified, %1$s, is invalid. Must be one of: %2$s.',
136                 $type,
137                 join(', ', $valid_types)
138             );
139         }
140        
141         // Certain extensions don't offer a buffered query, so it is emulated using an array
142         if (in_array($extension, array('oci8', 'odbc', 'pdo', 'sqlsrv'))) {
143             $extension = 'array';
144         }
145        
146         $valid_extensions = array('array', 'mssql', 'mysql', 'mysqli', 'pgsql', 'sqlite');
147         if (!in_array($extension, $valid_extensions)) {
148             throw new fProgrammerException(
149                 'The database extension specified, %1$s, is invalid. Must be one of: %2$s.',
150                 $extension,
151                 join(', ', $valid_extensions)
152             );
153         }
154        
155         $this->type          = $type;
156         $this->extension     = $extension;
157         $this->character_set = $character_set;
158     }
159    
160    
161     /**
162     * Frees up the result object to save memory
163     *
164     * @internal
165      *
166     * @return void
167     */
168     public function __destruct()
169     {
170         if (!is_resource($this->result) && !is_object($this->result)) {
171             return;
172         }
173        
174         if ($this->extension == 'mssql') {
175             mssql_free_result($this->result);
176         } elseif ($this->extension == 'mysql') {
177             mysql_free_result($this->result);
178         } elseif ($this->extension == 'mysqli') {
179             mysqli_free_result($this->result);
180         } elseif ($this->extension == 'pgsql') {
181             pg_free_result($this->result);
182         } elseif ($this->extension == 'sqlite') {
183             // SQLite doesn't have a way to free a result
184         }
185        
186         $this->result = NULL;
187     }
188    
189    
190     /**
191     * All requests that hit this method should be requests for callbacks
192     *
193     * @param  string $method  The method to create a callback for
194     * @return callback  The callback for the method requested
195     */
196     public function __get($method)
197     {
198         return array($this, $method);       
199     }
200    
201    
202     /**
203     * Gets the next row from the result and assigns it to the current row
204     *
205     * @return void
206     */
207     private function advanceCurrentRow()
208     {
209         if ($this->extension == 'mssql') {
210             $row = mssql_fetch_assoc($this->result);
211             if (!empty($row)) {
212                 $row = $this->fixDblibMSSQLDriver($row);
213             }
214                
215         } elseif ($this->extension == 'mysql') {
216             $row = mysql_fetch_assoc($this->result);
217         } elseif ($this->extension == 'mysqli') {
218             $row = mysqli_fetch_assoc($this->result);
219         } elseif ($this->extension == 'pgsql') {
220             $row = pg_fetch_assoc($this->result);
221         } elseif ($this->extension == 'sqlite') {
222             $row = sqlite_fetch_array($this->result, SQLITE_ASSOC);
223         } elseif ($this->extension == 'array') {
224             $row = $this->result[$this->pointer];
225         }
226        
227         // Fix uppercase column names to lowercase
228         if ($row && $this->type == 'oracle') {
229             $new_row = array();
230             foreach ($row as $column => $value) {
231                 $new_row[strtolower($column)] = $value;
232             }   
233             $row = $new_row;
234         }
235        
236         // This is an unfortunate fix that required for databases that don't support limit
237         // clauses with an offset. It prevents unrequested columns from being returned.
238         if ($row && ($this->type == 'mssql' || $this->type == 'oracle')) {
239             if ($this->untranslated_sql !== NULL && isset($row['flourish__row__num'])) {
240                 unset($row['flourish__row__num']);
241             }   
242         }
243        
244         // This decodes the data coming out of MSSQL into UTF-8
245         if ($row && $this->type == 'mssql') {
246             if ($this->character_set) {
247                 foreach ($row as $key => $value) {
248                     if (!is_string($value) || strpos($key, 'fmssqln__') === 0 || isset($row['fmssqln__' . $key]) || preg_match('#[\x0-\x8\xB\xC\xD-\x1F]#', $value)) {
249                         continue;
250                     }         
251                     $row[$key] = iconv($this->character_set, 'UTF-8', $value);
252                 }
253             }
254             $row = $this->decodeMSSQLNationalColumns($row);
255         }
256        
257         $this->current_row = $row;
258     }
259    
260    
261     /**
262     * Returns the number of rows affected by the query
263     *
264     * @return integer  The number of rows affected by the query
265     */
266     public function countAffectedRows()
267     {
268         return $this->affected_rows;
269     }
270    
271    
272     /**
273     * Returns the number of rows returned by the query
274     *
275     * @return integer  The number of rows returned by the query
276     */
277     public function countReturnedRows()
278     {
279         return $this->returned_rows;
280     }
281    
282    
283     /**
284     * Returns the current row in the result set (required by iterator interface)
285     *
286     * @throws fNoRowsException
287     * @throws fNoRemainingException
288     * @internal
289      *
290     * @return array  The current row
291     */
292     public function current()
293     {
294         if(!$this->returned_rows) {
295             throw new fNoRowsException('The query did not return any rows');
296         }
297        
298         if (!$this->valid()) {
299             throw new fNoRemainingException('There are no remaining rows');
300         }
301        
302         // Primes the result set
303         if ($this->pointer === NULL) {
304             $this->pointer = 0;
305             $this->advanceCurrentRow();
306         }
307        
308         return $this->current_row;
309     }
310    
311    
312     /**
313     * Decodes national (unicode) character data coming out of MSSQL into UTF-8
314     *
315     * @param  array $row  The row from the database
316     * @return array  The fixed row
317     */
318     private function decodeMSSQLNationalColumns($row)
319     {
320         if (strpos($this->sql, 'fmssqln__') === FALSE) {
321             return $row;
322         }
323        
324         $columns = array_keys($row);
325        
326         foreach ($columns as $column) {
327             if (substr($column, 0, 9) != 'fmssqln__') {
328                 continue;
329             }   
330            
331             $real_column = substr($column, 9);
332            
333             $row[$real_column] = iconv('ucs-2le', 'utf-8', $row[$column]);
334             unset($row[$column]);
335         }
336        
337         return $row;
338     }
339    
340    
341     /**
342     * Returns all of the rows from the result set
343     *
344     * @return array  The array of rows
345     */
346     public function fetchAllRows()
347     {
348         $this->seek(0);
349        
350         $all_rows = array();
351         foreach ($this as $row) {
352             $all_rows[] = $row;
353         }
354         return $all_rows;
355     }
356    
357    
358     /**
359     * Returns the row next row in the result set (where the pointer is currently assigned to)
360     *
361     * @throws fNoRowsException
362     * @throws fNoRemainingException
363     *
364     * @return array  The associative array of the row
365     */
366     public function fetchRow()
367     {
368         $row = $this->current();
369         $this->next();
370         return $row;
371     }
372    
373    
374     /**
375     * Wraps around ::fetchRow() and returns the first field from the row instead of the whole row
376     *
377     * @throws fNoRowsException
378     * @throws fNoRemainingException
379     *
380     * @return string|number  The first scalar value from ::fetchRow()
381     */
382     public function fetchScalar()
383     {
384         $row = $this->fetchRow();
385         return array_shift($row);
386     }
387    
388    
389     /**
390     * Warns the user about bugs in the DBLib driver for MSSQL, fixes some bugs
391     *
392     * @param  array $row  The row from the database
393     * @return array  The fixed row
394     */
395     private function fixDblibMSSQLDriver($row)
396     {
397         static $using_dblib = NULL;
398        
399         if ($using_dblib === NULL) {
400        
401             // If it is not a windows box we are definitely not using dblib
402             if (!fCore::checkOS('windows')) {
403                 $using_dblib = FALSE;
404            
405             // Check this windows box for dblib
406             } else {
407                 ob_start();
408                 phpinfo(INFO_MODULES);
409                 $module_info = ob_get_contents();
410                 ob_end_clean();
411                
412                 $using_dblib = !preg_match('#FreeTDS#ims', $module_info, $match);
413             }
414         }
415        
416         if (!$using_dblib) {
417             return $row;
418         }
419        
420         foreach ($row as $key => $value) {
421             if ($value == ' ') {
422                 $row[$key] = '';
423                 trigger_error(
424                     self::compose(
425                         'A single space was detected coming out of the database and was converted into an empty string - see %s for more information',
426                         'http://bugs.php.net/bug.php?id=26315'
427                     ),
428                     E_USER_NOTICE
429                 );
430             }
431             if (strlen($key) == 30) {
432                 trigger_error(
433                     self::compose(
434                         'A column name exactly 30 characters in length was detected coming out of the database - this column name may be truncated, see %s for more information.',
435                         'http://bugs.php.net/bug.php?id=23990'
436                     ),
437                     E_USER_NOTICE
438                 );
439             }
440             if (strlen($value) == 256) {
441                 trigger_error(
442                     self::compose(
443                         'A value exactly 255 characters in length was detected coming out of the database - this value may be truncated, see %s for more information.',
444                         'http://bugs.php.net/bug.php?id=37757'
445                     ),
446                     E_USER_NOTICE
447                 );
448             }
449         }
450        
451         return $row;
452     }
453    
454    
455     /**
456     * Returns the last auto incremented value for this database connection. This may or may not be from the current query.
457     *
458     * @return integer  The auto incremented value
459     */
460     public function getAutoIncrementedValue()
461     {
462         return $this->auto_incremented_value;
463     }
464    
465    
466     /**
467     * Returns the result
468     *
469     * @internal
470      *
471     * @return mixed  The result of the query
472     */
473     public function getResult()
474     {
475         return $this->result;
476     }
477    
478    
479     /**
480     * Returns the SQL used in the query
481     *
482     * @return string  The SQL used in the query
483     */
484     public function getSQL()
485     {
486         return $this->sql;
487     }
488    
489    
490     /**
491     * Returns the SQL as it was before translation
492     *
493     * @return string  The SQL from before translation
494     */
495     public function getUntranslatedSQL()
496     {
497         return $this->untranslated_sql;
498     }
499    
500    
501     /**
502     * Returns the current row number (required by iterator interface)
503     *
504     * @throws fNoRowsException
505     * @internal
506      *
507     * @return integer  The current row number
508     */
509     public function key()
510     {
511         if ($this->pointer === NULL) {
512             $this->current();
513         }
514        
515         return $this->pointer;
516     }
517    
518    
519     /**
520     * Advances to the next row in the result (required by iterator interface)
521     *
522     * @throws fNoRowsException
523     * @internal
524      *
525     * @return void
526     */
527     public function next()
528     {
529         if ($this->pointer === NULL) {
530             $this->current();
531         }
532        
533         $this->pointer++;
534        
535         if ($this->valid()) {
536             $this->advanceCurrentRow();
537         } else {
538             $this->current_row = NULL;
539         }
540     }
541    
542    
543     /**
544     * Rewinds the query (required by iterator interface)
545     *
546     * @internal
547      *
548     * @return void
549     */
550     public function rewind()
551     {
552         try {
553             $this->seek(0);
554         } catch (Exception $e) { }
555     }
556    
557    
558     /**
559     * Seeks to the specified zero-based row for the specified SQL query
560     *
561     * @throws fNoRowsException
562     *
563     * @param  integer $row  The row number to seek to (zero-based)
564     * @return void
565     */
566     public function seek($row)
567     {
568         if(!$this->returned_rows) {
569             throw new fNoRowsException('The query did not return any rows');
570         }
571        
572         if ($row >= $this->returned_rows || $row < 0) {
573             throw new fProgrammerException('The row requested does not exist');
574         }
575        
576         $this->pointer = $row;
577                    
578         if ($this->extension == 'mssql') {
579             $success = mssql_data_seek($this->result, $row);
580         } elseif ($this->extension == 'mysql') {
581             $success = mysql_data_seek($this->result, $row);
582         } elseif ($this->extension == 'mysqli') {
583             $success = mysqli_data_seek($this->result, $row);
584         } elseif ($this->extension == 'pgsql') {
585             $success = pg_result_seek($this->result, $row);
586         } elseif ($this->extension == 'sqlite') {
587             $success = sqlite_seek($this->result, $row);
588         } elseif ($this->extension == 'array') {
589             // Do nothing since we already changed the pointer
590             $success = TRUE;
591         }
592        
593         if (!$success) {
594             throw new fSQLException(
595                 'There was an error seeking to row %s',
596                 $row
597             );
598         }
599        
600         $this->advanceCurrentRow();
601     }
602    
603    
604     /**
605     * Sets the number of affected rows
606     *
607     * @internal
608      *
609     * @param  integer $affected_rows  The number of affected rows
610     * @return void
611     */
612     public function setAffectedRows($affected_rows)
613     {
614         if ($affected_rows === -1) { $affected_rows = 0; }
615         $this->affected_rows = (int) $affected_rows;
616     }
617    
618    
619     /**
620     * Sets the auto incremented value
621     *
622     * @internal
623      *
624     * @param  integer $auto_incremented_value  The auto incremented value
625     * @return void
626     */
627     public function setAutoIncrementedValue($auto_incremented_value)
628     {
629         $this->auto_incremented_value = ($auto_incremented_value == 0) ? NULL : $auto_incremented_value;
630     }
631    
632    
633     /**
634     * Sets the result from the query
635     *
636     * @internal
637      *
638     * @param  mixed $result  The result from the query
639     * @return void
640     */
641     public function setResult($result)
642     {
643         $this->result = $result;
644     }
645    
646    
647     /**
648     * Sets the number of rows returned
649     *
650     * @internal
651      *
652     * @param  integer $returned_rows  The number of rows returned
653     * @return void
654     */
655     public function setReturnedRows($returned_rows)
656     {
657         $this->returned_rows = (int) $returned_rows;
658         if ($this->returned_rows) {
659             $this->affected_rows = 0;
660         }
661     }
662    
663    
664     /**
665     * Sets the SQL used in the query
666     *
667     * @internal
668      *
669     * @param  string $sql  The SQL used in the query
670     * @return void
671     */
672     public function setSQL($sql)
673     {
674         $this->sql = $sql;
675     }
676    
677    
678     /**
679     * Sets the SQL from before translation
680     *
681     * @internal
682      *
683     * @param  string $untranslated_sql  The SQL from before translation
684     * @return void
685     */
686     public function setUntranslatedSQL($untranslated_sql)
687     {
688         $this->untranslated_sql = $untranslated_sql;
689     }
690    
691    
692     /**
693     * Throws an fNoResultException if the query did not return any rows
694     *
695     * @throws fNoRowsException
696     *
697     * @param  string $message  The message to use for the exception if there are no rows in this result set
698     * @return void
699     */
700     public function tossIfNoRows($message=NULL)
701     {
702         if (!$this->returned_rows && !$this->affected_rows) {
703             if ($message === NULL) {
704                 $message = self::compose('No rows were returned or affected by the query');   
705             }
706             throw new fNoRowsException($message);
707         }
708     }
709    
710    
711     /**
712     * Returns if the query has any rows left
713     *
714     * @return boolean  If the iterator is still valid
715     */
716     public function valid()
717     {
718         if (!$this->returned_rows) {
719             return FALSE;
720         }
721        
722         if ($this->pointer === NULL) {
723             return TRUE;
724         }
725        
726         return ($this->pointer < $this->returned_rows);
727     }
728 }
729  
730  
731  
732 /**
733  * Copyright (c) 2007-2009 Will Bond <will@flourishlib.com>
734  *
735  * Permission is hereby granted, free of charge, to any person obtaining a copy
736  * of this software and associated documentation files (the "Software"), to deal
737  * in the Software without restriction, including without limitation the rights
738  * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
739  * copies of the Software, and to permit persons to whom the Software is
740  * furnished to do so, subject to the following conditions:
741  *
742  * The above copyright notice and this permission notice shall be included in
743  * all copies or substantial portions of the Software.
744  *
745  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
746  * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
747  * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
748  * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
749  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
750  * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
751  * THE SOFTWARE.
752  */