使用 antlr 分析树将 sql 查询解析为 Java 中的 mongo bson 文档 [英] Parse sql query using antlr parsetree to mongo bson document in Java
问题描述
我有一个类似 SQL 的查询示例:
I have a SQL like query example:
Select id,name from employee where age > 30 and department = 'IT' limit 200
SQL 查询语法在 ANTLR4 语法文件中定义.是否有任何实现将这个查询的解析树转换为 bson 文档?
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?
然后将使用 bson 文档来查询 mongo 数据库.
The bson document will then be used to query a mongo db.
推荐答案
在我之前的一项工作中，我做了类似的事情:得到一个查询(不是 sql，但非常相似)并使用 antlr 将其转换为 mongo 查询.
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:
Mongo 不兼容 SQL，因此您不能只采用 sql 语法.JOIN 和所有关系代数呢?在 mongo 中使用聚合框架非常棘手的聚合呢?在相反的方向，您如何生成在 mongo 中被翻译为存在"子句的 SQL.有很多这样的东西，有些很小，有些很大，但最重要的是你必须谈论某种 sql 的子集，一些 DSL 被允许用作查询语言并且看起来像"一个 sql 因为人们习惯了 SQL.
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.
考虑到这一点，您应该创建自己的语法，Antlr 将为您生成一个词法分析器/解析器.您还将在 Runtime 中获得理所当然的查询语法检查.如果查询的格式不正确，Antlr 将无法解析查询，某些语法规则将失败.这是不按原样"使用 SQL 的另一个原因.
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".
到目前为止一切顺利，您已经创建了自己的侦听器/访问者.就我而言，我选择使用内部状态和所有内容创建查询的对象表示.所以查询
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
被转换为对象类型:
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));
然后可以在查询中进行一些优化(例如，如果需要，可以嵌入 where 子句，或者，例如，如果您在多租户环境中工作并且为不同的租户有不同的集合，则操作For"部分).
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).
毕竟您可以使用设计模式解释器"并递归解析查询对象并将它们翻译"为有效的 mongo 查询.我记得这一步花了我大约 1 天的时间来完成(我猜是 7 年前使用 mongo 2，但仍然如此)，考虑到表示查询的对象的正确结构，所以这不应该那么复杂.我提出这个问题，因为它看起来像是你在问题中的主要关注点.
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 分析树将 sql 查询解析为 Java 中的 mongo bson 文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
