电子词典
项目要求:
-
登录注册功能,不能重复登录,重复注册
-
单词查询功能
-
历史记录功能,存储单词,意思,以及查询时间
-
基于TCP,支持多客户端连接
-
采用数据库保存用户信息与历史记录
-
将dict.txt的数据导入到数据库中保存。
-
按下ctrl+c退出客户端后,注销该客户端的登录信息
格式要求:
-
main函数只跑逻辑,不允许跑功能代码
-
功能代码封装成函数
思维逻辑
客户端代码
#include <stdio.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <sys/types.h>
#include <string.h>
#include <unistd.h>
#include <arpa/inet.h>
#include <stdlib.h>
#define NOTLOGIN 0
#define LOGIN 1
#define YES 2
#define NO 3
#define REGISTER 4
#define WORD 5
#define HISTORY 6
#define IP "192.168.31.215"
#define ERR_MSG(msg) do{\
fprintf(stderr, "__%d__", __LINE__);\
perror(msg);\
}while(0)
int search_word(int sfd);
int search_history(int sfd);
int login(int sfd);
int my_register(int sfd);
int main(int argc, const char *argv[])
{
//创建流式套接字
int sfd = socket(AF_INET,SOCK_STREAM, 0);
if(sfd<0)
{
ERR_MSG("socket");
return -1;
}
//填充要来链接的服务器的窗口和ip
struct sockaddr_in sin;
sin.sin_family =AF_INET;
sin.sin_port =htons(8888);
sin.sin_addr.s_addr =inet_addr(IP);
if(connect(sfd, (struct sockaddr*)&sin, sizeof(sin))<0)
{
ERR_MSG("connect");
return -1;
}
printf("connect success\n");
char num;
while(1)
{
system("clear");
printf("*******1.注册******\n");
printf("*******2.登录******\n");
printf("*******3.退出******\n");
printf("请输入功能》》");
scanf("%c", &num);
while(getchar()!=10);
switch(num)
{
case '1':
//注册
my_register(sfd);
break;
case '2':
//登录
login(sfd);
break;
case '3':
//退出
goto END;
break;
default:
printf("输入错误,请重新输入\n");
}
printf("请输入任一字符清屏\n");
while(getchar()!=10);
}
END:
close(sfd);
return 0;
}
//注册
int my_register(int sfd)
{
char c;
char buf[128]="";
ssize_t res;
char username[30]="";
char password[30]="";
printf("请输入用户名》》");
fgets(username, sizeof(username), stdin);
username[strlen(username)-1]=0;
printf("请输入密码》》");
fgets(password, sizeof(password), stdin);
password[strlen(password)-1]=0;
//组注册协议语句
sprintf(buf,"%c%s%c%s", REGISTER, username,0, password);
if(send(sfd,buf, 1+strlen(username)+1+strlen(password), 0)<0)
{
ERR_MSG("send");
return 0;
}
//接收服务器返回的信息
bzero(buf, sizeof(buf));
res=recv(sfd, buf, sizeof(buf),0);
if(res<0)
{
perror("recv");
return -1;
}
else if(res==0)
{
printf("服务器关闭\n");
return -1;
}
//拆解buf 信息
c = *buf;
if(c == YES)
{
printf("********注册成功********\n");
}
else if(c == NO)
{
printf("*******用户已存在*******\n");
}
return 0;
}
//登录
int login(int sfd)
{
ssize_t res;
char buf[128]="";
char username[20]="";
char password[20]="";
bzero(username, sizeof(username));
bzero(password, sizeof(password));
while(1)
{
printf("请输入用户名>>");
fgets(username, sizeof(username), stdin);
username[strlen(username)-1]=0;
// getchar();
printf("请输入密码>>");
fgets(password, 10, stdin);
// printf("%s\n", password);
password[strlen(password)-1]=0;
// printf("%s %s", buf+1,buf+1+strlen(username)+1);
/* do
{ bzero(username,sizeof(username));
fgets(username,sizeof(username),stdin);
}while(username[0]=='\n');
username[strlen(username)-1]=0;
printf("请输入密码>>");
do
{ bzero(password,sizeof(password));
fgets(password,sizeof(password),stdin);
}while(password[0]=='\n');
password[strlen(password)-1]=0;*/
// printf("%s %s", buf+1,buf+1+strlen(username)+1);
bzero(buf, sizeof(buf));
//组登录协议语句
sprintf(buf, "%c%s%c%s", LOGIN, username,0, password);
//向服务器发送协议
if(send(sfd, buf, 1+strlen(username)+1+strlen(password),0)<0)
{
perror("send");
return -1;
}
//等待服务器回应
bzero(buf, sizeof(buf));
res = recv(sfd, buf, sizeof(buf), 0);
if(res<0)
{
perror("recv");
return -1;
}
else if(res==0)
{
printf("服务器下线\n");
return -1;
}
//分解buf信息
char c = *buf;
if(c == YES)
{
printf("登录成功\n");
char choose;
while(1)
{
system("clear");
printf("登录成功\n");
printf("*********************\n");
printf("*****1.查询单词******\n");
printf("*****2.历史记录******\n");
printf("*****3.返回上级******\n");
printf("*********************\n");
printf("请选择功能>>");
choose = getchar();
while(getchar() != 10);
switch(choose)
{
//查询单词
case '1':
search_word(sfd);
break;
case '2':
//查询历史记录
search_history(sfd);
break;
case '3':
//退出
//发送下线信息
bzero(buf, sizeof(buf));
sprintf(buf, "%c%s%c",NOTLOGIN, username,0);
if(send(sfd, buf, 1+strlen(username)+1,0)<0)
{
perror("send");
return -1;
}
return 0;
default:
printf("输入错误,请重新输入\n");
}
printf("请输入任一字符清屏>>");
while(getchar() != 10);
}
}
else if(c == NO)
{
printf("账号密码错误\n");
}
else if(c == LOGIN)
{
printf("重复登录\n");
}
}
return 0;
}
//查找单词
int search_word(int sfd)
{
char buf[128]="";
char word[20]="";
ssize_t res;
//循环查找单词
while(1)
{
printf("请输入查询单词“qu”可以退出>>");
fgets(word, sizeof(word), stdin);
word[strlen(word)-1]=0;
if(strcmp(word, "qu")==0)
{
return -1;
}
//发送word协议查找单词
sprintf(buf, "%c%s%c", WORD,word, 0);
if(send(sfd, buf, 1+strlen(word)+1, 0)<0)
{
perror("send");
return -1;
}
printf("%s\n", word);
//等待服务器回应是否存在单词
bzero(buf, sizeof(buf));
res = recv(sfd, buf, sizeof(buf), 0);
if(res < 0)
{
perror("recv");
return -1;
}
else if(res == 0)
{
printf("服务器下线\n");
}
//拆解buf信息
char c;
c = *buf;
//存在单词
if(c == YES)
{
while(1)
{
//告诉服务器可以继续发送
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", YES, 0);
if(send(sfd, buf, 2, 0)<0)
{
perror("send");
return -1;
}
//等待服务器回应单词内容
bzero(buf, sizeof(buf));
res = recv(sfd, buf, sizeof(buf), 0);
if(res < 0)
{
perror("recv");
return -1;
}
if(res ==0)
{
printf("服务器下线\n");
}
char p = *buf;
if(p == NO)
{
//说明传输完成
break;
}
//提取单词意思
char* mean = buf+strlen(buf)+1;
printf("%-10s\t%s\n", buf, mean);
}
}
else if(c == NO)
{
printf("没有这个单词\n");
}
}
return 0;
}
//查询历史记录
int search_history(int sfd)
{
char buf[128]="";
ssize_t res;
sprintf(buf, "%c%c", HISTORY, 0);
if(send(sfd, buf, 2, 0)<0)
{
perror("send");
return -1;
}
//等待服务器回应
bzero(buf, sizeof(buf));
res = recv(sfd, buf, sizeof(buf), 0);
if(res<0)
{
perror("recv");
return -1;
}
else if(res == 0)
{
printf("服务器下线\n");
return -1;
}
//拆解buf信息
char c;
c = *buf;
if(c == YES)
{
while(1)
{
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", YES, 0);
//向服务器发送yes说明可以开始传输数据了
if(send(sfd, buf, 2, 0)<0)
{
perror("send");
return -1;
}
//等待服务器回应单词内容
bzero(buf, sizeof(buf));
res = recv(sfd, buf, sizeof(buf), 0);
if(res < 0)
{
perror("recv");
return -1;
}
if(res ==0)
{
printf("服务器下线\n");
}
char p = *buf;
if(p == NO)
{
//说明传输完成
break;
}
//提取单词意思和时间
char* mean = buf+strlen(buf)+1;
char* time = mean + strlen(mean)+1;
printf("%-10s\t%-20s\t%s\n", buf, mean, time);
}
}
//没有历史记录
else if(c == NO)
{
printf("没有历史记录\n");
}
printf("****over*****\n");
return 0;
}
服务器代码
#include <stdio.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <sys/types.h>
#include <string.h>
#include <unistd.h>
#include <pthread.h>
#include <stdlib.h>
#include <sqlite3.h>
#define NOTLOGIN 0
#define LOGIN 1
#define YES 2
#define NO 3
#define REGISTER 4
#define WORD 5
#define HISTORY 6
void update_user(sqlite3 *db, char* username);
sqlite3* create();
int do_insert(sqlite3* db);
#define IP "192.168.31.215"
#define ERR_MSG(msg) do{\
fprintf(stderr, "__%d__", __LINE__);\
perror(msg);\
}while(0)
void* callback(void* arg);
struct msg
{
int newfd;
struct sockaddr_in sed;
sqlite3* db;
};
int main(int argc, const char *argv[])
{
//创建并打开数据库以及历史记录表。用户表,单词表
sqlite3* db;
db = create();
//创建流式套接字
int sfd = socket(AF_INET,SOCK_STREAM, 0);
if(sfd<0)
{
ERR_MSG("socket");
return -1;
}
//允许端口快速重用
int reuse=1;
if(setsockopt(sfd, SOL_SOCKET, SO_REUSEADDR, &reuse, sizeof(reuse))<0)
{
ERR_MSG("setsockopt");
return -1;
}
printf("允许端口快速重用设置成功\n");
//填充ip和端口到地址信息集团构体中
struct sockaddr_in sin;
sin.sin_family =AF_INET;
sin.sin_port =htons(8888);
sin.sin_addr.s_addr =inet_addr(IP);
//绑定服务器的ip端口和地址,必须绑定
if(bind(sfd, (struct sockaddr*)&sin, sizeof(sin))<0)
{
ERR_MSG("bind");
return -1;
}
//将套接字设置为被动监听状态
if(listen(sfd, 10)<0)
{
ERR_MSG("listen");
return -1;
}
printf("listen succes\n");
struct sockaddr_in sed;
int a=sizeof(sed);
pthread_t tid;
struct msg info;
while(1)
{
//循环链接客户端获取newfd
int newfd=accept(sfd, (struct sockaddr*)&sed, &a);
if(newfd<0)
{
ERR_MSG("accept");
return -1;
}
printf("newfd=%d\n", newfd);
info.newfd=newfd;
info.sed=sed;
info.db=db;
//创建新线程
pthread_create(&tid, NULL, callback, (void*)&info);
pthread_detach(tid);
}
close(sfd);
return 0;
}
void* callback(void* arg)
{
//接收传参来的数据
struct msg info=*(struct msg*)arg;
int newfd = info.newfd;
struct sockaddr_in sed=info.sed;
//数据库首地址
sqlite3* db = info.db;
char buf[128]="";
ssize_t res=0;
//定义用户名全局变量,不同case语句都可使用
char username[20]="";
char password[20]="";
char choose;
printf("[port: %d ip: %s]用户连接\n",ntohs(sed.sin_port), inet_ntoa(sed.sin_addr));
while(1)
{
bzero(buf, sizeof(buf));
//首次接收客户端信息
res=recv(newfd, buf, sizeof(buf), 0);
// printf("%s\n", buf+1);
if(res<0)
{
ERR_MSG("recv");
break;
}
else if(0==res)
{
fprintf(stderr, "newfd=%d客户端下线\n", newfd);
update_user(db, username);
break;
}
//拆解buf信息
choose = *buf;
switch(choose)
{
//注册
case REGISTER:
{
bzero(username, sizeof(username));
strcpy(username, buf+1);
strcpy(password, buf+1+strlen(username)+1);
bzero(buf, sizeof(buf));
char* errmsg = NULL;
char** pres;
int row, column;
//查找是否存在用户
sprintf(buf, "select* from user where username=\"%s\";", username);
if(sqlite3_get_table(db, buf,&pres, &row, &column, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_get_table: %s\n", __LINE__, errmsg);
return NULL;
}
if(row != 1)
{
//不存在该用户则可以注册
//发送YES给客户端
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", YES, 0);
if(send(newfd, buf, sizeof(buf), 0)<0)
{
perror("send");
return NULL;
}
bzero(buf, sizeof(buf));
//组插入语句将用户插入数据库
sprintf(buf, "insert into user values(\"%s\",\"%s\", %d);",username, password,NOTLOGIN);
if(sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_ex: %s\n", __LINE__, errmsg);
return NULL;
}
printf("存入数据库成功\n");
}
//用户已存在
else
{
//用户已经存在不可以注册
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", NO, 0);
//发送no
if(send(newfd, buf, sizeof(buf), 0)<0)
{
perror("send");
return NULL;
}
}
//释放堆区空间
sqlite3_free_table(pres);
pres = NULL;
}break;
case LOGIN:
{
bzero(password, sizeof(password));
bzero(username, sizeof(username));
//提取用户名和密码
strcpy(username, buf+1);
strcpy(password, buf+1+strlen(username)+1);
bzero(buf, sizeof(buf));
char* errmsg = NULL;
char** pres;
int row=0, column=0;
//查找账户是否存在
// printf("%s\n", username);
sprintf(buf, "select* from user where username=\"%s\" and password=\"%s\";", username, password);
if(sqlite3_get_table(db, buf,&pres, &row, &column, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_get_table: %s\n", __LINE__, errmsg);
return NULL;
}
//账户存在
if(row == 1)
{
bzero(buf, sizeof(buf));
sprintf(buf, "select* from user where username=\"%s\" and password=\"%s\" and stat=%d;", username, password, NOTLOGIN);
if(sqlite3_get_table(db, buf,&pres, &row, &column, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_get_table: %s\n", __LINE__, errmsg);
return NULL;
}
//账户存在且若果不为登录状态则可以登录
if(row == 1)
{
bzero(buf, sizeof(buf));
//发送YES
sprintf(buf, "%c%c", YES, 0);
if(send(newfd, buf, 2, 0)<0)
{
perror("send");
return NULL;
}
//修改登录装填值为登录状态
bzero(buf, sizeof(buf));
sprintf(buf, "update user set stat=%d where username=\"%s\";", LOGIN, username);
if(sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_ex: %s\n", __LINE__, errmsg);
return NULL;
}
printf("登录成功\n");
break;
}
//否则告诉客户端已经登录
else
{
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", LOGIN, 0);
if(send(newfd, buf, 2, 0)<0)
{
perror("send");
return NULL;
}
}
}
//账号不存在或者密码错误
else
{
// printf("%s%s\n", username, password);
bzero(buf, sizeof(buf));
//向客户端发送NO说明密码错误或者账户不存在
sprintf(buf, "%c%c", NO, 0);
if(send(newfd, buf, 2, 0)<0)
{
perror("send");
return NULL;
}
}
sqlite3_free_table(pres);
pres=NULL;
}break;
//查找单词
case WORD:
{
char* errmsg = NULL;
char** pres;
int row=0, column=0;
char mean[20]="";
char word[20]="";
ssize_t res;
strcpy(word, buf+1);
printf("%s\n", buf+1);
bzero(buf, sizeof(buf));
//查找单词是否存在,组查询语句
sprintf(buf, "select* from dict where word=\"%s\";", word);
if(sqlite3_get_table(db, buf,&pres, &row, &column, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_get_table: %s\n", __LINE__, errmsg);
return NULL;
}
//单词存在
if(row>0)
{
bzero(buf, sizeof(buf));
//告诉客户端单词存在
sprintf(buf, "%c%c", YES, 0);
if(send(newfd, buf, 2, 0)<0)
{
perror("send");
return NULL;
}
//等待接收客户端信息是否可以继续发送
bzero(buf, sizeof(buf));
res = recv(newfd, buf, sizeof(buf), 0);
if(res < 0)
{
perror("recv");
return NULL;
}
else if(res == 0)
{
printf("客户端下线\n");
//需要更新用户登录状态值
/
update_user(db, username);
return NULL;
}
//提取单词和意思
int i;
bzero(buf, sizeof(buf));
//去除第一行字段
for(i=column; i<(row+1)*column; i++)
{
if(i % column ==0)
{
strcpy(word, pres[i]);
}
if(i % column == (column -1))
{
strcpy(mean, pres[i]);
//整合一个数组
sprintf(buf, "%s%c%s", word, 0, mean);
if(send(newfd, buf, strlen(word)+1+strlen(mean)+1, 0)<0)
{
perror("send");
return NULL;
}
//获取时间
bzero(buf, sizeof(buf));
time_t t;
struct tm* info = NULL;
char time_record[20]="";
t = time(NULL);
info = localtime(&t);
//整合时间数组
sprintf(time_record, "%d-%d-%d %d:%d", info->tm_year+1900, info->tm_mon+1, \
info->tm_mday, info->tm_hour, info->tm_min);
//整合插入历史记录语句
sprintf(buf, "insert into record values(\"%s\", \"%s\", \"%s\", \"%s\");", \
username, word, mean, time_record);
char* errmsg = NULL;
if(sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_ex: %s\n", __LINE__, errmsg);
return NULL;
}
printf("%s历史记录插入成功\n", username);
bzero(buf, sizeof(buf));
res = recv(newfd, buf, sizeof(buf), 0);
if(res<0)
{
perror("send");
return NULL;
}
if(res == 0)
{
printf("客户端下线\n");
//更新用户状态信息
update_user(db, username);
}
char c;
c = *buf;
if(c == YES)
{
//说明可以继续发送
bzero(buf, sizeof(buf));
}
else if(c == NO)
{
//不在进行传输
printf("停止传输\n");
break;
}
}
}
printf("传输完成\n");
//向客户端发送no说明传输完成
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", NO, 0);
if(send(newfd, buf, 2, 0)<0)
{
perror("send");
return NULL;
}
}
else
{
printf("单词不存在\n");
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", NO, 0);
if(send(newfd, buf, 2, 0)<0)
{
perror("send");
return NULL;
}
}
sqlite3_free_table(pres);
pres = NULL;
}
break;
case HISTORY:
{
printf("__%d__\n",__LINE__);
char* errmsg = NULL;
char** pres;
int row=0, column=0;
char mean[20]="";
char word[20]="";
char time[20]="";
ssize_t res;
strcpy(word, buf+1);
printf("%s\n", buf+1);
bzero(buf, sizeof(buf));
//查找历史用户是否存在,组查询语句
sprintf(buf, "select* from record where username=\"%s\";", username);
if(sqlite3_get_table(db, buf,&pres, &row, &column, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_get_table: %s\n", __LINE__, errmsg);
return NULL;
}
//如果存在历史记录
if(row>0)
{
//向客户端发送yes说明有记录,打算发送数据了
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", YES, 0);
if(send(newfd, buf, 2, 0)<0)
{
ERR_MSG("send");
return NULL ;
}
//等待接收客户端消息
bzero(buf, sizeof(buf));
res = recv(newfd, buf, sizeof(buf), 0);
if(res <0)
{
ERR_MSG("recv");
return NULL;
}
else if(res == 0)
{
printf("客户端下线\n");
//需要将登录状态值改为NOTLOGIN;
update_user(db, username);
break;
}
//拆解buf信息 默认一定为yes
//提取查询到的历史记录信息
int i;
//忽略表头
bzero(buf, sizeof(buf));
for(i=column; i<(row+1)*column; i++)
{
if(i%column==1)
{
strcpy(word, pres[i]);
}
else if(i%column==2)
{
strcpy(mean, pres[i]);
}
else if(i%column==3)
{
strcpy(time, pres[i]);
//整合到一个数组里
sprintf(buf, "%s%c%s%c%s", word, 0, mean, 0, time);
if(send(newfd, buf, strlen(word)+1+strlen(mean)+1+strlen(time), 0)<0)
{
ERR_MSG("send");
return NULL;
}
//发送一个等待回应是否可以继续发送
bzero(buf, sizeof(buf));
res = recv(newfd, buf, sizeof(buf, 0), 0);
if(res < 0)
{
ERR_MSG("recv");
return NULL;
}
else if(res == 0)
{
printf("客户端下线\n");
//更新用户状态信息
update_user(db, username);
break;
}
//判断buf信息
if(*buf == YES)
{
//说明可以继续发送
}
else if(*buf == NO)
{
//不让再发送
break;
}
}
}
printf("数据发送完毕\n");
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", NO, 0);
//向客户发送no 说明发送完毕
if(send(newfd, buf,2,0)<0)
{
ERR_MSG("send");
return NULL;
}
}
//没有历史记录
else
{
bzero(buf, sizeof(buf));
sprintf(buf, "%c%c", NO, 0);
//向客户发送no 说明没有历史记录
if(send(newfd, buf,2,0)<0)
{
ERR_MSG("send");
return NULL;
}
}
sqlite3_free_table(pres);
pres=NULL;
}
break;
case NOTLOGIN:
{
update_user(db, username);
}
break;
default:
printf("未知信息\n");
}
}
}
sqlite3* create()
{
char sql[128]="";
sqlite3* db = NULL;
if(sqlite3_open("./stu.db", &db)!=SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_open: %s\n",__LINE__, sqlite3_errmsg(db));
fprintf(stderr, "error_code:%d", sqlite3_errcode(db));
return NULL;
}
//单词表
printf("sqlite3 open success\n");
strcpy(sql,"create table if not exists dict( word char, mean char);");
char* errmsg = NULL;
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_exec: %s\n", __LINE__, errmsg);
return NULL;
}
printf("create table dict success\n");
//历史记录表
bzero(sql, sizeof(sql));
strcpy(sql,"create table if not exists record( username char, word char, mean char, time char);");
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_exec: %s\n", __LINE__, errmsg);
return NULL;
}
printf("create table record success\n");
//用户表
bzero(sql, sizeof(sql));
strcpy(sql,"create table if not exists user( username char primary key, password char, stat cahr);");
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_exec: %s\n", __LINE__, errmsg);
return NULL;
}
printf("create table user success\n");
//插入单词表
printf("下载文件中》》");
do_insert(db);
return db;
}
int do_insert(sqlite3* db)
{
FILE *fp =fopen("./dict.txt", "r");
char buf[50]="";
int i=0;
char world[15]="";
char mean[20]="";
int len=1;
char sql[128]="";
while(1)
{
//循环读取文件
bzero(buf, sizeof(buf));
if(fgets(buf, sizeof(buf), fp)==NULL)
{
break;
}
buf[strlen(buf)-1]=0;
char* p = buf;
//遍历判断单词和意思的位置
for(i=0; i<strlen(buf); i++)
{
if(*(p+i)==' ' && *(p+i+1)==' ' && *(p+i+2)==' ')
{
*(p+i) = '\0';
strcpy(world, p);
strcpy(mean, p+i+2);
sprintf(sql, "insert into dict values( \"%s\", \"%s\");", world, mean);
char* errmsg = NULL;
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_exec: %s\n", __LINE__, errmsg);
return -1;
}
len++;
}
}
}
printf("单词表插入成功\n");
return 0;
}
void update_user(sqlite3* db, char* username)
{
char* errmsg = NULL;
char** pres;
int row=0, column=0;
char buf[128]="";
ssize_t res;
// strcpy(word, buf+1);
// printf("%s\n", buf+1);
bzero(buf, sizeof(buf));
//查找历史用户是否存在,组查询语句
sprintf(buf, "select* from user where username=\"%s\"and stat=%d;", username, LOGIN);
if(sqlite3_get_table(db, buf,&pres, &row, &column, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_get_table: %s\n", __LINE__, errmsg);
return ;
}
//存在已经登录的账号信息
if(row > 0)
{
bzero(buf, sizeof(buf));
//修改登录状态值
sprintf(buf, "update user set stat=%d where username=\"%s\";", NOTLOGIN, username);
if(sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "__%d__sqlite3_ex: %s\n", __LINE__, errmsg);
return ;
}
printf("用户: %s已下线\n", username);
}
else
{
printf("无用户上线\n");
}
}
运行结果
注册
登录
查询单词
查询历史记录
验证是否可以重复登录
验证是否可以重复注册