coffee PeopleSoft trace file (SQL+) extractor = work in progress - currently works for SQLs (on both .trc and .tracesql)

logparserstream.coffee

Transform = require('stream').Transform
stream = require 'stream'
split = require 'split'
            
# references:
#     http://nicolashery.com/parse-data-files-using-nodejs-streams/
#     http://nodejs.org/api/stream.html#stream_readable_pipe_destination_options        
#     https://github.com/substack/stream-handbook
#     https://www.npmjs.com/package/split
#     http://codewinds.com/blog/2013-08-04-nodejs-readable-streams.html
#     http://coffeescript.org/ 
#     http://arcturo.github.io/library/coffeescript/index.html     
        
class LogLiner extends Transform
   @APPTRC_BASE_RX = ///^[PSASRV]+\.([0-9]+)\s\(([0-9]+)\)\s*([0-9]+)-([0-9]+)\s*([.0-9]+)\s+([.0-9]+)(?:\s+)(.*)///i
   @TRC_REX_BASE = ///^([0-9]+)(?:\s*)([.0-9]+)(?:\s+)([.0-9]+)(?:\s+)(.*)$///i     
   @BASE_RX = @TRC_REX_BASE
   
   @TRC_COMMAND_COL = 4
   @TRC_LINE_NBR_COL = 1
   @APP_COMMAND_COL = 7
   
   constructor: () ->
      super()
      @parser = null
      @COMMAND_COL = 0
      
   _transform: (data, encoding, done) =>
      dataStr = data.toString()

      if @parser == null
         @parser = LogLiner.TRC_REX_BASE
         if @parser.test(dataStr)
            @COMMAND_COL = LogLiner.TRC_COMMAND_COL
         else
            @parser = LogLiner.APPTRC_BASE_RX
            if @parser.test(dataStr)
               @COMMAND_COL = LogLiner.APP_COMMAND_COL
            else
               @parser = null
         
      
      if @parser != null                  
         if @parser.test(dataStr)
            matchGroups = @parser.exec(dataStr)
            #console.log matchGroups[ @COMMAND_COL]
            @push matchGroups[ @COMMAND_COL]
   
      done()
      
      
class SQLEr extends Transform
   
   @CURID_COL = 1
   @DURATION_COL = 4
   @BINDID_COL = 5
   @STMT_COL = 5
   @BINDVAL_COL = 8
   @SESSIONID_COL = 2
   
   @CUR_RX = ///Cur#([0-9]+)///i
   @SESSION_ID_RX = ///\.([.A-Za-z0-9_]+)///i
   @RC_RX  = ///\s+RC=([0-9]+)///i
   @DUR_RX = ///\s+Dur=([.0-9]+)///i
   @TYPE_RX = ///\s+type=([0-9]+)///i
   @LENGTH_RX = ///\s+length=([0-9]+)///i
   
   @SQL_PREFIX_RX = new RegExp(@CUR_RX.source + @SESSION_ID_RX.source + @RC_RX.source + @DUR_RX.source, "i")
   @SQL_START_RX = new RegExp( @SQL_PREFIX_RX.source + "\\s+COM\\s+Stmt=(.*)", "i")
   @SQL_BIND_RX = new RegExp( @SQL_PREFIX_RX.source + "\\s+Bind-([0-9]+)" + @TYPE_RX.source + @LENGTH_RX.source + "\\s+(?:value=|LONG\\sTEXT\\sDATA)(.*)", "i")
   @SQL_EXE_RX = new RegExp( @SQL_PREFIX_RX.source + "\\s+(EXE)", "i")
   @SQL_FETCH_RX = new RegExp( @SQL_PREFIX_RX.source + "\\s+(Fetch)", "i")
   @SQL_END_RX = new RegExp( @SQL_PREFIX_RX.source + "\\s+(Disconnect)", "i")
   @SQL_CUR_OPN_RX = new RegExp( @SQL_PREFIX_RX.source + "\\s+Open Cursor Handle=([^ ]+).*", "i")
   
   constructor: (@outTemplate) ->
      super()     
      @statements = {}       
   
   _transform: (data, encoding, done) =>
      commandData = String.fromCharCode.apply(null, new Uint8Array(data))
      if commandData
         @process commandData
      done()

   bind: (type, commandData) =>
      matchGroups = SQLEr.SQL_BIND_RX.exec(commandData)
      curId = matchGroups[SQLEr.CURID_COL]         
      id = ":" + matchGroups[SQLEr.BINDID_COL]
      value = matchGroups[SQLEr.BINDVAL_COL]
      if @statements[curId]
         if @statements[curId].binds == null
            @statements[curId].binds = { ':1' : null }
         
         @statements[curId].binds[id] = value
   
   statement: (type, commandData) =>
      if type == "OPEN"
         rx = SQLEr.SQL_CUR_OPN_RX
      else if type == "START"
         rx =  SQLEr.SQL_START_RX
      else if type == "EXE"
         rx = SQLEr.SQL_EXE_RX
      else if type == "END"
         rx = SQLEr.SQL_END_RX
      else 
         return 

      matchGroups = rx.exec(commandData)      
      matchGroups['TYPE'] = type
      curId = matchGroups[SQLEr.CURID_COL]
      duration = matchGroups[SQLEr.DURATION_COL]
      sessionId = matchGroups[SQLEr.SESSIONID_COL]
      statement = matchGroups[SQLEr.STMT_COL]
      
      if @statements == null or @statements == undefined
         @statements = { 1 : null }
      
      if @statements[curId] == null or @statements[curId] == undefined
         @statements[curId] = { sessionId: null, statement: null, binds: null , duration: 0}
      
      if type == "OPEN"                     
         @statements[curId].sessionId = sessionId
         @statements[curId].statement =  null
         @statements[curId].binds = null
         #console.log "OPEN #{curId} #{commandData} " 
         #console.log @statements['22']
      else if type == "START"
         @statements[curId].sessionId = sessionId
         @statements[curId].statement =  statement
         @statements[curId].binds = null
         #console.log "START"
      else if type == "EXE"
         # apply bind variables here 
         if @statements[curId]
            cachedStatement = @statements[curId].statement
            cachedBinds =  @statements[curId].binds
            if cachedStatement != null
               for own key, value of cachedBinds
                  cachedStatement = cachedStatement.replace(key, "'" + value + "'" ) 
               
               outStr = "#{cachedStatement}\n"
               if (@outTemplate & 1) == 1
                  outStr = "#{duration}: #{outStr}" 
               if (@outTemplate & 2) == 2
                  outStr = "#{duration}: #{outStr}" 
               if (@outTemplate & 4) == 4
                  outStr = "#{duration}: #{outStr}" 
               if (@outTemplate & 8) == 8
                  outStr = "#{duration}: #{outStr}" 
                                 
               @push outStr
               
      else if type == "END"
         # possibly apply bind variables here and clear the cursor from the cache 
         @statements[curId].sessionId = null
         @statements[curId].statement = null
         @statements[curId].binds = null               
         
   process: (commandData) =>
      if SQLEr.SQL_CUR_OPN_RX.test(commandData)
         @statement "OPEN", commandData
      else if SQLEr.SQL_START_RX.test(commandData)
         @statement "START", commandData
      else if SQLEr.SQL_BIND_RX.test(commandData)
         @bind "TRC_SQL_BIND", commandData
      else if SQLEr.SQL_EXE_RX.test(commandData)
         @statement "EXE", commandData
      else if SQLEr.SQL_END_RX.test(commandData)
         @statement "END", commandData
         

liner = new LogLiner()                  
sqler = new SQLEr(1)  # 1 - include time taken as first column
   
process.stdin
.pipe(split())
.pipe(liner)
.pipe(sqler)
.pipe(process.stdout)

#Some programs like `head` send an error on stdout
#when they don't want any more data
process.stdout.on( 'error', process.exit )