developer.jelix.org is not used any more and exists only for history. Post new tickets on the Github account.
developer.jelix.org n'est plus utilisée, et existe uniquement pour son historique. Postez les nouveaux tickets sur le compte github.

Opened 10 years ago

Closed 9 years ago

#1160 closed enhancement (fixed)

mssql driver, limit query implementation

Reported by: laurentj Owned by:
Priority: normal Milestone: Jelix 1.3 beta 1
Component: jelix:plugins:db Version: trunk
Severity: normal Keywords: mssql
Cc: Blocked By:
Blocking: Documentation needed: no
Hosting Provider: Php version:

Description

Proposition of an implementation on the forum http://jelix.org/forums/forum/5-jelix-utilisation-et-developpement/posts/6921-6866-mssql-limit

    /**
     * (non-PHPdoc)
     * @see lib/jelix/db/jDbConnection#_doLimitQuery()
     */
    protected function _doLimitQuery ($queryString, $offset, $number) {

        if(!mssql_select_db ($this->profile['database'], $this->_connection)) {
            if(mssql_get_last_message()) {
                throw new jException('jelix~db.error.database.unknow',$this->profile['database']);
            } else {
                throw new jException('jelix~db.error.connection.closed',$this->profile['name']);
            }
        }

        // on supprime le plausible premier 'TOP XX' 
        $queryString = preg_replace('/^SELECT TOP[ ]\d*\s*/i', 'SELECT ', $queryString);

        $distinct = false;

        // On récupère la partie de la requete entre les SELECT et le FROM
        $tmp = explode('FROM', $queryString);
        preg_match_all('/' . preg_quote('SELECT', '/') . '([^\)]+)'. preg_quote('FROM', '/').'/i', $tmp[0]." FROM", $matches['fields']);        

        // On coupe les champs par la virgule
        $res = explode(',', $matches['fields'][1][0]);

        // Si il y a une alias de champs
        if (stristr($res[0], ' as ') != false) {
            $res = explode(' as ', $res[0]);
            $key = $res[1]; // on récupère cette alias
        } else { // sinon on récupère le champs
            if (stristr($res[0], 'DISTINCT') != false) {
                $res[0] = $key = preg_replace('/DISTINCT/i', '', $res[0]);
                $distinct = true; // variable permettant de savoir si un distinct est présent dans la requete de base
            }
            if (stristr($res[0], '.')) { // après le '.' pour court circuiter l'explicitation de la table
                $res = explode('.', $res[0]);
                $key = $res[1];
            } else {
                $key = $res[0];
            }
        }
        //jLog::log($key);

        $orderby = stristr($queryString, 'ORDER BY');
        // Si il y a un ORDER BY dans la requete
        if ($orderby !== false) {
            // on récupère '[champ] ASC' ou '[champ] DESC'
            $order = str_ireplace('ORDER BY', '', $orderby);
        } else {
            $order = $key.' ASC';
            $queryString .= ' ORDER BY '.$order;
        }

        // Si la requete de base ne comportait pas de DISTINCT
        if(!$distinct)
            $queryString = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($number+$offset) . ' ', $queryString);
        else
            $queryString = preg_replace('/^SELECT DISTINCT\s/i', 'SELECT DISTINCT TOP ' . ($number+$offset) . ' ', $queryString);

        $queryString = 'SELECT TOP '.$number.' * FROM (SELECT TOP ' . $number . ' * FROM (' . $queryString . ') AS inner_tbl';
        $queryString .= ' ORDER BY ';

        $outer_order = preg_replace(array('/\bASC\b/i', '/\bDESC\b/i'), array('_DESC', '_ASC'), $order);
        $outer_order = str_replace(array('_DESC', '_ASC'), array('DESC', 'ASC'), $outer_order);

        $queryString .= $outer_order . ' ) AS outer_tbl ORDER BY ' . $order;

        // Requete de sortie
        //jLog::log($queryString);

        if(!mssql_select_db ($this->profile['database'], $this->_connection))
            throw new jException('jelix~db.error.database.unknow',$this->profile['database']);

        if ($qI = mssql_query($queryString, $this->_connection)) {
            return new mssqlDbResultSet ($qI);
        } else {
            throw new jException('jelix~db.error.query.bad',  mssql_get_last_message());
        }
    }


Change History (2)

comment:1 Changed 9 years ago by laurentj

  • Keywords mssql added
  • Milestone set to Jelix 1.3

comment:2 Changed 9 years ago by laurentj

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.