X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/33ff96fd8323040f4551839e55c061b048329f66..6625e93be673a2b400a466f2f024d97716f8da72:/lib/DB.php?ds=sidebyside diff --git a/lib/DB.php b/lib/DB.php index 17dfe67d..38b3e27e 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,13 +133,9 @@ class DB { $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 + $stmt = $this->getQueryStatement($sSQL, $aInputVars, $sErrMessage); + + while (($val = $stmt->fetchColumn(0)) !== false) { // returns first column or false $aVals[] = $val; } } catch (\PDOException $e) { @@ -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' @@ -243,11 +241,103 @@ class DB } /** - * Since the DSN includes the database name, checks if the connection works. + * 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. + * + * @param string $sTableName + * + * @return boolean + */ + public function deleteTable($sTableName) + { + 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. * * @return boolean */ - public function databaseExists() + public function checkConnection() { $bExists = true; try { @@ -282,11 +372,18 @@ class DB return (float) ($aMatches[1].'.'.$aMatches[2]); } + /** + * Returns an associate array of postgresql database connection settings. Keys can + * be 'database', 'hostspec', 'port', 'username', 'password'. + * Returns empty array on failure, thus check if at least 'database' is set. + * + * @return array[] + */ public static function parseDSN($sDSN) { // https://secure.php.net/manual/en/ref.pdo-pgsql.connection.php $aInfo = array(); - if (preg_match('/^pgsql:(.+)/', $sDSN, $aMatches)) { + if (preg_match('/^pgsql:(.+)$/', $sDSN, $aMatches)) { foreach (explode(';', $aMatches[1]) as $sKeyVal) { list($sKey, $sVal) = explode('=', $sKeyVal, 2); if ($sKey == 'host') $sKey = 'hostspec'; @@ -297,4 +394,28 @@ class DB } return $aInfo; } + + /** + * Takes an array of settings and return the DNS string. Key names can be + * 'database', 'hostspec', 'port', 'username', 'password' but aliases + * 'dbname', 'host' and 'user' are also supported. + * + * @return string + * + */ + public static function generateDSN($aInfo) + { + $sDSN = sprintf( + 'pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s;', + $aInfo['host'] ?? $aInfo['hostspec'] ?? '', + $aInfo['port'] ?? '', + $aInfo['dbname'] ?? $aInfo['database'] ?? '', + $aInfo['user'] ?? '', + $aInfo['password'] ?? '' + ); + $sDSN = preg_replace('/\b\w+=;/', '', $sDSN); + $sDSN = preg_replace('/;\Z/', '', $sDSN); + + return $sDSN; + } }