在线英英词典(sqlite3)
项目功能描述：
1.用户注册和登录验证：服务器端将用户信息和历史记录保存在数据库中，客户端输入用户名和密码，服务器端在数据库中查找、匹配，返回结果。
2.单词在线翻译：根据客户端输入的单词在字典文件中搜索。
3.历史记录查询
include.h
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <sqlite3.h>
#include <signal.h>
#include <time.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#define DATABASE "my.db"
typedef struct
{
int
type;
char
name[20];
char
data[256]; // password or word
}MSG;
server.c
#include "include.h"
void do_register(int connectfd, MSG *msg, sqlite3
*db) //注册用户
{
char
sqlstr[128];
char
*errmsg;
sprintf(sqlstr, "insert into usr values ('%s', '%s')",
msg->name,
msg->data);//封装SQL操作语句
printf("%s\n", sqlstr);
if
(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) !=
SQLITE_OK) //执行SQL操作语句
{
sqlite3_free(errmsg);
sprintf(msg->data, "user %s already exist!!!",
msg->name);
}
else
{
strncpy(msg->data, "OK", 256);
// 操作成功
}
send(connectfd, msg, sizeof(MSG), 0);
return;
}
void do_login(int connectfd, MSG *msg, sqlite3
*db) //登录用户
{
char
sqlstr[128];
char
*errmsg, **result;
int nrow,
ncolumn;
sprintf(sqlstr, "select * from usr where name = '%s' and pass =
'%s'", msg->name, msg->data);
if
(sqlite3_get_table(db, sqlstr, &result,
&nrow, &ncolumn,
&errmsg) != SQLITE_OK)
{
printf("error : %s\n", errmsg);
sqlite3_free(errmsg);
}
if (nrow ==
0)
//没有找到登录的用户，报错
{
strncpy(msg->data, "name or password is wrong!!!",
256);
}
else
//登录成功，发OK
{
strncpy(msg->data, "OK", 256);
}
send(connectfd, msg, sizeof(MSG), 0);
sqlite3_free_table(result);
return;
}
void get_date(char date[]) //获得历史记录的时间
{
time_t
t;
struct tm
*tp;
time(&t);
tp =
localtime(&t);
strftime(date, 64, "%Y-%m-%d %H:%M:%S", tp);
return;
}
int do_searchword(int connectfd, MSG *msg)
//搜索单词
{
FILE
*fp;
char
s[300];
char *p;
if ((fp =
fopen("dict.txt", "r")) == NULL)
{
strcpy(msg->data, "dict on server can't be opened
:(");
send(connectfd, msg, sizeof(MSG), 0);
}
printf("query word is %s\n", msg->data);
while
(fgets(s, 300, fp) !=
NULL)
{
if (0 ==
strncmp(msg->data,s,strlen(msg->data))
)
{
p = s + strlen(msg->data);
while(0 == strncmp(p," ",1) ) p++;
//去掉这一行前面的单词和空格
strcpy(msg->data,p);
//只发送解释的部分
send(connectfd,msg,sizeof(MSG),0);
fclose(fp);
return 1; //返回1说明查找成功
}
}
fclose(fp);
return 0;
//返回0说明查找失败
}
void do_query(int connectfd, MSG *msg, sqlite3
*db) //将历史记录插入到record表中
{
char
sqlstr[128], *errmsg;
int found =
0;
char
date[64], word[64];
strcpy(word,
msg->data);
found =
do_searchword(connectfd, msg);
if ( found )
//判断找到单词是否成功
{
get_date(date); //如果成功，记录时间，插入历史表
sprintf(sqlstr, "insert into record values ('%s', '%s', '%s')",
msg->name, date, word);
if (sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg)
!= SQLITE_OK)
{
printf("error : %s\n", errmsg);
sqlite3_free(errmsg);
}
}
else
//如果失败，报错
{
strcpy(msg->data, "not found\n");
}
send(connectfd, msg, sizeof(MSG), 0);
return;
}
int history_callback(void *arg, int f_num, char **f_value, char
**f_name)//回调表中msg->name的记录
{
int
connectfd;
MSG
msg;
connectfd =
*(int *)arg;
sprintf(msg.data, "%s : %s", f_value[1], f_value[2]);
send(connectfd, &msg, sizeof(msg), 0);
return
0;
}
void do_history(int connectfd, MSG *msg, sqlite3
*db) //查找当前用户的历史记录
{
char
sqlstr[128], *errmsg;
sprintf(sqlstr, "select * from record where name = '%s'",
msg->name);
if
(sqlite3_exec(db, sqlstr, history_callback, (void
*)&connectfd, &errmsg) !=
SQLITE_OK)
{
printf("error : %s\n", errmsg);
sqlite3_free(errmsg);
}
msg->data[0] = '\0';
//表示回调结束
send(connectfd, msg, sizeof(MSG), 0);
return;
}
void do_client(int connectfd, sqlite3 *db)
//处理客户机发来的信息
{
MSG
msg;
while
(recv(connectfd, &msg, sizeof(msg), 0)
> 0)
{
switch ( msg.type )
{
case 1 :
do_register(connectfd, &msg, db);
//注册
break;
case 2 :
do_login(connectfd, &msg, db);
//登录
break;
case 3 :
do_query(connectfd, &msg, db);
//查询单词
break;
case 4 :
do_history(connectfd, &msg, db);
//历史记录
break;
}
}
printf("client quit\n");
exit(0);
return;
}
main_server.c
#include "include.h"
int main(int argc, char *argv[])
{
int
listenfd, connectfd;
struct
sockaddr_in server_addr;
pid_t
pid;
sqlite3
*db;
if
(sqlite3_open(DATABASE, &db) != SQLITE_OK)
{
printf("error : %s\n", sqlite3_errmsg(db));
exit(-1);
}
//create table record (name
text,date,text,word text);
//create table usr (name text primary
key,pass text);
//这边没有创建数据库的表，请在my.db里自行创建以上两个表
if
((listenfd = socket(PF_INET, SOCK_STREAM, 0)) <
0)
{
perror("fail to socket");
exit(-1);
}
bzero(&server_addr, sizeof(server_addr));
server_addr.sin_family = PF_INET;
server_addr.sin_addr.s_addr = inet_addr("192.168.1.106");
server_addr.sin_port = htons(8888);
if
(bind(listenfd, (struct sockaddr *)&server_addr,
sizeof(server_addr)) < 0)
{
perror("fail to bind");
exit(-1);
}
if
(listen(listenfd, 5) < 0)
{
perror("fail to listen");
exit(-1);
}
signal(SIGCHLD, SIG_IGN); //
为了不生成僵尸进程
while (1)
{
if ((connectfd = accept(listenfd, NULL, NULL)) <
0)
{
perror("fail to accept");
exit(-1);
}
if ((pid = fork()) < 0)
//多进程以便多用户登录
{
perror("fail to fork");
exit(-1);
}
if (pid == 0)
//子进程执行客户机函数
{
do_client(connectfd, db);
}
close(connectfd);
}
return
0;
}
client.c
#include "include.h"
void do_register(int socketfd, MSG *msg)
//注册用户
{
msg->type = 1;
printf("input name : ");
scanf("%s",
msg->name);
printf("input password : ");
scanf("%s",
msg->data);
send(socketfd, msg, sizeof(MSG), 0);
recv(socketfd, msg, sizeof(MSG), 0);
printf("register : %s\n", msg->data);
return;
}
int do_login(int socketfd, MSG *msg) //登录用户
{
msg->type = 2;
printf("input name : ");
scanf("%s",
msg->name);
printf("input password : ");
scanf("%s",
msg->data);
send(socketfd, msg, sizeof(MSG), 0);
recv(socketfd, msg, sizeof(MSG), 0);
if
(strncmp(msg->data, "OK", 3) == 0)
//判断接受的是不是OK
{
printf("login : OK\n");
return 1;
//返回1说明登录成功
}
printf("login : %s\n", msg->data);
//打印报错的信息
return 0;
//返回0说明登录失败
}
void do_query(int socketfd, MSG *msg)
//把输入的单词发给服务器
{
msg->type = 3;
while
(1)
{
scanf("%s", msg->data);
if (strcmp(msg->data, "#") == 0)
break; //输入“#”退出
send(socketfd, msg, sizeof(MSG), 0);
recv(socketfd, msg, sizeof(MSG), 0);
printf("%s\n",msg->data);
//打印单词解释，或是没有找到
}
return;
}
void do_history(int socketfd, MSG *msg)
//保存记录到历史记录里
{
msg->type = 4;
send(socketfd, msg, sizeof(MSG), 0);
while
(1)
{
recv(socketfd, msg, sizeof(MSG), 0);
//执行一次接收一条历史记录
if (msg->data[0] == '\0') break;
//如果遇到'\0'表示回调历史记录结束
printf("%s\n", msg->data);
//打印一条历史记录
}
return;
}
main_client.c
#include "include.h"
int main(int argc, char *argv[])
{
int socketfd
;
struct
sockaddr_in server_addr;
MSG msg;
if
((socketfd = socket(PF_INET, SOCK_STREAM, 0)) <
0)
{
perror("fail to socket");
}
bzero(&server_addr, sizeof(server_addr));
server_addr.sin_family = PF_INET;
server_addr.sin_addr.s_addr = inet_addr("192.168.1.106");
server_addr.sin_port = htons(8888);
if
(connect(socketfd, (struct sockaddr *)&server_addr,
sizeof(server_addr)) < 0)
{
perror("fail to connect");
exit(-1);
}
int n;
while (1)
//用户注册登录界面
{
printf("************************************\n");
printf("* 1: register 2:
login 3: quit *\n");
printf("************************************\n");
printf("please choose : ");
if (scanf("%d", &n) <= 0)
{
perror("scanf");
exit(-1);
}
switch (n)
{
case 1 :
do_register(socketfd, &msg);
break;
case 2 :
if (do_login(socketfd, &msg) == 1)
{
printf("\n");
goto next; //登录成功则跳至查询单词的界面
加载中…