public static void handle(String sql, ServerConnection c, int offset) {
String schema = sql.substring(offset).trim();
int length = schema.length();
if (length > 0) {
if (schema.endsWith(";")) schema = schema.substring(0, schema.length() - 1);
schema = StringUtil.replaceChars(schema, "`", null);
length = schema.length();
if (schema.charAt(0) == '\'' && schema.charAt(length - 1) == '\'') {
schema = schema.substring(1, length - 1);
}
}
// 检查schema的有效性
FrontendPrivileges privileges = c.getPrivileges();
if (schema == null || !privileges.schemaExists(schema)) {
c.writeErrMessage(ErrorCode.ER_BAD_DB_ERROR, "Unknown database '" + schema + "'");
return;
}
String user = c.getUser();
if (!privileges.userExists(user, c.getHost())) {
c.writeErrMessage(
ErrorCode.ER_ACCESS_DENIED_ERROR, "Access denied for user '" + c.getUser() + "'");
return;
}
Set<String> schemas = privileges.getUserSchemas(user);
if (schemas == null || schemas.size() == 0 || schemas.contains(schema)) {
c.setSchema(schema);
ByteBuffer buffer = c.allocate();
c.write(c.writeToBuffer(OkPacket.OK, buffer));
} else {
String msg = "Access denied for user '" + c.getUser() + "' to database '" + schema + "'";
c.writeErrMessage(ErrorCode.ER_DBACCESS_DENIED_ERROR, msg);
}
}
@Test
public void test8() {
String oriSql =
" /* */ insert into employee(id,name,sharding_id) values(4, 'myhome', 10011) /**/";
String tableName = StringUtil.getTableName(oriSql);
Assert.assertEquals("employee", tableName);
}
@Test
public void test5() {
String oriSql =
" /* ApplicationName=DBeaver 3.3.1 - Main connection */ insert into employee(id,name,sharding_id) values(4, 'myhome', 10011)";
String tableName = StringUtil.getTableName(oriSql);
Assert.assertEquals("employee", tableName);
}
/**
* 寻找joinKey的索引
*
* @param columns
* @param joinKey
* @return -1表示没找到，>=0表示找到了
*/
private static int getJoinKeyIndex(List<SQLExpr> columns, String joinKey) {
for (int i = 0; i < columns.size(); i++) {
String col = StringUtil.removeBackquote(columns.get(i).toString()).toUpperCase();
if (col.equals(joinKey)) {
return i;
}
}
return -1;
}
@Test
public void test13() {
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
pw.println("insert into");
pw.println("employee(id,name,sharding_id) values(4, 'myhome', 10011)");
pw.flush();
String oriSql = sw.toString();
String tableName = StringUtil.getTableName(oriSql);
Assert.assertEquals("employee", tableName);
}
public static boolean processInsert(
SchemaConfig schema, int sqlType, String origSQL, ServerConnection sc)
throws SQLNonTransientException {
String tableName = StringUtil.getTableName(origSQL).toUpperCase();
TableConfig tableConfig = schema.getTables().get(tableName);
boolean processedInsert = false;
if (null != tableConfig && tableConfig.isAutoIncrement()) {
String primaryKey = tableConfig.getPrimaryKey();
processedInsert = processInsert(sc, schema, sqlType, origSQL, tableName, primaryKey);
}
return processedInsert;
}
private static RowDataPacket getRow(BackendConnection c, String charset) {
RowDataPacket row = new RowDataPacket(FIELD_COUNT);
if (c instanceof BackendAIOConnection) {
row.add(((BackendAIOConnection) c).getProcessor().getName().getBytes());
} else if (c instanceof JDBCConnection) {
row.add(((JDBCConnection) c).getProcessor().getName().getBytes());
} else {
row.add("N/A".getBytes());
}
row.add(LongUtil.toBytes(c.getId()));
long threadId = 0;
if (c instanceof MySQLConnection) {
threadId = ((MySQLConnection) c).getThreadId();
}
row.add(LongUtil.toBytes(threadId));
row.add(StringUtil.encode(c.getHost(), charset));
row.add(IntegerUtil.toBytes(c.getPort()));
row.add(IntegerUtil.toBytes(c.getLocalPort()));
row.add(LongUtil.toBytes(c.getNetInBytes()));
row.add(LongUtil.toBytes(c.getNetOutBytes()));
row.add(LongUtil.toBytes((TimeUtil.currentTimeMillis() - c.getStartupTime()) / 1000L));
row.add(c.isClosed() ? "true".getBytes() : "false".getBytes());
// boolean isRunning = c.isRunning();
// row.add(isRunning ? "true".getBytes() : "false".getBytes());
boolean isBorrowed = c.isBorrowed();
row.add(isBorrowed ? "true".getBytes() : "false".getBytes());
int writeQueueSize = 0;
String schema = "";
String charsetInf = "";
String txLevel = "";
String txAutommit = "";
if (c instanceof MySQLConnection) {
MySQLConnection mysqlC = (MySQLConnection) c;
writeQueueSize = mysqlC.getWriteQueue().size();
schema = mysqlC.getSchema();
charsetInf = mysqlC.getCharset() + ":" + mysqlC.getCharsetIndex();
txLevel = mysqlC.getTxIsolation() + "";
txAutommit = mysqlC.isAutocommit() + "";
}
row.add(IntegerUtil.toBytes(writeQueueSize));
row.add(schema.getBytes());
row.add(charsetInf.getBytes());
row.add(txLevel.getBytes());
row.add(txAutommit.getBytes());
return row;
}
public static LinkedHashMap<String, Object> loadLinkElements(Element parent) {
LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
NodeList children = parent.getChildNodes();
for (int i = 0; i < children.getLength(); i++) {
Node node = children.item(i);
if (node instanceof Element) {
Element e = (Element) node;
String name = e.getNodeName();
if ("property".equals(name)) {
String key = e.getAttribute("name");
NodeList nl = e.getElementsByTagName("bean");
if (nl.getLength() == 0) {
String value = e.getTextContent();
map.put(key, StringUtil.isEmpty(value) ? null : value.trim());
} else {
map.put(key, loadBean((Element) nl.item(0)));
}
}
}
}
return map;
}
public static boolean processERChildTable(
final SchemaConfig schema, final String origSQL, final ServerConnection sc)
throws SQLNonTransientException {
String tableName = StringUtil.getTableName(origSQL).toUpperCase();
final TableConfig tc = schema.getTables().get(tableName);
if (null != tc && tc.isChildTable()) {
final RouteResultset rrs = new RouteResultset(origSQL, ServerParse.INSERT);
String joinKey = tc.getJoinKey();
MySqlInsertStatement insertStmt =
(MySqlInsertStatement) (new MySqlStatementParser(origSQL)).parseInsert();
int joinKeyIndex = getJoinKeyIndex(insertStmt.getColumns(), joinKey);
if (joinKeyIndex == -1) {
String inf = "joinKey not provided :" + tc.getJoinKey() + "," + insertStmt;
LOGGER.warn(inf);
throw new SQLNonTransientException(inf);
}
if (isMultiInsert(insertStmt)) {
String msg = "ChildTable multi insert not provided";
LOGGER.warn(msg);
throw new SQLNonTransientException(msg);
}
String joinKeyVal = insertStmt.getValues().getValues().get(joinKeyIndex).toString();
String sql = insertStmt.toString();
// try to route by ER parent partion key
RouteResultset theRrs = RouterUtil.routeByERParentKey(sql, rrs, tc, joinKeyVal);
if (theRrs != null) {
rrs.setFinishedRoute(true);
sc.getSession2().execute(rrs, ServerParse.INSERT);
return true;
}
// route by sql query root parent's datanode
final String findRootTBSql = tc.getLocateRTableKeySql().toLowerCase() + joinKeyVal;
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("find root parent's node sql " + findRootTBSql);
}
ListenableFuture<String> listenableFuture =
MycatServer.getInstance()
.getListeningExecutorService()
.submit(
new Callable<String>() {
@Override
public String call() throws Exception {
FetchStoreNodeOfChildTableHandler fetchHandler =
new FetchStoreNodeOfChildTableHandler();
return fetchHandler.execute(
schema.getName(), findRootTBSql, tc.getRootParent().getDataNodes());
}
});
Futures.addCallback(
listenableFuture,
new FutureCallback<String>() {
@Override
public void onSuccess(String result) {
if (Strings.isNullOrEmpty(result)) {
StringBuilder s = new StringBuilder();
LOGGER.warn(
s.append(sc.getSession2()).append(origSQL).toString()
+ " err:"
+ "can't find (root) parent sharding node for sql:"
+ origSQL);
sc.writeErrMessage(
ErrorCode.ER_PARSE_ERROR,
"can't find (root) parent sharding node for sql:" + origSQL);
return;
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(
"found partion node for child table to insert " + result + " sql :" + origSQL);
}
RouteResultset executeRrs = RouterUtil.routeToSingleNode(rrs, result, origSQL);
sc.getSession2().execute(executeRrs, ServerParse.INSERT);
}
@Override
public void onFailure(Throwable t) {
StringBuilder s = new StringBuilder();
LOGGER.warn(
s.append(sc.getSession2()).append(origSQL).toString() + " err:" + t.getMessage());
sc.writeErrMessage(ErrorCode.ER_PARSE_ERROR, t.getMessage() + " " + s.toString());
}
},
MycatServer.getInstance().getListeningExecutorService());
return true;
}
return false;
}
@Test
public void test4() {
String oriSql = "INSERT INTO test_activity_input (id,vip_no";
String tableName = StringUtil.getTableName(oriSql);
Assert.assertEquals("test_activity_input", tableName);
}
@Test
public void test3() {
String oriSql = " insert into isd(id) values (s)";
String tableName = StringUtil.getTableName(oriSql);
Assert.assertEquals("isd", tableName);
}