Location: PHPKode > projects > ETraxis > etraxis-2.1.1/src/engine/dal.php
<?php

/**
 * Database Abstraction Layer
 *
 * This module implements eTraxis connectivity.
 *
 * @package Engine
 * @subpackage DAL
 */

//--------------------------------------------------------------------------------------------------
//
//  eTraxis - Records tracking web-based system.
//  Copyright (C) 2004-2009 by Artem Rodygin
//
//  This program is free software; you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation; either version 2 of the License, or
//  (at your option) any later version.
//
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License along
//  with this program; if not, write to the Free Software Foundation, Inc.,
//  51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
//
//--------------------------------------------------------------------------------------------------
//  Author                  Date            Description of modifications
//--------------------------------------------------------------------------------------------------
//  Artem Rodygin           2004-11-17      new-001: Records tracking web-based system should be implemented.
//  Artem Rodygin           2005-07-31      new-006: Records search.
//  Artem Rodygin           2005-08-22      bug-041: PHP Warning: odbc_exec(): SQL error: The name 'ru0' is not permitted in this context.
//  Artem Rodygin           2005-08-29      new-068: System settings in 'config.php' should be accessable through web-interface.
//  Artem Rodygin           2005-09-01      bug-079: String database columns are not enough to store UTF-8 values.
//  Artem Rodygin           2005-09-22      new-141: Source code review.
//  Artem Rodygin           2006-03-20      new-219: Dump of query should be moved to top of 'dal_execute' function.
//  Artem Rodygin           2006-05-12      bug-172: Extra long comments are cut when submitted.
//  Artem Rodygin           2006-05-14      bug-256: UTF-8 values are cut in MySQL database.
//  Artem Rodygin           2006-05-16      new-005: Oracle support.
//  Artem Rodygin           2006-05-26      bug-252: Sablotron fails if record contains non-ASCII characters and MSSQL connection is used.
//  Artem Rodygin           2006-05-30      bug-264: PHP Warning: dbx_error: not supported in this module
//  Artem Rodygin           2006-06-01      bug-265: PHP Warning: ociexecute(): OCIStmtExecute: ORA-00904: "R"."RECORD_ID": invalid identifier
//  Artem Rodygin           2007-02-03      bug-493: [SF1650590] doesn't work with Oracle XE (10g)
//  Artem Rodygin           2007-07-01      bug-537: PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 16 bytes)
//  Artem Rodygin           2007-11-26      new-633: The 'dbx' extension should not be used.
//  Artem Rodygin           2008-02-26      bug-679: "User is not authorized" is permanently shown with Oracle.
//  Artem Rodygin           2008-02-28      new-294: PostgreSQL support.
//  Artem Rodygin           2008-04-24      new-708: [SF1950362] UNIX socket for PostgreSQL
//  Artem Rodygin           2008-04-24      bug-709: [SF1950363] PostgreSQL connection string has unprintable chars
//  Artem Rodygin           2009-03-11      bug-799: eTraxis doesn't work with XAMPP on Windows.
//  Artem Rodygin           2009-06-01      new-824: PHP 4 is discontinued.
//  Artem Rodygin           2009-06-17      bug-825: Database gets empty strings instead of NULL values.
//  Artem Rodygin           2009-08-17      new-826: Native unicode support for Microsoft SQL Server.
//  Artem Rodygin           2009-09-06      new-827: Microsoft SQL Server 2005/2008 support.
//--------------------------------------------------------------------------------------------------

/**#@+
 * Dependency.
 */
require_once('../engine/debug.php');
require_once('../engine/utility.php');
require_once('../engine/locale.php');
/**#@-*/

/**#@+
 * Supported database driver.
 */
define('DRIVER_MYSQL50', 1);  // MySQL 5.0 or later
define('DRIVER_MSSQL2K', 2);  // Microsoft SQL Server 2000 or later
define('DRIVER_ORACLE9', 3);  // Oracle 9i or later
define('DRIVER_PGSQL80', 4);  // PostgreSQL 8.0 or later
/**#@-*/

$res_driver = array
(
    DRIVER_MYSQL50 => RES_MYSQL_ID,
    DRIVER_MSSQL2K => RES_MSSQL_ID,
    DRIVER_ORACLE9 => RES_ORACLE_ID,
    DRIVER_PGSQL80 => RES_POSTGRESQL_ID,
);

//--------------------------------------------------------------------------------------------------
//  DAL recordset.
//--------------------------------------------------------------------------------------------------

/**
 * Database connection, implemented via Singleton pattern.
 * @package Engine
 * @subpackage DAL
 * @ignore
 */
class CDatabase
{
    // Static object of itself.
    private static $object = NULL;

    // Link of opened connection.
    private $link = FALSE;

    // Establishes connection to eTraxis database.
    private function __construct ()
    {
        debug_write_log(DEBUG_TRACE, '[CDatabase::__construct]');

        if (DATABASE_DRIVER == DRIVER_MYSQL50)
        {
            $this->link = mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD);

            if ($this->link)
            {
                if (mysql_select_db(DATABASE_DBNAME, $this->link))
                {
                    mysql_query('set names utf8', $this->link);
                }
                else
                {
                    debug_write_log(DEBUG_WARNING, '[CDatabase::__construct] Error on selecting MySQL database.');
                    mysql_close($this->link);
                    $this->link = FALSE;
                }
            }
        }
        elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
        {
            $conn_info = array
            (
                'APP'          => 'eTraxis',
                'CharacterSet' => 'UTF-8',
                'Database'     => DATABASE_DBNAME,
            );

            if (ustrlen(trim(DATABASE_USERNAME)) != 0)
            {
                $conn_info['UID'] = DATABASE_USERNAME;
                $conn_info['PWD'] = DATABASE_PASSWORD;
            }

            $this->link = sqlsrv_connect(DATABASE_HOST, $conn_info);
        }
        elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
        {
            $this->link = dbx_connect(DBX_OCI8, DATABASE_HOST, DATABASE_DBNAME, DATABASE_USERNAME, DATABASE_PASSWORD);
        }
        elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
        {
            if (strlen(trim(DATABASE_HOST)) == 0)
            {
                $this->link = pg_connect(sprintf('dbname=%s user=%s password=%s', DATABASE_DBNAME, DATABASE_USERNAME, DATABASE_PASSWORD));
            }
            else
            {
                $this->link = pg_connect(sprintf('host=%s dbname=%s user=%s password=%s', DATABASE_HOST, DATABASE_DBNAME, DATABASE_USERNAME, DATABASE_PASSWORD));
            }
        }
        else
        {
            debug_write_log(DEBUG_WARNING, '[CDatabase::__construct] Unknown database driver.');
            $this->link = FALSE;
        }
    }

    // Closes connection to eTraxis database.
    public function __destruct()
    {
        if (DATABASE_DRIVER == DRIVER_MYSQL50)
        {
            mysql_close($this->link);
        }
        elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
        {
            sqlsrv_close($this->link);
        }
        elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
        {
            dbx_close($this->link);
        }
        elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
        {
            pg_close($this->link);
        }
    }

    // Tries to connect to database.
    // Return resource for connection to eTraxis database on success, FALSE otherwise.
    public static function connect ()
    {
        if (is_null(self::$object))
        {
            self::$object = new CDatabase();
        }

        return self::$object->link;
    }
}

/**
 * DAL recordset.
 *
 * The class implements DAL recordset and several functions to work with.
 * The implementation is universal and doesn't depend on type of database.
 *
 * @package Engine
 * @subpackage DAL
 */
class CRecordset
{
    /**#@+
     * For internal use only.
     * @ignore
     */
    private $handle;  // [resource] connection
    private $result;  // [resource] query result
    private $resptr;  // [int]      recordset cursor (number of current record from 0)
    /**#@-*/

    /**
     * Number of rows in resulted recordset (read-only).
     * @var int
     */
    protected $rows;

    /**
     * Number of columns in resulted recordset (read-only).
     * @var int
     */
    protected $cols;

    /**
     * Executes specified query and constructs itself as resulted recordset.
     *
     * @param string $sql SQL-query being executed.
     */
    public function __construct ($sql)
    {
        $this->handle = CDatabase::connect();
        $this->result = FALSE;
        $this->resptr = 0;
        $this->rows   = 0;
        $this->cols   = 0;

        list($msec, $sec) = explode(' ', microtime());
        $start = (float)$msec + (float)$sec;

        if (DATABASE_DRIVER == DRIVER_MYSQL50)
        {
            $this->result = mysql_query($sql, $this->handle);

            if (is_resource($this->result))
            {
                $this->rows = mysql_num_rows($this->result);
                $this->cols = mysql_num_fields($this->result);
            }
        }
        elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
        {
            $this->result = sqlsrv_query($this->handle, $sql, NULL, array('Scrollable' => SQLSRV_CURSOR_STATIC));

            if (is_resource($this->result))
            {
                $this->rows = sqlsrv_num_rows($this->result);
                $this->cols = sqlsrv_num_fields($this->result);
            }

            $this->resptr = -1;
        }
        elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
        {
            $this->result = dbx_query($this->handle, $sql, DBX_COLNAMES_LOWERCASE);

            if (is_object($this->result))
            {
                $this->rows = $this->result->rows;
                $this->cols = $this->result->cols;
            }
        }
        elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
        {
            $this->result = pg_query($this->handle, $sql);

            if (is_resource($this->result))
            {
                $this->rows = pg_num_rows($this->result);
                $this->cols = pg_num_fields($this->result);
            }
        }
        else
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::__construct] Unknown database driver.');
        }

        list($msec, $sec) = explode(' ', microtime());
        $stop = (float)$msec + (float)$sec;

        debug_write_log(DEBUG_DUMP,        'SQL text = ' . $sql);
        debug_write_log(DEBUG_PERFORMANCE, 'SQL time = ' . ($stop - $start));

        if (!$this->result)
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::__construct] ' . $this->error());
        }
    }

    /**
     * Frees all resources associated with the recordset.
     */
    public function __destruct()
    {
        if (is_resource($this->result))
        {
            if (DATABASE_DRIVER == DRIVER_MYSQL50)
            {
                mysql_free_result($this->result);
            }
            elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
            {
                sqlsrv_free_stmt($this->result);
            }
            elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
            {
                // nothing to do in case of DBX
            }
            elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
            {
                pg_free_result($this->result);
            }
            else
            {
                debug_write_log(DEBUG_WARNING, '[CRecordset::__destruct] Unknown database driver.');
            }
        }
    }

    /**
     * @ignore
     */
    public function __get ($name)
    {
        switch ($name)
        {
            case 'rows': return $this->rows;
            case 'cols': return $this->cols;
            default:     return NULL;
        }
    }

    /**
     * Returns error message of last operation.
     *
     * @return string Error message of last operation, or NULL on failure.
     */
    public function error ()
    {
        if (DATABASE_DRIVER == DRIVER_MYSQL50)
        {
            $errno  = mysql_errno($this->handle);
            $error  = mysql_error($this->handle);
            $retval = "MySQL error {$errno}: {$error}";
        }
        elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
        {
            $error  = sqlsrv_errors(SQLSRV_ERR_ALL);
            $retval = (is_null($error)
                    ? NULL
                    : "MSSQL error {$error['code']}: {$error['message']}");
        }
        elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
        {
            $error  = ocierror($this->handle->handle);
            $retval = "Oracle error {$error['code']}: {$error['message']}";
        }
        elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
        {
            $error  = pg_last_error($this->handle);
            $retval = "PostgreSQL error: {$error}";
        }
        else
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::error] Unknown database driver.');
            return NULL;
        }

        return $retval;
    }

    /**
     * Moves cursor to specified record.
     *
     * @param int $row_number Number of record, zero-based.
     * @return bool TRUE on success, FALSE otherwise.
     */
    public function seek ($row_number = 0)
    {
        if (!$this->result)
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::seek] No stored recordset.');
            return FALSE;
        }

        if ($row_number < 0 || $row_number >= $this->rows)
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::seek] Row number is out of stored recordset.');
            return FALSE;
        }

        if (DATABASE_DRIVER == DRIVER_MYSQL50)
        {
            $retval = mysql_data_seek($this->result, $row_number);
        }
        elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
        {
            $this->resptr = $row_number;
            $retval = TRUE;
        }
        elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
        {
            $this->resptr = $row_number;
            $retval = TRUE;
        }
        elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
        {
            $retval = pg_result_seek($this->result, $row_number);
        }
        else
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::seek] Unknown database driver.');
            return FALSE;
        }

        if (!$retval)
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::seek] ' . $this->error());
        }

        return $retval;
    }

    /**
     * Returns next record from recordset.
     *
     * Returns the record for current cursor and then moves cursor forward to the next one.
     * The record is returned as array with two sets of keys - one set is zero-based indexes, another is names of record fields.
     *
     * @param int|string $field Optional field name or zero-based index.
     * @return mixed|array If <i>field</i> is not specified, returns whole record as an array, or FALSE if there is no more record to return.
     * If <i>field</i> is specified, then returns value of specified field (it could be both zero-based index, or field name).
     *
     * Example #1:
     * <code>
     * $rs = new CRecordset("select my_id, my_field from my_table");
     *
     * while ($row = $rs->fetch())
     * {
     *     printf("%u\t%s\n", $row["my_id"], $row["my_field"]);
     * }
     * </code>
     *
     * Example #2:
     * <code>
     * $rs = new CRecordset("select count(*) from my_table");
     * echo($rs->fetch(0));
     * </code>
     */
    public function fetch ($field = NULL)
    {
        if (!$this->result)
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::fetch] No stored recordset.');
            return FALSE;
        }

        if (DATABASE_DRIVER == DRIVER_MYSQL50)
        {
            $retval = mysql_fetch_array($this->result, MYSQL_BOTH);
        }
        elseif (DATABASE_DRIVER == DRIVER_MSSQL2K)
        {
            if ($this->resptr == -1)
            {
                $retval = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_BOTH, SQLSRV_SCROLL_NEXT);
            }
            else
            {
                $retval = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_BOTH, SQLSRV_SCROLL_ABSOLUTE, $this->resptr);
                $this->resptr = -1;
            }
        }
        elseif (DATABASE_DRIVER == DRIVER_ORACLE9)
        {
            if ($this->resptr == $this->result->rows)
            {
                debug_write_log(DEBUG_WARNING, '[CRecordset::fetch] No more rows to return.');
                return FALSE;
            }

            $retval = $this->result->data[$this->resptr++];
        }
        elseif (DATABASE_DRIVER == DRIVER_PGSQL80)
        {
            $retval = pg_fetch_array($this->result);
        }
        else
        {
            debug_write_log(DEBUG_WARNING, '[CRecordset::fetch] Unknown database driver.');
            return FALSE;
        }

        if (!is_array($retval))
        {
            return NULL;
        }

        return (is_null($field) ? $retval : $retval[$field]);
    }
}

//--------------------------------------------------------------------------------------------------
//  Functions.
//--------------------------------------------------------------------------------------------------

/**
 * Executes specified SQL-file from "sql" eTraxis directory.
 *
 * The function accepts variable number of arguments. It opens specified SQL-file and replaces each "%i"
 * (where <i>i</i> is a natural number) substring with related additional argument.
 *
 * @param string $query Path to file with SQL-query (path is related to "sql" directory).
 * @param mixed Value, which each "%1" substring will be replaced with.
 * @param mixed Value, which each "%2" substring will be replaced with.
 * @param mixed ... (and so on)
 * @return CRecordset Resulted {@link CRecordset DAL recordset}.
 *
 * Example of usage:
 * <code>
 * $rs = dal_query("accounts/list.sql", "username");
 *
 * while ($row = $rs->fetch())
 * {
 *     foreach ($row as $item)
 *     {
 *         echo("$item\t");
 *     }
 *
 *     echo("\n");
 * }
 * </code>
 */
function dal_query ($query)
{
    debug_write_log(DEBUG_TRACE, '[dal_query] ' . $query);

    $sql = file_get_contents(LOCALROOT . 'sql/' . $query);
    $sql = str_replace("\n", ' ', $sql);
    $sql = preg_replace('([ ]+)', ' ', $sql);

    if (DATABASE_DRIVER == DRIVER_ORACLE9)
    {
        $pos = ustrrpos($sql, 'order by');

        if ($pos !== FALSE)
        {
            $sql = usubstr($sql, 0, $pos) . preg_replace('(\s[a-z]+\.)', ' ', usubstr($sql, $pos));
        }
    }

    $count = func_num_args() - 1;

    for ($i = $count; $i >= 1; $i--)
    {
        $search  = '%' . $i;
        $replace = func_get_arg($i);

        if (strpos($sql, "'{$search}'") === FALSE)
        {
            if (is_null($replace))
            {
                $replace = 'NULL';
            }
        }
        else
        {
            if (is_null($replace))
            {
                $search  = "'{$search}'";
                $replace = 'NULL';
            }
            else
            {
                $replace = ustr2sql($replace);
            }
        }

        $sql = ustr_replace($search, $replace, $sql);
    }

    return new CRecordset($sql);
}

?>
Return current item: ETraxis