def test_query_missing_account_id_field(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 = schemas.JobCreate( name="test_job", description="A Test Job With A Bad Query", graph_spec=schemas.JobGraphSpec(graph_names=["1", "2"]), category=schemas.Category.gov, severity=schemas.Severity.info, query="select ?s ?p ?foo where {?s ?p ?foo} limit 10", max_graph_age_sec=int(1e6), result_expiration_sec=int(1e6), max_result_age_sec=int(1e6), notify_if_results=False, ) with self.assertRaises(JobQueryMissingAccountId): job_crud.create(db_session=session, job_create_in=job_create)
def test_get_active_job_with_no_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_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), notify_if_results=False, ) job_crud.create(db_session=session, job_create_in=job_create) with self.assertRaises(ActiveJobVersionNotFound): job_crud.get_active(session, "test_job")
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])