/**
* Handles creating or replacing the view for this survey
*
* @param \Gems_Tracker_Survey $viewName
* @param \MUtil_Model_ModelAbstract $answerModel
*/
protected function replaceCreateView(\Gems_Tracker_Survey $survey, \MUtil_Model_ModelAbstract $answerModel)
{
$viewName = $this->getViewName($survey);
$responseDb = $this->project->getResponseDatabase();
$fieldSql = '';
foreach ($answerModel->getItemsOrdered() as $name) {
if (true === $answerModel->get($name, 'survey_question') && !in_array($name, array('submitdate', 'startdate', 'datestamp')) && !$answerModel->is($name, 'type', \MUtil_Model::TYPE_NOVALUE)) {
// Only real answers
$fieldSql .= ',MAX(IF(gdr_answer_id = ' . $responseDb->quote($name) . ', gdr_response, NULL)) AS ' . $responseDb->quoteIdentifier($name);
}
}
if ($fieldSql > '') {
$dbConfig = $this->db->getConfig();
$tokenTable = $this->db->quoteIdentifier($dbConfig['dbname'] . '.gems__tokens');
$createViewSql = 'CREATE OR REPLACE VIEW ' . $responseDb->quoteIdentifier($viewName) . ' AS SELECT gdr_id_token';
$createViewSql .= $fieldSql;
$createViewSql .= "FROM gemsdata__responses join " . $tokenTable . " on (gto_id_token=gdr_id_token and gto_id_survey=" . $survey->getSurveyId() . ") GROUP BY gdr_id_token;";
try {
$responseDb->query($createViewSql)->execute();
} catch (Exception $exc) {
$responseConfig = $responseDb->getConfig();
$dbUser = $this->db->quoteIdentifier($responseConfig['username']) . '@' . $this->db->quoteIdentifier($responseConfig['host']);
$statement = "GRANT SELECT ON " . $tokenTable . " TO " . $dbUser;
$this->getBatch()->addMessage(sprintf($this->_("Creating view failed, try adding rights using the following statement: %s"), $statement));
}
}
}
public function inspect()
{
$insp = new Inspection('Db Connection');
try {
$this->getDbAdapter()->getConnection();
$config = $this->dbAdapter->getConfig();
$insp->write(sprintf('Connection to %s as %s on %s:%s successful', $config['dbname'], $config['username'], $config['host'], $config['port']));
switch ($this->dbType) {
case 'mysql':
$rows = $this->dbAdapter->query('SHOW VARIABLES WHERE variable_name ' . 'IN (\'version\', \'protocol_version\', \'version_compile_os\');')->fetchAll();
$sqlinsp = new Inspection('MySQL');
foreach ($rows as $row) {
$sqlinsp->write($row->variable_name . ': ' . $row->value);
}
$insp->write($sqlinsp);
break;
case 'pgsql':
$row = $this->dbAdapter->query('SELECT version();')->fetchAll();
$sqlinsp = new Inspection('PostgreSQL');
$sqlinsp->write($row[0]->version);
$insp->write($sqlinsp);
break;
}
} catch (Exception $e) {
return $insp->error(sprintf('Connection failed %s', $e->getMessage()));
}
return $insp;
}
/**
* Returns SQL header data
*/
public function getHeader()
{
$dbConfig = $this->_read->getConfig();
$versionRow = $this->_read->fetchRow('SHOW VARIABLES LIKE \'version\'');
$header = "-- Magento DB backup\n" . "--\n" . "-- Host: {$dbConfig['host']} Database: {$dbConfig['dbname']}\n" . "-- ------------------------------------------------------\n" . "-- Server version: {$versionRow['Value']}\n\n" . "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n" . "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n" . "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n" . "/*!40101 SET NAMES utf8 */;\n" . "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n" . "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n" . "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n" . "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n";
return $header;
}
/**
* Initializes metadata.
*
* If metadata cannot be loaded from cache, adapter's describeTable() method is called to discover metadata
* information. Returns true if and only if the metadata are loaded from cache.
*
* @return boolean
* @throws Kwf_Exception
*/
protected function _setupMetadata()
{
if (count($this->_metadata) > 0) {
return true;
}
// Assume that metadata will be loaded from cache
$isMetadataFromCache = true;
// Define the cache identifier where the metadata are saved
//get db configuration
$dbConfig = $this->_db->getConfig();
$port = isset($dbConfig['options']['port']) ? ':' . $dbConfig['options']['port'] : (isset($dbConfig['port']) ? ':' . $dbConfig['port'] : null);
$host = isset($dbConfig['options']['host']) ? ':' . $dbConfig['options']['host'] : (isset($dbConfig['host']) ? ':' . $dbConfig['host'] : null);
// Define the cache identifier where the metadata are saved
$cacheId = 'dbtbl_' . md5($port . $host . '/' . $dbConfig['dbname'] . ':' . $this->_schema . '.' . $this->_name);
// If $this has no metadata cache or metadata cache misses
if (!($metadata = Kwf_Cache_SimpleStatic::fetch($cacheId))) {
// Metadata are not loaded from cache
$isMetadataFromCache = false;
// Fetch metadata from the adapter's describeTable() method
$metadata = $this->_db->describeTable($this->_name, $this->_schema);
// If $this has a metadata cache, then cache the metadata
Kwf_Cache_SimpleStatic::add($cacheId, $metadata);
}
// Assign the metadata to $this
$this->_metadata = $metadata;
// Return whether the metadata were loaded from cache
return $isMetadataFromCache;
}
/**
* restore sql
* @param string $path
* @throws Exception
* @return boolean
*/
protected function _restoreSql($path)
{
$dbConfig = $this->_db->getConfig();
$cmd = $this->_mysqlExec . ' -u' . $dbConfig['username'] . ' -p' . $dbConfig['password'] . ' -t ' . $dbConfig['dbname'] . ' < ' . $path;
if (system($cmd) === false) {
$this->_errorStatus = self::ERROR_SQL_FAIL;
return false;
}
return true;
}
/**
* Constructs a sequence name based on a table name
*
* @param string $tableName table name
* @return string
* @throws Streamwide_Db_Adapter_Decorator_Exception
*/
protected function _getSequenceName($tableName)
{
$config = $this->_adapter->getConfig();
if (!is_array($config)) {
require_once 'Streamwide/Db/Adapter/Decorator/Exception.php';
throw new Streamwide_Db_Adapter_Decorator_Exception('Retrieved an invalid adapter configuration format');
}
if (!isset($config['options']['sequenceGetter'])) {
return false;
}
$sequenceGetter = Streamwide_Db_SequenceGetter::factory($config['options']['sequenceGetter']);
if (false === ($sequenceName = $sequenceGetter->getSequenceName($tableName))) {
return false;
}
return $sequenceName;
}
protected function executeQuery($sqlFile, $dir)
{
$dbConf = $this->db->getConfig();
$cmd = sprintf('mysql --default-character-set=utf8 --host=%s --user=%s --password=%s %s < %s', $dbConf['host'], $dbConf['username'], $dbConf['password'], $dbConf['dbname'], $dir . '/' . $sqlFile);
$this->writeLn("Executing [{$sqlFile}]", 'yellow');
$retval = null;
$lastLine = system($cmd, $retval);
//update db log
if ($retval == 0) {
try {
$this->db->insert('upgrade_db_log', array('file' => $sqlFile, 'insert_dt' => date('c')));
$this->writeLn("{$sqlFile} OK", 'green');
} catch (Exception $exc) {
$this->writeLn("ERR updating log for [{$sqlFile}]:\n" . $exc->getMessage(), null, 'red');
}
} else {
$this->writeLn("ERR executing [{$sqlFile}]:\n" . $lastLine, null, 'red');
}
}
/**
* Initializes metadata.
*
* If metadata cannot be loaded from cache, adapter's describeTable() method is called to discover metadata
* information. Returns true if and only if the metadata are loaded from cache.
*
* @return boolean
* @throws Zend_Db_Table_Exception
*/
protected function _setupMetadata()
{
if ($this->metadataCacheInClass() && count($this->_metadata) > 0) {
return true;
}
// Assume that metadata will be loaded from cache
$isMetadataFromCache = true;
// If $this has no metadata cache but the class has a default metadata cache
if (null === $this->_metadataCache && null !== self::$_defaultMetadataCache) {
// Make $this use the default metadata cache of the class
$this->_setMetadataCache(self::$_defaultMetadataCache);
}
// If $this has a metadata cache
if (null !== $this->_metadataCache) {
// Define the cache identifier where the metadata are saved
//get db configuration
$dbConfig = $this->_db->getConfig();
$port = isset($dbConfig['options']['port']) ? ':' . $dbConfig['options']['port'] : (isset($dbConfig['port']) ? ':' . $dbConfig['port'] : null);
$host = isset($dbConfig['options']['host']) ? ':' . $dbConfig['options']['host'] : (isset($dbConfig['host']) ? ':' . $dbConfig['host'] : null);
// Define the cache identifier where the metadata are saved
$cacheId = md5($port . $host . '/' . $dbConfig['dbname'] . ':' . $this->_schema . '.' . $this->_name);
}
// If $this has no metadata cache or metadata cache misses
if (null === $this->_metadataCache || !($metadata = $this->_metadataCache->load($cacheId))) {
// Metadata are not loaded from cache
$isMetadataFromCache = false;
// Fetch metadata from the adapter's describeTable() method
$metadata = $this->_db->describeTable($this->_name, $this->_schema);
// If $this has a metadata cache, then cache the metadata
if (null !== $this->_metadataCache && !$this->_metadataCache->save($metadata, $cacheId)) {
trigger_error('Failed saving metadata to metadataCache', E_USER_NOTICE);
}
}
// Assign the metadata to $this
$this->_metadata = $metadata;
// Return whether the metadata were loaded from cache
return $isMetadataFromCache;
}
/**
* Store the profile info
*
* @param Zend_Db_Adapter_Abstract $db
* @return void
*/
public function saveProfileInfo(Zend_Db_Adapter_Abstract $db = null, Zend_Controller_Request_Abstract $request)
{
if (($db instanceof Zend_Db_Adapter_Pdo_Mysql || $db instanceof Zend_Db_Adapter_Mysqli) && $this->_enabled) {
$values = array();
$values["ip"] = new Zend_Db_Expr('inet_aton("' . $request->getServer('REMOTE_ADDR', '') . '")');
$values["page"] = $request->getServer('REQUEST_URI', '');
$values["user_agent"] = $request->getServer('HTTP_USER_AGENT', '');
$values["referer"] = $request->getServer('HTTP_REFERER', '');
foreach ($this->_tableNames as $key => $value) {
$values[$value] = 0;
$values[$value . "_comment"] = "";
}
foreach ($this->_timerProfiles as $key => $tp) {
if ($tp->hasEnded()) {
$values[$this->_tableNames[$tp->getTimerType()]] += $tp->getElapsedSecs();
$values[$this->_tableNames[$tp->getTimerType()] . "_comment"] .= $tp->getTimerComment();
}
}
$dbname = "profiler";
$config = $db->getConfig();
if (isset($config["dbname"])) {
$dbname = strpos($config["dbname"], $dbname) !== false ? $config["dbname"] : $config["dbname"] . "_profiler";
}
$tableName = $dbname . ".profiler_log_" . date("Ymd");
try {
/**
* Catch table not exists error
* faster then checking if table exists
*/
$db->insert($tableName, $values);
} catch (Zend_Db_Statement_Mysqli_Exception $zdsmex) {
if (preg_match("/Mysqli prepare error: Table '(.*)' doesn't exist/", $zdsmex->getMessage())) {
$db->query("create table " . $tableName . " like " . $dbname . ".profiler_log_template");
$db->insert($tableName, $values);
} else {
throw $zdsmex;
}
} catch (Zend_Db_Statement_Exception $zdsex) {
if (preg_match("/SQLSTATE\\[42S02\\]: Base table or view not found: 1146 Table '(.*)' doesn't exist/", $zdsex->getMessage())) {
$db->query("create table " . $tableName . " like " . $dbname . ".profiler_log_template");
$db->insert($tableName, $values);
} else {
throw $zdsex;
}
} catch (Exception $ex) {
throw $ex;
}
}
}
/**
* Gibt den Datenbanknamen der Storage Engine zurück
* @return string
*/
public function getDatabasename()
{
$config = $this->_adapter->getConfig();
return $config['dbname'];
}
