/** update the view count for page $node_id
 *
 * @param int $node_id the page (node) that need its view_count incremented with 1
 * @return void we assume that everything goes smooth, and if it doesn't: too bad.
 */
function update_view_count($node_id)
{
    global $DB;
    $datim = db_escape_and_quote(strftime("%Y-%m-%d %T"));
    $sql = 'UPDATE ' . $DB->prefix . 'nodes ' . 'SET view_count = view_count + 1, atime = ' . $datim . ' ' . 'WHERE node_id = ' . intval($node_id);
    return $DB->exec($sql);
}
/** workhorse for removing obsolete sessions from the database
 *
 * this logs and subsequently removes obsolete sessions from the sessions table
 * It is a workhorse function for both {@link dbsession_garbage_collection()}
 * and {@link dbsession_expire()}.
 *
 * Session records are removed when the $time_field in the sessions table
 * contains a date/time that is older than $seconds seconds ago. Before
 * the records are removed, we retrieve them and log pertinent information from
 * each one via logger(), for future reference.
 *
 * Note that we try to continue with deleting records, even if the logging appears
 * to have generated errors.
 *
 * @param int $seconds the period of time after which the session is obsolete
 * @param string $time_field the field to use for time comparisons: either 'atime' or 'ctime'
 * @return bool TRUE if everything went well, FALSE otherwise
 *
 */
function dbsession_remove_obsolete_sessions($seconds, $time_field)
{
    global $DB;
    $retval = TRUE;
    // assume success
    $xtime = strftime('%Y-%m-%d %T', time() - intval($seconds));
    $table_users = $DB->prefix . 'users';
    $table_sessions = $DB->prefix . 'sessions';
    $sql = "SELECT s.session_id, s.user_id, u.username, s.user_information, s.ctime, s.atime " . "FROM {$table_sessions} AS s LEFT JOIN {$table_users} AS u ON s.user_id = u.user_id " . "WHERE s.{$time_field} < " . db_escape_and_quote($xtime) . " " . "ORDER BY s.{$time_field}";
    $DBResult = $DB->query($sql);
    if ($DBResult === FALSE) {
        if ($DB->debug) {
            trigger_error($DB->errno . '/\'' . $DB->error . '\'');
        }
        $retval = FALSE;
    }
    if ($retval !== FALSE) {
        $records = $DBResult->fetch_all_assoc('session_id');
        $DBResult->close();
        if ($records === FALSE) {
            if ($DB->debug) {
                trigger_error($DB->errno . '/\'' . $DB->error . '\'');
            }
            $retval = FALSE;
        }
    }
    if ($retval === FALSE) {
        logger('dbsession_remove_obsolete_sessions(): errors retrieving obsolete sessions', WLOG_DEBUG);
    } elseif (sizeof($records) < 1) {
        logger('dbsession_remove_obsolete_sessions(): nothing to do', WLOG_DEBUG);
    } else {
        foreach ($records as $session_id => $record) {
            $msg = sprintf("session %d %s (%d seconds) [login %s(%d) from %s on %s, last access %s]", $record['session_id'], $time_field == 'ctime' ? 'terminated' : 'timed out', $seconds, empty($record['username']) ? '?' : $record['username'], $record['user_id'], $record['user_information'], $record['ctime'], $record['atime']);
            logger($msg);
        }
        logger('dbsession_remove_obsolete_sessions(): number of removed sessions: ' . sizeof($records), WLOG_DEBUG);
    }
    unset($records);
    $where = $time_field . ' < ' . db_escape_and_quote($xtime);
    if (db_delete('sessions', $where) === FALSE) {
        return FALSE;
    } else {
        return $retval;
    }
}
 /** add a message to message queue of 0 or more alerts
  *
  * this adds $alert_message to the message buffers of 0 or more alert accounts
  * The alerts that qualify to receive this addition via the alerts_areas_nodes table.
  * The logic in that table is as follows:
  *  - the area_id must match the area_id(s) (specified in $areas) OR 
  *    it must be 0 which acts as a wildcard for ALL areas
  *  - the node_id must match the node_id(s) specified in $nodes) OR
  *    it must be 0 which acts as a wildcard for ALL nodes
  * Also the account must be active and the flag for the area/node-combination
  * must be TRUE.
  *
  * As a rule this routine is called with a single area_id in $areas and
  * a collection of node_id's in $nodes. The nodes follow the path up through
  * the tree, in order to alert accounts that are only watching a section at
  * a higher level.
  * 
  * Example:
  * If user 'webmaster' adds new page, say 34, to subsection 8 in
  * section 4 in area 1, you get something like this:
  *
  *     queue_area_node_alert(1,array(8,4,34),'node 34 added','webmaster');
  *
  * The effect will be that all accounts with the following combinations of
  * area A and node N have the message added to their buffers:
  * A=0, N=1 - qualifies for all nodes in all areas
  * A=1, N=0 - qualifies for all nodes in area 1
  * A=1, N=4 - qualifies for node 4 in area 1
  * A=1, N=8 - qualifies for node 8 in area 1
  *
  * It is very well possible that no message is added at all if there is no
  * alert account watching the specified area and node (using wildcards or
  * otherwise).
  *
  * Near the end of this routine, we check the queue with pending messages,
  * and perhaps send out a few alerts. The number of messages that can be
  * sent from here is limited; we don't want to steal too much time from an
  * unsuspecting user. It is the task of cron.php to take care of eventually
  * sending the queued messages. However, sending only a few messages won't
  * be noticed. I hope.
  *
  * Note that this routine adds a timestamp to the message and, if it is 
  * specified, the name of the user.
  *
  * Also note that the messages are added to the buffer with the last message
  * at the top, it means that the receiver will travel back in time reading
  * the collection of messages. This is based on the assumption that the latest
  * messages sometimes override a previous message and therefore should be
  * read first.
  *
  * @param mixed $areas an array or a single int identifying the area(s) of interest
  * @param mixed $nodes an array or a single int identifying the node(s) of interest
  * @param string $message the message to add to the buffer of qualifying alert accounts
  * @param string $username (optional) the name of the user that initiated the action
  * @return void
  * @uses $DB;
  */
 function queue_area_node_alert($areas, $nodes, $alert_message, $username = '')
 {
     global $DB;
     // 0 -- massage the message, add a timestamp, optional username and an extra empty line
     $message = sprintf("%s%s\n%s\n\n", strftime('%Y-%m-%d %T'), empty($username) ? '' : ' (' . $username . ')', $alert_message);
     // 1 -- construct the area part of the statement
     // example where-clause (part 1/3): ((area_id = 0) OR (area_id = 1))
     $where_clause = '';
     if (!empty($areas)) {
         $where_clause_area = '(n.area_id = 0)';
         // area_id = 0 means 'any area' in this context
         if (is_array($areas)) {
             foreach ($areas as $area_id) {
                 $where_clause_area .= sprintf(' OR (n.area_id = %d)', intval($area_id));
             }
         } else {
             $where_clause_area .= sprintf(' OR (n.area_id = %d)', intval($areas));
         }
         $where_clause .= '(' . $where_clause_area . ') AND ';
     }
     // 2 -- construct the node part of the statement
     // example where-clause (part 2/3): ((node_id = 0) OR (node_id = 4) OR (node_id = 8) OR (node_id = 34))
     if (!empty($nodes)) {
         $where_clause_node = '(n.node_id = 0)';
         // node_id = 0 means 'any node' in this context
         if (is_array($nodes)) {
             foreach ($nodes as $node_id) {
                 $where_clause_node .= sprintf(' OR (n.node_id = %d)', intval($node_id));
             }
         } else {
             $where_clause_node .= sprintf(' OR (n.node_id = %d)', intval($nodes));
         }
         $where_clause .= '(' . $where_clause_node . ') AND ';
     }
     // 3 -- only send msgs to active alerts
     // example where-clause (part 3/3): combine previous two parts with check for active alert accounts/flags
     $where_clause .= '(a.is_active) AND (n.flag)';
     // 4 -- construct complete statement
     // Note that we also are constructing the update of the message field manually, so
     // we MUST take care of proper escaping and quoting. Also, the trick with $DB->concat()
     // complicates this SQL-statement, but alas this is necessary due to the non-standard way
     // MySQL interprets the string concatenation operator '||'. Aaarrrghhhhhh
     // (see http://troels.arvin.dk/db/rdbms/#functions-concat)
     $sql = sprintf('UPDATE %salerts AS a INNER JOIN %salerts_areas_nodes AS n ON a.alert_id = n.alert_id ' . 'SET a.message_buffer = %s, a.messages = a.messages + 1 ' . 'WHERE %s', $DB->prefix, $DB->prefix, $DB->concat(db_escape_and_quote($message), 'a.message_buffer'), $where_clause);
     $retval = $DB->exec($sql);
     if ($retval === FALSE) {
         logger(__CLASS__ . ": error queueing alerts: '" . db_errormessage() . "' with sql = {$sql}");
     } else {
         logger(__CLASS__ . ": number of alerts queued: {$retval}");
         logger(__FUNCTION__ . "(): sql = " . $sql, WLOG_DEBUG);
     }
     // Even if we did not add a message ourselves, we can 'steal' some time
     // of the current user and see if there are mails that need to be sent out.
     cron_send_queued_alerts(2);
     // limit processing to 2 messages at this time
 }
/** add 1 point to score for a particular IP-address and failed procedure, return the new score
 *
 * This records a login failure in a table and returns the the number
 * of failures for the specified procedure in the past T1 minutes.
 *
 * @param string $remote_addr the remote IP-address that is the origin of the failure
 * @param int $procedure indicates in which procedure the user failed
 * @param string $username extra information, could be useful for troubleshooting afterwards
 * @return int the current score
 */
function login_failure_increment($remote_addr, $procedure, $username = '')
{
    global $CFG;
    // this array used to validate $procedure _and_ to make a human readable description with logger()
    static $procedure_names = array(LOGIN_PROCEDURE_NORMAL => 'normal login', LOGIN_PROCEDURE_CHANGE_PASSWORD => 'change password', LOGIN_PROCEDURE_SEND_LAISSEZ_PASSER => 'send laissez passer', LOGIN_PROCEDURE_SEND_BYPASS => 'send bypass');
    $retval = 0;
    $procedure = intval($procedure);
    if (isset($procedure_names[$procedure])) {
        $now = strftime('%Y-%m-%d %T');
        $retval = db_insert_into('login_failures', array('remote_addr' => $remote_addr, 'datim' => $now, 'failed_procedure' => $procedure, 'points' => 1, 'username' => $username));
        if ($retval !== FALSE) {
            $minutes = intval($CFG->login_failures_interval);
            $interval_begin = strftime('%Y-%m-%d %T', time() - $minutes * 60);
            $where = 'remote_addr = ' . db_escape_and_quote($remote_addr) . ' AND failed_procedure = ' . $procedure . ' AND ' . db_escape_and_quote($interval_begin) . ' < datim';
            $record = db_select_single_record('login_failures', 'SUM(points) AS score', $where);
            if ($record !== FALSE) {
                $retval = intval($record['score']);
            } else {
                logger('could not calculate failure score', WLOG_DEBUG);
            }
        } else {
            logger('could not increment failure count', WLOG_DEBUG);
        }
        logger('login: failed; procedure=' . $procedure_names[$procedure] . ', count=' . $retval . ', username=\'' . $username . '\'');
    } else {
        logger('internal error: unknown procedure', WLOG_DEBUG);
    }
    return $retval;
}
/** send pending messages/alerts
 *
 * this goes through all the alert accounts to see if any messages need
 * to be sent out by email. The strategy is as follows.
 * First we collect a maximum of $max_messages alerts in in core 
 * (1 trip to the database) Then we iterate through that collection
 * and for every alert we
 *  1. construct and send an email message
 *  2. update the record (reset the message buffer 
 *     and message count) (+1 trip to the database)
 *
 * Locking and unlocking would be even more expensive, especially when
 * chances of race conditions are not so big. (An earlier version of
 * this routine went to the database once for the list of all pending
 * alerts and subsequently twice for each alert but eventually I
 * considered that too expensive too).
 *
 * Assuming that an UPDATE is more or less atomic, we hopefully
 * can get away with an UPDATE with a where clause looking explicitly
 * for the previous value of the message count. If a message was added
 * after retrieving the alerts but before updating, the message count
 * would be incremented (by the other process) which would prevent us from
 * updating. The alert would be left unchanged but including
 * the added message. Worst case: the receiver gets the same list of
 * alerts again and again. I consider that a fair trade off, given the
 * low probability of it happening. (Mmmm, famous last words...)
 *
 * Bottom line, we don't do locking in this routine.
 *
 * Note that we add a small reminder to the message buffer about
 * us processing the alert and sending a message. However, we don't
 * set the number of messages to 1 because otherwise that would be
 * the signal to sent this message the next time. We don't want
 * sent a message every $cron_interval minutes basically saying 
 * that we didn't do anything since the previous run. (Or is this
 * a feature after all?)
 *
 * Failures are logged, success are logged as WLOG_DEBUG.
 *
 * @param int $max_messages do not send more than this number of messages
 * @return int the number of messages that were processed
 */
function cron_send_queued_alerts($max_messages = 10)
{
    global $CFG;
    //
    // 1 -- any work to do at all?
    //
    $now = strftime('%Y-%m-%d %T');
    $table = 'alerts';
    $fields = '*';
    $where = '(messages > 0) AND (is_active = ' . SQL_TRUE . ') AND (cron_next <= ' . db_escape_and_quote($now) . ')';
    $order = 'cron_next';
    $keyfield = 'alert_id';
    $limit = max(1, intval($max_messages));
    // at least go for 1 alert
    if (($alerts = db_select_all_records($table, $fields, $where, $order, $keyfield, $limit)) === FALSE) {
        // ignore error
        logger(sprintf('%s(): error retrieving alerts: %s', __FUNCTION__, db_errormessage()));
        return 0;
    } elseif (sizeof($alerts) < 1) {
        // nothing to do
        logger(sprintf('%s(): nothing to do', __FUNCTION__), WLOG_DEBUG);
        return 0;
    }
    //
    // 2 -- yes, work to do: iterate through until at most $max_messages are sent
    //
    $alert_messages_sent = 0;
    /** make sure utility routines for creating/sending email messages are available */
    require_once $CFG->progdir . '/lib/email.class.php';
    $email = new Email();
    foreach ($alerts as $alert_id => $alert) {
        $messages = intval($alert['messages']);
        $mailto = $alert['email'];
        $full_name = $alert['full_name'];
        $email->set_mailto($mailto, $full_name);
        $email->set_subject(t('alerts_mail_subject', '', array('{ALERTS}' => $messages, '{SITENAME}' => $CFG->title)));
        $email->set_message(wordwrap($alert['message_buffer'], 70));
        if ($email->send()) {
            // alert was accepted, reset our message buffer, counter
            $cron_next = strftime('%Y-%m-%d %T', time() + 60 * intval($alert['cron_interval']));
            $continuation_line = $now . "\n" . t('alerts_processed', '', array('{ALERTS}' => $messages)) . "\n";
            $fields = array('cron_next' => $cron_next, 'messages' => 0, 'message_buffer' => $continuation_line);
            $where = array('alert_id' => $alert_id, 'messages' => $messages);
            // don't update if another message was added while we were working
            if (($retval = db_update('alerts', $fields, $where)) !== FALSE) {
                logger(sprintf('%s(): %d message(s) for %s (%s) (id=%d) sent; %d record(s) updated', __FUNCTION__, $messages, $mailto, $full_name, $alert_id, $retval), WLOG_DEBUG);
                ++$alert_messages_sent;
                if ($max_messages <= $alert_messages_sent) {
                    break;
                }
            } else {
                logger(sprintf('%s(): error with alert for %s (%s) (id=%d): ' . 'mail was sent, but record not reset. ' . 'Was another process updating this record while we were not looking?', __FUNCTION__, $mailto, $full_name, $alert_id));
            }
        } else {
            logger(sprintf('%s(): error: %d message(s) for %s (%s) (id=%d) NOT sent', __FUNCTION__, $messages, $mailto, $full_name, $alert_id));
        }
    }
    logger(sprintf('%s(): success processing %d alert(s)', __FUNCTION__, $alert_messages_sent));
    return $alert_messages_sent;
}
/** construct a where clause from string/array, including the word WHERE
 *
 * this constructs a where clause including the word 'WHERE' based on
 * the string or array $where. 
 *
 * The optional parameter $where is either a simple string with an appropriate
 * expression (without the keyword WHERE) or an array with fieldname/value-pairs.
 * In the latter case the clauses fieldname=value are AND'ed together.
 * If the specified values are string-like, they are properly quoted.
 * Boolean values are treated properly too. NULL-values yield a standard 'IS NULL'
 * type of expression.
 *
 *
 * @param mixed a single clause or an array with fieldnames => values ((without the WHERE keyword)
 * @return string empty string or a WHERE-clause with leading space and the word 'WHERE'
 */
function db_where_clause($where = '')
{
    $where_clause = '';
    if (!empty($where)) {
        if (is_string($where)) {
            $where_clause = ' WHERE ' . $where;
        } elseif (is_array($where)) {
            $where_clause = ' WHERE ';
            $glue = '';
            foreach ($where as $field => $value) {
                if (is_null($value)) {
                    $where_clause .= $glue . '(' . $field . ' IS NULL)';
                } else {
                    $where_clause .= $glue . '(' . $field . ' = ' . db_escape_and_quote($value) . ')';
                }
                $glue = ' AND ';
            }
        }
    }
    return $where_clause;
}