Drupal private messages database table design

Question

I am trying to get the raw message data from a Drupal instance running the Privatemsg module.

I have tracked the message data down to the pm_message and pm_index tables but I am struggling to understand the database structure / design.

I am trying to get the author and recipient users for a message, however in the majority of cases the pm_message.author and pm_index.recipient are exactly the same user. How do I get the author and recipient of a message from the database?

I think this maybe due to me not understanding threads in database design (can't see how thread_id is created or relevant), sample query I am running to get the data:

SELECT 
author, recipient, body, timestamp
FROM pm_message, pm_index
WHERE pm_message.mid = pm_index.mid

Answer 1

In general with Drupal you want to avoid directly querying the database if you didn't create the tables yourself. Instead try to use functions provided by core or the module to get the data for you. The table structures are often complicated and may change without notice, but the functions for accessing those tables generally adapt to the changes (this is more true of core than contrib modules, but is frequently true of both).

In the case of Privatemsg the Drupal 7 version (and probably 6) provide several useful functions to pull the message data (see the main module file). For example you can load an entire thread with privatemsg_thread_load:

/**
 * Load a thread with all the messages and participants.
 *
 * This function is called by the menu system through the %privatemsg_thread
 * wildcard.
 *
 * @param $thread_id
 *   Thread id, pmi.thread_id or pm.mid of the first message in that thread.
 * @param $account
 *   User object for which the thread should be loaded, defaults to
 *   the current user.
 * @param $start
 *   Message offset from the start of the thread.
 * @param $useAccessDenied
 *   Set to TRUE if the function should forward to the access denied page
 *   instead of not found. This is used by the menu system because that does
 *   load arguments before access checks are made. Defaults to FALSE.
 *
 * @return
 *   $thread object, with keys messages, participants, title and user. messages
 *   contains an array of messages, participants an array of user, subject the
 *   subject of the thread and user the user viewing the thread.
 *
 *   If no messages are found, or the thread_id is invalid, the function returns
 *   FALSE.

 * @ingroup api
 */
function privatemsg_thread_load($thread_id, $account = NULL, $start = NULL, $useAccessDenied = FALSE) {

There are also several functions that generate the query objects for pulling the module's data like privatemsg_sql_list:

/**
 * Query definition to load a list of threads.
 *
 * @param $account
 *  User object for which the messages are being loaded.
 * @param $argument
 *  string argument which can be used in the query builder to modify the thread listing.
 *
 * @see hook_query_privatemsg_list_alter()
 */
function privatemsg_sql_list($account, $argument = 'list') {