数据库访问--web服务器

//http.h
#pragma once
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sys/types.h>
#include<sys/socket.h>
#include<sys/sendfile.h>
#include<sys/stat.h>
#include<sys/epoll.h>
#include<unistd.h>
#include<fcntl.h>
#include<netinet/in.h>
#include<arpa/inet.h>
#include<assert.h>
#include<pthread.h>
#include<errno.h>
#define _DEF_PAGE_ "index.html"
#define _SIZE_ 1024
typedef struct bf
{
	char _buf[_SIZE_];
	int _fd;
	int _cgi;
	char _method[_SIZE_/2];
	char _path[_SIZE_];
	union
	{
		char* _query_string;
		int _st_size;
	};
	int _err;

}bf_t,*bf_p;
void printLog(const char* const str,const char* const fun,int line);
void usage(const char*  const  proc);
int startup(char* ip,int port);
void response_err(bf_p bf,int eno);
void echo_error(bf_p bf,int eno);
int get_line(int sock,char* buf,int size);
void clear_head(int sock);
void execute_cgi(bf_p bf);
void echo_html(bf_p bf);
void accept_request(void* ptr);
//http.c
#include"http.h"
void printLog(const char* const str,const char* const fun,int line)
{
	printf("%s:%s:%d\n",str,fun,line);
}
void usage(const char*  const  proc)
{
	assert(proc);
	printLog(proc,__FUNCTION__,__LINE__);
}
int startup(char* ip,int port)
{
	assert(ip);
	int sock=socket(AF_INET,SOCK_STREAM,0);
	if(sock<0)
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		exit(1);
	}
	int opt=1;
	setsockopt(sock,SOL_SOCKET,SO_REUSEADDR,&opt,sizeof(opt));
	struct sockaddr_in local;
	local.sin_family=AF_INET;
	local.sin_port=htons(port);
	if(strncmp(ip,"any",3)==0)
	{
		local.sin_addr.s_addr= INADDR_ANY;
	}
	else
	{
		local.sin_addr.s_addr=inet_addr(ip);
	}
	if(bind(sock,(struct sockaddr*)&local,sizeof(local))<0)
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		exit(1);
	}
	if(listen(sock,5)<0)
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		exit(1);
	}
	return sock;
}
void response_err(bf_p bf,int eno)
{
	char state_line[_SIZE_];
	char err_des[30];//error description
	memset(state_line,'\0',sizeof(state_line));
	memset(err_des,'\0',sizeof(err_des));
	switch(eno)
	{
		case 301:
			strcpy(err_des,"Removed");
			break;
		case 404:
			strcpy(err_des,"Not Found");
			break;
		case 403:
			strcpy(err_des,"Forbid");
			break;
		case 500:
			strcpy(err_des,"Inner Error");
			break;
		case 501:
			strcpy(err_des,"Not execute");
			break;
		default:
			break;
	}
	sprintf(state_line,"HTTP/1.0 %d %s\r\n\r\n",eno,err_des);
	//printf("state_line:%s\n",state_line);
	strcpy(bf->_buf,state_line);
}
void echo_error(bf_p bf,int eno)
{
	switch(eno)
	{
		case 301:
			response_err(bf,eno);
			break;
		case 404:
			response_err(bf,eno);
			break;
		case 403:
			response_err(bf,eno);
			break;
		case 500:
			response_err(bf,eno);
			break;
		case 501:
			response_err(bf,eno);
			break;
		default:
			break;
	}
	return;
}
int get_line(int sock,char* buf,int size)
{
	assert(buf);
	int i=0;
	ssize_t _s=-1;
	char ch='\0';
//	printf("getLine");
	while(i<size-1&&ch!='\n')
	{
		_s=recv(sock,&ch,1,0);
		if(_s)
		{
			if(ch=='\r')
			{
				if((_s=recv(sock,&ch,1,MSG_PEEK)))
				{
					if(_s>0&&ch=='\n')
						recv(sock,&ch,1,0);
				}
			}
			buf[i++]=ch;
		}
		else
		{
			buf[i++]='\n';
			break;
		}
	}
//	printf("endddddddddddd");
	buf[i]='\0';
	return i;
}
void clear_head(int sock)
{
	char buf[_SIZE_];
	buf[0]='\0';
	ssize_t _s=1;
	while(_s>0&&strcmp(buf,"\n")!=0)
	{
		_s=get_line(sock,buf,sizeof(buf));
	}
}

void execute_cgi(bf_p bf)
{
	int content_len=0;//post method content-length
	ssize_t _s=-1;
	char buf[_SIZE_];
	char method_env[30];
	char query_env[_SIZE_];
	char content_len_env[30];
	memset(buf,'\0',sizeof(buf));
	memset(method_env,'\0',sizeof(method_env));
	memset(query_env,'\0',sizeof(query_env));
	memset(content_len_env,'\0',sizeof(content_len_env));
	//if method=get,query_string not null,if method=post,query_string is null
	sprintf(method_env,"REQUEST_METHOD=%s",bf->_method);
	putenv(method_env);
	//printf("method:%s,path:%s,query_string:%s\n",method,path,query_string);
	if(strcasecmp(bf->_method,"GET")==0)
	{
		sprintf(query_env,"QUERY_STRING=%s",bf->_query_string);
	    putenv(query_env);
	}
	else
	{
		while((_s=get_line(bf->_fd,buf,sizeof(buf)))>1)
		{
			if(strncasecmp(buf,"Content-Length:",15)==0)
			{
				//printf("length::::::::::%s\n",buf);
				content_len=atoi(buf+15);
			//	break;//not break!!!!!!!!!!!!!!!
			}
		}
		//printf("Content-Length:%d\n",content_len);
		sprintf(content_len_env,"CONTENT_LENGTH=%d",content_len);
		putenv(content_len_env);
	}
	//printf("ready env\n");
	//ready
	pid_t id;
	int in_fds[2];
	int out_fds[2];
	if(pipe(in_fds)<0)
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		exit(1);
	}
	if(pipe(out_fds)<0)	
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		exit(1);
	}
//	printf("exxxxxxxxxxxxxxx");
	if((id=fork())<0)
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		exit(1);
	}
	else if(id==0)//child
	{
		close(in_fds[1]);
		close(out_fds[0]);
		dup2(in_fds[0],0);
		dup2(out_fds[1],1);
		execl(bf->_path,bf->_path,NULL);
			
		close(in_fds[0]);
		close(out_fds[1]);
	}
	else//father
	{
		close(in_fds[0]);
		close(out_fds[1]);
		char vals[1024];
		memset(vals,'\0',sizeof(vals));
		ssize_t _s=-1;
		int index=0;
		char ch='0';
		if(strcasecmp(bf->_method,"POST")==0)
		{
			while(index<content_len&&read(bf->_fd,&ch,1)>0)
			{	
				vals[index++]=ch;
			}
		}
	//	printf("%s\n",vals);
		write(in_fds[1],vals,strlen(vals));
	//	printf("ffffffffffff");
		memset(bf->_buf,'\0',sizeof(bf->_buf));
		char* status_line="HTTP/1.0 200 ok\r\n\r\n";
		sprintf(bf->_buf,status_line,strlen(status_line));
//		printf("response\n");
	    int i=strlen(bf->_buf);
		while(read(out_fds[0],&ch,1)>0)
		{
			bf->_buf[i++]=ch;
		}
		bf->_buf[i]='\0';
		close(in_fds[1]);
		close(out_fds[0]);
		waitpid(id);
	}
}
void echo_html(bf_p bf)
{
	char* status_line="HTTP/1.0  200 ok\r\n\r\n";
	sprintf(bf->_buf,status_line,strlen(status_line));
	//printf("%s!!!!!!!!!!!!!!!!\n",bf->_buf);
}

void accept_request(void* ptr)
{
	bf_p bf=(bf_p)ptr;
	bf->_err=0;
	int ret=-1;
	int i=0,j=0;
	char url[_SIZE_];
	memset(bf->_method,'\0',sizeof(bf->_method));
	memset(bf->_buf,'\0',sizeof(bf->_buf));
	memset(bf->_path,'\0',sizeof(bf->_path));
	memset(url,'\0',sizeof(url));

	if(get_line(bf->_fd,bf->_buf,sizeof(bf->_buf))==0)
	{
		printLog("errno",__FUNCTION__,__LINE__);
		return;
	}
	i=j=0;
	while('\0'!=bf->_buf[i]&&!isspace(bf->_buf[i])&&i<strlen(bf->_buf)&&j<sizeof(bf->_method)-1)
	{
		bf->_method[j]=bf->_buf[i];//get method
		++i;
		++j;
	}
	bf->_method[j]='\0';
	//printf("method:%s\n",bf->_method);	
	j=0;
	while('\0'!=bf->_buf[i]&&isspace(bf->_buf[i]))
	{
		++i;
	}
	while('\0'!=bf->_buf[i]&&!isspace(bf->_buf[i])&&i<strlen(bf->_buf)&&j<sizeof(url)-1)
	{
		url[j]=bf->_buf[i];
		++j;
		++i;
	}
	url[j]='\0';
	//printf("url:%s\n",url);
	bf->_cgi=0;
	if(strcasecmp(bf->_method,"POST")!=0&&strcasecmp(bf->_method,"GET")!=0)
	{
		echo_error(bf,500);
		bf->_err=1;
		//return;
	}
	bf->_query_string=NULL;
	if(strcasecmp(bf->_method,"POST")==0)
	{
		bf->_cgi=1;
		bf->_query_string=NULL;
	}
	if(strcasecmp(bf->_method,"GET")==0)
	{
		bf->_query_string=url;
		while(*bf->_query_string!='\0'&&*bf->_query_string!='?')
		{
			++bf->_query_string;
		}
		    
		//printf("query_string::::::::::::::::\n");		
		if(*bf->_query_string=='?')
		{
		   *bf->_query_string='\0';
		   bf->_cgi=1;
		   ++bf->_query_string;
		}
	}
	
	sprintf(bf->_path,"htdocs%s",url);
	struct stat st;
	if(stat(bf->_path,&st)<0)//not exist
	{
		echo_error(bf,404);
		bf->_err=1;
		//return;
	
	}
	else if(S_IFDIR&st.st_mode)//dir
	{
		if(strcmp(bf->_path,"htdocs/")!=0)
			strcpy(bf->_path,"htdocs/");
		strcat(bf->_path,_DEF_PAGE_);
	}
	else if((st.st_mode&S_IXUSR)||(st.st_mode&S_IXGRP)||(st.st_mode&S_IXOTH))
	{
		bf->_cgi=1;
	}
//	printf("%d:%s\n",bf->_cgi,bf->_path);
	if(bf->_err==0)
	{
		if(bf->_cgi)
		{
			execute_cgi(bf);
		}
		else
		{
			clear_head(bf->_fd);
			bf->_st_size=st.st_size;
			echo_html(bf);
		}
	}
}
//main.c
#include"http.h"
int main(int argc,char* argv[])
{
	if(argc!=3)
	{
		usage(argv[0]);
		return 1;
	}
	char* ip=argv[1];
	int port=atoi(argv[2]);
	int listen_sock=startup(ip,port);
	struct sockaddr_in client;
	socklen_t len=sizeof(client);
	int epo_fd=epoll_create(256);
	if(epo_fd<0)//success:not 0 fd/error:-1
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		return -1;
	}
	bf_p fd_bf=(bf_p)malloc(sizeof(bf_t));
	memset(fd_bf->_buf,'\0',sizeof(fd_bf->_buf));
	fd_bf->_fd=listen_sock;
	struct epoll_event ev;
	ev.events=EPOLLIN;
	ev.data.fd=listen_sock;
	if(epoll_ctl(epo_fd,EPOLL_CTL_ADD,listen_sock,&ev)<0)//success:0 fail:-1
	{
		printLog(strerror(errno),__FUNCTION__,__LINE__);
		return -1;
	}
	struct epoll_event evfds[_SIZE_];//_SIZE_ 1024
	int _timeout=5000;
	int ret=-1;
	
	int i=0;
	while(1)
	{
		switch((ret=epoll_wait(epo_fd,evfds,_SIZE_,_timeout)))
		{
			case -1://error
				printLog(strerror(errno),__FUNCTION__,__LINE__);
				break;
			case 0://time out
				printf("time out...\n");
				break;
			default://normal
				{
					for(i=0;i<ret;++i)
					{
						if(evfds[i].data.fd==listen_sock&&evfds[i].events&EPOLLIN)
						{
							int new_sock=accept(listen_sock,(struct sockaddr*)&client,&len);
							if(new_sock<0)
							{
								printLog(strerror(errno),__FUNCTION__,__LINE__);
								continue;
							}
							bf_p _bf=(bf_p)malloc(sizeof( bf_t));
							memset(_bf->_buf,'\0',sizeof(_bf->_buf));
							_bf->_fd=new_sock;

							ev.events=EPOLLIN;
							ev.data.ptr=_bf;
							epoll_ctl(epo_fd,EPOLL_CTL_ADD,new_sock,&ev);
						}
						else if(((bf_p)(evfds[i].data.ptr))->_fd>0&&evfds[i].events&EPOLLIN)
						{
							accept_request(evfds[i].data.ptr);
							ev.events=EPOLLOUT;
							ev.data.ptr=evfds[i].data.ptr;
							epoll_ctl(epo_fd,EPOLL_CTL_MOD,((bf_p)(evfds[i].data.ptr))->_fd,&ev);
						}
						else if(((bf_p)(evfds[i].data.ptr))->_fd>0&&evfds[i].events&EPOLLOUT)
						{
							bf_p _bf=(bf_p)evfds[i].data.ptr;
							if(_bf->_err)
							{
								send(_bf->_fd,_bf->_buf,strlen(_bf->_buf),0);
							}
							else if(_bf->_cgi)//cgi=1
							{
								send(_bf->_fd,_bf->_buf,strlen(_bf->_buf),0);
							}
							else
							{
								char* path=_bf->_path;
								int fd=open(path,O_RDONLY);
								if(fd<0)
								{
									printLog(strerror(errno),__FUNCTION__,__LINE__);
									exit(1);
								}
								send(_bf->_fd,_bf->_buf,strlen(_bf->_buf),0);
								if(sendfile(_bf->_fd,fd,NULL,_bf->_st_size)<0)
								{
									printf("error");
								}
								close(fd);
							}
		
							epoll_ctl(epo_fd,EPOLL_CTL_DEL,_bf->_fd,NULL);
							close(_bf->_fd);
							free(_bf);
						}
					}

					break;
				}
		}
	}
	return 0;
}
//sql_api.h
#include<iostream>
#include<string.h>
#include<stdlib.h>
#include<errno.h>
#include<vector>
#include<mysql.h>
using namespace std;
class sql_api
{
public:
	sql_api();
	~sql_api();
	bool connect_mysql();
	bool close_mysql();
	bool insert_mysql(string name,string school,string hobby);
	bool delete_mysql(string id,string name);
	bool select_mysql();
	bool select_name_school(string name,string school);
	bool updata_mysql(string id,string name,string school,string hobby);
private:
	bool _select_mysql();
	bool _op_sql(string _sql);
private:
	MYSQL* _conn_fd;
	string _host;
	string _user;
	string _passwd;
	string _db;
	short _port;
	
	MYSQL_RES* _res;	
};
void anly_query(string& query,vector<string>& ret);
//sql_api.cpp
#include"sql_api.h"
sql_api::sql_api()
:_conn_fd(NULL),
_host("127.0.0.1"),
_user("root"),
_passwd(""),
_db("9_class"),
_port(3306),
_res(NULL)
{}
sql_api::~sql_api()
{
	if(_res)
	{
		free(_res);
	}
	close_mysql();
}
bool  sql_api::connect_mysql()
{
	_conn_fd=mysql_init(NULL);
	mysql_real_connect(_conn_fd,_host.c_str(),_user.c_str(),_passwd.c_str(),_db.c_str(),_port,NULL,0);
	return true;
}
bool sql_api::close_mysql()
{
	mysql_close(_conn_fd);
	return true;
}
bool sql_api::insert_mysql(string name,string school,string hobby)
{
	string _sql="INSERT INTO stu ";
	_sql+="(name,school,hobby) ";
	_sql+="VALUES ('";
	_sql+=name;
	_sql+="','";
	_sql+=school;
	_sql+="','";
	_sql+=hobby;
	_sql+="')";
//	cout<<_sql<<endl;
//	cout<<"insert\n"<<endl;
	return _op_sql(_sql);
}
bool sql_api::delete_mysql(string id,string name)
{
	bool ret=false;
	string _sql="delete from stu where id=";
	_sql+=id;
	_sql+=" and name='";
	_sql+=name;
	_sql+="'";
	//cout<<_sql<<endl;
	return _op_sql(_sql);
}
bool sql_api::select_mysql()
{
	string _sql="select* from stu";//must
	_op_sql(_sql);
	return _select_mysql();
}
bool sql_api::select_name_school(string name,string school)
{
	bool ret=false;
	string _sql="select* from stu where name='";
	_sql+=name;
	_sql+="' and school='";
	_sql+=school;
	_sql+="'";
	//cout<<_sql<<endl;
	_op_sql(_sql);
	return _select_mysql();
}

bool sql_api::updata_mysql(string id,string name,string school,string hobby)
{
	bool ret=false;
	//updata stu set something='1';
	string _sql="update stu set name='";
	_sql+=name;
	_sql+="',school='";
	_sql+=school;
	_sql+="',hobby='";
	_sql+=hobby;
	_sql+="' where id=";
	_sql+=id;
	//cout<<id<<_sql<<endl;
	return _op_sql(_sql);
}
bool sql_api::_select_mysql()
{
	int _row=0;
	int _field=0;
	//_res=(MYSQL_RES*)malloc(sizeof(MYSQL_RES));//not require
	//memset(_res,'\0',2048);//not require
	_res=mysql_store_result(_conn_fd);
	if(_res)
	{
	//	cout<<"KKKKKKKKKK"<<endl;
		_row=mysql_num_rows(_res);
		_field=mysql_num_fields(_res);
		cout<<"row:"<<_row<<"field:"<<_field<<endl;
		MYSQL_FIELD* _fd;
		for(;_fd=mysql_fetch_field(_res);)
		{
			cout<<_fd->name<<'\t';
		}
		cout<<endl;
	}
	//cout<<"select no;\n";
	//cout<<"row:"<<_row<<"field:"<<_field<<endl;
	MYSQL_ROW row_line;
	while(_row)
	{
		row_line=mysql_fetch_row(_res);
		int i=0;
		for(;i<_field;++i)
		{
			cout<<row_line[i]<<'\t';
		}
		cout<<endl;
		--_row;
	}
}
bool sql_api::_op_sql(string _sql)
{
	bool ret=false;
	if(0==mysql_query(_conn_fd,_sql.c_str()))
	{
		ret=true;
		cout<<_sql<<" success"<<endl;
	}
	return ret;
}
void anly_query(string& query,vector<string>& ret)
{
	int index=query.size()-1;
	string str=query;
	while(index>=0)
	{
		if(str[index]=='=')
		{
			ret.push_back(&str[index+1]);
		}
		if(str[index]=='&')
		{
			str[index]='\0';
		}
		--index;
	}
}
//insert.cpp
#include"sql_api.h"
int main()
{
	char method[1024];
	char buf[1024];
	memset(buf,'\0',sizeof(buf));
	string query_string;
	int content_length=0;
	if(getenv("REQUEST_METHOD"))
	{
		strcpy(method,getenv("REQUEST_METHOD"));
	}
	else
	{
		cout<<strerror(errno)<<endl;
		return -1;
	}
	if(strcasecmp(method,"GET")==0)
	{
		query_string+=getenv("QUERY_STRING");
	}
	else//POST
	{
		ssize_t _s=-1;
		if((_s=read(0,buf,sizeof(buf)))>0)
		{
			buf[_s]='\0';
			query_string+=buf;
		}
	}
	vector<string> ret;
	//string query_string="name=lxj&school=xpu&hobby=play";
	anly_query(query_string,ret);
	sql_api _tb;
	_tb.connect_mysql();
	//_tb.insert_mysql("rz","xpu","chess");
	_tb.insert_mysql(ret[2],ret[1],ret[0]);
	//_tb.updata_mysql("xian");
	//_tb.delete_mysql("haha");
	//_tb.select_mysql();
	return 0;
}
//delete.cpp
#include"sql_api.h"
int main()
{
	char method[1024];
	char buf[1024];
	memset(buf,'\0',sizeof(buf));
	string query_string;
	int content_length=0;
	if(getenv("REQUEST_METHOD"))
	{
		strcpy(method,getenv("REQUEST_METHOD"));
	}
	else
	{
		cout<<strerror(errno)<<endl;
		return -1;
	}
	if(strcasecmp(method,"GET")==0)
	{
		query_string+=getenv("QUERY_STRING");
	}
	else//POST
	{
		ssize_t _s=-1;
		if((_s=read(0,buf,sizeof(buf)))>0)
		{
			buf[_s]='\0';
			query_string+=buf;
		}
	}
	vector<string> ret;
	anly_query(query_string,ret);
	sql_api _tb;;
	_tb.connect_mysql();
	_tb.delete_mysql(ret[1],ret[0]);
	//_tb.select_mysql();
	return 0;
}
//updata.cpp
#include"sql_api.h"
int main()
{
	char method[1024];
	char buf[1024];
	memset(buf,'\0',sizeof(buf));
	string query_string;
	int content_length=0;
	if(getenv("REQUEST_METHOD"))
	{
		strcpy(method,getenv("REQUEST_METHOD"));
	}
	else
	{
		cout<<strerror(errno)<<endl;
		return -1;
	}
	if(strcasecmp(method,"GET")==0)
	{
		query_string+=getenv("QUERY_STRING");
	}
	else//POST
	{
		ssize_t _s=-1;
		if((_s=read(0,buf,sizeof(buf)))>0)
		{
			buf[_s]='\0';
			query_string+=buf;
		}
	}
	vector<string> ret;
	anly_query(query_string,ret);
	sql_api _tb;;
	_tb.connect_mysql();
	//cout<<ret[3]<<ret[2]<<ret[1]<<ret[0];
	_tb.updata_mysql(ret[3],ret[2],ret[1],ret[0]);
	//_tb.updata_mysql(2,"haha","xpu","sss");
	//_tb.select_mysql();
	return 0;
}
注：可选择把公共代码写为函数，提供接口
//start.sh
#!/bin/bash
export LD_LIBRARY_PATH='/home/xiaozhi/_git/web_stu_database/htdocs/sql_api/lib/lib';./sql_cgi_insert;./sql_cgi_select
//Makefile
ROOT_PATH=$(shell pwd)
MYSQL_INCLUDE=$(ROOT_PATH)/lib/include
MYSQL_LIB=$(ROOT_PATH)/lib/lib

BIN_INSERT=sql_cgi_insert
BIN_SELECT=sql_cgi_select
BIN_SELECT_NAME=sql_cgi_select_name
BIN_UPDATA=sql_cgi_updata
BIN_DELETE=sql_cgi_delete

INCLUDE=-I$(MYSQL_INCLUDE)
LIB=-L$(MYSQL_LIB)
LIB_LINK=-lmysqlclient

.PHONY:output
output:$(BIN_INSERT) $(BIN_SELECT) $(BIN_SELECT_NAME) $(BIN_UPDATA) $(BIN_DELETE)
$(BIN_INSERT):sql_api.cpp insert.cpp
	g++ -o $@ $^ $(INCLUDE) $(LIB) $(LIB_LINK) 
$(BIN_SELECT):sql_api.cpp select.cpp
	 g++ -o $@ $^ $(INCLUDE) $(LIB) $(LIB_LINK) 
$(BIN_SELECT_NAME):sql_api.cpp select_name.cpp
	 g++ -o $@ $^ $(INCLUDE) $(LIB) $(LIB_LINK) 
$(BIN_UPDATA):sql_api.cpp updata.cpp
	g++ -o $@ $^ $(INCLUDE) $(LIB) $(LIB_LINK) 
$(BIN_DELETE):sql_api.cpp delete.cpp
	g++ -o $@ $^ $(INCLUDE) $(LIB) $(LIB_LINK) 


.PHONY:clean
clean:
	rm -f $(BIN_INSERT) $(BIN_SELECT) $(BIN_SELECT_NAME) $(BIN_UPDATA) $(BIN_DELETE)