手写sqlite数据库Part 2——手写SQL编译器和虚拟机

 🌈hello，你好鸭，我是Ethan，西安电子科技大学大三在读，很高兴你能来阅读。

✔️目前博客主要更新Java系列、项目案例、计算机必学四件套等。
🏃人生之义，在于追求，不在成败，勤通大道。加油呀！

🔥个人主页：Ethan Yankang
🔥推荐：史上最强八股文 || 一分钟看完我的上千篇博客

🔥温馨提示：划到文末发现专栏彩蛋   点击这里直接传送

🔥本篇概览：数据库的前世今生 || 详细讲解了手写sqlite数据库Part 2——手写SQL编译器和虚拟机。🌈⭕🔥

---

【计算机领域一切迷惑的源头都是基本概念的模糊，算法除外】

---

🌈序言

数据库乃我长久之志也，此关必过。今日既得之方向，应按此路学之习之，而长久不可懈怠。

---

🔥 手写类sqlite数据库系列

🔥 手写底层系列

---

1.背景：

sqlite的“前端”是一个SQL编译器，它解析字符串并输出一个称为字节码的内部表示。这个字节码被传递给虚拟机，虚拟机执行它。

我们手写的sqlite的架构

---

这段字节码被传递给虚拟机，虚拟机执行它。

---

像这样把事情分成两个步骤有几个好处：

• 降低每个部分的复杂性（例如，虚拟机不担心语法错误）
• 允许编译一次常见查询并缓存字节码以提高性能

---

考虑到这一点，让我们重构我们的主要功能并在过程中支持两个新关键字：insert、select

---

2.命令分类        

1.元命令

像.exit之类，是非SQL语句。它们都以点开头，所以我们检查它们并在单独的函数中处理它们。

函数：目前仅仅有exit一个元命令。

/*
 * Handle the metadata, like .exit, our token must be match metadata first,then match the statement
 */
MetaCommandResult do_meta_command(InputBuffer *inputBuffer) {
    if (strcmp(inputBuffer->buffer, ".exit") == 0) {
        exit(EXIT_SUCCESS);
    } else {
        return META_COMMAND_UNRECOGNIZED_COMMAND;
    }
}

---

2.SQL命令(语句)

sql命令枚举：

/*
 * our sql statement's type
 */
typedef enum {
    STATEMENT_INSERT,
    STATEMENT_SELECT,
} StatementType;


/*
 * our sql statement
 */
typedef struct {
    StatementType type;
} Statement;

指示sql语句正确与否的函数

/*
 * indicating our Prepare result
 */
typedef enum {
    PREPARE_SUCCESS,
    PREPARE_UNRECOGNIZED_STATEMENT,
} PrepareResult;

---

处理sql语句函数(sql编译器)

目前仅有两个命令

/*
 * Handle our statement(not the metadata),this is our sql compiler
 */
PrepareResult prepare_statement(InputBuffer *input_buffer, Statement *statement) {
    if (strncmp(input_buffer->buffer, "insert", 6) == 0) {
        statement->type = STATEMENT_INSERT;
        return PREPARE_SUCCESS;
    }
    if (strncmp(input_buffer->buffer, "select", 6) == 0) {
        statement->type = STATEMENT_SELECT;
        return PREPARE_SUCCESS;
    }
    return PREPARE_UNRECOGNIZED_STATEMENT;
}

---

真正执行SQL语句的函数(虚拟机)

这里的虚拟字节码就是SQL语句！

/*
 * here just really execute our statement now!
 */

void execute_statement(Statement *statement) {
    switch (statement->type) {
        case STATEMENT_INSERT:
            printf("This is where we would do an insert.\n");
            break;
        case STATEMENT_SELECT:
            printf("This is where we would do an select.\n");
            break;
    }
}

---

3.全部函数

REPL.h

//
// Created by 29001 on 2024/11/20.
//

#ifndef HANDWRITTEN_SQLITE_REPL_H
#define HANDWRITTEN_SQLITE_REPL_H
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<math.h>
#include<stdbool.h>
#include <errno.h>
#include <stdint.h>

ssize_t getline(char **lineptr, size_t *n, FILE *stream);

/*
 * the data structure is for input token
 */
typedef struct {
    char *buffer;
    size_t buffer_length;//size_t equals to unsigned long long int
    ssize_t input_length;//ssize_t equals to long long int
} InputBuffer;


/*
 * just initialised a new buffer
 * return the structure of buffer for containing the next input token
 */
InputBuffer *new_input_buffer() {//
    InputBuffer *input_buffer = malloc(sizeof(InputBuffer));//dynamic memory in the heap
    input_buffer->buffer = NULL;//the token chars haven't pointed to anything
    input_buffer->buffer_length = 0;
    input_buffer->input_length = 0;
    return input_buffer;
}

void print_prompt() {
    printf("EYKDB > ");//can be custom
}

/*
 * input the token now!
 * used the getline function
 * two details:
 * 1.replace the enter char with '\0' at the end
 * 2.the real length must minus 1
 */
void read_input(InputBuffer *inputBuffer) {
    ssize_t byte_read = getline(&(inputBuffer->buffer), &(inputBuffer->buffer_length), stdin);
    if (byte_read <= 0) {
        printf("Error reading input\n");
        exit(EXIT_FAILURE);
    }
    inputBuffer->input_length = byte_read - 1;
    inputBuffer->buffer[byte_read - 1] = 0;
}

/*
 * free the dynamic memory allocated in the input_buffer(all the point's data should be free)
 */
void close_input_buffer(InputBuffer *input_buffer) {
    free(input_buffer->buffer);
    free(input_buffer);
}


/*
 * the getline function
 * lineptr :
 * a pointer to the variable we use to point to the buffer containing the read line.
 * If it set to NULL it is mallocatted by getline and should thus be freed by the user, even if the command fails.
 *
 * n :
 * a pointer to the variable we use to save the size of allocated buffer.
 *
 * stream :
 * the input stream to read from. We’ll be reading from standard input.
 *
 * return value :
 * the number of bytes read, which may be less than the size of the buffer.
 */

ssize_t getline(char **lineptr, size_t *n, FILE *stream) {
    size_t pos;
    int c;

    if (lineptr == NULL || stream == NULL || n == NULL) {
        errno = EINVAL;
        return -1;
    }

    c = getc(stream);
    if (c == EOF) {
        return -1;
    }

    if (*lineptr == NULL) {
        *lineptr = malloc(128);
        if (*lineptr == NULL) {
            return -1;
        }
        *n = 128;
    }

    pos = 0;
    while (c != EOF) {
        if (pos + 1 >= *n) {
            size_t new_size = *n + (*n >> 2);
            if (new_size < 128) {
                new_size = 128;
            }
            char *new_ptr = realloc(*lineptr, new_size);
            if (new_ptr == NULL) {
                return -1;
            }
            *n = new_size;
            *lineptr = new_ptr;
        }

        ((unsigned char *) (*lineptr))[pos++] = c;
        if (c == '\n') {
            break;
        }
        c = getc(stream);
    }

    (*lineptr)[pos] = '\0';
    return pos;
}

#endif //HANDWRITTEN_SQLITE_REPL_H

---

 simple_SQL_compiler.h

//
// Created by 29001 on 2024/11/20.
//

#ifndef HANDWRITTEN_SQLITE_SIMPLE_SQL_COMPILER_H
#define HANDWRITTEN_SQLITE_SIMPLE_SQL_COMPILER_H

#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<math.h>
#include<stdbool.h>
#include <errno.h>
#include <stdint.h>
#include "REPL.h"

/*
 * our new functions return enums indicating success or failure
 */
typedef enum {
    META_COMMAND_SUCCESS,
    META_COMMAND_UNRECOGNIZED_COMMAND,
} MetaCommandResult;

/*
 * our sql statement's type
 */
typedef enum {
    STATEMENT_INSERT,
    STATEMENT_SELECT,
} StatementType;

/*
 * our sql statement
 */
typedef struct {
    StatementType type;
} Statement;


/*
 * indicating our Prepare result
 */
typedef enum {
    PREPARE_SUCCESS,
    PREPARE_UNRECOGNIZED_STATEMENT,
} PrepareResult;

/*
 * Handle the metadata, like .exit, our token must be match metadata first,then match the statement
 */
MetaCommandResult do_meta_command(InputBuffer *inputBuffer) {
    if (strcmp(inputBuffer->buffer, ".exit") == 0) {
        exit(EXIT_SUCCESS);
    } else {
        return META_COMMAND_UNRECOGNIZED_COMMAND;
    }
}

/*
 * Handle our statement(not the metadata),this is our sql compiler
 */
PrepareResult prepare_statement(InputBuffer *input_buffer, Statement *statement) {
    if (strncmp(input_buffer->buffer, "insert", 6) == 0) {
        statement->type = STATEMENT_INSERT;
        return PREPARE_SUCCESS;
    }
    if (strncmp(input_buffer->buffer, "select", 6) == 0) {
        statement->type = STATEMENT_SELECT;
        return PREPARE_SUCCESS;
    }
    return PREPARE_UNRECOGNIZED_STATEMENT;
}

/*
 * here just really execute our statement now!
 */

void execute_statement(Statement *statement) {
    switch (statement->type) {
        case STATEMENT_INSERT:
            printf("This is where we would do an insert.\n");
            break;
        case STATEMENT_SELECT:
            printf("This is where we would do an select.\n");
            break;
    }
}


#endif //HANDWRITTEN_SQLITE_SIMPLE_SQL_COMPILER_H

---

main.c

#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<math.h>
#include<stdbool.h>
#include <errno.h>
#include <stdint.h>
#include "simple_SQL_compiler.h"
#include "REPL.h"

/*
 * main function: just a infinite loop
 * first ,if start with a . check up is the meta or not
 * second ,check the sql statement right or not
 * third ,execute the sql statement in the virtual machine
 */
int main(int argc, char *argv[]) {
    InputBuffer *input_buffer = new_input_buffer();
    while (true) {
        print_prompt();
        read_input(input_buffer);
        if (input_buffer->buffer[0] == '.') {//judge is the meta command or not like .like
            switch (do_meta_command(input_buffer)) {
                case META_COMMAND_SUCCESS:
                    continue;
                case META_COMMAND_UNRECOGNIZED_COMMAND:
                    printf("Unrecognized command '%s'\n", input_buffer->buffer);
                    continue;
            }
        }

// except the meta command , now analyze the token
        Statement statement;
        switch (prepare_statement(input_buffer, &statement)) {
            case PREPARE_SUCCESS:
                break;
            case PREPARE_UNRECOGNIZED_STATEMENT:
                printf("Unrecognized keyword at start of '%s'.\n", input_buffer->buffer);
                continue;//enter the next loop
        }
        execute_statement(&statement);
        //close_input_buffer(input_buffer);//i don't why add this function will report an error, Illegal exit
        printf("Executed.\n");
    }
}

---

4.数据库运行结果

---

---

📣非常感谢你阅读到这里，如果这篇文章对你有帮助，希望能留下你的点赞👍 关注❤收藏✅ 评论💬，大佬三连必回哦！thanks！！！
📚愿大家都能学有所得，功不唐捐！

👇下面是专栏彩蛋系列，你会喜欢的！👇

---

💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖

热门专栏

🌈🌈专栏彩蛋系列

🌈🌈史上最全八股文，欢迎收藏

🌈🌈一篇文章了解我的上千篇博客

💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖

---