/**
* 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);
}
}
