Database Abstraction Layer?

[chodgson]

I'm using PHPBB on my postgresql database. I would like to use coppermine with it. However, When I went to install coppermine, I realized that it only supports mysql - I clearly ignored this requirement when I was reading them.

I am an experienced php and db programmer, and I am willing to do the work to make this happen. My first thought was to simply replace "mysql" with "pgsql" everywhere in the code, and fix the few places which use "mysql_insert_id" to use a sequence. However, I then thought that if I'm going to go and do all that work, why not replace all the db calls with nicely abstracted ones that work for other databases too...

I am willing to do most of the work on this, but the other developers are going to have to continue to use whatever db abstraction layer gets used, forevermore. Personally, I like PEARDB, and it is fairly standard and well distributed.

Would all you other developers be interesed in switching to using a db abstraction layer? Do you have any preferences of which one is used? Can I get CVS access to do this? Have you considered doing this before, or are you already in the process of doing it? I didn't see any mention of it, but I didn't dig too deep, either.

Thanks
Chris

[Patrick 2.0]

Personally, I'd love to see the built in PHP DB abstraction layer used, DBX.  It supports most commonly used DB's, is faster then any PHP abstraction layer I've used, and makes working with databases much easer IMO.

Patrick

[chodgson]

Good idea. I haven't actually used dbx yet, but that is definitely the best way to go.

Can I get any comment on this topic from the developers?

[Joachim Müller]

we decided to not support database abstraction in coppermine 1.x, because it will result in additional problems for "regular" users. There are actually two groups of users: those who want a gallery that's easy to install, maintain and modify and those who want it full of features. We have to decide on each occasion / feature: will there be a benefit for the majority of users?
Currently, a vast majority of webservers run mySQL - only very few run postgre or ms sql. They surely have good reasons to do so, but when they decided to have "non-standard" databases, they knew that a big amount of free scripts on the internet will not be available.
If someone comes up with a good (and working) hack, we'll be glad to add it to the download section...
We'll give database abstraction another thought when we start to develop cpg2.x though.

GauGau

[chodgson]

Actually, I think that PHPBB proves that it is possible to have a db abstraction layer and still be easy to install... a bigger problem in my mind is the upgrade process from the my-sql only version to the abstracted version... but I'll send in my hack, if/when I get it done.

Thanks for your response, GauGau.

[omniscientdeveloper]

Maybe dbx is better to use, since it doesn't require the user to include anything.

[Tarique Sani]

dbx is better but not compiled by default on many webservers

[Patrick 2.0]

dbx is better but not compiled by default on many webservers

Well, I guess I could make my DBX-emulator script available.  It's about 95% done and could be included automaticly if DBX is not compiled in

[Tarique Sani]

Well, I guess I could make my DBX-emulator script available.  It's about 95% done and could be included automaticly if DBX is not compiled in

If that works as you say it would indeed be the answer:)

[omniscientdeveloper]

Hopefully, it's not too fat.  :?  :wink:

[Patrick 2.0]

If that works as you say it would indeed be the answer:)

Let me do a little bit of code cleanup and I'll I'll send it to you.  Just need to optimize a couple functions (removing some debug lines)

[Patrick 2.0]

Ok, anyone I should send the script to?

It's still not complete, DBX_OCI8 & DBX_ODBC have not been finished (I have no experance with these) and it needs more tsting for non-MySQL DB's.  But, it should extend the allowed databases quite a bit.

[Joachim Müller]

Here's the code Patrick sent to me by email - he agreed on having this posted:

Hi!

Here's the script.  Let me know what you think.

Patrick

Code Select Expand

<?php
// ----------------------------------------------------------------------
// Pseudo-DBX
//
// A drop-in replacement for the DBX functions in PHP installs that are
// missing this option
//
// Copyright (C) 2003, 2004 by PNK Technology.
// http://www.digimon-wranglers.com/
// ----------------------------------------------------------------------
// LICENSE
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library 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
// Lesser General Public License for more details.
//
// To read the license please visit http://www.gnu.org/copyleft/lesser.html
// ----------------------------------------------------------------------
// Version 0.2 beta - March 04, 2004
// Notes:
//      o   Fixed values of defines.
//      o   Added support for SQLite, but it needs testing.
//
// Version 0.1 beta - March 28, 2003
// Notes:
//      o   Currently, the only tested DBX type id the DBX_MYSQL one.
//
// Todo:
//      o   Add support for DBX_RESULT_UNBUFFERED (CVS only).
//      o   Impliment dbx_fetch_row.
//      o   The dbx_escape_string function needs work for non DBX_MYSQL &
//          DBX_PGSQL types.
//      o   The DBX_OCI8 & DBX_ODBC types are not finished due to my lack
//          of knowledge of these database types.
// ----------------------------------------------------------------------

define('DBX_UNKNOWN', 0);
define('DBX_MYSQL', 1);
define('DBX_ODBC', 2);
define('DBX_PGSQL', 3);
define('DBX_MSSQL', 4);
define('DBX_FBSQL', 5);     // available from PHP 4.1.0
define('DBX_OCI8', 6);      // available from PHP 4.3.0
define('DBX_SYBASECT', 7);  // available from PHP 4.2.0
define('DBX_SQLITE ', 8);   // CVS Only
//
define('DBX_PERSISTENT', 1);
define('DBX_RESULT_UNBUFFERED', 6); // CVS Only
//
define('DBX_RESULT_INFO', 1);
define('DBX_RESULT_INDEX', 2);
define('DBX_RESULT_ASSOC', 4);
define('DBX_COLNAMES_UNCHANGED', 8);    // available from PHP 4.3.0
define('DBX_COLNAMES_UPPERCASE', 16);   // available from PHP 4.3.0
define('DBX_COLNAMES_LOWERCASE', 32);   // available from PHP 4.3.0
//
define('DBX_CMP_NATIVE', 1);
define('DBX_CMP_TEXT', 2);
define('DBX_CMP_NUMBER', 4);
define('DBX_CMP_ASC', 8);
define('DBX_CMP_DESC', 16);

function dbx_close($link_identifier)
{
    switch ($link_identifier['module'])
    {
        case DBX_MYSQL:
        {
            return mysql_close($link_identifier['handle']);
        }
        case DBX_ODBC:
        {
            odbc_close($link_identifier['handle']);
            return true;
        }
        case DBX_PGSQL:
        {
            return pg_close($link_identifier['handle']);
        }
        case DBX_MSSQL:
        {
            return mssql_close($link_identifier['handle']);
        }
        case DBX_FBSQL:
        {
            return fbsql_close($link_identifier['handle']);
        }
        case DBX_SYBASECT:
        {
            return sybase_close($link_identifier['handle']);
        }
        case DBX_SQLITE:
        {
            return sqlite_close($link_identifier['handle']);
        }
        case DBX_OCI8:
        default:
        {
            return false;
        }
    }
}

function dbx_compare($row_a, $row_b, $column_key, $flags=false)
{
    // set default
    if ($flags == false)
    {
        $flags = DBX_CMP_ASC | DBX_CMP_NATIVE;
    }
    // sort
    if (($flags && DBX_CMP_TEXT) || ($flags && DBX_CMP_NATIVE))
    {
        if ($flags && DBX_CMP_ASC)
        {
            if ($flags && DBX_CMP_NATIVE)
            {
                $ret = strcasecmp($row_a[$column_key], $row_b[$column_key]);
            }
            else
            {
                $ret = strcmp($row_a[$column_key], $row_b[$column_key]);
            }
            if ($ret < 0)
            {
                return -1;
            }
            elseif ($ret > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
        else
        {
            if ($flags && DBX_CMP_NATIVE)
            {
                $ret = strcasecmp($row_b[$column_key], $row_a[$column_key]);
            }
            else
            {
                $ret = strcmp($row_b[$column_key], $row_a[$column_key]);
            }
            if ($ret < 0)
            {
                return -1;
            }
            elseif ($ret > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
    }
    else // DBX_CMP_NUMBER
    {
        if ($row_a[$column_key] == $row_b[$column_key])
        {
            return 0;
        }
        if ($flags && DBX_CMP_ASC)
        {
            if ($row_a[$column_key] < $row_b[$column_key])
            {
                return -1;
            }
            if ($row_a[$column_key] > $row_b[$column_key])
            {
                return 1;
            }
        }
        else
        {
            if ($row_a[$column_key] > $row_b[$column_key])
            {
                return -1;
            }
            if ($row_a[$column_key] < $row_b[$column_key])
            {
                return 1;
            }
        }
    }
}

function dbx_connect($module, $host, $database, $username, $password, $persistent=0)
{
    switch ($module)
    {
        case DBX_MYSQL:
        case 'mysql':
        {
            $module = DBX_MYSQL;
            if ($persistent == DBX_PERSISTENT)
            {
                $link_identifier = mysql_pconnect($host, $username, $password);
            }
            else
            {
                $link_identifier = mysql_connect($host, $username, $password);
            }
            if ($link_identifier == false)
            {
                return false;
            }
            if (mysql_select_db($database, $link_identifier) == false)
            {
                @mysql_close($link_identifier);
                return false;
            }
            break;
        }
        case DBX_PGSQL:
        case 'pgsql':
        {
            $module = DBX_PGSQL;
            if ($persistent == DBX_PERSISTENT)
            {
                $link_identifier = pg_pconnect('host='.$host.' port=5432 dbname='.$database.' user='.$username.' password='.$password);
            }
            else
            {
                $link_identifier = pg_connect('host='.$host.' port=5432 dbname='.$database.' user='.$username.' password='.$password);
            }
            if ($link_identifier == false)
            {
                return false;
            }
            break;
        }
        case DBX_MSSQL:
        case 'mssql':
        {
            $module = DBX_MSSQL;
            if ($persistent == DBX_PERSISTENT)
            {
                $link_identifier = mssql_pconnect($host, $username, $password);
            }
            else
            {
                $link_identifier = mssql_connect($host, $username, $password);
            }
            if ($link_identifier == false)
            {
                return false;
            }
            if (mssql_select_db($database, $link_identifier) == false)
            {
                @mssql_close($link_identifier);
                return false;
            }
            break;
        }
        case DBX_FBSQL:
        case 'fbsql':
        {
            $module = DBX_FBSQL;
            if ($persistent == DBX_PERSISTENT)
            {
                $link_identifier = fbsql_pconnect($host, $username, $password);
            }
            else
            {
                $link_identifier = fbsql_connect($host, $username, $password);
            }
            if ($link_identifier == false)
            {
                return false;
            }
            if (fbsql_select_db($database, $link_identifier) == false)
            {
                @fbsql_close($link_identifier);
                return false;
            }
            break;
        }
        case DBX_SYBASECT:
        case 'sybase_ct':
        {
            $module = DBX_SYBASECT;
            if ($persistent == DBX_PERSISTENT)
            {
                $link_identifier = sybase_pconnect($host, $username, $password);
            }
            else
            {
                $link_identifier = sybase_connect($host, $username, $password);
            }
            if ($link_identifier == false)
            {
                return false;
            }
            if (sybase_select_db($database, $link_identifier) == false)
            {
                @sybase_close($link_identifier);
                return false;
            }
            break;
        }
        case DBX_SQLITE:
        case 'sqlite':
        {
            $module = DBX_SQLITE;
            if ($persistent == DBX_PERSISTENT)
            {
                $link_identifier = sqlite_popen($database);
            }
            else
            {
                $link_identifier = sqlite_open($database);
            }
            if ($link_identifier == false)
            {
                return false;
            }
            break;
        }
        case DBX_ODBC:
        case 'odbc':
        case DBX_OCI8:
        case 'oci8':
        default:
        {
            return false;
        }
    }
    return array(
        'database'=>$database,
        'handle'=>$link_identifier,
        'module'=>$module
    );
}

function dbx_error($link_identifier)
{
    switch ($link_identifier['module'])
    {
        case DBX_MYSQL:
        {
            return mysql_errno($link_identifier['handle']).': '.mysql_error($link_identifier['handle']);
        }
        case DBX_ODBC:
        {
            return odbc_error($link_identifier['handle']).': '.odbc_errormsg($link_identifier['handle']);
        }
        case DBX_PGSQL:
        {
            if (function_exists('pg_last_error'))
            {
                return pg_last_error($link_identifier['handle']);
            }
            else
            {
                return pg_errormessage($link_identifier['handle']);
            }
        }
        case DBX_MSSQL:
        {
            return mssql_get_last_message();
        }
        case DBX_FBSQL:
        {
            return fbsql_errno($link_identifier['handle']).': '.fbsql_error($link_identifier['handle']);
        }
        case DBX_SYBASECT:
        {
            return sybase_get_last_message();
        }
        case DBX_SQLITE:
        {
            return sqlite_last_error($link_identifier['handle']).': '.sqlite_error_string(sqlite_last_error($link_identifier['handle']));
        }
        case DBX_OCI8:
        default:
        {
            return '';
        }
    }
}

function dbx_escape_string($link_identifier, $text)
{
    switch ($link_identifier['module'])
    {
        case DBX_MYSQL:
        {
            // test for better escape string introduced in PHP 4.3.0
            if (function_exists('mysql_real_escape_string'))
            {
                return mysql_real_escape_string($text, $link_identifier['handle']);
            }
            else
            {
                return mysql_escape_string($text);
            }
        }
        case DBX_ODBC:
        {
            return addslashes($text);
        }
        case DBX_PGSQL:
        {
            // maybe add support for testing if it's a binary string???
            return pg_escape_string($text);
        }
        case DBX_MSSQL:
        {
            return addslashes($text);
        }
        case DBX_FBSQL:
        {
            return addslashes($text);
        }
        case DBX_SYBASECT:
        {
            return addslashes($text);
        }
        case DBX_SQLITE:
        {
            return sqlite_escape_string($text);
        }
        case DBX_OCI8:
        default:
        {
            return false;
        }
    }

}

function dbx_fetch_row($result_identifier)
{
}

function dbx_query($link_identifier, $sql_statement, $flags=false)
{
    switch ($link_identifier['module'])
    {
        case DBX_MYSQL:
        {
            return dbx_mysql_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_ODBC:
        {
            return dbx_odbc_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_PGSQL:
        {
            return dbx_pgsql_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_MSSQL:
        {
            return dbx_mssql_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_FBSQL:
        {
            return dbx_fbsql_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_SYBASECT:
        {
            return dbx_sybase_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_SQLITE:
        {
            return dbx_sqlite_query($link_identifier, $sql_statement, $flags);
        }
        case DBX_OCI8:
        default:
        {
            return false;
        }
    }
}

function dbx_sort($result, $user_compare_function)
{
    if (!is_array($result['data']))
    {
        return false;
    }
    uasort($result['data'], $user_compare_function);
    return true;
}

// ----------------------------------------------------------------------
// Private Functions
// ----------------------------------------------------------------------

function dbx_mysql_query($link_identifier, $sql_statement, $flags)
{
    $result = mysql_query($sql_statement, $link_identifier['handle']);
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        if ($flags && DBX_RESULT_ASSOC)
        {
            $rt = MYSQL_BOTH;
        }
        else
        {
            $rt = MYSQL_NUM;
        }
        $rows = array();
        while($row = mysql_fetch_array($result, $rt))
        {
            $rows[] = $row;
        }
        $rows = dbx_change_key_case($rows, $flags);
        $ret = array(
            'handle'=>$result,
            'cols'=>@mysql_num_fields($result),
            'rows'=>@mysql_num_rows($result),
            'data'=>$rows
        );
        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            for ($i = 0; $i < $ret['cols']; $i++)
            {
                $ret['info']['name'][] = mysql_field_name($result, $i);
                $ret['info']['type'][] = mysql_field_type($result, $i);
            }
        }
    }
    @mysql_free_result($result);
    return $ret;
}

function dbx_odbc_query($link_identifier, $sql_statement, $flags)
{
    $result = odbc_exec($link_identifier['handle'], $sql_statement);
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        if (function_exists('odbc_fetch_array'))
        {
            $rows = array();
            $i = 0;
            while($row = odbc_fetch_array($result, $i))
            {
                $rows[] = $row;
                $i++;
            }
            if (!($flags && DBX_RESULT_ASSOC))
            {
                $rows2 = array();
                foreach ($rows as $f=>$v)
                {
                    if (is_int($f))
                    {
                        $rows2[$f] = $v;
                    }
                }
                $rows = $rows2;
            }
        }
        else
        {
            $fcount = odbc_num_fields($result);
            $rows = array();
            while (odbc_fetch_row($result))
            {
                $row = array();
                for ($i = 0; $i < $fcount; $i++)
                {
                    $row[$i] = odbc_result($result, $i);
                    if ($flags && DBX_RESULT_ASSOC)
                    {
                        $fname = obdc_field_name($result, $i);
                        $row[$fname] = $row[$i];
                    }
                }
                $rows[] = $row;
            }
        }
        $rows = dbx_change_key_case($rows, $flags);
        $ret = array(
            'handle'=>$result,
            'cols'=>@odbc_num_fields($result),
            'rows'=>@odbc_num_rows($result),
            'data'=>$rows
        );
        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            for ($i = 0; $i < $ret['cols']; $i++)
            {
                $ret['info']['name'][] = odbc_field_name($result, $i);
                $ret['info']['type'][] = odbc_field_type($result, $i);
            }
        }
    }
    @odbc_free_result($result);
    return $ret;
}

function dbx_pgsql_query($link_identifier, $sql_statement, $flags)
{
    if (function_exists('pg_query'))
    {
        $result = pg_query($link_identifier['handle'], $sql_statement);
    }
    else
    {
        $result = pg_exec($link_identifier['handle'], $sql_statement);
    }
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        if ($flags && DBX_RESULT_ASSOC)
        {
            $rt = PGSQL_BOTH;
        }
        else
        {
            $rt = PGSQL_NUM;
        }

        $rows = array();
        $i = 0;
        while($row = pg_fetch_array($result, $i, $rt))
        {
            $rows[] = $row;
            $i++;
        }
        $rows = dbx_change_key_case($rows, $flags);
        $ret = array(
            'handle'=>$result,
            'data'=>$rows
        );
        if (function_exists('pg_num_fields'))
        {
            $ret['cols'] = @pg_num_fields($result);
            $ret['rows'] = @pg_num_rows($result);
        }
        else
        {
            $ret['cols'] = @pg_numfields($result);
            $ret['rows'] = @pg_numrows($result);
        }

        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            if (function_exists('pg_field_name'))
            {
                for ($i = 0; $i < $ret['cols']; $i++)
                {
                    $ret['info']['name'][] = pg_field_name($result, $i);
                    $ret['info']['type'][] = pg_field_type($result, $i);
                }
            }
            else
            {
                for ($i = 0; $i < $ret['cols']; $i++)
                {
                    $ret['info']['name'][] = pg_fieldname($result, $i);
                    $ret['info']['type'][] = pg_fieldtype($result, $i);
                }
            }
        }
    }
    if (function_exists('pg_free_result'))
    {
        @pg_free_result($result);
    }
    else
    {
        @pg_freeresult($result);
    }
    return $ret;
}

function dbx_mssql_query($link_identifier, $sql_statement, $flags)
{
    $result = mssql_query($sql_statement, $link_identifier['handle']);
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        if (defined(MSSQL_BOTH))
        {
            if ($flags && DBX_RESULT_ASSOC)
            {
                $rt = MSSQL_BOTH;
            }
            else
            {
                $rt = MSSQL_NUM;
            }
            $rows = array();
            while($row = mssql_fetch_array($result, $rt))
            {
                $rows[] = $row;
            }
        }
        else
        {
            // hack for older versions of PHP
            $rows = array();
            while($row = mssql_fetch_array($result))
            {
                $rows[] = $row;
            }
            if (!($flags && DBX_RESULT_ASSOC))
            {
                $rows2 = array();
                foreach ($rows as $f=>$v)
                {
                    if (is_int($f))
                    {
                        $rows2[$f] = $v;
                    }
                }
                $rows = $rows2;
            }
        }
        $rows = dbx_change_key_case($rows, $flags);
        $ret = array(
            'handle'=>$result,
            'cols'=>@mssql_num_fields($result),
            'rows'=>@mssql_num_rows($result),
            'data'=>$rows
        );
        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            for ($i = 0; $i < $ret['cols']; $i++)
            {
                $ret['info']['name'][] = mssql_field_name($result, $i);
                $ret['info']['type'][] = mssql_field_type($result, $i);
            }
        }
    }
    @mssql_free_result($result);
    return $ret;
}

function dbx_fbsql_query($link_identifier, $sql_statement, $flags)
{
    $result = fbsql_query($sql_statement, $link_identifier['handle']);
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        if ($flags && DBX_RESULT_ASSOC)
        {
            $rt = FBSQL_BOTH;
        }
        else
        {
            $rt = FBSQL_NUM;
        }

        $rows = array();
        while($row = fbsql_fetch_array($result, $rt))
        {
            $rows[] = $row;
        }
        $rows = dbx_change_key_case($rows, $flags);
        $ret = array(
            'handle'=>$result,
            'cols'=>@fbsql_num_fields($result),
            'rows'=>@fbsql_num_rows($result),
            'data'=>$rows
        );
        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            for ($i = 0; $i < $ret['cols']; $i++)
            {
                $ret['info']['name'][] = fbsql_field_name($result, $i);
                $ret['info']['type'][] = fbsql_field_type($result, $i);
            }
        }
    }
    @fbsql_free_result($result);
    return $ret;
}

function dbx_sybase_query($link_identifier, $sql_statement, $flags)
{
    $result = sybase_query($sql_statement, $link_identifier['handle']);
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        $rows = array();
        if ($flags && DBX_RESULT_ASSOC)
        {
            while($row = sybase_fetch_array($result))
            {
                $rows[] = $row;
            }
            $rows = dbx_change_key_case($rows, $flags);
        }
        else
        {
            while($row = sybase_fetch_row($result))
            {
                $rows[] = $row;
            }
        }
        $ret = array(
            'handle'=>$result,
            'cols'=>@sybase_num_fields($result),
            'rows'=>@sybase_num_rows($result),
            'data'=>$rows
        );
        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            for ($i = 0; $i < $ret['cols']; $i++)
            {
                $field = sybase_fetch_field($result, $i);
                $ret['info']['name'][] = $field['name'];
                $ret['info']['type'][] = $field['type'];
            }
        }
    }
    @sybase_free_result($result);
    return $ret;
}

function dbx_sqlite_query($link_identifier, $sql_statement, $flags)
{
    $result = sqlite_unbuffered_query($link_identifier['handle'], $sql_statement);
    if ($result == false)
    {
        $ret = false;
    }
    if (stristr(trim($sql_statement), 'select') == false)
    {
        $ret = true;
    }
    else
    {
        if ($flags && DBX_RESULT_ASSOC)
        {
            $rt = SQLITE_BOTH;
        }
        else
        {
            $rt = SQLITE_NUM;
        }
        $rows = array();
        while($row = sqlite_fetch_array($result, $rt))
        {
            $rows[] = $row;
        }
        $rows = dbx_change_key_case($rows, $flags);
        $ret = array(
            'handle'=>$result,
            'cols'=>@sqlite_num_fields($result),
            'rows'=>count($rows),
            'data'=>$rows
        );
        if (($flags && DBX_RESULT_INFO) || ($flags && DBX_RESULT_ASSOC))
        {
            $ret['info']['name'] = array();
            $ret['info']['type'] = array();
            for ($i = 0; $i < $ret['cols']; $i++)
            {
                $ret['info']['name'][] = sqlite_field_name($result, $i);
                // Currently, there appears to be no way to get the column
                // type for SQLite DB's, even the DBX extension has most of
                // this code commented out.
                $ret['info']['type'][] = 'string';
            }
        }
    }
    return $ret;
}

function dbx_change_key_case($rows, $flags)
{
    if ($flags && DBX_COLNAMES_UPPERCASE)
    {
        if (function_exists('array_change_key_case'))
        {
            $rows = array_change_key_case($rows, CASE_UPPER);
        }
        else
        {
            $nrows = array();
            foreach ($rows as $row)
            {
                foreach($row as $f=>$v)
                {
                    $nrows[strtoupper($f)] = $v;
                }
            }
            $rows = $nrows;
        }
    }
    elseif ($flags && DBX_COLNAMES_LOWERCASE)
    {
        if (function_exists('array_change_key_case'))
        {
            $rows = array_change_key_case($rows, CASE_LOWER);
        }
        else
        {
            $nrows = array();
            foreach ($rows as $row)
            {
                foreach($row as $f=>$v)
                {
                    $nrows[strtolower($f)] = $v;
                }
            }
            $rows = $nrows;
        }
    }
    return $rows;
}
?>

[BRC2]

Has anyone implemented this script? Successful? Anyone tried it with mssql? Where do I place the script to test?

Thanks,
Brian

[Joachim Müller]

Be the first to test it... Please report back.

GauGau

[Patrick 2.0]

If you have any questions, feel free to ask.  I have no free time to work on PHP, but I might be able to answer questions.

Patrick

[msti]

Hi!
How can I use this file with coppermine? I want to use sqlite beacause mysql is too heavy with 39000 images...
Any help appreciated
Thanks