Render LIMIT OFFSET clause

@param string   $sql SQL query
@return string
960301
protected function _renderLimitoffset($sql)
    {
        $count = 0;
        $offset = 0;

        if (!empty($this->_parts[self::LIMIT_OFFSET])) {
            $offset = (int) $this->_parts[self::LIMIT_OFFSET];
            $count = PHP_INT_MAX;
        }

        if (!empty($this->_parts[self::LIMIT_COUNT])) {
            $count = (int) $this->_parts[self::LIMIT_COUNT];
        }

        /*
         * Add limits clause
         */
        if ($count > 0) {
            $sql = trim($this->_adapter->limit($sql, $count, $offset));
        }

        return $sql;
    }
1263577
public function bindLimit()
    {
        if ($this->limit !== null) {            
            $this->sqlQuery->bindValue($this->positioningMarker, $this->limit, PDO::PARAM_INT);
            $this->positioningMarker++;
            $this->sqlQuery->bindValue($this->positioningMarker, $this->offset, PDO::PARAM_INT);
        }

        return $this;
    }
111465
protected function buildLimit() {
		if(!$this->limit && !$this->offset)
			return '';

		$driver = $this->db->getPDO()->getAttribute(\PDO::ATTR_DRIVER_NAME);
		if($driver === 'pgsql') {
			$limit = '';
			if($this->limit)
				$limit .= "\n".'LIMIT '.$this->limit;
			if($this->offset)
				$limit .= "\n".'OFFSET '.$this->offset;
		}
		else {
			$limit = "\n".'LIMIT ';
			if($this->offset) {
				$limit .= $this->offset;
				if($this->limit)
					$limit .= ', '.$this->limit;
				else
					$limit .= ', '.PHP_INT_MAX;
			}
			else
				$limit .= $this->limit;
		}

		return $limit;
	}
581791
protected function query($sql) {
		$this->sqlCnt++;
		$res = $this->db->query($sql);
		if (!$res) {
			$this->dbError = $this->db->error;
		}
		return $res;
	}
1195013
protected function executeWithPaging($sql)
    {
        $count = 0;
        do {
            $stmt = $this->entityManager->getConnection()->executeQuery($sql);
            $stmt->execute();
            $lastCount = $stmt->rowCount();
            $count += $lastCount;
        } while ($lastCount > 0);

        return $count;
    }
755432
public static function highlight(string $sql): string
	{
		static $keywords1 = 'SELECT|(?:ON\s+DUPLICATE\s+KEY)?UPDATE|INSERT(?:\s+INTO)?|REPLACE(?:\s+INTO)?|SHOW|DELETE|CALL|UNION|FROM|WHERE|HAVING|GROUP\s+BY|ORDER\s+BY|LIMIT|OFFSET|SET|VALUES|LEFT\s+JOIN|INNER\s+JOIN|TRUNCATE|START\s+TRANSACTION|COMMIT|ROLLBACK|(?:RELEASE\s+|ROLLBACK\s+TO\s+)?SAVEPOINT';
		static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';
		$sql = ' ' . $sql . ' ';
		$sql = htmlspecialchars($sql, ENT_IGNORE, 'UTF-8');
		$sql = preg_replace_callback(sprintf('#(/\\*.+?\\*/)|(?<=[\\s,(])(%s)(?=[\\s,)])|(?<=[\\s,(=])(%s)(?=[\\s,)=])#is', $keywords1, $keywords2), function ($matches) {
			if (!empty($matches[1])) { // comment
				return '<em style="color:gray">' . $matches[1] . '</em>';
			}

			if (!empty($matches[2])) { // most important keywords
				return '<strong style="color:#2D44AD">' . $matches[2] . '</strong>';
			}

			if (!empty($matches[3])) { // other keywords
				return '<strong>' . $matches[3] . '</strong>';
			}
		}, $sql);

		return trim((string) $sql);
	}
229374
protected function buildLimit()
    {
        $has_limit  = $this->type == 'DELETE' || $this->type == 'UPDATE';
        $has_offset = $this->type == 'SELECT';

        if ($has_offset && $this->limit) {
            $clause = " LIMIT {$this->limit}";
            if ($this->offset) {
                $clause .= " OFFSET {$this->offset}";
            }
            return $clause;
        } elseif ($has_limit && $this->limit) {
            return " LIMIT {$this->limit}";
        }
        return '';
    }
899541
protected function set_limit($query, $limit = 0, $offset = 0)
    {
        $limit  = intval($limit);
        $offset = intval($offset);
        $end    = $offset + $limit;

        // query without OFFSET
        if (!$offset) {
            $query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query);
            return $query;
        }

        $orderby = stristr($query, 'ORDER BY');
        $offset += 1;

        if ($orderby !== false) {
            $query = trim(substr($query, 0, -1 * strlen($orderby)));
        }
        else {
            // it shouldn't happen, paging without sorting has not much sense
            // @FIXME: I don't know how to build paging query without ORDER BY
            $orderby = "ORDER BY 1";
        }

        $query = preg_replace('/^SELECT\s/i', '', $query);
        $query = "WITH paging AS (SELECT ROW_NUMBER() OVER ($orderby) AS [RowNumber], $query)"
            . " SELECT * FROM paging WHERE [RowNumber] BETWEEN $offset AND $end ORDER BY [RowNumber]";

        return $query;
    }
749066
protected function checkForMainOffsetOrLimit($sql)
    {
        if ("" !== $this->stripQueryToUnnestedKeyword($sql, $unnested_keyword = 'LIMIT')) {
            throw new \LogicException("Query cannot contain an unnested LIMIT when trying to paginate");
        }

        if ("" !== $this->stripQueryToUnnestedKeyword($sql, $unnested_keyword = 'OFFSET')) {
            throw new \LogicException("Query cannot contain an unnested OFFSET when trying to paginate");
        }

        return true;
    }
1264072
protected function query_row ( $sql )
	{
		$sql = $this->sql_printf(func_get_args());
		try 
		{
			$all = $this->conn->query($sql, PDO::FETCH_NUM);
			$row = array();
			foreach ($all as $r) {
				$row = $r;
				break;
			}
		}
		catch (PDOException $e)
		{
			$this->sql_errcheck($sql);
		}
		return $row;
	}
535773
public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /**
             * @see Zend_Db_Adapter_Oracle_Exception
             */
            // require_once 'Zend/Db/Adapter/Oracle/Exception.php';
            throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /**
             * @see Zend_Db_Adapter_Oracle_Exception
             */
            // require_once 'Zend/Db/Adapter/Oracle/Exception.php';
            throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument offset=$offset is not valid");
        }

        /**
         * Oracle does not implement the LIMIT clause as some RDBMS do.
         * We have to simulate it with subqueries and ROWNUM.
         * Unfortunately because we use the column wildcard "*",
         * this puts an extra column into the query result set.
         */
        $limit_sql = "SELECT z2.*
            FROM (
                SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
        return $limit_sql;
    }
244037
protected function _limit($sql)
	{
		// Limit clause depends on if Interbase or Firebird
		if (stripos($this->version(), 'firebird') !== FALSE)
		{
			$select = 'FIRST '.$this->qb_limit
				.($this->qb_offset ? ' SKIP '.$this->qb_offset : '');
		}
		else
		{
			$select = 'ROWS '
				.($this->qb_offset ? $this->qb_offset.' TO '.($this->qb_limit + $this->qb_offset) : $this->qb_limit);
		}

		return preg_replace('`SELECT`i', 'SELECT '.$select, $sql, 1);
	}
830943
function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
	{
		$save = $this->_driver->fetchMode;
		$this->_driver->fetchMode = $this->fetchMode;
		$this->_driver->debug = $this->debug;
		$ret = $this->_driver->SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
		$this->_driver->fetchMode = $save;
		return $ret;
	}
855719
protected function _renderFrom($sql)
    {
        /*
         * If no table specified, use RDBMS-dependent solution
         * for table-less query.  e.g. DUAL in Oracle.
         */
        if (empty($this->_parts[self::FROM])) {
            $this->_parts[self::FROM] = $this->_getDummyTable();
        }

        $from = array();

        foreach ($this->_parts[self::FROM] as $correlationName => $table) {
            $tmp = '';

            $joinType = ($table['joinType'] == self::FROM) ? self::INNER_JOIN : $table['joinType'];

            // Add join clause (if applicable)
            if (! empty($from)) {
                $tmp .= ' ' . strtoupper($joinType) . ' ';
            }

            $tmp .= $this->_getQuotedSchema($table['schema']);
            $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName);

            // Add join conditions (if applicable)
            if (!empty($from) && ! empty($table['joinCondition'])) {
                $tmp .= ' ' . self::SQL_ON . ' ' . $table['joinCondition'];
            }

            // Add the table name and condition add to the list
            $from[] = $tmp;
        }

        // Add the list of all joins
        if (!empty($from)) {
            $sql .= ' ' . self::SQL_FROM . ' ' . implode("\n", $from);
        }

        return $sql;
    }
316247
public function formatLimitOffset($limit, $offset = 0) {
        if ($limit && $offset) {
            return sprintf($this->getClause(self::LIMIT_OFFSET), (int) $limit, (int) $offset);
        }

        return $this->formatLimit($limit);
    }
138686
public function limit($sql, $count, $offset = 0)
     {
        $count = intval($count);
        if ($count <= 0) {
            // require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /** @see Zend_Db_Adapter_Exception */
            // require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
        }

        if ($offset == 0) {
            $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql);
        } else {
            $orderby = stristr($sql, 'ORDER BY');

            if (!$orderby) {
                $over = 'ORDER BY (SELECT 0)';
            } else {
                $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
            }

            // Remove ORDER BY clause from $sql
            $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);

            // Add ORDER BY clause as an argument for ROW_NUMBER()
            $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"ZEND_DB_ROWNUM\", * FROM ($sql) AS inner_tbl";

            $start = $offset + 1;
            $end = $offset + $count;

            $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" BETWEEN $start AND $end";
        }

        return $sql;
    }
1166855
public static function splitSql($sql)
  {
    $start = 0;
    $open = false;
    $char = '';
    $end = strlen($sql);
    $queries = [];

    for($i=0; $i<$end; $i++){
      $current = substr($sql, $i, 1);
      if ( $current == '"' || $current == '\'' ){
        $n = 2;

        while(substr($sql, $i - $n + 1, 1) == '\\' && $n < $i){
          $n++;
        }

        if ( $n % 2 == 0 ){
          if ( $open ){
            if ( $current == $char ){
              $open = false;
              $char = '';
            }
          }
          else {
            $open = true;
            $char = $current;
          }
        }
      }

      if ( ($current == ';' && !$open) || $i == $end - 1 ){
        $queries[] = substr($sql, $start, ($i - $start + 1));
        $start = $i + 1;
      }
    }

    return $queries;
  }