/**
* 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(JO_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(JO_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;
}
/**
* Called by parent table's class during delete() method.
*
* @param string $parentTableClassname
* @param array $primaryKey
* @return int Number of affected rows
*/
public function _cascadeDelete($parentTableClassname, array $primaryKey)
{
$this->_setupMetadata();
$rowsAffected = 0;
foreach ($this->_getReferenceMapNormalized() as $map) {
if ($map[self::REF_TABLE_CLASS] == $parentTableClassname && isset($map[self::ON_DELETE])) {
switch ($map[self::ON_DELETE]) {
case self::CASCADE:
$where = array();
for ($i = 0; $i < count($map[self::COLUMNS]); ++$i) {
$col = $this->_db->foldCase($map[self::COLUMNS][$i]);
$refCol = $this->_db->foldCase($map[self::REF_COLUMNS][$i]);
$type = $this->_metadata[$col]['DATA_TYPE'];
$where[] = $this->_db->quoteInto($this->_db->quoteIdentifier($col, true) . ' = ?', $primaryKey[$refCol], $type);
}
$rowsAffected += $this->delete($where);
break;
default:
// no action
break;
}
}
}
return $rowsAffected;
}