Merge remote-tracking branch 'upstream/master'

[nominatim.git] / lib-php / DB.php

diff --git a/lib-php/DB.php b/lib-php/DB.php
index 0454a0ff6190567f57acd6d19f46817759ebf7b4..553d9452e2f524b75d2f12631a18140c34460407 100644 (file)
--- a/lib-php/DB.php
+++ b/lib-php/DB.php
@@ -1,4 +1,12 @@
 <?php
 <?php

+/**
+ * SPDX-License-Identifier: GPL-2.0-only
+ *
+ * This file is part of Nominatim. (https://nominatim.org)
+ *
+ * Copyright (C) 2022 by the Nominatim developer community.
+ * For a full list of authors see the git log.
+ */

 
 namespace Nominatim;
 
 
 namespace Nominatim;
 


@@ -30,18 +38,25 @@ class DB
 
         // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php
         try {
 
         // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php
         try {

-            $conn = new \PDO($this->sDSN, null, null, $aConnOptions);
+            $this->connection = 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());
         }
 
         } 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'");
+        $this->connection->exec("SET DateStyle TO 'sql,european'");
+        $this->connection->exec("SET client_encoding TO 'utf-8'");
+        // Disable JIT and parallel workers. They interfere badly with search SQL.
+        $this->connection->exec('SET max_parallel_workers_per_gather TO 0');
+        if ($this->getPostgresVersion() >= 11) {
+            $this->connection->exec('SET jit_above_cost TO -1');
+        }
+        

         $iMaxExecution = ini_get('max_execution_time');
         $iMaxExecution = ini_get('max_execution_time');

-        if ($iMaxExecution > 0) $conn->setAttribute(\PDO::ATTR_TIMEOUT, $iMaxExecution); // seconds
+        if ($iMaxExecution > 0) {
+            $this->connection->setAttribute(\PDO::ATTR_TIMEOUT, $iMaxExecution); // seconds
+        }

 
 

-        $this->connection = $conn;

         return true;
     }
 
         return true;
     }
 


@@ -95,7 +110,9 @@ class DB
         try {
             $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
             $row = $stmt->fetch(\PDO::FETCH_NUM);
         try {
             $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage);
             $row = $stmt->fetch(\PDO::FETCH_NUM);

-            if ($row === false) return false;
+            if ($row === false) {
+                return false;
+            }

         } catch (\PDOException $e) {
             throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
         }
         } catch (\PDOException $e) {
             throw new \Nominatim\DatabaseError($sErrMessage, 500, null, $e, $sSQL);
         }


@@ -240,16 +257,6 @@ class DB
         return ($this->getOne($sSQL, array(':tablename' => $sTableName)) == 1);
     }
 
         return ($this->getOne($sSQL, array(':tablename' => $sTableName)) == 1);
     }
 

-    /**
-    * Returns a list of table names in the database
-    *
-    * @return array[]
-    */
-    public function getListOfTables()
-    {
-        return $this->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
-    }
-

     /**
      * Deletes a table. Returns true if deleted or didn't exist.
      *
     /**
      * Deletes a table. Returns true if deleted or didn't exist.
      *


@@ -262,76 +269,6 @@ class DB
         return $this->exec('DROP TABLE IF EXISTS '.$sTableName.' CASCADE') == 0;
     }
 
         return $this->exec('DROP TABLE IF EXISTS '.$sTableName.' CASCADE') == 0;
     }
 

-    /**
-    * Check if an index exists in the database. Optional filtered by tablename
-    *
-    * @param string  $sTableName
-    *
-    * @return boolean
-    */
-    public function indexExists($sIndexName, $sTableName = null)
-    {
-        return in_array($sIndexName, $this->getListOfIndices($sTableName));
-    }
-
-    /**
-    * Returns a list of index names in the database, optional filtered by tablename
-    *
-    * @param string  $sTableName
-    *
-    * @return array
-    */
-    public function getListOfIndices($sTableName = null)
-    {
-        //  table_name            | index_name                      | column_name
-        // -----------------------+---------------------------------+--------------
-        //  country_name          | idx_country_name_country_code   | country_code
-        //  country_osm_grid      | idx_country_osm_grid_geometry   | geometry
-        //  import_polygon_delete | idx_import_polygon_delete_osmid | osm_id
-        //  import_polygon_delete | idx_import_polygon_delete_osmid | osm_type
-        //  import_polygon_error  | idx_import_polygon_error_osmid  | osm_id
-        //  import_polygon_error  | idx_import_polygon_error_osmid  | osm_type
-        $sSql = <<< END
-SELECT
-    t.relname as table_name,
-    i.relname as index_name,
-    a.attname as column_name
-FROM
-    pg_class t,
-    pg_class i,
-    pg_index ix,
-    pg_attribute a
-WHERE
-    t.oid = ix.indrelid
-    and i.oid = ix.indexrelid
-    and a.attrelid = t.oid
-    and a.attnum = ANY(ix.indkey)
-    and t.relkind = 'r'
-    and i.relname NOT LIKE 'pg_%'
-    FILTERS
- ORDER BY
-    t.relname,
-    i.relname,
-    a.attname
-END;
-
-        $aRows = null;
-        if ($sTableName) {
-            $sSql = str_replace('FILTERS', 'and t.relname = :tablename', $sSql);
-            $aRows = $this->getAll($sSql, array(':tablename' => $sTableName));
-        } else {
-            $sSql = str_replace('FILTERS', '', $sSql);
-            $aRows = $this->getAll($sSql);
-        }
-
-        $aIndexNames = array_unique(array_map(function ($aRow) {
-            return $aRow['index_name'];
-        }, $aRows));
-        sort($aIndexNames);
-
-        return $aIndexNames;
-    }
-

     /**
      * Tries to connect to the database but on failure doesn't throw an exception.
      *
     /**
      * Tries to connect to the database but on failure doesn't throw an exception.
      *


@@ -386,9 +323,13 @@ END;
         if (preg_match('/^pgsql:(.+)$/', $sDSN, $aMatches)) {
             foreach (explode(';', $aMatches[1]) as $sKeyVal) {
                 list($sKey, $sVal) = explode('=', $sKeyVal, 2);
         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';
+                if ($sKey == 'host') {
+                    $sKey = 'hostspec';
+                } elseif ($sKey == 'dbname') {
+                    $sKey = 'database';
+                } elseif ($sKey == 'user') {
+                    $sKey = 'username';
+                }

                 $aInfo[$sKey] = $sVal;
             }
         }
                 $aInfo[$sKey] = $sVal;
             }
         }