/**
* Get sql select string or object
*
* @param bool $stringMode
* @return string || Zend_Db_Select
*/
public function getSelectSql($stringMode = false)
{
if ($stringMode) {
return $this->_select->__toString();
}
return $this->_select;
}
/**
* Returns an array of items for a page.
*
* @param integer $offset Page offset
* @param integer $itemCountPerPage Number of items per page
* @return array
*/
public function getItems($offset, $itemCountPerPage)
{
// Cast to integers, as $itemCountPerPage can be string sometimes and that would fail later checks
$offset = (int) $offset;
$itemCountPerPage = (int) $itemCountPerPage;
if ($this->_lastOffset === $offset && $this->_lastItemCount === $itemCountPerPage && null !== $this->_lastItems) {
return $this->_lastItems;
}
$this->_lastOffset = $offset;
$this->_lastItemCount = $itemCountPerPage;
// Optimization: by using the MySQL feature SQL_CALC_FOUND_ROWS
// we can get the count and the results in a single query.
$db = $this->_select->getAdapter();
if (null === $this->_count && $db instanceof \Zend_Db_Adapter_Mysqli) {
$this->_select->limit($itemCountPerPage, $offset);
$sql = $this->_select->__toString();
if (\MUtil_String::startsWith($sql, 'select ', true)) {
$sql = 'SELECT SQL_CALC_FOUND_ROWS ' . substr($sql, 7);
}
$this->_lastItems = $db->fetchAll($sql);
$this->_count = $db->fetchOne('SELECT FOUND_ROWS()');
} else {
$this->_lastItems = $this->_selectAdapter->getItems($offset, $itemCountPerPage);
}
if (is_array($this->_lastItems)) {
if (isset($this->_model->prefetchIterator) && $this->_model->prefetchIterator) {
$this->_lastItems = new \ArrayIterator($this->_lastItems);
}
$this->_lastItems = $this->_model->processAfterLoad($this->_lastItems, false, false);
}
return $this->_lastItems;
}
/**
* Get full details for a given record
*
* @param array $where Conditions to build query
*
* @return false|array
*/
public function fetchDetail(array $where)
{
foreach ($where as $field => $value) {
if (array_key_exists($field, $this->_fields)) {
$field = $this->_fields[$field]['field'];
}
$this->_select->where($field . '=?', $value);
}
$this->_select->reset(Zend_Db_Select::LIMIT_COUNT);
$this->_select->reset(Zend_Db_Select::LIMIT_OFFSET);
if ($this->_cache['enable'] == 1) {
$hash = 'Bvb_Grid' . md5($this->_select->__toString());
if (!($result = $this->_cache['instance']->load($hash))) {
$final = $this->_select->query(Zend_Db::FETCH_ASSOC);
$result = $final->fetchAll();
$this->_cache['instance']->save($result, $hash, array($this->_cache['tag']));
}
} else {
$final = $this->_select->query(Zend_Db::FETCH_ASSOC);
$result = $final->fetchAll();
}
if (!isset($result[0])) {
return false;
}
if (isset($result[0]['ZFG_GHOST'])) {
unset($result[0]['ZFG_GHOST']);
}
return $result[0];
}
/**
* Produces the string version of the query that's built so far
* @return string
*/
public function query()
{
try {
return $this->_select->__toString();
} catch (Exception $e) {
return $e->getMessage();
}
}
/**
* _authenticateQuerySelect() - This method accepts a Zend_Db_Select object and
* performs a query against the database with that object.
*
* @param Zend_Db_Select $dbSelect
* @throws Zend_Auth_Adapter_Exception - when an invalid select
* object is encountered
* @return array
*/
protected function _authenticateQuerySelect(Zend_Db_Select $dbSelect)
{
try {
if ($this->_zendDb->getFetchMode() != Zend_DB::FETCH_ASSOC) {
$origDbFetchMode = $this->_zendDb->getFetchMode();
$this->_zendDb->setFetchMode(Zend_DB::FETCH_ASSOC);
}
$resultIdentities = $this->_zendDb->fetchAll($dbSelect->__toString());
if (isset($origDbFetchMode)) {
$this->_zendDb->setFetchMode($origDbFetchMode);
unset($origDbFetchMode);
}
} catch (Exception $e) {
/**
* @see Zend_Auth_Adapter_Exception
*/
require_once 'Zend/Auth/Adapter/Exception.php';
throw new Zend_Auth_Adapter_Exception('The supplied parameters to Zend_Auth_Adapter_DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e);
}
return $resultIdentities;
}
/**
* fetch rows from db
*
* @param Zend_Db_Select $_select
* @param string $_mode
* @return array
*/
protected function _fetch(Zend_Db_Select $_select, $_mode = self::FETCH_MODE_SINGLE)
{
if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . $_select->__toString());
}
Tinebase_Backend_Sql_Abstract::traitGroup($_select);
$this->_checkTracing($_select);
$stmt = $this->_db->query($_select);
if ($_mode === self::FETCH_ALL) {
$result = (array) $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
} else {
$result = array();
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {
if ($_mode === self::FETCH_MODE_SINGLE) {
$result[] = $row[0];
} else {
if ($_mode === self::FETCH_MODE_PAIR) {
$result[$row[0]] = $row[1];
}
}
}
}
return $result;
}
/**
* Performs a validation on the select query before passing back to the parent class.
* Ensures that only columns from the primary Zend_Db_Table are returned in the result.
*
* @return string This object as a SELECT string.
*/
public function __toString()
{
$fields = $this->getPart(Zend_Db_Table_Select::COLUMNS);
$primary = $this->_info[Zend_Db_Table_Abstract::NAME];
$schema = $this->_info[Zend_Db_Table_Abstract::SCHEMA];
// If no fields are specified we assume all fields from primary table
if (!count($fields)) {
$this->from($primary, self::SQL_WILDCARD, $schema);
$fields = $this->getPart(Zend_Db_Table_Select::COLUMNS);
}
$from = $this->getPart(Zend_Db_Table_Select::FROM);
if ($this->_integrityCheck !== false) {
foreach ($fields as $columnEntry) {
list($table, $column) = $columnEntry;
// Check each column to ensure it only references the primary table
if ($column) {
if (!isset($from[$table]) || $from[$table]['tableName'] != $primary) {
trigger_error("Select query cannot join with another table", E_USER_WARNING);
return '';
}
}
}
}
return parent::__toString();
}
/**
* Adds a sub-select table to the FROM clause of the SELECT query.
*
* Adds a table to the FROM clause of the SELECT statement. At any moment,
* a current set of tables is maintained in the object, so when a new one
* is added, it is combined with the current set.
* @param object SelectQueryInterface $subQuery
* @param integer $joinType Specifies what type of join to perform between
* the current set of tables and the table being added. Could be one of
* the following: NO_JOIN, LEFT_JOIN, INNER_JOIN, RIGHT_JOIN.
* @param string $joinCondition If a join is to be performed, then this
* will indicate the join condition.
* @param string alias An alias for this table.
* @use NO_JOIN
* @use LEFT_JOIN
* @use INNER_JOIN
* @use RIGHT_JOIN
* @access public
*/
function addDerivedTable(Zend_Db_Select $subQuery, $joinType = NO_JOIN, $joinCondition = "", $alias = "")
{
ArgumentValidator::validate($joinType, IntegerValidatorRule::getRule());
ArgumentValidator::validate($joinCondition, StringValidatorRule::getRule());
ArgumentValidator::validate($alias, StringValidatorRule::getRule());
$this->addTable(new Zend_Db_Expr('(' . $subQuery->__toString() . ')'), $joinType, $joinCondition, $alias);
}
/**
* Get cache identifier base on select
*
* @param Zend_Db_Select $select
* @return string
*/
protected function _getSelectCacheId($select)
{
$id = md5($select->__toString());
if (isset($this->_cacheConf['prefix'])) {
$id = $this->_cacheConf['prefix'] . '_' . $id;
}
return $id;
}
/**
* _authenticateQuerySelect() - This method accepts a Zend_Db_Select object and
* performs a query against the database with that object.
*
* @param Zend_Db_Select $dbSelect
* @throws \Zend\Authentication\Adapter\Exception - when an invalid select
* object is encountered
* @return array
*/
protected function _authenticateQuerySelect(\Zend_Db_Select $dbSelect)
{
try {
if ($this->_zendDb->getFetchMode() != \Zend_DB::FETCH_ASSOC) {
$origDbFetchMode = $this->_zendDb->getFetchMode();
$this->_zendDb->setFetchMode(\Zend_DB::FETCH_ASSOC);
}
$resultIdentities = $this->_zendDb->fetchAll($dbSelect->__toString());
if (isset($origDbFetchMode)) {
$this->_zendDb->setFetchMode($origDbFetchMode);
unset($origDbFetchMode);
}
} catch (\Exception $e) {
throw new Exception('The supplied parameters to Zend\\Authentication\\Adapter\\DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e);
}
return $resultIdentities;
}
/**
*
* @return string SQL Select statement
*/
public function __toString()
{
return $this->sql_select->__toString();
}
/**
* Return the latest SQL Select
* @return string
*/
public function getSql()
{
return $this->_select ? $this->_select->__toString() : false;
}
/**
* Performs a validation on the select query before passing back to the parent class.
* Ensures that only columns from the primary Zend_Db_Table are returned in the result.
*
* @return string This object as a SELECT string.
*/
public function __toString()
{
$fields = $this->getPart(Zend_Db_Table_Select::COLUMNS);
$primary = $this->_info[Zend_Db_Table_Abstract::NAME];
$schema = $this->_info[Zend_Db_Table_Abstract::SCHEMA];
// If no fields are specified we assume all fields from primary table
if (!count($fields)) {
$this->from($primary, '*', $schema);
$fields = $this->getPart(Zend_Db_Table_Select::COLUMNS);
}
$from = $this->getPart(Zend_Db_Table_Select::FROM);
if ($this->_integrityCheck !== false) {
foreach ($fields as $columnEntry) {
list($table, $column) = $columnEntry;
// Check each column to ensure it only references the primary table
if ($column) {
if (!isset($from[$table]) || $from[$table]['tableName'] != $primary) {
require_once 'Zend/Db/Table/Select/Exception.php';
throw new Zend_Db_Table_Select_Exception("Select query cannot join with another table");
}
}
}
}
return parent::__toString();
}
public function __toString()
{
try {
return parent::__toString();
} catch (Exception $e) {
echo $e;
exit;
}
}
