#include "zhlb.ch"
#include <conio.h>
#include <stdlib.h>
#include <stdio.h>
#include <dos.h>
#include <string.h>
#include <sys\types.h>
#include <sys\stat.h>
#include <io.h>
#include <errno.h>
#include <fcntl.h>
#include <math.h>

#define NUM 1000
#define FLDNAME                 1
#define CHINESENAME             2
#define TYPELEN                 3
#define PKEY                    4
#define FKEY                    5
#define REFTAB                  6
#define REFCOL                  7
#define _NULL_                  8
#define CASCADE                 9
#define DEFAULT                 10
#define RULE                    11

char *volid_table[20]={"database","login","grant","revoke","data",NULL};
char *data_type[20]={"char","int","tinyint","smallint","float","image","text","integer","numeric",NULL};
char buf[NUM];
char dbname[20];
long lsk;
FILE *fmt_lp,*sql_lp;

void get_table_name(int,char *);
void create_table(char *);
void load_data(char *);
void creat_forkey(char *);
void creat_uniindex(char *);
void creat_default(char *);
void creat_rule(char *);
void creat_trigger(char *);
void get_name(char *,char*,int);

main(int argc,char *argv[])
{
	char file_fmt[30],file_sql[30];
	char cc[20];
	int number;

	if(argc<4) exit(0);
	strcpy(dbname,argv[3]);
	strcpy(file_fmt,argv[4]);strcat(file_fmt,".fmt");
	strcpy(file_sql,argv[4]);strcat(file_sql,".sql");
	if((fmt_lp=fopen(file_fmt,"r+w"))==NULL)
	{ printf("%s not found\n",file_fmt);exit(0); }
	if((sql_lp=fopen(file_sql,"w+t"))==NULL)
	{ printf("%s create fail\n",file_sql);exit(0); }
	while(1)  {
	printf("              1:Create table\n");
	printf("              2:Insert data\n");
	printf("              3:Create foreign keys\n");
	printf("              4:Create index\n");
	printf("              5:Create default\n");
	printf("              6:Create rule\n");
	printf("              7:Create trigger\n");
	printf("              8:Create all(1-7)\n");
	printf("              ************************\n");
	printf("              Please Input(q-quit):");
	gets(cc);number=atoi(cc);printf("\n");
	if(cc[0]=='q'||cc[0]=='Q') {fclose(fmt_lp);fclose(sql_lp);exit(0);}
	while(1)  {
	  printf("              Input table name(*-all table,q-quit):");
	  gets(cc);printf("\n");
	  if(cc[0]=='q'||cc[0]=='Q') break;
	  sprintf(buf,"use %s\n",dbname);
	  fputs(buf,sql_lp);
	  fputs("go\n",sql_lp);
	  fseek(fmt_lp,0L,SEEK_SET);
	  switch(number)  {
	    case 1:
		sprintf(buf,"/** create table into %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(1,cc);
		break;
	  case 2:
		sprintf(buf,"/** load data into %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(2,cc);
		break;
	  case 3:
		sprintf(buf,"/** create foreignkey for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(3,cc);
		break;
	  case 4:
		sprintf(buf,"/** create unique index for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(4,cc);
		break;
	  case 5:
		sprintf(buf,"/** create default for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(5,cc);
		break;
	  case 6:
		sprintf(buf,"/** create rule for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(6,cc);
		break;
	  case 7:
		sprintf(buf,"/** create trigger for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(7,cc);
		break;
	  case 8:
		sprintf(buf,"/** create table into %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(1,cc);
		fseek(fmt_lp,0L,SEEK_SET);
		sprintf(buf,"/** load data into %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(2,cc);
		fseek(fmt_lp,0L,SEEK_SET);
		sprintf(buf,"/** create foreignkey for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(3,cc);
		fseek(fmt_lp,0L,SEEK_SET);
		sprintf(buf,"/** create unique index for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(4,cc);
		fseek(fmt_lp,0L,SEEK_SET);
		sprintf(buf,"/** create default for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(5,cc);
		fseek(fmt_lp,0L,SEEK_SET);
		sprintf(buf,"/** create rule for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(6,cc);
		fseek(fmt_lp,0L,SEEK_SET);
		sprintf(buf,"/** create trigger for %s **/\n",dbname);
		fputs(buf,sql_lp);
		get_table_name(7,cc);
		break;
	  }
	}
	}
	fclose(fmt_lp);
	fclose(sql_lp);
}

void get_table_name(int flag,char *table_name)
{
	int i,j,k;
	char buf1[NUM];

	while(fgets(buf,NUM,fmt_lp)!=NULL)  {
	  sub_space_tab(buf);
	  if(buf[0]=='[')  {
	    i=1;j=1;
	    while(buf[i]!=']'&&buf[i]!='\0')
	    buf1[i-j]=buf[i],i++;
	    if(buf[i]==']')  {
	      buf1[i-j]='\0';
	      sub_space_tab(buf1);
	      if(table_name[0]!='*'&&stricmp(buf1,table_name)!=0) continue;
	      k=0;
	      while(volid_table[k]!=NULL&&stricmp(volid_table[k],buf1)!=0) k++;
	      if(volid_table[k]==NULL)
	      switch(flag)  {
		case 1:
			create_table(buf1);
			break;
		case 2:
			load_data(buf1);
			break;
		case 3:
			creat_forkey(buf1);
			break;
		case 4:
			creat_uniindex(buf1);
			break;
		case 5:
			creat_default(buf1);
			break;
		case 6:
			creat_rule(buf1);
			break;
		case 7:
			creat_trigger(buf1);
			break;
	      }
	    }
	  }
	}
}

void load_data(char *table_name)
{
	int i,j,k;
	char buf1[NUM],cc[200],typelen[80];
	int type_flag[100];

	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);
	if(buf1[0]=='#')  {
	  i=0;
	  while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
	    sub_space_tab(buf1);
	    if(buf1[0]=='[') break;
	    if(buf1[0]=='\n')  continue;
	    get_name(buf1,typelen,TYPELEN);
	    strncpy(cc,typelen,4);cc[4]='\0';
	    if(stricmp(cc,"char")==0) type_flag[i]=0;
	    else  {
	      j=0;while(data_type[j]!=NULL&&stricmp(typelen,data_type[j])!=0) j++;
	      if(data_type[j]==NULL)  { printf("%s not found %s type\n",table_name,typelen);exit(0); }
	      type_flag[i]=j;
	    }
	    i++;
	  }
	  i=1;while(buf1[i]!=']'&&buf1[i]!='\0') cc[i-1]=buf1[i],i++;
	  cc[i-1]='\0';
	  if(stricmp(cc,"data")==0)  {
	   while(1) {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
	    sub_space_tab(buf1);
	    if(buf1[0]=='[') break;
	    if(buf1[0]=='\n') continue;
	    i=0;j=0;k=j;
	    sprintf(buf,"insert into %s..%s values(",dbname,table_name);
	    while(buf1[j]!='\0')  {
	      while(buf1[j]!='|'&&buf1[j]!='\n'&&buf1[j]!='\0')
	      cc[j-k]=buf1[j],j++;
	      cc[j-k]='\0';
	      sub_space_tab(cc);
	      if(cc[0]=='\0') strcat(buf,"NULL");
	      else  {
		if(type_flag[i]==0)
		strcat(buf,"'"),strcat(buf,cc),strcat(buf,"'");
		else strcat(buf,cc);
	      }
	      if(buf1[j]=='\n') j++;
	      if(buf1[j]=='|') j++,strcat(buf,",");
	      else strcat(buf,")\n");
	      k=j;
	      i++;
	    }
	    fputs(buf,sql_lp);
	    fputs("go\n",sql_lp);
	   }
	  }
	  fseek(fmt_lp,lsk,SEEK_SET);
	}
	else { printf("%s format fail\n",table_name);exit(0);}
}

void create_table(char *table_name)
{
	char buf1[NUM];
	char fldname[80],typelen[80],null_f[80],pkey[80],prim[200];
	int fl;

	sprintf(buf1,"if exists (select * from sysobjects where type='U' and name='%s')\n",table_name);
	fputs(buf1,sql_lp);
	fputs("begin\n",sql_lp);
	sprintf(buf1,"  drop table %s\n",table_name);
	fputs(buf1,sql_lp);
	fputs("end\n",sql_lp);
	fputs("go\n",sql_lp);
	sprintf(buf1,"create table %s..%s (\n",dbname,table_name);
	fputs(buf1,sql_lp);
	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);strcpy(prim,"");
	if(buf1[0]=='#')  {
	  fl=0;
	  while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) { fputs(")\n",sql_lp);break; }
	    sub_space_tab(buf1);
	    if(buf1[0]!='\n'&&buf1[0]!='[')
	    if(fl!=0) fputs(",\n",sql_lp);
	    else fl=1;
	    if(buf1[0]=='[') { fseek(fmt_lp,lsk,SEEK_SET);fputs(")\n",sql_lp);break;  }
	    if(buf1[0]=='\n')  continue;
	    get_name(buf1,fldname,FLDNAME);
	    get_name(buf1,typelen,TYPELEN);
	    get_name(buf1,null_f,_NULL_);
	    get_name(buf1,pkey,PKEY);
	    if(pkey[0]=='y'||pkey[0]=='Y') 
	    strcat(prim,","),strcat(prim,fldname);
	    if(null_f[0]=='n'||null_f[0]=='N')
	    strcpy(null_f,"not null");
	    else
	    strcpy(null_f,"null");
	    sprintf(buf1,"   %s %s %s",fldname,typelen,null_f);
	    fputs(buf1,sql_lp);
	  }
	  fputs("go\n",sql_lp);
	  sprintf(buf1,"execute sp_primarykey %s%s\n",table_name,prim);
	  fputs(buf1,sql_lp);
	  fputs("go\n",sql_lp);
	}
	else { printf("%s format fail\n",table_name);exit(0);}
}

void creat_forkey(char *table_name)
{
	char buf1[NUM];
	char reftab[80],fkey[80],fldname[20];

	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);
	if(buf1[0]=='#')  {
	  while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
	    sub_space_tab(buf1);
	    if(buf1[0]=='[') { fseek(fmt_lp,lsk,SEEK_SET);break;  }
	    if(buf1[0]=='\n')  continue;
	    get_name(buf1,fldname,FLDNAME);
	    get_name(buf1,reftab,REFTAB);
	    get_name(buf1,fkey,FKEY);
	    if(fkey[0]=='y'||fkey[0]=='Y')  {
	      sprintf(buf1,"execute sp_foreignkey %s,%s,%s\n",table_name,reftab,fldname);
	      fputs(buf1,sql_lp);
	      fputs("go\n",sql_lp);
	    }
	  }
	}
	else { printf("%s format fail\n",table_name);exit(0);}
}

void creat_uniindex(char *table_name)
{
	char buf1[NUM];
	char fldname[80],pkey[80],prim[200];

	sprintf(buf1,"if exists (select * from sysindexes where name='%s.%suniindex') \n",table_name,table_name);
	fputs(buf1,sql_lp);
	fputs("begin\n",sql_lp);
	sprintf(buf1,"  drop index %s.%suniindex\n",table_name,table_name);
	fputs(buf1,sql_lp);
	fputs("end\n",sql_lp);
	fputs("go\n",sql_lp);
	sprintf(prim,"create unique index %suniindex on %s(",table_name,table_name);
	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);
	if(buf1[0]=='#')  {