Linux下基于TCP和sqlite3实现的网络词典

一、sqlite3使用：

1.终端界面

查看版本信息

sqlite3 --version

获取新版sqlite3

sudo apt-get install sqlite3

打开sqlite3

sqlite3
//打开sqlite3

sqlite3 <tablename>
//&#x6253;&#x5F00;&#x6216;&#x521B;&#x5EFA;tablename&#x5E93;&#x6587;&#x4EF6;</tablename>

sqlite3界面

数据库文件，以db结尾：my.db

进入数据库：sqlite3或者sqlite3 数据库文件名

常用基础指令：
.help:查看帮助手册
.quit:退出
.exit:退出
.table:显示表信息
.schema:显示表信息

sqlite3相关sql语句，sql必须以分号结尾//text,integer,float…

基本操作：
创建表{
create table

sql语句必须以;结尾，命令必须以.开头，更多使用方法通过.help查看。

2.数据库(sqlite3)编程
sqlite3相关接口函数

        
SQLITE_API int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
返回值：成功返回0，失败返回错误码(非零值)

SQLITE_API int sqlite3_close(sqlite3*);
返回值：成功返回0，失败返回错误码

SQLITE_API const char *sqlite3_errmsg(sqlite3*);

SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);
返回值：成功返回0，失败返回错误码

typedef int (*sqlite3_callback)(void*,int,char**, char**);
                功能：每找到一条记录自动执行一次回调函数
                para：传递给回调函数的参数
                f_num：记录中包含的字段数目
                f_value：包含每个字段值的指针数组
                f_name：包含每个字段名称的指针数组
                返回值：成功返回0，失败返回-1

更多函数与描述通过vim /usr/include/sqlite3.h查看

sqlite3_exec与callback：通常只有select的sql语句才需要回调函数，其他语句的exec的第三个参数通常填NULL，第四个参数为为回调函数的第一个参数，select如果不需要在回调中处理信息通常也填NULL。select执行成功一次并且返回一次时才调用一次回调函数！例如：select * from table; 有多少行数据则调用多少次回调函数；select info1 from table where info2 == …;则是由多少行数据满足info2==…调用多少次回调函数。不需要在exec和callback外部加循环，查询返回时自动调用回调函数。

二、网络词典

1. 实现步骤

1.头文件
2.宏定义
3.服务器主程序/客户端主程序
    服务器：
        socket/bind/listen        sqlite3_open
        while(1) accept 链接成功开启进线程//多路复用
    客户端：
        socket/connect 链接成功切入功能选项
4.服务器信息处理程序（接口对应）
    按功能请求接入对应代码段
    switch(msg.type)case:break;...default:...

5.功能实现
    (1)注册功能 type = R
        服务器：
            select usrname from usr where msg.name == ...
            insert into usr values(...
        客户端：
            msg.name/msg.pass
    (2)登录功能 type = L
        服务器：
            select * from usr ...
        客户端：
            msg.name/msg.pass
    (3)查询功能 type = F
        服务器：
            select * from dict ...
        客户端：
            msg.text
    (4)在线历史/本地记录
        服务器：
            sqlite3_open
            sqlite3_ctl
            sqlite3_exec
            sqlite3_close
        客户端：
            open/read/write/close
6.僵尸进程处理程序
    signal_handler{
        wait
    }

2.服务器

/*********server.h**********/
#ifndef _SERVER_H_
#define _SERVER_H_

#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sys types.h>
#include<sys socket.h>
#include<netinet in.h>
#include<netinet ip.h>
#include<arpa inet.h>
#include<unistd.h>
#include<signal.h>
#include<sys wait.h>
#include<sqlite3.h>
#include<sys stat.h>
#include<fcntl.h>

#define CORVAL 1
#define NORVAL 0
#define ERRVAL -1
#define _STR(x) _VAL(x)
#define _VAL(x) #x
#define handle_error(LOG) do { perror(LOG); exit(EXIT_FAILURE); } while(0)
#define qDbug() do { puts(_STR(__LINE__)); } while(0)
#define __port 50000
#define __ip "192.168.223.132"
#define __backlog 1024
#define R 'R'//&#x6CE8;&#x518C;
#define L 'L'//&#x767B;&#x5F55;
#define S 'S'//&#x67E5;&#x627E;
#define Q 'Q'//&#x63A8;&#x51FA;
#define __usrlen 32
#define __textlen 1024

typedef struct msg{
    char type;
    char name[__usrlen];
    char pass[__usrlen];
    char text[__textlen];
    int connfd;
}Msg, *MsgP;

sqlite3 *usrdb = NULL;
sqlite3 *dicdb = NULL;
char *errmsg = NULL;
int flag, flag_log, flag_reg;

int listenfd_init();
void handler_func(int);
void handler_cli(int);
void signal_handler(int);
int callback(void *para, int f_num, char **f_value, char **f_name);
void regis(int, MsgP);
void login(int, MsgP);
void look_up(int, MsgP);
void time_log();
void quit(int, MsgP);

#endif

/*********server_main.c********/
#include"server.h"

int main(int argc, char **argv){
    signal(SIGCHLD, signal_handler);
    int listenfd = listenfd_init();
    handler_func(listenfd);
    sqlite3_close(usrdb);
    sqlite3_close(dicdb);
    sqlite3_free(errmsg);
}

/************server.c*************/
#include"server.h"

void signal_handler(int signum){
    wait(NULL);
}

int listenfd_init(){
    int sockfd;
    if(ERRVAL == (sockfd = socket(AF_INET, SOCK_STREAM, IPPROTO_TCP))) handle_error("socket");
    struct sockaddr_in saddr;
    socklen_t addrlen = sizeof(saddr);
    saddr.sin_family = AF_INET;
    saddr.sin_port = htons(__port);
    saddr.sin_addr.s_addr = INADDR_ANY;
    if(ERRVAL == bind(sockfd, (const struct sockaddr *)&saddr, addrlen)) handle_error("bind");
    if(ERRVAL == listen(sockfd, __backlog)) handle_error("listen");
    return sockfd;
}

void handler_func(int sockfd){
    if(SQLITE_ERROR == sqlite3_open("usr.db", &usrdb)){
        printf("open usr database failed!\n");
        printf("%s\n", errmsg);
        exit(EXIT_FAILURE);
    }
    if(SQLITE_ERROR == sqlite3_exec(usrdb, "CREATE TABLE if not exists usr(usrname text, usrpass text);", NULL, NULL, &errmsg)){
        printf("%s\n", errmsg);
        exit(EXIT_FAILURE);
    }
    if(SQLITE_ERROR == sqlite3_open("dict.db", &dicdb)){
        printf("open usr database failed!\n");
        printf("%s\n", errmsg);
        exit(EXIT_FAILURE);
    }
    int connfd;
    pid_t pid;
    struct sockaddr_in caddr;
    socklen_t addrlen = sizeof(caddr);
    while(1){
        if(ERRVAL == (connfd = accept(sockfd, (struct sockaddr *)&caddr, &addrlen))) handle_error("accept");
        if(ERRVAL == (pid = fork())) handle_error("fork");
        if(0 == pid){
            close(sockfd);
            handler_cli(connfd);
        }
        else{
            close(connfd);
        }
    }
}

void handler_cli(int connfd){
    Msg msg;
    memset(&msg, 0, sizeof(msg));
    char buf[__textlen];
    while(1){
        if(ERRVAL == recv(connfd, &msg, sizeof(msg), 0)) handle_error("recv");
        switch(msg.type){
            case R:
                regis(connfd, &msg);
                break;
            case L:
                login(connfd, &msg);
                break;
            case S:
                look_up(connfd, &msg);
                break;
            case Q:
                quit(connfd, &msg);
                break;
            default:
                sprintf(buf, "msg.type error");
                if(ERRVAL == send(connfd, buf, strlen(buf), 0)) handle_error("send");
        }
    }
}

int callback_dic(void *para, int f_num, char **f_value, char **f_name){
    int i = 0;
    if(0 == strcmp(f_value[i], (*(MsgP)para).text)){
        if(ERRVAL == send((*(MsgP)para).connfd, f_value[i + 1], strlen(f_value[i + 1]), 0)) handle_error("send");
        flag = 1;
    }
}

int callback_log(void *para, int f_num, char **f_value, char **f_name){
    int i = 0;
    if(0 == strcmp(f_value[i], (*(MsgP)para).name)){
        if(0 == strcmp(f_value[i + 1], (*(MsgP)para).pass)){
            flag_log = 1;
            if(ERRVAL == send((*(MsgP)para).connfd, "login success", strlen("login success"), 0)) handle_error("send");
        }
        else{
            if(ERRVAL == send((*(MsgP)para).connfd, "login failed------password error", strlen("login failed------password error"), 0)) handle_error("send");
        }
    }
}

int callback_reg(void *para, int f_num, char **f_value, char **f_name){
    int i = 0;
    if(0 == strcmp(f_value[i], (*(MsgP)para).name)){
        flag_reg = 1;
        if(ERRVAL == send((*(MsgP)para).connfd, "The user name has been occupied", strlen("The user name has been occupied"), 0)) handle_error("send");
    }
}

void regis(int connfd, MsgP msg){
    int ret;
    flag_reg = 0;
    (*msg).connfd = connfd;
    char buf[__textlen];
    sprintf(buf, "SELECT * FROM usr where usrname = '%s';", (*msg).name);
    if(SQLITE_OK != (ret = sqlite3_exec(usrdb, buf, callback_reg, msg, &errmsg))){
        printf("%s\n", errmsg);
    }
    if(0 == flag_reg){
        sprintf(buf, "INSERT INTO usr VALUES('%s', '%s');", (*msg).name, (*msg).pass);
        if(SQLITE_OK != (ret = sqlite3_exec(usrdb, buf, NULL, NULL, &errmsg))){
            printf("%s\n", errmsg);
            if(ERRVAL == send(connfd, "register failed", sizeof("register failed"), 0)) handle_error("send");
        }
        else{
            if(ERRVAL == send(connfd, "register success", sizeof("register success"), 0)) handle_error("send");
        }
    }
}

void login(int connfd, MsgP msg){
    int ret;
    flag_log = 0;
    char buf[__textlen];
    msg->connfd = connfd;
    sprintf(buf, "SELECT * FROM usr where usrname = '%s';", (*msg).name);
    if(SQLITE_ERROR == (ret = sqlite3_exec(usrdb, buf, callback_log, msg, &errmsg))) printf("%s\n", errmsg);
    if(0 == flag_log){
        if(ERRVAL == send((*(MsgP)msg).connfd, "login failed-----name error", strlen("login failed-----name error"), 0)) handle_error("send");
    }
}

void quit(int connfd, MsgP msg){
    close(connfd);
    exit(0);
}

void look_up(int connfd, MsgP msg){
    int ret;
    flag = 0;
    char buf[__textlen];
    msg->connfd = connfd;
    sprintf(buf, "SELECT * FROM dict where word = '%s';", msg->text);
    if(SQLITE_ERROR == (ret = sqlite3_exec(dicdb, buf, callback_dic, msg, &errmsg))) printf("%s\n", errmsg);
    if(0 == flag){
        if(ERRVAL == send(connfd, "not found", strlen("not found"), 0)) handle_error("send");;
    }
}</fcntl.h></sys></sqlite3.h></sys></signal.h></unistd.h></arpa></netinet></netinet></sys></sys></string.h></stdlib.h></stdio.h>

3.客户端

/********client.h*********/
#ifndef _CLIENT_H_
#define _CLIENT_H_

#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sys types.h>
#include<sys socket.h>
#include<netinet in.h>
#include<netinet ip.h>
#include<arpa inet.h>
#include<unistd.h>
#include<fcntl.h>
#include<sys stat.h>
#include<time.h>

#define CORVAL 1
#define NORVAL 0
#define ERRVAL -1
#define _STR(x) _VAL(x)
#define _VAL(x) #x
#define handle_error(LOG) do { perror(LOG); exit(EXIT_FAILURE); } while(0)
#define qDbug() do { puts(_STR(__LINE__)); } while(0)
#define __port 50000
#define __ip "192.168.223.132"
#define __usrlen 32
#define __textlen 1024
#define R 'R'//&#x6CE8;&#x518C;
#define L 'L'//&#x767B;&#x5F55;
#define S 'S'//&#x67E5;&#x627E;
#define Q 'Q'//&#x63A8;&#x51FA;
#define __mode 0777

typedef struct msg{
    char type;
    char name[__usrlen];
    char pass[__usrlen];
    char text[__textlen];
}Msg, *MsgP;

Msg msg;
int filefd;

int init_sockfd();
void handler_func(int);
void regis(int);
void login(int);
void auto_login(int);
void look_up(int);
void time_log();
void quit(int);

#endif

/*********client_main.c*********/
#include"client.h"

int main(int argc, char **argv){
    int sockfd = init_sockfd();
    handler_func(sockfd);
}

/**********client.c**********/
#include"client.h"

int init_sockfd(){
    int sockfd, inputnum;
    if(ERRVAL == (sockfd = socket(AF_INET, SOCK_STREAM, IPPROTO_TCP))) handle_error("socket");
    struct sockaddr_in saddr;
    socklen_t addrlen = sizeof(saddr);
    saddr.sin_family = AF_INET;
    saddr.sin_port = htons(__port);
    saddr.sin_addr.s_addr = inet_addr(__ip);
    if(ERRVAL == connect(sockfd, (const struct sockaddr *)&saddr, addrlen)) handle_error("connect");
    return sockfd;
}

void handler_func(int sockfd){
    char buf[__textlen];
    char num;
    while(1){
        printf("Welcome to Unixdictonaries\n**************\n**1.&#x6CE8;&#x518C;&#x5E10;&#x53F7;**\n**2.&#x767B;&#x5F55;&#x5E10;&#x53F7;**\n**3.&#x672C;&#x5730;&#x8BB0;&#x5F55;**\n**4.&#x63A8;&#x51FA;&#x7A0B;&#x5E8F;**\n**************\n");
        fgets(buf, __textlen, stdin);
        num = buf[0];
        switch(num){
            case '1':
                regis(sockfd);
                break;
            case '2':
                login(sockfd);
                goto _loop;
                break;
            case '3':
                time_log();
                break;
            case '4':
                quit(sockfd);
                goto _loop;
                break;
            default:
                printf("Please input again\n");
        }
        /*
           if('1' == num) regis(sockfd);
           else if('2' == num) login(sockfd);
           else if('3' == num) time_log();
           else if('4' == num) quit(sockfd);
           else printf("Please input again\n");
           */
    }
_loop:
    printf("Thanks for using!\n");
}

void regis(int sockfd){
    msg.type = R;
    char buf[__usrlen];
    while(1){
        printf("Pleas input your usrname\n");
        fgets(buf, __usrlen, stdin);
        buf[(strlen(buf)) - 1] = '\0';
        strcpy(msg.name, buf);
        printf("Pleas input your usrpass\n");
        read(0, &buf, __usrlen);
        buf[(strlen(buf)) - 1] = '\0';
        strcpy(msg.pass, buf);
        if(ERRVAL == send(sockfd, &msg, sizeof(msg), 0)) handle_error("send");
        memset(buf, 0, __usrlen);
        if(ERRVAL == recv(sockfd, buf, __usrlen, 0)) handle_error("recv");
        if(0 == strncmp(buf, "register success", strlen("register success"))){
            printf("you registed successfully!\nDo you want to Login now? <y n>\n");
            read(0, &buf, __usrlen);
            if('n' == buf[0] || 'N' == buf[0])
                break;
            if('y' == buf[0] || 'Y' == buf[0]){
                auto_login(sockfd);
                break;
            }
        }
        else{
            printf("you registed failed!\n");
            puts(buf);
        }
    }
}

void login(int sockfd){
    char buf[__usrlen];
    printf("Pleas input your usrname\n");
    fgets(buf, __usrlen, stdin);
    buf[(strlen(buf)) - 1] = '\0';
    strcpy(msg.name, buf);
    printf("Pleas input your usrpass\n");
    read(0, &buf, __usrlen);
    buf[(strlen(buf)) - 1] = '\0';
    strcpy(msg.pass, buf);
    auto_login(sockfd);
}

void auto_login(int sockfd){
    msg.type = L;
    char buf[__usrlen];
    if(ERRVAL == send(sockfd, &msg, sizeof(msg), 0)) handle_error("send");
    memset(buf, 0, __usrlen);
    if(ERRVAL == recv(sockfd, buf, __usrlen, 0)) handle_error("recv");
    if(0 == strncmp(buf, "login success", strlen("login success"))){
        printf("login success! Input word to get explan\n");
        look_up(sockfd);
    }
    else {
        puts(buf);
        printf("login failed! Please login again\n");
        login(sockfd);
    }
    memset(buf, 0, __usrlen);
}

void quit(int sockfd){
    msg.type = Q;
    if(ERRVAL == send(sockfd, &msg, sizeof(msg), 0)) handle_error("send");
    close(sockfd);
}

void time_log(){
    int ret;
    char buf[__textlen];
    if(ERRVAL == (filefd = open("./time.log", O_APPEND | O_CREAT, O_RDWR | __mode))) handle_error("open");
    while(0 < (ret = read(filefd, buf, __textlen))) write(1, buf, ret);
    close(filefd);
    puts("--------END--------");
}

void look_up(int sockfd){
    msg.type = S;
    char buf[__textlen];
    time_t timep;
    printf("you could input '3' for look time.log\nAnd you could input '4' for exit this application\n");
    while(1){
        memset(buf, 0, __textlen);
        memset(msg.text, 0, __textlen);
        fgets(buf, __textlen, stdin);
        buf[strlen(buf) - 1] = '\0';
        if(0 == strncmp(buf, "3", 1)) time_log();
        else if(0 == strncmp(buf, "4", 1)) break;
        else{
            strncpy(msg.text, buf, strlen(buf));
            memset(buf, 0, __textlen);
            if(ERRVAL == send(sockfd, &msg, sizeof(msg), 0)) handle_error("send");
            if(ERRVAL == recv(sockfd, buf, __textlen, 0)) handle_error("recv");
            if(0 == strcmp(buf, "not found")){
                puts(buf);
                continue;
            }
            else{
                time(&timep);
                write(1, buf, strlen(buf));
                putchar(10);
                strcat(msg.text, " | ");
                strcat(msg.text, buf);
                strcpy(buf, msg.text);
                strcat(buf, " | ");
                strcat(buf, ctime(&timep));
                if(ERRVAL == (filefd = open("./time.log", O_APPEND | O_CREAT | O_RDWR, __mode))) handle_error("open");
                write(filefd, buf, strlen(buf));
                close(filefd);
            }
        }
    }
    quit(sockfd);
}</y></time.h></sys></fcntl.h></unistd.h></arpa></netinet></netinet></sys></sys></string.h></stdlib.h></stdio.h>

Original: https://blog.csdn.net/qq_48633111/article/details/122627783
Author: 玲今天提不起劲
Title: Linux下基于TCP和sqlite3实现的网络词典