/**
* get all rights of a given tag
*
* @param string $_tagId
* @return Tinebase_Record_RecordSet Set of Tinebase_Model_TagRight
*/
public function getRights($_tagId)
{
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'tags_acl', array('tag_id', 'account_type', 'account_id', 'account_right' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier('account_right'))))->where($this->_db->quoteInto($this->_db->quoteIdentifier('tag_id') . ' = ?', $_tagId))->group(array('tag_id', 'account_type', 'account_id'));
$stmt = $this->_db->query($select);
$rows = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
$rights = new Tinebase_Record_RecordSet('Tinebase_Model_TagRight', $rows, true);
return $rights;
}
/**
* Prepares and executes an SQL statement with bound data.
*
* @param mixed $sql The SQL statement with placeholders.
* May be a string or Zend_Db_Select.
* @param mixed $bind An array of data to bind to the placeholders.
* @return Zend_Db_Statement_Interface
*/
public function query($sql, $bind = array())
{
$attempts = 0;
while ($attempts < self::ATTEMPTS) {
try {
return parent::query($sql, $bind);
} catch (Zend_Db_Exception $ex) {
$this->catchDbException($ex);
$attempts++;
}
}
throw new Exception('Query could not be executed in ' . self::ATTEMPTS . ' attempts. Each waiting for ' . self::SLEEP_BETWEEN_ATTEMPTS . ' seconds. ' . 'See the previous exception: "' . $ex->getMessage() . '"', 0, $ex);
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param array $bind An array of data to bind to the placeholders.
* @return Zend_Db_Statement_Pdo
* @throws Zend_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = array())
{
if (empty($bind) && $sql instanceof Zend_Db_Select) {
$bind = $sql->getBind();
}
if (!empty($bind)) {
foreach ($bind as $name => $value) {
if ($value instanceof Zend_Date) {
$bind[$name] = $value->toString('yyyy-MM-dd HH:mm:ss');
}
}
}
return parent::query($sql, $bind);
}
/**
* get all note types
*
* @param boolean|optional $onlyNonSystemNotes
* @return Tinebase_Record_RecordSet of Tinebase_Model_NoteType
*/
public function getNoteTypes($onlyNonSystemNotes = false, $onlyIds = false)
{
$select = $this->_db->select()->from(array('note_types' => SQL_TABLE_PREFIX . 'note_types'), $onlyIds ? 'id' : '*');
if ($onlyNonSystemNotes) {
$select->where($this->_db->quoteIdentifier('is_user_type') . ' = 1');
}
$stmt = $this->_db->query($select);
if ($onlyIds) {
$types = $stmt->fetchAll(Zend_Db::FETCH_COLUMN);
} else {
$rows = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
$types = new Tinebase_Record_RecordSet('Tinebase_Model_NoteType', $rows, true);
}
return $types;
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'.
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param mixed $bind An array of data or data itself to bind to the placeholders.
*
* @return Zend_Db_Statement_Pdo
* @throws Zend_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = [])
{
$this->_debugTimer();
$result = null;
try {
$this->_checkDdlTransaction($sql);
$this->_prepareQuery($sql, $bind);
$maxTries = 1 + (isset($this->_config['retries']) ? min(max(intval($this->_config['retries']), 0), 5) : 5);
$retryPower = isset($this->_config['retry_power']) ? min(max(intval($this->_config['retry_power']), 1), 5) : 2;
$try = 0;
while ($try < $maxTries) {
try {
$result = Zend_Db_Adapter_Pdo_Mysql::query($sql, $bind);
$try = $maxTries;
} catch (Exception $e) {
$try++;
Mage::log("Max retry:{$maxTries} retry power:{$retryPower}", Zend_Log::DEBUG);
if ($try < $maxTries) {
$message = null;
if ($e instanceof PDOException) {
$message = $e->getMessage();
} elseif ($e->getPrevious() instanceof PDOException) {
$message = $e->getPrevious()->getMessage();
} else {
Mage::log("Exception is instance of " . get_class($e), Zend_Log::DEBUG);
Mage::log("Previous Exception is instance of " . get_class($e->getPrevious()), Zend_Log::DEBUG);
}
if ($message && in_array($message, $this->retryOnMessages)) {
$sleepSeconds = pow($try, $retryPower);
Mage::log("Retrying query [retry:{$try} delay:{$sleepSeconds}]: {$message}", Zend_Log::DEBUG);
if ($try === 1) {
Mage::logException($e);
}
sleep($sleepSeconds);
continue;
}
}
throw $e;
}
}
} catch (Exception $e) {
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
$this->_debugException($e);
}
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
return $result;
}
/**
* SUPER-FIXME!
*/
public static function getAllAsociado($sitio = null, $pagina = null, $limit = 0, $orden = "id")
{
$registry = Zend_Registry::getInstance();
$r = $registry->get('config');
$db = new Zend_Db_Adapter_Pdo_Mysql(array('host' => $r->db->config->host, 'username' => $r->db->config->username, 'password' => $r->db->config->password, 'dbname' => $r->db->config->dbname));
$sql = ' select distinct(a.id),a.*, pa.* from archivos as a left join paginas_archivos as pa ON pa.id_archivo = a.id ';
if ($sitio) {
$where = "where ";
$sql_add[] = "a.id_sitio = " . $sitio . " ";
}
if ($pagina) {
$where = "where ";
$sql_add[] = "pa.id_pagina = " . $pagina . " ";
}
$where = $where . implode(' AND ', $sql_add);
echo $sql . $where;
$result = $db->query($sql . $where);
$archivos = $result->fetchAll();
return $archivos;
}
/**
* returns rights for given application and accountId
*
* @param string $_application the name of the application
* @param int $_accountId the numeric account id
* @return array list of rights
* @throws Tinebase_Exception_AccessDenied
*
* @todo add right group by to statement if possible or remove duplicates in result array
*/
public function getApplicationRights($_application, $_accountId)
{
$application = Tinebase_Application::getInstance()->getApplicationByName($_application);
if ($application->status != 'enabled') {
throw new Tinebase_Exception_AccessDenied('User has no rights. the application is disabled.');
}
$roleMemberships = $this->getRoleMemberships($_accountId);
$select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'role_rights', array('account_rights' => Tinebase_Backend_Sql_Command::getAggregateFunction($this->_db, $this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'role_rights.right'))))->where($this->_db->quoteInto($this->_db->quoteIdentifier(SQL_TABLE_PREFIX . 'role_rights.application_id') . ' = ?', $application->getId()))->where($this->_db->quoteInto($this->_db->quoteIdentifier('role_id') . ' IN (?)', $roleMemberships))->group(SQL_TABLE_PREFIX . 'role_rights.application_id');
$stmt = $this->_db->query($select);
$row = $stmt->fetch(Zend_Db::FETCH_ASSOC);
if ($row === false) {
return array();
}
$rights = explode(',', $row['account_rights']);
// remove duplicates
$result = array();
foreach ($rights as $right) {
if (!in_array($right, $result)) {
$result[] = $right;
}
}
return $result;
}
/**
* Operational test for the MySQL database instance:
* @param PDO|Zend_Db_Adapter_Pdo_Mysql $db Zend Db adapter instance
* @return boolean sanity check result
*/
private static function testDb($db)
{
$expected = array('posts' => null, 'users' => null, 'topics' => null, 'attachments' => null);
foreach ($db->query('show tables') as $row) {
unset($expected[array_pop($row)]);
}
if (count($expected)) {
ZFDemo_Log::log(_('database missing tables: ') . implode(' ', $expected));
return false;
}
return true;
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param array $bind An array of data to bind to the placeholders.
* @return Zend_Db_Pdo_Statement
* @throws Zend_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = array())
{
$this->_debugTimer();
try {
$sql = (string) $sql;
if (strpos($sql, ':') !== false || strpos($sql, '?') !== false) {
$this->_bindParams = $bind;
$sql = preg_replace_callback('#(([\'"])((\\2)|((.*?[^\\\\])\\2)))#', array($this, 'proccessBindCallback'), $sql);
Varien_Exception::processPcreError();
$bind = $this->_bindParams;
}
$result = parent::query($sql, $bind);
} catch (Exception $e) {
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
$this->_debugException($e);
}
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
return $result;
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'.
*
* @param string|\Zend_Db_Select $sql The SQL statement with placeholders.
* @param mixed $bind An array of data or data itself to bind to the placeholders.
* @return \Zend_Db_Statement_Pdo|void
* @throws \Zend_Db_Adapter_Exception To re-throw \PDOException.
* @throws LocalizedException In case multiple queries are attempted at once, to protect from SQL injection
* @SuppressWarnings(PHPMD.CyclomaticComplexity)
*/
protected function _query($sql, $bind = [])
{
$connectionErrors = [
2006, // SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
2013, // SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
];
$triesCount = 0;
do {
$retry = false;
$this->logger->startTimer();
try {
$this->_checkDdlTransaction($sql);
$this->_prepareQuery($sql, $bind);
$result = parent::query($sql, $bind);
$this->logger->logStats(LoggerInterface::TYPE_QUERY, $sql, $bind, $result);
return $result;
} catch (\Exception $e) {
// Finalize broken query
$profiler = $this->getProfiler();
if ($profiler instanceof Profiler) {
/** @var Profiler $profiler */
$profiler->queryEndLast();
}
/** @var $pdoException \PDOException */
$pdoException = null;
if ($e instanceof \PDOException) {
$pdoException = $e;
} elseif (($e instanceof \Zend_Db_Statement_Exception) && ($e->getPrevious() instanceof \PDOException)) {
$pdoException = $e->getPrevious();
}
// Check to reconnect
if ($pdoException && $triesCount < self::MAX_CONNECTION_RETRIES
&& in_array($pdoException->errorInfo[1], $connectionErrors)
) {
$retry = true;
$triesCount++;
$this->closeConnection();
$this->_connect();
}
if (!$retry) {
$this->logger->logStats(LoggerInterface::TYPE_QUERY, $sql, $bind);
$this->logger->critical($e);
throw $e;
}
}
} while ($retry);
}
private function connectDB()
{
$xml = readLangFile('config/config.xml');
setIncludePath(array('special' => array('folder' => $xml->host->folder)));
try {
$dbconfig = array('host' => $xml->database->host, 'username' => $xml->database->username, 'password' => $xml->database->password, 'dbname' => $xml->database->db);
//here we check for database type
switch ($xml->database->type) {
case "mysql":
require_once 'Zend/Db/Adapter/Pdo/Mysql.php';
$db = new Zend_Db_Adapter_Pdo_Mysql($dbconfig);
$db->getConnection();
$db->setFetchMode(Zend_Db::FETCH_ASSOC);
$smt = $db->query("SET NAMES 'utf8'");
$smt->execute();
break;
default:
require_once 'Zend/Db/Adapter/Pdo/Mysql.php';
$db = new Zend_Db_Adapter_Pdo_Mysql($dbconfig);
$db->getConnection();
$db->setFetchMode(Zend_Db::FETCH_ASSOC);
$smt = $db->query("SET NAMES 'utf8'");
$smt->execute();
}
} catch (Zend_Db_Adapter_Exception $e) {
$this->triggerError($this->language['db_connect_error']);
} catch (Zend_Exception $e) {
$this->triggerError($this->language['db_factory_error']);
}
$this->db = $db;
}
function query($sql, $bind = array())
{
My_Logger::log('QUERY: ' . $sql);
return parent::query($sql, $bind);
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'.
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param mixed $bind An array of data or data itself to bind to the placeholders.
* @return Zend_Db_Pdo_Statement
* @throws Zend_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = array())
{
$this->_debugTimer();
try {
//commenting below function as it doesnot alow user to perform trucate or create using begin and commit functionalities - Issue in Magento Enterprise 1.12
//$this->_checkDdlTransaction($sql);
$this->_prepareQuery($sql, $bind);
$result = parent::query($sql, $bind);
} catch (Exception $e) {
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
$this->_debugException($e);
}
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
return $result;
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'.
*
* @param string|\Zend_Db_Select $sql The SQL statement with placeholders.
* @param mixed $bind An array of data or data itself to bind to the placeholders.
* @return \Zend_Db_Statement_Pdo|void
* @throws \Zend_Db_Adapter_Exception To re-throw \PDOException.
*/
public function query($sql, $bind = array())
{
$connectionErrors = array(2006, 2013);
$triesCount = 0;
do {
$retry = false;
$this->_debugTimer();
try {
$this->_checkDdlTransaction($sql);
$this->_prepareQuery($sql, $bind);
$result = parent::query($sql, $bind);
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
return $result;
} catch (\Exception $e) {
// Finalize broken query
$profiler = $this->getProfiler();
if ($profiler instanceof Profiler) {
/** @var Profiler $profiler */
$profiler->queryEndLast();
}
/** @var $pdoException \PDOException */
$pdoException = null;
if ($e instanceof \PDOException) {
$pdoException = $e;
} elseif ($e instanceof \Zend_Db_Statement_Exception && $e->getPrevious() instanceof \PDOException) {
$pdoException = $e->getPrevious();
}
// Check to reconnect
if ($pdoException && $triesCount < self::MAX_CONNECTION_RETRIES && in_array($pdoException->errorInfo[1], $connectionErrors)) {
$retry = true;
$triesCount++;
$this->closeConnection();
$this->_connect();
}
if (!$retry) {
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
$this->_debugException($e);
}
}
} while ($retry);
}
/**
* Prepares and executes an SQL statement with bound data.
* Caches prepared statements to avoid preparing the same query more than once
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param array $bind An array of data to bind to the placeholders.
* @return Zend_Db_Statement_Interface
*/
public function query($sql, $bind = array())
{
if (!is_string($sql)) {
return parent::query($sql, $bind);
}
if (isset($this->cachePreparedStatement[$sql])) {
if (!is_array($bind)) {
$bind = array($bind);
}
$stmt = $this->cachePreparedStatement[$sql];
$stmt->execute($bind);
return $stmt;
}
$stmt = parent::query($sql, $bind);
$this->cachePreparedStatement[$sql] = $stmt;
return $stmt;
}
private function connectDB()
{
try {
$dbconfig = array('host' => $this->config->database->host, 'username' => $this->config->database->username, 'password' => $this->config->database->password, 'dbname' => $this->config->database->db);
$dbconfig = $this->doQoolHook('front_pre_connectdb', $dbconfig);
//here we check for database type
switch ($this->config->database->type) {
case "mysql":
$db = new Zend_Db_Adapter_Pdo_Mysql($dbconfig);
$db->getConnection();
$db->setFetchMode(Zend_Db::FETCH_ASSOC);
$smt = $db->query("SET NAMES 'utf8'");
$smt->execute();
break;
case "sqlite":
$db = new Zend_Db_Adapter_Pdo_Sqlite($dbconfig);
$db->getConnection();
$db->setFetchMode(Zend_Db::FETCH_ASSOC);
break;
default:
$db = new Zend_Db_Adapter_Pdo_Mysql($dbconfig);
$db->getConnection();
$db->setFetchMode(Zend_Db::FETCH_ASSOC);
$smt = $db->query("SET NAMES 'utf8'");
$smt->execute();
}
} catch (Zend_Db_Adapter_Exception $e) {
$this->triggerError($this->language['db_connect_error']);
} catch (Zend_Exception $e) {
$this->triggerError($this->language['db_factory_error']);
}
$db = $this->doQoolHook('front_after_connectdb', $db);
$this->db = $db;
}
/**
* Operational test for the MySQL database instance:
*/
private static function testDb(Zend_Db_Adapter_Pdo_Mysql $db)
{
// (re)load the DB instance with the ZF Demo tables and data (overwrites existing tables)
$registry = Zend_Registry::getInstance();
$filename = $registry['configDir'] . 'zfdemo.' . $registry['config']->db->type . '.sql';
$statements = preg_split('/;\\n/', file_get_contents($filename, false));
foreach ($statements as $blocks) {
$sql = '';
foreach (explode("\n", $blocks) as $line) {
if (empty($line) || !strncmp($line, '--', 2)) {
continue;
}
$sql .= $line . "\n";
}
$sql = trim($sql);
if (!empty($sql)) {
$db->query($sql);
}
}
// test to see if tables were loaded
$expected = array('posts' => null, 'users' => null, 'topics' => null, 'attachments' => null);
try {
foreach ($db->query('show tables') as $row) {
unset($expected[array_pop($row)]);
}
if (count($expected)) {
ZFDemo_Log::log('database missing tables: ' . implode(' ', $expected));
return false;
}
return true;
} catch (Exception $exception) {
echo <<<EOD
<html><head><title>Missing PDO MySQL driver?</title></head><body>
<pre>
Please make sure the PDO MySQL extension has been installed and enabled.';
Either install the PECL packages below, or compile PHP with:
<b>--enable=mysql --enable-pdo --with-pdo-mysql</b>
<a href="http://www.php.net/manual/en/install.pecl.php">http://www.php.net/manual/en/install.pecl.php</a>
<a href="http://pecl.php.net/package/PDO">http://pecl.php.net/package/PDO</a>
<a href="http://pecl.php.net/package/mysql">http://pecl.php.net/package/mysql</a>
EOD;
echo "Exception: <b>", $exception->getMessage(), "</b>: \n";
echo substr(print_r($exception, true), 0, 1024);
echo '</pre>';
phpinfo(E_ALL);
exit;
}
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'.
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param mixed $bind An array of data or data itself to bind to the placeholders.
* @return Zend_Db_Statement_Pdo
* @throws Zend_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = array())
{
$this->_debugTimer();
try {
#$this->_checkDdlTransaction($sql);
$this->_prepareQuery($sql, $bind);
$result = parent::query($sql, $bind);
} catch (Exception $e) {
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
$this->_debugException($e);
}
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
return $result;
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'.
*
* @param string|Zend_Db_Select $sql The SQL statement with placeholders.
* @param mixed $bind An array of data or data itself to bind to the placeholders.
* @return Zend_Db_Statement_Pdo
* @throws Zend_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = array())
{
$this->_debugTimer();
try {
$sql = (string) $sql;
if (strpos($sql, ':') !== false || strpos($sql, '?') !== false) {
$this->_bindParams = $bind;
## $sql = preg_replace_callback('#((["])((2)|((.*?[^\])2))")#', array($this, 'proccessBindCallback'), $sql);
$sql = preg_replace_callback("/#(([\\'\"])((2)|((.*?[^\\\\])2)))#/", array($this, 'proccessBindCallback'), $sql);
$bind = $this->_bindParams;
}
$code = 'SQL: ' . $sql . "\r\n";
if ($bind) {
$code .= 'BIND: ' . print_r($bind, true) . "\r\n";
}
$this->_debugWriteToFile("[" . date('Y-m-d H:i:s') . "] " . $code);
$result = parent::query($sql, $bind);
} catch (Exception $e) {
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
$this->_debugException($e);
}
$this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
return $result;
}
