@Test
public void testMissingPKColumns() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
TestUtil.executeScript(
con,
"create table foobar (id1 integer not null, id2 integer not null, somedata varchar(50), primary key (id1, id2));\n"
+ "commit;");
String sql = "select id1, somedata from FOOBAR";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), con);
}
UpdateTableDetector detector = new UpdateTableDetector(con);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("foobar");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("FOOBAR", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
List<ColumnIdentifier> cols = detector.getMissingPkColumns();
assertNotNull(cols);
assertEquals(1, cols.size());
assertEquals("ID2", cols.get(0).getColumnName());
}
@Test
public void testNotNullIndex() throws Exception {
TestUtil util = getTestUtil();
WbConnection conn = util.getConnection();
TestUtil.executeScript(
conn,
"create table person (id integer, id2 integer not null, name varchar(20) not null);\n"
+ "create unique index aaaa on person (id);\n"
+ "create unique index zzzz on person (id2);\n"
+ "commit;");
String sql = "select id, id2, name from person";
ResultInfo info = null;
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), conn);
}
UpdateTableDetector detector = new UpdateTableDetector(conn);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("person");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("PERSON", tbl.getTableName());
assertFalse(info.getColumn(0).isPkColumn());
assertTrue(info.getColumn(1).isPkColumn());
}
@Override
public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain)
throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest) req;
HttpServletResponse response = (HttpServletResponse) resp;
request.setCharacterEncoding(httpEncoding);
response.setCharacterEncoding(httpEncoding);
String path = RequestUtils.getPathInfo(request);
if (httpCache == false) {
ResponseUtils.setBufferOff(response);
}
if (bypassRequestUrls != null && bypassRequestUrls.accept(request, path)) {
chain.doFilter(request, response);
return;
}
HttpMethod httpMethod = HttpMethod.valueOf(request.getMethod());
if (corsRequestProcessor != null) {
corsRequestProcessor.setHeaders(request, response);
if (httpMethod == HttpMethod.OPTIONS) {
response.setStatus(HttpServletResponse.SC_OK);
return;
}
}
RouteInfo route = router.lookup(request, path, httpMethod);
request = fileUploadResolver.transform(request);
RequestContext ctx = new RequestContext(request, response, path, httpMethod, route);
try {
if (route == null || route == RouteInfo.NOT_FOUND) {
throw new ActionNotFoundException(path);
}
InterceptorChainImpl interceptorChain =
new InterceptorChainImpl(WebConfig.getInterceptors(), ctx);
interceptorChain.invoke();
ResultInfo result = interceptorChain.getResult();
if (result != null) {
ResultHandler<Object> handler = resultHandlerResolver.lookup(result.getResultClass());
handler.handle(ctx, result.getResultObject());
}
} catch (Exception e) {
handleError(ctx, e);
} finally {
if (ctx != null) {
ctx.destory();
}
}
}
@SuppressWarnings("unchecked")
@Override
public ResultInfo<DataDefine> getResponseData() {
ResultInfo<DataDefine> result = new ResultInfo<DataDefine>();
if (mData != null) {
String str = new String(mData);
if (!TextUtils.isEmpty(str)) {
try {
JSONObject json = new JSONObject(str.toString());
if (json.getString("Success").equals("false")) {
result.Success = false;
result.Message = json.getString("Message");
} else {
json = new JSONObject(json.getString("Data"));
result.Data = new DataDefine(json);
}
} catch (JSONException e) {
result.Success = false;
result.Message = "服务器异常，获取勘察表失败";
DataLogOperator.taskHttp(
"GetDataDefineDataTask=>获取勘察匹配表完整信息失败(getResponseData)", e.getMessage());
}
} else {
result.Success = false;
result.Message = "没有返回数据";
}
}
return result;
}
@Test
public void testDuplicateNames() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
String sql =
"CREATE TABLE one (ident int, refid int, PRIMARY KEY(ident));\n"
+ "CREATE TABLE two (ident int, refid int, PRIMARY KEY(ident));\n"
+ "commit;";
TestUtil.executeScript(con, sql);
String query =
"SELECT one.ident, two.ident \n" + "FROM one, two \n" + "WHERE one.refid = two.refid;";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
info = new ResultInfo(rs.getMetaData(), con);
}
SourceTableDetector std = new SourceTableDetector();
std.checkColumnTables(query, info, con);
UpdateTableDetector utd = new UpdateTableDetector(con);
utd.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("TWO");
utd.checkUpdateTable(toCheck, info);
TableIdentifier tbl = utd.getUpdateTable();
assertEquals("TWO", tbl.getTableName());
assertEquals("PUBLIC", tbl.getSchema());
assertEquals("one", info.getColumn(0).getSourceTableName());
assertEquals("two", info.getColumn(1).getSourceTableName());
assertTrue(info.getColumn(1).isUpdateable());
assertTrue(info.getColumn(1).isPkColumn());
assertTrue(CollectionUtil.isEmpty(utd.getMissingPkColumns()));
}
/**
* 获取指定勘察匹配表完整信息
*
* @param currentUser
* @param dataDefine
* @return
*/
public ResultInfo<DataDefine> request(UserInfo currentUser, DataDefine dataDefine) {
ResultInfo<DataDefine> result = new ResultInfo<DataDefine>();
String url = currentUser.LatestServer + "/apis/GetAllDataFieldDefine/" + dataDefine.DDID;
// 填充参数，key-value。key是接口要求传的变量名称
Hashtable<String, Object> params = new Hashtable<String, Object>(1);
params.put("token", currentUser.Token);
CommonRequestPackage requestPackage =
new CommonRequestPackage(url, RequestTypeEnum.GET, params);
try {
YFHttpClient.request(requestPackage, this);
result = getResponseData();
} catch (Exception e) {
result.Success = false;
result.Message = "服务器异常，获取勘察表失败";
DataLogOperator.taskHttp("GetDataDefineDataTask=>获取指定勘察匹配表完整信息失败(request)", e.getMessage());
}
return result;
}
public List<ResultInfo> getAllData() {
List<ResultInfo> results = new ArrayList<ResultInfo>();
ResultInfo info = null;
String sql = String.format("SELECT * from ssq"); // WHERE period = \'%s\'", period);
try {
ResultSet result = _statement.executeQuery(sql);
while (result.next()) {
List<Integer> reds = new ArrayList<Integer>();
reds.add(result.getInt(2));
reds.add(result.getInt(3));
reds.add(result.getInt(4));
reds.add(result.getInt(5));
reds.add(result.getInt(6));
reds.add(result.getInt(7));
info = new ResultInfo(reds, result.getInt(8));
info.period = result.getInt(1);
results.add(info);
}
} catch (Exception e) {
e.printStackTrace();
}
return results;
}
@Test
public void testSpecialName() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
TestUtil.executeScript(
con,
"create table \"FOO.BAR\" (id integer primary key, somedata varchar(50));\n" + "commit;");
String sql = "select id, somedata from \"FOO.BAR\"";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), con);
}
UpdateTableDetector detector = new UpdateTableDetector(con);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("\"FOO.BAR\"");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("FOO.BAR", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
resetInfo(info);
detector.setCheckPKOnly(true);
detector.checkUpdateTable(toCheck, info);
tbl = detector.getUpdateTable();
assertEquals("FOO.BAR", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
}
@Test
public void testSynonyms() throws Exception {
TestUtil util = getTestUtil();
WbConnection conn = DerbyTestUtil.getDerbyConnection(util.getBaseDir());
TestUtil.executeScript(
conn,
"create table person (id integer primary key, name varchar(20) not null);\n"
+ "create synonym psyn for person;\n");
String sql = "select id, name from psyn";
ResultInfo info = null;
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), conn);
}
info.getColumn(0).setIsPkColumn(false);
UpdateTableDetector detector = new UpdateTableDetector(conn);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("psyn");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("PSYN", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
resetInfo(info);
detector.setCheckPKOnly(true);
detector.checkUpdateTable(toCheck, info);
assertEquals("PSYN", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
}
private void resetInfo(ResultInfo info) {
for (ColumnIdentifier col : info.getColumns()) {
col.setIsPkColumn(false);
col.setIsNullable(true);
}
}