[Sommaire] [Imprimer]

   1  <?php
   2  // +----------------------------------------------------------------------+
   3  // | PHP Version 4                                                        |
   4  // +----------------------------------------------------------------------+
   5  // | Copyright (c) 1998-2004 Manuel Lemos, Tomas V.V.Cox,                 |
   6  // | Stig. S. Bakken, Lukas Smith                                         |
   7  // | All rights reserved.                                                 |
   8  // +----------------------------------------------------------------------+
   9  // | MDB is a merge of PEAR DB and Metabases that provides a unified DB   |
  10  // | API as well as database abstraction for PHP applications.            |
  11  // | This LICENSE is in the BSD license style.                            |
  12  // |                                                                      |
  13  // | Redistribution and use in source and binary forms, with or without   |
  14  // | modification, are permitted provided that the following conditions   |
  15  // | are met:                                                             |
  16  // |                                                                      |
  17  // | Redistributions of source code must retain the above copyright       |
  18  // | notice, this list of conditions and the following disclaimer.        |
  19  // |                                                                      |
  20  // | Redistributions in binary form must reproduce the above copyright    |
  21  // | notice, this list of conditions and the following disclaimer in the  |
  22  // | documentation and/or other materials provided with the distribution. |
  23  // |                                                                      |
  24  // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken,    |
  25  // | Lukas Smith nor the names of his contributors may be used to endorse |
  26  // | or promote products derived from this software without specific prior|
  27  // | written permission.                                                  |
  28  // |                                                                      |
  29  // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS  |
  30  // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT    |
  31  // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS    |
  32  // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE      |
  33  // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,          |
  34  // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
  35  // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
  36  // |  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED  |
  37  // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT          |
  38  // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
  39  // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE          |
  40  // | POSSIBILITY OF SUCH DAMAGE.                                          |
  41  // +----------------------------------------------------------------------+
  42  // | Author: Frank M. Kromann <frank@kromann.info                         |
  43  // +----------------------------------------------------------------------+
  44  //
  45  // $Id: mssql.php,v 1.4.4.8 2004/04/10 08:02:31 lsmith Exp $
  46  //
  47  
  48  if(!defined('MDB_MANAGER_MSSQL_INCLUDED'))
  49  {
  50      define('MDB_MANAGER_MSSQL_INCLUDED',1);
  51  
  52  require_once ('MDB/Modules/Manager/Common.php');
  53  
  54  /**
  55   * MDB MSSQL driver for the management modules
  56   *
  57   * @package MDB
  58   * @category Database
  59   * @author  Frank M. Kromann <frank@kromann.info
  60   */
  61  class MDB_Manager_mssql extends MDB_Manager_Common
  62  {
  63      // }}}
  64      // {{{ createDatabase()
  65  
  66      /**
  67       * create a new database
  68       *
  69       * @param object    $dbs        database object that is extended by this class
  70       * @param string $name name of the database that should be created
  71       * @return mixed MDB_OK on success, a MDB error on failure
  72       * @access public
  73       */
  74      function createDatabase(&$db, $name)
  75      {
  76          $DatabaseDevice = isset($db->options["DatabaseDevice"]) ? $db->options["DatabaseDevice"] : "DEFAULT";
  77          $DatabaseSize = isset($db->options["DatabaseSize"]) ? ", SIZE=".$db->options["DatabaseSize"] : "";
  78          return($db->standaloneQuery("CREATE DATABASE $name ON ".$DatabaseDevice.$DatabaseSize));
  79      }
  80  
  81      // }}}
  82      // {{{ dropDatabase()
  83  
  84      /**
  85       * drop an existing database
  86       *
  87       * @param object    $dbs        database object that is extended by this class
  88       * @param string $name name of the database that should be dropped
  89       * @return mixed MDB_OK on success, a MDB error on failure
  90       * @access public
  91       */
  92      function dropDatabase(&$db, $name)
  93      {
  94          return($db->standaloneQuery("DROP DATABASE $name"));
  95      }
  96  
  97      // }}}
  98      // {{{ alterTable()
  99  
 100      /**
 101       * alter an existing table
 102       *
 103       * @param object    $dbs        database object that is extended by this class
 104       * @param string $name         name of the table that is intended to be changed.
 105       * @param array $changes     associative array that contains the details of each type
 106       *                             of change that is intended to be performed. The types of
 107       *                             changes that are currently supported are defined as follows:
 108       *
 109       *                             name
 110       *
 111       *                                New name for the table.
 112       *
 113       *                            AddedFields
 114       *
 115       *                                Associative array with the names of fields to be added as
 116       *                                 indexes of the array. The value of each entry of the array
 117       *                                 should be set to another associative array with the properties
 118       *                                 of the fields to be added. The properties of the fields should
 119       *                                 be the same as defined by the Metabase parser.
 120       *
 121       *                                Additionally, there should be an entry named Declaration that
 122       *                                 is expected to contain the portion of the field declaration already
 123       *                                 in DBMS specific SQL code as it is used in the CREATE TABLE statement.
 124       *
 125       *                            RemovedFields
 126       *
 127       *                                Associative array with the names of fields to be removed as indexes
 128       *                                 of the array. Currently the values assigned to each entry are ignored.
 129       *                                 An empty array should be used for future compatibility.
 130       *
 131       *                            RenamedFields
 132       *
 133       *                                Associative array with the names of fields to be renamed as indexes
 134       *                                 of the array. The value of each entry of the array should be set to
 135       *                                 another associative array with the entry named name with the new
 136       *                                 field name and the entry named Declaration that is expected to contain
 137       *                                 the portion of the field declaration already in DBMS specific SQL code
 138       *                                 as it is used in the CREATE TABLE statement.
 139       *
 140       *                            ChangedFields
 141       *
 142       *                                Associative array with the names of the fields to be changed as indexes
 143       *                                 of the array. Keep in mind that if it is intended to change either the
 144       *                                 name of a field and any other properties, the ChangedFields array entries
 145       *                                 should have the new names of the fields as array indexes.
 146       *
 147       *                                The value of each entry of the array should be set to another associative
 148       *                                 array with the properties of the fields to that are meant to be changed as
 149       *                                 array entries. These entries should be assigned to the new values of the
 150       *                                 respective properties. The properties of the fields should be the same
 151       *                                 as defined by the Metabase parser.
 152       *
 153       *                                If the default property is meant to be added, removed or changed, there
 154       *                                 should also be an entry with index ChangedDefault assigned to 1. Similarly,
 155       *                                 if the notnull constraint is to be added or removed, there should also be
 156       *                                 an entry with index ChangedNotNull assigned to 1.
 157       *
 158       *                                Additionally, there should be an entry named Declaration that is expected
 159       *                                 to contain the portion of the field changed declaration already in DBMS
 160       *                                 specific SQL code as it is used in the CREATE TABLE statement.
 161       *                            Example
 162       *                                array(
 163       *                                    'name' => 'userlist',
 164       *                                    'AddedFields' => array(
 165       *                                        'quota' => array(
 166       *                                            'type' => 'integer',
 167       *                                            'unsigned' => 1
 168       *                                            'Declaration' => 'quota INT'
 169       *                                        )
 170       *                                    ),
 171       *                                    'RemovedFields' => array(
 172       *                                        'file_limit' => array(),
 173       *                                        'time_limit' => array()
 174       *                                        ),
 175       *                                    'ChangedFields' => array(
 176       *                                        'gender' => array(
 177       *                                            'default' => 'M',
 178       *                                            'ChangeDefault' => 1,
 179       *                                            'Declaration' => "gender CHAR(1) DEFAULT 'M'"
 180       *                                        )
 181       *                                    ),
 182       *                                    'RenamedFields' => array(
 183       *                                        'sex' => array(
 184       *                                            'name' => 'gender',
 185       *                                            'Declaration' => "gender CHAR(1) DEFAULT 'M'"
 186       *                                        )
 187       *                                    )
 188       *                                )
 189       *
 190       * @param boolean $check     indicates whether the function should just check if the DBMS driver
 191       *                             can perform the requested table alterations if the value is true or
 192       *                             actually perform them otherwise.
 193       * @access public
 194       *
 195        * @return mixed MDB_OK on success, a MDB error on failure
 196       */
 197      function alterTable(&$db, $name, $changes, $check)
 198      {
 199          if ($check) {
 200              for ($change = 0, reset($changes);
 201                  $change < count($changes);
 202                  next($changes), $change++
 203              ) {
 204                  switch (key($changes)) {
 205                      case "AddedFields":
 206                          break;
 207                      case "RemovedFields":
 208                      case "name":
 209                      case "RenamedFields":
 210                      case "ChangedFields":
 211                      default:
 212                          return($db->raiseError(MDB_ERROR_CANNOT_ALTER, NULL, NULL,
 213                              'Alter table: change type "'.key($changes).'" not yet supported'));
 214                  }
 215              }
 216              return(MDB_OK);
 217          } else {
 218              if (isset($changes[$change = 'RemovedFields'])
 219                  || isset($changes[$change = 'name'])
 220                  || isset($changes[$change = 'RenamedFields'])
 221                  || isset($changes[$change = 'ChangedFields'])
 222              ) {
 223                  return($db->raiseError(MDB_ERROR_CANNOT_ALTER, NULL, NULL,
 224                      'Alter table: change type "'.$change.'" is not supported by the server"'));
 225              }
 226              $query='';
 227              if (isset($changes['AddedFields'])) {
 228                  if(strcmp($query, '')) {
 229                      $query.= ', ';
 230                  }
 231                  $query.= 'ADD ';
 232                  $fields = $changes['AddedFields'];
 233                  for ($field = 0, reset($fields);
 234                      $field < count($fields);
 235                      next($fields), $field++)
 236                  {
 237                      if (strcmp($query, '')) {
 238                          $query.= ', ';
 239                      }
 240                      $query.= $fields[key($fields)]['Declaration'];
 241                  }
 242              }
 243              return(strcmp($query, '') ? $db->query("ALTER TABLE $name $query") : MDB_OK);
 244          }
 245      }
 246  
 247      // }}}
 248      // {{{ listTables()
 249  
 250      /**
 251       * list all tables in the current database
 252       *
 253       * @param object    $db        database object that is extended by this class
 254       * @return mixed data array on success, a MDB error on failure
 255       * @access public
 256       **/
 257      function listTables(&$db)
 258      {
 259          $query = 'EXECUTE sp_tables @table_type = "\'TABLE\'"';
 260          $table_names = $db->queryCol($query, null, 2);
 261          if (MDB::isError($table_names)) {
 262              return($table_names);
 263          }
 264          $tables = array();
 265          for ($i = 0, $j = count($table_names); $i <$j; ++$i) {
 266              if (!$this->_isSequenceName($db, $table_names[$i])) {
 267                  $tables[] = $table_names[$i];
 268              }
 269          }
 270          return($tables);
 271      }
 272  
 273      // }}}
 274      // {{{ listTableFields()
 275  
 276      /**
 277       * list all fields in a tables in the current database
 278       *
 279       * @param object    $db        database object that is extended by this class
 280       * @param string $table name of table that should be used in method
 281       * @return mixed data array on success, a MDB error on failure
 282       * @access public
 283       */
 284      function listTableFields(&$db, $table)
 285      {
 286          $result = $db->query("SELECT * FROM $table");
 287          if( MDB::isError($result)) {
 288              return($result);
 289          }
 290          $columns = $db->getColumnNames($result);
 291          if (MDB::isError($columns)) {
 292              $db->freeResult($columns);
 293              return $columns;
 294          }
 295          return(array_flip($columns));
 296      }
 297  
 298      // }}}
 299      // {{{ getTableFieldDefinition()
 300  
 301      /**
 302       * get the stucture of a field into an array; this method is still alpha quality!
 303       *
 304       * @param object    $db        database object that is extended by this class
 305       * @param string    $table         name of table that should be used in method
 306       * @param string    $field_name     name of field that should be used in method
 307       * @return mixed data array on success, a MDB error on failure
 308       * @access public
 309       */
 310      function getTableFieldDefinition(&$db, $table, $field_name)
 311      {
 312          $columns = $db->queryRow("EXEC sp_columns @table_name='$table',
 313                     @column_name='$field_name'", NULL, MDB_FETCHMODE_ASSOC );
 314          if (MDB::isError($columns)) {
 315              return($columns);
 316          }
 317          if ($db->options['optimize'] != 'portability') {
 318              array_change_key_case($columns);
 319          }
 320          if (!isset($columns[$column = 'column_name'])
 321              || !isset($columns[$column = 'type_name'])
 322          ) {
 323              return($db->raiseError(MDB_ERROR_MANAGER, NULL, NULL,
 324                  'Get table field definition: no result, please check table '.
 325                  $table.' and field '.$field_name.' are correct'));
 326          }
 327          $field_column = $columns['column_name'];
 328          $type_column = $columns['type_name'];
 329          $db_type = strtolower($type_column);
 330          if (strpos($type_column, ' ') !== FALSE) {
 331              $db_type = strtok($db_type, ' ');
 332          }
 333          $length = $columns['precision'];
 334          $decimal = $columns['scale'];
 335          $type = array();
 336          switch ($db_type) {
 337              case 'bigint':
 338              case 'int':
 339              case 'smallint':
 340              case 'tinyint':
 341                  $type[0] = 'integer';
 342                  if ($length == '1') {
 343                      $type[1] = 'boolean';
 344                  }
 345                  break;
 346              case 'bit':
 347                  $type[0] = 'integer';
 348                  $type[1] = 'boolean';
 349                  break;
 350              case 'decimal':
 351              case 'numeric':
 352                  $type[0] = 'decimal';
 353                  break;
 354              case 'money':
 355              case 'smallmoney':
 356                  $type[0] = 'decimal';
 357                  $type[1] = 'float';
 358                  break;
 359              case 'float':
 360              case 'real':
 361                  $type[0] = 'float';
 362                  break;
 363              case 'datetime':
 364              case 'smalldatetime':
 365                  $type[0] = 'timestamp';
 366                  break;
 367              case 'char':
 368              case 'varchar':
 369              case 'nchar':
 370              case 'nvarchar':
 371                  $type[0] = 'text';
 372                  if ($length == '1') {
 373                      $type[1] = 'boolean';
 374                  }
 375                  break;
 376              case 'text':
 377              case 'ntext':
 378                  $type[0] = 'clob';
 379                  $type[1] = 'text';
 380                  break;
 381              case 'binary':
 382              case 'varbinary':
 383              case 'image':
 384                  $type[0] = 'blob';
 385                  break;
 386              case 'timestamp':
 387                  $type[0] = 'blob';
 388                  break;
 389              default:
 390                  return($db->raiseError(MDB_ERROR_MANAGER, NULL, NULL,
 391                      'List table fields: unknown database attribute type'));
 392          }
 393          unset($notnull);
 394          if ($columns['nullable'] == 0) {
 395              $notnull = 1;
 396          }
 397          unset($default);
 398          if (isset($columns['column_def']) && ($columns['column_def'] != NULL)) {
 399              if (($type[0] = 'integer') OR ($type[0] = 'boolean')) {
 400                  $columns['column_def'] = str_replace( '(', '', $columns['column_def'] );
 401                  $columns['column_def'] = str_replace( ')', '', $columns['column_def'] );
 402              }
 403              $default = $columns['column_def'];
 404          }
 405          $definition = array();
 406          for ($field_choices = array(), $datatype = 0; $datatype < count($type); $datatype++) {
 407              $field_choices[$datatype] = array('type' => $type[$datatype]);
 408              if (isset($notnull)) {
 409                  $field_choices[$datatype]['notnull'] = 1;
 410              }
 411              if (isset($default)) {
 412                  $field_choices[$datatype]['default'] = $default;
 413              }
 414              if ($type[$datatype] != 'boolean'
 415                  && $type[$datatype] != 'time'
 416                  && $type[$datatype] != 'date'
 417                  && $type[$datatype] != 'timestamp'
 418              ) {
 419                  if (strlen($length)) {
 420                      $field_choices[$datatype]['length'] = $length;
 421                  }
 422              }
 423          }
 424          $definition[0] = $field_choices;
 425          if (strpos($type_column, 'identity') !== FALSE) {
 426              $implicit_sequence = array();
 427              $implicit_sequence['on'] = array();
 428              $implicit_sequence['on']['table'] = $table;
 429              $implicit_sequence['on']['field'] = $field_name;
 430              $definition[1]['name'] = $table.'_'.$field_name;
 431              $definition[1]['definition'] = $implicit_sequence;
 432          }
 433          if (MDB::isError($indexes = $db->queryAll("EXEC sp_pkeys @table_name='$table'"
 434                  , NULL, MDB_FETCHMODE_ASSOC)))
 435          {
 436              return $indexes;
 437          }
 438          if ($indexes != NULL) {
 439              $is_primary = FALSE;
 440              foreach ($indexes as $index) {
 441                  if ($index['column_name'] == $field_name) {
 442                      $is_primary = TRUE;
 443                      break;
 444                  }
 445              }
 446              if ($is_primary) {
 447                  $implicit_index = array();
 448                  $implicit_index['unique'] = 1;
 449                  $implicit_index['FIELDS'][$field_name] = '';
 450                  $definition[2]['name'] = $field_name;
 451                  $definition[2]['definition'] = $implicit_index;
 452              }
 453          }
 454          return($definition);
 455      }
 456  
 457      // }}}
 458      // {{{ createSequence()
 459  
 460      /**
 461       * create sequence
 462       *
 463       * @param object    $dbs        database object that is extended by this class
 464       * @param string    $seq_name     name of the sequence to be created
 465       * @param string    $start         start value of the sequence; default is 1
 466       * @return mixed MDB_OK on success, a MDB error on failure
 467       * @access public
 468       */
 469      function createSequence(&$db, $seq_name, $start)
 470      {
 471          $sequence_name = $db->getSequenceName($seq_name);
 472          $query = "CREATE TABLE $sequence_name (".$db->options['sequence_col_name']." INT NOT NULL IDENTITY($start,1) PRIMARY KEY CLUSTERED)";
 473          return($db->query($query));
 474      }
 475  
 476      // }}}
 477      // {{{ dropSequence()
 478  
 479      /**
 480       * drop existing sequence
 481       *
 482       * @param object    $dbs        database object that is extended by this class
 483       * @param string    $seq_name     name of the sequence to be dropped
 484       * @return mixed MDB_OK on success, a MDB error on failure
 485       * @access public
 486       */
 487      function dropSequence(&$db, $seq_name)
 488      {
 489          $sequence_name = $db->getSequenceName($seq_name);
 490          return($db->Query("DROP TABLE $sequence_name"));
 491      }
 492  }
 493  
 494  };
 495  ?>