dbbackup.cpp

基于ODBC的数据库备份及恢复

// DBBackup.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include <stdio.h>
#include <ctype.h>
#include "FuctionDef.h"
#define MAX_DATA 100

int main(int argc, char* argv[])
{	
	int run=1;//系统内部传递命令执行状况的参数  
	int choice;	
	while (run!=0)
	{	
		OrderList();
		scanf("%d",&choice);
		if (choice==3) run=Quit();
		else if (choice==1) run=Backup();
		else if (choice==2) run=Restore();
		else puts("Input Error!");
	}
	return 0;
}//主函数

int OrderList()
{   
	puts("");
	puts("enter 1 to Backup");
	puts("enter 2 to Restore");
	puts("enter 3 to Exit");
	puts("");
	return 1;
}//命令列表

int Quit()
{	
	return 0;
}//退出系统，释放内存，保存数据 

int strtoint(char *s)
{int n,k,i,j,result;
 boolean minus;

 n=strlen(s);
 if (s[0]=='-') 
   {minus=true;
    k=1;
    n--;
   }
 else 
   {minus=false;
    k=0;
   }//判断正负数
 result=0;                
 while (n!=0)
   {j=1; 
    for(i=1;i<=n-1;i++) j=j*10;
    result=result+(s[k]-48)*j;
    n--;
    k++;
   }
 if (minus) result=(-1)*result;     
 return(result); 
}//将字符串转化为整形

int Backup()
{   struct StrBuf
	{char string[MAX_DATA];
	 struct StrBuf *next;};

	FILE *fp;
	int i;

	RETCODE rc,rt;
	HENV henv;
	HDBC hdbc;
	HSTMT hstmt;
	
	SQLCHAR DBName[MAX_DATA];
	SQLCHAR DBUser[MAX_DATA];
	SQLCHAR DBPassword[MAX_DATA];
	SQLCHAR TableName[MAX_DATA];
	SQLCHAR Select[MAX_DATA]="select * from ";
	char BackupPath[MAX_DATA];
	 
	char szTableName[MAX_DATA];
	char szTableType[MAX_DATA];
	char szColumnName[MAX_DATA];
	char szTypeName[MAX_DATA];
	char szSQLTypeName[MAX_DATA];
	char szData[MAX_DATA];
	char szNullable[MAX_DATA];
	char szUnique[MAX_DATA];
	char szIndexName[MAX_DATA];
	char szIndexType[MAX_DATA];
	char szCollation[MAX_DATA];
	char szPrivilege[MAX_DATA];
	char szGrantee[MAX_DATA];
	char szGrantable[MAX_DATA];
	

	struct StrBuf *TableNameBuf;
	struct StrBuf *p;

	SDWORD cbTableName;
	SDWORD cbTableType;
	SDWORD cbColumnName;
	SDWORD cbTypeName;	
	SDWORD cbSQLTypeName;
	SDWORD cbData;
	SDWORD cbNullable;
	SDWORD cbUnique;
	SDWORD cbIndexName;
	SDWORD cbIndexType;
	SDWORD cbCollation;
	SDWORD cbPrivilege;
	SDWORD cbGrantee;
	SDWORD cbGrantable;

	puts("please input DateBase Name:");
	scanf("%s",DBName);

	SQLAllocEnv(&henv);
	SQLAllocConnect(henv,&hdbc);
	rc=SQLConnect(hdbc,DBName,SQL_NTS,NULL,0,NULL,0);
	if (rc!=SQL_SUCCESS)
	{
		SQLFreeConnect(hdbc);
		SQLFreeEnv(henv);
		puts("ODBC Connection Error!");
		return 1;
	}//连接数据库	

	puts("please input DateBase Backup Entire Path:");
	scanf("%s",BackupPath);	
	
	if ((fp=fopen(BackupPath,"w"))==NULL)
      {
		puts("File Writting Error!");
		return 1;
      }//打开文件

	TableNameBuf=new(StrBuf);
	p=TableNameBuf;
	SQLAllocStmt(hdbc,&hstmt);
	SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL,0);
	for(rc=SQLFetch(hstmt);rc==SQL_SUCCESS;rc=SQLFetch(hstmt))
	{		
		SQLGetData(hstmt,3,SQL_C_CHAR,szTableName,sizeof(szTableName),&cbTableName);
		SQLGetData(hstmt,4,SQL_C_CHAR,szTableType,sizeof(szTableType),&cbTableType);		
		for(i=0;szTableType[i]!='\0';i++) szTableType[i]=tolower(szTableType[i]);
		if (!strcmp(szTableType,"table")) 
		{
			fputs(szTableName,fp);			
			fputc('\n',fp);
			strcpy(p->string,szTableName);
			strcpy(szTableName,"");
			p->next=new(StrBuf);
			p=p->next;
		}
	}
	SQLFreeStmt(hstmt,SQL_DROP);
	fputc('\n',fp);		
	p->next=NULL;//获取数据库中全部用户表名

	for(p=TableNameBuf;p->next;p=p->next)
	{
		for(i=0;i<MAX_DATA;i++) TableName[i]=p->string[i];
		SQLAllocStmt(hdbc,&hstmt);
		SQLColumns(hstmt,NULL,0,NULL,0,TableName,SQL_NTS, NULL,0);  
		for(rc=SQLFetch(hstmt);rc==SQL_SUCCESS;rc=SQLFetch(hstmt))
		{		
			SQLGetData(hstmt,4,SQL_C_CHAR,szColumnName,sizeof(szColumnName),&cbColumnName);
			SQLGetData(hstmt,6,SQL_C_CHAR,szTypeName,sizeof(szTypeName),&cbTypeName);
			SQLGetData(hstmt,11,SQL_C_CHAR,szNullable,sizeof(szNullable),&cbNullable);
			SQLGetData(hstmt,14,SQL_C_CHAR,szSQLTypeName,sizeof(szSQLTypeName),&cbSQLTypeName);
			fputs(szColumnName,fp);
			strcpy(szColumnName,"");
			fputc(',',fp);			
			fputs(szTypeName,fp);
			strcpy(szTypeName,"");
			fputc(',',fp);
			fputs(szNullable,fp);
			strcpy(szNullable,"");
			fputc(',',fp);
			fputs(szSQLTypeName,fp);
			strcpy(szSQLTypeName,"");
			fputc(',',fp);
		}
		SQLFreeStmt(hstmt,SQL_DROP);
		fputc('\n',fp);//获取表中属性名及其数据类型	

		SQLAllocStmt(hdbc,&hstmt);
		for(i=14;i<MAX_DATA;i++) Select[i]=p->string[i-14];
		SQLExecDirect(hstmt,Select,SQL_NTS);  
		for(rc=SQLFetch(hstmt);rc==SQL_SUCCESS;rc=SQLFetch(hstmt))
		{		
			i=1;
			do
			{
				rt=SQLGetData(hstmt,i,SQL_C_CHAR,szData,sizeof(szData),&cbData);
				if (rt!=SQL_SUCCESS) break;
				fputs(szData,fp);
				fputc(',',fp);
				strcpy(szData,"");
				i++;
			}while(1);
			fputc('\n',fp);
		}
		SQLFreeStmt(hstmt,SQL_DROP);
		fputc('\n',fp);//获取元数据
			
		SQLAllocStmt(hdbc,&hstmt);
		SQLStatistics(hstmt,NULL,0,NULL,0,TableName,SQL_NTS,SQL_INDEX_ALL,SQL_ENSURE);
		for(rc=SQLFetch(hstmt);rc==SQL_SUCCESS;rc=SQLFetch(hstmt))
		{				
			SQLGetData(hstmt,4,SQL_C_CHAR,szUnique,sizeof(szUnique),&cbUnique);		
			SQLGetData(hstmt,6,SQL_C_CHAR,szIndexName,sizeof(szIndexName),&cbIndexName);
			SQLGetData(hstmt,7,SQL_C_CHAR,szIndexType,sizeof(szIndexType),&cbIndexType);			
			SQLGetData(hstmt,9,SQL_C_CHAR,szColumnName,sizeof(szColumnName),&cbColumnName);
			SQLGetData(hstmt,10,SQL_C_CHAR,szCollation,sizeof(szCollation),&cbCollation);
			if ((strtoint(szUnique)==0 || strtoint(szUnique)==1)&&(strlen(szIndexName)!=0))
			{				
				fputs(szUnique,fp);
				strcpy(szUnique,"");
				fputc(',',fp);
				fputs(szIndexName,fp);
				strcpy(szIndexName,"");
				fputc(',',fp);
				fputs(szIndexType,fp);
				strcpy(szIndexType,"");
				fputc(',',fp);
				fputs(szColumnName,fp);
				strcpy(szColumnName,"");
				fputc(',',fp);
				fputs(szCollation,fp);
				strcpy(szCollation,"");
				fputc(',',fp);
				fputc('\n',fp);
			}
		}
		SQLFreeStmt(hstmt,SQL_DROP);
		fputc('\n',fp);//获取数据库中索引
	}
	
	delete(TableNameBuf);
	fclose(fp);
	SQLDisconnect(hdbc);
	SQLFreeConnect(hdbc);
	SQLFreeEnv(henv);
	puts("Backup Process has been completed.");
	return 1;
}

int Restore()
{   struct StrBuf
	{char string[MAX_DATA];
	 struct StrBuf *next;};

	struct AtrBuf
	{char ColumnName[MAX_DATA];
	 char TypeName[MAX_DATA];
	 int SQLType;
	 int Nullable;
	 char Data[MAX_DATA];
	 struct AtrBuf *next;};

	FILE *fp;
	int i;
	char c;

	RETCODE rc;
	HENV henv;
	HDBC hdbc;
	HSTMT hstmt;
	
	SQLCHAR DBName[MAX_DATA];
	SQLCHAR DBUser[MAX_DATA];
	SQLCHAR DBPassword[MAX_DATA];
	SQLCHAR SQLStatement[255];	
	char string[255];
	char BackupPath[MAX_DATA];
	 
	char szTableName[MAX_DATA];
	char szColumnName[MAX_DATA];
	char szTypeName[MAX_DATA];	
	char szSQLTypeName[MAX_DATA];
	char szData[MAX_DATA];
	char szNullable[MAX_DATA];
	char szUnique[MAX_DATA];
	char szIndexName[MAX_DATA];
	char szIndexType[MAX_DATA];
	char szCollation[MAX_DATA];
	char szPrivilege[MAX_DATA];
	char szGrantee[MAX_DATA];
	char szGrantable[MAX_DATA];
	

	struct StrBuf *TableNameBuf;
	struct StrBuf *p;
	struct AtrBuf *TypeNameBuf;
	struct AtrBuf *q;

	puts("please input DateBase Backup Entire Path:");
	scanf("%s",BackupPath);	
	
	if ((fp=fopen(BackupPath,"r"))==NULL)
      {
		puts("File Reading Error!");
		puts(BackupPath);
		return 1;
      }//打开文件

	puts("please input DateBase Name:");
	scanf("%s",DBName);

	SQLAllocEnv(&henv);
	SQLAllocConnect(henv,&hdbc);
	rc=SQLConnect(hdbc,DBName,SQL_NTS,NULL,0,NULL,0);
	if (rc!=SQL_SUCCESS)
	{
		SQLFreeConnect(hdbc);
		SQLFreeEnv(henv);
		puts("ODBC Connection Error!");
		return 1;
	}//连接数据库

	TableNameBuf=new(StrBuf);
	p=TableNameBuf;
	while(1)
	{	
		i=0;
		do
		{
			c=fgetc(fp);
			szTableName[i]=c;
			i++;
		}while (c!='\n');
		if (i==1) break;
		szTableName[i-1]='\0';
		strcpy(p->string,szTableName);
		strcpy(szTableName,"");
		p->next=new(StrBuf);
		p=p->next;
	}	
	p->next=NULL;//读出全部表名称

	for(p=TableNameBuf;p->next;p=p->next)
	{	
		TypeNameBuf=new(AtrBuf);
		q=TypeNameBuf;
		while(1)
		{
			i=0;
			do
			{
				c=fgetc(fp);
				if (c=='\n') break;
				szColumnName[i]=c;
				i++;
			}while (c!=',');
			if (c=='\n') break;
			szColumnName[i-1]='\0';
			i=0;
			do
			{
				c=fgetc(fp);
				szTypeName[i]=c;
				i++;
			}while (c!=',');
			szTypeName[i-1]='\0';			
			i=0;
			do
			{
				c=fgetc(fp);
				szNullable[i]=c;
				i++;
			}while (c!=',');
			szNullable[i-1]='\0';
			i=0;
			do
			{
				c=fgetc(fp);
				szSQLTypeName[i]=c;
				i++;
			}while (c!=',');
			szSQLTypeName[i-1]='\0';
			strcpy(q->ColumnName,szColumnName);
			strcpy(szColumnName,"");
			strcpy(q->TypeName,szTypeName);
			strcpy(szTypeName,"");
			q->Nullable=strtoint(szNullable);
			strcpy(szNullable,"");			
			q->SQLType=strtoint(szSQLTypeName);
			strcpy(szSQLTypeName,"");
			q->next=new(AtrBuf);
			q=q->next;
		}
		q->next=NULL;//读出表的属性

		strcpy(string,"DROP TABLE ");
		strcat(string,p->string);		
		for(i=0;string[i]!='\0';i++) SQLStatement[i]=string[i];	
		SQLStatement[i]='\0';
		SQLAllocStmt(hdbc,&hstmt);
		SQLExecDirect(hstmt,SQLStatement,SQL_NTS);  
		SQLFreeStmt(hstmt,SQL_DROP);//删除现有的表

		strcpy(string,"CREATE TABLE ");
		strcat(string,p->string);
		strcat(string," ( ");
		for(q=TypeNameBuf;q->next;q=q->next)
		{
			strcat(string,q->ColumnName);
			strcat(string," ");
			strcat(string,q->TypeName);
			if (q->Nullable==0) strcat(string," NOT NULL");
			strcat(string," , ");			
		}
		string[strlen(string)-3]='\0';
		strcat(string," ) ");
		for(i=0;string[i]!='\0';i++) SQLStatement[i]=string[i];
		SQLStatement[i]='\0';
		SQLAllocStmt(hdbc,&hstmt);
		SQLExecDirect(hstmt,SQLStatement,SQL_NTS);  
		SQLFreeStmt(hstmt,SQL_DROP);//创建表		
			

		while(1)
		{
			for(q=TypeNameBuf;q->next;q=q->next)
			{
				i=0;
				do
				{
					c=fgetc(fp);
					if (c=='\n') break;
					szData[i]=c;
					i++;
				}while (c!=',');
				if (c=='\n') break;
				szData[i-1]='\0';
				if (i==1) q->Nullable=1;
				else q->Nullable=0;
				if (q->SQLType!=SQL_DECIMAL  && q->SQLType!=SQL_NUMERIC  && q->SQLType!=SQL_SMALLINT  && q->SQLType!=SQL_INTEGER && q->SQLType!=SQL_REAL  && q->SQLType!=SQL_FLOAT  && q->SQLType!=SQL_BIT  && q->SQLType!=SQL_DOUBLE  && q->SQLType!=SQL_TINYINT && q->SQLType!=SQL_BIGINT)
				{
					strcpy(q->Data,"'");
					strcat(q->Data,szData);
					strcat(q->Data,"'");					
				}
				else
					strcpy(q->Data,szData);
				strcpy(szData,"");
			}
			if (c=='\n') break;//读出数据
			strcpy(string,"INSERT INTO ");
			strcat(string,p->string);
			strcat(string," ( ");
			for(q=TypeNameBuf;q->next;q=q->next)
			{
				if (q->Nullable==0) 
				{
					strcat(string,q->ColumnName);				
					strcat(string," , ");			
				}
			}
			string[strlen(string)-3]='\0';
			strcat(string," ) VALUES ( ");	
			for(q=TypeNameBuf;q->next;q=q->next)
			{
				if (q->Nullable==0) 
				{
					strcat(string,q->Data);				
					strcat(string," , ");			
				}
			}
			string[strlen(string)-3]='\0';
			strcat(string," ) ");	
			for(i=0;string[i]!='\0';i++) SQLStatement[i]=string[i];
			SQLStatement[i]='\0';
			SQLAllocStmt(hdbc,&hstmt);
			SQLExecDirect(hstmt,SQLStatement,SQL_NTS);  
			SQLFreeStmt(hstmt,SQL_DROP);	
			fgetc(fp);//插入数据
		}
		delete(TypeNameBuf);

		while(1)
		{
			i=0;
			do
			{
				c=fgetc(fp);
				if (c=='\n') break;
				szUnique[i]=c;
				i++;
			}while (c!=',');
			if (c=='\n') break;
			szUnique[i-1]='\0';
			i=0;
			do
			{
				c=fgetc(fp);
				if (c=='\n') break;
				szIndexName[i]=c;
				i++;
			}while (c!=',');
			if (c=='\n') break;
			szIndexName[i-1]='\0';
			i=0;
			do
			{
				c=fgetc(fp);
				szIndexType[i]=c;
				i++;
			}while (c!=',');
			szIndexType[i-1]='\0';	
			i=0;
			do
			{
				c=fgetc(fp);
				szColumnName[i]=c;
				i++;
			}while (c!=',');
			szColumnName[i-1]='\0';
			i=0;
			do
			{
				c=fgetc(fp);
				szCollation[i]=c;
				i++;
			}while (c!=',');
			szCollation[i-1]='\0';		
			fgetc(fp);//读出索引
			strcpy(string,"DROP INDEX ");
			strcat(string,szIndexName);
			for(i=0;string[i]!='\0';i++) SQLStatement[i]=string[i];
			SQLStatement[i]='\0';
			SQLAllocStmt(hdbc,&hstmt);
			SQLExecDirect(hstmt,SQLStatement,SQL_NTS);  
			SQLFreeStmt(hstmt,SQL_DROP);//删除现有索引
			strcpy(string,"CREATE ");
			if (strtoint(szUnique)==FALSE) strcat(string,"UNIQUE ");
			if (strtoint(szIndexType)==SQL_INDEX_CLUSTERED) strcat(string,"CLUSTER ");
			strcat(string,"Index ");
			strcat(string,szIndexName);
			strcat(string," On ");
			strcat(string,p->string);
			strcat(string," ( ");
			strcat(string,szColumnName);
			if (szCollation[0]=='A') strcat(string," ASC ) ");
			else strcat(string," DESC ) ");
			for(i=0;string[i]!='\0';i++) SQLStatement[i]=string[i];
			SQLStatement[i]='\0';
			SQLAllocStmt(hdbc,&hstmt);
			SQLExecDirect(hstmt,SQLStatement,SQL_NTS);  
			SQLFreeStmt(hstmt,SQL_DROP);//创建索引
		}	
	}

	delete(TableNameBuf);	
	fclose(fp);
	SQLDisconnect(hdbc);
	SQLFreeConnect(hdbc);
	SQLFreeEnv(henv);
	puts("Restore Process has been completed.");
	return 1;
}