/** * Gets last used schema from #_schema * * @return string json */ public function checkDb() { $res = array(); // 1. Check the schema version $man = NewsletterHelper::getManifest(); $version = $man->version; $schema = EnvironmentHelper::getLastSchema(); $sc = version_compare($schema, $version) <= 0; $res[] = array('text' => JText::_('COM_NEWSLETTER_MAINTAINANCE_CHECKSCHEMA') . ': ' . $schema, 'type' => $sc); // 2. Check if all tables are present $res[] = array('text' => JText::_('COM_NEWSLETTER_MAINTAINANCE_CHECKSCHEMA') . ': ' . $schema, 'type' => $sc); $installFile = file_get_contents(JPATH_COMPONENT_ADMINISTRATOR . DS . 'install' . DS . 'install.sql'); // explode whole script to table alter scripts $tableAlters = array(); preg_match_all("/create\\s*table\\s*[\\`\"\\']?([\\#\\_a-zA-Z0-9]+)[\\`\"\\']?[^;]*/is", $installFile, $tableAlters); $dbo = JFactory::getDbo(); for ($i = 0; $i < count($tableAlters[0]); $i++) { // Process each table... $tableName = $tableAlters[1][$i]; $alterScript = $tableAlters[0][$i]; // Get fields of table from alter script $matches = array(); preg_match_all("/^\\s*(?:\\`|\"|\\')([a-z0-9_]+)/m", $alterScript, $matches); $fields = $matches[1]; // Get destription of a table from DB $dbo->setQuery('DESCRIBE ' . $tableName); $data = $dbo->loadAssocList(); // Check if table absent at all if ($data === null) { $res[] = array('text' => JText::sprintf('COM_NEWSLETTER_MAINTAINANCE_TABLE_ABSENT', $tableName), 'type' => false); } else { // Check if some fields of a table absent $fieldsPresent = array(); foreach ($data as $item) { $fieldsPresent[] = $item['Field']; } foreach ($fields as $field) { if (!in_array($field, $fieldsPresent)) { $res[] = array('text' => JText::sprintf('COM_NEWSLETTER_MAINTAINANCE_TABLE_FIELD_ABSENT', $field, $tableName), 'type' => false); } } } } // 3. Check conflicts $count = EnvironmentHelper::getConflictsCount(); $res[] = array('text' => JText::_('COM_NEWSLETTER_MAINTAINANCE_CHECKUSERCONFLICTS') . ' ' . JText::sprintf('COM_NEWSLETTER_CONFLICTS_FOUND', $count), 'type' => $count == 0); // 4. Remove all died rows $dbo = JFactory::getDbo(); $dbo->setQuery('DELETE FROM #__newsletter_subscribers ' . 'USING #__newsletter_subscribers ' . 'LEFT JOIN #__users AS u ON u.id = #__newsletter_subscribers.user_id ' . 'WHERE #__newsletter_subscribers.email="" AND #__newsletter_subscribers.user_id > 0 AND u.id IS NULL'); if ($dbo->query()) { $diedCnt = $dbo->getAffectedRows(); $res[] = array('text' => JText::_('COM_NEWSLETTER_MAINTAINANCE_CHECKDIEDROWS') . ':' . $diedCnt, 'type' => true); } else { $res[] = array('text' => JText::_('COM_NEWSLETTER_MAINTAINANCE_CHECKDIEDROWS'), 'type' => false); } // Return data NewsletterHelper::jsonMessage('checkDb', $res); }