Esempio n. 1
0
 /**
  * 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;
 }
Esempio n. 2
0
 /**
  * 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;
 }
Esempio n. 3
0
 /**
  * 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;
 }