if ($aCMDResult['create-tables'] || $aCMDResult['all']) {
$bDidSomething = true;
- $oSetup->createTables($aCMDResult['reverse-only']);
- $oSetup->createFunctions();
- $oSetup->createTableTriggers();
+ $oCmd = (clone($oNominatimCmd))->addParams('transition', '--create-tables');
+
+ if ($aCMDResult['reverse-only'] ?? false) {
+ $oCmd->addParams('--reverse-only');
+ }
+
+ run($oCmd);
}
if ($aCMDResult['create-partition-tables'] || $aCMDResult['all']) {
$bDidSomething = true;
- $oSetup->createPartitionTables();
+ run((clone($oNominatimCmd))->addParams('transition', '--create-partition-tables'));
}
if ($aCMDResult['create-partition-functions'] || $aCMDResult['all']) {
$this->createSqlFunctions();
}
- public function createTables($bReverseOnly = false)
- {
- info('Create Tables');
-
- $sTemplate = file_get_contents(CONST_SqlDir.'/tables.sql');
- $sTemplate = $this->replaceSqlPatterns($sTemplate);
-
- $this->pgsqlRunScript($sTemplate, false);
-
- if ($bReverseOnly) {
- $this->dropTable('search_name');
- }
-
- (clone($this->oNominatimCmd))->addParams('refresh', '--address-levels')->run();
- }
-
- public function createTableTriggers()
- {
- info('Create Tables');
-
- $sTemplate = file_get_contents(CONST_SqlDir.'/table-triggers.sql');
- $sTemplate = $this->replaceSqlPatterns($sTemplate);
-
- $this->pgsqlRunScript($sTemplate, false);
- }
-
- public function createPartitionTables()
- {
- info('Create Partition Tables');
-
- $sTemplate = file_get_contents(CONST_SqlDir.'/partition-tables.src.sql');
- $sTemplate = $this->replaceSqlPatterns($sTemplate);
-
- $this->pgsqlRunPartitionScript($sTemplate);
- }
-
public function importTigerData($sTigerPath)
{
info('Import Tiger data');
$oCmd->run(!$this->sIgnoreErrors);
}
- private function pgsqlRunPartitionScript($sTemplate)
- {
- $sSQL = 'select distinct partition from country_name order by partition';
- $aPartitions = $this->db()->getCol($sSQL);
- if ($aPartitions[0] != 0) $aPartitions[] = 0;
-
- 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);
- }
-
- $this->pgsqlRunScript($sTemplate);
- }
-
private function pgsqlRunScriptFile($sFilename)
{
if (!file_exists($sFilename)) fail('unable to find '.$sFilename);
);
--- start
-CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large) {ts:address-data};
-CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) {ts:address-index};
-CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry) {ts:address-index};
+{% for partition in db.partitions %}
+ CREATE TABLE location_area_large_{{ partition }} () INHERITS (location_area_large) {{db.tablespace.address_data}};
+ CREATE INDEX idx_location_area_large_{{ partition }}_place_id ON location_area_large_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}};
+ CREATE INDEX idx_location_area_large_{{ partition }}_geometry ON location_area_large_{{ partition }} USING GIST (geometry) {{db.tablespace.address_index}};
-CREATE TABLE search_name_-partition- () INHERITS (search_name_blank) {ts:address-data};
-CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id) {ts:address-index};
-CREATE INDEX idx_search_name_-partition-_centroid_street ON search_name_-partition- USING GIST (centroid) {ts:address-index} where address_rank between 26 and 27;
-CREATE INDEX idx_search_name_-partition-_centroid_place ON search_name_-partition- USING GIST (centroid) {ts:address-index} where address_rank between 2 and 25;
+ CREATE TABLE search_name_{{ partition }} () INHERITS (search_name_blank) {{db.tablespace.address_data}};
+ CREATE INDEX idx_search_name_{{ partition }}_place_id ON search_name_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}};
+ CREATE INDEX idx_search_name_{{ partition }}_centroid_street ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} where address_rank between 26 and 27;
+ CREATE INDEX idx_search_name_{{ partition }}_centroid_place ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} where address_rank between 2 and 25;
-DROP TABLE IF EXISTS location_road_-partition-;
-CREATE TABLE location_road_-partition- (
- place_id BIGINT,
- partition SMALLINT,
- country_code VARCHAR(2),
- geometry GEOMETRY(Geometry, 4326)
- ) {ts:address-data};
-CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry) {ts:address-index};
-CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id) {ts:address-index};
+ DROP TABLE IF EXISTS location_road_{{ partition }};
+ CREATE TABLE location_road_{{ partition }} (
+ place_id BIGINT,
+ partition SMALLINT,
+ country_code VARCHAR(2),
+ geometry GEOMETRY(Geometry, 4326)
+ ) {{db.tablespace.address_data}};
+ CREATE INDEX idx_location_road_{{ partition }}_geometry ON location_road_{{ partition }} USING GIST (geometry) {{db.tablespace.address_index}};
+ CREATE INDEX idx_location_road_{{ partition }}_place_id ON location_road_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}};
--- end
+{% endfor %}
sequence_id integer,
indexed boolean
);
-GRANT SELECT ON import_status TO "{www-user}" ;
+GRANT SELECT ON import_status TO "{{config.DATABASE_WEBUSER}}" ;
drop table if exists import_osmosis_log;
CREATE TABLE import_osmosis_log (
secret text
);
CREATE INDEX idx_new_query_log_starttime ON new_query_log USING BTREE (starttime);
-GRANT INSERT ON new_query_log TO "{www-user}" ;
-GRANT UPDATE ON new_query_log TO "{www-user}" ;
-GRANT SELECT ON new_query_log TO "{www-user}" ;
+GRANT INSERT ON new_query_log TO "{{config.DATABASE_WEBUSER}}" ;
+GRANT UPDATE ON new_query_log TO "{{config.DATABASE_WEBUSER}}" ;
+GRANT SELECT ON new_query_log TO "{{config.DATABASE_WEBUSER}}" ;
-GRANT SELECT ON TABLE country_name TO "{www-user}";
+GRANT SELECT ON TABLE country_name TO "{{config.DATABASE_WEBUSER}}";
DROP TABLE IF EXISTS nominatim_properties;
CREATE TABLE nominatim_properties (
property TEXT,
value TEXT
);
-GRANT SELECT ON TABLE nominatim_properties TO "{www-user}";
+GRANT SELECT ON TABLE nominatim_properties TO "{{config.DATABASE_WEBUSER}}";
drop table IF EXISTS word;
CREATE TABLE word (
country_code varchar(2),
search_name_count INTEGER,
operator TEXT
- ) {ts:search-data};
-CREATE INDEX idx_word_word_token on word USING BTREE (word_token) {ts:search-index};
-GRANT SELECT ON word TO "{www-user}" ;
+ ) {{db.tablespace.search_data}};
+CREATE INDEX idx_word_word_token on word USING BTREE (word_token) {{db.tablespace.search_index}};
+GRANT SELECT ON word TO "{{config.DATABASE_WEBUSER}}" ;
DROP SEQUENCE IF EXISTS seq_word;
CREATE SEQUENCE seq_word start 1;
place_id BIGINT,
country_code varchar(2),
geometry GEOMETRY(Geometry, 4326)
- ) {ts:address-data};
-CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {ts:address-index};
+ ) {{db.tablespace.address_data}};
+CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {{db.tablespace.address_index}};
drop table IF EXISTS location_property CASCADE;
CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
-GRANT SELECT ON location_property_aux TO "{www-user}";
+GRANT SELECT ON location_property_aux TO "{{config.DATABASE_WEBUSER}}";
CREATE TABLE location_property_tiger (
place_id BIGINT,
linegeo GEOMETRY,
interpolationtype TEXT,
postcode TEXT);
-GRANT SELECT ON location_property_tiger TO "{www-user}";
+GRANT SELECT ON location_property_tiger TO "{{config.DATABASE_WEBUSER}}";
drop table if exists location_property_osmline;
CREATE TABLE location_property_osmline (
address HSTORE,
postcode TEXT,
country_code VARCHAR(2)
- ){ts:search-data};
-CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {ts:search-index};
-CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {ts:address-index};
-CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {ts:search-index};
-GRANT SELECT ON location_property_osmline TO "{www-user}";
+ ){{db.tablespace.search_data}};
+CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {{db.tablespace.search_index}};
+CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {{db.tablespace.address_index}};
+CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {{db.tablespace.search_index}};
+GRANT SELECT ON location_property_osmline TO "{{config.DATABASE_WEBUSER}}";
drop table IF EXISTS search_name;
+{% if not db.reverse_only %}
CREATE TABLE search_name (
place_id BIGINT,
importance FLOAT,
nameaddress_vector integer[],
country_code varchar(2),
centroid GEOMETRY(Geometry, 4326)
- ) {ts:search-data};
-CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {ts:search-index};
+ ) {{db.tablespace.search_data}};
+CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {{db.tablespace.search_index}};
+GRANT SELECT ON search_name to "{{config.DATABASE_WEBUSER}}" ;
+{% endif %}
drop table IF EXISTS place_addressline;
CREATE TABLE place_addressline (
cached_rank_address SMALLINT,
fromarea boolean,
isaddress boolean
- ) {ts:search-data};
-CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {ts:search-index};
+ ) {{db.tablespace.search_data}};
+CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {{db.tablespace.search_index}};
drop table if exists placex;
CREATE TABLE placex (
housenumber TEXT,
postcode TEXT,
centroid GEOMETRY(Geometry, 4326)
- ) {ts:search-data};
-CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {ts:search-index};
-CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {ts:search-index};
-CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {ts:address-index} WHERE linked_place_id IS NOT NULL;
-CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {ts:address-index};
-CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {ts:search-index};
-CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {ts:address-index} WHERE osm_type='N' and rank_search < 26;
-CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {ts:address-index} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
+ ) {{db.tablespace.search_data}};
+CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {{db.tablespace.search_index}};
+CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {{db.tablespace.search_index}};
+CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {{db.tablespace.address_index}} WHERE linked_place_id IS NOT NULL;
+CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
+CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
+CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {{db.tablespace.address_index}} WHERE osm_type='N' and rank_search < 26;
+CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {{db.tablespace.address_index}} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
DROP SEQUENCE IF EXISTS seq_place;
CREATE SEQUENCE seq_place start 1;
-GRANT SELECT on placex to "{www-user}" ;
-GRANT SELECT ON search_name to "{www-user}" ;
-GRANT SELECT on place_addressline to "{www-user}" ;
-GRANT SELECT ON seq_word to "{www-user}" ;
-GRANT SELECT ON planet_osm_ways to "{www-user}" ;
-GRANT SELECT ON planet_osm_rels to "{www-user}" ;
-GRANT SELECT on location_area to "{www-user}" ;
+GRANT SELECT on placex to "{{config.DATABASE_WEBUSER}}" ;
+GRANT SELECT on place_addressline to "{{config.DATABASE_WEBUSER}}" ;
+GRANT SELECT ON seq_word to "{{config.DATABASE_WEBUSER}}" ;
+GRANT SELECT ON planet_osm_ways to "{{config.DATABASE_WEBUSER}}" ;
+GRANT SELECT ON planet_osm_rels to "{{config.DATABASE_WEBUSER}}" ;
+GRANT SELECT on location_area to "{{config.DATABASE_WEBUSER}}" ;
-- Table for synthetic postcodes.
DROP TABLE IF EXISTS location_postcode;
postcode TEXT,
geometry GEOMETRY(Geometry, 4326)
);
-CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index};
-GRANT SELECT ON location_postcode TO "{www-user}" ;
+CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {{db.tablespace.address_index}};
+GRANT SELECT ON location_postcode TO "{{config.DATABASE_WEBUSER}}" ;
DROP TABLE IF EXISTS import_polygon_error;
CREATE TABLE import_polygon_error (
newgeometry GEOMETRY(Geometry, 4326)
);
CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
-GRANT SELECT ON import_polygon_error TO "{www-user}";
+GRANT SELECT ON import_polygon_error TO "{{config.DATABASE_WEBUSER}}";
DROP TABLE IF EXISTS import_polygon_delete;
CREATE TABLE import_polygon_delete (
type TEXT NOT NULL
);
CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
-GRANT SELECT ON import_polygon_delete TO "{www-user}";
+GRANT SELECT ON import_polygon_delete TO "{{config.DATABASE_WEBUSER}}";
DROP SEQUENCE IF EXISTS file;
CREATE SEQUENCE file start 1;
drop=args.no_updates,
ignore_errors=args.ignore_errors)
- LOG.warning('Create functions (1st pass)')
with connect(args.config.get_libpq_dsn()) as conn:
+ LOG.warning('Create functions (1st pass)')
refresh.create_functions(conn, args.config, args.sqllib_dir,
False, False)
-
- LOG.warning('Create tables')
- params = ['setup.php', '--create-tables', '--create-partition-tables']
- if args.reverse_only:
- params.append('--reverse-only')
- run_legacy_script(*params, nominatim_env=args,
- throw_on_fail=not args.ignore_errors)
-
- LOG.warning('Create functions (2nd pass)')
- with connect(args.config.get_libpq_dsn()) as conn:
+ LOG.warning('Create tables')
+ database_import.create_tables(conn, args.config, args.sqllib_dir,
+ reverse_only=args.reverse_only)
+ refresh.load_address_levels_from_file(conn, Path(args.config.ADDRESS_LEVEL_CONFIG))
+ LOG.warning('Create functions (2nd pass)')
+ refresh.create_functions(conn, args.config, args.sqllib_dir,
+ False, False)
+ LOG.warning('Create table triggers')
+ database_import.create_table_triggers(conn, args.config, args.sqllib_dir)
+ LOG.warning('Create partition tables')
+ database_import.create_partition_tables(conn, args.config, args.sqllib_dir)
+ LOG.warning('Create functions (3rd pass)')
refresh.create_functions(conn, args.config, args.sqllib_dir,
False, False)
help='Import a osm file')
group.add_argument('--load-data', action='store_true',
help='Copy data to live tables from import table')
+ group.add_argument('--create-tables', action='store_true',
+ help='Create main tables')
+ group.add_argument('--create-partition-tables', action='store_true',
+ help='Create required partition tables')
group.add_argument('--index', action='store_true',
help='Index the data')
group = parser.add_argument_group('Options')
help='Do not perform analyse operations during index')
group.add_argument('--ignore-errors', action='store_true',
help="Ignore certain erros on import.")
+ group.add_argument('--reverse-only', action='store_true',
+ help='Do not create search tables and indexes')
@staticmethod
def run(args):
from ..tools import database_import
+ from ..tools import refresh
if args.create_db:
LOG.warning('Create DB')
drop=args.drop,
ignore_errors=args.ignore_errors)
+ if args.create_tables:
+ LOG.warning('Create Tables')
+ with connect(args.config.get_libpq_dsn()) as conn:
+ database_import.create_tables(conn, args.config, args.sqllib_dir, args.reverse_only)
+ refresh.load_address_levels_from_file(conn, Path(args.config.ADDRESS_LEVEL_CONFIG))
+ refresh.create_functions(conn, args.config, args.sqllib_dir,
+ enable_diff_updates=False)
+ database_import.create_table_triggers(conn, args.config, args.sqllib_dir)
+
+ if args.create_partition_tables:
+ LOG.warning('Create Partition Tables')
+ with connect(args.config.get_libpq_dsn()) as conn:
+ database_import.create_partition_tables(conn, args.config, args.sqllib_dir)
+
if args.load_data:
LOG.warning('Load data')
with connect(args.config.get_libpq_dsn()) as conn:
db_info['tables'] = _get_tables(conn)
db_info['reverse_only'] = 'search_name' not in db_info['tables']
+ db_info['tablespace'] = {}
+ for subset in ('ADDRESS', 'SEARCH', 'AUX'):
+ for kind in ('DATA', 'INDEX'):
+ tspace = getattr(config, 'TABLESPACE_{}_{}'.format(subset, kind))
+ if tspace:
+ tspace = 'TABLESPACE "{}"'.format(tspace)
+ db_info['tablespace']['{}_{}'.format(subset.lower, kind.lower())] = tspace
+
self.env.globals['config'] = config
self.env.globals['db'] = db_info
self.env.globals['modulepath'] = config.DATABASE_MODULE_PATH or \
from ..db.connection import connect, get_pg_env
from ..db import utils as db_utils
from ..db.async_connection import DBConnection
+from ..db.sql_preprocessor import SQLPreprocessor
from .exec_utils import run_osm2pgsql
from ..errors import UsageError
from ..version import POSTGRESQL_REQUIRED_VERSION, POSTGIS_REQUIRED_VERSION
Path(options['flatnode_file']).unlink()
+def create_tables(conn, config, sqllib_dir, reverse_only=False):
+ """ Create the set of basic tables.
+ When `reverse_only` is True, then the main table for searching will
+ be skipped and only reverse search is possible.
+ """
+ sql = SQLPreprocessor(conn, config, sqllib_dir)
+ sql.env.globals['db']['reverse_only'] = reverse_only
+
+ sql.run_sql_file(conn, 'tables.sql')
+
+
+def create_table_triggers(conn, config, sqllib_dir):
+ """ Create the triggers for the tables. The trigger functions must already
+ have been imported with refresh.create_functions().
+ """
+ sql = SQLPreprocessor(conn, config, sqllib_dir)
+ sql.run_sql_file(conn, 'table-triggers.sql')
+
+
+def create_partition_tables(conn, config, sqllib_dir):
+ """ Create tables that have explicit partioning.
+ """
+ sql = SQLPreprocessor(conn, config, sqllib_dir)
+ sql.run_sql_file(conn, 'partition-tables.src.sql')
+
+
def truncate_data_tables(conn, max_word_frequency=None):
""" Truncate all data tables to prepare for a fresh load.
"""
mock_func_factory(nominatim.tools.refresh, 'import_wikipedia_articles'),
mock_func_factory(nominatim.tools.database_import, 'truncate_data_tables'),
mock_func_factory(nominatim.tools.database_import, 'load_data'),
+ mock_func_factory(nominatim.tools.database_import, 'create_tables'),
+ mock_func_factory(nominatim.tools.database_import, 'create_table_triggers'),
+ mock_func_factory(nominatim.tools.database_import, 'create_partition_tables'),
+ mock_func_factory(nominatim.tools.refresh, 'load_address_levels_from_file'),
mock_func_factory(nominatim.indexer.indexer.Indexer, 'index_full'),
mock_func_factory(nominatim.tools.refresh, 'setup_website'),
mock_func_factory(nominatim.db.properties, 'set_property')
projects / nominatim.git / commitdiff