Skip to content

Commit f9e13ae

Browse files
numtelnumtel
committed
Added OFFSET to LIMIT clause and MSSQL OFFSET/FETCH
* MySQL style LIMIT Number, Number also now supported
1 parent 4da6dca commit f9e13ae

9 files changed

Lines changed: 268 additions & 90 deletions

File tree

‎browser/sql-parser.js‎

Lines changed: 72 additions & 42 deletions
Large diffs are not rendered by default.

‎lib/compiled_parser.js‎

Lines changed: 48 additions & 33 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎lib/grammar.js‎

Lines changed: 27 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎lib/lexer.js‎

Lines changed: 2 additions & 4 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎lib/nodes.js‎

Lines changed: 22 additions & 5 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎src/grammar.coffee‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -90,10 +90,13 @@ grammar =
9090

9191
LimitClause: [
9292
o 'LIMIT Number', -> new Limit($2)
93+
o 'LIMIT Number SEPARATOR Number', -> new Limit($4, $2)
94+
o 'LIMIT Number OFFSET Number', -> new Limit($2, $4)
9395
]
9496

9597
OrderClause: [
9698
o 'ORDER BY OrderArgs', -> new Order($3)
99+
o 'ORDER BY OrderArgs OffsetClause', -> new Order($3, $4)
97100
]
98101

99102
OrderArgs: [
@@ -106,6 +109,22 @@ grammar =
106109
o 'Value DIRECTION', -> new OrderArgument($1, $2)
107110
]
108111

112+
OffsetClause: [
113+
# MS SQL Server 2012+
114+
o 'OFFSET OffsetRows', -> new Offset($2)
115+
o 'OFFSET OffsetRows FetchClause', -> new Offset($2, $3)
116+
]
117+
118+
OffsetRows: [
119+
o 'Number ROW', -> $1
120+
o 'Number ROWS', -> $1
121+
]
122+
123+
FetchClause: [
124+
o 'FETCH FIRST OffsetRows ONLY', -> $3
125+
o 'FETCH NEXT OffsetRows ONLY', -> $3
126+
]
127+
109128
GroupClause: [
110129
o 'GroupBasicClause'
111130
o 'GroupBasicClause HavingClause', -> $1.having = $2; $1

‎src/lexer.coffee‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -82,7 +82,15 @@ class Lexer
8282
@tokenizeFromWord('ON') or
8383
@tokenizeFromWord('AS') or
8484
@tokenizeFromWord('UNION') or
85-
@tokenizeFromWord('ALL')
85+
@tokenizeFromWord('ALL') or
86+
@tokenizeFromWord('LIMIT') or
87+
@tokenizeFromWord('OFFSET') or
88+
@tokenizeFromWord('FETCH') or
89+
@tokenizeFromWord('ROW') or
90+
@tokenizeFromWord('ROWS') or
91+
@tokenizeFromWord('ONLY') or
92+
@tokenizeFromWord('NEXT') or
93+
@tokenizeFromWord('FIRST')
8694

8795
dotToken: -> @tokenizeFromWord('DOT', '.')
8896
operatorToken: -> @tokenizeFromList('OPERATOR', SQL_OPERATORS)
@@ -128,7 +136,6 @@ class Lexer
128136
regexEscape: (str) ->
129137
str.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, "\\$&")
130138

131-
SQL_KEYWORDS = ['SELECT', 'FROM', 'WHERE', 'GROUP BY', 'ORDER BY', 'HAVING', 'AS']
132139
SQL_FUNCTIONS = ['AVG', 'COUNT', 'MIN', 'MAX', 'SUM']
133140
SQL_SORT_ORDERS = ['ASC', 'DESC']
134141
SQL_OPERATORS = ['=', '!=', '>=', '>', '<=', '<>', '<', 'LIKE', 'IS NOT', 'IS']

‎src/nodes.coffee‎

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@ exports.LiteralValue = class LiteralValue
5050
else
5151
@nested = false
5252
@values = [@value]
53+
# TODO: Backtick quotes only supports MySQL, Postgres uses double-quotes
5354
toString: -> "`#{@values.join('.')}`"
5455

5556
exports.StringValue = class StringValue
@@ -98,16 +99,22 @@ exports.FunctionValue = class FunctionValue
9899
"#{@name.toUpperCase()}()"
99100

100101
exports.Order = class Order
101-
constructor: (@orderings) ->
102-
toString: -> "ORDER BY #{@orderings.join(', ')}"
102+
constructor: (@orderings, @offset) ->
103+
toString: -> "ORDER BY #{@orderings.join(', ')}" +
104+
(if @offset then "\n" + @offset.toString() else "")
103105

104106
exports.OrderArgument = class OrderArgument
105107
constructor: (@value, @direction='ASC') -> null
106108
toString: -> "#{@value} #{@direction}"
107109

110+
exports.Offset = class Offset
111+
constructor: (@row_count, @limit) -> null
112+
toString: -> "OFFSET #{@row_count} ROWS" +
113+
(if @limit then "\nFETCH NEXT #{@limit} ROWS ONLY" else "")
114+
108115
exports.Limit = class Limit
109-
constructor: (@value) -> null
110-
toString: -> "LIMIT #{@value}"
116+
constructor: (@value, @offset) -> null
117+
toString: -> "LIMIT #{@value}" + (if @offset then "\nOFFSET #{@offset}" else "")
111118

112119
exports.Table = class Table
113120
constructor: (@name, @alias=null, @win=null, @winFn=null, @winArg=null) -> null

‎test/grammar.spec.coffee‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,40 @@ describe "SQL Grammar", ->
1414
ORDER BY `x` DESC
1515
"""
1616

17+
it "parses ORDER BY clauses with OFFSET n ROWS", ->
18+
parse("SELECT * FROM my_table ORDER BY x DESC OFFSET 10 ROWS").toString().should.eql """
19+
SELECT *
20+
FROM `my_table`
21+
ORDER BY `x` DESC
22+
OFFSET 10 ROWS
23+
"""
24+
25+
it "parses ORDER BY clauses with OFFSET n ROW", ->
26+
parse("SELECT * FROM my_table ORDER BY x DESC OFFSET 10 ROW").toString().should.eql """
27+
SELECT *
28+
FROM `my_table`
29+
ORDER BY `x` DESC
30+
OFFSET 10 ROWS
31+
"""
32+
33+
it "parses ORDER BY clauses with OFFSET n ROW FETCH FIRST n ONLY", ->
34+
parse("SELECT * FROM my_table ORDER BY x DESC OFFSET 10 ROW FETCH FIRST 15 ROW ONLY").toString().should.eql """
35+
SELECT *
36+
FROM `my_table`
37+
ORDER BY `x` DESC
38+
OFFSET 10 ROWS
39+
FETCH NEXT 15 ROWS ONLY
40+
"""
41+
42+
it "parses ORDER BY clauses with OFFSET n ROW FETCH NEXT n ONLY", ->
43+
parse("SELECT * FROM my_table ORDER BY x DESC OFFSET 10 ROW FETCH NEXT 15 ROWS ONLY").toString().should.eql """
44+
SELECT *
45+
FROM `my_table`
46+
ORDER BY `x` DESC
47+
OFFSET 10 ROWS
48+
FETCH NEXT 15 ROWS ONLY
49+
"""
50+
1751
it "parses GROUP BY clauses", ->
1852
parse("SELECT * FROM my_table GROUP BY x, y").toString().should.eql """
1953
SELECT *
@@ -28,6 +62,30 @@ describe "SQL Grammar", ->
2862
LIMIT 10
2963
"""
3064

65+
it "parses LIMIT clauses after ORDER BY", ->
66+
parse("SELECT * FROM my_table ORDER BY cat DESC LIMIT 10").toString().should.eql """
67+
SELECT *
68+
FROM `my_table`
69+
ORDER BY `cat` DESC
70+
LIMIT 10
71+
"""
72+
73+
it "parses LIMIT clauses with comma separated offset", ->
74+
parse("SELECT * FROM my_table LIMIT 30, 10").toString().should.eql """
75+
SELECT *
76+
FROM `my_table`
77+
LIMIT 10
78+
OFFSET 30
79+
"""
80+
81+
it "parses LIMIT clauses with OFFSET keyword", ->
82+
parse("SELECT * FROM my_table LIMIT 10 OFFSET 30").toString().should.eql """
83+
SELECT *
84+
FROM `my_table`
85+
LIMIT 10
86+
OFFSET 30
87+
"""
88+
3189
it "parses SELECTs with FUNCTIONs", ->
3290
parse("SELECT a, COUNT(1, b) FROM my_table LIMIT 10").toString().should.eql """
3391
SELECT `a`, COUNT(1, `b`)

0 commit comments

Comments
 (0)