www.pudn.com > database_edwardversion.rar > query.c
#include#include #include #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 !\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 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 !\n"); return 0; } i++; // tablename if( strcmp(com[++i],"where")!=0 ||strcmp(com[i+2],"is")!=0 ) { printf("ERROR: WHERE 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 // 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"); }