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