Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to carry out database middleware MyCAT source code analysis, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Overview

You may be a little surprised to see this title. MyCAT can use MongoDB as a data node. Yes, yes, you can.

Ho ho ho, let's start this magical journey.

This paper is divided into four parts:

Overall process, so that you have an overall understanding

Query operation

Insert operation

two。 Main process

MyCAT Server receives a request from MySQL Client based on MySQL protocol, translates SQL into MongoDB operation and sends it to MongoDB Server.

MyCAT Server receives the MongoDB data returned by MongoDB Server, translates it into MySQL data and returns it to MySQL Client.

From this point of view, MyCAT connection MongoDB is not less magical column.

Java database connection (Java Database Connectivity, referred to as JDBC) is an application program interface used in Java language to regulate how the client program accesses the database. It provides methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. JDBC is oriented to relational databases.

MyCAT uses the JDBC specification to abstract access to MongoDB. In this way, MyCAT also abstracts SequoiaDB access. Perhaps this statement is a bit abstract, looking at a class diagram to depress surprise.

Is it a familiar smell. I have to say that the JDBC specification is exquisite.

3. Query operation

SELECT id, name FROM user WHERE name >''ORDER BY _ id DESC

Look at the sequence diagram has been very convenient to understand the overall logic, I will not talk much nonsense. Let's take a 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) field 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 to 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) } / / orderby 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、查询条件 // 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 = "$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、解析 MongoDB 数据 // 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]) / / parse fields if (this._cursor.hasNext ()) {_ cur = _ cursor.next (); if (_ cur! = null) {if (select.length = = 0) {SetFields (_ cur.keySet ());} _ row = 1 }} / / set fields 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 the SELECT * query field, fields uses the fields returned by * 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 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 fields = new ArrayList (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(); } 当返回字段值是 Object 时，返回该对象.toString()。例如： 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)); iTunes;} DBCollection coll = this._db.getCollection (table.toString ()); coll.insert (o); return 1 } about how to carry on the database middleware MyCAT source code analysis to share here, hoped that the above content can have the certain help to everybody, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.