public static String DeleteDownload(String uuid, String callback)
throws SQLException, JSONException {
JSONObject jsonObj = new JSONObject();
MySqlConn m_fpsql = getConn();
Connection conn = m_fpsql.getConn();
String strSql = "update adhoc_download set status='DEL',endtime=? where uuid=? ";
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
int index = 1;
m_fps.setTimestamp(index++, new java.sql.Timestamp(System.currentTimeMillis()));
m_fps.setString(index++, uuid);
m_fps.executeUpdate();
String fullstrSql = m_fps.toString();
jsonObj.put("code", "1");
jsonObj.put("__debug", fullstrSql);
} catch (Exception e) {
jsonObj.put("code", "0");
TableJoin.LOG.error("updatePercent" + m_fps.toString(), e);
jsonObj.put("__debugerror", m_fps.toString());
} finally {
m_fps.close();
m_fpsql.close();
}
if (callback != null && callback.length() > 0) {
return callback + "(" + jsonObj.toString() + ")";
} else {
return jsonObj.toString();
}
}
public static String updatePercent(String uuid, String percent, String status)
throws SQLException {
MySqlConn m_fpsql = getConn();
Connection conn = m_fpsql.getConn();
String strSql =
"update adhoc_joins set " + "percent=?,status=?,lastuptime=? where tableName=? ";
System.out.println(strSql);
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
int index = 1;
m_fps.setString(index++, percent);
m_fps.setString(index++, status);
m_fps.setTimestamp(index++, new java.sql.Timestamp(System.currentTimeMillis()));
m_fps.setString(index++, uuid);
m_fps.executeUpdate();
String fullstrSql = m_fps.toString();
return fullstrSql;
} catch (Exception e) {
TableJoin.LOG.error("updatePercent" + m_fps.toString(), e);
} finally {
m_fps.close();
m_fpsql.close();
}
return "";
}
public static HashMap<String, String> getTableInfo(String uuid) throws SQLException {
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
String day = fmt.format(new Date());
Map stormconf = Utils.readStormConfig();
String hdpConf = (String) stormconf.get("hadoop.conf.dir");
String connstr = (String) stormconf.get("higo.download.offline.conn");
String uname = (String) stormconf.get("higo.download.offline.username");
String passwd = (String) stormconf.get("higo.download.offline.passwd");
String tmppath =
(String) stormconf.get("higo.download.offline.store")
+ "/"
+ day
+ "/tmp_"
+ java.util.UUID.randomUUID().toString();
MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);
Connection conn = m_fpsql.getConn();
Statement stmt = conn.createStatement();
String sql =
"select tableShowName,tableName,colsShowName,colsName,colsType,splitString,txtStorePath,indexStorePath,status,username,createtime,lastuptime,joins,percent from adhoc_joins where tableName='"
+ uuid.replaceAll("'", "")
+ "' limit 10";
ResultSet res = stmt.executeQuery(sql);
HashMap<String, String> rtn = new HashMap<String, String>();
rtn.put("_exehql", sql);
boolean issetup = false;
while (res.next()) {
rtn.put("tableShowName", res.getString("tableShowName"));
rtn.put("txtStorePath", res.getString("txtStorePath"));
rtn.put("indexStorePath", res.getString("indexStorePath"));
rtn.put("splitString", res.getString("splitString"));
rtn.put("colsName", res.getString("colsName"));
rtn.put("status", res.getString("status"));
rtn.put("splitString", res.getString("splitString"));
issetup = true;
}
m_fpsql.close();
if (!issetup) {
return null;
}
return rtn;
}
public static HashMap<String, String> getUserTablesCount(String username, int type)
throws SQLException {
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
Map stormconf = Utils.readStormConfig();
String connstr = (String) stormconf.get("higo.download.offline.conn");
String uname = (String) stormconf.get("higo.download.offline.username");
String passwd = (String) stormconf.get("higo.download.offline.passwd");
MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);
Connection conn = m_fpsql.getConn();
Statement stmt = conn.createStatement();
String strsqlJoin =
"select count(*) as cnt"
+ " from adhoc_joins where username='******'", "")
+ "' and status<>'DEL' ";
StringBuffer bufferSql = new StringBuffer();
if (type == 0) {
String strsqlDownload =
"select count(*) as cnt"
+ " from adhoc_download where username='******'", "")
+ "' and status<>'DEL' ";
bufferSql.append("select sum(cnt) as cnt");
bufferSql.append(" from (" + strsqlJoin + " union " + strsqlDownload + ") tmp limit 10 ");
}
if (type == 1) // for join
{
bufferSql.append(strsqlJoin + " and status='INDEX' order by createtime desc limit 10 ");
}
String sql = bufferSql.toString();
ResultSet res = stmt.executeQuery(sql);
HashMap<String, String> rtn = new HashMap<String, String>();
rtn.put("_exehql", sql);
while (res.next()) {
rtn.put("cnt", String.valueOf(res.getInt("cnt")));
}
m_fpsql.close();
return rtn;
}
public static String create(String userid, Map<String, String> val, String user)
throws Exception {
MySqlConn m_fpsql = getConn();
val.put("opuser", String.valueOf(user));
val.put("optime", getNowTime());
JSONObject jsonObj = new JSONObject();
StringBuffer sqlbuffer = new StringBuffer();
StringBuffer sqlbuffer2 = new StringBuffer();
String joinchar = "";
String[] indexval = new String[val.size()];
int index = 0;
for (Entry<String, String> e : val.entrySet()) {
sqlbuffer.append(joinchar).append(e.getKey());
sqlbuffer2.append(joinchar).append("?");
joinchar = ",";
indexval[index] = e.getValue();
index++;
}
Connection conn = m_fpsql.getConn();
String strSql =
"insert into users_json " + "(" + sqlbuffer + ")" + "values" + "(" + sqlbuffer2 + ")";
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
for (int i = 0; i < indexval.length; i++) {
m_fps.setString(i + 1, indexval[i]);
}
m_fps.executeUpdate();
jsonObj.put("code", "1");
jsonObj.put("____debug", m_fps.toString());
} catch (Exception e) {
jsonObj.put("____debug2", m_fps.toString());
jsonObj.put("____debugerror", e.toString());
jsonObj.put("code", "0");
} finally {
m_fps.close();
m_fpsql.close();
}
return jsonObj.toString();
}
public static String del(String userid, String user) throws Exception {
JSONObject jsonObj = new JSONObject();
jsonObj.put("code", "0");
MySqlConn m_fpsql = getConn();
Connection conn = m_fpsql.getConn();
String strSql = "delete from users_json where userid=? ";
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
int index = 1;
m_fps.setString(index++, userid);
m_fps.executeUpdate();
jsonObj.put("code", "1");
} catch (Exception e) {
jsonObj.put("message", e.toString());
jsonObj.put("code", "0");
} finally {
m_fps.close();
m_fpsql.close();
}
return jsonObj.toString();
}
public static String updateKb(String uuid, long sz) throws SQLException {
MySqlConn m_fpsql = getConn();
Connection conn = m_fpsql.getConn();
String strSql = "update adhoc_joins set " + "resultkb=? where tableName=? ";
System.out.println(strSql);
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
int index = 1;
m_fps.setInt(index++, (int) sz / 1024);
m_fps.setString(index++, uuid);
m_fps.executeUpdate();
String fullstrSql = m_fps.toString();
return fullstrSql;
} catch (Exception e) {
TableJoin.LOG.error("updatePercent" + m_fps.toString(), e);
} finally {
m_fps.close();
m_fpsql.close();
}
return "";
}
public String update(String userid, Map<String, String> val, String user) throws SQLException {
MySqlConn m_fpsql = getConn();
val.put("opuser", String.valueOf(user));
val.put("optime", getNowTime());
StringBuffer sqlbuffer = new StringBuffer();
String joinchar = "";
String[] indexval = new String[val.size()];
int index = 0;
for (Entry<String, String> e : val.entrySet()) {
sqlbuffer.append(joinchar).append(e.getKey() + "=?");
joinchar = ",";
indexval[index] = e.getValue();
index++;
}
Connection conn = m_fpsql.getConn();
String strSql = "update users_json set " + " " + sqlbuffer + " where userid=? ";
System.out.println(strSql);
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
for (int i = 0; i < indexval.length; i++) {
m_fps.setString(i + 1, indexval[i]);
}
m_fps.setString(indexval.length + 1, userid);
m_fps.executeUpdate();
String fullstrSql = m_fps.toString();
return fullstrSql;
} catch (Exception e) {
LOG.error("updatePercent" + m_fps.toString(), e);
} finally {
m_fps.close();
m_fpsql.close();
}
return "";
}
public static String getJson(String daystart, boolean extamsg)
throws JSONException, SQLException {
JSONObject jsonObj = new JSONObject();
jsonObj.put("code", "1");
MySqlConn m_fpsql = getConn();
String strSql =
"select userid,email,cname,role,permission,'-' as queryday,opuser,optime from users_json order by role desc,userid";
if (daystart != null && !daystart.isEmpty()) {
strSql =
" select userid,email,cname,role,permission,opuser,optime from users_json where (cname not in (select nick from query_analyser.day_user_pv where queryday>'"
+ daystart
+ "')) order by role desc,userid";
}
if (extamsg) {
strSql =
" select a.userid as userid,a.email as email ,a.cname as cname,a.role as role,a.permission as permission,a.opuser as opuser,a.optime as optime, b.queryday as queryday "
+ "from ( "
+ ""
+ strSql
+ ") "
+ " a "
+ " left join (select nick,max(queryday) as queryday from query_analyser.day_user_pv group by nick ) b"
+ " on a.cname=b.nick order by role desc,queryday desc";
}
jsonObj.put("_exehql", strSql);
Connection conn = m_fpsql.getConn();
Statement stmt = conn.createStatement();
try {
ResultSet res = stmt.executeQuery(strSql);
JSONArray userlist = new JSONArray();
while (res.next()) {
JSONObject item = new JSONObject();
item.put("userid", String.valueOf(res.getString("userid")));
item.put("email", String.valueOf(res.getString("email")));
item.put("queryday", String.valueOf(res.getString("queryday")));
item.put("cname", String.valueOf(res.getString("cname")));
item.put("role", Integer.parseInt(res.getString("role")));
item.put("permission", new JSONArray(res.getString("permission")));
item.put("opuser", String.valueOf(res.getString("opuser")));
item.put("optime", String.valueOf(res.getString("optime")));
userlist.put(item);
}
m_fpsql.close();
jsonObj.put("code", "1");
jsonObj.put("message", "success");
JSONObject dddd = new JSONObject();
dddd.put("users", userlist);
jsonObj.put("data", dddd);
} catch (Exception e) {
jsonObj.put("message", e.toString());
jsonObj.put("code", "0");
} finally {
m_fpsql.close();
}
return jsonObj.toString();
}
public static String create(
String tableShowName, String colsShowName, String splitString, String username, String joins)
throws Exception {
JSONObject jsonObj = new JSONObject();
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
String day = fmt.format(new Date());
Map stormconf = Utils.readStormConfig();
String hdpConf = (String) stormconf.get("hadoop.conf.dir");
String connstr = (String) stormconf.get("higo.download.offline.conn");
String uname = (String) stormconf.get("higo.download.offline.username");
String passwd = (String) stormconf.get("higo.download.offline.passwd");
String store =
(String) stormconf.get("higo.download.offline.store")
+ "/"
+ day
+ "/"
+ java.util.UUID.randomUUID().toString();
MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);
String tableName = java.util.UUID.randomUUID().toString();
Connection conn = m_fpsql.getConn();
String strSql =
"insert into adhoc_joins "
+ "(tableShowName,tableName,colsShowName,colsName,colsType,splitString,txtStorePath,indexStorePath,status,username,createtime,lastuptime,joins,percent)"
+ "values"
+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement m_fps = conn.prepareStatement(strSql);
try {
int index = 1;
m_fps.setString(index++, tableShowName);
m_fps.setString(index++, tableName);
m_fps.setString(index++, colsShowName);
StringBuffer colsName = new StringBuffer();
StringBuffer colsType = new StringBuffer();
String[] cols = colsShowName.split(",");
String join = "";
HashMap<String, String> colsNames = new HashMap<String, String>();
for (int i = 0; i < cols.length; i++) {
String colname = "cols_" + i + "_s";
colsName.append(join);
colsName.append(colname);
colsType.append(join);
colsType.append("string");
colsNames.put(cols[i], colname);
join = ",";
}
m_fps.setString(index++, colsName.toString());
m_fps.setString(index++, colsType.toString());
m_fps.setString(index++, MakeIndex.parseSplit(splitString));
m_fps.setString(index++, store + "/txt");
m_fps.setString(index++, store + "/index");
m_fps.setString(index++, "init");
m_fps.setString(index++, username);
long nowtims = System.currentTimeMillis();
m_fps.setTimestamp(index++, new java.sql.Timestamp(nowtims));
m_fps.setTimestamp(index++, new java.sql.Timestamp(nowtims));
StringBuffer joinbuff = new StringBuffer();
String joinchar = "";
for (String joindesc : joins.split(",")) {
String[] arr = joindesc.split(":");
joinbuff.append(joinchar);
joinbuff.append(arr[0]);
joinbuff.append(":");
joinbuff.append(arr[1]);
joinbuff.append(":");
joinbuff.append(colsNames.get(arr[2]));
joinchar = ",";
}
m_fps.setString(index++, joinbuff.toString());
m_fps.setString(index++, "");
m_fps.executeUpdate();
jsonObj.put("code", "1");
jsonObj.put("tableid", tableName);
jsonObj.put("____debug", m_fps.toString());
} catch (Exception e) {
jsonObj.put("____debug2", m_fps.toString());
jsonObj.put("____debugerror", e.toString());
jsonObj.put("code", "0");
} finally {
m_fps.close();
m_fpsql.close();
}
return jsonObj.toString();
}
public static String getUserTables(
String username, int start, int rows, int type, String callback)
throws SQLException, JSONException {
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
Map stormconf = Utils.readStormConfig();
String connstr = (String) stormconf.get("higo.download.offline.conn");
String uname = (String) stormconf.get("higo.download.offline.username");
String passwd = (String) stormconf.get("higo.download.offline.passwd");
MySqlConn m_fpsql = new MySqlConn(connstr, uname, passwd);
Connection conn = m_fpsql.getConn();
Statement stmt = conn.createStatement();
String strsqlJoin =
"select '1' as source "
+ ",tableShowName as tableShowName"
+ ",tableName as tableName"
+ ",colsShowName as colsShowName"
+ ",colsName as colsName"
+ ",colsType as colsType"
+ ",splitString as splitString"
+ ",txtStorePath as txtStorePath"
+ ",indexStorePath as indexStorePath"
+ ",'0' as extval"
+ ",'0' as isfinish"
+ ",status as status"
+ ",username as username"
+ ",createtime as createtime"
+ ",lastuptime as lastuptime"
+ ",joins as joins"
+ ",'2' as stage"
+ ",percent as percent"
+ ",resultkb as resultkb"
+ " from adhoc_joins where username='******'", "")
+ "' and status<>'DEL' ";
StringBuffer bufferSql = new StringBuffer();
if (type == 0) // 个人中心列表
{
String strsqlDownload =
"select '2' as source "
+ ",jobname as tableShowName"
+ ",uuid as tableName"
+ ",'empty' as colsShowName"
+ ",'empty' as colsName"
+ ",'empty' as colsType"
+ ",'empty' as splitString"
+ ",storedir as txtStorePath"
+ ",'empty' as indexStorePath"
+ ",extval as extval"
+ ",isfinish as isfinish"
+ ",'INDEX' as status"
+ ",username as username"
+ ",starttime as createtime"
+ ",endtime as lastuptime"
+ ",'' as joins"
+ ",stage as stage"
+ ",percent as percent"
+ ",resultkb as resultkb"
+ " from adhoc_download where username='******'", "")
+ "' and status<>'DEL' ";
bufferSql.append(
"select source,tableShowName,tableName,colsShowName"
+ ",colsName,colsType,splitString,txtStorePath,indexStorePath,extval,status,username,"
+ "createtime,lastuptime,joins,stage,percent,resultkb");
bufferSql.append(
" from ("
+ strsqlJoin
+ " union "
+ strsqlDownload
+ ") tmp order by tmp.createtime desc limit "
+ start
+ ","
+ rows
+ " ");
}
if (type == 1) // for join
{
bufferSql.append(
strsqlJoin
+ " and status='INDEX' order by createtime desc limit "
+ start
+ ","
+ rows
+ " ");
}
String sql = bufferSql.toString();
TableJoin.LOG.info("getUserTables:" + sql);
ResultSet res = stmt.executeQuery(sql);
JSONObject jsonObj = new JSONObject();
jsonObj.put("code", "1");
jsonObj.put("_exehql", sql);
JSONArray jsonArray = new JSONArray();
while (res.next()) {
JSONObject item = new JSONObject();
item.put("source", res.getString("source"));
item.put("tableShowName", res.getString("tableShowName")); // 展示名称
item.put("tableName", res.getString("tableName")); // uuid
item.put("colsShowName", res.getString("colsShowName"));
item.put("colsName", res.getString("colsName"));
item.put("colsType", res.getString("colsType"));
item.put("splitString", res.getString("splitString"));
item.put("txtStorePath", res.getString("txtStorePath"));
item.put("indexStorePath", res.getString("indexStorePath"));
item.put("extval", res.getString("extval"));
item.put("status", res.getString("status"));
item.put("username", res.getString("username"));
item.put("createtime", res.getString("createtime"));
item.put("lastuptime", res.getString("lastuptime"));
item.put("joins", res.getString("joins"));
item.put("stage", res.getString("stage"));
item.put("percent", res.getString("percent"));
item.put("resultkb", res.getString("resultkb"));
boolean issuccess =
res.getString("status").equals("INDEX") && res.getString("extval").equals("0");
item.put(
"proccess", parsePercent(res.getString("stage"), res.getString("percent"), issuccess));
item.put("isActive", String.valueOf(issuccess));
jsonArray.put(item);
}
HashMap<String, String> cnt = getUserTablesCount(username, type);
JSONObject data = new JSONObject();
data.put("list", jsonArray);
data.put("total", cnt.get("cnt"));
jsonObj.put("data", data);
jsonObj.put("total_debug", new JSONObject(cnt));
m_fpsql.close();
if (callback != null && callback.length() > 0) {
return callback + "(" + jsonObj.toString() + ")";
} else {
return jsonObj.toString();
}
}