手写SQL词法分析

要写一个词法分析，首先是要对一段 sql 进行解析，然后将其解析为一个一个的 token.

每个 token 是都特定含义的，固定义 token 结构如下：

/**
* token for sql.
*/
public final class SQLToken {
    // 可能称为类型更合适些, 用于标识解析出来的 token 的类型.
    // 比如 select, insert, 字符串, id 等.
    private final int value;
    // token 在 sql 中的偏移量
    private final int offset;
    // token 的长度
    private final int length;
    private String name; // 用于字符串.

    //用于构造带引号的字符串
    public SQLToken(int value, int offsetStart, int offsetEnd, String name) {
        this.value = value;
        this.offset = offsetStart;
        this.length = offsetEnd - offsetStart;
        this.name = name;
    }

    public SQLToken(int value, int offsetStart, int offsetEnd) {
        this.value = value;
        this.offset = offsetStart;
        this.length = offsetEnd - offsetStart;
    }

    // 返回 token 的类型
    public int getValue() {
        return value;
    }

    // 返回 token 的值.
    public String getName(char[] sql) {
        if(null != this.name) return this.name;
        return new String(sql, this.offset, this.length);
    }
}

定义了 token 结构后，下一步要做的工作是解析 sql. 思路是首先读取一个 char，然后判断是否是注释，如果是注释，则直接跳过，如果不是注释，则进一步判断是否是字符串，判断是否是小数，最后处理 identifier.

实现如下：



public static List<SQLToken> parseSQL(char[] sql) throws SQLException {
        SearchNode node = searchTree;
        ArrayList tokens = new ArrayList();
        int value = 0;
        int tokenStart = 0;
        boolean wasWhiteSpace = true;
        int comment = NOT_COMMENT;
        char quote = 0; //引号
        StringBuffer quoteBuffer = new StringBuffer();

        for(int i=0; i<sql.length; i++){
            char c = sql[i];
            switch(c){
                case '\"':
                case '\'':
                    // 如果是注释, 就直接跳过.
                    if (comment != NOT_COMMENT) {
                        break;
                    }else if(quote == 0){
                        // 将第一次遇到的单引号或双引号赋值给 quote.
                        quote = c;
                    }else if(quote == c){
                        // check on escaped quote
                        // 这一段代码是为了解决单引号和双引号作为字符串中的字符这种情况.
                        // 例如 insert into Person values('xh'', 'xh'''). 这时候, 通过如下这段代码就会把第一个、第二个
                        // ' 当成字符串中的字符.
                        if(i+1<sql.length && sql[i+1] == quote){
                            quoteBuffer.append(quote);
                            i++;
                        }else{
                            // 如果不是上述情况的话, 那么应该就要构造字符串了.
                            // quoteBuffer 就是在 default 处处理的字符.
                            // 这里我我估计该作者认为, 不管是数字还是字符串, 只要是以 ' 结尾的话, 那么就是字符串, 否则
                            // 就是 id.
                            tokens.add( new SQLToken((quote == '\'') ? STRING : IDENTIFIER, tokenStart, i+1, quoteBuffer.toString()));
                            quoteBuffer.setLength(0);
                            quote = 0;
                            tokenStart = i+1;
                            wasWhiteSpace = true;
                        }
                        // 这里还有一个单双引号的问题, 假设如果开始进入的时候是 ", 后来进入的时候是 ',
                        // 那么此时这段代码就能发挥作用了.
                    }else quoteBuffer.append(c);
                    break;
                case '.':
                    // 如果是注释, 就直接跳过.
                    if (comment != NOT_COMMENT) {
                        break;
                    }else if(quote == 0){
                        // there are follow cases with a point
                        // "abc"."abc" --> identifier --> multiple tokens
                        // "5"."3" --> identifier --> multiple tokens
                        // 5.3 --> number --> one token
                        // 5.e3 --> number --> one token
                        // .3 --> number --> one token
                        // .e3 --> identifier --> multiple tokens
                        int k=tokenStart;
                        // 当第一个字符就为 . 时: 检查后续是否为数值.
                        if(k == i){ // point is first character
                            if(sql.length> k+1){
                                char cc = sql[k+1];
                                if((cc >= '0') && cc <= '9') break; // is a number --> break
                            }
                        }else{
                            for(; k<i; k++){
                                char cc = sql[k];
                                if((cc != '-' && cc != '$' && cc < '0') || cc > '9') break; // is identifier --> break
                            }
                            if(k>=i) break; // preceding tokens are only digits that it is not an identifier else a floating number
                        }
                    }
                    // character before is not a digit that it is an identifier
                    // no break;
                case '-':
                    // 如果是注释, 就直接跳过.
                    if (comment != NOT_COMMENT) {
                        break;
                    }
/* start of single line comment */
                    // 如果是单行注释.
                    else if (c == '-' && (i+1 < sql.length) && (sql[i+1] == '-')) {
                        if(!wasWhiteSpace){
                            tokens.add( new SQLToken( value, tokenStart, i) );
                            value = 0;
                        }
                        i++;
                        tokenStart = i+1;
                        // 将 comment 转成单行注释.
                        comment = LINE_COMMENT;
                    }
                    else if(quote == 0 && !wasWhiteSpace){
                        char c1 = sql[tokenStart];
                        char cx = sql[i-1];
                        if(((c1 >= '0' && c1 <= '9') || c1 == '.') && (cx == 'e' || cx == 'E'))
                            //negative exponential number
                            break;
                        if(c1 == '$' && tokenStart+1 == i)
                            // money number
                            break;
                    }
                case ' ':
                case '\t':
                case '\n':
                case '\r':
                case ',':
                case '(':
                case ')':
                case '{':
                case '}':
                case '*':
                case '+':
                case '/':
                case '%':
                case '&':
                case '|':
                case '=':
                case '<':
                case '>':
                case '?':
                case '^':
                case '~':
                /* end of line comment */
                    if (comment == LINE_COMMENT) {
                        // '\r'/'\n' check needed because of fall-through
                        // 当换到下一行时, 需要重置 comment 为 NOT_COMMENT.
                        if (c == '\r' || c == '\n') {
                            comment = NOT_COMMENT;
                            wasWhiteSpace = true;
                        }
                        tokenStart = i+1;
                        break;
                    }
                /* end of multi-line comment */
                    else if (comment == MULTI_COMMENT) {
                        // '*' check needed because of fall-through
                        // 当遇到 */ 时, 需要将 comment 重置为 NOT_COMMENT.
                        if (c == '*' && (i+1 < sql.length) && (sql[i+1] == '/')) {
                            comment = NOT_COMMENT;
                            wasWhiteSpace = true;
                            i++;
                        }
                        tokenStart = i + 1;
                        break;
                    }
                    else if(quote == 0){
                        // 这里是将字符串分割成一个一个的 Token. 同时将 value 置为 0.
                        if(!wasWhiteSpace){
                            tokens.add( new SQLToken( value, tokenStart, i) );
                            value = 0;
                        }
                        switch(c){
                            case ' ':
                            case '\t':
                            case '\n':
                            case '\r':
                                // skip this characters, this are not tokens, this are only source formatter
                                // 跳过 ' '、'\t'、'\n'、'\r' 这些字符, 因为这些是源输入的格式符.
                                break;
                            case '<':
                                if((i+1 < sql.length) && (sql[i+1] == '>')){
                                    tokens.add( new SQLToken( UNEQUALS, i, i+2) );
                                    i++;
                                    break;
                                }
                            case '>':
                                if((i+1 < sql.length) && (sql[i+1] == '=')){
                                    tokens.add( new SQLToken( 100 + c, i, i+2) );
                                    i++;
                                    break;
                                }
                                    /* start of multi-line comment */
                            case '/':
                                // 这种情况是多行注释.
                                if((i+1 < sql.length) && (sql[i+1] == '*')){
                                    i++;
                                    tokenStart = i+1;
                                    comment = MULTI_COMMENT;
                                    break;
                                }
                            default:
                                // 在这里就可以处理类似于 () 等字符.
                                tokens.add( new SQLToken( c, i, i+1) );
                        }
                        wasWhiteSpace = true;
                        tokenStart = i+1;
                    }else{
                        quoteBuffer.append(c);
                    }
                    break;
                default:
                    // 这里处理正常字符串的逻辑.
                    if (comment != NOT_COMMENT) {
                        break;
                    }else if(quote == 0){
                        if(wasWhiteSpace){
                            // 当出现空格的时候, 说明是一个新的字符, 所有这里需要重新将 node 赋值为 searchTree.
                            node = searchTree;
                        }else{
                            // 当 node 为空的时候，就是出现了 searchTree 中没有出现的字符, 这里同样是跳过该字符.
                            // 这里将 wasWhiteSpace 赋值为 false, 就是为了避免 node 被重新初始化为 searchTree.
                            if(node == null){
                                value = 0;
                                wasWhiteSpace = false;
                                break;
                            }
                        }
                        // 将所有的字符全部转换为小写.
                        c |= 0x20; // case insensitive
                        // 从 searchTree 中找到该字符开始的 nextEntry.
                        while(node != null && node.letter != c) node = node.nextEntry;
                        if(node != null){
                            // 这里是找到了那个 node. 假设执行的是 drop table Person 语句的话. 此时: value = 0
                            // node = e.
                            value = node.value;
                            node = node.nextLetter;
                        }else{
                            // 如果没有找到的话，将 value 赋值为 0, 同时 node 置为空. 目的是为了跳过后面的检查.
                            value = 0;
                            node = null;
                        }
                    }else{
                        quoteBuffer.append(c);
                    }
                    // 执行完后, 将 wasWhiteSpace 赋值为 false, 防止 node 被重新初始化为 searchTree.
                    wasWhiteSpace = false;
                    break;
            }
        }
        // 如果 comment 还等于 MULTI_COMMENT 它的话, 说明这没有被重置了, 即: 还没有遇到 */ 也就是注释没有关闭.
        if (comment == MULTI_COMMENT) {
            throw SmallDBException.create(Language.STXADD_COMMENT_OPEN);
        }

        // 如果最后 wasWhiteSpace 不是另一个单词的开始的话, 应该要将最后一个 SQLToken 构造出来.
        if(!wasWhiteSpace) {
            tokens.add( new SQLToken( value, tokenStart, sql.length) );
        }

        return tokens;
    }

上面一段中，其实还有更好的写法，按照空格分隔(将 SearchNode 的搜索过程转换成从 keywords 中获取 token 的类型)，然后去 keywords 集合中匹配关键字，这样效率会更高.