sqlpython: sqlpyPlus.py comparison

comparison sqlpyPlus.py @ 10:2ef0e2608123

reworking pull

author	devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
date	Tue, 18 Dec 2007 17:00:45 -0500
parents	8e909570e7de
children	cab368ea3ec8

comparison

equal deleted inserted replaced

-:f70cc3be6377
+:2ef0e2608123
 :b = 3
 - catherinedevlin.blogspot.com  May 31, 2006
 """
 # note in cmd.cmd about supporting emacs commands?
-pullQueries = {
-'PROCEDURE':("""
-text
-FROM   all_source
-WHERE  owner = :owner
-AND    name = :object_name
-""",),
-'PACKAGE':("""
-text
-FROM   all_source
-WHERE  owner = :owner
-AND    name = :object_name
-AND    type = 'PACKAGE_BODY'
-""",),
-'TYPE':("""
-text
-FROM   all_source
-WHERE  owner = :owner
-AND    name = :object_name
-AND    type = 'TYPE'
-""",
-"""
-text
-FROM   all_source
-WHERE  owner = :owner
-AND    name = :object_name
-AND    type = 'TYPE_BODY'
-""",)
-}
-pullQueries['TRIGGER'] = pullQueries['PROCEDURE']
-pullQueries['FUNCTION'] = pullQueries['PROCEDURE']
 descQueries = {
 'TABLE': ("""
 atc.column_name,
 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
 FROM all_tab_columns atc
 JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
 WHERE atc.table_name = :object_name
 AND      atc.owner = :owner
 ORDER BY atc.column_id;""",
+#thanks to Senora.pm for "refs"
+'refs': """
+NULL               referenced_by,
+c2.table_name      references,
+c1.constraint_name constraint
+FROM
+user_constraints c1,
+user_constraints c2
+WHERE
+c1.table_name = :object_name
+and c1.constraint_type ='R'
+and c1.r_constraint_name = c2.constraint_name
+and c1.r_owner = c2.owner
+and c1.owner = :owner
+UNION
+SELECT c1.table_name      referenced_by,
+NULL               references,
+c1.constraint_name constraint
+FROM
+user_constraints c1,
+user_constraints c2
+WHERE
+c2.table_name = :object_name
+and c1.constraint_type ='R'
+and c1.r_constraint_name = c2.constraint_name
+and c1.r_owner = c2.owner
+and c1.owner = :owner
+"""
 }
 import sys, os, re, sqlpython, cx_Oracle, pyparsing
 if float(sys.version[:3]) < 2.3:
 try:
 getme = int(getme)
 if getme < 0:
 return self[:(-1 * getme)]
 else:
-return self[getme-1]
+return [self[getme-1]]
 except IndexError:
 return []
 except (ValueError, TypeError):
 getme = getme.strip()
 mtch = self.rangeFrom.search(getme)
 self.spoolFile = None
 self.autobind = False
 self.failover = False
 self.multiline = '''select insert update delete tselect
 create drop alter'''.split()
-self.excludeFromHistory = '''run r list l history hi ed'''.split()
+self.excludeFromHistory = '''run r list l history hi ed li'''.split()
 def default(self, arg, do_everywhere=False):
 sqlpython.sqlpython.default(self, arg, do_everywhere)
 self.sqlBuffer.append(self.query)
 except Exception:
 return line
 def postcmd(self, stop, line):
 """Hook method executed just after a command dispatch is finished."""
-command = line.split(None,1)[0].lower()
+	try:
-if command not in self.excludeFromHistory:
+	    command = line.split(None,1)[0].lower()
-self.history.append(line)
+	    if command not in self.excludeFromHistory:
-return stop
+		self.history.append(line)
+	finally:
+	    return stop
 def onecmd_plus_hooks(self, line):
 line = self.precmd(line)
 stop = self.onecmd(line)
 stop = self.postcmd(stop, line)
 def do_pull(self, arg):
 "Displays source code."
 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
 print "%s %s.%s" % (object_type, owner, object_name)
-pullQ = pullQueries.get(object_type)
+	print self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB,
-if pullQ:
+				 [object_type, object_name, owner])
-for q in pullQ:
-self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
 def do_describe(self, arg):
 "emulates SQL*Plus's DESCRIBE"
 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
 print "%s %s.%s" % (object_type, owner, object_name)
 descQ = descQueries.get(object_type)
 if descQ:
 for q in descQ:
 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
 elif object_type == 'PACKAGE':
-self.curs.execute(descQueries['PackageObjects'], {'package_name':object_name, 'owner':owner})
+self.curs.execute(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner})
-for (packageObj_name,) in self.curs:
+	    packageContents = self.curs.fetchall()
+for (packageObj_name,) in packageContents:
 print packageObj_name
-self.do_select(descQueries['PackageObjArgs'],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
+self.do_select(descQueries['PackageObjArgs'][0],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
 do_desc = do_describe
 def do_comments(self, arg):
 'Prints comments on a table and its columns.'
 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
 def do_grep(self, arg):
 """grep PATTERN TABLE - search for term in any of TABLE's fields"""
 targets = arg.split()
 pattern = targets.pop(0)
 for target in targets:
+target = target.rstrip(';')
 sql = []
-print 'select * from %s where 1=0' % target
 try:
 self.curs.execute('select * from %s where 1=0' % target)
 sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
 sql = '* FROM %s WHERE %s' % (target, sql)
 self.do_select(sql)
 except Exception, e:
 print e
 import traceback
 traceback.print_exc(file=sys.stdout)
+def do_refs(self, arg):
+object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
+	if object_type == 'TABLE':
+	    self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner})
 def _test():
 import doctest
 doctest.testmod()
 if __name__ == "__main__":

Mercurial > sqlpython

comparison sqlpyPlus.py @ 10:2ef0e2608123