def _getMeta(schema, tablename):
'''
从元数据库获取元数据
@param schema:
@param tablename:
@return:
'''
ident = db.querySQL2map("SELECT IDENTID,"
"YSJBZ,"
"IDEDVER,"
"IDTITLE,"
"IDENTCODE,"
"IDABS,"
"IDPURP,"
"KEYWORD,"
"ISTATUS,"
"ADALANG,"
"DATACHAR,"
"TPCAT,"
"GTMEAN,"
"GTMEANBH,"
"FORMATNAME,"
"FORMATVER "
"FROM MD_IDENT_M where IDENTID='%s.%s'" %
(schema.lower(), tablename.lower()),
dbid="mdu")
if len(ident) == 0:
return None
fields = db.querySQL2map(
"SELECT IDENTID,CNNAME,ENNAME,VERSION,MS,LX,CD,GS,DW,ZY,GLSJJ,IS_PK,IS_MC "
"FROM MD_DATAMETA_M WHERE IDENTID='%s.%s'" %
(schema.lower(), tablename.lower()),
dbid="mdu")
if len(fields) == 0:
return None
return {
'tablename':
ident[0]["IDTITLE"],
'tableid':
ident[0]["IDENTCODE"],
'fields':
list(
map(
lambda x: ({
'name': x["CNNAME"],
'code': x["ENNAME"],
'unit': x["DW"],
'type': x["LX"],
'length': x["CD"]
}), fields))
}
def _reloadAppky(appkey=None):
"""
重新加载AppKey
:return:
redis中
appkey的生成逻辑是“APPKEY_”与appid的字符串连接
rest服务的redis key 是SRV_URL_
"""
if appkey is None:
appsrv = db.querySQL2map(
"select APP_ID,SERVICE_ID from uservice_servicerole")
else:
appsrv = db.querySQL2map(
"select APP_ID,SERVICE_ID from uservice_servicerole where APP_ID=:APPID",
{"APPID": appkey})
def reloadAppkey(cur):
"""
重新加载appkey回调句柄
:param cur:
:return:
"""
preKey = "APPKEY_"
ks = GLOBAREDIS.keys(preKey + "*")
field = ['app_id', 'publickey', 'privatekey', 'uname', 'pwd']
appkeys = []
for item in cur.fetchall():
d = dict(zip(field, item))
srvs = []
for srv in appsrv:
if srv["APP_ID"] == d['app_id']:
srvs.append(srv["SERVICE_ID"])
d["services"] = srvs
key = preKey + d['app_id']
redistip.put2redis(key, d)
if key in ks:
ks.remove(key)
appkeys.append(key)
if settings.DEBUG:
settings.infoLogger.info("add app data,appkey:%s" %
(preKey + d['app_id']))
for k in ks:
GLOBAREDIS.delete(k)
if appkey is None:
db.querySQL(
"SELECT APP_ID,PUBLICKEY,PRIVATEKEY,UNAME,PWD FROM P_APPKEY",
reloadAppkey)
else:
db.querySQL(
"SELECT APP_ID,PUBLICKEY,PRIVATEKEY,UNAME,PWD FROM P_APPKEY where APP_ID=:APP_ID",
{"APP_ID": appkey}, reloadAppkey)
def reloadDataSource():
'''
加载数据库中的P_AT_DATASOURCE表保存的数据源，只支持jdbc:oracle:thin:@127.0.0.1:1521:orcl,stu,stu格式的数据库链接串
@return:
'''
dbmap = db.querySQL2map("select DATAID,DATAURL from P_AT_DATASOURCE")
if len(dbmap) == 0:
return
rs = {}
for item in dbmap:
url = item["DATAURL"]
if url.find("jdbc:oracle:thin") != -1:
# jdbc:oracle:thin:@127.0.0.1:1521:orcl,stu,stu
us = url.split(",")
if len(us) != 3:
continue
r = {"USER": us[1], "PASSWORD": us[2]}
i = us[0].find("@")
if i == -1:
continue
s = us[0][-1 * (len(us[0]) - us[0].find("@") - 1):]
ss = s.split(":")
if len(ss) != 3:
continue
r["HOST"] = ss[0]
r["PORT"] = ss[1]
r["NAME"] = ss[2]
rs[item["DATAID"]] = r
redistip.put2redis("P_AT_DATASOURCE", rs)
for key in rs:
redistip.put2redis("P_AT_DATASOURCE_%s" % key, rs[key])
settings.DATABASES[key] = rs[key]
def _getDict(schema, tablename):
'''
从Oracle获取数据字典
@param schema:
@param tablename:
@return:
'''
if settings.SRVDATABASETYPE != 'oracle':
return None
dicts = db.querySQL2map(
"select table_name,owner,"
"column_name,data_type,data_length,"
"data_precision,Data_Scale,nullable "
"from all_tab_columns where owner='%s' and table_name='%s'" %
(schema.upper(), tablename.upper()))
if len(dicts) == 0:
return None
return [
dict(
list(
map(lambda x: [x, row[x]], [
'TABLE_NAME', 'OWNER', 'COLUMN_NAME', 'DATA_TYPE',
'DATA_LENGTH'
]))) for row in dicts
]
def _getAllSrv():
'''
返回除WS和REST外所有服务type=3,4,5,6,7
@return:
'''
datas = db.querySQL2map(
"SELECT SERVICEID,TYPE,SERVICENAME,URL,ENABLED,CACHETYPE,SECRET,MSGLOG FROM USERVICE_DATA WHERE type<>1 and type<>2 and type<>8"
)
result = []
for data in datas:
result.append(_getServiceFromDB(data))
return result
def _reloadCacheData():
ks = GLOBAREDIS.keys("CACHE_*")
for k in ks:
GLOBAREDIS.delete(k)
for item in settings.CACHE_TABLES:
key = "CACHE_" + item[3] + "_"
data = db.querySQL2map(item[0], None, item[1])
for d in data:
k = key + '_'.join(map(lambda x: d[x], item[2]))
if (item[4] is None) or (len(item[4]) == 0):
redistip.put2redis(k, d)
elif len(item[4]) == 1:
redistip.putStr2redis(k, d[item[4][0]])
else:
redistip.put2redis(k, dict([[r, d[r]] for r in item[4]]))
def post(self):
key = self.get_argument('key')
type = self.get_argument('type')
page_num = 1
page_size = 20
try:
page_num = int(self.get_argument('page_num'))
except:
page_num = 1
if type.strip() == '' or type is None:
type = 'ROT'
keylist = key.split()
default_dbcfg = resSearch.getDBcfg()
result_list = []
# fenye_list = []
if type == 'DAT' or type == 'ROT':
sql = """select a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,b.IDENTID,c.IDTITLE
from RD_JGHMLB a LEFT JOIN RD_DAT b ON b.MLBH = a.MLBH LEFT JOIN MD_IDENT_M c ON
c.IDENTID = b.IDENTID """
sql = " ".join(
[sql, "WHERE a.RSTYPE = '{typeone}'".format(typeone='DAT')])
for i in [i.strip() for i in keylist if len(keylist) > 0]:
where_or = "AND (a.MLMC like '%{keyone}%' or c.IDTITLE like '%{keyone}%') ".format(
keyone=i)
sql = " ".join([sql, where_or])
result_list.extend(db.querySQL2map(sql, None, "mdu"))
else:
result_list.extend(db.querySQL2map(sql, None, "mdu"))
if type == 'DOC' or type == 'ROT':
pass
if type == 'COM' or type == 'ROT':
sql = """select a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.SUBASS_NAME,c.SUBASS_PATH,c.SUBASS_SIZE,c.SUBASS_TYPE FROM RD_JGHMLB a
LEFT JOIN RD_COM b ON b.MLBH = a.MLBH LEFT JOIN {bjntu}.P_AT_SUBASSEMBLY c ON c.SUBASS_ID = b.ASSID
where a.RSTYPE = 'COM' """.format(
bjntu=default_dbcfg["USER"])
if len(keylist) > 0:
for i in [i.strip() for i in keylist if len(keylist) > 0]:
where_or = " AND (a.MLMC like '%{keyone}%' OR c.SUBASS_NAME like '%{keyone}%' or c.SUBASS_PATH like '%{keyone}%' or c.SUBASS_SIZE like '%{keyone}%' or c.SUBASS_TYPE like '%{keyone}%') ".format(
keyone=i)
sql = " ".join([sql, where_or])
result_list.extend(db.querySQL2map(sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
else:
result_list.extend(db.querySQL2map(sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
if type == 'APP' or type == 'ROT':
sql = """select a.MLBH,a.MLMC,a.RSTYPE,b.APPID,c.APP_IP,c.APP_NAME,c.APP_URL FROM RD_JGHMLB a
LEFT JOIN RD_APP b ON a.MLBH = b.MLBH LEFT JOIN {bjntu}.p_appinfo c ON c.APP_ID = b.APPID
where a.RSTYPE = 'APP' """.format(
bjntu=default_dbcfg["USER"])
if len(keylist) > 0:
for i in [i.strip() for i in keylist if len(keylist) > 0]:
where_or = "AND (a.MLMC like '%{keyone}%' or c.APP_ID like '%{keyone}%' or c.APP_NAME like '%{keyone}%' or c.APP_URL like '%{keyone}%') ".format(
keyone=i)
sql = " ".join([sql, where_or])
result_list.extend(db.querySQL2map(sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
else:
result_list.extend(db.querySQL2map(sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
if type == 'USR' or type == 'ROT':
sql = """SELECT a.MLBH, a.MLMC, a.FJMLBH, a.STATUS, a.RSTYPE,c.USER_ID,c.USER_NAME,c.USER_GENDER,d.ORG_NAME FROM RD_JGHMLB a
LEFT JOIN RD_USR b on a.MLBH = b.MLBH LEFT JOIN {bjntu}.jeda_user c on c.USER_ID = b.USER_ID
RIGHT JOIN {bjntu}.JEDA_ORG d on c.ORG_ID =d.ORG_ID WHERE a.RSTYPE = 'USR' """.format(
bjntu=default_dbcfg["USER"])
if len(keylist) > 0:
for i in [i.strip() for i in keylist if len(keylist) > 0]:
where_or = """ AND (a.MLMC like '%{keyone}%'
or c.LOGIN_NAME like '%{keyone}%'
or c.USER_NAME like '%{keyone}%'
or d.ORG_NAME like '%{keyone}%')""".format(
keyone=i)
sql = " ".join([sql, where_or])
result_list.extend(db.querySQL2map(sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
else:
result_list.extend(db.querySQL2map(sql, None, "mdu"))
if type == 'SRV' or type == 'ROT':
if len(keylist) > 0:
for i in [i.strip() for i in keylist if len(keylist) > 0]:
"""用于查询webservice型服务"""
WS_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,d.ORIGIALURL,c.ENABLED,c.SECRET,d.NAMESPACE FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_WS d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
AND (a.MLMC like '%{keyone}%' OR c.SERVICENAME like '%{keyone}%') """.format(
bjntu=default_dbcfg["USER"], keyone=i)
result_list.extend(db.querySQL2map(WS_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于查询表封装服务"""
table_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,d.SCHEMANAME,d.TABLENAME FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_TABLE d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
AND (a.MLMC like '%{keyone}%' OR c.SERVICENAME like '%{keyone}%') """.format(
bjntu=default_dbcfg["USER"], keyone=i)
result_list.extend(db.querySQL2map(table_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于查询数据查询服务"""
sql_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,d.SQL FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_SQL d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
AND (a.MLMC like '%{keyone}%' OR c.SERVICENAME like '%{keyone}%') """.format(
bjntu=default_dbcfg["USER"], keyone=i)
result_list.extend(db.querySQL2map(sql_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于查询rest服务和urlget以及urlpost服务"""
rest_sql = """ SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,
d.ORIGIALURL FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_REST d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
AND (a.MLMC like '%{keyone}%' OR c.SERVICENAME like '%{keyone}%') """.format(
bjntu=default_dbcfg["USER"], keyone=i)
result_list.extend(db.querySQL2map(rest_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于大平台查询服务"""
sql_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,d.SERVICEID,d.ORIGIALURL,d.SERVICETYPE,d.ORIGIALSECURITY,d.META,c.SERVICENAME,c.NOTE,c.TYPE,c.URL,c.ENABLED,c.SECRET,f.AREA FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH
RIGHT JOIN {bjntu}.USERVICE_DATA c ON c. SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_CLOUD d on d.SERVICEID = c.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_CLOUDROLE e ON e.SERVICEID = d.SERVICEID
RIGHT JOIN {bjntu}.P_PLATFORM f ON f.PLATID = e.PLATID
where a.RSTYPE = 'SRV'
AND (a.MLMC like '%{keyone}%' OR c.SERVICENAME like '%{keyone}%')""".format(
bjntu=default_dbcfg["USER"], keyone=i)
result_list.extend(db.querySQL2map(sql_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
else:
WS_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,d.ORIGIALURL,d.NAMESPACE FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_WS d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
""".format(bjntu=default_dbcfg["USER"])
result_list.extend(db.querySQL2map(WS_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于查询表封装服务"""
table_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,d.SCHEMANAME,d.TABLENAME FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_TABLE d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
""".format(bjntu=default_dbcfg["USER"])
result_list.extend(db.querySQL2map(table_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于查询数据查询服务"""
sql_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,d.SQL FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_SQL d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
""".format(bjntu=default_dbcfg["USER"])
result_list.extend(db.querySQL2map(sql_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
"""用于查询rest服务和urlget以及urlpost服务"""
rest_sql = """ SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.NOTE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL,c.ENABLED,c.SECRET,d.ORIGIALURL FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH RIGHT JOIN
{bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_REST d ON d. SERVICEID = c.SERVICEID where a.RSTYPE = 'SRV'
""".format(bjntu=default_dbcfg["USER"])
result_list.extend(db.querySQL2map(rest_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
# sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,c.SERVICEID,c.SERVICENAME,c.TYPE,c.URL FROM RD_JGHMLB a
# LEFT JOIN RD_SRV b on a.MLBH = b.MLBH LEFT JOIN
# {bjntu}.USERVICE_DATA c on c.SERVICEID = b.SERVICEID
# result_list.extend(db.querySQL2map(sql, None, "mdu"))
# result_list = [dict(t) for t in set([tuple(d.items()) for d in result_list])]
"""用于大平台查询服务"""
sql_sql = """SELECT a.MLBH,a.MLMC,a.FJMLBH,a.STATUS,a.RSTYPE,d.SERVICEID,d.ORIGIALURL,d.SERVICETYPE,d.ORIGIALSECURITY,d.META,c.SERVICENAME,c.NOTE,c.TYPE,c.URL,c.ENABLED,c.SECRET,f.AREA FROM RD_JGHMLB a
RIGHT JOIN RD_SRV b on a.MLBH = b.MLBH
RIGHT JOIN {bjntu}.USERVICE_DATA c ON c. SERVICEID = b.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_CLOUD d on d.SERVICEID = c.SERVICEID
RIGHT JOIN {bjntu}.USERVICE_CLOUDROLE e ON e.SERVICEID = d.SERVICEID
RIGHT JOIN {bjntu}.P_PLATFORM f ON f.PLATID = e.PLATID
where a.RSTYPE = 'SRV'
""".format(bjntu=default_dbcfg["USER"])
result_list.extend(db.querySQL2map(sql_sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
if type == 'OTH' or type == 'ROT':
sql = """SELECT a.MLBH, a.MLMC,a. FJMLBH, a.STATUS, a.RSTYPE,b.ATTNAME,b.ATTTYPE,b.ATTMEMO,c.ATTVALUE,c.RESID
FROM RD_JGHMLB a LEFT JOIN RD_OTH_DICT b on b.MLBH = a.MLBH
LEFT JOIN RD_OTH_RES c on c.ATTNAME = b.ATTNAME INNER JOIN RD_JGHMLB d on a.MLBH = c.MLBH where a.RSTYPE = 'OTH' """
for i in [i.strip() for i in keylist if len(keylist) > 0]:
where_or = " AND (a.MLMC like '%{keyone}%' OR b.ATTNAME like '%{keyone}%') ".format(
keyone=i)
sql = " ".join([sql, where_or])
result_list.extend(db.querySQL2map(sql, None, "mdu"))
result_list = [
dict(t)
for t in set([tuple(d.items()) for d in result_list])
]
# 这句话是去重的
result_list = [
dict(t) for t in set([tuple(d.items()) for d in result_list])
]
self.render(u"ResSearch/indexpost.html",
item=result_list[(page_num - 1) * 20:page_num * 20 + 1],
p_num=page_num,
p_next=page_num + 1,
p_air=page_num - 1,
p_last=math.ceil(len(result_list) / 20.0),
p_type=type,
p_nums=len(result_list),
keylist_len=len(keylist))
def _getServiceFromDB(data):
'''
返回一个服务，data是非RS和REST的服务数据
@param data:
@return:
'''
result = {
'serviceid': data["SERVICEID"],
'serviceName': data["SERVICENAME"],
'enabled': data["ENABLED"],
'type': data["TYPE"],
'url': data["URL"],
'secret': data["SECRET"],
'msglog': data["MSGLOG"],
'cachetype': data["CACHETYPE"],
}
if data["TYPE"] == 5:
# QSFramework
dbt = db.querySQL2map(
"SELECT SERVICEID, SQL, CREATOR,DATASOURCE FROM USERVICE_SQL WHERE SERVICEID=%s"
% data["SERVICEID"])
if len(dbt) == 0:
return None
result["sql"] = dbt[0]["SQL"]
result["datasource"] = dbt[0]["DATASOURCE"]
dbt = db.querySQL2map(
"SELECT ID,SERVICEID,FIELDNAME,FIELDTYPE,CATALOG,FIELDDESC,FIELDDEFAULT FROM USERVICE_DATA_FIELD WHERE SERVICEID=%s"
% data["SERVICEID"])
if len(dbt) == 0:
return result
result["param"] = []
for p in dbt:
result["param"].append({
'name': p["FIELDNAME"],
'type': p["FIELDTYPE"],
'desc': p["FIELDDESC"],
'cagalog': p["CATALOG"],
'default': p["FIELDDEFAULT"]
})
return result
if data["TYPE"] == 6:
# DBTable
dbt = db.querySQL2map(
"SELECT SERVICEID,SCHEMANAME,TABLENAME,INNERJOIN,FIELDLIST,DATASOURCE FROM USERVICE_TABLE WHERE SERVICEID=%s"
% data["SERVICEID"])
if len(dbt) == 0:
return None
result['schemaName'] = dbt[0]["SCHEMANAME"]
result['tableName'] = dbt[0]["TABLENAME"]
result['dict'] = _getDict(dbt[0]["SCHEMANAME"], dbt[0]["TABLENAME"])
result['meta'] = _getMeta(dbt[0]["SCHEMANAME"], dbt[0]["TABLENAME"])
result['fieldlist'] = dbt[0]["FIELDLIST"]
result['innerjoin'] = dbt[0]["INNERJOIN"]
result["datasource"] = dbt[0]["DATASOURCE"]
return result
if data["TYPE"] == 7:
dbt = db.querySQL2map(
"SELECT SERVICEID,SCHEMANAME,TABLENAME,INNERJOIN,FIELDLIST FROM USERVICE_TABLE WHERE SERVICEID=%s"
% data["SERVICEID"])
if len(dbt) == 0:
return None
result['schemaName'] = dbt[0]["SCHEMANAME"]
result['tableName'] = dbt[0]["TABLENAME"]
result['fieldlist'] = dbt[0]["FIELDLIST"]
result['innerjoin'] = dbt[0]["INNERJOIN"]
return result
return None
def tableService(srv, params):
starttime = (datetime.now())
p = params
if p['action'] == 'meta':
# 返回元数据
r = json.dumps(srv['meta'],
cls=redis.DateTimeEncoder,
ensure_ascii=False)
return {"result": r}
if p['action'] == 'dict':
# 返回元数据
r = json.dumps(srv['dict'],
cls=redis.DateTimeEncoder,
ensure_ascii=False)
return {"result": r}
# 默认动作，查询数据及根据主键返回数据
selectBuilder = sqlGenerator.SelectBuilder()
if srv['fieldlist'] != '' and not (srv['fieldlist'] is None):
str = srv['fieldlist']
sarry = str.split(',')
for item in sarry:
selectBuilder.column(item)
tablename = srv['tableName']
if tablename.find(".") == -1:
tablename = srv['schemaName'] + "." + srv['tableName']
selectBuilder.table(tablename)
if p['action'] == 'query' and p['opt'] != "":
# 根据条件返回数据
selectBuilder.addWhere(p['opt'])
# elif p['action'] == 'sql' and request.method == "POST":
# # 执行SELECT语句
# pass
elif len(p['pks']) != 0:
# 处理主键查询
for pk in p['pks']:
pass
if p['order'].strip():
# 处理排序字段
orders = p['order'].split("|")
for s in orders:
selectBuilder.orderby(s)
# stnm from stu.st_stbprp_b on st_stbprp_b.stcd=src.stcd
if srv['innerjoin'] != "" and not (srv['innerjoin'] is None):
# '_CACHE:stnm on stcd'
if srv['innerjoin'][0:6] != '_CACHE:':
selectBuilder = sqlGenerator.InnerSelectBuilder(selectBuilder, srv['innerjoin'])
if p['pagesize'] == 0 and p['limit'] == 0:
p["limit"] = 10000
if p["limit"] > 10000:
p["limit"] = 10000
ds = srv["datasource"]
if ds is None:
ds = settings.DEFAULTRESTCONN
builder = sqlGenerator.SelectPagerBuilder(selectBuilder, p['pagesize'], p['pageindex'], p['limit'],dbtype=dbtip.getDBType(ds))
sql = builder.buildSQL().getSQL()
if settings.DEBUG:
settings.debugLogger.debug("pure sql:" + selectBuilder.buildSQL().getSQL())
settings.debugLogger.debug("pager sql:" + sql)
if p['sql'] == "true":
return {"result": sql}
if not sqlParse.availableSQL(selectBuilder.buildSQL().getSQL()):
# 非法SQL语句
# self.render('msg.html', title=u"错误", msg=u"SQL语句非法:" + selectBuilder.buildSQL().getSQL())
# self.finish()
# callback()
return {"errormsg": u"SQL语句非法:" + selectBuilder.buildSQL().getSQL()}
# cursor = connections[settings.DEFAULTRESTCONN].cursor()
# cursor.execute(sql)
DBstarttime = (datetime.now())
data = dbtip.querySQL2map(sql, param=None, dbid=ds)
spdbtime = (datetime.now() - DBstarttime).total_seconds() * 1000
if srv['innerjoin'] != "" and not (srv['innerjoin'] is None) and (
srv['innerjoin'][0:7] == '_CACHE:'):
# '_CACHE:stnm on stcd'
result = innerjoinfromcache(srv['innerjoin'], data)
else:
result = data
if p['opt'] == 'd':
# 处理元数据动作
result = {'dict': srv['dict'], 'data': result}
if p['opt'] == 'm':
# 处理元数据动作
result = {'dict': srv['meta'], 'data': result}
r = json.dumps(result, cls=redis.DateTimeEncoder, ensure_ascii=False)
return {"result": r,
"spdbtime": spdbtime,
"sptime": (datetime.now() - starttime).total_seconds() * 1000}