def setUp(self): now = datetime.utcnow() case_kwargs = { 'owner_id': uuid4().hex, 'modified_on': now, 'server_modified_on': now, } self.host_case_id = uuid4().hex case = CommCareCaseSQL( case_id=self.host_case_id, domain=self.domain, type='person', name='Ted', **case_kwargs, ) self.host_case = create_case(case) case = CommCareCaseSQL( case_id=uuid4().hex, domain=self.domain, type='person_name', name='Theodore', case_json={ 'given_names': 'Theodore John', 'family_name': 'Kaczynski', }, **case_kwargs, ) index = CommCareCaseIndexSQL( domain=self.domain, identifier='host', referenced_type='person', referenced_id=self.host_case_id, relationship_id=CommCareCaseIndexSQL.EXTENSION, ) self.ext_case_1 = create_case_with_index(case, index) case = CommCareCaseSQL( case_id=uuid4().hex, domain=self.domain, type='person_name', name='Unabomber', case_json={ 'given_names': 'Unabomber', }, **case_kwargs, ) index = CommCareCaseIndexSQL( domain=self.domain, identifier='host', referenced_type='person', referenced_id=self.host_case_id, relationship_id=CommCareCaseIndexSQL.EXTENSION, ) self.ext_case_2 = create_case_with_index(case, index)
def setUp(self): now = datetime.utcnow() yesterday = now - timedelta(days=1) owner_id = uuid4().hex self.parent_case_id = uuid4().hex case = CommCareCaseSQL( case_id=self.parent_case_id, domain=self.domain, type='person', name='Joe', owner_id=owner_id, modified_on=yesterday, server_modified_on=yesterday, ) self.parent_case = create_case(case) case = CommCareCaseSQL( case_id=uuid4().hex, domain=self.domain, type='temperature', case_json={ 'value': '36.2', }, owner_id=owner_id, modified_on=yesterday, server_modified_on=yesterday, ) index = CommCareCaseIndexSQL( domain=self.domain, identifier='parent', referenced_type='person', referenced_id=self.parent_case_id, relationship_id=CommCareCaseIndexSQL.CHILD, ) self.child_case_1 = create_case_with_index(case, index) case = CommCareCaseSQL( case_id=uuid4().hex, domain=self.domain, type='temperature', case_json={ 'value': '36.6', }, owner_id=owner_id, modified_on=now, server_modified_on=now, ) index = CommCareCaseIndexSQL( domain=self.domain, identifier='parent', referenced_type='person', referenced_id=self.parent_case_id, relationship_id=CommCareCaseIndexSQL.CHILD, ) self.child_case_2 = create_case_with_index(case, index)
def _apply_index_action(self, action): if not action.indices: return for index_update in action.indices: if self.case.has_index(index_update.identifier): if not index_update.referenced_id: # empty ID = delete index = self.case.get_index(index_update.identifier) self.case.track_delete(index) else: # update index = self.case.get_index(index_update.identifier) index.referenced_type = index_update.referenced_type index.referenced_id = index_update.referenced_id index.relationship = index_update.relationship self.case.track_update(index) else: # no id, no index if index_update.referenced_id: index = CommCareCaseIndexSQL( domain=self.case.domain, case=self.case, identifier=index_update.identifier, referenced_type=index_update.referenced_type, referenced_id=index_update.referenced_id, relationship=index_update.relationship ) self.case.track_create(index)
def _add_unique_constraint_to_case_index_table(db): """This will add a unique index concurrently and also a table constraint that uses the index. The result will be the same as adding a 'unique_together' option in the Django model. """ create_index_sql = """ CREATE UNIQUE INDEX CONCURRENTLY {index_name} on {case_index_table} ("case_id", "identifier") """.format( case_index_table=CommCareCaseIndexSQL._meta.db_table, index_name=UNIQIE_INDEX_NAME, ) add_constraint_sql = """ ALTER TABLE {case_index_table} ADD CONSTRAINT {index_name} UNIQUE USING INDEX {index_name} """.format( case_index_table=CommCareCaseIndexSQL._meta.db_table, index_name=UNIQIE_INDEX_NAME, ) try: with CommCareCaseIndexSQL.get_cursor_for_partition_db(db) as cursor: log_sql(create_index_sql) cursor.execute(create_index_sql) log_sql(add_constraint_sql) cursor.execute(add_constraint_sql) except: # if the index creation failed make sure we remove it otherwise we # are left with an invalid index _drop_index(db, UNIQIE_INDEX_NAME) raise
def test_patch_missing_case_index(self): self.submit_form(make_test_form("form-1", case_id="case-1")) self.do_migration(case_diff="none") CommCareCaseIndexSQL( domain=self.domain_name, case_id="case-1", identifier="parent", referenced_id="a53346d5", referenced_type="household", relationship_id=CommCareCaseIndexSQL.CHILD, ).save() with self.diff_without_rebuild(): self.do_case_diffs() index = { "case_id": "case-1", "identifier": "parent", "referenced_id": "a53346d5", "referenced_type": "household", "relationship": "child", } self.compare_diffs([ Diff('case-1', 'missing', ['indices', '[*]'], old=MISSING, new=index), ]) with self.diff_without_rebuild(): self.do_case_patch() self.compare_diffs() self.assert_patched_cases(["case-1"])
def _drop_index(db, index_name): """Drop the index if it exists""" drop_identifier_index = """ DROP INDEX CONCURRENTLY IF EXISTS {index_name} """.format(index_name=index_name) with CommCareCaseIndexSQL.get_cursor_for_partition_db(db) as cursor: log_sql(drop_identifier_index) cursor.execute(drop_identifier_index)
def _index_exists(db, index_name): with CommCareCaseIndexSQL.get_cursor_for_partition_db( db).cursor() as cursor: sql = "SELECT to_regclass('{}') IS NOT NULL as index_exists".format( index_name) log_sql(sql) cursor.execute(sql) return fetchone_as_namedtuple(cursor).index_exists
def _add_temp_index(db): """Add an index to the 'identifier' column to make queries faster.""" add_identifier_index = """ CREATE INDEX CONCURRENTLY {identifier_index_name} ON {case_index_table} (identifier) """.format(case_index_table=CommCareCaseIndexSQL._meta.db_table, identifier_index_name=IDENTIFIER_INDEX_NAME) with CommCareCaseIndexSQL.get_cursor_for_partition_db(db) as cursor: if not _index_exists(db, IDENTIFIER_INDEX_NAME): log_sql(add_identifier_index) cursor.execute(add_identifier_index)
def _get_case_ids_with_dupe_indices(db): """Get case_ids that have duplicate indices (same identifier) """ case_id_with_dupes_sql = """ SELECT case_id, identifier, count(*) FROM {case_index_table} GROUP BY case_id, identifier HAVING count(*) > 1 """.format(case_index_table=CommCareCaseIndexSQL._meta.db_table) with CommCareCaseIndexSQL.get_cursor_for_partition_db(db) as cursor: log_sql(case_id_with_dupes_sql) cursor.execute(case_id_with_dupes_sql) rows_with_dupes = fetchall_as_namedtuple(cursor) case_ids = {row.case_id for row in rows_with_dupes} return case_ids
def _delete_duplicate_indices(case_ids, db): """Delete duplicate indices on cases only if they point to the same target case and have the same identifier and relationship""" delete_dupes_sql = """ DELETE FROM {case_index_table} WHERE id in ( SELECT id FROM ( SELECT id, case_id, row_number() OVER (PARTITION BY case_id, identifier, referenced_id, relationship_id) FROM {case_index_table} JOIN (SELECT UNNEST(ARRAY['{{case_ids}}']) AS case_id) AS cx USING (case_id)) as indices WHERE row_number > 1 ) """.format(case_index_table=CommCareCaseIndexSQL._meta.db_table) for chunk in chunked(case_ids, 100): with CommCareCaseIndexSQL.get_cursor_for_partition_db(db) as cursor: delete_sql = delete_dupes_sql.format(case_ids="','".join(chunk)) log_sql(delete_sql) cursor.execute(delete_sql)