(iv) Focus on Wechat Public Number: [Taro Back-end Cabin] Welfare:
RocketMQ/MyCAT/Sharing-JDBC All Source Analysis Articles List
RocketMQ/MyCAT/Sharing-JDBC Chinese Annotation Source GitHub Address
Every message you leave about the source code will be answered carefully. Even if you don't know how to read the source code, you can ask.
New source parsing articles are notified in real time. Update one article per week or so.
1. Overview
Maybe you'll be surprised to see this heading. MyCAT can use MongoDB as a data node. Yes, yes, indeed.
Let's start this magical journey by roaring.
This paper is mainly divided into four parts:
Overall process, let you have a holistic understanding
Query operation
Insert operation
Colored Eggs, (vii) Colored Eggs, (vii) Colored Eggs
I suggest you read these two articles (not necessary):
2. Mainstream process
MyCAT Server receives MySQL Client's request based on MySQL protocol, translates SQL into MongoDB operation and sends it to MongoDB Server.
MyCAT Server receives MongoDB data returned by MongoDB Server, translates it into MySQL data and returns it to MySQL Client.
In this way, does MyCAT connect to MongoDB a little less magical?
Java Database Connectivity (JDBC) is an application program interface used in Java language to standardize how client programs access the database, providing methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. JDBC is oriented to relational database.
MyCAT uses the JDBC specification to abstract access to MongoDB. In this way, MyCAT also abstracts access to SequoiaDB. Maybe it's a bit abstract. Look at the class diagram and you'll be shocked.
Is it a familiar taste? I have to say that the JDBC specification is exquisite.
3. Query operation
SELECT id, name FROM user WHERE name > '' ORDER BY _id DESC;
It's easy to understand the whole logic by looking at the sequence diagram, so I won't talk much nonsense. Let's look at some core code logic.
1. Query MongoDB
// MongoSQLParser.java public MongoData query() throws MongoSQLException { if (!(statement instanceof SQLSelectStatement)) { //return null; throw new IllegalArgumentException("not a query sql statement"); } MongoData mongo = new MongoData(); DBCursor c = null; SQLSelectStatement selectStmt = (SQLSelectStatement) statement; SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery(); int icount = 0; if (sqlSelectQuery instanceof MySqlSelectQueryBlock) { MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery(); BasicDBObject fields = new BasicDBObject(); // Display (return) fields for (SQLSelectItem item : mysqlSelectQuery.getSelectList()) { //System.out.println(item.toString()); if (!(item.getExpr() instanceof SQLAllColumnExpr)) { if (item.getExpr() instanceof SQLAggregateExpr) { SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr(); if (expr.getMethodName().equals("COUNT")) { // TODO Read: count (*) icount = 1; mongo.setField(getExprFieldName(expr), Types.BIGINT); } fields.put(getExprFieldName(expr), 1); } else { fields.put(getFieldName(item), 1); } } } // Table name SQLTableSource table = mysqlSelectQuery.getFrom(); DBCollection coll = this._db.getCollection(table.toString()); mongo.setTable(table.toString()); // WHERE SQLExpr expr = mysqlSelectQuery.getWhere(); DBObject query = parserWhere(expr); // GROUP BY SQLSelectGroupByClause groupby = mysqlSelectQuery.getGroupBy(); BasicDBObject gbkey = new BasicDBObject(); if (groupby != null) { for (SQLExpr gbexpr : groupby.getItems()) { if (gbexpr instanceof SQLIdentifierExpr) { String name = ((SQLIdentifierExpr) gbexpr).getName(); gbkey.put(name, Integer.valueOf(1)); } } icount = 2; } // SKIP / LIMIT int limitoff = 0; int limitnum = 0; if (mysqlSelectQuery.getLimit() != null) { limitoff = getSQLExprToInt(mysqlSelectQuery.getLimit().getOffset()); limitnum = getSQLExprToInt(mysqlSelectQuery.getLimit().getRowCount()); } if (icount == 1) { // COUNT(*) mongo.setCount(coll.count(query)); } else if (icount == 2) { // MapReduce BasicDBObject initial = new BasicDBObject(); initial.put("num", 0); String reduce = "function (obj, prev) { " + " prev.num++}"; mongo.setGrouyBy(coll.group(gbkey, query, initial, reduce)); } else { if ((limitoff > 0) || (limitnum > 0)) { c = coll.find(query, fields).skip(limitoff).limit(limitnum); } else { c = coll.find(query, fields); } // order by SQLOrderBy orderby = mysqlSelectQuery.getOrderBy(); if (orderby != null) { BasicDBObject order = new BasicDBObject(); for (int i = 0; i < orderby.getItems().size(); i++) { SQLSelectOrderByItem orderitem = orderby.getItems().get(i); order.put(orderitem.getExpr().toString(), getSQLExprToAsc(orderitem.getType())); } c.sort(order); // System.out.println(order); } } mongo.setCursor(c); } return mongo; }
2. Query Conditions
// MongoSQLParser.java private void parserWhere(SQLExpr aexpr, BasicDBObject o) { if (aexpr instanceof SQLBinaryOpExpr) { SQLBinaryOpExpr expr = (SQLBinaryOpExpr) aexpr; SQLExpr exprL = expr.getLeft(); if (!(exprL instanceof SQLBinaryOpExpr)) { if (expr.getOperator().getName().equals("=")) { o.put(exprL.toString(), getExpValue(expr.getRight())); } else { String op = ""; if (expr.getOperator().getName().equals("<")) { op = "$lt"; } else if (expr.getOperator().getName().equals("<=")) { op = "$lte"; } else if (expr.getOperator().getName().equals(">")) { op = "$gt"; } else if (expr.getOperator().getName().equals(">=")) { op = "$gte"; } else if (expr.getOperator().getName().equals("!=")) { op = "$ne"; } else if (expr.getOperator().getName().equals("<>")) { op = "$ne"; } parserDBObject(o, exprL.toString(), op, getExpValue(expr.getRight())); } } else { if (expr.getOperator().getName().equals("AND")) { parserWhere(exprL, o); parserWhere(expr.getRight(), o); } else if (expr.getOperator().getName().equals("OR")) { orWhere(exprL, expr.getRight(), o); } else { throw new RuntimeException("Can't identify the operation of of where"); } } } } private void orWhere(SQLExpr exprL, SQLExpr exprR, BasicDBObject ob) { BasicDBObject xo = new BasicDBObject(); BasicDBObject yo = new BasicDBObject(); parserWhere(exprL, xo); parserWhere(exprR, yo); ob.put("$or", new Object[]{xo, yo}); }
3. Parsing MongoDB data
// MongoResultSet.java public MongoResultSet(MongoData mongo, String schema) throws SQLException { this._cursor = mongo.getCursor(); this._schema = schema; this._table = mongo.getTable(); this.isSum = mongo.getCount() > 0; this._sum = mongo.getCount(); this.isGroupBy = mongo.getType(); if (this.isGroupBy) { dblist = mongo.getGrouyBys(); this.isSum = true; } if (this._cursor != null) { select = _cursor.getKeysWanted().keySet().toArray(new String[0]); // Analyzing fields if (this._cursor.hasNext()) { _cur = _cursor.next(); if (_cur != null) { if (select.length == 0) { SetFields(_cur.keySet()); } _row = 1; } } // Setting field type if (select.length == 0) { select = new String[]{"_id"}; SetFieldType(true); } else { SetFieldType(false); } } else { SetFields(mongo.getFields().keySet());//new String[]{"COUNT(*)"}; SetFieldType(mongo.getFields()); } }
When using SELECT * to query fields, fields use the fields returned from the first data. Even if the following data has other fields, it will not be returned.
4. Return data to MySQL Client
// JDBCConnection.java private void ouputResultSet(ServerConnection sc, String sql) throws SQLException { ResultSet rs = null; Statement stmt = null; try { stmt = con.createStatement(); rs = stmt.executeQuery(sql); // header List<FieldPacket> fieldPks = new LinkedList<>(); ResultSetUtil.resultSetToFieldPacket(sc.getCharset(), fieldPks, rs, this.isSpark); int colunmCount = fieldPks.size(); ByteBuffer byteBuf = sc.allocate(); ResultSetHeaderPacket headerPkg = new ResultSetHeaderPacket(); headerPkg.fieldCount = fieldPks.size(); headerPkg.packetId = ++packetId; byteBuf = headerPkg.write(byteBuf, sc, true); byteBuf.flip(); byte[] header = new byte[byteBuf.limit()]; byteBuf.get(header); byteBuf.clear(); List<byte[]> fields = new ArrayList<byte[]>(fieldPks.size()); for (FieldPacket curField : fieldPks) { curField.packetId = ++packetId; byteBuf = curField.write(byteBuf, sc, false); byteBuf.flip(); byte[] field = new byte[byteBuf.limit()]; byteBuf.get(field); byteBuf.clear(); fields.add(field); } // header eof EOFPacket eofPckg = new EOFPacket(); eofPckg.packetId = ++packetId; byteBuf = eofPckg.write(byteBuf, sc, false); byteBuf.flip(); byte[] eof = new byte[byteBuf.limit()]; byteBuf.get(eof); byteBuf.clear(); this.respHandler.fieldEofResponse(header, fields, eof, this); // row while (rs.next()) { RowDataPacket curRow = new RowDataPacket(colunmCount); for (int i = 0; i < colunmCount; i++) { int j = i + 1; if (MysqlDefs.isBianry((byte) fieldPks.get(i).type)) { curRow.add(rs.getBytes(j)); } else if (fieldPks.get(i).type == MysqlDefs.FIELD_TYPE_DECIMAL || fieldPks.get(i).type == (MysqlDefs.FIELD_TYPE_NEW_DECIMAL - 256)) { // field type is unsigned byte // ensure that do not use scientific notation format BigDecimal val = rs.getBigDecimal(j); curRow.add(StringUtil.encode(val != null ? val.toPlainString() : null, sc.getCharset())); } else { curRow.add(StringUtil.encode(rs.getString(j), sc.getCharset())); } } curRow.packetId = ++packetId; byteBuf = curRow.write(byteBuf, sc, false); byteBuf.flip(); byte[] row = new byte[byteBuf.limit()]; byteBuf.get(row); byteBuf.clear(); this.respHandler.rowResponse(row, this); } fieldPks.clear(); // row eof eofPckg = new EOFPacket(); eofPckg.packetId = ++packetId; byteBuf = eofPckg.write(byteBuf, sc, false); byteBuf.flip(); eof = new byte[byteBuf.limit()]; byteBuf.get(eof); sc.recycle(byteBuf); this.respHandler.rowEofResponse(eof, this); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } } } } // MongoResultSet.java @Override public String getString(String columnLabel) throws SQLException { Object x = getObject(columnLabel); if (x == null) { return null; } return x.toString(); }
When the return field value is Object, the object. toString() is returned. For example:
mysql> select * from user order by _id asc; +--------------------------+------+-------------------------------+ | _id | name | profile | +--------------------------+------+-------------------------------+ | 1 | 123 | { "age" : 1 , "height" : 100} |
4. Insert operation
// MongoSQLParser.java public int executeUpdate() throws MongoSQLException { if (statement instanceof SQLInsertStatement) { return InsertData((SQLInsertStatement) statement); } if (statement instanceof SQLUpdateStatement) { return UpData((SQLUpdateStatement) statement); } if (statement instanceof SQLDropTableStatement) { return dropTable((SQLDropTableStatement) statement); } if (statement instanceof SQLDeleteStatement) { return DeleteDate((SQLDeleteStatement) statement); } if (statement instanceof SQLCreateTableStatement) { return 1; } return 1; } private int InsertData(SQLInsertStatement state) { if (state.getValues().getValues().size() == 0) { throw new RuntimeException("number of columns error"); } if (state.getValues().getValues().size() != state.getColumns().size()) { throw new RuntimeException("number of values and columns have to match"); } SQLTableSource table = state.getTableSource(); BasicDBObject o = new BasicDBObject(); int i = 0; for (SQLExpr col : state.getColumns()) { o.put(getFieldName2(col), getExpValue(state.getValues().getValues().get(i))); i++; } DBCollection coll = this._db.getCollection(table.toString()); coll.insert(o); return 1; }
5. Eggs
Old iron, see here, a wave of WeChat public attention?
1. Support multi-MongoDB and use MyCAT to segment.
MyCAT configuration: multi_mongodb
2. Supporting MongoDB + MySQL as the data node of the same MyCAT Table. When querying, data results can be merged.
When querying, the return MySQL data record field is more complete than MongoDB data record field, otherwise, the merge result will be wrong.
MyCAT configuration: single_mongodb_mysql
3. When MongoDB is a data node, it can use the database primary key field function provided by MyCAT.
MyCAT configuration: single_mongodb