きっかけ
DBプロトコル
・MySQL
・Postgres
SELECTを送り、ResultSetが戻ってくるまでの部分
JDBC
・Types(1,2,3,4)
・Version
ChromeAppsではJSで直接TCP叩けると聞き
Postgresのドライバ書いて遊んでいた
(Trust認証のみ、SSLは非対応)
なお、ChromeAppsは廃止予定のもよう
tag 1byte
length 4byte
body
51 00 00 00 19 53 45 4c 45 43 54 20 2a 20 46 52
4f 4d 20 42 4f 4f 4b 53 3b 00
SELECT * FROM BOOKS;
をpsqlから実行した時のパケット
51
00 00 00 19
53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 42 4f 4f 4b 53 3b 00
= 'Q'
= 25
SELECT * FROM BOOKS;<NULL>
(6 + 1 + 1 + 1 + 4 + 1 + 5 + 1+ 1= 21)
http://www.utf8-chartable.de/
メタデータ (全カラム分)
tag: 'T'
54 00 00 00 4b 00 03 69 64 00 00 00 40 0e 00 01
00 00 00 17 00 04 ff ff ff ff 00 00 6e 61 6d 65
00 00 00 40 0e 00 02 00 00 00 19 ff ff ff ff ff
ff 00 00 61 75 74 68 65 72 00 00 00 40 0e 00 03
00 00 00 19 ff ff ff ff ff ff 00 00
PostgreSQL
Type: Row description
Length: 75
Field count: 3
Column name: id
Table OID: 16398
Column index: 1
Type OID: 23
Column length: 4
Type modifier: -1
Format: Text (0)
Column name: name
Column name: auther
44 00 00 00 3f 00 03 00 00 00 01 31 00 00 00 1e
52 65 2d 45 6e 67 69 6e 65 65 72 69 6e 67 20 4c
65 67 61 63 79 20 53 6f 66 74 77 61 72 65 00 00
00 0e 43 68 72 69 73 20 42 69 72 63 68 61 6c 6c
PostgreSQL
Type: Data row
Length: 63
Field count: 3
Column length: 1
Data: 31
Column length: 30
Data: 52652d456e67696e656572696e67204c656761637920536f...
Column length: 14
Data: 4368726973204269726368616c6c
43 00 00 00 0d 53 45 4c 45 43 54 20 33 00
PostgreSQL
Type: Command completion
Length: 13
Tag: SELECT 3
5a 00 00 00 05 49
PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
seq 1byte
length 3byte
body
14 00 00 00 03 53 45 4c 45 43 54 20 2a 20 46 52
4f 4d 20 42 4f 4f 4b 53
SELECT * FROM BOOKS;
をmysqlから実行した時のパケット
14 00 00
00
53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 42 4f 4f 4b 53
= 20 (little endian)
= 0 (SEQ)
SELECT * FROM BOOKS
(6 + 1 + 1 + 1 + 4 + 1 + 5= 19)
セミコロンとNULL文字含まない
03
= type query
Postgresと異なり、
パケット単体で種別の判別は厳しい
カラム数
01 00 00 01 03
MySQL Protocol
Packet Length: 1
Packet Number: 1
Number of fields: 3
28 00 00 02 03 64 65 66 04 74 65 73 74 05 42 4f
4f 4b 53 05 42 4f 4f 4b 53 02 69 64 02 69 64 0c
3f 00 0b 00 00 00 03 03 42 00 00 00
MySQL Protocol
Packet Length: 40
Packet Number: 2
Catalog: def
Database: test
Table: BOOKS
Original table: BOOKS
Name: id
Original name: id
Charset number: binary COLLATE binary (63)
Length: 11
Type: FIELD_TYPE_LONG (3)
Flags: 0x4203
.... .... .... ...1 = Not null: Set
.... .... .... ..1. = Primary key: Set
.... .... .... .0.. = Unique key: Not set
.... .... .... 0... = Multiple key: Not set
.... .... ...0 .... = Blob: Not set
.... .... ..0. .... = Unsigned: Not set
.... .... .0.. .... = Zero fill: Not set
.... .... 0... .... = Binary: Not set
.... ...0 .... .... = Enum: Not set
.... ..1. .... .... = Auto increment: Set
.... .0.. .... .... = Timestamp: Not set
.... 0... .... .... = Set: Not set
Decimals: 0
30 00 00 05 01 31 1e 52 65 2d 45 6e 67 69 6e 65
65 72 69 6e 67 20 4c 65 67 61 63 79 20 53 6f 66
74 77 61 72 65 0e 43 68 72 69 73 20 42 69 72 63
68 61 6c 6c
MySQL Protocol
Packet Length: 48
Packet Number: 5
Catalog: 1
Database: Re-Engineering Legacy Software
Table: Chris Birchall
このWireSharkの解析は嘘。
PackertNumber以降は、以下の形式の繰り返し。
[len byte][char ...] ...
07 00 00 08 fe 00 00 22 00 00 00
MySQL Protocol
Packet Length: 7
Packet Number: 8
EOF marker: 254
Warnings: 0
Server Status: 0x0022
Payload: 0000
Body部が fe で始まるパケット
package com.mysql.jdbc;
...
public class Buffer {
...
public static final short TYPE_ID_EOF = 0xFE;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM BOOKS");
while ( rs.next() ) {
resultSet.getString(0);
}
ResultSetMetaData meta = rs.getMetaData();
こういう単純なクエリならば
接続先が何だろうと同じように動く
SQLの方言の吸収はORマッパー、ライブラリの仕事
ODBCドライバをJNI経由で呼び出す
OpenDataBaseConnectivity
C言語用のDBアクセス統一API
sun.jdbc.odbc.jdbcodbcdriver
JDK8から同梱されなくなった模様
各種DBの違いはODBCで吸収されるため、事実上これが唯一の実装
DB固有ネイティブドライバをJNI経由で呼び出す
Oracle OCIドライバ
IBM DB2 にも存在したが、廃止された模様
実行環境にクライアントライブラリが
インストールされている必要がある
DBに直接アクセスする中間サーバがまずあり、
そのサーバにより変換されたプロトコルを
PureJava実装されたクライアントドライバで解釈する
DB2
com.ibm.db2.jdbc.net.DB2Driver
Java アプレット用に設計された物らしい
忘れていいと思います
PureJavaでDBに直接アクセス
(ネットワーク java.net.Socketなど)
送信パケット構築
受信パケット解析
全てJavaで実装
MySQL, PostgreSQL, Oracle, MSSQL...
private void sendSimpleQuery(SimpleQuery query, SimpleParameterList params) throws IOException {
String nativeSql = query.toString(params);
LOGGER.log(Level.FINEST, " FE=> SimpleQuery(query=\"{0}\")", nativeSql);
Encoding encoding = pgStream.getEncoding();
byte[] encoded = encoding.encode(nativeSql);
pgStream.sendChar('Q');
pgStream.sendInteger4(encoded.length + 4 + 1);
pgStream.send(encoded);
pgStream.sendChar(0);
pgStream.flush();
pendingExecuteQueue.add(new ExecuteRequest(query, null, true));
pendingDescribePortalQueue.add(query);
}
final ResultSetInternalMethods sqlQueryDirect(StatementImpl callingStatement, String query, String characterEncoding, Buffer queryPacket, int maxRows,
int resultSetType, int resultSetConcurrency, boolean streamResults, String catalog, Field[] cachedMetadata) throws Exception {
this.statementExecutionDepth++;
try {
if (this.statementInterceptors != null) {
ResultSetInternalMethods interceptedResults = invokeStatementInterceptorsPre(query, callingStatement, false);
if (interceptedResults != null) {
return interceptedResults;
}
}
long queryStartTime = 0;
long queryEndTime = 0;
String statementComment = this.connection.getStatementComment();
if (this.connection.getIncludeThreadNamesAsStatementComment()) {
statementComment = (statementComment != null ? statementComment + ", " : "") + "java thread: " + Thread.currentThread().getName();
}
if (query != null) {
// We don't know exactly how many bytes we're going to get from the query. Since we're dealing with Unicode, the max is 2, so pad it
// (2 * query) + space for headers
int packLength = HEADER_LENGTH + 1 + (query.length() * 3) + 2;
byte[] commentAsBytes = null;
if (statementComment != null) {
commentAsBytes = StringUtils.getBytes(statementComment, null, characterEncoding, this.connection.getServerCharset(),
this.connection.parserKnowsUnicode(), getExceptionInterceptor());
packLength += commentAsBytes.length;
packLength += 6; // for /*[space] [space]*/
}
if (this.sendPacket == null) {
this.sendPacket = new Buffer(packLength);
} else {
this.sendPacket.clear();
}
this.sendPacket.writeByte((byte) MysqlDefs.QUERY); // == 3 /////////////
if (commentAsBytes != null) {
this.sendPacket.writeBytesNoNull(Constants.SLASH_STAR_SPACE_AS_BYTES);
this.sendPacket.writeBytesNoNull(commentAsBytes);
this.sendPacket.writeBytesNoNull(Constants.SPACE_STAR_SLASH_SPACE_AS_BYTES);
}
if (characterEncoding != null) {
if (this.platformDbCharsetMatches) {
this.sendPacket.writeStringNoNull(query, characterEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(),
this.connection);
} else {
if (StringUtils.startsWithIgnoreCaseAndWs(query, "LOAD DATA")) {
this.sendPacket.writeBytesNoNull(StringUtils.getBytes(query));
} else {
this.sendPacket.writeStringNoNull(query, characterEncoding, this.connection.getServerCharset(),
this.connection.parserKnowsUnicode(), this.connection);
}
}
} else {
this.sendPacket.writeStringNoNull(query);
}
queryPacket = this.sendPacket;
}
ネット上のサンプルはこれが意外と多い
Class.forName(DriverName) 不要化
(META-INF/services/java.sql.Driver)
try-with-resources 対応
Connection,Statement,ResultSetなどが
AutoCloseableになった
Postgresドライバでアクセスできる
=
Postgresと同じ規則でTCPパケットを入出力
方式的にはJDBCでいうType2みたい
libpq.so, libmysqlclient を参照していた