Idiorm and Firebird

[mapner]

Hi,

I've adapted Idiorm for Firebird (www.firebirdsql.org).

/**
* Return the correct character used to quote identifiers (table
* names, column names etc) by looking at the driver being used by PDO.
*/
protected static function _detect_identifier_quote_character() {
    switch(self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)) {
        case 'pgsql':
        case 'sqlsrv':
        case 'dblib':
        case 'mssql':
        case 'sybase':

        //
        //
        case 'firebird':
        return '"';
        //
        //

        case 'mysql':
        case 'sqlite':
        case 'sqlite2':
        default:
        return '`';
    }
}


/**
* Return the correct word LIMIT wordused 
* by looking at the driver being used by PDO.
*/
protected static function _detect_identifier_limit_word() {
    switch(self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)) {                

        //
        //
        case 'firebird':
        return 'FIRST ';
        //
        //

        default:
        return 'LIMIT ';
    }
}

/**
* Return the correct word OFFSET wordused 
* by looking at the driver being used by PDO.
*/
protected static function _detect_identifier_offset_word() {
    switch(self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)) {                

        //
        //
        case 'firebird':
        return 'SKIP ';
        //
        //

        default:
        return 'OFFSET ';
    }
}


/**
* Tell the ORM that you wish to execute a COUNT query.
* Will return an integer representing the number of
* rows returned.
*/
public function count() {
    //$this->select_expr('COUNT(*)', 'count');

    //
    //
    // 'count ' is reseved word in Firebird
    $_order_by = $this->_order_by;
    $this->_order_by = array(); // MP empty order by...
    $this->select_expr('COUNT(*)', 'cnt');

    $result = $this->find_one();
    $this->_order_by = $_order_by;
    return ($result !== false && isset($result->CNT)) ? (int) $result->CNT : 0;
    //
    //

}

/**
* Build a SELECT statement based on the clauses that have
* been passed to this instance by chaining method calls.
*/
protected function _build_select() {
    // If the query is raw, just set the $this->_values to be
    // the raw query parameters and return the raw query
    if ($this->_is_raw_query) {
        $this->_values = $this->_raw_parameters;
        return $this->_raw_query;
    }

    // Build and return the full SELECT statement by concatenating
    // the results of calling each separate builder method.
    return $this->_join_if_not_empty(" ", array(
    $this->_build_select_start(),
    $this->_build_join(),
    $this->_build_where(),
    $this->_build_group_by(),
    $this->_build_order_by(),

    //
    //
(self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)=='firebird')?'':$this->_build_limit(),
    (self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)=='firebird')?'':$this->_build_offset(),
    ));
    //
    //

}

/**
* Build the start of the SELECT statement
*/
protected function _build_select_start() {
    $result_columns = join(', ', $this->_result_columns);

    if ($this->_distinct) {
        $result_columns = 'DISTINCT ' . $result_columns;
    }

    //
    //
    if(self::$_db->getAttribute(PDO::ATTR_DRIVER_NAME)=='firebird') {
        $limit = $this->_build_limit(); 
        $offset = $this->_build_offset();               
    }
    else {
        $limit = '';
        $offset = ''; 
    }

    $fragment = "SELECT {$limit} {$offset} {$result_columns} FROM " . $this->_quote_identifier($this->_table_name);

    if (!is_null($this->_table_alias)) {
        $fragment .= " " . $this->_quote_identifier($this->_table_alias);
        }       

    return $fragment;
    //
    //

}

[treffynnon]

I think that it would better to use Firebird's ROWS ^DOCs directive instead of SKIP and OFFSET as it fits better with the way Idiorm is generating queries. It would mean that you don't need to change _build_select() or _build_select_start().

Plus the Firebird manual describes ROWS as a better alternative.

count() can just be changed for all DB types to use an alias of row_count or something else innocuous to prevent a Firebird specialist hack in that part of the code. Isn't the alias being escaped by the character in _detect_identifier_quote_character() anyway? In which case this should not be needed at all.

Do you have any thoughts about those changes or know of any reasons it won't work?

[treffynnon]

I have committed my amended version of this path in commit bc0832e on the develop branch. Please could you try it out (paying particular attention to the count(), limit() and offset() methods).

Ensure you download Idiorm from the develop branch: https://github.com/j4mie/idiorm/tree/develop

[mapner]

Hi,

The changes you proposed work ok only in Firebird version >= 2.1

I use 'FIRST' 'SKIP' and 'COUNT' alias to maintain code of legacy firebird version (1.5.6) and are compatible with new versions.

[treffynnon]

I have just taken a look and:

1. ROWS was added in version 2.0 and not 2.1 according to the documentation
2. Firebird 2.0 was released on 12 November 2006

Therefore I consider this feature to have been available and stable for long enough to implement it in preference to the SKIP/FIRST method, which appears to now be deprecated from what I can see in the Firebird documentation.

I maybe wrong, but based on this it would seem unlikely that anyone would be running servers prior to version 2.0 of Firebird so I would rather keep Idiorm cleaner and not cater to Firebird 1.0.

A similar compromise was reached with a similar ticket relating to PostgreSQL: #62

[mapner]

You're right. Is V 2.0 and the V. 1.5.6 is depreciated but I have clients that still use it!(dont ask why) But I understand follow the most stable version.

[treffynnon]

OK fair enough. I think you will need to keep a custom fork for that functionality though I am afraid. Thank you for your code though :)

[asavasamuel]

STOP SPAMMING WITH PAID LINKS [edited by @treffynnon]