/**
* 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);
}
