105 lines
3.4 KiB
Python
105 lines
3.4 KiB
Python
from django.db import connection
|
|
|
|
|
|
def get_sql(sql, params):
|
|
"""
|
|
Execute an SQL query and return the associated cursor.
|
|
"""
|
|
cursor = connection.cursor()
|
|
cursor.execute(sql, params)
|
|
return cursor
|
|
|
|
|
|
def get_sql_count(sql, params):
|
|
"""
|
|
Execute a count on SUB-SELECT and return the count.
|
|
"""
|
|
cursor = get_sql('''SELECT COUNT(*) FROM (%s) AS CNT''' % sql, params)
|
|
return cursor.fetchone()[0]
|
|
|
|
|
|
def get_sql_ids(sql, params):
|
|
"""
|
|
Retrieve a list of numerical ids.
|
|
"""
|
|
cursor = get_sql(sql, params)
|
|
return (row[0] for row in cursor.fetchall())
|
|
|
|
|
|
def get_complex_join(qs, sql, params):
|
|
ids = list(get_sql_ids(sql, params))
|
|
return qs.filter(pk__in=ids)
|
|
|
|
|
|
def get_unseen_documents_count(related_users, user):
|
|
query = GET_UNSEEN_DOCUMENTS_SQL % ('(%s)' % ', '.join(['%s'] * len(related_users)))
|
|
return get_sql_count(
|
|
query,
|
|
(False,)
|
|
+ tuple(related_users.values_list('id', flat=True))
|
|
+ (user.pk, user.pk, False, user.pk, True),
|
|
)
|
|
|
|
|
|
def get_documents(qs, related_users, user, outbox):
|
|
query = GET_DOCUMENTS_SQL % ('(%s)' % ', '.join(['%s'] * len(related_users)))
|
|
qs = get_complex_join(
|
|
qs,
|
|
query,
|
|
(outbox,) + tuple(related_users.values_list('id', flat=True)) + (user.pk, False, user.pk, True),
|
|
)
|
|
qs = qs.prefetch_related('to_list', 'to_user', 'mailboxes__owner')
|
|
qs = qs.extra(select={'seen': SEEN_DOCUMENT % user.pk})
|
|
return qs
|
|
|
|
|
|
def get_trash_documents(qs, related_users, user, outbox):
|
|
query = GET_TRASH_DOCUMENTS_SQL % ('(%s)' % ', '.join(['%s'] * len(related_users)))
|
|
qs = get_complex_join(
|
|
qs,
|
|
query,
|
|
(outbox,) + tuple(related_users.values_list('id', flat=True)) + (user.pk, False, user.pk, True),
|
|
)
|
|
qs = qs.prefetch_related('to_list', 'to_user', 'mailboxes__owner')
|
|
return qs
|
|
|
|
|
|
GET_UNSEEN_DOCUMENTS_SQL = '''SELECT d.id
|
|
FROM docbow_document AS d
|
|
INNER JOIN docbow_mailbox AS mb
|
|
ON mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
|
|
WHERE
|
|
NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
|
|
AND NOT EXISTS(SELECT 1 FROM docbow_seendocument sd WHERE sd.document_id = d.id AND sd.user_id = %%s)
|
|
AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
|
|
GROUP BY d.id, d.date
|
|
ORDER BY d.date
|
|
'''
|
|
|
|
GET_DOCUMENTS_SQL = '''SELECT d.id
|
|
FROM docbow_document AS d
|
|
INNER JOIN docbow_mailbox AS mb ON
|
|
mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
|
|
WHERE
|
|
NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
|
|
AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
|
|
GROUP BY d.id, d.date
|
|
ORDER BY d.date'''
|
|
|
|
|
|
GET_TRASH_DOCUMENTS_SQL = '''SELECT d.id
|
|
FROM docbow_document AS d
|
|
INNER JOIN docbow_mailbox AS mb ON
|
|
mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
|
|
LEFT JOIN docbow_deleteddocument AS dd ON
|
|
dd.document_id = d.id AND dd.user_id = %%s AND dd.soft_delete
|
|
WHERE (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
|
|
GROUP BY d.id, d.date
|
|
ORDER BY d.date'''
|
|
|
|
|
|
SEEN_DOCUMENT = '''SELECT COUNT(*) > 0
|
|
FROM docbow_seendocument
|
|
WHERE docbow_seendocument.document_id = docbow_document.id
|
|
AND docbow_seendocument.user_id = %s'''
|