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 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 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 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 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; """)