db_extras.inc

<?php
function db_extras_subquery(&$db, $sql, $subs, $default_value=null)
{

    switch ($db->phptype) {
        // Sub-query compatible DB's
        case 'pgsql':
        case 'oci8':

            $find = Array();
            for ($i = 0; $i < count($subs); $i++) {
                $find[] = '~SQ'.$i.'~';
            }

            $sql = str_replace($find, $subs, $sql);
            return $sql;

            break;

        // Non-compatible DB's (...none now MySQL is not supported)
        default:

            $find    = Array();
            $replace = Array();
            for ($i = 0; $i < count($subs); $i++) {
                $find[$i] = '~SQ'.$i.'~';

                $result = MatrixDAL::executeSqlAssoc($subs[$i], 0);

                $replace[$i] = '';
                for ($j = 0, $total= count($result); $j < $total; $j++) {
                    $replace[$i] .= (($replace[$i]) ? ',' : '').MatrixDAL::quote($result[$j]);
                }

                if ($replace[$i] == '') $replace[$i] = MatrixDAL::quote($default_value);

            }//end for

            $sql = str_replace($find, $replace, $sql);
            return $sql;

    }//end switch

}//end db_extras_subquery()


function db_extras_insert_select(&$db, $insert, $select)
{

    switch ($db->phptype) {
        // INSERT INTO ... SELECT FROM Compatible DB's
        case 'pgsql' :
        case 'oci8'  :

            $result = MatrixDAL::executeSql($insert.' '.$select);
            if (DB::isError($result)) return $result;
            return TRUE;

            break;

        // Non Compatible DB's
        default :
            // ??? A bit difficult with
            $select_result = $db->query($select);
            if (DB::isError($select_result)) return $select_result;

            $row = Array();
            while (DB_OK === $select_result->fetchInto($row, DB_FETCHMODE_ORDERED)) {
                $field_total = count($row);
                if (!$field_total) continue;
                $sql = $insert.' VALUES (';
                for ($j = 0; $j < $field_total; $j++) {
                    $sql .= (($j) ? ',' : '').MatrixDAL::quote($row[$j]);
                }
                $sql .= ')';

                $insert_result = $db->query($sql);
                if (DB::isError($insert_result)) {
                    $select_result->free();
                    return $insert_result;
                }
            }
            $select_result->free();

            return TRUE;

    }//end switch

}//end db_extras_insert_select()


function db_extras_bitand($db_type, $val1, $val2)
{
    if ((int) $val1 != $val1) $val1 = MatrixDAL::quote($val1);
    if ((int) $val2 != $val2) $val2 = MatrixDAL::quote($val2);

    if ($db_type == 'oci') {
        $bit_op = 'BITAND('.$val1.', '.$val2.')';
    } else {
        $bit_op = '('.$val1.' & '.$val2.')';
    }

    return $bit_op;

}//end db_extras_bitand()


function db_extras_todate($db_type, $iso8601_date, $add_quotes=TRUE)
{
    // TODO:
    if ($add_quotes === TRUE) {
        $iso8601_date = '\''.$iso8601_date.'\'';
    }

    if ($db_type == 'pgsql') {
        return 'TO_TIMESTAMP('.$iso8601_date.', \'YYYY-MM-DD HH24:MI:SS\')';
    } else if ($db_type == 'oci') {
        return 'TO_DATE('.$iso8601_date.',\'YYYY-MM-DD HH24:MI:SS\')';
    }

}//end db_extras_todate()


function db_extras_prepare_todate($db_type)
{
    if ($db_type == 'pgsql') {
        return 'TO_TIMESTAMP(?, ?)';
    } else if ($db_type == 'oci8') {
        return 'TO_DATE(?, ?)';
    }

}//end db_extras_prepare_todate()


function db_get_function_result($db_type, $sql, $bind_vars=Array())
{
    $db = MatrixDAL::getDb();
    $result = NULL;

    if ($db_type == 'pgsql') {
        $sql = 'SELECT '.$sql;
        $query  = MatrixDAL::preparePdoQuery($sql);
        foreach ($bind_vars as $key => $value) {
            MatrixDAL::bindValueToPdo($query, $key, $value);
        }
        $result = MatrixDAL::executePdoOne($query);

    } else if ($db_type == 'oci') {
        // the reason that we don't do 'SELECT function() from DUAL' here is that
        // if the function peforms any DML operations, we will get an error that
        // you cannot perform DML operations from within a select query.
        $sql = 'BEGIN :res := '.$sql.'; END;';

        try {
            $query = MatrixDAL::preparePdoQuery($sql);
            MatrixDAL::bindValueToPdoByRef($query, 'res', $result, PDO::PARAM_INPUT_OUTPUT, 4000);

            // Bind the other variables for input only
            foreach ($bind_vars as $key => $value) {
                MatrixDAL::bindValueToPdo($query, $key, $value);
            }

            MatrixDAL::execPdoQuery($query);
        } catch (Exception $e) {
            throw new Exception('Could not run function due to database error: '.$e->getMessage());
        }

    }
    return $result;

}//end db_get_function_result()


function db_extras_modify_limit_clause($sql, $db_type, $limit, $offset=0)
{
    if ($db_type == 'oci') {
        if ($offset > 0) {
            $inner_sql = 'SELECT original_query.*, rownum rnum FROM (' . $sql . ') original_query WHERE rownum <= ' . ($offset + $limit);
            $sql = 'SELECT * FROM (' . $inner_sql . ') WHERE rnum > ' . $offset;
        } else {
            $sql  = 'SELECT * FROM ('.$sql.')';
            $sql .= ' WHERE ROWNUM BETWEEN 0 AND '.$limit;
        }
    } else { // psql, db2, mysql
        $sql  = $sql.' LIMIT '.$limit.' OFFSET '.$offset;
    }

    return $sql;

}//end db_modify_limit_clause()


?>