Skip to content

Commit 55dd4e7

Browse files
ansjsunansjsun
committed
support wildcard field and include exclude function for NLPchina#204 
1 parent 59aef95 commit 55dd4e7

File tree

5 files changed

+176
-110
lines changed

5 files changed

+176
-110
lines changed

‎src/main/java/org/nlpcn/es4sql/parse/FieldMaker.java

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -34,21 +34,20 @@ public static Field makeField(SQLExpr expr, String alias,String tableAlias) thro
3434

3535
String methodName = mExpr.getMethodName();
3636

37-
if(methodName.toLowerCase().equals("nested") ||methodName.toLowerCase().equals("reverse_nested") ){
37+
if(methodName.equalsIgnoreCase("nested") ||methodName.equalsIgnoreCase("reverse_nested") ){
3838
NestedType nestedType = new NestedType();
3939
if(nestedType.tryFillFromExpr(mExpr)){
4040
return handleIdentifier(nestedType, alias, tableAlias);
4141
}
42-
}
43-
if(methodName.toLowerCase().equals("children")){
42+
}else if(methodName.equalsIgnoreCase("children")){
4443
ChildrenType childrenType = new ChildrenType();
4544
if(childrenType.tryFillFromExpr(mExpr)){
4645
return handleIdentifier(childrenType, alias, tableAlias);
4746
}
48-
}
49-
else if (methodName.toLowerCase().equals("filter")){
47+
}else if (methodName.equalsIgnoreCase("filter")){
5048
return makeFilterMethodField(mExpr,alias);
5149
}
50+
5251
return makeMethodField(methodName, mExpr.getParameters(), null, alias);
5352
} else if (expr instanceof SQLAggregateExpr) {
5453
SQLAggregateExpr sExpr = (SQLAggregateExpr) expr;

‎src/main/java/org/nlpcn/es4sql/query/DefaultQueryAction.java

Lines changed: 78 additions & 75 deletions
Original file line numberDiff line numberDiff line change
@@ -20,8 +20,7 @@
2020
/**
2121
* Transform SQL query to standard Elasticsearch search query
2222
*/
23-
public class
24-
DefaultQueryAction extends QueryAction {
23+
public class DefaultQueryAction extends QueryAction {
2524

2625
private final Select select;
2726
private SearchRequestBuilder request;
@@ -42,122 +41,126 @@ public SqlElasticSearchRequestBuilder explain() throws SqlParseException {
4241
setSorts(select.getOrderBys());
4342
setLimit(select.getOffset(), select.getRowCount());
4443

45-
boolean usedScroll = useScrollIfNeeded(select.isOrderdSelect());
46-
if(!usedScroll){
47-
request.setSearchType(SearchType.DFS_QUERY_THEN_FETCH);
48-
}
49-
updateRequestWithIndexAndRoutingOptions(select, request);
50-
updateRequestWithHighlight(select, request);
44+
boolean usedScroll = useScrollIfNeeded(select.isOrderdSelect());
45+
if (!usedScroll) {
46+
request.setSearchType(SearchType.DFS_QUERY_THEN_FETCH);
47+
}
48+
updateRequestWithIndexAndRoutingOptions(select, request);
49+
updateRequestWithHighlight(select, request);
5150

52-
SqlElasticSearchRequestBuilder sqlElasticRequestBuilder = new SqlElasticSearchRequestBuilder(request);
51+
SqlElasticSearchRequestBuilder sqlElasticRequestBuilder = new SqlElasticSearchRequestBuilder(request);
5352

5453
return sqlElasticRequestBuilder;
5554
}
5655

57-
private boolean useScrollIfNeeded(boolean existsOrderBy) {
58-
Hint scrollHint = null;
59-
for(Hint hint: select.getHints()){
60-
if(hint.getType() == HintType.USE_SCROLL){
61-
scrollHint = hint;
62-
break;
63-
}
64-
}
65-
if(scrollHint!=null) {
66-
int scrollSize = (Integer) scrollHint.getParams()[0];
67-
int timeoutInMilli = (Integer) scrollHint.getParams()[1];
68-
if(!existsOrderBy) request.setSearchType(SearchType.SCAN);
69-
request.setScroll(new TimeValue(timeoutInMilli))
70-
.setSize(scrollSize);
71-
}
72-
return scrollHint !=null ;
73-
}
74-
75-
/**
56+
private boolean useScrollIfNeeded(boolean existsOrderBy) {
57+
Hint scrollHint = null;
58+
for (Hint hint : select.getHints()) {
59+
if (hint.getType() == HintType.USE_SCROLL) {
60+
scrollHint = hint;
61+
break;
62+
}
63+
}
64+
if (scrollHint != null) {
65+
int scrollSize = (Integer) scrollHint.getParams()[0];
66+
int timeoutInMilli = (Integer) scrollHint.getParams()[1];
67+
if (!existsOrderBy)
68+
request.setSearchType(SearchType.SCAN);
69+
request.setScroll(new TimeValue(timeoutInMilli)).setSize(scrollSize);
70+
}
71+
return scrollHint != null;
72+
}
73+
74+
/**
7675
* Set indices and types to the search request.
7776
*/
7877
private void setIndicesAndTypes() {
7978
request.setIndices(query.getIndexArr());
8079

8180
String[] typeArr = query.getTypeArr();
82-
if(typeArr != null) {
81+
if (typeArr != null) {
8382
request.setTypes(typeArr);
8483
}
8584
}
8685

87-
8886
/**
8987
* Set source filtering on a search request.
90-
* @param fields list of fields to source filter.
88+
*
89+
* @param fields
90+
* list of fields to source filter.
9191
*/
9292
private void setFields(List<Field> fields) throws SqlParseException {
9393
if (select.getFields().size() > 0) {
9494
ArrayList<String> includeFields = new ArrayList<String>();
95+
ArrayList<String> excludeFields = new ArrayList<String>();
9596

9697
for (Field field : fields) {
97-
if(field instanceof MethodField){
98-
handleMethodField((MethodField) field);
99-
}
100-
else if (field instanceof Field) {
98+
if (field instanceof MethodField) {
99+
MethodField method = (MethodField) field;
100+
if (method.getName().toLowerCase().equals("script")) {
101+
handleScriptField(method);
102+
} else if (method.getName().equalsIgnoreCase("include")) {
103+
for (KVValue kvValue : method.getParams()) {
104+
includeFields.add(kvValue.value.toString()) ;
105+
}
106+
} else if (method.getName().equalsIgnoreCase("exclude")) {
107+
for (KVValue kvValue : method.getParams()) {
108+
excludeFields.add(kvValue.value.toString()) ;
109+
}
110+
}
111+
} else if (field instanceof Field) {
101112
includeFields.add(field.getName());
102113
}
103114
}
104115

105-
request.setFetchSource(includeFields.toArray(new String[includeFields.size()]), null);
116+
request.setFetchSource(includeFields.toArray(new String[includeFields.size()]), excludeFields.toArray(new String[excludeFields.size()]));
106117
}
107118
}
108119

109-
private void handleMethodField(MethodField field) throws SqlParseException {
110-
MethodField method = (MethodField) field;
111-
if(method.getName().toLowerCase().equals("script")){
112-
handleScriptField(method);
113-
}
114-
}
115-
116-
private void handleScriptField(MethodField method) throws SqlParseException {
117-
List<KVValue> params = method.getParams();
118-
if(params.size() == 2){
119-
request.addScriptField(params.get(0).value.toString(),new Script(params.get(1).value.toString()));
120-
}
121-
else if(params.size() == 3){
122-
request.addScriptField(params.get(0).value.toString(),new Script(params.get(1).value.toString(), ScriptService.ScriptType.INLINE,params.get(2).value.toString(),null));
123-
}
124-
else {
125-
throw new SqlParseException("scripted_field only allows script(name,script) or script(name,lang,script)");
126-
}
127-
}
128-
129-
130-
/**
131-
* Create filters or queries based on
132-
* the Where clause.
133-
* @param where the 'WHERE' part of the SQL query.
120+
private void handleScriptField(MethodField method) throws SqlParseException {
121+
List<KVValue> params = method.getParams();
122+
if (params.size() == 2) {
123+
request.addScriptField(params.get(0).value.toString(), new Script(params.get(1).value.toString()));
124+
} else if (params.size() == 3) {
125+
request.addScriptField(params.get(0).value.toString(), new Script(params.get(1).value.toString(), ScriptService.ScriptType.INLINE, params.get(2).value.toString(), null));
126+
} else {
127+
throw new SqlParseException("scripted_field only allows script(name,script) or script(name,lang,script)");
128+
}
129+
}
130+
131+
/**
132+
* Create filters or queries based on the Where clause.
133+
*
134+
* @param where
135+
* the 'WHERE' part of the SQL query.
134136
* @throws SqlParseException
135137
*/
136138
private void setWhere(Where where) throws SqlParseException {
137139
if (where != null) {
138-
BoolQueryBuilder boolQuery = QueryMaker.explan(where);
139-
request.setQuery(boolQuery);
140+
BoolQueryBuilder boolQuery = QueryMaker.explan(where);
141+
request.setQuery(boolQuery);
140142
}
141143
}
142144

143-
144145
/**
145-
* Add sorts to the elasticsearch query
146-
* based on the 'ORDER BY' clause.
147-
* @param orderBys list of Order object
146+
* Add sorts to the elasticsearch query based on the 'ORDER BY' clause.
147+
*
148+
* @param orderBys
149+
* list of Order object
148150
*/
149151
private void setSorts(List<Order> orderBys) {
150152
for (Order order : orderBys) {
151153
request.addSort(order.getName(), SortOrder.valueOf(order.getType()));
152154
}
153155
}
154156

155-
156157
/**
157-
* Add from and size to the ES query
158-
* based on the 'LIMIT' clause
159-
* @param from starts from document at position from
160-
* @param size number of documents to return.
158+
* Add from and size to the ES query based on the 'LIMIT' clause
159+
*
160+
* @param from
161+
* starts from document at position from
162+
* @param size
163+
* number of documents to return.
161164
*/
162165
private void setLimit(int from, int size) {
163166
request.setFrom(from);
@@ -167,7 +170,7 @@ private void setLimit(int from, int size) {
167170
}
168171
}
169172

170-
public SearchRequestBuilder getRequestBuilder() {
171-
return request;
172-
}
173+
public SearchRequestBuilder getRequestBuilder() {
174+
return request;
175+
}
173176
}

‎src/test/java/org/nlpcn/es4sql/MainTestSuite.java

Lines changed: 30 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,8 @@
3535
WktToGeoJsonConverterTests.class,
3636
SqlParserTests.class,
3737
ShowTest.class,
38-
CSVResultsExtractorTests.class
38+
CSVResultsExtractorTests.class,
39+
SourceFieldTest.class
3940
})
4041
public class MainTestSuite {
4142

@@ -53,34 +54,34 @@ public static void setUp() throws Exception {
5354
String clusterName = nodeInfos.getClusterName().value();
5455
System.out.println(String.format("Found cluster... cluster name: %s", clusterName));
5556

56-
// Load test data.
57-
if(client.admin().indices().prepareExists(TEST_INDEX).execute().actionGet().isExists()){
58-
client.admin().indices().prepareDelete(TEST_INDEX).get();
59-
}
60-
loadBulk("src/test/resources/accounts.json");
61-
loadBulk("src/test/resources/online.json");
62-
preparePhrasesIndex();
63-
loadBulk("src/test/resources/phrases.json");
64-
loadBulk("src/test/resources/dogs.json");
65-
loadBulk("src/test/resources/peoples.json");
66-
loadBulk("src/test/resources/game_of_thrones_complex.json");
67-
68-
prepareOdbcIndex();
69-
loadBulk("src/test/resources/odbc-date-formats.json");
70-
71-
prepareSpatialIndex("location");
72-
loadBulk("src/test/resources/locations.json");
73-
74-
prepareSpatialIndex("location2");
75-
loadBulk("src/test/resources/locations2.json");
76-
77-
prepareNestedTypeIndex();
78-
loadBulk("src/test/resources/nested_objects.json");
79-
80-
prepareChildrenTypeIndex();
81-
prepareParentTypeIndex();
82-
loadBulk("src/test/resources/parent_objects.json");
83-
loadBulk("src/test/resources/children_objects.json");
57+
// // Load test data.
58+
// if(client.admin().indices().prepareExists(TEST_INDEX).execute().actionGet().isExists()){
59+
// client.admin().indices().prepareDelete(TEST_INDEX).get();
60+
// }
61+
// loadBulk("src/test/resources/accounts.json");
62+
// loadBulk("src/test/resources/online.json");
63+
// preparePhrasesIndex();
64+
// loadBulk("src/test/resources/phrases.json");
65+
// loadBulk("src/test/resources/dogs.json");
66+
// loadBulk("src/test/resources/peoples.json");
67+
// loadBulk("src/test/resources/game_of_thrones_complex.json");
68+
//
69+
// prepareOdbcIndex();
70+
// loadBulk("src/test/resources/odbc-date-formats.json");
71+
//
72+
// prepareSpatialIndex("location");
73+
// loadBulk("src/test/resources/locations.json");
74+
//
75+
// prepareSpatialIndex("location2");
76+
// loadBulk("src/test/resources/locations2.json");
77+
//
78+
// prepareNestedTypeIndex();
79+
// loadBulk("src/test/resources/nested_objects.json");
80+
//
81+
// prepareChildrenTypeIndex();
82+
// prepareParentTypeIndex();
83+
// loadBulk("src/test/resources/parent_objects.json");
84+
// loadBulk("src/test/resources/children_objects.json");
8485

8586
searchDao = new SearchDao(client);
8687

‎src/test/java/org/nlpcn/es4sql/ShowTest.java

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,5 @@ private GetIndexResponse runShowQuery(String query) throws SqlParseException, SQ
6464
SearchDao searchDao = MainTestSuite.getSearchDao();
6565
SqlElasticRequestBuilder requestBuilder = searchDao.explain(query).explain();
6666
return (GetIndexResponse) requestBuilder.get();
67-
6867
}
6968
}

‎src/test/java/org/nlpcn/es4sql/SourceFieldTest.java

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,64 @@
1+
package org.nlpcn.es4sql;
2+
3+
import static org.nlpcn.es4sql.TestsConstants.TEST_INDEX;
4+
5+
import java.io.IOException;
6+
import java.sql.SQLFeatureNotSupportedException;
7+
import java.util.Set;
8+
9+
import org.elasticsearch.action.search.SearchResponse;
10+
import org.elasticsearch.search.SearchHit;
11+
import org.elasticsearch.search.SearchHits;
12+
import org.junit.Assert;
13+
import org.junit.Test;
14+
import org.nlpcn.es4sql.exception.SqlParseException;
15+
import org.nlpcn.es4sql.query.SqlElasticSearchRequestBuilder;
16+
17+
public class SourceFieldTest {
18+
19+
@Test
20+
public void includeTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
21+
SearchHits response = query(String.format("SELECT include('*name','*ge'),include('b*'),include('*ddre*'),include('gender') FROM %s/account LIMIT 1000", TEST_INDEX));
22+
for (SearchHit hit : response.getHits()) {
23+
Set<String> keySet = hit.getSource().keySet();
24+
for (String field : keySet) {
25+
Assert.assertTrue(field.endsWith("name") || field.endsWith("ge") || field.startsWith("b") || field.contains("ddre") || field.equals("gender"));
26+
}
27+
}
28+
29+
}
30+
31+
@Test
32+
public void excludeTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
33+
34+
SearchHits response = query(String.format("SELECT exclude('*name','*ge'),exclude('b*'),exclude('*ddre*'),exclude('gender') FROM %s/account LIMIT 1000", TEST_INDEX));
35+
36+
for (SearchHit hit : response.getHits()) {
37+
Set<String> keySet = hit.getSource().keySet();
38+
for (String field : keySet) {
39+
Assert.assertFalse(field.endsWith("name") || field.endsWith("ge") || field.startsWith("b") || field.contains("ddre") || field.equals("gender"));
40+
}
41+
}
42+
}
43+
44+
@Test
45+
public void allTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
46+
47+
SearchHits response = query(String.format("SELECT exclude('*name','*ge'),include('b*'),exclude('*ddre*'),include('gender') FROM %s/account LIMIT 1000", TEST_INDEX));
48+
49+
for (SearchHit hit : response.getHits()) {
50+
Set<String> keySet = hit.getSource().keySet();
51+
for (String field : keySet) {
52+
Assert.assertFalse(field.endsWith("name") || field.endsWith("ge") || field.contains("ddre") );
53+
Assert.assertTrue(field.startsWith("b") || field.equals("gender"));
54+
}
55+
}
56+
}
57+
58+
private SearchHits query(String query) throws SqlParseException, SQLFeatureNotSupportedException, SQLFeatureNotSupportedException {
59+
SearchDao searchDao = MainTestSuite.getSearchDao();
60+
SqlElasticSearchRequestBuilder select = (SqlElasticSearchRequestBuilder) searchDao.explain(query).explain();
61+
return ((SearchResponse) select.get()).getHits();
62+
}
63+
64+
}

0 commit comments

Comments
 (0)