diff options
Diffstat (limited to 'includes/DatabasePostgres.php')
| -rw-r--r-- | includes/DatabasePostgres.php | 336 |
1 files changed, 283 insertions, 53 deletions
diff --git a/includes/DatabasePostgres.php b/includes/DatabasePostgres.php index 5897386f..a5e02e77 100644 --- a/includes/DatabasePostgres.php +++ b/includes/DatabasePostgres.php @@ -1,7 +1,7 @@ <?php /** - * This is PostgreSQL database abstraction layer. + * This is Postgres database abstraction layer. * * As it includes more generic version for DB functions, * than MySQL ones, some of them should be moved to parent @@ -10,11 +10,6 @@ * @package MediaWiki */ -/** - * Depends on database - */ -require_once( 'Database.php' ); - class DatabasePostgres extends Database { var $mInsertId = NULL; var $mLastResult = NULL; @@ -30,8 +25,10 @@ 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); } @@ -47,11 +44,12 @@ class DatabasePostgres extends Database { * If the failFunction is set to a non-zero integer, returns success */ function open( $server, $user, $password, $dbName ) { - # Test for PostgreSQL support, to avoid suppressed fatal error + # Test for Postgres support, to avoid suppressed fatal error if ( !function_exists( 'pg_connect' ) ) { - throw new DBConnectionError( $this, "PostgreSQL functions missing, have you compiled PHP with the --with-pgsql option?\n" ); + 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; $this->close(); @@ -62,7 +60,6 @@ class DatabasePostgres extends Database { $this->mDBname = $dbName; $success = false; - $hstring=""; if ($server!=false && $server!="") { $hstring="host=$server "; @@ -71,8 +68,11 @@ class DatabasePostgres extends Database { $hstring .= "port=$port "; } - error_reporting( E_ALL ); + 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"); if ( $this->mConn == false ) { @@ -83,12 +83,153 @@ class DatabasePostgres extends Database { } $this->mOpened = true; - ## If this is the initial connection, setup the schema stuff - if (defined('MEDIAWIKI_INSTALL') and !defined('POSTGRES_SEARCHPATH')) { - global $wgDBmwschema, $wgDBts2schema, $wgDBname; + ## If this is the initial connection, setup the schema stuff and possibly create the user + if (defined('MEDIAWIKI_INSTALL')) { + global $wgDBname, $wgDBuser, $wgDBpass, $wgDBsuperuser, $wgDBmwschema, + $wgDBts2schema, $wgDBts2locale; + print "OK</li>\n"; + + print "<li>Checking the version of Postgres..."; + $version = pg_fetch_result($this->doQuery("SELECT version()"),0,0); + if (!preg_match("/PostgreSQL (\d+\.\d+)(\S+)/", $version, $thisver)) { + print "<b>FAILED</b> (could not determine the version)</li>\n"; + dieout("</ul>"); + } + $PGMINVER = "8.1"; + if ($thisver[1] < $PGMINVER) { + print "<b>FAILED</b>. Required version is $PGMINVER. You have $thisver[1]$thisver[2]</li>\n"; + dieout("</ul>"); + } + print "version $thisver[1]$thisver[2] is OK.</li>\n"; + + $safeuser = $this->quote_ident($wgDBuser); + ## Are we connecting as a superuser for the first time? + if ($wgDBsuperuser) { + ## Are we really a superuser? Check out our rights + $SQL = "SELECT + CASE WHEN usesuper IS TRUE THEN + CASE WHEN usecreatedb IS TRUE THEN 3 ELSE 1 END + ELSE CASE WHEN usecreatedb IS TRUE THEN 2 ELSE 0 END + END AS rights + FROM pg_catalog.pg_user WHERE usename = " . $this->addQuotes($wgDBsuperuser); + $rows = $this->numRows($res = $this->doQuery($SQL)); + if (!$rows) { + print "<li>ERROR: Could not read permissions for user \"$wgDBsuperuser\"</li>\n"; + dieout('</ul>'); + } + $perms = pg_fetch_result($res, 0, 0); + + $SQL = "SELECT 1 FROM pg_catalog.pg_user WHERE usename = " . $this->addQuotes($wgDBuser); + $rows = $this->numRows($this->doQuery($SQL)); + if ($rows) { + print "<li>User \"$wgDBuser\" already exists, skipping account creation.</li>"; + } + else { + if ($perms != 1 and $perms != 3) { + print "<li>ERROR: the user \"$wgDBsuperuser\" cannot create other users. "; + print 'Please use a different Postgres user.</li>'; + dieout('</ul>'); + } + print "<li>Creating user <b>$wgDBuser</b>..."; + $safepass = $this->addQuotes($wgDBpass); + $SQL = "CREATE USER $safeuser NOCREATEDB PASSWORD $safepass"; + $this->doQuery($SQL); + print "OK</li>\n"; + } + ## User now exists, check out the database + if ($dbName != $wgDBname) { + $SQL = "SELECT 1 FROM pg_catalog.pg_database WHERE datname = " . $this->addQuotes($wgDBname); + $rows = $this->numRows($this->doQuery($SQL)); + if ($rows) { + print "<li>Database \"$wgDBname\" already exists, skipping database creation.</li>"; + } + else { + if ($perms < 2) { + print "<li>ERROR: the user \"$wgDBsuperuser\" cannot create databases. "; + print 'Please use a different Postgres user.</li>'; + dieout('</ul>'); + } + print "<li>Creating database <b>$wgDBname</b>..."; + $safename = $this->quote_ident($wgDBname); + $SQL = "CREATE DATABASE $safename OWNER $safeuser "; + $this->doQuery($SQL); + print "OK</li>\n"; + ## Hopefully tsearch2 and plpgsql are in template1... + } + + ## Reconnect to check out tsearch2 rights for this user + print "<li>Connecting to \"$wgDBname\" as superuser \"$wgDBsuperuser\" to check rights..."; + @$this->mConn = pg_connect("$hstring dbname=$wgDBname user=$user password=$password"); + if ( $this->mConn == false ) { + print "<b>FAILED TO CONNECT!</b></li>"; + dieout("</ul>"); + } + print "OK</li>\n"; + } + + ## Tsearch2 checks + print "<li>Checking that tsearch2 is installed in the database \"$wgDBname\"..."; + if (! $this->tableExists("pg_ts_cfg", $wgDBts2schema)) { + print "<b>FAILED</b>. tsearch2 must be installed in the database \"$wgDBname\"."; + print "Please see <a href='http://www.devx.com/opensource/Article/21674/0/page/2'>this article</a>"; + print " for instructions or ask on #postgresql on irc.freenode.net</li>\n"; + dieout("</ul>"); + } + print "OK</li>\n"; + print "<li>Ensuring that user \"$wgDBuser\" has select rights on the tsearch2 tables..."; + foreach (array('cfg','cfgmap','dict','parser') as $table) { + $SQL = "GRANT SELECT ON pg_ts_$table TO $safeuser"; + $this->doQuery($SQL); + } + print "OK</li>\n"; + + + ## Setup the schema for this user if needed + $result = $this->schemaExists($wgDBmwschema); + $safeschema = $this->quote_ident($wgDBmwschema); + if (!$result) { + print "<li>Creating schema <b>$wgDBmwschema</b> ..."; + $result = $this->doQuery("CREATE SCHEMA $safeschema AUTHORIZATION $safeuser"); + if (!$result) { + print "<b>FAILED</b>.</li>\n"; + dieout("</ul>"); + } + print "OK</li>\n"; + } + else { + print "<li>Schema already exists, explicitly granting rights...\n"; + $safeschema2 = $this->addQuotes($wgDBmwschema); + $SQL = "SELECT 'GRANT ALL ON '||pg_catalog.quote_ident(relname)||' TO $safeuser;'\n". + "FROM pg_catalog.pg_class p, pg_catalog.pg_namespace n\n". + "WHERE relnamespace = n.oid AND n.nspname = $safeschema2\n". + "AND p.relkind IN ('r','S','v')\n"; + $SQL .= "UNION\n"; + $SQL .= "SELECT 'GRANT ALL ON FUNCTION '||pg_catalog.quote_ident(proname)||'('||\n". + "pg_catalog.oidvectortypes(p.proargtypes)||') TO $safeuser;'\n". + "FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n\n". + "WHERE p.pronamespace = n.oid AND n.nspname = $safeschema2"; + $res = $this->doQuery($SQL); + if (!$res) { + print "<b>FAILED</b>. Could not set rights for the user.</li>\n"; + dieout("</ul>"); + } + $this->doQuery("SET search_path = $safeschema"); + $rows = $this->numRows($res); + while ($rows) { + $rows--; + $this->doQuery(pg_fetch_result($res, $rows, 0)); + } + print "OK</li>"; + } + + $wgDBsuperuser = ''; + return true; ## Reconnect as regular user + } + + if (!defined('POSTGRES_SEARCHPATH')) { ## Do we have the basic tsearch2 table? - print "<li>Checking for tsearch2 ..."; + print "<li>Checking for tsearch2 in the schema \"$wgDBts2schema\"..."; if (! $this->tableExists("pg_ts_dict", $wgDBts2schema)) { print "<b>FAILED</b>. Make sure tsearch2 is installed. See <a href="; print "'http://www.devx.com/opensource/Article/21674/0/page/2'>this article</a>"; @@ -97,15 +238,78 @@ class DatabasePostgres extends Database { } print "OK</li>\n"; + ## 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..."; + $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"; + dieout("</ul>"); + } + print "OK</li>"; + + ## Will the current locale work? Can we force it to? + print "<li>Verifying tsearch2 locale with $ctype..."; + $rows = $this->numRows($res); + $resetlocale = 0; + if (!$rows) { + print "<b>not found</b></li>\n"; + print "<li>Attempting to set default tsearch2 locale to \"$ctype\"..."; + $resetlocale = 1; + } + else { + $tsname = pg_fetch_result($res, 0, 0); + if ($tsname != 'default') { + print "<b>not set to default ($tsname)</b>"; + print "<li>Attempting to change tsearch2 default locale to \"$ctype\"..."; + $resetlocale = 1; + } + } + if ($resetlocale) { + $SQL = "UPDATE $wgDBts2schema.pg_ts_cfg SET locale = '$ctype' WHERE ts_name = 'default'"; + $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"; + dieout("</ul>"); + } + print "OK</li>"; + } + + ## Final test: try out a simple tsearch2 query + $SQL = "SELECT $wgDBts2schema.to_tsvector('default','MediaWiki tsearch2 testing')"; + $res = $this->doQuery($SQL); + if (!$res) { + print "<b>FAILED</b>. Specifically, \"$SQL\" did not work.</li>"; + dieout("</ul>"); + } + print "OK</li>"; + ## Do we have plpgsql installed? - print "<li>Checking for plpgsql ..."; + print "<li>Checking for Pl/Pgsql ..."; $SQL = "SELECT 1 FROM pg_catalog.pg_language WHERE lanname = 'plpgsql'"; - $res = $this->doQuery($SQL); $rows = $this->numRows($this->doQuery($SQL)); if ($rows < 1) { - print "<b>FAILED</b>. Make sure the language plpgsql is installed for the database <tt>$wgDBname</tt>t</li>"; - ## XXX Better help - dieout("</ul>"); + // plpgsql is not installed, but if we have a pg_pltemplate table, we should be able to create it + print "not installed. Attempting to install Pl/Pgsql ..."; + $SQL = "SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) ". + "WHERE relname = 'pg_pltemplate' AND nspname='pg_catalog'"; + $rows = $this->numRows($this->doQuery($SQL)); + if ($rows >= 1) { + $result = $this->doQuery("CREATE LANGUAGE plpgsql"); + if (!$result) { + print "<b>FAILED</b>. You need to install the language plpgsql in the database <tt>$wgDBname</tt></li>"; + dieout("</ul>"); + } + } + else { + print "<b>FAILED</b>. You need to install the language plpgsql in the database <tt>$wgDBname</tt></li>"; + dieout("</ul>"); + } } print "OK</li>\n"; @@ -115,40 +319,46 @@ class DatabasePostgres extends Database { print "<li>Creating schema <b>$wgDBmwschema</b> ..."; $result = $this->doQuery("CREATE SCHEMA $wgDBmwschema"); if (!$result) { - print "FAILED.</li>\n"; - return false; + print "<b>FAILED</b>.</li>\n"; + dieout("</ul>"); } - print "ok</li>\n"; + print "OK</li>\n"; } else if ($result != $user) { - print "<li>Schema <b>$wgDBmwschema</b> exists but is not owned by <b>$user</b>. Not ideal.</li>\n"; + print "<li>Schema \"$wgDBmwschema\" exists but is not owned by \"$user\". Not ideal.</li>\n"; } else { - print "<li>Schema <b>$wgDBmwschema</b> exists and is owned by <b>$user ($result)</b>. Excellent.</li>\n"; + print "<li>Schema \"$wgDBmwschema\" exists and is owned by \"$user\". Excellent.</li>\n"; } ## Fix up the search paths if needed - print "<li>Setting the search path for user <b>$user</b> ..."; - $path = "$wgDBmwschema"; + print "<li>Setting the search path for user \"$user\" ..."; + $path = $this->quote_ident($wgDBmwschema); if ($wgDBts2schema !== $wgDBmwschema) - $path .= ", $wgDBts2schema"; + $path .= ", ". $this->quote_ident($wgDBts2schema); if ($wgDBmwschema !== 'public' and $wgDBts2schema !== 'public') $path .= ", public"; - $SQL = "ALTER USER $user SET search_path = $path"; + $SQL = "ALTER USER $safeuser SET search_path = $path"; $result = pg_query($this->mConn, $SQL); if (!$result) { - print "FAILED.</li>\n"; - return false; + print "<b>FAILED</b>.</li>\n"; + dieout("</ul>"); } - print "ok</li>\n"; + print "OK</li>\n"; ## Set for the rest of this session $SQL = "SET search_path = $path"; $result = pg_query($this->mConn, $SQL); if (!$result) { print "<li>Failed to set search_path</li>\n"; - return false; + dieout("</ul>"); } define( "POSTGRES_SEARCHPATH", $path ); + }} + + global $wgCommandLineMode; + ## If called from the command-line (e.g. importDump), only show errors + if ($wgCommandLineMode) { + $this->doQuery("SET client_min_messages = 'ERROR'"); } return $this->mConn; @@ -177,7 +387,7 @@ class DatabasePostgres extends Database { function freeResult( $res ) { if ( !@pg_free_result( $res ) ) { - throw new DBUnexpectedError($this, "Unable to free PostgreSQL result\n" ); + throw new DBUnexpectedError($this, "Unable to free Postgres result\n" ); } } @@ -228,7 +438,9 @@ class DatabasePostgres extends Database { return "No database connection"; } } - function lastErrno() { return 1; } + function lastErrno() { + return pg_last_error() ? 1 : 0; + } function affectedRows() { return pg_affected_rows( $this->mLastResult ); @@ -266,7 +478,7 @@ class DatabasePostgres extends Database { } function insert( $table, $a, $fname = 'Database::insert', $options = array() ) { - # PostgreSQL doesn't support options + # Postgres doesn't support options # We have a go at faking one of them # TODO: DELAYED, LOW_PRIORITY @@ -295,16 +507,12 @@ class DatabasePostgres extends Database { } function tableName( $name ) { - # Replace backticks into double quotes - $name = strtr($name,'`','"'); - - # Now quote PG reserved keywords + # Replace reserved words with better ones switch( $name ) { case 'user': - case 'old': - case 'group': - return '"' . $name . '"'; - + return 'mwuser'; + case 'text': + return 'pagecontent'; default: return $name; } @@ -323,15 +531,14 @@ class DatabasePostgres extends Database { } /** - * USE INDEX clause - * PostgreSQL doesn't have them and returns "" + * Postgres does not have a "USE INDEX" clause, so return an empty string */ function useIndexClause( $index ) { return ''; } # REPLACE query wrapper - # PostgreSQL simulates this with a DELETE followed by INSERT + # Postgres simulates this with a DELETE followed by INSERT # $row is the row to insert, an associative array # $uniqueIndexes is an array of indexes. Each element may be either a # field name or an array of field names @@ -433,7 +640,7 @@ class DatabasePostgres extends Database { /** * Returns an SQL expression for a simple conditional. - * Uses CASE on PostgreSQL. + * Uses CASE on Postgres * * @param string $cond SQL expression which will result in a boolean value * @param string $trueVal SQL expression to return if true @@ -449,9 +656,8 @@ class DatabasePostgres extends Database { return false; } - # Return DB-style timestamp used for MySQL schema function timestamp( $ts=0 ) { - return wfTimestamp(TS_DB,$ts); + return wfTimestamp(TS_POSTGRES,$ts); } /** @@ -499,7 +705,8 @@ class DatabasePostgres extends Database { $etable = preg_replace("/'/", "''", $table); $eschema = preg_replace("/'/", "''", $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'"; + . "WHERE c.relnamespace = n.oid AND c.relname = '$etable' AND n.nspname = '$eschema' " + . "AND c.relkind IN ('r','v')"; $res = $this->query( $SQL ); $count = $res ? pg_num_rows($res) : 0; if ($res) @@ -563,9 +770,28 @@ class DatabasePostgres extends Database { return $sql; } - function update_interwiki() { + function setup_database() { + global $wgVersion, $wgDBmwschema, $wgDBts2schema, $wgDBport; + + dbsource( "../maintenance/postgres/tables.sql", $this); + + ## Update version information + $mwv = $this->addQuotes($wgVersion); + $pgv = $this->addQuotes($this->getServerVersion()); + $pgu = $this->addQuotes($this->mUser); + $mws = $this->addQuotes($wgDBmwschema); + $tss = $this->addQuotes($wgDBts2schema); + $pgp = $this->addQuotes($wgDBport); + $dbn = $this->addQuotes($this->mDBname); + $ctype = pg_fetch_result($this->doQuery("SHOW lc_ctype"),0,0); + + $SQL = "UPDATE mediawiki_version SET mw_version=$mwv, pg_version=$pgv, pg_user=$pgu, ". + "mw_schema = $mws, ts2_schema = $tss, pg_port=$pgp, pg_dbname=$dbn, ". + "ctype = '$ctype' ". + "WHERE type = 'Creation'"; + $this->query($SQL); + ## Avoid the non-standard "REPLACE INTO" syntax - ## Called by config/index.php $f = fopen( "../maintenance/interwiki.sql", 'r' ); if ($f == false ) { dieout( "<li>Could not find the interwiki.sql file"); @@ -604,6 +830,10 @@ class DatabasePostgres extends Database { return "E'" . pg_escape_string($s) . "'"; } + function quote_ident( $s ) { + return '"' . preg_replace( '/"/', '""', $s) . '"'; + } + } ?> |