MS SQL Server Syntax

[fabioars]

There is some diferences between MS SQL Server syntax and others DBMS. I'm using GenericBuilder How can I adapt for MS SQL Server?

Example: MySQL: SELECT * FROM table_name LIMIT 10

SQL Server: SELECT TOP 10 * FROM table_name

[joseph-montanez]

Looks like you'll need to create your own builder like the MySQL builder and then replace some of the query writers:

    protected $queryWriterArray = [
        'SELECT' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createSelectWriter',
        'INSERT' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createInsertWriter',
        'UPDATE' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createUpdateWriter',
        'DELETE' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createDeleteWriter',
        'INTERSECT' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createIntersectWriter',
        'MINUS' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createMinusWriter',
        'UNION' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createUnionWriter',
        'UNION ALL' => '\NilPortugues\Sql\QueryBuilder\Builder\Syntax\WriterFactory::createUnionAllWriter',
    ];

You'll need to extend the src/Builder/Syntax/SelectWriter.php as your own and then replace the select writer in that query builder writer array.

[huchim]

I recently discover this package and it's awesome!

But I had implemented in this way:

SqlSrvBuilder:

<?php namespace MyNamespace\SqlSrv;

use \NilPortugues\Sql\QueryBuilder\Builder\Syntax\PlaceholderWriter;
use \NilPortugues\Sql\QueryBuilder\Builder\BuilderInterface;
use \NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

class SqlSrvBuilder extends GenericBuilder {
    public function __construct() {
        parent::__construct();
        
        // override select factory
        $this->queryWriterArray["SELECT"] = self::class . "::createSelectWriter";
    }

    public static function createSelectWriter(BuilderInterface $writer, PlaceholderWriter $placeholderWriter) {
        return new CustomSelectWriter($writer, $placeholderWriter);
    }
}

CustomSelectWriter:

<?php namespace MyNamespace\SqlSrv;

use NilPortugues\Sql\QueryBuilder\Manipulation\Select;
use \NilPortugues\Sql\QueryBuilder\Builder\Syntax\SelectWriter;

class CustomSelectWriter extends SelectWriter {
    const rowNumberColumn = "_RowNumber";

    public function writeSelectColumns(Select $select, array &$parts)
    {
        $mask = $this->getStartingLimit($select).$this->getLimitCount($select);

        if ($mask !== '00') {
            $select->setFunctionAsColumn("ROW_NUMBER() OVER ({_orderClause})", array(), self::rowNumberColumn);
        }

        parent::writeSelectColumns($select, $parts);

        return $this;
    }

    /**
     * @param Select $select
     * @param array  $parts
     *
     * @return $this
     */
    public function writeSelectLimit(Select $select, array &$parts)
    {
        $mask = $this->getStartingLimit($select).$this->getLimitCount($select);
        $limit = [""];

        if ($mask !== '00') {
            $start = $this->placeholderWriter->add($select->getLimitStart());
            $count = $this->placeholderWriter->add($select->getLimitCount());
            $limit = "LIMIT2 {$start}, {$count}";
            
            $orderClause = "";
            $orderIndex = -1;

            foreach ($parts as $index => $part) {
                // Localizar la ordenación.
                if (substr($part, 0, 8) == "ORDER BY") {
                    $orderClause = $part;
                    $orderIndex = $index;
                    break;
                }
            }

            if ($orderIndex === -1) {
                throw new \Exception("ORDER BY not implemented.");
            }

            foreach ($parts as $index => $part) {
                if (strpos($part, "ROW_NUMBER()") !== false) {
                    $parts[$index] = str_replace("{_orderClause}", $orderClause, $part);
                }
            }

            unset($parts[$orderIndex]);

            $sql = ["SELECT", "*","FROM", "("];
            $sql[] = implode(" ", \array_filter($parts));
            $parts = \array_merge($sql, [
                ")", " AS _OrderedTable", "WHERE", 
                self::rowNumberColumn, ">=", $start, "AND", 
                self::rowNumberColumn, "<=", $count]);
        }

        return $this;
    }
}

I know this and old issue!

Thanks for your code!