/**
* Executes a prepared statement.
*
* @param array $params OPTIONAL Values to bind to parameter placeholders.
* @return bool
*/
public function execute(array $params = null)
{
/*
* Simple case - no query profiler to manage.
*/
if ($this->_queryId === null) {
return $this->_execute($params);
}
/*
* Do the same thing, but with query profiler
* management before and after the execute.
*/
$prof = $this->_adapter->getProfiler();
$qp = $prof->getQueryProfile($this->_queryId);
if ($qp->hasEnded()) {
$this->_queryId = $prof->queryClone($qp);
$qp = $prof->getQueryProfile($this->_queryId);
}
if ($params !== null) {
$qp->bindParams($params);
} else {
$qp->bindParams($this->_bindParam);
}
$qp->start($this->_queryId);
$retval = $this->_execute($params);
$prof->queryEnd($this->_queryId);
return $retval;
}
/**
* Executes a prepared statement.
*
* @param array $params OPTIONAL Values to bind to parameter placeholders.
* @return bool
*/
public function execute(array $params = null)
{
if ($this->_queryProfile) {
if ($this->_queryProfile->hasEnded()) {
$prof = $this->_adapter->getProfiler();
$q = $prof->queryClone($this->_queryProfile);
$this->_queryProfile = $prof->getQueryProfile($q);
}
if ($params !== null) {
foreach ($params as $param => $variable) {
if (is_int($param)) {
$param++;
}
$this->_queryProfile->bindParam($param, $variable);
}
}
$this->_queryProfile->start();
}
$retval = true;
if ($params !== null) {
$retval = $this->_execute($params);
} else {
$retval = $this->_execute();
}
if ($this->_queryProfile) {
$this->_queryProfile->end();
}
return $retval;
}
protected function _disableDbProfiler()
{
$profile = $this->_db->getProfiler()->getLastQueryProfile();
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' profile query: ' . $profile->getQuery() . ' profile params: ' . print_r($profile->getQueryParams(), true));
}
$this->_db->getProfiler()->setEnabled(false);
}
/**
* Create a new connection
*/
private function connect()
{
$genericAdapterOptions = self::$genericAdapterOptions;
$driverOptions = self::$driverOptions;
$adapterParamaters = array('host' => $this->config->host, 'username' => $this->config->username, 'password' => $this->config->password, 'dbname' => $this->config->dbname, 'options' => &$genericAdapterOptions, 'driver_options' => &$driverOptions);
$this->dbType = strtolower($this->config->get('db', 'mysql'));
switch ($this->dbType) {
case 'mysql':
$adapter = 'Pdo_Mysql';
/*
* Set MySQL server SQL modes to behave as closely as possible to Oracle and PostgreSQL. Note that the
* ONLY_FULL_GROUP_BY mode is left on purpose because MySQL requires you to specify all non-aggregate
* columns in the group by list even if the query is grouped by the master table's primary key which is
* valid ANSI SQL though. Further in that case the query plan would suffer if you add more columns to
* the group by list.
*/
$driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET SESSION SQL_MODE=\'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,' . 'NO_AUTO_CREATE_USER,ANSI_QUOTES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION\';';
$adapterParamaters['port'] = $this->config->get('port', 3306);
break;
case 'pgsql':
$adapter = 'Pdo_Pgsql';
$adapterParamaters['port'] = $this->config->get('port', 5432);
if ($adapterParamaters['host'] === '') {
unset($adapterParamaters['host']);
}
break;
/*case 'oracle':
if ($this->dbtype === 'oracle') {
$attributes['persistent'] = true;
}
$this->db = ZfDb::factory($adapter, $attributes);
if ($adapter === 'Oracle') {
$this->db->setLobAsString(false);
}
break;*/
/*case 'oracle':
if ($this->dbtype === 'oracle') {
$attributes['persistent'] = true;
}
$this->db = ZfDb::factory($adapter, $attributes);
if ($adapter === 'Oracle') {
$this->db->setLobAsString(false);
}
break;*/
default:
throw new ConfigurationError('Backend "%s" is not supported', $this->dbType);
}
$this->dbAdapter = Zend_Db::factory($adapter, $adapterParamaters);
$this->dbAdapter->setFetchMode(Zend_Db::FETCH_OBJ);
// TODO(el/tg): The profiler is disabled per default, why do we disable the profiler explicitly?
$this->dbAdapter->getProfiler()->setEnabled(false);
}
/**
* @param $url
*/
protected function debugResponse($url)
{
parent::debugResponse($url);
$this->debugSection('Session', json_encode($_COOKIE));
if ($this->db) {
$profiler = $this->db->getProfiler();
$queries = $profiler->getTotalNumQueries() - $this->queries;
$time = $profiler->getTotalElapsedSecs() - $this->time;
$this->debugSection('Db', $queries . ' queries');
$this->debugSection('Time', round($time, 2) . ' secs taken');
$this->time = $profiler->getTotalElapsedSecs();
$this->queries = $profiler->getTotalNumQueries();
}
}
protected function debugResponse()
{
// $this->debugsection('Route', sprintf('%/%/%',
// $this->client->getzendrequest()->getmodulename(),
// $this->client->getzendrequest()->getcontrollername(),
// $this->client->getzendrequest()->getactionname()
// ));
$this->debugSection('Session', json_encode($_COOKIE));
if ($this->db) {
$profiler = $this->db->getProfiler();
$queries = $profiler->getTotalNumQueries() - $this->queries;
$time = $profiler->getTotalElapsedSecs() - $this->time;
$this->debugSection('Db', $queries . ' queries');
$this->debugSection('Time', round($time, 2) . ' secs taken');
$this->time = $profiler->getTotalElapsedSecs();
$this->queries = $profiler->getTotalNumQueries();
}
}
/**
* Gets database debug information, including query count and run time and
* the actual queries that were run.
*
* @param Zend_Db_Adapter_Abstract $db
*
* @return array Keys: queryCount, totalQueryRunTime, queryHtml
*/
public static function getDatabaseDebugInfo(Zend_Db_Adapter_Abstract $db)
{
$return = array('queryCount' => 0, 'totalQueryRunTime' => 0, 'queryHtml' => '');
/* @var $profiler Zend_Db_Profiler */
$profiler = $db->getProfiler();
$return['queryCount'] = $profiler->getTotalNumQueries();
if ($return['queryCount']) {
$return['queryHtml'] .= '<ol>';
$queries = $profiler->getQueryProfiles();
foreach ($queries as $query) {
$queryText = rtrim($query->getQuery());
if (preg_match('#(^|\\n)(\\t+)([ ]*)(?=\\S)#', $queryText, $match)) {
$queryText = preg_replace('#(^|\\n)\\t{1,' . strlen($match[2]) . '}#', '$1', $queryText);
}
$boundParams = array();
foreach ($query->getQueryParams() as $param) {
$boundParams[] = htmlspecialchars($param);
}
$explainOutput = '';
if (preg_match('#^\\s*SELECT\\s#i', $queryText) && in_array(get_class($db), array('Zend_Db_Adapter_Mysqli'))) {
$explainQuery = $db->query('EXPLAIN ' . $query->getQuery(), $query->getQueryParams());
$explainRows = $explainQuery->fetchAll();
if ($explainRows) {
$explainOutput .= '<table border="1">' . '<tr>' . '<th>Select Type</th><th>Table</th><th>Type</th><th>Possible Keys</th>' . '<th>Key</th><th>Key Len</th><th>Ref</th><th>Rows</th><th>Extra</th>' . '</tr>';
foreach ($explainRows as $explainRow) {
foreach ($explainRow as $key => $value) {
if (trim($value) === '') {
$explainRow[$key] = ' ';
} else {
$explainRow[$key] = htmlspecialchars($value);
}
}
$explainOutput .= '<tr>' . '<td>' . $explainRow['select_type'] . '</td>' . '<td>' . $explainRow['table'] . '</td>' . '<td>' . $explainRow['type'] . '</td>' . '<td>' . $explainRow['possible_keys'] . '</td>' . '<td>' . $explainRow['key'] . '</td>' . '<td>' . $explainRow['key_len'] . '</td>' . '<td>' . $explainRow['ref'] . '</td>' . '<td>' . $explainRow['rows'] . '</td>' . '<td>' . $explainRow['Extra'] . '</td>' . '</tr>';
}
$explainOutput .= '</table>';
}
}
$return['queryHtml'] .= '<li>' . '<pre>' . htmlspecialchars($queryText) . '</pre>' . ($boundParams ? '<div><strong>Params:</strong> ' . implode(', ', $boundParams) . '</div>' : '') . '<div><strong>Run Time:</strong> ' . number_format($query->getElapsedSecs(), 6) . '</div>' . $explainOutput . "</li>\n";
$return['totalQueryRunTime'] += $query->getElapsedSecs();
}
$return['queryHtml'] .= '</ol>';
}
return $return;
}
/**
* @group 9294
*/
public function testGcWithAutoQuoteIdentifiersEnabledAndDisabled()
{
$config = $this->_saveHandlerTableConfig;
$configDb = array('options' => array('autoQuoteIdentifiers' => false), 'profiler' => true);
$this->_setupDb($config['primary'], $configDb);
$config['db'] = $this->_db;
$saveHandler = new Zend_Session_SaveHandler_DbTable($config);
$saveHandler->gc(false);
$lastQuery = $this->_db->getProfiler()->getLastQueryProfile()->getQuery();
$partQueryExpected = "WHERE (modified + lifetime < ";
$this->assertContains($partQueryExpected, $lastQuery);
$configDb = array('options' => array('autoQuoteIdentifiers' => true), 'profiler' => true);
$this->_setupDb($config['primary'], $configDb);
$config['db'] = $this->_db;
$saveHandler = new Zend_Session_SaveHandler_DbTable($config);
$saveHandler->gc(false);
$lastQuery = $this->_db->getProfiler()->getLastQueryProfile()->getQuery();
$partQueryExpected = "WHERE (\"modified\" + \"lifetime\" < ";
$this->assertContains($partQueryExpected, $lastQuery);
}
/**
* @param Zend_Db_Adapter_Abstract $db
* @return void
*/
protected function _startProfiler($db)
{
if ($db instanceof Zend_Db_Adapter_Abstract && Mage::getIsDeveloperMode()) {
$profiler = $db->getProfiler();
$profiler->setEnabled(true)->setFilterQueryType(null);
}
}
/**
* Returns the profiler for this adapter.
*
* @return Zend_Db_Profiler
*/
public function getProfiler()
{
return $this->_adapter->getProfiler();
}
/**
* Setup profiler
*
* @param Zend_Config $profilerConfig
* @param Zend_Db_Adapter_Abstract $db
*/
protected function _setupProfiler(Zend_Config $profilerConfig, Zend_Db_Adapter_Abstract $db)
{
// Get profiler obj
$profiler = $db->getProfiler();
// Handle profiler elapsed secs filter
if ($profilerConfig->get('filter')->get('elapsed_secs')) {
$profiler->setFilterElapsedSecs($profilerConfig->get('filter')->get('elapsed_secs'));
}
// Handle profiler query type filter
if ($profilerConfig->get('filter')->get('query_type')) {
// TODO: Remove eval() hack used for logical OR of values
$profiler->setFilterQueryType(eval("return {$profilerConfig->get('filter')->get('query_type')};"));
}
}
public static function profiler(Zend_Db_Adapter_Abstract $database)
{
if ($database->getProfiler()->getEnabled()) {
$totalTime = $database->getProfiler()->getTotalElapsedSecs();
$queryCount = $database->getProfiler()->getTotalNumQueries();
$longestTime = 0;
$longestQuery = null;
$s = '<ol>';
foreach ($database->getProfiler()->getQueryProfiles() as $profile) {
$s .= '<li>' . $profile->getQuery();
$s .= ' <span style="color: #green;">(' . $profile->getElapsedSecs() . ')</span></li>';
if ($profile->getElapsedSecs() > $longestTime) {
$longestTime = $profile->getElapsedSecs();
$longestQuery = $profile->getQuery();
}
}
$s .= '</ol>';
$s .= '<hr />';
$s .= '<ul>';
$s .= '<li>Executed ' . $queryCount . ' queries in ' . $totalTime . ' seconds</li>';
$s .= '<li>Average query length: ' . $totalTime / $queryCount . ' seconds</li>';
$s .= '<li>Queries per second: ' . $queryCount / $totalTime . '</li>';
$s .= '<li>Longest query length: ' . $longestTime . '</li>';
$s .= '<li>Longest query: ' . $longestQuery . '</li>';
$s .= '</ul>';
print $s;
} else {
print 'Profiler must be enabled first: $database->getProfiler()->setEnabled(true);';
}
}
/**
* Create a new connection
*/
private function connect()
{
$genericAdapterOptions = self::$genericAdapterOptions;
$driverOptions = self::$driverOptions;
$adapterParamaters = array('host' => $this->config->host, 'username' => $this->config->username, 'password' => $this->config->password, 'dbname' => $this->config->dbname, 'charset' => $this->config->charset ?: null, 'persistent' => (bool) $this->config->get('persistent', false), 'options' => &$genericAdapterOptions, 'driver_options' => &$driverOptions);
$this->dbType = strtolower($this->config->get('db', 'mysql'));
switch ($this->dbType) {
case 'mssql':
$adapter = 'Pdo_Mssql';
$adapterParamaters['pdoType'] = $this->config->get('pdoType', 'dblib');
break;
case 'mysql':
$adapter = 'Pdo_Mysql';
if ($this->config->use_ssl) {
# The presence of these keys as empty strings or null cause non-ssl connections to fail
if ($this->config->ssl_key) {
$adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_KEY] = $this->config->ssl_key;
}
if ($this->config->ssl_cert) {
$adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CERT] = $this->config->ssl_cert;
}
if ($this->config->ssl_ca) {
$adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CA] = $this->config->ssl_ca;
}
if ($this->config->ssl_capath) {
$adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CAPATH] = $this->config->ssl_capath;
}
if ($this->config->ssl_cipher) {
$adapterParamaters['driver_options'][PDO::MYSQL_ATTR_SSL_CIPHER] = $this->config->ssl_cipher;
}
}
/*
* Set MySQL server SQL modes to behave as closely as possible to Oracle and PostgreSQL. Note that the
* ONLY_FULL_GROUP_BY mode is left on purpose because MySQL requires you to specify all non-aggregate
* columns in the group by list even if the query is grouped by the master table's primary key which is
* valid ANSI SQL though. Further in that case the query plan would suffer if you add more columns to
* the group by list.
*/
$driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET SESSION SQL_MODE=\'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,' . 'NO_AUTO_CREATE_USER,ANSI_QUOTES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION\'';
if (isset($adapterParamaters['charset'])) {
$driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .= ', NAMES ' . $adapterParamaters['charset'];
unset($adapterParamaters['charset']);
}
$driverOptions[PDO::MYSQL_ATTR_INIT_COMMAND] .= ';';
$adapterParamaters['port'] = $this->config->get('port', 3306);
break;
case 'oci':
$adapter = 'Oracle';
unset($adapterParamaters['options']);
unset($adapterParamaters['driver_options']);
$adapterParamaters['driver_options'] = array('lob_as_string' => true);
break;
case 'oracle':
$adapter = 'Pdo_Oci';
break;
case 'pgsql':
$adapter = 'Pdo_Pgsql';
$adapterParamaters['port'] = $this->config->get('port', 5432);
break;
default:
throw new ConfigurationError('Backend "%s" is not supported', $this->dbType);
}
$this->dbAdapter = Zend_Db::factory($adapter, $adapterParamaters);
$this->dbAdapter->setFetchMode(Zend_Db::FETCH_OBJ);
// TODO(el/tg): The profiler is disabled per default, why do we disable the profiler explicitly?
$this->dbAdapter->getProfiler()->setEnabled(false);
}
/**
* Constructor
*/
protected function __construct($blnWithDbh = true, Zend_Config_Xml &$sysConfig, Zend_Config_Xml &$zooConfig, Zend_Config_Xml &$config)
{
/**
* set sys config object
*/
$this->sysConfig = $sysConfig;
/**
* set modules config object
*/
$this->zooConfig = $zooConfig;
/**
* set website config object
*/
$this->config = $config;
/**
* initialize Zend_Log
*/
$this->logger = new Zend_Log();
/**
* initialize Zend_Session_Namespace
*/
$this->objCoreSession = new Zend_Session_Namespace('Core');
/**
* create logfile extension for file writer
*/
$strLogFileExtension = '';
if ($this->sysConfig->logger->priority > Zend_Log::ERR) {
if (isset($_SESSION["sesUserName"]) && isset($_SERVER['REMOTE_ADDR'])) {
$strLogFileExtension = '_' . $_SESSION["sesUserName"] . '_' . $_SERVER['REMOTE_ADDR'];
} else {
if (isset($_SERVER['REMOTE_ADDR'])) {
$strLogFileExtension = '_' . $_SERVER['REMOTE_ADDR'];
} else {
$strLogFileExtension = '_local';
}
}
}
/**
* create log file writer
*/
$writer = new Zend_Log_Writer_Stream(GLOBAL_ROOT_PATH . $this->sysConfig->logger->path . 'log_' . date('Ymd') . $strLogFileExtension . '.log');
$this->logger->addWriter($writer);
/**
* set log priority
*/
$filter = new Zend_Log_Filter_Priority((int) $this->sysConfig->logger->priority);
$this->logger->addFilter($filter);
/**
* get language and set translate object
*/
$this->logger->info('get language from ... ');
if (isset($_GET['language'])) {
$this->logger->info('GET');
$this->strLanguageCode = trim($_GET['language'], '/');
foreach ($this->config->languages->language->toArray() as $arrLanguage) {
if (array_key_exists('code', $arrLanguage) && $arrLanguage['code'] == strtolower($this->strLanguageCode)) {
$this->intLanguageId = $arrLanguage['id'];
break;
}
}
if ($this->intLanguageId == null) {
if (isset($this->objCoreSession->languageId)) {
$this->logger->info('SESSION');
$this->intLanguageId = $this->objCoreSession->languageId;
$this->strLanguageCode = $this->objCoreSession->languageCode;
} else {
$this->logger->info('DEFAULT');
$this->blnIsDefaultLanguage = true;
$this->intLanguageId = $this->sysConfig->languages->default->id;
$this->strLanguageCode = $this->sysConfig->languages->default->code;
}
}
} else {
if (isset($_SERVER['REQUEST_URI']) && preg_match('/^\\/[a-zA-Z\\-]{2,5}\\//', $_SERVER['REQUEST_URI'])) {
$this->logger->info('URI');
preg_match('/^\\/[a-zA-Z\\-]{2,5}\\//', $_SERVER['REQUEST_URI'], $arrMatches);
$this->strLanguageCode = trim($arrMatches[0], '/');
foreach ($this->config->languages->language->toArray() as $arrLanguage) {
if (array_key_exists('code', $arrLanguage) && $arrLanguage['code'] == strtolower($this->strLanguageCode)) {
$this->intLanguageId = $arrLanguage['id'];
break;
}
}
if ($this->intLanguageId == null) {
if (isset($this->objCoreSession->languageId)) {
$this->logger->info('SESSION');
$this->intLanguageId = $this->objCoreSession->languageId;
$this->strLanguageCode = $this->objCoreSession->languageCode;
} else {
$this->logger->info('DEFAULT');
$this->blnIsDefaultLanguage = true;
$this->intLanguageId = $this->sysConfig->languages->default->id;
$this->strLanguageCode = $this->sysConfig->languages->default->code;
}
}
} else {
if (isset($this->objCoreSession->languageId)) {
$this->logger->info('SESSION');
$this->intLanguageId = $this->objCoreSession->languageId;
$this->strLanguageCode = $this->objCoreSession->languageCode;
} else {
$this->logger->info('DEFAULT');
$this->blnIsDefaultLanguage = true;
$this->intLanguageId = $this->sysConfig->languages->default->id;
$this->strLanguageCode = $this->sysConfig->languages->default->code;
}
}
}
/**
* set up zoolu translate obj
*/
$this->intZooluLanguageId = Zend_Auth::getInstance()->hasIdentity() ? Zend_Auth::getInstance()->getIdentity()->languageId : $this->intLanguageId;
$this->strZooluLanguageCode = Zend_Auth::getInstance()->hasIdentity() ? Zend_Auth::getInstance()->getIdentity()->languageCode : $this->strLanguageCode;
if (file_exists(GLOBAL_ROOT_PATH . 'application/zoolu/language/zoolu-' . $this->strZooluLanguageCode . '.mo')) {
$this->translate = new HtmlTranslate('gettext', GLOBAL_ROOT_PATH . 'application/zoolu/language/zoolu-' . $this->strZooluLanguageCode . '.mo');
} else {
$this->translate = new HtmlTranslate('gettext', GLOBAL_ROOT_PATH . 'application/zoolu/language/zoolu-' . $this->zooConfig->languages->default->code . '.mo');
}
// update session language
$this->updateSessionLanguage();
if ($blnWithDbh == true) {
/**
* initialize the ZEND DB Connection
* do lazy connection binding, so db connection will be established on first use with dbh->getConnection()
*/
try {
$pdoParams = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true);
$dbhParameters = array('host' => $this->sysConfig->database->params->host, 'username' => $this->sysConfig->database->params->username, 'password' => $this->sysConfig->database->params->password, 'dbname' => $this->sysConfig->database->params->dbname, 'driver_options' => $pdoParams);
$this->dbh = Zend_Db::factory($this->sysConfig->database->adapter, $dbhParameters);
if ($this->sysConfig->logger->priority == Zend_Log::DEBUG) {
$this->dbh->getProfiler()->setEnabled(true);
}
$this->dbh->getConnection();
$this->dbh->exec('SET CHARACTER SET ' . $this->sysConfig->encoding->db);
Zend_Db_Table::setDefaultAdapter($this->dbh);
/**
* using a default metadata cache for all table objects
*
* set up the cache
*/
$arrFrontendOptions = array('automatic_serialization' => true);
/**
* memcache server configuration
*/
$arrServer = array('host' => Zend_Cache_Backend_Memcached::DEFAULT_HOST, 'port' => Zend_Cache_Backend_Memcached::DEFAULT_PORT, 'persistent' => Zend_Cache_Backend_Memcached::DEFAULT_PERSISTENT);
$arrBackendOptions = array('cache_dir' => GLOBAL_ROOT_PATH . $this->sysConfig->path->cache->tables);
$objCache = Zend_Cache::factory('Core', 'File', $arrFrontendOptions, $arrBackendOptions);
/**
* set the cache to be used with all table objects
*/
Zend_Db_Table_Abstract::setDefaultMetadataCache($objCache);
} catch (Zend_Db_Adapter_Exception $exc) {
$this->logger->err($exc);
header('Location: http://' . $this->sysConfig->hostname);
die;
} catch (Zend_Exception $exc) {
$this->logger->err($exc);
header('Location: http://' . $this->sysConfig->hostname);
die;
}
}
}
