def setUp(self):
now = datetime.utcnow()
yesterday = now - timedelta(days=1)
owner_id = uuid4().hex
self.parent_case_id = uuid4().hex
case = CommCareCase(
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 = CommCareCase(
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 = CommCareCaseIndex(
domain=self.domain,
identifier='parent',
referenced_type='person',
referenced_id=self.parent_case_id,
relationship_id=CommCareCaseIndex.CHILD,
)
self.child_case_1 = create_case_with_index(case, index)
case = CommCareCase(
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 = CommCareCaseIndex(
domain=self.domain,
identifier='parent',
referenced_type='person',
referenced_id=self.parent_case_id,
relationship_id=CommCareCaseIndex.CHILD,
)
self.child_case_2 = create_case_with_index(case, index)
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 = CommCareCase(
case_id=self.host_case_id,
domain=self.domain,
type='person',
name='Ted',
**case_kwargs,
)
self.host_case = create_case(case)
case = CommCareCase(
case_id=uuid4().hex,
domain=self.domain,
type='person_name',
name='Theodore',
case_json={
'given_names': 'Theodore John',
'family_name': 'Kaczynski',
},
**case_kwargs,
)
index = CommCareCaseIndex(
domain=self.domain,
identifier='host',
referenced_type='person',
referenced_id=self.host_case_id,
relationship_id=CommCareCaseIndex.EXTENSION,
)
self.ext_case_1 = create_case_with_index(case, index)
case = CommCareCase(
case_id=uuid4().hex,
domain=self.domain,
type='person_name',
name='Unabomber',
case_json={
'given_names': 'Unabomber',
},
**case_kwargs,
)
index = CommCareCaseIndex(
domain=self.domain,
identifier='host',
referenced_type='person',
referenced_id=self.host_case_id,
relationship_id=CommCareCaseIndex.EXTENSION,
)
self.ext_case_2 = create_case_with_index(case, index)
def setUp(self):
i1 = {
'identifier': 'i1',
'referenced_type': 't1',
'referenced_id': 'id1',
}
i2 = {
'identifier': 'i2',
'referenced_type': 't2',
'referenced_id': 'id2',
}
self.i1 = CommCareCaseIndex(**i1)
self.i2 = CommCareCaseIndex(**i2)
self.case = CommCareCase(indices=[i1, i2])
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=CommCareCaseIndex._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=CommCareCaseIndex._meta.db_table,
index_name=UNIQIE_INDEX_NAME,
)
try:
with CommCareCaseIndex.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: # noqa: E722
# 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 _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 CommCareCaseIndex.get_cursor_for_partition_db(db) as cursor:
log_sql(drop_identifier_index)
cursor.execute(drop_identifier_index)
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=CommCareCaseIndex._meta.db_table,
identifier_index_name=IDENTIFIER_INDEX_NAME)
with CommCareCaseIndex.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 test_constructor_ignores_doc_type(self):
# Just ensure it doesn't raise an exception
data = {
'doc_type': 'CommCareCaseIndex',
'identifier': 'my_parent',
'relationship': 'child',
'referenced_type': 'comunidad',
'referenced_id': 'ed285193-3795-4b39-b08b-ac9ad941527f'
}
CommCareCaseIndex(**data)
def test_fields(self):
data = {
'identifier': 'my_parent',
'relationship': 'child',
'referenced_type': 'some_type',
'referenced_id': 'some_id'
}
index = CommCareCaseIndex(**data)
self.assertEqual(index.identifier, 'my_parent')
self.assertEqual(index.relationship, 'child')
self.assertEqual(index.referenced_type, 'some_type')
self.assertEqual(index.referenced_id, 'some_id')
def _mock_case(case_id, props=None, domain=TARGET_DOMAIN, case_type="patient"):
props = props if props is not None else {
"existing_prop": uuid.uuid4().hex,
"existing_blank_prop": ""
}
case = CommCareCase(
domain=domain, type=case_type, case_id=case_id,
name=None, external_id=None,
case_json=props,
)
case.cached_indices = [
CommCareCaseIndex(
domain=domain, case_id=case_id,
identifier="parent_c", referenced_type="parent_type",
referenced_id="parent_case_id", relationship_id=CommCareCaseIndex.CHILD
),
CommCareCaseIndex(
domain=domain, case_id=case_id,
identifier="host_c", referenced_type="host_type",
referenced_id="host_case_id", relationship_id=CommCareCaseIndex.EXTENSION
)
]
return case
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=CommCareCaseIndex._meta.db_table)
with CommCareCaseIndex.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(%s) AS case_id) AS cx USING (case_id)) as indices
WHERE row_number > 1
)
""".format(case_index_table=CommCareCaseIndex._meta.db_table)
for chunk in chunked(case_ids, 100, list):
with CommCareCaseIndex.get_cursor_for_partition_db(db) as cursor:
log_sql(delete_dupes_sql % repr(chunk))
cursor.execute(delete_dupes_sql, [chunk])
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):
# 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 = CommCareCaseIndex(
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 _index_exists(db, index_name):
with CommCareCaseIndex.get_cursor_for_partition_db(db).cursor() as cursor:
sql = "SELECT to_regclass(%s) IS NOT NULL as index_exists"
log_sql(sql % repr(index_name))
cursor.execute(sql, [index_name])
return cursor.fetchone()[0]
def indices(self):
return [
CommCareCaseIndex(**index) for index in self._data['indices']
if index["referenced_id"]
]
def test_relationship_id_is_set_by_relationship(self):
index = CommCareCaseIndex(relationship='extension')
self.assertEqual(index.relationship_id, 2)