MS SQL allows exporting database objects into sql script file (for instance: stored procedures with the body). ( Database -> Tasks->Generate Scripts) This option allows creating one single script file for all objects exported or separate file for every object.
But what to do if we have one single file with all stored procedures without an access to MS SQL. Or if we want pure SP body not enriched by additional stuff created by "Generate Scripts".
Sometimes we have two versions of the script file (earlier and later) and want to compare what was developed in the meantime. It is much easier to compare having every SP in different file, we can use a standard comparing software (like Meld).
Facing this problem I created a simple Python script which extracts all SP from a single script file and put them into separate files.
Python code - Extract class
''' Created on 2 lip 2014 @author: sbartkowski ''' import logging import os import shutil LOGNAME="EXTRACT" _L = logging.getLogger(LOGNAME) _CREATE="CREATE" _PROCEDURE="PROCEDURE" _GO="GO" class EXTRACT() : ''' Extracts SP from single export file. Every SP extracted is placed in a separate file. It is assumed that the body of the SP is enclosed by CREATE PROCEDURE and GO statements. The file naming convention is : schema_SP name.sql Example: CREATE PROCEDURE [sales].[SelectUnsold] .... GO file name: sales_SelectUnsold.sql The output directory is created or cleaned if exists Attributes: infile : the name of the input file containg a bunch of SP outdir : output directory f : 'file' object' opened as 'infile' ''' def __init__(self, infile,outdir): ''' Constructor Args: infile : input file outdir : output directory ''' self.infile = infile self.outdir = outdir self.f = None def _isProc(self,l): ''' Test if the line contains CREATE PROCEDURE Args: l : source line Returns: True : if CREATE PROCEDURE False: otherwise ''' tokens = l.split() if len(tokens) < 3 : return None if tokens[0].upper() != _CREATE or tokens[1].upper() != _PROCEDURE : return None return tokens[2] def _isClosingGo(self,l): ''' Test if the line contains GO statement (denoting the end of SP) Args: l : source line Returns: True: line with GO False: otherwise ''' tokens = l.upper().split() if len(tokens) != 1 : return False return tokens[0] == _GO def _outFileName(self,procName) : ''' Constructs the name of the output file related to the SP name Args: SP name in shape of [schema name].[proc name] Returns: The output file name (without path) ''' pName = procName.replace("[","").replace("]","").replace(".","_") outFileName = os.path.join(self.outdir,pName) + ".sql" return outFileName def _writeFile(self,fileName,listofl): ''' Flushes the body of the SP to the output file Args: fileName : the name of the output file listofl : the list of lines with SP body ''' f = open(fileName,"w") f.writelines(listofl) f.close() def run(self): ''' The main method, bounding them all ''' # print self.infile, self.outdir _L.info("Start executing") _L.info("Source : " + self.infile) _L.info("Output dir :" + self.outdir) if os.path.exists(self.outdir): _L.info("Removing content of " + self.outdir) shutil.rmtree(self.outdir) _L.info("Creating directory " + self.outdir) os.mkdir(self.outdir) _L.info("Opening source file") self.f = open(self.infile, 'r') _L.info("Success, opened") listofl = None procName = None noprocs = 0 nolines = 0 # read line by line for l in self.f : if listofl == None : procName = self._isProc(l) if procName : _L.info(procName) listofl = [l,] noprocs = noprocs + 1 continue else : listofl.append(l) if self._isClosingGo(l) : _L.info("End of " + procName + " found") outfileName = self._outFileName(procName) nolines = nolines + len(listofl) _L.info("Creating " + outfileName + " lines:" + str(len(listofl))) self._writeFile(outfileName,listofl) listofl = None _L.info("Closing source file") _L.info(str(noprocs) + " SP extracted with " + str(nolines) + " lines of code") self.f.close()Python code - main
Below is an example of usage of this files. It reads two scripts file containing SP and put them into two directories, "old" and "new"
''' Created on 2 lip 2014 @author: sbartkowski ''' import logging from extr import extr def setLogger(): logger = logging.getLogger(extr.LOGNAME) logger.setLevel(logging.INFO) ch = logging.StreamHandler() ch.setLevel(logging.INFO) #formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') #ch.setFormatter(formatter) logger.addHandler(ch) INDIR1="/home/sbartkowski/Dokumenty/db2/SP/2014-03-22" INDIR2="/home/sbartkowski/Dokumenty/db2/SP/2014-06-22" OUTDIR1="/home/sbartkowski/Dokumenty/db2/SP/old" OUTDIR2="/home/sbartkowski/Dokumenty/db2/SP/new" INFILE="SP.sql" def main() : setLogger() E = extr.EXTRACT(INDIR1+INFILE,OUTDIR1) E.run() E = extr.EXTRACT(INDIR2+INFILE,OUTDIR2) E.run() if __name__ == '__main__': main()
Brak komentarzy:
Prześlij komentarz