1: <?php namespace Laravel\Database\Query\Grammars;
  2: 
  3: use Laravel\Database\Query;
  4: 
  5: class SQLServer extends Grammar {
  6: 
  7:     /**
  8:      * The keyword identifier for the database system.
  9:      *
 10:      * @var string
 11:      */
 12:     protected $wrapper = '[%s]';
 13: 
 14:     /**
 15:      * The format for properly saving a DateTime.
 16:      *
 17:      * @var string
 18:      */
 19:     public $datetime = 'Y-m-d H:i:s.000';
 20: 
 21:     /**
 22:      * Compile a SQL SELECT statement from a Query instance.
 23:      *
 24:      * @param  Query   $query
 25:      * @return string
 26:      */
 27:     public function select(Query $query)
 28:     {
 29:         $sql = parent::components($query);
 30: 
 31:         // SQL Server does not currently implement an "OFFSET" type keyword, so we
 32:         // actually have to generate the ANSI standard SQL for doing offset like
 33:         // functionality. OFFSET is in SQL Server 2012, however.
 34:         if ($query->offset > 0)
 35:         {
 36:             return $this->ansi_offset($query, $sql);
 37:         }
 38: 
 39:         // Once all of the clauses have been compiled, we can join them all as
 40:         // one statement. Any segments that are null or an empty string will
 41:         // be removed from the array before imploding.
 42:         return $this->concatenate($sql);
 43:     }
 44: 
 45:     /**
 46:      * Compile the SELECT clause for a query.
 47:      *
 48:      * @param  Query   $query
 49:      * @return string
 50:      */
 51:     protected function selects(Query $query)
 52:     {
 53:         if ( ! is_null($query->aggregate)) return;
 54: 
 55:         $select = ($query->distinct) ? 'SELECT DISTINCT ' : 'SELECT ';
 56: 
 57:         // Instead of using a "LIMIT" keyword, SQL Server uses the TOP keyword
 58:         // within the SELECT statement. So, if we have a limit, we will add
 59:         // it to the query here if there is not an OFFSET present.
 60:         if ($query->limit > 0 and $query->offset <= 0)
 61:         {
 62:             $select .= 'TOP '.$query->limit.' ';
 63:         }
 64: 
 65:         return $select.$this->columnize($query->selects);
 66:     }
 67: 
 68:     /**
 69:      * Generate the ANSI standard SQL for an offset clause.
 70:      *
 71:      * @param  Query  $query
 72:      * @param  array  $components
 73:      * @return array
 74:      */
 75:     protected function ansi_offset(Query $query, $components)
 76:     {
 77:         // An ORDER BY clause is required to make this offset query work, so if
 78:         // one doesn't exist, we'll just create a dummy clause to trick the
 79:         // database and pacify it so it doesn't complain about the query.
 80:         if ( ! isset($components['orderings']))
 81:         {
 82:             $components['orderings'] = 'ORDER BY (SELECT 0)';
 83:         }
 84: 
 85:         // We need to add the row number to the query so we can compare it to
 86:         // the offset and limit values given for the statement. So we'll add
 87:         // an expression to the select for the row number.
 88:         $orderings = $components['orderings'];
 89: 
 90:         $components['selects'] .= ", ROW_NUMBER() OVER ({$orderings}) AS RowNum";
 91: 
 92:         unset($components['orderings']);
 93: 
 94:         $start = $query->offset + 1;
 95: 
 96:         // Next we need to calculate the constraint that should be placed on
 97:         // the row number to get the correct offset and limit on the query.
 98:         // If there is not a limit, we'll just handle the offset.
 99:         if ($query->limit > 0)
100:         {
101:             $finish = $query->offset + $query->limit;
102: 
103:             $constraint = "BETWEEN {$start} AND {$finish}";
104:         }
105:         else
106:         {
107:             $constraint = ">= {$start}";
108:         }
109: 
110:         // We're finally ready to build the final SQL query so we'll create
111:         // a common table expression with the query and select all of the
112:         // results with row numbers between the limit and offset.
113:         $sql = $this->concatenate($components);
114: 
115:         return "SELECT * FROM ($sql) AS TempTable WHERE RowNum {$constraint}";
116:     }
117: 
118:     /**
119:      * Compile the LIMIT clause for a query.
120:      *
121:      * @param  Query   $query
122:      * @return string
123:      */
124:     protected function limit(Query $query)
125:     {
126:         return '';
127:     }
128: 
129:     /**
130:      * Compile the OFFSET clause for a query.
131:      *
132:      * @param  Query   $query
133:      * @return string
134:      */
135:     protected function offset(Query $query)
136:     {
137:         return '';
138:     }
139: 
140: }