Home » Database » DB » Bug #5476

[2005-09-21 19:42 UTC] myke at adicio dot com

Description:
------------
This is a DB module for connecting to a texis database. Sorry
for apparently posting this to the wrong place (mailing list).

Test script:
---------------
<?php

/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
* The PEAR DB driver for a texis extension
* for interacting with Texis databases
*
* PHP versions 4 and 5
*
* LICENSE: This source file is subject to version 3.0 of the PHP license
* that is available through the world-wide-web at the following URI:
* http://www.php.net/license/3_0.txt. If you did not receive a copy of
* the PHP License and are unable to obtain it through the web, please
* send a note to license@php.net so we can mail you a copy immediately.
*
* Use:
* In order to use this script you need to first setup a vortex script
* that will return your XML to this module. (name script xml)
* <code>
* <a name=xmloutput public>
* <sql output=xml db=$db $sqlstatement></sql>
* </a>
* </code>
*
*
* @category Database
* @package DB
* @author Myke Hines <myke@adicio.com>
* @copyright 1997-2005 The PHP Group
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version CVS: $Id: texis.php,v 1.117 2005/03/29 15:03:26 myke Exp $
* @link http://pear.php.net/package/DB
*/

/**
* Obtain the DB_common class so it can be extended from
*/
require_once 'DB/common.php';

/**
* The methods PEAR DB uses to interact with PHP's texis extension
* for interacting with Texis databases
*
* These methods overload the ones declared in DB_common.
*
* @category Database
* @package DB
* @author Stig Bakken <ssb@php.net>
* @author Daniel Convissor <danielc@php.net>
* @copyright 1997-2005 The PHP Group
* @license http://www.php.net/license/3_0.txt PHP License 3.0
* @version Release: 1.7.6
* @link http://pear.php.net/package/DB
*/
class DB_texis extends DB_common
{
// {{{ properties

/**
* The DB driver type (texis, oci8, odbc, etc.)
* @var string
*/
var $phptype = 'texis';

/**
* The database syntax variant to be used (db2, access, etc.), if any
* @var string
*/
var $dbsyntax = 'texis';

/**
* The capabilities of this DB implementation
*
* The 'new_link' element contains the PHP version that first provided
* new_link support for this DBMS. Contains false if it's unsupported.
*
* Meaning of the 'limit' element:
* + 'emulate' = emulate with fetch row by number
* + 'alter' = alter the query
* + false = skip rows
*
* @var array
*/
var $features = array(
'limit' => 'alter',
'new_link' => '4.2.0',
'numrows' => true,
'pconnect' => false,
'prepare' => false,
'ssl' => false,
'transactions' => true,
);

/**
* A mapping of native error codes to DB error codes
* @var array
*/
var $errorcode_map = array(
1004 => DB_ERROR_CANNOT_CREATE,
1005 => DB_ERROR_CANNOT_CREATE,
1006 => DB_ERROR_CANNOT_CREATE,
1007 => DB_ERROR_ALREADY_EXISTS,
1008 => DB_ERROR_CANNOT_DROP,
1022 => DB_ERROR_ALREADY_EXISTS,
1044 => DB_ERROR_ACCESS_VIOLATION,
1046 => DB_ERROR_NODBSELECTED,
1048 => DB_ERROR_CONSTRAINT,
1049 => DB_ERROR_NOSUCHDB,
1050 => DB_ERROR_ALREADY_EXISTS,
1051 => DB_ERROR_NOSUCHTABLE,
1054 => DB_ERROR_NOSUCHFIELD,
1061 => DB_ERROR_ALREADY_EXISTS,
1062 => DB_ERROR_ALREADY_EXISTS,
1064 => DB_ERROR_SYNTAX,
1091 => DB_ERROR_NOT_FOUND,
1100 => DB_ERROR_NOT_LOCKED,
1136 => DB_ERROR_VALUE_COUNT_ON_ROW,
1142 => DB_ERROR_ACCESS_VIOLATION,
1146 => DB_ERROR_NOSUCHTABLE,
1216 => DB_ERROR_CONSTRAINT,
1217 => DB_ERROR_CONSTRAINT,
);

/**
* The raw database connection created by PHP
* @var resource
*/
var $connection;

/**
* The DSN information for connecting to a database
* @var array
*/
var $dsn = array();

/**
* Should data manipulation queries be committed automatically?
* @var bool
* @access private
*/
var $autocommit = true;

/**
* The quantity of transactions begun
*
* {@internal While this is private, it can't actually be designated
* private in PHP 5 because it is directly accessed in the test suite.}}
*
* @var integer
* @access private
*/
var $transaction_opcount = 0;

/**
* The database specified in the DSN
*
* It's a fix to allow calls to different databases in the same script.
*
* @var string
* @access private
*/
var $_db = '';

// }}}
// {{{ constructor

/**
* This constructor calls <kbd>$this->DB_common()</kbd>
*
* @return void
*/
function DB_texis()
{
$this->DB_common();
}

// }}}
// {{{ connect()

/**
* Connect to the database server, log in and open the database
*
* Don't call this method directly. Use DB::connect() instead.
*
* PEAR DB's texis driver supports the following extra DSN options:
* + new_link If set to true, causes subsequent calls to connect()
* to return a new connection link instead of the
* existing one. WARNING: this is not portable to
* other DBMS's. Available since PEAR DB 1.7.0.
* + client_flags Any combination of MYSQL_CLIENT_* constants.
* Only used if PHP is at version 4.3.0 or greater.
* Available since PEAR DB 1.7.0.
*
* @param array $dsn the data source name
* @param bool $persistent should the connection be persistent?
*
* @return int DB_OK on success. A DB_Error object on failure.
*/
function connect($dsn, $persistent = false)
{
$this->dsn = $dsn;
if ($dsn['dbsyntax']) {
$this->dbsyntax = $dsn['dbsyntax'];
}

$params = array();
if ($dsn['protocol'] && $dsn['protocol'] == 'unix') {
$params[0] = ':' . $dsn['socket'];
} else {
$params[0] = $dsn['hostspec'] ? $dsn['hostspec']
: 'central';
if ($dsn['port']) {
$params[0] .= ':' . $dsn['port'];
}
}

$php_errormsg = '';

$this->host = $params[0];
if ($dsn['database']) {
$this->_db = '/home/db/' . $dsn['database'];
}
$this->connection = TRUE;
return DB_OK;
}

// }}}
// {{{ disconnect()

/**
* Disconnects from the database server
*
* @return bool TRUE on success, FALSE on failure
*/
function disconnect()
{
return TRUE;
}

// }}}
// {{{ simpleQuery()

/**
* Sends a query to the database server
*
* @param string the SQL query string
*
* @return mixed + a PHP result resrouce for successful SELECT queries
* + the DB_OK constant for other successful queries
* + a DB_Error object on failure
*/
function simpleQuery($query)
{
$ismanip = DB::isManip($query);
$this->last_query = $query;
//$query = $this->modifyQuery($query);

unset($this->resultCount);
$queryUrl='http://' . $this->host . '/texis/xml/xmloutput?db=' .
urlencode($this->_db) . '&q=' . urlencode($query) . "&skip=$skip&max=$max&getcount=$getResultCount";

$rawTexisOutput=file_get_contents($queryUrl);
if (isset($_REQUEST['debug'])){
echo "<xmp> $rawTexisOutput </xmp>";
}

if (! strstr($rawTexisOutput, '<record')) {
// No results, for one reason or another. Return empty array.
// Error
}
$texisXml = xml_parser_create('UTF-8');
xml_parser_set_option($texisXml,XML_OPTION_CASE_FOLDING,0);

if (! xml_parse_into_struct($texisXml, $rawTexisOutput, $vals, $index)) {
// xml parsing failed. That sucks. Return empty array.
if (isset($_REQUEST['debug'])){
echo xml_error_string(xml_get_error_code($texisXml));
echo "Warning XML parsing failed!";
}
return array();
}

$out=array();

// xml_parse_into_struct produces a format more suitable for complex xml, rebuild here into a
// simple multi dimensional array similar to the format returned by mysql's fetch_assoc
$row=0;
for ($i=0; $i < sizeof($vals); $i++) {
$tag=$vals[$i]['tag'];
if ($tag == 'resultSet') {
if (isset($vals[$i]['attributes']['matches'])){
$this->resultCount=$vals[$i]['attributes']['matches'];
}
continue;
} elseif ($tag == 'record') {
if ($vals[$i]['type'] == 'close'){
$row++;
}
continue;
}
if ($returnEmptyVals){
$out[$row][$tag]=@$vals[$i]['value'];
}else{
if (isset($vals[$i]['value'])) {
$out[$row][$tag]=@$vals[$i]['value'];
}
}
}
$result_obj = new DB_result($this, $out);

return $result_obj;

}

// }}}
// {{{ nextResult()

/**
* Move the internal texis result pointer to the next available result
*
* This method has not been implemented yet.
*
* @param a valid sql result resource
*
* @return false
*/
function nextResult($result)
{
return false;
}

// }}}
// {{{ fetchInto()

/**
* Places a row from the result set into the given array
*
* Formating of the array and the data therein are configurable.
* See DB_result::fetchInto() for more information.
*
* This method is not meant to be called directly. Use
* DB_result::fetchInto() instead. It can't be declared "protected"
* because DB_result is a separate object.
*
* @param resource $result the query result resource
* @param array $arr the referenced array to put the data in
* @param int $fetchmode how the resulting array should be indexed
* @param int $rownum the row number to fetch (0 = first row)
*
* @return mixed DB_OK on success, NULL when the end of a result set is
* reached or on failure
*
* @see DB_result::fetchInto()
*/
function fetchInto($result, &$arr, $fetchmode, $rownum = null)
{
if ($rownum !== null) {
$result->rowPointer = $rownum;
}
if (! isset ($result->rowPointer)) {
$result->rowPointer = 0;
}

if ($fetchmode & DB_FETCHMODE_ASSOC) {
if (!isset ($this->rowCounter)) {
$this->rowCounter = 1; //Element 0 is the texis header
}
$arr = $result->result[$this->rowCounter];
$this->rowCounter++;

} else {
$arr = $result->result[$result->rowPointer];
$result->rowPointer++;
}
if (!$arr) {
return null;
}
if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
/*
* Even though this DBMS already trims output, we do this because
* a field might have intentional whitespace at the end that
* gets removed by DB_PORTABILITY_RTRIM under another driver.
*/
$this->_rtrimArrayValues($arr);
}
if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
$this->_convertNullArrayValuesToEmpty($arr);
}
return DB_OK;
}

// }}}
// {{{ freeResult()

/**
* Deletes the result set and frees the memory occupied by the result set
*
* This method is not meant to be called directly. Use
* DB_result::free() instead. It can't be declared "protected"
* because DB_result is a separate object.
*
* @param resource $result PHP's query result resource
*
* @return bool TRUE on success, FALSE if $result is invalid
*
* @see DB_result::free()
*/
function freeResult($result)
{
return 1;
}

// }}}
// {{{ numCols()

/**
* Gets the number of columns in a result set
*
* This method is not meant to be called directly. Use
* DB_result::numCols() instead. It can't be declared "protected"
* because DB_result is a separate object.
*
* @param resource $result PHP's query result resource
*
* @return int the number of columns. A DB_Error object on failure.
*
* @see DB_result::numCols()
*/
function numCols($result)
{
$cols = count($result->result[0]);
if (!$cols) {
return $this->texisRaiseError();
}
return $cols;
}

// }}}
// {{{ numRows()

/**
* Gets the number of rows in a result set
*
* This method is not meant to be called directly. Use
* DB_result::numRows() instead. It can't be declared "protected"
* because DB_result is a separate object.
*
* @param resource $result PHP's query result resource
*
* @return int the number of rows. A DB_Error object on failure.
*
* @see DB_result::numRows()
*/
function numRows($result)
{
$rows = count($result->result);
if ($rows === null) {
return $this->texisRaiseError();
}
return $rows;
}

// }}}
// {{{ autoCommit()

/**
* Enables or disables automatic commits
*
* @param bool $onoff true turns it on, false turns it off
*
* @return int DB_OK on success. A DB_Error object if the driver
* doesn't support auto-committing transactions.
*/
function autoCommit($onoff = false)
{
// XXX if $this->transaction_opcount > 0, we should probably
// issue a warning here.
$this->autocommit = $onoff ? true : false;
return DB_OK;
}

// }}}
// {{{ affectedRows()

/**
* Determines the number of rows affected by a data maniuplation query
*
* 0 is returned for queries that don't manipulate data.
*
* @return int the number of rows. A DB_Error object on failure.
*/
function affectedRows()
{
if (DB::isManip($this->last_query)) {
return 0;
} else {
return 0;
}
}

// }}}
// {{{ nextId()

/**
* Returns the next free id in a sequence
*
* @param string $seq_name name of the sequence
* @param boolean $ondemand when true, the seqence is automatically
* created if it does not exist
*
* @return int the next id number in the sequence.
* A DB_Error object on failure.
*
* @see DB_common::nextID(), DB_common::getSequenceName(),
* DB_texis::createSequence(), DB_texis::dropSequence()
*/
function nextId($seq_name, $ondemand = true)
{
return $this->raiseError($result);
}

// }}}
// {{{ createSequence()

/**
* Creates a new sequence
*
* @param string $seq_name name of the new sequence
*
* @return int DB_OK on success. A DB_Error object on failure.
*
* @see DB_common::createSequence(), DB_common::getSequenceName(),
* DB_texis::nextID(), DB_texis::dropSequence()
*/
function createSequence($seq_name)
{
return $this->raiseError($seq_name);
}

// }}}
// {{{ dropSequence()

/**
* Deletes a sequence
*
* @param string $seq_name name of the sequence to be deleted
*
* @return int DB_OK on success. A DB_Error object on failure.
*
* @see DB_common::dropSequence(), DB_common::getSequenceName(),
* DB_texis::nextID(), DB_texis::createSequence()
*/
function dropSequence($seq_name)
{
return $this->raiseError ($seq_name);
}

// }}}
// {{{ _BCsequence()

/**
* Backwards compatibility with old sequence emulation implementation
* (clean up the dupes)
*
* @param string $seqname the sequence name to clean up
*
* @return bool true on success. A DB_Error object on failure.
*
* @access private
*/
function _BCsequence($seqname)
{
return $this->raiseError ($seq_name);
}

// }}}
// {{{ quoteIdentifier()

/**
* Quotes a string so it can be safely used as a table or column name
*
* Texis can't handle the backtick character (<kbd>`</kbd>) in
* table or column names.
*
* @param string $str identifier name to be quoted
*
* @return string quoted identifier string
*
* @see DB_common::quoteIdentifier()
* @since Method available since Release 1.6.0
*/
function quoteIdentifier($str)
{
return '`' . $str . '`';
}

// }}}
// {{{ quote()

/**
* @deprecated Deprecated in release 1.6.0
*/
function quote($str)
{
return $this->quoteSmart($str);
}

// }}}
// {{{ escapeSimple()

/**
* mysql_escape_string replaces new lines with \n\r, etc. This is
* not the desired behavior for Texis, manually escape single
* quotes, black slash, and null instead.
*
* @param string $str the string to be escaped
*
* @return string the escaped string
*
* @see DB_common::quoteSmart()
* @since Method available since Release 1.6.0
*/
function escapeSimple($str)
{
return preg_replace('/(\x00|\xC5|\x27)/', '\\\$1', $in);
}

// }}}
// {{{ modifyQuery()

/**
* Changes a query string for various DBMS specific reasons
*
* This little hack lets you know how many rows were deleted
* when running a "DELETE FROM table" query. Only implemented
* if the DB_PORTABILITY_DELETE_COUNT portability option is on.
*
* @param string $query the query string to modify
*
* @return string the modified query string
*
* @access protected
* @see DB_common::setOption()
*/
function modifyQuery($query)
{
if ($this->options['portability'] & DB_PORTABILITY_DELETE_COUNT) {
// "DELETE FROM table" gives 0 affected rows in Texis.
// This little hack lets you know how many rows were deleted.
if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
$query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
'DELETE FROM \1 WHERE 1=1', $query);
}
}
return $query;
}

// }}}
// {{{ modifyLimitQuery()

/**
* Adds LIMIT clauses to a query string according to current DBMS standards
*
* @param string $query the query to modify
* @param int $from the row to start to fetching (0 = the first row)
* @param int $count the numbers of rows to fetch
* @param mixed $params array, string or numeric data to be used in
* execution of the statement. Quantity of items
* passed must match quantity of placeholders in
* query: meaning 1 placeholder for non-array
* parameters or 1 placeholder per array element.
*
* @return string the query string with LIMIT clauses added
*
* @access protected
*/
function modifyLimitQuery($query, $from, $count, $params = array())
{
if (DB::isManip($query)) {
return $query . " LIMIT $count";
} else {
return $query . " LIMIT $from, $count";
}
}

// }}}
// {{{ texisRaiseError()

/**
* Produces a DB_Error object regarding the current problem
*
* @param int $errno if the error is being manually raised pass a
* DB_ERROR* constant here. If this isn't passed
* the error information gathered from the DBMS.
*
* @return object the DB_Error object
*
* @see DB_common::raiseError(),
* DB_texis::errorNative(), DB_common::errorCode()
*/
function texisRaiseError($errno = null)
{
return $this->raiseError($errno, null, null, null,
"Unknown" . ' ** ' .
"Error with your query");
}

// }}}
// {{{ errorNative()

/**
* Gets the DBMS' native error code produced by the last query
*
* @return int the DBMS' error code
*/
function errorNative()
{
return 0;
}

// }}}
// {{{ tableInfo()

/**
* Returns information about a table or a result set
*
* @param object|string $result DB_result object from a query or a
* string containing the name of a table.
* While this also accepts a query result
* resource identifier, this behavior is
* deprecated.
* @param int $mode a valid tableInfo mode
*
* @return array an associative array with the information requested.
* A DB_Error object on failure.
*
* @see DB_common::tableInfo()
*/
function tableInfo($result, $mode = null)
{
if (is_string($result)) {
/*
* Probably received a table name.
* Create a result resource identifier.
*/
$id = @texis_list_fields($this->dsn['database'],
$result, $this->connection);
$got_string = true;
} elseif (isset($result->result)) {
/*
* Probably received a result object.
* Extract the result resource identifier.
*/
$id = $result->result;
$got_string = false;
} else {
/*
* Probably received a result resource identifier.
* Copy it.
* Deprecated. Here for compatibility only.
*/
$id = $result;
$got_string = false;
}

if (!is_resource($id)) {
return $this->texisRaiseError(DB_ERROR_NEED_MORE_DATA);
}

if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
$case_func = 'strtolower';
} else {
$case_func = 'strval';
}

$count = $result->numCols();
$res = array();

if ($mode) {
$res['num_fields'] = $count;
}

for ($i = 0; $i < $count; $i++) {
$res[$i] = array(
'table' => '', //This needs to be fixed
'name' => $case_func($id[$i]),
'type' => '',
'len' => count($id[$i]),
'flags' => '',
);
if ($mode & DB_TABLEINFO_ORDER) {
$res['order'][$res[$i]['name']] = $i;
}
if ($mode & DB_TABLEINFO_ORDERTABLE) {
$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
}
}

return $res;
}

// }}}
// {{{ getSpecialQuery()

/**
* Obtains the query string needed for listing a given type of objects
*
* @param string $type the kind of objects you want to retrieve
*
* @return string the SQL query string or null if the driver doesn't
* support the object type requested
*
* @access protected
* @see DB_common::getListOf()
*/
function getSpecialQuery($type)
{
switch ($type) {
case 'tables':
return 'SHOW TABLES';
case 'users':
return 'SELECT DISTINCT User FROM texis.user';
case 'databases':
return 'SHOW DATABASES';
default:
return null;
}
}

// }}}

}

/*
* Local variables:
* tab-width: 4
* c-basic-offset: 4
* End:
*/

?>

[2006-08-18 21:15 UTC] stubie at ucsd dot edu

- getSpecialQuery is returning commands specific to mysql
which won't work with Texis.

- simpleQuery calls Texis database over http and is using some
unknown custom script? wtf?