/**
* Converts columns from strings to types according to
* PDOStatement::columnMeta
* http://stackoverflow.com/a/9952703/3006989
*
* @param PDOStatement $st
* @param array $assoc returned by PDOStatement::fetch with PDO::FETCH_ASSOC
* @return copy of $assoc with matching type fields
*/
public static function convertToNativeTypes(PDOStatement $statement, $rows)
{
for ($i = 0; $columnMeta = $statement->getColumnMeta($i); $i++) {
$type = $columnMeta['native_type'];
switch ($type) {
case 'DECIMAL':
case 'TINY':
case 'SHORT':
case 'LONG':
case 'LONGLONG':
case 'INT24':
if (isset($rows[$columnMeta['name']])) {
$rows[$columnMeta['name']] = $rows[$columnMeta['name']] + 0;
} else {
if (is_array($rows) || $rows instanceof Traversable) {
foreach ($rows as &$row) {
if (isset($row[$columnMeta['name']])) {
$row[$columnMeta['name']] = $row[$columnMeta['name']] + 0;
}
}
}
}
break;
case 'DATETIME':
case 'DATE':
case 'TIMESTAMP':
// convert to date type?
break;
// default: keep as string
}
}
return $rows;
}
function fetchAll(PDOStatement $statement)
{
try {
$columnCount = $statement->columnCount();
$manualFetch = false;
$booleanColumns = array();
for ($i = 0; $i < $columnCount; $i++) {
$meta = $statement->getColumnMeta($i);
if ($meta['sqlite:decl_type'] == 'BOOLEAN') {
$manualFetch = true;
$booleanColumns[] = $meta['name'];
}
}
} catch (PDOException $e) {
return $statement->fetchAll();
}
if (!$manualFetch) {
return $statement->fetchAll();
} else {
$results = array();
while ($result = $statement->fetch()) {
foreach ($booleanColumns as $column) {
$result->{$column} = $result->{$column} == 1;
}
$results[] = $result;
}
return $results;
}
}
/**
* Returns metadata for a column in a result set.
*
* @param int $column
* @return mixed
* @throws Zend_Db_Statement_Exception
*/
public function getColumnMeta($column)
{
try {
return $this->_stmt->getColumnMeta($column);
} catch (PDOException $e) {
require_once 'Zend/Db/Statement/Exception.php';
throw new Zend_Db_Statement_Exception($e->getMessage());
}
}
public function dump()
{
$metas = array();
for ($col = 0, $nCols = $this->columnCount(); $col < $nCols; $col++) {
$pdo_metas = parent::getColumnMeta($col);
$metas[] = array('name' => $pdo_metas['name'], 'max_length' => $pdo_metas['len'], 'type' => $pdo_metas['native_type']);
}
return array('RESULTSET' => $this->fetchAll(Driver::FETCH_NUM), 'COLUMN_META' => $metas, 'CREATED' => $this->timeCreated);
}
/**
* @param \PDOStatement $stmt
* @return array
*/
private function getFields(\PDOStatement $stmt)
{
$fields = [];
$columnCount = $stmt->columnCount();
for ($i = 0; $i < $columnCount; $i++) {
$fields[$i] = $stmt->getColumnMeta($i);
}
return $fields;
}
protected function bindColumns()
{
if (!$this->columnsBound) {
$this->columnsBound = true;
$this->actRow = array();
$this->actRowIndex = array();
$count = $this->pdoStmt->columnCount();
for ($i = 0; $i < $count; $i++) {
$meta = $this->pdoStmt->getColumnMeta($i);
if (array_key_exists('native_type', $meta) && $meta['native_type'] == 'BLOB') {
$this->pdoStmt->bindColumn($i + 1, $this->actRow[$meta['name']], \PDO::PARAM_LOB);
} else {
$this->pdoStmt->bindColumn($i + 1, $this->actRow[$meta['name']]);
}
$this->actRowIndex[$i] =& $this->actRow[$meta['name']];
}
}
}
/**
* Load the column metadata from the last query.
* @return array
*/
public function load_col_info()
{
if ($this->col_info) {
return $this->col_info;
}
$num_fields = $this->result->columnCount();
for ($i = 0; $i < $num_fields; $i++) {
$this->col_info[$i] = (object) $this->result->getColumnMeta($i);
}
return $this->col_info;
}
private function fetchMeta()
{
if ($this->fieldnames === null) {
$this->rawFieldnames = [];
$this->fieldnames = [];
$this->tablenames = [];
for ($i = 0; $i < $this->getFields(); ++$i) {
$metadata = $this->stmt->getColumnMeta($i);
// strip table-name from column
$this->fieldnames[] = substr($metadata['name'], strlen($metadata['table'] . '.'));
$this->rawFieldnames[] = $metadata['name'];
if (!in_array($metadata['table'], $this->tablenames)) {
$this->tablenames[] = $metadata['table'];
}
}
}
}
/**
* Translate native pgtype (json for example) to PgBabylon\PDO::PARAM_*
*
* @param $columnIndex
* @return int|null
*/
protected function _getColumnDataType($columnIndex)
{
$meta = $this->_statement->getColumnMeta($columnIndex);
switch ($meta['native_type']) {
case 'json':
return PDO::PARAM_JSON;
case 'timestamp':
return PDO::PARAM_DATETIME;
case 'date':
return PDO::PARAM_DATE;
case '_text':
return PDO::PARAM_ARRAY;
case '_int4':
return PDO::PARAM_ARRAY;
}
return null;
}
/**
* Import table headers and data from \PDOStatement
*
* @param \PDOStatement $stmt
*/
public function importFromPDO(\PDOStatement $stmt)
{
$this->_header = array();
$i = 0;
while (($column = $stmt->getColumnMeta($i++)) !== false) {
$this->_header[] = $column['name'];
}
$this->_data = array();
while (($row = $stmt->fetch(\PDO::FETCH_NUM)) !== false) {
$this->_data[] = $row;
}
// if options is empty we want to regenerate defaults
if (count($this->_options) < 1) {
$this->setOptions();
}
$this->_executeFormats();
}
/**
* Builds a map of the columns contained in a result
*
* @param PDOStatement $results
* @return void
*/
public function resultSet($results)
{
$this->map = array();
$numFields = $results->columnCount();
$index = 0;
while ($numFields-- > 0) {
$column = $results->getColumnMeta($index);
if (empty($column['native_type'])) {
$type = $column['len'] == 1 ? 'boolean' : 'string';
} else {
$type = $column['native_type'];
}
if (strpos($column['name'], '__')) {
list($column['table'], $column['name']) = explode('__', $column['name']);
}
if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) {
$this->map[$index++] = array($column['table'], $column['name'], $type);
} else {
$this->map[$index++] = array(0, $column['name'], $type);
}
}
}
/**
* Finds duplicate columns in select statement
* @param \PDOStatement
* @return string
*/
public static function findDuplicates(\PDOStatement $statement)
{
$cols = [];
for ($i = 0; $i < $statement->columnCount(); $i++) {
$meta = $statement->getColumnMeta($i);
$cols[$meta['name']][] = isset($meta['table']) ? $meta['table'] : '';
}
$duplicates = [];
foreach ($cols as $name => $tables) {
if (count($tables) > 1) {
$tables = array_filter(array_unique($tables));
$duplicates[] = "'{$name}'" . ($tables ? ' (from ' . implode(', ', $tables) . ')' : '');
}
}
return implode(', ', $duplicates);
}
/**
* Define columns from result.
*
* @param \PDOStatement $result
*
* @return array
*/
private function _define_column_types(\PDOStatement $result)
{
$columns = array();
$intTypes = array('int', 'integer', 'long', 'tiny', 'smallint', 'int4');
try {
for ($i = 0; $i < $result->columnCount(); ++$i) {
$meta = $result->getColumnMeta($i);
if ($meta && $meta['name'] != '[]') {
$metaType = strtolower($meta['native_type']);
$pdoType = $meta['pdo_type'];
if (in_array($pdoType, array(\PDO::PARAM_STR, \PDO::PARAM_LOB))) {
$metaType = 'string';
} elseif (in_array($metaType, $intTypes)) {
$metaType = 'integer';
} elseif ($pdoType === \PDO::PARAM_NULL) {
$metaType = 'null';
}
$columns[$meta['name']] = $metaType;
}
}
} catch (\PDOException $e) {
}
return $columns;
}
/**
* @return void
*/
public function getColumnMeta($column = 0)
{
$this->execute();
return parent::getColumnMeta($column);
}
/**
* @param $column
* @return array
*/
public function getColumnMeta($column)
{
$archLog = array('method' => 'PDOStatement::getColumnMeta', 'input' => array('column' => $column), 'output' => $this->PDOStatement->getColumnMeta($column), 'pointer' => $this->assignPointerString());
return self::setLogReturnOutput($archLog);
}
/**
* Fetch all returns columns typed as Recess expects:
* i.e. Dates become Unix Time Based and TinyInts are converted to Boolean
*
* TODO: Refactor this into the query code so that Postgresql does the type conversion
* instead of doing it slow and manually in PHP.
*
* @param PDOStatement $statement
* @return array fetchAll() of statement
*/
function fetchAll(PDOStatement $statement)
{
try {
$columnCount = $statement->columnCount();
$manualFetch = false;
$booleanColumns = array();
$dateColumns = array();
$timeColumns = array();
$pointColumns = array();
$boxColumns = array();
for ($i = 0; $i < $columnCount; $i++) {
$meta = $statement->getColumnMeta($i);
if (isset($meta['native_type'])) {
switch ($meta['native_type']) {
case 'timestamp':
case 'date':
$dateColumns[] = $meta['name'];
break;
case 'time':
$timeColumns[] = $meta['name'];
break;
case 'point':
$pointColumns[] = $meta['name'];
break;
case 'box':
$boxColumns[] = $meta['name'];
}
} else {
if ($meta['len'] == 1) {
$booleanColumns[] = $meta['name'];
}
}
}
if (!empty($booleanColumns) || !empty($datetimeColumns) || !empty($dateColumns) || !empty($timeColumns) || !empty($pointColumns) || !empty($boxColumns)) {
$manualFetch = true;
}
} catch (PDOException $e) {
return $statement->fetchAll();
}
if (!$manualFetch) {
return $statement->fetchAll();
} else {
$results = array();
while ($result = $statement->fetch()) {
foreach ($booleanColumns as $column) {
$result->{$column} = $result->{$column} == 1;
}
foreach ($dateColumns as $column) {
$result->{$column} = strtotime($result->{$column});
}
foreach ($timeColumns as $column) {
$result->{$column} = strtotime($result->{$column});
}
foreach ($pointColumns as $column) {
$matches = array();
preg_match('/^\\((.*),(.*)\\)$/', $result->{$column}, $matches);
if (count($matches) == 3) {
$result->{$column} = array('latlong' => $matches[0], 'latitude' => $matches[1], 'longitude' => $matches[2]);
}
}
foreach ($boxColumns as $column) {
$matches = array();
preg_match('/^\\((.*),(.*)\\),\\((.*),(.*)\\)$/', $result->{$column}, $matches);
if (count($matches) == 5) {
$result->{$column} = array('box' => $matches[0], 'neLatitude' => $matches[1], 'neLongitude' => $matches[2], 'swLatitude' => $matches[3], 'swLongitude' => $matches[4]);
}
}
$results[] = $result;
}
return $results;
}
}
function get_convertors(\PDOStatement $statement)
{
$convertors = array();
$column_count = $statement->columnCount();
for ($i = 0; $i < $column_count; $i++) {
$column_meta = $statement->getColumnMeta($i);
if ($column_meta) {
$convertor = null;
switch ($column_meta['native_type']) {
case 'TINY':
$convertor = $column_meta['len'] === 1 ? 'boolval' : 'intval';
break;
case 'LONGLONG':
if ($column_meta['len'] <= PHP_INT_MAX) {
$convertor = 'intval';
}
break;
case 'SHORT':
case 'LONG':
case 'INT24':
case 'BIT':
$convertor = 'intval';
break;
case 'FLOAT':
case 'DOUBLE':
case 'NEWDECIMAL':
$convertor = 'floatval';
break;
}
$convertors[$column_meta['name']] = $convertor;
}
}
return $convertors;
}
/**
* Builds a map of the columns contained in a result
*
* @param PDOStatement $results
*/
function resultSet($results)
{
$this->map = array();
$numFields = $results->columnCount();
$index = 0;
$j = 0;
while ($j < $numFields) {
$column = $results->getColumnMeta($j);
if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) {
$this->map[$index++] = array($column['table'], $column['name']);
} else {
$this->map[$index++] = array(0, $column['name']);
}
$j++;
}
}
/**
* Common column type detection.
* @return array
*/
public static function detectTypes(\PDOStatement $statement)
{
$types = array();
$count = $statement->columnCount();
// driver must be meta-aware, see PHP bugs #53782, #54695
for ($col = 0; $col < $count; $col++) {
$meta = $statement->getColumnMeta($col);
if (isset($meta['native_type'])) {
$types[$meta['name']] = self::detectType($meta['native_type']);
}
}
return $types;
}
/**
* Returns associative array of detected types (IReflection::FIELD_*) in result set.
*/
public function getColumnTypes(\PDOStatement $statement)
{
$types = [];
$count = $statement->columnCount();
for ($col = 0; $col < $count; $col++) {
$meta = $statement->getColumnMeta($col);
if (isset($meta['native_type'])) {
$types[$meta['name']] = $type = Nette\Database\Helpers::detectType($meta['native_type']);
if ($type === Nette\Database\IStructure::FIELD_TIME) {
$types[$meta['name']] = Nette\Database\IStructure::FIELD_TIME_INTERVAL;
}
}
}
return $types;
}
protected function FetchFields()
{
for ($i = 0; $i < $this->statement->columnCount(); $i++) {
$columnMetadata = $this->statement->getColumnMeta($i);
$this->AddField($columnMetadata['name']);
if (isset($columnMetadata['native_type'])) {
$this->nativeTypes[$columnMetadata['name']] = $columnMetadata['native_type'];
}
}
}
/**
233: * Builds a map of the columns contained in a result
234: *
235: * @param PDOStatement $results
236: * @return void
237: */
public function resultSet($results)
{
$this->map = array();
$numFields = $results->columnCount();
$index = 0;
while ($numFields-- > 0) {
$column = $results->getColumnMeta($index);
if ($column['len'] === 1 && (empty($column['native_type']) || $column['native_type'] === 'TINY')) {
$type = 'boolean';
} else {
$type = empty($column['native_type']) ? 'string' : $column['native_type'];
}
if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) {
$this->map[$index++] = array($column['table'], $column['name'], $type);
} else {
$this->map[$index++] = array(0, $column['name'], $type);
}
}
}
/**
* Builds a map of the columns contained in a result
*
* @param PDOStatement $results
* @return void
*/
public function resultSet($results)
{
$this->results = $results;
$this->map = array();
$numFields = $results->columnCount();
$index = 0;
$j = 0;
//PDO::getColumnMeta is experimental and does not work with oci,
// so try to figure it out based on the querystring
$querystring = $results->queryString;
if (strpos($querystring, 'cake_paging') !== false) {
$querystring = preg_replace('/^SELECT \\* FROM \\((.*)\\) cake_paging.+$/s', '$1', $querystring);
$querystring = trim($querystring);
}
if (stripos($querystring, 'SELECT') === 0) {
$last = strripos($querystring, 'FROM');
if ($last !== false) {
$selectpart = substr($querystring, 7, $last - 8);
$selects = String::tokenize($selectpart, ',', '(', ')');
}
} elseif (strpos($querystring, 'PRAGMA table_info') === 0) {
$selects = array('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk');
} elseif (strpos($querystring, 'PRAGMA index_list') === 0) {
$selects = array('seq', 'name', 'unique');
} elseif (strpos($querystring, 'PRAGMA index_info') === 0) {
$selects = array('seqno', 'cid', 'name');
}
while ($j < $numFields) {
if (!isset($selects[$j])) {
$j++;
continue;
}
if (preg_match('/\\bAS\\s+(.*)/i', $selects[$j], $matches)) {
$columnName = trim($matches[1], '"');
} else {
$columnName = trim(str_replace('"', '', $selects[$j]));
}
if (strpos($selects[$j], 'DISTINCT') === 0) {
$columnName = str_ireplace('DISTINCT', '', $columnName);
}
$metaType = false;
try {
$metaData = (array) $results->getColumnMeta($j);
if (!empty($metaData['oci:decl_type'])) {
$metaType = trim($metaData['oci:decl_type']);
}
} catch (Exception $e) {
}
if (strpos($columnName, '__')) {
$parts = explode('__', $columnName);
$this->map[$index++] = array(trim($parts[0]), trim($parts[1]), $metaType);
} else {
$this->map[$index++] = array(0, $columnName, $metaType);
}
$j++;
}
}
/**
* Returns associative array of detected types (IReflection::FIELD_*) in result set.
*/
public function getColumnTypes(\PDOStatement $statement)
{
$types = [];
$count = $statement->columnCount();
for ($col = 0; $col < $count; $col++) {
$meta = $statement->getColumnMeta($col);
if (isset($meta['sqlite:decl_type'])) {
if ($meta['sqlite:decl_type'] === 'DATE') {
$types[$meta['name']] = Nette\Database\IStructure::FIELD_UNIX_TIMESTAMP;
} else {
$types[$meta['name']] = Nette\Database\Helpers::detectType($meta['sqlite:decl_type']);
}
} elseif (isset($meta['native_type'])) {
$types[$meta['name']] = Nette\Database\Helpers::detectType($meta['native_type']);
}
}
return $types;
}
public function bind(PDOStatement $stmt, YokazeDb_Vo $vo)
{
$columnCount = $stmt->columnCount();
$columnTypes = array();
$columnNames = array();
$VoNames = array();
for ($i = 0; $i < $columnCount; $i++){
$meta = $stmt->getColumnMeta($i);
if (!$meta)
break;
//if (isset($meta['pdo_type']))
$columnTypes[$i] = $meta['pdo_type'];
$columnNames[$i] = $meta['name'];
$name = implode('', array_map('ucfirst', explode('_', $meta['name'])));
$name[0] = strtolower($name[0]);
$voNames[$i] = $name;
}
for($i = 0; $i < $columnCount; $i++){
$name = $voNames[$i];
$vo->$name = null;
$stmt->bindColumn($columnNames[$i], $vo->$name, $columnTypes[$i]);
}
}
/**
* Builds a map of the columns contained in a result
*
* @param PDOStatement $results
* @return void
*/
public function resultSet($results)
{
$this->map = array();
$numFields = $results->columnCount();
$index = 0;
while ($numFields-- > 0) {
$column = $results->getColumnMeta($index);
$name = $column['name'];
if (strpos($name, '__')) {
if (isset($this->_fieldMappings[$name]) && strpos($this->_fieldMappings[$name], '.')) {
$map = explode('.', $this->_fieldMappings[$name]);
} elseif (isset($this->_fieldMappings[$name])) {
$map = array(0, $this->_fieldMappings[$name]);
} else {
$map = array(0, $name);
}
} else {
$map = array(0, $name);
}
$map[] = $column['sqlsrv:decl_type'] === 'bit' ? 'boolean' : $column['native_type'];
$this->map[$index++] = $map;
}
}
/**
* Fetch all returns columns typed as Recess expects:
* i.e. Dates become Unix Time Based and TinyInts are converted to Boolean
*
* TODO: Refactor this into the query code so that MySql does the type conversion
* instead of doing it slow and manually in PHP.
*
* @param PDOStatement $statement
* @return array fetchAll() of statement
*/
function fetchAll(PDOStatement $statement)
{
try {
$columnCount = $statement->columnCount();
$manualFetch = false;
$booleanColumns = array();
$dateColumns = array();
$timeColumns = array();
for ($i = 0; $i < $columnCount; $i++) {
$meta = $statement->getColumnMeta($i);
if (isset($meta['native_type'])) {
switch ($meta['native_type']) {
case 'TIMESTAMP':
case 'DATETIME':
case 'DATE':
$dateColumns[] = $meta['name'];
break;
case 'TIME':
$timeColumns[] = $meta['name'];
break;
}
} else {
if ($meta['len'] == 1) {
$booleanColumns[] = $meta['name'];
}
}
}
if (!empty($booleanColumns) || !empty($datetimeColumns) || !empty($dateColumns) || !empty($timeColumns)) {
$manualFetch = true;
}
} catch (PDOException $e) {
return $statement->fetchAll();
}
if (!$manualFetch) {
return $statement->fetchAll();
} else {
$results = array();
while ($result = $statement->fetch()) {
foreach ($booleanColumns as $column) {
$result->{$column} = $result->{$column} == 1;
}
foreach ($dateColumns as $column) {
$result->{$column} = strtotime($result->{$column});
}
foreach ($timeColumns as $column) {
$result->{$column} = strtotime('1970-01-01 ' . $result->{$column});
}
$results[] = $result;
}
return $results;
}
}
/**
* @param int $column
* @return array
* @throws \Compeek\PDOWrapper\NotConnectedException
*/
public function getColumnMeta($column)
{
$this->requireConnection();
return $this->pdoStatement->getColumnMeta($column);
}
/**
* Returns associative array of detected types (IReflection::FIELD_*) in result set.
*/
public function getColumnTypes(\PDOStatement $statement)
{
$types = array();
$count = $statement->columnCount();
for ($col = 0; $col < $count; $col++) {
$meta = $statement->getColumnMeta($col);
if (isset($meta['sqlsrv:decl_type']) && $meta['sqlsrv:decl_type'] !== 'timestamp') {
// timestamp does not mean time in sqlsrv
$types[$meta['name']] = Nette\Database\Helpers::detectType($meta['sqlsrv:decl_type']);
} elseif (isset($meta['native_type'])) {
$types[$meta['name']] = Nette\Database\Helpers::detectType($meta['native_type']);
}
}
return $types;
}
