<?php

/**

 * Zend Framework

 *

 * LICENSE

 *

 * This source file is subject to the new BSD license that is bundled

 * with this package in the file LICENSE.txt.

 * It is also available through the world-wide-web at this URL:

 * http://framework.zend.com/license/new-bsd

 * If you did not receive a copy of the license and are unable to

 * obtain it through the world-wide-web, please send an email

 * to license@zend.com so we can send you a copy immediately.

 *

 * @category   Zend

 * @package    Zend_Db

 * @subpackage Adapter

 * @copyright  Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)

 * @license    http://framework.zend.com/license/new-bsd     New BSD License

 * @version    $Id: Ids.php 24593 2012-01-05 20:35:02Z matthew $

 */

/** @see Zend_Db_Adapter_Pdo_Ibm */

require_once 'Zend/Db/Adapter/Pdo/Ibm.php';

/** @see Zend_Db_Statement_Pdo_Ibm */

require_once 'Zend/Db/Statement/Pdo/Ibm.php';

/**

 * @category   Zend

 * @package    Zend_Db

 * @subpackage Adapter

 * @copyright  Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)

 * @license    http://framework.zend.com/license/new-bsd     New BSD License

 */

class Zend_Db_Adapter_Pdo_Ibm_Ids

{

    /**

     * @var Zend_Db_Adapter_Abstract

     */

    protected $_adapter = null;

    /**

     * Construct the data server class.

     *

     * It will be used to generate non-generic SQL

     * for a particular data server

     *

     * @param Zend_Db_Adapter_Abstract $adapter

     */

    public function __construct($adapter)

    {

        $this->_adapter = $adapter;

    }

    /**

     * Returns a list of the tables in the database.

     *

     * @return array

     */

    public function listTables()

    {

        $sql = "SELECT tabname "

        . "FROM systables ";

        return $this->_adapter->fetchCol($sql);

    }

    /**

     * 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,

               d.default, c.collength, t.tabid

               FROM syscolumns c

               JOIN systables t ON c.tabid = t.tabid

               LEFT JOIN sysdefaults d ON c.tabid = d.tabid AND c.colno = d.colno

               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(Zend_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;

    }

    /**

     * Map number representation of a data type

     * to a string

     *

     * @param int $typeNo

     * @return string

     */

    protected function _getDataType($typeNo)

    {

        $typemap = array(

            0       => "CHAR",

            1       => "SMALLINT",

            2       => "INTEGER",

            3       => "FLOAT",

            4       => "SMALLFLOAT",

            5       => "DECIMAL",

            6       => "SERIAL",

            7       => "DATE",

            8       => "MONEY",

            9       => "NULL",

            10      => "DATETIME",

            11      => "BYTE",

            12      => "TEXT",

            13      => "VARCHAR",

            14      => "INTERVAL",

            15      => "NCHAR",

            16      => "NVARCHAR",

            17      => "INT8",

            18      => "SERIAL8",

            19      => "SET",

            20      => "MULTISET",

            21      => "LIST",

            22      => "Unnamed ROW",

            40      => "Variable-length opaque type",

            4118    => "Named ROW"

        );

        if ($typeNo - 256 >= 0) {

            $typeNo = $typeNo - 256;

        }

        return $typemap[$typeNo];

    }

    /**

     * 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,

                i.part7, i.part8, i.part9, i.part10, i.part11, i.part12,

                i.part13, i.part14, i.part15, i.part16

                FROM sysindexes i

                JOIN sysconstraints c ON c.idxname = i.idxname

                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;

            }

        }

    }

    /**

     * Adds an IDS-specific LIMIT clause to the SELECT statement.

     *

     * @param string $sql

     * @param integer $count

     * @param integer $offset OPTIONAL

     * @throws Zend_Db_Adapter_Exception

     * @return string

     */

    public function limit($sql, $count, $offset = 0)

    {

        $count = intval($count);

        if ($count < 0) {

            /** @see Zend_Db_Adapter_Exception */

            require_once 'Zend/Db/Adapter/Exception.php';

            throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");

        } else if ($count == 0) {

              $limit_sql = str_ireplace("SELECT", "SELECT * FROM (SELECT", $sql);

              $limit_sql .= ") WHERE 0 = 1";

        } else {

            $offset = intval($offset);

            if ($offset < 0) {

                /** @see Zend_Db_Adapter_Exception */

                require_once 'Zend/Db/Adapter/Exception.php';

                throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");

            }

            if ($offset == 0) {

                $limit_sql = str_ireplace("SELECT", "SELECT FIRST $count", $sql);

            } else {

                $limit_sql = str_ireplace("SELECT", "SELECT SKIP $offset LIMIT $count", $sql);

            }

        }

        return $limit_sql;

    }

    /**

     * IDS-specific last sequence id

     *

     * @param string $sequenceName

     * @return integer

     */

    public function lastSequenceId($sequenceName)

    {

        $sql = 'SELECT '.$this->_adapter->quoteIdentifier($sequenceName).'.CURRVAL FROM '

               .'systables WHERE tabid = 1';

        $value = $this->_adapter->fetchOne($sql);

        return $value;

    }

     /**

     * IDS-specific sequence id value

     *

     *  @param string $sequenceName

     *  @return integer

     */

    public function nextSequenceId($sequenceName)

    {

        $sql = 'SELECT '.$this->_adapter->quoteIdentifier($sequenceName).'.NEXTVAL FROM '

               .'systables WHERE tabid = 1';

        $value = $this->_adapter->fetchOne($sql);

        return $value;

    }

}