From: marc tobias <mtmail@gmx.net>
Date: Wed, 17 Apr 2019 16:51:22 +0000 (+0200)
Subject: utils/export.php broke after switch to PDO DB abstraction
X-Git-Tag: v3.3.0~7^2
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/0862e21a1bdd7d88719c1062db2f25c073b32fc2?ds=sidebyside;hp=c148b768f4926d578c499d24d93dbf6ea5b0b7e2

utils/export.php broke after switch to PDO DB abstraction
---

diff --git a/lib/DB.php b/lib/DB.php
index 17dfe67d..51fd49fc 100644
--- a/lib/DB.php
+++ b/lib/DB.php
@@ -74,12 +74,7 @@ class DB
     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);
-            }
+            $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
             $row = $stmt->fetch();
         } catch (\PDOException $e) {
             throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
@@ -98,12 +93,7 @@ class DB
     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);
-            }
+            $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
             $row = $stmt->fetch(\PDO::FETCH_NUM);
             if ($row === false) return false;
         } catch (\PDOException $e) {
@@ -123,12 +113,7 @@ class DB
     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);
-            }
+            $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
             $rows = $stmt->fetchAll();
         } catch (\PDOException $e) {
             throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
@@ -148,12 +133,8 @@ class DB
     {
         $aVals = array();
         try {
-            if (isset($aInputVars)) {
-                $stmt = $this->connection->prepare($sSQL);
-                $stmt->execute($aInputVars);
-            } else {
-                $stmt = $this->connection->query($sSQL);
-            }
+            $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
+
             while ($val = $stmt->fetchColumn(0)) { // returns first column or false
                 $aVals[] = $val;
             }
@@ -174,12 +155,8 @@ class DB
     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);
-            }
+            $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
+
             $aList = array();
             while ($aRow = $stmt->fetch(\PDO::FETCH_NUM)) {
                 $aList[$aRow[0]] = $aRow[1];
@@ -190,6 +167,27 @@ class DB
         return $aList;
     }
 
+    /**
+     * Executes query. Returns a PDO statement to iterate over.
+     *
+     * @param string  $sSQL
+     *
+     * @return PDOStatement
+     */
+    public function getQueryStatement($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);
+            }
+        } catch (\PDOException $e) {
+            throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
+        }
+        return $stmt;
+    }
 
     /**
      * St. John's Way => 'St. John\'s Way'
diff --git a/utils/export.php b/utils/export.php
index 9d3037aa..ef55aab2 100644
--- a/utils/export.php
+++ b/utils/export.php
@@ -116,10 +116,8 @@
         $sOsmId = $aCMDResult['restrict-to-osm-relation'];
     }
     if ($sOsmType) {
-        $sSQL = 'select place_id from placex where';
-        $sSQL .= ' osm_type = '.$oDB->getDBQuoted($sOsmType);
-        $sSQL .= ' and osm_id = '.$sOsmId;
-        $sParentId = $oDB->getOne($sSQL);
+        $sSQL = 'select place_id from placex where osm_type = :osm_type and osm_id = :osm_id';
+        $sParentId = $oDB->getOne($sSQL, array('osm_type' => $sOsmType, 'osm_id' => $sOsmId));
         if (!$sParentId) fail('Could not find place '.$sOsmType.' '.$sOsmId);
     }
     if ($sParentId) {
@@ -131,15 +129,15 @@
     // Iterate over placeids
     // to get further hierarchical information
     //var_dump($sPlacexSQL);
-    $aRes =& $oDB->query($sPlacexSQL);
+    $oResults = $oDB->getQueryStatement($sPlacexSQL);
     $fOutstream = fopen('php://output', 'w');
-    while ($aRes->fetchInto($aRow)) {
-    //var_dump($aRow);
+    while ($aRow = $oResults->fetch()) {
+        //var_dump($aRow);
         $iPlaceID = $aRow['place_id'];
-        $sSQL = "select rank_address,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata($iPlaceID, -1)";
+        $sSQL = "select rank_address,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata(:place_id, -1)";
         $sSQL .= ' WHERE isaddress';
         $sSQL .= ' order by rank_address desc,isaddress desc';
-        $aAddressLines = $oDB->getAll($sSQL);
+        $aAddressLines = $oDB->getAll($sSQL, array('place_id' => $iPlaceID));
 
         $aOutput = array_fill(0, $iNumCol, '');
         // output address parts
@@ -154,9 +152,9 @@
                 $sSQL = 'select array_agg(px.postcode) from placex px join place_addressline pa ';
                 $sSQL .= 'on px.place_id = pa.address_place_id ';
                 $sSQL .= 'where pa.cached_rank_address in (5,11) ';
-                $sSQL .= 'and pa.place_id in (select place_id from place_addressline where address_place_id in ('.substr($aRow['place_ids'], 1, -1).')) ';
+                $sSQL .= 'and pa.place_id in (select place_id from place_addressline where address_place_id in (:first_place_id)) ';
                 $sSQL .= 'group by postcode order by count(*) desc limit 1';
-                $sRes = $oDB->getOne($sSQL);
+                $sRes = $oDB->getOne($sSQL, array('first_place_id' => substr($aRow['place_ids'], 1, -1)));
 
                 $aOutput[$aColumnMapping['postcode']] = substr($sRes, 1, -1);
             } else {