def create_or_update_active_job( self, job_in: schemas.JobCreate) -> Optional[schemas.Job]: """This method ensures that an active Job as described by JobCreate exists. If the Job does not exist it will be created. If a Job which can be updated exists (e.g. query is the same but perhaps category is different) that job will be updated. The created or updated job will be activated. Note that for finding a job candidate to update, only active jobs are considered - an inactive job will never be updated and used, instead a new version would be created. If an update was made, a Job object is returned, otherwise None""" job = self.get_job(job_name=job_in.name) if job: if self._job_job_create_identical(job, job_in): return None if self._job_job_create_nonupdatable_fields_identical(job, job_in): job_update = schemas.JobUpdate.from_job_create(job_in) return self.update_job(job_name=job.name, job_created=job.created, job_in=job_update) job = self.create_job(job_in) job_update = schemas.JobUpdate(active=True) return self.update_job(job_name=job.name, job_created=job.created, job_in=job_update) job = self.create_job(job_in) job_update = schemas.JobUpdate(active=True) return self.update_job(job_name=job.name, job_created=job.created, job_in=job_update)
def test_get_active_job(self): db_ro_user = "******" job_crud = CRUDJob( db_ro_user=db_ro_user, result_expiration_sec_default=int(1e6), result_expiration_sec_limit=int(1e6), max_graph_age_sec_default=int(1e6), max_graph_age_sec_limit=int(1e6), max_result_age_sec_default=int(1e6), max_result_age_sec_limit=int(1e6), account_id_key="test_account_id", ) with temp_db_session() as session: session.execute(f"CREATE ROLE {db_ro_user}") # job which will be activated job_create = schemas.JobCreate( name="test_job", description="A Test Job", graph_spec=schemas.JobGraphSpec(graph_names=["1", "2"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?s ?p ?test_account_id where {?s ?p ?test_account_id} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), ) created_timestamp = job_crud.create( db_session=session, job_create_in=job_create).created # dummy inactive job another_job_create = schemas.JobCreate( name="test_job", description="A Test Job", graph_spec=schemas.JobGraphSpec(graph_names=["a", "b"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?q ?r ?test_account_id where {?q ?r ?test_account_id} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), ) job_crud.create(db_session=session, job_create_in=another_job_create).created # activate job_update = schemas.JobUpdate(active=True) _activated_job = job_crud.update_version( db_session=session, job_name="test_job", created=created_timestamp, job_update=job_update, ) activated_job = schemas.Job.from_orm(_activated_job) _job = job_crud.get_active(session, "test_job") job = schemas.Job.from_orm(_job) self.assertEqual(activated_job, job)
def test_views_with_two_fresh_result_sets_one_missing_an_account_other_expired( self): """Add two result sets, one of which are within max_result_age_sec and the other is expired. The newer is missing one account. Run a query against the latest view to validate we get data for only one account (from the unexpired set)""" db_ro_user = "******" job_crud = CRUDJob( db_ro_user=db_ro_user, result_expiration_sec_default=int(1e6), result_expiration_sec_limit=int(1e6), max_graph_age_sec_default=int(1e6), max_graph_age_sec_limit=int(1e6), max_result_age_sec_default=int(1e6), max_result_age_sec_limit=int(1e6), account_id_key="test_account_id", ) result_set_crud = CRUDResultSet( max_result_set_results=int(1e6), max_result_size_bytes=int(1e6), job_crud=job_crud, ) with temp_db_session() as session: session.execute(f"CREATE ROLE {db_ro_user}") job_create = schemas.JobCreate( name="test_job", description="A Test Job", graph_spec=schemas.JobGraphSpec(graph_names=["test"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?test_account_id ?foo ?boo where {?test_account_id ?foo ?boo} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) created_timestamp = job_crud.create( db_session=session, job_create_in=job_create).created # activate job_update = schemas.JobUpdate(active=True) _job = job_crud.update_version( db_session=session, job_name="test_job", created=created_timestamp, job_update=job_update, ) job = schemas.Job.from_orm(_job) account_id_a = "012345678901" account_id_b = "567890123456" result_set_1_time = datetime.now() - timedelta( seconds=job_create.max_result_age_sec + 1) result_set_1_graph_spec = schemas.ResultSetGraphSpec( graph_uris_load_times={"test": result_set_1_time.timestamp()}) results_1 = [ schemas.Result( account_id=account_id_a, result={ "foo": "oldhello_a", "boo": "oldthere_a" }, ), schemas.Result( account_id=account_id_a, result={ "foo": "oldboo_a", "boo": "oldfoo_a" }, ), schemas.Result( account_id=account_id_b, result={ "foo": "oldhello_b", "boo": "oldthere_b" }, ), schemas.Result( account_id=account_id_b, result={ "foo": "oldboo_b", "boo": "oldfoo_b" }, ), ] result_set_1_create = ResultSetCreate( job=job, graph_spec=result_set_1_graph_spec, results=results_1, created=result_set_1_time, ) result_set_crud.create(db_session=session, obj_in=result_set_1_create) result_set_2_time = datetime.now() result_set_2_graph_spec = schemas.ResultSetGraphSpec( graph_uris_load_times={"test": result_set_2_time.timestamp()}) results_2 = [ schemas.Result( account_id=account_id_a, result={ "foo": "newhello_a", "boo": "newthere_a" }, ), schemas.Result( account_id=account_id_a, result={ "foo": "newboo_a", "boo": "newfoo_a" }, ), ] result_set_2_create = ResultSetCreate( job=job, graph_spec=result_set_2_graph_spec, results=results_2, created=result_set_2_time, ) result_set_crud.create(db_session=session, obj_in=result_set_2_create) # check latest results latest_results = session.execute("select * from test_job_latest") latest_rows = latest_results.fetchall() self.assertSequenceEqual( sorted(latest_rows), sorted([ (result_set_2_time, account_id_a, "newhello_a", "newthere_a"), (result_set_2_time, account_id_a, "newboo_a", "newfoo_a"), ]), ) # check all results all_results = session.execute("select * from test_job_all") all_rows = all_results.fetchall() self.assertSequenceEqual( sorted(all_rows), sorted([ (result_set_1_time, account_id_a, "oldhello_a", "oldthere_a"), (result_set_1_time, account_id_a, "oldboo_a", "oldfoo_a"), (result_set_1_time, account_id_b, "oldhello_b", "oldthere_b"), (result_set_1_time, account_id_b, "oldboo_b", "oldfoo_b"), (result_set_2_time, account_id_a, "newhello_a", "newthere_a"), (result_set_2_time, account_id_a, "newboo_a", "newfoo_a"), ]), )
def test_views_with_expired_result_set(self): """Add a single result set which is older than max_result_age_sec. Validate the latest view returns no results, also validate all_view""" db_ro_user = "******" job_crud = CRUDJob( db_ro_user=db_ro_user, result_expiration_sec_default=int(1e6), result_expiration_sec_limit=int(1e6), max_graph_age_sec_default=int(1e6), max_graph_age_sec_limit=int(1e6), max_result_age_sec_default=int(1e6), max_result_age_sec_limit=int(1e6), account_id_key="test_account_id", ) result_set_crud = CRUDResultSet( max_result_set_results=int(1e6), max_result_size_bytes=int(1e6), job_crud=job_crud, ) with temp_db_session() as session: session.execute(f"CREATE ROLE {db_ro_user}") job_create = schemas.JobCreate( name="test_job", description="A Test Job", graph_spec=schemas.JobGraphSpec(graph_names=["test"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?test_account_id ?foo ?boo where {?test_account_id ?foo ?boo} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) created_timestamp = job_crud.create( db_session=session, job_create_in=job_create).created # activate job_update = schemas.JobUpdate(active=True) _job = job_crud.update_version( db_session=session, job_name="test_job", created=created_timestamp, job_update=job_update, ) job = schemas.Job.from_orm(_job) account_id_a = "012345678901" account_id_b = "567890123456" result_set_1_time = datetime.now() - timedelta( seconds=job_create.max_result_age_sec + 1) result_set_1_graph_spec = schemas.ResultSetGraphSpec( graph_uris_load_times={"test": result_set_1_time.timestamp()}) results_1 = [ schemas.Result( account_id=account_id_a, result={ "foo": "oldhello_a", "boo": "oldthere_a" }, ), schemas.Result( account_id=account_id_a, result={ "foo": "oldboo_a", "boo": "oldfoo_a" }, ), schemas.Result( account_id=account_id_b, result={ "foo": "oldhello_b", "boo": "oldthere_b" }, ), schemas.Result( account_id=account_id_b, result={ "foo": "oldboo_b", "boo": "oldfoo_b" }, ), ] result_set_1_create = ResultSetCreate( job=job, graph_spec=result_set_1_graph_spec, results=results_1, created=result_set_1_time, ) result_set_crud.create(db_session=session, obj_in=result_set_1_create) # check latest results latest_results = session.execute("select * from test_job_latest") self.assertEqual(latest_results.rowcount, 0) # check all results all_results = session.execute("select * from test_job_all") all_rows = all_results.fetchall() self.assertSequenceEqual( sorted(all_rows), sorted([ (result_set_1_time, account_id_a, "oldhello_a", "oldthere_a"), (result_set_1_time, account_id_a, "oldboo_a", "oldfoo_a"), (result_set_1_time, account_id_b, "oldhello_b", "oldthere_b"), (result_set_1_time, account_id_b, "oldboo_b", "oldfoo_b"), ]), )
def test_activate_job_creates_views(self): """Test that activating a job via CRUDJob.update will create sql views and validate some basic information schema data about them""" db_ro_user = "******" job_crud = CRUDJob( db_ro_user=db_ro_user, result_expiration_sec_default=int(1e6), result_expiration_sec_limit=int(1e6), max_graph_age_sec_default=int(1e6), max_graph_age_sec_limit=int(1e6), max_result_age_sec_default=int(1e6), max_result_age_sec_limit=int(1e6), account_id_key="test_account_id", ) with temp_db_session() as session: session.execute(f"CREATE ROLE {db_ro_user}") # job which will be activated job_create = schemas.JobCreate( name="test_job", description="A Test Job", graph_spec=schemas.JobGraphSpec(graph_names=["1", "2"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?test_account_id ?foo ?boo where {?test_account_id ?foo ?boo} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) created_timestamp = job_crud.create( db_session=session, job_create_in=job_create).created # activate job_update = schemas.JobUpdate(active=True) job_crud.update_version( db_session=session, job_name="test_job", created=created_timestamp, job_update=job_update, ) # check latest latest_results = session.execute( "select * from information_schema.views where table_name='test_job_latest';" ) self.assertEqual(latest_results.rowcount, 1) latest_row = next(latest_results) self.assertEqual(latest_row["table_catalog"], "qj") self.assertEqual(latest_row["table_schema"], "public") self.assertEqual(latest_row["table_name"], "test_job_latest") self.assertEqual(latest_row["check_option"], "NONE") self.assertEqual("NO", latest_row["is_updatable"]) self.assertEqual("NO", latest_row["is_insertable_into"]) self.assertEqual("NO", latest_row["is_trigger_updatable"]) self.assertEqual("NO", latest_row["is_trigger_deletable"]) self.assertEqual("NO", latest_row["is_trigger_insertable_into"]) # check all all_results = session.execute( "select * from information_schema.views where table_name='test_job_all';" ) self.assertEqual(all_results.rowcount, 1) all_row = next(all_results) self.assertEqual(all_row["table_catalog"], "qj") self.assertEqual(all_row["table_schema"], "public") self.assertEqual(all_row["table_name"], "test_job_all") self.assertEqual(all_row["check_option"], "NONE") self.assertEqual("NO", all_row["is_updatable"]) self.assertEqual("NO", all_row["is_insertable_into"]) self.assertEqual("NO", all_row["is_trigger_updatable"]) self.assertEqual("NO", all_row["is_trigger_deletable"]) self.assertEqual("NO", all_row["is_trigger_insertable_into"])
def test(self): db_ro_user = "******" job_crud = CRUDJob( db_ro_user=db_ro_user, result_expiration_sec_default=int(1e6), result_expiration_sec_limit=int(1e6), max_graph_age_sec_default=int(1e6), max_graph_age_sec_limit=int(1e6), max_result_age_sec_default=int(1e6), max_result_age_sec_limit=int(1e6), account_id_key="test_account_id", ) with temp_db_session() as session: session.execute(f"CREATE ROLE {db_ro_user}") job_create_1 = schemas.JobCreate( name="test_job_1", description="A Test Job 1", graph_spec=schemas.JobGraphSpec(graph_names=["1", "2"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?s ?p ?test_account_id where {?s ?p ?test_account_id} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) created_timestamp_1 = job_crud.create( db_session=session, job_create_in=job_create_1).created job_create_2 = schemas.JobCreate( name="test_job_2", description="A Test Job 2", graph_spec=schemas.JobGraphSpec(graph_names=["a", "b"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?q ?r ?test_account_id where {?q ?r ?test_account_id} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) created_timestamp_2 = job_crud.create( db_session=session, job_create_in=job_create_2).created job_create_3 = schemas.JobCreate( name="test_job_3", description="A Test Job 3", graph_spec=schemas.JobGraphSpec(graph_names=["a", "b"]), category=schemas.Category.gov, severity=schemas.Severity.info, query= "select ?q ?r ?test_account_id where {?q ?r ?test_account_id} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) _job_3 = job_crud.create(db_session=session, job_create_in=job_create_3) job_3 = schemas.Job.from_orm(_job_3) # activate 1, 2 job_update = schemas.JobUpdate(active=True) _activated_job_1 = job_crud.update_version( db_session=session, job_name="test_job_1", created=created_timestamp_1, job_update=job_update, ) activated_job_1 = schemas.Job.from_orm(_activated_job_1) _activated_job_2 = job_crud.update_version( db_session=session, job_name="test_job_2", created=created_timestamp_2, job_update=job_update, ) activated_job_2 = schemas.Job.from_orm(_activated_job_2) _active_only_jobs = job_crud.get_multi(db_session=session, active_only=True) active_only_jobs = [ schemas.Job.from_orm(job) for job in _active_only_jobs ] self.assertEqual(active_only_jobs, [activated_job_1, activated_job_2]) _all_jobs = job_crud.get_multi(db_session=session, active_only=False) all_jobs = [schemas.Job.from_orm(job) for job in _all_jobs] self.assertEqual(all_jobs, [activated_job_1, activated_job_2, job_3])