function _outputDBData($bAllowExportAllDb, $bEchoOutput, $sFileName, $oFile) { if ($bAllowExportAllDb) { $aTables = Yii::app()->db->getSchema()->getTableNames(); } else { $aTables = Yii::app()->db->createCommand(dbSelectTablesLike(addcslashes(Yii::app()->db->tablePrefix, '_') . "%"))->queryColumn(); } foreach ($aTables as $sTableName) { $oTableData = Yii::app()->db->getSchema()->getTable($sTableName); $sOutput = _outputTableDescription($sTableName); if ($bEchoOutput) { echo $sOutput; } if (!is_null($sFileName)) { fwrite($oFile, $sOutput); } _outputTableData($sTableName, $oTableData, $bEchoOutput, $sFileName, $oFile); } }
/** * Show dialogs and create a new tokens table */ function _newtokentable($iSurveyId) { $clang = $this->getController()->lang; $aSurveyInfo = getSurveyInfo($iSurveyId); if (!Permission::model()->hasSurveyPermission($iSurveyId, 'surveysettings', 'update') && !Permission::model()->hasSurveyPermission($iSurveyId, 'tokens', 'create')) { Yii::app()->session['flashmessage'] = $clang->gT("Tokens have not been initialised for this survey."); $this->getController()->redirect(array("/admin/survey/sa/view/surveyid/{$iSurveyId}")); } $bTokenExists = tableExists('{{tokens_' . $iSurveyId . '}}'); if ($bTokenExists) { Yii::app()->session['flashmessage'] = $clang->gT("Tokens already exist for this survey."); $this->getController()->redirect(array("/admin/survey/sa/view/surveyid/{$iSurveyId}")); } // The user have rigth to create token, then don't test right after Yii::import('application.helpers.admin.token_helper', true); if (Yii::app()->request->getQuery('createtable') == "Y") { createTokenTable($iSurveyId); LimeExpressionManager::SetDirtyFlag(); // LimeExpressionManager needs to know about the new token table $this->_renderWrappedTemplate('token', array('message' => array('title' => $clang->gT("Token control"), 'message' => $clang->gT("A token table has been created for this survey.") . " (\"" . Yii::app()->db->tablePrefix . "tokens_{$iSurveyId}\")<br /><br />\n" . "<input type='submit' value='" . $clang->gT("Continue") . "' onclick=\"window.open('" . $this->getController()->createUrl("admin/tokens/sa/index/surveyid/{$iSurveyId}") . "', '_top')\" />\n"))); } elseif (returnGlobal('restoretable') == "Y" && Yii::app()->request->getPost('oldtable')) { //Rebuild attributedescription value for the surveys table $table = Yii::app()->db->schema->getTable(Yii::app()->request->getPost('oldtable')); $fields = array_filter(array_keys($table->columns), 'filterForAttributes'); $fieldcontents = $aSurveyInfo['attributedescriptions']; if (!is_array($fieldcontents)) { $fieldcontents = array(); } foreach ($fields as $fieldname) { $name = $fieldname; if ($fieldname[10] == 'c') { //This belongs to a cpdb attribute $cpdbattid = substr($fieldname, 15); $data = ParticipantAttributeName::model()->getAttributeName($cpdbattid, Yii::app()->session['adminlang']); $name = $data['attribute_name']; } if (!isset($fieldcontents[$fieldname])) { $fieldcontents[$fieldname] = array('description' => $name, 'mandatory' => 'N', 'show_register' => 'N'); } } Survey::model()->updateByPk($iSurveyId, array('attributedescriptions' => serialize($fieldcontents))); Yii::app()->db->createCommand()->renameTable(Yii::app()->request->getPost('oldtable'), Yii::app()->db->tablePrefix . "tokens_" . intval($iSurveyId)); Yii::app()->db->schema->getTable(Yii::app()->db->tablePrefix . "tokens_" . intval($iSurveyId), true); // Refresh schema cache just in case the table existed in the past //Check that the tokens table has the required fields TokenDynamic::model($iSurveyId)->checkColumns(); //Add any survey_links from the renamed table SurveyLink::model()->rebuildLinksFromTokenTable($iSurveyId); $this->_renderWrappedTemplate('token', array('message' => array('title' => $clang->gT("Import old tokens"), 'message' => $clang->gT("A token table has been created for this survey and the old tokens were imported.") . " (\"" . Yii::app()->db->tablePrefix . "tokens_{$iSurveyId}" . "\")<br /><br />\n" . "<input type='submit' value='" . $clang->gT("Continue") . "' onclick=\"window.open('" . $this->getController()->createUrl("admin/tokens/sa/index/surveyid/{$iSurveyId}") . "', '_top')\" />\n"))); LimeExpressionManager::SetDirtyFlag(); // so that knows that token tables have changed } else { $this->getController()->loadHelper('database'); $result = Yii::app()->db->createCommand(dbSelectTablesLike("{{old_tokens_" . intval($iSurveyId) . "_%}}"))->queryAll(); $tcount = count($result); if ($tcount > 0) { foreach ($result as $rows) { $oldlist[] = reset($rows); } $aData['oldlist'] = $oldlist; } $thissurvey = getSurveyInfo($iSurveyId); $aData['thissurvey'] = $thissurvey; $aData['surveyid'] = $iSurveyId; $aData['tcount'] = $tcount; $aData['databasetype'] = Yii::app()->db->getDriverName(); $this->_renderWrappedTemplate('token', 'tokenwarning', $aData); } }
/** * Gets the table names. Do not prefix. * @param string $table String to match * @uses dbSelectTablesLike() To get the tables like sql query * @return array Array of matched table names */ function dbGetTablesLike($table) { return (array) Yii::app()->db->createCommand(dbSelectTablesLike("{{{$table}}}"))->queryAll(); }
/** * This function checks the LimeSurvey database for logical consistency and returns an according array * containing all issues in the particular tables. * @returns Array with all found issues. */ protected function _checkintegrity() { $clang = Yii::app()->lang; /*** Plainly delete survey permissions if the survey or user does not exist ***/ $users = User::model()->findAll(); $uids = array(); foreach ($users as $user) { $uids[] = $user['uid']; } $oCriteria = new CDbCriteria(); $oCriteria->addNotInCondition('uid', $uids, 'OR'); $surveys = Survey::model()->findAll(); $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $oCriteria->addNotInCondition('entity_id', $sids, 'OR'); $oCriteria->addCondition("entity='survey'"); Permission::model()->deleteAll($oCriteria); // Deactivate surveys that have a missing response table foreach ($surveys as $survey) { if ($survey['active'] == 'Y' && !tableExists("{{survey_{$survey['sid']}}}")) { Survey::model()->updateByPk($survey['sid'], array('active' => 'N')); } } unset($surveys); // Fix subquestions fixSubquestions(); /*** Check for active survey tables with missing survey entry and rename them ***/ $sDBPrefix = Yii::app()->db->tablePrefix; $sQuery = dbSelectTablesLike('{{survey}}\\_%'); $aResult = dbQueryOrFalse($sQuery); foreach ($aResult->readAll() as $aRow) { $sTableName = substr(reset($aRow), strlen($sDBPrefix)); if ($sTableName == 'survey_links' || $sTableName == 'survey_url_parameters') { continue; } $aTableName = explode('_', $sTableName); if (isset($aTableName[1]) && ctype_digit($aTableName[1])) { $iSurveyID = $aTableName[1]; if (!in_array($iSurveyID, $sids)) { $sDate = date('YmdHis') . rand(1, 1000); $sOldTable = "survey_{$iSurveyID}"; $sNewTable = "old_survey_{$iSurveyID}_{$sDate}"; try { $deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}"); } catch (CDbException $e) { die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />'); } } } } /*** Check for active token tables with missing survey entry ***/ $aResult = dbQueryOrFalse(dbSelectTablesLike('{{tokens}}\\_%')); foreach ($aResult->readAll() as $aRow) { $sTableName = substr(reset($aRow), strlen($sDBPrefix)); $iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1); if (!in_array($iSurveyID, $sids)) { $sDate = date('YmdHis') . rand(1, 1000); $sOldTable = "tokens_{$iSurveyID}"; $sNewTable = "old_tokens_{$iSurveyID}_{$sDate}"; try { $deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}"); } catch (CDbException $e) { die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />'); } } } /**********************************************************************/ /* Check conditions */ /**********************************************************************/ $okQuestion = array(); $sQuery = 'SELECT cqid,cid,cfieldname FROM {{conditions}}'; $aConditions = Yii::app()->db->createCommand($sQuery)->queryAll(); foreach ($aConditions as $condition) { if ($condition['cqid'] != 0) { // skip case with cqid=0 for codnitions on {TOKEN:EMAIL} for instance if (!array_key_exists($condition['cqid'], $okQuestion)) { $iRowCount = Question::model()->countByAttributes(array('qid' => $condition['cqid'])); if (Question::model()->hasErrors()) { safeDie(Question::model()->getError()); } if (!$iRowCount) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CQID')); } else { $okQuestion[$condition['cqid']] = $condition['cqid']; } } } if ($condition['cfieldname']) { if (preg_match('/^\\+{0,1}[0-9]+X[0-9]+X*$/', $condition['cfieldname'])) { // only if cfieldname isn't Tag such as {TOKEN:EMAIL} or any other token list($surveyid, $gid, $rest) = explode('X', $condition['cfieldname']); $iRowCount = count(QuestionGroup::model()->findAllByAttributes(array('gid' => $gid))); if (QuestionGroup::model()->hasErrors()) { safeDie(QuestionGroup::model()->getError()); } if (!$iRowCount) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CFIELDNAME group!') . " ({$gid}) ({$condition['cfieldname']})"); } } } elseif (!$condition['cfieldname']) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No CFIELDNAME field set!') . " ({$condition['cfieldname']})"); } } unset($okQuestion); unset($aConditions); /**********************************************************************/ /* Check question attributes */ /**********************************************************************/ $question_attributes = QuestionAttribute::model()->findAllBySql('select qid from {{question_attributes}} where qid not in (select qid from {{questions}})'); if (QuestionAttribute::model()->hasErrors()) { safeDie(QuestionAttribute::model()->getError()); } foreach ($question_attributes as $question_attribute) { $aDelete['questionattributes'][] = array('qid' => $question_attribute['qid']); } // foreach /**********************************************************************/ /* Check default values */ /**********************************************************************/ $oCriteria = new CDbCriteria(); $oCriteria->join = 'LEFT JOIN {{questions}} q ON t.qid=q.qid'; $oCriteria->condition = 'q.qid IS NULL'; $aRecords = DefaultValue::model()->findAll($oCriteria); $aDelete['defaultvalues'] = count($aRecords); if (DefaultValue::model()->hasErrors()) { safeDie(DefaultValue::model()->getError()); } /**********************************************************************/ /* Check quotas */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $oCriteria = new CDbCriteria(); $oCriteria->addNotInCondition('sid', $sids); $aDelete['quotas'] = count(Quota::model()->findAll($oCriteria)); if (Quota::model()->hasErrors()) { safeDie(Quota::model()->getError()); } /**********************************************************************/ /* Check quota languagesettings */ /**********************************************************************/ $quotas = Quota::model()->findAll(); if (Quota::model()->hasErrors()) { safeDie(Quota::model()->getError()); } $ids = array(); foreach ($quotas as $quota) { $ids[] = $quota['id']; } $oCriteria = new CDbCriteria(); $oCriteria->addNotInCondition('quotals_quota_id', $ids); $aDelete['quotals'] = count(QuotaLanguageSetting::model()->findAll($oCriteria)); if (QuotaLanguageSetting::model()->hasErrors()) { safeDie(QuotaLanguageSetting::model()->getError()); } /**********************************************************************/ /* Check quota members */ /**********************************************************************/ $oCriteria = new CDbCriteria(); $oCriteria->join = 'LEFT JOIN {{questions}} q ON t.qid=q.qid LEFT JOIN {{surveys}} s ON t.sid=s.sid'; $oCriteria->condition = '(q.qid IS NULL) OR (s.sid IS NULL)'; $aDelete['quotamembers'] = count(QuotaMember::model()->findAll($oCriteria)); if (QuotaMember::model()->hasErrors()) { safeDie(QuotaMember::model()->getError()); } /**********************************************************************/ /* Check assessments */ /**********************************************************************/ $oCriteria = new CDbCriteria(); $oCriteria->compare('scope', 'T'); $assessments = Assessment::model()->findAll($oCriteria); if (Assessment::model()->hasErrors()) { safeDie(Assessment::model()->getError()); } foreach ($assessments as $assessment) { $iAssessmentCount = count(Survey::model()->findAllByPk($assessment['sid'])); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } if (!$iAssessmentCount) { $aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching survey')); } } $oCriteria = new CDbCriteria(); $oCriteria->compare('scope', 'G'); $assessments = Assessment::model()->findAll($oCriteria); if (Assessment::model()->hasErrors()) { safeDie(Assessment::model()->getError()); } foreach ($assessments as $assessment) { $iAssessmentCount = count(QuestionGroup::model()->findAllByPk(array('gid' => $assessment['gid'], 'language' => $assessment['language']))); if (QuestionGroup::model()->hasErrors()) { safeDie(QuestionGroup::model()->getError()); } if (!$iAssessmentCount) { $aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching group')); } } unset($assessments); /**********************************************************************/ /* Check answers */ /**********************************************************************/ $oCriteria = new CDbCriteria(); $oCriteria->join = 'LEFT JOIN {{questions}} q ON t.qid=q.qid'; $oCriteria->condition = '(q.qid IS NULL)'; $answers = Answer::model()->findAll($oCriteria); foreach ($answers as $answer) { $aDelete['answers'][] = array('qid' => $answer['qid'], 'code' => $answer['code'], 'reason' => $clang->gT('No matching question')); } /***************************************************************************/ /* Check survey languagesettings and restore them if they don't exist */ /***************************************************************************/ $surveys = Survey::model()->findAll(); foreach ($surveys as $survey) { $aLanguages = $survey->additionalLanguages; $aLanguages[] = $survey->language; foreach ($aLanguages as $langname) { if ($langname) { $oLanguageSettings = SurveyLanguageSetting::model()->find('surveyls_survey_id=:surveyid AND surveyls_language=:langname', array(':surveyid' => $survey->sid, ':langname' => $langname)); if (!$oLanguageSettings) { $oLanguageSettings = new SurveyLanguageSetting(); $languagedetails = getLanguageDetails($langname); $insertdata = array('surveyls_survey_id' => $survey->sid, 'surveyls_language' => $langname, 'surveyls_title' => '', 'surveyls_dateformat' => $languagedetails['dateformat']); foreach ($insertdata as $k => $v) { $oLanguageSettings->{$k} = $v; } $usresult = $oLanguageSettings->save(); } } } } /**********************************************************************/ /* Check survey language settings */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $oCriteria = new CDbCriteria(); $oCriteria->addNotInCondition('surveyls_survey_id', $sids); $surveys_languagesettings = SurveyLanguageSetting::model()->findAll($oCriteria); if (SurveyLanguageSetting::model()->hasErrors()) { safeDie(SurveyLanguageSetting::model()->getError()); } foreach ($surveys_languagesettings as $surveys_languagesetting) { $aDelete['surveylanguagesettings'][] = array('slid' => $surveys_languagesetting['surveyls_survey_id'], 'reason' => $clang->gT('The related survey is missing.')); } /**********************************************************************/ /* Check questions */ /**********************************************************************/ $oCriteria = new CDbCriteria(); $oCriteria->join = 'LEFT JOIN {{surveys}} s ON t.sid=s.sid LEFT JOIN {{groups}} g ON t.gid=g.gid'; $oCriteria->condition = '(g.gid IS NULL) OR (s.sid IS NULL)'; $questions = Question::model()->findAll($oCriteria); if (Question::model()->hasErrors()) { safeDie(Question::model()->getError()); } foreach ($questions as $question) { $aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('No matching group') . " ({$question['gid']})"); } /**********************************************************************/ /* Check groups */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $oCriteria = new CDbCriteria(); $oCriteria->addNotInCondition('sid', $sids); $groups = QuestionGroup::model()->findAll($oCriteria); foreach ($groups as $group) { $aDelete['groups'][] = array('gid' => $group['gid'], 'reason' => $clang->gT('There is no matching survey.') . ' SID:' . $group['sid']); } /**********************************************************************/ /* Check old survey tables */ /**********************************************************************/ //1: Get list of 'old_survey' tables and extract the survey id //2: Check if that survey id still exists //3: If it doesn't offer it for deletion $sQuery = dbSelectTablesLike('{{old_survey}}%'); $aTables = Yii::app()->db->createCommand($sQuery)->queryColumn(); $aOldSIDs = array(); $aSIDs = array(); foreach ($aTables as $sTable) { list($sOldText, $SurveyText, $iSurveyID, $sDate) = explode('_', substr($sTable, strlen($sDBPrefix))); $aOldSIDs[] = $iSurveyID; $aFullOldSIDs[$iSurveyID][] = $sTable; } $aOldSIDs = array_unique($aOldSIDs); //$sQuery = 'SELECT sid FROM {{surveys}} ORDER BY sid'; //$oResult = dbExecuteAssoc($sQuery) or safeDie('Couldn\'t get unique survey ids'); $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $aSIDs = array(); foreach ($surveys as $survey) { $aSIDs[] = $survey['sid']; } foreach ($aOldSIDs as $iOldSID) { if (!in_array($iOldSID, $aSIDs)) { foreach ($aFullOldSIDs[$iOldSID] as $sTableName) { $aDelete['orphansurveytables'][] = $sTableName; } } else { foreach ($aFullOldSIDs[$iOldSID] as $sTableName) { $aTableParts = explode('_', substr($sTableName, strlen($sDBPrefix))); if (count($aTableParts) == 4) { $sOldText = $aTableParts[0]; $SurveyText = $aTableParts[1]; $iSurveyID = $aTableParts[2]; $sDateTime = $aTableParts[3]; $sType = $clang->gT('responses'); } elseif (count($aTableParts) == 5) { //This is a timings table ( $sOldText = $aTableParts[0]; $SurveyText = $aTableParts[1]; $iSurveyID = $aTableParts[2]; $sDateTime = $aTableParts[4]; $sType = $clang->gT('timings'); } $iYear = substr($sDateTime, 0, 4); $iMonth = substr($sDateTime, 4, 2); $iDay = substr($sDateTime, 6, 2); $iHour = substr($sDateTime, 8, 2); $iMinute = substr($sDateTime, 10, 2); $sDate = date('d M Y H:i', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear)); $sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName; $aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow(); if ($aFirstRow['recordcount'] == 0) { // empty table - so add it to immediate deletion $aDelete['orphansurveytables'][] = $sTableName; } else { $aOldSurveyTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s) (%s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'], $sType)); } } } } /**********************************************************************/ /* CHECK OLD TOKEN TABLES */ /**********************************************************************/ //1: Get list of 'old_token' tables and extract the survey id //2: Check if that survey id still exists //3: If it doesn't offer it for deletion $sQuery = dbSelectTablesLike('{{old_token}}%'); $aTables = Yii::app()->db->createCommand($sQuery)->queryColumn(); $aOldTokenSIDs = array(); $aTokenSIDs = array(); $aFullOldTokenSIDs = array(); foreach ($aTables as $sTable) { list($sOldText, $SurveyText, $iSurveyID, $sDateTime) = explode('_', substr($sTable, strlen($sDBPrefix))); $aTokenSIDs[] = $iSurveyID; $aFullOldTokenSIDs[$iSurveyID][] = $sTable; } $aOldTokenSIDs = array_unique($aTokenSIDs); $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $aSIDs = array(); foreach ($surveys as $survey) { $aSIDs[] = $survey['sid']; } foreach ($aOldTokenSIDs as $iOldTokenSID) { if (!in_array($iOldTokenSID, $aOldTokenSIDs)) { foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) { $aDelete['orphantokentables'][] = $sTableName; } } else { foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) { list($sOldText, $sTokensText, $iSurveyID, $sDateTime) = explode('_', substr($sTableName, strlen($sDBPrefix))); $iYear = substr($sDateTime, 0, 4); $iMonth = substr($sDateTime, 4, 2); $iDay = substr($sDateTime, 6, 2); $iHour = substr($sDateTime, 8, 2); $iMinute = substr($sDateTime, 10, 2); $sDate = date('D, d M Y h:i a', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear)); $sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName; $aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow(); if ($aFirstRow['recordcount'] == 0) { // empty table - so add it to immediate deletion $aDelete['orphantokentables'][] = $sTableName; } else { $aOldTokenTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'])); } } } } if ($aDelete['defaultvalues'] == 0 && $aDelete['quotamembers'] == 0 && $aDelete['quotas'] == 0 && $aDelete['quotals'] == 0 && count($aDelete) == 4) { $aDelete['integrityok'] = true; } else { $aDelete['integrityok'] = false; } if (!isset($aOldTokenTableAsk) && !isset($aOldSurveyTableAsk)) { $aDelete['redundancyok'] = true; } else { $aDelete['redundancyok'] = false; $aDelete['redundanttokentables'] = array(); $aDelete['redundantsurveytables'] = array(); if (isset($aOldTokenTableAsk)) { $aDelete['redundanttokentables'] = $aOldTokenTableAsk; } if (isset($aOldSurveyTableAsk)) { $aDelete['redundantsurveytables'] = $aOldSurveyTableAsk; } } /**********************************************************************/ /* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN TABLES */ /**********************************************************************/ //1: Get distinct list of survey_link survey ids, check if tokens // table still exists for each one, and remove if not /* TODO */ /**********************************************************************/ /* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN ENTRIES */ /**********************************************************************/ //1: For each survey_link, see if the matching entry still exists in // the token table and remove if it doesn't. /* TODO */ return $aDelete; }
/** * This function checks the LimeSurvey database for logical consistency and returns an according array * containing all issues in the particular tables. * @returns Array with all found issues. */ protected function _checkintegrity() { $clang = Yii::app()->lang; /*** Plainly delete survey permissions if the survey or user does not exist ***/ $users = User::model()->findAll(); $uids = array(); foreach ($users as $user) { $uids[] = $user['uid']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('uid', $uids, 'OR'); $surveys = Survey::model()->findAll(); $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $criteria->addNotInCondition('sid', $sids, 'OR'); Survey_permissions::model()->deleteAll($criteria); // Fix subquestions fixSubquestions(); /*** Check for active survey tables with missing survey entry and rename them ***/ $sDBPrefix = Yii::app()->db->tablePrefix; $sQuery = dbSelectTablesLike('{{survey}}\\_%'); $aResult = dbQueryOrFalse($sQuery) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />"); foreach ($aResult->readAll() as $aRow) { $sTableName = substr(reset($aRow), strlen($sDBPrefix)); if ($sTableName == 'survey_permissions' || $sTableName == 'survey_links' || $sTableName == 'survey_url_parameters') { continue; } $iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1); $count = $surveys = Survey::model()->findAllByPk($iSurveyID); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } if ($count == 0) { $sDate = date('YmdHis') . rand(1, 1000); $sOldTable = "survey_{$iSurveyID}"; $sNewTable = "old_survey_{$iSurveyID}_{$sDate}"; try { $deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}"); } catch (CDbException $e) { die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />'); } } } /*** Check for active token tables with missing survey entry ***/ $aResult = dbQueryOrFalse(dbSelectTablesLike('{{tokens}}\\_%')) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />"); foreach ($aResult->readAll() as $aRow) { $sTableName = substr(reset($aRow), strlen($sDBPrefix)); $iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1); $count = count(Survey::model()->findAllByPk($iSurveyID)); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } if ($count == 0) { $sDate = date('YmdHis') . rand(1, 1000); $sOldTable = "tokens_{$iSurveyID}"; $sNewTable = "old_tokens_{$iSurveyID}_{$sDate}"; try { $deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}"); } catch (CDbException $e) { die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />'); } } } /**********************************************************************/ /* Check conditions */ /**********************************************************************/ // TMSW Conditions->Relevance: Replace this with analysis of relevance $conditions = Conditions::model()->findAll(); if (Conditions::model()->hasErrors()) { safeDie(Conditions::model()->getError()); } foreach ($conditions as $condition) { $iRowCount = count(Questions::model()->findAll()); if (Questions::model()->hasErrors()) { safeDie(Questions::model()->getError()); } if (!$iRowCount) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => 'No matching QID'); } if ($condition['cqid'] != 0) { // skip case with cqid=0 for codnitions on {TOKEN:EMAIL} for instance $iRowCount = Questions::model()->countByAttributes(array('qid' => $condition['cqid'])); if (Questions::model()->hasErrors()) { safeDie(Questions::model()->getError()); } if (!$iRowCount) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CQID')); } } if ($condition['cfieldname']) { if (preg_match('/^\\+{0,1}[0-9]+X[0-9]+X*$/', $condition['cfieldname'])) { // only if cfieldname isn't Tag such as {TOKEN:EMAIL} or any other token list($surveyid, $gid, $rest) = explode('X', $condition['cfieldname']); $iRowCount = count(Groups::model()->findAllByPk($gid)); if (Groups::model()->hasErrors()) { safeDie(Groups::model()->getError()); } if (!$iRowCount) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CFIELDNAME group!') . " ({$gid}) ({$condition['cfieldname']})"); } } } elseif (!$condition['cfieldname']) { $aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No CFIELDNAME field set!') . " ({$condition['cfieldname']})"); } } /**********************************************************************/ /* Check question attributes */ /**********************************************************************/ $question_attributes = Question_attributes::model()->findAll(); if (Question_attributes::model()->hasErrors()) { safeDie(Question_attributes::model()->getError()); } foreach ($question_attributes as $question_attribute) { $iRowCount = Questions::model()->countByAttributes(array('qid' => $question_attribute['qid'])); if (Questions::model()->hasErrors()) { safeDie(Questions::model()->getError()); } if (!$iRowCount) { $aDelete['questionattributes'][] = array('qid' => $question_attribute['qid']); } } // foreach /**********************************************************************/ /* Check default values */ /**********************************************************************/ $questions = Questions::model()->findAll(); if (Questions::model()->hasErrors()) { safeDie(Questions::model()->getError()); } $qids = array(); foreach ($questions as $question) { $qids[] = $question['qid']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('qid', $qids); $aDelete['defaultvalues'] = count(Defaultvalues::model()->findAll($criteria)); if (Defaultvalues::model()->hasErrors()) { safeDie(Defaultvalues::model()->getError()); } /**********************************************************************/ /* Check quotas */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('sid', $sids); $aDelete['quotas'] = count(Quota::model()->findAll($criteria)); if (Quota::model()->hasErrors()) { safeDie(Quota::model()->getError()); } /**********************************************************************/ /* Check quota languagesettings */ /**********************************************************************/ $quotas = Quota::model()->findAll(); if (Quota::model()->hasErrors()) { safeDie(Quota::model()->getError()); } $ids = array(); foreach ($quotas as $quota) { $ids[] = $quota['id']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('quotals_quota_id', $ids); $aDelete['quotals'] = count(Quota_languagesettings::model()->findAll($criteria)); if (Quota_languagesettings::model()->hasErrors()) { safeDie(Quota_languagesettings::model()->getError()); } /**********************************************************************/ /* Check quota members */ /**********************************************************************/ $quotas = Quota::model()->findAll(); $quota_ids = array(); foreach ($quotas as $quota) { $quota_ids[] = $quota['id']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('quota_id', $quota_ids); $questions = Questions::model()->findAll(); $qids = array(); foreach ($questions as $question) { $qids[] = $question['qid']; } $criteria->addNotInCondition('qid', $qids, 'OR'); $surveys = Survey::model()->findAll(); $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $criteria->addNotInCondition('sid', $sids, 'OR'); $aDelete['quotamembers'] = count(Quota_members::model()->findAll($criteria)); if (Quota_members::model()->hasErrors()) { safeDie(Quota_members::model()->getError()); } /**********************************************************************/ /* Check assessments */ /**********************************************************************/ $criteria = new CDbCriteria(); $criteria->compare('scope', 'T'); $assessments = Assessment::model()->findAll($criteria); if (Assessment::model()->hasErrors()) { safeDie(Assessment::model()->getError()); } foreach ($assessments as $assessment) { $iAssessmentCount = count(Survey::model()->findAllByPk($assessment['sid'])); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } if (!$iAssessmentCount) { $aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching survey')); } } $criteria = new CDbCriteria(); $criteria->compare('scope', 'G'); $assessments = Assessment::model()->findAll($criteria); if (Assessment::model()->hasErrors()) { safeDie(Assessment::model()->getError()); } foreach ($assessments as $assessment) { $iAssessmentCount = count(Groups::model()->findAllByPk($assessment['gid'])); if (Groups::model()->hasErrors()) { safeDie(Groups::model()->getError()); } if (!$iAssessmentCount) { $aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching group')); } } /**********************************************************************/ /* Check answers */ /**********************************************************************/ $answers = Answers::model()->findAll(); if (Answers::model()->hasErrors()) { safeDie(Answers::model()->getError()); } foreach ($answers as $answer) { $iAnswerCount = Questions::model()->countByAttributes(array('qid' => $answer['qid'])); if (Questions::model()->hasErrors()) { safeDie(Questions::model()->getError()); } if (!$iAnswerCount) { $aDelete['answers'][] = array('qid' => $answer['qid'], 'code' => $answer['code'], 'reason' => $clang->gT('No matching question')); } } /**********************************************************************/ /* Check surveys */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } foreach ($surveys as $survey) { $criteria = new CDbCriteria(); $criteria->compare('surveyls_survey_id', $survey['sid']); $iSurveyLangSettingsCount = count(Surveys_languagesettings::model()->findAll($criteria)); if (Surveys_languagesettings::model()->hasErrors()) { safeDie(Surveys_languagesettings::model()->getError()); } if (!$iSurveyLangSettingsCount) { $aDelete['surveys'][] = array('sid' => $survey['sid'], 'reason' => $clang->gT('Language specific settings missing')); } } /**********************************************************************/ /* Check survey language settings */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('surveyls_survey_id', $sids); $surveys_languagesettings = Surveys_languagesettings::model()->findAll($criteria); if (Surveys_languagesettings::model()->hasErrors()) { safeDie(Surveys_languagesettings::model()->getError()); } foreach ($surveys_languagesettings as $surveys_languagesetting) { $aDelete['surveylanguagesettings'][] = array('slid' => $surveys_languagesetting['surveyls_survey_id'], 'reason' => $clang->gT('The related survey is missing.')); } /**********************************************************************/ /* Check questions */ /**********************************************************************/ $questions = Questions::model()->findAll(); if (Questions::model()->hasErrors()) { safeDie(Questions::model()->getError()); } foreach ($questions as $question) { //Make sure the group exists $criteria = new CDbCriteria(); $criteria->compare('gid', $question['gid']); $iQuestionCount = count(Groups::model()->findAll($criteria)); if (Groups::model()->hasErrors()) { safeDie(Groups::model()->getError()); } if (!$iQuestionCount) { $aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('No matching group') . " ({$question['gid']})"); } //Make sure survey exists $iQuestionCount = count(Survey::model()->findAllByPk($question['sid'])); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } if (!$iQuestionCount) { $aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('There is no matching survey.') . " ({$question['sid']})"); } } /**********************************************************************/ /* Check groups */ /**********************************************************************/ $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $sids = array(); foreach ($surveys as $survey) { $sids[] = $survey['sid']; } $criteria = new CDbCriteria(); $criteria->addNotInCondition('sid', $sids); $groups = Groups::model()->findAll($criteria); foreach ($groups as $group) { $aDelete['groups'][] = array('gid' => $group['gid'], 'reason' => $clang->gT('There is no matching survey.') . ' SID:' . $group['sid']); } /**********************************************************************/ /* Check old survey tables */ /**********************************************************************/ //1: Get list of 'old_survey' tables and extract the survey id //2: Check if that survey id still exists //3: If it doesn't offer it for deletion $sQuery = dbSelectTablesLike('{{old_survey}}%'); $aResult = dbQueryOrFalse($sQuery) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />"); $aTables = $aResult->readAll(); $aOldSIDs = array(); $aSIDs = array(); foreach ($aTables as $sTable) { $sTable = reset($sTable); list($sOldText, $SurveyText, $iSurveyID, $sDate) = explode('_', substr($sTable, strlen($sDBPrefix))); $aOldSIDs[] = $iSurveyID; $aFullOldSIDs[$iSurveyID][] = $sTable; } $aOldSIDs = array_unique($aOldSIDs); $sQuery = 'SELECT sid FROM {{surveys}} ORDER BY sid'; $oResult = dbExecuteAssoc($sQuery) or safeDie('Couldn\'t get unique survey ids'); $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $aSIDs = array(); foreach ($surveys as $survey) { $aSIDs[] = $survey['sid']; } foreach ($aOldSIDs as $iOldSID) { if (!in_array($iOldSID, $aSIDs)) { foreach ($aFullOldSIDs[$iOldSID] as $sTableName) { $aDelete['orphansurveytables'][] = $sTableName; } } else { foreach ($aFullOldSIDs[$iOldSID] as $sTableName) { $aTableParts = explode('_', substr($sTableName, strlen($sDBPrefix))); if (count($aTableParts) == 4) { $sOldText = $aTableParts[0]; $SurveyText = $aTableParts[1]; $iSurveyID = $aTableParts[2]; $sDateTime = $aTableParts[3]; $sType = $clang->gT('responses'); } elseif (count($aTableParts) == 5) { //This is a timings table ( $sOldText = $aTableParts[0]; $SurveyText = $aTableParts[1]; $iSurveyID = $aTableParts[2]; $sDateTime = $aTableParts[4]; $sType = $clang->gT('timings'); } $iYear = substr($sDateTime, 0, 4); $iMonth = substr($sDateTime, 4, 2); $iDay = substr($sDateTime, 6, 2); $iHour = substr($sDateTime, 8, 2); $iMinute = substr($sDateTime, 10, 2); $sDate = date('d M Y H:i', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear)); $sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName; $aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow(); if ($aFirstRow['recordcount'] == 0) { // empty table - so add it to immediate deletion $aDelete['orphansurveytables'][] = $sTableName; } else { $aOldSurveyTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s) (%s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'], $sType)); } } } } /**********************************************************************/ /* CHECK OLD TOKEN TABLES */ /**********************************************************************/ //1: Get list of 'old_token' tables and extract the survey id //2: Check if that survey id still exists //3: If it doesn't offer it for deletion $aResult = dbQueryOrFalse(dbSelectTablesLike('{{old_token}}%')) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />"); $aTables = $aResult->readAll(); $aOldTokenSIDs = array(); $aTokenSIDs = array(); $aFullOldTokenSIDs = array(); foreach ($aTables as $sTable) { $sTable = reset($sTable); list($sOldText, $SurveyText, $iSurveyID, $sDateTime) = explode('_', substr($sTable, strlen($sDBPrefix))); $aTokenSIDs[] = $iSurveyID; $aFullOldTokenSIDs[$iSurveyID][] = $sTable; } $aOldTokenSIDs = array_unique($aOldTokenSIDs); $surveys = Survey::model()->findAll(); if (Survey::model()->hasErrors()) { safeDie(Survey::model()->getError()); } $aSIDs = array(); foreach ($surveys as $survey) { $aSIDs[] = $survey['sid']; } foreach ($oResult->readAll() as $aRow) { $aTokenSIDs[] = $aRow['sid']; } foreach ($aOldTokenSIDs as $iOldTokenSID) { if (!in_array($iOldTokenSID, $aTokenSIDs)) { foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) { $aDelete['orphantokentables'][] = $sTableName; } } else { foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) { list($sOldText, $sTokensText, $iSurveyID, $sDateTime) = explode('_', substr($sTableName, strlen($sDBPrefix))); $iYear = substr($sDateTime, 0, 4); $iMonth = substr($sDateTime, 4, 2); $iDay = substr($sDateTime, 6, 2); $iHour = substr($sDateTime, 8, 2); $iMinute = substr($sDateTime, 10, 2); $sDate = date('D, d M Y h:i a', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear)); $sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName; $aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow(); if ($aFirstRow['recordcount'] == 0) { // empty table - so add it to immediate deletion $aDelete['orphantokentables'][] = $sTableName; } else { $aOldTokenTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'])); } } } } if ($aDelete['defaultvalues'] == 0 && $aDelete['quotamembers'] == 0 && $aDelete['quotas'] == 0 && $aDelete['quotals'] == 0 && count($aDelete) == 4) { $aDelete['integrityok'] = true; } else { $aDelete['integrityok'] = false; } if (!isset($aOldTokenTableAsk) && !isset($aOldSurveyTableAsk)) { $aDelete['redundancyok'] = true; } else { $aDelete['redundancyok'] = false; $aDelete['redundanttokentables'] = array(); $aDelete['redundantsurveytables'] = array(); if (isset($aOldTokenTableAsk)) { $aDelete['redundanttokentables'] = $aOldTokenTableAsk; } if (isset($aOldSurveyTableAsk)) { $aDelete['redundantsurveytables'] = $aOldSurveyTableAsk; } } /**********************************************************************/ /* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN TABLES */ /**********************************************************************/ //1: Get distinct list of survey_link survey ids, check if tokens // table still exists for each one, and remove if not /* TODO */ /**********************************************************************/ /* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN ENTRIES */ /**********************************************************************/ //1: For each survey_link, see if the matching entry still exists in // the token table and remove if it doesn't. /* TODO */ return $aDelete; }