Skip to content

Commit 305d12a

Browse files
TommyLemonTommyLemon
committed
新增支持 Elasticsearch, Presto, Trino, InfluxDB, MariaDB, KingBase
1 parent 65dce0e commit 305d12a

File tree

3 files changed

+154
-30
lines changed

3 files changed

+154
-30
lines changed

‎APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java‎

Lines changed: 119 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -154,9 +154,16 @@ public abstract class AbstractSQLConfig implements SQLConfig {
154154
DATABASE_LIST.add(DATABASE_SQLSERVER);
155155
DATABASE_LIST.add(DATABASE_ORACLE);
156156
DATABASE_LIST.add(DATABASE_DB2);
157+
DATABASE_LIST.add(DATABASE_MARIADB);
158+
DATABASE_LIST.add(DATABASE_TIDB);
157159
DATABASE_LIST.add(DATABASE_DAMENG);
160+
DATABASE_LIST.add(DATABASE_KINGBASE);
161+
DATABASE_LIST.add(DATABASE_ELASTICSEARCH);
158162
DATABASE_LIST.add(DATABASE_CLICKHOUSE);
159163
DATABASE_LIST.add(DATABASE_HIVE);
164+
DATABASE_LIST.add(DATABASE_PRESTO);
165+
DATABASE_LIST.add(DATABASE_TRINO);
166+
DATABASE_LIST.add(DATABASE_INFLUXDB);
160167
DATABASE_LIST.add(DATABASE_TDENGINE);
161168

162169

@@ -964,27 +971,31 @@ public boolean isMySQL() {
964971
public static boolean isMySQL(String db) {
965972
return DATABASE_MYSQL.equals(db);
966973
}
967-
@Override
974+
975+
@Override
968976
public boolean isPostgreSQL() {
969977
return isPostgreSQL(getSQLDatabase());
970978
}
971979
public static boolean isPostgreSQL(String db) {
972980
return DATABASE_POSTGRESQL.equals(db);
973981
}
982+
974983
@Override
975984
public boolean isSQLServer() {
976985
return isSQLServer(getSQLDatabase());
977986
}
978987
public static boolean isSQLServer(String db) {
979988
return DATABASE_SQLSERVER.equals(db);
980989
}
981-
@Override
990+
991+
@Override
982992
public boolean isOracle() {
983993
return isOracle(getSQLDatabase());
984994
}
985995
public static boolean isOracle(String db) {
986996
return DATABASE_ORACLE.equals(db);
987997
}
998+
988999
@Override
9891000
public boolean isDb2() {
9901001
return isDb2(getSQLDatabase());
@@ -993,20 +1004,54 @@ public static boolean isDb2(String db) {
9931004
return DATABASE_DB2.equals(db);
9941005
}
9951006

1007+
@Override
1008+
public boolean isMariaDB() {
1009+
return isMariaDB(getSQLDatabase());
1010+
}
1011+
public static boolean isMariaDB(String db) {
1012+
return DATABASE_MARIADB.equals(db);
1013+
}
1014+
1015+
@Override
1016+
public boolean isTiDB() {
1017+
return isTiDB(getSQLDatabase());
1018+
}
1019+
public static boolean isTiDB(String db) {
1020+
return DATABASE_TIDB.equals(db);
1021+
}
1022+
9961023
@Override
9971024
public boolean isDameng() {
9981025
return isDameng(getSQLDatabase());
9991026
}
10001027
public static boolean isDameng(String db) {
10011028
return DATABASE_DAMENG.equals(db);
10021029
}
1030+
1031+
@Override
1032+
public boolean isKingBase() {
1033+
return isKingBase(getSQLDatabase());
1034+
}
1035+
public static boolean isKingBase(String db) {
1036+
return DATABASE_KINGBASE.equals(db);
1037+
}
1038+
1039+
@Override
1040+
public boolean isElasticsearch() {
1041+
return isElasticsearch(getSQLDatabase());
1042+
}
1043+
public static boolean isElasticsearch(String db) {
1044+
return DATABASE_ELASTICSEARCH.equals(db);
1045+
}
1046+
10031047
@Override
10041048
public boolean isClickHouse() {
10051049
return isClickHouse(getSQLDatabase());
10061050
}
10071051
public static boolean isClickHouse(String db) {
10081052
return DATABASE_CLICKHOUSE.equals(db);
10091053
}
1054+
10101055
@Override
10111056
public boolean isHive() {
10121057
return isHive(getSQLDatabase());
@@ -1015,6 +1060,30 @@ public static boolean isHive(String db) {
10151060
return DATABASE_HIVE.equals(db);
10161061
}
10171062

1063+
@Override
1064+
public boolean isPresto() {
1065+
return isPresto(getSQLDatabase());
1066+
}
1067+
public static boolean isPresto(String db) {
1068+
return DATABASE_PRESTO.equals(db);
1069+
}
1070+
1071+
@Override
1072+
public boolean isTrino() {
1073+
return isTrino(getSQLDatabase());
1074+
}
1075+
public static boolean isTrino(String db) {
1076+
return DATABASE_TRINO.equals(db);
1077+
}
1078+
1079+
@Override
1080+
public boolean isInfluxDB() {
1081+
return isInfluxDB(getSQLDatabase());
1082+
}
1083+
public static boolean isInfluxDB(String db) {
1084+
return DATABASE_INFLUXDB.equals(db);
1085+
}
1086+
10181087
@Override
10191088
public boolean isTDengine() {
10201089
return isTDengine(getSQLDatabase());
@@ -1026,7 +1095,7 @@ public static boolean isTDengine(String db) {
10261095

10271096
@Override
10281097
public String getQuote() {
1029-
return isMySQL() || isClickHouse() || isTDengine() ? "`" : "\"";
1098+
return isMySQL() || isMariaDB() || isTiDB() || isClickHouse() || isTDengine() ? "`" : "\"";
10301099
}
10311100

10321101
@Override
@@ -2320,7 +2389,13 @@ public String getLimitString() {
23202389
if (count <= 0 || RequestMethod.isHeadMethod(getMethod(), true)) {
23212390
return "";
23222391
}
2323-
return getLimitString(getPage(), getCount(), isOracle() || isSQLServer() || isDb2(), isOracle());
2392+
return getLimitString(
2393+
getPage()
2394+
, getCount()
2395+
, isOracle() || isSQLServer() || isDb2()
2396+
, isOracle() || isDameng() || isKingBase()
2397+
, isPresto() || isTrino()
2398+
);
23242399
}
23252400
/**获取限制数量及偏移量
23262401
* @param page
@@ -2330,12 +2405,31 @@ public String getLimitString() {
23302405
* @return
23312406
*/
23322407
public static String getLimitString(int page, int count, boolean isTSQL, boolean isOracle) {
2408+
return getLimitString(page, count, isTSQL, isOracle, false);
2409+
}
2410+
/**获取限制数量及偏移量
2411+
* @param page
2412+
* @param count
2413+
* @param isTSQL
2414+
* @param isOracle
2415+
* @param isPresto
2416+
* @return
2417+
*/
2418+
public static String getLimitString(int page, int count, boolean isTSQL, boolean isOracle, boolean isPresto) {
23332419
int offset = getOffset(page, count);
23342420

2421+
if (isOracle) { // TODO 判断版本，高版本可以用 OFFSET FETCH
2422+
return " WHERE ROWNUM BETWEEN " + offset + " AND " + (offset + count);
2423+
}
2424+
23352425
if (isTSQL) { // OFFSET FECTH 中所有关键词都不可省略, 另外 Oracle 数据库使用子查询加 where 分页
2336-
return isOracle ? " WHERE ROWNUM BETWEEN "+ offset +" AND "+ (offset + count) : " OFFSET " + offset + " ROWS FETCH FIRST " + count + " ROWS ONLY";
2426+
return " OFFSET " + offset + " ROWS FETCH FIRST " + count + " ROWS ONLY";
23372427
}
23382428

2429+
if (isPresto) { // https://prestodb.io/docs/current/sql/select.html
2430+
return (offset <= 0 ? "" : " OFFSET " + offset) + " LIMIT " + count;
2431+
}
2432+
23392433
return " LIMIT " + count + (offset <= 0 ? "" : " OFFSET " + offset); // DELETE, UPDATE 不支持 OFFSET
23402434
}
23412435

@@ -3456,16 +3550,19 @@ public String getRegExpString(String key, String column, Object[] values, int ty
34563550
*/
34573551
@JSONField(serialize = false)
34583552
public String getRegExpString(String key, String column, String value, boolean ignoreCase) {
3459-
if (isPostgreSQL()) {
3553+
if (isPostgreSQL() || isInfluxDB()) {
34603554
return getKey(column) + " ~" + (ignoreCase ? "* " : " ") + getValue(key, column, value);
34613555
}
3462-
if (isOracle() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
3556+
if (isPresto() || isTrino() || isOracle() || isDameng() || isKingBase() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
34633557
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
34643558
}
34653559
if (isClickHouse()) {
34663560
return "match(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
34673561
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
34683562
}
3563+
if (isElasticsearch()) {
3564+
return getKey(column) + " RLIKE " + getValue(key, column, value);
3565+
}
34693566
if (isHive()) {
34703567
return (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
34713568
+ " REGEXP " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "");
@@ -3761,10 +3858,10 @@ public String getContainString(String key, String column, Object[] childs, int t
37613858
}
37623859

37633860
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR));
3764-
if (isPostgreSQL()) {
3861+
if (isPostgreSQL() || isInfluxDB()) {
37653862
condition += (getKey(column) + " @> " + getValue(key, column, newJSONArray(c))); //operator does not exist: jsonb @> character varying "[" + c + "]");
37663863
}
3767-
else if (isOracle()) {
3864+
else if (isOracle() || isDameng() || isKingBase()) {
37683865
condition += ("json_textcontains(" + getKey(column) + ", " + (StringUtil.isEmpty(path, true) ? "'$'" : getValue(key, column, path)) + ", " + getValue(key, column, c == null ? null : c.toString()) + ")");
37693866
}
37703867
else {
@@ -4025,15 +4122,15 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
40254122
}
40264123
return "DELETE FROM " + tablePath + config.getWhereString(true) + (config.isMySQL() ? config.getLimitString() : ""); // PostgreSQL 不允许 LIMIT
40274124
default:
4028-
String explain = config.isExplain() ? (config.isSQLServer() ? "SET STATISTICS PROFILE ON " : (config.isOracle() ? "EXPLAIN PLAN FOR " : "EXPLAIN ")) : "";
4125+
String explain = config.isExplain() ? (config.isSQLServer() ? "SET STATISTICS PROFILE ON " : (config.isOracle() || config.isDameng() || config.isKingBase() ? "EXPLAIN PLAN FOR " : "EXPLAIN ")) : "";
40294126
if (config.isTest() && RequestMethod.isGetMethod(config.getMethod(), true)) { // FIXME 为啥是 code 而不是 count ？
40304127
String q = config.getQuote(); // 生成 SELECT ( (24 >=0 AND 24 <3) ) AS `code` LIMIT 1 OFFSET 0
40314128
return explain + "SELECT " + config.getWhereString(false) + " AS " + q + JSONResponse.KEY_COUNT + q + config.getLimitString();
40324129
}
40334130

40344131
config.setPreparedValueList(new ArrayList<Object>());
40354132
String column = config.getColumnString();
4036-
if (config.isOracle()) {
4133+
if (config.isOracle() || config.isDameng() || config.isKingBase()) {
40374134
//When config's database is oracle,Using subquery since Oracle12 below does not support OFFSET FETCH paging syntax.
40384135
//针对oracle分组后条数的统计
40394136
if (StringUtil.isNotEmpty(config.getGroup(),true) && RequestMethod.isHeadMethod(config.getMethod(), true)){
@@ -4322,24 +4419,28 @@ else if (l > 0 && StringUtil.isName(String.valueOf(l))) {
43224419
}
43234420
else if (rt.endsWith("~")) {
43244421
boolean ignoreCase = "*~".equals(rt);
4325-
if (isPostgreSQL()) {
4422+
if (isPostgreSQL() || isInfluxDB()) {
43264423
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
43274424
+ " ~" + (ignoreCase ? "* " : " ") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
43284425
}
4329-
else if (isOracle()) {
4426+
else if (isPresto() || isTrino() || isOracle() || isDameng() || isKingBase()) {
43304427
sql += (first ? ON : AND) + "regexp_like(" + quote + jt + quote + "." + quote + on.getKey() + quote
43314428
+ ", " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
43324429
}
43334430
else if (isClickHouse()) {
43344431
sql += (first ? ON : AND) + "match(" + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
43354432
+ ", " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";
43364433
}
4434+
else if (isElasticsearch()) {
4435+
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4436+
+ " RLIKE " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4437+
}
43374438
else if (isHive()) {
4338-
sql += (first ? ON : AND) + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4439+
sql += (first ? ON : AND) + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
43394440
+ " REGEXP " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "");
4340-
}
4441+
}
43414442
else {
4342-
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4443+
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
43434444
+ " REGEXP " + (ignoreCase ? "" : "BINARY ") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
43444445
}
43454446
}
@@ -4380,11 +4481,11 @@ else if ("{}".equals(rt) || "<>".equals(rt)) {
43804481
itemKeyPath = quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
43814482
}
43824483

4383-
if (isPostgreSQL()) { //operator does not exist: jsonb @> character varying "[" + c + "]");
4484+
if (isPostgreSQL() || isInfluxDB()) { //operator does not exist: jsonb @> character varying "[" + c + "]");
43844485
sql += (first ? ON : AND) + (isNot ? "( " : "") + getCondition(isNot, arrKeyPath
43854486
+ " IS NOT NULL AND " + arrKeyPath + " @> " + itemKeyPath) + (isNot ? ") " : "");
43864487
}
4387-
else if (isOracle()) {
4488+
else if (isOracle() || isDameng() || isKingBase()) {
43884489
sql += (first ? ON : AND) + (isNot ? "( " : "") + getCondition(isNot, arrKeyPath
43894490
+ " IS NOT NULL AND json_textcontains(" + arrKeyPath
43904491
+ ", '$', " + itemKeyPath + ")") + (isNot ? ") " : "");

‎APIJSONORM/src/main/java/apijson/orm/AbstractVerifier.java‎

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,10 @@
6666
import apijson.orm.model.SysColumn;
6767
import apijson.orm.model.SysTable;
6868
import apijson.orm.model.Table;
69+
import apijson.orm.model.AllTable;
70+
import apijson.orm.model.AllColumn;
71+
import apijson.orm.model.AllTableComment;
72+
import apijson.orm.model.AllColumnComment;
6973
import apijson.orm.model.TestRecord;
7074

7175
/**校验器(权限、请求参数、返回结果等)
@@ -151,10 +155,14 @@ public abstract class AbstractVerifier<T extends Object> implements Verifier<T>,
151155

152156
if (Log.DEBUG) {
153157
SYSTEM_ACCESS_MAP.put(Table.class.getSimpleName(), getAccessMap(Table.class.getAnnotation(MethodAccess.class)));
154-
SYSTEM_ACCESS_MAP.put(Column.class.getSimpleName(), getAccessMap(Column.class.getAnnotation(MethodAccess.class)));
158+
SYSTEM_ACCESS_MAP.put(Column.class.getSimpleName(), getAccessMap(Column.class.getAnnotation(MethodAccess.class)));
155159
SYSTEM_ACCESS_MAP.put(PgAttribute.class.getSimpleName(), getAccessMap(PgAttribute.class.getAnnotation(MethodAccess.class)));
156-
SYSTEM_ACCESS_MAP.put(PgClass.class.getSimpleName(), getAccessMap(PgClass.class.getAnnotation(MethodAccess.class)));
157-
SYSTEM_ACCESS_MAP.put(SysTable.class.getSimpleName(), getAccessMap(SysTable.class.getAnnotation(MethodAccess.class)));
160+
SYSTEM_ACCESS_MAP.put(PgClass.class.getSimpleName(), getAccessMap(PgClass.class.getAnnotation(MethodAccess.class)));
161+
SYSTEM_ACCESS_MAP.put(AllTable.class.getSimpleName(), getAccessMap(AllTable.class.getAnnotation(MethodAccess.class)));
162+
SYSTEM_ACCESS_MAP.put(AllTableComment.class.getSimpleName(), getAccessMap(AllTableComment.class.getAnnotation(MethodAccess.class)));
163+
SYSTEM_ACCESS_MAP.put(AllColumn.class.getSimpleName(), getAccessMap(AllColumn.class.getAnnotation(MethodAccess.class)));
164+
SYSTEM_ACCESS_MAP.put(AllColumnComment.class.getSimpleName(), getAccessMap(AllColumnComment.class.getAnnotation(MethodAccess.class)));
165+
SYSTEM_ACCESS_MAP.put(SysTable.class.getSimpleName(), getAccessMap(SysTable.class.getAnnotation(MethodAccess.class)));
158166
SYSTEM_ACCESS_MAP.put(SysColumn.class.getSimpleName(), getAccessMap(SysColumn.class.getAnnotation(MethodAccess.class)));
159167
SYSTEM_ACCESS_MAP.put(ExtendedProperty.class.getSimpleName(), getAccessMap(ExtendedProperty.class.getAnnotation(MethodAccess.class)));
160168

‎APIJSONORM/src/main/java/apijson/orm/SQLConfig.java‎

Lines changed: 24 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -17,15 +17,23 @@
1717
*/
1818
public interface SQLConfig {
1919

20-
String DATABASE_MYSQL = "MYSQL";
21-
String DATABASE_POSTGRESQL = "POSTGRESQL";
22-
String DATABASE_SQLSERVER = "SQLSERVER";
23-
String DATABASE_ORACLE = "ORACLE";
24-
String DATABASE_DB2 = "DB2";
25-
String DATABASE_DAMENG = "DAMENG";
26-
String DATABASE_CLICKHOUSE = "CLICKHOUSE";
27-
String DATABASE_HIVE = "HIVE";
28-
String DATABASE_TDENGINE = "TDENGINE";
20+
String DATABASE_MYSQL = "MYSQL"; // https://www.mysql.com
21+
String DATABASE_POSTGRESQL = "POSTGRESQL"; // https://www.postgresql.org
22+
String DATABASE_SQLSERVER = "SQLSERVER"; // https://www.microsoft.com/en-us/sql-server
23+
String DATABASE_ORACLE = "ORACLE"; // https://www.oracle.com/database
24+
String DATABASE_DB2 = "DB2"; // https://www.ibm.com/products/db2
25+
String DATABASE_MARIADB = "MARIADB"; // https://mariadb.org
26+
String DATABASE_TIDB = "TIDB"; // https://www.pingcap.com/tidb
27+
String DATABASE_DAMENG = "DAMENG"; // https://www.dameng.com
28+
String DATABASE_KINGBASE = "KINGBASE"; // https://www.kingbase.com.cn
29+
String DATABASE_ELASTICSEARCH = "ELASTICSEARCH"; // https://www.elastic.co/guide/en/elasticsearch/reference/7.4/xpack-sql.html
30+
String DATABASE_CLICKHOUSE = "CLICKHOUSE"; // https://clickhouse.com
31+
String DATABASE_HIVE = "HIVE"; // https://hive.apache.org
32+
String DATABASE_PRESTO = "PRESTO"; // Facebook PrestoDB https://prestodb.io
33+
String DATABASE_TRINO = "TRINO"; // PrestoSQL https://trino.io
34+
String DATABASE_INFLUXDB = "INFLUXDB"; // https://www.influxdata.com/products/influxdb-overview
35+
String DATABASE_TDENGINE = "TDENGINE"; // https://tdengine.com
36+
2937

3038
String SCHEMA_INFORMATION = "information_schema"; //MySQL, PostgreSQL, SQL Server 都有的系统模式
3139
String SCHEMA_SYS = "sys"; //SQL Server 系统模式
@@ -41,9 +49,16 @@ public interface SQLConfig {
4149
boolean isSQLServer();
4250
boolean isOracle();
4351
boolean isDb2();
52+
boolean isMariaDB();
53+
boolean isTiDB();
4454
boolean isDameng();
55+
boolean isKingBase();
56+
boolean isElasticsearch();
4557
boolean isClickHouse();
4658
boolean isHive();
59+
boolean isPresto();
60+
boolean isTrino();
61+
boolean isInfluxDB();
4762
boolean isTDengine();
4863

4964

0 commit comments

Comments
 (0)