move postcode table setup to sql/

Also moves the call to the setup from the setup-db
step to the calculate-postcodes step. The tables also need
no longer be accessible by the webservice.

diff --git a/data/gb_postcode_table.sql b/data/gb_postcode_table.sql
deleted file mode 100644 (file)
index bee8a96..0000000
--- a/data/gb_postcode_table.sql
+++ /dev/null
@@ -1,26 +0,0 @@
--- This data contains Ordnance Survey data © Crown copyright and database right 2010. 
--- Code-Point Open contains Royal Mail data © Royal Mail copyright and database right 2010.
--- OS data may be used under the terms of the OS OpenData licence:
--- http://www.ordnancesurvey.co.uk/oswebsite/opendata/licence/docs/licence.pdf
-
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = off;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-SET escape_string_warning = off;
-
-SET search_path = public, pg_catalog;
-
-SET default_tablespace = '';
-
-SET default_with_oids = false;
-
-CREATE TABLE gb_postcode (
-    id integer,
-    postcode character varying(9),
-    geometry geometry,
-    CONSTRAINT enforce_dims_geometry CHECK ((st_ndims(geometry) = 2)),
-    CONSTRAINT enforce_srid_geometry CHECK ((st_srid(geometry) = 4326))
-);
-

diff --git a/data/us_postcode_table.sql b/data/us_postcode_table.sql
deleted file mode 100644 (file)
index 9958916..0000000
--- a/data/us_postcode_table.sql
+++ /dev/null
@@ -1,16 +0,0 @@
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-
-SET search_path = public, pg_catalog;
-
-SET default_tablespace = '';
-
-SET default_with_oids = false;
-
-CREATE TABLE us_postcode (
-    postcode text,
-    x double precision,
-    y double precision
-);

diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php
index dda491603c3e673576f35ab0e03802874127ee82..4e9daaf8dda9a8c4e1d667b773bae0b8e6fde0c4 100755 (executable)
--- a/lib/setup/SetupClass.php
+++ b/lib/setup/SetupClass.php
@@ -173,22 +173,6 @@ class SetupFunctions
         }
         $this->pgsqlRunScriptFile(CONST_DataDir.'/data/country_name.sql');
         $this->pgsqlRunScriptFile(CONST_DataDir.'/data/country_osm_grid.sql.gz');
         }
         $this->pgsqlRunScriptFile(CONST_DataDir.'/data/country_name.sql');
         $this->pgsqlRunScriptFile(CONST_DataDir.'/data/country_osm_grid.sql.gz');

-        $this->pgsqlRunScriptFile(CONST_DataDir.'/data/gb_postcode_table.sql');
-        $this->pgsqlRunScriptFile(CONST_DataDir.'/data/us_postcode_table.sql');
-
-        $sPostcodeFilename = CONST_InstallDir.'/gb_postcode_data.sql.gz';
-        if (file_exists($sPostcodeFilename)) {
-            $this->pgsqlRunScriptFile($sPostcodeFilename);
-        } else {
-            warn('optional external GB postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
-        }
-
-        $sPostcodeFilename = CONST_InstallDir.'/us_postcode_data.sql.gz';
-        if (file_exists($sPostcodeFilename)) {
-            $this->pgsqlRunScriptFile($sPostcodeFilename);
-        } else {
-            warn('optional external US postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
-        }

 
         if ($this->bNoPartitions) {
             $this->pgsqlRunScript('update country_name set partition = 0');
 
         if ($this->bNoPartitions) {
             $this->pgsqlRunScript('update country_name set partition = 0');


@@ -521,6 +505,23 @@ class SetupFunctions
     public function calculatePostcodes($bCMDResultAll)
     {
         info('Calculate Postcodes');
     public function calculatePostcodes($bCMDResultAll)
     {
         info('Calculate Postcodes');

+        $this->pgsqlRunScriptFile(CONST_DataDir.'/sql/postcode_tables.sql');
+
+        $sPostcodeFilename = CONST_InstallDir.'/gb_postcode_data.sql.gz';
+        if (file_exists($sPostcodeFilename)) {
+            $this->pgsqlRunScriptFile($sPostcodeFilename);
+        } else {
+            warn('optional external GB postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
+        }
+
+        $sPostcodeFilename = CONST_InstallDir.'/us_postcode_data.sql.gz';
+        if (file_exists($sPostcodeFilename)) {
+            $this->pgsqlRunScriptFile($sPostcodeFilename);
+        } else {
+            warn('optional external US postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
+        }
+
+

         $this->db()->exec('TRUNCATE location_postcode');
 
         $sSQL  = 'INSERT INTO location_postcode';
         $this->db()->exec('TRUNCATE location_postcode');
 
         $sSQL  = 'INSERT INTO location_postcode';

diff --git a/sql/postcode_tables.sql b/sql/postcode_tables.sql
new file mode 100644 (file)
index 0000000..c445d6a
--- /dev/null
+++ b/sql/postcode_tables.sql
@@ -0,0 +1,15 @@
+DROP TABLE IF EXISTS gb_postcode;
+CREATE TABLE gb_postcode (
+    id integer,
+    postcode character varying(9),
+    geometry geometry,
+    CONSTRAINT enforce_dims_geometry CHECK ((st_ndims(geometry) = 2)),
+    CONSTRAINT enforce_srid_geometry CHECK ((st_srid(geometry) = 4326))
+);
+
+DROP TABLE IF EXISTS us_postcode;
+CREATE TABLE us_postcode (
+    postcode text,
+    x double precision,
+    y double precision
+);

diff --git a/sql/tables.sql b/sql/tables.sql
index 8647e304331279bae635ea3f3f4189af06efcdd3..d15e42c445eebf71d5cbc1d0b005ee4f1ad3a6bc 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -35,8 +35,6 @@ GRANT UPDATE ON new_query_log TO "{www-user}" ;
 GRANT SELECT ON new_query_log TO "{www-user}" ;
 
 GRANT SELECT ON TABLE country_name TO "{www-user}";
 GRANT SELECT ON new_query_log TO "{www-user}" ;
 
 GRANT SELECT ON TABLE country_name TO "{www-user}";

-GRANT SELECT ON TABLE gb_postcode TO "{www-user}";
-GRANT SELECT ON TABLE us_postcode TO "{www-user}";

 
 drop table IF EXISTS word;
 CREATE TABLE word (
 
 drop table IF EXISTS word;
 CREATE TABLE word (