diff options
Diffstat (limited to 'includes/DatabasePostgres.php')
| -rw-r--r-- | includes/DatabasePostgres.php | 332 |
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() { |