/** * * Check whether an index has the right type and has all specified columns. * * @access private * * @param string $idxname The index name. * * @param string $newIdxName The prefixed and suffixed index name. * * @param string $type The index type. * * @param mixed $cols The column names for the index. * * @param mixed $table_indexes Array with all indexes of the table. * * @param string $mode The name of the calling function, this can be either * 'verify' or 'alter'. * * @return bool|object Boolean true if the index has the right type and all * specified columns. Otherwise, either boolean false (case 'alter') or a * PEAR_Error (case 'verify'). * */ function _checkIndex($idxname, $newIdxName, $type, $cols, &$table_indexes, $mode) { $index_found = false; foreach ($table_indexes[$type] as $index_name => $index_fields) { if (strtolower($index_name) == strtolower($newIdxName)) { $index_found = true; array_walk($cols, create_function('&$value,$key', '$value = trim(strtolower($value));')); array_walk($index_fields, create_function('&$value,$key', '$value = trim(strtolower($value));')); foreach ($index_fields as $index_field) { if (($key = array_search($index_field, $cols)) !== false) { unset($cols[$key]); } } break; } } if (!$index_found) { return $mode == 'alter' ? false : DB_Table::throwError(DB_TABLE_ERR_VER_IDX_MISSING, "'{$idxname}' ('{$newIdxName}')"); } if (count($cols) > 0) { // string of column names $colstring = implode(', ', $cols); return $mode == 'alter' ? false : DB_Table::throwError(DB_TABLE_ERR_VER_IDX_COL_MISSING, "'{$idxname}' ({$colstring})"); } return true; }
/** * alter an existing table * * @param string $name name of the table that is intended to be changed. * @param array $changes associative array that contains the details of each type * of change that is intended to be performed. The types of * changes that are currently supported are defined as follows: * * name * * New name for the table. * * add * * Associative array with the names of fields to be added as * indexes of the array. The value of each entry of the array * should be set to another associative array with the properties * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * * * remove * * Associative array with the names of fields to be removed as indexes * of the array. Currently the values assigned to each entry are ignored. * An empty array should be used for future compatibility. * * rename * * Associative array with the names of fields to be renamed as indexes * of the array. The value of each entry of the array should be set to * another associative array with the entry named name with the new * field name and the entry named Declaration that is expected to contain * the portion of the field declaration already in DBMS specific SQL code * as it is used in the CREATE TABLE statement. * * change * * Associative array with the names of the fields to be changed as indexes * of the array. Keep in mind that if it is intended to change either the * name of a field and any other properties, the change array entries * should have the new names of the fields as array indexes. * * The value of each entry of the array should be set to another associative * array with the properties of the fields to that are meant to be changed as * array entries. These entries should be assigned to the new values of the * respective properties. The properties of the fields should be the same * as defined by the Metabase parser. * * Example * array( * 'name' => 'userlist', * 'add' => array( * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 * ) * ), * 'remove' => array( * 'file_limit' => array(), * 'time_limit' => array() * ), * 'change' => array( * 'name' => array( * 'length' => '20', * 'definition' => array( * 'type' => 'text', * 'length' => 20, * ), * ) * ), * 'rename' => array( * 'sex' => array( * 'name' => 'gender', * 'definition' => array( * 'type' => 'text', * 'length' => 1, * 'default' => 'M', * ), * ) * ) * ) * * @param boolean $check (ignored in DB_Table) * @access public * * @return mixed DB_OK on success, a PEAR error on failure */ function alterTable($name, $changes, $check) { foreach ($changes as $change_name => $change) { switch ($change_name) { case 'add': case 'remove': case 'change': case 'rename': case 'name': break; default: return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS); } } $query = ''; if (array_key_exists('name', $changes)) { $change_name = $this->_db->quoteIdentifier($changes['name']); $query .= 'RENAME TO ' . $change_name; } if (array_key_exists('add', $changes)) { foreach ($changes['add'] as $field_name => $field) { if ($query) { $query .= ', '; } $query .= 'ADD ' . $field_name . ' ' . $field; } } if (array_key_exists('remove', $changes)) { foreach ($changes['remove'] as $field_name => $field) { if ($query) { $query .= ', '; } $field_name = $this->_db->quoteIdentifier($field_name); $query .= 'DROP ' . $field_name; } } $rename = array(); if (array_key_exists('rename', $changes)) { foreach ($changes['rename'] as $field_name => $field) { $rename[$field['name']] = $field_name; } } if (array_key_exists('change', $changes)) { foreach ($changes['change'] as $field_name => $field) { if ($query) { $query .= ', '; } if (isset($rename[$field_name])) { $old_field_name = $rename[$field_name]; unset($rename[$field_name]); } else { $old_field_name = $field_name; } $old_field_name = $this->_db->quoteIdentifier($old_field_name); $query .= "CHANGE {$old_field_name} " . $field_name . ' ' . $field['definition']; } } if (!empty($rename)) { foreach ($rename as $rename_name => $renamed_field) { if ($query) { $query .= ', '; } $field = $changes['rename'][$renamed_field]; $renamed_field = $this->_db->quoteIdentifier($renamed_field); $query .= 'CHANGE ' . $renamed_field . ' ' . $field['name'] . ' ' . $renamed_field['definition']; } } if (!$query) { return DB_OK; } $name = $this->_db->quoteIdentifier($name); return $this->_db->query("ALTER TABLE {$name} {$query}"); }
/** * alter an existing table * * @param string $name name of the table that is intended to be changed. * @param array $changes associative array that contains the details of each type * of change that is intended to be performed. The types of * changes that are currently supported are defined as follows: * * name * * New name for the table. * * add * * Associative array with the names of fields to be added as * indexes of the array. The value of each entry of the array * should be set to another associative array with the properties * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * * * remove * * Associative array with the names of fields to be removed as indexes * of the array. Currently the values assigned to each entry are ignored. * An empty array should be used for future compatibility. * * rename * * Associative array with the names of fields to be renamed as indexes * of the array. The value of each entry of the array should be set to * another associative array with the entry named name with the new * field name and the entry named Declaration that is expected to contain * the portion of the field declaration already in DBMS specific SQL code * as it is used in the CREATE TABLE statement. * * change * * Associative array with the names of the fields to be changed as indexes * of the array. Keep in mind that if it is intended to change either the * name of a field and any other properties, the change array entries * should have the new names of the fields as array indexes. * * The value of each entry of the array should be set to another associative * array with the properties of the fields to that are meant to be changed as * array entries. These entries should be assigned to the new values of the * respective properties. The properties of the fields should be the same * as defined by the Metabase parser. * * Example * array( * 'name' => 'userlist', * 'add' => array( * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 * ) * ), * 'remove' => array( * 'file_limit' => array(), * 'time_limit' => array() * ), * 'change' => array( * 'name' => array( * 'length' => '20', * 'definition' => array( * 'type' => 'text', * 'length' => 20, * ), * ) * ), * 'rename' => array( * 'sex' => array( * 'name' => 'gender', * 'definition' => array( * 'type' => 'text', * 'length' => 1, * 'default' => 'M', * ), * ) * ) * ) * * @param boolean $check (ignored in DB_Table) * @access public * * @return mixed DB_OK on success, a PEAR error on failure */ function alterTable($name, $changes, $check) { $version = $this->_getServerVersion(); foreach ($changes as $change_name => $change) { switch ($change_name) { case 'add': if ($version['major'] >= 3 && $version['minor'] >= 1) { break; } case 'name': if ($version['major'] >= 3 && $version['minor'] >= 1) { break; } case 'remove': case 'change': case 'rename': default: return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS); } } $query = ''; if (array_key_exists('name', $changes)) { $change_name = $this->_db->quoteIdentifier($changes['name']); $query .= 'RENAME TO ' . $change_name; } if (array_key_exists('add', $changes)) { foreach ($changes['add'] as $field_name => $field) { if ($query) { $query .= ', '; } $query .= 'ADD COLUMN ' . $field_name . ' ' . $field; } } if (!$query) { return DB_OK; } $name = $this->_db->quoteIdentifier($name); return $this->_db->query("ALTER TABLE {$name} {$query}"); }
/** * alter an existing table * * @param string $name name of the table that is intended to be changed. * @param array $changes associative array that contains the details of each type * of change that is intended to be performed. The types of * changes that are currently supported are defined as follows: * * name * * New name for the table. * * add * * Associative array with the names of fields to be added as * indexes of the array. The value of each entry of the array * should be set to another associative array with the properties * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * * * remove * * Associative array with the names of fields to be removed as indexes * of the array. Currently the values assigned to each entry are ignored. * An empty array should be used for future compatibility. * * rename * * Associative array with the names of fields to be renamed as indexes * of the array. The value of each entry of the array should be set to * another associative array with the entry named name with the new * field name and the entry named Declaration that is expected to contain * the portion of the field declaration already in DBMS specific SQL code * as it is used in the CREATE TABLE statement. * * change * * Associative array with the names of the fields to be changed as indexes * of the array. Keep in mind that if it is intended to change either the * name of a field and any other properties, the change array entries * should have the new names of the fields as array indexes. * * The value of each entry of the array should be set to another associative * array with the properties of the fields to that are meant to be changed as * array entries. These entries should be assigned to the new values of the * respective properties. The properties of the fields should be the same * as defined by the Metabase parser. * * Example * array( * 'name' => 'userlist', * 'add' => array( * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 * ) * ), * 'remove' => array( * 'file_limit' => array(), * 'time_limit' => array() * ), * 'change' => array( * 'name' => array( * 'length' => '20', * 'definition' => array( * 'type' => 'text', * 'length' => 20, * ), * ) * ), * 'rename' => array( * 'sex' => array( * 'name' => 'gender', * 'definition' => array( * 'type' => 'text', * 'length' => 1, * 'default' => 'M', * ), * ) * ) * ) * * @param boolean $check (ignored in DB_Table) * @access public * * @return mixed DB_OK on success, a PEAR error on failure */ function alterTable($name, $changes, $check) { foreach ($changes as $change_name => $change) { switch ($change_name) { case 'add': case 'remove': case 'change': case 'name': case 'rename': break; default: return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS); } } if (array_key_exists('add', $changes)) { foreach ($changes['add'] as $field_name => $field) { $query = 'ADD ' . $field_name . ' ' . $field; $result = $this->_db->query("ALTER TABLE {$name} {$query}"); if (PEAR::isError($result)) { return $result; } } } if (array_key_exists('remove', $changes)) { foreach ($changes['remove'] as $field_name => $field) { $field_name = $this->_db->quoteIdentifier($field_name); $query = 'DROP ' . $field_name; $result = $this->_db->query("ALTER TABLE {$name} {$query}"); if (PEAR::isError($result)) { return $result; } } } if (array_key_exists('change', $changes)) { foreach ($changes['change'] as $field_name => $field) { $field_name = $this->_db->quoteIdentifier($field_name); if (array_key_exists('type', $field)) { $query = "ALTER {$field_name} TYPE " . $field['definition']; $result = $this->_db->query("ALTER TABLE {$name} {$query}"); if (PEAR::isError($result)) { return $result; } } /* default / notnull changes not (yet) supported in DB_Table if (array_key_exists('default', $field)) { $query = "ALTER $field_name SET DEFAULT ".$db->quote($field['definition']['default'], $field['definition']['type']); $result = $db->exec("ALTER TABLE $name $query"); if (PEAR::isError($result)) { return $result; } } if (array_key_exists('notnull', $field)) { $query.= "ALTER $field_name ".($field['definition']['notnull'] ? "SET" : "DROP").' NOT NULL'; $result = $db->exec("ALTER TABLE $name $query"); if (PEAR::isError($result)) { return $result; } } */ } } if (array_key_exists('rename', $changes)) { foreach ($changes['rename'] as $field_name => $field) { $field_name = $this->_db->quoteIdentifier($field_name); $result = $this->_db->query("ALTER TABLE {$name} RENAME COLUMN {$field_name} TO " . $this->_db->quoteIdentifier($field['name'])); if (PEAR::isError($result)) { return $result; } } } $name = $this->_db->quoteIdentifier($name); if (array_key_exists('name', $changes)) { $change_name = $this->_db->quoteIdentifier($changes['name']); $result = $this->_db->query("ALTER TABLE {$name} RENAME TO " . $change_name); if (PEAR::isError($result)) { return $result; } } return DB_OK; }
/** * * Generates a sequence value; sequence name defaults to the table name. * * @access public * * @param string $seq_name The sequence name; defaults to table_id. * * @return integer The next value in the sequence. * * @see DB::nextID() * * @see MDB2::nextID() * */ function nextID($seq_name = null) { if (is_null($seq_name)) { $seq_name = "{$this->table}"; } else { $seq_name = "{$this->table}_{$seq_name}"; } // the maximum length is 30, but PEAR DB/MDB2 will add "_seq" to the // name, so the max length here is less 4 chars. we have to // check here because the sequence will be created automatically // by PEAR DB/MDB2, which will not check for length on its own. if (strlen($seq_name) > 26) { return DB_Table::throwError(DB_TABLE_ERR_SEQ_STRLEN, " ('{$seq_name}')"); } return $this->db->nextId($seq_name); }
/** * alter an existing table * * @param string $name name of the table that is intended to be changed. * @param array $changes associative array that contains the details of each type * of change that is intended to be performed. The types of * changes that are currently supported are defined as follows: * * name * * New name for the table. * * add * * Associative array with the names of fields to be added as * indexes of the array. The value of each entry of the array * should be set to another associative array with the properties * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * * * remove * * Associative array with the names of fields to be removed as indexes * of the array. Currently the values assigned to each entry are ignored. * An empty array should be used for future compatibility. * * rename * * Associative array with the names of fields to be renamed as indexes * of the array. The value of each entry of the array should be set to * another associative array with the entry named name with the new * field name and the entry named Declaration that is expected to contain * the portion of the field declaration already in DBMS specific SQL code * as it is used in the CREATE TABLE statement. * * change * * Associative array with the names of the fields to be changed as indexes * of the array. Keep in mind that if it is intended to change either the * name of a field and any other properties, the change array entries * should have the new names of the fields as array indexes. * * The value of each entry of the array should be set to another associative * array with the properties of the fields to that are meant to be changed as * array entries. These entries should be assigned to the new values of the * respective properties. The properties of the fields should be the same * as defined by the Metabase parser. * * Example * array( * 'name' => 'userlist', * 'add' => array( * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 * ) * ), * 'remove' => array( * 'file_limit' => array(), * 'time_limit' => array() * ), * 'change' => array( * 'name' => array( * 'length' => '20', * 'definition' => array( * 'type' => 'text', * 'length' => 20, * ), * ) * ), * 'rename' => array( * 'sex' => array( * 'name' => 'gender', * 'definition' => array( * 'type' => 'text', * 'length' => 1, * 'default' => 'M', * ), * ) * ) * ) * * @param boolean $check (ignored in DB_Table) * @access public * * @return mixed DB_OK on success, a PEAR error on failure */ function alterTable($name, $changes, $check) { foreach ($changes as $change_name => $change) { switch ($change_name) { case 'add': case 'remove': case 'rename': case 'change': break; default: return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS); } } $query = ''; if (array_key_exists('add', $changes)) { foreach ($changes['add'] as $field_name => $field) { if ($query) { $query .= ', '; } $query .= 'ADD ' . $field_name . ' ' . $field; } } if (array_key_exists('remove', $changes)) { foreach ($changes['remove'] as $field_name => $field) { if ($query) { $query .= ', '; } $field_name = $this->_db->quoteIdentifier($field_name); $query .= 'DROP ' . $field_name; } } if (array_key_exists('rename', $changes)) { foreach ($changes['rename'] as $field_name => $field) { if ($query) { $query .= ', '; } $field_name = $this->_db->quoteIdentifier($field_name); $query .= 'ALTER ' . $field_name . ' TO ' . $this->_db->quoteIdentifier($field['name']); } } if (array_key_exists('change', $changes)) { // missing support to change DEFAULT and NULLability foreach ($changes['change'] as $field_name => $field) { if ($query) { $query .= ', '; } $field_name = $this->_db->quoteIdentifier($field_name); $query .= 'ALTER ' . $field_name . ' TYPE ' . $field['definition']; } } if (!strlen($query)) { return DB_OK; } $name = $this->_db->quoteIdentifier($name); $result = $this->_db->query("ALTER TABLE {$name} {$query}"); return $result; }
/** * alter an existing table * * @param string $name name of the table that is intended to be changed. * @param array $changes associative array that contains the details of each type * of change that is intended to be performed. The types of * changes that are currently supported are defined as follows: * * name * * New name for the table. * * add * * Associative array with the names of fields to be added as * indexes of the array. The value of each entry of the array * should be set to another associative array with the properties * of the fields to be added. The properties of the fields should * be the same as defined by the Metabase parser. * * * remove * * Associative array with the names of fields to be removed as indexes * of the array. Currently the values assigned to each entry are ignored. * An empty array should be used for future compatibility. * * rename * * Associative array with the names of fields to be renamed as indexes * of the array. The value of each entry of the array should be set to * another associative array with the entry named name with the new * field name and the entry named Declaration that is expected to contain * the portion of the field declaration already in DBMS specific SQL code * as it is used in the CREATE TABLE statement. * * change * * Associative array with the names of the fields to be changed as indexes * of the array. Keep in mind that if it is intended to change either the * name of a field and any other properties, the change array entries * should have the new names of the fields as array indexes. * * The value of each entry of the array should be set to another associative * array with the properties of the fields to that are meant to be changed as * array entries. These entries should be assigned to the new values of the * respective properties. The properties of the fields should be the same * as defined by the Metabase parser. * * Example * array( * 'name' => 'userlist', * 'add' => array( * 'quota' => array( * 'type' => 'integer', * 'unsigned' => 1 * ) * ), * 'remove' => array( * 'file_limit' => array(), * 'time_limit' => array() * ), * 'change' => array( * 'name' => array( * 'length' => '20', * 'definition' => array( * 'type' => 'text', * 'length' => 20, * ), * ) * ), * 'rename' => array( * 'sex' => array( * 'name' => 'gender', * 'definition' => array( * 'type' => 'text', * 'length' => 1, * 'default' => 'M', * ), * ) * ) * ) * * @param boolean $check (ignored in DB_Table) * @access public * * @return mixed DB_OK on success, a PEAR error on failure */ function alterTable($name, $changes, $check) { foreach ($changes as $change_name => $change) { switch ($change_name) { case 'add': case 'remove': case 'change': case 'name': case 'rename': break; default: return DB_Table::throwError(DB_TABLE_ERR_ALTER_TABLE_IMPOS); } } $name = $this->_db->quoteIdentifier($name); if (array_key_exists('add', $changes)) { $fields = array(); foreach ($changes['add'] as $field_name => $field) { $fields[] = $field_name . ' ' . $field; } $result = $this->_db->query("ALTER TABLE {$name} ADD (" . implode(', ', $fields) . ')'); if (PEAR::isError($result)) { return $result; } } if (array_key_exists('change', $changes)) { $fields = array(); foreach ($changes['change'] as $field_name => $field) { $fields[] = $field_name . ' ' . $field['definition']; } $result = $this->_db->query("ALTER TABLE {$name} MODIFY (" . implode(', ', $fields) . ')'); if (PEAR::isError($result)) { return $result; } } if (array_key_exists('rename', $changes)) { foreach ($changes['rename'] as $field_name => $field) { $field_name = $this->_db->quoteIdentifier($field_name); $query = "ALTER TABLE {$name} RENAME COLUMN {$field_name} TO " . $this->_db->quoteIdentifier($field['name']); $result = $this->_db->query($query); if (PEAR::isError($result)) { return $result; } } } if (array_key_exists('remove', $changes)) { $fields = array(); foreach ($changes['remove'] as $field_name => $field) { $fields[] = $this->_db->quoteIdentifier($field_name); } $result = $this->_db->query("ALTER TABLE {$name} DROP COLUMN " . implode(', ', $fields)); if (PEAR::isError($result)) { return $result; } } if (array_key_exists('name', $changes)) { $change_name = $this->_db->quoteIdentifier($changes['name']); $result = $this->_db->query("ALTER TABLE {$name} RENAME TO " . $change_name); if (PEAR::isError($result)) { return $result; } } return DB_OK; }
/** * * Get the column declaration string for a DB_Table column. * * @static * * @access public * * @param string $phptype The DB phptype key. * * @param string $coltype The DB_Table column type. * * @param int $size The size for the column (needed for string and * decimal). * * @param int $scope The scope for the column (needed for decimal). * * @param bool $require True if the column should be NOT NULL, false * allowed to be NULL. * * @param string $default The SQL calculation for a default value. * * @return string|object A declaration string on success, or a * PEAR_Error on failure. * */ function getDeclare($phptype, $coltype, $size = null, $scope = null, $require = null, $default = null) { // validate char and varchar: does it have a size? if (($coltype == 'char' || $coltype == 'varchar') && ($size < 1 || $size > 255)) { return DB_Table::throwError(DB_TABLE_ERR_DECLARE_STRING, "(size='{$size}')"); } // validate decimal: does it have a size and scope? if ($coltype == 'decimal' && ($size < 1 || $size > 255 || $scope < 0 || $scope > $size)) { return DB_Table::throwError(DB_TABLE_ERR_DECLARE_DECIMAL, "(size='{$size}' scope='{$scope}')"); } // map of column types and declarations for this RDBMS $map = $GLOBALS['_DB_TABLE']['type'][$phptype]; // is it a recognized column type? $types = array_keys($map); if (!in_array($coltype, $types)) { return DB_Table::throwError(DB_TABLE_ERR_DECLARE_TYPE, "('{$coltype}')"); } // basic declaration switch ($coltype) { case 'char': case 'varchar': $declare = $map[$coltype] . "({$size})"; break; case 'decimal': $declare = $map[$coltype] . "({$size},{$scope})"; break; default: $declare = $map[$coltype]; break; } // set the "NULL"/"NOT NULL" portion $declare .= $require ? ' NOT NULL' : ' NULL'; // set the "DEFAULT" portion $declare .= $default ? " DEFAULT {$default}" : ''; // done return $declare; }