# HG changeset patch # User catherine@cordelia # Date 1255144130 14400 # Node ID 1e3179b31a1e771e97a1379023bb2f7e72d8b6e6 # Parent 20ea81921cd3877581f38c706c367a7f4a9b90b4 nice progress on tab completion diff -r 20ea81921cd3 -r 1e3179b31a1e sqlpython/schemagroup.py --- a/sqlpython/schemagroup.py Fri Oct 09 12:07:21 2009 -0400 +++ b/sqlpython/schemagroup.py Fri Oct 09 23:08:50 2009 -0400 @@ -1,8 +1,23 @@ -import gerald, re, datetime, threading, time +import gerald, re, datetime, threading, time, operator def gerald_connection_string(sqlalchemy_connection_string): return sqlalchemy_connection_string.split('/?mode=')[0].replace('//','/') - + +def build_column_list(schema): + schema.column_names = [[c for c in o.columns] for o in schema.schema.values() + if hasattr(o, 'columns') + and hasattr(o.columns, 'keys')] + schema.column_names = reduce(operator.add, schema.column_names, []) + schema.table_names = [] + schema.qual_table_names = [] + for t in schema.schema.values(): + if hasattr(t, 'columns') and isinstance(t.columns, dict): + schema.table_names.append(t.name) + schema.qual_table_names.append('%s.%s' % (schema.name, t.name)) + for c in t.columns: + schema.column_names.append('%s.%s' % (t.name, c)) + schema.qual_column_names = ['%s.%s' % (schema.name, c) for c in schema.column_names] + class RefreshThread(threading.Thread): def __init__(self, schemagroup, owner): threading.Thread.__init__(self) @@ -14,29 +29,33 @@ self.schema = self.schemagroup.childtype(self.owner, self.schemagroup) else: self.schema.refreshed = self.schema.time() - self.schemagroup.schemas[self.owner] = self.schema - + self.schemagroup.schemas[self.owner] = self.schema + build_column_list(self.schema) + class RefreshGroupThread(threading.Thread): - def __init__(self, schemas, minutes_between_refreshes): + def __init__(self, schemas): threading.Thread.__init__(self) self.parent = threading.current_thread() self.schemas = schemas - self.minutes_between_refreshes = minutes_between_refreshes self.daemon = True def run(self): - while self.parent.isAlive(): - self.schemas.refresh() - time.sleep(60 * self.minutes_between_refreshes) + self.schemas.refresh() + s.column_names = reduce(operator.add, + [s.qual_column_names for s in self.schemas.values()], + []) + s.table_names = reduce(operator.add, + [s.qual_table_names for s in self.schemas.values()], + []) class SchemaDict(dict): schema_types = {'oracle': gerald.OracleSchema} - def __init__(self, dct, rdbms, user, connection, connection_string, minutes_between_refreshes): + def __init__(self, dct, rdbms, user, connection, connection_string): dict.__init__(self, dct) self.child_type = self.schema_types[rdbms] self.user = user self.connection = connection self.gerald_connection_string = gerald_connection_string(connection_string) - self.refresh_thread = RefreshGroupThread(self, minutes_between_refreshes) + self.refresh_thread = RefreshGroupThread(self) self.complete = 0 def refresh_asynch(self): self.refresh_thread.start() @@ -59,13 +78,16 @@ # what if a user's last object is deleted? if isinstance(self.complete, int): self.complete += 1 + self.column_names = [s.column_names for s in self.values()] + self.columns = reduce(operator.add, [s.column_names for s in self.values()]) self.complete = 'all' + print 'metadata discovered' def refresh_one(self, owner, current_database_time=None): if not current_database_time: current_database_time = self.get_current_database_time() self[owner] = self.child_type(owner, self.gerald_connection_string) self[owner].refreshed = current_database_time - + build_column_list(self[owner]) class PlainObject(object): '''Simply a dumb container for attributes.''' @@ -107,4 +129,15 @@ return self.object_name def descriptor(self, qualified=False): return '%s/%s' % (self.db_type, self.name(qualified)) - \ No newline at end of file + +s = None + +if __name__ == '__main__': + connection_string = 'oracle://jrrt:password@orcl/?mode=0' + connection = None + dct = {} + user = 'jrrt' + rdbms = 'oracle' + s = SchemaDict(dct, rdbms, user, connection, connection_string, 100) + sch = s.child_type('jrrt',s.gerald_connection_string) + \ No newline at end of file diff -r 20ea81921cd3 -r 1e3179b31a1e sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Fri Oct 09 12:07:21 2009 -0400 +++ b/sqlpython/sqlpyPlus.py Fri Oct 09 23:08:50 2009 -0400 @@ -352,7 +352,7 @@ sqlpython.sqlpython.__init__(self) self.binds = CaselessDict() self.settable += '''autobind bloblimit colors commit_on_exit maxfetch maxtselctrows - minutes_between_refreshes rows_remembered scan serveroutput + rows_remembered scan serveroutput sql_echo timeout heading wildsql version'''.split() self.settable.remove('case_insensitive') self.settable.sort() @@ -369,7 +369,6 @@ self.result_history = [] self.rows_remembered = 10000 self.bloblimit = 5 - self.minutes_between_refreshes = 1 self.version = 'SQLPython %s' % sqlpython.__version__ self.pystate = {'r': [], 'binds': self.binds, 'substs': self.substvars} @@ -544,40 +543,25 @@ r'select\s+(.*)from', re.IGNORECASE | re.DOTALL | re.MULTILINE) def completedefault(self, text, line, begidx, endidx): + (username, schemas) = self.metadata() segment = completion.whichSegment(line) text = text.upper() completions = [] if segment == 'select': - stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - if not completions: - stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - if segment == 'from': - columnNames = self.columnNameRegex.search(line) - if columnNames: - columnNames = columnNames.group(1) - columnNames = [c.strip().upper() for c in columnNames.split(',')] - stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'" - for columnName in columnNames: - # and if columnName is * ? - completions.extend(self.select_scalar_list(stmt1 % (columnName, text))) - if segment in ('from', 'update', 'insert into') and (not completions): - stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'" - completions = self.select_scalar_list(stmt % (text)) - if not completions: - stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%' - UNION - SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'""" - completions = self.select_scalar_list(stmt % (text, text)) - if segment in ('where', 'group by', 'order by', 'having', 'set'): - tableNames = completion.tableNamesFromFromClause(line) - if tableNames: - stmt = """SELECT column_name FROM all_tab_columns - WHERE table_name IN (%s)""" % \ - (','.join("'%s'" % (t) for t in tableNames)) - stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text) - completions = self.select_scalar_list(stmt) + completions = [c for c in schemas[username].column_names if c.startswith(text)] \ + or [c for c in schemas.qual_column_names if c.startswith(text)] + # TODO: the latter not working + if segment in ('from', 'update', 'insert into'): + completions = [t for t in schemas[username].table_names if t.startswith(text)] + #tableNames = completion.tableNamesFromFromClause(line) + if segment == 'where': + completions = [] + for table_name in completion.tableNamesFromFromClause(line): + table = schemas[username].schema[table_name] + completions.extend(c['name'] for c in table.columns.values()) + completions.extend('%s.%s' % (table_name, c['name']) for c in table.columns.values()) + completions = [c for c in completions if c.startswith(text)] + if not segment: stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'" completions = self.select_scalar_list(stmt % (text)) @@ -1535,7 +1519,27 @@ if exact: seekpatt = '^%s$' % seekpatt return re.compile(seekpatt, re.IGNORECASE) - + + @options([make_option('-a', '--all', action='store_true', help="all schemas' objects"), + make_option('-i', '--immediate', action='store_true', help="Wait until refresh is done"), + make_option('-c', '--check', action='store_true', help="Don't refresh, just check refresh status")]) + def do_refresh(self, arg, opts): + '''Refreshes metadata for the specified schema; only required + if table structures, etc. have changed. ''' + (username, schemas) = self.metadata() + if opts.check: + print schemas.complete + return + if opts.all: + if opts.immediate: + self.perror("Don't combine --all and --immediate. It will take too long.") + return + schemas.refresh() + elif arg: + schemas.refresh_one(arg) + else: + schemas.refresh_one(username) + def _matching_database_objects(self, arg, opts): # jrrt.p* should work even if not --all # doesn't get java$options diff -r 20ea81921cd3 -r 1e3179b31a1e sqlpython/sqlpython.py --- a/sqlpython/sqlpython.py Fri Oct 09 12:07:21 2009 -0400 +++ b/sqlpython/sqlpython.py Fri Oct 09 23:08:50 2009 -0400 @@ -103,8 +103,7 @@ conn = {'conn': self.conn, 'prompt': self.prompt, 'dbname': eng.url.database, 'rdbms': rdbms, 'user': user, 'eng': eng, 'schemas': schemagroup.SchemaDict({}, - rdbms=rdbms, user=user, connection=self.conn, connection_string=arg, - minutes_between_refreshes = self.minutes_between_refreshes)} + rdbms=rdbms, user=user, connection=self.conn, connection_string=arg)} s = conn['schemas'] s.refresh_asynch() return conn