use of com.alibaba.druid.sql.ast.expr.SQLBinaryOperator in project Mycat-Server by MyCATApache.
the class DruidSelectDb2Parser method parseNativePageSql.
protected void parseNativePageSql(SQLStatement stmt, RouteResultset rrs, OracleSelectQueryBlock mysqlSelectQuery, SchemaConfig schema) {
//第一层子查询
SQLExpr where = mysqlSelectQuery.getWhere();
SQLTableSource from = mysqlSelectQuery.getFrom();
if (where instanceof SQLBinaryOpExpr && from instanceof SQLSubqueryTableSource) {
SQLBinaryOpExpr one = (SQLBinaryOpExpr) where;
SQLExpr left = one.getLeft();
SQLBinaryOperator operator = one.getOperator();
SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
SQLOrderBy orderBy = null;
if (subSelect instanceof OracleSelectQueryBlock) {
boolean hasRowNumber = false;
OracleSelectQueryBlock subSelectOracle = (OracleSelectQueryBlock) subSelect;
List<SQLSelectItem> sqlSelectItems = subSelectOracle.getSelectList();
for (SQLSelectItem sqlSelectItem : sqlSelectItems) {
SQLExpr sqlExpr = sqlSelectItem.getExpr();
if (sqlExpr instanceof SQLAggregateExpr) {
SQLAggregateExpr agg = (SQLAggregateExpr) sqlExpr;
if ("row_number".equalsIgnoreCase(agg.getMethodName()) && agg.getOver() != null) {
hasRowNumber = true;
orderBy = agg.getOver().getOrderBy();
}
}
}
if (hasRowNumber) {
if ((operator == SQLBinaryOperator.LessThan || operator == SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
int firstrownum = right.getNumber().intValue();
if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
if (subSelect instanceof OracleSelectQueryBlock) {
rrs.setLimitStart(0);
rrs.setLimitSize(firstrownum);
//为了继续解出order by 等
mysqlSelectQuery = (OracleSelectQueryBlock) subSelect;
if (orderBy != null) {
OracleSelect oracleSelect = (OracleSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
isNeedParseOrderAgg = false;
}
} else if (operator == SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr && one.getRight() instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr leftE = (SQLBinaryOpExpr) left;
SQLBinaryOpExpr rightE = (SQLBinaryOpExpr) one.getRight();
SQLBinaryOpExpr small = null;
SQLBinaryOpExpr larger = null;
int firstrownum = 0;
int lastrownum = 0;
if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.GreaterThan || leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
small = leftE;
firstrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
if (leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
} else if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.LessThan || leftE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
larger = leftE;
lastrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
if (leftE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
lastrownum = lastrownum - 1;
}
}
if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.GreaterThan || rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
small = rightE;
firstrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
if (rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
} else if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.LessThan || rightE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
larger = rightE;
lastrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
if (rightE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
lastrownum = lastrownum - 1;
}
}
if (small != null && larger != null) {
setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum);
if (orderBy != null) {
OracleSelect oracleSelect = (OracleSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupOracle(stmt, rrs, (OracleSelectQueryBlock) subSelect, schema);
isNeedParseOrderAgg = false;
}
}
} else {
parseNativeSql(stmt, rrs, mysqlSelectQuery, schema);
}
}
} else {
parseNativeSql(stmt, rrs, mysqlSelectQuery, schema);
}
if (isNeedParseOrderAgg) {
parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
}
}
use of com.alibaba.druid.sql.ast.expr.SQLBinaryOperator in project Mycat-Server by MyCATApache.
the class DruidSelectOracleParser method parseNativePageSql.
protected void parseNativePageSql(SQLStatement stmt, RouteResultset rrs, OracleSelectQueryBlock mysqlSelectQuery, SchemaConfig schema) {
//第一层子查询
SQLExpr where = mysqlSelectQuery.getWhere();
SQLTableSource from = mysqlSelectQuery.getFrom();
if (where instanceof SQLBinaryOpExpr && from instanceof SQLSubqueryTableSource) {
SQLBinaryOpExpr one = (SQLBinaryOpExpr) where;
SQLExpr left = one.getLeft();
SQLBinaryOperator operator = one.getOperator();
//解析只有一层rownum限制大小
if (one.getRight() instanceof SQLIntegerExpr && "rownum".equalsIgnoreCase(left.toString()) && (operator == SQLBinaryOperator.LessThanOrEqual || operator == SQLBinaryOperator.LessThan)) {
SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
int firstrownum = right.getNumber().intValue();
if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
if (subSelect instanceof OracleSelectQueryBlock) {
rrs.setLimitStart(0);
rrs.setLimitSize(firstrownum);
//为了继续解出order by 等
mysqlSelectQuery = (OracleSelectQueryBlock) subSelect;
parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
isNeedParseOrderAgg = false;
}
} else //解析oracle三层嵌套分页
if (one.getRight() instanceof SQLIntegerExpr && !"rownum".equalsIgnoreCase(left.toString()) && (operator == SQLBinaryOperator.GreaterThan || operator == SQLBinaryOperator.GreaterThanOrEqual)) {
parseThreeLevelPageSql(stmt, rrs, schema, (SQLSubqueryTableSource) from, one, operator);
} else //解析oracle rownumber over分页
{
SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
SQLOrderBy orderBy = null;
if (subSelect instanceof OracleSelectQueryBlock) {
boolean hasRowNumber = false;
OracleSelectQueryBlock subSelectOracle = (OracleSelectQueryBlock) subSelect;
List<SQLSelectItem> sqlSelectItems = subSelectOracle.getSelectList();
for (SQLSelectItem sqlSelectItem : sqlSelectItems) {
SQLExpr sqlExpr = sqlSelectItem.getExpr();
if (sqlExpr instanceof SQLAggregateExpr) {
SQLAggregateExpr agg = (SQLAggregateExpr) sqlExpr;
if ("row_number".equalsIgnoreCase(agg.getMethodName()) && agg.getOver() != null) {
hasRowNumber = true;
orderBy = agg.getOver().getOrderBy();
}
}
}
if (hasRowNumber) {
if ((operator == SQLBinaryOperator.LessThan || operator == SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
int firstrownum = right.getNumber().intValue();
if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
if (subSelect instanceof OracleSelectQueryBlock) {
rrs.setLimitStart(0);
rrs.setLimitSize(firstrownum);
mysqlSelectQuery = (OracleSelectQueryBlock) subSelect;
if (orderBy != null) {
OracleSelect oracleSelect = (OracleSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
isNeedParseOrderAgg = false;
}
} else if (operator == SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr && one.getRight() instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr leftE = (SQLBinaryOpExpr) left;
SQLBinaryOpExpr rightE = (SQLBinaryOpExpr) one.getRight();
SQLBinaryOpExpr small = null;
SQLBinaryOpExpr larger = null;
int firstrownum = 0;
int lastrownum = 0;
if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.GreaterThan || leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
small = leftE;
firstrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
if (leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
} else if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.LessThan || leftE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
larger = leftE;
lastrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
if (leftE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
lastrownum = lastrownum - 1;
}
}
if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.GreaterThan || rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
small = rightE;
firstrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
if (rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
} else if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.LessThan || rightE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
larger = rightE;
lastrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
if (rightE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
lastrownum = lastrownum - 1;
}
}
if (small != null && larger != null) {
setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum);
if (orderBy != null) {
OracleSelect oracleSelect = (OracleSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupOracle(stmt, rrs, (OracleSelectQueryBlock) subSelect, schema);
isNeedParseOrderAgg = false;
}
}
} else {
parseNativeSql(stmt, rrs, mysqlSelectQuery, schema);
}
}
}
} else {
parseNativeSql(stmt, rrs, mysqlSelectQuery, schema);
}
if (isNeedParseOrderAgg) {
parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
}
}
use of com.alibaba.druid.sql.ast.expr.SQLBinaryOperator in project Mycat-Server by MyCATApache.
the class DruidSelectSqlServerParser method parseSqlServerPageSql.
private void parseSqlServerPageSql(SQLStatement stmt, RouteResultset rrs, SQLServerSelectQueryBlock sqlserverSelectQuery, SchemaConfig schema) {
//第一层子查询
SQLExpr where = sqlserverSelectQuery.getWhere();
SQLTableSource from = sqlserverSelectQuery.getFrom();
if (sqlserverSelectQuery.getTop() != null) {
SQLServerTop top = sqlserverSelectQuery.getTop();
SQLExpr sqlExpr = top.getExpr();
if (sqlExpr instanceof SQLIntegerExpr) {
int topValue = ((SQLIntegerExpr) sqlExpr).getNumber().intValue();
rrs.setLimitStart(0);
rrs.setLimitSize(topValue);
}
} else if (where instanceof SQLBinaryOpExpr && from instanceof SQLSubqueryTableSource) {
SQLBinaryOpExpr one = (SQLBinaryOpExpr) where;
SQLExpr left = one.getLeft();
SQLBinaryOperator operator = one.getOperator();
SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
SQLOrderBy orderBy = null;
if (subSelect instanceof SQLServerSelectQueryBlock) {
boolean hasRowNumber = false;
boolean hasSubTop = false;
int subTop = 0;
SQLServerSelectQueryBlock subSelectOracle = (SQLServerSelectQueryBlock) subSelect;
List<SQLSelectItem> sqlSelectItems = subSelectOracle.getSelectList();
for (SQLSelectItem sqlSelectItem : sqlSelectItems) {
SQLExpr sqlExpr = sqlSelectItem.getExpr();
if (sqlExpr instanceof SQLAggregateExpr) {
SQLAggregateExpr agg = (SQLAggregateExpr) sqlExpr;
if ("row_number".equalsIgnoreCase(agg.getMethodName()) && agg.getOver() != null) {
hasRowNumber = true;
orderBy = agg.getOver().getOrderBy();
}
}
}
if (subSelectOracle.getFrom() instanceof SQLSubqueryTableSource) {
SQLSubqueryTableSource subFrom = (SQLSubqueryTableSource) subSelectOracle.getFrom();
if (subFrom.getSelect().getQuery() instanceof SQLServerSelectQueryBlock) {
SQLServerSelectQueryBlock sqlSelectQuery = (SQLServerSelectQueryBlock) subFrom.getSelect().getQuery();
if (sqlSelectQuery.getTop() != null) {
SQLExpr sqlExpr = sqlSelectQuery.getTop().getExpr();
if (sqlExpr instanceof SQLIntegerExpr) {
hasSubTop = true;
subTop = ((SQLIntegerExpr) sqlExpr).getNumber().intValue();
orderBy = subFrom.getSelect().getOrderBy();
}
}
}
}
if (hasRowNumber) {
if (hasSubTop && (operator == SQLBinaryOperator.GreaterThan || operator == SQLBinaryOperator.GreaterThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
int firstrownum = right.getNumber().intValue();
if (operator == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
int lastrownum = subTop;
setLimitIFChange(stmt, rrs, schema, one, firstrownum, lastrownum);
if (orderBy != null) {
SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupSqlServer(schema, stmt, rrs, (SQLServerSelectQueryBlock) subSelect);
isNeedParseOrderAgg = false;
} else if ((operator == SQLBinaryOperator.LessThan || operator == SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
int firstrownum = right.getNumber().intValue();
if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
if (subSelect instanceof SQLServerSelectQueryBlock) {
rrs.setLimitStart(0);
rrs.setLimitSize(firstrownum);
//为了继续解出order by 等
sqlserverSelectQuery = (SQLServerSelectQueryBlock) subSelect;
if (orderBy != null) {
SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupSqlServer(schema, stmt, rrs, sqlserverSelectQuery);
isNeedParseOrderAgg = false;
}
} else if (operator == SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr && one.getRight() instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr leftE = (SQLBinaryOpExpr) left;
SQLBinaryOpExpr rightE = (SQLBinaryOpExpr) one.getRight();
SQLBinaryOpExpr small = null;
SQLBinaryOpExpr larger = null;
int firstrownum = 0;
int lastrownum = 0;
if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.GreaterThan || leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
small = leftE;
firstrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
if (leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
} else if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.LessThan || leftE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
larger = leftE;
lastrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
if (leftE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
lastrownum = lastrownum - 1;
}
}
if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.GreaterThan || rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
small = rightE;
firstrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
if (rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
firstrownum = firstrownum - 1;
}
} else if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.LessThan || rightE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
larger = rightE;
lastrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
if (rightE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
lastrownum = lastrownum - 1;
}
}
if (small != null && larger != null) {
setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum);
if (orderBy != null) {
SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
oracleSelect.setOrderBy(orderBy);
}
parseOrderAggGroupSqlServer(schema, stmt, rrs, (SQLServerSelectQueryBlock) subSelect);
isNeedParseOrderAgg = false;
}
}
}
}
}
}
use of com.alibaba.druid.sql.ast.expr.SQLBinaryOperator in project druid by alibaba.
the class OracleToMySqlOutputVisitor method visit.
public boolean visit(OracleSelectQueryBlock x) {
boolean parentIsSelectStatment = false;
{
if (x.getParent() instanceof SQLSelect) {
SQLSelect select = (SQLSelect) x.getParent();
if (select.getParent() instanceof SQLSelectStatement || select.getParent() instanceof SQLSubqueryTableSource) {
parentIsSelectStatment = true;
}
}
}
if (!parentIsSelectStatment) {
return super.visit(x);
}
if (//
x.getWhere() instanceof SQLBinaryOpExpr && //
x.getFrom() instanceof SQLSubqueryTableSource) {
int rownum;
String ident;
SQLBinaryOpExpr where = (SQLBinaryOpExpr) x.getWhere();
if (where.getRight() instanceof SQLIntegerExpr && where.getLeft() instanceof SQLIdentifierExpr) {
rownum = ((SQLIntegerExpr) where.getRight()).getNumber().intValue();
ident = ((SQLIdentifierExpr) where.getLeft()).getName();
} else {
return super.visit(x);
}
SQLSelect select = ((SQLSubqueryTableSource) x.getFrom()).getSelect();
SQLSelectQueryBlock queryBlock = null;
SQLSelect subSelect = null;
SQLBinaryOpExpr subWhere = null;
boolean isSubQueryRowNumMapping = false;
if (select.getQuery() instanceof SQLSelectQueryBlock) {
queryBlock = (SQLSelectQueryBlock) select.getQuery();
if (queryBlock.getWhere() instanceof SQLBinaryOpExpr) {
subWhere = (SQLBinaryOpExpr) queryBlock.getWhere();
}
for (SQLSelectItem selectItem : queryBlock.getSelectList()) {
if (isRowNumber(selectItem.getExpr())) {
if (where.getLeft() instanceof SQLIdentifierExpr && ((SQLIdentifierExpr) where.getLeft()).getName().equals(selectItem.getAlias())) {
isSubQueryRowNumMapping = true;
}
}
}
SQLTableSource subTableSource = queryBlock.getFrom();
if (subTableSource instanceof SQLSubqueryTableSource) {
subSelect = ((SQLSubqueryTableSource) subTableSource).getSelect();
}
}
if ("ROWNUM".equalsIgnoreCase(ident)) {
SQLBinaryOperator op = where.getOperator();
Integer limit = null;
if (op == SQLBinaryOperator.LessThanOrEqual) {
limit = rownum;
} else if (op == SQLBinaryOperator.LessThan) {
limit = rownum - 1;
}
if (limit != null) {
select.accept(this);
println();
print0(ucase ? "LIMIT " : "limit ");
print(limit);
return false;
}
} else if (isSubQueryRowNumMapping) {
SQLBinaryOperator op = where.getOperator();
SQLBinaryOperator subOp = subWhere.getOperator();
if (//
isRowNumber(subWhere.getLeft()) && subWhere.getRight() instanceof SQLIntegerExpr) {
int subRownum = ((SQLIntegerExpr) subWhere.getRight()).getNumber().intValue();
Integer offset = null;
if (op == SQLBinaryOperator.GreaterThanOrEqual) {
offset = rownum + 1;
} else if (op == SQLBinaryOperator.GreaterThan) {
offset = rownum;
}
if (offset != null) {
Integer limit = null;
if (subOp == SQLBinaryOperator.LessThanOrEqual) {
limit = subRownum - offset;
} else if (subOp == SQLBinaryOperator.LessThan) {
limit = subRownum - 1 - offset;
}
if (limit != null) {
subSelect.accept(this);
println();
print0(ucase ? "LIMIT " : "limit ");
print(offset);
print0(", ");
print(limit);
return false;
}
}
}
}
}
return super.visit(x);
}
Aggregations