Skip to content

Commit 8027dbf

Browse files
manticore-projectsmanticore-projects
committed
feat: T-SQL FOR ... clause
- fixes #1800
1 parent 6d2b421 commit 8027dbf

9 files changed

Lines changed: 296 additions & 47 deletions

File tree

‎src/main/java/net/sf/jsqlparser/expression/TrimFunction.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -96,7 +96,7 @@ public void accept(ExpressionVisitor expressionVisitor) {
9696
expressionVisitor.visit(this);
9797
}
9898

99-
StringBuilder appendTo(StringBuilder builder) {
99+
public StringBuilder appendTo(StringBuilder builder) {
100100
builder.append("Trim(");
101101

102102
if (trimSpecification != null) {

‎src/main/java/net/sf/jsqlparser/parser/ASTNodeAccessImpl.java‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,4 +23,15 @@ public void setASTNode(SimpleNode node) {
2323
this.node = node;
2424
}
2525

26+
public StringBuilder appendTo(StringBuilder builder) {
27+
SimpleNode simpleNode = getASTNode();
28+
Token token = simpleNode.jjtGetFirstToken();
29+
Token lastToken = simpleNode.jjtGetLastToken();
30+
while (token.next != null && token.absoluteEnd <= lastToken.absoluteEnd) {
31+
builder.append(" ").append(token.image);
32+
token = token.next;
33+
}
34+
return builder;
35+
}
36+
2637
}

‎src/main/java/net/sf/jsqlparser/statement/select/ForClause.java‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
package net.sf.jsqlparser.statement.select;
2+
3+
import net.sf.jsqlparser.parser.ASTNodeAccessImpl;
4+
5+
public class ForClause extends ASTNodeAccessImpl {
6+
public enum ForOption {
7+
BROWSE, XML, JSON;
8+
9+
public static ForOption from(String option) {
10+
return Enum.valueOf(ForOption.class, option.toUpperCase());
11+
}
12+
}
13+
14+
private ForOption forOption;
15+
16+
public ForOption getForOption() {
17+
return forOption;
18+
}
19+
20+
public ForClause setForOption(String forOption) {
21+
this.forOption = ForOption.from(forOption);
22+
return this;
23+
}
24+
25+
@Override
26+
public String toString() {
27+
return appendTo(new StringBuilder()).toString();
28+
}
29+
}

‎src/main/java/net/sf/jsqlparser/statement/select/Select.java‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,9 @@ public abstract class Select extends ASTNodeAccessImpl implements Statement, Exp
3030
Fetch fetch;
3131
WithIsolation isolation;
3232
boolean oracleSiblings = false;
33+
34+
ForClause forClause = null;
35+
3336
List<OrderByElement> orderByElements;
3437

3538
public static String orderByToString(List<OrderByElement> orderByElements) {
@@ -154,6 +157,15 @@ public Select withOracleSiblings(boolean oracleSiblings) {
154157
return this;
155158
}
156159

160+
public ForClause getForClause() {
161+
return forClause;
162+
}
163+
164+
public Select setForClause(ForClause forClause) {
165+
this.forClause = forClause;
166+
return this;
167+
}
168+
157169
public List<OrderByElement> getOrderByElements() {
158170
return orderByElements;
159171
}
@@ -261,6 +273,10 @@ public StringBuilder appendTo(StringBuilder builder) {
261273

262274
appendSelectBodyTo(builder);
263275

276+
if (forClause != null) {
277+
forClause.appendTo(builder);
278+
}
279+
264280
builder.append(orderByToString(oracleSiblings, orderByElements));
265281

266282
if (limitBy != null) {

‎src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -820,12 +820,12 @@ public void visit(IntervalExpression intervalExpression) {
820820
if (intervalExpression.isUsingIntervalKeyword()) {
821821
buffer.append("INTERVAL ");
822822
}
823-
if (intervalExpression.getExpression()!=null) {
823+
if (intervalExpression.getExpression() != null) {
824824
intervalExpression.getExpression().accept(this);
825825
} else {
826826
buffer.append(intervalExpression.getParameter());
827827
}
828-
if (intervalExpression.getIntervalType()!=null) {
828+
if (intervalExpression.getIntervalType() != null) {
829829
buffer.append(" ").append(intervalExpression.getIntervalType());
830830
}
831831
}

‎src/main/java/net/sf/jsqlparser/util/deparser/SelectDeParser.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -272,6 +272,10 @@ public void visit(PlainSelect plainSelect) {
272272
buffer.append(" SKIP LOCKED");
273273
}
274274
}
275+
if (plainSelect.getForClause() != null) {
276+
plainSelect.getForClause().appendTo(buffer);
277+
}
278+
275279
if (plainSelect.getOrderByElements() != null) {
276280
new OrderByDeParser(expressionVisitor, buffer).deParse(plainSelect.isOracleSiblings(),
277281
plainSelect.getOrderByElements());

‎src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt‎

Lines changed: 138 additions & 43 deletions
Large diffs are not rendered by default.

‎src/test/java/net/sf/jsqlparser/statement/select/ForClauseTest.java‎

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
package net.sf.jsqlparser.statement.select;
2+
3+
import net.sf.jsqlparser.JSQLParserException;
4+
import net.sf.jsqlparser.test.TestUtils;
5+
import org.junit.jupiter.api.Test;
6+
7+
class ForClauseTest {
8+
9+
@Test
10+
void testForBrowse() throws JSQLParserException {
11+
String sqlStr = "SELECT * FROM table FOR BROWSE";
12+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
13+
}
14+
15+
@Test
16+
void testForXMLPath() throws JSQLParserException {
17+
String sqlStr =
18+
"SELECT * " +
19+
" FROM table " +
20+
" FOR XML PATH('something'), ROOT('trkseg'), TYPE, BINARY BASE64, ELEMENTS ABSENT ";
21+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
22+
}
23+
24+
@Test
25+
void testForXMLRaw() throws JSQLParserException {
26+
String sqlStr =
27+
"SELECT * " +
28+
" FROM table " +
29+
" FOR XML RAW('something'), ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA ";
30+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
31+
}
32+
33+
@Test
34+
void testForXMLAuto() throws JSQLParserException {
35+
String sqlStr =
36+
"SELECT * " +
37+
" FROM table " +
38+
" FOR XML AUTO, ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA ";
39+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
40+
}
41+
42+
@Test
43+
void testForXMLExplicit() throws JSQLParserException {
44+
String sqlStr =
45+
"SELECT * " +
46+
" FROM table " +
47+
" FOR XML EXPLICIT, ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA ";
48+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
49+
}
50+
51+
@Test
52+
void testForXML() throws JSQLParserException {
53+
String sqlStr =
54+
"SELECT * " +
55+
" FROM table " +
56+
" FOR XML EXPLICIT, ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA " +
57+
"UNION ALL " +
58+
"SELECT * " +
59+
" FROM table " +
60+
" FOR XML EXPLICIT, ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA " +
61+
"UNION ALL " +
62+
"SELECT * " +
63+
" FROM table " +
64+
" FOR XML AUTO, ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA " +
65+
"UNION ALL " +
66+
"SELECT * " +
67+
" FROM table " +
68+
" FOR XML RAW('something'), ROOT('trkseg'), TYPE, BINARY BASE64, XMLDATA ";
69+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
70+
}
71+
72+
@Test
73+
void testForJSON() throws JSQLParserException {
74+
String sqlStr =
75+
"SELECT * " +
76+
" FROM table " +
77+
" FOR JSON AUTO, ROOT('trkseg'), WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES "
78+
+
79+
"UNION ALL " +
80+
"SELECT * " +
81+
" FROM table " +
82+
" FOR JSON PATH, ROOT('trkseg'), INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER ";
83+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
84+
}
85+
86+
@Test
87+
void testIssue1800() throws JSQLParserException {
88+
String sqlStr =
89+
"SELECT (SELECT '1.0' AS '@Version', (SELECT 'Test' AS 'name', (SELECT (SELECT DISTINCT 51.64315 AS '@lat', 14.31709 AS '@lon' FOR XML PATH('trkpt'), TYPE) FOR XML PATH(''), ROOT('trkseg'), TYPE) FOR XML PATH('trk'), TYPE) FOR XML PATH('gpx'), TYPE) FOR XML PATH('')";
90+
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
91+
}
92+
93+
}

‎src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4213,7 +4213,8 @@ public void testSqlContainIsNullFunctionShouldBeParsed3() throws JSQLParserExcep
42134213
@Test
42144214
public void testForXmlPath() throws JSQLParserException {
42154215
assertSqlCanBeParsedAndDeparsed(
4216-
"SELECT '|' + person_name FROM person JOIN person_group ON person.person_id = person_group.person_id WHERE person_group.group_id = 1 FOR XML PATH('')");
4216+
"SELECT '|' + person_name FROM person JOIN person_group ON person.person_id = person_group.person_id WHERE person_group.group_id = 1 FOR XML PATH('')",
4217+
true);
42174218
}
42184219

42194220
// @Test

0 commit comments

Comments
 (0)