From: Sarah Hoffmann <lonvia@denofr.de>
Date: Sun, 8 Oct 2017 08:06:17 +0000 (+0200)
Subject: add function to convert array to SQL
X-Git-Tag: v3.1.0~47^2~15
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/d72c8633531c859ebf20ed366f1d6976853ffe0d

add function to convert array to SQL
---

diff --git a/lib/Geocode.php b/lib/Geocode.php
index f65a485a..f3cc10da 100644
--- a/lib/Geocode.php
+++ b/lib/Geocode.php
@@ -411,7 +411,10 @@ class Geocode
         //$aPlaceIDs is an array with key: placeID and value: tiger-housenumber, if found, else -1
         if (sizeof($aPlaceIDs) == 0) return array();
 
-        $sLanguagePrefArraySQL = "ARRAY[".join(',', array_map("getDBQuoted", $this->aLangPrefOrder))."]";
+        $sLanguagePrefArraySQL = getArraySQL(
+            array_map("getDBQuoted",
+            $this->aLangPrefOrder)
+        );
 
         // Get the details for display (is this a redundant extra step?)
         $sPlaceIDs = join(',', array_keys($aPlaceIDs));
@@ -980,7 +983,10 @@ class Geocode
         if (!$this->sQuery && !$this->aStructuredQuery) return array();
 
         $sNormQuery = $this->normTerm($this->sQuery);
-        $sLanguagePrefArraySQL = "ARRAY[".join(',', array_map("getDBQuoted", $this->aLangPrefOrder))."]";
+        $sLanguagePrefArraySQL = getArraySQL(
+            array_map("getDBQuoted",
+            $this->aLangPrefOrder)
+        );
         $sCountryCodesSQL = false;
         if ($this->aCountryCodes) {
             $sCountryCodesSQL = join(',', array_map('addQuotes', $this->aCountryCodes));
diff --git a/lib/SearchDescription.php b/lib/SearchDescription.php
index 99860ce0..1a994acd 100644
--- a/lib/SearchDescription.php
+++ b/lib/SearchDescription.php
@@ -130,20 +130,11 @@ class SearchDescription
         return (bool) $this->sHouseNumber;
     }
 
-    public function poiTable()
+    private function poiTable()
     {
         return 'place_classtype_'.$this->sClass.'_'.$this->sType;
     }
 
-    public function addressArraySQL()
-    {
-        return 'ARRAY['.join(',', $this->aAddress).']';
-    }
-    public function nameArraySQL()
-    {
-        return 'ARRAY['.join(',', $this->aName).']';
-    }
-
     public function countryCodeSQL($sVar, $sCountryList)
     {
         if ($this->sCountryCode) {
@@ -263,7 +254,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 .= '      @> '.$this->addressArraySQL().' AND ';
+            $sSQL .= '      @> '.getArraySQL($this->aAddress).' AND ';
         } else {
             $sSQL .= 'WHERE ';
         }
@@ -312,7 +303,7 @@ class SearchDescription
         }
 
         if (sizeof($this->aName)) {
-            $aTerms[] = 'name_vector @> '.$this->nameArraySQL();
+            $aTerms[] = 'name_vector @> '.getArraySQL($this->aName);
         }
         if (sizeof($this->aAddress)) {
             // For infrequent name terms disable index usage for address
@@ -321,9 +312,9 @@ class SearchDescription
                 && $aWordFrequencyScores[$this->aName[reset($this->aName)]]
                      < CONST_Search_NameOnlySearchFrequencyThreshold
             ) {
-                $aTerms[] = 'array_cat(nameaddress_vector,ARRAY[]::integer[]) @> '.$this->addressArraySQL();
+                $aTerms[] = 'array_cat(nameaddress_vector,ARRAY[]::integer[]) @> '.getArraySQL($this->aAddress);
             } else {
-                $aTerms[] = 'nameaddress_vector @> '.$this->addressArraySQL();
+                $aTerms[] = 'nameaddress_vector @> '.getArraySQL($this->aAddress);
             }
         }
 
@@ -381,11 +372,11 @@ class SearchDescription
 
         if (sizeof($this->aFullNameAddress)) {
             $sExactMatchSQL = ' ( ';
-            $sExactMatchSQL .= '   SELECT count(*) FROM ( ';
-            $sExactMatchSQL .= '      SELECT unnest(ARRAY['.join($this->aFullNameAddress, ",").']) ';
-            $sExactMatchSQL .= '      INTERSECT ';
-            $sExactMatchSQL .= '      SELECT unnest(nameaddress_vector)';
-            $sExactMatchSQL .= '   ) s';
+            $sExactMatchSQL .= ' SELECT count(*) FROM ( ';
+            $sExactMatchSQL .= '  SELECT unnest('.getArraySQL($this->aFullNameAddress).')';
+            $sExactMatchSQL .= '    INTERSECT ';
+            $sExactMatchSQL .= '  SELECT unnest(nameaddress_vector)';
+            $sExactMatchSQL .= ' ) s';
             $sExactMatchSQL .= ') as exactmatch';
             $aOrder[] = 'exactmatch DESC';
         } else {
diff --git a/lib/db.php b/lib/db.php
index 145c57d0..01fc344b 100644
--- a/lib/db.php
+++ b/lib/db.php
@@ -23,6 +23,11 @@ function getDBQuoted($s)
     return "'".pg_escape_string($s)."'";
 }
 
+function getArraySQL($a)
+{
+    return 'ARRAY['.join(',', $a).']';
+}
+
 function getPostgresVersion(&$oDB)
 {
     $sVersionString = $oDB->getOne('select version()');