split partition.sql into table and function parts

Makes it easier to patch partition functions on an already existing
database.

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 8d5e8eff039d65462d9e4ec0aa79ee5478a20ab9..9ea70409bd432963261d2dd4734a29e4dc004c73 100644 (file)
--- 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 (file)
index 0000000..554c3b0
--- /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 473d0cb0bce8aa686750122a1af60ba8d74dab86..1ebee32ff1104df99e85cfbb4c2ed8bdb4bc995a 100755 (executable)
--- 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)
                {