From d4b633bfc50188f36e3c4a8b2b99c3a0e6a7f12e Mon Sep 17 00:00:00 2001 From: marc tobias Date: Sun, 24 Feb 2019 16:14:36 +0100 Subject: [PATCH] replace database abstraction DB with PDO --- VAGRANT.md | 2 +- docs/admin/Migration.md | 12 ++ lib/DatabaseError.php | 12 +- lib/Geocode.php | 8 +- lib/PlaceLookup.php | 9 +- lib/SearchDescription.php | 14 +- lib/Status.php | 15 +- lib/TokenList.php | 2 +- lib/cmd.php | 6 +- lib/db.php | 199 +++++++++++++++++++---- lib/init-website.php | 5 +- lib/lib.php | 2 +- lib/log.php | 22 ++- lib/setup/AddressLevelParser.php | 14 +- lib/setup/SetupClass.php | 90 ++++++---- lib/setup_functions.php | 12 +- settings/defaults.php | 2 +- test/bdd/api/status/failures.feature | 4 +- test/bdd/environment.py | 14 +- test/bdd/steps/queries.py | 2 +- test/php/Nominatim/DBTest.php | 68 ++++++++ test/php/Nominatim/DatabaseErrorTest.php | 15 +- test/php/Nominatim/StatusTest.php | 33 ++-- test/php/Nominatim/TokenListTest.php | 13 +- utils/export.php | 15 +- utils/importWikipedia.php | 11 +- utils/query.php | 4 +- utils/update.php | 41 +++-- utils/warm.php | 3 +- vagrant/Install-on-Centos-7.sh | 6 +- vagrant/Install-on-Ubuntu-16.sh | 6 +- vagrant/Install-on-Ubuntu-18-nginx.sh | 2 +- vagrant/Install-on-Ubuntu-18.sh | 6 +- vagrant/install-on-travis-ci.sh | 22 +-- website/deletable.php | 3 +- website/details.php | 36 +--- website/hierarchy.php | 7 +- website/lookup.php | 3 +- website/polygons.php | 3 +- website/reverse.php | 3 +- website/search.php | 3 +- website/status.php | 5 +- 42 files changed, 499 insertions(+), 255 deletions(-) create mode 100644 test/php/Nominatim/DBTest.php diff --git a/VAGRANT.md b/VAGRANT.md index d4e88304..0cab24fa 100644 --- a/VAGRANT.md +++ b/VAGRANT.md @@ -171,7 +171,7 @@ If the Postgres installation is behind a firewall, you can try inside the virtual machine. It will map the port to `localhost:9999` and then you edit `settings/local.php` with - @define('CONST_Database_DSN', 'pgsql://postgres@localhost:9999/nominatim_it'); + @define('CONST_Database_DSN', 'pgsql:host=localhost;port=9999;user=postgres;dbname=nominatim_it'); To access postgres directly remember to specify the hostname, e.g. `psql --host localhost --port 9999 nominatim_it` diff --git a/docs/admin/Migration.md b/docs/admin/Migration.md index 94f403ec..6ae7a927 100644 --- a/docs/admin/Migration.md +++ b/docs/admin/Migration.md @@ -9,6 +9,18 @@ SQL statements should be executed from the postgres commandline. Execute ## 3.2.0 -> master +### New database connection string (DSN) format + +Previously database connection setting (`CONST_Database_DSN` in `settings/*.php`) had the format + + * (simple) `pgsql://@/nominatim` + * (complex) `pgsql://johndoe:secret@machine1.domain.com:1234/db1` + +The new format is + + * (simple) `pgsql:dbname=nominatim` + * (complex) `pgsql:dbname=db1;host=machine1.domain.com;port=1234;user=johndoe;password=secret` + ### Natural Earth country boundaries no longer needed as fallback ``` diff --git a/lib/DatabaseError.php b/lib/DatabaseError.php index 2df331bf..3a53bc8f 100644 --- a/lib/DatabaseError.php +++ b/lib/DatabaseError.php @@ -5,10 +5,12 @@ namespace Nominatim; class DatabaseError extends \Exception { - public function __construct($message, $code = 500, Exception $previous = null, $oSql) + public function __construct($message, $code = 500, Exception $previous = null, $oPDOErr, $sSql = null) { parent::__construct($message, $code, $previous); - $this->oSql = $oSql; + // https://secure.php.net/manual/en/class.pdoexception.php + $this->oPDOErr = $oPDOErr; + $this->sSql = $sSql; } public function __toString() @@ -18,15 +20,15 @@ class DatabaseError extends \Exception public function getSqlError() { - return $this->oSql->getMessage(); + return $this->oPDOErr->getMessage(); } public function getSqlDebugDump() { if (CONST_Debug) { - return var_export($this->oSql, true); + return var_export($this->oPDOErr, true); } else { - return $this->oSql->getUserInfo(); + return $this->sSql; } } } diff --git a/lib/Geocode.php b/lib/Geocode.php index 8b153d9a..492c6865 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -527,8 +527,8 @@ class Geocode $sNormQuery = $this->normTerm($this->sQuery); Debug::printVar('Normalized query', $sNormQuery); - $sLanguagePrefArraySQL = getArraySQL( - array_map('getDBQuoted', $this->aLangPrefOrder) + $sLanguagePrefArraySQL = $this->oDB->getArraySQL( + $this->oDB->getDBQuotedList($this->aLangPrefOrder) ); $sQuery = $this->sQuery; @@ -629,7 +629,7 @@ class Geocode $aPhrases = array(); foreach ($aInPhrases as $iPhrase => $sPhrase) { $sPhrase = chksql( - $this->oDB->getOne('SELECT make_standard_name('.getDBQuoted($sPhrase).')'), + $this->oDB->getOne('SELECT make_standard_name('.$this->oDB->getDBQuoted($sPhrase).')'), 'Cannot normalize query string (is it a UTF-8 string?)' ); if (trim($sPhrase)) { @@ -647,7 +647,7 @@ class Geocode if (!empty($aTokens)) { $sSQL = 'SELECT word_id, word_token, word, class, type, country_code, operator, search_name_count'; $sSQL .= ' FROM word '; - $sSQL .= ' WHERE word_token in ('.join(',', array_map('getDBQuoted', $aTokens)).')'; + $sSQL .= ' WHERE word_token in ('.join(',', $this->oDB->getDBQuotedList($aTokens)).')'; Debug::printSQL($sSQL); diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php index 46b17882..c5aea588 100644 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@ -52,7 +52,7 @@ class PlaceLookup { $aLangs = $oParams->getPreferredLanguages(); $this->aLangPrefOrderSql = - 'ARRAY['.join(',', array_map('getDBQuoted', $aLangs)).']'; + 'ARRAY['.join(',', $this->oDB->getDBQuotedList($aLangs)).']'; $this->bExtraTags = $oParams->getBool('extratags', false); $this->bNameDetails = $oParams->getBool('namedetails', false); @@ -132,8 +132,9 @@ class PlaceLookup public function setLanguagePreference($aLangPrefOrder) { - $this->aLangPrefOrderSql = - 'ARRAY['.join(',', array_map('getDBQuoted', $aLangPrefOrder)).']'; + $this->aLangPrefOrderSql = $this->oDB->getArraySQL( + $this->oDB->getDBQuotedList($aLangPrefOrder) + ); } private function addressImportanceSql($sGeometry, $sPlaceId) @@ -515,7 +516,7 @@ class PlaceLookup $aPointPolygon = chksql($this->oDB->getRow($sSQL), 'Could not get outline'); - if ($aPointPolygon['place_id']) { + if ($aPointPolygon && $aPointPolygon['place_id']) { if ($aPointPolygon['centrelon'] !== null && $aPointPolygon['centrelat'] !== null) { $aOutlineResult['lat'] = $aPointPolygon['centrelat']; $aOutlineResult['lon'] = $aPointPolygon['centrelon']; diff --git a/lib/SearchDescription.php b/lib/SearchDescription.php index f5179ce1..fac21972 100644 --- a/lib/SearchDescription.php +++ b/lib/SearchDescription.php @@ -504,8 +504,10 @@ class SearchDescription Debug::printSQL($sSQL); + $iPlaceId = $oDB->getOne($sSQL); + $aResults = array(); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + if ($iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId); } @@ -577,7 +579,7 @@ class SearchDescription $sSQL .= ', search_name s '; $sSQL .= 'WHERE s.place_id = p.parent_place_id '; $sSQL .= 'AND array_cat(s.nameaddress_vector, s.name_vector)'; - $sSQL .= ' @> '.getArraySQL($this->aAddress).' AND '; + $sSQL .= ' @> '.$oDB->getArraySQL($this->aAddress).' AND '; } else { $sSQL .= 'WHERE '; } @@ -633,14 +635,14 @@ class SearchDescription } if (!empty($this->aName)) { - $aTerms[] = 'name_vector @> '.getArraySQL($this->aName); + $aTerms[] = 'name_vector @> '.$oDB->getArraySQL($this->aName); } if (!empty($this->aAddress)) { // For infrequent name terms disable index usage for address if ($this->bRareName) { - $aTerms[] = 'array_cat(nameaddress_vector,ARRAY[]::integer[]) @> '.getArraySQL($this->aAddress); + $aTerms[] = 'array_cat(nameaddress_vector,ARRAY[]::integer[]) @> '.$oDB->getArraySQL($this->aAddress); } else { - $aTerms[] = 'nameaddress_vector @> '.getArraySQL($this->aAddress); + $aTerms[] = 'nameaddress_vector @> '.$oDB->getArraySQL($this->aAddress); } } @@ -695,7 +697,7 @@ class SearchDescription if (!empty($this->aFullNameAddress)) { $sExactMatchSQL = ' ( '; $sExactMatchSQL .= ' SELECT count(*) FROM ( '; - $sExactMatchSQL .= ' SELECT unnest('.getArraySQL($this->aFullNameAddress).')'; + $sExactMatchSQL .= ' SELECT unnest('.$oDB->getArraySQL($this->aFullNameAddress).')'; $sExactMatchSQL .= ' INTERSECT '; $sExactMatchSQL .= ' SELECT unnest(nameaddress_vector)'; $sExactMatchSQL .= ' ) s'; diff --git a/lib/Status.php b/lib/Status.php index 86f5cac3..a276c4d5 100644 --- a/lib/Status.php +++ b/lib/Status.php @@ -3,7 +3,6 @@ namespace Nominatim; use Exception; -use PEAR; class Status { @@ -16,12 +15,18 @@ class Status public function status() { - if (!$this->oDB || PEAR::isError($this->oDB)) { + if (!$this->oDB) { throw new Exception('No database', 700); } + try { + $this->oDB->connect(); + } catch (\Nominatim\DatabaseError $e) { + throw new Exception('Database connection failed', 700); + } + $sStandardWord = $this->oDB->getOne("SELECT make_standard_name('a')"); - if (PEAR::isError($sStandardWord)) { + if ($sStandardWord === false) { throw new Exception('Module failed', 701); } @@ -32,7 +37,7 @@ class Status $sSQL = 'SELECT word_id, word_token, word, class, type, country_code, '; $sSQL .= "operator, search_name_count FROM word WHERE word_token IN (' a')"; $iWordID = $this->oDB->getOne($sSQL); - if (PEAR::isError($iWordID)) { + if ($iWordID === false) { throw new Exception('Query failed', 703); } if (!$iWordID) { @@ -45,7 +50,7 @@ class Status $sSQL = 'SELECT EXTRACT(EPOCH FROM lastimportdate) FROM import_status LIMIT 1'; $iDataDateEpoch = $this->oDB->getOne($sSQL); - if (PEAR::isError($iDataDateEpoch)) { + if ($iDataDateEpoch === false) { throw Exception('Data date query failed '.$iDataDateEpoch->getMessage(), 705); } diff --git a/lib/TokenList.php b/lib/TokenList.php index a5b3c2d2..2d0c7c16 100644 --- a/lib/TokenList.php +++ b/lib/TokenList.php @@ -85,7 +85,7 @@ class TokenList $sSQL = 'SELECT word_id, word_token, word, class, type, country_code,'; $sSQL .= ' operator, coalesce(search_name_count, 0) as count'; $sSQL .= ' FROM word WHERE word_token in ('; - $sSQL .= join(',', array_map('getDBQuoted', $aTokens)).')'; + $sSQL .= join(',', $oDB->getDBQuotedList($aTokens)).')'; Debug::printSQL($sSQL); diff --git a/lib/cmd.php b/lib/cmd.php index 44923618..43669069 100644 --- a/lib/cmd.php +++ b/lib/cmd.php @@ -122,10 +122,6 @@ function showUsage($aSpec, $bExit = false, $sError = false) function chksql($oSql, $sMsg = false) { - if (PEAR::isError($oSql)) { - fail($sMsg || $oSql->getMessage(), $oSql->userinfo); - } - return $oSql; } @@ -155,7 +151,7 @@ function repeatWarnings() function runSQLScript($sScript, $bfatal = true, $bVerbose = false, $bIgnoreErrors = false) { // Convert database DSN to psql parameters - $aDSNInfo = DB::parseDSN(CONST_Database_DSN); + $aDSNInfo = \Nominatim\DB::parseDSN(CONST_Database_DSN); if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432; $sCMD = 'psql -p '.$aDSNInfo['port'].' -d '.$aDSNInfo['database']; if (isset($aDSNInfo['hostspec']) && $aDSNInfo['hostspec']) { diff --git a/lib/db.php b/lib/db.php index 8dbe4535..cac6f2cd 100644 --- a/lib/db.php +++ b/lib/db.php @@ -1,43 +1,176 @@ setFetchMode(DB_FETCHMODE_ASSOC); - $oDB->query("SET DateStyle TO 'sql,european'"); - $oDB->query("SET client_encoding TO 'utf-8'"); - $iMaxExecution = ini_get('max_execution_time') * 1000; - if ($iMaxExecution > 0) $oDB->query("SET statement_timeout TO $iMaxExecution"); - return $oDB; -} + public $connection; -function getDBQuoted($s) -{ - return "'".pg_escape_string($s)."'"; -} + public function __construct($sDSN = CONST_Database_DSN) + { + $this->sDSN = $sDSN; + } -function getArraySQL($a) -{ - return 'ARRAY['.join(',', $a).']'; -} + public function connect($bNew = false, $bPersistent = true) + { + $aConnOptions = array( + \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, + \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC, + \PDO::ATTR_PERSISTENT => $bPersistent + ); -function getPostgresVersion(&$oDB) -{ - $sVersionString = $oDB->getOne('SHOW server_version_num'); - preg_match('#([0-9]?[0-9])([0-9][0-9])[0-9][0-9]#', $sVersionString, $aMatches); - return (float) ($aMatches[1].'.'.$aMatches[2]); -} + // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php + try { + $conn = new \PDO($this->sDSN, null, null, $aConnOptions); + } catch (\PDOException $e) { + $sMsg = 'Failed to establish database connection:' . $e->getMessage(); + throw new \Nominatim\DatabaseError($sMsg, 500, null, $e->getMessage()); + } -function getPostgisVersion(&$oDB) -{ - $sVersionString = $oDB->getOne('select postgis_lib_version()'); - preg_match('#^([0-9]+)[.]([0-9]+)[.]#', $sVersionString, $aMatches); - return (float) ($aMatches[1].'.'.$aMatches[2]); + $conn->exec("SET DateStyle TO 'sql,european'"); + $conn->exec("SET client_encoding TO 'utf-8'"); + $iMaxExecution = ini_get('max_execution_time'); + if ($iMaxExecution > 0) $conn->setAttribute(\PDO::ATTR_TIMEOUT, $iMaxExecution); // seconds + + $this->connection = $conn; + return true; + } + + // returns the number of rows that were modified or deleted by the SQL + // statement. If no rows were affected returns 0. + public function exec($sSQL) + { + $val = null; + try { + $val = $this->connection->exec($sSQL); + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL); + } + return $val; + } + + public function getRow($sSQL) + { + try { + $stmt = $this->connection->query($sSQL); + $row = $stmt->fetch(); + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL); + } + return $row; + } + + public function getOne($sSQL) + { + try { + $stmt = $this->connection->query($sSQL); + $row = $stmt->fetch(\PDO::FETCH_NUM); + if ($row === false) return false; + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL); + } + return $row[0]; + } + + public function getAll($sSQL) + { + try { + $stmt = $this->connection->query($sSQL); + $rows = $stmt->fetchAll(); + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL); + } + return $rows; + } + + public function getCol($sSQL) + { + $aVals = array(); + try { + $stmt = $this->connection->query($sSQL); + while ($val = $stmt->fetchColumn(0)) { // returns first column or false + $aVals[] = $val; + } + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL); + } + return $aVals; + } + + public function getAssoc($sSQL) + { + try { + $stmt = $this->connection->query($sSQL); + $aList = array(); + while ($aRow = $stmt->fetch(\PDO::FETCH_NUM)) { + $aList[$aRow[0]] = $aRow[1]; + } + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError('Database query failed', 500, null, $e, $sSQL); + } + return $aList; + } + + + // St. John's Way => 'St. John\'s Way' + public function getDBQuoted($sVal) + { + return $this->connection->quote($sVal); + } + + public function getDBQuotedList($aVals) + { + return array_map(function ($sVal) { + return $this->getDBQuoted($sVal); + }, $aVals); + } + + public function getArraySQL($a) + { + return 'ARRAY['.join(',', $a).']'; + } + + public function getLastError() + { + // https://secure.php.net/manual/en/pdo.errorinfo.php + return $this->connection->errorInfo(); + } + + public function tableExists($sTableName) + { + $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = '.$this->getDBQuoted($sTableName); + return ($this->getOne($sSQL) == 1); + } + + public function getPostgresVersion() + { + $sVersionString = $this->getOne('SHOW server_version_num'); + preg_match('#([0-9]?[0-9])([0-9][0-9])[0-9][0-9]#', $sVersionString, $aMatches); + return (float) ($aMatches[1].'.'.$aMatches[2]); + } + + public function getPostgisVersion() + { + $sVersionString = $this->getOne('select postgis_lib_version()'); + preg_match('#^([0-9]+)[.]([0-9]+)[.]#', $sVersionString, $aMatches); + return (float) ($aMatches[1].'.'.$aMatches[2]); + } + + public static function parseDSN($sDSN) + { + // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php + $aInfo = array(); + if (preg_match('/^pgsql:(.+)/', $sDSN, $aMatches)) { + foreach (explode(';', $aMatches[1]) as $sKeyVal) { + list($sKey, $sVal) = explode('=', $sKeyVal, 2); + if ($sKey == 'host') $sKey = 'hostspec'; + if ($sKey == 'dbname') $sKey = 'database'; + if ($sKey == 'user') $sKey = 'username'; + $aInfo[$sKey] = $sVal; + } + } + return $aInfo; + } } diff --git a/lib/init-website.php b/lib/init-website.php index ae2a5d36..39df4022 100644 --- a/lib/init-website.php +++ b/lib/init-website.php @@ -2,7 +2,6 @@ require_once('init.php'); require_once('ParameterParser.php'); -require_once('DatabaseError.php'); require_once(CONST_Debug ? 'DebugHtml.php' : 'DebugNone.php'); /*************************************************************************** @@ -14,9 +13,7 @@ require_once(CONST_Debug ? 'DebugHtml.php' : 'DebugNone.php'); function chksql($oSql, $sMsg = 'Database request failed') { - if (!PEAR::isError($oSql)) return $oSql; - - throw new Nominatim\DatabaseError($sMsg, 500, null, $oSql); + return $oSql; } diff --git a/lib/lib.php b/lib/lib.php index d77a82c1..759c71d7 100644 --- a/lib/lib.php +++ b/lib/lib.php @@ -4,7 +4,7 @@ function fail($sError, $sUserError = false) { if (!$sUserError) $sUserError = $sError; error_log('ERROR: '.$sError); - echo $sUserError."\n"; + var_dump($sUserError)."\n"; exit(-1); } diff --git a/lib/log.php b/lib/log.php index 97d7d9c9..ae4253c0 100644 --- a/lib/log.php +++ b/lib/log.php @@ -36,8 +36,18 @@ function logStart(&$oDB, $sType = '', $sQuery = '', $aLanguageList = array()) $sUserAgent = $_SERVER['HTTP_USER_AGENT']; else $sUserAgent = ''; $sSQL = 'insert into new_query_log (type,starttime,query,ipaddress,useragent,language,format,searchterm)'; - $sSQL .= ' values ('.getDBQuoted($sType).','.getDBQuoted($hLog[0]).','.getDBQuoted($hLog[2]); - $sSQL .= ','.getDBQuoted($hLog[1]).','.getDBQuoted($sUserAgent).','.getDBQuoted(join(',', $aLanguageList)).','.getDBQuoted($sOutputFormat).','.getDBQuoted($hLog[3]).')'; + $sSQL .= ' values ('. + $sSQL .= join(',', $oDB->getDBQuotedList(array( + $sType, + $hLog[0], + $hLog[2], + $hLog[1], + $sUserAgent, + join(',', $aLanguageList), + $sOutputFormat, + $hLog[3] + ))); + $sSQL .= ')'; $oDB->query($sSQL); } @@ -53,10 +63,10 @@ function logEnd(&$oDB, $hLog, $iNumResults) if (!$aEndTime[1]) $aEndTime[1] = '0'; $sEndTime = date('Y-m-d H:i:s', $aEndTime[0]).'.'.$aEndTime[1]; - $sSQL = 'update new_query_log set endtime = '.getDBQuoted($sEndTime).', results = '.$iNumResults; - $sSQL .= ' where starttime = '.getDBQuoted($hLog[0]); - $sSQL .= ' and ipaddress = '.getDBQuoted($hLog[1]); - $sSQL .= ' and query = '.getDBQuoted($hLog[2]); + $sSQL = 'update new_query_log set endtime = '.$oDB->getDBQuoted($sEndTime).', results = '.$iNumResults; + $sSQL .= ' where starttime = '.$oDB->getDBQuoted($hLog[0]); + $sSQL .= ' and ipaddress = '.$oDB->getDBQuoted($hLog[1]); + $sSQL .= ' and query = '.$oDB->getDBQuoted($hLog[2]); $oDB->query($sSQL); } diff --git a/lib/setup/AddressLevelParser.php b/lib/setup/AddressLevelParser.php index 86674243..92092428 100644 --- a/lib/setup/AddressLevelParser.php +++ b/lib/setup/AddressLevelParser.php @@ -53,21 +53,21 @@ class AddressLevelParser */ public function createTable($oDB, $sTable) { - chksql($oDB->query('DROP TABLE IF EXISTS '.$sTable)); + chksql($oDB->exec('DROP TABLE IF EXISTS '.$sTable)); $sSql = 'CREATE TABLE '.$sTable; $sSql .= '(country_code varchar(2), class TEXT, type TEXT,'; $sSql .= ' rank_search SMALLINT, rank_address SMALLINT)'; - chksql($oDB->query($sSql)); + chksql($oDB->exec($sSql)); $sSql = 'CREATE UNIQUE INDEX ON '.$sTable.'(country_code, class, type)'; - chksql($oDB->query($sSql)); + chksql($oDB->exec($sSql)); $sSql = 'INSERT INTO '.$sTable.' VALUES '; foreach ($this->aLevels as $aLevel) { $aCountries = array(); if (isset($aLevel['countries'])) { foreach ($aLevel['countries'] as $sCountry) { - $aCountries[$sCountry] = getDBQuoted($sCountry); + $aCountries[$sCountry] = $oDB->getDBQuoted($sCountry); } } else { $aCountries['NULL'] = 'NULL'; @@ -75,8 +75,8 @@ class AddressLevelParser foreach ($aLevel['tags'] as $sKey => $aValues) { foreach ($aValues as $sValue => $mRanks) { $aFields = array( - getDBQuoted($sKey), - $sValue ? getDBQuoted($sValue) : 'NULL' + $oDB->getDBQuoted($sKey), + $sValue ? $oDB->getDBQuoted($sValue) : 'NULL' ); if (is_array($mRanks)) { $aFields[] = (string) $mRanks[0]; @@ -93,6 +93,6 @@ class AddressLevelParser } } } - chksql($oDB->query(rtrim($sSql, ','))); + chksql($oDB->exec(rtrim($sSql, ','))); } } diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php index e6b07998..9cc00a5f 100755 --- a/lib/setup/SetupClass.php +++ b/lib/setup/SetupClass.php @@ -40,7 +40,7 @@ class SetupFunctions info('module path: ' . $this->sModulePath); // parse database string - $this->aDSNInfo = array_filter(\DB::parseDSN(CONST_Database_DSN)); + $this->aDSNInfo = \Nominatim\DB::parseDSN(CONST_Database_DSN); if (!isset($this->aDSNInfo['port'])) { $this->aDSNInfo['port'] = 5432; } @@ -74,8 +74,15 @@ class SetupFunctions public function createDB() { info('Create DB'); - $sDB = \DB::connect(CONST_Database_DSN, false); - if (!\PEAR::isError($sDB)) { + $bExists = true; + try { + $oDB = new \Nominatim\DB; + $oDB->connect(); + } catch (\Nominatim\DatabaseError $e) { + $bExists = false; + } + + if ($bExists) { fail('database already exists ('.CONST_Database_DSN.')'); } @@ -94,14 +101,15 @@ class SetupFunctions public function connect() { - $this->oDB =& getDB(); + $this->oDB = new \Nominatim\DB(); + $this->oDB->connect(); } public function setupDB() { info('Setup DB'); - $fPostgresVersion = getPostgresVersion($this->oDB); + $fPostgresVersion = $this->oDB->getPostgresVersion(); echo 'Postgres version found: '.$fPostgresVersion."\n"; if ($fPostgresVersion < 9.01) { @@ -122,7 +130,7 @@ class SetupFunctions } - $fPostgisVersion = getPostgisVersion($this->oDB); + $fPostgisVersion = $this->oDB->getPostgisVersion(); echo 'Postgis version found: '.$fPostgisVersion."\n"; if ($fPostgisVersion < 2.1) { @@ -379,6 +387,7 @@ class SetupFunctions $sSQL = 'select distinct partition from country_name'; $aPartitions = chksql($this->oDB->getCol($sSQL)); + if (!$this->bNoPartitions) $aPartitions[] = 0; foreach ($aPartitions as $sPartition) { $this->pgExec('TRUNCATE location_road_'.$sPartition); @@ -399,34 +408,48 @@ class SetupFunctions info('Load Data'); $sColumns = 'osm_type, osm_id, class, type, name, admin_level, address, extratags, geometry'; + $aDBInstances = array(); $iLoadThreads = max(1, $this->iInstances - 1); for ($i = 0; $i < $iLoadThreads; $i++) { - $aDBInstances[$i] =& getDB(true); + // https://secure.php.net/manual/en/function.pg-connect.php + $DSN = CONST_Database_DSN; + $DSN = preg_replace('/^pgsql:/', '', $DSN); + $DSN = preg_replace('/;/', ' ', $DSN); + $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW); + pg_ping($aDBInstances[$i]); + } + + for ($i = 0; $i < $iLoadThreads; $i++) { $sSQL = "INSERT INTO placex ($sColumns) SELECT $sColumns FROM place WHERE osm_id % $iLoadThreads = $i"; $sSQL .= " and not (class='place' and type='houses' and osm_type='W'"; $sSQL .= " and ST_GeometryType(geometry) = 'ST_LineString')"; $sSQL .= ' and ST_IsValid(geometry)'; if ($this->bVerbose) echo "$sSQL\n"; - if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) { - fail(pg_last_error($aDBInstances[$i]->connection)); + if (!pg_send_query($aDBInstances[$i], $sSQL)) { + fail(pg_last_error($aDBInstances[$i])); } } // last thread for interpolation lines - $aDBInstances[$iLoadThreads] =& getDB(true); + // https://secure.php.net/manual/en/function.pg-connect.php + $DSN = CONST_Database_DSN; + $DSN = preg_replace('/^pgsql:/', '', $DSN); + $DSN = preg_replace('/;/', ' ', $DSN); + $aDBInstances[$iLoadThreads] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW); + pg_ping($aDBInstances[$iLoadThreads]); $sSQL = 'insert into location_property_osmline'; $sSQL .= ' (osm_id, address, linegeo)'; $sSQL .= ' SELECT osm_id, address, geometry from place where '; $sSQL .= "class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString'"; if ($this->bVerbose) echo "$sSQL\n"; - if (!pg_send_query($aDBInstances[$iLoadThreads]->connection, $sSQL)) { - fail(pg_last_error($aDBInstances[$iLoadThreads]->connection)); + if (!pg_send_query($aDBInstances[$iLoadThreads], $sSQL)) { + fail(pg_last_error($aDBInstances[$iLoadThreads])); } $bFailed = false; for ($i = 0; $i <= $iLoadThreads; $i++) { - while (($hPGresult = pg_get_result($aDBInstances[$i]->connection)) !== false) { + while (($hPGresult = pg_get_result($aDBInstances[$i])) !== false) { $resultStatus = pg_result_status($hPGresult); // PGSQL_EMPTY_QUERY, PGSQL_COMMAND_OK, PGSQL_TUPLES_OK, // PGSQL_COPY_OUT, PGSQL_COPY_IN, PGSQL_BAD_RESPONSE, @@ -442,17 +465,22 @@ class SetupFunctions if ($bFailed) { fail('SQL errors loading placex and/or location_property_osmline tables'); } + + for ($i = 0; $i < $this->iInstances; $i++) { + pg_close($aDBInstances[$i]); + } + echo "\n"; info('Reanalysing database'); $this->pgsqlRunScript('ANALYSE'); $sDatabaseDate = getDatabaseDate($this->oDB); - pg_query($this->oDB->connection, 'TRUNCATE import_status'); - if ($sDatabaseDate === false) { + $this->oDB->exec('TRUNCATE import_status'); + if (!$sDatabaseDate) { warn('could not determine database date.'); } else { $sSQL = "INSERT INTO import_status (lastimportdate) VALUES('".$sDatabaseDate."')"; - pg_query($this->oDB->connection, $sSQL); + $this->oDB->exec($sSQL); echo "Latest data imported from $sDatabaseDate.\n"; } } @@ -477,7 +505,12 @@ class SetupFunctions $aDBInstances = array(); for ($i = 0; $i < $this->iInstances; $i++) { - $aDBInstances[$i] =& getDB(true); + // https://secure.php.net/manual/en/function.pg-connect.php + $DSN = CONST_Database_DSN; + $DSN = preg_replace('/^pgsql:/', '', $DSN); + $DSN = preg_replace('/;/', ' ', $DSN); + $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW | PGSQL_CONNECT_ASYNC); + pg_ping($aDBInstances[$i]); } foreach (glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile) { @@ -487,11 +520,11 @@ class SetupFunctions $iLines = 0; while (true) { for ($i = 0; $i < $this->iInstances; $i++) { - if (!pg_connection_busy($aDBInstances[$i]->connection)) { - while (pg_get_result($aDBInstances[$i]->connection)); + if (!pg_connection_busy($aDBInstances[$i])) { + while (pg_get_result($aDBInstances[$i])); $sSQL = fgets($hFile, 100000); if (!$sSQL) break 2; - if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($this->oDB->connection)); + if (!pg_send_query($aDBInstances[$i], $sSQL)) fail(pg_last_error($aDBInstances[$i])); $iLines++; if ($iLines == 1000) { echo '.'; @@ -507,13 +540,17 @@ class SetupFunctions while ($bAnyBusy) { $bAnyBusy = false; for ($i = 0; $i < $this->iInstances; $i++) { - if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true; + if (pg_connection_busy($aDBInstances[$i])) $bAnyBusy = true; } usleep(10); } echo "\n"; } + for ($i = 0; $i < $this->iInstances; $i++) { + pg_close($aDBInstances[$i]); + } + info('Creating indexes on Tiger data'); $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql'); $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate); @@ -711,7 +748,7 @@ class SetupFunctions } foreach ($aDropTables as $sDrop) { if ($this->bVerbose) echo "Dropping table $sDrop\n"; - @pg_query($this->oDB->connection, "DROP TABLE $sDrop CASCADE"); + $this->oDB->exec("DROP TABLE $sDrop CASCADE"); // ignore warnings/errors as they might be caused by a table having // been deleted already by CASCADE } @@ -776,7 +813,7 @@ class SetupFunctions private function pgsqlRunPartitionScript($sTemplate) { $sSQL = 'select distinct partition from country_name'; - $aPartitions = chksql($this->oDB->getCol($sSQL)); + $aPartitions = $this->oDB->getCol($sSQL); if (!$this->bNoPartitions) $aPartitions[] = 0; preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); @@ -883,9 +920,7 @@ class SetupFunctions */ private function pgExec($sSQL) { - if (!pg_query($this->oDB->connection, $sSQL)) { - fail(pg_last_error($this->oDB->connection)); - } + $this->oDB->exec($sSQL); } /** @@ -895,7 +930,6 @@ class SetupFunctions */ private function dbReverseOnly() { - $sSQL = "SELECT count(*) FROM pg_tables WHERE tablename = 'search_name'"; - return !(chksql($this->oDB->getOne($sSQL))); + return !($this->oDB->tableExists('search_name')); } } diff --git a/lib/setup_functions.php b/lib/setup_functions.php index b1417678..89736ae0 100755 --- a/lib/setup_functions.php +++ b/lib/setup_functions.php @@ -24,15 +24,17 @@ function checkModulePresence() $sSQL .= $sModulePath . "/nominatim.so', 'transliteration' LANGUAGE c IMMUTABLE STRICT"; $sSQL .= ';DROP FUNCTION nominatim_test_import_func(text);'; - $oDB = &getDB(); - $oResult = $oDB->query($sSQL); + $oDB = new \Nominatim\DB(); + $oDB->connect(); $bResult = true; - - if (PEAR::isError($oResult)) { + try { + $oDB->exec($sSQL); + } catch (\Nominatim\DatabaseError $e) { echo "\nERROR: Failed to load nominatim module. Reason:\n"; - echo $oResult->userinfo . "\n\n"; + echo $oDB->getLastError()[2] . "\n\n"; $bResult = false; } + return $bResult; } diff --git a/settings/defaults.php b/settings/defaults.php index 2a2aea36..71413852 100644 --- a/settings/defaults.php +++ b/settings/defaults.php @@ -7,7 +7,7 @@ if (isset($_GET['debug']) && $_GET['debug']) @define('CONST_Debug', true); // General settings @define('CONST_Debug', false); -@define('CONST_Database_DSN', 'pgsql://@/nominatim'); // ://:@:/ +@define('CONST_Database_DSN', 'pgsql:dbname=nominatim'); // or add ;host=...;port=...;user=...;password=... @define('CONST_Database_Web_User', 'www-data'); @define('CONST_Database_Module_Path', CONST_InstallPath.'/module'); @define('CONST_Max_Word_Frequency', '50000'); diff --git a/test/bdd/api/status/failures.feature b/test/bdd/api/status/failures.feature index 9ef06532..70e9589a 100644 --- a/test/bdd/api/status/failures.feature +++ b/test/bdd/api/status/failures.feature @@ -5,7 +5,7 @@ Feature: Status queries against unknown database Scenario: Failed status as text When sending text status query Then a HTTP 500 is returned - And the page contents equals "ERROR: No database" + And the page contents equals "ERROR: Database connection failed" Scenario: Failed status as json When sending json status query @@ -13,5 +13,5 @@ Feature: Status queries against unknown database And the result is valid json And results contain | status | message | - | 700 | No database | + | 700 | Database connection failed | And result has not attributes data_updated diff --git a/test/bdd/environment.py b/test/bdd/environment.py index 25b118b4..02d09824 100644 --- a/test/bdd/environment.py +++ b/test/bdd/environment.py @@ -73,12 +73,14 @@ class NominatimEnvironment(object): def write_nominatim_config(self, dbname): f = open(self.local_settings_file, 'w') - f.write("expectException(DatabaseError::class); + $this->expectExceptionMessage('Failed to establish database connection'); + + $oDB = new \Nominatim\DB('pgsql:dbname=abc'); + $oDB->connect(); + } + + public function testErrorHandling2() + { + $this->expectException(DatabaseError::class); + $this->expectExceptionMessage('Database query failed'); + + $oPDOStub = $this->getMockBuilder(PDO::class) + ->setMethods(array('query', 'quote')) + ->getMock(); + + $oPDOStub->method('query') + ->will($this->returnCallback(function ($sVal) { + return "'$sVal'"; + })); + + $oPDOStub->method('query') + ->will($this->returnCallback(function () { + throw new \PDOException('ERROR: syntax error at or near "FROM"'); + })); + + $oDB = new \Nominatim\DB(''); + $oDB->connection = $oPDOStub; + $oDB->tableExists('abc'); + } + + public function testParseDSN() + { + $this->assertEquals( + array(), + \Nominatim\DB::parseDSN('') + ); + $this->assertEquals( + array( + 'database' => 'db1', + 'hostspec' => 'machine1' + ), + \Nominatim\DB::parseDSN('pgsql:dbname=db1;host=machine1') + ); + $this->assertEquals( + array( + 'database' => 'db1', + 'hostspec' => 'machine1', + 'port' => '1234', + 'username' => 'john', + 'password' => 'secret' + ), + \Nominatim\DB::parseDSN('pgsql:dbname=db1;host=machine1;port=1234;user=john;password=secret') + ); + } +} diff --git a/test/php/Nominatim/DatabaseErrorTest.php b/test/php/Nominatim/DatabaseErrorTest.php index 25b4aa0b..7b461894 100644 --- a/test/php/Nominatim/DatabaseErrorTest.php +++ b/test/php/Nominatim/DatabaseErrorTest.php @@ -10,7 +10,7 @@ class DatabaseErrorTest extends \PHPUnit\Framework\TestCase public function testSqlMessage() { - $oSqlStub = $this->getMockBuilder(\DB_Error::class) + $oSqlStub = $this->getMockBuilder(PDOException::class) ->setMethods(array('getMessage')) ->getMock(); @@ -21,9 +21,6 @@ class DatabaseErrorTest extends \PHPUnit\Framework\TestCase $this->assertEquals('Sql error', $oErr->getMessage()); $this->assertEquals(123, $oErr->getCode()); $this->assertEquals('Unknown table.', $oErr->getSqlError()); - - // causes a circular reference warning during dump - // $this->assertRegExp('/Mock_DB_Error/', $oErr->getSqlDebugDump()); } public function testSqlObjectDump() @@ -31,14 +28,4 @@ class DatabaseErrorTest extends \PHPUnit\Framework\TestCase $oErr = new DatabaseError('Sql error', 123, null, array('one' => 'two')); $this->assertRegExp('/two/', $oErr->getSqlDebugDump()); } - - public function testChksqlThrows() - { - $this->expectException(DatabaseError::class); - $this->expectExceptionMessage('My custom error message'); - $this->expectExceptionCode(500); - - $oDB = new \DB_Error; - $this->assertEquals(false, chksql($oDB, 'My custom error message')); - } } diff --git a/test/php/Nominatim/StatusTest.php b/test/php/Nominatim/StatusTest.php index eb4ad68a..dbf15fd4 100644 --- a/test/php/Nominatim/StatusTest.php +++ b/test/php/Nominatim/StatusTest.php @@ -23,19 +23,20 @@ class StatusTest extends \PHPUnit\Framework\TestCase public function testNoDatabaseConnectionFail() { $this->expectException(\Exception::class); - $this->expectExceptionMessage('No database'); + $this->expectExceptionMessage('Database connection failed'); $this->expectExceptionCode(700); - // causes 'Non-static method should not be called statically, assuming $this from incompatible context' - // failure on travis - // $oDB = \DB::connect('', false); // returns a DB_Error instance + $oDbStub = $this->getMockBuilder(Nominatim\DB::class) + ->setMethods(array('connect')) + ->getMock(); - $oDB = new \DB_Error; - $oStatus = new Status($oDB); - $this->assertEquals('No database', $oStatus->status()); + $oDbStub->method('connect') + ->will($this->returnCallback(function () { + throw new \Nominatim\DatabaseError('psql connection problem', 500, null, 'unknown database'); + })); - $oDB = null; - $oStatus = new Status($oDB); + + $oStatus = new Status($oDbStub); $this->assertEquals('No database', $oStatus->status()); } @@ -47,8 +48,8 @@ class StatusTest extends \PHPUnit\Framework\TestCase $this->expectExceptionCode(702); // stub has getOne method but doesn't return anything - $oDbStub = $this->getMockBuilder(\DB::class) - ->setMethods(array('getOne')) + $oDbStub = $this->getMockBuilder(Nominatim\DB::class) + ->setMethods(array('connect', 'getOne')) ->getMock(); $oStatus = new Status($oDbStub); @@ -62,8 +63,8 @@ class StatusTest extends \PHPUnit\Framework\TestCase $this->expectExceptionMessage('No value'); $this->expectExceptionCode(704); - $oDbStub = $this->getMockBuilder(\DB::class) - ->setMethods(array('getOne')) + $oDbStub = $this->getMockBuilder(Nominatim\DB::class) + ->setMethods(array('connect', 'getOne')) ->getMock(); // return no word_id @@ -80,8 +81,8 @@ class StatusTest extends \PHPUnit\Framework\TestCase public function testOK() { - $oDbStub = $this->getMockBuilder(\DB::class) - ->setMethods(array('getOne')) + $oDbStub = $this->getMockBuilder(Nominatim\DB::class) + ->setMethods(array('connect', 'getOne')) ->getMock(); $oDbStub->method('getOne') @@ -96,7 +97,7 @@ class StatusTest extends \PHPUnit\Framework\TestCase public function testDataDate() { - $oDbStub = $this->getMockBuilder(\DB::class) + $oDbStub = $this->getMockBuilder(Nominatim\DB::class) ->setMethods(array('getOne')) ->getMock(); diff --git a/test/php/Nominatim/TokenListTest.php b/test/php/Nominatim/TokenListTest.php index 4016a839..417f52c0 100644 --- a/test/php/Nominatim/TokenListTest.php +++ b/test/php/Nominatim/TokenListTest.php @@ -56,9 +56,18 @@ class TokenTest extends \PHPUnit\Framework\TestCase { $this->expectOutputRegex('/

/'); - $oDbStub = $this->getMockBuilder(\DB::class) - ->setMethods(array('getAll')) + $oDbStub = $this->getMockBuilder(Nominatim\DB::class) + ->setMethods(array('getAll', 'getDBQuotedList')) ->getMock(); + + $oDbStub->method('getDBQuotedList') + ->will($this->returnCallback(function ($aVals) { + return array_map(function ($sVal) { + return "'".$sVal."'"; + }, $aVals); + })); + + $oDbStub->method('getAll') ->will($this->returnCallback(function ($sql) { $aResults = array(); diff --git a/utils/export.php b/utils/export.php index 34f1b011..9d3037aa 100644 --- a/utils/export.php +++ b/utils/export.php @@ -41,7 +41,8 @@ 'path' => 27 ); - $oDB =& getDB(); + $oDB = new Nominatim\DB(); + $oDB->connect(); if (isset($aCMDResult['output-type'])) { if (!isset($aRankmap[$aCMDResult['output-type']])) fail('unknown output-type: '.$aCMDResult['output-type']); @@ -55,7 +56,7 @@ $oParams = new Nominatim\ParameterParser(); if (!isset($aCMDResult['language'])) $aCMDResult['language'] = 'xx'; $aLangPrefOrder = $oParams->getPreferredLanguages($aCMDResult['language']); - $sLanguagePrefArraySQL = 'ARRAY['.join(',', array_map('getDBQuoted', $aLangPrefOrder)).']'; + $sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder)); // output formatting: build up a lookup table that maps address ranks to columns $aColumnMapping = array(); @@ -95,7 +96,7 @@ $sPlacexSQL .= ' and rank_address = '.$iOutputRank; if (isset($aCMDResult['restrict-to-country'])) { - $sPlacexSQL .= ' and country_code = '.getDBQuoted($aCMDResult['restrict-to-country']); + $sPlacexSQL .= ' and country_code = '.$oDB->getDBQuoted($aCMDResult['restrict-to-country']); } // restriction to parent place id @@ -116,10 +117,9 @@ } if ($sOsmType) { $sSQL = 'select place_id from placex where'; - $sSQL .= ' osm_type = '.getDBQuoted($sOsmType); + $sSQL .= ' osm_type = '.$oDB->getDBQuoted($sOsmType); $sSQL .= ' and osm_id = '.$sOsmId; $sParentId = $oDB->getOne($sSQL); - if (PEAR::isError($sParentId)) fail(pg_last_error($oDB->connection)); if (!$sParentId) fail('Could not find place '.$sOsmType.' '.$sOsmId); } if ($sParentId) { @@ -132,7 +132,6 @@ // to get further hierarchical information //var_dump($sPlacexSQL); $aRes =& $oDB->query($sPlacexSQL); - if (PEAR::isError($aRes)) fail(pg_last_error($oDB->connection)); $fOutstream = fopen('php://output', 'w'); while ($aRes->fetchInto($aRow)) { //var_dump($aRow); @@ -141,8 +140,6 @@ $sSQL .= ' WHERE isaddress'; $sSQL .= ' order by rank_address desc,isaddress desc'; $aAddressLines = $oDB->getAll($sSQL); - if (PEAR::IsError($aAddressLines)) fail(pg_last_error($oDB->connection)); - $aOutput = array_fill(0, $iNumCol, ''); // output address parts @@ -160,7 +157,7 @@ $sSQL .= 'and pa.place_id in (select place_id from place_addressline where address_place_id in ('.substr($aRow['place_ids'], 1, -1).')) '; $sSQL .= 'group by postcode order by count(*) desc limit 1'; $sRes = $oDB->getOne($sSQL); - if (PEAR::IsError($sRes)) fail(pg_last_error($oDB->connection)); + $aOutput[$aColumnMapping['postcode']] = substr($sRes, 1, -1); } else { $aOutput[$aColumnMapping['postcode']] = $aRow['postcode']; diff --git a/utils/importWikipedia.php b/utils/importWikipedia.php index 06db6036..2e256e35 100644 --- a/utils/importWikipedia.php +++ b/utils/importWikipedia.php @@ -48,7 +48,8 @@ exit; $a = array(); $a[] = 'test'; - $oDB &= getDB(); + $oDB = new Nominatim\DB(); + $oDB->connect(); if ($aCMDResult['drop-tables']) { @@ -304,7 +305,9 @@ function _templatesToProperties($aTemplates) } if (isset($aCMDResult['parse-wikipedia'])) { - $oDB =& getDB(); + $oDB = new Nominatim\DB(); + $oDB->connect(); + $sSQL = 'select page_title from content where page_namespace = 0 and page_id %10 = '; $sSQL .= $aCMDResult['parse-wikipedia']; $sSQL .= ' and (page_content ilike \'%{{Coord%\' or (page_content ilike \'%lat%\' and page_content ilike \'%lon%\'))'; @@ -366,7 +369,9 @@ function nominatimXMLEnd($hParser, $sName) if (isset($aCMDResult['link'])) { - $oDB =& getDB(); + $oDB = new Nominatim\DB(); + $oDB->connect(); + $aWikiArticles = $oDB->getAll("select * from wikipedia_article where language = 'en' and lat is not null and osm_type is null and totalcount < 31 order by importance desc limit 200000"); // If you point this script at production OSM you will be blocked diff --git a/utils/query.php b/utils/query.php index 9694bbb9..f8047ffc 100644 --- a/utils/query.php +++ b/utils/query.php @@ -25,7 +25,9 @@ $aCMDOptions ); getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true); -$oDB =& getDB(); +$oDB = new Nominatim\DB; +$oDB->connect(); + $oParams = new Nominatim\ParameterParser($aCMDResult); if ($oParams->getBool('search')) { diff --git a/utils/update.php b/utils/update.php index 3ef12cfb..96789ef4 100644 --- a/utils/update.php +++ b/utils/update.php @@ -52,9 +52,10 @@ if (!isset($aResult['index-rank'])) $aResult['index-rank'] = 0; date_default_timezone_set('Etc/UTC'); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); -$aDSNInfo = DB::parseDSN(CONST_Database_DSN); +$aDSNInfo = Nominatim\DB::parseDSN(CONST_Database_DSN); if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432; // cache memory to be used by osm2pgsql, should not be more than the available memory @@ -115,7 +116,7 @@ if ($aResult['init-updates']) { } $sDatabaseDate = getDatabaseDate($oDB); - if ($sDatabaseDate === false) { + if (!$sDatabaseDate) { fail('Cannot determine date of database.'); } $sWindBack = strftime('%Y-%m-%dT%H:%M:%SZ', strtotime($sDatabaseDate) - (3*60*60)); @@ -128,10 +129,13 @@ if ($aResult['init-updates']) { fail('Error running pyosmium tools'); } - pg_query($oDB->connection, 'TRUNCATE import_status'); + $oDB->exec('TRUNCATE import_status'); $sSQL = "INSERT INTO import_status (lastimportdate, sequence_id, indexed) VALUES('"; $sSQL .= $sDatabaseDate."',".$aOutput[0].', true)'; - if (!pg_query($oDB->connection, $sSQL)) { + + try { + $oDB->exec($sSQL); + } catch (\Nominatim\DatabaseError $e) { fail('Could not enter sequence into database.'); } @@ -219,9 +223,10 @@ if ($bHaveDiff) { } if ($aResult['deduplicate']) { - $oDB =& getDB(); + $oDB = new Nominatim\DB(); + $oDB->connect(); - if (getPostgresVersion($oDB) < 9.3) { + if ($oDB->getPostgresVersion() < 9.3) { fail('ERROR: deduplicate is only currently supported in postgresql 9.3'); } @@ -259,32 +264,32 @@ if ($aResult['deduplicate']) { $sSQL .= ' name_vector = array_replace(name_vector,'.$aRemove['word_id'].','.$iKeepID.'),'; $sSQL .= ' nameaddress_vector = array_replace(nameaddress_vector,'.$aRemove['word_id'].','.$iKeepID.')'; $sSQL .= ' where name_vector @> ARRAY['.$aRemove['word_id'].']'; - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); $sSQL = 'update search_name set'; $sSQL .= ' nameaddress_vector = array_replace(nameaddress_vector,'.$aRemove['word_id'].','.$iKeepID.')'; $sSQL .= ' where nameaddress_vector @> ARRAY['.$aRemove['word_id'].']'; - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); $sSQL = 'update location_area_country set'; $sSQL .= ' keywords = array_replace(keywords,'.$aRemove['word_id'].','.$iKeepID.')'; $sSQL .= ' where keywords @> ARRAY['.$aRemove['word_id'].']'; - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); foreach ($aPartitions as $sPartition) { $sSQL = 'update search_name_'.$sPartition.' set'; $sSQL .= ' name_vector = array_replace(name_vector,'.$aRemove['word_id'].','.$iKeepID.')'; $sSQL .= ' where name_vector @> ARRAY['.$aRemove['word_id'].']'; - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); $sSQL = 'update location_area_country set'; $sSQL .= ' keywords = array_replace(keywords,'.$aRemove['word_id'].','.$iKeepID.')'; $sSQL .= ' where keywords @> ARRAY['.$aRemove['word_id'].']'; - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); } $sSQL = 'delete from word where word_id = '.$aRemove['word_id']; - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); } } } @@ -306,7 +311,7 @@ if ($aResult['index']) { runWithEnv($sCmd, $aProcEnv); - $oDB->query('update import_status set indexed = true'); + $oDB->exec('update import_status set indexed = true'); } if ($aResult['update-address-levels']) { @@ -413,12 +418,12 @@ if ($aResult['import-osmosis'] || $aResult['import-osmosis-all']) { $sSQL .= date('Y-m-d H:i:s', $fCMDStartTime)."','"; $sSQL .= date('Y-m-d H:i:s')."','import')"; var_Dump($sSQL); - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); // update the status $sSQL = "UPDATE import_status SET lastimportdate = '$sBatchEnd', indexed=false, sequence_id = $iEndSequence"; var_Dump($sSQL); - chksql($oDB->query($sSQL)); + chksql($oDB->exec($sSQL)); echo date('Y-m-d H:i:s')." Completed download step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60, 2)." minutes\n"; } @@ -440,11 +445,11 @@ if ($aResult['import-osmosis'] || $aResult['import-osmosis-all']) { $sSQL .= date('Y-m-d H:i:s', $fCMDStartTime)."','"; $sSQL .= date('Y-m-d H:i:s')."','index')"; var_Dump($sSQL); - $oDB->query($sSQL); + $oDB->exec($sSQL); echo date('Y-m-d H:i:s')." Completed index step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60, 2)." minutes\n"; $sSQL = 'update import_status set indexed = true'; - $oDB->query($sSQL); + $oDB->exec($sSQL); } else { if ($aResult['import-osmosis-all']) { echo "Error: --no-index cannot be used with continuous imports (--import-osmosis-all).\n"; diff --git a/utils/warm.php b/utils/warm.php index 96372aec..8ba746a2 100644 --- a/utils/warm.php +++ b/utils/warm.php @@ -18,7 +18,8 @@ require_once(CONST_BasePath.'/lib/Geocode.php'); require_once(CONST_BasePath.'/lib/PlaceLookup.php'); require_once(CONST_BasePath.'/lib/ReverseGeocode.php'); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); $bVerbose = $aResult['verbose']; diff --git a/vagrant/Install-on-Centos-7.sh b/vagrant/Install-on-Centos-7.sh index 50eeaaec..19b7ff84 100755 --- a/vagrant/Install-on-Centos-7.sh +++ b/vagrant/Install-on-Centos-7.sh @@ -23,7 +23,7 @@ sudo yum install -y postgresql-server postgresql-contrib postgresql-devel \ postgis postgis-utils \ wget git cmake make gcc gcc-c++ libtool policycoreutils-python \ - php-pgsql php php-pear php-pear-DB php-intl libpqxx-devel \ + php-pgsql php php-intl libpqxx-devel \ proj-epsg bzip2-devel proj-devel libxml2-devel boost-devel \ expat-devel zlib-devel @@ -34,7 +34,9 @@ sudo yum install -y python34-pip python34-setuptools python34-devel \ php-phpunit-PHPUnit pip3 install --user behave nose pytidylib psycopg2 - sudo pear install PHP_CodeSniffer + + composer global require "squizlabs/php_codesniffer=*" + sudo ln -s ~/.config/composer/vendor/bin/phpcs /usr/bin/ # # System Configuration diff --git a/vagrant/Install-on-Ubuntu-16.sh b/vagrant/Install-on-Ubuntu-16.sh index c93cbcac..f05f2b63 100755 --- a/vagrant/Install-on-Ubuntu-16.sh +++ b/vagrant/Install-on-Ubuntu-16.sh @@ -29,7 +29,7 @@ export DEBIAN_FRONTEND=noninteractive #DOCS: libbz2-dev libpq-dev libproj-dev \ postgresql-server-dev-9.5 postgresql-9.5-postgis-2.2 \ postgresql-contrib-9.5 \ - apache2 php php-pgsql libapache2-mod-php php-pear php-db \ + apache2 php php-pgsql libapache2-mod-php \ php-intl git # If you want to run the test suite, you need to install the following @@ -39,7 +39,9 @@ export DEBIAN_FRONTEND=noninteractive #DOCS: python3-psycopg2 python3-tidylib phpunit php-cgi pip3 install --user behave nose - sudo pear install PHP_CodeSniffer + + composer global require "squizlabs/php_codesniffer=*" + sudo ln -s ~/.config/composer/vendor/bin/phpcs /usr/bin/ # # System Configuration diff --git a/vagrant/Install-on-Ubuntu-18-nginx.sh b/vagrant/Install-on-Ubuntu-18-nginx.sh index fdd7c469..49334d44 100755 --- a/vagrant/Install-on-Ubuntu-18-nginx.sh +++ b/vagrant/Install-on-Ubuntu-18-nginx.sh @@ -22,7 +22,7 @@ export DEBIAN_FRONTEND=noninteractive libbz2-dev libpq-dev libproj-dev \ postgresql-server-dev-10 postgresql-10-postgis-2.4 \ postgresql-contrib-10 \ - nginx php-fpm php php-pgsql php-pear php-db \ + nginx php-fpm php php-pgsql \ php-intl git export USERNAME=vagrant diff --git a/vagrant/Install-on-Ubuntu-18.sh b/vagrant/Install-on-Ubuntu-18.sh index 4162f151..c27a5d40 100755 --- a/vagrant/Install-on-Ubuntu-18.sh +++ b/vagrant/Install-on-Ubuntu-18.sh @@ -29,7 +29,7 @@ export DEBIAN_FRONTEND=noninteractive #DOCS: libbz2-dev libpq-dev libproj-dev \ postgresql-server-dev-10 postgresql-10-postgis-2.4 \ postgresql-contrib-10 postgresql-10-postgis-scripts \ - apache2 php php-pgsql libapache2-mod-php php-pear php-db \ + apache2 php php-pgsql libapache2-mod-php \ php-intl git # If you want to run the test suite, you need to install the following @@ -39,7 +39,9 @@ export DEBIAN_FRONTEND=noninteractive #DOCS: python3-psycopg2 python3-tidylib phpunit php-cgi pip3 install --user behave nose - sudo pear install PHP_CodeSniffer + + composer global require "squizlabs/php_codesniffer=*" + sudo ln -s ~/.config/composer/vendor/bin/phpcs /usr/bin/ # # System Configuration diff --git a/vagrant/install-on-travis-ci.sh b/vagrant/install-on-travis-ci.sh index 883a9472..fa2a43e0 100755 --- a/vagrant/install-on-travis-ci.sh +++ b/vagrant/install-on-travis-ci.sh @@ -16,34 +16,16 @@ sudo apt-get install -y -qq libboost-dev libboost-system-dev \ libboost-filesystem-dev libexpat1-dev zlib1g-dev libxml2-dev\ libbz2-dev libpq-dev libproj-dev \ postgresql-server-dev-9.6 postgresql-9.6-postgis-2.4 postgresql-contrib-9.6 \ - apache2 php php-pgsql php-intl php-pear + apache2 php php-pgsql php-intl sudo apt-get install -y -qq python3-dev python3-pip python3-psycopg2 php-cgi pip3 install --quiet behave nose pytidylib psycopg2-binary -# Travis uses phpenv to support multiple PHP versions. We need to make sure -# these packages get installed to the phpenv-set PHP (inside /home/travis/.phpenv/), -# not the system PHP (/usr/bin/php, /usr/share/php/ etc) - -# $PHPENV_VERSION and $TRAVIS_PHP_VERSION are unset. -export PHPENV_VERSION=$(cat /home/travis/.phpenv/version) -echo $PHPENV_VERSION - -# https://github.com/pear/DB -composer global require "pear/db=1.9.3" # https://github.com/squizlabs/PHP_CodeSniffer composer global require "squizlabs/php_codesniffer=*" sudo ln -s /home/travis/.config/composer/vendor/bin/phpcs /usr/bin/ - -# make sure PEAR.php and DB.php are in the include path -tee /tmp/travis.php.ini << EOF -include_path = .:/home/travis/.phpenv/versions/$PHPENV_VERSION/share/pear:/home/travis/.config/composer/vendor/pear/db -EOF -phpenv config-add /tmp/travis.php.ini - - sudo -u postgres createuser -S www-data # Make sure that system servers can read from the home directory: @@ -77,7 +59,7 @@ make tee settings/local.php << EOF connect(); $sSQL = 'select placex.place_id, country_code,'; $sSQL .= " name->'name' as name, i.* from placex, import_polygon_delete i"; diff --git a/website/details.php b/website/details.php index 4a67f70a..c64eaa45 100644 --- a/website/details.php +++ b/website/details.php @@ -12,7 +12,6 @@ $sOutputFormat = $oParams->getSet('format', array('html', 'json'), 'html'); set_exception_handler_by_format($sOutputFormat); $aLangPrefOrder = $oParams->getPreferredLanguages(); -$sLanguagePrefArraySQL = 'ARRAY['.join(',', array_map('getDBQuoted', $aLangPrefOrder)).']'; $sPlaceId = $oParams->getString('place_id'); $sOsmType = $oParams->getSet('osmtype', array('N', 'W', 'R')); @@ -26,7 +25,10 @@ $bIncludeHierarchy = $oParams->getBool('hierarchy', $sOutputFormat == 'html'); $bGroupHierarchy = $oParams->getBool('group_hierarchy', false); $bIncludePolygonAsGeoJSON = $oParams->getBool('polygon_geojson', $sOutputFormat == 'html'); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); + +$sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder)); if ($sOsmType && $iOsmId > 0) { $sSQL = sprintf( @@ -59,7 +61,7 @@ if ($sOsmType && $iOsmId > 0) { $sSQL .= ' ORDER BY updated DESC'; $sSQL .= ' LIMIT 1'; $aPointDetails = chksql($oDB->getRow($sSQL)); - if (!PEAR::isError($aPointDetails) && $aPointDetails) { + if ($aPointDetails) { if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) { $aPointDetails['error_x'] = $aMatches[1]; $aPointDetails['error_y'] = $aMatches[2]; @@ -74,7 +76,7 @@ if ($sOsmType && $iOsmId > 0) { } -if (!$sPlaceId) userError('Please select a place id'); +if ($sPlaceId === false) userError('Please select a place id'); $iPlaceID = (int)$sPlaceId; @@ -141,25 +143,16 @@ $aPointDetails['rank_search_label'] = getSearchRankLabel($aPointDetails['rank_se $sSQL = 'SELECT (each(name)).key,(each(name)).value FROM placex '; $sSQL .= "WHERE place_id = $iPlaceID ORDER BY (each(name)).key"; $aPointDetails['aNames'] = $oDB->getAssoc($sSQL); -if (PEAR::isError($aPointDetails['aNames'])) { // possible timeout - $aPointDetails['aNames'] = array(); -} // Address tags $sSQL = 'SELECT (each(address)).key as key,(each(address)).value FROM placex '; $sSQL .= "WHERE place_id = $iPlaceID ORDER BY key"; $aPointDetails['aAddressTags'] = $oDB->getAssoc($sSQL); -if (PEAR::isError($aPointDetails['aAddressTags'])) { // possible timeout - $aPointDetails['aAddressTags'] = array(); -} // Extra tags $sSQL = 'SELECT (each(extratags)).key,(each(extratags)).value FROM placex '; $sSQL .= "WHERE place_id = $iPlaceID ORDER BY (each(extratags)).key"; $aPointDetails['aExtraTags'] = $oDB->getAssoc($sSQL); -if (PEAR::isError($aPointDetails['aExtraTags'])) { // possible timeout - $aPointDetails['aExtraTags'] = array(); -} // Address $aAddressLines = false; @@ -191,9 +184,6 @@ if ($bIncludeLinkedPlaces) { $sSQL .= " get_name_by_language(name, $sLanguagePrefArraySQL), "; $sSQL .= ' housenumber'; $aLinkedLines = $oDB->getAll($sSQL); - if (PEAR::isError($aLinkedLines)) { // possible timeout - $aLinkedLines = array(); - } } // All places this is an imediate parent of @@ -225,32 +215,20 @@ if ($bIncludeHierarchy) { $sSQL .= ' localname, '; $sSQL .= ' housenumber'; $aHierarchyLines = $oDB->getAll($sSQL); - if (PEAR::isError($aHierarchyLines)) { // possible timeout - $aHierarchyLines = array(); - } } $aPlaceSearchNameKeywords = false; $aPlaceSearchAddressKeywords = false; if ($bIncludeKeywords) { $sSQL = "SELECT * FROM search_name WHERE place_id = $iPlaceID"; - $aPlaceSearchName = $oDB->getRow($sSQL); // can be null - if (!$aPlaceSearchName || PEAR::isError($aPlaceSearchName)) { // possible timeout - $aPlaceSearchName = array(); - } + $aPlaceSearchName = $oDB->getRow($sSQL); if (!empty($aPlaceSearchName)) { $sSQL = 'SELECT * FROM word WHERE word_id in ('.substr($aPlaceSearchName['name_vector'], 1, -1).')'; $aPlaceSearchNameKeywords = $oDB->getAll($sSQL); - if (PEAR::isError($aPlaceSearchNameKeywords)) { // possible timeout - $aPlaceSearchNameKeywords = array(); - } $sSQL = 'SELECT * FROM word WHERE word_id in ('.substr($aPlaceSearchName['nameaddress_vector'], 1, -1).')'; $aPlaceSearchAddressKeywords = $oDB->getAll($sSQL); - if (PEAR::isError($aPlaceSearchAddressKeywords)) { // possible timeout - $aPlaceSearchAddressKeywords = array(); - } } } diff --git a/website/hierarchy.php b/website/hierarchy.php index 419a0474..2c56b015 100644 --- a/website/hierarchy.php +++ b/website/hierarchy.php @@ -10,13 +10,16 @@ $oParams = new Nominatim\ParameterParser(); $sOutputFormat = $oParams->getSet('format', array('html', 'json'), 'html'); $aLangPrefOrder = $oParams->getPreferredLanguages(); -$sLanguagePrefArraySQL = 'ARRAY['.join(',', array_map('getDBQuoted', $aLangPrefOrder)).']'; + $sPlaceId = $oParams->getString('place_id'); $sOsmType = $oParams->getSet('osmtype', array('N', 'W', 'R')); $iOsmId = $oParams->getInt('osmid', -1); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); + +$sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder)); if ($sOsmType && $iOsmId > 0) { $sPlaceId = chksql($oDB->getOne("select place_id from placex where osm_type = '".$sOsmType."' and osm_id = ".$iOsmId." order by type = 'postcode' asc")); diff --git a/website/lookup.php b/website/lookup.php index ae76df2f..20d06af0 100644 --- a/website/lookup.php +++ b/website/lookup.php @@ -15,7 +15,8 @@ set_exception_handler_by_format($sOutputFormat); // Preferred language $aLangPrefOrder = $oParams->getPreferredLanguages(); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); $hLog = logStart($oDB, 'place', $_SERVER['QUERY_STRING'], $aLangPrefOrder); diff --git a/website/polygons.php b/website/polygons.php index e5d459f3..00e043bd 100644 --- a/website/polygons.php +++ b/website/polygons.php @@ -12,7 +12,8 @@ $iDays = $oParams->getInt('days', false); $bReduced = $oParams->getBool('reduced', false); $sClass = $oParams->getString('class', false); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); $iTotalBroken = (int) chksql($oDB->getOne('select count(*) from import_polygon_error')); diff --git a/website/reverse.php b/website/reverse.php index 51a8e904..e1a1b672 100644 --- a/website/reverse.php +++ b/website/reverse.php @@ -16,7 +16,8 @@ set_exception_handler_by_format($sOutputFormat); // Preferred language $aLangPrefOrder = $oParams->getPreferredLanguages(); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); $hLog = logStart($oDB, 'reverse', $_SERVER['QUERY_STRING'], $aLangPrefOrder); diff --git a/website/search.php b/website/search.php index 696cdf73..a9bb3f0b 100644 --- a/website/search.php +++ b/website/search.php @@ -6,7 +6,8 @@ require_once(CONST_BasePath.'/lib/Geocode.php'); require_once(CONST_BasePath.'/lib/output.php'); ini_set('memory_limit', '200M'); -$oDB =& getDB(); +$oDB = new Nominatim\DB(); +$oDB->connect(); $oParams = new Nominatim\ParameterParser(); $oGeocode = new Nominatim\Geocode($oDB); diff --git a/website/status.php b/website/status.php index 90be9388..0d483544 100644 --- a/website/status.php +++ b/website/status.php @@ -7,9 +7,7 @@ require_once(CONST_BasePath.'/lib/Status.php'); $oParams = new Nominatim\ParameterParser(); $sOutputFormat = $oParams->getSet('format', array('text', 'json'), 'text'); -$oDB = DB::connect(CONST_Database_DSN, false); -$oStatus = new Nominatim\Status($oDB); - +$oDB = new Nominatim\DB(); if ($sOutputFormat == 'json') { header('content-type: application/json; charset=UTF-8'); @@ -17,6 +15,7 @@ if ($sOutputFormat == 'json') { try { + $oStatus = new Nominatim\Status($oDB); $oStatus->status(); } catch (Exception $oErr) { if ($sOutputFormat == 'json') { -- 2.39.5