2.11 sqlite3数据库【数据库的相关操作指令、函数】

练习：

将 epoll 服务器 客户端拿来用
客户端：写一个界面，里面有注册登录
服务器：处理注册和登录逻辑，注册的话将注册的账号密码写入数据库，登录的话查询数据库中是否存在账号，并验证密码是否正确
额外功能：客户端登录的时候，服务器向客户端发送一个验证码，只有验证码也正确的时候，才能登录成功·     

server.c 

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <fcntl.h>
#include <sys/epoll.h>
#include <sqlite3.h>
#include <errno.h>
#include <time.h>

#define MAX_EVENTS 100

typedef struct sockaddr_in addr_in_t;
typedef struct sockaddr addr_t;

enum Type {
    TYPE_REGIST,
    TYPE_LOGIN,
    TYPE_CHAT
};

typedef struct Pack {
    enum Type type;
    char name[20];
    char pswd[20];
    char tarname[20];
    char text[1024];
} pack_t;

sqlite3 *db;  // 数据库指针

// 生成验证码
void generate_code(char *code, int length) {
    const char charset[] = "0123456789";
    srand(time(NULL));
    for (int i = 0; i < length; i++) {
        code[i] = charset[rand() % 10];
    }
    code[length] = '\0';
}

// 设置文件描述符非阻塞模式
void set_nonblocking(int fd) {
    int flags = fcntl(fd, F_GETFL, 0);
    fcntl(fd, F_SETFL, flags | O_NONBLOCK);
}

// 处理客户端请求
void handle_client(int client) {
    pack_t pack = {0};
    int res = read(client, &pack, sizeof(pack));
    if (res <= 0) {
        printf("客户端断开连接\n");
        close(client);
        return;
    }

    char sql[256];
    char *errmsg = NULL;

    switch (pack.type) {
        case TYPE_REGIST:
            snprintf(sql, sizeof(sql), "INSERT INTO users (name, password) VALUES ('%s', '%s');", pack.name, pack.pswd);
            if (sqlite3_exec(db, sql, 0, 0, &errmsg) != SQLITE_OK) {
                printf("注册失败: %s\n", errmsg);
                strcpy(pack.text, "注册失败，该账号可能已存在！");
                sqlite3_free(errmsg);
            } else {
                strcpy(pack.text, "注册成功！");
            }
            write(client, &pack, sizeof(pack));
            break;

        case TYPE_LOGIN: {
            snprintf(sql, sizeof(sql), "SELECT password FROM users WHERE name = '%s';", pack.name);
            sqlite3_stmt *stmt;
            if (sqlite3_prepare_v2(db, sql, -1, &stmt, 0) == SQLITE_OK) {
                if (sqlite3_step(stmt) == SQLITE_ROW) {
                    const char *db_pswd = (const char*)sqlite3_column_text(stmt, 0);
                    if (strcmp(db_pswd, pack.pswd) == 0) {
                        char code[6];
                        generate_code(code, 5);
                        snprintf(pack.text, sizeof(pack.text), "验证码：%s\n", code);
                        write(client, &pack, sizeof(pack));

                        // 等待客户端输入验证码
                        read(client, &pack, sizeof(pack));
                        if (strcmp(pack.text, code) == 0) {
                            strcpy(pack.text, "登录成功！");
                        } else {
                            strcpy(pack.text, "验证码错误，登录失败！");
                        }
                    } else {
                        strcpy(pack.text, "密码错误！");
                    }
                } else {
                    strcpy(pack.text, "账号不存在！");
                }
                sqlite3_finalize(stmt);
            }
            write(client, &pack, sizeof(pack));
            break;
        }

        case TYPE_CHAT:
            printf("收到消息: %s\n", pack.text);
            break;
    }
}

int main(int argc, const char *argv[]) {
    if (argc != 2) {
        printf("请输入端口号\n");
        return 1;
    }

    int port = atoi(argv[1]);

    // 初始化数据库
    if (sqlite3_open("users.db", &db) != SQLITE_OK) {
        printf("无法打开数据库\n");
        return 1;
    }

    const char *create_table_sql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE, password TEXT);";
    char *errmsg = NULL;
    if (sqlite3_exec(db, create_table_sql, 0, 0, &errmsg) != SQLITE_OK) {
        printf("创建表失败: %s\n", errmsg);
        sqlite3_free(errmsg);
        return 1;
    }

    // 创建服务器 socket
    int server = socket(AF_INET, SOCK_STREAM, 0);
    addr_in_t addr = {0};
    addr.sin_family = AF_INET;
    addr.sin_port = htons(port);
    addr.sin_addr.s_addr = INADDR_ANY;

    bind(server, (addr_t*)&addr, sizeof(addr));
    listen(server, 10);

    // 设置非阻塞模式
    set_nonblocking(server);

    // 创建 epoll
    int epoll_fd = epoll_create1(0);
    struct epoll_event ev, events[MAX_EVENTS];

    ev.events = EPOLLIN;
    ev.data.fd = server;
    epoll_ctl(epoll_fd, EPOLL_CTL_ADD, server, &ev);

    printf("服务器启动，监听端口 %d...\n", port);

    while (1) {
        int n = epoll_wait(epoll_fd, events, MAX_EVENTS, -1);
        for (int i = 0; i < n; i++) {
            if (events[i].data.fd == server) {
                // 处理新连接
                int client = accept(server, NULL, NULL);
                if (client < 0) {
                    perror("accept");
                    continue;
                }
                set_nonblocking(client);
                ev.events = EPOLLIN | EPOLLET;
                ev.data.fd = client;
                epoll_ctl(epoll_fd, EPOLL_CTL_ADD, client, &ev);
                printf("新客户端连接: %d\n", client);
            } else {
                // 处理客户端请求
                handle_client(events[i].data.fd);
            }
        }
    }

    sqlite3_close(db);
    close(server);
    return 0;
}

client.c

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <arpa/inet.h>

typedef struct sockaddr_in addr_in_t;
typedef struct sockaddr addr_t;

enum Type {
    TYPE_REGIST,
    TYPE_LOGIN,
    TYPE_CHAT
};

typedef struct Pack {
    enum Type type;
    char name[20];
    char pswd[20];
    char tarname[20];
    char text[1024];
} pack_t;

void handle_response(int client) {
    pack_t pack;
    int res = read(client, &pack, sizeof(pack));
    if (res > 0) {
        printf("服务器: %s\n", pack.text);
    }
}

int main(int argc, const char *argv[]) {
    if (argc != 2) {
        printf("输入端口号\n");
        return 1;
    }

    int port = atoi(argv[1]);
    int client = socket(AF_INET, SOCK_STREAM, 0);
    addr_in_t addr = {0};
    addr.sin_family = AF_INET;
    addr.sin_port = htons(port);
    addr.sin_addr.s_addr = inet_addr("192.168.126.245");

    if (connect(client, (addr_t*)&addr, sizeof(addr)) == -1) {
        perror("连接失败");
        return 1;
    }

    printf("连接服务器成功！\n");

    while (1) {
        int ch;
        printf("1: 注册\n2: 登录\n3: 聊天\n0: 退出\n请选择: ");
        scanf("%d", &ch);
        while (getchar() != '\n');

        pack_t pack = {0};
        
        switch (ch) {
            case 1:
                printf("输入账号: ");
                scanf("%s", pack.name);
                while (getchar() != '\n');
                printf("输入密码: ");
                scanf("%s", pack.pswd);
                while (getchar() != '\n');
                pack.type = TYPE_REGIST;
                write(client, &pack, sizeof(pack));
                handle_response(client);
                break;

            case 2:
                printf("输入账号: ");
                scanf("%s", pack.name);
                while (getchar() != '\n');
                printf("输入密码: ");
                scanf("%s", pack.pswd);
                while (getchar() != '\n');
                pack.type = TYPE_LOGIN;
                write(client, &pack, sizeof(pack));

                handle_response(client); // 服务器发送验证码

                printf("输入验证码: ");
                scanf("%s", pack.text);
                while (getchar() != '\n');
                write(client, &pack, sizeof(pack));
                
                handle_response(client); // 登录成功 or 失败
                break;

            case 3:
                printf("输入聊天对象: ");
                scanf("%s", pack.tarname);
                while (getchar() != '\n');
                printf("输入消息: ");
                fgets(pack.text, sizeof(pack.text), stdin);
                pack.text[strcspn(pack.text, "\n")] = '\0'; // 去除换行符
                pack.type = TYPE_CHAT;
                write(client, &pack, sizeof(pack));
                handle_response(client);
                break;

            case 0:
                close(client);
                return 0;

            default:
                printf("无效选项\n");
        }
    }

    return 0;
}