C语言基于SQLite3的服务器端建议推送系统

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

#define MAX_BUFFER_SIZE 1024

// 回调函数用于处理数据库查询结果 int callback(void *data, int argc, char **argv, char **azColName) { char *suggestion = (char *)data; sprintf(suggestion, '%s%s', suggestion, argv[0]); return 0; }

int main() { int serverSocket, clientSocket; struct sockaddr_in serverAddr, clientAddr; socklen_t addrSize = sizeof(struct sockaddr_in); char buffer[MAX_BUFFER_SIZE]; int userid; sqlite3 *db; char *errMsg = 0; char suggestion[MAX_BUFFER_SIZE];

// 创建数据库连接
if (sqlite3_open('database.db', &db) != SQLITE_OK) {
    fprintf(stderr, '无法打开数据库: %s\n', sqlite3_errmsg(db));
    return 1;
}

// 创建服务器socket
if ((serverSocket = socket(AF_INET, SOCK_STREAM, 0)) == -1) {
    perror('创建socket失败');
    return 1;
}

// 设置服务器地址和端口
serverAddr.sin_family = AF_INET;
serverAddr.sin_port = htons(8080);
serverAddr.sin_addr.s_addr = INADDR_ANY;
memset(serverAddr.sin_zero, '\0', sizeof(serverAddr.sin_zero));

// 绑定服务器地址和端口
if (bind(serverSocket, (struct sockaddr *)&serverAddr, sizeof(struct sockaddr)) == -1) {
    perror('绑定失败');
    return 1;
}

// 监听连接
if (listen(serverSocket, 10) == -1) {
    perror('监听失败');
    return 1;
}

printf('服务器已启动，等待连接...\n');

while (1) {
    // 接受客户端连接
    if ((clientSocket = accept(serverSocket, (struct sockaddr *)&clientAddr, &addrSize)) == -1) {
        perror('接受连接失败');
        return 1;
    }

    // 读取客户端发送的userid
    if (recv(clientSocket, &userid, sizeof(userid), 0) == -1) {
        perror('读取userid失败');
        close(clientSocket);
        continue;
    }

    printf('接收到来自客户端的连接，userid: %d\n', userid);

    // 清空suggestion数组
    memset(suggestion, '\0', sizeof(suggestion));

    // 查询数据库获取建议
    sprintf(buffer, 'SELECT suggestion FROM Status WHERE uid = %d AND device_name = '' ORDER BY sid DESC LIMIT 1;', userid);
    if (sqlite3_exec(db, buffer, callback, suggestion, &errMsg) != SQLITE_OK) {
        fprintf(stderr, '数据库查询失败: %s\n', errMsg);
        close(clientSocket);
        continue;
    }

    printf('发送建议给客户端: %s\n', suggestion);

    // 判断suggestion是否为空
    if (strlen(suggestion) == 0) {
        printf('没有找到建议\n');
        close(clientSocket);
        continue;
    }

    // 发送建议给客户端
    if (send(clientSocket, suggestion, strlen(suggestion), 0) == -1) {
        perror('发送建议失败');
        close(clientSocket);
        continue;
    }

    // 关闭与客户端的连接
    close(clientSocket);
}

// 关闭数据库连接
sqlite3_close(db);

// 关闭服务器socket
close(serverSocket);

return 0;

}