1 <?php
2 /**
3 * Abstract database connectivity class.
4 * Sub-classes of this implement the actual database connection libraries
5 * @package sapphire
6 * @subpackage model
7 */
8 abstract class SS_Database {
9 /**
10 * Connection object to the database.
11 * @param resource
12 */
13 static $globalConn;
14
15 /**
16 * If this is false, then information about database operations
17 * will be displayed, eg creation of tables.
18 * @param boolean
19 */
20 protected $supressOutput = false;
21
22 /**
23 * Execute the given SQL query.
24 * This abstract function must be defined by subclasses as part of the actual implementation.
25 * It should return a subclass of SS_Query as the result.
26 * @param string $sql The SQL query to execute
27 * @param int $errorLevel The level of error reporting to enable for the query
28 * @return SS_Query
29 */
30 abstract function query($sql, $errorLevel = E_USER_ERROR);
31
32 /**
33 * Get the autogenerated ID from the previous INSERT query.
34 * @return int
35 */
36 abstract function getGeneratedID($table);
37
38 /**
39 * Check if the connection to the database is active.
40 * @return boolean
41 */
42 abstract function isActive();
43
44 /**
45 * Create the database and connect to it. This can be called if the
46 * initial database connection is not successful because the database
47 * does not exist.
48 *
49 * It takes no parameters, and should create the database from the information
50 * specified in the constructor.
51 *
52 * @return boolean Returns true if successful
53 */
54 abstract function createDatabase();
55
56 /**
57 * Build the connection string from input
58 * @param array $parameters The connection details
59 * @return string $connect The connection string
60 **/
61 abstract function getConnect($parameters);
62
63 /**
64 * Create a new table.
65 * @param $tableName The name of the table
66 * @param $fields A map of field names to field types
67 * @param $indexes A map of indexes
68 * @param $options An map of additional options. The available keys are as follows:
69 * - 'MSSQLDatabase'/'MySQLDatabase'/'PostgreSQLDatabase' - database-specific options such as "engine" for MySQL.
70 * - 'temporary' - If true, then a temporary table will be created
71 * @return The table name generated. This may be different from the table name, for example with temporary tables.
72 */
73 abstract function createTable($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null);
74
75 /**
76 * Alter a table's schema.
77 */
78 abstract function alterTable($table, $newFields = null, $newIndexes = null, $alteredFields = null, $alteredIndexes = null, $alteredOptions=null, $advancedOptions=null);
79
80 /**
81 * Rename a table.
82 * @param string $oldTableName The old table name.
83 * @param string $newTableName The new table name.
84 */
85 abstract function renameTable($oldTableName, $newTableName);
86
87 /**
88 * Create a new field on a table.
89 * @param string $table Name of the table.
90 * @param string $field Name of the field to add.
91 * @param string $spec The field specification, eg 'INTEGER NOT NULL'
92 */
93 abstract function createField($table, $field, $spec);
94
95 /**
96 * Change the database column name of the given field.
97 *
98 * @param string $tableName The name of the tbale the field is in.
99 * @param string $oldName The name of the field to change.
100 * @param string $newName The new name of the field
101 */
102 abstract function renameField($tableName, $oldName, $newName);
103
104 /**
105 * Get a list of all the fields for the given table.
106 * Returns a map of field name => field spec.
107 * @param string $table The table name.
108 * @return array
109 */
110 protected abstract function fieldList($table);
111
112 /**
113 * Returns a list of all tables in the database.
114 * Keys are table names in lower case, values are table names in case that
115 * database expects.
116 * @return array
117 */
118 protected abstract function tableList();
119
120
121 /**
122 * Returns true if the given table exists in the database
123 */
124 abstract function hasTable($tableName);
125
126 /**
127 * Returns the enum values available on the given field
128 */
129 abstract function enumValuesForField($tableName, $fieldName);
130
131 /**
132 * The table list, generated by the tableList() function.
133 * Used by the requireTable() function.
134 * @var array
135 */
136 protected $tableList;
137
138 /**
139 * The field list, generated by the fieldList() function.
140 * An array of maps of field name => field spec, indexed
141 * by table name.
142 * @var array
143 */
144 protected $fieldList;
145
146 /**
147 * The index list for each table, generated by the indexList() function.
148 * An map from table name to an array of index names.
149 * @var array
150 */
151 protected $indexList;
152
153
154 /**
155 * Large array structure that represents a schema update transaction
156 */
157 protected $schemaUpdateTransaction;
158
159 /**
160 * Start a schema-updating transaction.
161 * All calls to requireTable/Field/Index will keep track of the changes requested, but not actually do anything.
162 * Once
163 */
164 function beginSchemaUpdate() {
165 $this->tableList = array();
166 $tables = $this->tableList();
167 foreach($tables as $table) $this->tableList[strtolower($table)] = $table;
168
169 $this->indexList = null;
170 $this->fieldList = null;
171 $this->schemaUpdateTransaction = array();
172 }
173
174 /**
175 * Completes a schema-updated transaction, executing all the schema chagnes.
176 */
177 function endSchemaUpdate() {
178 foreach($this->schemaUpdateTransaction as $tableName => $changes) {
179 switch($changes['command']) {
180 case 'create':
181 $this->createTable($tableName, $changes['newFields'], $changes['newIndexes'], $changes['options'], @$changes['advancedOptions']);
182 break;
183
184 case 'alter':
185 $this->alterTable($tableName, $changes['newFields'], $changes['newIndexes'],
186 $changes['alteredFields'], $changes['alteredIndexes'], $changes['alteredOptions'], @$changes['advancedOptions']);
187 break;
188 }
189 }
190 $this->schemaUpdateTransaction = null;
191 }
192
193 /**
194 * Cancels the schema updates requested after a beginSchemaUpdate() call.
195 */
196 function cancelSchemaUpdate() {
197 $this->schemaUpdateTransaction = null;
198 }
199
200 /**
201 * Returns true if schema modifications were requested after a beginSchemaUpdate() call.
202 */
203 function doesSchemaNeedUpdating() {
204 return (bool)$this->schemaUpdateTransaction;
205 }
206
207 // Transactional schema altering functions - they don't do anyhting except for update schemaUpdateTransaction
208
209 /**
210 * @param string $table
211 * @param string $options
212 */
213 function transCreateTable($table, $options = null, $advanced_options = null) {
214 $this->schemaUpdateTransaction[$table] = array('command' => 'create', 'newFields' => array(), 'newIndexes' => array(), 'options' => $options, 'advancedOptions' => $advanced_options);
215 }
216
217 /**
218 * @param string $table
219 * @param array $options
220 */
221 function transAlterTable($table, $options, $advanced_options) {
222 $this->transInitTable($table);
223 $this->schemaUpdateTransaction[$table]['alteredOptions'] = $options;
224 $this->schemaUpdateTransaction[$table]['advancedOptions'] = $advanced_options;
225 }
226
227 function transCreateField($table, $field, $schema) {
228 $this->transInitTable($table);
229 $this->schemaUpdateTransaction[$table]['newFields'][$field] = $schema;
230 }
231 function transCreateIndex($table, $index, $schema) {
232 $this->transInitTable($table);
233 $this->schemaUpdateTransaction[$table]['newIndexes'][$index] = $schema;
234 }
235 function transAlterField($table, $field, $schema) {
236 $this->transInitTable($table);
237 $this->schemaUpdateTransaction[$table]['alteredFields'][$field] = $schema;
238 }
239 function transAlterIndex($table, $index, $schema) {
240 $this->transInitTable($table);
241 $this->schemaUpdateTransaction[$table]['alteredIndexes'][$index] = $schema;
242 }
243
244 /**
245 * Handler for the other transXXX methods - mark the given table as being altered
246 * if it doesn't already exist
247 */
248 protected function transInitTable($table) {
249 if(!isset($this->schemaUpdateTransaction[$table])) {
250 $this->schemaUpdateTransaction[$table] = array(
251 'command' => 'alter',
252 'newFields' => array(),
253 'newIndexes' => array(),
254 'alteredFields' => array(),
255 'alteredIndexes' => array(),
256 'alteredOptions' => ''
257 );
258 }
259 }
260
261
262 /**
263 * Generate the following table in the database, modifying whatever already exists
264 * as necessary.
265 * @todo Change detection for CREATE TABLE $options other than "Engine"
266 *
267 * @param string $table The name of the table
268 * @param string $fieldSchema A list of the fields to create, in the same form as DataObject::$db
269 * @param string $indexSchema A list of indexes to create. See {@link requireIndex()}
270 * @param array $options
271 */
272 function requireTable($table, $fieldSchema = null, $indexSchema = null, $hasAutoIncPK=true, $options = Array(), $extensions=false) {
273
274 if(!isset($this->tableList[strtolower($table)])) {
275 $this->transCreateTable($table, $options, $extensions);
276 $this->alterationMessage("Table $table: created","created");
277 } else {
278 $this->checkAndRepairTable($table, $options);
279
280 // Check if options changed
281 $tableOptionsChanged = false;
282 if(isset($options[get_class($this)]) || true) {
283 if(isset($options[get_class($this)])) {
284 if(preg_match('/ENGINE=([^\s]*)/', $options[get_class($this)], $alteredEngineMatches)) {
285 $alteredEngine = $alteredEngineMatches[1];
286 $tableStatus = DB::query(sprintf(
287 'SHOW TABLE STATUS LIKE \'%s\'',
288 $table
289 ))->first();
290 $tableOptionsChanged = ($tableStatus['Engine'] != $alteredEngine);
291 }
292 }
293 }
294
295 if($tableOptionsChanged || ($extensions && DB::getConn()->supportsExtensions()))
296 $this->transAlterTable($table, $options, $extensions);
297
298 }
299
300 //DB ABSTRACTION: we need to convert this to a db-specific version:
301 $this->requireField($table, 'ID', DB::getConn()->IdColumn(false, $hasAutoIncPK));
302
303 // Create custom fields
304 if($fieldSchema) {
305 foreach($fieldSchema as $fieldName => $fieldSpec) {
306
307 //Is this an array field?
308 $arrayValue='';
309 if(strpos($fieldSpec, '[')!==false){
310 //If so, remove it and store that info separately
311 $pos=strpos($fieldSpec, '[');
312 $arrayValue=substr($fieldSpec, $pos);
313 $fieldSpec=substr($fieldSpec, 0, $pos);
314 }
315
316 $fieldObj = Object::create_from_string($fieldSpec, $fieldName);
317 $fieldObj->arrayValue=$arrayValue;
318
319 $fieldObj->setTable($table);
320 $fieldObj->requireField();
321 }
322 }
323
324 // Create custom indexes
325 if($indexSchema) {
326 foreach($indexSchema as $indexName => $indexDetails) {
327 $this->requireIndex($table, $indexName, $indexDetails);
328 }
329 }
330 }
331
332 /**
333 * If the given table exists, move it out of the way by renaming it to _obsolete_(tablename).
334 * @param string $table The table name.
335 */
336 function dontRequireTable($table) {
337 if(isset($this->tableList[strtolower($table)])) {
338 $suffix = '';
339 while(isset($this->tableList[strtolower("_obsolete_{$table}$suffix")])) {
340 $suffix = $suffix ? ($suffix+1) : 2;
341 }
342 $this->renameTable($table, "_obsolete_{$table}$suffix");
343 $this->alterationMessage("Table $table: renamed to _obsolete_{$table}$suffix","obsolete");
344 }
345 }
346
347 /**
348 * Generate the given index in the database, modifying whatever already exists as necessary.
349 *
350 * The keys of the array are the names of the index.
351 * The values of the array can be one of:
352 * - true: Create a single column index on the field named the same as the index.
353 * - array('type' => 'index|unique|fulltext', 'value' => 'FieldA, FieldB'): This gives you full
354 * control over the index.
355 *
356 * @param string $table The table name.
357 * @param string $index The index name.
358 * @param string|boolean $spec The specification of the index. See requireTable() for more information.
359 */
360 function requireIndex($table, $index, $spec) {
361 $newTable = false;
362
363 //DB Abstraction: remove this ===true option as a possibility?
364 if($spec === true) {
365 $spec = "($index)";
366 }
367
368 //Indexes specified as arrays cannot be checked with this line: (it flattens out the array)
369 if(!is_array($spec))
370 $spec = ereg_replace(" *, *",",",$spec);
371
372 if(!isset($this->tableList[strtolower($table)])) $newTable = true;
373
374 if(!$newTable && !isset($this->indexList[$table])) {
375 $this->indexList[$table] = $this->indexList($table);
376 }
377
378 //Fix up the index for database purposes
379 $index=DB::getConn()->getDbSqlDefinition($table, $index, null, true);
380
381 //Fix the key for database purposes
382 $index_alt=DB::getConn()->modifyIndex($index, $spec);
383
384 if(!$newTable) {
385 if(isset($this->indexList[$table][$index_alt])) {
386 if(is_array($this->indexList[$table][$index_alt])) {
387 $array_spec = $this->indexList[$table][$index_alt]['spec'];
388 } else {
389 $array_spec = $this->indexList[$table][$index_alt];
390 }
391 }
392 }
393
394 if($newTable || !isset($this->indexList[$table][$index_alt])) {
395 $this->transCreateIndex($table, $index, $spec);
396 $textSpec = $spec;
397 if (is_array($spec)) {
398 $textSpec = implode(' ', $spec);
399 }
400 $this->alterationMessage("Index $table.$index: created as $textSpec","created");
401 } else if($array_spec != DB::getConn()->convertIndexSpec($spec)) {
402 $this->transAlterIndex($table, $index, $spec);
403 $spec_msg=DB::getConn()->convertIndexSpec($spec);
404 $this->alterationMessage("Index $table.$index: changed to $spec_msg <i style=\"color: #AAA\">(from {$array_spec})</i>","changed");
405 }
406 }
407
408 /**
409 * Generate the given field on the table, modifying whatever already exists as necessary.
410 * @param string $table The table name.
411 * @param string $field The field name.
412 * @param array|string $spec The field specification. If passed in array syntax, the specific database
413 * driver takes care of the ALTER TABLE syntax. If passed as a string, its assumed to
414 * be prepared as a direct SQL framgment ready for insertion into ALTER TABLE. In this case you'll
415 * need to take care of database abstraction in your DBField subclass.
416 */
417 function requireField($table, $field, $spec) {
418 //TODO: this is starting to get extremely fragmented.
419 //There are two different versions of $spec floating around, and their content changes depending
420 //on how they are structured. This needs to be tidied up.
421 $fieldValue = null;
422 $newTable = false;
423
424 Profiler::mark('requireField');
425
426 // backwards compatibility patch for pre 2.4 requireField() calls
427 $spec_orig=$spec;
428
429 if(!is_string($spec)) {
430 $spec['parts']['name'] = $field;
431 $spec_orig['parts']['name'] = $field;
432 //Convert the $spec array into a database-specific string
433 $spec=DB::getConn()->$spec['type']($spec['parts'], true);
434 }
435
436 // Collations didn't come in until MySQL 4.1. Anything earlier will throw a syntax error if you try and use
437 // collations.
438 // TODO: move this to the MySQLDatabase file, or drop it altogether?
439 if(!$this->supportsCollations()) {
440 $spec = eregi_replace(' *character set [^ ]+( collate [^ ]+)?( |$)','\\2',$spec);
441 }
442
443 if(!isset($this->tableList[strtolower($table)])) $newTable = true;
444
445 if(!$newTable && !isset($this->fieldList[$table])) {
446 $this->fieldList[$table] = $this->fieldList($table);
447 }
448
449 if(is_array($spec)) {
450 $specValue = DB::getConn()->$spec_orig['type']($spec_orig['parts']);
451 } else {
452 $specValue = $spec;
453 }
454
455 // We need to get db-specific versions of the ID column:
456 if($spec_orig==DB::getConn()->IdColumn() || $spec_orig==DB::getConn()->IdColumn(true))
457 $specValue=DB::getConn()->IdColumn(true);
458
459 if(!$newTable) {
460 if(isset($this->fieldList[$table][$field])) {
461 if(is_array($this->fieldList[$table][$field])) {
462 $fieldValue = $this->fieldList[$table][$field]['data_type'];
463 } else {
464 $fieldValue = $this->fieldList[$table][$field];
465 }
466 }
467 }
468
469 // Get the version of the field as we would create it. This is used for comparison purposes to see if the
470 // existing field is different to what we now want
471 if(is_array($spec_orig)) {
472 $spec_orig=DB::getConn()->$spec_orig['type']($spec_orig['parts']);
473 }
474
475 if($newTable || $fieldValue=='') {
476 Profiler::mark('createField');
477
478 $this->transCreateField($table, $field, $spec_orig);
479 Profiler::unmark('createField');
480 $this->alterationMessage("Field $table.$field: created as $spec_orig","created");
481 } else if($fieldValue != $specValue) {
482 // If enums/sets are being modified, then we need to fix existing data in the table.
483 // Update any records where the enum is set to a legacy value to be set to the default.
484 // One hard-coded exception is SiteTree - the default for this is Page.
485 foreach(array('enum','set') as $enumtype) {
486 if(preg_match("/^$enumtype/i",$specValue)) {
487 $newStr = preg_replace("/(^$enumtype\s*\(')|('$\).*)/i","",$spec_orig);
488 $new = preg_split("/'\s*,\s*'/", $newStr);
489
490 $oldStr = preg_replace("/(^$enumtype\s*\(')|('$\).*)/i","", $fieldValue);
491 $old = preg_split("/'\s*,\s*'/", $newStr);
492
493 $holder = array();
494 foreach($old as $check) {
495 if(!in_array($check, $new)) {
496 $holder[] = $check;
497 }
498 }
499 if(count($holder)) {
500 $default = explode('default ', $spec_orig);
501 $default = $default[1];
502 if($default == "'SiteTree'") $default = "'Page'";
503 $query = "UPDATE \"$table\" SET $field=$default WHERE $field IN (";
504 for($i=0;$i+1<count($holder);$i++) {
505 $query .= "'{$holder[$i]}', ";
506 }
507 $query .= "'{$holder[$i]}')";
508 DB::query($query);
509 $amount = DB::affectedRows();
510 $this->alterationMessage("Changed $amount rows to default value of field $field (Value: $default)");
511 }
512 }
513 }
514 Profiler::mark('alterField');
515 $this->transAlterField($table, $field, $spec_orig);
516 Profiler::unmark('alterField');
517 $this->alterationMessage("Field $table.$field: changed to $specValue <i style=\"color: #AAA\">(from {$fieldValue})</i>","changed");
518 }
519 Profiler::unmark('requireField');
520 }
521
522 /**
523 * If the given field exists, move it out of the way by renaming it to _obsolete_(fieldname).
524 *
525 * @param string $table
526 * @param string $fieldName
527 */
528 function dontRequireField($table, $fieldName) {
529 $fieldList = $this->fieldList($table);
530 if(array_key_exists($fieldName, $fieldList)) {
531 $suffix = '';
532 while(isset($fieldList[strtolower("_obsolete_{$fieldName}$suffix")])) {
533 $suffix = $suffix ? ($suffix+1) : 2;
534 }
535 $this->renameField($table, $fieldName, "_obsolete_{$fieldName}$suffix");
536 $this->alterationMessage("Field $table.$fieldName: renamed to $table._obsolete_{$fieldName}$suffix","obsolete");
537 }
538 }
539
540 /**
541 * Execute a complex manipulation on the database.
542 * A manipulation is an array of insert / or update sequences. The keys of the array are table names,
543 * and the values are map containing 'command' and 'fields'. Command should be 'insert' or 'update',
544 * and fields should be a map of field names to field values, including quotes. The field value can
545 * also be a SQL function or similar.
546 * @param array $manipulation
547 */
548 function manipulate($manipulation) {
549 if($manipulation) foreach($manipulation as $table => $writeInfo) {
550
551 if(isset($writeInfo['fields']) && $writeInfo['fields']) {
552 $fieldList = $columnList = $valueList = array();
553 foreach($writeInfo['fields'] as $fieldName => $fieldVal) {
554 $fieldList[] = "\"$fieldName\" = $fieldVal";
555 $columnList[] = "\"$fieldName\"";
556
557 // Empty strings inserted as null in INSERTs. Replacement of SS_Database::replace_with_null().
558 if($fieldVal === "''") $valueList[] = "null";
559 else $valueList[] = $fieldVal;
560 }
561
562 if(!isset($writeInfo['where']) && isset($writeInfo['id'])) {
563 $writeInfo['where'] = "\"ID\" = " . (int)$writeInfo['id'];
564 }
565
566 switch($writeInfo['command']) {
567 case "update":
568 // Test to see if this update query shouldn't, in fact, be an insert
569 if($this->query("SELECT \"ID\" FROM \"$table\" WHERE $writeInfo[where]")->value()) {
570 $fieldList = implode(", ", $fieldList);
571 $sql = "UPDATE \"$table\" SET $fieldList where $writeInfo[where]";
572 $this->query($sql);
573 break;
574 }
575
576 // ...if not, we'll skip on to the insert code
577
578 case "insert":
579 if(!isset($writeInfo['fields']['ID']) && isset($writeInfo['id'])) {
580 $columnList[] = "\"ID\"";
581 $valueList[] = (int)$writeInfo['id'];
582 }
583
584 $columnList = implode(", ", $columnList);
585 $valueList = implode(", ", $valueList);
586 $sql = "INSERT INTO \"$table\" ($columnList) VALUES ($valueList)";
587 $this->query($sql);
588 break;
589
590 default:
591 $sql = null;
592 user_error("SS_Database::manipulate() Can't recognise command '$writeInfo[command]'", E_USER_ERROR);
593 }
594 }
595 }
596 }
597
598 /** Replaces "\'\'" with "null", recursively walks through the given array.
599 * @param string $array Array where the replacement should happen
600 */
601 static function replace_with_null(&$array) {
602 $array = ereg_replace('= *\'\'', "= null", $array);
603
604 if(is_array($array)) {
605 foreach($array as $key => $value) {
606 if(is_array($value)) {
607 array_walk($array, array(SS_Database, 'replace_with_null'));
608 }
609 }
610 }
611
612 return $array;
613 }
614
615 /**
616 * Error handler for database errors.
617 * All database errors will call this function to report the error. It isn't a static function;
618 * it will be called on the object itself and as such can be overridden in a subclass.
619 * @todo hook this into a more well-structured error handling system.
620 * @param string $msg The error message.
621 * @param int $errorLevel The level of the error to throw.
622 */
623 function databaseError($msg, $errorLevel = E_USER_ERROR) {
624 user_error($msg, $errorLevel);
625 }
626
627 /**
628 * Enable supression of database messages.
629 */
630 function quiet() {
631 $this->supressOutput = true;
632 }
633
634 /**
635 * Show a message about database alteration
636 */
637 function alterationMessage($message,$type=""){
638 if(!$this->supressOutput) {
639 if(Director::is_cli()) {
640 switch ($type){
641 case "created":
642 case "changed":
643 case "repaired":
644 $sign = "+";
645 break;
646 case "obsolete":
647 case "deleted":
648 $sign = '-';
649 break;
650 case "error":
651 $sign = "!";
652 break;
653 default:
654 $sign=" ";
655 }
656 $message = strip_tags($message);
657 echo " $sign $message\n";
658 } else {
659 switch ($type){
660 case "created":
661 $color = "green";
662 break;
663 case "obsolete":
664 $color = "red";
665 break;
666 case "error":
667 $color = "red";
668 break;
669 case "deleted":
670 $color = "red";
671 break;
672 case "changed":
673 $color = "blue";
674 break;
675 case "repaired":
676 $color = "blue";
677 break;
678 default:
679 $color="";
680 }
681 echo "<li style=\"color: $color\">$message</li>";
682 }
683 }
684 }
685
686 /**
687 * Convert a SQLQuery object into a SQL statement
688 */
689 public function sqlQueryToString(SQLQuery $sqlQuery) {
690 if (!$sqlQuery->from) return '';
691 $distinct = $sqlQuery->distinct ? "DISTINCT " : "";
692 if($sqlQuery->delete) {
693 $text = "DELETE ";
694 } else if($sqlQuery->select) {
695 $text = "SELECT $distinct" . implode(", ", $sqlQuery->select);
696 }
697 $text .= " FROM " . implode(" ", $sqlQuery->from);
698
699 if($sqlQuery->where) $text .= " WHERE (" . $sqlQuery->getFilter(). ")";
700 if($sqlQuery->groupby) $text .= " GROUP BY " . implode(", ", $sqlQuery->groupby);
701 if($sqlQuery->having) $text .= " HAVING ( " . implode(" ) AND ( ", $sqlQuery->having) . " )";
702 if($sqlQuery->orderby) $text .= " ORDER BY " . $sqlQuery->orderby;
703
704 if($sqlQuery->limit) {
705 $limit = $sqlQuery->limit;
706 // Pass limit as array or SQL string value
707 if(is_array($limit)) {
708 if(!array_key_exists('limit',$limit)) user_error('SQLQuery::limit(): Wrong format for $limit', E_USER_ERROR);
709
710 if(isset($limit['start']) && is_numeric($limit['start']) && isset($limit['limit']) && is_numeric($limit['limit'])) {
711 $combinedLimit = "$limit[limit] OFFSET $limit[start]";
712 } elseif(isset($limit['limit']) && is_numeric($limit['limit'])) {
713 $combinedLimit = (int)$limit['limit'];
714 } else {
715 $combinedLimit = false;
716 }
717 if(!empty($combinedLimit)) $text .= " LIMIT " . $combinedLimit;
718
719 } else {
720 $text .= " LIMIT " . $sqlQuery->limit;
721 }
722 }
723
724 return $text;
725 }
726
727 /**
728 * Function to return an SQL datetime expression that can be used with the adapter in use
729 * used for querying a datetime in a certain format
730 * @param string $date to be formated, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
731 * @param string $format to be used, supported specifiers:
732 * %Y = Year (four digits)
733 * %m = Month (01..12)
734 * %d = Day (01..31)
735 * %H = Hour (00..23)
736 * %i = Minutes (00..59)
737 * %s = Seconds (00..59)
738 * %U = unix timestamp, can only be used on it's own
739 * @return string SQL datetime expression to query for a formatted datetime
740 */
741 abstract function formattedDatetimeClause($date, $format);
742
743 /**
744 * Function to return an SQL datetime expression that can be used with the adapter in use
745 * used for querying a datetime addition
746 * @param string $date, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
747 * @param string $interval to be added, use the format [sign][integer] [qualifier], e.g. -1 Day, +15 minutes, +1 YEAR
748 * supported qualifiers:
749 * - years
750 * - months
751 * - days
752 * - hours
753 * - minutes
754 * - seconds
755 * This includes the singular forms as well
756 * @return string SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition
757 */
758 abstract function datetimeIntervalClause($date, $interval);
759
760 /**
761 * Function to return an SQL datetime expression that can be used with the adapter in use
762 * used for querying a datetime substraction
763 * @param string $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
764 * @param string $date2 to be substracted of $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
765 * @return string SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction
766 */
767 abstract function datetimeDifferenceClause($date1, $date2);
768
769 }
770
771 /**
772 * Abstract query-result class.
773 * Once again, this should be subclassed by an actual database implementation. It will only
774 * ever be constructed by a subclass of SS_Database. The result of a database query - an iteratable object that's returned by DB::SS_Query
775 *
776 * Primarily, the SS_Query class takes care of the iterator plumbing, letting the subclasses focusing
777 * on providing the specific data-access methods that are required: {@link nextRecord()}, {@link numRecords()}
778 * and {@link seek()}
779 * @package sapphire
780 * @subpackage model
781 */
782 abstract class SS_Query implements Iterator {
783 /**
784 * The current record in the interator.
785 * @var array
786 */
787 private $currentRecord = null;
788
789 /**
790 * The number of the current row in the interator.
791 * @var int
792 */
793 private $rowNum = -1;
794
795 /**
796 * Flag to keep track of whether iteration has begun, to prevent unnecessary seeks
797 */
798 private $queryHasBegun = false;
799
800 /**
801 * Return an array containing all the values from a specific column. If no column is set, then the first will be
802 * returned
803 *
804 * @param string $column
805 * @return array
806 */
807 public function column($column = null) {
808 $result = array();
809
810 while($record = $this->next()) {
811 if($column) $result[] = $record[$column];
812 else $result[] = $record[key($record)];
813 }
814
815 return $result;
816 }
817
818 /**
819 * Return an array containing all values in the leftmost column, where the keys are the
820 * same as the values.
821 * @return array
822 */
823 public function keyedColumn() {
824 $column = array();
825 foreach($this as $record) {
826 $val = $record[key($record)];
827 $column[$val] = $val;
828 }
829 return $column;
830 }
831
832 /**
833 * Return a map from the first column to the second column.
834 * @return array
835 */
836 public function map() {
837 $column = array();
838 foreach($this as $record) {
839 $key = reset($record);
840 $val = next($record);
841 $column[$key] = $val;
842 }
843 return $column;
844 }
845
846 /**
847 * Returns the next record in the iterator.
848 * @return array
849 */
850 public function record() {
851 return $this->next();
852 }
853
854 /**
855 * Returns the first column of the first record.
856 * @return string
857 */
858 public function value() {
859 $record = $this->next();
860 if($record) return $record[key($record)];
861 }
862
863 /**
864 * Return an HTML table containing the full result-set
865 */
866 public function table() {
867 $first = true;
868 $result = "<table>\n";
869
870 foreach($this as $record) {
871 if($first) {
872 $result .= "<tr>";
873 foreach($record as $k => $v) {
874 $result .= "<th>" . Convert::raw2xml($k) . "</th> ";
875 }
876 $result .= "</tr> \n";
877 }
878
879 $result .= "<tr>";
880 foreach($record as $k => $v) {
881 $result .= "<td>" . Convert::raw2xml($v) . "</td> ";
882 }
883 $result .= "</tr> \n";
884
885 $first = false;
886 }
887
888 if($first) return "No records found";
889 return $result;
890 }
891
892 /**
893 * Iterator function implementation. Rewind the iterator to the first item and return it.
894 * Makes use of {@link seek()} and {@link numRecords()}, takes care of the plumbing.
895 * @return array
896 */
897 public function rewind() {
898 if($this->queryHasBegun && $this->numRecords() > 0) {
899 $this->queryHasBegun = false;
900 return $this->seek(0);
901 }
902 }
903
904 /**
905 * Iterator function implementation. Return the current item of the iterator.
906 * @return array
907 */
908 public function current() {
909 if(!$this->currentRecord) {
910 return $this->next();
911 } else {
912 return $this->currentRecord;
913 }
914 }
915
916 /**
917 * Iterator function implementation. Return the first item of this iterator.
918 * @return array
919 */
920 public function first() {
921 $this->rewind();
922 return $this->current();
923 }
924
925 /**
926 * Iterator function implementation. Return the row number of the current item.
927 * @return int
928 */
929 public function key() {
930 return $this->rowNum;
931 }
932
933 /**
934 * Iterator function implementation. Return the next record in the iterator.
935 * Makes use of {@link nextRecord()}, takes care of the plumbing.
936 * @return array
937 */
938 public function next() {
939 $this->queryHasBegun = true;
940 $this->currentRecord = $this->nextRecord();
941 $this->rowNum++;
942 return $this->currentRecord;
943 }
944
945 /**
946 * Iterator function implementation. Check if the iterator is pointing to a valid item.
947 * @return boolean
948 */
949 public function valid() {
950 if(!$this->currentRecord) $this->next();
951 return $this->currentRecord !== false;
952 }
953
954 /**
955 * Return the next record in the query result.
956 * @return array
957 */
958 abstract function nextRecord();
959
960 /**
961 * Return the total number of items in the query result.
962 * @return int
963 */
964 abstract function numRecords();
965
966 /**
967 * Go to a specific row number in the query result and return the record.
968 * @param int $rowNum Tow number to go to.
969 * @return array
970 */
971 abstract function seek($rowNum);
972 }
973
974 ?>
975