/** perform actual update to version 2012041900
 *
 * Changes between 2011093000 and 2012041900:
 *  - addition of the ckeditor-option in the site configuration table
 *
 * @param object &$output collects the html output
 * @return bool TRUE on success, FALSE otherwise
 */
function update_core_2012041900(&$output)
{
    global $CFG, $DB;
    $version = 2012041900;
    if ($CFG->version >= $version) {
        return TRUE;
    }
    //
    // 1 -- maybe change default editor to CKEditor
    //
    $table = 'config';
    $fields = array('extra' => 'options=ckeditor,fckeditor,plain', 'description' => 'Default rich text editor - USER-defined, default ckeditor');
    $where = array('name' => 'editor');
    if (!isset($CFG->editor) || $CFG->editor == 'fckeditor') {
        $fields['value'] = 'ckeditor';
    }
    if (db_update($table, $fields, $where) === FALSE) {
        $msg = sprintf("%s(): cannot update editor configuration: %s", __FUNCTION__, db_errormessage());
        logger($msg);
        $output->add_message(htmlspecialchars($msg));
        $output->add_message(t('update_core_error', 'admin', array('{VERSION}' => strval($version))));
        return FALSE;
    }
    //
    // 2 -- maybe add some additional fields to nodes, users
    //
    $addfielddefs = array('nodes:style' => array('name' => 'style', 'type' => 'text', 'notnull' => TRUE, 'comment' => 'additional style information to add AFTER static and area-level style'), 'users:skin' => array('name' => 'skin', 'type' => 'varchar', 'length' => 20, 'notnull' => TRUE, 'default' => 'base', 'comment' => 'preferred skin'));
    foreach ($addfielddefs as $table_field => $fielddef) {
        list($table, $field) = explode(':', $table_field);
        if (($DBResult = $DB->query(db_select_sql($table, $field), 1)) !== FALSE) {
            $DBResult->close();
            logger(sprintf("%s(): field '%s' already exists in '%s', skipping ALTER TABLE", __FUNCTION__, $field, $table));
        } else {
            $sql = sprintf('ALTER TABLE %s%s ADD COLUMN (%s)', $DB->prefix, $table, $DB->column_definition($fielddef));
            if (($retval = $DB->exec($sql)) === FALSE) {
                $msg = sprintf("%s(): cannot add field '%s' to table '%s': %s", __FUNCTION__, $field, $table, db_errormessage());
                logger($msg);
                $output->add_message(htmlspecialchars($msg));
                $output->add_message(t('update_core_error', 'admin', array('{VERSION}' => strval($version))));
                return FALSE;
            } else {
                logger(sprintf("%s(): success adding field '%s' to table '%s'", __FUNCTION__, $field, $table));
            }
        }
    }
    //
    // 3 -- maybe get rid of old field 'high_visibility'
    //
    if (($DBResult = $DB->query(db_select_sql('users', 'high_visibility'), 1)) === FALSE) {
        logger(sprintf("%s(): field 'high_visibility' no longer exists in 'users', skipping ALTER TABLE", __FUNCTION__));
    } else {
        $DBResult->close();
        $changes = array('base' => FALSE, 'textonly' => TRUE);
        foreach ($changes as $newval => $oldval) {
            if (($retval = db_update('users', array('skin' => $newval), array('high_visibility' => $oldval))) === FALSE) {
                $msg = sprintf("%s(): cannot update field 'users.skin' to '%s': %s", __FUNCTION__, $newval, db_errormessage());
                logger($msg);
                $output->add_message(htmlspecialchars($msg));
                $output->add_message(t('update_core_error', 'admin', array('{VERSION}' => strval($version))));
                return FALSE;
            } else {
                logger(sprintf("%s(): success setting 'users.skin' to '%s' in %d record(s)", __FUNCTION__, $newval, $retval));
            }
        }
        $sql = sprintf('ALTER TABLE %susers DROP COLUMN high_visibility', $DB->prefix);
        if (($retval = $DB->exec($sql)) === FALSE) {
            $msg = sprintf("%s(): cannot drop field 'high_visibility' from table 'users': %s", __FUNCTION__, db_errormessage());
            logger($msg);
            $output->add_message(htmlspecialchars($msg));
            $output->add_message(t('update_core_error', 'admin', array('{VERSION}' => strval($version))));
            return FALSE;
        } else {
            logger(sprintf("%s(): success dropping field 'high_visibility' from table 'users'", __FUNCTION__));
        }
    }
    //
    // 4 -- If all is well, we update the version number in the database AND in $CFG->version
    //
    return update_core_version($output, $version);
}
/** try to retrieve a valid area record based on values of requested area and requested node
 *
 * this determines which area to use. If the user specifies nothing (no area, no node), we
 * simply go for the default area or the first available area. If the user does specify an
 * area and/or a node, we use that information to get to the area. Note that if the user specifies
 * both area and node, the two should match. That is: you cannot specify a node from area X
 * and also request area Y: that yields no results. If only a node is specified, the area is
 * calculated from the area to which the node belongs.
 *
 * We let the database do most of the work by constructing and executing an appropriate SQL-statement.
 *
 * @param int|null $requested_area the area the user specified or NULL if no area specifically requested
 * @param int|null $requested_node the node the user specified or NULL if no node specifically requested
 * @return bool|array FALSE on error/not found, otherwise an array with a complete area record from database
 * @uses $DB
 */
function calculate_area($requested_area, $requested_node)
{
    global $DB;
    $tbl_areas = $DB->prefix . 'areas';
    $tbl_nodes = $DB->prefix . 'nodes';
    if (is_null($requested_node)) {
        if (is_null($requested_area)) {
            $where = array('is_active' => TRUE);
            $order = array('CASE WHEN (is_default = ' . SQL_TRUE . ') THEN 0 ELSE 1 END', 'sort_order');
            $sql = db_select_sql('areas', '*', $where, $order);
        } else {
            $where = array('is_active' => TRUE, 'area_id' => intval($requested_area));
            $sql = db_select_sql('areas', '*', $where);
        }
    } else {
        $sql = "SELECT a.* FROM {$tbl_areas} AS a INNER JOIN {$tbl_nodes} AS n ON a.area_id = n.area_id " . "WHERE (a.is_active = " . SQL_TRUE . ") AND (n.node_id = " . intval($requested_node) . ")";
        if (!is_null($requested_area)) {
            $sql .= " AND (a.area_id = " . intval($requested_area) . ")";
        }
    }
    if (($DBResult = $DB->query($sql, 1)) === FALSE) {
        logger("DEBUG [{$sql}]: error {$DB->errno}/'{$DB->error}'", WLOG_DEBUG);
        return FALSE;
    } elseif ($DBResult->num_rows != 1) {
        $DBResult->close();
        return FALSE;
    } else {
        $record = $DBResult->fetch_row_assoc();
        $DBResult->close();
        return $record;
    }
}
/** fetch all selected records from the database in one array
 *
 * @param string $tablename name of the table to select from (without prefix)
 * @param mixed $fields fieldname or array with fieldnames to select
 * @param mixed $where a single clause or an array with fieldnames => values ((without the WHERE keyword)
 * @param mixed $order fieldname or array with fieldnames to determine sort order (without ORDER BY keyword)
 * @param string $keyfield field to use as the key in the returned array or empty for 0-based numeric array key
 * @param int $limit the maximum number of records to retrieve
 * @param int $offset the number of records to skip initially
 * @return bool|array the selected records as an array of associative arrays or FALSE on error or not found
 * @uses db_select_sql
 */
function db_select_all_records($tablename, $fields, $where = '', $order = '', $keyfield = '', $limit = '', $offset = '')
{
    global $DB;
    $sql = db_select_sql($tablename, $fields, $where, $order);
    if (($DBResult = $DB->query($sql, $limit, $offset)) === FALSE) {
        if ($DB->debug) {
            trigger_error($DB->errno . '/\'' . $DB->error . '\'');
        }
        return FALSE;
    } else {
        $records = $DBResult->fetch_all_assoc($keyfield);
        $DBResult->close();
        return $records;
    }
}