/**
* Db2 catalog lookup for describe table
*
* @param string $tableName
* @param string $schemaName OPTIONAL
* @return array
*/
public function describeTable($tableName, $schemaName = null)
{
$sql = "SELECT DISTINCT c.tabschema, c.tabname, c.colname, c.colno,\n c.typename, c.default, c.nulls, c.length, c.scale,\n c.identity, tc.type AS tabconsttype, k.colseq\n FROM syscat.columns c\n LEFT JOIN (syscat.keycoluse k JOIN syscat.tabconst tc\n ON (k.tabschema = tc.tabschema\n AND k.tabname = tc.tabname\n AND tc.type = 'P'))\n ON (c.tabschema = k.tabschema\n AND c.tabname = k.tabname\n AND c.colname = k.colname)\n WHERE " . $this->_adapter->quoteInto('UPPER(c.tabname) = UPPER(?)', $tableName);
if ($schemaName) {
$sql .= $this->_adapter->quoteInto(' AND UPPER(c.tabschema) = UPPER(?)', $schemaName);
}
$sql .= " ORDER BY c.colno";
$desc = array();
$stmt = $this->_adapter->query($sql);
/**
* To avoid case issues, fetch using FETCH_NUM
*/
$result = $stmt->fetchAll(\Zend\Db\Db::FETCH_NUM);
/**
* The ordering of columns is defined by the query so we can map
* to variables to improve readability
*/
$tabschema = 0;
$tabname = 1;
$colname = 2;
$colno = 3;
$typename = 4;
$default = 5;
$nulls = 6;
$length = 7;
$scale = 8;
$identityCol = 9;
$tabconstype = 10;
$colseq = 11;
foreach ($result as $key => $row) {
list($primary, $primaryPosition, $identity) = array(false, null, false);
if ($row[$tabconstype] == 'P') {
$primary = true;
$primaryPosition = $row[$colseq];
}
/**
* In Ibm Db2, an column can be IDENTITY
* even if it is not part of the PRIMARY KEY.
*/
if ($row[$identityCol] == 'Y') {
$identity = true;
}
$desc[$this->_adapter->foldCase($row[$colname])] = array('SCHEMA_NAME' => $this->_adapter->foldCase($row[$tabschema]), 'TABLE_NAME' => $this->_adapter->foldCase($row[$tabname]), 'COLUMN_NAME' => $this->_adapter->foldCase($row[$colname]), 'COLUMN_POSITION' => $row[$colno] + 1, 'DATA_TYPE' => $row[$typename], 'DEFAULT' => $row[$default], 'NULLABLE' => (bool) ($row[$nulls] == 'Y'), 'LENGTH' => $row[$length], 'SCALE' => $row[$scale], 'PRECISION' => $row[$typename] == 'DECIMAL' ? $row[$length] : 0, 'UNSIGNED' => false, 'PRIMARY' => $primary, 'PRIMARY_POSITION' => $primaryPosition, 'IDENTITY' => $identity);
}
return $desc;
}
/**
* Ids catalog lookup for describe table
*
* @param string $tableName
* @param string $schemaName OPTIONAL
* @return array
*/
public function describeTable($tableName, $schemaName = null)
{
// this is still a work in progress
$sql = "SELECT DISTINCT t.owner, t.tabname, c.colname, c.colno, c.coltype,\n d.default, c.collength, t.tabid\n FROM syscolumns c\n JOIN systables t ON c.tabid = t.tabid\n LEFT JOIN sysdefaults d ON c.tabid = d.tabid AND c.colno = d.colno\n WHERE " . $this->_adapter->quoteInto('UPPER(t.tabname) = UPPER(?)', $tableName);
if ($schemaName) {
$sql .= $this->_adapter->quoteInto(' AND UPPER(t.owner) = UPPER(?)', $schemaName);
}
$sql .= " ORDER BY c.colno";
$desc = array();
$stmt = $this->_adapter->query($sql);
$result = $stmt->fetchAll(\Zend\Db\Db::FETCH_NUM);
/**
* The ordering of columns is defined by the query so we can map
* to variables to improve readability
*/
$tabschema = 0;
$tabname = 1;
$colname = 2;
$colno = 3;
$typename = 4;
$default = 5;
$length = 6;
$tabid = 7;
$primaryCols = null;
foreach ($result as $key => $row) {
$primary = false;
$primaryPosition = null;
if (!$primaryCols) {
$primaryCols = $this->_getPrimaryInfo($row[$tabid]);
}
if (array_key_exists($row[$colno], $primaryCols)) {
$primary = true;
$primaryPosition = $primaryCols[$row[$colno]];
}
$identity = false;
if ($row[$typename] == 6 + 256 || $row[$typename] == 18 + 256) {
$identity = true;
}
$desc[$this->_adapter->foldCase($row[$colname])] = array('SCHEMA_NAME' => $this->_adapter->foldCase($row[$tabschema]), 'TABLE_NAME' => $this->_adapter->foldCase($row[$tabname]), 'COLUMN_NAME' => $this->_adapter->foldCase($row[$colname]), 'COLUMN_POSITION' => $row[$colno], 'DATA_TYPE' => $this->_getDataType($row[$typename]), 'DEFAULT' => $row[$default], 'NULLABLE' => (bool) (!($row[$typename] - 256 >= 0)), 'LENGTH' => $row[$length], 'SCALE' => $row[$typename] == 5 ? $row[$length] & 255 : 0, 'PRECISION' => $row[$typename] == 5 ? (int) ($row[$length] / 256) : 0, 'UNSIGNED' => false, 'PRIMARY' => $primary, 'PRIMARY_POSITION' => $primaryPosition, 'IDENTITY' => $identity);
}
return $desc;
}
/**
* _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;
}
/**
* Internal function for creating the where clause
*
* @param string $condition
* @param mixed $value optional
* @param string $type optional
* @param boolean $bool true = AND, false = OR
* @return string clause
*/
protected function _where($condition, $value = null, $type = null, $bool = true)
{
if (count($this->_parts[self::UNION])) {
throw new SelectException("Invalid use of where clause with " . self::SQL_UNION);
}
if ($value !== null) {
$condition = $this->_adapter->quoteInto($condition, $value, $type);
}
$cond = "";
if ($this->_parts[self::WHERE]) {
if ($bool === true) {
$cond = self::SQL_AND . ' ';
} else {
$cond = self::SQL_OR . ' ';
}
}
return $cond . "({$condition})";
}
/**
* 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);
}
