query.c

1．简介 本程序是用纯C语言编的一个基于菜单命令行的数据库系统。可以创建多个数据库

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "database.h"


/*///////////////////////////////////////////////////////////////////
	the functions below are used to show some infomation
	about databases, tables, columns 
		
	Author:  Edward Lee
///////////////////////////////////////////////////////////////////**/

void ShowDatabaseInfo(void)
{
	struct database *pdb;

	pdb=databasehead;
	if(pdb==NULL)
	{
		printf("No database found!\n");
		return;
	}
	while(pdb!=NULL)
	{
		printf("DATABASE	CREATOR		CREATETIME	NUMofTABLES\n");
		printf("%s\t\t%s\t\t%d-%d-%d %d:%d\t\t%d\n",pdb->cName,pdb->cCreatorName,
			pdb->tCreateTime.year,pdb->tCreateTime.month,pdb->tCreateTime.day,
			pdb->tCreateTime.hour,pdb->tCreateTime.minute,pdb->iTableNum);
		pdb=pdb->next;
	}
}

void ShowTableInfo(struct database *pdb)
{
	struct table *ptab;
	ptab=pdb->tablehead;

	if(ptab==NULL)
	{
		printf("No table found!\n");
		return;
	}
	while(ptab!=NULL)
	{
		printf("TABLE		CREATETIME	NUMofCOLUMNS\n");
		printf("%s\t\t%d-%d-%d %d:%d\t\t%d\n",ptab->cName,
			ptab->tCreateTime.year,ptab->tCreateTime.month,ptab->tCreateTime.day,
			ptab->tCreateTime.hour,ptab->tCreateTime.minute,ptab->iColNum);
		ptab=ptab->next;
	}
}

void ShowColumnInfo(struct database *pdb,struct table *ptab)
{
	struct column *pcol;
	pcol=ptab->columnhead;

	if(pcol==NULL)
	{
		printf("No column found!\n");
		return;
	}
	while(pcol!=NULL)
	{
		printf("\n");
		printf("COLUMN		VALUETYPE	NUMofLINES\n");
		printf("%s\t\t%c\t\t%d\n",pcol->cName,pcol->cType,pcol->iLineNum);
		pcol=pcol->next;
	}

}

/*---------------------------------------
	Show column infomation of a 
	certain table	 
---------------------------------------*/
void QueryColumnInfo(void)
{
	struct database *pdb;
	struct table *ptab;
	char databasename[MAX_NAME];
	char tablename[MAX_NAME];

	ShowDatabaseInfo();
	printf("Database Name:");
	scanf(" %s",databasename);
	pdb=GetDatabase(databasename);
	ShowTableInfo(pdb);
	printf("Table Name:");
	scanf(" %s",tablename);
	ptab=GetTable(pdb,tablename);	

	ShowColumnInfo(pdb,ptab);
}

/*---------------------------------------
	Show table infomation of a 
	certain database	 
---------------------------------------*/
void QueryTableInfo(void)
{
	struct database *pdb;
	char databasename[MAX_NAME];

	ShowDatabaseInfo();
	printf("Database Name:");
	scanf(" %s",databasename);
	pdb=GetDatabase(databasename);

	ShowTableInfo(pdb);
}

void QueryRecords(void)
{
	struct database *pdb;
	struct table *ptab;
	char databasename[MAX_NAME];
	char tablename[MAX_NAME];

	ShowDatabaseInfo();
	printf("Database Name:");
	scanf(" %s",databasename);
	pdb=GetDatabase(databasename);
	ShowTableInfo(pdb);
	printf("Table Name:");
	scanf(" %s",tablename);
	ptab=GetTable(pdb,tablename);

	ShowAllRecord(ptab);
}

/*///////////////////////////////////////////////////////////////////
	the functions below are interfaces :
		Find(): find a record
		Delete(): delete a record
		Modify(): modify a record
	Author: Lijun
///////////////////////////////////////////////////////////////////**/

/*---------------------------------------
	Find a record interface	 
---------------------------------------*/
void Find(void)
{
	char databasename[MAX_NAME];
	char tablename[MAX_NAME];
	char columnname[MAX_NAME];

	struct database *pdb;
	struct table *ptab;
	struct column *pcol;
	struct data data;
	int index;
	data.next=NULL;

	printf("Find a record from a table\n");	
	ShowDatabaseInfo();	
	printf("Database Name:");
	scanf(" %s",databasename);
	pdb=GetDatabase(databasename);
	if(pdb==NULL)return;
ShowTableInfo(pdb);
	printf("Table Name:");
	scanf(" %s",tablename);
	ptab=GetTable(pdb,tablename);
	if(ptab==NULL)return;
ShowColumnInfo(pdb,ptab);	
	printf("According to which Column:");
	scanf(" %s",columnname);
	pcol=GetColumn(ptab,columnname);
	if(pcol==NULL)return;

	printf("Item Value:");
	switch(pcol->cType)
	{
	case 'i':	scanf(" %d",&data.value.iValue);break;
	case 'f':	scanf(" %f",&data.value.fValue);break;
	case 'c':	scanf(" %c",&data.value.cValue);break;
	case 's':	scanf(" %s",data.value.strValue);break;
	}

	index=FindRecord(ptab,pcol,&data);
	ShowRecord(databasehead->tablehead,index+1);
}

/*---------------------------------------
	Modify a record interface	 
---------------------------------------*/
void Modify(void)
{
	char databasename[MAX_NAME];
	char tablename[MAX_NAME];
	char columnname[MAX_NAME];

	struct database *pdb;
	struct table *ptab;
	struct column *pcol;
	struct data data;
	data.next=NULL;
	
	printf("Modify a record from a table\n");	
	printf("Database Name:");
	scanf(" %s",databasename);
	pdb=GetDatabase(databasename);
	if(pdb==NULL)return;
	printf("Table Name:");
	scanf(" %s",tablename);
	ptab=GetTable(pdb,tablename);
	if(ptab==NULL)return;
	printf("According to which Column:");
	scanf(" %s",columnname);
	pcol=GetColumn(ptab,columnname);
	if(pcol==NULL)return;
	printf("Item Value:");
	switch(pcol->cType)
	{
	case 'i':	scanf(" %d",&data.value.iValue);break;
	case 'f':	scanf(" %f",&data.value.fValue);break;
	case 'c':	scanf(" %c",&data.value.cValue);break;
	case 's':	scanf(" %s",data.value.strValue);break;
	}

	ModifyRecord(ptab,pcol,&data);
}

/*---------------------------------------
	Delete a record interface	 
---------------------------------------*/
void Delete(void)
{
	char databasename[MAX_NAME];
	char tablename[MAX_NAME];
	char columnname[MAX_NAME];

	struct database *pdb;
	struct table *ptab;
	struct column *pcol;
	struct data data;
	data.next=NULL;
	
	printf("Delete a record from a table\n");	
	printf("Database Name:");
	scanf(" %s",databasename);
	pdb=GetDatabase(databasename);
	if(pdb==NULL)return;
	printf("Table Name:");
	scanf(" %s",tablename);
	ptab=GetTable(pdb,tablename);
	if(ptab==NULL)return;
	printf("According to which Column:");
	scanf(" %s",columnname);
	pcol=GetColumn(ptab,columnname);
	if(pcol==NULL)return;
	printf("Item Value:");
	switch(pcol->cType)
	{
	case 'i':	scanf(" %d",&data.value.iValue);break;
	case 'f':	scanf(" %f",&data.value.fValue);break;
	case 'c':	scanf(" %c",&data.value.cValue);break;
	case 's':	scanf(" %s",data.value.strValue);break;
	}

	DeleteRecord(ptab,pcol,&data);
}


/*///////////////////////////////////////////////////////////////////
	the functions below are used to get a pointer to a database
		or a table or a column
	Author: Lijun
///////////////////////////////////////////////////////////////////**/

/*---------------------------------------
	 Get pointer to the database
---------------------------------------*/
struct database *GetDatabase(char databasename[])
{
	struct database *searchdatabase=databasehead;
	
	while(searchdatabase!=NULL)
	{	// find it
		if(strcmp(searchdatabase->cName,databasename)==0)
			return searchdatabase;
		else
			searchdatabase=searchdatabase->next;  
	}

	// cannot find it
	printf("Sorry  can not find  the  database \n ");
	return NULL;
}
/*---------------------------------------
	Get pointer to the table 
---------------------------------------*/
struct table *GetTable(struct database * pdb,char tablename[])
{
	struct table *searchtable=pdb->tablehead; 

	while(searchtable!=NULL)
	{	// find it
		if(strcmp(searchtable->cName,tablename)==0)
			return searchtable;
		else
			searchtable=searchtable->next;
	}

	// cannot find it
	printf("Sorry  can not find  the  table \n ");
	return NULL;
}
/*---------------------------------------
	 Get pointer to the column
---------------------------------------*/
struct column *GetColumn(struct table *ptab,char columnname[])
{
	struct column *searchcolumn=ptab->columnhead; 

	while(searchcolumn!=NULL)
	{	// find it
		if(strcmp(searchcolumn->cName,columnname)==0)
			return searchcolumn;
		else
			searchcolumn=searchcolumn->next;
	}

	// cannot find it
	printf("Sorry  can not find  the  column \n ");
	return NULL;
}

/*///////////////////////////////////////////////////////////////////
	the functions below are for SQL function
	Author: Lijun
///////////////////////////////////////////////////////////////////**/

/*---------------------------------------
	 input the SQL sentences
---------------------------------------*/
void InputSQL()
{
	char word[MAX_STR];
	int i;

	printf("Please input your commands:\n");
	scanf(" %s",word);
	for(i=0;strcmp(word,"go")!=0&&strcmp(word,"GO")!=0;i++)
	{
		strcpy(com[i],word);
		scanf(" %s",word);
	}
	strcpy(com[i],word);// store go
}
/*---------------------------------------
	 check the grammer of the SQL
---------------------------------------*/
int CheckSQL()
{
	int i;

	if( strcmp(com[0],"use")!=0&&strcmp(com[0],"USE")!=0 )
		{
			printf("ERROR: USE is not used correctly!\n");
			return 0;
		}
	// com[1] is databasename
	for(i=2;i<50;i++)
	{	
		if(strcmp(com[i],"select")==0||strcmp(com[i],"SELECT")==0) 
		{	
			if( strcmp(com[++i],"*")!=0||strcmp(com[++i],"from")!=0 ) 
			{
				printf("ERROR: SELECT * FROM <tablename>!\n");
				return 0;
			}
			i++;	// tablename
			if(	  strcmp(com[i+1],"SELECT")==0
				||strcmp(com[i+1],"DELETE")==0
				||strcmp(com[i+1],"select")==0
				||strcmp(com[i+1],"delete")==0
				||strcmp(com[i+1],"go")!=0
				||strcmp(com[i+1],"GO")!=0 )
			{}
			
			else if(strcmp(com[++i],"where")!=0	// skip columnname
					||strcmp(com[i+2],"is")!=0 ) 
			{
				printf("ERROR: WHERE <columnname> IS!\n");
				return 0;
				i++;	// columnvalue
			}
		}

		if(strcmp(com[i],"delete")==0||strcmp(com[i],"DELETE")==0) 
		{
			if(strcmp(com[++i],"from")!=0&&strcmp(com[i],"FROM")!=0) 
			{
				printf("ERROR: DELETLE FROM <tablename>!\n");
				return 0;
			}
			i++;	// tablename
			if(   strcmp(com[++i],"where")!=0
				||strcmp(com[i+2],"is")!=0   ) 
			{
			
				printf("ERROR: WHERE <columnname> IS!\n");
				return 0;
				i++;	// columnvalue
			}
		}

		if(strcmp(com[i],"go")==0||strcmp(com[i],"GO")==0)
			break;
	}
	if(i>=50)
	{
		printf("ERROR: lack of GO!\n");
		return 0;
	}
	return 1;
}
/*---------------------------------------
	 convert char[] into float
---------------------------------------*/
float chartofloat(char s[])
{
	int isfrac=0;
	char *p;
	float power=1,value=0;
	p=s;
	while(*p!='\0')
	{
		if(*p=='.') isfrac=1;
		else if(isfrac==0)value=value*10+(float)(*p-48);
		else
		{
			power=power*10;
			value=value+(float)(*p-48)/power;
		}
		p++;
	}
	return(value);
}
/*---------------------------------------
	 execute SQL
---------------------------------------*/
int DoSQL()			
{
	char databasename[MAX_NAME];
	char tablename[MAX_NAME];
	char columnname[MAX_NAME];

	struct database *pdb;
	struct table *ptab;
	struct column *pcol;
	struct data data;

	int i;
	int del=0,sel=0;
	// store the name of database
	strcpy(databasename,com[1]);
	//find the point to database
	pdb=GetDatabase(databasename);
	
	printf("Please wait ...\n");
	for(i=0;strcmp(com[i],"go")!=0&&strcmp(com[i],"GO")!=0;i++)
	{	
		if(strcmp(com[i],"select")==0||strcmp(com[i],"SELECT")==0) 
		{	
			i=i+2;	// skip * from
			strcpy(tablename,com[++i]);
			//find the point to  table
			ptab=GetTable(pdb,tablename);
			// select * from <tablename>
			// show all the records in a table
			if(strcmp(com[i+1],"WHERE")!=0&&strcmp(com[i+1],"where")!=0)
				ShowAllRecord(ptab);
			else 
				sel=1;

		}
		if(strcmp(com[i],"delete")==0||strcmp(com[i],"DELETE")==0) 
		{
			i=i+1;	// skip from
			// store the tablename
			strcpy(tablename,com[++i]);
			//find the point to table
			ptab=GetTable(pdb,tablename);
			del=1;
		}
	
		if(strcmp(com[i],"where")==0||strcmp(com[i],"WHERE")==0) 
		{
			// store the columnname
			strcpy(columnname,com[++i]);
			//find the point to column 
			pcol=GetColumn(ptab,columnname);

			i=i+1;	// skip is
			// convert the value to data according to the type of the column
			switch(pcol->cType)
			{
			case 'i':data.value.iValue=(int)chartofloat(com[++i]);	break;
			case 'f':data.value.fValue=chartofloat(com[++i]);	break;
			case 'c':data.value.cValue=com[++i][0];			break;
			case 's':strcpy(data.value.strValue,com[++i]);	break;
			}

			if(sel==1)
			{	
				// find the record contain the data and show it
				ShowRecord(ptab,FindRecord(ptab,pcol,&data)+1);
				sel=0;
			}
			if(del==1)
			{
				// delete the record contain the data
				DeleteRecord(ptab,pcol,&data);
				del=0;
			}
		}
	}
	// sucessful
	if(strcmp(com[i],"go")==0||strcmp(com[i],"GO")==0)
		return 1;
	// processing error
	return 0;
}
/*---------------------------------------
	 Interface of executing SQL
---------------------------------------*/
void SQL(void)
{
	InputSQL();
	if(!CheckSQL())
	{	printf("Wrong Grammer!\n");
		system("pause");
		return;
	}
	if(!DoSQL())
	{	printf("Processing Error !\n");
		system("pause");
		return;
	}
	printf("Processing sucessful!\n");
	system("pause");
}