基于sqlite3和套接字的并发服务器

服务器端的查询程序有问题，等深入学习后在回来修改

net.h

#ifndef __NET_H__
#define __NET_H__


#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <strings.h>
#include <sys/types.h>
#include <netinet/in.h>
#include <netinet/ip.h>
#include <errno.h>
#include <stdlib.h>
#include <arpa/inet.h>
#include <pthread.h>
#include <pthread.h>
#include <signal.h>
#include <sys/wait.h>
#include <sqlite3.h>

#define SERV_PORT 5001
#define SERV_IP_ADDR "192.168.26.128"
#define BACKLOG 5
#define QUIT_STR "quit"


#endif

server.c

#include "net.h"

#define  DATABASE  "student1.db"
#define  N  128

int do_insert(sqlite3 *db, int newfd, char *buf)
{
	int id;
	char name[32] = {};
	char sex[6] = {};
	int score;
	char sql[N] = {};
	char *errmsg;

	write(newfd, "Input id:",9);
	read (newfd, buf, BUFSIZ - 1);
	//while(read < 0);
	id = atoi(buf);
	printf("%d",id);

	write(newfd, "Input name:", 11);
	read (newfd, buf, BUFSIZ - 1);
	strncpy(name,buf,sizeof(buf)); 
	printf("%s", name);

	write(newfd, "Input sex:", 10);
	read (newfd, buf, BUFSIZ - 1);
	strncpy(sex,buf,sizeof(buf));
	printf("%s",sex);

	write(newfd, "Input score:", 12);
	read (newfd, buf, BUFSIZ - 1);
	score = atoi(buf);
	printf("%d",score);

	sprintf(sql, "insert into stu values(%d, '%s', '%s', %d)", id, name, sex, score);

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		write(newfd,errmsg,sizeof(errmsg));
		write(newfd,"\n", 1);
	}
	else
	{
		write(newfd, "Insert done.\n", 13);
	}

	return 0;
}
int do_delete(sqlite3 *db, int newfd, char *buf)
{
	int id;
	char sql[N] = {};
	char *errmsg;

	write(newfd, "Input id:", 9);
	read (newfd, buf, BUFSIZ - 1);
	id = atoi(buf);

	sprintf(sql, "delete from stu where id = %d", id);

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("%s\n", errmsg);
	}
	else
	{
		write(newfd, "Delete done.\n",13);
	}

	return 0;
}
int do_update(sqlite3 *db, int newfd, char *buf)
{
	int id;
	char sql[N] = {};
	char name[32] = {};
	char *errmsg;

	write(newfd, "Input id:",9);
	read (newfd, buf, BUFSIZ - 1);
	//while(read < 0);
	id = atoi(buf);
	printf("%d",id);

	write(newfd, "Input name:", 11);
	read (newfd, buf, BUFSIZ - 1);
	strncpy(name,buf,sizeof(buf)); 
	printf("%s", name);

	sprintf(sql, "update stu set name='%s' where id=%d", name,id);

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("%s\n", errmsg);
	}
	else
	{
		write(newfd, "update done.\n",13);
	}

	return 0;
}


int callback(void *arg, int f_num, char ** f_value, char ** f_name)//FIXME
{
	char *buf;
	int i = 0;
	int newfd = (int)arg;
	for(i = 0; i < f_num; i++)
	{
	//	printf("%-8s %s", f_value[i], f_name[i]);
		sprintf(buf,"%-8s", f_value[i]);
		write(5, buf, sizeof(buf));
	}

	write(5, "++++++++++++++++++++++",strlen("++++++++++++++++++++++"));
	//putchar(10);
	write(5, "          ", 10);
	return 0;
}

int do_query(sqlite3 *db, int newfd, char *buf)//FIXME
{
	char *errmsg;
	char sql[N] = "select * from stu;";

	if(sqlite3_exec(db, sql, callback,(void*)newfd , &errmsg) != SQLITE_OK)
	{
		printf("%s", errmsg);
	}
	else
	{
		write(newfd,"select done.\n",strlen("select done.\n"));
	}
}

int do_query1(sqlite3 *db, int newfd, char *buf)
{
	char *errmsg;
	char ** resultp;
	int nrow;
	int ncolumn;
	//char buf1[32];

	if(sqlite3_get_table(db, "select * from stu", &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		write(newfd, errmsg,sizeof(errmsg));
		write(newfd, "\n",1);
		return -1;
	}
	else
	{
		write(newfd,"query done.\n",strlen("query done.\n"));
	}

	int i = 0;
	int j = 0;
	int index = ncolumn;

	for(j = 0; j < ncolumn; j++)
	{
		sprintf(buf,"%-10s ", resultp[j]);
		write(newfd,buf,1);
	}
	//putchar(10);

	for(i = 0; i < nrow; i++)
	{
		for(j = 0; j < ncolumn; j++)
		{
			sprintf(buf,"%-10s ", resultp[index++]);
			write(newfd,buf,1);
		}
		//putchar(10);
	}

return 0;
}
void sig_child_handle(int signo)
{
	if(SIGCHLD == signo) {
		waitpid(-1, NULL,  WNOHANG);
	}
}
void cli_data_handle (void *arg, sqlite3 *db)
{
	int newfd = *(int *) arg;

	printf ("Child handling process: newfd =%d\n", newfd);

	//..和newfd进行数据读写
	int ret = -1;
	char buf[BUFSIZ];
	while (1) {
		write(newfd, "********************************************\n1: insert  2:query  3:delete 4:update 5:quit\n********************************************\nPlease select:", strlen("********************************************\n1: insert  2:query  3:delete 4:update 5:quit\n********************************************\nPlease select:"));
		bzero (buf, BUFSIZ);
		do {
			ret = read (newfd, buf, BUFSIZ - 1);
		} while (ret < 0 && EINTR == errno);
		if (ret < 0) {

			perror ("read");
			exit (1);
		}
		if (!ret) {				//对方已经关闭
			break;
		}
		printf ("Receive data: %s\n", buf);
		//write(buf1);

		switch(atoi(buf))
		{
			case 1:
				do_insert(db, newfd, buf);
				break;
			case 2:
				do_query(db, newfd, buf);//FIXME
				//do_query1(db, newfd, buf);
				break;
			case 3:
				do_delete(db, newfd, buf);
				break;
			case 4:
				do_update(db, newfd, buf);
				break;
			case 5:
				printf("main exit.\n");
				sqlite3_close(db);
				exit(0);
				break;
			default :
				write(newfd, "Invalid data n.\n", strlen("Invalid data n.\n"));
		
		}
		/*if (!strncasecmp (buf, QUIT_STR, strlen (QUIT_STR))) {	//用户输入了quit字符
			printf ("Client(fd=%d) is exiting!\n", newfd);
			break;
		}*/
	}
	close (newfd);

}

int main(int argc, const char *argv[])
{

	sqlite3 *db;
	char *errmsg;//errmsg是一个二级指针，定义一个一级指针取地址。
	int n;
	
	if(sqlite3_open(DATABASE, &db) != SQLITE_OK)
	{
		printf("%s\n", sqlite3_errmsg(db));
		return -1;
	}
	else
	{
		printf("open DATABASE success.\n");
	}

	if(sqlite3_exec(db, "create table if not exists stu(id int, name char , sex char , score int);",NULL, NULL, &errmsg) != SQLITE_OK)//分号可加可不加
	{
		printf("%s\n", errmsg);
	}
	else
	{
		printf("Create or open table success.\n");
	}



	int fd = -1;
	struct sockaddr_in sin;
	
	signal(SIGCHLD, sig_child_handle);	

	/* 1. 创建socket fd */
	if ((fd = socket (AF_INET, SOCK_STREAM, 0)) < 0) {
		perror ("socket");
		exit (1);
	}

	/*优化4： 允许绑定地址快速重用 */
	int b_reuse = 1;
	setsockopt (fd, SOL_SOCKET, SO_REUSEADDR, &b_reuse, sizeof (int));


	/*2. 绑定 */
	/*2.1 填充struct sockaddr_in结构体变量 */
	bzero (&sin, sizeof (sin));
	sin.sin_family = AF_INET;
	sin.sin_port = htons (SERV_PORT);	//网络字节序的端口号

	/*优化1: 让服务器程序能绑定在任意的IP上 */
#if 1
	sin.sin_addr.s_addr = htonl (INADDR_ANY);
#else
	if (inet_pton (AF_INET, SERV_IP_ADDR, (void *) &sin.sin_addr) != 1) {
		perror ("inet_pton");
		exit (1);
	}
#endif
	/*2.2 绑定 */
	if (bind (fd, (struct sockaddr *) &sin, sizeof (sin)) < 0) {
		perror ("bind");
		exit (1);
	}

	/*3. 调用listen()把主动套接字变成被动套接字 */
	if (listen (fd, BACKLOG) < 0) {
		perror ("listen");
		exit (1);
	}
	printf ("Server starting....OK!\n");
	int newfd = -1;
	/*4. 阻塞等待客户端连接请求 */
	
        struct sockaddr_in cin;
        socklen_t addrlen = sizeof (cin);
	while(1) {
		pid_t pid = -1;
		if ((newfd = accept (fd, (struct sockaddr *) &cin, &addrlen)) < 0) {
                        perror ("accept");
                        break;
                }
		/*创建一个子进程用于处理已建立连接的客户的交互数据*/
		if((pid = fork()) < 0) {
			perror("fork");
			break;
		}
		
		if(0 == pid) {  //子进程中
			close(fd);
			char ipv4_addr[16];
                
			if (!inet_ntop (AF_INET, (void *) &cin.sin_addr, ipv4_addr, sizeof (cin))) {
                        	perror ("inet_ntop");
                        	exit (1);
               	 	}

               	 	printf ("Clinet(%s:%d) is connected!\n", ipv4_addr, ntohs(cin.sin_port));	
			cli_data_handle(&newfd, db);		
			return 0;	
		
		} else { //实际上此处 pid >0, 父进程中 
			close(newfd);
		}
		

	}		


	close (fd);
	return 0;
}

client.c

/*./client serv_ip serv_port */
#include "net.h"



void usage (char *s)
{
	printf ("\n%s serv_ip serv_port", s);
	printf ("\n\t serv_ip: server ip address");
	printf ("\n\t serv_port: server port(>5000)\n\n");
}

int main (int argc, char **argv)
{
	int fd = -1;

	int port = -1;
	struct sockaddr_in sin;

	if (argc != 3) {
		usage (argv[0]);
		exit (1);
	}
	/* 1. 创建socket fd */
	if ((fd = socket (AF_INET, SOCK_STREAM, 0)) < 0) {
		perror ("socket");
		exit (1);
	}

	port = atoi (argv[2]);
	if (port < 5000) {
		usage (argv[0]);
		exit (1);
	}
	/*2.连接服务器 */

	/*2.1 填充struct sockaddr_in结构体变量 */
	bzero (&sin, sizeof (sin));

	sin.sin_family = AF_INET;
	sin.sin_port = htons (port);	//网络字节序的端口号
#if 0
	sin.sin_addr.s_addr = inet_addr (SERV_IP_ADDR);
#else
	if (inet_pton (AF_INET, argv[1], (void *) &sin.sin_addr) != 1) {
		perror ("inet_pton");
		exit (1);
	}
#endif

	if (connect (fd, (struct sockaddr *) &sin, sizeof (sin)) < 0) {
		perror ("connect");
		exit (1);
	}

	printf ("Client staring...OK!\n");
	/*3. 读写数据 */
	char buf[BUFSIZ];
	char buf1[BUFSIZ];
	int ret = -1;
	while (1) {
		bzero (buf1, BUFSIZ);
		do {
			ret = read (fd, buf1, BUFSIZ - 1);
		} while (ret < 0 && EINTR == errno);
		if (ret < 0) {
			perror ("read");
			exit (1);
		}
		if (!ret) {				//对方已经关闭
			break;
		}
		printf("%s", buf1);
		bzero (buf, BUFSIZ);
		if (fgets (buf, BUFSIZ - 1, stdin) == NULL) {
			continue;
		}
		do {
			ret = write (fd, buf, strlen (buf));
		} while (ret < 0 && EINTR == errno);

		if (!strncasecmp (buf, QUIT_STR, strlen (QUIT_STR))) {	//用户输入了quit字符
			printf ("Client is exiting!\n");
			break;
		}
	}

	/*4.关闭套接字 */
	close (fd);
}