用TCP及fork实现数据库sqlite3(升级)

目录

sever.c

client.c

---

实现在客户端，显示打印信息及操作内容，但是核心都在服务器上

修改内容：将客户端进行了升级，加了多进程的应用，一个是读一个是写

小问题：直接用父进程杀死的子进程实现回收

直接上程序

sever.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <sys/types.h>          /* See NOTES */
#include <sys/socket.h>
#include <stdint.h>
#include <arpa/inet.h>
#include <unistd.h>
#include <sys/wait.h>
#include <signal.h>
#include <unistd.h>
#include <strings.h>


#define PORT 5001
#define IP "192.168.**.***" //自己的ip
#define BACKLOG 5
#define  N  128

#define table "1_data.db"
int send_data(int newfd,char *buf)
{
	int ret;
	do{
		ret = write(newfd, buf, 1024);
	}while(ret < 0);
	if(ret < 0)
	{
		perror("write");
	}
	return 1;
}
char *read_data(int newfd)
{
	static char recv[1024]={0};
	int ret;
	do{
		ret = read(newfd , recv, 1024);
	}while(ret < 0);
	if(ret < 0)
	{
		perror("read");
		return NULL;
	}
	if(!ret)
	{
		return NULL;
	}else{
		return recv;
	}

}
int do_id(sqlite3 *db,int id,int newfd)
{
	char sql[1024]={0};
	char *pzErrmsg;
	char **pazResult;
	int pnRow,pnColumn;
	char csql[1024]={0};

	sprintf(sql,"select * from stu;");

	if( sqlite3_get_table(db,sql,&pazResult,&pnRow,&pnColumn,&pzErrmsg) != 0)
	{
		sprintf(csql,"get_table:%s",pzErrmsg);
		send_data(newfd,csql);
	}else
	{
		int flag=0;
		int i,j,ret;
		int index=pnColumn;
		for(i=0;i<pnRow;i++)
		{
			ret = atoi(pazResult[index]);
			for(j=0;j<pnColumn;j++)
			{
				if((ret == id) & (0==j))
				{
					flag=1;
				}
				index++;
			}
		}
		if(flag == 1)
		{
			return 1;
		}else
		{
			sprintf(csql,"id not exit...\n");
			send_data(newfd,csql);
			return -1;
		}
	}
	return 0;
}
void insert_list(sqlite3 *db,int newfd)
{
	char sql[1024]={0};
	char csql[1024]={0};
	int id,age,ret;
	float score;
	char name[4]={0};
	bzero(name,sizeof(name));
	char *errmsg;
	sprintf(csql,"input id:\n");
	send_data(newfd,csql);
	id = atoi(read_data(newfd));
	sprintf(csql,"you input:%d\n",id);
	send_data(newfd,csql);
	sprintf(csql,"input name:\n");
	send_data(newfd,csql);
	strcpy(name,read_data(newfd));
	int len = strlen(name);
	if(len > 0)
	{
		name[len-1] = '\0';
	}
	sprintf(csql,"you input:%s\n",name);
	send_data(newfd,csql);
	sprintf(csql,"input age:\n");
	send_data(newfd,csql);
	age = atoi(read_data(newfd));
	sprintf(csql,"you input:%d\n",age);
	send_data(newfd,csql);
	sprintf(csql,"input score:\n");
	send_data(newfd,csql);
	score = atoi(read_data(newfd));
	sprintf(csql,"you input:%f\n",score);
	send_data(newfd,csql);

	sprintf(sql,"insert into stu values(%d,'%s',%d,%f);",id,name,age,score);
	ret = sqlite3_exec(db, sql,NULL,NULL,&errmsg);
	if(ret != 0)
	{
		sprintf(csql,"insert %s\n",errmsg);
		send_data(newfd,csql);
	}else{
		//printf("insert success\n");
	}
}
void delete_list(sqlite3 *db,int newfd)
{
	int id,ret;
	char sql[1024]={0};
	char csql[1024]={0};
	char *errmsg;
	sprintf(csql,"input you want delete id:\n");
	send_data(newfd,csql);
	id = atoi(read_data(newfd));
	sprintf(csql,"you input:%d\n",id);
	send_data(newfd,csql);
	ret = do_id(db,id,newfd);
	if(ret < 0)
	{
		return ;
	}
	sprintf(sql,"delete from stu where id = %d;",id);
	ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if(ret != 0)
	{
		sprintf(csql,"delete %s\n",errmsg);
		send_data(newfd,csql);
		exit(0);
	}else
	{
		//printf("delete success\n");
	}
}
void do_select_one(sqlite3 *db,int newfd)
{
	int id;
	char sql[1024]={0};
	char csql[1024]={0};
	sprintf(csql,"input you want select id:\n");
	send_data(newfd,csql);
	id = atoi(read_data(newfd));
	sprintf(csql,"you input:%d\n",id);
	send_data(newfd,csql);
	sprintf(sql,"select * from stu where id = %d",id);
	char *pzErrmsg;
	char **pazResult;
	int pnRow,pnColumn;

	if(sqlite3_get_table(db,sql,&pazResult,&pnRow,&pnColumn,&pzErrmsg) != 0)
	{
		sprintf(csql,"get_table:%s",pzErrmsg);
		send_data(newfd,csql);
	}else
	{
		int i,j;
		int index=pnColumn;
		int num;
		int ret;
		int flag=0;
		char csql[1024]={0};
		for(i=0;i<pnRow;i++)
		{
			ret = atoi(pazResult[index]);
			for(j=0;j<pnColumn;j++)
			{
				if((ret == id) & (0==j))
				{
					flag=1;
					for(num=0;num<pnColumn;num++)
					{
						sprintf(csql,"%-8s    ", pazResult[index]);
		send_data(newfd,csql);
						index++;
					}
				}
				index++;
			}
			sprintf(csql,"\n");
		send_data(newfd,csql);
		}
		if(flag == 0){
			sprintf(csql,"not select id...\n");
		send_data(newfd,csql);
		}
	}
}
void do_select_all(sqlite3 *db,int newfd)
{
	char sql[1024]={0};
	char csql[1024]={0};
	sprintf(sql,"select * from stu;");
	char *pzErrmsg;
	char **pazResult;
	int pnRow,pnColumn;

	if( sqlite3_get_table(db,sql,&pazResult,&pnRow,&pnColumn,&pzErrmsg) != 0)
	{
		printf("get_table:%s",pzErrmsg);
	}else
	{
		int i,j;
		int index=0;
		if((pnRow == 0) & (pnColumn ==0))
		{
			sprintf(csql,"id not exit...\n");
		send_data(newfd,csql);
			return ;
		}

		for(i=0;i<=pnRow;i++)
		{
			for(j=0;j<pnColumn;j++)
			{
				sprintf(csql,"%-8s    ",pazResult[index++]);
		send_data(newfd,csql);
			}
			sprintf(csql,"\n");
		send_data(newfd,csql);
		}
	}
}
void select_list(sqlite3 *db,int newfd)
{
	int cmd;
	char csql[1024]={0};
	while(1)
	{
		sprintf(csql,"/***********************************/\n");
		send_data(newfd,csql);
		sprintf(csql,"/* 1.查看单个id  2.查看所有  3.退出*/\n");
		send_data(newfd,csql);
		sprintf(csql,"/***********************************/\n");
		send_data(newfd,csql);
		sprintf(csql,"input:");
		send_data(newfd,csql);
		cmd = atoi(read_data(newfd));
		sprintf(csql,"you input cmd:%d\n",cmd);
		send_data(newfd,csql);
		if(cmd == 3)
		{
			//printf("sqlect quiting...\n");
			break;
		}
		switch(cmd)
		{
			case 1:do_select_one(db,newfd);break;
			case 2:do_select_all(db,newfd);break;
			default:break;
		}
	}
}
void set_updata_name(sqlite3 *db,int id,int newfd)
{
	char name[128]={0};
	int ret;
	char sql[1024]={0};
	char csql[1024]={0};
	char *errmsg;
	sprintf(csql,"set name:\n");
		send_data(newfd,csql);
	strcpy(name,read_data(newfd));
	int len = strlen(name);
	if(len > 0)
	{
		name[len-1] = '\0';
	}
	sprintf(csql,"you input name:%s\n",name);
		send_data(newfd,csql);
	sprintf(sql,"update stu set name='%s' where id=%d;",name, id);
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(ret != 0)
	{
		sprintf(csql,"update name :%s\n",errmsg);
		send_data(newfd,csql);
		exit(0);
	}else
	{
		//printf("update name success\n");
	}
}
void set_updata_age(sqlite3 *db,int id,int newfd)
{
	int age;
	int ret;
	char sql[1024]={0};
	char csql[1024]={0};
	char *errmsg;
	sprintf(csql,"set age:\n");
	send_data(newfd,csql);
	age = atoi(read_data(newfd));
	sprintf(csql,"you input:%d\n",age);
	send_data(newfd,csql);
	sprintf(sql,"update stu set age = %d where id=%d;",age,id);
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(ret != 0)
	{
		sprintf(csql,"update:%s\n",errmsg);
		send_data(newfd,csql);
		exit(0);
	}else
	{
		//printf("update age success\n");
	}
}
void handler(int arg)
{
	if(SIGCHLD == arg)
	{
		waitpid(-1, NULL, WNOHANG);
	}
}
void set_updata_score(sqlite3 *db,int id,int newfd)
{
	float score;
	int ret;
	char sql[1024]={0};
	char csql[1024]={0};
	char *errmsg;
	sprintf(csql,"set score:\n");
		send_data(newfd,csql);
	score = atoi(read_data(newfd));
	sprintf(csql,"you input:%f\n",score);
		send_data(newfd,csql);
	sprintf(sql,"update stu set score = %f where id=%d;",score,id);
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(ret != 0)
	{
		sprintf(csql,"update:%s\n",errmsg);
		send_data(newfd,csql);
		exit(0);
	}else
	{
		//printf("update score success\n");
	}
}
void update_list(sqlite3 *db,int newfd)
{
	int id,cmd,flag;
	char csql[1024]={0};
	while(1)
	{
		sprintf(csql,"input you want update id:\n");
		send_data(newfd,csql);
		id = atoi(read_data(newfd));
		sprintf(csql,"you input:%d\n",id);
		send_data(newfd,csql);
		flag = do_id(db,id,newfd);
		if(flag > 0)
		{
			
			sprintf(csql,"/*************************************************/\n");
			send_data(newfd,csql);
			sprintf(csql,"/* set: 1.name     2.age     3.score     4.quit  */\n");
			send_data(newfd,csql);
			sprintf(csql,"/*************************************************/\n");
			send_data(newfd,csql);
			
			cmd = atoi(read_data(newfd));
			sprintf(csql,"you input:%d\n",cmd);
			send_data(newfd,csql);
			if(cmd == 4)
			{
				//printf("set quiting...\n");
				break;
			}
			switch(cmd)
			{
				case 1:set_updata_name(db,id,newfd);break;
				case 2:set_updata_age(db,id,newfd);break;
				case 3:set_updata_score(db,id,newfd);break;
			}
		}
		break;
	}
	return ;
}
int printf_init(sqlite3 *db,int newfd)
{
	char csql[1024]={0};
	while(1)
	{
		sprintf(csql,"/****************************************************/\n");
		send_data(newfd,csql);
		sprintf(csql,"/* 1.插入数据 2.删除数据 3.更新数据 4.查看表 5.退出 */\n");
		send_data(newfd,csql);
		sprintf(csql,"/****************************************************/\n");
		send_data(newfd,csql);
		sprintf(csql,"input select data:\n");
		send_data(newfd,csql);
		int cmd;
		//scanf("%d",&cmd);
		//getchar();
		cmd = atoi(read_data(newfd));
		sprintf(csql,"you select data=%d\n",cmd);
		send_data(newfd,csql);
		switch(cmd)
		{
			case 1:insert_list(db,newfd);break;
			case 2:delete_list(db,newfd);break;
			case 3:update_list(db,newfd);break;
			case 4:select_list(db,newfd);break;
			default:return 1;
		}
	}
}
int main(int argc,char *argv[])
{
	sqlite3 *db;
	int ret = -1;
	int flag=-1;
	ret = access(table, F_OK);
	if(ret == 0)
	{
		unlink(table);
	}
	ret = sqlite3_open(table, &db);
	if(ret != 0)
	{
		printf("open %s\n",sqlite3_errmsg(db));
		exit(0);
	}else{
		//printf("open %s success\n",table);
	}
	char *errmsg;
	ret = sqlite3_exec(db,"create table stu (id integer, name text, age integer, score integer);",NULL,NULL,&errmsg);
	if(ret != 0)
	{
		printf("create:%s\n",errmsg);
		exit(0);
	}else
	{
		//printf("create table stu success\n");
	}

	int fd;
	fd = socket(AF_INET, SOCK_STREAM, 0);
	if(fd < 0)
	{
		perror("socket");
		return -1;
	}
	int optval = 1;
	setsockopt(fd, SOL_SOCKET, SO_REUSEADDR, &optval, sizeof(int));
	signal(SIGCHLD, handler);
	struct sockaddr_in addr;
	bzero(&addr,sizeof(addr));
	addr.sin_family = AF_INET;
	addr.sin_port = htons(PORT);
	socklen_t addrlen = sizeof(addr);
	ret = inet_pton(AF_INET, IP, (void *)&addr.sin_addr);
	if(ret != 1)
	{
		perror("inet_pton");
		return -1;
	}
	ret = bind(fd, (struct sockaddr *)&addr,addrlen);
	if(ret < 0)
	{
		perror("bind");
		close(fd);
		return -1;
	}
	ret = listen(fd, BACKLOG);
	if(ret < 0)
	{
		perror("listen");
		close(fd);
		return -1;
	}

	int newfd;
	struct sockaddr_in cin_addr;
	socklen_t cin_addrlen = sizeof(cin_addr);
	char csql[1024]={0};
	char cin_buf[16]={0};
	pid_t pid=-1;
	while(1)
	{
		newfd = accept(fd, (struct sockaddr *)&cin_addr, &cin_addrlen);
		if(newfd < 0)
		{
			perror("accept");
			close(fd);
			return -1;
		}
		pid = fork();
		if(pid < 0)
		{
			perror("fork");
			break;
		}else if(0 == pid)
		{
			printf("<%d:%d:%s> client connect success...\n",newfd,ntohs(cin_addr.sin_port), inet_ntop(AF_INET, (void *)&cin_addr.sin_addr,cin_buf,16));
			while(1)
			{
				flag = printf_init(db,newfd);
				if(1 == flag)
					break;
			}
			sprintf(csql,"quit");
			send_data(newfd,csql);
			printf("<%d:%d:%s> client exit success...\n",newfd,ntohs(cin_addr.sin_port), inet_ntop(AF_INET, (void *)&cin_addr.sin_addr,cin_buf,16));
			close(newfd);
			return -1;
		}else
		{
		}
	}
	sqlite3_close(db);
	close(newfd);
	close(fd);
	return 0;
}

client.c

#include <stdio.h>
#include <sys/types.h>          /* See NOTES */
#include <sys/socket.h>
#include <string.h>
#include <stdint.h>
#include <arpa/inet.h>
#include <unistd.h>
#include <stdlib.h>
#include <signal.h>
#include <sys/wait.h>

#define port atoi(argv[1])
#define ip argv[2]

void handler(int arg)
{
	if(SIGCHLD == arg)
	{
		waitpid(-1, NULL, WNOHANG);
	}
}
int main(int argc,char *argv[])
{
	if(argc != 3)
	{
		printf("%s <port> <ip>\n", __FILE__);
		return -1;
	}
	if(port < 5000)
	{
		printf("port > 5000\n");
		return -1;
	}
	//   printf("port = %s, ip = %s\n",port,ip);

	int fd;
	int ret;
	fd = socket(AF_INET, SOCK_STREAM, 0);
	if(fd < 0)
	{
		perror("socket");
		return -1;
	}
	struct sockaddr_in addr;
	bzero(&addr, sizeof(addr));
	addr.sin_family = AF_INET;
	addr.sin_port = htons(port);
	addr.sin_addr.s_addr = inet_addr(ip);
	signal(SIGCHLD, handler);
	ret = connect(fd, (struct sockaddr *)&addr, sizeof(addr));
	if(ret < 0)
	{
		perror("connect");
		return -1;
	}
	printf("client start >>>>\n");
	pid_t pid;
	pid = fork();
	if(pid < 0)
	{
		perror("fork");
	}else if(0 == pid)
	{
			printf("getpid = %d\n",getpid());
	char buf[1024];
	while(1)
	{
		do{
		bzero(buf, sizeof(buf));
		fgets(buf, 1024, stdin);
		ret = write(fd, buf, sizeof(buf));
		}while(ret < 0);
		if(ret < 0)
		{
			perror("write");
			break;
		}
		if(!ret)
			break;
	}
	return -1;
	}else{
		char recvbuf[1024];
		int ret = 0;
		while(1)
		{
		bzero(recvbuf, sizeof(recvbuf));
		do
		{
			ret = read(fd, recvbuf, sizeof(recvbuf));
		}while(ret < 0);
		if(ret < 0)
		{
			perror("read");
			break;
		}
		if((strncmp(recvbuf, "quit", 4)) == 0)
		{
			//printf("getppid = %d\n",getppid());
			printf("getpid = %d\n",getpid()+1);
			kill(getpid()+1,SIGKILL);
			break;
		}
		printf("%s",recvbuf);
		}
	}
	close(fd);
	return 0;
}