From cf4a44aaf7f8dd9ac7396deb0fe597980e22df96 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Thu, 9 Jun 2016 00:24:18 +0200 Subject: [PATCH] introduce chksql function for phpscripts Checks SQL query results for errors and bails out if it finds one. Avoids some heavy code duplication all over the place. --- lib/cmd.php | 10 ++++++ utils/setup.php | 89 +++++++++++++++++------------------------------- utils/update.php | 57 +++++-------------------------- 3 files changed, 51 insertions(+), 105 deletions(-) diff --git a/lib/cmd.php b/lib/cmd.php index 0c9201a8..c73074a1 100644 --- a/lib/cmd.php +++ b/lib/cmd.php @@ -143,3 +143,13 @@ echo "\n"; exit; } + + function chksql($oSql, $sMsg = false) + { + if (PEAR::isError($oSql)) + { + fail($sMsg || $oSql->getMessage(), $oSql->userinfo); + } + + return $oSql; + } diff --git a/utils/setup.php b/utils/setup.php index 92375608..94c7edb5 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -123,11 +123,8 @@ // For extratags and namedetails the hstore_to_json converter is // needed which is only available from Postgresql 9.3+. For older // versions add a dummy function that returns nothing. - $iNumFunc = $oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'"); - if (PEAR::isError($iNumFunc)) - { - fail("Cannot query stored procedures.", $iNumFunc); - } + $iNumFunc = chksql($oDB->getOne("select count(*) from pg_proc where proname = 'hstore_to_json'")); + if ($iNumFunc == 0) { pgsqlRunScript("create function hstore_to_json(dummy hstore) returns text AS 'select null::text' language sql immutable"); @@ -204,11 +201,10 @@ passthruCheckReturn($osm2pgsql); $oDB =& getDB(); - $x = $oDB->getRow('select * from place limit 1'); - if (PEAR::isError($x)) { - fail($x->getMessage()); + if (!chksql($oDB->getRow('select * from place limit 1'))) + { + fail('No Data'); } - if (!$x) fail('No Data'); } if ($aCMDResult['create-functions'] || $aCMDResult['all']) @@ -249,14 +245,6 @@ { echo "Partition Tables\n"; $bDidSomething = true; - $oDB =& getDB(); - $sSQL = 'select distinct partition from country_name'; - $aPartitions = $oDB->getCol($sSQL); - if (PEAR::isError($aPartitions)) - { - fail($aPartitions->getMessage()); - } - if (!$aCMDResult['no-partitions']) $aPartitions[] = 0; $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql'); $sTemplate = replace_tablespace('{ts:address-data}', @@ -271,18 +259,8 @@ CONST_Tablespace_Aux_Data, $sTemplate); $sTemplate = replace_tablespace('{ts:aux-index}', CONST_Tablespace_Aux_Index, $sTemplate); - preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); - foreach($aMatches as $aMatch) - { - $sResult = ''; - foreach($aPartitions as $sPartitionName) - { - $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]); - } - $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate); - } - pgsqlRunScript($sTemplate); + pgsqlRunPartitionScript($sTemplate); } @@ -290,28 +268,10 @@ { echo "Partition Functions\n"; $bDidSomething = true; - $oDB =& getDB(); - $sSQL = 'select distinct partition from country_name'; - $aPartitions = $oDB->getCol($sSQL); - if (PEAR::isError($aPartitions)) - { - fail($aPartitions->getMessage()); - } - if (!$aCMDResult['no-partitions']) $aPartitions[] = 0; $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql'); - preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); - foreach($aMatches as $aMatch) - { - $sResult = ''; - foreach($aPartitions as $sPartitionName) - { - $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]); - } - $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate); - } - pgsqlRunScript($sTemplate); + pgsqlRunPartitionScript($sTemplate); } if ($aCMDResult['import-wikipedia-articles'] || $aCMDResult['all']) @@ -370,11 +330,7 @@ echo '.'; $sSQL = 'select distinct partition from country_name'; - $aPartitions = $oDB->getCol($sSQL); - if (PEAR::isError($aPartitions)) - { - fail($aPartitions->getMessage()); - } + $aPartitions = chksql($oDB->getCol($sSQL)); if (!$aCMDResult['no-partitions']) $aPartitions[] = 0; foreach($aPartitions as $sPartition) { @@ -673,11 +629,8 @@ $oDB =& getDB(); $aDropTables = array(); - $aHaveTables = $oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'"); - if (PEAR::isError($aHaveTables)) - { - fail($aPartitions->getMessage()); - } + $aHaveTables = chksql($oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'")); + foreach($aHaveTables as $sTable) { $bFound = false; @@ -806,6 +759,28 @@ } } + function pgsqlRunPartitionScript($sTemplate) + { + $oDB =& getDB(); + + $sSQL = 'select distinct partition from country_name'; + $aPartitions = chksql($oDB->getCol($sSQL)); + if (!$aCMDResult['no-partitions']) $aPartitions[] = 0; + + preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER); + foreach($aMatches as $aMatch) + { + $sResult = ''; + foreach($aPartitions as $sPartitionName) + { + $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]); + } + $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate); + } + + pgsqlRunScript($sTemplate); + } + function pgsqlRunRestoreData($sDumpFile) { // Convert database DSN to psql parameters diff --git a/utils/update.php b/utils/update.php index 34d30a4f..82362b31 100755 --- a/utils/update.php +++ b/utils/update.php @@ -185,26 +185,17 @@ $oDB =& getDB(); $sSQL = 'select partition from country_name order by country_code'; - $aPartitions = $oDB->getCol($sSQL); - if (PEAR::isError($aPartitions)) - { - fail($aPartitions->getMessage()); - } + $aPartitions = chksql($oDB->getCol($sSQL)); $aPartitions[] = 0; $sSQL = "select word_token,count(*) from word where substr(word_token, 1, 1) = ' ' and class is null and type is null and country_code is null group by word_token having count(*) > 1 order by word_token"; - $aDuplicateTokens = $oDB->getAll($sSQL); + $aDuplicateTokens = chksql($oDB->getAll($sSQL)); foreach($aDuplicateTokens as $aToken) { if (trim($aToken['word_token']) == '' || trim($aToken['word_token']) == '-') continue; echo "Deduping ".$aToken['word_token']."\n"; $sSQL = "select word_id,(select count(*) from search_name where nameaddress_vector @> ARRAY[word_id]) as num from word where word_token = '".$aToken['word_token']."' and class is null and type is null and country_code is null order by num desc"; - $aTokenSet = $oDB->getAll($sSQL); - if (PEAR::isError($aTokenSet)) - { - var_dump($aTokenSet, $sSQL); - exit(1); - } + $aTokenSet = chksql($oDB->getAll($sSQL)); $aKeep = array_shift($aTokenSet); $iKeepID = $aKeep['word_id']; @@ -215,63 +206,33 @@ $sSQL .= " name_vector = array_replace(name_vector,".$aRemove['word_id'].",".$iKeepID."),"; $sSQL .= " nameaddress_vector = array_replace(nameaddress_vector,".$aRemove['word_id'].",".$iKeepID.")"; $sSQL .= " where name_vector @> ARRAY[".$aRemove['word_id']."]"; - $x = $oDB->query($sSQL); - if (PEAR::isError($x)) - { - var_dump($x); - exit(1); - } + chksql($oDB->query($sSQL)); $sSQL = "update search_name set"; $sSQL .= " nameaddress_vector = array_replace(nameaddress_vector,".$aRemove['word_id'].",".$iKeepID.")"; $sSQL .= " where nameaddress_vector @> ARRAY[".$aRemove['word_id']."]"; - $x = $oDB->query($sSQL); - if (PEAR::isError($x)) - { - var_dump($x); - exit(1); - } + chksql($oDB->query($sSQL)); $sSQL = "update location_area_country set"; $sSQL .= " keywords = array_replace(keywords,".$aRemove['word_id'].",".$iKeepID.")"; $sSQL .= " where keywords @> ARRAY[".$aRemove['word_id']."]"; - $x = $oDB->query($sSQL); - if (PEAR::isError($x)) - { - var_dump($x); - exit(1); - } + chksql($oDB->query($sSQL)); foreach ($aPartitions as $sPartition) { $sSQL = "update search_name_".$sPartition." set"; $sSQL .= " name_vector = array_replace(name_vector,".$aRemove['word_id'].",".$iKeepID.")"; $sSQL .= " where name_vector @> ARRAY[".$aRemove['word_id']."]"; - $x = $oDB->query($sSQL); - if (PEAR::isError($x)) - { - var_dump($x); - exit(1); - } + chksql($oDB->query($sSQL)); $sSQL = "update location_area_country set"; $sSQL .= " keywords = array_replace(keywords,".$aRemove['word_id'].",".$iKeepID.")"; $sSQL .= " where keywords @> ARRAY[".$aRemove['word_id']."]"; - $x = $oDB->query($sSQL); - if (PEAR::isError($x)) - { - var_dump($x); - exit(1); - } + chksql($oDB->query($sSQL)); } $sSQL = "delete from word where word_id = ".$aRemove['word_id']; - $x = $oDB->query($sSQL); - if (PEAR::isError($x)) - { - var_dump($x); - exit(1); - } + chksql($oDB->query($sSQL)); } } } -- 2.45.1