From 87b250e739862b5ff51c4508f87501bba47f82f8 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sat, 19 Jan 2013 16:19:55 +0100 Subject: [PATCH] split partition.sql into table and function parts Makes it easier to patch partition functions on an already existing database. --- ...ns.src.sql => partition-functions.src.sql} | 59 ------------------- sql/partition-tables.src.sql | 58 ++++++++++++++++++ utils/setup.php | 40 +++++++++++-- 3 files changed, 93 insertions(+), 64 deletions(-) rename sql/{partitions.src.sql => partition-functions.src.sql} (79%) create mode 100644 sql/partition-tables.src.sql diff --git a/sql/partitions.src.sql b/sql/partition-functions.src.sql similarity index 79% rename from sql/partitions.src.sql rename to sql/partition-functions.src.sql index 8d5e8eff..9ea70409 100644 --- a/sql/partitions.src.sql +++ b/sql/partition-functions.src.sql @@ -1,62 +1,3 @@ -drop type nearplace cascade; -create type nearplace as ( - place_id BIGINT -); - -drop type nearfeature cascade; -create type nearfeature as ( - place_id BIGINT, - keywords int[], - rank_address integer, - rank_search integer, - distance float, - isguess boolean -); - -drop type nearfeaturecentr cascade; -create type nearfeaturecentr as ( - place_id BIGINT, - keywords int[], - rank_address integer, - rank_search integer, - distance float, - isguess boolean, - centroid GEOMETRY -); - -CREATE TABLE location_area_country () INHERITS (location_area_large); -CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry); - -CREATE TABLE search_name_country () INHERITS (search_name_blank); -CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id); -CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off); - --- start -CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large); -CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id); -CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry); - -CREATE TABLE search_name_-partition- () INHERITS (search_name_blank); -CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id); -CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid); -CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off); - -CREATE TABLE location_property_-partition- () INHERITS (location_property); -CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id); -CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id); -CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber); - -CREATE TABLE location_road_-partition- ( - partition integer, - place_id BIGINT, - country_code VARCHAR(2) - ); -SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2); -CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry); -CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id); - --- end - create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$ DECLARE r nearfeaturecentr%rowtype; diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql new file mode 100644 index 00000000..554c3b04 --- /dev/null +++ b/sql/partition-tables.src.sql @@ -0,0 +1,58 @@ +drop type nearplace cascade; +create type nearplace as ( + place_id BIGINT +); + +drop type nearfeature cascade; +create type nearfeature as ( + place_id BIGINT, + keywords int[], + rank_address integer, + rank_search integer, + distance float, + isguess boolean +); + +drop type nearfeaturecentr cascade; +create type nearfeaturecentr as ( + place_id BIGINT, + keywords int[], + rank_address integer, + rank_search integer, + distance float, + isguess boolean, + centroid GEOMETRY +); + +CREATE TABLE location_area_country () INHERITS (location_area_large); +CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry); + +CREATE TABLE search_name_country () INHERITS (search_name_blank); +CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id); +CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector) WITH (fastupdate = off); + +-- start +CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large); +CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id); +CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry); + +CREATE TABLE search_name_-partition- () INHERITS (search_name_blank); +CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id); +CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid); +CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector) WITH (fastupdate = off); + +CREATE TABLE location_property_-partition- () INHERITS (location_property); +CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id); +CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id); +CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber); + +CREATE TABLE location_road_-partition- ( + partition integer, + place_id BIGINT, + country_code VARCHAR(2) + ); +SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2); +CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry); +CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id); + +-- end diff --git a/utils/setup.php b/utils/setup.php index 473d0cb0..1ebee32f 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -24,13 +24,14 @@ array('enable-debug-statements', '', 0, 1, 0, 0, 'bool', 'Include debug warning statements in pgsql commands'), array('create-minimal-tables', '', 0, 1, 0, 0, 'bool', 'Create minimal main tables'), array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'), - array('create-partitions', '', 0, 1, 0, 0, 'bool', 'Create required partition tables and triggers'), + array('create-partition-tables', '', 0, 1, 0, 0, 'bool', 'Create required partition tables'), + array('create-partition-functions', '', 0, 1, 0, 0, 'bool', 'Create required partition triggers'), array('import-wikipedia-articles', '', 0, 1, 0, 0, 'bool', 'Import wikipedia article dump'), array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'), array('disable-token-precalc', '', 0, 1, 0, 0, 'bool', 'Disable name precalculation (EXPERT)'), array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'), array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'), - array('create-roads', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'), + array('create-roads', '', 0, 1, 0, 0, 'bool', ''), array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'), array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'), array('index-noanalyse', '', 0, 1, 0, 0, 'bool', 'Do not perform analyse operations during index (EXPERT)'), @@ -222,9 +223,9 @@ pgsqlRunScript($sTemplate); } - if ($aCMDResult['create-partitions'] || $aCMDResult['all']) + if ($aCMDResult['create-partition-tables'] || $aCMDResult['all']) { - echo "Partitions\n"; + echo "Partition Tables\n"; $bDidSomething = true; $oDB =& getDB(); $sSQL = 'select partition from country_name order by country_code'; @@ -235,7 +236,36 @@ } $aPartitions[] = 0; - $sTemplate = file_get_contents(CONST_BasePath.'/sql/partitions.src.sql'); + $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.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); + } + + + if ($aCMDResult['create-partition-functions'] || $aCMDResult['all']) + { + echo "Partition Functions\n"; + $bDidSomething = true; + $oDB =& getDB(); + $sSQL = 'select partition from country_name order by country_code'; + $aPartitions = $oDB->getCol($sSQL); + if (PEAR::isError($aPartitions)) + { + fail($aPartitions->getMessage()); + } + $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) { -- 2.39.5