def parse_select(self, map, depth): ln=0 #DELETE FROM CSMARTBSER.FAILS_REPORTS WHERE cob_date = v_cob_dt AND sourcesystem =v_sourcesystem; m = re.findall(r'(SELECT(?P<select>(?:(?!SELECT|INTO).)*)(?P<into>INTO[\w\_\d\s]+)?FROM\s+(?P<from_>(?:(?!FROM|WHERE).)*)(?P<where>(?:(?!\;).)*)\;)',map, re.S) #sys.exit(1) if m: ln=len(m) pprint(m) #sys.exit(1) if ln>0: self._logger.debug('Got %d %s.' % (len(m), self._type)) if depth not in self._obj.keys(): self._obj[depth] = {} bid=0 for dml in m: #print dml (select, columns, into, from_, where)=dml #if into: # print select ,into,from_,where #if nif in block: #sys.exit(1) confirm (select in map,'Cannot find %s in map.' % (self._type)) pos = map.find(select) self._logger.debug('Confirmed %s at position %d.' % (self._type, pos )) map_id = self.get_map_id('SEL_STM',depth,bid) print map_id map = map[:pos] + map_id + map[(pos+len(select)):] selst= SQL_Select_Statement({'select_statement':select,'where': where, 'into':into,'from':from_,'control_statement':map},self._logger) selst.parse_src() self._obj[depth][map_id]=(pos,select,selst) bid +=1 else: self._logger.debug('No DELETEs found for depth %d.' % depth) #pprint(self._obj) return (ln,map)
def __init__(self, stub, logger): """Generic PL/SQL method """ PL_SQL_Block.__init__(self, stub, logger) self._name=self._stub['method_name'] self._method_block=None if self._stub.has_key('method_block'): self._method_block=self._stub['method_block'] self._type='PROCEDURE' confirm (self._type == self._stub['method_type'], 'It''s not a procedure.') self._params={} self._block_obj={} #block
def parse_src(self): """parse: statement""" self._logger.debug('Parsing %s.' % self._type) #print self._src m = re.match(r'(DELETE FROM (?P<schema_name>[\w\_\d]+)?\.?(?P<table_name>[\w\_\d]+)(?P<where>(?:(?!\;).)*)\;)',self._src,re.S) if m: stub=m.groupdict() pprint(stub) self._schema_name = stub['schema_name'] self._table_name = stub['table_name'] self._where = stub['where'] #print self._schema_name,self._table_name,self._where confirm(len(stub)>0, 'Cannot parse %s.' % self._type) confirm(stub.has_key('table_name'),'Table name is undefined.')
def parse_src(self, depth): """Parse: EXCEPTION section source""" self._depth=depth out_map=self._src ln=None if out_map: #print self._src m = re.findall(r'(WHEN(?P<ex_name>(?:(?!WHEN).)*)THEN(?P<ex_body>(?:(?!WHEN|\s+END\;).)*))',out_map, re.S) ln=len(m) bid=0 if depth not in self._obj.keys(): self._obj[depth] = {} for (ex_case,ex_name,ex_body) in m: ex_name=ex_name.strip().upper() #print b #sys.exit(1) #ex=_e #en= _e[1].strip() if self._el.has_key(ex_name): self._logger.error('Exception %s already processed.' % ex_name) else: confirm (ex_case in out_map,'Cannot find exception %s in %s.' % (ex_name, self._type)) pos = out_map.find(ex_case) self._logger.debug('Confirmed exception case %s at position %d.' % (ex_name,pos) ) map_id = self.get_map_id('EXC_CS',depth,bid) print map_id out_map = out_map[:pos] + map_id + out_map[(pos+len(ex_case)):] ex= PL_SQL_ExceptionCase({'exception_name':ex_name,'exception_body':ex_body},self._logger) ex.parse_src() self._el[ex_name] =ex self._obj[depth][map_id]=(pos,ex_case,ex) bid +=1 #sys.exit(1) if len(m)>0: self._logger.debug('Got %d exceptions.' % len(m)) else: self._logger.debug('Got 0 exceptions.') else: self._logger.debug('%s: %s does not exists for block.' % (self.__class__.__name__, self._type)) self._map_src=out_map return (ln,out_map)
def unnest_blocks(self,src): self._logger.debug('Unnesting blocks in %s.' % self._type) confirm(src, 'Src is not set.') block=self._src #check for sub-blocks bid=0 cnt=0 depth=self._depth (cnt,block) = self.unnest_block(block,depth) pprint(self._obj) #sys.exit(1) while cnt: bid +=1 #print block (cnt,block) = self.unnest_block(block,depth+bid) self._logger.debug('Done un-nesting.') #pprint(self._obj) #print(block) #sys.exit(1) return (cnt, block)
def unnest_if_cs(self, block, depth=0): ln=0 m = re.findall(r'(IF(?:(?!IF|THEN|ELSE).)*THEN(?:(?!END IF|THEN).)*END IF\;)',block, re.S) if m: ln=len(m) pprint(m) #sys.exit(1) if ln>0: self._logger.debug('Got %d nested IF statements.' % len(m)) self._obj[depth] = {} bid=0 for nif in m: print nif #if nif in block: confirm (nif in block,'Cannot find match nested IF found in %s.' % (self._type)) pos = block.find(nif) self._logger.debug('Confirmed nested block at position %d.' % pos ) map_id = self.get_map_id('I_F_CS',depth,bid) print map_id block = block[:pos] + map_id + block[(pos+len(nif)):] ifcs= PL_SQL_IF_Statement({'if_statement':nif,'anonymous_block':block},self._logger) ifcs.parse_src() #sys.exit(1) self.add_map((map_id,ifcs)) self._obj[depth][map_id]=(pos,nif,ifcs) #.append((pos,nif,ifcs)) print self._obj bid +=1 #else: #self._logger.debug('Cannot find match nested IF found in %s.' % (nif,self._type)) #sys.exit(1) #print block #sys.exit(1) else: self._logger.debug('No nested IFs found for depth %d.' % depth) return (ln,block)
def parse_delete_dml(self, map, depth): ln=0 #DELETE FROM CSMARTBSER.FAILS_REPORTS WHERE cob_date = v_cob_dt AND sourcesystem =v_sourcesystem; m = re.findall(r'(DELETE FROM(?:(?!DELETE FROM|WHERE).)*(WHERE(?:(?!WHERE|DELETE FROM|COMMIT|ROLLBACK).)*)?\;)\s+(COMMIT\;|ROLLBACK\;)?',map, re.S) #sys.exit(1) if m: ln=len(m) pprint(m) #sys.exit(1) if ln>0: self._logger.debug('Got %d %s.' % (len(m), self._type)) if depth not in self._obj.keys(): self._obj[depth] = {} bid=0 for dml in m: print dml (delete,where,trans)=dml if trans: print delete,where,trans #if nif in block: #sys.exit(1) confirm (delete in map,'Cannot find %s in map.' % (self._type)) pos = map.find(delete) self._logger.debug('Confirmed DELETE at position %d.' % pos ) map_id = self.get_map_id('DML_DEL',depth,bid) print map_id map = map[:pos] + map_id + map[(pos+len(delete)):] ifcs= SQL_Delete_Statement({'delete_statement':delete,'where': where, 'transaction':trans,'control_statement':map},self._logger) ifcs.parse_src() #sys.exit(1) #self.add_map((map_id,ifcs)) self._obj[depth][map_id]=(pos,delete,ifcs) #.append((pos,nif,ifcs)) #print self._obj bid +=1 else: self._logger.debug('No DELETEs found for depth %d.' % depth) pprint(self._obj) return (ln,map)
def parse_src(self): """parse: SQL statement""" self._logger.debug('Parsing %s.' % self._type) #print self._src #SELECT /*+ FIRST_ROWS(1) */ 1 INTO v_cob_flg FROM CSMARTBSER.FAILS_REPORTS_HISTORY #WHERE cob_date = v_cob_dt AND sourcesystem =v_sourcesystem AND rownum < 2; m = re.match(r'(?P<query>SELECT\s+(?P<hint>\/\*\+.*\*\/)?(?P<select>(?:(?!SELECT|INTO|\/\*\+).)*)(?P<into>INTO[\w\_\d\s]+)?FROM\s+(?P<schema_name>[\w\_\d]+)?\.?(?P<table_name>[\w\_\d]+)(?P<where>(?:(?!\;).)*)\;)',self._src,re.S) if m: stub=m.groupdict() pprint(stub) self._select = stub['select'] self._into = stub['into'].strip('INTO').strip() self._schema_name = stub['schema_name'] self._table_name = stub['table_name'] self._where = stub['where'] self._hint = stub['hint'] print self._schema_name,self._table_name,self._where self._map_src=stub['query'] confirm(len(stub)>0, 'Cannot parse %s.' % self._type) confirm(stub.has_key('into'),'Into is undefined.') confirm(stub.has_key('table_name'),'Table name is undefined.')
def add_map(self, args): (map_id, obj) = args confirm(map_id not in self._map, 'Object %s has already mapped.') self._map[map_id]=obj
def add_mvar(self, decl): """add maintenance variable""" (name, type, size, val, cmnts) = decl confirm(name not in self._const, 'Mvariable %s already defined.' % name) self._mvar[name]=(type, size, val, cmnts)