/** * 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); } } } } }
/** * Drops the database table for session data * * @return void */ protected function _dropTable() { if (!$this->_db instanceof AbstractAdapter) { return; } $this->_db->query('DROP TABLE Sessions'); }
/** * 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; }
/** * Executes the current select object and returns the result * * @param integer $fetchMode OPTIONAL * @param mixed $bind An array of data to bind to the placeholders. * @return PDO_Statement|\Zend\Db\Statement */ public function query($fetchMode = null, $bind = array()) { if (!empty($bind)) { $this->bind($bind); } $stmt = $this->_adapter->query($this); if ($fetchMode == null) { $fetchMode = $this->_adapter->getFetchMode(); } $stmt->setFetchMode($fetchMode); return $stmt; }
/** * Helper method to retrieve primary key column * and column location * * @param int $tabid * @return array */ protected function _getPrimaryInfo($tabid) { $sql = "SELECT i.part1, i.part2, i.part3, i.part4, i.part5, i.part6,\n i.part7, i.part8, i.part9, i.part10, i.part11, i.part12,\n i.part13, i.part14, i.part15, i.part16\n FROM sysindexes i\n JOIN sysconstraints c ON c.idxname = i.idxname\n WHERE i.tabid = " . $tabid . " AND c.constrtype = 'P'"; $stmt = $this->_adapter->query($sql); $results = $stmt->fetchAll(); $cols = array(); // this should return only 1 row // unless there is no primary key, // in which case, the empty array is returned if ($results) { $row = $results[0]; } else { return $cols; } $position = 0; foreach ($row as $key => $colno) { $position++; if ($colno == 0) { return $cols; } else { $cols[$colno] = $position; } } }
/** * Support method for fetching rows. * * @param \Zend\DB\Table\Select\Select $select query options. * @return array An array containing the row results in FETCH_ASSOC mode. */ protected function _fetch(Select\Select $select) { $stmt = $this->_db->query($select); $data = $stmt->fetchAll(\Zend\DB\DB::FETCH_ASSOC); return $data; }
/** * Special handling for Pdo query(). * All bind parameter names must begin with ':' * * @param string|\Zend\Db\Select $sql The SQL statement with placeholders. * @param array $bind An array of data to bind to the placeholders. * @return \Zend\Db\Statement\Pdo * @throws \Zend\Db\Adapter\Exception To re-throw PdoException. */ public function query($sql, $bind = array()) { if (empty($bind) && $sql instanceof Select) { $bind = $sql->getBind(); } if (is_array($bind)) { foreach ($bind as $name => $value) { if (!is_int($name) && !preg_match('/^:/', $name)) { $newName = ":$name"; unset($bind[$name]); $bind[$newName] = $value; } } } try { return parent::query($sql, $bind); } catch (\PDOException $e) { throw new \Zend\Db\Statement\Exception($e->getMessage(), $e->getCode(), $e); } }