def test_user_not_in_postgres(self):
"""Test if results are transfered for users which are not yet in Postgres."""
pg_db = auth.postgresDB()
# Make sure user and results are not yet in Postgres
sql_query = (f"DELETE FROM results "
f"WHERE user_id = '{self.project_id}' "
f"AND project_id = '{self.project_id}'")
pg_db.query(sql_query)
sql_query = "DELETE FROM users WHERE user_id = '{0}'".format(
self.project_id)
pg_db.query(sql_query)
transfer_results()
sql_query = "SELECT * FROM users WHERE user_id = '{0}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
sql_query = (f"SELECT * "
f"FROM results "
f"WHERE project_id = '{self.project_id}' "
f"AND user_id = '{self.project_id}'")
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
def test_create_footprint_project(self):
runner = CliRunner()
result = runner.invoke(mapswipe_workers.run_create_projects)
if result.exit_code != 0:
raise result.exception
pg_db = auth.postgresDB()
for element in self.project_id:
query = "SELECT project_id FROM projects WHERE project_id = %s"
result = pg_db.retr_query(query, [element])[0][0]
self.assertEqual(result, element)
# check if usernames made it to postgres
if element != "test_footprint_link":
query = """
SELECT count(*)
FROM tasks
WHERE project_id = %s
and project_type_specifics->'properties'->'username' is not null
"""
result = pg_db.retr_query(query, [element])[0][0]
self.assertGreater(result, 0)
fb_db = auth.firebaseDB()
ref = fb_db.reference(f"/v2/projects/{element}")
result = ref.get(shallow=True)
self.assertIsNotNone(result)
ref = fb_db.reference(f"/v2/groups/{element}")
result = ref.get(shallow=True)
self.assertIsNotNone(result)
# Footprint projects have tasks in Firebase
ref = fb_db.reference(f"/v2/tasks/{element}")
result = ref.get(shallow=True)
self.assertIsNotNone(result)
def get_recent_projects(hours: int = 3):
"""Get ids for projects when results have been submitted within the last x hours."""
pg_db = auth.postgresDB()
query_insert_results = """
select project_id
from results
-- Using timestamp attribute here which is set for all projects
-- and also represents the start_time for newer projects.
-- "Old" projects have no start_time attribute.
-- There is an index defined on "timestamp".
where "timestamp" >= %(timestamp)s
group by project_id
"""
timestamp = (dt.datetime.utcnow() -
dt.timedelta(hours=hours)).isoformat()[0:-3] + "Z"
project_info = pg_db.retr_query(query_insert_results,
{"timestamp": timestamp})
project_ids = []
for project_id in project_info:
project_ids.append(project_id[0])
logger.info(
f"Got {len(project_ids)} projects from postgres with recent results.")
return project_ids
def test_deletion(self):
"""Test if tasks, groups, project and results are deleted."""
delete_project.delete_project([self.project_id])
fb_db = auth.firebaseDB()
ref = fb_db.reference("v2/results/{0}".format(self.project_id))
self.assertIsNone(ref.get())
ref = fb_db.reference("v2/tasks/{0}".format(self.project_id))
self.assertIsNone(ref.get())
ref = fb_db.reference("v2/groups/{0}".format(self.project_id))
self.assertIsNone(ref.get())
ref = fb_db.reference("v2/projects/{0}".format(self.project_id))
self.assertIsNone(ref.get())
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM tasks WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
sql_query = "SELECT * FROM groups WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
sql_query = "SELECT * FROM projects WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
sql_query = "SELECT * FROM results WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
def save_results_to_postgres(results_file):
"""
Saves results to a temporary table in postgres using the COPY Statement of Postgres
for a more efficient import into the database.
Parameters
----------
results_file: io.StringIO
"""
p_con = auth.postgresDB()
columns = [
"project_id",
"group_id",
"user_id",
"task_id",
"timestamp",
"start_time",
"end_time",
"result",
]
p_con.copy_from(results_file, "results_temp", columns)
results_file.close()
query_insert_results = """
INSERT INTO results
SELECT * FROM results_temp
ON CONFLICT (project_id,group_id,user_id,task_id)
DO NOTHING;
"""
p_con.query(query_insert_results)
del p_con
def update_groups_table(project_id: str):
"""Remove duplicates in 'project_types_specifics' attribute in groups table."""
logger.info(f"Start process for project: '{project_id}'")
p_con = auth.postgresDB()
query = """
UPDATE groups
SET project_type_specifics = project_type_specifics::jsonb
#- '{projectId}'
#- '{id}'
#- '{requiredCount}'
#- '{finishedCount}'
#- '{neededCount}'
#- '{reportCount}'
#- '{distributedCount}'
WHERE project_id = %(project_id)s
"""
try:
p_con.query(query, {"project_id": project_id})
logger.info(f"Updated tasks table for project '{project_id}'.")
except Exception as e:
sentry.capture_exception(e)
sentry.capture_message(
f"Could NOT update tasks table for project '{project_id}'.")
logger.exception(e)
logger.warning(
f"Could NOT update tasks table for project '{project_id}'.")
def test_no_users_in_postgres(self):
"""Test update users when no users are in postgres yet."""
update_data.update_user_data()
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM users WHERE user_id = ANY( %(user_ids)s )"
result = pg_db.retr_query(sql_query, {"user_ids": self.user_ids})
self.assertEqual(len(result), self.num_users)
def test_project_id_equals_none(self):
"""Test for project id which does not exists."""
delete_project.delete_project([None])
time.sleep(5) # Wait for Firebase Functions to complete
fb_db = auth.firebaseDB()
ref = fb_db.reference("v2/results")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/tasks")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/groups")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/groupsUsers")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/projects")
self.assertIsNotNone(ref.get(shallow=True))
pg_db = auth.postgresDB()
sql_query = f"SELECT * FROM tasks WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
sql_query = f"SELECT * FROM groups WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
sql_query = f"SELECT * FROM projects WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
sql_query = f"SELECT * FROM results WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
def test_project_id_equals_none(self):
"""Test for project id which does not exists."""
delete_project.delete_project([None])
fb_db = auth.firebaseDB()
ref = fb_db.reference("v2/results")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/tasks")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/groups")
self.assertIsNotNone(ref.get(shallow=True))
ref = fb_db.reference("v2/projects")
self.assertIsNotNone(ref.get(shallow=True))
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM tasks WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
sql_query = "SELECT * FROM groups WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
sql_query = "SELECT * FROM projects WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
sql_query = "SELECT * FROM results WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertNotEqual(result, [])
def test_with_user_ids(self):
update_data.update_user_data([self.user_id])
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM users WHERE user_id = '{0}'".format(
self.user_id)
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
def test_deletion(self):
"""Test if tasks, groups, project and results are deleted."""
delete_project.delete_project([self.project_id])
time.sleep(1) # Wait for Firebase Functions to complete
fb_db = auth.firebaseDB()
ref = fb_db.reference(f"v2/results/{self.project_id}")
self.assertIsNone(ref.get())
ref = fb_db.reference(f"v2/tasks/{self.project_id}")
self.assertIsNone(ref.get())
ref = fb_db.reference(f"v2/groups/{self.project_id}")
self.assertIsNone(ref.get())
ref = fb_db.reference(f"v2/groupsUsers/{self.project_id}")
self.assertIsNone(ref.get())
ref = fb_db.reference(f"v2/projects/{self.project_id}")
self.assertIsNone(ref.get())
pg_db = auth.postgresDB()
sql_query = f"SELECT * FROM tasks WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
sql_query = f"SELECT * FROM groups WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
sql_query = f"SELECT * FROM projects WHERE project_id = '{self.project_id}'"
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
sql_query = "SELECT * FROM results WHERE project_id = '{}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertEqual(result, [])
def add_project_geometries_to_api():
"""Load project geometries from postgres and save as geojson."""
# load from postgres
pg_db = auth.postgresDB()
sql_query = """
SELECT
project_id
,ST_AsText(geom) as geom
FROM projects
"""
data = pg_db.retr_query(sql_query)
print(len(data))
# save as geojson one by one
for project in data:
project_id = project[0]
wkt_geom = project[1]
outfile = (
f"{DATA_PATH}/api/project_geometries/project_geom_{project_id}.geojson"
)
try:
geometries = [ogr.CreateGeometryFromWkt(wkt_geom)]
geojson_functions.create_geojson_file(geometries, outfile)
except Exception:
print(f"got an error for {project_id}")
# just ignore if this fails
pass
def test_no_users_in_postgres(self):
"""Test update users when no users are in postgres yet."""
update_data.update_user_data()
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM users WHERE user_id = '{0}'".format(
self.user_id)
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
def update_user_data(user_ids: list = []) -> None:
"""Copies new users from Firebase to Postgres."""
# TODO: On Conflict
fb_db = auth.firebaseDB()
pg_db = auth.postgresDB()
ref = fb_db.reference("v2/users")
last_updated = get_last_updated_timestamp()
if user_ids:
logger.info("Add custom user ids to user list, which will be updated.")
users = {}
for user_id in user_ids:
users[user_id] = ref.child(user_id).get()
elif last_updated:
# Get only new users from Firebase.
query = ref.order_by_child("created").start_at(last_updated)
users = query.get()
if len(users) == 0:
logger.info("there are no new users in Firebase.")
else:
# Delete first user in ordered dict (FIFO).
# This user is already in the database (user.created = last_updated).
users.popitem(last=False)
else:
# Get all users from Firebase.
users = ref.get()
for user_id, user in users.items():
# Convert timestamp (ISO 8601) from string to a datetime object.
try:
created = dt.datetime.strptime(
user["created"].replace("Z", ""), "%Y-%m-%dT%H:%M:%S.%f"
)
except KeyError:
# If user has no "created" attribute set it to current time.
created = dt.datetime.utcnow().isoformat()[0:-3] + "Z"
logger.info(
"user {0} didn't have a 'created' attribute. ".format(user_id)
+ "Set it to '{0}' now.".format(created)
)
username = user.get("username", None)
query_update_user = """
INSERT INTO users (user_id, username, created)
VALUES(%s, %s, %s)
ON CONFLICT (user_id) DO UPDATE
SET username=%s,
created=%s;
"""
data_update_user = [
user_id,
username,
created,
username,
created,
]
pg_db.query(query_update_user, data_update_user)
logger.info("Updated user data in Potgres.")
def write_sql_to_csv(filename: str, sql_query: sql.SQL):
"""
Use the copy statement to write data from postgres to a csv file.
"""
pg_db = auth.postgresDB()
with open(filename, "w") as f:
pg_db.copy_expert(sql_query, f)
logger.info(f"wrote csv file from sql: {filename}")
def truncate_temp_results():
p_con = auth.postgresDB()
query_truncate_temp_results = """
TRUNCATE results_temp
"""
p_con.query(query_truncate_temp_results)
del p_con
return
def set_postgres_test_data(project_type: str, data_type: str,
fixture_name: str) -> None:
test_dir = os.path.dirname(__file__)
fixture_name = fixture_name + ".csv"
file_path = os.path.join(test_dir, "fixtures", project_type, data_type,
fixture_name)
pg_db = auth.postgresDB()
with open(file_path) as test_file:
pg_db.copy_from(test_file, data_type)
def test_last_updated_users(self):
"""Test update users when some users are in postgres."""
update_data.update_user_data()
user_id = set_up.create_test_user("tile_map_service_grid",
"test_user_2")
self.user_ids.append(user_id)
update_data.update_user_data()
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM users WHERE user_id = '{0}'".format(user_id)
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
def delete_test_user(user_ids: List) -> None:
for user_id in user_ids:
if not re.match(r"[-a-zA-Z0-9]+", user_id):
raise ValueError(f"Given argument resulted in invalid "
f"Firebase Realtime Database reference. ")
fb_db = auth.firebaseDB()
ref = fb_db.reference("v2/users/{0}".format(user_id))
ref.delete()
pg_db = auth.postgresDB()
sql_query = "DELETE FROM users WHERE user_id = '{0}'".format(user_id)
pg_db.query(sql_query)
def test_changes(self):
"""Test if results are deleted from Firebase for given project id."""
transfer_results.transfer_results()
fb_db = auth.firebaseDB()
ref = fb_db.reference("v2/results/{0}".format(self.project_id))
self.assertIsNone(ref.get())
pg_db = auth.postgresDB()
sql_query = "SELECT * FROM results WHERE project_id = '{0}' and user_id = '{0}'".format(
self.project_id)
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
def run_vacuum_groups_table():
"""Run vacuum to reclaim storage."""
logger.info("Start vacuum on groups table.")
p_con = auth.postgresDB()
# isolation_level 0 will move you out of a transaction block
old_isolation_level = p_con._db_connection.isolation_level
p_con._db_connection.set_isolation_level(0)
query = """
VACUUM groups
"""
p_con.query(query)
# set isolation_level back to initial value
p_con._db_connection.set_isolation_level(old_isolation_level)
logger.info("Finish vacuum on groups table.")
def test_create_project(self):
runner = CliRunner()
runner.invoke(mapswipe_workers.run_create_projects)
pg_db = auth.postgresDB()
query = "SELECT * FROM projects WHERE project_id = '{0}'".format(
self.project_id)
result = pg_db.retr_query(query)
self.assertIsNone(result)
fb_db = auth.firebaseDB()
ref = fb_db.reference("/v2/projects/{0}".format(self.project_id))
result = ref.get()
self.assertIsNone(result)
def get_project_ids_from_postgres():
"""Get project ids."""
p_con = auth.postgresDB()
query = """
SELECT project_id FROM projects;
"""
data = p_con.retr_query(query)
project_ids = [item[0] for item in data]
logger.info("Got projects from postgres.")
logger.info(project_ids)
return project_ids
def get_projects_from_postgres():
"""
Get the id of all projects in postgres
"""
pg_db = auth.postgresDB()
sql_query = """
SELECT project_id from projects;
"""
raw_ids = pg_db.retr_query(sql_query, None)
project_ids = [i[0] for i in raw_ids]
del pg_db
return project_ids
def delete_test_data(project_id: str) -> None:
"""
Delete test project indluding groups, tasks and results
from Firebase and Postgres
"""
if not re.match(r"[-a-zA-Z0-9]+", project_id):
raise ValueError(
"Given argument resulted in invalid Firebase Realtime Database reference. "
)
fb_db = auth.firebaseDB()
ref = fb_db.reference(f"v2/results/{project_id}")
ref.delete()
ref = fb_db.reference(f"v2/tasks/{project_id}")
ref.delete()
ref = fb_db.reference(f"v2/groupsUsers/{project_id}")
ref.delete()
time.sleep(1) # Wait for Firebase Functions to complete
ref = fb_db.reference(f"v2/groups/{project_id}")
ref.delete()
ref = fb_db.reference(f"v2/projects/{project_id}")
ref.delete()
ref = fb_db.reference(f"v2/projectDrafts/{project_id}")
ref.delete()
ref = fb_db.reference(f"v2/users/{project_id}")
ref.delete()
pg_db = auth.postgresDB()
sql_query = "DELETE FROM results WHERE project_id = %s"
pg_db.query(sql_query, [project_id])
sql_query = "DELETE FROM results_temp WHERE project_id = %s"
pg_db.query(sql_query, [project_id])
sql_query = "DELETE FROM tasks WHERE project_id = %s"
pg_db.query(sql_query, [project_id])
sql_query = "DELETE FROM groups WHERE project_id = %s"
pg_db.query(sql_query, [project_id])
sql_query = "DELETE FROM projects WHERE project_id = %s"
pg_db.query(sql_query, [project_id])
sql_query = "DELETE FROM users WHERE user_id = 'test_build_area'"
pg_db.query(sql_query)
sql_query = "DELETE FROM users_temp WHERE user_id = 'test_build_area'"
pg_db.query(sql_query)
sql_query = "DELETE FROM users WHERE user_id = 'test_build_area_heidelberg'"
pg_db.query(sql_query)
sql_query = "DELETE FROM users_temp WHERE user_id = 'test_build_area_heidelberg'"
pg_db.query(sql_query)
def delete_test_user(user_ids: List) -> None:
for user_id in user_ids:
if not re.match(r"[-a-zA-Z0-9]+", user_id):
raise ValueError("Given argument resulted in invalid "
"Firebase Realtime Database reference. ")
fb_db = auth.firebaseDB()
ref = fb_db.reference(f"v2/users/{user_id}")
ref.delete()
pg_db = auth.postgresDB()
sql_query = "DELETE FROM users WHERE user_id = ANY( %(user_ids)s );"
pg_db.query(sql_query, {"user_ids": user_ids})
sql_query = "DELETE FROM users_temp WHERE user_id = ANY( %(user_ids)s );"
pg_db.query(sql_query, {"user_ids": user_ids})
def test_changes_given_project_id(self):
"""Test if results are deleted from Firebase for given project id."""
transfer_results(project_id_list=[self.project_id])
fb_db = auth.firebaseDB()
ref = fb_db.reference("v2/results/{0}".format(self.project_id))
self.assertIsNone(ref.get())
pg_db = auth.postgresDB()
sql_query = (f"SELECT * "
f"FROM results "
f"WHERE project_id = '{self.project_id}' "
f"AND user_id = '{self.project_id}'")
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
def get_project_static_info(filename: str) -> pd.DataFrame:
"""
The function queries the projects table.
Each row represents a single project and provides the information which is static.
By static we understand all attributes which are not affected by new results being
contributed.
The results are stored in a csv file and also returned as a pandas DataFrame.
Parameters
----------
filename: str
"""
pg_db = auth.postgresDB()
# make sure to replace newline characters here
sql_query = """
COPY (
SELECT
project_id
,regexp_replace(name, E'[\\n\\r]+', ' ', 'g' ) as name
,regexp_replace(project_details, E'[\\n\\r]+', ' ', 'g' ) as
project_details
,regexp_replace(look_for, E'[\\n\\r]+', ' ', 'g' ) as look_for
,project_type
-- add an array of the tile server names
,CASE
WHEN project_type_specifics->'tileServer'->'name' IS NOT NULL THEN
Array[project_type_specifics->'tileServer'->>'name']
ELSE Array[project_type_specifics->'tileServerA'->>'name',
project_type_specifics->'tileServerB'->>'name']
END as tile_server_names
,regexp_replace(status, E'[\\n\\r]+', ' ', 'g' ) as status
,ST_Area(geom::geography)/1000000 as area_sqkm
,ST_AsText(geom) as geom
,ST_AsText(ST_Centroid(geom)) as centroid
FROM projects
) TO STDOUT WITH CSV HEADER"""
with open(filename, "w") as f:
pg_db.copy_expert(sql_query, f)
del pg_db
logger.info("got projects from postgres.")
df = pd.read_csv(filename)
return df
def test_additional_results_for_same_group_are_not_deleted(self):
"""Test if new results are not deleted when workflow has already started.
Here we check what happens to results that are submitted,
when the transfer of results workflow is already running.
We expect that these new results should remain in Firebase
until the next run of transfer results.
"""
fb_db = auth.firebaseDB()
results_ref = fb_db.reference(f"v2/results/{self.project_id}")
results = results_ref.get()
# Add additional results in Firebase for group with key 'g115'.
# This is the same group for which results already exist.
# So here we simulate another user mapping a group
# that has been mapped before already.
# These results should not get deleted by the function below.
test_dir = os.path.dirname(__file__)
fixture_name = "build_area_additional_results_same_group.json"
file_path = os.path.join(test_dir, "fixtures", "tile_map_service_grid",
"results", fixture_name)
with open(file_path) as test_file:
new_results = json.load(test_file)
results_ref = fb_db.reference(
f"v2/results/{self.project_id}/g115/new_user/")
results_ref.set(new_results)
# run transfer results function
transfer_results_for_project(self.project_id, results)
# Check if results have been transferred
pg_db = auth.postgresDB()
sql_query = (f"SELECT * "
f"FROM results "
f"WHERE project_id = '{self.project_id}' "
f"AND user_id = '{self.project_id}'")
result = pg_db.retr_query(sql_query)
self.assertIsNotNone(result)
# check if new results for the group 'g115' are still in Firebase
ref = fb_db.reference(
f"v2/results/{self.project_id}/g115/new_user/results")
self.assertEqual(len(ref.get(shallow=True)), 252)
def write_sql_to_gzipped_csv(filename: str, sql_query: sql.SQL):
"""
Use the copy statement to write data from postgres to a csv file.
"""
# generate temporary file which will be automatically deleted at the end
tmp_csv_file = os.path.join(tempfile._get_default_tempdir(), "tmp.csv")
pg_db = auth.postgresDB()
with open(tmp_csv_file, "w") as f:
pg_db.copy_expert(sql_query, f)
normalize_project_type_specifics(tmp_csv_file)
with open(tmp_csv_file, "rb") as f_in, gzip.open(filename, "wb") as f_out:
f_out.writelines(f_in)
logger.info(f"wrote gzipped csv file from sql: {filename}")
