Linux下基于TCP和sqlite3实现的网络词典
一、sqlite3使用：
1.终端界面
查看版本信息
sqlite3 --version
获取新版sqlite3
sudo apt-get install sqlite3
打开sqlite3
sqlite3
//打开sqlite3sqlite3
//打开或创建tablename库文件 sqlite3界面
数据库文件，以db结尾：my.db
进入数据库：sqlite3或者sqlite3 数据库文件名
常用基础指令：
.help:查看帮助手册
.quit:退出
.exit:退出
.table:显示表信息
.schema:显示表信息
sqlite3相关sql语句，sql必须以分号结尾//text,integer,float...
基本操作：
创建表{
create table
}
增{
insert into
}
删{
delete from
使用实例
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_openwhile(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_opensqlite3_ctlsqlite3_execsqlite3_close客户端：open/read/write/close
6.僵尸进程处理程序signal_handler{wait}2.服务器
/*********server.h**********/
#ifndef _SERVER_H_
#define _SERVER_H_#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include#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'//注册
#define L 'L'//登录
#define S 'S'//查找
#define Q 'Q'//推出
#define __usrlen 32
#define __textlen 1024typedef 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");;}
} 3.客户端
/********client.h*********/
#ifndef _CLIENT_H_
#define _CLIENT_H_#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include#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'//注册
#define L 'L'//登录
#define S 'S'//查找
#define Q 'Q'//推出
#define __mode 0777typedef 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.注册帐号**\n**2.登录帐号**\n**3.本地记录**\n**4.推出程序**\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? \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);
} 本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！