summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorSascha Grossenbacher2009-11-19 20:32:43 (GMT)
committer Sascha Grossenbacher2009-11-19 20:32:43 (GMT)
commit5b3def3203926177c020e3d6f24193e604c16671 (patch)
treeab87c61368e6c5d36375deb16da071539e327de1
parent5c32916297db8d6dd95d8bc78e588384686b626d (diff)
#507680 by Berdir | nbz: Changed Document query fragments.
-rw-r--r--privatemsg.module57
-rw-r--r--privatemsg_filter/privatemsg_filter.module29
2 files changed, 77 insertions, 9 deletions
diff --git a/privatemsg.module b/privatemsg.module
index 32451e5..f0787db 100644
--- a/privatemsg.module
+++ b/privatemsg.module
@@ -1004,21 +1004,29 @@ function pm_preview($form, &$form_state) {
function privatemsg_sql_list(&$fragments, $account, $argument = 'list') {
$fragments['primary_table'] = '{pm_message} pm';
- // Load enabled columns
+ // Load enabled columns.
$fields = array_filter(variable_get('privatemsg_display_fields', array('participants')));
- // Required columns
+ // Required columns.
$fragments['select'][] = 'pmi.thread_id';
+ // We have to use MIN as the subject might not be the same in some threads.
+ // MIN() does not have a useful meaning except that it helps to correctly
+ // aggregate the thread on PostgreSQL.
$fragments['select'][] = 'MIN(pm.subject) as subject';
$fragments['select'][] = 'MAX(pm.timestamp) as last_updated';
+ // We use SUM so that we can count the number of unread messages.
$fragments['select'][] = 'SUM(pmi.is_new) as is_new';
+ // Select number of messages in the thread if the count is
+ // set to be displayed.
if (in_array('count', $fields)) {
- $fragments['select'][] = 'COUNT(distinct pmi.mid) as count'; // We only want the distinct number of messages in this thread.
+ $fragments['select'][] = 'COUNT(distinct pmi.mid) as count';
}
if (in_array('participants', $fields)) {
- // Query for a string with uid's, for example "1,6,7". This needs a subquery on PostgreSQL.
+ // Query for a string with uid's, for example "1,6,7".
+ // @todo: Replace this with a single query similiar to the tag list.
if ($GLOBALS['db_type'] == 'pgsql') {
+ // PostgreSQL does not know GROUP_CONCAT, so a subquery is required.
$fragments['select'][] = "array_to_string(array(SELECT DISTINCT textin(int4out(pmia.uid))
FROM {pm_index} pmia
WHERE pmia.thread_id = pmi.thread_id), ',') AS participants";
@@ -1032,8 +1040,10 @@ function privatemsg_sql_list(&$fragments, $account, $argument = 'list') {
if (in_array('thread_started', $fields)) {
$fragments['select'][] = 'MIN(pm.timestamp) as thread_started';
}
- // pm_index needs to be the first join.
+
$fragments['inner_join'][] = 'INNER JOIN {pm_index} pmi ON pm.mid = pmi.mid';
+
+ // Only load undeleted messages of the current user and group by thread.
$fragments['where'][] = 'pmi.uid = %d';
$fragments['query_args']['where'][] = $account->uid;
$fragments['where'][] = 'pmi.deleted = 0';
@@ -1057,7 +1067,7 @@ function privatemsg_sql_list(&$fragments, $account, $argument = 'list') {
* Account for which the messages should be loaded.
*/
function privatemsg_sql_load(&$fragments, $pmids, $account = NULL) {
- $fragments['primary_table'] = '{pm_message} pm'; // Our primary table
+ $fragments['primary_table'] = '{pm_message} pm';
$fragments['select'][] = "pm.mid";
$fragments['select'][] = "pm.author";
@@ -1069,6 +1079,7 @@ function privatemsg_sql_load(&$fragments, $pmids, $account = NULL) {
$fragments['select'][] = "pmi.thread_id";
$fragments['inner_join'][] = 'INNER JOIN {pm_index} pmi ON pm.mid = pmi.mid';
+ // Use IN() to load multiple messages at the same time.
$fragments['where'][] = 'pmi.mid IN (' . db_placeholders($pmids) . ')';
$fragments['query_args']['where'] += $pmids;
if ($account) {
@@ -1093,6 +1104,8 @@ function privatemsg_sql_load(&$fragments, $pmids, $account = NULL) {
function privatemsg_sql_messages(&$fragments, $threads, $account = NULL, $load_all = FALSE) {
$fragments['primary_table'] = '{pm_index} pmi';
+ // Use DISTINCT to only load each mid once even if the user is listed more
+ // than once as recipient.
$fragments['select'][] = 'DISTINCT(pmi.mid) as mid';
$fragments['where'][] = 'pmi.thread_id IN ('. db_placeholders($threads) .')';
$fragments['query_args']['where'] += $threads;
@@ -1103,6 +1116,7 @@ function privatemsg_sql_messages(&$fragments, $threads, $account = NULL, $load_a
$fragments['query_args']['where'][] = $account->uid;
}
if (!$load_all) {
+ // Also load deleted messages when requested.
$fragments['where'][] = 'pmi.deleted = 0';
}
$fragments['order_by'][] = 'pm.timestamp ASC';
@@ -1119,6 +1133,8 @@ function privatemsg_sql_messages(&$fragments, $threads, $account = NULL, $load_a
function privatemsg_sql_participants(&$fragments, $thread_id) {
$fragments['primary_table'] = '{pm_index} pmi';
+ // Only load each participant once since they are listed as recipient for
+ // every message of that thread.
$fragments['select'][] = 'DISTINCT(pmi.uid) AS uid';
$fragments['select'][] = 'u.name AS name';
@@ -1127,26 +1143,48 @@ function privatemsg_sql_participants(&$fragments, $thread_id) {
$fragments['query_args']['where'][] = $thread_id;
}
-
+/**
+ * Query definition to count unread messages.
+ *
+ * @param $fragments
+ * Query fragments array.
+ * @param $account
+ * User object for which the messages are being counted.
+ */
function privatemsg_sql_unread_count(&$fragments, $account) {
$fragments['primary_table'] = '{pm_index} pmi';
$fragments['select'][] = 'COUNT(DISTINCT thread_id) as unread_count';
+
+ // Only count new messages that have not been deleted.
$fragments['where'][] = 'pmi.deleted = 0';
$fragments['where'][] = 'pmi.is_new = 1';
$fragments['where'][] = 'pmi.uid = %d';
$fragments['query_args']['where'][] = $account->uid;
}
+/**
+ * Query definition to search for username autocomplete suggestions.
+ *
+ * @param $fragments
+ * Query fragments array.
+ * @param $search
+ * Which search string is currently searched for.
+ * @param $names
+ * Array of names not to be used as suggestions.
+ */
function privatemsg_sql_autocomplete(&$fragments, $search, $names) {
$fragments['primary_table'] = '{users} u';
$fragments['select'][] = 'u.name';
+ // Escape the % to get it through the placeholder replacement.
$fragments['where'][] = "u.name LIKE '%s'";
$fragments['query_args']['where'][] = $search .'%%';
if (!empty($names)) {
+ // If there are already names selected, exclude them from the suggestions.
$fragments['where'][] = "u.name NOT IN (". db_placeholders($names, 'text') .")";
$fragments['query_args']['where'] += $names;
}
+ // Only load active users and sort them by name.
$fragments['where'][] = 'u.status <> 0';
$fragments['order_by'][] = 'u.name ASC';
}
@@ -1163,14 +1201,19 @@ function privatemsg_sql_deleted(&$fragments, $days) {
$fragments['primary_table'] = '{pm_message} pm';
$fragments['select'][] = 'pm.mid';
+ // The lowest value is higher than 0 if all recipients have deleted a message.
$fragments['select'][] = 'MIN(pmi.deleted) as is_deleted';
+ // The time the most recent deletion happened.
$fragments['select'][] = 'MAX(pmi.deleted) as last_deleted';
$fragments['inner_join'][] = 'INNER JOIN {pm_index} pmi ON (pmi.mid = pm.mid)';
$fragments['group_by'][] = 'pm.mid';
+ // Ignore messages that have not been deleted by all users.
$fragments['having'][] = 'MIN(pmi.deleted) > 0';
+
+ // Only select messages that have been deleted more than n days ago.
$fragments['having'][] = 'MAX(pmi.deleted) < %d';
$fragments['query_args']['having'][] = time() - $days * 86400;
}
diff --git a/privatemsg_filter/privatemsg_filter.module b/privatemsg_filter/privatemsg_filter.module
index 3f0fd81..d088800 100644
--- a/privatemsg_filter/privatemsg_filter.module
+++ b/privatemsg_filter/privatemsg_filter.module
@@ -764,6 +764,11 @@ function privatemsg_filter_tags_autocomplete($string) {
}
/**
+ * @addtogroup sql
+ * @{
+ */
+
+/**
* Limit the user autocomplete for the filter widget.
*
* @param $fragments
@@ -775,8 +780,15 @@ function privatemsg_filter_tags_autocomplete($string) {
*/
function privatemsg_filter_privatemsg_sql_autocomplete_alter(&$fragments, $search, $names) {
global $user;
+ // arg(1) is an additional URL argument passed to the URL when only
+ // users that are listed as recipient for threads of that user should be
+ // displayed.
+ // @todo: Check if these results can be grouped to avoid unecessary loops.
if (arg(1) == 'filter') {
+ // JOIN on index entries where the to be selected user is a recipient.
$fragments['inner_join'][] = 'INNER JOIN {pm_index} pip ON pip.uid = u.uid';
+ // JOIN on rows where the current user is the recipient and that have the
+ // same mid as those above.
$fragments['inner_join'][] = 'INNER JOIN {pm_index} piu ON piu.uid = %d AND pip.mid = piu.mid';
$fragments['query_args']['join'][] = $user->uid;
}
@@ -801,13 +813,16 @@ function privatemsg_filter_sql_tags(&$fragments, $user = NULL, $threads = NULL,
$fragments['select'][] = 't.public';
if (!empty($threads)) {
+ // If the tag list needs to be for specific threads.
$fragments['select'][] = 'ti.thread_id';
$fragments['inner_join'][] = 'INNER JOIN {pm_tags_index} ti on ti.tag_id = t.tag_id';
$fragments['where'][] = 'ti.thread_id IN (' . db_placeholders($threads) . ')';
$fragments['query_args']['where'] += $threads;
}
else {
+ // Tag usage counter is only used when we select all tags.
$fragments['select'][] = 'COUNT(ti.thread_id) as count';
+ // LEFT JOIN so that unused tags are displayed too.
$fragments['inner_join'][] = 'LEFT JOIN {pm_tags_index} ti ON t.tag_id = ti.tag_id';
$fragments['group_by'][] = 't.tag_id';
$fragments['group_by'][] = 't.tag';
@@ -818,7 +833,6 @@ function privatemsg_filter_sql_tags(&$fragments, $user = NULL, $threads = NULL,
$fragments['query_args']['where'][] = $user->uid;
}
-
// Only select n tags per thread (ordered per tag_id), see
// http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
//
@@ -835,7 +849,9 @@ function privatemsg_filter_sql_tags(&$fragments, $user = NULL, $threads = NULL,
AND pmtic.tag_id < ti.tag_id) < %d';
$fragments['query_args']['where'][] = $limit;
}
- elseif (!empty($thread_id) || !empty($user)) {
+ elseif (!empty($threads) || !empty($user)) {
+ // Only add a sort when we are not loading the tags for the admin page.
+ // Sorting is handled through tablesort_sql() then.
$fragments['order_by'][] = 't.tag ASC';
}
}
@@ -856,18 +872,27 @@ function privatemsg_filter_sql_tags_autocomplete(&$fragments, $search, $tags) {
$fragments['primary_table'] = '{pm_tags} pmt';
$fragments['select'][] = 'pmt.tag';
$fragments['where'][] = "pmt.tag LIKE '%s'";
+ // Escape % to get through the placeholder replacement.
$fragments['query_args']['where'][] = $search .'%%';
if (!empty($tags)) {
+ // Exclude tags.
$fragments['where'][] = "pmt.tag NOT IN (". db_placeholders($tags, 'text') .")";
$fragments['query_args']['where'] += $tags;
}
+ // LEFT JOIN to be able to load public, unused tags.
$fragments['inner_join'][] = 'LEFT JOIN {pm_tags_index} pmti ON pmt.tag_id = pmti.tag_id AND pmti.uid = %d';
$fragments['query_args']['join'][] = $user->uid;
+ // Autocomplete should only display Tags used by that user or public tags.
+ // This is done to avoid information disclosure as part of tag names.
$fragments['where'][] = '(pmti.uid IS NOT NULL OR pmt.public = 1)';
$fragments['order_by'][] = 'pmt.tag ASC';
}
/**
+ * @}
+ */
+
+/**
* Implement hook_user().
*/
function privatemsg_filter_user($op, &$edit, &$account, $category = NULL) {