Overview

Packages

  • Db
    • Adapter
    • Migration

Classes

  • Horde_Db_Adapter_Base
  • Horde_Db_Adapter_Base_Column
  • Horde_Db_Adapter_Base_ColumnDefinition
  • Horde_Db_Adapter_Base_Index
  • Horde_Db_Adapter_Base_Schema
  • Horde_Db_Adapter_Base_Table
  • Horde_Db_Adapter_Base_TableDefinition
  • Horde_Db_Adapter_Mysql
  • Horde_Db_Adapter_Mysql_Column
  • Horde_Db_Adapter_Mysql_Result
  • Horde_Db_Adapter_Mysql_Schema
  • Horde_Db_Adapter_Mysqli
  • Horde_Db_Adapter_Mysqli_Result
  • Horde_Db_Adapter_Pdo_Base
  • Horde_Db_Adapter_Pdo_Mysql
  • Horde_Db_Adapter_Pdo_Pgsql
  • Horde_Db_Adapter_Pdo_Sqlite
  • Horde_Db_Adapter_Postgresql_Column
  • Horde_Db_Adapter_Postgresql_Schema
  • Horde_Db_Adapter_SplitRead
  • Horde_Db_Adapter_Sqlite_Column
  • Horde_Db_Adapter_Sqlite_Schema

Interfaces

  • Horde_Db_Adapter
  • Overview
  • Package
  • Class
  • Tree
   1: <?php
   2: /**
   3:  * Base class for managing database schemes and handling database-specific SQL
   4:  * dialects and quoting.
   5:  *
   6:  * Copyright 2007 Maintainable Software, LLC
   7:  * Copyright 2008-2012 Horde LLC (http://www.horde.org/)
   8:  *
   9:  * @author     Mike Naberezny <mike@maintainable.com>
  10:  * @author     Derek DeVries <derek@maintainable.com>
  11:  * @author     Chuck Hagenbuch <chuck@horde.org>
  12:  * @author     Jan Schneider <jan@horde.org>
  13:  * @license    http://www.horde.org/licenses/bsd
  14:  * @category   Horde
  15:  * @package    Db
  16:  * @subpackage Adapter
  17:  */
  18: 
  19: /**
  20:  * @author     Mike Naberezny <mike@maintainable.com>
  21:  * @author     Derek DeVries <derek@maintainable.com>
  22:  * @author     Chuck Hagenbuch <chuck@horde.org>
  23:  * @author     Jan Schneider <jan@horde.org>
  24:  * @license    http://www.horde.org/licenses/bsd
  25:  * @category   Horde
  26:  * @package    Db
  27:  * @subpackage Adapter
  28:  */
  29: abstract class Horde_Db_Adapter_Base_Schema
  30: {
  31:     /**
  32:      * A Horde_Db_Adapter instance.
  33:      *
  34:      * @var Horde_Db_Adapter_Base
  35:      */
  36:     protected $_adapter = null;
  37: 
  38:     /**
  39:      * List of public methods supported by the attached adapter.
  40:      *
  41:      * Method names are in the keys.
  42:      *
  43:      * @var array
  44:      */
  45:     protected $_adapterMethods = array();
  46: 
  47: 
  48:     /*##########################################################################
  49:     # Construct/Destruct
  50:     ##########################################################################*/
  51: 
  52:     /**
  53:      * Constructor.
  54:      *
  55:      * @param Horde_Db_Adapter_Base $adapter  A Horde_Db_Adapter instance.
  56:      */
  57:     public function __construct(Horde_Db_Adapter $adapter)
  58:     {
  59:         $this->setAdapter($adapter);
  60:     }
  61: 
  62:     /**
  63:      * Setter for a Horde_Db_Adapter instance.
  64:      *
  65:      * @param Horde_Db_Adapter $adapter  A Horde_Db_Adapter instance.
  66:      */
  67:     public function setAdapter(Horde_Db_Adapter $adapter)
  68:     {
  69:         $this->_adapter = $adapter;
  70:         $this->_adapterMethods = array_flip(get_class_methods($adapter));
  71:     }
  72: 
  73: 
  74:     /*##########################################################################
  75:     # Object factories
  76:     ##########################################################################*/
  77: 
  78:     /**
  79:      * Factory for Column objects.
  80:      *
  81:      * @param string $name     The column's name, such as "supplier_id" in
  82:      *                         "supplier_id int(11)".
  83:      * @param string $default  The type-casted default value, such as "new" in
  84:      *                         "sales_stage varchar(20) default 'new'".
  85:      * @param string $sqlType  Used to extract the column's type, length and
  86:      *                         signed status, if necessary. For example
  87:      *                         "varchar" and "60" in "company_name varchar(60)"
  88:      *                         or "unsigned => true" in "int(10) UNSIGNED".
  89:      * @param boolean $null    Whether this column allows NULL values.
  90:      *
  91:      * @return Horde_Db_Adapter_Base_Column  A column object.
  92:      */
  93:     public function makeColumn($name, $default, $sqlType = null, $null = true)
  94:     {
  95:         return new Horde_Db_Adapter_Base_Column($name, $default, $sqlType, $null);
  96:     }
  97: 
  98:     /**
  99:      * Factory for ColumnDefinition objects.
 100:      *
 101:      * @return Horde_Db_Adapter_Base_ColumnDefinition  A column definition
 102:      *                                                 object.
 103:      */
 104:     public function makeColumnDefinition(
 105:         $base, $name, $type, $limit = null, $precision = null, $scale = null,
 106:         $unsigned = null, $default = null, $null = null, $autoincrement = null)
 107:     {
 108:         return new Horde_Db_Adapter_Base_ColumnDefinition(
 109:             $base, $name, $type, $limit, $precision, $scale, $unsigned,
 110:             $default, $null, $autoincrement);
 111:     }
 112: 
 113:     /**
 114:      * Factory for Index objects.
 115:      *
 116:      * @param string  $table    The table the index is on.
 117:      * @param string  $name     The index's name.
 118:      * @param boolean $primary  Is this a primary key?
 119:      * @param boolean $unique   Is this a unique index?
 120:      * @param array   $columns  The columns this index covers.
 121:      *
 122:      * @return Horde_Db_Adapter_Base_Index  An index object.
 123:      */
 124:     public function makeIndex($table, $name, $primary, $unique, $columns)
 125:     {
 126:         return new Horde_Db_Adapter_Base_Index($table, $name, $primary, $unique, $columns);
 127:     }
 128: 
 129:     /**
 130:      * Factory for Table objects.
 131:      *
 132:      * @return Horde_Db_Adapter_Base_Table  A table object.
 133:      */
 134:     public function makeTable($name, $primaryKey, $columns, $indexes)
 135:     {
 136:         return new Horde_Db_Adapter_Base_Table($name, $primaryKey, $columns, $indexes);
 137:     }
 138: 
 139:     /**
 140:      * Factory for TableDefinition objects.
 141:      *
 142:      * @return Horde_Db_Adapter_Base_TableDefinition  A table definition object.
 143:      */
 144:     public function makeTableDefinition($name, $base, $options = array())
 145:     {
 146:         return new Horde_Db_Adapter_Base_TableDefinition($name, $base, $options);
 147:     }
 148: 
 149: 
 150:     /*##########################################################################
 151:     # Object composition
 152:     ##########################################################################*/
 153: 
 154:     /**
 155:      * Delegates calls to the adapter object.
 156:      *
 157:      * @param string $method  A method name.
 158:      * @param array  $args    Method parameters.
 159:      *
 160:      * @return mixed  The method call result.
 161:      * @throws BadMethodCallException if method doesn't exist in the adapter.
 162:      */
 163:     public function __call($method, $args)
 164:     {
 165:         if (isset($this->_adapterMethods[$method])) {
 166:             return call_user_func_array(array($this->_adapter, $method), $args);
 167:         }
 168: 
 169:         throw new BadMethodCallException('Call to undeclared method "' . $method . '"');
 170:     }
 171: 
 172:     /**
 173:      * Delegates access to $_cache and $_logger to the adapter object.
 174:      *
 175:      * @param string $key  Property name. Only '_cache' and '_logger' are
 176:      *                     supported.
 177:      *
 178:      * @return object  The request property object.
 179:      */
 180:     public function __get($key)
 181:     {
 182:         if ($key == '_cache' || $key == '_logger') {
 183:             $getter = 'get' . ucfirst(substr($key, 1));
 184:             return $this->_adapter->$getter();
 185:         }
 186:     }
 187: 
 188: 
 189:     /*##########################################################################
 190:     # Quoting
 191:     ##########################################################################*/
 192: 
 193:     /**
 194:      * Quotes the column value to help prevent SQL injection attacks.
 195:      *
 196:      * This method makes educated guesses on the scalar type based on the
 197:      * passed value. Make sure to correctly cast the value and/or pass the
 198:      * $column parameter to get the best results.
 199:      *
 200:      * @param mixed $value    The scalar value to quote, a Horde_Db_Value,
 201:      *                        Horde_Date, or DateTime instance, or an object
 202:      *                        implementing quotedId().
 203:      * @param object $column  An object implementing getType().
 204:      *
 205:      * @return string  The correctly quoted value.
 206:      */
 207:     public function quote($value, $column = null)
 208:     {
 209:         if (is_object($value) && is_callable(array($value, 'quotedId'))) {
 210:             return $value->quotedId();
 211:         }
 212: 
 213:         if ($value instanceof Horde_Db_Value) {
 214:             return $value->quote($this->_adapter);
 215:         }
 216: 
 217:         $type = isset($column) ? $column->getType() : null;
 218: 
 219:         if (is_null($value)) {
 220:             return 'NULL';
 221:         } elseif ($value === true) {
 222:             return $type == 'integer' ? '1' : $this->quoteTrue();
 223:         } elseif ($value === false) {
 224:             return $type == 'integer' ? '0' : $this->quoteFalse();
 225:         } elseif (is_float($value)) {
 226:             return sprintf('%F', $value);
 227:         } elseif (is_int($value)) {
 228:             return $value;
 229:         } elseif ($value instanceof DateTime || $value instanceof Horde_Date) {
 230:             return $this->_adapter->quoteString($type == 'integer'
 231:                                                 ? $value->format('U')
 232:                                                 : $value->format('Y-m-d H:i:s'));
 233:         } elseif ($type == 'integer') {
 234:             return (int)$value;
 235:         } elseif ($type == 'float') {
 236:             return sprintf('%F', $value);
 237:         } else {
 238:             return $this->_adapter->quoteString($value);
 239:         }
 240:     }
 241: 
 242:     /**
 243:      * Quotes a string, escaping any ' (single quote) and \ (backslash)
 244:      * characters.
 245:      *
 246:      * @param string $string  A string to escape.
 247:      *
 248:      * @return string  The escaped and quoted string.
 249:      */
 250:     public function quoteString($string)
 251:     {
 252:         return "'" . str_replace(array('\\', '\''), array('\\\\', '\\\''), $string) . "'";
 253:     }
 254: 
 255:     /**
 256:      * Returns a quoted form of the column name.
 257:      *
 258:      * @param string $name  A column name.
 259:      *
 260:      * @return string  The quoted column name.
 261:      */
 262:     abstract public function quoteColumnName($name);
 263: 
 264:     /**
 265:      * Returns a quoted form of the table name.
 266:      *
 267:      * Defaults to column name quoting.
 268:      *
 269:      * @param string $name  A table name.
 270:      *
 271:      * @return string  The quoted table name.
 272:      */
 273:     public function quoteTableName($name)
 274:     {
 275:         return $this->quoteColumnName($name);
 276:     }
 277: 
 278:     /**
 279:      * Returns a quoted boolean true.
 280:      *
 281:      * @return string  The quoted boolean true.
 282:      */
 283:     public function quoteTrue()
 284:     {
 285:         return "'t'";
 286:     }
 287: 
 288:     /**
 289:      * Returns a quoted boolean false.
 290:      *
 291:      * @return string  The quoted boolean false.
 292:      */
 293:     public function quoteFalse()
 294:     {
 295:         return "'f'";
 296:     }
 297: 
 298:     /**
 299:      * Returns a quoted date value.
 300:      *
 301:      * @param mixed  A date value that can be casted to string.
 302:      *
 303:      * @return string  The quoted date value.
 304:      */
 305:     public function quoteDate($value)
 306:     {
 307:         return $this->quoteString((string)$value);
 308:     }
 309: 
 310:     /**
 311:      * Returns a quoted binary value.
 312:      *
 313:      * @param mixed  A binary value.
 314:      *
 315:      * @return string  The quoted binary value.
 316:      */
 317:     public function quoteBinary($value)
 318:     {
 319:         return $this->quoteString($value);
 320:     }
 321: 
 322:     /*##########################################################################
 323:     # Schema Statements
 324:     ##########################################################################*/
 325: 
 326:     /**
 327:      * Returns a hash of mappings from the abstract data types to the native
 328:      * database types.
 329:      *
 330:      * See TableDefinition::column() for details on the recognized abstract
 331:      * data types.
 332:      *
 333:      * @see TableDefinition::column()
 334:      *
 335:      * @return array  A database type map.
 336:      */
 337:     public function nativeDatabaseTypes()
 338:     {
 339:         return array();
 340:     }
 341: 
 342:     /**
 343:      * Returns the maximum length a table alias can have.
 344:      *
 345:      * @return integer  The maximum table alias length.
 346:      */
 347:     public function tableAliasLength()
 348:     {
 349:         return 255;
 350:     }
 351: 
 352:     /**
 353:      * Converts a table name into a suitable table alias.
 354:      *
 355:      * @param string $tableName  A table name.
 356:      *
 357:      * @return string  A possible alias name for the table.
 358:      */
 359:     public function tableAliasFor($tableName)
 360:     {
 361:         $alias = substr($tableName, 0, $this->tableAliasLength());
 362:         return str_replace('.', '_', $alias);
 363:     }
 364: 
 365:     /**
 366:      * Returns a list of all tables of the current database.
 367:      *
 368:      * @return array  A table list.
 369:      */
 370:     abstract public function tables();
 371: 
 372:     /**
 373:      * Returns a Horde_Db_Adapter_Base_Table object for a table.
 374:      *
 375:      * @param string $tableName  A table name.
 376:      * @param string $name       (can be removed?)
 377:      *
 378:      * @return Horde_Db_Adapter_Base_Table  A table object.
 379:      */
 380:     public function table($tableName, $name = null)
 381:     {
 382:         return $this->makeTable(
 383:             $tableName,
 384:             $this->primaryKey($tableName),
 385:             $this->columns($tableName, $name),
 386:             $this->indexes($tableName, $name)
 387:         );
 388:     }
 389: 
 390:     /**
 391:      * Returns a table's primary key.
 392:      *
 393:      * @param string $tableName  A table name.
 394:      * @param string $name       (can be removed?)
 395:      *
 396:      * @return Horde_Db_Adapter_Base_Index  The primary key index object.
 397:      */
 398:     abstract public function primaryKey($tableName, $name = null);
 399: 
 400:     /**
 401:      * Returns a list of tables indexes.
 402:      *
 403:      * @param string $tableName  A table name.
 404:      * @param string $name       (can be removed?)
 405:      *
 406:      * @return array  A list of Horde_Db_Adapter_Base_Index objects.
 407:      */
 408:     abstract public function indexes($tableName, $name = null);
 409: 
 410:     /**
 411:      * Returns a list of table columns.
 412:      *
 413:      * @param string $tableName  A table name.
 414:      * @param string $name       (can be removed?)
 415:      *
 416:      * @return array  A list of Horde_Db_Adapter_Base_Column objects.
 417:      */
 418:     abstract public function columns($tableName, $name = null);
 419: 
 420:     /**
 421:      * Creates a new table.
 422:      *
 423:      * The $options hash can include the following keys:
 424:      * - autoincrementKey (string|array):
 425:      *   The name of the autoincrementing primary key, if one is to be added
 426:      *   automatically. Defaults to "id".
 427:      * - options (array):
 428:      *   Any extra options you want appended to the table definition.
 429:      * - temporary (boolean):
 430:      *   Make a temporary table.
 431:      * - force (boolean):
 432:      *   Set to true or false to drop the table before creating it.
 433:      *   Defaults to false.
 434:      *
 435:      * Examples:
 436:      * <code>
 437:      * // Add a backend specific option to the generated SQL (MySQL)
 438:      * $schema->createTable('suppliers', array('options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8')));
 439:      * </code>
 440:      * generates:
 441:      * <pre>
 442:      *  CREATE TABLE suppliers (
 443:      *    id int(10) UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY
 444:      *  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 445:      * </pre>
 446:      *
 447:      * <code>
 448:      * // Rename the primary key column
 449:      * $table = $schema->createTable('objects', array('autoincrementKey' => 'guid'));
 450:      * $table->column('name', 'string', array('limit' => 80));
 451:      * $table->end();
 452:      * </code>
 453:      * generates:
 454:      * <pre>
 455:      *  CREATE TABLE objects (
 456:      *    guid int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 457:      *    name varchar(80)
 458:      *  )
 459:      * </pre>
 460:      *
 461:      * <code>
 462:      * // Do not add a primary key column, use fluent interface, use type
 463:      * // method.
 464:      * $schema->createTable('categories_suppliers', array('autoincrementKey' => false))
 465:      *     ->column('category_id', 'integer')
 466:      *     ->integer('supplier_id')
 467:      *     ->end();
 468:      * </code>
 469:      * generates:
 470:      * <pre>
 471:      *  CREATE TABLE categories_suppliers (
 472:      *    category_id int(11),
 473:      *    supplier_id int(11)
 474:      *  )
 475:      * </pre>
 476:      *
 477:      * See also Horde_Db_Adapter_Base_TableDefinition::column() for details on
 478:      * how to create columns.
 479:      *
 480:      * @param string $name    A table name.
 481:      * @param array $options  A list of table options, see the method
 482:      *                        description.
 483:      *
 484:      * @return Horde_Db_Adapter_Base_TableDefinition  The definition of the
 485:      *                                                created table.
 486:      */
 487:     public function createTable($name, $options = array())
 488:     {
 489:         $tableDefinition = $this->makeTableDefinition($name, $this, $options);
 490: 
 491:         if (isset($options['autoincrementKey'])) {
 492:             if ($options['autoincrementKey'] === true ||
 493:                 $options['autoincrementKey'] === 'true' ||
 494:                 $options['autoincrementKey'] === 't' ||
 495:                 $options['autoincrementKey'] === 1 ||
 496:                 $options['autoincrementKey'] === '1') {
 497:                 $pk = 'id';
 498:             } elseif ($options['autoincrementKey'] === false ||
 499:                       $options['autoincrementKey'] === 'false' ||
 500:                       $options['autoincrementKey'] === 'f' ||
 501:                       $options['autoincrementKey'] === 0 ||
 502:                       $options['autoincrementKey'] === '0') {
 503:                 $pk = false;
 504:             } else {
 505:                 $pk = $options['autoincrementKey'];
 506:             }
 507:         } else {
 508:             $pk = 'id';
 509:         }
 510: 
 511:         if ($pk != false) {
 512:             $tableDefinition->primaryKey($pk);
 513:         }
 514: 
 515:         return $tableDefinition;
 516:     }
 517: 
 518:     /**
 519:      * Finishes and executes table creation.
 520:      *
 521:      * @param string|Horde_Db_Adapter_Base_TableDefinition $name
 522:      *        A table name or object.
 523:      * @param array $options
 524:      *        A list of options. See createTable().
 525:      */
 526:     public function endTable($name, $options = array())
 527:     {
 528:         if ($name instanceof Horde_Db_Adapter_Base_TableDefinition) {
 529:             $tableDefinition = $name;
 530:             $options = array_merge($tableDefinition->getOptions(), $options);
 531:         } else {
 532:             $tableDefinition = $this->createTable($name, $options);
 533:         }
 534: 
 535:         // Drop previous table.
 536:         if (isset($options['force'])) {
 537:             $this->dropTable($tableDefinition->getName(), $options);
 538:         }
 539: 
 540:         $temp = !empty($options['temporary']) ? 'TEMPORARY'         : null;
 541:         $opts = !empty($options['options'])   ? $options['options'] : null;
 542:         $sql  = sprintf("CREATE %s TABLE %s (\n%s\n) %s",
 543:                         $temp,
 544:                         $this->quoteTableName($tableDefinition->getName()),
 545:                         $tableDefinition->toSql(),
 546:                         $opts);
 547: 
 548:         return $this->execute($sql);
 549:     }
 550: 
 551:     /**
 552:      * Renames a table.
 553:      *
 554:      * @param string $name     A table name.
 555:      * @param string $newName  The new table name.
 556:      */
 557:     abstract public function renameTable($name, $newName);
 558: 
 559:     /**
 560:      * Drops a table from the database.
 561:      *
 562:      * @param string $name  A table name.
 563:      */
 564:     public function dropTable($name)
 565:     {
 566:         $this->_clearTableCache($name);
 567:         return $this->execute('DROP TABLE ' . $this->quoteTableName($name));
 568:     }
 569: 
 570:     /**
 571:      * Adds a new column to a table.
 572:      *
 573:      * @param string $tableName   A table name.
 574:      * @param string $columnName  A column name.
 575:      * @param string $type        A data type.
 576:      * @param array $options      Column options. See
 577:      *                            Horde_Db_Adapter_Base_TableDefinition#column()
 578:      *                            for details.
 579:      */
 580:     public function addColumn($tableName, $columnName, $type,
 581:                               $options = array())
 582:     {
 583:         $this->_clearTableCache($tableName);
 584: 
 585:         $options = array_merge(
 586:             array('limit'     => null,
 587:                   'precision' => null,
 588:                   'scale'     => null,
 589:                   'unsigned'  => null),
 590:             $options);
 591: 
 592:         $sql = sprintf('ALTER TABLE %s ADD %s %s',
 593:                        $this->quoteTableName($tableName),
 594:                        $this->quoteColumnName($columnName),
 595:                        $this->typeToSql($type,
 596:                                         $options['limit'],
 597:                                         $options['precision'],
 598:                                         $options['scale'],
 599:                                         $options['unsigned']));
 600:         $sql = $this->addColumnOptions($sql, $options);
 601: 
 602:         return $this->execute($sql);
 603:     }
 604: 
 605:     /**
 606:      * Removes a column from a table.
 607:      *
 608:      * @param string $tableName   A table name.
 609:      * @param string $columnName  A column name.
 610:      */
 611:     public function removeColumn($tableName, $columnName)
 612:     {
 613:         $this->_clearTableCache($tableName);
 614:         $sql = sprintf('ALTER TABLE %s DROP %s',
 615:                        $this->quoteTableName($tableName),
 616:                        $this->quoteColumnName($columnName));
 617:         return $this->execute($sql);
 618:     }
 619: 
 620:     /**
 621:      * Changes an existing column's definition.
 622:      *
 623:      * @param string $tableName   A table name.
 624:      * @param string $columnName  A column name.
 625:      * @param string $type        A data type.
 626:      * @param array $options      Column options. See
 627:      *                            Horde_Db_Adapter_Base_TableDefinition#column()
 628:      *                            for details.
 629:      */
 630:     abstract public function changeColumn($tableName, $columnName, $type, $options = array());
 631: 
 632:     /**
 633:      * Sets a new default value for a column.
 634:      *
 635:      * If you want to set the default value to NULL, you are out of luck. You
 636:      * need to execute the apppropriate SQL statement yourself.
 637:      *
 638:      * @param string $tableName   A table name.
 639:      * @param string $columnName  A column name.
 640:      * @param mixed $default      The new default value.
 641:      */
 642:     abstract public function changeColumnDefault($tableName, $columnName, $default);
 643: 
 644:     /**
 645:      * Renames a column.
 646:      *
 647:      * @param string $tableName      A table name.
 648:      * @param string $columnName     A column name.
 649:      * @param string $newColumnName  The new column name.
 650:      */
 651:     abstract public function renameColumn($tableName, $columnName, $newColumnName);
 652: 
 653:     /**
 654:      * Adds a primary key to a table.
 655:      *
 656:      * @since Horde_Db 1.1.0
 657:      *
 658:      * @param string $tableName         A table name.
 659:      * @param string|array $columnName  One or more column names.
 660:      *
 661:      * @throws Horde_Db_Exception
 662:      */
 663:     public function addPrimaryKey($tableName, $columns)
 664:     {
 665:         $this->_clearTableCache($tableName);
 666:         $columns = (array)$columns;
 667:         $sql = sprintf('ALTER TABLE %s ADD PRIMARY KEY (%s)',
 668:                        $this->quoteTableName($tableName),
 669:                        implode(', ', $columns));
 670:         return $this->execute($sql);
 671:     }
 672: 
 673:     /**
 674:      * Removes a primary key from a table.
 675:      *
 676:      * @since Horde_Db 1.1.0
 677:      *
 678:      * @param string $tableName  A table name.
 679:      *
 680:      * @throws Horde_Db_Exception
 681:      */
 682:     abstract public function removePrimaryKey($tableName);
 683: 
 684:     /**
 685:      * Adds a new index to a table.
 686:      *
 687:      * The index will be named after the table and the first column names,
 688:      * unless you pass 'name' as an option.
 689:      *
 690:      * When creating an index on multiple columns, the first column is used as
 691:      * a name for the index. For example, when you specify an index on two
 692:      * columns 'first' and 'last', the DBMS creates an index for both columns
 693:      * as well as an index for the first colum 'first'. Using just the first
 694:      * name for this index makes sense, because you will never have to create a
 695:      * singular index with this name.
 696:      *
 697:      * Examples:
 698:      *
 699:      * Creating a simple index
 700:      * <code>
 701:      * $schema->addIndex('suppliers', 'name');
 702:      * </code>
 703:      * generates
 704:      * <code>
 705:      * CREATE INDEX suppliers_name_index ON suppliers(name)
 706:      * </code>
 707:      *
 708:      * Creating a unique index
 709:      * <code>
 710:      * $schema->addIndex('accounts',
 711:      *                   array('branch_id', 'party_id'),
 712:      *                   array('unique' => true));
 713:      * </code>
 714:      * generates
 715:      * <code>
 716:      * CREATE UNIQUE INDEX accounts_branch_id_index ON accounts(branch_id, party_id)
 717:      * </code>
 718:      *
 719:      * Creating a named index
 720:      * <code>
 721:      * $schema->addIndex('accounts',
 722:      *                   array('branch_id', 'party_id'),
 723:      *                   array('unique' => true, 'name' => 'by_branch_party'));
 724:      * </code>
 725:      * generates
 726:      * <code>
 727:      * CREATE UNIQUE INDEX by_branch_party ON accounts(branch_id, party_id)
 728:      * </code>
 729:      *
 730:      * @param string $tableName         A table name.
 731:      * @param string|array $columnName  One or more column names.
 732:      * @param array $options            Index options:
 733:      *                                  - name: (string) the index name.
 734:      *                                  - unique: (boolean) create a unique
 735:      *                                            index?
 736:      */
 737:     public function addIndex($tableName, $columnName, $options = array())
 738:     {
 739:         $this->_clearTableCache($tableName);
 740: 
 741:         $columnNames = (array)$columnName;
 742:         $indexName = empty($options['name'])
 743:             ? $this->indexName($tableName, array('column' => $columnNames))
 744:             : $options['name'];
 745:         foreach ($columnNames as &$colName) {
 746:             $colName = $this->quoteColumnName($colName);
 747:         }
 748: 
 749:         $sql = sprintf('CREATE %s INDEX %s ON %s (%s)',
 750:                        empty($options['unique']) ? null : 'UNIQUE',
 751:                        $this->quoteColumnName($indexName),
 752:                        $this->quoteTableName($tableName),
 753:                        implode(', ', $columnNames));
 754: 
 755:         return $this->execute($sql);
 756:     }
 757: 
 758:     /**
 759:      * Removes an index from a table.
 760:      *
 761:      * Examples:
 762:      *
 763:      * Remove the suppliers_name_index in the suppliers table:
 764:      * <code>
 765:      * $schema->removeIndex('suppliers', 'name');
 766:      * </code>
 767:      *
 768:      * Remove the index named accounts_branch_id in the accounts table:
 769:      * <code>
 770:      * $schema->removeIndex('accounts', array('column' => 'branch_id'));
 771:      * </code>
 772:      *
 773:      * Remove the index named by_branch_party in the accounts table:
 774:      * <code>
 775:      * $schema->removeIndex('accounts', array('name' => 'by_branch_party'));
 776:      * </code>
 777:      *
 778:      * You can remove an index on multiple columns by specifying the first
 779:      * column:
 780:      * <code>
 781:      * $schema->addIndex('accounts', array('username', 'password'))
 782:      * $schema->removeIndex('accounts', 'username');
 783:      * </code>
 784:      *
 785:      * @param string $tableName      A table name.
 786:      * @param string|array $options  Either a column name or index options:
 787:      *                               - name: (string) the index name.
 788:      *                               - column: (string|array) column name(s).
 789:      */
 790:     public function removeIndex($tableName, $options = array())
 791:     {
 792:         $this->_clearTableCache($tableName);
 793: 
 794:         $index = $this->indexName($tableName, $options);
 795:         $sql = sprintf('DROP INDEX %s ON %s',
 796:                        $this->quoteColumnName($index),
 797:                        $this->quoteTableName($tableName));
 798: 
 799:         return $this->execute($sql);
 800:     }
 801: 
 802:     /**
 803:      * Builds the name for an index.
 804:      *
 805:      * @param string $tableName      A table name.
 806:      * @param string|array $options  Either a column name or index options:
 807:      *                               - column: (string|array) column name(s).
 808:      *                               - name: (string) the index name to fall
 809:      *                                 back to if no column names specified.
 810:      */
 811:     public function indexName($tableName, $options = array())
 812:     {
 813:         if (!is_array($options)) {
 814:             $options = array('column' => $options);
 815:         }
 816:         if (isset($options['column'])) {
 817:             $columns = (array)$options['column'];
 818:             return "index_{$tableName}_on_" . implode('_and_', $columns);
 819:         }
 820:         if (isset($options['name'])) {
 821:             return $options['name'];
 822:         }
 823:         throw new Horde_Db_Exception('You must specify the index name');
 824:     }
 825: 
 826:     /**
 827:      * Recreates, i.e. drops then creates a database.
 828:      *
 829:      * @param string $name  A database name.
 830:      */
 831:     public function recreateDatabase($name)
 832:     {
 833:         $this->dropDatabase($name);
 834:         return $this->createDatabase($name);
 835:     }
 836: 
 837:     /**
 838:      * Creates a database.
 839:      *
 840:      * @param string $name    A database name.
 841:      * @param array $options  Database options.
 842:      */
 843:     abstract public function createDatabase($name, $options = array());
 844: 
 845:     /**
 846:      * Drops a database.
 847:      *
 848:      * @param string $name  A database name.
 849:      */
 850:     abstract public function dropDatabase($name);
 851: 
 852:     /**
 853:      * Returns the name of the currently selected database.
 854:      *
 855:      * @return string  The database name.
 856:      */
 857:     abstract public function currentDatabase();
 858: 
 859:     /**
 860:      * Generates the SQL definition for a column type.
 861:      *
 862:      * @param string $type        A column type.
 863:      * @param integer $limit      Maximum column length (non decimal type only)
 864:      * @param integer $precision  The number precision (decimal type only).
 865:      * @param integer $scale      The number scaling (decimal columns only).
 866:      * @param boolean $unsigned   Whether the column is an unsigned number
 867:      *                            (non decimal columns only).
 868:      *
 869:      * @return string  The SQL definition. If $type is not one of the
 870:      *                 internally supported types, $type is returned unchanged.
 871:      */
 872:     public function typeToSql($type, $limit = null, $precision = null,
 873:                               $scale = null, $unsigned = null)
 874:     {
 875:         $natives = $this->nativeDatabaseTypes();
 876:         $native = isset($natives[$type]) ? $natives[$type] : null;
 877:         if (empty($native)) {
 878:             return $type;
 879:         }
 880: 
 881:         $sql = is_array($native) ? $native['name'] : $native;
 882:         if ($type == 'decimal') {
 883:             $nativePrec  = isset($native['precision']) ? $native['precision'] : null;
 884:             $nativeScale = isset($native['scale'])     ? $native['scale']     : null;
 885: 
 886:             $precision = !empty($precision) ? $precision : $nativePrec;
 887:             $scale     = !empty($scale)     ? $scale     : $nativeScale;
 888:             if ($precision) {
 889:                 $sql .= $scale ? "($precision, $scale)" : "($precision)";
 890:             }
 891:         } else {
 892:             $nativeLimit = is_array($native) ? $native['limit'] : null;
 893: 
 894:             // If there is no explicit limit, adjust $nativeLimit for unsigned
 895:             // integers.
 896:             if (!empty($unsigned) && empty($limit) && is_integer($nativeLimit)) {
 897:                 $nativeLimit--;
 898:             }
 899: 
 900:             if ($limit = !empty($limit) ? $limit : $nativeLimit) {
 901:                 $sql .= "($limit)";
 902:             }
 903:         }
 904: 
 905:         return $sql;
 906:     }
 907: 
 908:     /**
 909:      * Adds default/null options to column SQL definitions.
 910:      *
 911:      * @param string $sql     Existing SQL definition for a column.
 912:      * @param array $options  Column options:
 913:      *                        - null: (boolean) Whether to allow NULL values.
 914:      *                        - default: (mixed) Default column value.
 915:      *                        - autoincrement: (boolean) Whether the column is
 916:      *                          an autoincrement column. Driver depedendent.
 917:      *
 918:      * @return string  The manipulated SQL definition.
 919:      */
 920:     public function addColumnOptions($sql, $options)
 921:     {
 922:         /* 'autoincrement' is not handled here - it varies too much between
 923:          * DBs. Do autoincrement-specific handling in the driver. */
 924: 
 925:         if (isset($options['null']) && $options['null'] === false) {
 926:             $sql .= ' NOT NULL';
 927:         }
 928: 
 929:         if (isset($options['default'])) {
 930:             $default = $options['default'];
 931:             $column  = isset($options['column']) ? $options['column'] : null;
 932:             $sql .= ' DEFAULT ' . $this->quote($default, $column);
 933:         }
 934: 
 935:         return $sql;
 936:     }
 937: 
 938:     /**
 939:      * Generates a DISTINCT clause for SELECT queries.
 940:      *
 941:      * <code>
 942:      * $connection->distinct('posts.id', 'posts.created_at DESC')
 943:      * </code>
 944:      *
 945:      * @param string $columns  A column list.
 946:      * @param string $orderBy  An ORDER clause.
 947:      *
 948:      * @return string  The generated DISTINCT clause.
 949:      */
 950:     public function distinct($columns, $orderBy = null)
 951:     {
 952:         return 'DISTINCT ' . $columns;
 953:     }
 954: 
 955:     /**
 956:      * Adds an ORDER BY clause to an existing query.
 957:      *
 958:      * @param string $sql     An SQL query to manipulate.
 959:      * @param array $options  Options:
 960:      *                        - order: Order column an direction.
 961:      *
 962:      * @return string  The manipulated SQL query.
 963:      */
 964:     public function addOrderByForAssocLimiting($sql, $options)
 965:     {
 966:         return $sql . 'ORDER BY ' . $options['order'];
 967:     }
 968: 
 969:     /**
 970:      * Generates an INTERVAL clause for SELECT queries.
 971:      *
 972:      * @deprecated since version 1.2.0. This function does not work with SQLite
 973:      * as a backend so you should avoid using it. Use "modifyDate()" instead.
 974:      *
 975:      * @param string $interval   The interval.
 976:      * @param string $precision  The precision.
 977:      *
 978:      * @return string  The generated INTERVAL clause.
 979:      */
 980:     public function interval($interval, $precision)
 981:     {
 982:         return 'INTERVAL ' . $precision . ' ' . $interval;
 983:     }
 984: 
 985:     /**
 986:      * Generates a modified date for SELECT queries.
 987:      *
 988:      * @since Horde_Db 1.2.0
 989:      *
 990:      * @param string $reference  The reference date - this is a column
 991:      *                           referenced in the SELECT.
 992:      * @param string $operator   Add or subtract time? (+/-)
 993:      * @param integer $amount    The shift amount (number of days if $interval
 994:      *                           is DAY, etc).
 995:      * @param string $interval   The interval (SECOND, MINUTE, HOUR, DAY,
 996:      *                           MONTH, YEAR).
 997:      *
 998:      * @return string  The generated INTERVAL clause.
 999:      */
1000:     public function modifyDate($reference, $operator, $amount, $interval)
1001:     {
1002:         if (!is_int($amount)) {
1003:             throw new InvalidArgumentException('$amount parameter must be an integer');
1004:         }
1005:         return sprintf('%s %s INTERVAL \'%s\' %s',
1006:                        $reference,
1007:                        $operator,
1008:                        $amount,
1009:                        $interval);
1010:     }
1011: 
1012:     /**
1013:      * Returns an expression using the specified operator.
1014:      *
1015:      * @param string $lhs    The column or expression to test.
1016:      * @param string $op     The operator.
1017:      * @param string $rhs    The comparison value.
1018:      * @param boolean $bind  If true, the method returns the query and a list
1019:      *                       of values suitable for binding as an array.
1020:      * @param array $params  Any additional parameters for the operator.
1021:      *
1022:      * @return string|array  The SQL test fragment, or an array containing the
1023:      *                       query and a list of values if $bind is true.
1024:      */
1025:     public function buildClause($lhs, $op, $rhs, $bind = false,
1026:                                 $params = array())
1027:     {
1028:         $lhs = $this->_escapePrepare($lhs);
1029:         switch ($op) {
1030:         case '|':
1031:         case '&':
1032:             if ($bind) {
1033:                 return array($lhs . ' ' . $op . ' ?',
1034:                              array((int)$rhs));
1035:             }
1036:             return $lhs . ' ' . $op . ' ' . (int)$rhs;
1037: 
1038:         case '~':
1039:             if ($bind) {
1040:                 return array($lhs . ' ' . $op . ' ?', array($rhs));
1041:             }
1042:             return $lhs . ' ' . $op . ' ' . $rhs;
1043: 
1044:         case 'IN':
1045:             if ($bind) {
1046:                 if (is_array($rhs)) {
1047:                     return array($lhs . ' IN (?' . str_repeat(', ?', count($rhs) - 1) . ')', $rhs);
1048:                 }
1049:                 /* We need to bind each member of the IN clause separately to
1050:                  * ensure proper quoting. */
1051:                 if (substr($rhs, 0, 1) == '(') {
1052:                     $rhs = substr($rhs, 1);
1053:                 }
1054:                 if (substr($rhs, -1) == ')') {
1055:                     $rhs = substr($rhs, 0, -1);
1056:                 }
1057: 
1058:                 $ids = preg_split('/\s*,\s*/', $rhs);
1059: 
1060:                 return array($lhs . ' IN (?' . str_repeat(', ?', count($ids) - 1) . ')', $ids);
1061:             }
1062:             if (is_array($rhs)) {
1063:                 return $lhs . ' IN ' . implode(', ', $rhs);
1064:             }
1065:             return $lhs . ' IN ' . $rhs;
1066: 
1067:         case 'LIKE':
1068:             $query = 'LOWER(%s) LIKE LOWER(%s)';
1069:             if ($bind) {
1070:                 if (empty($params['begin'])) {
1071:                     return array(sprintf($query, $lhs, '?'),
1072:                                  array('%' . $rhs . '%'));
1073:                 }
1074:                 return array(sprintf('(' . $query . ' OR ' . $query . ')',
1075:                                      $lhs, '?', $lhs, '?'),
1076:                              array($rhs . '%', '% ' . $rhs . '%'));
1077:             }
1078:             if (empty($params['begin'])) {
1079:                 return sprintf($query,
1080:                                $lhs,
1081:                                $this->_escapePrepare($this->quote('%' . $rhs . '%')));
1082:             }
1083:             return sprintf('(' . $query . ' OR ' . $query . ')',
1084:                            $lhs,
1085:                            $this->_escapePrepare($this->quote($rhs . '%')),
1086:                            $lhs,
1087:                            $this->_escapePrepare($this->quote('% ' . $rhs . '%')));
1088: 
1089:         default:
1090:             if ($bind) {
1091:                 return array($lhs . ' ' . $this->_escapePrepare($op) . ' ?', array($rhs));
1092:             }
1093:             return $lhs . ' ' . $this->_escapePrepare($op . ' ' . $this->quote($rhs));
1094:         }
1095:     }
1096: 
1097:     /**
1098:      * Escapes all characters in a string that are placeholders for
1099:      * prepare/execute methods.
1100:      *
1101:      * @param string $query  A string to escape.
1102:      *
1103:      * @return string  The correctly escaped string.
1104:      */
1105:     protected function _escapePrepare($query)
1106:     {
1107:         return preg_replace('/[?!&]/', '\\\\$0', $query);
1108:     }
1109: 
1110: 
1111:     /*##########################################################################
1112:     # Protected
1113:     ##########################################################################*/
1114: 
1115:     /**
1116:      * Clears the cache for tables when altering them.
1117:      *
1118:      * @param string $tableName  A table name.
1119:      */
1120:     protected function _clearTableCache($tableName)
1121:     {
1122:         $this->_cache->set('tables/columns/' . $tableName, '');
1123:         $this->_cache->set('tables/indexes/' . $tableName, '');
1124:     }
1125: }
1126: 
API documentation generated by ApiGen