Mercurial > sqlpython

comparison sqlpyPlus.py @ 132:2baecb3d5356

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
improving tab completion
author catherine@Elli.myhome.westell.com
date Wed, 27 Aug 2008 21:49:26 -0400
parents 2b7ce838120d
children 25cbb45b190d
comparison
equal deleted inserted replaced
131:2b7ce838120d 132:2baecb3d5356
183 and c1.r_owner = c2.owner 183 and c1.r_owner = c2.owner
184 and c1.owner = :owner 184 and c1.owner = :owner
185 """ 185 """
186 } 186 }
187 187
188 import sys, os, re, sqlpython, cx_Oracle, pyparsing 188 import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion
189 from cmd2 import Cmd, make_option, options, Statekeeper 189 from cmd2 import Cmd, make_option, options, Statekeeper
190 190
191 if float(sys.version[:3]) < 2.3: 191 if float(sys.version[:3]) < 2.3:
192 def enumerate(lst): 192 def enumerate(lst):
193 return zip(range(len(lst)), lst) 193 return zip(range(len(lst)), lst)
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 complete_select(self, text, line, begidx, endidx): 509 def select_list(self, sql):
510 self.curs.execute(sql)
511 return [r[0] for r in self.curs.fetchall()]
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 columnNameRegex = re.compile(
517 r'select\s+(.*)from',
518 re.IGNORECASE | re.DOTALL | re.MULTILINE)
519 def completedefault(self, text, line, begidx, endidx):
520 segment = completion.whichSegment(line)
521 text = text.upper()
510 completions = [] 522 completions = []
511 for statement in """SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%' 523 if segment == 'select':
512 SELECT table_name FROM all_tables WHERE table_name LIKE '%s%%' 524 stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'"
513 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'""".splitlines(): 525 completions = self.select_list(stmt % (text))
514 self.curs.execute(statement % text.upper()) 526 if not completions:
515 completions.extend([r[0] for r in self.curs.fetchall()]) 527 stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'"
528 completions = self.select_list(stmt % (text))
529 elif segment in ('from', 'update'):
530 stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'"
531 completions = self.select_list(stmt % (text))
532 if not completions:
533 stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'
534 UNION
535 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'"""
536 completions = self.select_list(stmt % (text, text))
537 elif segment in ('where', 'group by', 'order by'):
538 try:
539 owner, tableName = self.tableNameRegex.search(line).groups()[2:4]
540 except AttributeError:
541 return []
542 if owner:
543 stmt = "SELECT column_name FROM all_tab_columns WHERE owner = '%s' AND table_name = '%s'" \
544 % (owner.upper(), tableName.upper())
545 else:
546 stmt = "SELECT column_name FROM all_tab_columns WHERE table_name = '%s'" \
547 % (tableName.upper())
548 completions = self.select_list(stmt)
549
516 return completions 550 return completions
517 551
518 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') 552 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
519 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h') 553 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h')
520 ^ pyparsing.Literal('\n/') ^ \ 554 ^ pyparsing.Literal('\n/') ^ \
975 def do_refs(self, arg): 1009 def do_refs(self, arg):
976 arg = self.parsed(arg).unterminated.upper() 1010 arg = self.parsed(arg).unterminated.upper()
977 object_type, owner, object_name = self.resolve(arg) 1011 object_type, owner, object_name = self.resolve(arg)
978 if object_type == 'TABLE': 1012 if object_type == 'TABLE':
979 self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner}) 1013 self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner})
980
981 sqlStyleComment = pyparsing.Literal("--") + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n"))
982 keywords = {'order by': pyparsing.Keyword('order', caseless=True) +
983 pyparsing.Keyword('by', caseless=True),
984 'select': pyparsing.Keyword('select', caseless=True),
985 'from': pyparsing.Keyword('from', caseless=True),
986 'having': pyparsing.Keyword('having', caseless=True),
987 'update': pyparsing.Keyword('update', caseless=True),
988 'set': pyparsing.Keyword('set', caseless=True),
989 'delete': pyparsing.Keyword('delete', caseless=True),
990 'insert into': pyparsing.Keyword('insert', caseless=True) +
991 pyparsing.Keyword('into', caseless=True),
992 'values': pyparsing.Keyword('values', caseless=True),
993 'group by': pyparsing.Keyword('group', caseless=True) +
994 pyparsing.Keyword('by', caseless=True),
995 'where': pyparsing.Keyword('where', caseless=True)}
996 for (name, parser) in keywords.items():
997 parser.ignore(pyparsing.sglQuotedString)
998 parser.ignore(pyparsing.dblQuotedString)
999 parser.ignore(pyparsing.cStyleComment)
1000 parser.ignore(sqlStyleComment)
1001 parser.name = name
1002
1003 def orderedParseResults(parsers, statement):
1004 results = []
1005 for parser in parsers:
1006 results.extend(parser.scanString(statement))
1007 results.sort(cmp=lambda x,y:cmp(x[1],y[1]))
1008 return results
1009
1010 def whichSqlPhrase(statement):
1011 results = orderedParseResults(keywords.values(), statement)
1012 if results:
1013 return ' '.join(results[-1][0])
1014 else:
1015 return None
1016
1017 oracleIdentifierCharacters = pyparsing.alphanums + '_#$'
1018 def wordInProgress(statement):
1019 result = []
1020 letters = list(statement)
1021 letters.reverse()
1022 for letter in letters:
1023 if letter not in oracleIdentifierCharacters:
1024 result.reverse()
1025 return ''.join(result)
1026 result.append(letter)
1027 result.reverse()
1028 return ''.join(result)
1029 1014
1030 def _test(): 1015 def _test():
1031 import doctest 1016 import doctest
1032 doctest.testmod() 1017 doctest.testmod()
1033 1018