From 890d415e1ffd997d11ed35dee04cb01b94ca133f Mon Sep 17 00:00:00 2001 From: marc tobias Date: Sun, 10 Mar 2019 15:42:58 +0100 Subject: [PATCH] Nominatim::DB support input variables, custom error messages --- lib/AddressDetails.php | 2 +- lib/DB.php | 307 +++++++++++++++++++++++++++ lib/Geocode.php | 14 +- lib/PlaceLookup.php | 8 +- lib/ReverseGeocode.php | 46 ++-- lib/SearchContext.php | 6 +- lib/SearchDescription.php | 48 ++--- lib/TokenList.php | 4 +- lib/cmd.php | 5 - lib/db.php | 176 --------------- lib/init-website.php | 7 - lib/init.php | 2 +- lib/setup/AddressLevelParser.php | 10 +- lib/setup/SetupClass.php | 20 +- test/php/Nominatim/DBTest.php | 56 ++++- test/php/Nominatim/StatusTest.php | 2 +- test/php/Nominatim/TokenListTest.php | 2 - utils/update.php | 28 +-- website/deletable.php | 2 +- website/details.php | 26 +-- website/hierarchy.php | 12 +- website/polygons.php | 4 +- website/reverse.php | 7 +- website/search.php | 2 +- 24 files changed, 468 insertions(+), 328 deletions(-) create mode 100644 lib/DB.php delete mode 100644 lib/db.php diff --git a/lib/AddressDetails.php b/lib/AddressDetails.php index 618e10c9..f5293586 100644 --- a/lib/AddressDetails.php +++ b/lib/AddressDetails.php @@ -26,7 +26,7 @@ class AddressDetails $sSQL .= ' FROM get_addressdata('.$iPlaceID.','.$sHousenumber.')'; $sSQL .= ' ORDER BY rank_address DESC, isaddress DESC'; - $this->aAddressLines = chksql($oDB->getAll($sSQL)); + $this->aAddressLines = $oDB->getAll($sSQL); } private static function isAddress($aLine) diff --git a/lib/DB.php b/lib/DB.php new file mode 100644 index 00000000..d0066852 --- /dev/null +++ b/lib/DB.php @@ -0,0 +1,307 @@ +sDSN = $sDSN; + } + + public function connect($bNew = false, $bPersistent = true) + { + if (isset($this->connection) && !$bNew) { + return true; + } + $aConnOptions = array( + \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, + \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC, + \PDO::ATTR_PERSISTENT => $bPersistent + ); + + // 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()); + } + + $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, $aInputVars = null, $sErrMessage = 'Database query failed') + { + $val = null; + try { + if (isset($aInputVars)) { + $stmt = $this->connection->prepare($sSQL); + $stmt->execute($aInputVars); + } else { + $val = $this->connection->exec($sSQL); + } + } catch (\PDOException $e) { + $sErrMessage = $e->message(); + throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); + } + return $val; + } + + /** + * Executes query. Returns first row as array. + * Returns false if no result found. + * + * @param string $sSQL + * + * @return array[] + */ + public function getRow($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed') + { + try { + if (isset($aInputVars)) { + $stmt = $this->connection->prepare($sSQL); + $stmt->execute($aInputVars); + } else { + $stmt = $this->connection->query($sSQL); + } + $row = $stmt->fetch(); + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); + } + return $row; + } + + /** + * Executes query. Returns first value of first result. + * Returns false if no results found. + * + * @param string $sSQL + * + * @return array[] + */ + public function getOne($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed') + { + try { + if (isset($aInputVars)) { + $stmt = $this->connection->prepare($sSQL); + $stmt->execute($aInputVars); + } else { + $stmt = $this->connection->query($sSQL); + } + $row = $stmt->fetch(\PDO::FETCH_NUM); + if ($row === false) return false; + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); + } + return $row[0]; + } + + /** + * Executes query. Returns array of results (arrays). + * Returns empty array if no results found. + * + * @param string $sSQL + * + * @return array[] + */ + public function getAll($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed') + { + try { + if (isset($aInputVars)) { + $stmt = $this->connection->prepare($sSQL); + $stmt->execute($aInputVars); + } else { + $stmt = $this->connection->query($sSQL); + } + $rows = $stmt->fetchAll(); + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); + } + return $rows; + } + + /** + * Executes query. Returns array of the first value of each result. + * Returns empty array if no results found. + * + * @param string $sSQL + * + * @return array[] + */ + public function getCol($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed') + { + $aVals = array(); + try { + if (isset($aInputVars)) { + $stmt = $this->connection->prepare($sSQL); + $stmt->execute($aInputVars); + } else { + $stmt = $this->connection->query($sSQL); + } + while ($val = $stmt->fetchColumn(0)) { // returns first column or false + $aVals[] = $val; + } + } catch (\PDOException $e) { + throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL); + } + return $aVals; + } + + /** + * Executes query. Returns associate array mapping first value to second value of each result. + * Returns empty array if no results found. + * + * @param string $sSQL + * + * @return array[] + */ + public function getAssoc($sSQL, $aInputVars = null, $sErrMessage = 'Database query failed') + { + try { + if (isset($aInputVars)) { + $stmt = $this->connection->prepare($sSQL); + $stmt->execute($aInputVars); + } else { + $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($sErrMessage, 500, null, $e, $sSQL); + } + return $aList; + } + + + /** + * St. John's Way => 'St. John\'s Way' + * + * @param string $sVal Text to be quoted. + * + * @return string + */ + public function getDBQuoted($sVal) + { + return $this->connection->quote($sVal); + } + + /** + * Like getDBQuoted, but takes an array. + * + * @param array $aVals List of text to be quoted. + * + * @return array[] + */ + public function getDBQuotedList($aVals) + { + return array_map(function ($sVal) { + return $this->getDBQuoted($sVal); + }, $aVals); + } + + /** + * [1,2,'b'] => 'ARRAY[1,2,'b']'' + * + * @param array $aVals List of text to be quoted. + * + * @return string + */ + public function getArraySQL($a) + { + return 'ARRAY['.join(',', $a).']'; + } + + public function getLastError() + { + // https://secure.php.net/manual/en/pdo.errorinfo.php + return $this->connection->errorInfo(); + } + + /** + * Check if a table exists in the database. Returns true if it does. + * + * @param string $sTableName + * + * @return boolean + */ + public function tableExists($sTableName) + { + $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = :tablename'; + return ($this->getOne($sSQL, array(':tablename' => $sTableName)) == 1); + } + + /** + * Since the DSN includes the database name, checks if the connection works. + * + * @return boolean + */ + public function databaseExists() + { + $bExists = true; + try { + $this->connect(true); + } catch (\Nominatim\DatabaseError $e) { + $bExists = false; + } + return $bExists; + } + + /** + * e.g. 9.6, 10, 11.2 + * + * @return float + */ + 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]); + } + + /** + * e.g. 2, 2.2 + * + * @return float + */ + 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/Geocode.php b/lib/Geocode.php index 492c6865..9e02150c 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -581,8 +581,9 @@ class Geocode if ($sSpecialTerm && !$aSearches[0]->hasOperator()) { $sSpecialTerm = pg_escape_string($sSpecialTerm); - $sToken = chksql( - $this->oDB->getOne("SELECT make_standard_name('$sSpecialTerm')"), + $sToken = $this->oDB->getOne( + 'SELECT make_standard_name(:term)', + array(':term' => $sSpecialTerm), 'Cannot decode query. Wrong encoding?' ); $sSQL = 'SELECT class, type FROM word '; @@ -590,7 +591,7 @@ class Geocode $sSQL .= ' AND class is not null AND class not in (\'place\')'; Debug::printSQL($sSQL); - $aSearchWords = chksql($this->oDB->getAll($sSQL)); + $aSearchWords = $this->oDB->getAll($sSQL); $aNewSearches = array(); foreach ($aSearches as $oSearch) { foreach ($aSearchWords as $aSearchTerm) { @@ -628,8 +629,9 @@ class Geocode $aTokens = array(); $aPhrases = array(); foreach ($aInPhrases as $iPhrase => $sPhrase) { - $sPhrase = chksql( - $this->oDB->getOne('SELECT make_standard_name('.$this->oDB->getDBQuoted($sPhrase).')'), + $sPhrase = $this->oDB->getOne( + 'SELECT make_standard_name(:phrase)', + array(':phrase' => $sPhrase), 'Cannot normalize query string (is it a UTF-8 string?)' ); if (trim($sPhrase)) { @@ -830,7 +832,7 @@ class Geocode if ($aFilterSql) { $sSQL = join(' UNION ', $aFilterSql); Debug::printSQL($sSQL); - $aFilteredIDs = chksql($this->oDB->getCol($sSQL)); + $aFilteredIDs = $this->oDB->getCol($sSQL); } $tempIDs = array(); diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php index c5aea588..cf3e4f7b 100644 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@ -163,8 +163,8 @@ class PlaceLookup public function lookupOSMID($sType, $iID) { - $sSQL = "select place_id from placex where osm_type = '".$sType."' and osm_id = ".$iID; - $iPlaceID = chksql($this->oDB->getOne($sSQL)); + $sSQL = 'select place_id from placex where osm_type = :type and osm_id = :id'; + $iPlaceID = $this->oDB->getOne($sSQL, array(':type' => $sType, ':id' => $iID)); if (!$iPlaceID) { return null; @@ -425,7 +425,7 @@ class PlaceLookup $sSQL = join(' UNION ', $aSubSelects); Debug::printSQL($sSQL); - $aPlaces = chksql($this->oDB->getAll($sSQL), 'Could not lookup place'); + $aPlaces = $this->oDB->getAll($sSQL, null, 'Could not lookup place'); foreach ($aPlaces as &$aPlace) { if ($this->bAddressDetails) { @@ -514,7 +514,7 @@ class PlaceLookup $sSQL .= $sFrom; } - $aPointPolygon = chksql($this->oDB->getRow($sSQL), 'Could not get outline'); + $aPointPolygon = $this->oDB->getRow($sSQL, null, 'Could not get outline'); if ($aPointPolygon && $aPointPolygon['place_id']) { if ($aPointPolygon['centrelon'] !== null && $aPointPolygon['centrelat'] !== null) { diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index 9601ecf5..ff20691a 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -63,8 +63,9 @@ class ReverseGeocode $sSQL .= ' and indexed_status = 0 and startnumber is not NULL '; $sSQL .= ' ORDER BY distance ASC limit 1'; - return chksql( - $this->oDB->getRow($sSQL), + return $this->oDB->getRow( + $sSQL, + null, 'Could not determine closest housenumber on an osm interpolation line.' ); } @@ -92,8 +93,9 @@ class ReverseGeocode $sSQL = 'SELECT country_code FROM country_osm_grid'; $sSQL .= ' WHERE ST_CONTAINS(geometry, '.$sPointSQL.') LIMIT 1'; - $sCountryCode = chksql( - $this->oDB->getOne($sSQL), + $sCountryCode = $this->oDB->getOne( + $sSQL, + null, 'Could not determine country polygon containing the point.' ); if ($sCountryCode) { @@ -115,10 +117,7 @@ class ReverseGeocode $sSQL .= ' LIMIT 1'; if (CONST_Debug) var_dump($sSQL); - $aPlace = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine place node.' - ); + $aPlace = $this->oDB->getRow($sSQL, null, 'Could not determine place node.'); if ($aPlace) { return new Result($aPlace['place_id']); } @@ -134,10 +133,7 @@ class ReverseGeocode $sSQL .= ' ORDER BY distance ASC'; if (CONST_Debug) var_dump($sSQL); - $aPlace = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine place node.' - ); + $aPlace = $this->oDB->getRow($sSQL, null, 'Could not determine place node.'); if ($aPlace) { return new Result($aPlace['place_id']); } @@ -178,10 +174,8 @@ class ReverseGeocode $sSQL .= ' WHERE ST_CONTAINS(geometry, '.$sPointSQL.' )'; $sSQL .= ' ORDER BY rank_address DESC LIMIT 1'; - $aPoly = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine polygon containing the point.' - ); + $aPoly = $this->oDB->getRow($sSQL, null, 'Could not determine polygon containing the point.'); + if ($aPoly) { // if a polygon is found, search for placenodes begins ... $iParentPlaceID = $aPoly['parent_place_id']; @@ -213,10 +207,7 @@ class ReverseGeocode $sSQL .= ' LIMIT 1'; if (CONST_Debug) var_dump($sSQL); - $aPlacNode = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine place node.' - ); + $aPlacNode = $this->oDB->getRow($sSQL, null, 'Could not determine place node.'); if ($aPlacNode) { return $aPlacNode; } @@ -271,10 +262,7 @@ class ReverseGeocode $sSQL .= ' OR ST_DWithin('.$sPointSQL.', centroid, '.$fSearchDiam.'))'; $sSQL .= ' ORDER BY distance ASC limit 1'; if (CONST_Debug) var_dump($sSQL); - $aPlace = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine closest place.' - ); + $aPlace = $this->oDB->getRow($sSQL, null, 'Could not determine closest place.'); if (CONST_Debug) var_dump($aPlace); if ($aPlace) { @@ -323,10 +311,7 @@ class ReverseGeocode $sSQL .= ' and indexed_status = 0 and linked_place_id is null'; $sSQL .= ' ORDER BY distance ASC limit 1'; if (CONST_Debug) var_dump($sSQL); - $aStreet = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine closest place.' - ); + $aStreet = $this->oDB->getRow($sSQL, null, 'Could not determine closest place.'); if ($aStreet) { if (CONST_Debug) var_dump($aStreet); $oResult = new Result($aStreet['place_id']); @@ -347,10 +332,7 @@ class ReverseGeocode $sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, 0.001)'; $sSQL .= ' ORDER BY distance ASC limit 1'; if (CONST_Debug) var_dump($sSQL); - $aPlaceTiger = chksql( - $this->oDB->getRow($sSQL), - 'Could not determine closest Tiger place.' - ); + $aPlaceTiger = $this->oDB->getRow($sSQL, null, 'Could not determine closest Tiger place.'); if ($aPlaceTiger) { if (CONST_Debug) var_dump('found Tiger housenumber', $aPlaceTiger); $oResult = new Result($aPlaceTiger['place_id'], Result::TABLE_TIGER); diff --git a/lib/SearchContext.php b/lib/SearchContext.php index 84159b83..3cb11e82 100644 --- a/lib/SearchContext.php +++ b/lib/SearchContext.php @@ -126,7 +126,7 @@ class SearchContext * The viewbox may be bounded which means that no search results * must be outside the viewbox. * - * @param object $oDB DB connection to use for computing the box. + * @param object $oDB Nominatim::DB instance to use for computing the box. * @param string[] $aRoutePoints List of x,y coordinates along a route. * @param float $fRouteWidth Buffer around the route to use. * @param bool $bBounded True if the viewbox bounded. @@ -146,11 +146,11 @@ class SearchContext $this->sqlViewboxCentre .= ")'::geometry,4326)"; $sSQL = 'ST_BUFFER('.$this->sqlViewboxCentre.','.($fRouteWidth/69).')'; - $sGeom = chksql($oDB->getOne('select '.$sSQL), 'Could not get small viewbox'); + $sGeom = $oDB->getOne('select '.$sSQL, null, 'Could not get small viewbox'); $this->sqlViewboxSmall = "'".$sGeom."'::geometry"; $sSQL = 'ST_BUFFER('.$this->sqlViewboxCentre.','.($fRouteWidth/30).')'; - $sGeom = chksql($oDB->getOne('select '.$sSQL), 'Could not get large viewbox'); + $sGeom = $oDB->getOne('select '.$sSQL, null, 'Could not get large viewbox'); $this->sqlViewboxLarge = "'".$sGeom."'::geometry"; } diff --git a/lib/SearchDescription.php b/lib/SearchDescription.php index fac21972..358e6969 100644 --- a/lib/SearchDescription.php +++ b/lib/SearchDescription.php @@ -404,7 +404,7 @@ class SearchDescription /** * Query database for places that match this search. * - * @param object $oDB Database connection to use. + * @param object $oDB Nominatim::DB instance to use. * @param integer $iMinRank Minimum address rank to restrict search to. * @param integer $iMaxRank Maximum address rank to restrict search to. * @param integer $iLimit Maximum number of results. @@ -479,7 +479,7 @@ class SearchDescription $sSQL .= ' WHERE place_id in ('.$sPlaceIds.')'; $sSQL .= " AND postcode != '".$this->sPostcode."'"; Debug::printSQL($sSQL); - $aFilteredPlaceIDs = chksql($oDB->getCol($sSQL)); + $aFilteredPlaceIDs = $oDB->getCol($sSQL); if ($aFilteredPlaceIDs) { foreach ($aFilteredPlaceIDs as $iPlaceId) { $aResults[$iPlaceId]->iResultRank++; @@ -523,8 +523,7 @@ class SearchDescription $aDBResults = array(); $sPoiTable = $this->poiTable(); - $sSQL = 'SELECT count(*) FROM pg_tables WHERE tablename = \''.$sPoiTable."'"; - if (chksql($oDB->getOne($sSQL))) { + if ($oDB->tableExists($sPoiTable)) { $sSQL = 'SELECT place_id FROM '.$sPoiTable.' ct'; if ($this->oContext->sqlCountryList) { $sSQL .= ' JOIN placex USING (place_id)'; @@ -544,14 +543,14 @@ class SearchDescription } elseif ($this->oContext->hasNearPoint()) { $sSQL .= ' ORDER BY '.$this->oContext->distanceSQL('ct.centroid').' ASC'; } - $sSQL .= " limit $iLimit"; + $sSQL .= " LIMIT $iLimit"; Debug::printSQL($sSQL); - $aDBResults = chksql($oDB->getCol($sSQL)); + $aDBResults = $oDB->getCol($sSQL); } if ($this->oContext->hasNearPoint()) { $sSQL = 'SELECT place_id FROM placex WHERE '; - $sSQL .= 'class=\''.$this->sClass."' and type='".$this->sType."'"; + $sSQL .= 'class = :class and type = :type'; $sSQL .= ' AND '.$this->oContext->withinSQL('geometry'); $sSQL .= ' AND linked_place_id is null'; if ($this->oContext->sqlCountryList) { @@ -560,7 +559,10 @@ class SearchDescription $sSQL .= ' ORDER BY '.$this->oContext->distanceSQL('centroid').' ASC'; $sSQL .= " LIMIT $iLimit"; Debug::printSQL($sSQL); - $aDBResults = chksql($oDB->getCol($sSQL)); + $aDBResults = $oDB->getCol( + $sSQL, + array(':class' => $this->sClass, ':type' => $this->sType) + ); } $aResults = array(); @@ -592,7 +594,7 @@ class SearchDescription Debug::printSQL($sSQL); $aResults = array(); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId, Result::TABLE_POSTCODE); } @@ -722,10 +724,7 @@ class SearchDescription Debug::printSQL($sSQL); - $aDBResults = chksql( - $oDB->getAll($sSQL), - 'Could not get places for search terms.' - ); + $aDBResults = $oDB->getAll($sSQL, null, 'Could not get places for search terms.'); foreach ($aDBResults as $aResult) { $oResult = new Result($aResult['place_id']); @@ -755,7 +754,7 @@ class SearchDescription Debug::printSQL($sSQL); // XXX should inherit the exactMatches from its parent - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId); } @@ -781,7 +780,7 @@ class SearchDescription Debug::printSQL($sSQL); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $oResult = new Result($iPlaceId, Result::TABLE_OSMLINE); $oResult->iHouseNumber = $iHousenumber; $aResults[$iPlaceId] = $oResult; @@ -797,7 +796,7 @@ class SearchDescription Debug::printSQL($sSQL); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId, Result::TABLE_AUX); } } @@ -818,7 +817,7 @@ class SearchDescription Debug::printSQL($sSQL); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $oResult = new Result($iPlaceId, Result::TABLE_TIGER); $oResult->iHouseNumber = $iHousenumber; $aResults[$iPlaceId] = $oResult; @@ -852,7 +851,7 @@ class SearchDescription Debug::printSQL($sSQL); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId); } } @@ -860,12 +859,11 @@ class SearchDescription // NEAR and IN are handled the same if ($this->iOperator == Operator::TYPE || $this->iOperator == Operator::NEAR) { $sClassTable = $this->poiTable(); - $sSQL = "SELECT count(*) FROM pg_tables WHERE tablename = '$sClassTable'"; - $bCacheTable = (bool) chksql($oDB->getOne($sSQL)); + $bCacheTable = $oDB->tableExists($sClassTable); $sSQL = "SELECT min(rank_search) FROM placex WHERE place_id in ($sPlaceIDs)"; Debug::printSQL($sSQL); - $iMaxRank = (int)chksql($oDB->getOne($sSQL)); + $iMaxRank = (int) $oDB->getOne($sSQL); // For state / country level searches the normal radius search doesn't work very well $sPlaceGeom = false; @@ -878,7 +876,7 @@ class SearchDescription $sSQL .= ' ORDER BY rank_search ASC '; $sSQL .= ' LIMIT 1'; Debug::printSQL($sSQL); - $sPlaceGeom = chksql($oDB->getOne($sSQL)); + $sPlaceGeom = $oDB->getOne($sSQL); } if ($sPlaceGeom) { @@ -888,7 +886,7 @@ class SearchDescription $sSQL = 'SELECT place_id FROM placex'; $sSQL .= " WHERE place_id in ($sPlaceIDs) and rank_search < $iMaxRank"; Debug::printSQL($sSQL); - $aPlaceIDs = chksql($oDB->getCol($sSQL)); + $aPlaceIDs = $oDB->getCol($sSQL); $sPlaceIDs = join(',', $aPlaceIDs); } @@ -934,7 +932,7 @@ class SearchDescription Debug::printSQL($sSQL); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId); } } else { @@ -966,7 +964,7 @@ class SearchDescription Debug::printSQL($sSQL); - foreach (chksql($oDB->getCol($sSQL)) as $iPlaceId) { + foreach ($oDB->getCol($sSQL) as $iPlaceId) { $aResults[$iPlaceId] = new Result($iPlaceId); } } diff --git a/lib/TokenList.php b/lib/TokenList.php index 2d0c7c16..84dc98d0 100644 --- a/lib/TokenList.php +++ b/lib/TokenList.php @@ -71,7 +71,7 @@ class TokenList /** * Add token information from the word table in the database. * - * @param object $oDB Database connection. + * @param object $oDB Nominatim::DB instance. * @param string[] $aTokens List of tokens to look up in the database. * @param string[] $aCountryCodes List of country restrictions. * @param string $sNormQuery Normalized query string. @@ -89,7 +89,7 @@ class TokenList Debug::printSQL($sSQL); - $aDBWords = chksql($oDB->getAll($sSQL), 'Could not get word tokens.'); + $aDBWords = $oDB->getAll($sSQL, null, 'Could not get word tokens.'); foreach ($aDBWords as $aWord) { $oToken = null; diff --git a/lib/cmd.php b/lib/cmd.php index 43669069..32fdc857 100644 --- a/lib/cmd.php +++ b/lib/cmd.php @@ -120,11 +120,6 @@ function showUsage($aSpec, $bExit = false, $sError = false) exit; } -function chksql($oSql, $sMsg = false) -{ - return $oSql; -} - function info($sMsg) { echo date('Y-m-d H:i:s == ').$sMsg."\n"; diff --git a/lib/db.php b/lib/db.php deleted file mode 100644 index cac6f2cd..00000000 --- a/lib/db.php +++ /dev/null @@ -1,176 +0,0 @@ -sDSN = $sDSN; - } - - 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 - ); - - // 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()); - } - - $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 39df4022..67fb52d1 100644 --- a/lib/init-website.php +++ b/lib/init-website.php @@ -10,13 +10,6 @@ require_once(CONST_Debug ? 'DebugHtml.php' : 'DebugNone.php'); * */ - -function chksql($oSql, $sMsg = 'Database request failed') -{ - return $oSql; -} - - function userError($sMsg) { throw new Exception($sMsg, 400); diff --git a/lib/init.php b/lib/init.php index d2188071..0bd23e31 100644 --- a/lib/init.php +++ b/lib/init.php @@ -1,7 +1,7 @@ exec('DROP TABLE IF EXISTS '.$sTable)); + $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->exec($sSql)); + $oDB->exec($sSql); - $sSql = 'CREATE UNIQUE INDEX ON '.$sTable.'(country_code, class, type)'; - chksql($oDB->exec($sSql)); + $sSql = 'CREATE UNIQUE INDEX ON '.$sTable.' (country_code, class, type)'; + $oDB->exec($sSql); $sSql = 'INSERT INTO '.$sTable.' VALUES '; foreach ($this->aLevels as $aLevel) { @@ -93,6 +93,6 @@ class AddressLevelParser } } } - chksql($oDB->exec(rtrim($sSql, ','))); + $oDB->exec(rtrim($sSql, ',')); } } diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php index 9cc00a5f..9fcec2f0 100755 --- a/lib/setup/SetupClass.php +++ b/lib/setup/SetupClass.php @@ -74,15 +74,9 @@ class SetupFunctions public function createDB() { info('Create DB'); - $bExists = true; - try { - $oDB = new \Nominatim\DB; - $oDB->connect(); - } catch (\Nominatim\DatabaseError $e) { - $bExists = false; - } + $oDB = new \Nominatim\DB; - if ($bExists) { + if ($oDB->databaseExists()) { fail('database already exists ('.CONST_Database_DSN.')'); } @@ -122,7 +116,7 @@ class SetupFunctions // For extratags and namedetails the hstore_to_json converter is // needed which is only available from Postgresql 9.3+. For older // versions add a dummy function that returns nothing. - $iNumFunc = chksql($this->oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'")); + $iNumFunc = $this->oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'"); if ($iNumFunc == 0) { $this->pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable"); @@ -142,7 +136,7 @@ class SetupFunctions $this->pgsqlRunScript('ALTER FUNCTION ST_Distance_Spheroid(geometry, geometry, spheroid) RENAME TO ST_DistanceSpheroid'); } - $i = chksql($this->oDB->getOne("select count(*) from pg_user where usename = '".CONST_Database_Web_User."'")); + $i = $this->oDB->getOne("select count(*) from pg_user where usename = '".CONST_Database_Web_User."'"); if ($i == 0) { echo "\nERROR: Web user '".CONST_Database_Web_User."' does not exist. Create it with:\n"; echo "\n createuser ".CONST_Database_Web_User."\n\n"; @@ -224,7 +218,7 @@ class SetupFunctions $this->runWithPgEnv($osm2pgsql); - if (!$this->sIgnoreErrors && !chksql($this->oDB->getRow('select * from place limit 1'))) { + if (!$this->sIgnoreErrors && !$this->oDB->getRow('select * from place limit 1')) { fail('No Data'); } } @@ -386,7 +380,7 @@ class SetupFunctions echo '.'; $sSQL = 'select distinct partition from country_name'; - $aPartitions = chksql($this->oDB->getCol($sSQL)); + $aPartitions = $this->oDB->getCol($sSQL); if (!$this->bNoPartitions) $aPartitions[] = 0; foreach ($aPartitions as $sPartition) { @@ -734,7 +728,7 @@ class SetupFunctions ); $aDropTables = array(); - $aHaveTables = chksql($this->oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'")); + $aHaveTables = $this->oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'"); foreach ($aHaveTables as $sTable) { $bFound = false; diff --git a/test/php/Nominatim/DBTest.php b/test/php/Nominatim/DBTest.php index e7bd18c7..38874c88 100644 --- a/test/php/Nominatim/DBTest.php +++ b/test/php/Nominatim/DBTest.php @@ -3,10 +3,32 @@ namespace Nominatim; require_once(CONST_BasePath.'/lib/lib.php'); -require_once(CONST_BasePath.'/lib/db.php'); +require_once(CONST_BasePath.'/lib/DB.php'); +// subclassing so we can set the protected connection variable +class NominatimSubClassedDB extends \Nominatim\DB +{ + public function setConnection($oConnection) + { + $this->connection = $oConnection; + } +} + +// phpcs:ignore PSR1.Classes.ClassDeclaration.MultipleClasses class DBTest extends \PHPUnit\Framework\TestCase { + public function testReusingConnection() + { + $oDB = new NominatimSubClassedDB(''); + $oDB->setConnection('anything'); + $this->assertTrue($oDB->connect()); + } + + public function testDatabaseExists() + { + $oDB = new \Nominatim\DB(''); + $this->assertFalse($oDB->databaseExists()); + } public function testErrorHandling() { @@ -36,9 +58,35 @@ class DBTest extends \PHPUnit\Framework\TestCase throw new \PDOException('ERROR: syntax error at or near "FROM"'); })); - $oDB = new \Nominatim\DB(''); - $oDB->connection = $oPDOStub; - $oDB->tableExists('abc'); + $oDB = new NominatimSubClassedDB(''); + $oDB->setConnection($oPDOStub); + $oDB->getOne('SELECT name FROM'); + } + + public function testGetPostgresVersion() + { + $oDBStub = $this->getMockBuilder(\Nominatim\DB::class) + ->disableOriginalConstructor() + ->setMethods(array('getOne')) + ->getMock(); + + $oDBStub->method('getOne') + ->willReturn('100006'); + + $this->assertEquals(10, $oDBStub->getPostgresVersion()); + } + + public function testGetPostgisVersion() + { + $oDBStub = $this->getMockBuilder(\Nominatim\DB::class) + ->disableOriginalConstructor() + ->setMethods(array('getOne')) + ->getMock(); + + $oDBStub->method('getOne') + ->willReturn('2.4.4'); + + $this->assertEquals(2.4, $oDBStub->getPostgisVersion()); } public function testParseDSN() diff --git a/test/php/Nominatim/StatusTest.php b/test/php/Nominatim/StatusTest.php index dbf15fd4..f45e6633 100644 --- a/test/php/Nominatim/StatusTest.php +++ b/test/php/Nominatim/StatusTest.php @@ -2,7 +2,7 @@ namespace Nominatim; -require_once(CONST_BasePath.'/lib/db.php'); +require_once(CONST_BasePath.'/lib/DB.php'); require_once(CONST_BasePath.'/lib/Status.php'); diff --git a/test/php/Nominatim/TokenListTest.php b/test/php/Nominatim/TokenListTest.php index 417f52c0..d31b219b 100644 --- a/test/php/Nominatim/TokenListTest.php +++ b/test/php/Nominatim/TokenListTest.php @@ -2,8 +2,6 @@ namespace Nominatim; -// require_once(CONST_BasePath.'/lib/db.php'); -// require_once(CONST_BasePath.'/lib/cmd.php'); require_once(CONST_BasePath.'/lib/TokenList.php'); diff --git a/utils/update.php b/utils/update.php index 96789ef4..a18c1721 100644 --- a/utils/update.php +++ b/utils/update.php @@ -143,7 +143,7 @@ if ($aResult['init-updates']) { } if ($aResult['check-for-updates']) { - $aLastState = chksql($oDB->getRow('SELECT sequence_id FROM import_status')); + $aLastState = $oDB->getRow('SELECT sequence_id FROM import_status'); if (!$aLastState['sequence_id']) { fail('Updates not set up. Please run ./utils/update.php --init-updates.'); @@ -231,13 +231,13 @@ if ($aResult['deduplicate']) { } $sSQL = 'select partition from country_name order by country_code'; - $aPartitions = chksql($oDB->getCol($sSQL)); + $aPartitions = $oDB->getCol($sSQL); $aPartitions[] = 0; // we don't care about empty search_name_* partitions, they can't contain mentions of duplicates foreach ($aPartitions as $i => $sPartition) { $sSQL = 'select count(*) from search_name_'.$sPartition; - $nEntries = chksql($oDB->getOne($sSQL)); + $nEntries = $oDB->getOne($sSQL); if ($nEntries == 0) { unset($aPartitions[$i]); } @@ -246,7 +246,7 @@ if ($aResult['deduplicate']) { $sSQL = "select word_token,count(*) from word where substr(word_token, 1, 1) = ' '"; $sSQL .= ' and class is null and type is null and country_code is null'; $sSQL .= ' group by word_token having count(*) > 1 order by word_token'; - $aDuplicateTokens = chksql($oDB->getAll($sSQL)); + $aDuplicateTokens = $oDB->getAll($sSQL); foreach ($aDuplicateTokens as $aToken) { if (trim($aToken['word_token']) == '' || trim($aToken['word_token']) == '-') continue; echo 'Deduping '.$aToken['word_token']."\n"; @@ -254,7 +254,7 @@ if ($aResult['deduplicate']) { $sSQL .= ' (select count(*) from search_name where nameaddress_vector @> ARRAY[word_id]) as num'; $sSQL .= " from word where word_token = '".$aToken['word_token']; $sSQL .= "' and class is null and type is null and country_code is null order by num desc"; - $aTokenSet = chksql($oDB->getAll($sSQL)); + $aTokenSet = $oDB->getAll($sSQL); $aKeep = array_shift($aTokenSet); $iKeepID = $aKeep['word_id']; @@ -264,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->exec($sSQL)); + $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->exec($sSQL)); + $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->exec($sSQL)); + $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->exec($sSQL)); + $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->exec($sSQL)); + $oDB->exec($sSQL); } $sSQL = 'delete from word where word_id = '.$aRemove['word_id']; - chksql($oDB->exec($sSQL)); + $oDB->exec($sSQL); } } } @@ -340,7 +340,7 @@ if ($aResult['import-osmosis'] || $aResult['import-osmosis-all']) { while (true) { $fStartTime = time(); - $aLastState = chksql($oDB->getRow('SELECT *, EXTRACT (EPOCH FROM lastimportdate) as unix_ts FROM import_status')); + $aLastState = $oDB->getRow('SELECT *, EXTRACT (EPOCH FROM lastimportdate) as unix_ts FROM import_status'); if (!$aLastState['sequence_id']) { echo "Updates not set up. Please run ./utils/update.php --init-updates.\n"; @@ -418,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->exec($sSQL)); + $oDB->exec($sSQL); // update the status $sSQL = "UPDATE import_status SET lastimportdate = '$sBatchEnd', indexed=false, sequence_id = $iEndSequence"; var_Dump($sSQL); - chksql($oDB->exec($sSQL)); + $oDB->exec($sSQL); echo date('Y-m-d H:i:s')." Completed download step for $sBatchEnd in ".round((time()-$fCMDStartTime)/60, 2)." minutes\n"; } diff --git a/website/deletable.php b/website/deletable.php index 4904b94c..ac4294ba 100644 --- a/website/deletable.php +++ b/website/deletable.php @@ -14,7 +14,7 @@ $sSQL = 'select placex.place_id, country_code,'; $sSQL .= " name->'name' as name, i.* from placex, import_polygon_delete i"; $sSQL .= ' where placex.osm_id = i.osm_id and placex.osm_type = i.osm_type'; $sSQL .= ' and placex.class = i.class and placex.type = i.type'; -$aPolygons = chksql($oDB->getAll($sSQL), 'Could not get list of deleted OSM elements.'); +$aPolygons = $oDB->getAll($sSQL, null, 'Could not get list of deleted OSM elements.'); if (CONST_Debug) { var_dump($aPolygons); diff --git a/website/details.php b/website/details.php index c64eaa45..b9818bbf 100644 --- a/website/details.php +++ b/website/details.php @@ -31,17 +31,13 @@ $oDB->connect(); $sLanguagePrefArraySQL = $oDB->getArraySQL($oDB->getDBQuotedList($aLangPrefOrder)); if ($sOsmType && $iOsmId > 0) { - $sSQL = sprintf( - "SELECT place_id FROM placex WHERE osm_type='%s' AND osm_id=%d", - $sOsmType, - $iOsmId - ); + $sSQL = 'SELECT place_id FROM placex WHERE osm_type = :type AND osm_id = :id'; // osm_type and osm_id are not unique enough if ($sClass) { $sSQL .= " AND class='".$sClass."'"; } $sSQL .= ' ORDER BY class ASC'; - $sPlaceId = chksql($oDB->getOne($sSQL)); + $sPlaceId = $oDB->getOne($sSQL, array(':type' => $sOsmType, ':id' => $iOsmId)); // Be nice about our error messages for broken geometry @@ -56,11 +52,11 @@ if ($sOsmType && $iOsmId > 0) { $sSQL .= ' ST_AsText(prevgeometry) AS prevgeom, '; $sSQL .= ' ST_AsText(newgeometry) AS newgeom'; $sSQL .= ' FROM import_polygon_error '; - $sSQL .= " WHERE osm_type = '".$sOsmType."'"; - $sSQL .= ' AND osm_id = '.$iOsmId; + $sSQL .= ' WHERE osm_type = :type'; + $sSQL .= ' AND osm_id = :id'; $sSQL .= ' ORDER BY updated DESC'; $sSQL .= ' LIMIT 1'; - $aPointDetails = chksql($oDB->getRow($sSQL)); + $aPointDetails = $oDB->getRow($sSQL, array(':type' => $sOsmType, ':id' => $iOsmId)); if ($aPointDetails) { if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) { $aPointDetails['error_x'] = $aMatches[1]; @@ -81,20 +77,20 @@ if ($sPlaceId === false) userError('Please select a place id'); $iPlaceID = (int)$sPlaceId; if (CONST_Use_US_Tiger_Data) { - $iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_property_tiger WHERE place_id = '.$iPlaceID)); + $iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_tiger WHERE place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; } // interpolated house numbers -$iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_property_osmline WHERE place_id = '.$iPlaceID)); +$iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_osmline WHERE place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; // artificial postcodes -$iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_postcode WHERE place_id = '.$iPlaceID)); +$iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_postcode WHERE place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; if (CONST_Use_Aux_Location_data) { - $iParentPlaceID = chksql($oDB->getOne('SELECT parent_place_id FROM location_property_aux WHERE place_id = '.$iPlaceID)); + $iParentPlaceID = $oDB->getOne('SELECT parent_place_id FROM location_property_aux WHERE place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; } @@ -129,7 +125,7 @@ if ($bIncludePolygonAsGeoJSON) { $sSQL .= ' FROM placex '; $sSQL .= " WHERE place_id = $iPlaceID"; -$aPointDetails = chksql($oDB->getRow($sSQL), 'Could not get details of place object.'); +$aPointDetails = $oDB->getRow($sSQL, null, 'Could not get details of place object.'); if (!$aPointDetails) { userError('Unknown place id.'); @@ -236,7 +232,7 @@ logEnd($oDB, $hLog, 1); if ($sOutputFormat=='html') { $sSQL = "SELECT TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' FROM import_status LIMIT 1"; - $sDataDate = chksql($oDB->getOne($sSQL)); + $sDataDate = $oDB->getOne($sSQL); $sTileURL = CONST_Map_Tile_URL; $sTileAttribution = CONST_Map_Tile_Attribution; } diff --git a/website/hierarchy.php b/website/hierarchy.php index 2c56b015..b31b85d1 100644 --- a/website/hierarchy.php +++ b/website/hierarchy.php @@ -22,7 +22,7 @@ $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")); + $sPlaceId = $oDB->getOne("select place_id from placex where osm_type = '".$sOsmType."' and osm_id = ".$iOsmId." order by type = 'postcode' asc"); // Be nice about our error messages for broken geometry if (!$sPlaceId) { @@ -31,7 +31,7 @@ if ($sOsmType && $iOsmId > 0) { $sSQL .= ' ST_AsText(prevgeometry) as prevgeom, ST_AsText(newgeometry) as newgeom'; $sSQL .= " from import_polygon_error where osm_type = '".$sOsmType; $sSQL .= "' and osm_id = ".$iOsmId.' order by updated desc limit 1'; - $aPointDetails = chksql($oDB->getRow($sSQL)); + $aPointDetails = $oDB->getRow($sSQL); if ($aPointDetails) { if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) { $aPointDetails['error_x'] = $aMatches[1]; @@ -48,12 +48,12 @@ if (!$sPlaceId) userError('Please select a place id'); $iPlaceID = (int)$sPlaceId; if (CONST_Use_US_Tiger_Data) { - $iParentPlaceID = chksql($oDB->getOne('select parent_place_id from location_property_tiger where place_id = '.$iPlaceID)); + $iParentPlaceID = $oDB->getOne('select parent_place_id from location_property_tiger where place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; } if (CONST_Use_Aux_Location_data) { - $iParentPlaceID = chksql($oDB->getOne('select parent_place_id from location_property_aux where place_id = '.$iPlaceID)); + $iParentPlaceID = $oDB->getOne('select parent_place_id from location_property_aux where place_id = '.$iPlaceID); if ($iParentPlaceID) $iPlaceID = $iParentPlaceID; } @@ -89,7 +89,7 @@ if ($sOutputFormat == 'json') { exit; } -$aRelatedPlaceIDs = chksql($oDB->getCol($sSQL = "select place_id from placex where linked_place_id = $iPlaceID or place_id = $iPlaceID")); +$aRelatedPlaceIDs = $oDB->getCol("select place_id from placex where linked_place_id = $iPlaceID or place_id = $iPlaceID"); $sSQL = 'select obj.place_id, osm_type, osm_id, class, type, housenumber, admin_level,'; $sSQL .= " rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, st_area(geometry) as area, "; @@ -97,7 +97,7 @@ $sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, lengt $sSQL .= ' from (select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, rank_search, geometry, name from placex '; $sSQL .= ' where parent_place_id in ('.join(',', $aRelatedPlaceIDs).') and name is not null order by rank_address asc,rank_search asc limit 500) as obj'; $sSQL .= ' order by rank_address asc,rank_search asc,localname,class, type,housenumber'; -$aParentOfLines = chksql($oDB->getAll($sSQL)); +$aParentOfLines = $oDB->getAll($sSQL); if (!empty($aParentOfLines)) { echo '

Parent Of:

'; diff --git a/website/polygons.php b/website/polygons.php index 00e043bd..a2264b18 100644 --- a/website/polygons.php +++ b/website/polygons.php @@ -15,7 +15,7 @@ $sClass = $oParams->getString('class', false); $oDB = new Nominatim\DB(); $oDB->connect(); -$iTotalBroken = (int) chksql($oDB->getOne('select count(*) from import_polygon_error')); +$iTotalBroken = (int) $oDB->getOne('select count(*) from import_polygon_error'); $aPolygons = array(); while ($iTotalBroken && empty($aPolygons)) { @@ -37,7 +37,7 @@ while ($iTotalBroken && empty($aPolygons)) { } $sSQL .= ' order by updated desc limit 1000'; - $aPolygons = chksql($oDB->getAll($sSQL)); + $aPolygons = $oDB->getAll($sSQL); } if (CONST_Debug) { diff --git a/website/reverse.php b/website/reverse.php index e1a1b672..7b9ef3b3 100644 --- a/website/reverse.php +++ b/website/reverse.php @@ -75,13 +75,16 @@ if (CONST_Debug) { } if ($sOutputFormat == 'html') { - $sDataDate = chksql($oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1")); + $sDataDate = $oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1"); $sTileURL = CONST_Map_Tile_URL; $sTileAttribution = CONST_Map_Tile_Attribution; } elseif ($sOutputFormat == 'geocodejson') { $sQuery = $fLat.','.$fLon; if (isset($aPlace['place_id'])) { - $fDistance = chksql($oDB->getOne('SELECT ST_Distance(ST_SetSRID(ST_Point('.$fLon.','.$fLat.'),4326), centroid) FROM placex where place_id='.$aPlace['place_id'])); + $fDistance = $oDB->getOne( + 'SELECT ST_Distance(ST_SetSRID(ST_Point(:lon,:lat),4326), centroid) FROM placex where place_id = :placeid', + array(':lon' => $fLon, ':lat' => $fLat, ':placeid' => $aPlace['place_id']) + ); } } diff --git a/website/search.php b/website/search.php index a9bb3f0b..26c10122 100644 --- a/website/search.php +++ b/website/search.php @@ -66,7 +66,7 @@ $hLog = logStart($oDB, 'search', $oGeocode->getQueryString(), $aLangPrefOrder); $aSearchResults = $oGeocode->lookup(); if ($sOutputFormat=='html') { - $sDataDate = chksql($oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1")); + $sDataDate = $oDB->getOne("select TO_CHAR(lastimportdate,'YYYY/MM/DD HH24:MI')||' GMT' from import_status limit 1"); } logEnd($oDB, $hLog, count($aSearchResults)); -- 2.39.5