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