Version: current

      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

[Raise a SilverStripe Framework issue/bug](https://github.com/silverstripe/silverstripe-framework/issues/new)
- [Raise a SilverStripe CMS issue/bug](https://github.com/silverstripe/silverstripe-cms/issues/new)
- Please use the Silverstripe Forums to ask development related questions. -