def scanEndOfSQLiteral(str): """ this function is called when FSM found a single quote which starts a single quoted literal. We are to return True, <port of literal> if the closing single quote is found within the input. Note that intermediate pairs of of single quotes may occur inside and even at the end of the second return value We are to return False, None if the closing single quote is NOT found within the input """ _dbx("input len:%d >>>%s" % (len(str), str)) ix = 0 part = "" endFound = False while ix < len(str) and not endFound: # print("dbx ix:%d ch:%s" % ( ix, str[ ix ] ) ) if str[ix] == "'": if str[ix:].find("''") == 0: # found two, at least # print( "found 2" ) part += "''" ix += 2 else: # ok only found one # print( "found 1" ) part += "'" ix += 1 endFound = True else: part += str[ix] ix += 1 if endFound: rv1, rv2 = True, part else: rv1, rv2 = False, None _dbx("rv1:%s, rv2>>%s" % (rv1, rv2)) return rv1, rv2
def getDdlScriptPath( dbName, object): # fixme: consider using os.path.join or os.path.sep """ compose full path name of DDL script. example: "/C/temp/LIONP01/SK_INVOICE_LSI-LIONP01.pkb" """ retVal = "%s\\%s\\%s-%s.%s" % (g_baseOfDDLScripts, dbName, object.name.upper(), dbName, object.fileExt) _dbx("retVal: %s" % retVal) return retVal
def action_devTest(jsonFile): if not os.path.exists(jsonFile): _errorExit("File %s does not seem to exist!" % (jsonFile)) jStr = open(jsonFile, "r").read() jData = json.loads(jStr) list = jData["filePaths"] filePaths = [] for elem in list: # .keys(): _dbx(filePaths) filePath = elem['path'] filePaths.append(filePath) return filePaths
def getEnvList(csv): """ convert CSV string in expected format to list of environment code ensuring that the code is valid """ retVal = [] for ix, envCode in enumerate(objectCsv.split(",")): _dbx("%d. env %s " % (ix, envCode)) if envCode not in g_mapDbNameOfEnvCode.keys(): _errorExit("envCode %s is not mapped!" % (envCode)) retVal.append(envCode) return retVal
def loadOraConnectionData( inputFilePath = None): """read connection data from json input file and return a list of dictionaires to the caller NO CONNECTIONs are actually opened to the database! Also password must be acquired by other means. """ if inputFilePath == None: moduleSelfDir = os.path.dirname( inspect.getfile(inspect.currentframe()) ) inputFilePath = os.path.join ( moduleSelfDir, './ora_connection_cfg.json' ) if not os.path.exists( inputFilePath ): _errorExit( "File %s does not seem to exist!" % ( inputFilePath ) ) conns = [] jData = json.load( open(inputFilePath, "r") ) _dbx( len( jData ) ) connRecs = jData[ "connectData" ] # __errorExit( "fixme: json structure chagned!" ) for connDict in connRecs : # .keys(): _dbx( connDict ) nickname= connDict[ 'nickname' ] host= connDict[ 'host' ] port= connDict[ 'port' ] service= connDict[ 'service' ] user= connDict[ 'user' ] conn = NicknamedOraConnection( nickname= nickname, host=host, port= port, service= service, username= user ) _dbx( str( conn ) ) conns.append( conn ) _dbx( len( conns ) ) return conns
def main(): global g_listIndexedBySchemaType, g_unknownFeature homeLocation = os.path.expanduser("~") _dbx(homeLocation) cmdLnConfig = parseCmdLine() setDebug(cmdLnConfig.debug) usedFeatureName = getGitCurrBranchName( ) if cmdLnConfig.featureName == g_unknownFeature else cmdLnConfig.featureName _infoTs("usedFeatureName: %s" % usedFeatureName) if cmdLnConfig.baseCommit: linesOfTouchedScripts = extractTouchedScripts( commitA=cmdLnConfig.baseCommit, commitB=cmdLnConfig.lastCommit) else: _infoTs("reading touched lines from stdin..") linesOfTouchedScripts = sys.stdin.readlines() if cmdLnConfig.action == "extract": _infoTs("scripts found: %s" % "\n".join(linesOfTouchedScripts)) elif cmdLnConfig.action == "make": sqlInstallTemplateFile = cmdLnConfig.sqlScriptTemplatePath if sqlInstallTemplateFile == None: moduleSelfDir = os.path.dirname( inspect.getfile(inspect.currentframe())) sqlInstallTemplateFile = os.path.join(moduleSelfDir, './install_template.sql') _infoTs("Will use following file as SQL install template: %s" % sqlInstallTemplateFile) fill_listIndexedBySchemaType( linesOfTouchedScripts=linesOfTouchedScripts) createSchemataInstallScripts( sqlScriptTemplatePath= sqlInstallTemplateFile \ , baseCommit= cmdLnConfig.baseCommit, lastCommit= cmdLnConfig.lastCommit \ , featureName= usedFeatureName, storeReleaseMetadata = cmdLnConfig.storeRelMeta \ , fileSufix= usedFeatureName \ ) if len(g_filesToExcludeFromInstall) > 0: _infoTs("Some files may have been excluded based on blacklist!") elif cmdLnConfig.action == "zip": zipFile = action_createFileTree(files=linesOfTouchedScripts, targetLocation=os.path.join( homeLocation, 'Downloads', usedFeatureName)) _infoTs("zip file can also be viewed at %s" % zipFile) _infoTs("Some files may have been excluded based on blacklist!") elif cmdLnConfig.action == "devTest": _dbx("got here") pass
def action_dbs(envCsv, objCsv): """ Extract DDL script for objects given by cmdArgs When we compare DDLs from 2 databases, the following additional task is performed: 1. compute the diff grade of the original DDLs 2. If the diff grade is zero or we generate the HTML diff report using the original DDLs 3. If the diff grade is high, we generate the HTML diff report using the formatted DDLs """ objectList = getObjectList(objCsv) # _errorExit( "test exit %s" % ( len( objectList ) ) ) envList = envCsv.split(",") if len(envList) > 2: raise ValueError( "diff report cannot be created for more than 2 databases. Consider action extract!" ) # regardless we if need to process 1 or 2 databases, we need to extract the scripts to the target location first action_extractScripts(objCsv=objCsv, envCsv=envCsv) for ix, env in enumerate(envList): if ix == 0: dbOneOriginPaths, dbOneFormattedPaths = CopyFilesForObjectListForEnv( envCode=env, objectList=objectList, staleMinutesOk=60) _dbx("dbOneOriginPaths len: %s" % (len(dbOneOriginPaths))) elif ix == 1: dbTwoOriginPaths, dbTwoFormattedPaths = CopyFilesForObjectListForEnv( envCode=env, objectList=objectList, staleMinutesOk=60) concatDiffReport = "\n" if len(envList) == 2: # _errorExit( "getHtmlDiffOutput method coded but not yet used! " ) for i in range(len(dbOneOriginPaths)): fileAOrigin = dbOneOriginPaths[i] fileBOrigin = dbTwoOriginPaths[i] lnCntA, lnCntB, newCnt, delOrChgCnt, diffGrade = getDiffStatsFromFiles( fileA=fileAOrigin, fileB=fileBOrigin) if diffGrade == 0 or diffGrade == 1: concatDiffReport += getHtmlDiffOutput(fileA=fileAOrigin, fileB=fileBOrigin) else: fileAFormatted = dbOneFormattedPaths[i] fileBFormatted = dbTwoFormattedPaths[i] concatDiffReport += getHtmlDiffOutput(fileA=fileAFormatted, fileB=fileBFormatted) _dbx(len(concatDiffReport)) diffRepFile = tempfile.mkstemp(suffix="-accu-diffs.html")[1] open(diffRepFile, "w").write(concatDiffReport) _infoTs("Diff report generated as %s " % (diffRepFile))
def getSenderInfo(infoJsonString): """ get sender server, port, username. password will be extracted from environment variable SENDER_SECRET """ infoDict = json.loads(infoJsonString) host = infoDict["host"] username = infoDict["username"] port = infoDict["port"] _dbx(host) senderSecret = os.environ["SENDER_SECRET"] senderInfo = SenderInfo(host=host, port=port, username=username, secret=senderSecret) return senderInfo
def extractTouchedScripts(commitA, commitB="HEAD"): """ extract scripts which have been modfified or added between 2 commits """ global g_filesToExcludeFromInstall args = ["git", "diff", "--name-only", commitA, commitB] outFh, tmpOutFile = tempfile.mkstemp() _dbx("using %s to capture output from git diff \nunix-style: %s" % (tmpOutFile, dosPath2Unix(tmpOutFile))) # outFh = open( tmpOutFile, "w" ) subprocess.run(args, stdout=outFh) # _errorExit("test exit") gitOutLines = open(tmpOutFile, "r").readlines() if len(gitOutLines) == 0: _errorExit("No lines found in git diff output file %s" % (tmpOutFile)) scriptsSet = set() for line in gitOutLines: if "we used git diff --name-status" == "but then there are issues with renames": # _dbx( line ) match = re.search("^([ADM])\s+(.*)$", line) if match == None: raise ValueError( "git diff returned line with unexpected content: %s" % line) else: staCode, script = match.groups(1)[0:2] #_dbx( staCode)# ; _dbx( script ) if staCode in "AM": scriptsSet.add(script) elif staCode == "D": scriptsSet.discard(script) else: doExclude = False for blackLine in g_filesToExcludeFromInstall: if line.strip() == blackLine.strip(): doExclude = True break if not doExclude: scriptsSet.add(line) _dbx(len(scriptsSet)) return list(scriptsSet)
def getGitCurrBranchName(): args = ["git", "branch"] outFh, tmpOutFile = tempfile.mkstemp() _dbx("using %s to capture output from git branch \nunix-style: %s" % (tmpOutFile, tmpOutFile)) # outFh = open( tmpOutFile, "w" ) subprocess.run(args, stdout=outFh) # _errorExit("test exit") gitOutLines = open(tmpOutFile, "r").readlines() if len(gitOutLines) == 0: _errorExit("No lines found in git branch output file %s" % (tmpOutFile)) branchName = None for line in gitOutLines: if line.startswith('*'): branchName = line.split()[1] return branchName
def genUnixDiff(oldPath, newPath, recursive=False): """Calls the unix diff command and returns its output to the calling function bomb out if any error was detected but only displayed upto 10 lines of the stderr """ diffCmdArgsUnix = ['diff', '-b', oldPath, newPath] if recursive: diffCmdArgsUnix.insert(1, '-r') # for a in diffCmdArgsUnix: _dbx( a ); _errorExit( 'test' ) proc = subprocess.Popen(diffCmdArgsUnix, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, universal_newlines=True) unixOutMsgs, errMsgs = proc.communicate() if len(errMsgs) > 0: # got error, return immediately _errorExit('got error from diff. Only first 10 lines are shown:\n%s ' % '\n'.join(errMsgs[0:10])) _dbx(len(unixOutMsgs)) return unixOutMsgs
def scanEndOfQNotationLiteral(q2and3, str): """ this function is called when FSM found a q notation start token, the literal can span many lines. We are to return True, <port of literal> if the closing single quote is found within the input. Note that intermediate pairs of of single quotes may occur inside and even at the end of the second return value We are to return False, None if the closing single quote is NOT found within the input """ qNotationCharPairs = {"[": "]", "{": "}", "'": "'", '"': '"', "#": "#"} _dbx("q2and3: %s >>>%s" % (q2and3, str)) q2Closer = qNotationCharPairs[q2and3[0]] q3Closer = qNotationCharPairs[q2and3[1]] q2and3Closer = q3Closer + q2Closer # indeed order must be reversed part = "" endFound = False foundAt = str.find(q2and3Closer) #_dbx ( "q2and3Closer: %s foundAt %d" % ( q2and3Closer, foundAt ) ) if foundAt >= 0: substrUpTo = foundAt + len(q2and3Closer) + 1 part += str[0:substrUpTo] _dbx("part: %s" % (part)) endFound = True if endFound: rv1, rv2 = True, part else: rv1, rv2 = False, None _dbx("rv1:%s, rv2>>%s" % (rv1, rv2)) return rv1, rv2
def parseCmdLine(): import argparse global g_inpFilePath, g_outFilePath, g_inpLines, g_fsmInitStatusCode parser = argparse.ArgumentParser() # lowercase shortkeys parser.add_argument('-i', '--inFile', help='input file, could also be sent as STDIN', required=False) parser.add_argument('-o', '--outFile', help='output file', required=False) parser.add_argument('-f', '--fsmStartStatus', help='finite machine start status', required=False) result = parser.parse_args() if result.inFile != None: g_inpFilePath = result.inFile g_inpLines = open(g_inpFilePath, "r").readlines() else: g_inpLines = sys.stdin.readlines() _dbx(len(g_inpLines)) # _dbx( "\n".join( g_inpLines[:3] ) ) if result.outFile != None: pass if result.fsmStartStatus != None: g_fsmInitStatusCode = result.fsmStartStatus else: g_fsmInitStatusCode = None return result
def CopyFilesForObjectListForEnv(envCode, objectList, staleMinutesOk=20): dbName = g_mapDbNameOfEnvCode[envCode] _dbx("db %s" % (dbName)) now = time.time() # returns seconds since epoch originFilePathsInDiffArea = [] formattedFilePaths = [] for obj in objectList: orginScriptPath = getDdlScriptPath(object=obj, dbName=dbName) fileModTime = os.path.getmtime(orginScriptPath) _dbx("now: %s mtime: %s" % (now, fileModTime)) elaMinutues = (now - fileModTime) / 60 _dbx("elaMinutues %s" % elaMinutues) if elaMinutues > staleMinutesOk: raise ValueError("file %s is %s minutes old!" % (orginScriptPath, elaMinutues)) if not os.path.exists(orginScriptPath): _infoTs("File %s does not seem to exist!" % (orginScriptPath)) else: prefix, fileExt = os.path.splitext( os.path.basename(orginScriptPath)) # lets also copy the original but leave a copy for users convenience newBaseName = prefix + '-orgF' + fileExt newPathOfOriginFile = os.path.join(g_diffLocation, newBaseName) shutil.copy(orginScriptPath, newPathOfOriginFile) _dbx("newPathOfOriginFile %s" % (newPathOfOriginFile)) originFilePathsInDiffArea.append(newPathOfOriginFile) # create formatted copy and MOVE it to diff area formattedOutPath = uglyFormat(inputFilePath=orginScriptPath) newBaseName = prefix + '-ugly' + fileExt newPathOfFormattedFile = os.path.join(g_diffLocation, newBaseName) shutil.move(formattedOutPath, newPathOfFormattedFile) _infoTs("Formatted file to be found as %s " % (newPathOfFormattedFile)) formattedFilePaths.append(newPathOfFormattedFile) # _errorExit( "originFilePathsInDiffArea len %s, formattedFilePaths len %s" % ( len( originFilePathsInDiffArea), len( formattedFilePaths) ) ) return originFilePathsInDiffArea, formattedFilePaths
def uglyFormat(inputFilePath): """ Read in lines of the input SQL file, format it with the simple/ugly formatter, * does some QA * dump the format result into a tempfile * return the temppath """ inputLines = open(inputFilePath, "r").readlines() _dbx("read %d lines from %s" % (len(inputLines), inputFilePath)) tree = fsm.plsqlTokenize(inputLines) formattedLines = tree.simpleFormatSemicolonAware() if True or "want to" == "QA": textWordCounter_a = charCounter.WordCounter(name="sql input", lines=inputLines, shortCode="sqlInput") textWordCounter_a.scan() wordCountResultLines_a = textWordCounter_a.report(printToStdout=False) forWordCountCheck_a = tempfile.mktemp() _dbx("forWordCountCheck_a: %s" % (forWordCountCheck_a)) open(forWordCountCheck_a, "w").write("\n".join(wordCountResultLines_a)) textWordCounter_b = charCounter.WordCounter(name="formatted result", lines=formattedLines, shortCode="sqlFormatted") textWordCounter_b.scan() wordCountResultLines_b = textWordCounter_b.report(printToStdout=False) forWordCountCheck_b = tempfile.mktemp() _dbx("forWordCountCheck_b: %s" % (forWordCountCheck_b)) open(forWordCountCheck_b, "w").write("\n".join(wordCountResultLines_b)) if "want see result of wordCount diff " == "which is barely usseful": _infoTs(" ************ DIFFing WordCounts ... ") diffWordCountResult = genUnixDiff(forWordCountCheck_a, forWordCountCheck_b) diffLinesToShow = 10 _infoTs( " ************ result of DIFFing WORD Counts, first %d lines only " % diffLinesToShow) print("\n".join( diffWordCountResult.split("\n")[0:diffLinesToShow])) inputFileBaseName = os.path.basename(inputFilePath) outPath = persistAndPrintName(textName="formatted %s" % inputFilePath, textContent=formattedLines, baseNamePrefix=inputFileBaseName + '-') return outPath
def action_createFileTree(files, targetLocation): """zip the given files: 1. if at least 1 file starts with root, find a common root of all. In worst case it is the root. For example /a/b/file1.txt and /a/foo/bar.py would have /a as common root 2. remove the common root from all The 2 files above become b/file1.txt foo/bar.py 3. put the files into the zip with the remaining relative paths """ if len(files) == 0: raise ValueError("list of files is empty") if files[0].startswith("/"): # if any file path starts with root, we strip off the common prefix commonRoot = os.path.commonprefix(files) _dbx("commonRoot %s" % commonRoot) pathsUsed = [ os.path.relpath(file, commonRoot).rstrip("\n") for file in files ] else: pathsUsed = [file.rstrip("\n") for file in files] _dbx("pathsUsed %s" % pathsUsed) zipArcPath = tempfile.mkstemp(suffix=".zip")[1] _dbx("zipArcPath type %s" % zipArcPath) with zipfile.ZipFile(zipArcPath, 'w') as zipWriter: for filePath in pathsUsed: if os.path.exists(filePath): zipWriter.write(filePath) else: _infoTs("File at path %s does NOT exist!" % filePath) # for more efficiency, unzip it to the target location with zipfile.ZipFile(zipArcPath, 'r') as zipReader: _infoTs("creating file tree in %s ... " % targetLocation) zipReader.extractall(path=targetLocation, members=None) # imples all members return zipArcPath
def getObjectList(objectCsv): """ convert CSV string in expected format to list of DBObject instances """ retVal = [] for ix, obj in enumerate(objectCsv.split(",")): _dbx("%d. obj %s " % (ix, obj)) tokens = obj.split(".") if len(tokens) != 3: _errorExit( "object %s does not conform to expected format of schema.name.type_code" % (obj)) (owner, name, typeCode) = tokens[:] _dbx("owner %s typ %s" % (obj, typeCode)) typeOfficial = oraUtils.g_mapFileExtDbmsMetaDataTypeToad[typeCode] _dbx("typeOfficial %s" % (typeOfficial)) retVal.append( oraUtils.DBObject(name=name, owner=owner, type=typeOfficial, fileExt=typeCode)) return retVal
def spoolScriptWithSqlplusDbmsMetadata ( spoolDestRoot, dirSep, dbObjects, connectQuadruple= None, connDataObj= None ): """ Use sqlplus connectQuadruple is colon(:) separated list of host, port, service, user """ if connectQuadruple == None and connDataObj == None: raise ValueError( "either connectQuadruple or connDataObj has to be provided") if connectQuadruple != None and connDataObj != None: raise ValueError( "either connectQuadruple or connDataObj has to be provided") if connectQuadruple != None: host, port, service, username = connectQuadruple.split( ":" ) elif connDataObj != None: host, port, service, username = connDataObj.host, connDataObj.part, connDataObj.service, connDataObj.username spoolScriptHeader = """ CONNECT {v_ez_connect} WHENEVER SQLERROR EXIT column db_name new_val db_name column spool_path_current new_val spool_path_current ALTER SESSION SET NLS_LANGUAGE=GERMAN ; set termout ON SELECT sys_context( 'userenv', 'db_name' ) AS db_name , user connect_as FROM dual ; """ # FIXME: we need to align the mapping of object type in the SQL script template and mapping defined in dict object! scriptBlockFor1Object = """ set termout OFF WITH prep_ AS ( SELECT UPPER( '{v_object_name}' ) || '-'||'&db_name' as obj_name_and_db_name , CASE upper('{v_object_type}') WHEN 'PACKAGE_BODY' THEN '.pkb' WHEN 'PACKAGE_SPEC' THEN '.pks' WHEN 'TRIGGER' THEN '.trg' WHEN 'TYPE_BODY' THEN '.tpb' WHEN 'TYPE_SPEC' THEN '.tps' WHEN 'FUNCTION' THEN '.fnc' WHEN 'PROCEDURE' THEN '.prc' WHEN 'VIEW' THEN '.vw' ELSE '.sql' END AS file_ext FROM DUAL ) SELECT '{spool_dest_root}'|| '{dir_sep}' || '&db_name' || '{dir_sep}' ||obj_name_and_db_name ||file_ext AS spool_path_current FROM prep_ ; set echo off feedback off head off linesize 1000 longchunksize 9999999 long 9999999 pagesize 49999 termout off trimspool on spool &spool_path_current SELECT dbms_metadata.get_ddl( upper('{v_object_type}'), upper( '{v_object_name}' ), upper( '{v_schema}' ) ) FROM DUAL ; spool off """ spoolScriptTrailer = """ EXIT """ password = getOraPassword ( oraUser= username, oraPasswordEnvVar= 'ORA_SECRET', batchMode= False ) ezConnect = """%s/"%s"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=%s)(PORT=%s)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=%s)))""" % ( username, password, host, port, service ) spoolPath = tempfile.mktemp() if "want to fight" == "the DOS vs gitbash vs unix platform gap": sqlTermoutPath = tempfile.mktemp() _dbx( "sqlTermoutPath %s" ) sqlpTermoutFh = open( sqlTermoutPath, "w" ) sqlpJob = subprocess.Popen( [ "sqlplus", "/nolog" ], stdin = subprocess.PIPE, stdout= sqlpTermoutFh ) sqlpJob.stdin.write( spoolScript.encode('utf-8') ) sqlpJob.communicate( ) sqlpTermoutFh = open( sqlTermoutPath, "r" ) _dbx( sqlpTermoutFh.readlines() ) else: # build one script block per DBObject scriptBlocks= [] for obj in dbObjects: scriptBlocks.append( scriptBlockFor1Object.format( spool_dest_root= spoolDestRoot, v_schema= obj.owner, v_object_type= obj.type, v_object_name= obj.name, dir_sep= dirSep ) ) _dbx( "len( scriptBlocks ) : %d " % len( scriptBlocks ) ) headerFormatted = spoolScriptHeader.format( v_ez_connect= ezConnect ) spoolScript = "%s \n%s \n%s" % ( headerFormatted, "\n".join( scriptBlocks), spoolScriptTrailer ) sqlplusScriptPath = tempfile.mktemp() + '.sql' _dbx( "sqlplusScriptPath: %s" % ( sqlplusScriptPath ) ) open( sqlplusScriptPath, "w").write( spoolScript ) return sqlplusScriptPath
def fetchOneScriptFromDatabase( objectSchema, objectType, objectName, sqlRunner ) : """Extract one DDL scripts from the given sqlRunner handle and return it as list of lines """ _dbx( type( sqlRunner ) ) extractorQuery = ' '.join( open( "./parameterized_extractor.sql", "r").readlines() ) _dbx( extractorQuery [ : 200] ) _dbx( len( extractorQuery ) ) if "need to " == "use all_objects": extractorQuery = extractorQuery.replace( '/*replace_start*/ dba_objects /*replace_end*/', ' all_objects ' ) _dbx( len( extractorQuery ) ) bindVar1 = ",".join( objectSchema ); _dbx( bindVar1 ) bindVar2 = ",".join( objectType ); _dbx( bindVar2 ) bindVar3 = ",".join( objectName ); _dbx( bindVar3 ) # _errorExit( "test" ) sqlRunner.execute( extractorQuery, [ bindVar1, bindVar2, bindVar3 ] ) dbName, scriptContent = sqlRunner.fetchone()[0] _dbx( type( scriptContent ) ) _dbx( len( scriptContent ) ) return dbName, scriptContent
sqlpJob.communicate( ) sqlpTermoutFh = open( sqlTermoutPath, "r" ) _dbx( sqlpTermoutFh.readlines() ) else: # build one script block per DBObject _infoTs( "The connecting user will need access to global temporary table %s !" % clobTempTable ) scriptBlocks= [] for obj in dbObjects: scriptBlocks.append( scriptBlockFor1Object.format( spool_dest_root= spoolDestRoot, lv_schema= obj.owner, lv_object_type= obj.type, lv_object_name= obj.name, dir_sep= dirSep, clobTempTable = clobTempTable ) ) _dbx( "len( scriptBlocks ) : %d " % len( scriptBlocks ) ) headerFormatted = spoolScriptHeader.format( v_ez_connect= ezConnect ) spoolScript = "%s \n%s \n%s" % ( headerFormatted, "\n".join( scriptBlocks), spoolScriptTrailer ) sqlplusScriptPath = tempfile.mktemp() + '.sql' _dbx( "sqlplusScriptPath: %s" % ( sqlplusScriptPath ) ) open( sqlplusScriptPath, "w").write( spoolScript ) return sqlplusScriptPath if __name__ == "__main__" : setDebug( True ) conns = loadOraConnectionData() _dbx( len( conns ) )
def plsqlTokenize(inpLines): lnCnt = len(inpLines) _dbx(lnCnt) lineNo = 0 nodeStack = TokenStack(name='plsqlTokenize') curTreeId = None tokBuf = "" interceptBufferLines = [] (interceptStartLineNo, interceptStartColNo) = (-1, -1) # just for clarity. First reference is when we hit block_comment_begin # match for alphanumString OR dblQuotedAlphanumString OR assignment OR singleEqual OR doubleEqual OR dotOperator # match macros # match block_comment_begin OR lineComment # match single quote, assignment operator # match comparison operators, named param operator # match arithmetric operators, left or right parenthesis, comma, semicolon # match Q notation begin in various flavours eng = re.compile( """^(\s*)(\$\$plsql_unit|\$\$plsql_line|q\{"|[\$#a-z0-9_]+|"[\$#a-z0-9_]+"|:=|>=|<=|>|<|!=|=>|=|/\*|--|\|\||\.\.|\.|%|\(|\)|\+|-|\*|/|,|;|@)(\s*)""", re.IGNORECASE) curSta = FsmState.start for line in inpLines[:29999]: colNo = 1 lineNo += 1 lnBuf = line eoLine = False if None != re.search('^(\s*)$', line): # match empty line pass # _dbx( 'Line %d is empty' % lineNo ) else: pass # _dbx( "line %d len: %d. Line content >>>>>>>>>%s" % ( lineNo, len( line ), line.rstrip("\n") ) ) i = 0 # do we need eoLine indeed or can we just bump colNo accordingly? while (i < 999 and colNo < len(line) and not eoLine ): # process line with safety belt against infinite loop i += 1 #_dbx( "Ln/col %d/%d curSta: '%s'" % ( lineNo, colNo, curSta ) ) if curSta == FsmState.find_block_comment_end: m = re.search('^(.*)(\*/)', lnBuf) # math end of block comment if m == None: #_dbx( "need to cache block comment" ) interceptBufferLines.append(lnBuf) eoLine = True continue else: # found end of block comment interceptBufferLines.append(m.group(1) + m.group(2)) _dbx("group1>>%s, group2>>%s, lnBuf>>>>%s" % (m.group(1), m.group(2), lnBuf)) lenUptoStarSlash = len(m.group(1)) + len(m.group(2)) _dbx("lenUptoStarSlash:%d" % (lenUptoStarSlash)) colNo += lenUptoStarSlash _dbx("found block comment end at col %d" % colNo) lnBuf = lnBuf[lenUptoStarSlash:] _dbx("stuff at comment is >>>%s" % (lnBuf.rstrip("\n"))) curSta = FsmState.start node = TokenNode(text="".join(interceptBufferLines), type=TokenType.block_comment_begin, staAtCreation=curSta, lineNo=interceptStartLineNo, colNo=interceptStartColNo, parentId=curTreeId) nodeStack.push(node) continue # while not EOL elif curSta == FsmState.in_single_quoted_literal: #_dbx( "scanning for end single quote in >>> %s " % lnBuf ) endOfLitFound, partOfLit = scanEndOfSQLiteral(lnBuf) if not endOfLitFound: # line break is part of string literal interceptBufferLines.append(lnBuf) eoLine = True # line is done else: # found end of literal in line, possibly with rest not belonging to literal curSta = FsmState.start interceptBufferLines.append(partOfLit) literalText = "".join(interceptBufferLines) node = TokenNode( text=literalText, type=TokenType.single_quoted_literal_begin, staAtCreation=curSta, lineNo=interceptStartLineNo, colNo=interceptStartColNo, parentId=curTreeId) nodeStack.push(node) colNo += len(partOfLit) lnBuf = line[colNo - 1:] #_dbx( "lnBuf>>>%s" % lnBuf ) continue elif curSta == FsmState.in_q_notation_begin: #_dbx( "scanning for end q notation literal in >>> %s " % lnBuf ) endOfLitFound, partOfLit = scanEndOfQNotationLiteral( q2and3, lnBuf) if not endOfLitFound: # line break is part of string literal interceptBufferLines.append(lnBuf) eoLine = True # line is done else: # found end of literal in line, possibly with rest not belonging to literal curSta = FsmState.start interceptBufferLines.append(partOfLit) literalText = "".join(interceptBufferLines) node = TokenNode( text=literalText, type=TokenType.single_quoted_literal_begin, staAtCreation=curSta, lineNo=interceptStartLineNo, colNo=interceptStartColNo, parentId=curTreeId) nodeStack.push(node) colNo += len(partOfLit) lnBuf = line[colNo - 1:] # _dbx( "lnBuf>>>%s" % lnBuf ) continue m = re.search('^(\s*)$', lnBuf) # match empty line if m != None: eoLine = True if eoLine: continue # process other types of token m = eng.match(lnBuf) # _dbx( type( m ) ) #_dbx( 'lnBuf being parsed >>>>>> %s' % lnBuf.rstrip("\n") ) if m == None: # the special scan for single quoted literal is no longer needed since we can use the triple single quotes! m = re.search("^(\s*)(')", lnBuf) # match single quote if m != None: # found single quote # stateStack.push( curSta, curTreeId ) curSta = FsmState.in_single_quoted_literal interceptBufferLines = [] (interceptStartLineNo, interceptStartColNo) = (lineNo, colNo) interceptBufferLines.append(m.group(2)) colNo += len(m.group(1) + m.group(2)) lnBuf = line[colNo - 1:] #_dbx( colNo ) continue # we must skip the fine-grained FSM else: _infoTs( "Rest of line %d could not be tokenized. Line content follows \n%s" % (lineNo, lnBuf)) return nodeStack else: # second re group i.e. token tok = m.group(2) # third re group i.e. optional whitespaces #if len( m.group(3) ) > 0: # found trailing whitespaces colNo += len(m.group(1)) + len(m.group(2)) + len(m.group(3)) # _dbx( "colNo: %d" % colNo ) #_dbx( "Ln/col %d/%d raw tok: '%s'" % ( lineNo, colNo, tok ) ) lnBuf = line[colNo - 1:] # _dbx( "rest of line: %s" % lnBuf.rstrip("\n") ) tokTyp, normed = gettokentype(tok) #_dbx( "tokTyp: %s normed: '%s'" % ( tokTyp, normed ) ) if tokTyp == TokenType.block_comment_begin: if curSta == FsmState.find_block_comment_end: _errorExit("Encountered tokTyp %s while in state %s!" % (tokTyp, curSta)) else: # found block_comment if the middle of somewhere, switch the parser to specifically search for end of comment curSta = FsmState.find_block_comment_end interceptBufferLines = [] (interceptStartLineNo, interceptStartColNo) = (lineNo, colNo) interceptBufferLines.append(tok) #_dbx( "we must skip the fine-grained FSM ") continue # we must skip the fine-grained FSM elif tokTyp == TokenType.single_line_comment_begin: # found double minus #_dbx( foo ) if curSta == FsmState.find_block_comment_end: _errorExit("Encountered tokTyp %s while in state %s!" % (tokTyp, curSta)) else: # not in wrong status, just push line comment node, no change of state node = TokenNode( text=normed + lnBuf.rstrip("\n"), type=TokenType.single_line_comment_begin, staAtCreation=curSta, lineNo=lineNo, colNo=colNo - len(normed), parentId=curTreeId) nodeStack.push(node) eoLine = True continue elif tokTyp == TokenType.q_notation_begin: #_dbx( foo ) if curSta == FsmState.in_q_notation_begin: _errorExit("Encountered tokTyp %s while in state %s!" % (tokTyp, curSta)) else: # not in wrong status, just push line comment node, no change of state # stateStack.push( curSta, curTreeId ) q2and3 = normed[ 1: 3] # should be the open bracket and single or double quote, in any order _dbx("normed>>>%s lnBuf>>> %s" % (normed, lnBuf)) curSta = FsmState.in_q_notation_begin interceptBufferLines = [] (interceptStartLineNo, interceptStartColNo) = (lineNo, colNo) interceptBufferLines.append(m.group(1) + m.group(2)) continue # we must skip the fine-grained FSM else: pass #_dbx( "lineNo/colNo: %d/%d lnBuf >>>%s" % ( lineNo, colNo, lnBuf )) # # node = TokenNode(text=normed, type=tokTyp, staAtCreation=None, lineNo=lineNo, colNo=colNo - len(normed), parentId=curTreeId) nodeStack.push(node) return nodeStack # plsqlTokenize
text = message.as_string() # Log in to server using secure context and send email context = ssl.create_default_context() with smtplib.SMTP_SSL(senderInfo.host, senderInfo.port, context=context) as server: server.login(senderInfo.username, senderInfo.secret) server.sendmail(senderInfo.username, recipients, text) if __name__ == "__main__": argConfig = parseCmdLine() senderInfoJson = "".join(open(argConfig.senderInfoFile, "r").readlines()) _dbx(senderInfoJson) senderInfo = getSenderInfo(infoJsonString=senderInfoJson) sendMail(senderInfo= senderInfo, recipients= argConfig.recipients \ , subject= argConfig.subject, plainText = argConfig.messagePlainText \ , htmlFile = argConfig.htmlTextFile, binAttachFile = argConfig.attachmentFile ) if "want to " == "create json sender info ": testDict = { 'host': 'messenger', 'port': "123", 'username': '******', 'secret': "100" } print(json.dumps(testDict))
def fsmMain(preTokStack, startStatus=FsmState.start): """ make ASSUMPTION that comments tokens are in a different stack and in the main stack we only have non-comment tokens. when later on significant tokens have been linked properpy we will have another pass to insert the comment tokens based on lineNo/ColNo """ retTokStack = TokenStack(name="fsmMain") stateStack = StateStack(name="main_state") preTokStackLen = len(preTokStack.arr) curTreeId = None curSta = startStatus thenComesFromStack = StateStack(name="thenComesFrom") while preTokStack.peekAhead() != None: curTokens = preTokStack.popComplexAware() tokId, normed, tokTyp = (curTokens[0].id, curTokens[0].text, curTokens[0].type) # lineNo, colNo = (curTokens[0].lineNo, curTokens[0].colNo ) _dbx("curSta %s curTokens len %d, 1st id:%s type:%s >>>%s" % (curSta, len(curTokens), tokId, tokTyp, normed)) if curSta == FsmState.start: if tokTyp == TokenType.relevant_keyword and normed == "CREATE": stateStack.push(curSta, curTreeId) newSta = FsmState.in_compilation_unit_header curTokens[0].state = staAtCreation = newSta newTreeId = curTokens[0].id else: _errorExit("Unknown token id %s type %s in state %s " % (tokId, tokTyp, curSta)) elif curSta == FsmState.in_compilation_unit_header: if tokTyp == TokenType.relevant_keyword and normed == "AS": _dbx(foo) newSta = FsmState.in_declaration curTokens[0].state = staAtCreation = newSta newTreeId = curTokens[0].id elif tokTyp == TokenType.semicolon: # forward declaration of function/procedure _dbx(foo) newSta, newTreeId = stateStack.pop() else: _dbx("other token type %s in state %s " % (tokTyp, curSta)) elif curSta == FsmState.in_declaration: if tokTyp == TokenType.relevant_keyword and normed in ["BEGIN"]: _dbx(foo) newSta = FsmState.in_body newTreeId = curTokens[0].id else: _dbx("other token type %s in state %s " % (tokTyp, curSta)) newSta = FsmState.started_declaration_entry newTreeId = curTokens[0].id elif curSta == FsmState.started_declaration_entry: if tokTyp == TokenType.semicolon: _dbx(foo) newSta = FsmState.in_declaration else: _dbx("other token type %s in state %s " % (tokTyp, curSta)) elif curSta == FsmState.in_body: if tokTyp in [TokenType.aggEndIdentSemic, TokenType.aggEndSemic]: _dbx(foo) newSta, newTreeId = stateStack.pop() elif normed == "BEGIN": _dbx(foo) newSta = FsmState.in_body newTreeId = curTokens[0].id stateStack.push(curSta, curTreeId) elif normed in ['IF', 'WHILE']: newSta = FsmState.expect_bool_expression newTreeId = curTokens[0].id stateStack.push(curSta, curTreeId) if normed == "IF": thenComesFromStack.push(FsmState.if_or_case_statement_open, None) elif normed == "CASE" and preTokStack.peekAhead().text == "WHEN": _dbx(foo) for nextTok in preTokStack.popComplexAware(): curTokens.append(nextTok) newSta = FsmState.expect_bool_expression newTreeId = curTokens[0].id stateStack.push(curSta, curTreeId) thenComesFromStack.push(FsmState.if_or_case_statement_open, None) elif normed == "CASE" and preTokStack.peekAhead().text != "WHEN": # here we must not pop the peeked token, it must go thru normal FSM newSta = FsmState.expect_expression newTreeId = curTokens[0].id #no pop expected! stateStack.push( curSta, curTreeId) thenComesFromStack.push(FsmState.if_or_case_statement_open) elif normed in ['DECLARE']: newSta = FsmState.in_declaration newTreeId = curTokens[0].id stateStack.push(curSta, curTreeId) else: _dbx("other token type %s in state %s " % (tokTyp, curSta)) stateStack.push(curSta, curTreeId) newSta = FsmState.expect_expression newTreeId = curTokens[0].id elif curSta in [FsmState.expect_expression]: if tokTyp in [ TokenType.semicolon, TokenType.aggEndSemic, TokenType.aggEndIfSemic, TokenType.aggEndCaseSemic, TokenType.aggEndLoopSemic ]: _dbx(foo) newSta, newTreeId = stateStack.pop() elif normed in ['THEN']: # this is for "CASE WHEN .. THEN .." _dbx(foo) peekThenComesFrom = thenComesFromStack.peek()[ 0] # we dont care about the parentId if peekThenComesFrom == FsmState.case_bool_expression_open: newSta = FsmState.in_body newTreeId = curTokens[0].id else: _errorExit( "Found THEN at %s without opening CASE token in thenComesFromStack" % tokId) thenComesFromStack.pop() # ignore return values elif normed == "ELSE": _dbx(foo) newSta = FsmState.in_body newTreeId = curTokens[0].id elif normed == "CASE" and preTokStack.peekAhead().text == "WHEN": _dbx(foo) for nextTok in preTokStack.popComplexAware(): curTokens.append(nextTok) newSta = FsmState.expect_bool_expression newTreeId = curTokens[0].id #do not expect pop! stateStack.push( curSta, curTreeId) thenComesFromStack.push(FsmState.case_bool_expression_open, None) elif normed == "CASE" and preTokStack.peekAhead().text != "WHEN": _dbx(foo) # here we must not pop the peeked token, it must go thru normal FSM newSta = FsmState.expect_expression newTreeId = curTokens[0].id # stateStack.push( curSta, curTreeId) thenComesFromStack.push(FsmState.case_bool_expression_open, None) elif normed in ['LOOP']: # this is for "FOR rec IN ( select * from xyz ) LOOP or similar constructs" newSta = FsmState.in_body elif tokTyp == TokenType.left_bracket: _dbx(foo) stateStack.push(curSta, curTreeId) newTreeId = curTokens[0].id elif tokTyp == TokenType.right_bracket: newSta, newTreeId = stateStack.pop() else: _dbx("other token type %s in state %s " % (tokTyp, curSta)) elif curSta in [FsmState.expect_bool_expression]: _dbx(foo) if tokTyp in [TokenType.aggEndSemic ]: # this is for "CASE ... END;" _dbx(foo) newSta, newTreeId = stateStack.pop() elif normed in [ 'THEN' ]: # this is for "IF x THEN .. ELSE " or "WHILE y LOOP" or "CASE WHEN .. THEN .." _dbx(foo) peekThenComesFrom = thenComesFromStack.peek()[ 0] # we dont care about the parentId if peekThenComesFrom == FsmState.if_or_case_statement_open: newSta = FsmState.in_body newTreeId = curTokens[0].id elif peekThenComesFrom == FsmState.case_bool_expression_open: newSta = FsmState.expect_expression newTreeId = curTokens[0].id else: _errorExit("No matching OPENER for THEN at %s" % tokId) thenComesFromStack.pop() # ignore return values elif normed in [ 'ELSE', 'ELSIF', 'LOOP' ]: # this is for "IF x THEN .. ELSE " or "WHILE y LOOP" or "CASE WHEN .. THEN .." _dbx(foo) newSta = FsmState.in_body newTreeId = curTokens[0].id elif tokTyp == TokenType.left_bracket: _dbx(foo) stateStack.push(curSta, curTreeId) newTreeId = curTokens[0].id elif tokTyp == TokenType.right_bracket: newSta, newTreeId = stateStack.pop() elif normed == "CASE" and preTokStack.peekAhead().text == "WHEN": for nextTok in preTokStack.popComplexAware(): curTokens.append(nextTok) newSta = FsmState.expect_bool_expression newTreeId = curTokens[0].id stateStack.push(curSta, curTreeId) elif normed == "CASE" and preTokStack.peekAhead().text != "WHEN": # here we must not pop the peeked token, it must go thru normal FSM newSta = FsmState.expect_expression newTreeId = curTokens[0].id stateStack.push(curSta, curTreeId) else: _errorExit("No handler for state %s with input %s " % (curSta, tokTyp)) for ix, curTok in enumerate(curTokens): # _dbx( "ix: %d type %s" % (ix, type( curTok)) ) newNode = TokenNode(text=curTok.text, type=curTok.type, staAtCreation=curSta, lineNo=curTok.lineNo, colNo=curTok.colNo, parentId=curTreeId) retTokStack.push(newNode) #_dbx( "ret stack len %d" % (len( retTokStack.arr ) ) ) _dbx("cur sta %s new sta %s" % (curSta, newSta)) curSta, curTreeId = newSta, newTreeId _infoTs("final sta was %s" % (newSta)) if preTokStackLen != len(retTokStack.arr): _errorExit( "OOPPS preTokStackLen is %d and len( retTokStack.arr ) is %d" % (preTokStackLen, len(retTokStack.arr))) return retTokStack # # editing mark # return retTokStack
def createSchemataInstallScripts(sqlScriptTemplatePath, baseCommit, lastCommit, featureName=None, fileSufix=None, storeReleaseMetadata=True): """ Create SQL and BAT install scripts for the schemata with deployable scripts Deployable scripts are: 1) file is not at top level of the schema and 2) extension is not blacklisted """ global g_internalSepator, g_listIndexedBySchemaType, g_schemataFound insertSqlStmtTemplate = """ -------------------------------------------------------------------------------- -- Store software release information: at this position we also record attempted -- deployment -------------------------------------------------------------------------------- DECLARE lv_rel_id NUMBER; BEGIN SELECT basic_data.APPL_RELEASE_SQ.nextval INTO lv_rel_id FROM dual; INSERT INTO basic_data.t_applied_releases( id, release_no, creation_dt ) VALUES( lv_rel_id, q'[{featureName}]', sysdate ); INSERT INTO basic_data.t_applied_files( id, release_id, filename ) SELECT appl_files_sq.nextval, lv_rel_id, q'[{basenameSqlScript}, git-branch: {featureName}, baseline-commit:{baselineCommit}, last-commit:{lastCommit}]' FROM dual; COMMIT; END; / """ suffixUsed = "-" + fileSufix if fileSufix else "" sentinelPatPrefix = "REM place_here_scripts_for:" fh = open(sqlScriptTemplatePath, mode="r") inpTemplateLines = fh.readlines() _dbx("got %d lines from template" % (len(inpTemplateLines))) scriptTemplateText = "".join(inpTemplateLines) tmpDir = tempfile.mkdtemp() _infoTs("install scripts will be placed under %s" % (tmpDir)) batchScriptTemplate = """ SET NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 SQLPLUS /nolog @{sqlScriptBaseName} """ readmeContentHeader = """ Order to run install scripts: """ readmeContentFooter = """ All processes in groups xxx, yyy must be stopped """ batchScripts = [] for schema in g_schemataFound: _dbx("schema %s\n" % (schema)) script4Schema = scriptTemplateText script4Schema = script4Schema.replace("<TARGET_SCHEMA>", schema) for schemaType in g_listIndexedBySchemaType.keys(): if schemaType.startswith(schema): typeOnly = schemaType.split(g_internalSepator)[1] if typeOnly.upper() not in [ '.SQL' ]: # dirty fix to filter out top-level sql script sentinelPattern = "%s%s" % (sentinelPatPrefix, typeOnly.upper()) _dbx("schemaType %s, sentinel %s" % (schemaType, sentinelPattern)) listOfScripts = g_listIndexedBySchemaType[schemaType] _dbx("cnt scripts %s" % (len(listOfScripts))) # aggregate scripts of schemaType into one string stringToAppend = "\n".join(listOfScripts) _dbx(stringToAppend) found = script4Schema.find(sentinelPattern) if found > 0: _dbx("found pattern") script4Schema = script4Schema.replace( sentinelPattern, "\n%s\n%s" % (sentinelPattern, stringToAppend)) else: _errorExit("Sentinel '%s' not found in template!" % (sentinelPattern)) # , isWarning = True # print( script4Schema ) # now remove the sentinel's tempScript = script4Schema newLines = [] for line in tempScript.split("\n"): if not line.startswith(sentinelPatPrefix): newLines.append(line) script4Schema = "\n".join(newLines) schemaDir = os.path.join(tmpDir, schema) os.mkdir(schemaDir) basenameSqlScript = "install_%s%s.sql" % (schema, suffixUsed) _dbx(basenameSqlScript) scriptPathSql = os.path.join(schemaDir, basenameSqlScript) if storeReleaseMetadata: # for INSERT of applied release information insertSqlStmt = insertSqlStmtTemplate.format( featureName= featureName \ , baselineCommit= baseCommit, lastCommit= lastCommit, basenameSqlScript= basenameSqlScript ) _dbx(insertSqlStmt) script4Schema = script4Schema.format( placeHolderStoreReleaseMetadata=insertSqlStmt, baselineCommit=baseCommit, featureName=featureName) else: script4Schema = script4Schema.format( placeHolderStoreReleaseMetadata="", baselineCommit=baseCommit, featureName=featureName) batScriptBaseName = "install_%s%s.bat" % (schema, suffixUsed) scriptPathBat = os.path.join(schemaDir, batScriptBaseName) fh = open(scriptPathSql, mode="w") fh.write(script4Schema) fh.close() sqlScriptBaseName = os.path.basename(scriptPathSql) batchScriptContent = batchScriptTemplate.format( sqlScriptBaseName=sqlScriptBaseName) fh = open(scriptPathBat, mode="w") fh.write(batchScriptContent) fh.close() _infoTs("output SQL script unix-style >>>>> %s \nDOS style >>>: %s" % (dosPath2Unix(scriptPathSql), scriptPathSql)) _infoTs("output BAT script unix-style >>>>> %s \nDOS style >>>: %s" % (dosPath2Unix(scriptPathBat), scriptPathBat)) #_errorExit( "in test - stopped after 1 schema!! " ); batchScripts.append(os.path.basename(scriptPathBat)) # create install readme readmeFile = os.path.join(tmpDir, "install%s-readme.txt" % (suffixUsed)) items = [] for batchScript in batchScripts: items.append("?. %s" % (batchScript)) _dbx(batchScript) itemsText = "\n".join(items) readmeText = "%s\n%s\n%s\n" % (readmeContentHeader, itemsText, readmeContentFooter) fh = open(readmeFile, mode="w") fh.write(readmeText) fh.close() _infoTs("readme file unix-style >>>>> %s \nDOS style >>>: %s" % (dosPath2Unix(readmeFile), readmeFile))
def main(): global g_fsmInitStatusCode argParserResult = parseCmdLine() if True: tree = fsm.plsqlTokenize(g_inpLines) formattedLines = tree.simpleFormatSemicolonAware() # print( "\n".join( formattedLines ) ) if False or "want to" == "compare output manually": #print( "*"*20 + "input sql" + "*"*20 ) #print( "".join( g_inpLines)) print("*" * 20 + "formatted" + "*" * 20) print("\n".join(formattedLines)) if "want to compare" == "char count": forCharCountCheck_A = tempfile.mktemp() _dbx("forCharCountCheck_A: %s" % (forCharCountCheck_A)) charCounter_A = charCounter.TextCharStatsIgnoreCase( textName="sql input", txt=g_inpLines) charCountResultLines_A = charCounter_A.report(printToStdout=False) open(forCharCountCheck_A, "w").write("\n".join(charCountResultLines_A)) forCharCountCheck_B = tempfile.mktemp() _dbx("forCharCountCheck_B: %s" % (forCharCountCheck_B)) charCounter_B = charCounter.TextCharStatsIgnoreCase( textName="formatted output", txt=formattedLines) charCountResultLines_B = charCounter_B.report(printToStdout=False) open(forCharCountCheck_B, "w").write("\n".join(charCountResultLines_B)) _infoTs(" ************ DIFFing CharCounts ... ") diffCharCountResult = genUnixDiff(forCharCountCheck_A, forCharCountCheck_B) _infoTs(" ************ result of DIFFing CharCounts") print(diffCharCountResult) if True: textWordCounter_a = charCounter.WordCounter(name="sql input", lines=g_inpLines, shortCode="sqlInput") textWordCounter_a.scan() wordCountResultLines_a = textWordCounter_a.report(printToStdout=False) forWordCountCheck_a = tempfile.mktemp() _dbx("forWordCountCheck_a: %s" % (forWordCountCheck_a)) open(forWordCountCheck_a, "w").write("\n".join(wordCountResultLines_a)) textWordCounter_b = charCounter.WordCounter(name="sql input", lines=formattedLines, shortCode="sqlInput") textWordCounter_b.scan() wordCountResultLines_b = textWordCounter_b.report(printToStdout=False) forWordCountCheck_b = tempfile.mktemp() _dbx("forWordCountCheck_b: %s" % (forWordCountCheck_b)) open(forWordCountCheck_b, "w").write("\n".join(wordCountResultLines_b)) _infoTs(" ************ DIFFing WordCounts ... ") diffWordCountResult = genUnixDiff(forWordCountCheck_a, forWordCountCheck_b) _infoTs(" ************ result of DIFFing WORD Counts") print(diffWordCountResult) persistAndPrintName(textName="formatted %s" % argParserResult.inFile, textContent=formattedLines, baseNamePrefix=argParserResult.inFile + '-') if "want to " == "use fsmMain": commentStack, signifStack = plstopa.separateCommentsFromSignficants( tree) #print( "*"*80 ); commentStack.simpleDump() #print( "*"*80 ); signifStack.simpleDump() signifStack.assembleComplexTokens() #signifStack.simpleDump( markComplexIdents= True ) useStatus = fsm.kickStartStatusByCode[ g_fsmInitStatusCode] if g_fsmInitStatusCode != None else plstopa.FsmState.start parsedTree = fsm.fsmMain(signifStack, startStatus=useStatus) # parsedTree.simpleDump() # eunitedTree = plstopa.mergeTokenTrees( commentStack, parsedTree ) reunitedTree = plstopa.mergeSignifcantAndCommentTrees( signifTree=parsedTree, commentTree=commentStack) _dbx("reunitedTree len %d" % (len(reunitedTree.arr))) print("*" * 30 + "reunited " + "*" * 20) #eunitedTree.simpleDump( markComplexIdents = True ) # reunitedTree.finalizeStats() # for node in reunitedTree.arr: node.showInfo() print(reunitedTree.formatTokenText()) if False: tree.assembleComplexTokens() # tree.simpleDump( markComplexIdents= False ) tree.simpleDump(markComplexIdents=False)
def spoolScriptWithSqlplusTempClob ( spoolDestRoot, dirSep, dbObjects, conn= None, envCode= None, clobTempTable = 'tt_extract_ddl_clob' ): """ Use sqlplus This method requires a global table accessible by the connecting user to write the source code extracted from DBA_SOURCE line by line as CLOB """ host, port, service, username = conn.host, conn.port, conn.service, conn.username spoolScriptHeader = """ WHENEVER SQLERROR EXIT WHENEVER OSERROR EXIT CONNECT {v_ez_connect} column db_name new_val db_name column spool_path_current new_val spool_path_current ALTER SESSION SET NLS_LANGUAGE=GERMAN ; set termout ON SELECT sys_context( 'userenv', 'db_name' ) AS db_name , user connect_as FROM dual ; set linesize 1000 longchunksize 9999999 long 9999999 pagesize 49999 """ scriptBlockFor1Object = """ WITH prep_ AS ( SELECT 'c:\\temp\&db_name\\' as base_folder , UPPER( '{lv_object_name}' ) || '-'||'&db_name' as obj_name_and_db_name , CASE upper('{lv_object_type}') WHEN 'PACKAGE_BODY' THEN '.pkb' WHEN 'PACKAGE_SPEC' THEN '.pks' WHEN 'TRIGGER' THEN '.trg' WHEN 'TYPE_BODY' THEN '.tpb' WHEN 'TYPE_SPEC' THEN '.tps' WHEN 'FUNCTION' THEN '.fnc' WHEN 'PROCEDURE' THEN '.prc' WHEN 'VIEW' THEN '.vw' ELSE '.sql' END AS file_ext FROM DUAL ) SELECT base_folder||obj_name_and_db_name||file_ext as spool_path_current FROM prep_ ; PROMPT spool_path_current set to &spool_path_current --CREATE global TEMPORARY TABLE tt_extract_ddl_clob ( owner varchar2(30), type varchar2(30), name varchar2(30), text clob ) on COMMIT preserve rows; SET ECHO OFF VERIFY OFF DECLARE lv_schema VARCHAR2(30) := UPPER('{lv_schema}'); lv_object_type VARCHAR2(30) := UPPER('{lv_object_type}'); lv_type_to_filter VARCHAR2(30) ; lv_object_name VARCHAR2(30) := UPPER('{lv_object_name}'); lv_clob CLOB := 'CREATE OR REPLACE '; lv_text LONG; BEGIN lv_type_to_filter := CASE lv_object_type WHEN 'PACKAGE_SPEC' THEN 'PACKAGE' WHEN 'PACKAGE_BODY' THEN 'PACKAGE BODY' WHEN 'TYPE_SPEC' THEN 'TYPE' WHEN 'TYPE_BODY' THEN 'TYPE BODY' ELSE lv_object_type END; EXECUTE IMMEDIATE 'truncate table {clobTempTable}'; FOR rec IN ( SELECT line, text FROM dba_source WHERE owner = lv_schema AND type = lv_type_to_filter AND name = lv_object_name ORDER BY line ) LOOP lv_text := rec.text; dbms_lob.append( lv_clob, lv_text ); -- dbms_OUTPUT.put_line( 'Ln'||$$plsql_line||': '||lv_offset ); -- IF mod(rec.line, 13) = 1 THEN dbms_output.put_line( rec.text ); END IF; END LOOP; INSERT INTO {clobTempTable} ( text ) VALUES ( lv_clob ); COMMIT; END; / set termout off trimspool on head off spool &spool_path_current SELECT text FROM {clobTempTable} ; spool off """ spoolScriptTrailer = """ EXIT """ password = getOraPassword ( oraUser= username, oraPasswordEnvVar= 'ORA_SECRET', batchMode= False, envSuffix= envCode ) ezConnect = """%s/"%s"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=%s)(PORT=%s)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=%s)))""" % ( username, password, host, port, service ) spoolPath = tempfile.mktemp() if "want to fight" == "the DOS vs gitbash vs unix platform gap": sqlTermoutPath = tempfile.mktemp() _dbx( "sqlTermoutPath %s" ) sqlpTermoutFh = open( sqlTermoutPath, "w" ) sqlpJob = subprocess.Popen( [ "sqlplus", "/nolog" ], stdin = subprocess.PIPE, stdout= sqlpTermoutFh ) sqlpJob.stdin.write( spoolScript.encode('utf-8') ) sqlpJob.communicate( ) sqlpTermoutFh = open( sqlTermoutPath, "r" ) _dbx( sqlpTermoutFh.readlines() ) else: # build one script block per DBObject _infoTs( "The connecting user will need access to global temporary table %s !" % clobTempTable ) scriptBlocks= [] for obj in dbObjects: scriptBlocks.append( scriptBlockFor1Object.format( spool_dest_root= spoolDestRoot, lv_schema= obj.owner, lv_object_type= obj.type, lv_object_name= obj.name, dir_sep= dirSep, clobTempTable = clobTempTable ) ) _dbx( "len( scriptBlocks ) : %d " % len( scriptBlocks ) ) headerFormatted = spoolScriptHeader.format( v_ez_connect= ezConnect ) spoolScript = "%s \n%s \n%s" % ( headerFormatted, "\n".join( scriptBlocks), spoolScriptTrailer ) sqlplusScriptPath = tempfile.mktemp() + '.sql' _dbx( "sqlplusScriptPath: %s" % ( sqlplusScriptPath ) ) open( sqlplusScriptPath, "w").write( spoolScript ) return sqlplusScriptPath
def fill_listIndexedBySchemaType(linesOfTouchedScripts): global g_internalSepator, g_listIndexedBySchemaType, g_schemataFound # _dbx( "foo" ); return _dbx(": %d" % (len(linesOfTouchedScripts))) schemaScripts = {} for line in linesOfTouchedScripts: pathNodes = line.split("/") _dbx(" nodes: %d" % (len(pathNodes))) schema = pathNodes[0] if len( pathNodes ) <= 2: # skip scripts which are on top level, e.g. BASIC_DATA/master.sql continue if schema not in schemaScripts.keys(): _dbx(line) _dbx(schema) schemaScripts[schema] = [] relPath = "/".join(pathNodes[1:]).rstrip("\n") schemaScripts[schema].append(relPath) _infoTs("Found touched scripts for schemata:\n %s" % (",".join(schemaScripts.keys()))) g_listIndexedBySchemaType = {} g_schemataFound = schemaScripts.keys() for schema in g_schemataFound: scriptList = schemaScripts[schema] for script in scriptList: fileExt = os.path.splitext(script)[1] if fileExt.upper() not in g_excludeTouchWithExtensions: scriptType = "UnknownScriptType" # _dbx( " ext: %s" % ( fileExt) ) # extract subfolder name pathNodes = script.split("/") if len(pathNodes) > 1: # pattern object_type / script_file subFolder = pathNodes[0] else: subFolder = None scriptType = "%s%s" % (subFolder if subFolder != None else '', fileExt.upper()) schemaType = schema + g_internalSepator + scriptType # _dbx("dbx script %s --> schemaType %s" % (script, schemaType) ) if not schemaType in g_listIndexedBySchemaType.keys(): g_listIndexedBySchemaType[schemaType] = [] _dbx(script) script = script.replace('/', '\\') script = "@@" + script _dbx(script) g_listIndexedBySchemaType[schemaType].append(script)
def getDiffStatsFromContents(contentA, contentB): lnCntA = len(contentA) lnCntB = len(contentB) _dbx("lnCntA %d" % (lnCntA)) diffOutput = difflib.context_diff(contentA, contentB, fromfile="fileA", tofile="fileB", n=1) newCnt = 0 delOrChgCnt = 0 for ln in diffOutput: if ln.startswith("! "): delOrChgCnt += 1 elif ln.startswith("+ "): newCnt += 1 _dbx("B has %d new lines and %d changed or deleted lines versus B" % (newCnt, delOrChgCnt)) _dbx("newCnt: %d" % (newCnt)) _dbx("delOrChgCnt: %d" % (delOrChgCnt)) maxLnCnt = lnCntA if lnCntA > lnCntB else lnCntB minLnCnt = lnCntA if lnCntA < lnCntB else lnCntA if maxLnCnt == 0: maxLnCnt = 1 if minLnCnt == 0: minLnCnt = 1 avgLnCnt = (maxLnCnt + minLnCnt) / 2 _dbx("minLnCnt: %d" % (minLnCnt)) _dbx("maxLnCnt: %d" % (maxLnCnt)) _dbx("avgLnCnt: %d" % (avgLnCnt)) # diffGrade meaning: # 0: no delta at all # 1: minor changes # 2: substantial changes if (maxLnCnt > minLnCnt * 2): diffGrade = 2 elif (avgLnCnt > (newCnt + delOrChgCnt) * 10): diffGrade = 1 elif avgLnCnt < 100 and (newCnt + delOrChgCnt) <= 10: diffGrade = 1 elif ((newCnt + delOrChgCnt) == 0): diffGrade = 0 else: _dbx("default diffGrade") diffGrade = 2 _dbx(diffGrade) return lnCntA, lnCntB, newCnt, delOrChgCnt, diffGrade