Documentation is available at RSDTable.php
1 <?php
2 // RSDEngine: The Rapid and Secure Development Engine
3 // Copyright (C) 2003 Lukas Feiler
4 //
5 // This library is free software; you can redistribute it and/or
6 // modify it under the terms of the GNU Lesser General Public
7 // License as published by the Free Software Foundation; either
8 // version 2.1 of the License, or (at your option) any later version.
9 //
10 // This library is distributed in the hope that it will be useful,
11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 // Lesser General Public License for more details.
14 //
15 // You should have received a copy of the GNU Lesser General Public
16 // License along with this library; if not, write to the Free Software
17 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18
19 /**Contains just the class RSDTable.
20 *
21 * @author Lukas Feiler <lukas.feiler@chello.at>
22 * @copyright Lukas Feiler 2003
23 * @package RSDEngine
24 * @filesource
25 */
26
27 /**RSDTable extends PEAR.
28 */
29 require_once('PEAR.php');
30
31 /**RSDTable requires RSArrayUtil for some array to string conversions.
32 */
33 require_once('RSArrayUtil/RSArrayUtil.php');
34
35 /**EAFProject uses the error codes defined in RSErrorManager.php.
36 */
37 require_once('RSErrorManager/RSErrorManager.php');
38
39 /**RSDApplication is needed to interact with other table object.
40 */
41 require_once('RSDApplication.php');
42
43 /**Pass this constant to the methods isValidColumnValueArray, isValidColumnValue or any other isValid* method to check also unique constraints (if existing).
44 */
45 DEFINE('RSD_CHECK_UNIQUE_CONSTRAINTS', true);
46
47 /**Pass this constant to the methods select, update, create, delete and all selectIncluding* methods to prevent LiveUser from checking for any rights.
48 */
49 DEFINE('RSD_NO_LIVEUSER_RIGHT_CHECK', false);
50
51 /**Some generated methods accept this constant as argument. If passed the method will not perform the actual operation but will
52 * only check if the user has the right to do so. If she has the method will return true. Otherwise it will return a permission
53 * denied error.
54 */
55 DEFINE('RSD_HYPOTHETICAL', true);
56
57 /**This is the superclass for all RSDTable classes created for a specific table.
58 *
59 * @author Lukas Feiler <lukas.feiler@chello.at>
60 * @version 0.1.9
61 * @copyright Lukas Feiler 2003
62 * @package RSDEngine
63 * @since 18.1.2003
64 */
65 class RSDTable extends PEAR {
66
67 /**An instance of PEAR::DB
68 * @var DB
69 */
70 var $db = null;
71
72 /**An instance of the generated Application Child Class
73 * @var mixed
74 */
75 var $app = null;
76
77 /**The name of the table.
78 * @var String
79 */
80 var $name = null;
81
82 /**Constructor that calls the base class constructor PEAR().
83 */
84 function RSDTable()
85 {
86 $this->PEAR();
87 }
88
89 /**Returns a two-demensional Array of records. One record is hold by one array.
90 *
91 * All arguments are optional.
92 * @see generateWhereClause
93 *
94 * @param String $whereClause Any value valid for generateWhereClause.
95 * @param String $orderByClause A SQL-ORDER BY clause like 'ORDER BY table.column1 AND table.column2'
96 * @param int $from If $from and $to are specified only the records from $from to $to are fechted and returened.
97 * @param int $to See $from
98 * @return Array An array of records.
99 */
100 function select($whereClause = '', $orderByClause = '', $columns = '*', $from = null, $to = null, $checkRight = true)
101 {
102 $query = "SELECT
103 $columns
104 FROM
105 $this->
106 name " . $this->generateWhereClause($whereClause) . "
107 $orderByClause";
108 return $this->getAll($query, null, DB_FETCHMODE_ASSOC, $from, $to);
109 }
110
111 /**This method should be overwritten in a child class. It should perform a join over all related tables.
112 *
113 * @param String $whereClause Any value valid for generateWhereClause.
114 * @param String $orderByClause A SQL-ORDER BY clause like 'ORDER BY table.column1 AND table.column2'
115 * @param int $from If $from and $to are specified only the records from $from to $to are fechted and returened.
116 * @param int $to See $from
117 * @return Array An array of records.
118 */
119 function selectIncludingAll($whereClause = '', $orderByClause = '', $columns = '*', $from = null, $to = null, $checkRight = true)
120 {
121 return $this->select($whereClause, $orderByClause, $columns, $from, $to, $checkRight);
122 }
123
124 /**This method should be overwritten in a child class. It should perform a join over all tables that referenced by a foreign key define in this table.
125 *
126 * @param String $whereClause Any value valid for generateWhereClause.
127 * @param String $orderByClause A SQL-ORDER BY clause like 'ORDER BY table.column1 AND table.column2'
128 * @param int $from If $from and $to are specified only the records from $from to $to are fechted and returened.
129 * @param int $to See $from
130 * @return Array An array of records.
131 */
132 function selectIncludingDirectlyRelated($whereClause = '', $orderByClause = '', $columns = '*', $from = null, $to = null, $checkRight = true)
133 {
134 return $this->select($whereClause, $orderByClause, $columns, $from, $to, $checkRight);
135 }
136
137 /**Inserts a record into the table.
138 *
139 * The argument $inserts contains a variable number of values assigned to fields.
140 * The key is always the clumn name. The value can be just a string as it is the case
141 * for f_title in the following example. But it is as well possible to specify the value
142 * as an instance of RSDColumnValue containing two properties. These properties are set by
143 * the contructor of RSDColumnValue. The first constructor argument specifies the value, the second
144 * specifies the prepare string (i.e. the way this value should be treated). You can set the second field to the string
145 * '!' as it is the case for f_date to prevent the value to be quoted. You can as well set it
146 * to '&' which means the value is a filename and its contents should be inserted. Note
147 * that the assignment for f_title is equivalent to the assignment for f_description because
148 * '?' is assumed if the value is not specified as an instance of RSDColumnValue.
149 * <code>
150 * $table->insert(
151 * array(
152 * "f_title" => "My Title",
153 * "f_date" => new RSDColumnValue("NOW()", "!"),
154 * "f_file" => new RSDColumnValue("test.text", "&"),
155 * "f_description" => new RSDColumnValue("This is a test.", "?")
156 * )
157 * );
158 * </code>
159 *
160 * @param Array $inserts Contains the columns and the values as key-value pairs.
161 * @param boolean $hypothetical If set to true this method will return true if the user has the right to perform this operation.
162 * Otherwise an instance of RSError will be returned. This argument is optional. The default is false.* @return mixed True on success, an instance of PEAR_Error on failure.
163 * @return mixed True on success or an instance of PEAR_Error on failure.
164 */
165 function insert($inserts, $hypothetical = false)
166 {
167 if ($hypothetical) {
168 return true;
169 }
170 if (!is_array($inserts)) {
171 return RSErrorManager::raiseRSError($this->name, "", "insert", "Passed argument is not an array.", RSERROR_NOT_ARRAY);
172 }
173 if (count($inserts) == 0) {
174 return RSErrorManager::raiseRSError($this->name, "", "insert", "Passed array contains no elements.", RSERROR_ARRAY_ZERO_LENGTH);
175 }
176
177 $error = false;
178 $columns = array();
179 while (list($column,$value) = each($inserts)) {
180 $rsdColumnValue =& new RSDColumnValue($value);
181 if (PEAR::isError($rsdColumnValue->getError())) {
182 return RSErrorManager::raiseRSError($this->name, "", "insert", "One of the assigned values is not a string nor is it an instance of RSDColumnValue.", RSERROR_NOT_STRING);
183 }
184 $value = $rsdColumnValue->getValue();
185 $prepareString = $rsdColumnValue->getPrepareString();
186
187 $columns[] = $column;
188 $values[] = $value;
189 $prepareStrings[$column] = $prepareString;
190
191 //check the value only if it is to be quoted, NOT if it is something that will be
192 //inserted AS IS ('!') like 'NOW()' or if it is a filename ('&').
193 if ($prepareStrings[$column] == '?') {
194 if (PEAR::isError($err = $this->isValidColumnValue($column, $value))) {
195 $error = $err;
196 }
197 }
198 }
199
200 //We return the last error that occured, but all others
201 //are cought by RSErrorManager anyway.
202 if (PEAR::isError($error)) {
203 return $error;
204 }
205
206 if (PEAR::isError($error = $this->checkUniqueConstraints($inserts))) {
207 return $error;
208 }
209 $handle = $this->db->prepare("INSERT INTO $this->name (" . RSArrayUtil::toString($columns,', ','%val') . ") VALUES(" . RSArrayUtil::toString($prepareStrings,', ','%val') . ")");
210 return $this->db->execute($handle, $values);
211 }
212
213 /**Updates records in this table.
214 *
215 * The argument $updates contains a variable number of values assigned to fields.
216 * The key is always the column name. The value can be just a string as it is the case
217 * for f_title in the following example. But it is as well possible to specify the value
218 * as an instance of RSDColumnValue containing two properties. These properties are set by
219 * the contructor of RSDColumnValue. The first constructor argument specifies the value, the second
220 * specifies the prepare string (i.e. the way this value should be treated). You can set the second field to the string
221 * '!' as it is the case for f_date to prevent the value to be quoted. You can as well set it
222 * to '&' which means the value is a filename and its contents should be inserted. Note
223 * that the assignment for f_title is equivalent to the assignment for f_description because
224 * '?' is assumed if the value is not specified as an instance of RSDColumnValue.
225 * <code>
226 * $table->update(
227 * array(
228 * "f_title" => "My Title",
229 * "f_date" => new RSDColumnValue("NOW()", "!"),
230 * "f_file" => new RSDColumnValue("test.text", "&"),
231 * "f_description" => new RSDColumnValue("This is a test.", "?")
232 * )
233 * );
234 * </code>
235 * @see generateWhereClause
236 *
237 * @param Array $updates
238 * @param mixed $whereConditions Any argument valid for generateWhereClause.
239 * @return mixed An instance of PEAR_Error or the boolean value true.
240 */
241 function update($updates, $whereConditions)
242 {
243 $error = false;
244
245 $whereClause = $this->generateWhereClause($whereConditions);
246
247 while (list($column, $value) = each($updates)) {
248 $rsdColumnValue =& new RSDColumnValue($value);
249 if (PEAR::isError($rsdColumnValue->getError())) {
250 return RSErrorManager::raiseRSError($this->name, "", "update", "One of the assigned values is not a string nor is it an instance of RSDColumnValue.", RSERROR_NOT_STRING);
251 }
252 $value = $rsdColumnValue->getValue();
253 $prepareString = $rsdColumnValue->getPrepareString();
254
255 $columns[] = $column;
256 $values[] = $value;
257 $prepareStrings[$column] = $prepareString;
258
259 //check the value only if it is to be quoted, NOT if it is something that will be
260 //inserted AS IS ('!') like 'NOW()' or if it is a filename ('&').
261 if ($prepareStrings[$column] == '?') {
262 if (PEAR::isError($err = $this->isValidColumnValue($column, $value))) {
263 $error = $err;
264 }
265 }
266 }
267
268 //We return the last error that occured, but all others
269 //are cought by RSErrorManager anyway.
270 if (PEAR::isError($error)) {
271 return $error;
272 }
273
274 if (PEAR::isError($error = $this->checkUniqueConstraints($updates, false, preg_replace('/^WHERE/', '', $whereClause)))) {
275 return $error;
276 }
277
278 $handle = $this->db->prepare("UPDATE $this->name SET " .
279 RSArrayUtil::toString($prepareStrings,', ','%key=%val') . " " .
280 $whereClause
281 );
282 return $this->db->execute($handle, $values);
283 }
284
285 /*Update a single record in the table.
286 *
287 * This method was implemented to make sure that at least one record is affected by
288 * the update operation. This could not be achived yet because mysql_affected_rows
289 * concerns a record only 'affected' if any data is actually changed.
290 *
291 * @param Array $updates
292 * @param mixed $whereConditions Any argument valid for generateWhereClause.
293 * @param boolean $checkRight Whether to perform a permission check. This argument is optional. The default is true.
294 * @return mixed An instance of PEAR_Error or the boolean value true.
295 */
296 function updateRow($updates, $whereConditions, $checkRight = true)
297 {
298 $error = $this->update($updates, $whereConditions);
299
300 /*
301 if (PEAR::isError($error)) {
302 return $error;
303 } else {
304 $affectedRows = $this->db->affectedRows();
305 if (PEAR::isError($affectedRows)) {
306 return $affectedRows;
307 } elseif ($affectedRows <= 0) {
308 return RSErrorManager::raiseRSError($this->name, "", "update", "No records were updated.", RSERROR_NO_SUCH_RECORD);
309 }
310 }
311 */
312
313 return $error;
314 }
315 /**Deletes records from this table.
316 * @see generateWhereClause
317 *
318 * @param String $whereConditions A SQL-WHERE clause like 'WHERE table.filed=1' or any other argument valid for generateWhereClause.
319 * @return mixed True on success, an instance of PEAR_Error on failure.
320 */
321 function delete($whereConditions)
322 {
323 return $this->db->query("DELETE FROM $this->name " . $this->generateWhereClause($whereConditions));
324 }
325
326
327
328
329 /**Returns an associative Array with all records from the specified select matching the conditions passed as first arguement.
330 *
331 * The select-method to invoke can be specified by the second argument.
332 * Valid values are the names of all methods stating with "selectIncluding..." and "select".
333 * The default is to invoke the method select.
334 * @see generatedWhereClause
335 *
336 * @param mixed $conditions Any argument valid vor generatedWhereClause.
337 * @param String $selectMethod Valid values are the names of all methods
338 * stating with "selectIncluding..." and "select".
339 * This argument is optional. The default is 'select'.
340 * @param String $orderByClause A ORDER BY clause like "ORDER BY f_date".
341 * @param String $columns The columns to select. The default is "*" (all).
342 * @param boolean $checkRight Whether to check if the current user has the right to perform this operation. The default is true.
343 * @return mixed An array of records or an instance of PEAR_Error.
344 */
345 function get($conditions = false, $selectMethod = "select", $orderByClause = "", $columns = "*", $from = null, $to = null, $checkRight = true)
346 {
347 if ($selectMethod == "") {
348 $selectMethod = "select";
349 }
350 if (!method_exists($this,$selectMethod)) {
351 return RSErrorManager::raiseRSError($this->name, "", "get", "No such method: $selectMethod.", RSERROR_FATAL_ERROR);
352 }
353 return $this->$selectMethod($this->generateWhereClause($conditions), $orderByClause, $columns, $from, $to, $checkRight);
354 }
355
356 function getPage($pageNumber = 1, $perPage = 10, $conditions = false, $selectMethod = "select", $orderByClause = "", $columns = "*", $checkRight = true)
357 {
358 $recordCount = $this->count($conditions, $selectMethod, $checkRight);
359 if (PEAR::isError($recordCount)) {
360 return $recordCount;
361 }
362
363 $pageCount = ((int) ($recordCount / $perPage)) + ($recordCount % $perPage == 0 ? 0 : 1);
364 if ($pageNumber > $pageCount) {
365 $pageNumber = $pageCount;
366 }
367
368 if ($pageNumber < $pageCount) {
369 $hasNextPage = true;
370 } else {
371 $hasNextPage = false;
372 }
373 $from = ($pageNumber - 1) * $perPage;
374 $to = ($pageNumber * $perPage) - 1;
375
376
377 if ($to >= $recordCount) {
378 $to = $recordCount - 1;
379 }
380
381 $records = $this->get($conditions, $selectMethod, $orderByClause, $columns, $from, $to, $checkRight);
382 if (PEAR::isError($records)) {
383 return $records;
384 }
385 $pageRecordsCount = count($records);
386
387 if ($from < -1) {
388 $from = -1;
389 }
390 $ret = array(
391 'from' => $from + 1,
392 'to' => $to + 1,
393 'pageNumber' => $pageNumber,
394 'pageCount' => $pageCount,
395 'hasNextPage' => $hasNextPage,
396 'recordCount' => $recordCount,
397 'pageRecordsCount' => $pageRecordsCount,
398 'records' => $records
399 );
400 return $ret;
401 }
402
403 /**Returns just one record from this table.
404 * @see get
405 *
406 * @param mixed $conditions Any argument valid vor generatedWhereClause.
407 * @param String $selectMethod Valid values are the names of all methods
408 * stating with "selectIncluding..." and "select".
409 * This argument is optional. The default is 'select'.
410 * @param String $columns The columns to select. The default is "*" (all).
411 * @param boolean $checkRight Whether to check if the current user has the right to perform this operation. The default is true.
412 * @return mixed An array holding the first record retrieved from the database or an instance of PEAR_Error on failure.
413 */
414 function getRow($conditions = false, $selectMethod = "select", $columns = "*", $checkRight = true)
415 {
416 $records = $this->get($conditions, $selectMethod, "", $columns, 0, 1, $checkRight);
417 if (PEAR::isError($records)) {
418 return $records;
419 }
420 if (!isset($records[0])) {
421 return RSErrorManager::raiseRSError($this->name, "", "getRow", "Query returned no records.", DB_ERROR_NOT_FOUND);
422 }
423 return $records[0];
424 }
425
426 /**Returns just one field of one record from this table.
427 *
428 * This method uses getRow to get one record from this table.
429 * array_shift is used to extract the first column.
430 * @see getRow
431 *
432 * @param conditions mixed Any argument valid vor generatedWhereClause.
433 * @param selectMethod String Valid values are the names of all methods
434 * stating with "selectIncluding..." and "select".
435 * This argument is optional. The default is 'select'.
436 * @param columns String The columns to select. The default is "*" (all).
437 * @param boolean $checkRight Whether to check if the current user has the right to perform this operation. The default is true.
438 * @return mixed A string holding value of the first field of the retrieved column or an instance of PEAR_Error on failure.
439 */
440 function getOne($conditions = false, $selectMethod = "select", $columns = "*", $checkRight = true)
441 {
442 $record = $this->getRow($conditions, $selectMethod, $columns, $checkRight);
443 if (PEAR::isError($record)) {
444 return $record;
445 }
446 return array_shift($record);
447 }
448
449 /**Returns an integer representing the number of records that matched the conditions passed as first argument.
450 * @param mixed $conditions Any value valid for generateWhereClause.
451 * @param String $selectMethod Valid values are the names of all methods
452 * stating with "selectIncluding..." and "select".
453 * This argument is optional. The default is 'select'.
454 * @param boolean $checkRight Whether to check if the current user has the right to perform this operation. The default is true.
455 * @return int The number of records that matched the conditions.
456 */
457 function count($conditions = false, $selectMethod = "select", $checkRight = true)
458 {
459 if ($checkRight) {
460 $records = $this->get($conditions, $selectMethod);
461 if (PEAR::isError($records)) {
462 return $records;
463 }
464 return count($records);
465 } else {
466 return $this->getOne($conditions, $selectMethod , "COUNT(*) c", false);
467 }
468 }
469
470 /**Whether records that matched the conditions passed as first argument exist.
471 * @param mixed $conditions Any value valid for generateWhereClause.
472 * @param String $selectMethod Valid values are the names of all methods
473 * stating with "selectIncluding..." and "select".
474 * This argument is optional. The default is 'select'.
475 * @param boolean $checkRight Whether to check if the current user has the right to perform this operation. The default is true.
476 * @return int The number of records that matched the conditions.
477 */
478 function exist($conditions = false, $selectMethod = "select", $checkRight = true)
479 {
480 $count = $this->count($conditions, $selectMethod, $checkRight);
481 if (PEAR::isError($count)) {
482 return $count;
483 }
484 return $count > 0;
485 }
486
487
488
489
490 /**Returns the name of the method that checks if a value is valid for the column supplied as argument.
491 * @param String $columnName The column name.
492 * @return String The name of the isValid-Method.
493 */
494 function getIsValidMethodName($columnName, $columnPrefix = 'f_')
495 {
496 $str = $columnName;
497 $str = preg_replace("/^$columnPrefix/i",'', $str);
498 $str = ucfirst(strtolower($str));
499 //the e modifier makes the second argument to be parsed as PHP-Code!
500 $str = preg_replace("/_([a-z0-9])/ie","strtoupper('\\1')",$str);
501 return "isValid" . $str;
502
503 }
504
505
506 /**Returns true if the value passed as second argument is valid for the column passed as first argument.
507 * @param column The name of the column. It can be passed in absolute notation ("table.column")
508 * or relativ to this table (just "culumn").
509 * @param value The value to check.
510 * @param boolean $forceReferentialIntegrityCheck Whether to force a referential integrity check.
511 * This argument is optional. The default is false.
512 * @param String $negativeConditions A string containing conditions that are used to solve the following problem:
513 * A record with the unique username 'tom' gets updated with a new password and the username is again set to 'tom'.
514 * When asking this method if the username 'tom' already exists the answer would be yes and our update operation would fail.
515 * The negative conditions allow us to exclude this very record and find out if there any other records with username='tom'.
516 * This argument is optional.
517 * @return boolean True if the value is valid an instance of PEAR_Error otherwise.
518 */
519 function isValidColumnValue($column, $value, $forceReferentialIntegrityCheck = false, $negativeConditions = false)
520 {
521 if(strpos($column, ".") === false){
522 $isValid = $this->getIsValidMethodName($column);
523 if (!method_exists($this,$isValid)) {
524 return RSErrorManager::raiseRSError($this->name, $column, 'validation', "No such method in $this->name: $isValid.", RSERROR_NO_SUCH_METHOD);
525 }
526 return $this->$isValid($value, $forceReferentialIntegrityCheck, $negativeConditions);
527 }else{
528 $tableName = substr($column, 0, strpos($column,"."));
529 $columnName = substr($column, strpos($column,".") + 1);
530
531
532 $table =& $this->app->getTable($tableName);
533 if (!is_object($table)) {
534 return RSErrorManager::raiseRSError($tableName, $column, 'validation', "No such RSDTable object: $tableName", RSERROR_NOT_OBJECT);
535 }
536 $isValid = $table->getIsValidMethodName($columnName);
537 if (!method_exists($table,$isValid)) {
538 return RSErrorManager::raiseRSError($tableName, $column, 'validation', "No such method in $tableName: $isValid.", RSERROR_NO_SUCH_METHOD);
539 }
540 return $table->$isValid($value, $forceReferentialIntegrityCheck, $negativeConditions);
541 }
542 }
543
544
545 /**Must be overwritten. Should return true if no unique constraints would be violated by using the array of assignments passed as argument for a INSERT or a UPDATE operation. Otherwise false should be returned.
546 * @param Array $assignments An array of key-value pairs.
547 * @return boolean
548 */
549 function checkUniqueConstraints($assignments)
550 {
551 return true;
552 }
553
554 /**Returns true if such a primary key exists in the specified table otherwise a new PEAR_Error.
555 *
556 * @param $foreignKey String The value of the foreign key.
557 * @param $primaryKey String The column that holds the primary keys in ablsolute notation ("table.column").
558 * @return boolean True if the value is a valid foreign key.
559 */
560 function isValidForeignKey($foreignKey, $primaryKey)
561 {
562 if (strpos($primaryKey,".") === false) {
563 return RSErrorManager::raiseRSError($this->name, '', 'validation', "The primary key was not specified as TABLE.KEY.", RSERROR_INVALID_FOREIGN_KEY);
564 }
565 $tableName = substr($primaryKey, 0, strpos($primaryKey,"."));
566 $columnName = substr($primaryKey, strpos($primaryKey,".") + 1);
567
568 $table =& $this->app->getTable($tableName);
569 if (!is_object($table)) {
570 return RSErrorManager::raiseRSError($this->name, '', 'validation', "The table that contains the primary key could not be found.", RSERROR_INVALID_FOREIGN_KEY);
571 }
572 $count = $table->count(array($columnName => $foreignKey), 'select', RSD_NO_LIVEUSER_RIGHT_CHECK);
573 if (PEAR::isError($count)) {
574 return $count;
575 }
576 if ($count > 0) {
577 return true;
578 } else {
579 return RSErrorManager::raiseRSError($this->name, '', 'validation', "invalid foreign key for referencing the primary key $primaryKey", RSERROR_INVALID_FOREIGN_KEY);
580 }
581 }
582
583 /**Returns a WHERE clause generated from the array passed as argument.
584 *
585 * @see _generateAssignment
586 *
587 * @param mixed $conditions An array from which to generate the WHERE clause.
588 * The first way is to construct $conditions from column-value assignments as in the following example:
589 * <code>
590 * $table->generateWhereClause(
591 * array(
592 * "f_title" => "Peters's",
593 * "f_date" => new RSDColumnValue("NOW()", "!"),
594 * "f_file" => RSDColumnValue("test.txt", "&"),
595 * "f_description" => RSDColumnValue("This is a test.", "?"),
596 * "f_data" => RSDColumnValue("test", "%")
597 * )
598 * );
599 * </code>
600 * The element key represents the column name. The value that should be 'assigned' to the column can either be
601 * specified directly as the element value or as an instance of RSDColumnValue.
602 * Note that "f_title" => "My Title" is equal to "f_title" => RSDColumnValue('My Title', '?') because '?' is the
603 * default. The column name, the column value and the 'prepareString' ('!','&','?' or '%') are passed to
604 * the method $this->_generateAssignment. The example above would result in the following where clause (if oracle is
605 * used; ' is escaped with ''):
606 * <code>
607 * WHERE
608 * f_title='Peters''s'
609 * AND
610 * f_date=NOW()
611 * AND
612 * f_file='The contents of the file test.txt with quotes('') escaped.'
613 * AND
614 * f_data LIKE '%test%'
615 * </code>
616 * If you wish to connect the conditions differently than by AND, use the following format:
617 * <code>
618 * $table->generateWhereClause(
619 * array(
620 * array(
621 * "f_title" => "Peters's",
622 * "f_date" => RSDColumnValue("NOW()", "!"),
623 * "f_file" => RSDColumnValue("test.txt", "&"),
624 * "f_description" => RSDColumnValue("This is a test.", "?"),
625 * "f_data" => RSDColumnValue("test", "%")
626 * ),
627 * "%s1 AND %s2 AND (%s3 OR %s4 OR %s5)"
628 * )
629 * );
630 * </code>
631 * Note that the the conditions-array from the previous example is now itself placed in an array (as the first
632 * element. The second element is a string that defines the format. %s1 is replaced with the first condition, %s2
633 * with the second condition, and so on...
634 * If $conditions is a string this string will be returned without modifications.
635 * If $conditions is an array and has has a length of 0 or is (===) the boolean value false,
636 * an empty string will be returned.
637 * NOTE: this might be dangerous if the where clause is used in a DELETE or UPDATE statement!
638 * If $conditions is not an array and not a string the string "WHERE 1=2" will be returned.
639 * This is because if it's not a string nor an array something must have gone wrong; and in
640 * in that case we do not want to risk to cause unexpected results.
641 */
642 function generateWhereClause($conditions)
643 {
644 if (is_string($conditions)) {
645 return $conditions;
646 }
647 if (count($conditions) == 0 || $conditions === false) {
648 return "";
649 }
650 if (!is_array($conditions)) {
651 return "WHERE 1 = 2";
652 }
653
654 if (count($conditions) == 2 && is_array($conditions[0]) && is_string($conditions[1])) {
655 $format = $conditions[1];
656 $conditions = $conditions[0];
657 } else {
658 //$conditions just contains conditions!
659 $format = false;
660 }
661
662 $ret = $format;
663 $i = 1;
664 while (list($column,$value) = each($conditions)) {
665 $columns[] = $column;
666 $rsdColumnValue =& new RSDColumnValue($value);
667 $condition = $this->_generateAssignment($column, $rsdColumnValue->getValue(), $rsdColumnValue->getPrepareString());
668 if (is_string($format)) {
669 $ret = str_replace("%s$i", $condition, $ret);
670 } else {
671 $ret .= ($ret == "" ? "" : " AND ") . $condition;
672 }
673
674 $i++;
675 }
676 return "WHERE $ret";
677 }
678
679 /**Generates an SQL-assignment of a value to a column usable in a where clause.
680 *
681 * @param String $column The name of the column the value should be assigned to.
682 * @param String $value The value to assign.
683 * @param String $prepareString If this argument
684 * is '?' the $value will be quoted using $this->db->quote;
685 * if it is '!' it will not be quoted;
686 * if it is '&' $value will be treated as a filename and the file's content will be quoted and assigned;
687 * if it is '%' the generated statement will be generated like this:
688 * <code>
689 * return "$column LIKE " . $this->db->quote("%$value%");
690 * </code>
691 */
692 function _generateAssignment($column, $value, $prepareString = '?')
693 {
694 switch ($prepareString) {
695 case '?':
696 return "$column=" . $this->db->quote($value);
697 break;
698 case '!':
699 return "$column=$value";
700 break;
701 case '&':
702 require_once('RSIO/RSIO.php');
703 $data = RSIO::getFileContents($value);
704 if (PEAR::isError($data)) {
705 $data = "";
706 }
707 return "$column=" . $this->db->quote($data);
708 break;
709 case '%':
710 return "$column LIKE " . $this->db->quote("%$value%");
711 break;
712 }
713 }
714
715 /**Returns the value of what ever was specified as column value.
716 *
717 * If an instance of RSDColumnValue was passed as argument the
718 * property value will be returned; if a string was passed, this string
719 * will be returned.
720 *
721 * @see RSDColumnValue
722 * @param mixed A string or an instance of RSDColumnValue
723 * @return String The value of the columnValue.
724 */
725 function getColumnValue($value)
726 {
727 $rsdColumnValue =& new RSDColumnValue($value);
728 if (PEAR::isError($rsdColumnValue->getError())) {
729 return '';
730 }
731 return $rsdColumnValue->getValue();
732 }
733
734 /**Returns an sql statement that can be used in a select query to retrieve a literal representation of this record.
735 * This method should be overwritten in the child class!
736 */
737 function getLabelColumn()
738 {
739 return 'LABEL';
740 }
741
742 /**Returns an array for use with a SMARTY {html_options} statement.
743 *
744 * In this example getSmartyHTMLOptionsArray would return the array $cust_options.
745 * <code>
746 * <select name=customer_id>
747 * {html_options options=$cust_options selected=$customer_id}
748 * </select>
749 * </code>
750 * @see getLabelColumn
751 *
752 * @param $primaryKeyColumn The column which should be used as value in the option tags.
753 * @param String $labelColumn This string can be any valid sql like CONCAT(f_lastname, ', ', f_firstname).
754 * This argument is optional. The default is to use the return value of getLabelColumn.
755 */
756 function getSmartyHTMLOptionsArray($primaryKeyColum, $labelColumn = false)
757 {
758 if ($labelColumn === false) {
759 $labelColumn = $this->getLabelColumn();
760 }
761 $records = $this->get(false, "select", "", "*, $primaryKeyColum, $labelColumn AS label");
762 if (PEAR::isError($records)) {
763 return $records;
764 }
765 $ret = array();
766 reset($records);
767 while (list($key, $record) = each($records)) {
768 $ret[$record[$primaryKeyColum]] = $record['label'];
769 }
770 return $ret;
771 }
772
773 /**Transforms an array of records to an array for use with a SMARTY {html_options} statement.
774 * This method is static! Be aware of the difference to getSmartyHTMLOptionsArray.
775 *
776 * @static
777 * @param $primaryKeyColumn The column which should be used as value in the option tags.
778 * @return An array of records.
779 * @param String $labelColumn This string can be any valid sql like CONCAT(f_lastname, ', ', f_firstname).
780 * This argument is optional. The default is to use the return value of getLabelColumn.
781 */
782 function recordArrayToSmartyHTMLOptionsArray($primaryKeyColum, &$records, $labelColumnName)
783 {
784 $ret = array();
785 if (!is_array($records)) {
786 return array();
787 }
788 reset($records);
789 while (list($key, $record) = each($records)) {
790 if (!isset($record[$labelColumnName])) {
791 return false;
792 }
793 $ret[$record[$primaryKeyColum]] = $record[$labelColumnName];
794 }
795 return $ret;
796 }
797
798
799
800
801
802
803
804
805 /**
806 * Fetch all the rows returned from a query.
807 *
808 * This method is just like the method DB::getAll. But it adds the possibility
809 * to limit the result by specifying the arguments $from and $to.
810 *
811 * @param string $query the SQL query
812 *
813 * @param array $params array if supplied, prepare/execute will be used
814 * with this array as execute parameters
815 * @param integer $fetchmode the fetch mode to use
816 *
817 * @access public
818 * @return array an nested array, or a DB error
819 */
820
821 function &getAll($query,
822 $params = null,
823 $fetchmode = DB_FETCHMODE_DEFAULT,
824 $from = null,
825 $to = null)
826 {
827 // compat check, the params and fetchmode parameters used to
828 // have the opposite order
829 if (!is_array($params)) {
830 if (is_array($fetchmode)) {
831 $tmp = $params;
832 $params = $fetchmode;
833 $fetchmode = $tmp;
834 } elseif ($params !== null) {
835 $fetchmode = $params;
836 $params = null;
837 }
838 }
839 $params = (empty($params)) ? array() : $params;
840 $fetchmode = (empty($fetchmode)) ? DB_FETCHMODE_DEFAULT : $fetchmode;
841 settype($params, "array");
842 if (sizeof($params) > 0) {
843 $sth = $this->db->prepare($query);
844
845 if (DB::isError($sth)) {
846 return $sth;
847 }
848
849 $res = $this->db->execute($sth, $params);
850 } else {
851 $res = $this->db->query($query);
852 }
853
854 if (DB::isError($res)) {
855 return $res;
856 }
857
858 $results = array();
859 $this->db->pushErrorHandling(PEAR_ERROR_RETURN);
860 // -- begin -- : this section was inserted by Lukas Feiler
861 if(is_numeric($from) && is_numeric($to)){
862 foreach (range($from, $to) as $rownum) {
863 if(DB_OK !== $res->fetchInto($row, $fetchmode, $rownum)){
864 break;
865 }
866 if ($fetchmode & DB_FETCHMODE_FLIPPED) {
867 foreach ($row as $key => $val) {
868 $results[$key][] = $val;
869 }
870 } else {
871 $results[] = $row;
872 }
873 }
874 }else{
875 // -- end --
876 while (DB_OK === $res->fetchInto($row, $fetchmode)) {
877 if ($fetchmode & DB_FETCHMODE_FLIPPED) {
878 foreach ($row as $key => $val) {
879 $results[$key][] = $val;
880 }
881 } else {
882 $results[] = $row;
883 }
884 }
885 //the following line was inserted by Lukas Feiler
886 }
887 $this->db->popErrorHandling();
888
889 $res->free();
890
891 if (isset($sth)) {
892 $this->db->freeResult($sth);
893 }
894 if (DB::isError($row)) {
895 return $this->db->raiseError($row);
896 }
897 return $results;
898 }
899
900
901 /**Returns whether the currently logged in user has the permission to perform an insert operation.
902 * @param Array $inserts An associative array of column-value pairs. This argument is optional.
903 */
904 function canInsert($inserts = array())
905 {
906 return $this->insert($inserts, RSD_HYPOTHETICAL);
907 }
908 }
909
910
911 /**This class can be used to express a value to be used in a SQL statement.
912 *
913 * @author Lukas Feiler <lukas.feiler@chello.at>
914 * @version 0.1.9
915 * @copyright Lukas Feiler 2003
916 * @package RSDEngine
917 * @since 29.11.2003
918 */
919 class RSDColumnValue {
920
921 /**Depending on $this->_prepareString $this->_value will be treaded differently.
922 * @see getValue
923 * @access private
924 * @var String
925 */
926 var $_value = '';
927
928 /**The prepare string.
929 *
930 * This can be
931 * '?' ... the value stored in $this->_value will be quoted
932 * '!' ... the value stored in $this->_value will not be quoted;
933 * it will be used AS IS
934 * '&' ... the value stored in $this->_value will be treated as
935 * a filename; the (quoted) contents of this file will
936 * be used
937 * '%' ... the value stored in $this->_value will be quoted and used
938 * like this: LIKE '%$QUOTED_VALUE%'; this is only useful
939 * for a WHERE clause.
940 *
941 * @see getPrepareString
942 * @access private
943 * @var String
944 */
945 var $_prepareString = '?';
946
947 /**This is where an error that occures in the constructor is stored.
948 * @see getError
949 * @access private
950 * @var RSError
951 */
952 var $_error = null;
953
954 /**The constructor method.
955 *
956 * If an instance of RSDColumnValue (or a subclass of RSDColumnValue)
957 * is passed as first argument, its value and prepareString will be
958 * used. But you can overwrite the prepareString of the passed RSDColumnValue
959 * instance by passing a prepareString of your choice as second argument.
960 *
961 * @param $value mixed A string or an instance of RSDColumnValue.
962 * @param $prepareString String The prepare string (one of '?', '!', '&').
963 * If this argument is omitted '?' will be used.
964 */
965 function RSDColumnValue($value, $prepareString = false)
966 {
967 //if the an instance of RSDColumnValue was passed as first argument.
968 if (is_a($value, 'rsdcolumnvalue')) {
969 $rsdColumnValue =& $value;
970
971 $this->_value = $rsdColumnValue->getValue();
972
973 //use the prepareString of the passed RSDColumnValue object
974 //unless a prepareString was passed as second arguemnt.
975 if ($prepareString == '?' || $prepareString == '!' || $prepareString == '&' || $prepareString == '%') {
976 $this->_prepareString = $prepareString;
977 } else {
978 $this->_prepareString = $rsdColumnValue->getPrepareString();
979 }
980 } elseif (!is_string($value) && !is_int($value) && !is_float($value)) {
981 $this->_error = RSErrorManager::raiseRSError('RSDColumnValue', 'value', 'RSDColumnValue::RSDColumnValue', "The value passed as first argument is not a string nor is it an instance of RSDColumnValue.", RSERROR_NOT_STRING);
982 } else {
983 $this->_value = $value;
984
985 if ($prepareString == '?' || $prepareString == '!' || $prepareString == '&' || $prepareString == '%') {
986 $this->_prepareString = $prepareString;
987 } else {
988 $this->_prepareString = '?';
989 }
990 }
991 }
992
993 /**Returns $this->_value.
994 *
995 * @see $this->_value
996 * @access public
997 * @return String
998 */
999 function getValue()
1000 {
1001 return $this->_value;
1002 }
1003
1004 /**Returns $this->_prepareString.
1005 *
1006 * @see $this->_prepareString
1007 * @access public
1008 * @return String
1009 */
1010 function getPrepareString()
1011 {
1012 return $this->_prepareString;
1013 }
1014
1015 /**Returns the error stored in $this->_error or if no error occurred false.
1016 *
1017 * @return mixed
1018 */
1019 function getError()
1020 {
1021 if (PEAR::isError($this->_error)) {
1022 return $this->_error;
1023 } else {
1024 return false;
1025 }
1026 }
1027 }
1028 ?>
Documentation generated on Mon, 8 Dec 2003 13:13:01 +0100 by phpDocumentor 1.2.3