Viewing file:  Postgres72.php (19.44 KB) -rw-rw-r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
/**
* A class that implements the DB interface for Postgres
* Note: This class uses ADODB and returns RecordSets.
*
* $Id: Postgres72.php,v 1.87 2006/12/28 05:03:41 xzilla Exp $
*/
include_once('./classes/database/Postgres71.php');
class Postgres72 extends Postgres71 {
var $major_version = 7.2;
// Set the maximum built-in ID.
var $_lastSystemOID = 16554;
// List of all legal privileges that can be applied to different types
// of objects.
var $privlist = array(
'table' => array('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'RULE', 'REFERENCES', 'TRIGGER', 'ALL PRIVILEGES'),
'view' => array('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'RULE', 'REFERENCES', 'TRIGGER', 'ALL PRIVILEGES'),
'sequence' => array('SELECT', 'UPDATE', 'ALL PRIVILEGES')
);
// Extra "magic" types. BIGSERIAL was added in PostgreSQL 7.2.
var $extraTypes = array('SERIAL', 'BIGSERIAL');
/**
* Constructor
* @param $conn The database connection
*/
function Postgres72($conn) {
$this->Postgres71($conn);
// Correct the error in the encoding tables, that was
// fixed in PostgreSQL 7.2
$this->codemap['LATIN5'] = 'ISO-8859-9';
}
// Help functions
function getHelpPages() {
include_once('./help/PostgresDoc72.php');
return $this->help_page;
}
// User functions
/**
* Helper function that computes encypted PostgreSQL passwords
* @param $username The username
* @param $password The password
*/
function _encryptPassword($username, $password) {
return 'md5' . md5($password . $username);
}
/**
* Changes a user's password
* @param $username The username
* @param $password The new password
* @return 0 success
*/
function changePassword($username, $password) {
$enc = $this->_encryptPassword($username, $password);
$this->fieldClean($username);
$this->clean($enc);
$sql = "ALTER USER \"{$username}\" WITH ENCRYPTED PASSWORD '{$enc}'";
return $this->execute($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'. '' means never expire
* @param $group (array) The groups to create the user in
* @return 0 success
*/
function createUser($username, $password, $createdb, $createuser, $expiry, $groups) {
$enc = $this->_encryptPassword($username, $password);
$this->fieldClean($username);
$this->clean($enc);
$this->clean($expiry);
$this->fieldArrayClean($groups);
$sql = "CREATE USER \"{$username}\"";
if ($password != '') $sql .= " WITH ENCRYPTED PASSWORD '{$enc}'";
$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}'";
else $sql .= " VALID UNTIL 'infinity'";
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'. '' means never expire.
* @return 0 success
*/
function setUser($username, $password, $createdb, $createuser, $expiry) {
$enc = $this->_encryptPassword($username, $password);
$this->fieldClean($username);
$this->clean($enc);
$this->clean($expiry);
$sql = "ALTER USER \"{$username}\"";
if ($password != '') $sql .= " WITH ENCRYPTED PASSWORD '{$enc}'";
$sql .= ($createdb) ? ' CREATEDB' : ' NOCREATEDB';
$sql .= ($createuser) ? ' CREATEUSER' : ' NOCREATEUSER';
if ($expiry != '') $sql .= " VALID UNTIL '{$expiry}'";
else $sql .= " VALID UNTIL 'infinity'";
return $this->execute($sql);
}
/**
* Returns all available process information.
* @param $database (optional) Find only connections to specified database
* @return A recordset
*/
function getProcesses($database = null) {
if ($database === null)
$sql = "SELECT * FROM pg_stat_activity ORDER BY datname, usename, procpid";
else {
$this->clean($database);
$sql = "SELECT * FROM pg_stat_activity WHERE datname='{$database}' ORDER BY usename, procpid";
}
return $this->selectSet($sql);
}
// Table functions
/**
* Returns the SQL for changing the current user
* @param $user The user to change to
* @return The SQL
*/
function getChangeUserSQL($user) {
$this->clean($user);
return "SET SESSION AUTHORIZATION '{$user}';";
}
/**
* Checks to see whether or not a table has a unique id column
* @param $table The table name
* @return True if it has a unique id, false otherwise
* @return -99 error
*/
function hasObjectID($table) {
$this->clean($table);
$sql = "SELECT relhasoids FROM pg_class WHERE relname='{$table}'";
$rs = $this->selectSet($sql);
if ($rs->recordCount() != 1) return -99;
else {
$rs->f['relhasoids'] = $this->phpBool($rs->f['relhasoids']);
return $rs->f['relhasoids'];
}
}
/**
* Returns table information
* @param $table The name of the table
* @return A recordset
*/
function getTable($table) {
$this->clean($table);
$sql = "SELECT pc.relname,
pg_get_userbyid(pc.relowner) AS relowner,
(SELECT description FROM pg_description pd
WHERE pc.oid=pd.objoid AND objsubid = 0) AS relcomment
FROM pg_class pc
WHERE pc.relname='{$table}'";
return $this->selectSet($sql);
}
/**
* Return all tables in current database
* @param $all True to fetch all tables, false for just in current schema
* @return All tables, sorted alphabetically
*/
function getTables($all = false) {
global $conf;
if (!$conf['show_system'] || $all) $where = "AND c.relname NOT LIKE 'pg\\\\_%' ";
else $where = '';
$sql = "SELECT NULL AS nspname, c.relname,
(SELECT usename FROM pg_user u WHERE u.usesysid=c.relowner) AS relowner,
(SELECT description FROM pg_description pd WHERE c.oid=pd.objoid AND objsubid = 0) AS relcomment,
reltuples::integer
FROM pg_class c WHERE c.relkind='r' {$where}ORDER BY relname";
return $this->selectSet($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,
format_type(a.atttypid, a.atttypmod) as type, a.atttypmod,
a.attnotnull, a.atthasdef, adef.adsrc,
-1 AS attstattarget, a.attstorage, t.typstorage, false AS attisserial,
description as comment
FROM
pg_attribute a LEFT JOIN pg_attrdef adef
ON a.attrelid=adef.adrelid AND a.attnum=adef.adnum
LEFT JOIN pg_type t ON a.atttypid=t.oid
LEFT JOIN pg_description d ON (a.attrelid = d.objoid AND a.attnum = d.objsubid)
WHERE
a.attrelid = (SELECT oid FROM pg_class WHERE relname='{$table}')
AND a.attnum > 0
ORDER BY a.attnum";
}
else {
$sql = "
SELECT
a.attname,
format_type(a.atttypid, a.atttypmod) as type,
format_type(a.atttypid, NULL) as base_type,
a.atttypmod,
a.attnotnull, a.atthasdef, adef.adsrc,
-1 AS attstattarget, a.attstorage, t.typstorage,
description as comment
FROM
pg_attribute a LEFT JOIN pg_attrdef adef
ON a.attrelid=adef.adrelid AND a.attnum=adef.adnum
LEFT JOIN pg_type t ON a.atttypid=t.oid
LEFT JOIN pg_description d ON (a.attrelid = d.objoid AND a.attnum = d.objsubid)
WHERE
a.attrelid = (SELECT oid FROM pg_class WHERE relname='{$table}')
AND a.attname = '{$field}'";
}
return $this->selectSet($sql);
}
// View functions
/**
* Returns a list of all views in the database
* @return All views
*/
function getViews() {
global $conf;
if (!$conf['show_system'])
$where = " WHERE viewname NOT LIKE 'pg\\\\_%'";
else
$where = '';
$sql = "SELECT viewname AS relname, viewowner AS relowner, definition AS vwdefinition,
(SELECT description FROM pg_description pd, pg_class pc
WHERE pc.oid=pd.objoid AND pc.relname=v.viewname AND pd.objsubid = 0) AS relcomment
FROM pg_views v
{$where}
ORDER BY relname";
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 AS relname, viewowner AS relowner, definition AS vwdefinition,
(SELECT description FROM pg_description pd, pg_class pc
WHERE pc.oid=pd.objoid AND pc.relname=v.viewname AND pd.objsubid = 0) AS relcomment
FROM pg_views v
WHERE viewname='{$view}'";
return $this->selectSet($sql);
}
// Constraint functions
/**
* Removes a constraint from a relation
* @param $constraint The constraint to drop
* @param $relation The relation from which to drop
* @param $type The type of constraint (c, f, u or p)
* @param $cascade True to cascade drop, false to restrict
* @return 0 success
* @return -99 dropping foreign keys not supported
*/
function dropConstraint($constraint, $relation, $type, $cascade) {
$this->fieldClean($constraint);
$this->fieldClean($relation);
switch ($type) {
case 'c':
// CHECK constraint
$sql = "ALTER TABLE \"{$relation}\" DROP CONSTRAINT \"{$constraint}\" RESTRICT";
return $this->execute($sql);
break;
case 'p':
case 'u':
// PRIMARY KEY or UNIQUE constraint
return $this->dropIndex($constraint, $cascade);
break;
case 'f':
// FOREIGN KEY constraint
return -99;
}
}
/**
* Adds a unique constraint to a table
* @param $table The table to which to add the unique key
* @param $fields (array) An array of fields over which to add the unique key
* @param $name (optional) The name to give the key, otherwise default name is assigned
* @param $tablespace (optional) The tablespace for the schema, '' indicates default.
* @return 0 success
* @return -1 no fields given
*/
function addUniqueKey($table, $fields, $name = '', $tablespace = '') {
if (!is_array($fields) || sizeof($fields) == 0) return -1;
$this->fieldClean($table);
$this->fieldArrayClean($fields);
$this->fieldClean($name);
$this->fieldClean($tablespace);
$sql = "ALTER TABLE \"{$table}\" ADD ";
if ($name != '') $sql .= "CONSTRAINT \"{$name}\" ";
$sql .= "UNIQUE (\"" . join('","', $fields) . "\")";
if ($tablespace != '' && $this->hasTablespaces())
$sql .= " USING INDEX TABLESPACE \"{$tablespace}\"";
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
* @param $tablespace (optional) The tablespace for the schema, '' indicates default.
* @return 0 success
* @return -1 no fields given
*/
function addPrimaryKey($table, $fields, $name = '', $tablespace = '') {
if (!is_array($fields) || sizeof($fields) == 0) return -1;
$this->fieldClean($table);
$this->fieldArrayClean($fields);
$this->fieldClean($name);
$this->fieldClean($tablespace);
$sql = "ALTER TABLE \"{$table}\" ADD ";
if ($name != '') $sql .= "CONSTRAINT \"{$name}\" ";
$sql .= "PRIMARY KEY (\"" . join('","', $fields) . "\")";
if ($tablespace != '' && $this->hasTablespaces())
$sql .= " USING INDEX TABLESPACE \"{$tablespace}\"";
return $this->execute($sql);
}
// Function functions
/**
* Returns a list of all functions in the database
* @param $all If true, will find all available functions, if false just userland ones
* @return All functions
*/
function getFunctions($all = false) {
if ($all)
$where = '';
else
$where = "AND p.oid > '{$this->_lastSystemOID}'";
$sql = "SELECT
p.oid AS prooid,
p.proname,
false AS proretset,
format_type(p.prorettype, NULL) AS proresult,
oidvectortypes(p.proargtypes) AS proarguments,
pl.lanname AS prolanguage,
(SELECT description FROM pg_description pd WHERE p.oid=pd.objoid) AS procomment,
p.proname || ' (' || oidvectortypes(p.proargtypes) || ')' AS proproto,
format_type(p.prorettype, NULL) AS proreturns
FROM
pg_proc p, pg_language pl
WHERE
p.prolang = pl.oid AND
(pronargs = 0 OR oidvectortypes(p.proargtypes) <> '')
{$where}
ORDER BY
p.proname, proresult
";
return $this->selectSet($sql);
}
/**
* Updates (replaces) a function.
* @param $function_oid The OID of the function
* @param $funcname The name of the function to create
* @param $newname The new name for the function
* @param $args The array of argument types
* @param $returns The return type
* @param $definition The definition for the new function
* @param $language The language the function is written for
* @param $flags An array of optional flags
* @param $setof True if returns a set, false otherwise
* @param $comment The comment on the function
* @return 0 success
* @return -1 transaction error
* @return -2 drop function error
* @return -3 create function error
* @return -4 comment error
*/
function setFunction($function_oid, $funcname, $newname, $args, $returns, $definition, $language, $flags, $setof, $comment) {
// Begin a transaction
$status = $this->beginTransaction();
if ($status != 0) {
$this->rollbackTransaction();
return -1;
}
// Replace the existing function
if ($funcname != $newname) {
$status = $this->dropFunction($function_oid, false);
if ($status != 0) {
$this->rollbackTransaction();
return -2;
}
$status = $this->createFunction($newname, $args, $returns, $definition, $language, $flags, $setof, false);
if ($status != 0) {
$this->rollbackTransaction();
return -3;
}
} else {
$status = $this->createFunction($funcname, $args, $returns, $definition, $language, $flags, $setof, true);
if ($status != 0) {
$this->rollbackTransaction();
return -3;
}
}
// Comment on the function
$this->fieldClean($newname);
$this->clean($comment);
$status = $this->setComment('FUNCTION', "\"{$newname}\"({$args})", null, $comment);
if ($status != 0) {
$this->rollbackTransaction();
return -4;
}
return $this->endTransaction();
}
// Type functions
/**
* Returns a list of all types in the database
* @param $all If true, will find all available functions, if false just those in search path
* @param $tabletypes If true, will include table types
* @param $domains Ignored
* @return A recordet
*/
function getTypes($all = false, $tabletypes = false, $domains = false) {
global $conf;
if ($all || $conf['show_system']) {
$where = '';
} else {
$where = "AND pt.oid > '{$this->_lastSystemOID}'::oid";
}
// Never show system table types
$where2 = "AND c.oid > '{$this->_lastSystemOID}'::oid";
// Create type filter
$tqry = "'c'";
if ($tabletypes)
$tqry .= ", 'r', 'v'";
$sql = "SELECT
pt.typname AS basename,
format_type(pt.oid, NULL) AS typname,
pu.usename AS typowner,
(SELECT description FROM pg_description pd WHERE pt.oid=pd.objoid) AS typcomment
FROM
pg_type pt,
pg_user pu
WHERE
pt.typowner = pu.usesysid
AND (pt.typrelid = 0 OR (SELECT c.relkind IN ({$tqry}) FROM pg_class c WHERE c.oid = pt.typrelid {$where2}))
AND typname !~ '^_'
{$where}
ORDER BY typname
";
return $this->selectSet($sql);
}
// Opclass functions
/**
* Gets all opclasses
* @return A recordset
*/
function getOpClasses() {
global $conf;
if ($conf['show_system'])
$where = '';
else
$where = "AND po.oid > '{$this->_lastSystemOID}'::oid";
$sql = "
SELECT DISTINCT
pa.amname,
po.opcname,
format_type(po.opcintype, NULL) AS opcintype,
TRUE AS opcdefault,
NULL::text AS opccomment
FROM
pg_opclass po, pg_am pa
WHERE
po.opcamid=pa.oid
{$where}
ORDER BY 1,2
";
return $this->selectSet($sql);
}
// Administration functions
/**
* Vacuums a database
* @param $table The table to vacuum
* @param $analyze If true, also does analyze
* @param $full If true, selects "full" vacuum (PostgreSQL >= 7.2)
* @param $freeze If true, selects aggressive "freezing" of tuples (PostgreSQL >= 7.2)
*/
function vacuumDB($table = '', $analyze = false, $full = false, $freeze = false) {
$sql = "VACUUM";
if ($full) $sql .= " FULL";
if ($freeze) $sql .= " FREEZE";
if ($analyze) $sql .= " ANALYZE";
if ($table != '') {
$this->fieldClean($table);
$sql .= " \"{$table}\"";
}
return $this->execute($sql);
}
/**
* Analyze a database
* @note PostgreSQL 7.2 finally had an independent ANALYZE command
* @param $table (optional) The table to analyze
*/
function analyzeDB($table = '') {
if ($table != '') {
$this->fieldClean($table);
$sql = "ANALYZE \"{$table}\"";
}
else
$sql = "ANALYZE";
return $this->execute($sql);
}
// Statistics collector functions
/**
* Fetches statistics for a database
* @param $database The database to fetch stats for
* @return A recordset
*/
function getStatsDatabase($database) {
$this->clean($database);
$sql = "SELECT * FROM pg_stat_database WHERE datname='{$database}'";
return $this->selectSet($sql);
}
/**
* Fetches tuple statistics for a table
* @param $table The table to fetch stats for
* @return A recordset
*/
function getStatsTableTuples($table) {
$this->clean($table);
$sql = 'SELECT * FROM pg_stat_all_tables WHERE';
if ($this->hasSchemas()) $sql .= " schemaname='{$this->_schema}' AND";
$sql .= " relname='{$table}'";
return $this->selectSet($sql);
}
/**
* Fetches I/0 statistics for a table
* @param $table The table to fetch stats for
* @return A recordset
*/
function getStatsTableIO($table) {
$this->clean($table);
$sql = 'SELECT * FROM pg_statio_all_tables WHERE';
if ($this->hasSchemas()) $sql .= " schemaname='{$this->_schema}' AND";
$sql .= " relname='{$table}'";
return $this->selectSet($sql);
}
/**
* Fetches tuple statistics for all indexes on a table
* @param $table The table to fetch index stats for
* @return A recordset
*/
function getStatsIndexTuples($table) {
$this->clean($table);
$sql = 'SELECT * FROM pg_stat_all_indexes WHERE';
if ($this->hasSchemas()) $sql .= " schemaname='{$this->_schema}' AND";
$sql .= " relname='{$table}' ORDER BY indexrelname";
return $this->selectSet($sql);
}
/**
* Fetches I/0 statistics for all indexes on a table
* @param $table The table to fetch index stats for
* @return A recordset
*/
function getStatsIndexIO($table) {
$this->clean($table);
$sql = 'SELECT * FROM pg_statio_all_indexes WHERE';
if ($this->hasSchemas()) $sql .= " schemaname='{$this->_schema}' AND";
$sql .= " relname='{$table}' ORDER BY indexrelname";
return $this->selectSet($sql);
}
// Capabilities
function hasWithoutOIDs() { return true; }
function hasPartialIndexes() { return true; }
function hasProcesses() { return true; }
function hasStatsCollector() { return true; }
function hasFullVacuum() { return true; }
}
?>
|