/** 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;
}
