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);
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) {
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);
{
$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) {
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];
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'
}
/**
- * 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 {
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';
}
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;
+ }
}
projects / nominatim.git / blobdiff