#!/usr/bin/python
# MySqlPy V1.3
# Author: Luca.Canali@cern.ch
# 
#
# Companion of SqlPython, a python module that reproduces Oracle's command line within python
# 'sqlplus inside python'
# See also: http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython
#           http://catherine.devlin.googlepages.com/

from sqlpyPlus import *
import binascii

class mysqlpy(sqlpyPlus):
    '''
MySqlPy V1.3 - 'sqlplus in python'
Author: Luca.Canali@cern.ch
Rev: 1.3.0, 17-Oct-07

Companion of SqlPython, a python module that reproduces Oracle's command line within python
and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com
Quick start command list:

- top     -> executes a query to list all active sessions in (Oracle 10g and RAC)
             (use: instance activity monitoring, a DBA tool)
- tselect -> prints the result set in trasposed form, useful to print result sets with
             many columns such as dba_ or v$ views (ex: dba_tables or v$instance)
- py      -> execute a python command (C.D.) 
- db      -> quick connect using credentials in pass.txt file
             (Ex: write username and pass in pass.txt and then "db db_alias" to connect)
- sql     -> prints the sql text from the cache. parameter: sql_id of the statement
             (Ex: sql fzqa1qj65nagki)
- explain -> prints the execution plan from the cache. parameter: sql_id of the statement 
- sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101)
- longops -> prints from gv$session_longops (running full scans, etc)
- load    -> prints the OS load on all cluster nodes (10g RAC)
- sleect,slect  -> alias for select (I mistyped select this way too many times...)
- top9i   -> 9i (and single instance) version of top
- describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.)
- shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.)
- :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.)

Example:
 SQL> connect username@dbalias or username/pass@dbalias
 SQL> select sysdate from dual;
 SQL> exit
    '''

    def __init__(self):
        sqlpyPlus.__init__(self)
        self.maxtselctrows = 10
        self.query_load10g = '''
	  ins.instance_name,ins.host_name,round(os.value,2) load
	  from gv$osstat os, gv$instance ins
	  where os.inst_id=ins.inst_id and os.stat_name='LOAD'
	  order by 3 desc;
        '''
        self.query_top9i = '''
          sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status,
	  module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps 
          from v$session 
          where username is not null and program not like 'emagent%' and status='ACTIVE'
                and audsid !=sys_context('USERENV','SESSIONID'); 
        '''
        self.query_ractop = '''
 	inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr,  
	sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event,
	case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps
        from gv$session 
        where status='ACTIVE' and username is not null 
	      and not (event like '% waiting for messages in the queue' and state='WAITING')
              and audsid !=sys_context('USERENV','SESSIONID');
        '''
        self.query_longops = '''
        inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message
        from gv$session_longops
        where time_remaining>0;
        '''

    def do_top9i(self,args):
        '''Runs query_top9i defined above, to display active sessions in Oracle 9i'''
        self.do_select(self.query_top9i)
    
    def do_top(self,args): 
        '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)'''
        self.do_select(self.query_ractop)

    def do_longops(self,args):
        '''Runs query_longops defined above, to display long running operations (full scans, etc)'''
        self.do_select(self.query_longops)

    def do_load(self,args):
        '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC)'''
        self.do_select(self.query_load10g)

    def do_db(self,args,filepath='pass.txt'): 
        '''Exec do_connect to db_alias in args (credentials form the file pass.txt) '''
        f = open(filepath,'r')
        connectstr = f.readline().strip() +'@'+args
        self.do_connect(connectstr)
        f.close()

    def do_py(self, arg):  
        '''Executes a python command'''
        try:
            exec(arg)
        except Exception, e:
            print e

    def do_tselect(self, arg, rowlimit=None):  
        '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' 
        self.query = sqlpython.Statement('select '+arg).query
        try:
            print self.query
            self.curs.execute(self.query)
            rows = self.curs.fetchmany(min(self.maxtselctrows, rowlimit or self.maxtselctrows))
            desc = self.curs.description
            self.rc = self.curs.rowcount
            rows.insert(0,[desc[x][0] for x in range(len(desc))]) # adds column name to the row set
            transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
            newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
            for x in range(len(desc)):
                if str(desc[x][1]) == "<type 'cx_Oracle.BINARY'>":  # handles RAW columns
                    rname = transpr[x][0]
                    transpr[x] = map(binascii.b2a_hex, transpr[x])
                    transpr[x][0] = rname
            self.debg=transpr
            newdesc[0][0] = 'COLUMN NAME'
            if self.rc > 0:
                print '\n' + sqlpython.pmatrix(transpr,newdesc)
            if self.rc == 0:
                print '\nNo rows Selected.\n'
            elif self.rc == 1: 
                print '\n1 row selected.\n'
            elif self.rc < self.maxtselctrows:
                print '\n%d rows selected.\n' % self.rc
            else:
                print '\nSelected Max Num rows (%d)' % self.rc                 
        except Exception, e:
            print e
            

    def do_sql(self,args):
        '''prints sql statement give the sql_id (Oracle 10gR2)'''
        self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'"
        try:
            self.curs.execute(self.query)
            row = self.curs.fetchone()
            print "\nSQL statement from cache"
            print "------------------------\n"
            while row:
                print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read()
                row = self.curs.next()
        except Exception, e:
            print e

    def do_explain(self,args):
        '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql '''
        self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))"
        try:
            self.curs.execute(self.query)
            rows = self.curs.fetchall()
            desc = self.curs.description
            self.rc = self.curs.rowcount
            if self.rc > 0:
                print '\n' + sqlpython.pmatrix(rows,desc,200)
        except Exception, e:
            print e

    def do_sessinfo(self,args):
        '''Reports session info for the give sid, extended to RAC with gv$'''
        self.do_tselect('* from gv$session where sid='+args+';')

    def do_sleect(self,args):    
        '''implements sleect = select, a common typo'''
        self.do_select(args)

    do_slect = do_sleect

def run():
    my=mysqlpy()
    print my.__doc__
    my.cmdloop()

if __name__ == '__main__':
    run()