package com.smtaiserver.smtaiserver.javaai.qwen.sqltree;
|
|
import java.util.Collection;
|
import java.util.HashMap;
|
import java.util.HashSet;
|
import java.util.LinkedHashMap;
|
import java.util.Map;
|
import java.util.Map.Entry;
|
import java.util.Set;
|
|
import com.smtaiserver.smtaiserver.core.SMTAIServerRequest;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecord;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecords;
|
import com.smtaiserver.smtaiserver.javaai.SMTJavaAIError;
|
import com.smtservlet.util.Json;
|
import com.smtservlet.util.SMTJsonWriter;
|
import com.smtservlet.util.SMTStatic;
|
|
public class SMTQwenASTSQLTreeParser
|
{
|
private Map<String, Json> _mapId2SQLParam = new LinkedHashMap<>();
|
private Map<String, String> _mapId2UserParam = new HashMap<>();
|
|
public SMTQwenASTSQLTreeParser(Collection<Json> sqlParams)
|
{
|
for(Json jsonSQLParam : sqlParams)
|
{
|
_mapId2SQLParam.put(jsonSQLParam.getJson("id").asString(), jsonSQLParam);
|
}
|
}
|
|
public void setUserParamMap(Json jsonUserParamMap)
|
{
|
for(Entry<String, Json> entry : jsonUserParamMap.asJsonMap().entrySet())
|
{
|
_mapId2UserParam.put(entry.getKey(), entry.getValue().asString());
|
}
|
}
|
|
public String convStringToTitle(String str) throws Exception
|
{
|
for(Json jsonSQLParam : _mapId2SQLParam.values())
|
{
|
String curId = jsonSQLParam.getJson("id").asString();
|
String curValue = getSQLParamValueByJson(curId);
|
String type = jsonSQLParam.getJson("type").asString();
|
if("list".equals(type))
|
{
|
for(Json jsonItem : jsonSQLParam.getJson("list").asJsonList())
|
{
|
String value = jsonItem.getJson("value").asString();
|
String title = jsonItem.getJson("title").asString();
|
if(curValue.equals(value))
|
{
|
curValue = title;
|
break;
|
}
|
}
|
}
|
str = str.replace(curId, curValue);
|
}
|
|
return str;
|
}
|
|
public SMTJavaAIError querySummaryValuesToJson(SMTDatabase db, Json jsonSQLTree, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq) throws Exception
|
{
|
StringBuilder sbSQL = new StringBuilder();
|
SMTJavaAIError error = parseSQLTreeToSQL(jsonSQLTree, tranReq, sbSQL);
|
if(error != null)
|
return error;
|
|
DBRecords recs = db.querySQL(sbSQL.toString(), null);
|
if(recs.getRowCount() == 0)
|
return null;
|
|
DBRecord rec = recs.getRecord(0);
|
jsonWr.beginArray("values");
|
{
|
jsonWr.beginMap(null);
|
{
|
jsonWr.addKeyValue("title", rec.getString("title"));
|
jsonWr.addKeyValue("value", rec.getString("value"));
|
}
|
jsonWr.endMap();
|
}
|
jsonWr.endArray();
|
|
return null;
|
}
|
|
public void outputSQLParamToJson(SMTJsonWriter jsonWr)
|
{
|
jsonWr.beginArray("params");
|
{
|
for(Json jsonParam : _mapId2SQLParam.values())
|
{
|
jsonWr.addKeyRaw(null, jsonParam);
|
}
|
}
|
jsonWr.endArray();
|
}
|
|
public SMTJavaAIError queryDetailValuesToJson(SMTDatabase db, Json jsonSQLTree, String title, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq) throws Exception
|
{
|
StringBuilder sbSQL = new StringBuilder();
|
SMTJavaAIError error = parseSQLTreeToSQL(jsonSQLTree, tranReq, sbSQL);
|
if(error != null)
|
return error;
|
|
String curTitle = convStringToTitle(title);
|
|
jsonWr.beginArray("values");
|
db.querySQLNotify(sbSQL.toString(), null, new SMTDatabase.DBQueryNotify() {
|
|
@Override
|
public boolean onNextRecord(DBRecord rec) throws Exception
|
{
|
jsonWr.beginArray(null);
|
{
|
jsonWr.addKeyValue(null, rec.getString("otime"));
|
jsonWr.addKeyValue(null, curTitle);
|
jsonWr.addKeyValue(null, rec.getString("value"));
|
}
|
jsonWr.endArray();
|
return true;
|
}
|
|
});
|
jsonWr.endArray();
|
|
return null;
|
}
|
|
|
private SMTJavaAIError parseSQLTreeToSQL(Json jsonSQLTree, SMTAIServerRequest tranReq, StringBuilder r_sbSQL) throws Exception
|
{
|
// 生成SQL
|
tranReq.traceLLMDebug("SQL语法树：\n" + SMTStatic.formatJson(jsonSQLTree));
|
SMTJavaAIError error = callParseSQLTreeToSQL(jsonSQLTree, r_sbSQL);
|
if(error != null)
|
return error;
|
tranReq.traceLLMDebug("SQL语句：\n" + r_sbSQL.toString());
|
|
return null;
|
}
|
|
private SMTJavaAIError callParseSQLTreeToSQL(Json jsonSQLTree, StringBuilder r_sbSQL) throws Exception
|
{
|
String call = jsonSQLTree.getJson("call").asString();
|
if("query_detail".equals(call))
|
{
|
return call_query_detail(jsonSQLTree, r_sbSQL);
|
}
|
else if("comp_values".equals(call))
|
{
|
return call_comp_values(jsonSQLTree, r_sbSQL);
|
}
|
|
|
throw new Exception("unknow parse sql : " + call);
|
}
|
|
private String getSQLParamValueByJson(String id) throws Exception
|
{
|
return getSQLParamValueByJson(id, null);
|
}
|
|
private String getSQLParamValueByJson(String id, Set<String> setChecked) throws Exception
|
{
|
Json jsonSQLParam = _mapId2SQLParam.get(id);
|
if(jsonSQLParam == null)
|
throw new Exception("can't find sql param id : " + id);
|
|
// 如果是引用id,则递归引用
|
String refId = jsonSQLParam.safeGetStr("ref_id", null);
|
if(!SMTStatic.isNullOrEmpty(refId))
|
{
|
if(setChecked == null)
|
setChecked = new HashSet<>();
|
else if(setChecked.contains(refId))
|
throw new Exception("ref id for sql param is existed");
|
|
setChecked.add(refId);
|
|
return getSQLParamValueByJson(refId, setChecked);
|
}
|
|
String value = this._mapId2UserParam.get(id);
|
if(value != null)
|
return value;
|
|
return jsonSQLParam.safeGetStr("value", "");
|
}
|
|
private String convSQLParam2SQLText(Json jsonSQLNode) throws Exception
|
{
|
String id = jsonSQLNode.getJson("id").asString();
|
String type = jsonSQLNode.getJson("type").asString();
|
String value = getSQLParamValueByJson(id);
|
|
if("start_time".equals(type))
|
{
|
return "'" + value.replace("'", "''") + "'::timestamp";
|
}
|
else if("end_time".equals(type))
|
{
|
return "'" + value.replace("'", "''") + "'::timestamp";
|
}
|
else if("timestamp".equals(type))
|
{
|
return "'" + value.replace("'", "''") + "'::timestamp";
|
} else if("split_otype".equals(type))
|
{
|
String[] sp = value.split("\\,", 2);
|
return "'" + sp[0].replace("'", "''") + "'";
|
}
|
else if("split_oname".equals(type))
|
{
|
String[] sp = value.split("\\,");
|
return "'" + sp[1].replace("'", "''") + "'";
|
}
|
else if("step_time".equals(type))
|
{
|
return "'" + value.replace("'", "''") + "'";
|
}
|
else
|
throw new Exception("unkonw sql param type : " + type);
|
}
|
|
private SMTJavaAIError call_query_detail(Json jsonSQLTree, StringBuilder r_sbSQL) throws Exception
|
{
|
Json jsonOffsetTime = jsonSQLTree.safeGetJson("offset_timestamp");
|
|
String OTIME_SQL;
|
String END_TIME_SQL;
|
if(jsonOffsetTime == null)
|
{
|
OTIME_SQL = jsonSQLTree.getJson("column_otime").asString();
|
END_TIME_SQL = convSQLParam2SQLText(jsonSQLTree.getJson("end_time"));
|
}
|
else
|
{
|
OTIME_SQL =
|
jsonSQLTree.getJson("column_otime").asString() + "::timetsamp"
|
+ " + ("
|
+ convSQLParam2SQLText(jsonOffsetTime.getJson("prev_start_time"))
|
+ "-"
|
+ convSQLParam2SQLText(jsonOffsetTime.getJson("cur_start_time"))
|
+ ")"
|
;
|
END_TIME_SQL =
|
convSQLParam2SQLText(jsonOffsetTime.getJson("cur_start_time"))
|
+ " + ("
|
+ convSQLParam2SQLText(jsonOffsetTime.getJson("prev_end_time"))
|
+ "-"
|
+ convSQLParam2SQLText(jsonOffsetTime.getJson("prev_start_time"))
|
+ ")"
|
;
|
|
}
|
|
|
// 生成基本明细SQL
|
String sqlDetail =
|
"SELECT\n"
|
+ " " + OTIME_SQL + " as OTIME,\n"
|
+ " " + jsonSQLTree.getJson("column_value").asString() + " as VALUE\n"
|
+ "FROM \n"
|
+ " " + jsonSQLTree.getJson("table_name").asString() + "\n"
|
+ "WHERE\n"
|
+ " " + jsonSQLTree.getJson("column_otime").asString()
|
+ " BETWEEN " + convSQLParam2SQLText(jsonSQLTree.getJson("start_time"))
|
+ " AND " + END_TIME_SQL + "\n"
|
+ " AND OTYPE=" + convSQLParam2SQLText(jsonSQLTree.getJson("otype")) + "\n"
|
+ " AND ONAME=" + convSQLParam2SQLText(jsonSQLTree.getJson("oname")) + "\n"
|
;
|
|
// 获取分组信息
|
Json jsonGroup = jsonSQLTree.safeGetJson("group");
|
if(jsonGroup == null)
|
{
|
r_sbSQL.append(sqlDetail);
|
return null;
|
}
|
|
String groupType = jsonGroup.getJson("type").asString();
|
if("summary".equals(groupType))
|
{
|
String title = jsonGroup.getJson("title").asString();
|
String operate = jsonGroup.getJson("operate").asString();
|
String sqlGroup = "SELECT " + operate + "(value) as value, '" + title.replace("'", "''") + "' as title FROM (\n" + sqlDetail + "\n) T";
|
r_sbSQL.append(sqlGroup);
|
return null;
|
}
|
else if("time_bucket".equals(groupType))
|
{
|
String operate = jsonGroup.getJson("operate").asString();
|
String sqlGroup =
|
"SELECT otime, " + operate + "(value) as value\n"
|
+ "FROM (SELECT time_bucket(" + convSQLParam2SQLText(jsonGroup.getJson("step_time")) + ", otime) as otime, value FROM (\n" + sqlDetail + "\n) T)T\n"
|
+ "GROUP BY OTIME\n"
|
+ "ORDER BY OTIME\n"
|
;
|
r_sbSQL.append(sqlGroup);
|
return null;
|
}
|
else
|
{
|
throw new Exception("unknow group type : " + groupType);
|
}
|
|
}
|
|
private SMTJavaAIError call_comp_values(Json jsonSQLTree, StringBuilder r_sbSQL) throws Exception
|
{
|
StringBuilder sbRS1 = new StringBuilder();
|
callParseSQLTreeToSQL(jsonSQLTree.getJson("recordset1"), sbRS1);
|
StringBuilder sbRS2 = new StringBuilder();
|
callParseSQLTreeToSQL(jsonSQLTree.getJson("recordset2"), sbRS2);
|
|
String operate = jsonSQLTree.getJson("operate").asString();
|
r_sbSQL.append(
|
"SELECT A.OTIME AS OTIME, " + operate + "\n"
|
+ "FROM (SELECT OTIME, VALUE AS VALUE1 FROM (" + sbRS1.toString() + "\n) T)A\n"
|
+ "INNER JOIN (SELECT OTIME, VALUE AS VALUE2 FROM (" + sbRS1.toString() + "\n) T)B\n"
|
+ "ON A.OTIME=B.OTIME\n"
|
);
|
|
return null;
|
}
|
}
|
