Blog do projektu Open Source JavaHotel

środa, 2 lipca 2014

MS SQL, SP and Python

Introduction
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:

Publikowanie komentarza