func.c
#include "./func.h"
//printf("%s %d\n",__FILE__,__LINE__);//查找错误
int deal_cli_msg(void* meg, sqlite3* db,int cfd)
{
if(0 == strcmp(((struct cli*)meg)->c.str,"reg"))
{
//查找用户是否已经被注册
or_reg(meg, db, cfd);
}
else if(0 == strcmp(((struct cli*)meg)->c.str,"loin"))
{
//查找用户是否已经登录
or_loin(meg, db, cfd);
}
else if(0 == strcmp(((struct cli*)meg)->c.str,"quit"))
{
printf("将数据库中用户状态改为下线\n");
do_update(((struct cli*)meg)->db, "0", "user", ((struct cli*)meg)->c.a.user);
}
else if(0 == strcmp(((struct cli*)meg)->c.str,"danci"))
{
//单词查询
danc(meg, db,cfd);
}
else if(0 == strcmp(((struct cli*)meg)->c.str,"gotoprint1"))
{
printf("选择了返回一级菜单的功能\n");
do_update(((struct cli*)meg)->db, "0", "user", ((struct cli*)meg)->c.a.user);
}
else if(0 == strcmp(((struct cli*)meg)->c.str,"recordlook"))
{
printf("选择了查询用户历史记录的功能\n");
recordlook(meg, db, cfd, "record");
}
}
//注册和登录
int reguser(void* meg, sqlite3* db, int cfd, char* str)
{
ssize_t res_rec;
int num = 0;
//接收用户名
res_rec = msg_rec(cfd,meg);
if(-1 == res_rec)
{
return -1;
}
strcpy(((struct cli*)meg)->c.a.user,((struct cli*)meg)->massage);
//接收密码
res_rec = msg_rec(cfd,meg);
if(-1 == res_rec)
{
return -1;
}
strcpy(((struct cli*)meg)->c.a.passd,((struct cli*)meg)->massage);
num = sql_reg(meg, db, str);
return num;
}
//获取时间函数
int time_fun(void* meg)
{
time_t tim;
time(&tim);
struct tm* p = localtime(&tim);
sprintf(((struct cli*)meg)->d.time,"%4d/%02d/%02d\t%02d:%02d",p->tm_year+1900,
p->tm_mon+1, p->tm_mday, p->tm_hour, p->tm_min);
return 0;
}
//客户端退出后释放资源相关操作
int rel_sour(void* meg)
{
do_update(((struct cli*)meg)->db, "0", "user", ((struct cli*)meg)->c.a.user);
free((*(struct cli*)meg).massage);
//free((*(struct cli*)meg).c.a.user);
free((*(struct cli*)meg).c.a.passd);
free((*(struct cli*)meg).c.a.online);
free((*(struct cli*)meg).c.str);
}
//查找用户是否已经被注册
int or_reg(void* meg, sqlite3* db,int cfd)
{
int num = 0;
char buf[40] = "";
printf("数据库中查找用户是否已经被注册\n");
num = reguser(meg, db, cfd, "user");
if( num == 0)
{
strcpy(buf, "user is exit!");
msg_send(cfd, (void*)buf);
}
else if(num < 0)
{
if(0 == do_insert(db, "user", meg))
strcpy(buf, "user reg success!");
msg_send(cfd,(void*)buf);
}
}
//查找用户是否已经登录
int or_loin(void* meg, sqlite3* db,int cfd)
{
int num = 0;
char buf[40] = "";
printf("数据库中查找用户状态是否已被登录\n");
num = reguser(meg, db, cfd, "loin");
if( -2 == num)
{
strcpy(buf, "user is loin!");
msg_send(cfd, (void*)buf);
}
else if(-3 == num)
{
strcpy(buf, "user or passd error!");
msg_send(cfd, (void*)buf);
}
else if(0 == num)
{
printf("%d\n",((struct cli*)meg)->flag);
do_update(db, "1", "user", ((struct cli*)meg)->c.a.user);
strcpy(buf, "user loin success!");
msg_send(cfd,(void*)buf);
printf("%s登录成功\n", ((struct cli*)meg)->c.a.user);
}
}
//单词查询
int danc(void* meg, sqlite3* db,int cfd)
{
printf("选择了查询单词功能\n");
msg_rec(cfd, meg);
((struct cli*)meg)->d.b.eng = (char*)malloc(60);
((struct cli*)meg)->d.b.chinese = (char*)malloc(60);
((struct cli*)meg)->d.time = (char*)malloc(60);
strcpy(((struct cli*)meg)->d.b.eng,((struct cli*)meg)->massage);
dancilook(meg, db, cfd, "danci");
printf("单词查询完毕\n");
free(((struct cli*)meg)->d.b.eng);
free(((struct cli*)meg)->d.b.chinese);
free(((struct cli*)meg)->d.time);
}
ser_main.c
#include <stdio.h>
#include "./sql.h"
#include "./tcp_ser.h"
//printf("%s %d\n",__FILE__,__LINE__);//查找错误
int main(int argc, const char *argv[])
{
//1.数据库初始化
sqlite3* db = (sqlite3*)sql_init();
printf("数据库初始化完毕\n");
//2.服务器初始化
int sfd = ser_init();
printf("服务器初始化完毕\n");
//2.等待客户端连接
deal_connect(sfd, db);
return 0;
}
sql.c
#include "./sql.h"
//printf("%s %d\n",__FILE__,__LINE__);//查找错误
//初始化
void* do_open()
{
sqlite3* db = NULL;
char str[20] = "";
//创建并打开数据库
strcpy(str,"./my.db");//需要打开的数据库;
if(sqlite3_open(str, &db) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_open:%s\t%d", sqlite3_errmsg(db), __LINE__);
return NULL;
}
printf("database open success!\n");
return (void*)db;
}
//创建表
int do_create(sqlite3* db,char* str)
{
char* sql = (char*)malloc(80);
*sql = 0;
char* errmsg = NULL;
if(0 == strcmp(str,"user"))
{
sprintf(sql,"create table if not exists %s (user char primary key, passd char, stat char);", str);//创建用户表
}
else if(0 == strcmp(str,"danci"))
{
sprintf(sql,"create table if not exists %s (eng char, chinese char);",str);//创建单词表
}
else if(0 == strcmp(str,"record"))
{
sprintf(sql,"create table if not exists %s (user char, eng char, chinese char, time char);",str);//创建历史记录表
}
//printf("%s",sql);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg))
{
fprintf(stderr, "sqlite3_exec:%s\t%d\n", errmsg, __LINE__);
puts(sql);
return -1;
}
printf("create %s table success!\n", str);
free(sql);
return 0;
}
//增
int do_insert(sqlite3* db, char* str, void* p)
{
//插入表
char* sql = (char*)malloc(200);
char* errmsg = NULL;
if(0 == strcmp(str,"user"))
{
sprintf(sql,"insert into %s ('user', 'passd', 'stat') values (\"%s\", \"%s\", '0');", str,
((struct cli*)p)->c.a.user, ((struct cli*)p)->c.a.passd);
}
else if(0 == strcmp(str,"danci"))
{
sprintf(sql,"insert into %s ('eng', 'chinese') values (\"%s\", \"%s\");", str,
((struct danciinfo*)p)->eng, ((struct danciinfo*)p)->chinese);
}
else if(0 == strcmp(str,"record"))
{
sprintf(sql,"insert into %s ('user', 'eng', 'chinese', 'time') values (\"%s\", \"%s\", \"%s\", \"%s\");",
str, ((struct cli*)p)->c.a.user, ((struct cli*)p)->d.b.eng,
((struct cli*)p)->d.b.chinese, ((struct cli*)p)->d.time);
}
//printf("%s\n",sql);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg))
{
fprintf(stderr, "sqlite3_exec:%s\t%d\n", errmsg, __LINE__);
fprintf(stderr,"%s\n",sql);
return -1;
}
free(sql);
return 0;
}
//删
int do_delete(sqlite3* db, char* str, void* p)
{
char* sql = (char*)malloc(60);
sprintf(sql,"delete from %s where eng='%s';", str, ((struct danciinfo*)p)->eng);
//printf("%s\n",sql);
char* errmsg = NULL;
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg))
{
fprintf(stderr, "sqlite3_exec:%s\t%d", errmsg, __LINE__);
return -1;
}
//printf("create table success!\n");
free(sql);
return 0;
}
//查询函数调用
int handler(void *arg, int column, char** column_text, char** column_name) //void* arg = &flag
{
//列数
//printf("column=%d __%d__\n", column, __LINE__);
//column_name
char online[1] = "0";
char buf[40] = "";
char* re = (char*)malloc(128);
bzero(re,128);
strcpy(((struct cli*)arg)->c.a.online,online);
int i = 0;
if(0 == (((struct cli*)arg)->flag))
{
for(i=0; i<column; i++)
{
printf("%s\t", column_name[i]);
}
printf("\n");
(((struct cli*)arg)->flag) = 1;
}
//column_text
for(i=0; i<column; i++)
{
//printf("i = %d :%s\t", i, column_text[i]);
if(0 == strcmp(((struct cli*)arg)->c.a.online, column_text[i]))
{
((struct cli*)arg)->num = 0;
}
if(0 == strcmp(((struct cli*)arg)->buf,"danci"))
{
((struct cli*)arg)->num++;
msg_send(((struct cli*)arg)->cfd,column_text[i]);
strcpy(((struct cli*)arg)->d.b.chinese ,column_text[i]);
//printf("%s %d\n",__FILE__,__LINE__);
time_fun(arg);
do_insert(((struct cli*)arg)->db, "record", arg);
}
if(0 == strcmp(((struct cli*)arg)->buf,"record"))
{
((struct cli*)arg)->num++;
sprintf(re,"%s %s ", re, column_text[i]);
}
}
if(0 == strcmp(((struct cli*)arg)->buf,"record"))
{
sprintf(re,"%s\n",re);
msg_send(((struct cli*)arg)->cfd,re);
printf("%s",re);
}
free(re);
return 0;
}
//查
int do_select(sqlite3* db, char* p, char* str, void* meg)
{
char sql[128] = "";
char* errmsg = NULL;
((struct cli*)meg)->flag = 0;
((struct cli*)meg)->num = 1;
if(0 == strcmp(str,"user"))
sprintf(sql,"select \"%s\" from \"%s\" where user=\"%s\";", "user", str, p);
else if (0 == strcmp(str,"loin"))
{
sprintf(sql,"select \"%s\" from \"%s\" where user=\"%s\" and passd = \"%s\";", "stat", "user", p, ((struct cli*)meg)->c.a.passd);
}
else if(0 == strcmp(str,"danci"))
{
sprintf(sql,"select \"%s\" from \"%s\" where eng=\"%s\";", "chinese", "danci", ((struct cli*)meg)->d.b.eng);
strcpy(((struct cli*)meg)->buf, "danci");
puts(sql);
}
else if(0 == strcmp(str,"record"))
{
sprintf(sql,"select * from \"%s\" where user=\"%s\";", "record", ((struct cli*)meg)->c.a.user);
strcpy(((struct cli*)meg)->buf, "record");
puts(sql);
}
//puts(sql);
if(sqlite3_exec(db, sql, handler, meg, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}
printf("查询完毕\n");
return 0;
}
//改
int do_update(sqlite3* db, char* p, char* str, char* username)
{
char sql[128] = "";
char* errmsg = NULL;
sprintf(sql,"update \"%s\" set stat=\"%s\" where user=\"%s\";", str, p, username);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg))
{
fprintf(stderr, "sqlite3_exec:%s\t%d", errmsg, __LINE__);
return -1;
}
puts(sql);
}
//关闭
int do_close(sqlite3* db)
{
//关闭数据库
if(sqlite3_close(db) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_close:%s\t%d", sqlite3_errmsg(db), __LINE__);
return -1;
}
}
//将单词插入到数据库中
int danci_insert(sqlite3* db)
{
FILE* fp = fopen("./1.txt","r");
if(NULL == fp)
{
perror("fopen");
return -1;
}
char str[100];
char* p;
struct danciinfo danci;
danci.chinese = (char*)malloc(50);
danci.eng = (char*)malloc(50);
while(1)
{
bzero(str,sizeof(str));
bzero(danci.chinese,50);
bzero(danci.eng,50);
p=str;
if(NULL == fgets(str,sizeof(str),fp))
{
break;
}
str[strlen(str)-1] = 0;
while(((*p) != ' ') || (*(p+1) != ' '))
p++;
*p = 0;
p = p+3;
strcpy(danci.eng,str);
strcpy(danci.chinese,p);
do_insert(db, "danci", (void*)&danci);
//puts(str);
//puts(p);
}
printf("单词导入成功!!!!\n");
free(danci.chinese);
free(danci.eng);
}
//初始化数据库
void* sql_init()
{
sqlite3* db = (sqlite3*)do_open(); //打开一个数据库
//do_create(db,"user"); //创建一个用户信息表
//do_create(db,"danci"); //创建一个单词表
//do_create(db,"record"); //创建一个历史记录表
//将单词插入到数据库中
//danci_insert(db);
return (void*)db;
}
//数据库中查找用户是否已经被注册 是否已经登录
int sql_reg(void* meg, sqlite3* db, char* str)
{
char p[40] = "";
if((0 == strcmp(str, "user"))||(0 == strcmp(str,"loin")))
strcpy(p,((struct cli*)meg)->c.a.user);
int num = do_select(db, p, str, meg);
printf("数据库查找操作\n");
if(0 == ((struct cli*)meg)->flag)
{
if(((struct cli*)meg)->num)
return -3;
else
return -3;
}
else if(((struct cli*)meg)->flag > 0)
{
if(((struct cli*)meg)->num)
{
if(0 == strcmp(str, "user"))
return 0;
else
return -2;
}
else
return 0;
}
}
//单词查询
int dancilook(void* meg, sqlite3* db, int cfd, char* p)
{
do_select(db, "chinese", "danci", meg);
//printf("%s %d\n",__FILE__,__LINE__);
if(((struct cli*)meg)->num > 1)
msg_send(cfd,"123456");
else if(1 == ((struct cli*)meg)->num)
msg_send(cfd,"123456789");
}
//历史记录
int recordlook(void* meg, sqlite3* db, int cfd, char* p)
{
do_select(db, "*", "record", meg);
if(((struct cli*)meg)->num > 1)
msg_send(cfd,"123456");
else if(1 == ((struct cli*)meg)->num)
msg_send(cfd,"123456789");
}
tcp_ser.c
#include "./tcp_ser.h"
//printf("%s %d\n",__FILE__,__LINE__);//查找错误
//线程执行体
void* callback(void* p)
{
//线程分离
pthread_detach(pthread_self());
struct cli meg1 = *(struct cli*)p;
void* meg = (void*)&meg1;
//资源申请
sou_ask(meg);
int cfd = ((struct cli*)meg)->cfd;
//信息处理
xin(meg, cfd);
//do_update(((struct cli*)meg)->db, "0", "user", ((struct cli*)meg)->c.a.user);
printf("cfd = %d 退出了\n", cfd);
close(cfd);
printf("vfd = %d关闭成功\n",cfd);
rel_sour(meg);
printf("资源释放成功\n");
pthread_exit(NULL);
}
int ser_init()
{
int sfd;
struct sockaddr_in server_addr;
//1.创建流式套接字
sfd = socket(AF_INET, SOCK_STREAM, 0);
if (sfd < 0)
{
ERROR("socket");
return -1;
}
memset(&server_addr, 0, sizeof(struct sockaddr_in));
//填充地址信息结构体
server_addr.sin_family = AF_INET;
server_addr.sin_port = htons(PORT); //端口的网络字节序
server_addr.sin_addr.s_addr = inet_addr(IP); //IP的网络字节序
//允许端口快速被重用
int reuse = 1;
if(setsockopt(sfd, SOL_SOCKET, SO_REUSEADDR, &reuse ,sizeof(reuse)) < 0)
{
ERROR("setsockopt");
return -1;
}
//2.将地址信息绑定到套接字上
if (bind(sfd, (struct sockaddr *) &server_addr,
sizeof(struct sockaddr_in)) < 0)
{
ERROR("bind");
return -1;
}
//3.将sfd设置为被动监听状态
if (listen(sfd, 128) < 0)
{
ERROR("listen");
return -1;
}
printf("服务器正在监听....\n");
return sfd;
}
int deal_connect(int sfd, sqlite3* db)
{
struct sockaddr_in server_addr,client_addr;
socklen_t client_addr_size;
int cfd;
struct cli info;
pthread_t tid;
//创建线程用收发信息
client_addr_size = sizeof(struct sockaddr_in);
while(1)
{
//4.从队列头获取一个客户的信息
cfd = accept(sfd, (struct sockaddr *) &client_addr,
&client_addr_size);
if (cfd < 0)
{
ERROR("accept");
return -1;
}
printf("[%s:%d] cfd = %d 连接成功\n", \
inet_ntoa(client_addr.sin_addr), \
ntohs(client_addr.sin_port), cfd);
//创建线程用于接待客户端
info.cfd = cfd;
info.client_addr = client_addr;
info.db = db;
if(pthread_create(&tid, NULL, callback,(void*)&info) < 0)
{
ERROR("pthread_create");
return -1;
}
}
}
int msg_send(int cfd,void* meg)
{
char buf[MAX] = "";
ssize_t res = 0;
bzero(buf, sizeof(buf));
strcpy(buf, (char*)meg);
//6.发送来自客户端信息
if((res = send(cfd, buf, sizeof(buf), 0)) < 0)
{
ERROR("send");
}
return res;
}
int msg_rec(int cfd,void* meg)
{
struct sockaddr_in client_addr = ((struct cli*)meg)->client_addr;
ssize_t res = 0;
char buf[MAX] = "";
//5.接收来自客户端信息
res = recv(cfd,buf, sizeof(buf), 0);
if(res < 0)
{
ERROR("recv");
return -1;
}
if(0 == res)
{
do_update(((struct cli*)meg)->db, "0", "user", ((struct cli*)meg)->c.a.user);
printf("客户端已关闭\n");
return -1;
}
strcpy(((struct cli*)meg)->massage,buf);
printf("[%s:%d]res = %ld :%s\n",
inet_ntoa(client_addr.sin_addr),
ntohs(client_addr.sin_port), res, (((struct cli*)meg)->massage));
return res;
}
//信息处理
int xin(void* meg, int cfd)
{
ssize_t res_rec,res_send;
while(1)
{
//接收来自客户端的信息
res_rec = msg_rec(cfd,meg);
if(-1 == res_rec)
{
break;
}
strcpy(((struct cli*)meg)->c.str,((struct cli*)meg)->massage);
//处理来自客户端的信息
deal_cli_msg(meg, ((struct cli*)meg)->db,cfd);
}
}
//资源申请
int sou_ask(void* meg)
{
(*(struct cli*)meg).massage = (char*)malloc(40);
(*(struct cli*)meg).c.a.user = (char*)malloc(40);
(*(struct cli*)meg).c.a.passd = (char*)malloc(40);
(*(struct cli*)meg).c.a.online = (char*)malloc(40);
(*(struct cli*)meg).c.str = (char*)malloc(40);
}
Makefile
all:ser
ser: func.o ser_main.o sql.o tcp_ser.o
gcc $^ -o ser -lsqlite3 -lpthread
%.o:%.c
gcc -c $< -o $@
clean:
rm *.o
func.h
#ifndef __FUN_H__
#define __FUN_H__
#include <stdio.h>
#include "./sql.h"
#include "./tcp_ser.h"
#include <time.h>
//处理消息
int deal_cli_msg(void* meg, sqlite3* db, int cfd);
//用户注册
int reguser(void* meg, sqlite3* db,int cfd, char* str);
//获取时间函数
int time_fun(void* meg);
//客户端退出后释放资源相关操作
int rel_sour(void* meg);
//查找用户是否已经被注册
int or_reg(void* meg, sqlite3* db,int cfd);
//查找用户是否已经登录
int or_loin(void* meg, sqlite3* db,int cfd);
//单词查询
int danc(void* meg, sqlite3* db,int cfd);
#endif
sql.h
#ifndef __SQL_H__
#define __SQL_H__
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <sys/stat.h>
#include "./tcp_ser.h"
struct userinfo
{
char* user;
char* passd;
char* online;
};
struct danciinfo
{
char* eng;
char* chinese;
};
struct recordinfo
{
char* user;
struct danciinfo b;
char* time;
};
struct msga
{
struct userinfo a;
char* str;
};
struct cli
{
int cfd;
struct sockaddr_in client_addr;
char *massage;
sqlite3* db;
struct msga c;
int flag;
struct recordinfo d;
int num;
char buf[4];
};
//初始化数据库
void* sql_init(void);
//打开某一个数据库
void* do_open(void);
//创建表
int do_create(sqlite3* db, char* str);
//增
int do_insert(sqlite3* db, char* str, void* p);
//删
int do_delete(sqlite3* db, char* str,void* p);
//查
int do_select(sqlite3* db, char* p, char* str, void* meg);
//改
int do_update(sqlite3* db, char* p, char* str, char* username);
//关闭
int do_close(sqlite3* db);
//将单词插入到数据库中
int danci_insert(sqlite3* db);
//数据库中查找用户是否已经被注册
int sql_reg(void* meg, sqlite3* db, char* str);
//数据库中查找用户状态是否已被登录
int sql_loin(char* p, sqlite3* db);
//将数据库中用户状态改为下线
int sql_quit(char* p, sqlite3* db);
//单词查询
int dancilook(void* meg, sqlite3* db, int cfd, char* p);
//历史记录查询
int recordlook(void* meg, sqlite3* db, int cfd, char* p);
#endif
tcp_ser.h
#ifndef __TCP_SER_H__
#define __TCP_SER_H__
#include <stdio.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <unistd.h>
#include <string.h>
#include <signal.h>
#include <sys/wait.h>
#include <stdlib.h>
#include <pthread.h>
#include "./func.h"
#include "./sql.h"
#define IP "192.168.8.101" //服务器 IP
#define PORT 8888 //端口号
#define MAX 128
#define ERROR(msg) do{\
fprintf(stderr, "%s:__%d__", __FILE__, __LINE__);\
perror(msg);\
}while(0)
//线程执行体
void* callback(void* meg);
//服务端初始化
int ser_init();
//等待客户端连接
int deal_connect(int sfd, sqlite3* db);
//发送信息
int msg_send(int cfd, void* meg);
//接收信息
int msg_rec(int cfd, void* meg);
//处理客户端发送的信息
int deal_msg(void* meg);
//信息处理
int xin(void* meg, int cfd);
//资源申请
int sou_ask(void* meg);
#endif