summaryrefslogtreecommitdiff
path: root/includes/DatabasePostgres.php
diff options
context:
space:
mode:
authorPierre Schmitz <pierre@archlinux.de>2007-05-16 20:58:53 +0000
committerPierre Schmitz <pierre@archlinux.de>2007-05-16 20:58:53 +0000
commitcecb985bee3bdd252e1b8dc0bd500b37cd52be01 (patch)
tree17266aa237742640aabee7856f0202317a45d540 /includes/DatabasePostgres.php
parent0bac06c301f2a83edb0236e4c2434da16848d549 (diff)
Aktualisierung auf MediaWiki 1.10.0
Plugins angepasst und verbessert kleine Korrekturen am Design
Diffstat (limited to 'includes/DatabasePostgres.php')
-rw-r--r--includes/DatabasePostgres.php332
1 files changed, 277 insertions, 55 deletions
diff --git a/includes/DatabasePostgres.php b/includes/DatabasePostgres.php
index 803c0e26..7158e2d1 100644
--- a/includes/DatabasePostgres.php
+++ b/includes/DatabasePostgres.php
@@ -7,12 +7,69 @@
* than MySQL ones, some of them should be moved to parent
* Database class.
*
- * @package MediaWiki
+ * @addtogroup Database
*/
+class PostgresField {
+ private $name, $tablename, $type, $nullable, $max_length;
+
+ static function fromText($db, $table, $field) {
+ global $wgDBmwschema;
+
+ $q = <<<END
+SELECT typname, attnotnull, attlen
+FROM pg_class, pg_namespace, pg_attribute, pg_type
+WHERE relnamespace=pg_namespace.oid
+AND relkind='r'
+AND attrelid=pg_class.oid
+AND atttypid=pg_type.oid
+AND nspname=%s
+AND relname=%s
+AND attname=%s;
+END;
+ $res = $db->query(sprintf($q,
+ $db->addQuotes($wgDBmwschema),
+ $db->addQuotes($table),
+ $db->addQuotes($field)));
+ $row = $db->fetchObject($res);
+ if (!$row)
+ return null;
+ $n = new PostgresField;
+ $n->type = $row->typname;
+ $n->nullable = ($row->attnotnull == 'f');
+ $n->name = $field;
+ $n->tablename = $table;
+ $n->max_length = $row->attlen;
+ return $n;
+ }
+
+ function name() {
+ return $this->name;
+ }
+
+ function tableName() {
+ return $this->tablename;
+ }
+
+ function type() {
+ return $this->type;
+ }
+
+ function nullable() {
+ return $this->nullable;
+ }
+ function maxLength() {
+ return $this->max_length;
+ }
+}
+
+/**
+ * @addtogroup Database
+ */
class DatabasePostgres extends Database {
var $mInsertId = NULL;
var $mLastResult = NULL;
+ var $numeric_version = NULL;
function DatabasePostgres($server = false, $user = false, $password = false, $dbName = false,
$failFunction = false, $flags = 0 )
@@ -25,24 +82,31 @@ class DatabasePostgres extends Database {
}
$this->mOut =& $wgOut;
$this->mFailFunction = $failFunction;
- $this->mCascadingDeletes = true;
- $this->mCleanupTriggers = true;
- $this->mStrictIPs = true;
$this->mFlags = $flags;
$this->open( $server, $user, $password, $dbName);
}
+ function cascadingDeletes() {
+ return true;
+ }
+ function cleanupTriggers() {
+ return true;
+ }
+ function strictIPs() {
+ return true;
+ }
function realTimestamps() {
return true;
}
-
function implicitGroupby() {
return false;
}
+ function searchableIPs() {
+ return true;
+ }
- static function newFromParams( $server = false, $user = false, $password = false, $dbName = false,
- $failFunction = false, $flags = 0)
+ static function newFromParams( $server, $user, $password, $dbName, $failFunction = false, $flags = 0)
{
return new DatabasePostgres( $server, $user, $password, $dbName, $failFunction, $flags );
}
@@ -57,9 +121,12 @@ class DatabasePostgres extends Database {
throw new DBConnectionError( $this, "Postgres functions missing, have you compiled PHP with the --with-pgsql option?\n (Note: if you recently installed PHP, you may need to restart your webserver and database)\n" );
}
-
global $wgDBport;
+ if (!strlen($user)) { ## e.g. the class is being loaded
+ return;
+ }
+
$this->close();
$this->mServer = $server;
$port = $wgDBport;
@@ -75,9 +142,6 @@ class DatabasePostgres extends Database {
$hstring .= "port=$port ";
}
- if (!strlen($user)) { ## e.g. the class is being loaded
- return;
- }
error_reporting( E_ALL );
@$this->mConn = pg_connect("$hstring dbname=$dbName user=$user password=$password");
@@ -94,21 +158,15 @@ class DatabasePostgres extends Database {
if (defined('MEDIAWIKI_INSTALL')) {
global $wgDBname, $wgDBuser, $wgDBpassword, $wgDBsuperuser, $wgDBmwschema,
$wgDBts2schema;
- print "OK</li>\n";
print "<li>Checking the version of Postgres...";
- $version = pg_fetch_result($this->doQuery("SELECT version()"),0,0);
- $thisver = array();
- if (!preg_match('/PostgreSQL (\d+\.\d+)(\S+)/', $version, $thisver)) {
- print "<b>FAILED</b> (could not determine the version)</li>\n";
- dieout("</ul>");
- }
+ $version = $this->getServerVersion();
$PGMINVER = "8.1";
- if ($thisver[1] < $PGMINVER) {
- print "<b>FAILED</b>. Required version is $PGMINVER. You have $thisver[1]$thisver[2]</li>\n";
+ if ($this->numeric_version < $PGMINVER) {
+ print "<b>FAILED</b>. Required version is $PGMINVER. You have $this->numeric_version ($version)</li>\n";
dieout("</ul>");
}
- print "version $thisver[1]$thisver[2] is OK.</li>\n";
+ print "version $this->numeric_version is OK.</li>\n";
$safeuser = $this->quote_ident($wgDBuser);
## Are we connecting as a superuser for the first time?
@@ -232,7 +290,8 @@ class DatabasePostgres extends Database {
$wgDBsuperuser = '';
return true; ## Reconnect as regular user
- }
+
+ } ## end superuser
if (!defined('POSTGRES_SEARCHPATH')) {
@@ -249,13 +308,24 @@ class DatabasePostgres extends Database {
## Does this user have the rights to the tsearch2 tables?
$ctype = pg_fetch_result($this->doQuery("SHOW lc_ctype"),0,0);
print "<li>Checking tsearch2 permissions...";
+ ## Let's check all four, just to be safe
+ error_reporting( 0 );
+ $ts2tables = array('cfg','cfgmap','dict','parser');
+ foreach ( $ts2tables AS $tname ) {
+ $SQL = "SELECT count(*) FROM $wgDBts2schema.pg_ts_$tname";
+ $res = $this->doQuery($SQL);
+ if (!$res) {
+ print "<b>FAILED</b> to access pg_ts_$tname. Make sure that the user ".
+ "\"$wgDBuser\" has SELECT access to all four tsearch2 tables</li>\n";
+ dieout("</ul>");
+ }
+ }
$SQL = "SELECT ts_name FROM $wgDBts2schema.pg_ts_cfg WHERE locale = '$ctype'";
$SQL .= " ORDER BY CASE WHEN ts_name <> 'default' THEN 1 ELSE 0 END";
- error_reporting( 0 );
$res = $this->doQuery($SQL);
error_reporting( E_ALL );
if (!$res) {
- print "<b>FAILED</b>. Make sure that the user \"$wgDBuser\" has SELECT access to the tsearch2 tables</li>\n";
+ print "<b>FAILED</b>. Could not determine the tsearch2 locale information</li>\n";
dieout("</ul>");
}
print "OK</li>";
@@ -282,7 +352,7 @@ class DatabasePostgres extends Database {
$res = $this->doQuery($SQL);
if (!$res) {
print "<b>FAILED</b>. ";
- print "Please make sure that the locale in pg_ts_cfg for \"default\" is set to \"ctype\"</li>\n";
+ print "Please make sure that the locale in pg_ts_cfg for \"default\" is set to \"$ctype\"</li>\n";
dieout("</ul>");
}
print "OK</li>";
@@ -325,9 +395,13 @@ class DatabasePostgres extends Database {
$result = $this->schemaExists($wgDBmwschema);
if (!$result) {
print "<li>Creating schema <b>$wgDBmwschema</b> ...";
+ error_reporting( 0 );
$result = $this->doQuery("CREATE SCHEMA $wgDBmwschema");
+ error_reporting( E_ALL );
if (!$result) {
- print "<b>FAILED</b>.</li>\n";
+ print "<b>FAILED</b>. The user \"$wgDBuser\" must be able to access the schema. ".
+ "You can try making them the owner of the database, or try creating the schema with a ".
+ "different user, and then grant access to the \"$wgDBuser\" user.</li>\n";
dieout("</ul>");
}
print "OK</li>\n";
@@ -339,6 +413,39 @@ class DatabasePostgres extends Database {
print "<li>Schema \"$wgDBmwschema\" exists and is owned by \"$user\". Excellent.</li>\n";
}
+ ## Always return GMT time to accomodate the existing integer-based timestamp assumption
+ print "<li>Setting the timezone to GMT for user \"$user\" ...";
+ $SQL = "ALTER USER $safeuser SET timezone = 'GMT'";
+ $result = pg_query($this->mConn, $SQL);
+ if (!$result) {
+ print "<b>FAILED</b>.</li>\n";
+ dieout("</ul>");
+ }
+ print "OK</li>\n";
+ ## Set for the rest of this session
+ $SQL = "SET timezone = 'GMT'";
+ $result = pg_query($this->mConn, $SQL);
+ if (!$result) {
+ print "<li>Failed to set timezone</li>\n";
+ dieout("</ul>");
+ }
+
+ print "<li>Setting the datestyle to ISO, YMD for user \"$user\" ...";
+ $SQL = "ALTER USER $safeuser SET datestyle = 'ISO, YMD'";
+ $result = pg_query($this->mConn, $SQL);
+ if (!$result) {
+ print "<b>FAILED</b>.</li>\n";
+ dieout("</ul>");
+ }
+ print "OK</li>\n";
+ ## Set for the rest of this session
+ $SQL = "SET datestyle = 'ISO, YMD'";
+ $result = pg_query($this->mConn, $SQL);
+ if (!$result) {
+ print "<li>Failed to set datestyle</li>\n";
+ dieout("</ul>");
+ }
+
## Fix up the search paths if needed
print "<li>Setting the search path for user \"$user\" ...";
$path = $this->quote_ident($wgDBmwschema);
@@ -455,6 +562,30 @@ class DatabasePostgres extends Database {
}
/**
+ * Estimate rows in dataset
+ * Returns estimated count, based on EXPLAIN output
+ * This is not necessarily an accurate estimate, so use sparingly
+ * Returns -1 if count cannot be found
+ * Takes same arguments as Database::select()
+ */
+
+ function estimateRowCount( $table, $vars='*', $conds='', $fname = 'Database::estimateRowCount', $options = array() ) {
+ $options['EXPLAIN'] = true;
+ $res = $this->select( $table, $vars, $conds, $fname, $options );
+ $rows = -1;
+ if ( $res ) {
+ $row = $this->fetchRow( $res );
+ $count = array();
+ if( preg_match( '/rows=(\d+)/', $row[0], $count ) ) {
+ $rows = $count[1];
+ }
+ $this->freeResult($res);
+ }
+ return $rows;
+ }
+
+
+ /**
* Returns information about an index
* If errors are explicitly ignored, returns NULL on failure
*/
@@ -645,7 +776,7 @@ class DatabasePostgres extends Database {
return '';
}
- function limitResult($sql, $limit,$offset) {
+ function limitResult($sql, $limit,$offset=false) {
return "$sql LIMIT $limit ".(is_numeric($offset)?" OFFSET {$offset} ":"");
}
@@ -707,26 +838,31 @@ class DatabasePostgres extends Database {
* @return string Version information from the database
*/
function getServerVersion() {
- $res = $this->query( "SELECT version()" );
- $row = $this->fetchRow( $res );
- $version = $row[0];
- $this->freeResult( $res );
+ $version = pg_fetch_result($this->doQuery("SELECT version()"),0,0);
+ $thisver = array();
+ if (!preg_match('/PostgreSQL (\d+\.\d+)(\S+)/', $version, $thisver)) {
+ die("Could not determine the numeric version from $version!");
+ }
+ $this->numeric_version = $thisver[1];
return $version;
}
/**
- * Query whether a given table exists (in the given schema, or the default mw one if not given)
+ * Query whether a given relation exists (in the given schema, or the
+ * default mw one if not given)
*/
- function tableExists( $table, $schema = false ) {
+ function relationExists( $table, $types, $schema = false ) {
global $wgDBmwschema;
+ if (!is_array($types))
+ $types = array($types);
if (! $schema )
$schema = $wgDBmwschema;
- $etable = preg_replace("/'/", "''", $table);
- $eschema = preg_replace("/'/", "''", $schema);
+ $etable = $this->addQuotes($table);
+ $eschema = $this->addQuotes($schema);
$SQL = "SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "
- . "WHERE c.relnamespace = n.oid AND c.relname = '$etable' AND n.nspname = '$eschema' "
- . "AND c.relkind IN ('r','v')";
+ . "WHERE c.relnamespace = n.oid AND c.relname = $etable AND n.nspname = $eschema "
+ . "AND c.relkind IN ('" . implode("','", $types) . "')";
$res = $this->query( $SQL );
$count = $res ? pg_num_rows($res) : 0;
if ($res)
@@ -734,6 +870,61 @@ class DatabasePostgres extends Database {
return $count;
}
+ /*
+ * For backward compatibility, this function checks both tables and
+ * views.
+ */
+ function tableExists ($table, $schema = false) {
+ return $this->relationExists($table, array('r', 'v'), $schema);
+ }
+
+ function sequenceExists ($sequence, $schema = false) {
+ return $this->relationExists($sequence, 'S', $schema);
+ }
+
+ function triggerExists($table, $trigger) {
+ global $wgDBmwschema;
+
+ $q = <<<END
+ SELECT 1 FROM pg_class, pg_namespace, pg_trigger
+ WHERE relnamespace=pg_namespace.oid AND relkind='r'
+ AND tgrelid=pg_class.oid
+ AND nspname=%s AND relname=%s AND tgname=%s
+END;
+ $res = $this->query(sprintf($q,
+ $this->addQuotes($wgDBmwschema),
+ $this->addQuotes($table),
+ $this->addQuotes($trigger)));
+ if (!$res)
+ return NULL;
+ $rows = pg_num_rows($res);
+ $this->freeResult($res);
+ return $rows;
+ }
+
+ function ruleExists($table, $rule) {
+ global $wgDBmwschema;
+ $exists = $this->selectField("pg_rules", "rulename",
+ array( "rulename" => $rule,
+ "tablename" => $table,
+ "schemaname" => $wgDBmwschema));
+ return $exists === $rule;
+ }
+
+ function constraintExists($table, $constraint) {
+ global $wgDBmwschema;
+ $SQL = sprintf("SELECT 1 FROM information_schema.table_constraints ".
+ "WHERE constraint_schema = %s AND table_name = %s AND constraint_name = %s",
+ $this->addQuotes($wgDBmwschema),
+ $this->addQuotes($table),
+ $this->addQuotes($constraint));
+ $res = $this->query($SQL);
+ if (!$res)
+ return NULL;
+ $rows = pg_num_rows($res);
+ $this->freeResult($res);
+ return $rows;
+ }
/**
* Query whether a given schema exists. Returns the name of the owner
@@ -752,7 +943,7 @@ class DatabasePostgres extends Database {
/**
* Query whether a given column exists in the mediawiki schema
*/
- function fieldExists( $table, $field ) {
+ function fieldExists( $table, $field, $fname = 'DatabasePostgres::fieldExists' ) {
global $wgDBmwschema;
$etable = preg_replace("/'/", "''", $table);
$eschema = preg_replace("/'/", "''", $wgDBmwschema);
@@ -760,7 +951,7 @@ class DatabasePostgres extends Database {
$SQL = "SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a "
. "WHERE c.relnamespace = n.oid AND c.relname = '$etable' AND n.nspname = '$eschema' "
. "AND a.attrelid = c.oid AND a.attname = '$ecol'";
- $res = $this->query( $SQL );
+ $res = $this->query( $SQL, $fname );
$count = $res ? pg_num_rows($res) : 0;
if ($res)
$this->freeResult( $res );
@@ -768,12 +959,10 @@ class DatabasePostgres extends Database {
}
function fieldInfo( $table, $field ) {
- $res = $this->query( "SELECT $field FROM $table LIMIT 1" );
- $type = pg_field_type( $res, 0 );
- return $type;
+ return PostgresField::fromText($this, $table, $field);
}
- function begin( $fname = 'DatabasePostgrs::begin' ) {
+ function begin( $fname = 'DatabasePostgres::begin' ) {
$this->query( 'BEGIN', $fname );
$this->mTrxLevel = 1;
}
@@ -791,10 +980,36 @@ class DatabasePostgres extends Database {
}
function setup_database() {
- global $wgVersion, $wgDBmwschema, $wgDBts2schema, $wgDBport;
+ global $wgVersion, $wgDBmwschema, $wgDBts2schema, $wgDBport, $wgDBuser;
+
+ ## Make sure that we can write to the correct schema
+ ## If not, Postgres will happily and silently go to the next search_path item
+ $ctest = "mw_test_table";
+ if ($this->tableExists($ctest, $wgDBmwschema)) {
+ $this->doQuery("DROP TABLE $wgDBmwschema.$ctest");
+ }
+ $SQL = "CREATE TABLE $wgDBmwschema.$ctest(a int)";
+ error_reporting( 0 );
+ $res = $this->doQuery($SQL);
+ error_reporting( E_ALL );
+ if (!$res) {
+ print "<b>FAILED</b>. Make sure that the user \"$wgDBuser\" can write to the schema \"$wgDBmwschema\"</li>\n";
+ dieout("</ul>");
+ }
+ $this->doQuery("DROP TABLE $wgDBmwschema.mw_test_table");
dbsource( "../maintenance/postgres/tables.sql", $this);
+ ## Version-specific stuff
+ if ($this->numeric_version == 8.1) {
+ $this->doQuery("CREATE INDEX ts2_page_text ON pagecontent USING gist(textvector)");
+ $this->doQuery("CREATE INDEX ts2_page_title ON page USING gist(titlevector)");
+ }
+ else {
+ $this->doQuery("CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector)");
+ $this->doQuery("CREATE INDEX ts2_page_title ON page USING gin(titlevector)");
+ }
+
## Update version information
$mwv = $this->addQuotes($wgVersion);
$pgv = $this->addQuotes($this->getServerVersion());
@@ -827,6 +1042,8 @@ class DatabasePostgres extends Database {
$this->query("$SQL $matches[1],$matches[2])");
}
print " (table interwiki successfully populated)...\n";
+
+ $this->doQuery("COMMIT");
}
function encodeBlob($b) {
@@ -870,7 +1087,7 @@ class DatabasePostgres extends Database {
* @return array
*/
function makeSelectOptions( $options ) {
- $tailOpts = '';
+ $preLimitTail = $postLimitTail = '';
$startOpts = '';
$noKeyOptions = array();
@@ -880,16 +1097,17 @@ class DatabasePostgres extends Database {
}
}
- if ( isset( $options['GROUP BY'] ) ) $tailOpts .= " GROUP BY {$options['GROUP BY']}";
- if ( isset( $options['ORDER BY'] ) ) $tailOpts .= " ORDER BY {$options['ORDER BY']}";
+ if ( isset( $options['GROUP BY'] ) ) $preLimitTail .= " GROUP BY " . $options['GROUP BY'];
+ if ( isset( $options['ORDER BY'] ) ) $preLimitTail .= " ORDER BY " . $options['ORDER BY'];
- if (isset($options['LIMIT'])) {
- $tailOpts .= $this->limitResult('', $options['LIMIT'],
- isset($options['OFFSET']) ? $options['OFFSET'] : false);
- }
-
- if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $tailOpts .= ' FOR UPDATE';
- if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $tailOpts .= ' LOCK IN SHARE MODE';
+ //if (isset($options['LIMIT'])) {
+ // $tailOpts .= $this->limitResult('', $options['LIMIT'],
+ // isset($options['OFFSET']) ? $options['OFFSET']
+ // : false);
+ //}
+
+ if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $postLimitTail .= ' FOR UPDATE';
+ if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $postLimitTail .= ' LOCK IN SHARE MODE';
if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT';
if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) {
@@ -898,7 +1116,11 @@ class DatabasePostgres extends Database {
$useIndex = '';
}
- return array( $startOpts, $useIndex, $tailOpts );
+ return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail );
+ }
+
+ public function setTimeout( $timeout ) {
+ // @todo fixme no-op
}
function ping() {