def _get_existing_cluster_config(cluster_name):
proxy_db = PartitionConfig().get_proxy_db()
with connections[proxy_db].cursor() as cursor:
cursor.execute('SELECT * from pg_foreign_server where srvname = %s', [cluster_name])
results = fetchall_as_namedtuple(cursor)
if results:
return results[0]
def hard_delete_forms(domain, form_ids, delete_attachments=True):
assert isinstance(form_ids, list)
if delete_attachments:
attachments = list(FormAccessorSQL.get_attachments_for_forms(form_ids))
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute('SELECT hard_delete_forms(%s, %s) AS deleted_count', [domain, form_ids])
results = fetchall_as_namedtuple(cursor)
deleted_count = sum([result.deleted_count for result in results])
if delete_attachments:
attachments_to_delete = attachments
if deleted_count != len(form_ids):
# in the unlikely event that we didn't delete all forms (because they weren't all
# in the specified domain), only delete attachments for forms that were deleted.
deleted_forms = set()
for form_id in form_ids:
if not FormAccessorSQL.form_exists(form_id):
deleted_forms.add(form_id)
attachments_to_delete = []
for attachment in attachments:
if attachment.form_id in deleted_forms:
attachments_to_delete.append(attachment)
db = get_blob_db()
paths = [
db.get_path(attachment.blob_id, attachment.blobdb_bucket())
for attachment in attachments_to_delete
]
db.bulk_delete(paths)
return deleted_count
def get_form_ids_in_domain_by_state(domain, state):
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT form_id from get_form_ids_in_domain_by_type(%s, %s)',
[domain, state]
)
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def get_indexed_case_ids(domain, case_ids):
"""
Given a base list of case ids, gets all ids of cases they reference (parent and host cases)
"""
with get_cursor(CommCareCaseIndexSQL) as cursor:
cursor.execute('SELECT referenced_id FROM get_indexed_case_ids(%s, %s)', [domain, list(case_ids)])
results = fetchall_as_namedtuple(cursor)
return [result.referenced_id for result in results]
def get_extension_case_ids(domain, case_ids):
"""
Given a base list of case ids, get all ids of all extension cases that reference them
"""
with get_cursor(CommCareCaseIndexSQL) as cursor:
cursor.execute('SELECT case_id FROM get_extension_case_ids(%s, %s)', [domain, list(case_ids)])
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def get_case_ids_modified_with_owner_since(domain, owner_id, reference_date):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_id FROM get_case_ids_modified_with_owner_since(%s, %s, %s)',
[domain, owner_id, reference_date]
)
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def get_extension_case_ids(domain, case_ids):
"""
Given a base list of case ids, get all ids of all extension cases that reference them
"""
with get_cursor(CommCareCaseIndexSQL) as cursor:
cursor.execute('SELECT case_id FROM get_extension_case_ids(%s, %s)', [domain, list(case_ids)])
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def _get_form_ids_for_user(domain, user_id, is_deleted):
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT form_id FROM get_form_ids_for_user(%s, %s, %s)',
[domain, user_id, is_deleted]
)
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def _get_form_ids_for_user(domain, user_id, is_deleted):
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT form_id FROM get_form_ids_for_user(%s, %s, %s)',
[domain, user_id, is_deleted]
)
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def get_case_xform_ids(case_id):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT form_id FROM get_case_transactions_by_type(%s, %s)',
[case_id, CaseTransaction.TYPE_FORM]
)
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def get_form_ids_in_domain_by_state(domain, state):
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT form_id from get_form_ids_in_domain_by_type(%s, %s)',
[domain, state]
)
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def _get_existing_cluster_config(cluster_config):
proxy_db = cluster_config.proxy_db
with connections[proxy_db].cursor() as cursor:
cursor.execute('SELECT * from pg_foreign_server where srvname = %s',
[cluster_config.cluster_name])
results = list(fetchall_as_namedtuple(cursor))
if results:
return results[0]
def compare_with_old_data(cls, state_id, month):
helper = PostnatalCareFormsCcsRecordAggregationHelper(state_id, month)
query, params = helper.compare_with_old_data_query()
with get_cursor(AggregateComplementaryFeedingForms) as cursor:
cursor.execute(query, params)
rows = fetchall_as_namedtuple(cursor)
return [row.child_health_case_id for row in rows]
def get_case_types_for_domain(domain):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_type FROM get_case_types_for_domain(%s)',
[domain]
)
results = fetchall_as_namedtuple(cursor)
return {result.case_type for result in results}
def get_case_ids_modified_with_owner_since(domain, owner_id, reference_date):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_id FROM get_case_ids_modified_with_owner_since(%s, %s, %s)',
[domain, owner_id, reference_date]
)
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def get_case_xform_ids(case_id):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT form_id FROM get_case_transactions_by_type(%s, %s)',
[case_id, CaseTransaction.TYPE_FORM]
)
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def get_case_types_for_domain(domain):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_type FROM get_case_types_for_domain(%s)',
[domain]
)
results = fetchall_as_namedtuple(cursor)
return {result.case_type for result in results}
def soft_undelete_cases(domain, case_ids):
assert isinstance(case_ids, list)
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT soft_undelete_cases(%s, %s) as affected_count',
[domain, case_ids])
results = fetchall_as_namedtuple(cursor)
return sum([result.affected_count for result in results])
def compare_with_old_data(cls, state_id, month):
from corehq.form_processor.utils.sql import fetchall_as_namedtuple
helper = ComplementaryFormsAggregationHelper(state_id, month)
query, params = helper.compare_with_old_data_query()
with get_cursor(AggregateComplementaryFeedingForms) as cursor:
cursor.execute(query, params)
rows = fetchall_as_namedtuple(cursor)
return [row.child_health_case_id for row in rows]
def soft_undelete_forms(domain, form_ids):
assert isinstance(form_ids, list)
problem = 'Restored on {}'.format(datetime.utcnow())
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT soft_undelete_forms(%s, %s, %s) as affected_count',
[domain, form_ids, problem])
results = fetchall_as_namedtuple(cursor)
return sum([result.affected_count for result in results])
def _get_case_ids_in_domain(domain, case_type=None, owner_ids=None, is_closed=None):
owner_ids = list(owner_ids) if owner_ids else None
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_id FROM get_case_ids_in_domain(%s, %s, %s, %s)',
[domain, case_type, owner_ids, is_closed]
)
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def _get_case_ids_in_domain(domain, case_type=None, owner_ids=None, is_closed=None):
owner_ids = list(owner_ids) if owner_ids else None
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_id FROM get_case_ids_in_domain(%s, %s, %s, %s)',
[domain, case_type, owner_ids, is_closed]
)
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def soft_undelete_cases(domain, case_ids):
assert isinstance(case_ids, list)
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT soft_undelete_cases(%s, %s) as affected_count',
[domain, case_ids]
)
results = fetchall_as_namedtuple(cursor)
return sum([result.affected_count for result in results])
def soft_undelete_forms(domain, form_ids):
assert isinstance(form_ids, list)
problem = 'Restored on {}'.format(datetime.utcnow())
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT soft_undelete_forms(%s, %s, %s) as affected_count',
[domain, form_ids, problem]
)
results = fetchall_as_namedtuple(cursor)
return sum([result.affected_count for result in results])
def get_indexed_case_ids(domain, case_ids):
"""
Given a base list of case ids, gets all ids of cases they reference (parent and host cases)
"""
with get_cursor(CommCareCaseIndexSQL) as cursor:
cursor.execute(
'SELECT referenced_id FROM get_multiple_cases_indices(%s, %s)',
[domain, list(case_ids)])
results = fetchall_as_namedtuple(cursor)
return [result.referenced_id for result in results]
def soft_delete_forms(domain, form_ids, deletion_date=None, deletion_id=None):
assert isinstance(form_ids, list)
deletion_date = deletion_date or datetime.utcnow()
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT soft_delete_forms(%s, %s, %s, %s) as affected_count',
[domain, form_ids, deletion_date, deletion_id]
)
results = fetchall_as_namedtuple(cursor)
return sum([result.affected_count for result in results])
def get_last_modified_dates(domain, case_ids):
"""
Given a list of case IDs, return a dict where the ids are keys and the
values are the last server modified date of that case.
"""
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_id, server_modified_on FROM get_case_last_modified_dates(%s, %s)',
[domain, case_ids]
)
results = fetchall_as_namedtuple(cursor)
return dict((result.case_id, result.server_modified_on) for result in results)
def get_last_modified_dates(domain, case_ids):
"""
Given a list of case IDs, return a dict where the ids are keys and the
values are the last server modified date of that case.
"""
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT case_id, server_modified_on FROM get_case_last_modified_dates(%s, %s)',
[domain, case_ids])
results = fetchall_as_namedtuple(cursor)
return dict((result.case_id, result.server_modified_on)
for result in results)
def delete_ledger_transactions_for_form(case_ids, form_id):
"""
Delete LedgerTransactions for form.
:param case_ids: list of case IDs which ledger transactions belong to (required for correct sharding)
:param form_id: ID of the form
:return: number of transactions deleted
"""
assert isinstance(case_ids, list)
with get_cursor(LedgerTransaction) as cursor:
cursor.execute(
"SELECT delete_ledger_transactions_for_form(%s, %s) as deleted_count",
[case_ids, form_id])
results = fetchall_as_namedtuple(cursor)
return sum([result.deleted_count for result in results])
def delete_ledger_transactions_for_form(case_ids, form_id):
"""
Delete LedgerTransactions for form.
:param case_ids: list of case IDs which ledger transactions belong to (required for correct sharding)
:param form_id: ID of the form
:return: number of transactions deleted
"""
assert isinstance(case_ids, list)
with get_cursor(LedgerTransaction) as cursor:
cursor.execute(
"SELECT delete_ledger_transactions_for_form(%s, %s) as deleted_count",
[case_ids, form_id]
)
results = fetchall_as_namedtuple(cursor)
return sum([result.deleted_count for result in results])
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 connections[db].cursor() 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 soft_delete_forms(domain, form_ids, deletion_date=None, deletion_id=None):
from corehq.form_processor.change_publishers import publish_form_deleted
assert isinstance(form_ids, list)
deletion_date = deletion_date or datetime.utcnow()
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT soft_delete_forms(%s, %s, %s, %s) as affected_count',
[domain, form_ids, deletion_date, deletion_id]
)
results = fetchall_as_namedtuple(cursor)
affected_count = sum([result.affected_count for result in results])
for form_id in form_ids:
publish_form_deleted(domain, form_id)
return affected_count
def soft_delete_forms(domain, form_ids, deletion_date=None, deletion_id=None):
from corehq.form_processor.change_publishers import publish_form_deleted
assert isinstance(form_ids, list)
deletion_date = deletion_date or datetime.utcnow()
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(
'SELECT soft_delete_forms(%s, %s, %s, %s) as affected_count',
[domain, form_ids, deletion_date, deletion_id]
)
results = fetchall_as_namedtuple(cursor)
affected_count = sum([result.affected_count for result in results])
for form_id in form_ids:
publish_form_deleted(domain, form_id)
return affected_count
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 connections[db].cursor() 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_ledger_values(case_id, section_id=None, entry_id=None):
"""
Delete LedgerValues marching passed in args
:param case_id: ID of the case
:param section_id: section ID or None
:param entry_id: entry ID or None
:return: number of values deleted
"""
try:
with get_cursor(LedgerValue) as cursor:
cursor.execute(
"SELECT delete_ledger_values(%s, %s, %s) as deleted_count",
[case_id, section_id, entry_id])
results = fetchall_as_namedtuple(cursor)
return sum([result.deleted_count for result in results])
except InternalError as e:
raise LedgerSaveError(e)
def hash_doc_ids_sql(doc_ids):
"""Get HASH for each doc_id from PostgreSQL
This is used to ensure the python version is consistent with what's
being used by PL/Proxy
"""
assert settings.USE_PARTITIONED_DATABASE
params = ','.join(["(%s)"] * len(doc_ids))
query = """
SELECT doc_id, hash_string(doc_id, 'siphash24') AS hash
FROM (VALUES {}) AS t (doc_id)
""".format(params)
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(query, doc_ids)
rows = fetchall_as_namedtuple(cursor)
return {row.doc_id: row.hash for row in rows}
def hash_doc_ids_sql(doc_ids):
"""Get HASH for each doc_id from PostgreSQL
This is used to ensure the python version is consistent with what's
being used by PL/Proxy
"""
assert settings.USE_PARTITIONED_DATABASE
params = ','.join(["(%s)"] * len(doc_ids))
query = """
SELECT doc_id, hash_string(doc_id, 'siphash24') AS hash
FROM (VALUES {}) AS t (doc_id)
""".format(params)
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute(query, doc_ids)
rows = fetchall_as_namedtuple(cursor)
return {row.doc_id: row.hash for row in rows}
def soft_delete_cases(domain, case_ids, deletion_date=None, deletion_id=None):
from corehq.form_processor.change_publishers import publish_case_deleted
assert isinstance(case_ids, list)
utcnow = datetime.utcnow()
deletion_date = deletion_date or utcnow
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT soft_delete_cases(%s, %s, %s, %s, %s) as affected_count',
[domain, case_ids, utcnow, deletion_date, deletion_id]
)
results = fetchall_as_namedtuple(cursor)
affected_count = sum([result.affected_count for result in results])
for case_id in case_ids:
publish_case_deleted(domain, case_id)
return affected_count
def delete_ledger_values(case_id, section_id=None, entry_id=None):
"""
Delete LedgerValues marching passed in args
:param case_id: ID of the case
:param section_id: section ID or None
:param entry_id: entry ID or None
:return: number of values deleted
"""
try:
with get_cursor(LedgerValue) as cursor:
cursor.execute(
"SELECT delete_ledger_values(%s, %s, %s) as deleted_count",
[case_id, section_id, entry_id]
)
results = fetchall_as_namedtuple(cursor)
return sum([result.deleted_count for result in results])
except InternalError as e:
raise LedgerSaveError(e)
def soft_delete_cases(domain, case_ids, deletion_date=None, deletion_id=None):
from corehq.form_processor.change_publishers import publish_case_deleted
assert isinstance(case_ids, list)
utcnow = datetime.utcnow()
deletion_date = deletion_date or utcnow
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute(
'SELECT soft_delete_cases(%s, %s, %s, %s, %s) as affected_count',
[domain, case_ids, utcnow, deletion_date, deletion_id]
)
results = fetchall_as_namedtuple(cursor)
affected_count = sum([result.affected_count for result in results])
for case_id in case_ids:
publish_case_deleted(domain, case_id)
return affected_count
def hard_delete_forms(domain, form_ids, delete_attachments=True):
assert isinstance(form_ids, list)
if delete_attachments:
attachments = list(
FormAccessorSQL.get_attachments_for_forms(form_ids))
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute('SELECT hard_delete_forms(%s, %s) AS deleted_count',
[domain, form_ids])
results = fetchall_as_namedtuple(cursor)
deleted_count = sum([result.deleted_count for result in results])
if delete_attachments:
attachments_to_delete = attachments
if deleted_count != len(form_ids):
# in the unlikely event that we didn't delete all forms (because they weren't all
# in the specified domain), only delete attachments for forms that were deleted.
deleted_forms = set()
for form_id in form_ids:
if not FormAccessorSQL.form_exists(form_id):
deleted_forms.add(form_id)
attachments_to_delete = []
for attachment in attachments:
if attachment.form_id in deleted_forms:
attachments_to_delete.append(attachment)
db = get_blob_db()
paths = [
db.get_path(attachment.blob_id, attachment.blobdb_bucket())
for attachment in attachments_to_delete
]
db.bulk_delete(paths)
return deleted_count
def hard_delete_forms(domain, form_ids):
assert isinstance(form_ids, list)
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute('SELECT hard_delete_forms(%s, %s) AS deleted_count', [domain, form_ids])
results = fetchall_as_namedtuple(cursor)
return sum([result.deleted_count for result in results])
def get_closed_case_ids(domain, owner_id):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute('SELECT case_id FROM get_closed_case_ids(%s, %s)', [domain, owner_id])
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def get_case_ids_in_domain(domain, type_=None):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute('SELECT case_id FROM get_case_ids_in_domain(%s, %s)', [domain, type_])
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def get_case_ids_in_domain(domain, type_=None):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute('SELECT case_id FROM get_case_ids_in_domain(%s, %s)', [domain, type_])
results = fetchall_as_namedtuple(cursor)
return [result.case_id for result in results]
def get_case_xform_ids(case_id):
with get_cursor(CommCareCaseSQL) as cursor:
cursor.execute('SELECT form_id FROM get_case_form_ids(%s)', [case_id])
results = fetchall_as_namedtuple(cursor)
return [result.form_id for result in results]
def hard_delete_forms(domain, form_ids):
assert isinstance(form_ids, list)
with get_cursor(XFormInstanceSQL) as cursor:
cursor.execute('SELECT hard_delete_forms(%s, %s) AS deleted_count', [domain, form_ids])
results = fetchall_as_namedtuple(cursor)
return sum([result.deleted_count for result in results])
