From dbe45bfe5ed6de3bc34dec721a1ee2825a125ad8 Mon Sep 17 00:00:00 2001 From: chriskl Date: Sat, 14 Dec 2002 10:56:26 +0000 Subject: [PATCH] drop and create indexes --- classes/database/Postgres.php | 2114 +++++++++++++++++---------------- public_html/tblproperties.php | 180 ++- 2 files changed, 1180 insertions(+), 1114 deletions(-) diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index a534c768..a2452182 100755 --- a/classes/database/Postgres.php +++ b/classes/database/Postgres.php @@ -1,1049 +1,1065 @@ - 'datname', 'dbcomment' => 'description'); - var $tbFields = array('tbname' => 'tablename', 'tbowner' => 'tableowner'); - var $vwFields = array('vwname' => 'viewname', 'vwowner' => 'viewowner', 'vwdef' => 'definition'); - var $uFields = array('uname' => 'usename', 'usuper' => 'usesuper', 'ucreatedb' => 'usecreatedb', 'uexpires' => 'valuntil'); - var $sqFields = array('seqname' => 'relname', 'seqowner' => 'usename', 'lastvalue' => 'last_value', 'incrementby' => 'increment_by', 'maxvalue' => 'max_value', 'minvalue'=> 'min_value', 'cachevalue' => 'cache_value', 'logcount' => 'log_cnt', 'iscycled' => 'is_cycled', 'iscalled' => 'is_called' ); - var $ixFields = array('idxname' => 'relname', 'idxdef' => 'pg_get_indexdef', 'uniquekey' => 'indisunique', 'primarykey' => 'indisprimary'); - var $tgFields = array('tgname' => 'tgname'); - - // Last oid assigned to a system object - var $_lastSystemOID = 18539; - var $_maxNameLen = 31; - - // Name of id column - var $id = 'oid'; - - function Postgres($host, $port, $database, $user, $password) { - $this->BaseDB('postgres7'); - - //$this->conn->host = $host - //$this->Port = $port; - $pghost = "$host:$port"; - - $this->conn->connect($pghost, $user, $password, $database); - } - - /** - * A function to check that the database functions are installed - * and running. - * @return True on success, false otherwise - */ - function isLoaded() { - return function_exists('pg_connect'); - } - - // Table functions - - /** - * Get the fields for uniquely identifying a row in a table - * @param $table The table for which to retrieve the identifier - * @return An array mapping attribute number to attribute name, empty for no identifiers - * @return -1 error - */ - function getRowIdentifier($table) { - $this->clean($table); - - $status = $this->beginTransaction(); - if ($status != 0) return -1; - - $sql = "SELECT indrelid, indkey FROM pg_index WHERE indisprimary AND indrelid=(SELECT oid FROM pg_class WHERE relname='{$table}')"; - $rs = $this->selectSet($sql); - - // If none, search for OID column - if ($rs->recordCount() == 0) { - $sql = "SELECT relhasoids FROM pg_class WHERE relname='{$table}'"; - $rs2 = $this->selectSet($sql); - if ($rs2->recordCount() == 0) { - $this->rollbackTransaction(); - return -1; - } - elseif ($rs2->f['relhasoids'] == 'f') { - $this->endTransaction(); - return array(); - } - else { - $this->endTransaction(); - return array(-2 => 'oid'); - } - } - // Otherwise select to find the names of the keys - else { - $in = str_replace(' ', ',', $rs->f['indkey']); - $sql = "SELECT attnum, attname FROM pg_attribute WHERE attrelid='{$rs->f['indrelid']}' AND attnum IN ({$in})"; - $rs2 = $this->selectSet($sql); - if ($rs2->recordCount() == 0) { - $this->rollbackTransaction(); - return -1; - } - else { - $temp = array(); - while (!$rs2->EOF) { - $temp[$rs2->f['attnum']] = $rs2->f['attname']; - $rs2->moveNext(); - } - $this->endTransaction(); - return $temp; - } - } - } - - /** - * Outputs the HTML code for a particular field - * @param $name The name to give the field - * @param $value The value of the field. Note this could be 'numeric(7,2)' sort of thing... - * @param $type The database type of the field - */ - function printField($name, $value, $type) { - switch ($type) { - case 'bool': - case 'boolean': - echo "\n"; - break; - case 'text': - case 'bytea': - echo "\n"; - break; - default: - echo "\n"; - break; - } - } - - /** - * Return all database available on the server - * @return A list of databases, sorted alphabetically - */ - function &getLanguages() { - $sql = ""; - return $this->selectSet($sql); - } - - /** - * Return all information about a particular database - * @param $database The name of the database to retrieve - * @return The database info - */ - function &getLanguage($database) { - $this->clean($database); - $sql = "SELECT * FROM pg_database WHERE datname='{$database}'"; - return $this->selectRow($sql); - } - - /** - * Creates a database - * @param $database The name of the database to create - * @return 0 success - */ - function createDatabase($database) { - $this->clean($database); - $sql = "CREATE DATABASE \"{$database}\""; - return $this->execute($sql); - } - - /** - * Drops a database - * @param $database The name of the database to drop - * @return 0 success - */ - function dropDatabase($database) { - $this->clean($database); - $sql = "DROP DATABASE \"{$database}\""; - return $this->execute($sql); - } - - // Table functions - - /** - * Return all tables in current database - * @return All tables, sorted alphabetically - */ - function &getTables() { - if (!$this->_showSystem) $where = "WHERE tablename NOT LIKE 'pg_%' "; - else $where = ''; - $sql = "SELECT tablename, tableowner FROM pg_tables {$where}ORDER BY tablename"; - return $this->selectSet($sql); - } - - /** - * Return all information relating to a table - * @param $table The name of the table - * @return Table information - */ - function &getTableByName($table) { - $this->clean($table); - $sql = "SELECT * FROM pg_class WHERE relname='{$table}'"; - return $this->selectRow($sql); - } - - /** - * Retrieve the attribute definition of a table - * @param $table The name of the table - * @param $field (optional) The name of a field to return - * @return All attributes in order - */ - function &getTableAttributes($table, $field = '') { - $this->clean($table); - $this->clean($field); - - if ($field == '') { - $sql = "SELECT - a.attname, t.typname as type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum - FROM - pg_class c, pg_attribute a, pg_type t - WHERE - c.relname = '{$table}' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid - ORDER BY a.attnum"; - } - else { - $sql = "SELECT - a.attname, t.typname as type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum - FROM - pg_class c, pg_attribute a, pg_type t - WHERE - c.relname = '{$table}' AND a.attname='{$column}' AND a.attrelid = c.oid AND a.atttypid = t.oid - ORDER BY a.attnum"; - } - - return $this->selectSet($sql); - } - - /** - * Drops a column from a table - * @param $table The table from which to drop a column - * @param $column The column to be dropped - * @param $behavior CASCADE or RESTRICT or empty - * @return 0 success - * @return -99 not implemented - */ - function dropColumn($table, $column, $behavior) { - return -99; - } - - /** - * Alters a column in a table - * @param $table The table in which the column resides - * @param $column The column to alter - * @param $name The new name for the column - * @param $notnull (boolean) True if not null, false otherwise - * @param $default The new default for the column - * @return 0 success - * @return -1 set not null error - * @return -2 set default error - * @return -3 rename column error - */ - function alterColumn($table, $column, $name, $notnull, $default) { - $this->beginTransaction(); - - // @@ NEED TO HANDLE "NESTED" TRANSACTION HERE - $status = $this->setColumnNull($table, $column, !$notnull); - if ($status != 0) { - $this->rollbackTransaction(); - return -1; - } - - $status = $this->setColumnDefault($table, $column, $default); - if ($status != 0) { - $this->rollbackTransaction(); - return -2; - } - - $status = $this->renameColumn($table, $column, $name); - if ($status != 0) { - $this->rollbackTransaction(); - return -3; - } - - return $this->endTransaction(); - } - - /** - * Creates a new table in the database - * @param $name The name of the table - * @param $fields The number of fields - * @param $field An array of field names - * @param $type An array of field types - * @param $length An array of field lengths - * @param $notnull An array of not null - * @param $default An array of default values - * @return 0 success - * @return -1 no fields supplied - */ - function createTable($name, $fields, $field, $type, $length, $notnull, $default) { - // @@ NOTE: $default field not being cleaned - how on earth DO we clean it?? - $this->fieldClean($name); - - $found = false; - $sql = "CREATE TABLE \"{$name}\" ("; - - for ($i = 0; $i < $fields; $i++) { - $this->fieldClean($field[$i]); - $this->clean($type[$i]); - $this->clean($length[$i]); - - // Skip blank columns - for user convenience - if ($field[$i] == '' || $type[$i] == '') continue; - - $sql .= "\"{$field[$i]}\" {$type[$i]}"; - if ($length[$i] != '') $sql .= "({$length[$i]})"; - if (isset($notnull[$i])) $sql .= " NOT NULL"; - if ($default[$i] != '') $sql .= " DEFAULT {$default[$i]}"; - if ($i != $fields - 1) $sql .= ", "; - - $found = true; - } - - if (!$found) return -1; - - $sql .= ")"; - - return $this->execute($sql); - } - - /** - * Removes a table from the database - * @param $table The table to drop - * @return 0 success - */ - function dropTable($table) { - $this->fieldClean($table); - - $sql = "DROP TABLE \"{$table}\""; - - return $this->execute($sql); - } - - /** - * Empties a table in the database - * @param $table The table to be emptied - * @return 0 success - */ - function emptyTable($table) { - $this->fieldClean($table); - - $sql = "DELETE FROM \"{$table}\""; - - return $this->execute($sql); - } - - /** - * Renames a table - * @param $table The table to be renamed - * @param $newName The new name for the table - * @return 0 success - */ - function renameTable($table, $newName) { - $this->fieldClean($table); - $this->fieldClean($newName); - - $sql = "ALTER TABLE \"{$table}\" RENAME TO \"{$newName}\""; - - // @@ How do you do this? - return $this->execute($sql); - } - - /** - * Returns a recordset of all columns in a table - * @param $table The name of a table - * @param $offset The offset into the table - * @param $limit The maximum number of records to return at once - * @return A recordset - */ - function &getTableRows($table) { - $this->fieldClean($table); - - return $this->selectTable("SELECT COUNT(*) FROM \"{$table}\"", $offset, $limit); - } - - /** - * Returns a recordset of all columns in a table - * @param $table The name of a table - * @param $offset The offset into the table - * @param $limit The maximum number of records to return at once - * @return A recordset - */ - function &browseTable($table, $offset = null, $limit = null) { - $this->fieldClean($table); - - return $this->selectTable("SELECT oid, * FROM \"{$table}\"", $offset, $limit); - } - - /** - * Returns a recordset of all columns in a table - * @param $table The name of a table - * @param $key The associative array holding the key to retrieve - * @return A recordset - */ - function &browseRow($table, $key) { - $this->fieldClean($table); - - $sql = "SELECT * FROM \"{$table}\" WHERE true"; - foreach ($key as $k => $v) { - $this->fieldClean($k); - $this->clean($v); - $sql .= " AND \"{$k}\"='{$v}'"; - } - - return $this->selectSet($sql); - } - - /** - * - */ - function &selectTable($sql, $offset, $limit) { - return $this->selectSet($sql, $offset, $limit); - } - - // Sequence functions - - /** - * Returns all sequences in the current database - * @return A recordset - */ - function &getSequences() { - if (!$this->_showSystem) $where = " AND relname NOT LIKE 'pg_%'"; - else $where = ''; - $sql = "SELECT c.relname, u.usename FROM pg_class c, pg_user u WHERE c.relowner=u.usesysid AND c.relkind = 'S'{$where} ORDER BY relname"; - return $this->selectSet( $sql ); - } - - /** - * Returns properties of a single sequence - * @return A recordset - */ - function &getSequence($sequence) { - if (!$this->_showSystem) $where = " AND relname NOT LIKE 'pg_%'"; - else $where = ''; - $sql = "SELECT sequence_name as relname,* FROM $sequence"; - return $this->selectSet( $sql ); - } - - /** - * Drops a given sequence - * @return 0 success - */ - function &dropSequence($sequence) { - $this->clean($sequence); - $sql = "DROP SEQUENCE {$sequence} "; - return $this->execute($sql); - } - - /** - * Creates a new sequence - * @return 0 success - */ - function &setSequence($sequence,$startval=1) { - $this->clean($sequence); - $sql = "CREATE SEQUENCE $seq_name START $startval"; - return $this->execute($sql); - } - - /** - * Modifies permissions on a given sequence - * @return 0 success - */ - function &setSequencePermissions($sequence) { - - } - - /** - * Resets a given sequence to 1 - * @return 0 success - */ - function &resetSequence($sequence) { - $this->clean($sequence); - $sql = "SELECT setval('$sequence',1)"; - return $this->execute($sql); - } - - - - - /** - * Adds a check constraint to a table - * @param $table The table to which to add the check - * @param $definition The definition of the check - * @param $name (optional) The name to give the check, otherwise default name is assigned - * @return 0 success - */ - function addCheckConstraint($table, $definition, $name = '') { - $this->fieldClean($table); - $this->fieldClean($name); - // @@ how the heck do you clean definition??? - - if ($name != '') - $sql = "ALTER TABLE \"{$table}\" ADD CONSTRAINT \"{$name}\" CHECK ({$definition})"; - else - $sql = "ALTER TABLE \"{$table}\" ADD CHECK ({$definition})"; - - // @@ How do you do this? - return $this->execute($sql); - } - - /** - * Drops a check constraint from a table - * @param $table The table from which to drop the check - * @param $name The name of the check to be dropped - * @return 0 success - * @return -2 transaction error - * @return -3 lock error - * @return -4 check drop error - */ - function dropCheckConstraint($table, $name) { - $this->clean($table); - $this->clean($name); - - // Begin transaction - $status = $this->beginTransaction(); - if ($status != 0) return -2; - - // Properly lock the table - $sql = "LOCK TABLE \"{$table}\" IN ACCESS EXCLUSIVE MODE"; - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -3; - } - - // Delete the check constraint - $sql = "DELETE FROM pg_relcheck WHERE rcrelid=(SELECT oid FROM pg_class WHERE relname='{$table}') AND rcname='{$name}'"; - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -4; - } - - // Update the pg_class catalog to reflect the new number of checks - $sql = "UPDATE pg_class SET relchecks=(SELECT COUNT(*) FROM pg_relcheck WHERE - rcrelid=(SELECT oid FROM pg_class WHERE relname='{$table}')) - WHERE relname='{$table}'"; - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -4; - } - - // Otherwise, close the transaction - return $this->endTransaction(); - } - - /** - * Adds a unique constraint to a table - * @param $table The table to which to add the unique - * @param $fields (array) An array of fields over which to add the unique - * @param $name (optional) The name to give the unique, otherwise default name is assigned - * @return 0 success - */ - function addUniqueConstraint($table, $fields, $name = '') { - $this->fieldClean($table); - $this->arrayClean($fields); - $this->fieldClean($name); - - if ($name != '') - $sql = "CREATE UNIQUE INDEX \"{$name}\" ON \"{$table}\"(\"" . join('","', $fields) . "\")"; - else return -99; // Not supported - - return $this->execute($sql); - } - - /** - * Drops a unique constraint from a table - * @param $table The table from which to drop the unique - * @param $name The name of the unique - * @return 0 success - */ - function dropUniqueConstraint($table, $name) { - $this->fieldClean($name); - - $sql = "DROP INDEX \"{$name}\""; - - return $this->execute($sql); - } - - /** - * Adds a primary key constraint to a table - * @param $table The table to which to add the primery key - * @param $fields (array) An array of fields over which to add the primary key - * @param $name (optional) The name to give the key, otherwise default name is assigned - * @return 0 success - */ - function addPrimaryKeyConstraint($table, $fields, $name = '') { - // This function can be faked with a unique index and a catalog twiddle, however - // how do we ensure that it's only used on NOT NULL fields? - return -99; // Not supported. - } - - /** - * Drops a primary key constraint from a table - * @param $table The table from which to drop the primary key - * @param $name The name of the primary key - * @return 0 success - */ - function dropPrimaryKeyConstraint($table, $name) { - $this->fieldClean($name); - - $sql = "DROP INDEX \"{$name}\""; - - return $this->execute($sql); - } - - /** - * Changes the owner of a table - * @param $table The table whose owner is to change - * @param $owner The new owner (username) of the table - * @return 0 success - */ - function setOwnerOfTable($table, $owner) { - $this->fieldClean($table); - $this->fieldClean($owner); - - $sql = "ALTER TABLE \"{$table}\" OWNER TO \"{$owner}\""; - - return $this->execute($sql); - } - - // Column Functions - - /** - * Add a new column to a table - * @param $table The table to add to - * @param $column The name of the new column - * @param $type The type of the column - * @param $size (optional) The optional size of the column (ie. 30 for varchar(30)) - * @return 0 success - */ - function addColumnToTable($table, $column, $type, $size = '') { - $this->clean($table); - $this->clean($column); - $this->clean($type); - $this->clean($size); - // @@ How the heck do you properly clean type and size? - - if ($size == '') - $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" {$type}"; - else - $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" {$type}({$size})"; - - // @@ How do you do this? - return $this->execute($sql); - } - - /** - * Drops a column from a table - * @param $table The table from which to drop - * @param $column The column name to drop - * @return 0 success - */ - function dropColumnFromTable($table, $column) { - return -99; // Not implemented - } - - /** - * Sets default value of a column - * @param $table The table from which to drop - * @param $column The column name to set - * @param $default The new default value - * @return 0 success - */ - function setColumnDefault($table, $column, $default) { - $this->fieldClean($table); - $this->fieldClean($column); - // @@ How the heck do you clean default clause? - - $sql = "ALTER TABLE \"{$table}\" ALTER COLUMN \"{$column}\" SET DEFAULT {$default}"; - - return $this->execute($sql); - } - - /** - * Drops default value of a column - * @param $table The table from which to drop - * @param $column The column name to drop default - * @return 0 success - */ - function dropColumnDefault($table, $column) { - $this->clean($table); - $this->clean($column); - - $sql = "ALTER TABLE \"{$table}\" ALTER COLUMN \"{$column}\" DROP DEFAULT"; - - // @@ How do you do this? - return $this->execute($sql); - } - - /** - * Sets whether or not a column can contain NULLs - * @param $table The table that contains the column - * @param $column The column to alter - * @param $state True to set null, false to set not null - * @return 0 success - * @return -1 attempt to set not null, but column contains nulls - * @return -2 transaction error - * @return -3 lock error - * @return -4 update error - */ - function setColumnNull($table, $column, $state) { - $this->clean($table); - $this->clean($column); - - // Begin transaction - $status = $this->beginTransaction(); - if ($status != 0) return -2; - - // Properly lock the table - $sql = "LOCK TABLE \"{$table}\" IN ACCESS EXCLUSIVE MODE"; - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -3; - } - - // Check for existing nulls - if (!$state) { - $sql = "SELECT COUNT(*) AS total FROM \"{$table}\" WHERE \"{$column}\" IS NULL"; - $result = $this->selectField($sql, 'total'); - if ($result > 0) { - $this->rollbackTransaction(); - return -1; - } - } - - // Otherwise update the table. Note the reverse-sensed $state variable - $sql = "UPDATE pg_attribute SET attnotnull = " . (($state) ? 'false' : 'true') . " - WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '{$table}') - AND attname = '{$column}'"; - - $status = $this->execute($sql); - if ($status != 0) { - $this->rollbackTransaction(); - return -4; - } - - // Otherwise, close the transaction - return $this->endTransaction(); - } - - /** - * Renames a column in a table - * @param $table The table containing the column to be renamed - * @param $column The column to be renamed - * @param $newName The new name for the column - * @return 0 success - */ - function renameColumn($table, $column, $newName) { - $this->fieldClean($table); - $this->fieldClean($column); - $this->fieldClean($newName); - - $sql = "ALTER TABLE \"{$table}\" RENAME COLUMN \"{$column}\" TO \"{$newName}\""; - - return $this->execute($sql); - } - - /** - * Grabs a list of indicies in the database or table - * @param $table (optional) The name of a table to get the indicies for - */ - function &getIndicies($table = '') { - $this->clean($table); - - $sql = "SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) - FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i - WHERE c.oid = '16977' AND c.oid = i.indrelid AND i.indexrelid = c2.oid - ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname"; - - - if ($table != '') - $where = "WHERE relname='{$table}' AND "; - elseif (!$this->_showSystem) - $where = "WHERE relname NOT LIKE 'pg_%' AND "; - else $where = ''; - - $sql = "SELECT relname FROM pg_class {$where} relkind ='i' ORDER BY relname"; - - return $this->selectSet($sql); - } - - function &getIndex($idxname) { - $sql = "SELECT - ic.relname AS relname, - bc.relname AS tab_name, - ta.attname AS column_name, - i.indisunique AS unique_key, - i.indisprimary AS primary_key - FROM - pg_class bc, - pg_class ic, - pg_index i, - pg_attribute ta, - pg_attribute ia - WHERE - bc.oid = i.indrelid - AND ic.oid = i.indexrelid - AND ia.attrelid = i.indexrelid - AND ta.attrelid = bc.oid - AND ic.relname = '$idxname' - AND ta.attrelid = i.indrelid - AND ta.attnum = i.indkey[ia.attnum-1] - ORDER BY - relname, tab_name, column_name"; - - return $this->selectSet($sql); - } - - -/* - function setIndex() - function delIndex() - - - // DML Functions - - function doSelect() - function doDelete() - function doUpdate() -*/ - - // View functions - - /** - * Returns a list of all views in the database - * @return All views - */ - function getViews() { - if (!$this->_showSystem) - $where = "WHERE viewname NOT LIKE 'pg_%'"; - else $where = ''; - - $sql = "SELECT viewname, viewowner FROM pg_views {$where} ORDER BY viewname"; - - return $this->selectSet($sql); - } - - /** - * Returns all details for a particular view - * @param $view The name of the view to retrieve - * @return View info - */ - function getView($view) { - $this->clean($view); - - $sql = "SELECT viewname, viewowner, definition FROM pg_views WHERE viewname='$view'"; - - return $this->selectSet($sql); - } - - /** - * Creates a new view. - * @param $viewname The name of the view to create - * @param $definition The definition for the new view - * @return 0 success - */ - function createView($viewname, $definition) { - $this->clean($viewname); - // Note: $definition not cleaned - - $sql = "CREATE VIEW \"{$viewname}\" AS {$definition}"; - - return $this->execute($sql); - } - - /** - * Drops a view. - * @param $viewname The name of the view to drop - * @return 0 success - */ - function dropView($viewname) { - $this->clean($viewname); - - $sql = "DROP VIEW \"{$viewname}\""; - - return $this->execute($sql); - } - - /** - * Updates a view. Postgres doesn't have CREATE OR REPLACE view, - * so we do it with a drop and a recreate. - * @param $viewname The name fo the view to update - * @param $definition The new definition for the view - * @return 0 success - * @return -1 transaction error - * @return -2 drop view error - * @return -3 create view error - */ - function setView($viewname, $definition) { - $status = $this->beginTransaction(); - if ($status != 0) return -1; - - $status = $this->dropView($viewname); - if ($status != 0) { - $this->rollbackTransaction(); - return -2; - } - - $status = $this->createView($viewname, $definition); - if ($status != 0) { - $this->rollbackTransaction(); - return -3; - } - - $status = $this->endTransaction(); - return ($status == 0) ? 0 : -1; - } - - // Operator functions - - /** - * Returns a list of all operators in the database - * @return All operators - */ - function getOperators() { - if (!$this->_showSystem) - $where = "WHERE po.oid > '{$this->_lastSystemOID}'::oid"; - else $where = ''; - - $sql = " - SELECT - po.oid, - po.oprname, - (SELECT typname FROM pg_type pt WHERE pt.oid=po.oprleft) AS oprleftname, - (SELECT typname FROM pg_type pt WHERE pt.oid=po.oprright) AS oprrightname, - (SELECT typname FROM pg_type pt WHERE pt.oid=po.oprresult) AS resultname - FROM - pg_operator po - {$where} - ORDER BY - po.oprname, po.oid - "; - - return $this->selectSet($sql); - } - - - /** - * Creates a new operator - */ - - // User and group functions - - /** - * Returns all users in the database cluster - * @return All users - */ - function &getUsers() { - $sql = "SELECT usename, usesuper, usecreatedb, valuntil FROM pg_shadow ORDER BY usename"; - - return $this->selectSet($sql); - } - - /** - * Return information about a single user - * @param $username The username of the user to retrieve - * @return The user's data - */ - function &getUser($username) { - $this->clean($username); - - $sql = "SELECT usename, usesuper, usecreatedb, valuntil FROM pg_shadow WHERE usename='{$username}'"; - - return $this->selectSet($sql); - } - - /** - * Creates a new user - * @param $username The username of the user to create - * @param $password A password for the user - * @param $createdb boolean Whether or not the user can create databases - * @param $createuser boolean Whether or not the user can create other users - * @param $expiry string Format 'YYYY-MM-DD HH:MM:SS'. When the account expires. - * @param $group (array) The groups to create the user in - * @return 0 success - */ - function createUser($username, $password, $createdb, $createuser, $expiry, $groups) { - $this->clean($username); - // @@ THIS IS A PROBLEM FOR TRIMMING PASSWORD!!! - $this->clean($password); - $this->clean($expiry); - $this->arrayClean($groups); - - $sql = "CREATE USER \"{$username}\""; - if ($password != '') $sql .= " WITH PASSWORD '{$password}'"; - $sql .= ($createdb) ? ' CREATEDB' : ' NOCREATEDB'; - $sql .= ($createuser) ? ' CREATEUSER' : ' NOCREATEUSER'; - if (is_array($groups) && sizeof($groups) > 0) $sql .= " IN GROUP '" . join("', '", $groups) . "'"; - if ($expiry != '') $sql .= " VALID UNTIL '{$expiry}'"; - - return $this->execute($sql); - } - - /** - * Adjusts a user's info - * @param $username The username of the user to modify - * @param $password A new password for the user - * @param $createdb boolean Whether or not the user can create databases - * @param $createuser boolean Whether or not the user can create other users - * @param $expiry string Format 'YYYY-MM-DD HH:MM:SS'. When the account expires. - * @return 0 success - */ - function setUser($username, $password, $createdb, $createuser, $expiry) { - $this->clean($username); - $this->clean($password); - $this->clean($expiry); - - $sql = "ALTER USER \"{$username}\""; - if ($password != '') $sql .= " WITH PASSWORD '{$password}'"; - $sql .= ($createdb) ? ' CREATEDB' : ' NOCREATEDB'; - $sql .= ($createuser) ? ' CREATEUSER' : ' NOCREATEUSER'; - if ($expiry != '') $sql .= " VALID UNTIL '{$expiry}'"; - - return $this->execute($sql); - } - - /** - * Removes a user - * @param $username The username of the user to drop - * @return 0 success - */ - function dropUser($username) { - $this->clean($username); - - $sql = "DROP USER \"{$username}\""; - - return $this->execute($sql); - } - - // Capabilities - function hasTables() { return true; } - function hasViews() { return true; } - function hasSequences() { return true; } - function hasFunctions() { return true; } - function hasTriggers() { return true; } - function hasOperators() { return true; } - function hasTypes() { return true; } - function hasAggregates() { return true; } - function hasIndicies() { return true; } - function hasRules() { return true; } - function hasLanguages() { return true; } - -} - -?> + 'datname', 'dbcomment' => 'description'); + var $tbFields = array('tbname' => 'tablename', 'tbowner' => 'tableowner'); + var $vwFields = array('vwname' => 'viewname', 'vwowner' => 'viewowner', 'vwdef' => 'definition'); + var $uFields = array('uname' => 'usename', 'usuper' => 'usesuper', 'ucreatedb' => 'usecreatedb', 'uexpires' => 'valuntil'); + var $sqFields = array('seqname' => 'relname', 'seqowner' => 'usename', 'lastvalue' => 'last_value', 'incrementby' => 'increment_by', 'maxvalue' => 'max_value', 'minvalue'=> 'min_value', 'cachevalue' => 'cache_value', 'logcount' => 'log_cnt', 'iscycled' => 'is_cycled', 'iscalled' => 'is_called' ); + var $ixFields = array('idxname' => 'relname', 'idxdef' => 'pg_get_indexdef', 'uniquekey' => 'indisunique', 'primarykey' => 'indisprimary'); + var $tgFields = array('tgname' => 'tgname'); + + // Last oid assigned to a system object + var $_lastSystemOID = 18539; + var $_maxNameLen = 31; + + // Name of id column + var $id = 'oid'; + + function Postgres($host, $port, $database, $user, $password) { + $this->BaseDB('postgres7'); + + //$this->conn->host = $host + //$this->Port = $port; + $pghost = "$host:$port"; + + $this->conn->connect($pghost, $user, $password, $database); + } + + /** + * A function to check that the database functions are installed + * and running. + * @return True on success, false otherwise + */ + function isLoaded() { + return function_exists('pg_connect'); + } + + // Table functions + + /** + * Get the fields for uniquely identifying a row in a table + * @param $table The table for which to retrieve the identifier + * @return An array mapping attribute number to attribute name, empty for no identifiers + * @return -1 error + */ + function getRowIdentifier($table) { + $this->clean($table); + + $status = $this->beginTransaction(); + if ($status != 0) return -1; + + $sql = "SELECT indrelid, indkey FROM pg_index WHERE indisprimary AND indrelid=(SELECT oid FROM pg_class WHERE relname='{$table}')"; + $rs = $this->selectSet($sql); + + // If none, search for OID column + if ($rs->recordCount() == 0) { + $sql = "SELECT relhasoids FROM pg_class WHERE relname='{$table}'"; + $rs2 = $this->selectSet($sql); + if ($rs2->recordCount() == 0) { + $this->rollbackTransaction(); + return -1; + } + elseif ($rs2->f['relhasoids'] == 'f') { + $this->endTransaction(); + return array(); + } + else { + $this->endTransaction(); + return array(-2 => 'oid'); + } + } + // Otherwise select to find the names of the keys + else { + $in = str_replace(' ', ',', $rs->f['indkey']); + $sql = "SELECT attnum, attname FROM pg_attribute WHERE attrelid='{$rs->f['indrelid']}' AND attnum IN ({$in})"; + $rs2 = $this->selectSet($sql); + if ($rs2->recordCount() == 0) { + $this->rollbackTransaction(); + return -1; + } + else { + $temp = array(); + while (!$rs2->EOF) { + $temp[$rs2->f['attnum']] = $rs2->f['attname']; + $rs2->moveNext(); + } + $this->endTransaction(); + return $temp; + } + } + } + + /** + * Outputs the HTML code for a particular field + * @param $name The name to give the field + * @param $value The value of the field. Note this could be 'numeric(7,2)' sort of thing... + * @param $type The database type of the field + */ + function printField($name, $value, $type) { + switch ($type) { + case 'bool': + case 'boolean': + echo "\n"; + break; + case 'text': + case 'bytea': + echo "\n"; + break; + default: + echo "\n"; + break; + } + } + + /** + * Return all database available on the server + * @return A list of databases, sorted alphabetically + */ + function &getLanguages() { + $sql = ""; + return $this->selectSet($sql); + } + + /** + * Return all information about a particular database + * @param $database The name of the database to retrieve + * @return The database info + */ + function &getLanguage($database) { + $this->clean($database); + $sql = "SELECT * FROM pg_database WHERE datname='{$database}'"; + return $this->selectRow($sql); + } + + /** + * Creates a database + * @param $database The name of the database to create + * @return 0 success + */ + function createDatabase($database) { + $this->clean($database); + $sql = "CREATE DATABASE \"{$database}\""; + return $this->execute($sql); + } + + /** + * Drops a database + * @param $database The name of the database to drop + * @return 0 success + */ + function dropDatabase($database) { + $this->clean($database); + $sql = "DROP DATABASE \"{$database}\""; + return $this->execute($sql); + } + + // Table functions + + /** + * Return all tables in current database + * @return All tables, sorted alphabetically + */ + function &getTables() { + if (!$this->_showSystem) $where = "WHERE tablename NOT LIKE 'pg_%' "; + else $where = ''; + $sql = "SELECT tablename, tableowner FROM pg_tables {$where}ORDER BY tablename"; + return $this->selectSet($sql); + } + + /** + * Return all information relating to a table + * @param $table The name of the table + * @return Table information + */ + function &getTableByName($table) { + $this->clean($table); + $sql = "SELECT * FROM pg_class WHERE relname='{$table}'"; + return $this->selectRow($sql); + } + + /** + * Retrieve the attribute definition of a table + * @param $table The name of the table + * @param $field (optional) The name of a field to return + * @return All attributes in order + */ + function &getTableAttributes($table, $field = '') { + $this->clean($table); + $this->clean($field); + + if ($field == '') { + $sql = "SELECT + a.attname, t.typname as type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum + FROM + pg_class c, pg_attribute a, pg_type t + WHERE + c.relname = '{$table}' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid + ORDER BY a.attnum"; + } + else { + $sql = "SELECT + a.attname, t.typname as type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum + FROM + pg_class c, pg_attribute a, pg_type t + WHERE + c.relname = '{$table}' AND a.attname='{$column}' AND a.attrelid = c.oid AND a.atttypid = t.oid + ORDER BY a.attnum"; + } + + return $this->selectSet($sql); + } + + /** + * Drops a column from a table + * @param $table The table from which to drop a column + * @param $column The column to be dropped + * @param $behavior CASCADE or RESTRICT or empty + * @return 0 success + * @return -99 not implemented + */ + function dropColumn($table, $column, $behavior) { + return -99; + } + + /** + * Alters a column in a table + * @param $table The table in which the column resides + * @param $column The column to alter + * @param $name The new name for the column + * @param $notnull (boolean) True if not null, false otherwise + * @param $default The new default for the column + * @return 0 success + * @return -1 set not null error + * @return -2 set default error + * @return -3 rename column error + */ + function alterColumn($table, $column, $name, $notnull, $default) { + $this->beginTransaction(); + + // @@ NEED TO HANDLE "NESTED" TRANSACTION HERE + $status = $this->setColumnNull($table, $column, !$notnull); + if ($status != 0) { + $this->rollbackTransaction(); + return -1; + } + + $status = $this->setColumnDefault($table, $column, $default); + if ($status != 0) { + $this->rollbackTransaction(); + return -2; + } + + $status = $this->renameColumn($table, $column, $name); + if ($status != 0) { + $this->rollbackTransaction(); + return -3; + } + + return $this->endTransaction(); + } + + /** + * Creates a new table in the database + * @param $name The name of the table + * @param $fields The number of fields + * @param $field An array of field names + * @param $type An array of field types + * @param $length An array of field lengths + * @param $notnull An array of not null + * @param $default An array of default values + * @return 0 success + * @return -1 no fields supplied + */ + function createTable($name, $fields, $field, $type, $length, $notnull, $default) { + // @@ NOTE: $default field not being cleaned - how on earth DO we clean it?? + $this->fieldClean($name); + + $found = false; + $sql = "CREATE TABLE \"{$name}\" ("; + + for ($i = 0; $i < $fields; $i++) { + $this->fieldClean($field[$i]); + $this->clean($type[$i]); + $this->clean($length[$i]); + + // Skip blank columns - for user convenience + if ($field[$i] == '' || $type[$i] == '') continue; + + $sql .= "\"{$field[$i]}\" {$type[$i]}"; + if ($length[$i] != '') $sql .= "({$length[$i]})"; + if (isset($notnull[$i])) $sql .= " NOT NULL"; + if ($default[$i] != '') $sql .= " DEFAULT {$default[$i]}"; + if ($i != $fields - 1) $sql .= ", "; + + $found = true; + } + + if (!$found) return -1; + + $sql .= ")"; + + return $this->execute($sql); + } + + /** + * Removes a table from the database + * @param $table The table to drop + * @return 0 success + */ + function dropTable($table) { + $this->fieldClean($table); + + $sql = "DROP TABLE \"{$table}\""; + + return $this->execute($sql); + } + + /** + * Empties a table in the database + * @param $table The table to be emptied + * @return 0 success + */ + function emptyTable($table) { + $this->fieldClean($table); + + $sql = "DELETE FROM \"{$table}\""; + + return $this->execute($sql); + } + + /** + * Renames a table + * @param $table The table to be renamed + * @param $newName The new name for the table + * @return 0 success + */ + function renameTable($table, $newName) { + $this->fieldClean($table); + $this->fieldClean($newName); + + $sql = "ALTER TABLE \"{$table}\" RENAME TO \"{$newName}\""; + + // @@ How do you do this? + return $this->execute($sql); + } + + /** + * Returns a recordset of all columns in a table + * @param $table The name of a table + * @param $offset The offset into the table + * @param $limit The maximum number of records to return at once + * @return A recordset + */ + function &getTableRows($table) { + $this->fieldClean($table); + + return $this->selectTable("SELECT COUNT(*) FROM \"{$table}\"", $offset, $limit); + } + + /** + * Returns a recordset of all columns in a table + * @param $table The name of a table + * @param $offset The offset into the table + * @param $limit The maximum number of records to return at once + * @return A recordset + */ + function &browseTable($table, $offset = null, $limit = null) { + $this->fieldClean($table); + + return $this->selectTable("SELECT oid, * FROM \"{$table}\"", $offset, $limit); + } + + /** + * Returns a recordset of all columns in a table + * @param $table The name of a table + * @param $key The associative array holding the key to retrieve + * @return A recordset + */ + function &browseRow($table, $key) { + $this->fieldClean($table); + + $sql = "SELECT * FROM \"{$table}\" WHERE true"; + foreach ($key as $k => $v) { + $this->fieldClean($k); + $this->clean($v); + $sql .= " AND \"{$k}\"='{$v}'"; + } + + return $this->selectSet($sql); + } + + /** + * + */ + function &selectTable($sql, $offset, $limit) { + return $this->selectSet($sql, $offset, $limit); + } + + // Sequence functions + + /** + * Returns all sequences in the current database + * @return A recordset + */ + function &getSequences() { + if (!$this->_showSystem) $where = " AND relname NOT LIKE 'pg_%'"; + else $where = ''; + $sql = "SELECT c.relname, u.usename FROM pg_class c, pg_user u WHERE c.relowner=u.usesysid AND c.relkind = 'S'{$where} ORDER BY relname"; + return $this->selectSet( $sql ); + } + + /** + * Returns properties of a single sequence + * @return A recordset + */ + function &getSequence($sequence) { + if (!$this->_showSystem) $where = " AND relname NOT LIKE 'pg_%'"; + else $where = ''; + $sql = "SELECT sequence_name as relname,* FROM $sequence"; + return $this->selectSet( $sql ); + } + + /** + * Drops a given sequence + * @return 0 success + */ + function &dropSequence($sequence) { + $this->clean($sequence); + $sql = "DROP SEQUENCE {$sequence} "; + return $this->execute($sql); + } + + /** + * Creates a new sequence + * @return 0 success + */ + function &setSequence($sequence,$startval=1) { + $this->clean($sequence); + $sql = "CREATE SEQUENCE $seq_name START $startval"; + return $this->execute($sql); + } + + /** + * Modifies permissions on a given sequence + * @return 0 success + */ + function &setSequencePermissions($sequence) { + + } + + /** + * Resets a given sequence to 1 + * @return 0 success + */ + function &resetSequence($sequence) { + $this->clean($sequence); + $sql = "SELECT setval('$sequence',1)"; + return $this->execute($sql); + } + + + + + /** + * Adds a check constraint to a table + * @param $table The table to which to add the check + * @param $definition The definition of the check + * @param $name (optional) The name to give the check, otherwise default name is assigned + * @return 0 success + */ + function addCheckConstraint($table, $definition, $name = '') { + $this->fieldClean($table); + $this->fieldClean($name); + // @@ how the heck do you clean definition??? + + if ($name != '') + $sql = "ALTER TABLE \"{$table}\" ADD CONSTRAINT \"{$name}\" CHECK ({$definition})"; + else + $sql = "ALTER TABLE \"{$table}\" ADD CHECK ({$definition})"; + + // @@ How do you do this? + return $this->execute($sql); + } + + /** + * Drops a check constraint from a table + * @param $table The table from which to drop the check + * @param $name The name of the check to be dropped + * @return 0 success + * @return -2 transaction error + * @return -3 lock error + * @return -4 check drop error + */ + function dropCheckConstraint($table, $name) { + $this->clean($table); + $this->clean($name); + + // Begin transaction + $status = $this->beginTransaction(); + if ($status != 0) return -2; + + // Properly lock the table + $sql = "LOCK TABLE \"{$table}\" IN ACCESS EXCLUSIVE MODE"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -3; + } + + // Delete the check constraint + $sql = "DELETE FROM pg_relcheck WHERE rcrelid=(SELECT oid FROM pg_class WHERE relname='{$table}') AND rcname='{$name}'"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -4; + } + + // Update the pg_class catalog to reflect the new number of checks + $sql = "UPDATE pg_class SET relchecks=(SELECT COUNT(*) FROM pg_relcheck WHERE + rcrelid=(SELECT oid FROM pg_class WHERE relname='{$table}')) + WHERE relname='{$table}'"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -4; + } + + // Otherwise, close the transaction + return $this->endTransaction(); + } + + /** + * Adds a unique constraint to a table + * @param $table The table to which to add the unique + * @param $fields (array) An array of fields over which to add the unique + * @param $name (optional) The name to give the unique, otherwise default name is assigned + * @return 0 success + */ + function addUniqueConstraint($table, $fields, $name = '') { + $this->fieldClean($table); + $this->arrayClean($fields); + $this->fieldClean($name); + + if ($name != '') + $sql = "CREATE UNIQUE INDEX \"{$name}\" ON \"{$table}\"(\"" . join('","', $fields) . "\")"; + else return -99; // Not supported + + return $this->execute($sql); + } + + /** + * Drops a unique constraint from a table + * @param $table The table from which to drop the unique + * @param $name The name of the unique + * @return 0 success + */ + function dropUniqueConstraint($table, $name) { + $this->fieldClean($name); + + $sql = "DROP INDEX \"{$name}\""; + + return $this->execute($sql); + } + + /** + * Adds a primary key constraint to a table + * @param $table The table to which to add the primery key + * @param $fields (array) An array of fields over which to add the primary key + * @param $name (optional) The name to give the key, otherwise default name is assigned + * @return 0 success + */ + function addPrimaryKeyConstraint($table, $fields, $name = '') { + // This function can be faked with a unique index and a catalog twiddle, however + // how do we ensure that it's only used on NOT NULL fields? + return -99; // Not supported. + } + + /** + * Drops a primary key constraint from a table + * @param $table The table from which to drop the primary key + * @param $name The name of the primary key + * @return 0 success + */ + function dropPrimaryKeyConstraint($table, $name) { + $this->fieldClean($name); + + $sql = "DROP INDEX \"{$name}\""; + + return $this->execute($sql); + } + + /** + * Changes the owner of a table + * @param $table The table whose owner is to change + * @param $owner The new owner (username) of the table + * @return 0 success + */ + function setOwnerOfTable($table, $owner) { + $this->fieldClean($table); + $this->fieldClean($owner); + + $sql = "ALTER TABLE \"{$table}\" OWNER TO \"{$owner}\""; + + return $this->execute($sql); + } + + // Column Functions + + /** + * Add a new column to a table + * @param $table The table to add to + * @param $column The name of the new column + * @param $type The type of the column + * @param $size (optional) The optional size of the column (ie. 30 for varchar(30)) + * @return 0 success + */ + function addColumnToTable($table, $column, $type, $size = '') { + $this->clean($table); + $this->clean($column); + $this->clean($type); + $this->clean($size); + // @@ How the heck do you properly clean type and size? + + if ($size == '') + $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" {$type}"; + else + $sql = "ALTER TABLE \"{$table}\" ADD COLUMN \"{$column}\" {$type}({$size})"; + + // @@ How do you do this? + return $this->execute($sql); + } + + /** + * Drops a column from a table + * @param $table The table from which to drop + * @param $column The column name to drop + * @return 0 success + */ + function dropColumnFromTable($table, $column) { + return -99; // Not implemented + } + + /** + * Sets default value of a column + * @param $table The table from which to drop + * @param $column The column name to set + * @param $default The new default value + * @return 0 success + */ + function setColumnDefault($table, $column, $default) { + $this->fieldClean($table); + $this->fieldClean($column); + // @@ How the heck do you clean default clause? + + $sql = "ALTER TABLE \"{$table}\" ALTER COLUMN \"{$column}\" SET DEFAULT {$default}"; + + return $this->execute($sql); + } + + /** + * Drops default value of a column + * @param $table The table from which to drop + * @param $column The column name to drop default + * @return 0 success + */ + function dropColumnDefault($table, $column) { + $this->clean($table); + $this->clean($column); + + $sql = "ALTER TABLE \"{$table}\" ALTER COLUMN \"{$column}\" DROP DEFAULT"; + + // @@ How do you do this? + return $this->execute($sql); + } + + /** + * Sets whether or not a column can contain NULLs + * @param $table The table that contains the column + * @param $column The column to alter + * @param $state True to set null, false to set not null + * @return 0 success + * @return -1 attempt to set not null, but column contains nulls + * @return -2 transaction error + * @return -3 lock error + * @return -4 update error + */ + function setColumnNull($table, $column, $state) { + $this->clean($table); + $this->clean($column); + + // Begin transaction + $status = $this->beginTransaction(); + if ($status != 0) return -2; + + // Properly lock the table + $sql = "LOCK TABLE \"{$table}\" IN ACCESS EXCLUSIVE MODE"; + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -3; + } + + // Check for existing nulls + if (!$state) { + $sql = "SELECT COUNT(*) AS total FROM \"{$table}\" WHERE \"{$column}\" IS NULL"; + $result = $this->selectField($sql, 'total'); + if ($result > 0) { + $this->rollbackTransaction(); + return -1; + } + } + + // Otherwise update the table. Note the reverse-sensed $state variable + $sql = "UPDATE pg_attribute SET attnotnull = " . (($state) ? 'false' : 'true') . " + WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '{$table}') + AND attname = '{$column}'"; + + $status = $this->execute($sql); + if ($status != 0) { + $this->rollbackTransaction(); + return -4; + } + + // Otherwise, close the transaction + return $this->endTransaction(); + } + + /** + * Renames a column in a table + * @param $table The table containing the column to be renamed + * @param $column The column to be renamed + * @param $newName The new name for the column + * @return 0 success + */ + function renameColumn($table, $column, $newName) { + $this->fieldClean($table); + $this->fieldClean($column); + $this->fieldClean($newName); + + $sql = "ALTER TABLE \"{$table}\" RENAME COLUMN \"{$column}\" TO \"{$newName}\""; + + return $this->execute($sql); + } + + /** + * Grabs a list of indicies in the database or table + * @param $table (optional) The name of a table to get the indicies for + */ + function &getIndicies($table = '') { + $this->clean($table); + + $sql = "SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) + FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i + WHERE c.oid = '16977' AND c.oid = i.indrelid AND i.indexrelid = c2.oid + ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname"; + + + if ($table != '') + $where = "WHERE relname='{$table}' AND "; + elseif (!$this->_showSystem) + $where = "WHERE relname NOT LIKE 'pg_%' AND "; + else $where = ''; + + $sql = "SELECT relname FROM pg_class {$where} relkind ='i' ORDER BY relname"; + + return $this->selectSet($sql); + } + + function &getIndex($idxname) { + $sql = "SELECT + ic.relname AS relname, + bc.relname AS tab_name, + ta.attname AS column_name, + i.indisunique AS unique_key, + i.indisprimary AS primary_key + FROM + pg_class bc, + pg_class ic, + pg_index i, + pg_attribute ta, + pg_attribute ia + WHERE + bc.oid = i.indrelid + AND ic.oid = i.indexrelid + AND ia.attrelid = i.indexrelid + AND ta.attrelid = bc.oid + AND ic.relname = '$idxname' + AND ta.attrelid = i.indrelid + AND ta.attnum = i.indkey[ia.attnum-1] + ORDER BY + relname, tab_name, column_name"; + + return $this->selectSet($sql); + } + + /** + * Creates a database + * @param $database The name of the database to create + * @return 0 success + */ + function createIndex($name, $table, $columns) { + $this->fieldClean($name); + $this->fieldClean($table); + $this->arrayClean($columns); + + $sql = "CREATE INDEX \"{$name}\" ON \"{$table}\"(\"" . + implode('","', $columns) . "\")"; + + return $this->execute($sql); + } + + /** + * Removes an index from the database + * @param $index The index to drop + * @return 0 success + */ + function dropIndex($index) { + $this->fieldClean($index); + + $sql = "DROP INDEX \"{$index}\""; + + return $this->execute($sql); + } + + // View functions + + /** + * Returns a list of all views in the database + * @return All views + */ + function getViews() { + if (!$this->_showSystem) + $where = "WHERE viewname NOT LIKE 'pg_%'"; + else $where = ''; + + $sql = "SELECT viewname, viewowner FROM pg_views {$where} ORDER BY viewname"; + + return $this->selectSet($sql); + } + + /** + * Returns all details for a particular view + * @param $view The name of the view to retrieve + * @return View info + */ + function getView($view) { + $this->clean($view); + + $sql = "SELECT viewname, viewowner, definition FROM pg_views WHERE viewname='$view'"; + + return $this->selectSet($sql); + } + + /** + * Creates a new view. + * @param $viewname The name of the view to create + * @param $definition The definition for the new view + * @return 0 success + */ + function createView($viewname, $definition) { + $this->clean($viewname); + // Note: $definition not cleaned + + $sql = "CREATE VIEW \"{$viewname}\" AS {$definition}"; + + return $this->execute($sql); + } + + /** + * Drops a view. + * @param $viewname The name of the view to drop + * @return 0 success + */ + function dropView($viewname) { + $this->clean($viewname); + + $sql = "DROP VIEW \"{$viewname}\""; + + return $this->execute($sql); + } + + /** + * Updates a view. Postgres doesn't have CREATE OR REPLACE view, + * so we do it with a drop and a recreate. + * @param $viewname The name fo the view to update + * @param $definition The new definition for the view + * @return 0 success + * @return -1 transaction error + * @return -2 drop view error + * @return -3 create view error + */ + function setView($viewname, $definition) { + $status = $this->beginTransaction(); + if ($status != 0) return -1; + + $status = $this->dropView($viewname); + if ($status != 0) { + $this->rollbackTransaction(); + return -2; + } + + $status = $this->createView($viewname, $definition); + if ($status != 0) { + $this->rollbackTransaction(); + return -3; + } + + $status = $this->endTransaction(); + return ($status == 0) ? 0 : -1; + } + + // Operator functions + + /** + * Returns a list of all operators in the database + * @return All operators + */ + function getOperators() { + if (!$this->_showSystem) + $where = "WHERE po.oid > '{$this->_lastSystemOID}'::oid"; + else $where = ''; + + $sql = " + SELECT + po.oid, + po.oprname, + (SELECT typname FROM pg_type pt WHERE pt.oid=po.oprleft) AS oprleftname, + (SELECT typname FROM pg_type pt WHERE pt.oid=po.oprright) AS oprrightname, + (SELECT typname FROM pg_type pt WHERE pt.oid=po.oprresult) AS resultname + FROM + pg_operator po + {$where} + ORDER BY + po.oprname, po.oid + "; + + return $this->selectSet($sql); + } + + + /** + * Creates a new operator + */ + + // User and group functions + + /** + * Returns all users in the database cluster + * @return All users + */ + function &getUsers() { + $sql = "SELECT usename, usesuper, usecreatedb, valuntil FROM pg_shadow ORDER BY usename"; + + return $this->selectSet($sql); + } + + /** + * Return information about a single user + * @param $username The username of the user to retrieve + * @return The user's data + */ + function &getUser($username) { + $this->clean($username); + + $sql = "SELECT usename, usesuper, usecreatedb, valuntil FROM pg_shadow WHERE usename='{$username}'"; + + return $this->selectSet($sql); + } + + /** + * Creates a new user + * @param $username The username of the user to create + * @param $password A password for the user + * @param $createdb boolean Whether or not the user can create databases + * @param $createuser boolean Whether or not the user can create other users + * @param $expiry string Format 'YYYY-MM-DD HH:MM:SS'. When the account expires. + * @param $group (array) The groups to create the user in + * @return 0 success + */ + function createUser($username, $password, $createdb, $createuser, $expiry, $groups) { + $this->clean($username); + // @@ THIS IS A PROBLEM FOR TRIMMING PASSWORD!!! + $this->clean($password); + $this->clean($expiry); + $this->arrayClean($groups); + + $sql = "CREATE USER \"{$username}\""; + if ($password != '') $sql .= " WITH PASSWORD '{$password}'"; + $sql .= ($createdb) ? ' CREATEDB' : ' NOCREATEDB'; + $sql .= ($createuser) ? ' CREATEUSER' : ' NOCREATEUSER'; + if (is_array($groups) && sizeof($groups) > 0) $sql .= " IN GROUP '" . join("', '", $groups) . "'"; + if ($expiry != '') $sql .= " VALID UNTIL '{$expiry}'"; + + return $this->execute($sql); + } + + /** + * Adjusts a user's info + * @param $username The username of the user to modify + * @param $password A new password for the user + * @param $createdb boolean Whether or not the user can create databases + * @param $createuser boolean Whether or not the user can create other users + * @param $expiry string Format 'YYYY-MM-DD HH:MM:SS'. When the account expires. + * @return 0 success + */ + function setUser($username, $password, $createdb, $createuser, $expiry) { + $this->clean($username); + $this->clean($password); + $this->clean($expiry); + + $sql = "ALTER USER \"{$username}\""; + if ($password != '') $sql .= " WITH PASSWORD '{$password}'"; + $sql .= ($createdb) ? ' CREATEDB' : ' NOCREATEDB'; + $sql .= ($createuser) ? ' CREATEUSER' : ' NOCREATEUSER'; + if ($expiry != '') $sql .= " VALID UNTIL '{$expiry}'"; + + return $this->execute($sql); + } + + /** + * Removes a user + * @param $username The username of the user to drop + * @return 0 success + */ + function dropUser($username) { + $this->clean($username); + + $sql = "DROP USER \"{$username}\""; + + return $this->execute($sql); + } + + // Capabilities + function hasTables() { return true; } + function hasViews() { return true; } + function hasSequences() { return true; } + function hasFunctions() { return true; } + function hasTriggers() { return true; } + function hasOperators() { return true; } + function hasTypes() { return true; } + function hasAggregates() { return true; } + function hasIndicies() { return true; } + function hasRules() { return true; } + function hasLanguages() { return true; } + +} + +?> diff --git a/public_html/tblproperties.php b/public_html/tblproperties.php index 93467b0f..2dc62dcc 100644 --- a/public_html/tblproperties.php +++ b/public_html/tblproperties.php @@ -3,7 +3,7 @@ /** * List tables in a database * - * $Id: tblproperties.php,v 1.8 2002/11/18 05:49:55 chriskl Exp $ + * $Id: tblproperties.php,v 1.9 2002/12/14 10:56:26 chriskl Exp $ */ // Include application functions @@ -19,14 +19,15 @@ doNav(); echo "

", htmlspecialchars($_REQUEST['database']), ": ", htmlspecialchars($_REQUEST['table']), ": {$strTriggers}

\n"; - + $misc->printMsg($msg); + $triggers = &$localData->getTriggers($_REQUEST['table']); - + if ($triggers->recordCount() > 0) { echo "\n"; echo "\n"; $i = 0; - + while (!$triggers->EOF) { $id = ( ($i % 2 ) == 0 ? '1' : '2' ); echo ""; @@ -45,19 +46,103 @@ } else echo "

{$strNoTriggers}

\n"; + + echo "

{$strCreateTrigger}

\n"; + } + + /** + * Displays a screen where they can enter a new index + */ + function doCreateIndex($msg = '') { + global $data, $localData, $misc; + global $PHP_SELF, $strName, $strDefinition; + + if (!isset($_POST['formIndex'])) $_POST['formIndex'] = ''; + if (!isset($_POST['formCols'])) $_POST['formCols'] = ''; + + echo "

", htmlspecialchars($_REQUEST['database']), ": Indexes: Create Index

\n"; + $misc->printMsg($msg); - echo "

{$strCreateTrigger}

\n"; + echo "\n"; + echo "
{$strName}{$strActions}
", htmlspecialchars( $triggers->f[$data->tgFields['tgname']]), "
\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "
{$strName}
_maxNameLen} maxlength={$data->_maxNameLen} value=\"", + htmlspecialchars($_POST['formIndex']), "\">
Columns
_maxNameLen} value=\"", + htmlspecialchars($_POST['formCols']), "\"> (eg. col1, col2)
\n"; + echo "

\n"; + echo "\n"; + echo "\n"; + echo "

\n"; + echo "\n"; + + echo "

Show All Indexes

\n"; + } + + /** + * Actually creates the new index in the database + * @@ Note: this function can't handle columns with commas in them + */ + function doSaveCreateIndex() { + global $localData; + + // Check that they've given a name and at least one column + if ($_POST['formIndex'] == '') doCreateIndex("Index needs a name."); + elseif ($_POST['formCols'] == '') doCreate("Index needs at least one column."); + else { + $status = $localData->createIndex($_POST['formIndex'], $_POST['table'], explode(',', $_POST['formCols'])); + if ($status == 0) + doIndicies('Index created.'); + else + doCreateIndex('Index creation failed.'); + } + } + + /** + * Show confirmation of drop index and perform actual drop + */ + function doDropIndex($confirm) { + global $localData, $database; + global $PHP_SELF; + + if ($confirm) { + echo "

", htmlspecialchars($_REQUEST['database']), ": Tables: ", + htmlspecialchars($_REQUEST['table']), ": " , htmlspecialchars($_REQUEST['index']), ": Drop

\n"; + + echo "

Are you sure you want to drop the index \"", htmlspecialchars($_REQUEST['index']), + "\" from table \"", htmlspecialchars($_REQUEST['table']), "\"?

\n"; + + echo "
\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo "\n"; + echo " \n"; + echo "
\n"; + } + else { + $status = $localData->dropIndex($_POST['index'], 'RESTRICT'); + if ($status == 0) + doIndicies('Index dropped.'); + else + doIndicies('Index drop failed.'); + } + } function doIndicies($msg = '') { - global $data, $localData, $misc; + global $data, $localData, $misc; global $PHP_SELF; global $strNoIndicies, $strIndicies, $strActions, $strName; - + doNav(); echo "

", htmlspecialchars($_REQUEST['database']), ": ", htmlspecialchars($_REQUEST['table']), ": {$strIndicies}

\n"; - + $misc->printMsg($msg); + $indexes = &$localData->getIndexes($_REQUEST['table']); if ($indexes->recordCount() > 0) { @@ -70,20 +155,21 @@ echo "", htmlspecialchars( $indexes->f[$data->ixFields['idxname']]), ""; echo "", htmlspecialchars( $indexes->f[$data->ixFields['idxdef']]), ""; echo ""; - echo "Drop\n"; + echo "Drop\n"; echo ""; - echo "Privileges\n"; + echo "Privileges\n"; $indexes->movenext(); $i++; } - + echo "\n"; } else echo "

{$strNoIndicies}

\n"; - echo "

Create Index

\n"; } @@ -151,7 +237,7 @@ if ($column->f['attnotnull']) $_REQUEST['notnull'] = 'YES'; } - echo "_maxNameLen} maxlength={$data->_maxNameLen} value=\"", + echo "_maxNameLen} maxlength={$data->_maxNameLen} value=\"", htmlspecialchars($_REQUEST['field']), "\">"; echo "", htmlspecialchars($column->f['type']), ""; echo "\n"; @@ -264,33 +350,6 @@ } echo "\n"; echo "
\n"; - - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - - $keys = &$localData->getTableKeys($_REQUEST['table']); - - if ($keys->recordCount() > 0) { - - $keys->f['unique_key'] = $localData->phpBool($keys->f['unique_key']); - $keys->f['primary_key'] = $localData->phpBool($keys->f['primary_key']); - - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "
$strKeyName$strUnique$strPrimary$strField$strAction
", htmlspecialchars($keys->f['index_name']), "", ($keys->f['unique_key'] ? 'Yes' : 'strNo'), "", ($keys->f['primary_key'] ? 'Yes' : 'strNo'), "", htmlspecialchars($keys->f['column_name']), "$strDrop
\n"; - } else { - echo 'No Keys Found'; - } echo <<Insert\n"; echo "
  • Drop
    • \n"; - -echo <<Privileges -
  • -
    - Add new field: - - - - - - -
    -
  • Insert textfiles into table -
  • -EOF; - - } else { echo "

    {$strNoTable}

    \n"; } - - echo "

    {$strShowAllTables}

    \n"; } function doNav() { global $PHP_SELF; - + $vars = 'database=' . urlencode($_REQUEST['database']) . '&table=' . urlencode($_REQUEST['table']); echo "\n"; @@ -358,6 +395,19 @@ EOF; case 'triggers': doTriggers(); break; + case 'save_create_index': + doSaveCreateIndex(); + break; + case 'create_index': + doCreateIndex(); + break; + case 'drop_index': + if ($_POST['choice'] == 'Yes') doDropIndex(false); + else doIndicies(); + break; + case 'confirm_drop_index': + doDropIndex(true); + break; case 'indicies': doIndicies(); break; @@ -373,7 +423,7 @@ EOF; break; case 'confirm_drop': doDrop(true); - break; + break; default: doDefault(); break; -- 2.39.5