02.11 数据库

1.思维导图

2.题目

• 将 epoll 服务器、客户端拿来用
• 客户端：写一个界面，里面有注册登录
• 服务器：处理注册和登录逻辑，注册的话将注册的账号密码写入数据库，登录的话查询数据库中是否存在账号，并验证密码是否正确

服务器代码

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <pthread.h>
#include <semaphore.h>
#include <wait.h>
#include <signal.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <sys/socket.h>
#include <sys/ipc.h>
#include <sys/sem.h>
#include <semaphore.h>
#include <sys/msg.h>
#include <sys/shm.h>
#include <sys/un.h>
#include <sqlite3.h>
#include <sys/epoll.h>typedef struct sockaddr_in addr_in_t;
typedef struct sockaddr addr_t;
typedef struct sockaddr_un addr_un_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;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;}// 创建服务器套接字int server = socket(AF_INET, SOCK_STREAM, 0);// 准备网络地址结构体：struct sockaddr_inaddr_in_t addr = {0};addr.sin_family = AF_INET;addr.sin_port = htons(port);addr.sin_addr.s_addr = inet_addr("0.0.0.0");// 为套接字绑定 ip 和 portif (bind(server, (addr_t *)&addr, sizeof(addr)) == -1) {perror("bind");return 1;}// 监听listen(server, 10);// 创建epoll实例int epfd = epoll_create1(EPOLL_CLOEXEC);if (epfd == -1) {perror("epoll_create1");return 1;}// 注册服务器套接字到epollstruct epoll_event event;event.events = EPOLLIN;event.data.fd = server;if (epoll_ctl(epfd, EPOLL_CTL_ADD, server, &event) == -1) {perror("epoll_ctl add server");return 1;}struct epoll_event active_events[100];while (1) {int num_events = epoll_wait(epfd, active_events, 100, -1);if (num_events == -1) {perror("epoll_wait");break;}for (int i = 0; i < num_events; i++) {if (active_events[i].data.fd == server) {// 有新客户端连接printf("有新客户端连接\n");int client = accept(server, NULL, NULL);if (client == -1) {perror("accept");continue;}event.events = EPOLLIN;event.data.fd = client;if (epoll_ctl(epfd, EPOLL_CTL_ADD, client, &event) == -1) {perror("epoll_ctl add client");close(client);}} else {// 处理客户端数据int client = active_events[i].data.fd;pack_t pack = {0};int res = read(client, &pack, sizeof(pack));if (res <= 0) {printf("客户端断开连接\n");close(client);if (epoll_ctl(epfd, EPOLL_CTL_DEL, client, NULL) == -1) {perror("epoll_ctl del client");}continue;}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) {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;}}}}close(server);close(epfd);sqlite3_close(db);return 0;
}

客户端代码

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <pthread.h>
#include <semaphore.h>
#include <wait.h>
#include <signal.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <sys/epoll.h>
#include <sys/ipc.h>
#include <sys/sem.h>
#include <semaphore.h>
#include <sys/msg.h>
#include <sys/shm.h>
#include <sys/un.h>typedef struct sockaddr_in addr_in_t;
typedef struct sockaddr addr_t;
typedef struct sockaddr_un addr_un_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_server_message(int client) {pack_t pack = {0};int res = read(client, &pack, sizeof(pack));if (res <= 0) {printf("服务器断开连接\n");exit(EXIT_SUCCESS);}printf("%s\n", pack.text);
}// 处理用户输入
void handle_user_input(int client) {int ch;printf("1: 注册\n2: 登录\n3: 退出\n选择:");scanf("%d", &ch);while (getchar() != '\n');pack_t pack = {0};if (ch == 1 || ch == 2) {printf("输入账号: ");scanf("%s", pack.name);while (getchar() != '\n');printf("输入密码: ");scanf("%s", pack.pswd);while (getchar() != '\n');pack.type = (ch == 1) ? TYPE_REGIST : TYPE_LOGIN;write(client, &pack, sizeof(pack));} else {close(client);exit(EXIT_SUCCESS);}
}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.110.87");if (connect(client, (addr_t*)&addr, sizeof(addr)) == -1) {perror("连接失败");return 1;}// 创建 epoll 实例int epoll_fd = epoll_create1(EPOLL_CLOEXEC);if (epoll_fd == -1) {perror("epoll_create1");return 1;}// 将客户端套接字和标准输入添加到 epoll 实例中struct epoll_event ev, events[2];ev.events = EPOLLIN;ev.data.fd = client;if (epoll_ctl(epoll_fd, EPOLL_CTL_ADD, client, &ev) == -1) {perror("epoll_ctl: client");return 1;}ev.data.fd = STDIN_FILENO;if (epoll_ctl(epoll_fd, EPOLL_CTL_ADD, STDIN_FILENO, &ev) == -1) {perror("epoll_ctl: stdin");return 1;}while (1) {int nfds = epoll_wait(epoll_fd, events, 2, -1);if (nfds == -1) {perror("epoll_wait");continue;}for (int i = 0; i < nfds; i++) {if (events[i].data.fd == client) {// 处理服务器消息handle_server_message(client);} else if (events[i].data.fd == STDIN_FILENO) {// 处理用户输入handle_user_input(client);}}}close(epoll_fd);close(client);return 0;
}