def _create_temporary_user_for_dump():
query = """
CREATE ROLE brian LOGIN PASSWORD 'brian';
GRANT ALL PRIVILEGES ON DATABASE {database} to brian;
""".format(database=settings.get("DB_NAME"))
exec_sql(query, user="******")
def _alter_wikipedia_dump_owner():
query = """
ALTER TABLE wikipedia_article OWNER TO {username};
ALTER TABLE wikipedia_redirect OWNER TO {username};
""".format(username=settings.get("DB_USER"))
exec_sql(query, user="******")
def session(engine):
session = Session(engine)
yield session
session.close()
exec_sql("SELECT truncate_tables('osm_test')")
def session(engine):
session = Session(engine)
yield session
session.close()
exec_sql("SELECT truncate_tables('osm_test')")
def create_database():
create_user_query = "CREATE USER {} WITH PASSWORD '{}';".format(
settings.get("DB_USER"), settings.get("DB_PASSWORD"))
create_database_query = "CREATE DATABASE {} WITH TEMPLATE template_postgis OWNER {};".format(
settings.get("DB_NAME"), settings.get("DB_USER"))
exec_sql(create_user_query, user="******", database="postgres")
exec_sql(create_database_query, user="******", database="postgres")
def create_database():
create_user_query = "CREATE USER {} WITH PASSWORD '{}';".format(settings.get("DB_USER"),
settings.get("DB_PASSWORD"))
create_database_query = "CREATE DATABASE {} WITH TEMPLATE template_postgis OWNER {};".format(
settings.get("DB_NAME"),
settings.get("DB_USER")
)
exec_sql(create_user_query, user="******", database="postgres")
exec_sql(create_database_query, user="******", database="postgres")
def _recreate_database():
print("drop database")
drop_database_query = "DROP DATABASE IF EXISTS {};".format(
settings.get("DB_NAME"))
drop_user_query = "DROP USER IF EXISTS {};".format(settings.get("DB_USER"))
exec_sql(drop_database_query, user="******", database="postgres")
exec_sql(drop_user_query, user="******", database="postgres")
print("create database")
init_database()
def _create_temporary_user_for_dump():
query = """
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'brian') THEN
CREATE ROLE brian LOGIN PASSWORD 'brian';
END IF;
END
$$;
GRANT ALL PRIVILEGES ON DATABASE {database} to brian;
""".format(database=settings.get("DB_NAME"))
exec_sql(query, user="******")
def _init_and_clear_database():
# creates database if necessary
init_database()
exec_sql("DROP OWNED BY osm_test")
# prepare schema for tests
current_directory = os.path.dirname(os.path.realpath(__file__))
exec_sql_from_file('helpers/schema.sql.dump', cwd=current_directory)
exec_sql_from_file('helpers/functions.sql', cwd=current_directory)
export_osmnames.create_functions()
# necessary for export tests
if not os.path.exists('/tmp/osmnames/export/'):
os.makedirs('/tmp/osmnames/export/')
def _init_and_clear_database():
# creates database if necessary
init_database()
exec_sql("DROP OWNED BY osm_test")
# prepare schema for tests
current_directory = os.path.dirname(os.path.realpath(__file__))
exec_sql_from_file('helpers/schema.sql.dump', cwd=current_directory)
exec_sql_from_file('helpers/functions.sql', cwd=current_directory)
export_osmnames.create_functions()
# necessary for export tests
if not os.path.exists('/tmp/osmnames/export/'):
os.makedirs('/tmp/osmnames/export/')
def create_basic_scaffolding():
query = """
CREATE TABLE wikipedia_article (
language text NOT NULL,
title text NOT NULL,
importance double precision
);
CREATE TABLE wikipedia_redirect (
language text,
from_title text NOT NULL,
to_title text NOT NULL
);
"""
exec_sql(query)
def prepare_wikipedia_redirects():
exec_sql("DROP INDEX idx_wikipedia_redirect_from_title")
exec_sql("""
UPDATE wikipedia_redirect
SET from_title = concat_ws(':', language, from_title),
to_title = concat_ws(':', language, to_title);
""")
exec_sql("CREATE INDEX ON wikipedia_redirect(from_title)")
def create_extensions():
exec_sql("CREATE EXTENSION IF NOT EXISTS hstore;",
user="******",
database="template_postgis")
exec_sql("CREATE EXTENSION IF NOT EXISTS unaccent;",
user="******",
database="template_postgis")
exec_sql("CREATE EXTENSION IF NOT EXISTS pg_trgm;",
user="******",
database="template_postgis")
def disable_notices():
exec_sql("SET client_min_messages TO WARNING;")
def create_wikipedia_index():
exec_sql("CREATE INDEX idx_wikipedia_article_title ON wikipedia_article (title);")
def _alter_wikipedia_dump_owner():
query = """
ALTER TABLE wikipedia_article OWNER TO {username};
""".format(username=settings.get("DB_USER"))
exec_sql(query, user="******")
def drop_parent_polygons():
exec_sql("DROP TABLE parent_polygons CASCADE")
def drop_unused_indexes():
for index in ["osm_linestring_osm_id_idx", "osm_housenumber_osm_id_idx"]:
exec_sql("DROP INDEX IF EXISTS {}".format(index))
def sanatize_for_import():
exec_sql("""DROP TABLE IF EXISTS osm_linestring,
osm_point,
osm_polygon,
osm_housenumber CASCADE""")
def create_geometry_indexes():
exec_sql("""
CREATE INDEX osm_linestring_geometry_center ON osm_linestring USING gist(geometry_center);
CREATE INDEX osm_housenumber_geometry_center ON osm_housenumber USING gist(geometry_center);
CREATE INDEX osm_polygon_geometry ON osm_polygon USING gist(geometry);
""")
def disable_notices():
exec_sql("SET client_min_messages TO WARNING;")
def drop_geometry_center_indexes():
exec_sql("""
DROP INDEX osm_linestring_geometry_center;
DROP INDEX osm_housenumber_geometry_center;
DROP INDEX osm_polygon_geometry;
""")
def set_tables_unlogged():
for table in ["osm_linestring", "osm_point", "osm_polygon", "osm_housenumber"]:
exec_sql("ALTER TABLE {} SET UNLOGGED;".format(table))
def drop_unused_indexes():
for index in ["osm_linestring_osm_id_idx", "osm_housenumber_osm_id_idx"]:
exec_sql("DROP INDEX IF EXISTS {}".format(index))
def set_tables_unlogged():
for table in ["osm_linestring", "osm_point", "osm_polygon", "osm_housenumber"]:
exec_sql("ALTER TABLE {} SET UNLOGGED;".format(table))
def create_indexes():
exec_sql("CREATE INDEX osm_housenumber_street_id ON osm_housenumber(street_id);")
def create_indexes():
exec_sql(
"CREATE INDEX osm_housenumber_street_id ON osm_housenumber(street_id);"
)
def drop_geometry_center_indexes():
exec_sql("""
DROP INDEX osm_linestring_geometry_center;
DROP INDEX osm_polygon_geometry_center;
DROP INDEX osm_housenumber_geometry_center;
""")
def sanatize_for_import():
exec_sql("""DROP TABLE IF EXISTS osm_linestring,
osm_point,
osm_polygon,
osm_housenumber CASCADE""")
def create_extensions():
exec_sql("CREATE EXTENSION IF NOT EXISTS hstore;", user="******", database="template_postgis")
exec_sql("CREATE EXTENSION IF NOT EXISTS unaccent;", user="******", database="template_postgis")
exec_sql("CREATE EXTENSION IF NOT EXISTS pg_trgm;", user="******", database="template_postgis")
