X-Git-Url: https://git.heureux-cyclage.org/?a=blobdiff_plain;f=includes%2FDatabasePostgres.php;h=48c99dbd8cd2a36e56df1b9b81e2388eddb3edfa;hb=08016be98c3f4dfae820039d871c8afa0150151c;hp=3fe8144f0023983be887121e5e8ac71aed70227f;hpb=3b4998082177d7f485478c48efecbdb3070c7ea0;p=lhc%2Fweb%2Fwiklou.git diff --git a/includes/DatabasePostgres.php b/includes/DatabasePostgres.php index 3fe8144f00..48c99dbd8c 100644 --- a/includes/DatabasePostgres.php +++ b/includes/DatabasePostgres.php @@ -7,8 +7,8 @@ * than MySQL ones, some of them should be moved to parent * Database class. * + * @addtogroup Database */ - class PostgresField { private $name, $tablename, $type, $nullable, $max_length; @@ -16,7 +16,13 @@ class PostgresField { global $wgDBmwschema; $q = <<numRows($res = $this->doQuery($SQL)); + } static function newFromParams( $server, $user, $password, $dbName, $failFunction = false, $flags = 0) { @@ -126,7 +146,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 +159,6 @@ class DatabasePostgres extends Database { $hstring .= "port=$port "; } - error_reporting( E_ALL ); @$this->mConn = pg_connect("$hstring dbname=$dbName user=$user password=$password"); @@ -151,288 +170,6 @@ 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(""); - } - 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(''); - } - $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(''); - } - 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 "
  • 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"; - } - - ## 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) { - $SQL = "GRANT SELECT ON pg_ts_$table TO $safeuser"; - $this->doQuery($SQL); - } - print "OK
    • \n"; - - - ## 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 "FAILED.
    • \n"; - dieout(""); - } - 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 - - } ## end superuser - - if (!defined('POSTGRES_SEARCHPATH')) { - - ## 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"; - - ## 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"; - 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"; - - ## 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 $wgDBts2schema.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"; - } - - ## 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
    • "; - dieout(""); - } - } - else { - print "FAILED. You need to install the language plpgsql in the database $wgDBname"; - dieout(""); - } - } - print "OK\n"; - - ## 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"); - 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"; - dieout(""); - } - print "OK\n"; - } - else if ($result != $user) { - print "
  • Schema \"$wgDBmwschema\" exists but is not owned by \"$user\". Not ideal.
    • \n"; - } - else { - print "
  • Schema \"$wgDBmwschema\" exists and is owned by \"$user\". Excellent.
    • \n"; - } - - ## Fix up the search paths if needed - print "
  • Setting the search path for user \"$user\" ..."; - $path = $this->quote_ident($wgDBmwschema); - if ($wgDBts2schema !== $wgDBmwschema) - $path .= ", ". $this->quote_ident($wgDBts2schema); - if ($wgDBmwschema !== 'public' and $wgDBts2schema !== 'public') - $path .= ", public"; - $SQL = "ALTER USER $safeuser SET search_path = $path"; - $result = pg_query($this->mConn, $SQL); - if (!$result) { - print "FAILED.
    • \n"; - dieout(""); - } - print "OK\n"; - ## Set for the rest of this session - $SQL = "SET search_path = $path"; - $result = pg_query($this->mConn, $SQL); - if (!$result) { - print "
  • Failed to set search_path
    • \n"; - dieout(""); - } - define( "POSTGRES_SEARCHPATH", $path ); - }} global $wgCommandLineMode; ## If called from the command-line (e.g. importDump), only show errors @@ -440,9 +177,353 @@ class DatabasePostgres extends Database { $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(''); + } + $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(''); + } + 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 "
  • 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..."; + + $hstring=""; + if ($this->mServer!=false && $this->mServer!="") { + $hstring="host=$this->mServer "; + } + if ($this->mPort!=false && $this->mPort!="") { + $hstring .= "port=$this->mPort "; + } + + @$this->mConn = pg_connect("$hstring dbname=$wgDBname user=$wgDBsuperuser password=$password"); + if ( $this->mConn == false ) { + print "FAILED TO CONNECT!
    • "; + dieout(""); + } + print "OK\n"; + } + + ## 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) { + $SQL = "GRANT SELECT ON pg_ts_$table TO $safeuser"; + $this->doQuery($SQL); + } + print "OK
    • \n"; + + + ## 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 "FAILED.
    • \n"; + dieout(""); + } + 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 + + } // end superuser + + if (!defined('POSTGRES_SEARCHPATH')) { + + ## 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"; + + ## 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(""); + } + } + $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. Could not determine the tsearch2 locale information\n"; + dieout(""); + } + print "OK"; + + ## 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"; + } + + ## 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"; + + ## 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) { + $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"; + + ## Does the schema already exist? Who owns it? + $result = $this->schemaExists($wgDBmwschema); + if (!$result) { + print "
  • Creating schema $wgDBmwschema ..."; + error_reporting( 0 ); + $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"; + dieout(""); + } + print "OK\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 \"$wgDBuser\". Excellent.
    • \n"; + } + + ## 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) { + print "FAILED.
    • \n"; + dieout(""); + } + print "OK\n"; + ## Set for the rest of this session + $SQL = "SET timezone = 'GMT'"; + $result = pg_query($this->mConn, $SQL); + if (!$result) { + print "
  • Failed to set timezone
    • \n"; + dieout(""); + } + + 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) { + print "FAILED.
    • \n"; + dieout(""); + } + print "OK\n"; + ## Set for the rest of this session + $SQL = "SET datestyle = 'ISO, YMD'"; + $result = pg_query($this->mConn, $SQL); + if (!$result) { + print "
  • Failed to set datestyle
    • \n"; + dieout(""); + } + + ## 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); + if ($wgDBmwschema !== 'public' and $wgDBts2schema !== 'public') + $path .= ", public"; + $SQL = "ALTER USER $safeuser SET search_path = $path"; + $result = pg_query($this->mConn, $SQL); + if (!$result) { + print "FAILED.
    • \n"; + dieout(""); + } + print "OK\n"; + ## Set for the rest of this session + $SQL = "SET search_path = $path"; + $result = pg_query($this->mConn, $SQL); + if (!$result) { + print "
  • Failed to set search_path
    • \n"; + dieout(""); + } + define( "POSTGRES_SEARCHPATH", $path ); + } + } + + /** * Closes a database connection, if it is open * Returns success, true if already closed @@ -457,6 +538,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); } @@ -465,18 +549,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) ) ); } @@ -484,6 +574,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) ) ); @@ -492,14 +585,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 @@ -508,7 +614,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(); @@ -522,9 +634,36 @@ class DatabasePostgres extends Database { } function affectedRows() { + if( !isset( $this->mLastResult ) or ! $this->mLastResult ) + return 0; + return pg_affected_rows( $this->mLastResult ); } + /** + * 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 @@ -535,13 +674,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; @@ -559,33 +694,83 @@ 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 ( !is_array($options)) - $options = array($options); + $table = $this->tableName( $table ); + if (! isset( $wgDBversion ) ) { + $this->getServerVersion(); + $wgDBversion = $this->numeric_version; + } + + if ( !is_array( $options ) ) + $options = array( $options ); + + 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 ); - if ( in_array( 'IGNORE', $options ) ) - $oldIgnore = $this->ignoreErrors( true ); + $sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES '; - # 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 ( $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 ) { @@ -612,6 +797,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 */ @@ -804,10 +1001,10 @@ 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; } /* @@ -823,7 +1020,7 @@ class DatabasePostgres extends Database { } 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, @@ -850,6 +1048,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 */ @@ -858,7 +1071,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; @@ -876,7 +1094,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; @@ -908,14 +1126,20 @@ END; ## Make sure that we can write to the correct schema ## If not, Postgres will happily and silently go to the next search_path item - $SQL = "CREATE TABLE $wgDBmwschema.mw_test_table(a int)"; - error_reporting( 0 ); + $ctest = "mw_test_table"; + $safeschema = $this->quote_ident($wgDBmwschema); + if ($this->tableExists($ctest, $wgDBmwschema)) { + $this->doQuery("DROP TABLE $safeschema.$ctest"); + } + $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 $safeschema.mw_test_table"); dbsource( "../maintenance/postgres/tables.sql", $this); @@ -961,12 +1185,18 @@ END; $this->query("$SQL $matches[1],$matches[2])"); } print " (table interwiki successfully populated)...\n"; + + $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 ); } @@ -977,11 +1207,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 ) { @@ -994,8 +1223,7 @@ END; } /** - * Returns an optional USE INDEX clause to go after the table, and a - * string to go at the end of the query + * Various select options * * @private * @@ -1005,7 +1233,7 @@ END; */ function makeSelectOptions( $options ) { $preLimitTail = $postLimitTail = ''; - $startOpts = ''; + $startOpts = $useIndex = ''; $noKeyOptions = array(); foreach ( $options as $key => $option ) { @@ -1015,6 +1243,7 @@ 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'])) { @@ -1025,27 +1254,33 @@ END; 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'] ) ) { - $useIndex = $this->useIndexClause( $options['USE INDEX'] ); - } else { - $useIndex = ''; - } + if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT'; return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail ); } public function setTimeout( $timeout ) { - /// @fixme no-op + // @todo fixme no-op } function ping() { wfDebug( "Function ping() not written for DatabasePostgres.php yet"); return true; } + + /** + * How lagged is this slave? + * + */ + public function getLag() { + # Not implemented for PostgreSQL + return false; + } + function buildConcat( $stringList ) { + return implode( ' || ', $stringList ); + } } // end DatabasePostgres class -?> +