Subversion-Projekte lars-tiefland.php_share

<?php

/*

*  $Id: DBMSSQL.php 1262 2009-10-26 20:54:39Z francois $

*

* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS

* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT

* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR

* A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT

* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,

* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT

* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,

* DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY

* THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT

* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE

* OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

*

* This software consists of voluntary contributions made by many individuals

* and is licensed under the LGPL. For more information please see

* <http://propel.phpdb.org>.

*/

/**

 * This is used to connect to a MSSQL database.

 *

 * @author     Hans Lellelid <hans@xmpl.org> (Propel)

 * @version    $Revision: 1262 $

 * @package    propel.adapter

 */

class DBMSSQL extends DBAdapter {

	/**

	 * This method is used to ignore case.

	 *

	 * @param      in The string to transform to upper case.

	 * @return     The upper case string.

	 */

	public function toUpperCase($in)

	{

		return "UPPER(" . $in . ")";

	}

	/**

	 * This method is used to ignore case.

	 *

	 * @param      in The string whose case to ignore.

	 * @return     The string in a case that can be ignored.

	 */

	public function ignoreCase($in)

	{

		return "UPPER(" . $in . ")";

	}

	/**

	 * Returns SQL which concatenates the second string to the first.

	 *

	 * @param      string String to concatenate.

	 * @param      string String to append.

	 * @return     string

	 */

	public function concatString($s1, $s2)

	{

		return "($s1 + $s2)";

	}

	/**

	 * Returns SQL which extracts a substring.

	 *

	 * @param      string String to extract from.

	 * @param      int Offset to start from.

	 * @param      int Number of characters to extract.

	 * @return     string

	 */

	public function subString($s, $pos, $len)

	{

		return "SUBSTRING($s, $pos, $len)";

	}

	/**

	 * Returns SQL which calculates the length (in chars) of a string.

	 *

	 * @param      string String to calculate length of.

	 * @return     string

	 */

	public function strLength($s)

	{

		return "LEN($s)";

	}

	/**

	 * @see        DBAdapter::quoteIdentifier()

	 */

	public function quoteIdentifier($text)

	{

		return '[' . $text . ']';

	}

	/**

	 * @see        DBAdapter::random()

	 */

	public function random($seed = null)

	{

		return 'rand('.((int) $seed).')';

	}

  /**

   * Simulated Limit/Offset

   * This rewrites the $sql query to apply the offset and limit.

   * @see        DBAdapter::applyLimit()

   * @author     Justin Carlson <justin.carlson@gmail.com>

   * @author     Benjamin Runnels <kraven@kraven.org>

   */

  public function applyLimit(&$sql, $offset, $limit)

  {

    // make sure offset and limit are numeric

    if (!is_numeric($offset) || !is_numeric($limit))

    {

      throw new Exception("DBMSSQL::applyLimit() expects a number for argument 2 and 3");

    }

    //split the select and from clauses out of the original query

    $selectSegment = array();

    preg_match('/\Aselect(.*)from(.*)/si',$sql,$selectSegment);

    if (count($selectSegment)==3)

    {

      $selectStatement = trim($selectSegment[1]);

      $fromStatement = trim($selectSegment[2]);

    }

    else

    {

      throw new Exception("DBMSSQL::applyLimit() could not locate the select statement at the start of the query. ");

    }

    //handle the ORDER BY clause if present

    $orderSegment = array();

    preg_match('/order by(.*)\Z/si',$fromStatement,$orderSegment);

    if (count($orderSegment)==2)

    {

      //remove the ORDER BY from $sql

      $fromStatement = trim(str_replace($orderSegment[0], '', $fromStatement));

      //the ORDER BY clause is used in our inner select ROW_NUMBER() clause

      $countColumn = trim($orderSegment[1]);

    }

    //setup inner and outer select selects

    $innerSelect = '';

    $outerSelect = '';

    foreach(explode(', ',$selectStatement) as $selCol) {

      @list($column,,$alias) = explode(' ', $selCol);

      //make sure the current column isn't * or an aggregate

      if ($column!='*' && !strstr($column,'(')) {

        //we can use the first non-aggregate column for ROW_NUMBER() if it wasn't already set from an order by clause

        if(!isset($countColumn)) {

          $countColumn = $column;

        }

        //add an alias to the inner select so all columns will be unique

        $innerSelect .= $column." AS [$column],";

        //use the alias in the outer select if one was present on the original select column

        if(isset($alias)) {

          $outerSelect .= "[$column] AS $alias,";

        } else {

          $outerSelect .= "[$column],";

        }

      } else {

        //agregate columns must always have an alias clause

        if(!isset($alias)) {

          throw new Exception("DBMSSQL::applyLimit() requires aggregate columns to have an Alias clause");

        }

        //use the whole aggregate column in the inner select

        $innerSelect .= "$selCol,";

        //only add the alias for the aggregate to the outer select

        $outerSelect .= "$alias,";

      }

    }

    //check if we got this far and still don't have a viable column to user with ROW_NUMBER()

    if(!isset($countColumn)) {

      throw new Exception("DBMSSQL::applyLimit() requires an ORDER BY clause or at least one non-aggregate column in the select statement");

    }

    //ROW_NUMBER() starts at 1 not 0

    $from = ($offset+1);

    $to = ($limit+$offset);

    //substring our select strings to get rid of the last comma and add our FROM and SELECT clauses

    $innerSelect = "SELECT ROW_NUMBER() OVER(ORDER BY $countColumn) AS RowNumber, ".substr($innerSelect,0,-1).' FROM';

    $outerSelect = 'SELECT '.substr($outerSelect,0,-1).' FROM';

    // build the query

    $sql = "$outerSelect ($innerSelect $fromStatement) AS derivedb WHERE RowNumber BETWEEN $from AND $to";

  }

}