使用antlr parsetree解析SQL查询到Java中的mongo bson文档 [英] Parse sql query using antlr parsetree to mongo bson document in Java

问题描述

我有一个类似SQL的查询示例:

Select id,name from employee where age > 30 and department = 'IT' limit 200

SQL查询语法是在ANTLR4语法文件中定义的.是否有任何将查询查询的分析树转换为bson文档的实现?

然后，将使用bson文档查询mongo数据库.

解决方案

在我之前的工作中，我做了类似的事情:得到一个查询(不是sql，但是很相似)，并使用antlr将其翻译成mongo查询. /p>

我没有要分享的代码，但是我可以分享我的想法:

1. Mongo不兼容SQL，因此您不能仅采用SQL语法.那么JOIN和所有关系代数呢?那么在mongo中使用其聚合框架非常棘手的聚合又如何呢?相反，如何生成在mongo中转换为"exists"子句的SQL.有很多类似的东西，有些很小，有些很大，但是最重要的是，您必须谈论某种sql子集，有些DSL可以用作查询语言，并且看起来像"sql"，因为人们习惯于使用SQL.

2. 请牢记这一点，您应该创建自己的语法，Antlr会为您生成一个词法分析器/解析器.您还将理所当然地在运行时中对该查询进行语法检查.如果Antlr显然格式不正确，将无法解析查询，某些语法规则将失败.这是不按原样使用SQL的另一个原因.

3. 到目前为止，您已经创建了自己的侦听器/访问者.就我而言，我选择使用内部状态和所有内容创建查询的对象表示. 因此查询

Select id,name 
from employee 
where age > 30 
 and department = 'IT' 
limit 200

已转换为以下类型的对象

:

class Query {
   private SelectClause select;
   private FromClause  from;
   private WhereClause where;
   private Limit        limit;
}

class SelectClause {
   private List<String> fields;
}
...
class WhereClause {
   Condition root;
}

interface Condition {
...
}

class AndCondition implements Condition { // the same for Not, Or

}

对于此特定查询，它类似于:

Query q = new Query(new SelectClause(["id", "name"]), new FromClause("employee"), new WhereClause(new AndCondition(new SimpleLeafCondition("age", Operators.GT, 30), new  SimpleLeafCondition("department", Operators.EQ, "IT" )), new Limit(30));

然后可以在查询中进行一些优化(例如，如果需要，可以嵌入where子句，或者，例如，如果您在多租户环境中工作并且对不同的租户有不同的集合，则可以操纵"For"部分) .

毕竟，您可以使用设计模式解释器"，并递归地解析查询对象，并将其转换"为有效的mongo查询. 我记得这一步花了我大约1天的时间(我想是在7年前使用mongo 2，但仍然如此)，因为代表查询的对象结构正确，所以这应该没有那么复杂.我提出这个问题，是因为这似乎是您在该问题中的主要关注点.

I have a SQL like query example:

Select id,name from employee where age > 30 and department = 'IT' limit 200

The SQL query grammer is defined in an ANTLR4 grammar file. Is there any implementation that converts the parse tree of this query to a bson document?

The bson document will then be used to query a mongo db.

解决方案

In one of my previous jobs I did something similar: got a query (not an sql, but pretty similar) and translated it to mongo query with antlr.

I don't have a code to share, However I can share my thoughts:

1. Mongo is not SQL compliant, so you can't just take a sql grammar. What about JOINs and all the relational algebra? What about aggregations that are pretty tricky in mongo with their aggregation framework? In the opposite direction, how do you generate SQL that gets translated to "exists" clause in mongo. There are many things like this, some are small, some are huge, but bottom line you must be talking about some kind of subset of sql ,some DSL that is allowed to be used as a query language and looks "like" an sql because people are used to SQL.

2. With that in mind, you should create your own grammar and Antlr will generate a lexer/parser for you. You'll also get for granted a syntax check of the query in Runtime. Antlr won't be able to parse the query if its not in a correct format obviously, some grammar rule will fail. This is an another reason to not take SQL "as is".

3. So far so good, you've created your own listener / visitor. In my case I've opted for creating an object representation of the query with internal state and everything. So the query

Select id,name 
from employee 
where age > 30 
 and department = 'IT' 
limit 200

Was translated to objects of type:

class Query {
   private SelectClause select;
   private FromClause  from;
   private WhereClause where;
   private Limit        limit;
}

class SelectClause {
   private List<String> fields;
}
...
class WhereClause {
   Condition root;
}

interface Condition {
...
}

class AndCondition implements Condition { // the same for Not, Or

}

For this particular query its something like:

Query q = new Query(new SelectClause(["id", "name"]), new FromClause("employee"), new WhereClause(new AndCondition(new SimpleLeafCondition("age", Operators.GT, 30), new  SimpleLeafCondition("department", Operators.EQ, "IT" )), new Limit(30));

Then Its possible to make some optimizations in the query (like embedding of where clauses if you need, or, for example, manipulating the "For" part if you're working multi tenant environment and have different collections for different tenants).

After all you can go with design pattern "interpreter" and recursively parse the query objects and "translate" them to valid mongo query. I remember that this step took me something like 1 day to accomplish (it was 7 years ago with mongo 2 I guess, but still), given the correct structure of objects representing the query, so this should not be that complicated. I'm bringing this up, because It looks like its your primary concern in the question.

这篇关于使用antlr parsetree解析SQL查询到Java中的mongo bson文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！