Mercurial > sqlpython

comparison sqlpyPlus.py @ 136:2e69a257b6ab

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
now catching multi table names in from clause
author catherine@Elli.myhome.westell.com
date Thu, 28 Aug 2008 08:07:27 -0400
parents 4c59f4a8b680
children c27eeeea8752
comparison
equal deleted inserted replaced
135:4c59f4a8b680 136:2e69a257b6ab
508 508
509 def select_list(self, sql): 509 def select_list(self, sql):
510 self.curs.execute(sql) 510 self.curs.execute(sql)
511 return [r[0] for r in self.curs.fetchall()] 511 return [r[0] for r in self.curs.fetchall()]
512 512
513 tableNameRegex = re.compile(
514 r'(from|update)\s+(([a-zA-Z0-9_#$]+)\.)?([a-zA-Z0-9_#$]+)\s+',
515 re.IGNORECASE | re.DOTALL | re.MULTILINE)
516 tableNameRegex = re.compile(
517 r'(from|update)(.*)(where|set)',
518 re.IGNORECASE | re.DOTALL | re.MULTILINE)
519 columnNameRegex = re.compile( 513 columnNameRegex = re.compile(
520 r'select\s+(.*)from', 514 r'select\s+(.*)from',
521 re.IGNORECASE | re.DOTALL | re.MULTILINE) 515 re.IGNORECASE | re.DOTALL | re.MULTILINE)
522 def completedefault(self, text, line, begidx, endidx): 516 def completedefault(self, text, line, begidx, endidx):
523 segment = completion.whichSegment(line) 517 segment = completion.whichSegment(line)
544 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%%'
545 UNION 539 UNION
546 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'""" 540 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'"""
547 completions = self.select_list(stmt % (text, text)) 541 completions = self.select_list(stmt % (text, text))
548 if segment in ('where', 'group by', 'order by', 'having', 'set'): 542 if segment in ('where', 'group by', 'order by', 'having', 'set'):
549 try: 543 tableNames = completion.tableNamesFromFromClause(line)
550 owner, tableName = self.tableNameRegex.search(line).groups()[2:4] 544 if tableNames:
551 except AttributeError: 545 stmt = """SELECT column_name FROM all_tab_columns
552 return [] 546 WHERE table_name IN (%s)""" % \
553 if owner: 547 (','.join("'%s'" % (t) for t in tableNames))
554 stmt = "SELECT column_name FROM all_tab_columns WHERE owner = '%s' AND table_name = '%s'" \
555 % (owner.upper(), tableName.upper())
556 else:
557 stmt = "SELECT column_name FROM all_tab_columns WHERE table_name = '%s'" \
558 % (tableName.upper())
559 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text) 548 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text)
560 completions = self.select_list(stmt) 549 completions = self.select_list(stmt)
561 550
562 return completions 551 return completions
563 552