#!/usr/bin/env python
########################################################################
#
# Oracle debugging utility module
#
########################################################################
#
# This module is for debug purposes only!
#
# It works in cooperation with the DCOracle2 Oracle interface module
#
# it will print every SQL statement and arguments, and detailed debugging info
# for slow queries
#
# Copyright (c) 2003, Kefta Inc. (www.kefta.com)
#
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#
# - Redistributions of source code must retain the above copyright notice, this
#   list of conditions and the following disclaimer.
#
# - Redistributions in binary form must reproduce the above copyright notice,
#   this list of conditions and the following disclaimer in the documentation
#   and/or other materials provided with the distribution.
#
# - Neither the name of Kefta, Inc. nor the names of its contributors may
#   be used to endorse or promote products derived from this software without
#   specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR
# ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
# SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
# CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
# DAMAGE.
#
# $Id: debug_ora.py,v 1.2 2003/10/14 21:46:22 majid Exp majid $
#
########################################################################

import sys, time, random, DCOracle2

# SQL execution time threshold beyond which to warn and dump an execution plan
slow_sql_threshold = 2.0

########################################################################
#
# The rest of this code should not require user intervention

# We will override methods of the DCOracle2 cursor class, and need to
# save them
save_exec = DCOracle2.cursor.execute
save_commit = DCOracle2.connection.commit

def new_commit(self):
  t = time.time()
  msec = int((t-int(t))*1000)
  print >> sys.stderr, "SQL: commit %s.%d" % \
        (time.strftime("%b-%d-%Y %H:%M:%S", time.localtime(t)), msec)
  save_commit(self)

def sql_literal(arg):
  """Convert a Python object to a SQL literal"""
  # expand :1, ... :n style bind variables
  if type(arg) == str:
    # escape single quotes
    # XXX should also expand other special characters
    arg = arg.replace("'", "''")
    # quote the entire string
    return "'" + arg + "'"
  elif type(arg) in [int, float, long]:
    arg = str(arg)
  elif isinstance(arg, DCOracle2.dco2.OracleDateType):
    arg = "to_date('%s', 'YYYY-MM-DD HH24:MI:SS')" % str(arg)
  else:
    raise NotImplementedError
  return arg

def new_exec(self, statement, *args, **kwargs):
  t = time.time()
  msec = int((t-int(t))*1000)
  print >> sys.stderr, "SQL:", \
        "%s:%d" % \
        (time.strftime("%b-%d-%Y %H:%M:%S", time.localtime(t)), msec)
  print >> sys.stderr, statement
  print >> sys.stderr, 'ARG:', args, kwargs
  sys.stderr.flush()
  save_bind_pointer = None
  for arg_bound in args:
    if type(arg_bound).__name__ == 'BindingArray':
      save_bind_pointer = arg_bound
  result = apply(save_exec, (self, statement) + args, kwargs)
  if save_bind_pointer != None:
    print >> sys.stderr, "BindingArray value:", save_bind_pointer[0]
  sys.stderr.flush()
  delta_t = time.time() - t
  # for slow queries, print out a warning and 
  if delta_t > slow_sql_threshold:
    print >> sys.stderr, 'SQL:', '!' * 16, 'slow query, time =',
    print >> sys.stderr, '%.1f' % delta_t, 'sec'
    # EXPLAIN PLAN only works for SELECT, UPDATE, INSERT, and DELETE statements
    if statement.upper().split()[0] not in [
      'SELECT', 'UPDATE', 'INSERT', 'DELETE']:
      return result
    print >> sys.stderr, 'SQL:', '!' * 16, 'execution plan follows'
    # EXPLAIN PLAN does not like bind variables in its statement
    expanded_stmt = statement
    # XXX this bind variable substitution does not work correctly if something
    # XXX that looks like a bind variable reference appears in a string
    # XXX literal, but even then it should not alter the execution plan
    # XXX significantly
    for i in range(len(args)):
      expanded_stmt = expanded_stmt.replace(':' + `i+1`, sql_literal(args[i]))
    for kw in kwargs:
      expanded_stmt = expanded_stmt.replace(':' + kw, sql_literal(kwargs[kw]))
    stmt_id = str(random.random())
    if isinstance(self, DCOracle2.cursor):
      c = self._connection.cursor()
    else:
      c = self.cursor()
    save_exec(c, "explain plan set statement_id='%s' for %s" \
              % (stmt_id, expanded_stmt))
    save_exec(c, """select id, parent_id, operation, options, optimizer,
    object_name
    from plan_table where statement_id='%s' order by id""" % stmt_id)
    plan = c.fetchall()
    # track level of indent required for this step in the plan
    ops = {}
    for op_id, parent_id, operation, options, optimizer, obj_name in plan:
      ops[op_id] = ops.get(parent_id, 0) + 1
      print >> sys.stderr, '%03d' % op_id,
      if parent_id:
        print >> sys.stderr, '%03d' % parent_id,
      else:
        print >> sys.stderr, '   ',
      print >> sys.stderr, '  ' * (ops[op_id] - 1), operation,
      if options:
        print >> sys.stderr, '(%s)' % options,
      if optimizer and optimizer != 'ANALYZED':
        print >> sys.stderr, 'Optimizer=%s' % optimizer,
      if obj_name:
        print >> sys.stderr, 'ON %s' % obj_name,
      print >> sys.stderr
    save_exec(c, """delete from plan_table where statement_id='%s'""" \
              % stmt_id)
    c.close()
    return result

DCOracle2.cursor.execute = new_exec
DCOracle2.connection.commit = new_commit

# A two-bit Oracle shell, which at least offers command-line history
# and editing, something Oracle hasn't gotten around to in the last
# 30 years or so...
if __name__ == '__main__':
  import readline
  assert(len(sys.argv) in [1, 2])
  if len(sys.argv) == 2:
    oracle_string = sys.argv[1]
  else:
    import os, getpass
    login = raw_input('Enter user-name: ')
    password = getpass.getpass('Enter password: ')
    sid = os.getenv('ORACLE_SID')
    oracle_string = '%s/%s' % (login, password)
  d = DCOracle2.connect(oracle_string)
  slow_sql_threshold = 0
  try:
    cmd = ''
    while True:
      if cmd:
        i += 1
        prompt = '  ' + `i` + ' '
      else:
        i = 1
        prompt = 'SQL> '
      cmd += raw_input(prompt).strip()
      if cmd.endswith(';'):
        cmd = cmd.strip(';')
        try:
          d.execute(cmd)
        except DCOracle2.Error, e:
          print
          print e
          cmd = ''
          continue
        rows = d.fetchall()
        col_size = map(
          max, zip(
          *[[len(str(entry)) for entry in row] for row in rows]))
        fmt = ' '.join(['%%-%ds' % l for l in col_size])
        print
        print fmt % tuple([col[0][0:size]
                           for col, size
                           in zip(d._cursor.description, col_size)])
        print fmt % tuple(['-' * n for n in col_size])
        for row in rows:
          print fmt % tuple(map(str, row))
        cmd = ''
  except EOFError:
    print >> sys.stderr