function fixMySQLCollations()
{
global $modifyoutput;
$oDB = Yii::app()->db;
$sql = 'SHOW TABLE STATUS';
$dbprefix = Yii::app()->getDb()->tablePrefix;
$result = Yii::app()->getDb()->createCommand($sql)->queryAll();
foreach ($result as $tables) {
// Loop through all tables in this database
$table = $tables['Name'];
$tablecollation = $tables['Collation'];
if (strpos($table, 'old_') === false && ($dbprefix == '' || $dbprefix != '' && strpos($table, $dbprefix) !== false)) {
if ($tablecollation != 'utf8_unicode_ci') {
modifyDatabase("", "ALTER TABLE {$table} COLLATE utf8_unicode_ci");
echo $modifyoutput;
flush();
@ob_flush();
}
# Now loop through all the fields within this table
$result2 = Yii::app()->getDb()->createCommand("SHOW FULL COLUMNS FROM " . $table)->queryAll();
foreach ($result2 as $column) {
if ($column['Collation'] != 'utf8_unicode_ci') {
$field_name = $column['Field'];
$field_type = $column['Type'];
$field_default = $column['Default'];
if ($field_default != 'NULL') {
$field_default = "'" . $field_default . "'";
}
# Change text based fields
$skipped_field_types = array('char', 'text', 'enum', 'set');
foreach ($skipped_field_types as $type) {
if (strpos($field_type, $type) !== false) {
$modstatement = "ALTER TABLE {$table} CHANGE `{$field_name}` `{$field_name}` {$field_type} CHARACTER SET utf8 COLLATE utf8_unicode_ci";
if ($type != 'text') {
$modstatement .= " DEFAULT {$field_default}";
}
modifyDatabase("", $modstatement);
echo $modifyoutput;
flush();
@ob_flush();
}
}
}
}
}
}
}
function mssql_drop_constraint($fieldname, $tablename)
{
global $modifyoutput;
Yii::app()->loadHelper("database");
// find out the name of the default constraint
// Did I already mention that this is the most suckiest thing I have ever seen in MSSQL database?
$dfquery = "SELECT c_obj.name AS constraint_name\n FROM sys.sysobjects AS c_obj INNER JOIN\n sys.sysobjects AS t_obj ON c_obj.parent_obj = t_obj.id INNER JOIN\n sys.sysconstraints AS con ON c_obj.id = con.constid INNER JOIN\n sys.syscolumns AS col ON t_obj.id = col.id AND con.colid = col.colid\n WHERE (c_obj.xtype = 'D') AND (col.name = '{$fieldname}') AND (t_obj.name='{{{$tablename}}}')";
$result = dbExecuteAssoc($dfquery)->read();
$defaultname = $result['CONTRAINT_NAME'];
if ($defaultname != false) {
modifyDatabase("", "ALTER TABLE {{{$tablename}}} DROP CONSTRAINT {$defaultname[0]}");
echo $modifyoutput;
flush();
}
}
function upgradeQuestionAttributes142()
{
global $modifyoutput;
$attributequery = "Select qid from {{question_attributes}} where attribute='exclude_all_other' group by qid having count(qid)>1 ";
$questionids = Yii::app()->getDb()->createCommand($attributequery)->queryRow();
if (!is_array($questionids)) {
return "Database Error";
} else {
foreach ($questionids as $questionid) {
//Select all affected question attributes
$attributevalues = Yii::app()->getDb()->createCommand("SELECT value from {{question_attributes}} where attribute='exclude_all_other' and qid=" . $questionid)->queryColumn();
modifyDatabase("", "delete from {{question_attributes}} where attribute='exclude_all_other' and qid=" . $questionid);
echo $modifyoutput;
flush();
@ob_flush();
$record['value'] = implode(';', $attributevalues);
$record['attribute'] = 'exclude_all_other';
$record['qid'] = $questionid;
Yii::app()->getDb()->createCommand()->insert('{{question_attributes}}', $record)->execute();
}
}
}
