?? gendb
字號:
#!/usr/bin/perl -wuse strict;use FileHandle;my ($sTableSql, $fhTableSql);my ($sTableH, $fhTableH);my ($sTablePc, $fhTablePc);my ($sLine, $sSql, $sTable);my ($sCols, @aCols);my ($sPKCols, @aPKCols, $iPKColsCount);my ($ahColsRef, $iColsCount)=([], 0);my ($sColLine);my ($name, $type, $length, $default, $null); my ($sTmp, $ColRef, $i, $j);if(@ARGV != 3){ print "Usage: gendb {table_sql} {db_h} {table_pc}\n"; exit;}$sTableSql=shift(@ARGV);$sTableH=shift(@ARGV);$sTablePc=shift(@ARGV);$fhTableSql=new FileHandle($sTableSql) || die "File open fail";$sSql='';while($sLine=<$fhTableSql>){ chop($sLine); $sSql=$sSql." ".$sLine;}$fhTableSql->close();$sSql =~ s/\t/ /g;$sSql =~ s/^\s+(.*)\s+$/$1/;$sSql =~ s/\s{2,}/ /g;$sSql =~ s/(\(\s*\d+\s*),(\s*\d+\s*\))/$1.$2/g;$sSql = lc($sSql);#得到表名( $sTable ) = ( $sSql =~ m/create table (.+?)\b/ );#得到域和主鍵#( $sCols ) = ( $sSql =~ m/\((.*),(\s*)primary key(.*)\);/ );if( $sSql !~ m/\((.*),(\s*)primary key(.*)\);/ ){ die("Sql error");}$sCols = $1;$sCols =~ s/^\s*(.*)\s*$/$1/;@aCols = split(m/,/, $sCols);$iColsCount=0;foreach $sColLine (@aCols){ $sColLine =~ s/^\s+(.*)\s+/$1/g;# $sColLine =~ s/\s+$//g;# $sColLine =~ s/\s+\(//g; ( $name ) = ( $sColLine =~ m/(.+?)\s/ ); if($name =~ m/[()]/) { print "$sColLine\n"; die("Column name Error"); } ( $ahColsRef->[$iColsCount]{'name'} ) = $name; ( $sTmp ) = ( $sColLine =~ m/\s+(.*)/ ); if( $sTmp =~ m/(char|varchar2|number|date)\b/g ) { ( $ahColsRef->[$iColsCount]{'type'} ) = "$1"; } else { print "$sColLine\n"; die("Column type error"); } if( $ahColsRef->[$iColsCount]{'type'} ne 'date' ) { if( $sTmp =~ m/\((.*)\)/g ) { if( $1 !~ m/\s/ ) { $length=$1; } else { print "$sColLine\n"; die("Column type error"); } } else { print "$sColLine\n"; die("Column type error"); } } else { $length=0; } $ahColsRef->[$iColsCount]{'length'} = $length; if( $sTmp =~ m/\s+default\s+(\S+)/g ) { $ahColsRef->[$iColsCount]{'default'}="$1"; } elsif($ahColsRef->[$iColsCount]{'type'} eq 'char' || $ahColsRef->[$iColsCount]{'type'} eq 'varchar2') { $ahColsRef->[$iColsCount]{'default'}="' '"; } elsif($ahColsRef->[$iColsCount]{'type'} eq 'number') { $ahColsRef->[$iColsCount]{'default'}=0; } elsif($ahColsRef->[$iColsCount]{'type'} eq 'date') { $ahColsRef->[$iColsCount]{'default'}="'19700101'"; } else { die("Column type error"); } if( $sTmp =~ m/\s+not\s+null/g ) { $ahColsRef->[$iColsCount]{'null'} = 'N'; } else { $ahColsRef->[$iColsCount]{'null'} = 'Y'; }# print $ahColsRef->[$iColsCount]{'name'}." ". $ahColsRef->[$iColsCount]{'type'}." ". $ahColsRef->[$iColsCount]{'length'}." ". $ahColsRef->[$iColsCount]{'null'}."\n"; $iColsCount++; }( $sPKCols ) = ( $sSql =~ m/primary key\s*\((.+?)\)/ );$sPKCols =~ s/^\s+(.*)\s+$/&1/;$sPKCols =~ s/\s*,\s*/,/g;@aPKCols = split(m/,/, $sPKCols);$iPKColsCount = @aPKCols;#print "$iPKColsCount @aPKCols\n";#生成宿主結(jié)構(gòu)$fhTableH=new FileHandle("> ".$sTableH) || die "File open fail";print $fhTableH "typedef struct\n{\n";foreach $ColRef (@$ahColsRef){ if($ColRef->{'type'} eq 'char' || $ColRef->{'type'} eq 'varchar2') { print $fhTableH "\tchar\t\t\t".$ColRef->{'name'}. "[".($ColRef->{'length'}+1)."];\n"; } elsif($ColRef->{'type'} eq 'number') { print $fhTableH "\t"; if($ColRef->{'length'} !~ m/\./) { if($ColRef->{'length'} <= 6) { print $fhTableH "int"; } else { print $fhTableH "double"; } } else { print $fhTableH "double"; } print $fhTableH "\t\t\t".$ColRef->{'name'}.";\n"; } elsif($ColRef->{'type'} eq 'date') { print $fhTableH "\tchar"."\t\t\t".$ColRef->{'name'}."[15];\n"; } else { die("Column type error"); }}print $fhTableH "} ".$sTable."_t;\n";$fhTableH->close();#============================## 生成基本SQL函數(shù) ##============================#$fhTablePc=new FileHandle("> ".$sTablePc) || die "File open fail";print $fhTablePc "#include <stdio.h>\n";print $fhTablePc "#include <stdlib.h>\n";print $fhTablePc "EXEC SQL include sqlca;\n";print $fhTablePc "EXEC SQL include sqlda;\n\n";print $fhTablePc "#include \"const.h\"\n";print $fhTablePc "#include \"db.h\"\n\n";print $fhTablePc "#define MODULE_NAME \"".$sTable.".pc\"\n";print $fhTablePc "#define TABLE_NAME \"".$sTable."\"\n\n";print $fhTablePc "int Dbs".uc($sTable)."(int iFunc, ".print $fhTablePc " switch(iFunc)\n {\n";#----- INIT -----#print $fhTablePc " case DBS_INIT:\n";for($i=0; $i<$iColsCount; $i++){ $ColRef=$ahColsRef->[$i]; print $fhTablePc " "; if($ColRef->{'type'} eq 'char' || $ColRef->{'type'} eq 'varchar2' || $ColRef->{'type'} eq 'date') { $sTmp=$ColRef->{'default'}; $sTmp=~s/'/"/g; print $fhTablePc "strcpy(p_".$sTable."->".$ColRef->{'name'}.", ".$sTmp.")"; } elsif($ColRef->{'type'} eq 'number') { print $fhTablePc "p_".$sTable."->".$ColRef->{'name'}."=".$ColRef->{'default'}; } else { die("Column type error"); } print $fhTablePc ";"; print $fhTablePc "\n";}print $fhTablePc " return 0;\n\n";#----- SELECT -----#print $fhTablePc " case DBS_SELECT:\n";print $fhTablePc " EXEC SQL select\n";for($i=0; $i<$iColsCount; $i++){ $ColRef=$ahColsRef->[$i]; print $fhTablePc "\t\t\t"; if($ColRef->{'type'} eq 'varchar2' ) { print $fhTablePc "rtrim(".$ColRef->{'name'}.", ' ')"; } elsif($ColRef->{'type'} eq 'date') { print $fhTablePc "to_char(".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc $ColRef->{'name'}; } if($i < $iColsCount-1) { print $fhTablePc ","; } print $fhTablePc "\n";}print $fhTablePc " into\n";for($i=0; $i<$iColsCount; $i++){ $ColRef=$ahColsRef->[$i]; print $fhTablePc "\t\t\t:p_".$sTable."->".$ColRef->{'name'}; if($i < $iColsCount-1) { print $fhTablePc ","; } print $fhTablePc "\n";}print $fhTablePc "\t\tfrom ".$sTable."\n";print $fhTablePc "\t\twhere\n";for($i=0; $i<$iPKColsCount; $i++){ for($j=0; $j<$iColsCount; $j++) { last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'}); } $j==$iColsCount && die "Primary key error"; $ColRef=$ahColsRef->[$j]; print $fhTablePc "\t\t\t".$ColRef->{'name'}."="; if($ColRef->{'type'} eq 'varchar2') { print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')"; } elsif($ColRef->{'type'} eq 'date') { print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'}; } if($i < $iPKColsCount-1) { print $fhTablePc " and"; } print $fhTablePc "\n";}print $fhTablePc "\t\t\t;\n SQL_CODE_PROCESS(\"SEL\");\n\n";#----- SELECT with update -----#print $fhTablePc " case DBS_LOCK:\n";print $fhTablePc "\tEXEC SQL select\n";for($i=0; $i<$iColsCount; $i++){ $ColRef=$ahColsRef->[$i]; print $fhTablePc "\t\t\t"; if($ColRef->{'type'} eq 'varchar2' ) { print $fhTablePc "rtrim(".$ColRef->{'name'}.", ' ')"; } elsif($ColRef->{'type'} eq 'date') { print $fhTablePc "to_char(".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc $ColRef->{'name'}; } if($i < $iColsCount-1) { print $fhTablePc ","; } print $fhTablePc "\n";}print $fhTablePc " into\n";for($i=0; $i<$iColsCount; $i++){ $ColRef=$ahColsRef->[$i]; print $fhTablePc "\t\t\t:p_".$sTable."->".$ColRef->{'name'}; if($i < $iColsCount-1) { print $fhTablePc ","; } print $fhTablePc "\n";}print $fhTablePc "\t\tfrom ".$sTable."\n";print $fhTablePc "\t\twhere\n";for($i=0; $i<$iPKColsCount; $i++){ for($j=0; $j<$iColsCount; $j++) { last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'}); } $j==$iColsCount && die "Primary key error"; $ColRef=$ahColsRef->[$j]; print $fhTablePc "\t\t\t".$ColRef->{'name'}."="; if($ColRef->{'type'} eq 'varchar2') { print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')"; } elsif($ColRef->{'type'} eq 'date') { print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'}; } if($i < $iPKColsCount-1) { print $fhTablePc " and"; } print $fhTablePc "\n";}print $fhTablePc "\t\tfor update\n\t\t\t;\n";print $fhTablePc " SQL_CODE_PROCESS(\"LCK\");\n\n";#----- UPDATE -----#print $fhTablePc " case DBS_UPDATE:\n";print $fhTablePc "\tEXEC SQL update ".$sTable."\n";print $fhTablePc " set\n";for($i=0; $i<$iColsCount; $i++){ $ColRef=$ahColsRef->[$i]; print $fhTablePc "\t\t\t"; if($ColRef->{'type'} eq 'date') { print $fhTablePc $ColRef->{'name'}."=to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc $ColRef->{'name'}."=:p_".$sTable."->".$ColRef->{'name'}; } if($i < $iColsCount-1) { print $fhTablePc ","; } print $fhTablePc "\n";}print $fhTablePc "\t\twhere\n";for($i=0; $i<$iPKColsCount; $i++){ for($j=0; $j<$iColsCount; $j++) { last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'}); } $j==$iColsCount && die "Primary key error"; $ColRef=$ahColsRef->[$j]; print $fhTablePc "\t\t\t".$ColRef->{'name'}."="; if($ColRef->{'type'} eq 'varchar2') { print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')"; } elsif($ColRef->{'type'} eq 'date') { print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'}; } if($i < $iPKColsCount-1) { print $fhTablePc " and"; } print $fhTablePc "\n";}print $fhTablePc "\t\t\t;\n";print $fhTablePc " SQL_CODE_PROCESS(\"UPD\");\n\n";#----- DELETE -----#print $fhTablePc " case DBS_DELETE:\n";print $fhTablePc "\tEXEC SQL delete from ".$sTable."\n";print $fhTablePc "\t\twhere\n";for($i=0; $i<$iPKColsCount; $i++){ for($j=0; $j<$iColsCount; $j++) { last if($aPKCols[$i] eq $ahColsRef->[$j]{'name'}); } $j==$iColsCount && die "Primary key error"; $ColRef=$ahColsRef->[$j]; print $fhTablePc "\t\t\t".$ColRef->{'name'}."="; if($ColRef->{'type'} eq 'varchar2') { print $fhTablePc "rtrim(:p_".$sTable."->".$ColRef->{'name'}.", ' ')"; } elsif($ColRef->{'type'} eq 'date') { print $fhTablePc "to_date(:p_".$sTable."->".$ColRef->{'name'}.", 'YYYYMMDDHH24MISS')"; } else { print $fhTablePc ":p_".$sTable."->".$ColRef->{'name'}; } if($i < $iPKColsCount-1) { print $fhTablePc " and"; } print $fhTablePc "\n";}print $fhTablePc "\t\t\t;\n";print $fhTablePc " SQL_CODE_PROCESS(\"DEL\");\n\n";print $fhTablePc " default: return(543);\n";print $fhTablePc " }\n";print $fhTablePc "}\n";$fhTablePc->close();?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -