function doGetAnnotation($id)
{
global $CFG;
// Check whether the range column exists (for backwards compatibility)
$range = '';
if (!column_type($this->tablePrefix . 'annotation', 'range')) {
$range = ', a.range AS range ';
}
// Caller should ensure that id is numeric
$query = "SELECT a.id, a.userid, a.url,\n\t\t\t a.start_block, a.start_xpath, a.start_word, a.start_char,\n\t\t\t a.end_block, a.end_xpath, a.end_word, a.end_char,\n\t\t\t a.note, a.access, a.quote, a.quote_title, a.quote_author,\n\t\t\t a.link, a.link_title, a.action,\n\t\t\t a.created, a.modified {$range}\n\t\t\t FROM {$this->tablePrefix}annotation AS a\n\t\t\tWHERE a.id = {$id}";
$resultSet = get_record_sql($query);
if ($resultSet && count($resultSet) != 0) {
$annotation = AnnotationGlobals::recordToAnnotation($resultSet);
return $annotation;
} else {
return null;
}
}
/**
* Add a new field to a table, or modify an existing one (if oldfield is defined).
*
* @uses $CFG
* @uses $db
* @param string $table ?
* @param string $oldfield ?
* @param string $field ?
* @param string $type ?
* @param string $size ?
* @param string $signed ?
* @param string $default ?
* @param string $null ?
* @todo Finish documenting this function
*/
function table_column($table, $oldfield, $field, $type = 'integer', $size = '10', $signed = 'unsigned', $default = '0', $null = 'not null', $after = '')
{
global $CFG, $db;
if (empty($oldfield) && !empty($field)) {
//adding
// check it doesn't exist first.
if ($columns = $db->MetaColumns($CFG->prefix . $table)) {
foreach ($columns as $c) {
if ($c->name == $field) {
$oldfield = $field;
}
}
}
}
switch (strtolower($CFG->dbtype)) {
case 'mysql':
case 'mysqlt':
switch (strtolower($type)) {
case 'text':
$type = 'TEXT';
$signed = '';
break;
case 'integer':
$type = 'INTEGER(' . $size . ')';
break;
case 'varchar':
$type = 'VARCHAR(' . $size . ')';
$signed = '';
break;
case 'char':
$type = 'CHAR(' . $size . ')';
$signed = '';
break;
}
if (!empty($oldfield)) {
$operation = 'CHANGE ' . $oldfield . ' ' . $field;
} else {
$operation = 'ADD ' . $field;
}
$default = 'DEFAULT \'' . $default . '\'';
if (!empty($after)) {
$after = 'AFTER `' . $after . '`';
}
return execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ' . $operation . ' ' . $type . ' ' . $signed . ' ' . $default . ' ' . $null . ' ' . $after);
case 'postgres7':
// From Petri Asikainen
//Check db-version
$dbinfo = $db->ServerInfo();
$dbver = substr($dbinfo['version'], 0, 3);
//to prevent conflicts with reserved words
$realfield = '"' . $field . '"';
$field = '"' . $field . '_alter_column_tmp"';
$oldfield = '"' . $oldfield . '"';
switch (strtolower($type)) {
case 'tinyint':
case 'integer':
if ($size <= 4) {
$type = 'INT2';
}
if ($size <= 10) {
$type = 'INT';
}
if ($size > 10) {
$type = 'INT8';
}
break;
case 'varchar':
$type = 'VARCHAR(' . $size . ')';
break;
case 'char':
$type = 'CHAR(' . $size . ')';
$signed = '';
break;
}
$default = '\'' . $default . '\'';
//After is not implemented in postgesql
//if (!empty($after)) {
// $after = "AFTER '$after'";
//}
//Use transactions
execute_sql('BEGIN');
//Always use temporary column
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ADD COLUMN ' . $field . ' ' . $type);
//Add default values
execute_sql('UPDATE ' . $CFG->prefix . $table . ' SET ' . $field . '=' . $default);
if ($dbver >= '7.3') {
// modifying 'not null' is posible before 7.3
//update default values to table
if (strtoupper($null) == 'NOT NULL') {
execute_sql('UPDATE ' . $CFG->prefix . $table . ' SET ' . $field . '=' . $default . ' WHERE ' . $field . ' IS NULL');
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ALTER COLUMN ' . $field . ' SET ' . $null);
} else {
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ALTER COLUMN ' . $field . ' DROP NOT NULL');
}
}
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ALTER COLUMN ' . $field . ' SET DEFAULT ' . $default);
if ($oldfield != '""') {
// We are changing the type of a column. This may require doing some casts...
$casting = '';
$oldtype = column_type($table, $oldfield);
$newtype = column_type($table, $field);
// Do we need a cast?
if ($newtype == 'N' && $oldtype == 'C') {
$casting = 'CAST(CAST(' . $oldfield . ' AS TEXT) AS REAL)';
} else {
if ($newtype == 'I' && $oldtype == 'C') {
$casting = 'CAST(CAST(' . $oldfield . ' AS TEXT) AS INTEGER)';
} else {
$casting = $oldfield;
}
}
// Run the update query, casting as necessary
execute_sql('UPDATE ' . $CFG->prefix . $table . ' SET ' . $field . ' = ' . $casting);
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' DROP COLUMN ' . $oldfield);
}
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' RENAME COLUMN ' . $field . ' TO ' . $realfield);
return execute_sql('COMMIT');
default:
switch (strtolower($type)) {
case 'integer':
$type = 'INTEGER';
break;
case 'varchar':
$type = 'VARCHAR';
break;
}
$default = 'DEFAULT \'' . $default . '\'';
if (!empty($after)) {
$after = 'AFTER ' . $after;
}
if (!empty($oldfield)) {
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' RENAME COLUMN ' . $oldfield . ' ' . $field);
} else {
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ADD COLUMN ' . $field . ' ' . $type);
}
execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ALTER COLUMN ' . $field . ' SET ' . $null);
return execute_sql('ALTER TABLE ' . $CFG->prefix . $table . ' ALTER COLUMN ' . $field . ' SET ' . $default);
}
}
/**
* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
* PostgreSQL has some severe limits:
* - Any change of type or precision requires a new temporary column to be created, values to
* be transfered potentially casting them, to apply defaults if the column is not null and
* finally, to rename it
* - Changes in null/not null require the SET/DROP NOT NULL clause
* - Changes in default require the SET/DROP DEFAULT clause
*/
function getAlterFieldSQL($xmldb_table, $xmldb_field)
{
global $db;
$results = array();
/// To store all the needed SQL commands
/// Get the quoted name of the table and field
$tablename = $this->getTableName($xmldb_table);
$fieldname = $this->getEncQuoted($xmldb_field->getName());
/// Take a look to field metadata
$meta = array_change_key_case($db->MetaColumns($tablename));
$metac = $meta[$fieldname];
$oldtype = strtolower($metac->type);
$oldmetatype = column_type($xmldb_table->getName(), $fieldname);
$oldlength = $metac->max_length;
$olddecimals = empty($metac->scale) ? null : $metac->scale;
$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':');
$typechanged = true;
//By default, assume that the column type has changed
$precisionchanged = true;
//By default, assume that the column precision has changed
$decimalchanged = true;
//By default, assume that the column decimal has changed
$defaultchanged = true;
//By default, assume that the column default has changed
$notnullchanged = true;
//By default, assume that the column notnull has changed
$from_temp_fields = false;
//By default don't assume we are going to use temporal fields
/// Detect if we are changing the type of the column
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') {
$typechanged = false;
}
/// Detect if we are changing the precision
if ($xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || $oldlength == -1 || $xmldb_field->getLength() == $oldlength) {
$precisionchanged = false;
}
/// Detect if we are changing the decimals
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER || $xmldb_field->getType() == XMLDB_TYPE_CHAR || $xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || !$xmldb_field->getDecimals() || !$olddecimals || $xmldb_field->getDecimals() == $olddecimals) {
$decimalchanged = false;
}
/// Detect if we are changing the default
if ($xmldb_field->getDefault() === null && $olddefault === null || $xmldb_field->getDefault() === $olddefault || "'" . $xmldb_field->getDefault() . "'" === $olddefault) {
//Equality with quotes because ADOdb returns the default with quotes
$defaultchanged = false;
}
/// Detect if we are changing the nullability
if ($xmldb_field->getNotnull() === $oldnotnull) {
$notnullchanged = false;
}
/// TODO: Some combinations like
/// TODO: integer->integer
/// TODO: integer->text
/// TODO: number->text
/// TODO: text->text
/// TODO: do not require the use of temp columns, because PG 8.0 supports them automatically
/// TODO: with a simple "alter table zzz alter column yyy type new specs"
/// TODO: Must be implemented that way. Eloy 09/2007
/// If the type or the precision or the decimals have changed, then we need to:
/// - create one temp column with the new specs
/// - fill the new column with the values from the old one (casting if needed)
/// - drop the old column
/// - rename the temp column to the original name
if ($typechanged || $precisionchanged || $decimalchanged) {
$tempcolname = $xmldb_field->getName() . '_alter_column_tmp';
/// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
$this->alter_column_skip_notnull = true;
$this->alter_column_skip_default = true;
$xmldb_field->setName($tempcolname);
/// Create the temporal column
$results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field));
/// Detect some basic casting options
if (substr($oldmetatype, 0, 1) == 'C' && $xmldb_field->getType() == XMLDB_TYPE_NUMBER || substr($oldmetatype, 0, 1) == 'C' && $xmldb_field->getType() == XMLDB_TYPE_FLOAT) {
$copyorigin = 'CAST(CAST(' . $fieldname . ' AS TEXT) AS REAL)';
//From char to number or float
} else {
if (substr($oldmetatype, 0, 1) == 'C' && $xmldb_field->getType() == XMLDB_TYPE_INTEGER) {
$copyorigin = 'CAST(CAST(' . $fieldname . ' AS TEXT) AS INTEGER)';
//From char to integer
} else {
$copyorigin = $fieldname;
//Direct copy between columns
}
}
/// Copy contents from original col to the temporal one
$results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $copyorigin;
/// Drop the old column
$xmldb_field->setName($fieldname);
//Set back the original field name
$results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
/// Rename the temp column to the original one
$results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
/// Mark we have performed one change based in temp fields
$from_temp_fields = true;
}
/// If the default has changed or we have used one temp field
if ($defaultchanged || $from_temp_fields) {
if ($default_clause = $this->getDefaultClause($xmldb_field)) {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause;
/// Add default clause
} else {
if (!$from_temp_fields) {
/// Only drop default if we haven't used the temp field, i.e. old column
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT';
/// Drop default clause
}
}
}
/// If the not null has changed or we have used one temp field
if ($notnullchanged || $from_temp_fields) {
if ($xmldb_field->getNotnull()) {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL';
} else {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP NOT NULL';
}
}
/// Return the results
return $results;
}
/**
* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
*/
function getAlterFieldSQL($xmldb_table, $xmldb_field)
{
global $db;
$results = array();
/// To store all the needed SQL commands
/// Get the quoted name of the table and field
$tablename = $this->getTableName($xmldb_table);
$fieldname = $this->getEncQuoted($xmldb_field->getName());
/// Take a look to field metadata
$meta = array_change_key_case($db->MetaColumns($tablename));
$metac = $meta[$fieldname];
$oldtype = strtolower($metac->type);
$oldmetatype = column_type($xmldb_table->getName(), $fieldname);
$oldlength = $metac->max_length;
$olddecimals = empty($metac->scale) ? null : $metac->scale;
$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':');
$typechanged = true;
//By default, assume that the column type has changed
$lengthchanged = true;
//By default, assume that the column length has changed
/// Detect if we are changing the type of the column
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') {
$typechanged = false;
}
/// Detect if we are changing the length of the column, not always necessary to drop defaults
/// if only the length changes, but it's safe to do it always
if ($xmldb_field->getLength() == $oldlength) {
$lengthchanged = false;
}
/// If type or length have changed drop the default if exists
if ($typechanged || $lengthchanged) {
$results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field);
}
/// Just prevent default clauses in this type of sentences for mssql and launch the parent one
$this->alter_column_skip_default = true;
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field));
// Call parent
/// Finally, process the default clause to add it back if necessary
if ($typechanged || $lengthchanged) {
$results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field));
}
/// Return results
return $results;
}
$default = isset($value['default']) ? $value['default'] : '';
$default = is_numeric($default) ? $default : strpos($default, "'") !== false ? $default : "'{$default}'";
$s_type_options = $s_num_options = '';
column_type($s_type_options, $s_num_options, $type, $num);
$template->assign_block_vars('columns', array('TABLE' => isset($value['table']) ? $value['table'] : '', 'ITEM' => $key + 1, 'NAME' => isset($value['name']) ? $value['name'] : '', 'TYPE_OPTIONS' => $s_type_options, 'TYPE' => $type, 'NUM_OPTIONS' => $s_num_options, 'NUM' => $num, 'DEFAULT' => $default, 'OPTION' => isset($value['option']) ? ' checked="checked"' : ''));
}
foreach ($table_add as $key => $value) {
if (!contains_data($value, array('name', 'type'), $table_name)) {
continue;
}
$type = isset($value['type']) ? $value['type'] : '';
$num = isset($value['num']) ? $value['num'] : 0;
$default = isset($value['default']) ? $value['default'] : '';
$default = is_numeric($default) ? $default : strpos($default, "'") !== false ? $default : "'{$default}'";
$s_type_options = $s_num_options = '';
column_type($s_type_options, $s_num_options, $type, $num);
$template->assign_block_vars('table', array('ITEM' => $key + 1, 'NAME' => isset($value['name']) ? $value['name'] : '', 'TYPE_OPTIONS' => $s_type_options, 'TYPE' => strpos($type, '%d') !== false ? $type . (int) $num : $type, 'NUM_OPTIONS' => $s_num_options, 'NUM' => $num, 'DEFAULT' => $default, 'OPTION' => isset($value['option']) ? ' checked="checked"' : ''));
}
foreach ($table_keys as $key => $value) {
if (!contains_data($value, array('index', 'type', 'column'))) {
continue;
}
$type = isset($value['type']) ? $value['type'] : '';
if ($type == 'PRIMARY' && $submit) {
$template->assign_block_vars('primary_keys', array('MULTIPLE_KEYS' => isset($value['column2']) ? true : false, 'COLUMN' => isset($value['column']) ? $value['column'] : '', 'COLUMN2' => isset($value['column2']) ? $value['column2'] : ''));
}
$template->assign_block_vars('table_keys', array('ITEM' => $key + 1, 'INDEX' => isset($value['index']) ? $value['index'] : '', 'TYPE' => $type, 'TYPE_OPTIONS' => index_type($type), 'COLUMN' => isset($value['column']) ? $value['column'] : '', 'COLUMN2' => isset($value['column2']) ? $value['column2'] : ''));
}
foreach ($index_add as $key => $value) {
if (!contains_data($value, array('table', 'index', 'column'))) {
continue;
}
# Adding the column to the structure table
$alter_queries[] = "\tADD COLUMN `" . $column->name . "` " . $column->type . $column->default . $column->comment . $column->position;
}
break;
# Changing a column
# Changing a column
case 'modify':
# Making sure we do have the column
if (isset($exist_data['COLUMN_NAME'])) {
# Sanatizing the columns
$column->default = $column->default == 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP' : (isset($column->default) && $column->default != '' ? $column->default : "");
$column->rename = !in_array($column->name, explode(',', NQ_LOCKED_FIELDS)) && isset($column->rename) && $column->rename != '' ? $column->rename : $column->name;
$renamed = $column->rename;
$column->rename = "`" . mysqli_escape_string($G_CONTROLLER_DBLINK, $column->rename) . "` ";
$column->type = column_type($column->type, $column->length, $column->values, $G_CONTROLLER_DBLINK) . " NOT NULL ";
$column->is_bit = substr($column->type, 0, 3) == 'BIT' ? 'b' : '';
$column->default = isset($column->default) && $column->default != '' ? " DEFAULT " . $column->is_bit . (substr($column->type, 0, 3) == 'BIT' ? boolval_ext($column->default) ? "'1'" : "'0'" : '"' . $column->default . '"') . " " : '';
$column->comment = isset($column->comment) ? "COMMENT '" . mysqli_escape_string($G_CONTROLLER_DBLINK, $column->comment) . "'" : '';
# Positioning the
if (isset($column->position)) {
if (strtolower($column->position) == 'first') {
$column->position = ' FIRST';
} elseif (strtolower($column->position) == 'after' && isset($column->position_column)) {
$column->position = ' AFTER `' . str_replace('`', '', $column->position_column) . '`';
}
}
# Altering the column in the structure table
$alter_queries[] = "\tCHANGE COLUMN `" . $column->name . "` " . $column->rename . $column->type . $column->default . $column->comment . $column->position;
}
break;
/**
* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
* Oracle has some severe limits:
* - clob and blob fields doesn't allow type to be specified
* - error is dropped if the null/not null clause is specified and hasn't changed
* - changes in precision/decimals of numeric fields drop an ORA-1440 error
*/
function getAlterFieldSQL($xmldb_table, $xmldb_field)
{
global $db;
$results = array();
/// To store all the needed SQL commands
/// Get the quoted name of the table and field
$tablename = $this->getTableName($xmldb_table);
$fieldname = $this->getEncQuoted($xmldb_field->getName());
/// Take a look to field metadata
$meta = array_change_key_case($db->MetaColumns($tablename));
$metac = $meta[$fieldname];
$oldtype = strtolower($metac->type);
$oldmetatype = column_type($xmldb_table->getName(), $fieldname);
$oldlength = $metac->max_length;
/// To calculate the oldlength if the field is numeric, we need to perform one extra query
/// because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883
if ($oldmetatype == 'N') {
$uppertablename = strtoupper($tablename);
$upperfieldname = strtoupper($fieldname);
if ($col = get_record_sql("SELECT cname, precision\n FROM col\n WHERE tname = '{$uppertablename}'\n AND cname = '{$upperfieldname}'")) {
$oldlength = $col->precision;
}
}
$olddecimals = empty($metac->scale) ? null : $metac->scale;
$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
$olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value;
$typechanged = true;
//By default, assume that the column type has changed
$precisionchanged = true;
//By default, assume that the column precision has changed
$decimalchanged = true;
//By default, assume that the column decimal has changed
$defaultchanged = true;
//By default, assume that the column default has changed
$notnullchanged = true;
//By default, assume that the column notnull has changed
$from_temp_fields = false;
//By default don't assume we are going to use temporal fields
/// Detect if we are changing the type of the column
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') {
$typechanged = false;
}
/// Detect if precision has changed
if ($xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || $oldlength == -1 || $xmldb_field->getLength() == $oldlength) {
$precisionchanged = false;
}
/// Detect if decimal has changed
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER || $xmldb_field->getType() == XMLDB_TYPE_CHAR || $xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || !$xmldb_field->getDecimals() || !$olddecimals || $xmldb_field->getDecimals() == $olddecimals) {
$decimalchanged = false;
}
/// Detect if we are changing the default
if ($xmldb_field->getDefault() === null && $olddefault === null || $xmldb_field->getDefault() === $olddefault || "'" . $xmldb_field->getDefault() . "'" === $olddefault) {
//Equality with quotes because ADOdb returns the default with quotes
$defaultchanged = false;
}
/// Detect if we are changing the nullability
if ($xmldb_field->getNotnull() === $oldnotnull) {
$notnullchanged = false;
}
/// If type has changed or precision or decimal has changed and we are in one numeric field
/// - create one temp column with the new specs
/// - fill the new column with the values from the old one
/// - drop the old column
/// - rename the temp column to the original name
if ($typechanged || $oldmetatype == 'N' && ($precisionchanged || $decimalchanged)) {
$tempcolname = $xmldb_field->getName() . '_alter_column_tmp';
/// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
$this->alter_column_skip_notnull = true;
$this->alter_column_skip_default = true;
$xmldb_field->setName($tempcolname);
/// Create the temporal column
$results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field));
/// Copy contents from original col to the temporal one
$results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname;
/// Drop the old column
$xmldb_field->setName($fieldname);
//Set back the original field name
$results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
/// Rename the temp column to the original one
$results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
/// Mark we have performed one change based in temp fields
$from_temp_fields = true;
/// Re-enable the notnull and default sections so the general AlterFieldSQL can use it
$this->alter_column_skip_notnull = false;
$this->alter_column_skip_default = false;
/// Dissable the type section because we have done it with the temp field
$this->alter_column_skip_type = true;
/// If new field is nullable, nullability hasn't changed
if (!$xmldb_field->getNotnull()) {
$notnullchanged = false;
}
/// If new field hasn't default, default hasn't changed
if ($xmldb_field->getDefault() === null) {
$defaultchanged = false;
}
}
/// If type and precision and decimals hasn't changed, prevent the type clause
if (!$typechanged && !$precisionchanged && !$decimalchanged) {
$this->alter_column_skip_type = true;
}
/// If NULL/NOT NULL hasn't changed
/// prevent null clause to be specified
if (!$notnullchanged) {
$this->alter_column_skip_notnull = true;
/// Initially, prevent the notnull clause
/// But, if we have used the temp field and the new field is not null, then enforce the not null clause
if ($from_temp_fields && $xmldb_field->getNotnull()) {
$this->alter_column_skip_notnull = false;
}
}
/// If default hasn't changed
/// prevent default clause to be specified
if (!$defaultchanged) {
$this->alter_column_skip_default = true;
/// Initially, prevent the default clause
/// But, if we have used the temp field and the new field has default clause, then enforce the default clause
if ($from_temp_fields && ($default_clause = $this->getDefaultClause($xmldb_field))) {
$this->alter_column_skip_default = false;
}
}
/// If arriving here, something is not being skiped (type, notnull, default), calculate the standar AlterFieldSQL
if (!$this->alter_column_skip_type || !$this->alter_column_skip_notnull || !$this->alter_column_skip_default) {
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field));
return $results;
}
/// Finally return results
return $results;
}
/**
* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
*/
function getAlterFieldSQL($xmldb_table, $xmldb_field)
{
global $db;
$results = array();
/// To store all the needed SQL commands
/// Get the quoted name of the table and field
$tablename = $this->getTableName($xmldb_table);
$fieldname = $this->getEncQuoted($xmldb_field->getName());
/// Take a look to field metadata
$meta = array_change_key_case($db->MetaColumns($tablename));
$metac = $meta[$fieldname];
$oldtype = strtolower($metac->type);
$oldmetatype = column_type($xmldb_table->getName(), $fieldname);
$oldlength = $metac->max_length;
$olddecimals = empty($metac->scale) ? null : $metac->scale;
$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':');
$typechanged = true;
//By default, assume that the column type has changed
$lengthchanged = true;
//By default, assume that the column length has changed
/// Detect if we are changing the type of the column
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') {
$typechanged = false;
}
/// If the new (and old) field specs are for integer, let's be a bit more specific diferentiating
/// types of integers. Else, some combinations can cause things like MDL-21868
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I') {
if ($xmldb_field->getLength() > 9) {
// Convert our new lenghts to detailed meta types
$newmssqlinttype = 'I8';
} else {
if ($xmldb_field->getLength() > 4) {
$newmssqlinttype = 'I';
} else {
$newmssqlinttype = 'I2';
}
}
if ($metac->type == 'bigint') {
// Convert current DB type to detailed meta type (adodb metatype is buggy!)
$oldmssqlinttype = 'I8';
} else {
if ($metac->type == 'smallint') {
$oldmssqlinttype = 'I2';
} else {
$oldmssqlinttype = 'I';
}
}
if ($newmssqlinttype != $oldmssqlinttype) {
// Compare new and old meta types
$typechanged = true;
// Change in meta type means change in type at all effects
}
}
/// Detect if we are changing the length of the column, not always necessary to drop defaults
/// if only the length changes, but it's safe to do it always
if ($xmldb_field->getLength() == $oldlength) {
$lengthchanged = false;
}
/// If type or length have changed drop the default if exists
if ($typechanged || $lengthchanged) {
$results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field);
}
/// Just prevent default clauses in this type of sentences for mssql and launch the parent one
$this->alter_column_skip_default = true;
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field));
// Call parent
/// Finally, process the default clause to add it back if necessary
if ($typechanged || $lengthchanged) {
$results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field));
}
/// Return results
return $results;
}
/**
* This takes a list of handlers, each of which corresponds to a particular type of
* query (e.g. discussion forum), along with search fields for performing a search.
* It returns the SQL query string.
*
* $searchAccess can be public, private, or empty. Public annotations are available to
* *everyone*, not just course members or Moodle users.
*/
function sql($orderby)
{
global $CFG, $USER;
// The query is a UNION of separate queries, one for each type of annotation
// This is unfortunate: with a common table structure, one for parent-child
// URL relationships, another with URL properties (title and owner would
// suffice), would forgo UNIONs and simplify this code.
// Users can only see their own annotations or the public annotations of others
// This is an awfully complex combination of conditions. I'm wondering if that's
// a design flaw.
$access_cond = null;
$desc_users = '';
// this was originally intended to allow more than one handler to respond to a request.
// That may still be necessary someday, but perhaps a compound handler would be the
// best way to respond to it. I eliminated the handler list because YAGNI.
$handler = $this->handler;
// Conditions under which someone else's annotation would be visible to this user
$access_visible = "a.access='public'";
if (array_key_exists('username', $USER)) {
$access_visible .= " OR a.userid='" . addslashes($USER->username) . "'" . " OR a.access like '%author%' AND a.quote_author='" . addslashes($USER->username) . "'";
$handler->fetchMetadata();
// Don't know how this should work due to changes between Moodle 1.6 and Moodle 1.8:
//if ( $USER->teacher[ $handler->courseId ] )
// $access_visible .= " OR a.access like '%teacher%'";
}
// Filter annotations according to their owners
if (null == $this->searchUser) {
$access_cond = " ({$access_visible}) ";
} elseif ('*students' == $this->searchUser) {
$access_cond = " ({$access_visible}) AND a.userid in (" . "SELECT stu.username FROM mdl_user stu " . "INNER JOIN mdl_user_students AS sts ON stu.id=sts.userid " . "WHERE sts.course=" . $handler->courseId . ")";
} elseif ('*teachers' == $this->searchUser) {
$access_cond = " ({$access_visible}) AND a.userid in (" . "SELECT teu.username FROM mdl_user AS teu " . "INNER JOIN mdl_user_teachers tet ON teu.id=tet.userid " . "WHERE tet.course=" . $handler->courseId . ")";
} else {
if (!array_key_exists('username', $USER) || $USER->username != $this->searchUser) {
$access_cond = "({$access_visible})";
}
if ($access_cond) {
$access_cond .= ' AND ';
}
$access_cond .= "a.userid='" . addslashes($this->searchUser) . "'";
}
// These are the fields to use for a search; specific annotations may add more fields
$std_search_fields = array('a.note', 'a.quote', 'u.firstname', 'u.lastname');
$prefix = $CFG->prefix;
// Do handler-specific stuff
// Check whether the range column exists (for backwards compatibility)
$range = '';
if (!column_type($CFG->prefix . 'annotation', 'range')) {
$range = ', a.range AS range ';
}
// These that follow are standard fields, for which no page type exceptions can apply
$q_std_select = "SELECT a.id AS id, a.url AS url, a.userid AS userid, " . "a.start_block, a.start_xpath, a.start_word, a.start_char, " . "a.end_block, a.end_xpath, a.end_word, a.end_char, " . "a.link AS link, a.link_title AS link_title, a.action AS action, " . "a.access AS access, a.created, a.modified {$range}" . ",\n concat(u.firstname, ' ', u.lastname) AS note_author" . ",\n concat('{$CFG->wwwroot}/user/view.php?id=',u.id) AS note_author_url" . ",\n a.note note, a.quote, a.quote_title AS quote_title" . ",\n concat(qu.firstname, ' ', qu.lastname) AS quote_author" . ",\n concat('{$CFG->wwwroot}/user/view.php?id=',qu.id) AS quote_author_url";
// Standard tables apply to all (but note the outer join of user, which if gone
// should not steal the annotation from its owner):
$q_std_from = "\nFROM {$prefix}annotation AS a" . "\n INNER JOIN {$prefix}user u ON u.username=a.userid" . "\n LEFT OUTER JOIN {$prefix}user qu on qu.username=a.quote_author";
// This search is always limited by access
$q_std_where = "\nWHERE ({$access_cond})";
// Searching limits also; fields searched are not alone those of the annotation:
// add to them also those a page of this type might use.
if (null != $this->searchQuery && '' != $this->searchQuery) {
$search_cond = '';
$add_search_fields = $handler->getSearchFields();
$search_cond = '';
$queryWords = split(' ', $this->searchQuery);
foreach ($queryWords as $word) {
$sWord = addslashes($word);
foreach ($std_search_fields as $field) {
$search_cond .= $search_cond == '' ? "{$field} LIKE '%{$sWord}%'" : " OR {$field} LIKE '%{$sWord}%'";
}
foreach ($add_search_fields as $field) {
$search_cond .= " OR {$field} LIKE '%{$sWord}%'";
}
}
$q_std_where .= "\n AND ({$search_cond})";
}
// The handler must construct the query, which might be a single SELECT or a UNION of multiple SELECTs
$q = $handler->getSql($q_std_select, $q_std_from, $q_std_where, $orderby);
return $q;
}
function view()
{
global $CFG, $USER;
$edit = optional_param('edit', 0, PARAM_BOOL);
$saved = optional_param('saved', 0, PARAM_BOOL);
$print = optional_param('print', 0, PARAM_BOOL);
$context = get_context_instance(CONTEXT_MODULE, $this->cm->id);
require_capability('mod/assignment:view', $context);
$submission = $this->get_submission();
//We need to add an extra field to the submissions table, for feedback using video or audio
//we check if it exists here, and if not we add it. Justin 20100324
if ($submission) {
if (!column_type('assignment_submissions', 'poodllfeedback')) {
// add field to store media comments (audio or video) filename to students submissions
$sql = "ALTER TABLE " . $CFG->prefix . "assignment_submissions ADD poodllfeedback TEXT";
$result = execute_sql($sql);
}
}
//Justin
//Are we printing this or not
if ($print) {
if (TCPPDF_OLD) {
require_once $CFG->libdir . '/tcpdf/tcpdf.php';
} else {
require_once $CFG->libdir . '/newtcpdf/tcpdf.php';
}
$pdf = new tcpdf(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true);
// remove default header/footer
//old version of tcppdf
if (TCPPDF_OLD) {
$pdf->print_header = false;
$pdf->print_footer = false;
} else {
//new version of tcppdf
$pdf->setPrintHeader(false);
$pdf->setPrintFooter(false);
}
//set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setFont('freeserif', '', 10);
//make page
$pdf->AddPage();
//prepare html content
$options = new object();
$options->smiley = false;
$options->filter = false;
$strHtml = format_text($submission->data1, FORMAT_HTML, $options);
//print the thing
$pdf->writeHTML($strHtml, true, 0, true, 0);
//The I is for inline, meaning tell the browser to shopw not download it.
$pdf->output('document.pdf', 'I');
//$pdf->output();
return;
}
//Guest can not submit nor edit an assignment (bug: 4604)
if (!has_capability('mod/assignment:submit', $context)) {
$editable = null;
} else {
$editable = $this->isopen() && (!$submission || $this->assignment->resubmit || !$submission->timemarked);
}
//modify Justin 20090305, we don't want to add this extra step for users.
//If they can edit, and they haven't submitted anything, then lets just show the form.
//If they have submitted something, lets give them an extra step if ytthey want to submit
//to protect accidental overwrite of their submission.
// $editmode = ($editable and $edit);
$editmode = ($editable and !$submission || $edit);
if ($editmode) {
//guest can not edit or submit assignment
if (!has_capability('mod/assignment:submit', $context)) {
print_error('guestnosubmit', 'assignment');
}
}
add_to_log($this->course->id, "assignment", "view", "view.php?id={$this->cm->id}", $this->assignment->id, $this->cm->id);
/// prepare form and process submitted data
//load it with some info it needs to determine the params for chosho recorder.
//for voice then text, we need to know if we already have voice or not
if (empty($submission)) {
$mediapath = "";
} else {
$mediapath = $submission->data2;
}
$mform = new mod_assignment_poodllonline_edit_form(null, array("cm" => $this->cm, "assignment" => $this->assignment, "mediapath" => $mediapath));
$defaults = new object();
$defaults->id = $this->cm->id;
if (!empty($submission)) {
//we always use html editor: Justin 20090225
//if ($this->usehtmleditor) {
if (true) {
$options = new object();
$options->smiley = false;
$options->filter = false;
$defaults->text = format_text($submission->data1, FORMAT_HTML, $options);
$defaults->format = FORMAT_HTML;
} else {
$defaults->text = $submission->data1;
$defaults->format = $submission->data2;
}
}
$mform->set_data($defaults);
if ($mform->is_cancelled()) {
redirect('view.php?id=' . $this->cm->id);
}
if ($data = $mform->get_data()) {
// No incoming data?
if ($editable && $this->update_submission($data)) {
//TODO fix log actions - needs db upgrade
$submission = $this->get_submission();
add_to_log($this->course->id, 'assignment', 'upload', 'view.php?a=' . $this->assignment->id, $this->assignment->id, $this->cm->id);
$this->email_teachers($submission);
//redirect to get updated submission date and word count
redirect('view.php?id=' . $this->cm->id . '&saved=1');
} else {
// TODO: add better error message
notify(get_string("error"));
//submitting not allowed!
}
}
/// print header, etc. and display form if needed
if ($editmode) {
$this->view_header(get_string('editmysubmission', 'assignment'));
} else {
$this->view_header();
}
$this->view_intro();
$this->view_dates();
if ($saved) {
notify(get_string('submissionsaved', 'assignment'), 'notifysuccess');
}
if (has_capability('mod/assignment:submit', $context)) {
print_simple_box_start('center', '70%', '', 0, 'generalbox', 'poodllonline');
if ($editmode) {
if ($submission) {
//Show our students answer box
echo get_string('mysubmission', 'assignment_poodllonline');
print_simple_box_start('center', '50%', '', 0, 'generalbox', 'mysubmission');
//check if we need media output
switch ($this->assignment->var3) {
case OM_REPLYVOICEONLY:
//format and echo text that our Audio filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VOICE='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=audio,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
case OM_REPLYVIDEOONLY:
//format and echo text that our Video filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VIDEO='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=video,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
case OM_REPLYVOICETHENTEXT:
//format and echo text that our Audio filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VOICE='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=audio,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
case OM_REPLYVIDEOTHENTEXT:
//format and echo text that our Video filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VIDEO='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=video,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
}
//check if we need text output
switch ($this->assignment->var3) {
case OM_REPLYVOICETHENTEXT:
case OM_REPLYVIDEOTHENTEXT:
if (empty($submission->data1)) {
break;
} else {
echo "<br />";
}
case OM_REPLYTEXTONLY:
default:
echo format_text($submission->data1, FORMAT_HTML);
}
//Close our students answer box
print_simple_box_end();
}
$mform->display();
} else {
if ($submission) {
//Show our students answer box
echo get_string('mysubmission', 'assignment_poodllonline');
print_simple_box_start('center', '50%', '', 0, 'generalbox', 'mysubmission');
switch ($this->assignment->var3) {
case OM_REPLYVOICEONLY:
//format and echo text that our Audio filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VOICE='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=audio,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
case OM_REPLYVIDEOONLY:
//format and echo text that our Video filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VIDEO='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=video,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
case OM_REPLYVOICETHENTEXT:
//format and echo text that our Audio filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VOICE='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=audio,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
case OM_REPLYVIDEOTHENTEXT:
//format and echo text that our Video filter will pick and show in a player
//needs to be formatted as html for filter to pick it up
//echo format_text('{FMS:VIDEO='. $submission->data2.'}', FORMAT_HTML);
echo format_text('{POODLL:type=video,path=' . $submission->data2 . ',protocol=rtmp}', FORMAT_HTML);
break;
}
//check if we need text output
switch ($this->assignment->var3) {
case OM_REPLYVOICETHENTEXT:
case OM_REPLYVIDEOTHENTEXT:
if (empty($submission->data1)) {
break;
} else {
echo "<br />";
}
case OM_REPLYTEXTONLY:
default:
echo format_text($submission->data1, FORMAT_HTML);
}
//Close out students answer box
print_simple_box_end();
} else {
if (!has_capability('mod/assignment:submit', $context)) {
//fix for #4604
echo '<div style="text-align:center">' . get_string('guestnosubmit', 'assignment') . '</div>';
} else {
if ($this->isopen()) {
//fix for #4206
echo '<div style="text-align:center">' . get_string('emptysubmission', 'assignment') . '</div>';
}
}
}
}
print_simple_box_end();
if (!$editmode && $editable) {
echo "<div style='text-align:center'>";
print_single_button('view.php', array('id' => $this->cm->id, 'edit' => '1'), get_string('editmysubmission', 'assignment'));
echo "</div>";
}
//show a print buttonif it is text only and not edit mode
if ($this->assignment->var3 == OM_REPLYTEXTONLY && !$editmode) {
echo "<br /><div style='text-align:center'>";
echo "<a href='view.php?id=" . $this->cm->id . "&print=1' target='_new'>" . get_string('printthissubmission', 'assignment_poodllonline') . "</a>";
//The target tag is ignored by print_single_button so not using it
//print_single_button('view.php', array('id'=>$this->cm->id,'print'=>'1'),get_string('printthissubmission', 'assignment_poodllonline'),'get','_new');
echo "</div>";
}
//end of if printable
}
//end of if can submit
$this->view_feedback();
$this->view_footer();
}
function post_check_14()
{
global $dbh;
$coltype = column_type("maia_mail", "contents");
if (is_mysql()) {
$desired_type = 'longblob';
} else {
$desired_type = 'bytea';
}
if ($coltype != $desired_type) {
return array(false, "Column 'contents' is still type " . strtoupper($coltype));
} else {
return array(true, "");
}
}
