SHOW:
|
|
- or go back to the newest paste.
| 1 | #!/usr/bin/python | |
| 2 | #Author: Sam Varadarajan | |
| 3 | #Date: 08/14/2012 | |
| 4 | #Purpose: To extract SQLs from a DBTrace file created by PowerBuilder code. | |
| 5 | # This is the python version of the perl script I posted earlier. My attempt to learn Python! | |
| 6 | ||
| 7 | import getopt, sys | |
| 8 | import codecs | |
| 9 | import re | |
| 10 | ||
| 11 | def main(): | |
| 12 | try: | |
| 13 | opts, args = getopt.getopt(sys.argv[1:], "hf:", ["help", "file="]) | |
| 14 | except getopt.GetoptError, err: | |
| 15 | # print help information and exit: | |
| 16 | print str(err) # will print something like "option -a not recognized" | |
| 17 | usage() | |
| 18 | sys.exit(2) | |
| 19 | fname = None | |
| 20 | for o, a in opts: | |
| 21 | if o in ("-f", "--file"):
| |
| 22 | fname = a | |
| 23 | elif o in ("-h", "--help"):
| |
| 24 | usage() | |
| 25 | sys.exit() | |
| 26 | else: | |
| 27 | assert False, "unhandled option" | |
| 28 | # ... | |
| 29 | ||
| 30 | if (fname == None): | |
| 31 | usage() | |
| 32 | sys.exit(2) | |
| 33 | ||
| 34 | #flen = 0 | |
| 35 | #flen = file_len(fname) | |
| 36 | #print "file length= %d" % (flen, ) | |
| 37 | process_file(fname, 'SELECT,INSERT,DELETE,UPDATE') | |
| 38 | ||
| 39 | def usage(): | |
| 40 | print "\r\nUsage: pbsqls -f <filename>" | |
| 41 | print "\r\n\tWhere filename is the name of the trace log file from PB" | |
| 42 | print "\ttypically saved in Windows directory" | |
| 43 | print "\r\n\tE.g., pbsqls -f c:\\windows\\dbtrace.log > dbtrace.sql" | |
| 44 | ||
| 45 | ||
| 46 | def process_file(fname, oplist): | |
| 47 | path = fname | |
| 48 | SQLOP= () | |
| 49 | SQLOP = oplist.split(',') # SELECT/INSERT/UPDATE/DELETE
| |
| 50 | #print join('|', @SQLOP)
| |
| 51 | sql_block_found = 0 | |
| 52 | lines = [] | |
| 53 | fcount = 0 | |
| 54 | line = '' | |
| 55 | result = '' | |
| 56 | print "--" + path + "\r\n" | |
| 57 | ||
| 58 | #PERL: open LOGFILE, "<:encoding(UCS-2LE)", path or die "Can't open path !\n" | |
| 59 | # LOGFILE = codecs.open(path, encoding="UTF-16-LE") | |
| 60 | str = '' | |
| 61 | # LOGFILE = open(path) | |
| 62 | # while str in iter(LOGFILE): | |
| 63 | # with open(path) as LOGFILE: | |
| 64 | sql_block_found = 0 | |
| 65 | with codecs.open(path, encoding="UTF-16-LE") as LOGFILE: | |
| 66 | for str in LOGFILE.readlines(): | |
| 67 | str = str.rstrip('\r\n') #PERL chomp
| |
| 68 | #print str | |
| 69 | ||
| 70 | if re.search("PREPARE",str):
| |
| 71 | #print str | |
| 72 | sql_block_found = 1 | |
| 73 | continue | |
| 74 | elif ((sql_block_found == 2) and | |
| 75 | (re.search("GET AFFECTED ROWS:",str) or
| |
| 76 | re.search("BIND SELECT OUTPUT BUFFER",str) or
| |
| 77 | re.search("DESCRIBE",str) or
| |
| 78 | re.search("EXECUTE",str))):
| |
| 79 | #print str | |
| 80 | ||
| 81 | result = "\r\n".join(lines).upper() | |
| 82 | # Python equiv. of s// : f=re.sub(searchregx,replacement_str,line) | |
| 83 | #PERL result =~ s/FROM/\r\nFROM/ | |
| 84 | #result = re.sub(",", ",\r\n", result)
| |
| 85 | result = re.sub(" FROM", "\r\nFROM", result)
| |
| 86 | result = re.sub(" VALUES", "\r\nVALUES", result)
| |
| 87 | result = re.sub(" SET", "\r\nSET", result)
| |
| 88 | result = re.sub(" WHERE", "\r\nWHERE", result)
| |
| 89 | result = re.sub(" AND", "\r\nAND", result)
| |
| 90 | result = re.sub(" ORDER BY", "\r\nORDER BY", result)
| |
| 91 | result = re.sub(" GROUP BY", "\r\nGROUP BY", result)
| |
| 92 | #PERL REDTAG: how?? result =~ s/\([0-9.]+ MS \/ [0-9.]+ MS\)// | |
| 93 | result = re.sub("(\([0-9.]+ MS \/ [0-9.]+ MS\))", ";\r\n", result)
| |
| 94 | ||
| 95 | #PERL if ($result && $result !~ /FN_SYSDATE/ && $result !~ /NAV_/) | |
| 96 | if (not re.search("FN_SYSDATE",result)
| |
| 97 | and not re.search("NAV_", result)
| |
| 98 | and not re.match("^[ |\t]+;", result)):
| |
| 99 | print result + "\r\n\r\n" | |
| 100 | sql_block_found = 0 | |
| 101 | lines = [] | |
| 102 | fcount = 0 | |
| 103 | continue | |
| 104 | elif (re.match("^\(.*\):", str)):
| |
| 105 | #PERL $str =~ s/^\(.*\):[ ]*//; | |
| 106 | str = re.sub("^\(.*\):[ ]*", "", str)
| |
| 107 | #PERL OP: foreach op(@SQLOP): | |
| 108 | for op in SQLOP: | |
| 109 | p = re.compile("^"+ op, re.I)
| |
| 110 | if p.match(str): | |
| 111 | sql_block_found = 2 | |
| 112 | break | |
| 113 | ||
| 114 | if (sql_block_found == 2): | |
| 115 | #print str + "\n" | |
| 116 | lines.append(str) | |
| 117 | #PERL elsif (str =~ /\/\*[ ]*[0-9]{2}.*\*\//):
| |
| 118 | else: | |
| 119 | p = re.compile("\/\*[ ]*[0-9]{2}.*\*")
| |
| 120 | if p.match(str): | |
| 121 | #remove /* */ | |
| 122 | #PERL $str =~ s/\/\*[ ]*/-- / | |
| 123 | str = re.sub("\/\*[ ]*", "-- ", str)
| |
| 124 | #PERL $str =~ s/\*\/// | |
| 125 | str = re.sub("\*\/", "", str)
| |
| 126 | #PERL push(@lines, $str) | |
| 127 | lines.append(str) | |
| 128 | LOGFILE.close() | |
| 129 | ||
| 130 | ||
| 131 | def file_len(fname): | |
| 132 | with open(fname) as f: | |
| 133 | for i, l in enumerate(f): | |
| 134 | pass | |
| 135 | f.close | |
| 136 | return i + 1 | |
| 137 | ||
| 138 | ||
| 139 | if __name__ == "__main__": | |
| 140 | main() |
