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)