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_ExtraDataPath.'/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');
165 public function importData($sOSMFile)
169 $osm2pgsql = CONST_Osm2pgsql_Binary;
170 if (!file_exists($osm2pgsql)) {
171 echo "Check CONST_Osm2pgsql_Binary in your local settings file.\n";
172 echo "Normally you should not need to set this manually.\n";
173 fail("osm2pgsql not found in '$osm2pgsql'");
176 $osm2pgsql .= ' -S '.escapeshellarg(CONST_Import_Style);
178 if (!is_null(CONST_Osm2pgsql_Flatnode_File) && CONST_Osm2pgsql_Flatnode_File) {
179 $osm2pgsql .= ' --flat-nodes '.escapeshellarg(CONST_Osm2pgsql_Flatnode_File);
182 if (CONST_Tablespace_Osm2pgsql_Data)
183 $osm2pgsql .= ' --tablespace-slim-data '.escapeshellarg(CONST_Tablespace_Osm2pgsql_Data);
184 if (CONST_Tablespace_Osm2pgsql_Index)
185 $osm2pgsql .= ' --tablespace-slim-index '.escapeshellarg(CONST_Tablespace_Osm2pgsql_Index);
186 if (CONST_Tablespace_Place_Data)
187 $osm2pgsql .= ' --tablespace-main-data '.escapeshellarg(CONST_Tablespace_Place_Data);
188 if (CONST_Tablespace_Place_Index)
189 $osm2pgsql .= ' --tablespace-main-index '.escapeshellarg(CONST_Tablespace_Place_Index);
190 $osm2pgsql .= ' -lsc -O gazetteer --hstore --number-processes 1';
191 $osm2pgsql .= ' -C '.escapeshellarg($this->iCacheMemory);
192 $osm2pgsql .= ' -P '.escapeshellarg($this->aDSNInfo['port']);
193 if (isset($this->aDSNInfo['username'])) {
194 $osm2pgsql .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
196 if (isset($this->aDSNInfo['hostspec'])) {
197 $osm2pgsql .= ' -H '.escapeshellarg($this->aDSNInfo['hostspec']);
199 $osm2pgsql .= ' -d '.escapeshellarg($this->aDSNInfo['database']).' '.escapeshellarg($sOSMFile);
201 $this->runWithPgEnv($osm2pgsql);
203 if (!$this->sIgnoreErrors && !$this->oDB->getRow('select * from place limit 1')) {
208 public function createFunctions()
210 info('Create Functions');
212 // Try accessing the C module, so we know early if something is wrong
213 checkModulePresence(); // raises exception on failure
215 $this->createSqlFunctions();
218 public function createTables($bReverseOnly = false)
220 info('Create Tables');
222 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tables.sql');
223 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
224 $sTemplate = $this->replaceTablespace(
226 CONST_Tablespace_Address_Data,
229 $sTemplate = $this->replaceTablespace(
230 '{ts:address-index}',
231 CONST_Tablespace_Address_Index,
234 $sTemplate = $this->replaceTablespace(
236 CONST_Tablespace_Search_Data,
239 $sTemplate = $this->replaceTablespace(
241 CONST_Tablespace_Search_Index,
244 $sTemplate = $this->replaceTablespace(
246 CONST_Tablespace_Aux_Data,
249 $sTemplate = $this->replaceTablespace(
251 CONST_Tablespace_Aux_Index,
255 $this->pgsqlRunScript($sTemplate, false);
258 $this->pgExec('DROP TABLE search_name');
261 $oAlParser = new AddressLevelParser(CONST_Address_Level_Config);
262 $oAlParser->createTable($this->oDB, 'address_levels');
265 public function createPartitionTables()
267 info('Create Partition Tables');
269 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-tables.src.sql');
270 $sTemplate = $this->replaceTablespace(
272 CONST_Tablespace_Address_Data,
276 $sTemplate = $this->replaceTablespace(
277 '{ts:address-index}',
278 CONST_Tablespace_Address_Index,
282 $sTemplate = $this->replaceTablespace(
284 CONST_Tablespace_Search_Data,
288 $sTemplate = $this->replaceTablespace(
290 CONST_Tablespace_Search_Index,
294 $sTemplate = $this->replaceTablespace(
296 CONST_Tablespace_Aux_Data,
300 $sTemplate = $this->replaceTablespace(
302 CONST_Tablespace_Aux_Index,
306 $this->pgsqlRunPartitionScript($sTemplate);
309 public function createPartitionFunctions()
311 info('Create Partition Functions');
313 $sTemplate = file_get_contents(CONST_BasePath.'/sql/partition-functions.src.sql');
314 $this->pgsqlRunPartitionScript($sTemplate);
317 public function importWikipediaArticles()
319 $sWikiArticlesFile = CONST_Wikipedia_Data_Path.'/wikimedia-importance.sql.gz';
320 if (file_exists($sWikiArticlesFile)) {
321 info('Importing wikipedia articles and redirects');
322 $this->pgExec('DROP TABLE IF EXISTS wikipedia_article');
323 $this->pgExec('DROP TABLE IF EXISTS wikipedia_redirect');
324 $this->pgsqlRunScriptFile($sWikiArticlesFile);
326 warn('wikipedia importance dump file not found - places will have default importance');
330 public function loadData($bDisableTokenPrecalc)
332 info('Drop old Data');
334 $this->pgExec('TRUNCATE word');
336 $this->pgExec('TRUNCATE placex');
338 $this->pgExec('TRUNCATE location_property_osmline');
340 $this->pgExec('TRUNCATE place_addressline');
342 $this->pgExec('TRUNCATE location_area');
344 if (!$this->dbReverseOnly()) {
345 $this->pgExec('TRUNCATE search_name');
348 $this->pgExec('TRUNCATE search_name_blank');
350 $this->pgExec('DROP SEQUENCE seq_place');
352 $this->pgExec('CREATE SEQUENCE seq_place start 100000');
355 $sSQL = 'select distinct partition from country_name';
356 $aPartitions = $this->oDB->getCol($sSQL);
358 if (!$this->bNoPartitions) $aPartitions[] = 0;
359 foreach ($aPartitions as $sPartition) {
360 $this->pgExec('TRUNCATE location_road_'.$sPartition);
364 // used by getorcreate_word_id to ignore frequent partial words
365 $sSQL = 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS ';
366 $sSQL .= '$$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE';
367 $this->pgExec($sSQL);
370 // pre-create the word list
371 if (!$bDisableTokenPrecalc) {
372 info('Loading word list');
373 $this->pgsqlRunScriptFile(CONST_BasePath.'/data/words.sql');
377 $sColumns = 'osm_type, osm_id, class, type, name, admin_level, address, extratags, geometry';
379 $aDBInstances = array();
380 $iLoadThreads = max(1, $this->iInstances - 1);
381 for ($i = 0; $i < $iLoadThreads; $i++) {
382 // https://secure.php.net/manual/en/function.pg-connect.php
383 $DSN = CONST_Database_DSN;
384 $DSN = preg_replace('/^pgsql:/', '', $DSN);
385 $DSN = preg_replace('/;/', ' ', $DSN);
386 $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW);
387 pg_ping($aDBInstances[$i]);
390 for ($i = 0; $i < $iLoadThreads; $i++) {
391 $sSQL = "INSERT INTO placex ($sColumns) SELECT $sColumns FROM place WHERE osm_id % $iLoadThreads = $i";
392 $sSQL .= " and not (class='place' and type='houses' and osm_type='W'";
393 $sSQL .= " and ST_GeometryType(geometry) = 'ST_LineString')";
394 $sSQL .= ' and ST_IsValid(geometry)';
395 if ($this->bVerbose) echo "$sSQL\n";
396 if (!pg_send_query($aDBInstances[$i], $sSQL)) {
397 fail(pg_last_error($aDBInstances[$i]));
401 // last thread for interpolation lines
402 // https://secure.php.net/manual/en/function.pg-connect.php
403 $DSN = CONST_Database_DSN;
404 $DSN = preg_replace('/^pgsql:/', '', $DSN);
405 $DSN = preg_replace('/;/', ' ', $DSN);
406 $aDBInstances[$iLoadThreads] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW);
407 pg_ping($aDBInstances[$iLoadThreads]);
408 $sSQL = 'insert into location_property_osmline';
409 $sSQL .= ' (osm_id, address, linegeo)';
410 $sSQL .= ' SELECT osm_id, address, geometry from place where ';
411 $sSQL .= "class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString'";
412 if ($this->bVerbose) echo "$sSQL\n";
413 if (!pg_send_query($aDBInstances[$iLoadThreads], $sSQL)) {
414 fail(pg_last_error($aDBInstances[$iLoadThreads]));
418 for ($i = 0; $i <= $iLoadThreads; $i++) {
419 while (($hPGresult = pg_get_result($aDBInstances[$i])) !== false) {
420 $resultStatus = pg_result_status($hPGresult);
421 // PGSQL_EMPTY_QUERY, PGSQL_COMMAND_OK, PGSQL_TUPLES_OK,
422 // PGSQL_COPY_OUT, PGSQL_COPY_IN, PGSQL_BAD_RESPONSE,
423 // PGSQL_NONFATAL_ERROR and PGSQL_FATAL_ERROR
424 // echo 'Query result ' . $i . ' is: ' . $resultStatus . "\n";
425 if ($resultStatus != PGSQL_COMMAND_OK && $resultStatus != PGSQL_TUPLES_OK) {
426 $resultError = pg_result_error($hPGresult);
427 echo '-- error text ' . $i . ': ' . $resultError . "\n";
433 fail('SQL errors loading placex and/or location_property_osmline tables');
436 for ($i = 0; $i < $this->iInstances; $i++) {
437 pg_close($aDBInstances[$i]);
441 info('Reanalysing database');
442 $this->pgsqlRunScript('ANALYSE');
444 $sDatabaseDate = getDatabaseDate($this->oDB);
445 $this->oDB->exec('TRUNCATE import_status');
446 if (!$sDatabaseDate) {
447 warn('could not determine database date.');
449 $sSQL = "INSERT INTO import_status (lastimportdate) VALUES('".$sDatabaseDate."')";
450 $this->oDB->exec($sSQL);
451 echo "Latest data imported from $sDatabaseDate.\n";
455 public function importTigerData()
457 info('Import Tiger data');
459 $aFilenames = glob(CONST_Tiger_Data_Path.'/*.sql');
460 info('Found '.count($aFilenames).' SQL files in path '.CONST_Tiger_Data_Path);
461 if (empty($aFilenames)) return;
463 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_start.sql');
464 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
465 $sTemplate = $this->replaceTablespace(
467 CONST_Tablespace_Aux_Data,
470 $sTemplate = $this->replaceTablespace(
472 CONST_Tablespace_Aux_Index,
475 $this->pgsqlRunScript($sTemplate, false);
477 $aDBInstances = array();
478 for ($i = 0; $i < $this->iInstances; $i++) {
479 // https://secure.php.net/manual/en/function.pg-connect.php
480 $DSN = CONST_Database_DSN;
481 $DSN = preg_replace('/^pgsql:/', '', $DSN);
482 $DSN = preg_replace('/;/', ' ', $DSN);
483 $aDBInstances[$i] = pg_connect($DSN, PGSQL_CONNECT_FORCE_NEW | PGSQL_CONNECT_ASYNC);
484 pg_ping($aDBInstances[$i]);
487 foreach ($aFilenames as $sFile) {
489 $hFile = fopen($sFile, 'r');
490 $sSQL = fgets($hFile, 100000);
493 for ($i = 0; $i < $this->iInstances; $i++) {
494 if (!pg_connection_busy($aDBInstances[$i])) {
495 while (pg_get_result($aDBInstances[$i]));
496 $sSQL = fgets($hFile, 100000);
498 if (!pg_send_query($aDBInstances[$i], $sSQL)) fail(pg_last_error($aDBInstances[$i]));
500 if ($iLines == 1000) {
513 for ($i = 0; $i < $this->iInstances; $i++) {
514 if (pg_connection_busy($aDBInstances[$i])) $bAnyBusy = true;
521 for ($i = 0; $i < $this->iInstances; $i++) {
522 pg_close($aDBInstances[$i]);
525 info('Creating indexes on Tiger data');
526 $sTemplate = file_get_contents(CONST_BasePath.'/sql/tiger_import_finish.sql');
527 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
528 $sTemplate = $this->replaceTablespace(
530 CONST_Tablespace_Aux_Data,
533 $sTemplate = $this->replaceTablespace(
535 CONST_Tablespace_Aux_Index,
538 $this->pgsqlRunScript($sTemplate, false);
541 public function calculatePostcodes($bCMDResultAll)
543 info('Calculate Postcodes');
544 $this->pgExec('TRUNCATE location_postcode');
546 $sSQL = 'INSERT INTO location_postcode';
547 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
548 $sSQL .= "SELECT nextval('seq_place'), 1, country_code,";
549 $sSQL .= " upper(trim (both ' ' from address->'postcode')) as pc,";
550 $sSQL .= ' ST_Centroid(ST_Collect(ST_Centroid(geometry)))';
551 $sSQL .= ' FROM placex';
552 $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'";
553 $sSQL .= ' AND geometry IS NOT null';
554 $sSQL .= ' GROUP BY country_code, pc';
555 $this->pgExec($sSQL);
557 // only add postcodes that are not yet available in OSM
558 $sSQL = 'INSERT INTO location_postcode';
559 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
560 $sSQL .= "SELECT nextval('seq_place'), 1, 'us', postcode,";
561 $sSQL .= ' ST_SetSRID(ST_Point(x,y),4326)';
562 $sSQL .= ' FROM us_postcode WHERE postcode NOT IN';
563 $sSQL .= ' (SELECT postcode FROM location_postcode';
564 $sSQL .= " WHERE country_code = 'us')";
565 $this->pgExec($sSQL);
567 // add missing postcodes for GB (if available)
568 $sSQL = 'INSERT INTO location_postcode';
569 $sSQL .= ' (place_id, indexed_status, country_code, postcode, geometry) ';
570 $sSQL .= "SELECT nextval('seq_place'), 1, 'gb', postcode, geometry";
571 $sSQL .= ' FROM gb_postcode WHERE postcode NOT IN';
572 $sSQL .= ' (SELECT postcode FROM location_postcode';
573 $sSQL .= " WHERE country_code = 'gb')";
574 $this->pgExec($sSQL);
576 if (!$bCMDResultAll) {
577 $sSQL = "DELETE FROM word WHERE class='place' and type='postcode'";
578 $sSQL .= 'and word NOT IN (SELECT postcode FROM location_postcode)';
579 $this->pgExec($sSQL);
582 $sSQL = 'SELECT count(getorcreate_postcode_id(v)) FROM ';
583 $sSQL .= '(SELECT distinct(postcode) as v FROM location_postcode) p';
584 $this->pgExec($sSQL);
587 public function index($bIndexNoanalyse)
590 $sBaseCmd = CONST_InstallPath.'/nominatim/nominatim -i'
591 .' -d '.escapeshellarg($this->aDSNInfo['database'])
592 .' -P '.escapeshellarg($this->aDSNInfo['port'])
593 .' -t '.escapeshellarg($this->iInstances.$sOutputFile);
594 if (isset($this->aDSNInfo['hostspec'])) {
595 $sBaseCmd .= ' -H '.escapeshellarg($this->aDSNInfo['hostspec']);
597 if (isset($this->aDSNInfo['username'])) {
598 $sBaseCmd .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
601 info('Index ranks 0 - 4');
602 $iStatus = $this->runWithPgEnv($sBaseCmd.' -R 4');
604 fail('error status ' . $iStatus . ' running nominatim!');
606 if (!$bIndexNoanalyse) $this->pgsqlRunScript('ANALYSE');
608 info('Index ranks 5 - 25');
609 $iStatus = $this->runWithPgEnv($sBaseCmd.' -r 5 -R 25');
611 fail('error status ' . $iStatus . ' running nominatim!');
613 if (!$bIndexNoanalyse) $this->pgsqlRunScript('ANALYSE');
615 info('Index ranks 26 - 30');
616 $iStatus = $this->runWithPgEnv($sBaseCmd.' -r 26');
618 fail('error status ' . $iStatus . ' running nominatim!');
621 info('Index postcodes');
622 $sSQL = 'UPDATE location_postcode SET indexed_status = 0';
623 $this->pgExec($sSQL);
626 public function createSearchIndices()
628 info('Create Search indices');
630 $sTemplate = file_get_contents(CONST_BasePath.'/sql/indices.src.sql');
631 if (!$this->dbReverseOnly()) {
632 $sTemplate .= file_get_contents(CONST_BasePath.'/sql/indices_search.src.sql');
634 $sTemplate = str_replace('{www-user}', CONST_Database_Web_User, $sTemplate);
635 $sTemplate = $this->replaceTablespace(
636 '{ts:address-index}',
637 CONST_Tablespace_Address_Index,
640 $sTemplate = $this->replaceTablespace(
642 CONST_Tablespace_Search_Index,
645 $sTemplate = $this->replaceTablespace(
647 CONST_Tablespace_Aux_Index,
650 $this->pgsqlRunScript($sTemplate);
653 public function createCountryNames()
655 info('Create search index for default country names');
657 $this->pgsqlRunScript("select getorcreate_country(make_standard_name('uk'), 'gb')");
658 $this->pgsqlRunScript("select getorcreate_country(make_standard_name('united states'), 'us')");
659 $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');
660 $this->pgsqlRunScript("select count(*) from (select getorcreate_country(make_standard_name(name->'name'), country_code) from country_name where name ? 'name') as x");
661 $sSQL = 'select count(*) from (select getorcreate_country(make_standard_name(v),'
662 .'country_code) from (select country_code, skeys(name) as k, svals(name) as v from country_name) x where k ';
663 if (CONST_Languages) {
666 foreach (explode(',', CONST_Languages) as $sLang) {
667 $sSQL .= $sDelim."'name:$sLang'";
672 // all include all simple name tags
673 $sSQL .= "like 'name:%'";
676 $this->pgsqlRunScript($sSQL);
679 public function drop()
681 info('Drop tables only required for updates');
683 // The implementation is potentially a bit dangerous because it uses
684 // a positive selection of tables to keep, and deletes everything else.
685 // Including any tables that the unsuspecting user might have manually
686 // created. USE AT YOUR OWN PERIL.
687 // tables we want to keep. everything else goes.
688 $aKeepTables = array(
694 'location_property*',
707 $aDropTables = array();
708 $aHaveTables = $this->oDB->getCol("SELECT tablename FROM pg_tables WHERE schemaname='public'");
710 foreach ($aHaveTables as $sTable) {
712 foreach ($aKeepTables as $sKeep) {
713 if (fnmatch($sKeep, $sTable)) {
718 if (!$bFound) array_push($aDropTables, $sTable);
720 foreach ($aDropTables as $sDrop) {
721 if ($this->bVerbose) echo "Dropping table $sDrop\n";
722 $this->oDB->exec("DROP TABLE IF EXISTS $sDrop CASCADE");
725 if (!is_null(CONST_Osm2pgsql_Flatnode_File) && CONST_Osm2pgsql_Flatnode_File) {
726 if (file_exists(CONST_Osm2pgsql_Flatnode_File)) {
727 if ($this->bVerbose) echo 'Deleting '.CONST_Osm2pgsql_Flatnode_File."\n";
728 unlink(CONST_Osm2pgsql_Flatnode_File);
733 private function pgsqlRunScript($sScript, $bfatal = true)
743 private function createSqlFunctions()
745 $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
746 $sTemplate = str_replace('{modulepath}', $this->sModulePath, $sTemplate);
747 if ($this->bEnableDiffUpdates) {
748 $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate);
750 if ($this->bEnableDebugStatements) {
751 $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
753 if (CONST_Limit_Reindexing) {
754 $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
756 if (!CONST_Use_US_Tiger_Data) {
757 $sTemplate = str_replace('-- %NOTIGERDATA% ', '', $sTemplate);
759 if (!CONST_Use_Aux_Location_data) {
760 $sTemplate = str_replace('-- %NOAUXDATA% ', '', $sTemplate);
763 $sReverseOnly = $this->dbReverseOnly() ? 'true' : 'false';
764 $sTemplate = str_replace('%REVERSE-ONLY%', $sReverseOnly, $sTemplate);
766 $this->pgsqlRunScript($sTemplate);
769 private function pgsqlRunPartitionScript($sTemplate)
771 $sSQL = 'select distinct partition from country_name';
772 $aPartitions = $this->oDB->getCol($sSQL);
773 if (!$this->bNoPartitions) $aPartitions[] = 0;
775 preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
776 foreach ($aMatches as $aMatch) {
778 foreach ($aPartitions as $sPartitionName) {
779 $sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
781 $sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
784 $this->pgsqlRunScript($sTemplate);
787 private function pgsqlRunScriptFile($sFilename)
789 if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
792 .' -p '.escapeshellarg($this->aDSNInfo['port'])
793 .' -d '.escapeshellarg($this->aDSNInfo['database']);
794 if (!$this->bVerbose) {
797 if (isset($this->aDSNInfo['hostspec'])) {
798 $sCMD .= ' -h '.escapeshellarg($this->aDSNInfo['hostspec']);
800 if (isset($this->aDSNInfo['username'])) {
801 $sCMD .= ' -U '.escapeshellarg($this->aDSNInfo['username']);
804 if (isset($this->aDSNInfo['password'])) {
805 $aProcEnv = array_merge(array('PGPASSWORD' => $this->aDSNInfo['password']), $_ENV);
808 if (preg_match('/\\.gz$/', $sFilename)) {
809 $aDescriptors = array(
810 0 => array('pipe', 'r'),
811 1 => array('pipe', 'w'),
812 2 => array('file', '/dev/null', 'a')
814 $hGzipProcess = proc_open('zcat '.escapeshellarg($sFilename), $aDescriptors, $ahGzipPipes);
815 if (!is_resource($hGzipProcess)) fail('unable to start zcat');
816 $aReadPipe = $ahGzipPipes[1];
817 fclose($ahGzipPipes[0]);
819 $sCMD .= ' -f '.escapeshellarg($sFilename);
820 $aReadPipe = array('pipe', 'r');
822 $aDescriptors = array(
824 1 => array('pipe', 'w'),
825 2 => array('file', '/dev/null', 'a')
828 $hProcess = proc_open($sCMD, $aDescriptors, $ahPipes, null, $aProcEnv);
829 if (!is_resource($hProcess)) fail('unable to start pgsql');
830 // TODO: error checking
831 while (!feof($ahPipes[1])) {
832 echo fread($ahPipes[1], 4096);
835 $iReturn = proc_close($hProcess);
837 fail("pgsql returned with error code ($iReturn)");
840 fclose($ahGzipPipes[1]);
841 proc_close($hGzipProcess);
845 private function replaceTablespace($sTemplate, $sTablespace, $sSql)
848 $sSql = str_replace($sTemplate, 'TABLESPACE "'.$sTablespace.'"', $sSql);
850 $sSql = str_replace($sTemplate, '', $sSql);
855 private function runWithPgEnv($sCmd)
857 if ($this->bVerbose) {
858 echo "Execute: $sCmd\n";
863 if (isset($this->aDSNInfo['password'])) {
864 $aProcEnv = array_merge(array('PGPASSWORD' => $this->aDSNInfo['password']), $_ENV);
867 return runWithEnv($sCmd, $aProcEnv);
871 * Execute the SQL command on the open database.
873 * @param string $sSQL SQL command to execute.
877 * @pre connect() must have been called.
879 private function pgExec($sSQL)
881 $this->oDB->exec($sSQL);
885 * Check if the database is in reverse-only mode.
887 * @return True if there is no search_name table and infrastructure.
889 private function dbReverseOnly()
891 return !($this->oDB->tableExists('search_name'));