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)
