def test_get_with_attachments(self): form = create_form_for_test(DOMAIN) form = FormAccessorSQL.get_form( form.form_id) # refetch to clear cached attachments with self.assertNumQueries( 1, using=db_for_read_write(XFormAttachmentSQL)): form.get_attachment_meta('form.xml') with self.assertNumQueries( 1, using=db_for_read_write(XFormAttachmentSQL)): form.get_attachment_meta('form.xml') with ExitStack() as stack: if settings.USE_PARTITIONED_DATABASE: proxy_queries = 1 stack.enter_context(self.assertNumQueries(1, using=form.db)) else: proxy_queries = 2 stack.enter_context( self.assertNumQueries( proxy_queries, using=db_for_read_write(XFormAttachmentSQL))) form = FormAccessorSQL.get_with_attachments(form.form_id) self._check_simple_form(form) with self.assertNumQueries( 0, using=db_for_read_write(XFormAttachmentSQL)): attachment_meta = form.get_attachment_meta('form.xml') self.assertEqual(form.form_id, attachment_meta.form_id) self.assertEqual('form.xml', attachment_meta.name) self.assertEqual('text/xml', attachment_meta.content_type)
def test_get_forms_with_attachments_meta(self): attachment_file = open('./corehq/ex-submodules/casexml/apps/case/tests/data/attachments/fruity.jpg', 'rb') attachments = { 'pic.jpg': UploadedFile(attachment_file, 'pic.jpg', content_type='image/jpeg') } form_with_pic = create_form_for_test(DOMAIN, attachments=attachments) plain_form = create_form_for_test(DOMAIN) forms = FormAccessorSQL.get_forms_with_attachments_meta( [form_with_pic.form_id, plain_form.form_id], ordered=True ) self.assertEqual(2, len(forms)) form = forms[0] self.assertEqual(form_with_pic.form_id, form.form_id) with self.assertNumQueries(0, using=db_for_read_write(XFormAttachmentSQL)): expected = { 'form.xml': 'text/xml', 'pic.jpg': 'image/jpeg', } attachments = form.get_attachments() self.assertEqual(2, len(attachments)) self.assertEqual(expected, {att.name: att.content_type for att in attachments}) with self.assertNumQueries(0, using=db_for_read_write(XFormAttachmentSQL)): expected = { 'form.xml': 'text/xml', } attachments = forms[1].get_attachments() self.assertEqual(1, len(attachments)) self.assertEqual(expected, {att.name: att.content_type for att in attachments})
def test_get_with_attachments(self): form = create_form_for_test(DOMAIN) with self.assertNumQueries(1, using=db_for_read_write(XFormAttachmentSQL)): form.get_attachment_meta('form.xml') with self.assertNumQueries(2, using=db_for_read_write(XFormAttachmentSQL)): form = FormAccessorSQL.get_with_attachments(form.form_id) self._check_simple_form(form) with self.assertNumQueries(0, using=db_for_read_write(XFormAttachmentSQL)): attachment_meta = form.get_attachment_meta('form.xml') self.assertEqual(form.form_id, attachment_meta.form_id) self.assertEqual('form.xml', attachment_meta.name) self.assertEqual('text/xml', attachment_meta.content_type)
def test_get_attachments(self): case = _create_case() case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='pic.jpg', content_type='image/jpeg' )) case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='doc', content_type='text/xml' )) CaseAccessorSQL.save_case(case) with self.assertRaises(AttachmentNotFound): CaseAccessorSQL.get_attachment_by_name(case.case_id, 'missing') with self.assertNumQueries(1, using=db_for_read_write(CaseAttachmentSQL)): attachments = CaseAccessorSQL.get_attachments(case.case_id) self.assertEqual(2, len(attachments)) sorted_attachments = sorted(attachments, key=lambda x: x.name) for att in attachments: self.assertEqual(case.case_id, att.case_id) self.assertEqual('doc', sorted_attachments[0].name) self.assertEqual('pic.jpg', sorted_attachments[1].name)
def _child_health_monthly_table(state_ids, day): helper = ChildHealthMonthlyAggregationHelper(state_ids, force_to_date(day)) celery_task_logger.info("Creating temporary table") with get_cursor(ChildHealthMonthly) as cursor: cursor.execute(helper.drop_temporary_table()) cursor.execute(helper.create_temporary_table()) # https://github.com/celery/celery/issues/4274 sub_aggregations = [ _child_health_helper.delay(query=query, params=params) for query, params in helper.pre_aggregation_queries() ] for sub_aggregation in sub_aggregations: sub_aggregation.get(disable_sync_subtasks=False) celery_task_logger.info("Inserting into child_health_monthly_table") with transaction.atomic(using=db_for_read_write(ChildHealthMonthly)): _run_custom_sql_script([ "SELECT create_new_table_for_month('child_health_monthly', %s)", ], day) ChildHealthMonthly.aggregate(state_ids, force_to_date(day)) celery_task_logger.info("Dropping temporary table") with get_cursor(ChildHealthMonthly) as cursor: cursor.execute(helper.drop_temporary_table())
def truncate_records_for_cls(cls, cascade=False): if settings.UNIT_TESTING: cls.objects.all().delete() return database = db_for_read_write(cls) with connections[database].cursor() as cursor: cursor.execute("TRUNCATE {} {}".format(cls._meta.db_table, 'CASCADE' if cascade else ''))
def raw(self, raw_query, params=None, translations=None, using=None): if not using: using = db_for_read_write(self.model) return RawQuerySet( raw_query, model=self.model, params=params, translations=translations, using=using )
def raw(self, raw_query, params=None, translations=None, using=None): from django.db.models.query import RawQuerySet if not using: using = db_for_read_write(self.model) return RawQuerySet(raw_query, model=self.model, params=params, translations=translations, using=using)
def test_get_attachment_by_name(self): case = _create_case() case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='pic.jpg', content_type='image/jpeg', blob_id='123', identifier='pic1', md5='123' )) case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='my_doc', content_type='text/xml', blob_id='124', identifier='doc1', md5='123' )) CaseAccessorSQL.save_case(case) with self.assertRaises(AttachmentNotFound): CaseAccessorSQL.get_attachment_by_identifier(case.case_id, 'missing') with self.assertNumQueries(1, using=db_for_read_write(CaseAttachmentSQL)): attachment_meta = CaseAccessorSQL.get_attachment_by_identifier(case.case_id, 'pic1') self.assertEqual(case.case_id, attachment_meta.case_id) self.assertEqual('pic.jpg', attachment_meta.name) self.assertEqual('image/jpeg', attachment_meta.content_type)
def test_get_attachments(self): case = _create_case() case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='pic.jpg', content_type='image/jpeg', blob_id='125', identifier='pic1', md5='123', )) case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='doc', content_type='text/xml', blob_id='126', identifier='doc1', md5='123', )) CaseAccessorSQL.save_case(case) with self.assertNumQueries(1, using=db_for_read_write(CaseAttachmentSQL)): attachments = CaseAccessorSQL.get_attachments(case.case_id) self.assertEqual(2, len(attachments)) sorted_attachments = sorted(attachments, key=lambda x: x.name) for att in attachments: self.assertEqual(case.case_id, att.case_id) self.assertEqual('doc', sorted_attachments[0].name) self.assertEqual('pic.jpg', sorted_attachments[1].name)
def test_get_case_by_id(self): case = _create_case() with self.assertNumQueries(1, using=db_for_read_write(CommCareCaseSQL)): case = CaseAccessorSQL.get_case(case.case_id) self.assertIsNotNone(case) self.assertIsInstance(case, CommCareCaseSQL) self.assertEqual(DOMAIN, case.domain) self.assertEqual('user1', case.owner_id)
def commit(self, batch): """ Commits records based on a time frame. :param batch: The Batch of the batch being committed :returns: True if commit passed validations, False otherwise """ with transaction.atomic(using=db_for_read_write(self.model_cls)): self.load(batch) return True
class CaseAccessorTestsSQL(TestCase): def tearDown(self): FormProcessorTestUtils.delete_all_sql_forms(DOMAIN) FormProcessorTestUtils.delete_all_sql_cases(DOMAIN) super(CaseAccessorTestsSQL, self).tearDown() def test_get_case_by_id(self): case = _create_case() with self.assertNumQueries(1, using=db_for_read_write(CommCareCaseSQL)): case = CaseAccessorSQL.get_case(case.case_id)
def load(cls, batch): from corehq.warehouse.models.shared import WarehouseTable ''' Bulk loads records for a dim or fact table from their corresponding dependencies ''' assert issubclass(cls, WarehouseTable) database = db_for_read_write(cls) with connections[database].cursor() as cursor: cursor.execute(cls._sql_query_template(cls.slug, batch))
def load(self, batch): from corehq.warehouse.loaders.base import BaseLoader """ Bulk loads records for a dim or fact table from their corresponding dependencies """ assert isinstance(self, BaseLoader) database = db_for_read_write(self.model_cls) with connections[database].cursor() as cursor: cursor.execute(self._sql_query_template(self.slug, batch)) if self.progress_logger: self.progress_logger.complete()
def test_get_attachment_by_name(self): form = create_form_for_test(DOMAIN) form_xml = get_simple_form_xml(form.form_id) with self.assertRaises(AttachmentNotFound): FormAccessorSQL.get_attachment_by_name(form.form_id, 'not_a_form.xml') with self.assertNumQueries(1, using=db_for_read_write(XFormAttachmentSQL)): attachment_meta = FormAccessorSQL.get_attachment_by_name(form.form_id, 'form.xml') self.assertEqual(form.form_id, attachment_meta.form_id) self.assertEqual('form.xml', attachment_meta.name) self.assertEqual('text/xml', attachment_meta.content_type) self.assertEqual(form_xml, attachment_meta.read_content())
def _update_aggregate_locations_tables(): try: celery_task_logger.info("Starting icds reports update_location_tables") with transaction.atomic(using=db_for_read_write(AwcLocation)): AwcLocation.aggregate() celery_task_logger.info("Ended icds reports update_location_tables_sql") except IntegrityError: # This has occurred when there's a location upload, but not all locations were updated. # Some more details are here https://github.com/dimagi/commcare-hq/pull/18839 # It's usually fixed by rebuild the location UCR table and running this task again, but # that PR should fix that issue _dashboard_team_soft_assert(False, "Error occurred while aggregating locations") raise except Exception: # I'm not sure what this one will be _dashboard_team_soft_assert( False, "Unexpected occurred while aggregating locations in dashboard aggregation") raise
def test_serialize_attachments(self): form_id = uuid.uuid4().hex form_xml = get_simple_form_xml(form_id) submit_form_locally(form_xml, domain=self.domain) form = FormAccessorSQL().get_form(form_id) with self.assertNumQueries(1, using=form.db): # 1 query to fetch the form.xml attachment. The rest are lazy form_json = form.to_json(include_attachments=True) form_xml = form.get_attachment_meta('form.xml') with self.assertNumQueries(1, using=form.db): # lazy evaluation of attachments list self.assertEqual(form_json['external_blobs']['form.xml']['id'], str(form_xml.key)) # this query goes through pl_proxy db = db_for_read_write(XFormInstanceSQL) with self.assertNumQueries(1, using=db): # lazy evaluation of history self.assertEqual(0, len(form_json['history']))
def _child_health_monthly_table(state_ids, day): helper = ChildHealthMonthlyAggregationHelper(state_ids, force_to_date(day)) celery_task_logger.info("Creating temporary table") with get_cursor(ChildHealthMonthly) as cursor: cursor.execute(helper.drop_temporary_table()) cursor.execute(helper.create_temporary_table()) sub_aggregations = group([ _child_health_helper.si(query=query, params=params) for query, params in helper.pre_aggregation_queries() ]).apply_async() sub_aggregations.get() celery_task_logger.info("Inserting into child_health_monthly_table") with transaction.atomic(using=db_for_read_write(ChildHealthMonthly)): _run_custom_sql_script([ "SELECT create_new_table_for_month('child_health_monthly', %s)", ], day) ChildHealthMonthly.aggregate(state_ids, force_to_date(day)) celery_task_logger.info("Dropping temporary table") with get_cursor(ChildHealthMonthly) as cursor: cursor.execute(helper.drop_temporary_table())
def _agg_ls_table(day): with transaction.atomic(using=db_for_read_write(AggLs)): AggLs.aggregate(force_to_date(day))
def _agg_ls_awc_mgt_form(state_id, day): with transaction.atomic(using=db_for_read_write(AggLs)): AggregateLsAWCVisitForm.aggregate(state_id, force_to_date(day))
def _agg_beneficiary_form(state_id, day): with transaction.atomic(using=db_for_read_write(AggLs)): AggregateBeneficiaryForm.aggregate(state_id, force_to_date(day))
def test_get_form_by_id(self): form = create_form_for_test(DOMAIN) with self.assertNumQueries(1, using=db_for_read_write(XFormInstanceSQL)): form = FormAccessorSQL.get_form(form.form_id) self._check_simple_form(form)
def get_cursor(model): db = db_for_read_write(model) return connections[db].cursor()
def _agg_child_health_table(day): with transaction.atomic(using=db_for_read_write(AggChildHealth)): _run_custom_sql_script([ "SELECT create_new_aggregate_table_for_month('agg_child_health', %s)", ], day) AggChildHealth.aggregate(force_to_date(day))
def _agg_ccs_record_table(day): with transaction.atomic(using=db_for_read_write(AggCcsRecord)): _run_custom_sql_script([ "SELECT create_new_aggregate_table_for_month('agg_ccs_record', %s)", ], day) AggCcsRecord.aggregate(force_to_date(day))
def aggregate_awc_daily(day): with transaction.atomic(using=db_for_read_write(AggAwcDaily)): AggAwcDaily.aggregate(force_to_date(day))
def aggregate(self, cursor): staging_queries = self.staging_queries() update_queries = self.update_queries() rollup_queries = [self.rollup_query(i) for i in range(4, 0, -1)] logger.info(f"Creating staging table for {self.helper_key}") self.cleanup(cursor) cursor.execute(f""" CREATE TABLE {self.staging_tablename} ( LIKE {self.base_tablename} INCLUDING INDEXES INCLUDING CONSTRAINTS INCLUDING DEFAULTS, CHECK (month = DATE '{self.month.strftime('%Y-%m-01')}') ) """) logger.info( f"Inserting inital data into staging table for {self.helper_key}") for staging_query, params in staging_queries: cursor.execute(staging_query, params) logger.info(f"Updating data into staging table for {self.helper_key}") for query, params in update_queries: cursor.execute(query, params) logger.info( f"Rolling up data into staging table for {self.helper_key}") for query in rollup_queries: cursor.execute(query) logger.info(f"Creating new table for {self.helper_key} {self.month}") cursor.execute(f""" CREATE TABLE IF NOT EXISTS "{self.monthly_tablename}" ( CHECK (month = DATE '{self.month.strftime('%Y-%m-01')}') ) INHERITS ({self.base_tablename}) """) logger.info( f"Deleting previous day's agg table for {self.helper_key} {self.month}" ) cursor.execute( f'DROP TABLE IF EXISTS "{self.previous_agg_table_name}"') logger.info(f"Creating indexes for {self.helper_key} {self.month}") for index_query in self.indexes(): cursor.execute(index_query) db_alias = db_for_read_write(self.model) with transaction.atomic(using=db_alias): logger.info( f"Dropping legacy tables for {self.helper_key} {self.month}") for i in range(1, 6): cursor.execute( f'DROP TABLE IF EXISTS "{self._legacy_tablename_func(i)}"') logger.info( f"Detaching previous table for {self.helper_key} {self.month}") cursor.execute( f'ALTER TABLE IF EXISTS "{self.monthly_tablename}" NO INHERIT "{self.base_tablename}"' ) logger.info( f"Renaming previous table for {self.helper_key} {self.month}") cursor.execute(f""" ALTER TABLE IF EXISTS "{self.monthly_tablename}" RENAME TO "{self.previous_agg_table_name}" """) logger.info( f"Renaming new table for {self.helper_key} {self.month}") cursor.execute(f""" ALTER TABLE IF EXISTS "{self.staging_tablename}" RENAME TO "{self.monthly_tablename}" """) logger.info( f"Attaching new table for {self.helper_key} {self.month}") cursor.execute( f'ALTER TABLE IF EXISTS "{self.monthly_tablename}" INHERIT "{self.base_tablename}"' ) self.cleanup(cursor)
def _agg_awc_table_weekly(day): with transaction.atomic(using=db_for_read_write(AggAwc)): AggAwc.weekly_aggregate(force_to_date(day))
def _agg_awc_table(day): with transaction.atomic(using=db_for_read_write(AggAwc)): _run_custom_sql_script([ "SELECT create_new_aggregate_table_for_month('agg_awc', %s)" ], day) AggAwc.aggregate(force_to_date(day))
md5='123' )) case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='my_doc', content_type='text/xml', blob_id='124', md5='123' )) CaseAccessorSQL.save_case(case) with self.assertRaises(AttachmentNotFound): CaseAccessorSQL.get_attachment_by_name(case.case_id, 'missing') with self.assertNumQueries(1, using=db_for_read_write(CaseAttachmentSQL)): attachment_meta = CaseAccessorSQL.get_attachment_by_name(case.case_id, 'pic.jpg') self.assertEqual(case.case_id, attachment_meta.case_id) self.assertEqual('pic.jpg', attachment_meta.name) self.assertEqual('image/jpeg', attachment_meta.content_type) def test_get_attachments(self): case = _create_case() case.track_create(CaseAttachmentSQL( case=case, attachment_id=uuid.uuid4().hex, name='pic.jpg', content_type='image/jpeg', blob_id='125',
def commit(cls, batch): with transaction.atomic(using=db_for_read_write(cls)): cls.load(batch) return True
def _agg_ls_vhnd_form(state_id, day): with transaction.atomic(using=db_for_read_write(AggLs)): AggregateLsVhndForm.aggregate(state_id, force_to_date(day))
def _ccs_record_monthly_table(day): with transaction.atomic(using=db_for_read_write(CcsRecordMonthly)): _run_custom_sql_script([ "SELECT create_new_table_for_month('ccs_record_monthly', %s)", ], day) CcsRecordMonthly.aggregate(force_to_date(day))