sql2stru.sh

简单的银行代收系统

#!/bin/ksh 
# luojun，2006-8-15
# 从 sql 建库脚本生成 dbbuf struct 结构定义
# sql脚本格式：
# create table t_abc 
#  (                                         // 1、前 ( 在 create table 后面一行
#     tlr_no char(4) not null ,   {柜员号}   // 2、字段名与类型定义必须在一行
#     draw_box_flag char(1)       {领用标志} // 3、字段名与类型定义必须在一行 
#                   default '0' not null,    // 4、其他约束条件可以另起行
#     csh_attr      char(1)                  // 5、每个字段的定义最多两行
#           default '0' not null  {现金性质} // 6、注释可以在不同的行
#  ) ；                                      // 7、后 ) 必须另起一行
#  revoke all on "cbs20".t_clm_box_mgmt 
#    from "public";
#
#  create unique index 
#  "cbs20".idx_clm_box_mgmt on 
#  "cbs20".t_clm_box_mgmt
#  (inst_no,box_no);                         // 8、revoke、index等其他定义忽略
#                                            // 9、可处理一个文件多个表的定义




if [ $# -ne 2 ]
then
	echo "sql2stru.sh sql脚本文件 生成的头文件名称"
	exit 0
fi

SQL_FILE=$1
INCL_FILE=$2

gen_date=`date +"%Y-%m-%d %T"`

echo "/* 从文件 " $SQL_FILE " 生成开始 */ " > $INCL_FILE

echo "/* 生成时间：\c" >> $INCL_FILE
echo $gen_date "*/" >> $INCL_FILE

awk '
BEGIN {
	printf "/* 以下是根据sql脚本自动建立的, 如果有错误请检查建库脚本 */ \n";
#	printf "$include \"datetime.h\";\n\n";
#	printf "#ifndef DBBUF_H\n";
#	printf "#define DBBUF_H\n";
#	printf "#define  MAX_HIS_LEN  1000 /*历史备份字串最大长度*/\n\n";


	one_row = 0;
	n_lines = 0;
	n_tables = 0;
	in_flag = 0;
}
{
	n_lines = n_lines + 1;
	if ( tolower($1) == "create" )
	{
		if ( tolower($2) == "table" )
		{
			if ( in_flag != 0 )
			{
				printf " ERROR line %d \n",n_lines;
			}
			in_flag = 1;
			sub_idx = index($3,".") + 1;
			struct_name = tolower(substr($3,sub_idx));
			struct_name = "s" substr(struct_name,2);
			printf "$struct %s {\n",struct_name;
			n_tables = n_tables + 1;

			if ( $4 == "(" && in_flag == 1 )
				in_flag = 2;

		}
	}

	if ( $1 == "(" && in_flag == 1 )
		in_flag = 2;

	
	if ( one_row == 1 && in_flag == 2 )
	{
		if ( cmt_str == "" )
			printf "    %-8s%-25s\n",fld_type,fld_name ";";
		else
			printf "    %-8s%-25s/* %s */\n",fld_type,fld_name ";",cmt_str;

		if ( next_table == 1 )
		{
			printf "};\n\n"
			next_table = 0;
			in_flag = 0;
		}
		one_row = 0;
	}
	
	if ( NF > 0 && in_flag == 3 )
	{
		if ( substr($1,1,1) == ")" )
		{
			in_flag = 2;
			one_row = 1;
			next_table = 1;
		}
		else
		{
			test_str = $0;
			cmt = tolower($NF);

			if ( index(cmt,"{") > 0 )
			{
				cmt_str1 = substr(cmt,index(cmt,"{") + 1);
				cmt_str = substr(cmt_str1,1,index(cmt_str1,"}") - 1);
			}
			else
			{
				cmt_str = "";
			}

			if ( index (test_str , ",") == 0 )
			{
				in_flag = 3;
			}
			else
			{
				in_flag = 2;
			}
			one_row = 1;
		}
	}
	else	if ( NF > 1 && in_flag == 2 && tolower($1) != "create" )
	{
		fld_name = tolower($1);
		fldtype = tolower($2);

		test_str = $0;

		if ( tolower(substr(fldtype,1,4)) == "date" )
		{
			fld_type = "long";
		}

		if ( tolower(substr(fldtype,1,8)) == "datetime" )
		{
			fld_type = "dtime_t";
		}

		if ( tolower(substr(fldtype,1,7)) == "int" )
		{
			fld_type = "long";
		}

		if ( tolower(substr(fldtype,1,7)) == "integer" )
		{
			fld_type = "long";
		}

		if ( tolower(substr(fldtype,1,7)) == "serial" )
		{
			fld_type = "long";
		}

		if ( tolower(substr(fldtype,1,7)) == "decimal" )
		{
			fld_type = "double";
			test_str = substr($0,index($0,",") + 1);
		}

		if ( tolower(substr(fldtype,1,4)) == "char" || tolower(substr(fldtype,1,7)) == "varchar")
		{
			fld_type = "char";
			len = substr(tolower(fldtype),index(tolower(fldtype),"(") + 1,index(tolower(fldtype),")") - index(tolower(fldtype),"(") - 1);
#			if ( substr(fld_name,length(fld_name) - 2,3) == "tlr" && len != 6 )
#			{
#				printf "ERROR line %d TLR length is not 6\n",n_lines;
#			} 
#			else
#			if ( substr(fld_name,1,3) == "tlr" && len != 6 )
#			{
#				printf "ERROR line %d TLR length is not 6\n",n_lines;
#			}

			fld_name = fld_name "[" len "+1]";
		}

		cmt = tolower($NF);
		if ( index(cmt,"{") > 0 )
		{
			cmt_str1 = substr(cmt,index(cmt,"{") + 1);
			cmt_str = substr(cmt_str1,1,index(cmt_str1,"}") - 1);
		}
		else
		{
			cmt_str = "";
		}
		if ( index (test_str , ",") == 0 )
		{
			in_flag = 3;
		}
		else
		{
			one_row = 1;
		}
	}

}
END {
	if ( in_flag != 0 && one_row != 0 )
	{
		printf " ERROR line %d \n",n_lines;
	}
#	printf "#endif\n";
	printf "/* 以上是根据sql脚本自动建立的, 如果有错误请检查建库脚本 */ \n";
	printf "/* 一共处理 %d 行脚本，生成 %d 个结构 */\n",n_lines,n_tables;
#	printf "/* 自动生成完成 */\n";
}' $SQL_FILE >> $INCL_FILE

echo "/* 从文件 " $SQL_FILE " 生成结束 */ " >> $INCL_FILE

echo "EXEC SQL INCLUDE "$INCL_FILE " ;\n" > $INCL_FILE.ec

esql -e $INCL_FILE.ec

if [ $? -eq 0 ]
then
	echo "No Error!\n"
else
	echo "Error!\n"
fi