X-Git-Url: https://git.heureux-cyclage.org/?a=blobdiff_plain;f=includes%2FDatabasePostgres.php;h=8f8488b9ae17eaa4feea7f532c3e9723b71de916;hb=3857496fe2bda374229d0652a9cf51817cab165b;hp=e58ff0dd115a8e6a62eb4325aaec226b4ffc4446;hpb=9648f3b4d0f55e0767fd24f3ec2b2b03985d78c0;p=lhc%2Fweb%2Fwiklou.git diff --git a/includes/DatabasePostgres.php b/includes/DatabasePostgres.php index e58ff0dd11..8f8488b9ae 100644 --- a/includes/DatabasePostgres.php +++ b/includes/DatabasePostgres.php @@ -1,4 +1,8 @@ numRows($res = $this->doQuery($SQL)); + } static function newFromParams( $server, $user, $password, $dbName, $failFunction = false, $flags = 0) { @@ -126,7 +151,7 @@ class DatabasePostgres extends Database { $this->close(); $this->mServer = $server; - $port = $wgDBport; + $this->mPort = $port = $wgDBport; $this->mUser = $user; $this->mPassword = $password; $this->mDBname = $dbName; @@ -139,7 +164,6 @@ class DatabasePostgres extends Database { $hstring .= "port=$port "; } - error_reporting( E_ALL ); @$this->mConn = pg_connect("$hstring dbname=$dbName user=$user password=$password"); @@ -151,93 +175,124 @@ class DatabasePostgres extends Database { } $this->mOpened = true; - ## If this is the initial connection, setup the schema stuff and possibly create the user - if (defined('MEDIAWIKI_INSTALL')) { - global $wgDBname, $wgDBuser, $wgDBpassword, $wgDBsuperuser, $wgDBmwschema, - $wgDBts2schema; - - print "
  • Checking the version of Postgres..."; - $version = $this->getServerVersion(); - $PGMINVER = "8.1"; - if ($this->numeric_version < $PGMINVER) { - print "FAILED. Required version is $PGMINVER. You have $this->numeric_version ($version)
    • \n"; - dieout(""); + + global $wgCommandLineMode; + ## If called from the command-line (e.g. importDump), only show errors + if ($wgCommandLineMode) { + $this->doQuery("SET client_min_messages = 'ERROR'"); + } + + global $wgDBmwschema, $wgDBts2schema; + if (isset( $wgDBmwschema ) && isset( $wgDBts2schema ) + && $wgDBmwschema !== 'mediawiki' + && preg_match( '/^\w+$/', $wgDBmwschema ) + && preg_match( '/^\w+$/', $wgDBts2schema ) + ) { + $safeschema = $this->quote_ident($wgDBmwschema); + $safeschema2 = $this->quote_ident($wgDBts2schema); + $this->doQuery("SET search_path = $safeschema, $wgDBts2schema, public"); + } + + return $this->mConn; + } + + + function initial_setup($password, $dbName) { + // If this is the initial connection, setup the schema stuff and possibly create the user + global $wgDBname, $wgDBuser, $wgDBpassword, $wgDBsuperuser, $wgDBmwschema, $wgDBts2schema; + + print "
  • Checking the version of Postgres..."; + $version = $this->getServerVersion(); + $PGMINVER = '8.1'; + if ($this->numeric_version < $PGMINVER) { + print "FAILED. Required version is $PGMINVER. You have $this->numeric_version ($version)
    • \n"; + dieout(""); + } + print "version $this->numeric_version is OK.\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 "
  • ERROR: Could not read permissions for user \"$wgDBsuperuser\"
    • \n"; + dieout(''); } - print "version $this->numeric_version is OK.\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 "
  • ERROR: Could not read permissions for user \"$wgDBsuperuser\"
    • \n"; + $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 "
  • User \"$wgDBuser\" already exists, skipping account creation.
    • "; + } + else { + if ($perms != 1 and $perms != 3) { + print "
  • ERROR: the user \"$wgDBsuperuser\" cannot create other users. "; + print 'Please use a different Postgres user.
    • '; dieout(''); } - $perms = pg_fetch_result($res, 0, 0); - - $SQL = "SELECT 1 FROM pg_catalog.pg_user WHERE usename = " . $this->addQuotes($wgDBuser); + print "
  • Creating user $wgDBuser..."; + $safepass = $this->addQuotes($wgDBpassword); + $SQL = "CREATE USER $safeuser NOCREATEDB PASSWORD $safepass"; + $this->doQuery($SQL); + print "OK
    • \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 "
  • User \"$wgDBuser\" already exists, skipping account creation.
    • "; + print "
  • Database \"$wgDBname\" already exists, skipping database creation.
    • "; } else { - if ($perms != 1 and $perms != 3) { - print "
  • ERROR: the user \"$wgDBsuperuser\" cannot create other users. "; + if ($perms < 2) { + print "
  • ERROR: the user \"$wgDBsuperuser\" cannot create databases. "; print 'Please use a different Postgres user.
    • '; dieout(''); } - print "
  • Creating user $wgDBuser..."; - $safepass = $this->addQuotes($wgDBpassword); - $SQL = "CREATE USER $safeuser NOCREATEDB PASSWORD $safepass"; + print "
  • Creating database $wgDBname..."; + $safename = $this->quote_ident($wgDBname); + $SQL = "CREATE DATABASE $safename OWNER $safeuser "; $this->doQuery($SQL); print "OK
    • \n"; + // Hopefully tsearch2 and plpgsql are in template1... } - ## 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 "
  • Database \"$wgDBname\" already exists, skipping database creation.
    • "; - } - else { - if ($perms < 2) { - print "
  • ERROR: the user \"$wgDBsuperuser\" cannot create databases. "; - print 'Please use a different Postgres user.
    • '; - dieout(''); - } - print "
  • Creating database $wgDBname..."; - $safename = $this->quote_ident($wgDBname); - $SQL = "CREATE DATABASE $safename OWNER $safeuser "; - $this->doQuery($SQL); - print "OK
    • \n"; - ## Hopefully tsearch2 and plpgsql are in template1... - } - ## Reconnect to check out tsearch2 rights for this user - print "
  • 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 "FAILED TO CONNECT!
    • "; - dieout(""); - } - print "OK\n"; + // Reconnect to check out tsearch2 rights for this user + print "
  • Connecting to \"$wgDBname\" as superuser \"$wgDBsuperuser\" to check rights..."; + + $hstring=""; + if ($this->mServer!=false && $this->mServer!="") { + $hstring="host=$this->mServer "; + } + if ($this->mPort!=false && $this->mPort!="") { + $hstring .= "port=$this->mPort "; } - ## Tsearch2 checks + @$this->mConn = pg_connect("$hstring dbname=$wgDBname user=$wgDBsuperuser password=$password"); + if ( $this->mConn == false ) { + print "FAILED TO CONNECT!
    • "; + dieout(""); + } + print "OK\n"; + } + + if ($this->numeric_version < 8.3) { + // Tsearch2 checks print "
  • Checking that tsearch2 is installed in the database \"$wgDBname\"..."; if (! $this->tableExists("pg_ts_cfg", $wgDBts2schema)) { print "FAILED. tsearch2 must be installed in the database \"$wgDBname\"."; print "Please see this article"; print " for instructions or ask on #postgresql on irc.freenode.net
    • \n"; dieout(""); - } + } print "OK\n"; print "
  • Ensuring that user \"$wgDBuser\" has select rights on the tsearch2 tables..."; foreach (array('cfg','cfgmap','dict','parser') as $table) { @@ -245,173 +300,159 @@ class DatabasePostgres extends Database { $this->doQuery($SQL); } print "OK
    • \n"; + } - - ## Setup the schema for this user if needed - $result = $this->schemaExists($wgDBmwschema); - $safeschema = $this->quote_ident($wgDBmwschema); + // Setup the schema for this user if needed + $result = $this->schemaExists($wgDBmwschema); + $safeschema = $this->quote_ident($wgDBmwschema); + if (!$result) { + print "
  • Creating schema $wgDBmwschema ..."; + $result = $this->doQuery("CREATE SCHEMA $safeschema AUTHORIZATION $safeuser"); if (!$result) { - print "
  • Creating schema $wgDBmwschema ..."; - $result = $this->doQuery("CREATE SCHEMA $safeschema AUTHORIZATION $safeuser"); - if (!$result) { - print "FAILED.
    • \n"; - dieout(""); - } - print "OK\n"; + print "FAILED.\n"; + dieout(""); } - else { - print "
  • 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 "FAILED. Could not set rights for the user.
    • \n"; - dieout(""); - } - $this->doQuery("SET search_path = $safeschema"); - $rows = $this->numRows($res); - while ($rows) { - $rows--; - $this->doQuery(pg_fetch_result($res, $rows, 0)); - } - print "OK"; + print "OK\n"; + } + else { + print "
  • 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 "FAILED. Could not set rights for the user.
    • \n"; + dieout(""); } + $this->doQuery("SET search_path = $safeschema"); + $rows = $this->numRows($res); + while ($rows) { + $rows--; + $this->doQuery(pg_fetch_result($res, $rows, 0)); + } + print "OK"; + } - $wgDBsuperuser = ''; - return true; ## Reconnect as regular user + // Install plpgsql if needed + $this->setup_plpgsql(); - } ## end superuser + $wgDBsuperuser = ''; + return true; // Reconnect as regular user - if (!defined('POSTGRES_SEARCHPATH')) { + } // end superuser - ## Do we have the basic tsearch2 table? - print "
  • Checking for tsearch2 in the schema \"$wgDBts2schema\"..."; - if (! $this->tableExists("pg_ts_dict", $wgDBts2schema)) { - print "FAILED. Make sure tsearch2 is installed. See this article"; - print " for instructions.
    • \n"; - dieout(""); - } - print "OK\n"; + if (!defined('POSTGRES_SEARCHPATH')) { - ## Does this user have the rights to the tsearch2 tables? - $ctype = pg_fetch_result($this->doQuery("SHOW lc_ctype"),0,0); - print "
  • 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 "FAILED to access pg_ts_$tname. Make sure that the user ". - "\"$wgDBuser\" has SELECT access to all four tsearch2 tables
    • \n"; + if ($this->numeric_version < 8.3) { + // Do we have the basic tsearch2 table? + print "
  • Checking for tsearch2 in the schema \"$wgDBts2schema\"..."; + if (! $this->tableExists("pg_ts_dict", $wgDBts2schema)) { + print "FAILED. Make sure tsearch2 is installed. See this article"; + print " for instructions.
    • \n"; dieout(""); } - } - $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"; - $res = $this->doQuery($SQL); - error_reporting( E_ALL ); - if (!$res) { - print "FAILED. Could not determine the tsearch2 locale information\n"; - dieout(""); - } - print "OK"; + print "OK\n"; - ## Will the current locale work? Can we force it to? - print "
  • Verifying tsearch2 locale with $ctype..."; - $rows = $this->numRows($res); - $resetlocale = 0; - if (!$rows) { - print "not found
    • \n"; - print "
  • Attempting to set default tsearch2 locale to \"$ctype\"..."; - $resetlocale = 1; - } - else { - $tsname = pg_fetch_result($res, 0, 0); - if ($tsname != 'default') { - print "not set to default ($tsname)"; - print "
  • Attempting to change tsearch2 default locale to \"$ctype\"..."; - $resetlocale = 1; + // Does this user have the rights to the tsearch2 tables? + $ctype = pg_fetch_result($this->doQuery("SHOW lc_ctype"),0,0); + print "
  • Checking tsearch2 permissions..."; + // Let's check all four, just to be safe + error_reporting( 0 ); + $ts2tables = array('cfg','cfgmap','dict','parser'); + $safetsschema = $this->quote_ident($wgDBts2schema); + foreach ( $ts2tables AS $tname ) { + $SQL = "SELECT count(*) FROM $safetsschema.pg_ts_$tname"; + $res = $this->doQuery($SQL); + if (!$res) { + print "FAILED to access pg_ts_$tname. Make sure that the user ". + "\"$wgDBuser\" has SELECT access to all four tsearch2 tables
    • \n"; + dieout(""); + } } - } - if ($resetlocale) { - $SQL = "UPDATE $wgDBts2schema.pg_ts_cfg SET locale = '$ctype' WHERE ts_name = 'default'"; + $SQL = "SELECT ts_name FROM $safetsschema.pg_ts_cfg WHERE locale = '$ctype'"; + $SQL .= " ORDER BY CASE WHEN ts_name <> 'default' THEN 1 ELSE 0 END"; $res = $this->doQuery($SQL); + error_reporting( E_ALL ); if (!$res) { - print "FAILED. "; - print "Please make sure that the locale in pg_ts_cfg for \"default\" is set to \"$ctype\"\n"; + print "FAILED. Could not determine the tsearch2 locale information\n"; dieout(""); } print "OK"; - } - ## Final test: try out a simple tsearch2 query - $SQL = "SELECT $wgDBts2schema.to_tsvector('default','MediaWiki tsearch2 testing')"; - $res = $this->doQuery($SQL); - if (!$res) { - print "FAILED. Specifically, \"$SQL\" did not work."; - dieout(""); - } - print "OK"; - - ## Do we have plpgsql installed? - print "
  • Checking for Pl/Pgsql ..."; - $SQL = "SELECT 1 FROM pg_catalog.pg_language WHERE lanname = 'plpgsql'"; - $rows = $this->numRows($this->doQuery($SQL)); - if ($rows < 1) { - // 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 "FAILED. You need to install the language plpgsql in the database $wgDBname
    • "; + // Will the current locale work? Can we force it to? + print "
  • Verifying tsearch2 locale with $ctype..."; + $rows = $this->numRows($res); + $resetlocale = 0; + if (!$rows) { + print "not found
    • \n"; + print "
  • Attempting to set default tsearch2 locale to \"$ctype\"..."; + $resetlocale = 1; + } + else { + $tsname = pg_fetch_result($res, 0, 0); + if ($tsname != 'default') { + print "not set to default ($tsname)"; + print "
  • Attempting to change tsearch2 default locale to \"$ctype\"..."; + $resetlocale = 1; + } + } + if ($resetlocale) { + $SQL = "UPDATE $safetsschema.pg_ts_cfg SET locale = '$ctype' WHERE ts_name = 'default'"; + $res = $this->doQuery($SQL); + if (!$res) { + print "FAILED. "; + print "Please make sure that the locale in pg_ts_cfg for \"default\" is set to \"$ctype\"
    • \n"; dieout(""); } + print "OK"; } - else { - print "FAILED. You need to install the language plpgsql in the database $wgDBname"; + + // Final test: try out a simple tsearch2 query + $SQL = "SELECT $safetsschema.to_tsvector('default','MediaWiki tsearch2 testing')"; + $res = $this->doQuery($SQL); + if (!$res) { + print "FAILED. Specifically, \"$SQL\" did not work."; dieout(""); } + print "OK"; } - print "OK\n"; - ## Does the schema already exist? Who owns it? + // Install plpgsql if needed + $this->setup_plpgsql(); + + // Does the schema already exist? Who owns it? $result = $this->schemaExists($wgDBmwschema); if (!$result) { print "
  • Creating schema $wgDBmwschema ..."; error_reporting( 0 ); - $result = $this->doQuery("CREATE SCHEMA $wgDBmwschema"); + $safeschema = $this->quote_ident($wgDBmwschema); + $result = $this->doQuery("CREATE SCHEMA $safeschema"); error_reporting( E_ALL ); if (!$result) { print "FAILED. 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.
    • \n"; + "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.\n"; dieout(""); } print "OK\n"; } - else if ($result != $user) { - print "
  • Schema \"$wgDBmwschema\" exists but is not owned by \"$user\". Not ideal.
    • \n"; + else if ($result != $wgDBuser) { + print "
  • Schema \"$wgDBmwschema\" exists but is not owned by \"$wgDBuser\". Not ideal.
    • \n"; } else { - print "
  • Schema \"$wgDBmwschema\" exists and is owned by \"$user\". Excellent.
    • \n"; + print "
  • Schema \"$wgDBmwschema\" exists and is owned by \"$wgDBuser\". Excellent.
    • \n"; } - ## Always return GMT time to accomodate the existing integer-based timestamp assumption - print "
  • Setting the timezone to GMT for user \"$user\" ..."; + // Always return GMT time to accomodate the existing integer-based timestamp assumption + print "
  • Setting the timezone to GMT for user \"$wgDBuser\" ..."; $SQL = "ALTER USER $safeuser SET timezone = 'GMT'"; $result = pg_query($this->mConn, $SQL); if (!$result) { @@ -419,7 +460,7 @@ class DatabasePostgres extends Database { dieout(""); } print "OK
    • \n"; - ## Set for the rest of this session + // Set for the rest of this session $SQL = "SET timezone = 'GMT'"; $result = pg_query($this->mConn, $SQL); if (!$result) { @@ -427,7 +468,7 @@ class DatabasePostgres extends Database { dieout(""); } - print "
  • Setting the datestyle to ISO, YMD for user \"$user\" ..."; + print "
  • Setting the datestyle to ISO, YMD for user \"$wgDBuser\" ..."; $SQL = "ALTER USER $safeuser SET datestyle = 'ISO, YMD'"; $result = pg_query($this->mConn, $SQL); if (!$result) { @@ -435,7 +476,7 @@ class DatabasePostgres extends Database { dieout(""); } print "OK
    • \n"; - ## Set for the rest of this session + // Set for the rest of this session $SQL = "SET datestyle = 'ISO, YMD'"; $result = pg_query($this->mConn, $SQL); if (!$result) { @@ -443,8 +484,8 @@ class DatabasePostgres extends Database { dieout(""); } - ## Fix up the search paths if needed - print "
  • Setting the search path for user \"$user\" ..."; + // Fix up the search paths if needed + print "
  • Setting the search path for user \"$wgDBuser\" ..."; $path = $this->quote_ident($wgDBmwschema); if ($wgDBts2schema !== $wgDBmwschema) $path .= ", ". $this->quote_ident($wgDBts2schema); @@ -457,7 +498,7 @@ class DatabasePostgres extends Database { dieout(""); } print "OK
    • \n"; - ## Set for the rest of this session + // Set for the rest of this session $SQL = "SET search_path = $path"; $result = pg_query($this->mConn, $SQL); if (!$result) { @@ -465,17 +506,39 @@ class DatabasePostgres extends Database { dieout(""); } 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; + + function setup_plpgsql() { + print "
  • Checking for Pl/Pgsql ..."; + $SQL = "SELECT 1 FROM pg_catalog.pg_language WHERE lanname = 'plpgsql'"; + $rows = $this->numRows($this->doQuery($SQL)); + if ($rows < 1) { + // 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) { + $olde = error_reporting(0); + error_reporting($olde - E_WARNING); + $result = $this->doQuery("CREATE LANGUAGE plpgsql"); + error_reporting($olde); + if (!$result) { + print "FAILED. You need to install the language plpgsql in the database $wgDBname
    • "; + dieout(""); + } + } + else { + print "FAILED. You need to install the language plpgsql in the database $wgDBname"; + dieout(""); + } + } + print "OK\n"; } + /** * Closes a database connection, if it is open * Returns success, true if already closed @@ -490,6 +553,9 @@ class DatabasePostgres extends Database { } function doQuery( $sql ) { + if (function_exists('mb_convert_encoding')) { + return $this->mLastResult=pg_query( $this->mConn , mb_convert_encoding($sql,'UTF-8') ); + } return $this->mLastResult=pg_query( $this->mConn , $sql); } @@ -498,18 +564,24 @@ class DatabasePostgres extends Database { } function freeResult( $res ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } if ( !@pg_free_result( $res ) ) { throw new DBUnexpectedError($this, "Unable to free Postgres result\n" ); } } function fetchObject( $res ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } @$row = pg_fetch_object( $res ); # FIXME: HACK HACK HACK HACK debug # TODO: # hashar : not sure if the following test really trigger if the object - # fetching failled. + # fetching failed. if( pg_last_error($this->mConn) ) { throw new DBUnexpectedError($this, 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) ); } @@ -517,6 +589,9 @@ class DatabasePostgres extends Database { } function fetchRow( $res ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } @$row = pg_fetch_array( $res ); if( pg_last_error($this->mConn) ) { throw new DBUnexpectedError($this, 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) ); @@ -525,14 +600,27 @@ class DatabasePostgres extends Database { } function numRows( $res ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } @$n = pg_num_rows( $res ); if( pg_last_error($this->mConn) ) { throw new DBUnexpectedError($this, 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) ); } return $n; } - function numFields( $res ) { return pg_num_fields( $res ); } - function fieldName( $res, $n ) { return pg_field_name( $res, $n ); } + function numFields( $res ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } + return pg_num_fields( $res ); + } + function fieldName( $res, $n ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } + return pg_field_name( $res, $n ); + } /** * This must be called after nextSequenceVal @@ -541,7 +629,13 @@ class DatabasePostgres extends Database { return $this->mInsertId; } - function dataSeek( $res, $row ) { return pg_result_seek( $res, $row ); } + function dataSeek( $res, $row ) { + if ( $res instanceof ResultWrapper ) { + $res = $res->result; + } + return pg_result_seek( $res, $row ); + } + function lastError() { if ( $this->mConn ) { return pg_last_error(); @@ -555,6 +649,9 @@ class DatabasePostgres extends Database { } function affectedRows() { + if( !isset( $this->mLastResult ) or ! $this->mLastResult ) + return 0; + return pg_affected_rows( $this->mLastResult ); } @@ -565,7 +662,7 @@ class DatabasePostgres extends Database { * 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 ); @@ -592,13 +689,9 @@ class DatabasePostgres extends Database { if ( !$res ) { return NULL; } - while ( $row = $this->fetchObject( $res ) ) { if ( $row->indexname == $index ) { return $row; - - // BUG: !!!! This code needs to be synced up with database.php - } } return false; @@ -616,33 +709,87 @@ class DatabasePostgres extends Database { } - function insert( $table, $a, $fname = 'Database::insert', $options = array() ) { - # Postgres doesn't support options - # We have a go at faking one of them - # TODO: DELAYED, LOW_PRIORITY + /** + * INSERT wrapper, inserts an array into a table + * + * $args may be a single associative array, or an array of these with numeric keys, + * for multi-row insert (Postgres version 8.2 and above only). + * + * @param array $table String: Name of the table to insert to. + * @param array $args Array: Items to insert into the table. + * @param array $fname String: Name of the function, for profiling + * @param mixed $options String or Array. Valid options: IGNORE + * + * @return bool Success of insert operation. IGNORE always returns true. + */ + function insert( $table, $args, $fname = 'DatabasePostgres::insert', $options = array() ) { + global $wgDBversion; + + if ( !count( $args ) ) { + return true; + } - if ( !is_array($options)) - $options = array($options); + $table = $this->tableName( $table ); + if (! isset( $wgDBversion ) ) { + $this->getServerVersion(); + $wgDBversion = $this->numeric_version; + } - if ( in_array( 'IGNORE', $options ) ) - $oldIgnore = $this->ignoreErrors( true ); + if ( !is_array( $options ) ) + $options = array( $options ); - # IGNORE is performed using single-row inserts, ignoring errors in each - # FIXME: need some way to distiguish between key collision and other types of error - $oldIgnore = $this->ignoreErrors( true ); - if ( !is_array( reset( $a ) ) ) { - $a = array( $a ); + if ( isset( $args[0] ) && is_array( $args[0] ) ) { + $multi = true; + $keys = array_keys( $args[0] ); + } + else { + $multi = false; + $keys = array_keys( $args ); + } + + $ignore = in_array( 'IGNORE', $options ) ? 1 : 0; + if ( $ignore ) + $olde = error_reporting( 0 ); + + $sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES '; + + if ( $multi ) { + if ( $wgDBversion >= 8.2 ) { + $first = true; + foreach ( $args as $row ) { + if ( $first ) { + $first = false; + } else { + $sql .= ','; + } + $sql .= '(' . $this->makeList( $row ) . ')'; + } + $res = (bool)$this->query( $sql, $fname, $ignore ); + } + else { + $res = true; + $origsql = $sql; + foreach ( $args as $row ) { + $tempsql = $origsql; + $tempsql .= '(' . $this->makeList( $row ) . ')'; + $tempres = (bool)$this->query( $tempsql, $fname, $ignore ); + if (! $tempres) + $res = false; + } + } + } + else { + $sql .= '(' . $this->makeList( $args ) . ')'; + $res = (bool)$this->query( $sql, $fname, $ignore ); } - foreach ( $a as $row ) { - parent::insert( $table, $row, $fname, array() ); + + if ( $ignore ) { + $olde = error_reporting( $olde ); + return true; } - $this->ignoreErrors( $oldIgnore ); - $retVal = true; - if ( in_array( 'IGNORE', $options ) ) - $this->ignoreErrors( $oldIgnore ); + return $res; - return $retVal; } function tableName( $name ) { @@ -669,6 +816,18 @@ class DatabasePostgres extends Database { return $this->mInsertId; } + /** + * Return the current value of a sequence. Assumes it has ben nextval'ed in this session. + */ + function currentSequenceValue( $seqName ) { + $safeseq = preg_replace( "/'/", "''", $seqName ); + $res = $this->query( "SELECT currval('$safeseq')" ); + $row = $this->fetchRow( $res ); + $currval = $row[0]; + $this->freeResult( $res ); + return $currval; + } + /** * Postgres does not have a "USE INDEX" clause, so return an empty string */ @@ -773,7 +932,7 @@ class DatabasePostgres extends Database { return ''; } - function limitResult($sql, $limit,$offset=false) { + function limitResult($sql, $limit, $offset=false) { return "$sql LIMIT $limit ".(is_numeric($offset)?" OFFSET {$offset} ":""); } @@ -807,9 +966,9 @@ class DatabasePostgres extends Database { function reportQueryError( $error, $errno, $sql, $fname, $tempIgnore = false ) { - # Ignore errors during error handling to avoid infinite recursion + // Ignore errors during error handling to avoid infinite recursion $ignore = $this->ignoreErrors( true ); - ++$this->mErrorCount; + $this->mErrorCount++; if ($ignore || $tempIgnore) { wfDebug("SQL ERROR (ignored): $error\n"); @@ -827,7 +986,7 @@ class DatabasePostgres extends Database { /** * @return string wikitext of a link to the server software's web site */ - function getSoftwareLink() { + function getSoftwareLink() { return "[http://www.postgresql.org/ PostgreSQL]"; } @@ -846,7 +1005,7 @@ class DatabasePostgres extends Database { /** - * Query whether a given relation exists (in the given schema, or the + * Query whether a given relation exists (in the given schema, or the * default mw one if not given) */ function relationExists( $table, $types, $schema = false ) { @@ -861,26 +1020,26 @@ class DatabasePostgres extends Database { . "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; + $count = $res ? $res->numRows() : 0; if ($res) $this->freeResult( $res ); - return $count; + return $count ? true : false; } /* - * For backward compatibility, this function checks both tables and + * 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; + global $wgDBmwschema; $q = <<addQuotes($wgDBmwschema), $this->addQuotes($table), $this->addQuotes($trigger))); - $row = $this->fetchRow($res); - $exists = !!$row; + if (!$res) + return NULL; + $rows = $res->numRows(); $this->freeResult($res); - return $exists; + return $rows; } function ruleExists($table, $rule) { - global $wgDBmwschema; + global $wgDBmwschema; $exists = $this->selectField("pg_rules", "rulename", array( "rulename" => $rule, "tablename" => $table, @@ -907,6 +1067,21 @@ END; 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 = $res->numRows(); + $this->freeResult($res); + return $rows; + } + /** * Query whether a given schema exists. Returns the name of the owner */ @@ -915,7 +1090,12 @@ END; $SQL = "SELECT rolname FROM pg_catalog.pg_namespace n, pg_catalog.pg_roles r " ."WHERE n.nspowner=r.oid AND n.nspname = '$eschema'"; $res = $this->query( $SQL ); - $owner = $res ? pg_num_rows($res) ? pg_fetch_result($res, 0, 0) : false : false; + if ( $res && $res->numRows() ) { + $row = $res->fetchObject(); + $owner = $row->rolname; + } else { + $owner = false; + } if ($res) $this->freeResult($res); return $owner; @@ -933,7 +1113,7 @@ END; . "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, $fname ); - $count = $res ? pg_num_rows($res) : 0; + $count = $res ? $res->numRows() : 0; if ($res) $this->freeResult( $res ); return $count; @@ -963,33 +1143,24 @@ END; function setup_database() { 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"; + // 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 = "mediawiki_test_table"; + $safeschema = $this->quote_ident($wgDBmwschema); if ($this->tableExists($ctest, $wgDBmwschema)) { - $this->doQuery("DROP TABLE $wgDBmwschema.$ctest"); + $this->doQuery("DROP TABLE $safeschema.$ctest"); } - $SQL = "CREATE TABLE $wgDBmwschema.$ctest(a int)"; - error_reporting( 0 ); + $SQL = "CREATE TABLE $safeschema.$ctest(a int)"; + $olde = error_reporting( 0 ); $res = $this->doQuery($SQL); - error_reporting( E_ALL ); + error_reporting( $olde ); if (!$res) { print "FAILED. Make sure that the user \"$wgDBuser\" can write to the schema \"$wgDBmwschema\"\n"; dieout(""); } - $this->doQuery("DROP TABLE $wgDBmwschema.mw_test_table"); + $this->doQuery("DROP TABLE $safeschema.$ctest"); - 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)"); - } + $res = dbsource( "../maintenance/postgres/tables.sql", $this); ## Update version information $mwv = $this->addQuotes($wgVersion); @@ -1027,10 +1198,14 @@ END; $this->doQuery("COMMIT"); } - function encodeBlob($b) { - return array('bytea',pg_escape_bytea($b)); + function encodeBlob( $b ) { + return new Blob ( pg_escape_bytea( $b ) ) ; } - function decodeBlob($b) { + + function decodeBlob( $b ) { + if ($b instanceof Blob) { + $b = $b->fetch(); + } return pg_unescape_bytea( $b ); } @@ -1041,11 +1216,10 @@ END; function addQuotes( $s ) { if ( is_null( $s ) ) { return 'NULL'; - } else if (is_array( $s )) { ## Assume it is bytea data - return "E'$s[1]'"; + } else if ($s instanceof Blob) { + return "'".$s->fetch($s)."'"; } return "'" . pg_escape_string($s) . "'"; - // Unreachable: return "E'" . pg_escape_string($s) . "'"; } function quote_ident( $s ) { @@ -1058,8 +1232,33 @@ END; } /** - * Returns an optional USE INDEX clause to go after the table, and a - * string to go at the end of the query + * Postgres specific version of replaceVars. + * Calls the parent version in Database.php + * + * @private + * + * @param string $com SQL string, read from a stream (usually tables.sql) + * + * @return string SQL string + */ + protected function replaceVars( $ins ) { + + $ins = parent::replaceVars( $ins ); + + if ($this->numeric_version >= 8.3) { + // Thanks for not providing backwards-compatibility, 8.3 + $ins = preg_replace( "/to_tsvector\s*\(\s*'default'\s*,/", 'to_tsvector(', $ins ); + } + + if ($this->numeric_version <= 8.1) { // Our minimum version + $ins = str_replace( 'USING gin', 'USING gist', $ins ); + } + + return $ins; + } + + /** + * Various select options * * @private * @@ -1069,7 +1268,7 @@ END; */ function makeSelectOptions( $options ) { $preLimitTail = $postLimitTail = ''; - $startOpts = ''; + $startOpts = $useIndex = ''; $noKeyOptions = array(); foreach ( $options as $key => $option ) { @@ -1079,29 +1278,24 @@ END; } if ( isset( $options['GROUP BY'] ) ) $preLimitTail .= " GROUP BY " . $options['GROUP BY']; + if ( isset( $options['HAVING'] ) ) $preLimitTail .= " HAVING {$options['HAVING']}"; 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'] + // 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( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT'; - if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) { - $useIndex = $this->useIndexClause( $options['USE INDEX'] ); - } else { - $useIndex = ''; - } - return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail ); } public function setTimeout( $timeout ) { - // @todo no-op + // @todo fixme no-op } function ping() { @@ -1109,7 +1303,28 @@ END; return true; } + /** + * How lagged is this slave? + * + */ + public function getLag() { + # Not implemented for PostgreSQL + return false; + } -} // end DatabasePostgres class + function setFakeSlaveLag() {} + function setFakeMaster() {} -?> + function getDBname() { + return $this->mDBname; + } + + function getServer() { + return $this->mServer; + } + + function buildConcat( $stringList ) { + return implode( ' || ', $stringList ); + } + +} // end DatabasePostgres class