3 namespace Nominatim\Setup;
5 require_once(CONST_BasePath.'/lib/setup/AddressLevelParser.php');
9 protected $iCacheMemory;
10 protected $iInstances;
11 protected $sModulePath;
14 protected $sIgnoreErrors;
15 protected $bEnableDiffUpdates;
16 protected $bEnableDebugStatements;
17 protected $bNoPartitions;
18 protected $oDB = null;
20 public function __construct(array $aCMDResult)
22 // by default, use all but one processor, but never more than 15.
23 $this->iInstances = isset($aCMDResult['threads'])
24 ? $aCMDResult['threads']
25 : (min(16, getProcessorCount()) - 1);
27 if ($this->iInstances < 1) {
28 $this->iInstances = 1;
29 warn('resetting threads to '.$this->iInstances);
32 // Assume we can steal all the cache memory in the box (unless told otherwise)
33 if (isset($aCMDResult['osm2pgsql-cache'])) {
34 $this->iCacheMemory = $aCMDResult['osm2pgsql-cache'];
36 $this->iCacheMemory = getCacheMemoryMB();
39 $this->sModulePath = CONST_Database_Module_Path;
40 info('module path: ' . $this->sModulePath);
42 // parse database string
43 $this->aDSNInfo = \Nominatim\DB::parseDSN(CONST_Database_DSN);
44 if (!isset($this->aDSNInfo['port'])) {
45 $this->aDSNInfo['port'] = 5432;
48 // setting member variables based on command line options stored in $aCMDResult
49 $this->bVerbose = $aCMDResult['verbose'];
51 //setting default values which are not set by the update.php array
52 if (isset($aCMDResult['ignore-errors'])) {
53 $this->sIgnoreErrors = $aCMDResult['ignore-errors'];
55 $this->sIgnoreErrors = false;
57 if (isset($aCMDResult['enable-debug-statements'])) {
58 $this->bEnableDebugStatements = $aCMDResult['enable-debug-statements'];
60 $this->bEnableDebugStatements = false;
62 if (isset($aCMDResult['no-partitions'])) {
63 $this->bNoPartitions = $aCMDResult['no-partitions'];
65 $this->bNoPartitions = false;
67 if (isset($aCMDResult['enable-diff-updates'])) {
68 $this->bEnableDiffUpdates = $aCMDResult['enable-diff-updates'];
70 $this->bEnableDiffUpdates = false;
74 public function createDB()
77 $oDB = new \Nominatim\DB;
79 if ($oDB->databaseExists()) {
80 fail('database already exists ('.CONST_Database_DSN.')');
83 $sCreateDBCmd = 'createdb -E UTF-8'
84 .' -p '.escapeshellarg($this->aDSNInfo['port'])
85 .' '.escapeshellarg($this->aDSNInfo['database']);
86 if (isset($this->aDSNInfo['username'])) {
87 $sCreateDBCmd .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
90 if (isset($this->aDSNInfo['hostspec'])) {
91 $sCreateDBCmd .= ' -h '.escapeshellarg($this->aDSNInfo['hostspec']);
94 $result = $this->runWithPgEnv($sCreateDBCmd);
95 if ($result != 0) fail('Error executing external command: '.$sCreateDBCmd);
98 public function connect()
100 $this->oDB = new \Nominatim\DB();
101 $this->oDB->connect();
104 public function setupDB()
108 $fPostgresVersion = $this->oDB->getPostgresVersion();
109 echo 'Postgres version found: '.$fPostgresVersion."\n";
111 if ($fPostgresVersion < 9.03) {
112 fail('Minimum supported version of Postgresql is 9.3.');
115 $this->pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS hstore');
116 $this->pgsqlRunScript('CREATE EXTENSION IF NOT EXISTS postgis');
118 $fPostgisVersion = $this->oDB->getPostgisVersion();
119 echo 'Postgis version found: '.$fPostgisVersion."\n";
121 if ($fPostgisVersion < 2.2) {
122 echo "Minimum required Postgis version 2.2\n";
126 $i = $this->oDB->getOne("select count(*) from pg_user where usename = '".CONST_Database_Web_User."'");
128 echo "\nERROR: Web user '".CONST_Database_Web_User."' does not exist. Create it with:\n";
129 echo "\n createuser ".CONST_Database_Web_User."\n\n";
133 // Try accessing the C module, so we know early if something is wrong
134 checkModulePresence(); // raises exception on failure
136 if (!file_exists(CONST_ExtraDataPath.'/country_osm_grid.sql.gz')) {
137 echo 'Error: you need to download the country_osm_grid first:';
138 echo "\n wget -O ".CONST_ExtraDataPath."/country_osm_grid.sql.gz https://www.nominatim.org/data/country_grid.sql.gz\n";
141 $this->pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
142 $this->pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql.gz');
143 $this->pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode_table.sql');
144 $this->pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode_table.sql');
146 $sPostcodeFilename = CONST_BasePath.'/data/gb_postcode_data.sql.gz';
147 if (file_exists($sPostcodeFilename)) {
148 $this->pgsqlRunScriptFile($sPostcodeFilename);
150 warn('optional external GB postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
153 $sPostcodeFilename = CONST_BasePath.'/data/us_postcode_data.sql.gz';
154 if (file_exists($sPostcodeFilename)) {
155 $this->pgsqlRunScriptFile($sPostcodeFilename);
157 warn('optional external US postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
160 if ($this->bNoPartitions) {
161 $this->pgsqlRunScript('update country_name set partition = 0');
164 // the following will be needed by createFunctions later but
165 // is only defined in the subsequently called createTables
166 // Create dummies here that will be overwritten by the proper
167 // versions in create-tables.
168 $this->pgsqlRunScript('CREATE TABLE IF NOT EXISTS place_boundingbox ()');
169 $this->pgsqlRunScript('CREATE TYPE wikipedia_article_match AS ()', false);
172 public function importData($sOSMFile)
176 $osm2pgsql = CONST_Osm2pgsql_Binary;
177 if (!file_exists($osm2pgsql)) {
178 echo "Check CONST_Osm2pgsql_Binary in your local settings file.\n";
179 echo "Normally you should not need to set this manually.\n";
180 fail("osm2pgsql not found in '$osm2pgsql'");
183 $osm2pgsql .= ' -S '.escapeshellarg(CONST_Import_Style);
185 if (!is_null(CONST_Osm2pgsql_Flatnode_File) && CONST_Osm2pgsql_Flatnode_File) {
186 $osm2pgsql .= ' --flat-nodes '.escapeshellarg(CONST_Osm2pgsql_Flatnode_File);
189 if (CONST_Tablespace_Osm2pgsql_Data)
190 $osm2pgsql .= ' --tablespace-slim-data '.escapeshellarg(CONST_Tablespace_Osm2pgsql_Data);
191 if (CONST_Tablespace_Osm2pgsql_Index)
192 $osm2pgsql .= ' --tablespace-slim-index '.escapeshellarg(CONST_Tablespace_Osm2pgsql_Index);
193 if (CONST_Tablespace_Place_Data)
194 $osm2pgsql .= ' --tablespace-main-data '.escapeshellarg(CONST_Tablespace_Place_Data);
195 if (CONST_Tablespace_Place_Index)
196 $osm2pgsql .= ' --tablespace-main-index '.escapeshellarg(CONST_Tablespace_Place_Index);
197 $osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
198 $osm2pgsql .= ' -C '.escapeshellarg($this->iCacheMemory);
199 $osm2pgsql .= ' -P '.escapeshellarg($this->aDSNInfo['port']);
200 if (isset($this->aDSNInfo['username'])) {
201 $osm2pgsql .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
203 if (isset($this->aDSNInfo['hostspec'])) {
204 $osm2pgsql .= ' -H '.escapeshellarg($this->aDSNInfo['hostspec']);
206 $osm2pgsql .= ' -d '.escapeshellarg($this->aDSNInfo['database']).' '.escapeshellarg($sOSMFile);
208 $this->runWithPgEnv($osm2pgsql);
210 if (!$this->sIgnoreErrors && !$this->oDB->getRow('select * from place limit 1')) {
215 public function createFunctions()
217 info('Create Functions');
219 // Try accessing the C module, so we know early if something is wrong
220 checkModulePresence(); // raises exception on failure
222 $this->createSqlFunctions();
225 public function createTables($bReverseOnly = false)
227 info('Create Tables');
229 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
230 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
231 $sTemplate = $this->replaceTablespace(
233 CONST_Tablespace_Address_Data,
236 $sTemplate = $this->replaceTablespace(
237 '{ts:address-index}',
238 CONST_Tablespace_Address_Index,
241 $sTemplate = $this->replaceTablespace(
243 CONST_Tablespace_Search_Data,
246 $sTemplate = $this->replaceTablespace(
248 CONST_Tablespace_Search_Index,
251 $sTemplate = $this->replaceTablespace(
253 CONST_Tablespace_Aux_Data,
256 $sTemplate = $this->replaceTablespace(
258 CONST_Tablespace_Aux_Index,
262 $this->pgsqlRunScript($sTemplate, false);
265 $this->pgExec('DROP TABLE search_name');
268 $oAlParser = new AddressLevelParser(CONST_Address_Level_Config);
269 $oAlParser->createTable($this->oDB, 'address_levels');
272 public function createPartitionTables()
274 info('Create Partition Tables');
276 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
277 $sTemplate = $this->replaceTablespace(
279 CONST_Tablespace_Address_Data,
283 $sTemplate = $this->replaceTablespace(
284 '{ts:address-index}',
285 CONST_Tablespace_Address_Index,
289 $sTemplate = $this->replaceTablespace(
291 CONST_Tablespace_Search_Data,
295 $sTemplate = $this->replaceTablespace(
297 CONST_Tablespace_Search_Index,
301 $sTemplate = $this->replaceTablespace(
303 CONST_Tablespace_Aux_Data,
307 $sTemplate = $this->replaceTablespace(
309 CONST_Tablespace_Aux_Index,
313 $this->pgsqlRunPartitionScript($sTemplate);
316 public function createPartitionFunctions()
318 info('Create Partition Functions');
320 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
321 $this->pgsqlRunPartitionScript($sTemplate);
324 public function importWikipediaArticles()
326 $sWikiArticlesFile = CONST_Wikipedia_Data_Path.'/wikipedia_article.sql.bin';
327 $sWikiRedirectsFile = CONST_Wikipedia_Data_Path.'/wikipedia_redirect.sql.bin';
328 if (file_exists($sWikiArticlesFile)) {
329 info('Importing wikipedia articles');
330 $this->pgsqlRunDropAndRestore($sWikiArticlesFile);
332 warn('wikipedia article dump file not found - places will have default importance');
334 if (file_exists($sWikiRedirectsFile)) {
335 info('Importing wikipedia redirects');
336 $this->pgsqlRunDropAndRestore($sWikiRedirectsFile);
338 warn('wikipedia redirect dump file not found - some place importance values may be missing');
342 public function loadData($bDisableTokenPrecalc)
344 info('Drop old Data');
346 $this->pgExec('TRUNCATE word');
348 $this->pgExec('TRUNCATE placex');
350 $this->pgExec('TRUNCATE location_property_osmline');
352 $this->pgExec('TRUNCATE place_addressline');
354 $this->pgExec('TRUNCATE place_boundingbox');
356 $this->pgExec('TRUNCATE location_area');
358 if (!$this->dbReverseOnly()) {
359 $this->pgExec('TRUNCATE search_name');
362 $this->pgExec('TRUNCATE search_name_blank');
364 $this->pgExec('DROP SEQUENCE seq_place');
366 $this->pgExec('CREATE SEQUENCE seq_place start 100000');
369 $sSQL = 'select distinct partition from country_name';
370 $aPartitions = $this->oDB->getCol($sSQL);
372 if (!$this->bNoPartitions) $aPartitions[] = 0;
373 foreach ($aPartitions as $sPartition) {
374 $this->pgExec('TRUNCATE location_road_'.$sPartition);
378 // used by getorcreate_word_id to ignore frequent partial words
379 $sSQL = 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS ';
380 $sSQL .= '$$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE';
381 $this->pgExec($sSQL);
384 // pre-create the word list
385 if (!$bDisableTokenPrecalc) {
386 info('Loading word list');
387 $this->pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
391 $sColumns = 'osm_type, osm_id, class, type, name, admin_level, address, extratags, geometry';
393 $aDBInstances = array();
394 $iLoadThreads = max(1, $this->iInstances - 1);
395 for ($i = 0; $i < $iLoadThreads; $i++) {
396 // https://secure.php.net/manual/en/function.pg-connect.php
397 $DSN = CONST_Database_DSN;
398 $DSN = preg_replace('/^pgsql:/', '', $DSN);
399 $DSN = preg_replace('/;/', ' ', $DSN);
400 $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW);
401 pg_ping($aDBInstances[$i]);
404 for ($i = 0; $i < $iLoadThreads; $i++) {
405 $sSQL = "INSERT INTO placex ($sColumns) SELECT $sColumns FROM place WHERE osm_id % $iLoadThreads = $i";
406 $sSQL .= " and not (class='place' and type='houses' and osm_type='W'";
407 $sSQL .= " and ST_GeometryType(geometry) = 'ST_LineString')";
408 $sSQL .= ' and ST_IsValid(geometry)';
409 if ($this->bVerbose) echo "$sSQL\n";
410 if (!pg_send_query($aDBInstances[$i], $sSQL)) {
411 fail(pg_last_error($aDBInstances[$i]));
415 // last thread for interpolation lines
416 // https://secure.php.net/manual/en/function.pg-connect.php
417 $DSN = CONST_Database_DSN;
418 $DSN = preg_replace('/^pgsql:/', '', $DSN);
419 $DSN = preg_replace('/;/', ' ', $DSN);
420 $aDBInstances[$iLoadThreads] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW);
421 pg_ping($aDBInstances[$iLoadThreads]);
422 $sSQL = 'insert into location_property_osmline';
423 $sSQL .= ' (osm_id, address, linegeo)';
424 $sSQL .= ' SELECT osm_id, address, geometry from place where ';
425 $sSQL .= "class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString'";
426 if ($this->bVerbose) echo "$sSQL\n";
427 if (!pg_send_query($aDBInstances[$iLoadThreads], $sSQL)) {
428 fail(pg_last_error($aDBInstances[$iLoadThreads]));
432 for ($i = 0; $i <= $iLoadThreads; $i++) {
433 while (($hPGresult = pg_get_result($aDBInstances[$i])) !== false) {
434 $resultStatus = pg_result_status($hPGresult);
435 // PGSQL_EMPTY_QUERY, PGSQL_COMMAND_OK, PGSQL_TUPLES_OK,
436 // PGSQL_COPY_OUT, PGSQL_COPY_IN, PGSQL_BAD_RESPONSE,
437 // PGSQL_NONFATAL_ERROR and PGSQL_FATAL_ERROR
438 // echo 'Query result ' . $i . ' is: ' . $resultStatus . "\n";
439 if ($resultStatus != PGSQL_COMMAND_OK && $resultStatus != PGSQL_TUPLES_OK) {
440 $resultError = pg_result_error($hPGresult);
441 echo '-- error text ' . $i . ': ' . $resultError . "\n";
447 fail('SQL errors loading placex and/or location_property_osmline tables');
450 for ($i = 0; $i < $this->iInstances; $i++) {
451 pg_close($aDBInstances[$i]);
455 info('Reanalysing database');
456 $this->pgsqlRunScript('ANALYSE');
458 $sDatabaseDate = getDatabaseDate($this->oDB);
459 $this->oDB->exec('TRUNCATE import_status');
460 if (!$sDatabaseDate) {
461 warn('could not determine database date.');
463 $sSQL = "INSERT INTO import_status (lastimportdate) VALUES('".$sDatabaseDate."')";
464 $this->oDB->exec($sSQL);
465 echo "Latest data imported from $sDatabaseDate.\n";
469 public function importTigerData()
471 info('Import Tiger data');
473 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
474 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
475 $sTemplate = $this->replaceTablespace(
477 CONST_Tablespace_Aux_Data,
480 $sTemplate = $this->replaceTablespace(
482 CONST_Tablespace_Aux_Index,
485 $this->pgsqlRunScript($sTemplate, false);
487 $aDBInstances = array();
488 for ($i = 0; $i < $this->iInstances; $i++) {
489 // https://secure.php.net/manual/en/function.pg-connect.php
490 $DSN = CONST_Database_DSN;
491 $DSN = preg_replace('/^pgsql:/', '', $DSN);
492 $DSN = preg_replace('/;/', ' ', $DSN);
493 $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW | PGSQL_CONNECT_ASYNC);
494 pg_ping($aDBInstances[$i]);
497 foreach (glob(CONST_Tiger_Data_Path.'/*.sql') as $sFile) {
499 $hFile = fopen($sFile, 'r');
500 $sSQL = fgets($hFile, 100000);
503 for ($i = 0; $i < $this->iInstances; $i++) {
504 if (!pg_connection_busy($aDBInstances[$i])) {
505 while (pg_get_result($aDBInstances[$i]));
506 $sSQL = fgets($hFile, 100000);
508 if (!pg_send_query($aDBInstances[$i], $sSQL)) fail(pg_last_error($aDBInstances[$i]));
510 if ($iLines == 1000) {
523 for ($i = 0; $i < $this->iInstances; $i++) {
524 if (pg_connection_busy($aDBInstances[$i])) $bAnyBusy = true;
531 for ($i = 0; $i < $this->iInstances; $i++) {
532 pg_close($aDBInstances[$i]);
535 info('Creating indexes on Tiger data');
536 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
537 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
538 $sTemplate = $this->replaceTablespace(
540 CONST_Tablespace_Aux_Data,
543 $sTemplate = $this->replaceTablespace(
545 CONST_Tablespace_Aux_Index,
548 $this->pgsqlRunScript($sTemplate, false);
551 public function calculatePostcodes($bCMDResultAll)
553 info('Calculate Postcodes');
554 $this->pgExec('TRUNCATE location_postcode');
556 $sSQL = 'INSERT INTO location_postcode';
557 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
558 $sSQL .= "SELECT nextval('seq_place'), 1, country_code,";
559 $sSQL .= " upper(trim (both ' ' from address->'postcode')) as pc,";
560 $sSQL .= ' ST_Centroid(ST_Collect(ST_Centroid(geometry)))';
561 $sSQL .= ' FROM placex';
562 $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'";
563 $sSQL .= ' AND geometry IS NOT null';
564 $sSQL .= ' GROUP BY country_code, pc';
565 $this->pgExec($sSQL);
567 // only add postcodes that are not yet available in OSM
568 $sSQL = 'INSERT INTO location_postcode';
569 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
570 $sSQL .= "SELECT nextval('seq_place'), 1, 'us', postcode,";
571 $sSQL .= ' ST_SetSRID(ST_Point(x,y),4326)';
572 $sSQL .= ' FROM us_postcode WHERE postcode NOT IN';
573 $sSQL .= ' (SELECT postcode FROM location_postcode';
574 $sSQL .= " WHERE country_code = 'us')";
575 $this->pgExec($sSQL);
577 // add missing postcodes for GB (if available)
578 $sSQL = 'INSERT INTO location_postcode';
579 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
580 $sSQL .= "SELECT nextval('seq_place'), 1, 'gb', postcode, geometry";
581 $sSQL .= ' FROM gb_postcode WHERE postcode NOT IN';
582 $sSQL .= ' (SELECT postcode FROM location_postcode';
583 $sSQL .= " WHERE country_code = 'gb')";
584 $this->pgExec($sSQL);
586 if (!$bCMDResultAll) {
587 $sSQL = "DELETE FROM word WHERE class='place' and type='postcode'";
588 $sSQL .= 'and word NOT IN (SELECT postcode FROM location_postcode)';
589 $this->pgExec($sSQL);
592 $sSQL = 'SELECT count(getorcreate_postcode_id(v)) FROM ';
593 $sSQL .= '(SELECT distinct(postcode) as v FROM location_postcode) p';
594 $this->pgExec($sSQL);
597 public function index($bIndexNoanalyse)
600 $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i'
601 .' -d '.escapeshellarg($this->aDSNInfo['database'])
602 .' -P '.escapeshellarg($this->aDSNInfo['port'])
603 .' -t '.escapeshellarg($this->iInstances.$sOutputFile);
604 if (isset($this->aDSNInfo['hostspec'])) {
605 $sBaseCmd .= ' -H '.escapeshellarg($this->aDSNInfo['hostspec']);
607 if (isset($this->aDSNInfo['username'])) {
608 $sBaseCmd .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
611 info('Index ranks 0 - 4');
612 $iStatus = $this->runWithPgEnv($sBaseCmd.' -R 4');
614 fail('error status ' . $iStatus . ' running nominatim!');
616 if (!$bIndexNoanalyse) $this->pgsqlRunScript('ANALYSE');
618 info('Index ranks 5 - 25');
619 $iStatus = $this->runWithPgEnv($sBaseCmd.' -r 5 -R 25');
621 fail('error status ' . $iStatus . ' running nominatim!');
623 if (!$bIndexNoanalyse) $this->pgsqlRunScript('ANALYSE');
625 info('Index ranks 26 - 30');
626 $iStatus = $this->runWithPgEnv($sBaseCmd.' -r 26');
628 fail('error status ' . $iStatus . ' running nominatim!');
631 info('Index postcodes');
632 $sSQL = 'UPDATE location_postcode SET indexed_status = 0';
633 $this->pgExec($sSQL);
636 public function createSearchIndices()
638 info('Create Search indices');
640 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
641 if (!$this->dbReverseOnly()) {
642 $sTemplate .= file_get_contents(CONST_BasePath.'/sql/indices_search.src.sql');
644 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
645 $sTemplate = $this->replaceTablespace(
646 '{ts:address-index}',
647 CONST_Tablespace_Address_Index,
650 $sTemplate = $this->replaceTablespace(
652 CONST_Tablespace_Search_Index,
655 $sTemplate = $this->replaceTablespace(
657 CONST_Tablespace_Aux_Index,
660 $this->pgsqlRunScript($sTemplate);
663 public function createCountryNames()
665 info('Create search index for default country names');
667 $this->pgsqlRunScript("select getorcreate_country(make_standard_name('uk'), 'gb')");
668 $this->pgsqlRunScript("select getorcreate_country(make_standard_name('united states'), 'us')");
669 $this->pgsqlRunScript('select count(*) from (select getorcreate_country(make_standard_name(country_code), country_code) from country_name where country_code is not null) as x');
670 $this->pgsqlRunScript("select count(*) from (select getorcreate_country(make_standard_name(name->'name'), country_code) from country_name where name ? 'name') as x");
671 $sSQL = 'select count(*) from (select getorcreate_country(make_standard_name(v),'
672 .'country_code) from (select country_code, skeys(name) as k, svals(name) as v from country_name) x where k ';
673 if (CONST_Languages) {
676 foreach (explode(',', CONST_Languages) as $sLang) {
677 $sSQL .= $sDelim."'name:$sLang'";
682 // all include all simple name tags
683 $sSQL .= "like 'name:%'";
686 $this->pgsqlRunScript($sSQL);
689 public function drop()
691 info('Drop tables only required for updates');
693 // The implementation is potentially a bit dangerous because it uses
694 // a positive selection of tables to keep, and deletes everything else.
695 // Including any tables that the unsuspecting user might have manually
696 // created. USE AT YOUR OWN PERIL.
697 // tables we want to keep. everything else goes.
698 $aKeepTables = array(
704 'location_property*',
717 $aDropTables = array();
718 $aHaveTables = $this->oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
720 foreach ($aHaveTables as $sTable) {
722 foreach ($aKeepTables as $sKeep) {
723 if (fnmatch($sKeep, $sTable)) {
728 if (!$bFound) array_push($aDropTables, $sTable);
730 foreach ($aDropTables as $sDrop) {
731 if ($this->bVerbose) echo "Dropping table $sDrop\n";
732 $this->oDB->exec("DROP TABLE $sDrop CASCADE");
733 // ignore warnings/errors as they might be caused by a table having
734 // been deleted already by CASCADE
737 if (!is_null(CONST_Osm2pgsql_Flatnode_File) && CONST_Osm2pgsql_Flatnode_File) {
738 if (file_exists(CONST_Osm2pgsql_Flatnode_File)) {
739 if ($this->bVerbose) echo 'Deleting '.CONST_Osm2pgsql_Flatnode_File."\n";
740 unlink(CONST_Osm2pgsql_Flatnode_File);
745 private function pgsqlRunDropAndRestore($sDumpFile)
748 .' -p '.escapeshellarg($this->aDSNInfo['port'])
749 .' -d '.escapeshellarg($this->aDSNInfo['database'])
750 .' --no-owner -Fc --clean '.escapeshellarg($sDumpFile);
751 if ($this->oDB->getPostgresVersion() >= 9.04) {
752 $sCMD .= ' --if-exists';
754 if (isset($this->aDSNInfo['hostspec'])) {
755 $sCMD .= ' -h '.escapeshellarg($this->aDSNInfo['hostspec']);
757 if (isset($this->aDSNInfo['username'])) {
758 $sCMD .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
761 $this->runWithPgEnv($sCMD);
764 private function pgsqlRunScript($sScript, $bfatal = true)
774 private function createSqlFunctions()
776 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
777 $sTemplate = str_replace('{modulepath}', $this->sModulePath, $sTemplate);
778 if ($this->bEnableDiffUpdates) {
779 $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate);
781 if ($this->bEnableDebugStatements) {
782 $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
784 if (CONST_Limit_Reindexing) {
785 $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
787 if (!CONST_Use_US_Tiger_Data) {
788 $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate);
790 if (!CONST_Use_Aux_Location_data) {
791 $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate);
794 $sReverseOnly = $this->dbReverseOnly() ? 'true' : 'false';
795 $sTemplate = str_replace('%REVERSE-ONLY%', $sReverseOnly, $sTemplate);
797 $this->pgsqlRunScript($sTemplate);
800 private function pgsqlRunPartitionScript($sTemplate)
802 $sSQL = 'select distinct partition from country_name';
803 $aPartitions = $this->oDB->getCol($sSQL);
804 if (!$this->bNoPartitions) $aPartitions[] = 0;
806 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
807 foreach ($aMatches as $aMatch) {
809 foreach ($aPartitions as $sPartitionName) {
810 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
812 $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
815 $this->pgsqlRunScript($sTemplate);
818 private function pgsqlRunScriptFile($sFilename)
820 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
823 .' -p '.escapeshellarg($this->aDSNInfo['port'])
824 .' -d '.escapeshellarg($this->aDSNInfo['database']);
825 if (!$this->bVerbose) {
828 if (isset($this->aDSNInfo['hostspec'])) {
829 $sCMD .= ' -h '.escapeshellarg($this->aDSNInfo['hostspec']);
831 if (isset($this->aDSNInfo['username'])) {
832 $sCMD .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
835 if (isset($this->aDSNInfo['password'])) {
836 $aProcEnv = array_merge(array('PGPASSWORD' => $this->aDSNInfo['password']), $_ENV);
839 if (preg_match('/\\.gz$/', $sFilename)) {
840 $aDescriptors = array(
841 0 => array('pipe', 'r'),
842 1 => array('pipe', 'w'),
843 2 => array('file', '/dev/null', 'a')
845 $hGzipProcess = proc_open('zcat '.escapeshellarg($sFilename), $aDescriptors, $ahGzipPipes);
846 if (!is_resource($hGzipProcess)) fail('unable to start zcat');
847 $aReadPipe = $ahGzipPipes[1];
848 fclose($ahGzipPipes[0]);
850 $sCMD .= ' -f '.escapeshellarg($sFilename);
851 $aReadPipe = array('pipe', 'r');
853 $aDescriptors = array(
855 1 => array('pipe', 'w'),
856 2 => array('file', '/dev/null', 'a')
859 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes, null, $aProcEnv);
860 if (!is_resource($hProcess)) fail('unable to start pgsql');
861 // TODO: error checking
862 while (!feof($ahPipes[1])) {
863 echo fread($ahPipes[1], 4096);
866 $iReturn = proc_close($hProcess);
868 fail("pgsql returned with error code ($iReturn)");
871 fclose($ahGzipPipes[1]);
872 proc_close($hGzipProcess);
876 private function replaceTablespace($sTemplate, $sTablespace, $sSql)
879 $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"', $sSql);
881 $sSql = str_replace($sTemplate, '', $sSql);
886 private function runWithPgEnv($sCmd)
888 if ($this->bVerbose) {
889 echo "Execute: $sCmd\n";
894 if (isset($this->aDSNInfo['password'])) {
895 $aProcEnv = array_merge(array('PGPASSWORD' => $this->aDSNInfo['password']), $_ENV);
898 return runWithEnv($sCmd, $aProcEnv);
902 * Execute the SQL command on the open database.
904 * @param string $sSQL SQL command to execute.
908 * @pre connect() must have been called.
910 private function pgExec($sSQL)
912 $this->oDB->exec($sSQL);
916 * Check if the database is in reverse-only mode.
918 * @return True if there is no search_name table and infrastructure.
920 private function dbReverseOnly()
922 return !($this->oDB->tableExists('search_name'));