Mercurial > sqlpython

comparison sqlpyPlus.py @ 137:c27eeeea8752

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
completion extended to catchall
author catherine@Elli.myhome.westell.com
date Thu, 28 Aug 2008 08:27:52 -0400
parents 2e69a257b6ab
children 3b3c78bad48f
comparison
equal deleted inserted replaced
136:2e69a257b6ab 137:c27eeeea8752
504 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) 504 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
505 return result 505 return result
506 506
507 legalOracle = re.compile('[a-zA-Z_$#]') 507 legalOracle = re.compile('[a-zA-Z_$#]')
508 508
509 def select_list(self, sql): 509 def select_scalar_list(self, sql, binds={}):
510 self.curs.execute(sql) 510 self.curs.execute(sql, binds)
511 return [r[0] for r in self.curs.fetchall()] 511 return [r[0] for r in self.curs.fetchall()]
512 512
513 columnNameRegex = re.compile( 513 columnNameRegex = re.compile(
514 r'select\s+(.*)from', 514 r'select\s+(.*)from',
515 re.IGNORECASE | re.DOTALL | re.MULTILINE) 515 re.IGNORECASE | re.DOTALL | re.MULTILINE)
517 segment = completion.whichSegment(line) 517 segment = completion.whichSegment(line)
518 text = text.upper() 518 text = text.upper()
519 completions = [] 519 completions = []
520 if segment == 'select': 520 if segment == 'select':
521 stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'" 521 stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'"
522 completions = self.select_list(stmt % (text)) 522 completions = self.select_scalar_list(stmt % (text))
523 if not completions: 523 if not completions:
524 stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'" 524 stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'"
525 completions = self.select_list(stmt % (text)) 525 completions = self.select_scalar_list(stmt % (text))
526 if segment == 'from': 526 if segment == 'from':
527 columnNames = self.columnNameRegex.search(line) 527 columnNames = self.columnNameRegex.search(line)
528 if columnNames: 528 if columnNames:
529 columnNames = columnNames.group(1) 529 columnNames = columnNames.group(1)
530 columnNames = [c.strip().upper() for c in columnNames.split(',')] 530 columnNames = [c.strip().upper() for c in columnNames.split(',')]
531 stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'" 531 stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'"
532 for columnName in columnNames: 532 for columnName in columnNames:
533 completions.extend(self.select_list(stmt1 % (columnName, text))) 533 completions.extend(self.select_scalar_list(stmt1 % (columnName, text)))
534 if segment in ('from', 'update', 'insert into') and (not completions): 534 if segment in ('from', 'update', 'insert into') and (not completions):
535 stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'" 535 stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'"
536 completions = self.select_list(stmt % (text)) 536 completions = self.select_scalar_list(stmt % (text))
537 if not completions: 537 if not completions:
538 stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%' 538 stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'
539 UNION 539 UNION
540 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'""" 540 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'"""
541 completions = self.select_list(stmt % (text, text)) 541 completions = self.select_scalar_list(stmt % (text, text))
542 if segment in ('where', 'group by', 'order by', 'having', 'set'): 542 if segment in ('where', 'group by', 'order by', 'having', 'set'):
543 tableNames = completion.tableNamesFromFromClause(line) 543 tableNames = completion.tableNamesFromFromClause(line)
544 if tableNames: 544 if tableNames:
545 stmt = """SELECT column_name FROM all_tab_columns 545 stmt = """SELECT column_name FROM all_tab_columns
546 WHERE table_name IN (%s)""" % \ 546 WHERE table_name IN (%s)""" % \
547 (','.join("'%s'" % (t) for t in tableNames)) 547 (','.join("'%s'" % (t) for t in tableNames))
548 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text) 548 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text)
549 completions = self.select_list(stmt) 549 completions = self.select_scalar_list(stmt)
550 550 if not segment:
551 stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'"
552 completions = self.select_scalar_list(stmt % (text))
551 return completions 553 return completions
552 554
553 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') 555 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
554 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h') 556 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h')
555 ^ pyparsing.Literal('\n/') ^ \ 557 ^ pyparsing.Literal('\n/') ^ \
668 descQ = descQueries.get(object_type) 670 descQ = descQueries.get(object_type)
669 if descQ: 671 if descQ:
670 for q in descQ: 672 for q in descQ:
671 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner}) 673 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
672 elif object_type == 'PACKAGE': 674 elif object_type == 'PACKAGE':
673 self.curs.execute(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner}) 675 packageContents = self.select_scalar_list(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner})
674 packageContents = self.curs.fetchall() 676 for packageObj_name in packageContents:
675 for (packageObj_name,) in packageContents: 677 self.stdout.write('Arguments to %s\n' % (packageObj_name))
676 self.stdout.write(packageObj_name + '\n')
677 self.do_select(descQueries['PackageObjArgs'][0],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}) 678 self.do_select(descQueries['PackageObjArgs'][0],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
678 do_desc = do_describe 679 do_desc = do_describe
679 680
680 def do_deps(self, arg): 681 def do_deps(self, arg):
681 arg = self.parsed(arg).unterminated.upper() 682 arg = self.parsed(arg).unterminated.upper()
709 to resolve a database object's name. """ 710 to resolve a database object's name. """
710 parts = identifier.split('.') 711 parts = identifier.split('.')
711 try: 712 try:
712 if len(parts) == 2: 713 if len(parts) == 2:
713 owner, object_name = parts 714 owner, object_name = parts
714 self.curs.execute('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name', 715 object_type = self.select_scalar_list('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
715 {'owner': owner, 'object_name': object_name}) 716 {'owner': owner, 'object_name': object_name}
716 object_type = self.curs.fetchone()[0] 717 )[0]
717 elif len(parts) == 1: 718 elif len(parts) == 1:
718 object_name = parts[0] 719 object_name = parts[0]
719 self.curs.execute(queries['resolve'], {'objName':object_name}) 720 self.curs.execute(queries['resolve'], {'objName':object_name})
720 object_type, object_name, owner = self.curs.fetchone() 721 object_type, object_name, owner = self.curs.fetchone()
721 except TypeError: 722 except TypeError: