/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
protected function _stripQuoted($sql)
{
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->_adapter->quoteIdentifier('a');
$d = $d[0];
// get the value used as an escaped delimited id quote,
// e.g. \" or "" or \`
$de = $this->_adapter->quoteIdentifier($d);
$de = substr($de, 1, 2);
$de = str_replace('\\', '\\\\', $de);
// get the character for value quoting
// this should be '
$q = $this->_adapter->quote('a');
$q = $q[0];
// get the value used as an escaped quote,
// e.g. \' or ''
$qe = $this->_adapter->quote($q);
$qe = substr($qe, 1, 2);
$qe = str_replace('\\', '\\\\', $qe);
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove "foo\"bar"
$sql = preg_replace("/{$q}({$qe}|\\\\{2}|[^{$q}])*{$q}/", '', $sql);
// remove 'foo\'bar'
if (!empty($q)) {
$sql = preg_replace("/{$q}({$qe}|[^{$q}])*{$q}/", '', $sql);
}
return $sql;
}
/**
* Truncate a given table.
*
* @param \Zend\Db\Adapter\AbstractAdapter $db
* @param string $tableName
* @return void
*/
protected function _truncate(\Zend\Db\Adapter\AbstractAdapter $db, $tableName)
{
$tableName = $db->quoteIdentifier($tableName);
if ($db instanceof \Zend\Db\Adapter\Pdo\Sqlite) {
$db->query('DELETE FROM ' . $tableName);
} else {
if ($db instanceof \Zend\Db\Adapter\Db2) {
/*if(strstr(PHP_OS, "WIN")) {
$file = tempnam(sys_get_temp_dir(), "zendtestdbibm_");
file_put_contents($file, "");
$db->query('IMPORT FROM '.$file.' OF DEL REPLACE INTO '.$tableName);
unlink($file);
} else {
$db->query('IMPORT FROM /dev/null OF DEL REPLACE INTO '.$tableName);
}*/
throw \Zend\Test\PHPUnit\Db\Exception\InvalidArgumentException("IBM Db2 TRUNCATE not supported.");
} else {
if ($this->_isMssqlOrOracle($db)) {
$db->query('TRUNCATE TABLE ' . $tableName);
} else {
if ($db instanceof \Zend\Db\Adapter\Pdo\PgSql) {
$db->query('TRUNCATE ' . $tableName . ' CASCADE');
} else {
$db->query('TRUNCATE ' . $tableName);
}
}
}
}
}
/**
* _authenticateCreateSelect() - This method creates a Zend_Db_Select object that
* is completely configured to be queried against the database.
*
* @return Zend_Db_Select
*/
protected function _authenticateCreateSelect()
{
// build credential expression
if (empty($this->_credentialTreatment) || strpos($this->_credentialTreatment, '?') === false) {
$this->_credentialTreatment = '?';
}
$credentialExpression = new DBExpr('(CASE WHEN ' . $this->_zendDb->quoteInto($this->_zendDb->quoteIdentifier($this->_credentialColumn, true) . ' = ' . $this->_credentialTreatment, $this->_credential) . ' THEN 1 ELSE 0 END) AS ' . $this->_zendDb->quoteIdentifier($this->_zendDb->foldCase('zend_auth_credential_match')));
// get select
$dbSelect = clone $this->getDbSelect();
$dbSelect->from($this->_tableName, array('*', $credentialExpression))->where($this->_zendDb->quoteIdentifier($this->_identityColumn, true) . ' = ?', $this->_identity);
return $dbSelect;
}
/**
* Render ORDER clause
*
* @param string $sql SQL query
* @return string
*/
protected function _renderOrder($sql)
{
if ($this->_parts[self::ORDER]) {
$order = array();
foreach ($this->_parts[self::ORDER] as $term) {
if (is_array($term)) {
if (is_numeric($term[0]) && strval(intval($term[0])) == $term[0]) {
$order[] = (int) trim($term[0]) . ' ' . $term[1];
} else {
$order[] = $this->_adapter->quoteIdentifier($term[0], true) . ' ' . $term[1];
}
} else {
if (is_numeric($term) && strval(intval($term)) == $term) {
$order[] = (int) trim($term);
} else {
$order[] = $this->_adapter->quoteIdentifier($term, true);
}
}
}
$sql .= ' ' . self::SQL_ORDER_BY . ' ' . implode(', ', $order);
}
return $sql;
}
/**
* Ids-specific sequence id value
*
* @param string $sequenceName
* @return integer
*/
public function nextSequenceId($sequenceName)
{
$sql = 'SELECT ' . $this->_adapter->quoteIdentifier($sequenceName) . '.NEXTVAL FROM ' . 'systables WHERE tabid = 1';
$value = $this->_adapter->fetchOne($sql);
return $value;
}
/**
* Returns a quoted schema object. (table name, column name, etc)
*
* @param string $object
* @return string
*/
public function quoteSchemaObject($object)
{
return $this->_connection->quoteIdentifier($object);
}
/**
* Db2-specific sequence id value
*
* @param string $sequenceName
* @return integer
*/
public function nextSequenceId($sequenceName)
{
$sql = 'SELECT NEXTVAL FOR ' . $this->_adapter->quoteIdentifier($sequenceName) . ' AS VAL FROM SYSIbm.SYSDUMMY1';
$value = $this->_adapter->fetchOne($sql);
return $value;
}
/**
* Fetches rows by primary key. The argument specifies one or more primary
* key value(s). To find multiple rows by primary key, the argument must
* be an array.
*
* This method accepts a variable number of arguments. If the table has a
* multi-column primary key, the number of arguments must be the same as
* the number of columns in the primary key. To find multiple rows in a
* table with a multi-column primary key, each argument must be an array
* with the same number of elements.
*
* The find() method always returns a Rowset object, even if only one row
* was found.
*
* @param mixed $key The value(s) of the primary keys.
* @return \Zend\DB\Table\Rowset\AbstractRowset Row(s) matching the criteria.
* @throws \Zend\DB\Table\Exception
*/
public function find()
{
$this->_setupPrimaryKey();
$args = func_get_args();
$keyNames = array_values((array) $this->_primary);
if (count($args) < count($keyNames)) {
throw new Exception("Too few columns for the primary key");
}
if (count($args) > count($keyNames)) {
throw new Exception("Too many columns for the primary key");
}
$whereList = array();
$numberTerms = 0;
foreach ($args as $keyPosition => $keyValues) {
$keyValuesCount = count($keyValues);
// Coerce the values to an array.
// Don't simply typecast to array, because the values
// might be Zend_Db_Expr objects.
if (!is_array($keyValues)) {
$keyValues = array($keyValues);
}
if ($numberTerms == 0) {
$numberTerms = $keyValuesCount;
} else {
if ($keyValuesCount != $numberTerms) {
throw new Exception("Missing value(s) for the primary key");
}
}
$keyValues = array_values($keyValues);
for ($i = 0; $i < $keyValuesCount; ++$i) {
if (!isset($whereList[$i])) {
$whereList[$i] = array();
}
$whereList[$i][$keyPosition] = $keyValues[$i];
}
}
$whereClause = null;
if (count($whereList)) {
$whereOrTerms = array();
$tableName = $this->_db->quoteTableAs($this->_name, null, true);
foreach ($whereList as $keyValueSets) {
$whereAndTerms = array();
foreach ($keyValueSets as $keyPosition => $keyValue) {
$type = $this->_metadata[$keyNames[$keyPosition]]['DATA_TYPE'];
$columnName = $this->_db->quoteIdentifier($keyNames[$keyPosition], true);
$whereAndTerms[] = $this->_db->quoteInto($tableName . '.' . $columnName . ' = ?', $keyValue, $type);
}
$whereOrTerms[] = '(' . implode(' AND ', $whereAndTerms) . ')';
}
$whereClause = '(' . implode(' OR ', $whereOrTerms) . ')';
}
// issue ZF-5775 (empty where clause should return empty rowset)
if ($whereClause == null) {
$rowsetClass = $this->getRowsetClass();
// if (!class_exists($rowsetClass)) {
// end\Loader::loadClass($rowsetClass);
// }
return new $rowsetClass(array('table' => $this, 'rowClass' => $this->getRowClass(), 'stored' => true));
}
return $this->fetchAll($whereClause);
}
