Location: PHPKode > projects > OpenREST > openrest-0.1/db/openrest-mssql.inc.php
<?php
/**
 *  @package ADOdb_extension
 *  @author Domenico Pontari <hide@address.com>
 *  @copyright Copyright (c) 2009, Domenico Pontari
 *  @license http://opensource.org/licenses/bsd-license.php New and Simplified BSD licenses
 */

/**
 *  required library
 */
require_once(ADODB_DIRROOT . '/drivers/adodb-mssql.inc.php');

/**
 *  @package ADOdb_extension
 */
class openrest_ADODB_mssql extends ADODB_mssql {
    protected $version = '';
    protected $_options = array ();

	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) {
        $result = parent::_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
        if ($result) {
            $serverInfo = $this->ServerInfo();
            $this->version = $serverInfo['version'];
        }
        return $result;
    }

	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) {
        $result = parent::_pconnect($argHostname, $argUsername, $argPassword, $argDatabasename);
        if ($result) {
            $serverInfo = $this->ServerInfo();
            $this->version = $serverInfo['version'];
        }
        return $result;
    }

	function ErrorNo() {
        if (substr($this->version, 0, 4) >= '9.00') return $this->_errorCode;
            else return parent::ErrorNo();
    }

    function addOption ($option) {
        array_push ($this->_options, $option);
    }

    /**
     *  I overload also this function to have a better error message solution
     *  Limitations:
     *  1) if SQL Server is less than SQL Server 2005
     *  2) you are using $inputarr var
     *  3) $sql is an array
     *  in these cases it will work as usual
     */
    function _query($sql,$inputarr) {
        foreach ($this->_options as $option) $sql = "$option $sql";
        
        $result = parent::_query($sql,$inputarr);
        if (    (substr($this->version, 0, 4) < '9.00') ||
                is_array($inputarr) || is_array($sql))
            return $result;
        if ($result === false) { // improve error message
            $id = @mssql_query("select @@ERROR",$this->_connectionID);
            if (!$id) $this->_errorCode = 0;
            else {
                $arr = mssql_fetch_array($id);
                @mssql_free_result($id);
                if (is_array($arr)) $this->_errorCode = $arr[0];
                    else $this->_errorCode = -1;
            }
            $sql = <<<EOD
BEGIN TRY
$sql
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS msg
END CATCH

EOD;
            $msgData = mssql_query ($sql, $this->_connectionID);
            $msg = mssql_fetch_assoc($msgData);
            $this->_errorMsg = $msg['msg'];
            $result = false;
        } else $this->_errorCode = 0;
        return $result;
    }


    function ParentType ($type) {
        $query = <<<EOD
SELECT pt.name FROM sys.types as t INNER JOIN sys.types as pt ON t.system_type_id = pt.user_type_id
    WHERE t.name like '$type'
EOD;
        return $this->GetOne ($query);
    }

    /**
     *  @return string|false
     */
    function SQLValue ($type, $value, $externalEncoding = 'utf-8') {
        switch ($type) {
            case 'nvarchar':
            case 'nchar':
                $result = "N" . $this->qstr($value);
            break;
            case 'varchar':
            case 'char':
                $result = $this->qstr($value);
            break;
            case 'int':
                $result = $value;
            break;
            default:
                return false;
        }
        return $result;
    }

    /**
     *  Not all data types are mapped. I put this function here and not in the
     *  Resultset class because I don't need to build a Resultset to know these things
     *
     *  @return char|false
     */
    function MetaType ($fieldobj) {
        if (get_class($fieldobj) != 'ADOFieldObject') return false;

        switch ($fieldobj->type) {
        case 'int':
        case 'bigint':
            $result = 'I';
        break;
        case 'image':
            $result = 'B';
        break;
        case 'text':
            $result = 'X';
        break;
        case 'float':
        case 'real':
            $result = 'N';
        break;
        case 'sysname':
        case 'nvarchar':
        case 'varchar':
        case 'nchar':
        case 'char':
            if ($fieldobj->max_length > 200) $result = 'X';
                else $result = 'C';
        break;
        case 'bit':
            $result = 'L';
        break;
        default:
            $parentType = $this->ParentType($fieldobj->type);
            if ($parentType != $fieldobj->type) {
                $fieldobj->type = $parentType;
                $result = $this->MetaType ($fieldobj);
            } else $result = false;
        }

        return $result;
    }

    /**
     *  We need to overload this function because the standard one doesn't return type and max_length
     */
    function MetaColumns ($table,$notcasesensitive=true) {
$query = <<<EOT
SELECT ac.name, ac.max_length, st.name as type FROM sys.all_columns ac
INNER JOIN sys.types as st ON ac.user_type_id = st.user_type_id
WHERE ac.object_id = OBJECT_ID ('$table')
EOT;

        $data = $this->GetAll ($query);
        $result = array ();
        foreach ($data as $field) {
            unset($el);
            $el = new ADOFieldObject();
            $el->name = $field['name'];
            $el->max_length = $field['max_length'];
            $el->type = $field['type'];
            array_push($result, $el);
        }
        return $result;
    }

    function MetaPrimaryKeys($table, $owner=false) {
$query = <<<EOT
SELECT ac.name AS col_name FROM sys.indexes idx
INNER JOIN sys.index_columns idc ON (idx.index_id = idc.index_id) AND (idx.object_id = idc.object_id)
INNER JOIN sys.all_columns ac ON (ac.column_id = idc.column_id) AND (ac.object_id = idc.object_id)
WHERE idx.object_id = OBJECT_ID ('$table') AND idx.is_primary_key <> 0
EOT;

        $result = $this->GetCol ($query);
        return $result;
    }
    
    function MetaForeignKeys($table, $owner=false, $upper=false) {
        $result = array();
$query = <<<EOT
SELECT OBJECT_NAME(fc.referenced_object_id) as refTable, arc.name AS refCol, apc.name AS thisCol FROM sys.foreign_key_columns fc INNER JOIN sys.all_columns apc ON (apc.object_id = fc.parent_object_id)
AND (apc.column_id = fc.parent_column_id) INNER JOIN sys.all_columns arc ON (arc.object_id = fc.referenced_object_id)
AND (arc.column_id = fc.referenced_column_id) WHERE apc.object_id = OBJECT_ID('$table')
EOT;
        $data = $this->GetAll ($query);
        foreach ($data as $row) {
            $row = array_values($row);
            if (isset ($el)) unset ($el);
            if (!isset($result[$row[0]])) $result[$row[0]] = array ();
            $el[$row[1]] = $row[2];
            $result[$row[0]] = array_merge($result[$row[0]], $el);
        }
        if (empty($result)) $result = false;
        return $result;
    }

    function MetaDatabases () {
        $pieces = array();
        $matched = eregi ("Initial Catalog=(.*);", $this->database, $pieces);
        if ($matched > 0) $result[0] = $pieces[1]; else $result = false;
        return $result;
    }

/*  TODO:
    function Permissions () {
$query = <<<EOT
select USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    OBJECT_NAME(p.major_id) AS [object_name],
    CASE WHEN ((OBJECTPROPERTYEX (p.major_id, 'IsTable') = 1) OR (OBJECTPROPERTYEX (p.major_id, 'IsView') = 1)) AND (p.minor_id <> 0) THEN COL_NAME (p.major_id, p.minor_id) ELSE '' END AS [col_name],
    p.permission_name,
    p.state_desc AS permission_state_desc,
    '' AS role_name
    FROM sys.database_permissions p
        LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
EOT;

        $result = $this->GetAll ($query);
        return $result;
    }
    
    function UserType ($code) {
        switch ($code) {
        case 'U': $result = 'WINDOWS_USER'; break;
        case 'S': $result = 'SQL_USER'; break;
        case 'R': $result = 'ROLE'; break;
        case 'C': $result = 'CERTIFICATE_MAPPED'; break;
        default: $result = 'UNKNOWN';
        }
        return $result;
    }

    function DatabasePrincipals () {
        $result = $this->GetAll ('SELECT name as username, type as usertype FROM sys.database_principals  ORDER BY username');
        foreach ($result as $num => $row) $result[$num]['usertype'] = $this->UserType ($result[$num]['usertype']);
        return $result;
    }

    function ServerPrincipals () {
        $result = $this->GetAll ('SELECT name as username, type as usertype, is_disabled as disabled FROM sys.server_principals ORDER BY username');
        foreach ($result as $num => $row) $result[$num]['usertype'] = $this->UserType ($result[$num]['usertype']);
        return $result;
    }
*/
}

?>
Return current item: OpenREST