SQLite实现在线电子词典

需求:

服务器:

1).提供英英方式的单词查询

2).同时记录用户的查询历史

2).客户机登陆需要密码

客户机:

1).登陆需要密码, 并且提供注册新用户功能, 注意在用户输入密码的时候不能显示密码,就像linux登陆时的那样

2).用户的查询即时给予回复

3).用户可以查询自己的查询历史

4).用户也可以清除自己的历史记录

下面是源码, 由于时间仓促,代码难免比较粗糙,希望谅解!

server.h:

/*************************************************************************
	> File Name: server.h
	> Author: Baniel Gao
	> Mail: createchance@163.com 
	> Created Time: Wed 15 Jan 2014 09:39:40 AM CST
 ************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <termios.h>
#include <unistd.h>
#include <string.h>
#include <sqlite3.h>
#include <errno.h>
#include <error.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <sys/ioctl.h>
#include <arpa/inet.h>
#include <netinet/in.h>
#include <netinet/ip.h>
#include <signal.h>

#define BUFF_SIZE		32
/*The len of sql*/
#define SQL_LEN			512

/*The max len of username*/
#define MAX_USERNAME	256
/*The max len of word and password*/
#define MAX_WORD		128
/*The max len of explaintion to word*/
#define EXP_LEN			1024

/*The flag of status
 *	R for register
 *	L for login
 *	Q for quary
 *	H for history
 *	S for client stop
 *	C for clear history
 * */
#define	R				1
#define L				2
#define Q				3
#define H				4
#define S				5
#define C				6

/*flags to show user status*/
#define ON_LINE			1
#define	OFF_LINE		0

/*network infomation*/
#define SERVER_IP		"0.0.0.0"
#define SERVER_PORT		50000

/*text dictionary path*/
#define DIC_PATH		"./dict/dic.txt"

/*system and user log path */
#define SYS_LOG			"./log/sys.log"
#define USER_LOG		"./log/user.log"

/*flags to set term display mode*/
#define ECHOFLAGS (ECHO | ECHOE | ECHOK | ECHONL)

/*error handler function*/
#define error_exit(_errmsg_)	error(EXIT_FAILURE, errno, _errmsg_)

/*client message struct*/
typedef struct _client_msg_ {
	char type;
	char name[MAX_USERNAME];
	char data[MAX_WORD];
} climsg_st;

/*server message struct*/
typedef struct _server_msg_ {
	char type;
	char text[EXP_LEN];
} sermsg_st;

/*functions to deal user quary*/
void menu(void);
void login_reg(void);
int show_history(sqlite3 *db, int connfd);
int clear_history(sqlite3 *db, int connfd);
int login(sqlite3 *db, int connfd);
int logout(sqlite3 *db, int connfd);
int regist(sqlite3 *db, int connfd);
int set_disp_mode(int fd,int option);
int quary(sqlite3 *db, int connfd);

server.c:

/*************************************************************************
	> File Name: server.c
	> Author: Baniel Gao
	> Mail: createchance@163.com 
	> Blog: blog.csdn.net/createchance 
	> Created Time: Wed 15 Jan 2014 11:52:12 AM CST
 ************************************************************************/
#include "server.h"

/*client and server message struct*/
climsg_st climsg;
sermsg_st sermsg;

int main(void)
{
	FILE *dictfp, *sysfp, *userfp;
	int sockfd, connfd;
	int ret;
	int lines = 0, alllines = 19661;
	pid_t pid;
	struct sockaddr_in server_addr, client_addr;
	socklen_t addrlen;
	sqlite3 *db;
	char explain[EXP_LEN];
	char *token;
	char sql[SQL_LEN];
	char *errmsg;

/*open local database my.db*/
	if (0 != sqlite3_open("my.db", &db)) {
		printf("error: %s \n", sqlite3_errmsg(db));
		return -1;
	}
/*open local text dictionary for read*/
	if (NULL == (dictfp = fopen(DIC_PATH, "r")))
		error_exit("fopen");
/*open system log for append*/
	if (NULL == (sysfp = fopen(SYS_LOG, "a")))
		error_exit("fopen");
/*open user log for append*/
	if (NULL == (userfp = fopen(USER_LOG, "a")))
		error_exit("fopen");

/*import the dictionary from local text dictionary*/
	while (1) {
		system("clear");
		printf("Importing data...\n");
		if (NULL == fgets(explain, EXP_LEN, dictfp))
			break;
		printf("\e[32m%.1f%% done!\e[0m \n", (lines++) * 100.0 / alllines);
		token = strtok(explain, " ");
		sprintf(sql, "insert into dictionary values(\"%s\", \"%s\");", token, explain + 17);
		if (0 != (ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg))) {
			if (19 != ret) {
				printf("error: %s \n", errmsg);
				return -1;
			}
		}
	}
	printf("Import done! \n");

/*create and open a socket for accept client connect*/
	if (-1 == (sockfd = socket(AF_INET, SOCK_STREAM, 0)))
		error_exit("socket");
	server_addr.sin_family = AF_INET;
	server_addr.sin_port = htons(SERVER_PORT);
	server_addr.sin_addr.s_addr = inet_addr(SERVER_IP);
	addrlen = sizeof(server_addr);
/*server needs to bind the opening socket to local address*/
	if (-1 == bind(sockfd, (const struct sockaddr *)&server_addr, addrlen))
		error_exit("bind");
/*server listen on the socket*/
	if (-1 == listen(sockfd, 10))
		error_exit("listen");
/* make server to ingore the child process exit signal(SIGCHLD),
 * this can help to avoid zombie process
 * */
	signal(SIGCHLD, SIG_IGN);
	while (1) {
/*when there is a connection has come, accept to deal it*/
		if (-1 == (connfd = accept(sockfd, (struct sockaddr *)&client_addr, &addrlen)))
			error_exit("accept");
/*create child process to handler client quary*/
		if (-1 == (pid = fork()))
			error_exit("fork");
		if (pid == 0) {
			while (1) {
				if (-1 == recv(connfd, &climsg, sizeof(climsg), 0))
					error_exit("recv");
				if (S == climsg.type) {
					logout(db, connfd);
					exit(0);
				}
				else if (L == climsg.type)
					login(db, connfd);
				else if (R == climsg.type)
					regist(db, connfd);
				else if (Q == climsg.type)
					quary(db, connfd);
				else if (H == climsg.type)
					show_history(db, connfd);
				else if (C == climsg.type)
					clear_history(db, connfd);
				}
		} else {
			close(connfd);
		}
	}

	return 0;
}

int regist(sqlite3 *db, int connfd)
{
	char regist[SQL_LEN];
	char *errmsg;

	sprintf(regist, "insert into userstat values(‘%s‘, ‘%s‘);", climsg.name, climsg.data);
	if (SQLITE_OK != sqlite3_exec(db, regist, NULL, NULL, &errmsg)) {
		sermsg.type = -1;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	} else {
		sermsg.type = 0;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	}
	sprintf(regist, "insert into userstat values(‘%s‘, %d);", climsg.name, OFF_LINE);
	if (SQLITE_OK != sqlite3_exec(db, regist, NULL, NULL, &errmsg)) {
		printf("error: %s \n", errmsg);	
	}

	return 0;
}

int login(sqlite3 *db, int connfd)
{
	char login[SQL_LEN];
	int nrow, ncol;
	char *errmsg;
	char **resultp;

	sprintf(login, "select * from userstat where username=‘%s‘ and status=%d;", climsg.name, OFF_LINE);
	if (0 != sqlite3_get_table(db, login, &resultp, &nrow, &ncol, &errmsg))
		printf("error: %s \n", errmsg);
	if (0 == nrow) {
		sermsg.type = -2;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
		return -1;
	}
	sprintf(login, "select * from user where username=‘%s‘ and password=‘%s‘;", climsg.name, climsg.data);
	if (0 != sqlite3_get_table(db, login, &resultp, &nrow, &ncol, &errmsg))
		printf("error: %s \n", errmsg);
	if (1 != nrow) {
		sermsg.type = -1;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	} else {
		sermsg.type = 0;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	}
	sprintf(login, "update userstat set status=%d where username=‘%s‘;", ON_LINE, climsg.name);
	if (SQLITE_OK != sqlite3_exec(db, login, NULL, NULL, &errmsg))
		printf("error: %s \n", errmsg);	
	
	return 0;
}

int logout(sqlite3 *db, int connfd)
{
	char logout[SQL_LEN];
	char *errmsg;

	sprintf(logout, "update userstat set status=%d where username=‘%s‘;", OFF_LINE, climsg.name);
	if (SQLITE_OK != sqlite3_exec(db, logout, NULL, NULL, &errmsg))
		printf("error: %s \n", errmsg);	

	return 0;
}
int quary(sqlite3 *db, int connfd)
{
	char quary[SQL_LEN];
	int nrow, ncol;
	char *errmsg;
	char **resultp;

	sprintf(quary, "select explain from dictionary where word=‘%s‘;", climsg.data);
	if (0 != sqlite3_get_table(db, quary, &resultp, &nrow, &ncol, &errmsg))
		printf("error: %s \n", errmsg);
	if (0 == nrow) {
		sermsg.type = -1;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	} else {
		sermsg.type = 0;
		sprintf(sermsg.text, "%s", resultp[1]);
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	}
	sprintf(quary, "insert into history values(‘%s‘, ‘%s‘);", climsg.name, climsg.data);
	if (SQLITE_OK != sqlite3_exec(db, quary, NULL, NULL, &errmsg))
		printf("error to write history\n");
	
	return 0;
}

int show_history(sqlite3 *db, int connfd)
{
	char show[SQL_LEN];
	int i;
	int nrow, ncol;
	char *errmsg;
	char **resultp;

	sprintf(show, "select word from history where username=‘%s‘;", climsg.name);
	if (0 != sqlite3_get_table(db, show, &resultp, &nrow, &ncol, &errmsg))
		printf("error: %s \n", errmsg);
	if (0 == nrow) {
		sermsg.type = -1;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	} else {
		for (i = 1; i <= nrow; i++) {
			sermsg.type = 0;
			sprintf(sermsg.text, "%s", resultp[i]);
			printf("%s \n", sermsg.text);
			if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
				error_exit("send");
		}
		sprintf(sermsg.text, "NULL");
		printf("%s \n", sermsg.text);
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	}

	return 0;
}

int clear_history(sqlite3 *db, int connfd)
{
	char clear[SQL_LEN];
	char *errmsg;

	sprintf(clear, "delete from history where username=‘%s‘;", climsg.name);
	if (SQLITE_OK != sqlite3_exec(db, clear, NULL, NULL, &errmsg)) {
		printf("error to clear history\n");
		sermsg.type = -1;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	} else {
		sermsg.type = 0;
		if (-1 == send(connfd, &sermsg, sizeof(sermsg), 0))
			error_exit("send");
	}
	
	return 0;
}

client.h:

/*************************************************************************
	> File Name: client.h
	> Author: Baniel Gao
	> Mail: createchance@163.com 
	> Created Time: Wed 15 Jan 2014 09:39:40 AM CST
 ************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <termios.h>
#include <unistd.h>
#include <string.h>
#include <errno.h>
#include <error.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <netinet/in.h>
#include <netinet/ip.h>

#define BUFF_SIZE		32
#define SQL_LEN			512

#define MAX_USERNAME	256
#define MAX_WORD		128
#define RET_SIZE		1024

#define	R				1
#define L				2
#define Q				3
#define H				4
#define S				5
/*clear history*/
#define C				6

#define SERVER_IP		"192.168.140.10"
#define SERVER_PORT		50000

#define ECHOFLAGS (ECHO | ECHOE | ECHOK | ECHONL)

#define error_exit(_errmsg_)	error(EXIT_FAILURE, errno, _errmsg_)

typedef struct _client_msg_ {
	char type;
	char name[MAX_USERNAME];
	char data[MAX_WORD];
} climsg_st;

typedef struct _server_msg_ {
	char type;
	char text[RET_SIZE];
} sermsg_st;

void menu(void);
void login_reg(void);
int insert(int sockfd);
int delete(int sockfd);
int show_history(int sockfd);
int clear_history(int sockfd);
int login(int sockfd);
int logout(int sockfd);
int regist(int sockfd);
int set_disp_mode(int fd,int option);
int quary(int sockfd);

client.c:
/*************************************************************************
	> File Name: client.c
	> Author: Baniel Gao
	> Mail: createchance@163.com 
	> Blog: blog.csdn.net/createchance 
	> Created Time: Tue 14 Jan 2014 01:33:49 PM CST
 ************************************************************************/
#include "client.h"

climsg_st climsg;
sermsg_st sermsg;

int main(void)
{
	char choice[BUFF_SIZE];
	int fail_times = 0;
	int ret;
	int sockfd;
	struct sockaddr_in server_addr;
	socklen_t addrlen;

	if (-1 == (sockfd = socket(AF_INET, SOCK_STREAM, 0)))
		error_exit("socket");
	server_addr.sin_family = AF_INET;
	server_addr.sin_port = htons(SERVER_PORT);
	server_addr.sin_addr.s_addr = inet_addr(SERVER_IP);
	addrlen = sizeof(server_addr);
	if (-1 == connect(sockfd, (const struct	sockaddr *)&server_addr, addrlen))
		error_exit("connect");
	while (1) {
		login_reg();
		while (1) {
			printf("Please input: ");
			if (strcmp("\n", fgets(choice, BUFF_SIZE, stdin)) && (2 == strlen(choice)))
				break;
		}
		switch (choice[0]) {
			case ‘1‘:
				if (!strcmp(choice, "1\n"))
					while (-1 == (ret = login(sockfd)) || -2 == ret) {
						if (-1 == ret) {
							printf("\n\e[31musername or password wrong!\e[0m \n");
							fail_times++;
						}
						else
							printf("\n\e[31m%s has already login!\e[0m \n", climsg.name);
						if (3 <= fail_times) {
							printf("\n\e[32mHave no account? Please join us! Do you want to register?(Yes/No):\e[0m ");
							if (!strcmp("Yes\n", fgets(choice, BUFF_SIZE, stdin)) || !strcmp("yes\n", choice)) {
								while (-1 == regist(sockfd)) 
									printf("\n\e[31musername has been registered!\e[0m \n");
								printf("\n\e[32mLet‘s login now!\e[0m\n");
							}
						}
					}
				break;
			case ‘2‘:
				while (-1 == regist(sockfd))
					printf("\n\e[31musername has been registered!\e[0m \n");
				printf("\n\e[32mLet‘s login now!\e[0m\n");
				while (-1 == (ret = login(sockfd)))
					printf("\n\e[31musername or password wrong!\e[0m \n");
				break;
			case ‘3‘:
				return 0;
			default:
				printf("\e[31mUnknown choice!\e[0m \n");
		}
		if (0 == ret)
			break;
	}
	while (1) {
		while (1) {
			menu();
			printf("Your choice: ");
			if (strcmp("\n", fgets(choice, BUFF_SIZE, stdin)), 2 == strlen(choice))
				break;
		}
		switch (choice[0]) {
			case ‘1‘:
				quary(sockfd);
				break;
			case ‘2‘:
				show_history(sockfd);
				break;
			case ‘3‘:
				clear_history(sockfd);
				break;
			case ‘4‘:
				logout(sockfd);
				return 0;
			default:
				printf("\e[31mUnknown choice!\e[0m \n");
		}
	}
	
	return 0;
}

int set_disp_mode(int fd,int option)  
{  
	int err;  
	struct termios term;  

	if(tcgetattr(fd,&term)==-1){  
		perror("Cannot get the attribution of the terminal");  
		return 1;  
	}  
	if(option)  
        term.c_lflag|=ECHOFLAGS;  
	else  
        term.c_lflag &=~ECHOFLAGS;  
	err=tcsetattr(fd,TCSAFLUSH,&term);  
	if(err==-1 && err==EINTR){  
		perror("Cannot set the attribution of the terminal");  
        return 1;  
	}

	return 0;  
}

int regist(int sockfd)
{
	char password_cfm[MAX_WORD];

	printf("\e[32mJoin us! Just one minute!\e[0m \n");
	while (1) {
		printf("username: ");
		if (strcmp("\n", fgets(climsg.name, MAX_USERNAME, stdin)))
			break;
	}
	climsg.name[strlen(climsg.name) - 1] = ‘\0‘;
	set_disp_mode(STDIN_FILENO, 0);
	while (1) {
		printf("password: ");
		if (!strcmp("\n", fgets(climsg.data, MAX_WORD, stdin)))
			continue;
		printf("\nRetype password: ");
		fgets(password_cfm, BUFF_SIZE, stdin);
		if (!strcmp(climsg.data, password_cfm))
			break;
		else
			printf("\n\e[31mpassword does not match!\e[0m \n");
	}
	climsg.data[strlen(climsg.data) - 1] = ‘\0‘;
	climsg.type = R;
	set_disp_mode(STDIN_FILENO, 1);
	if (-1 == send(sockfd, &climsg, sizeof(climsg_st), 0)) {
		printf("Network failure! \n");
		return -1;
	}
	if (-1 == recv(sockfd, &sermsg, sizeof(sermsg_st), 0))
		printf("Network failure! \n");
	if (-1 == sermsg.type)
		return -1;

	printf("\n\e[32mregister done!\e[0m \n");

	return 0;
}

int logout(int sockfd)
{
	climsg.type = S;

	if (-1 == send(sockfd, &climsg, sizeof(climsg_st), 0)) {
		printf("Network failure! \n");
		return -1;
	}

	return 0;
}

int login(int sockfd)
{
	while (1) {
		printf("username: ");
		if (strcmp("\n", fgets(climsg.name, MAX_USERNAME, stdin)))
			break;
	}
	climsg.name[strlen(climsg.name) - 1] = ‘\0‘;
	set_disp_mode(STDIN_FILENO, 0);
	while (1) {
		printf("password: ");
		if (strcmp("\n", fgets(climsg.data, MAX_WORD, stdin)))
			break;
	}
	climsg.data[strlen(climsg.data) - 1] = ‘\0‘;
	climsg.type = L;
	set_disp_mode(STDIN_FILENO, 1);
	if (-1 == send(sockfd, &climsg, sizeof(climsg_st), 0)) {
		printf("Network failure! \n");
		return -1;
	}
	if (-1 == recv(sockfd, &sermsg, sizeof(sermsg_st), 0))
		printf("Network failure! \n");
	if (-1 == sermsg.type)
		return -1;
	else if (-2 == sermsg.type)
		return -2;

	printf("\n\e[32mWelcome %s!\e[0m \n", climsg.name);
	return 0;
}

void login_reg(void)
{
	printf("*********************************\n");
	printf("*  1.login  2.register  3.quit  *\n");
	printf("*********************************\n");
}

void menu(void)
{
	printf("\e[35m****************************************************\n");
	printf("*  1.quary  2.show history  3.clear history  4.quit*\n");
	printf("****************************************************\e[0m\n");
}

int show_history(int sockfd)
{
	climsg.type = H;
	if (-1 == send(sockfd, &climsg, sizeof(climsg), 0)) {
		printf("\e[31mNetwork failure!\e[0m \n");
		return -1;
	}
	printf("\e[33m-----------------\n");
	while (1) {
		if (-1 == recv(sockfd, &sermsg, sizeof(sermsg), 0)) {
			printf("\e[31mNetwork failure!\e[0m \n");
			return -1;
		}
		if (-1 == sermsg.type) {
			printf("\e[31mNo history found!\e[0m \n");
			break;
		}
		if (!strcmp("NULL", sermsg.text))
			break;
		printf("|\t%s\t|\n", sermsg.text);
	}
	printf("\e[33m-----------------\e[0m\n\n");

	return 0;
}

int clear_history(int sockfd)
{
	climsg.type = C;

	if (-1 == send(sockfd, &climsg, sizeof(climsg), 0)) {
		printf("\e[31mNetwork failure!\e[0m \n");
		return -1;
	}
	if (-1 == recv(sockfd, &sermsg, sizeof(sermsg), 0)) {
		printf("\e[31mNetwork failure!\e[0m \n");
		return -1;
	}
	if (0 == sermsg.type)
		printf("\e[32mClear done!\e[0m \n");
	else
		printf("\e[31mClear failed!\e[0m \n");

	return 0;
}

int quary(int sockfd)
{
	int i;

	while (1) {
		printf("Input the word: ");
		if (!strcmp("\n", fgets(climsg.data, MAX_WORD, stdin)))
			continue;
		if (!strcmp("#\n", climsg.data))
			return -1;
		climsg.data[strlen(climsg.data) - 1] = ‘\0‘;
		for (i = 0; i < strlen(climsg.data); i++) {
			if (climsg.data[i] >= ‘A‘ && climsg.data[i] <= ‘Z‘)
				climsg.data[i] = climsg.data[i] + ‘ ‘;
			else if (climsg.data[i] >= ‘a‘ && climsg.data[i] <= ‘z‘)
				continue;
			else
				break;
		}
		if (i != strlen(climsg.data)) {
			printf("\e[31mInput invalid!\e[0m \n");
			continue;
		} else
			break;
	}
	climsg.type = Q;
	if (-1 == send(sockfd, &climsg, sizeof(climsg), 0)) {
		printf("\e[31mNetwork failure!\e[0m \n");
		return -1;
	}
	if (-1 == recv(sockfd, &sermsg, sizeof(sermsg), 0))
		printf("\e[31mNetwork failure!\e[0m \n");
	if (0 == sermsg.type) {
		printf("\e[33m----------------------------------------------------------------\n");
		printf("|%s\t|  %s\n", climsg.data, sermsg.text);
		printf("----------------------------------------------------------------\e[0m\n");

	}
	else
		printf("\e[31mSorry, %s not found!\e[0m \n", climsg.data);

	return 0;
}

以下是客户端的运行效果:

登陆:

SQLite实现在线电子词典


查询:

SQLite实现在线电子词典

查询历史记录:

SQLite实现在线电子词典

删除历史记录:

SQLite实现在线电子词典

SQLite实现在线电子词典

上一篇:OSX下完全删除MySQL和怎样使MySQL支持中文


下一篇:js函数