# HG changeset patch # User Catherine Devlin # Date 1288784837 14400 # Node ID c36e0aa695a44351ae5ceedb6ee28f3cde67afc1 # Parent 1feb3395744313b4bf2d1eb43c9e74bc04bdfbc3 column autocomplete working diff -r 1feb33957443 -r c36e0aa695a4 docs/source/intro.rst --- a/docs/source/intro.rst Mon Nov 01 22:31:49 2010 -0400 +++ b/docs/source/intro.rst Wed Nov 03 07:47:17 2010 -0400 @@ -58,6 +58,10 @@ sqlpython [username[/password][@SID]] ["SQL command 1", "@script.sql", "SQL command 2..."] +or + +sqlpython [--postgres|mysql] database-name username + Database connections can also be specified with URL syntax or with Oracle Easy Connect:: oracle://username:password@SID @@ -92,7 +96,4 @@ those databases has been very slight thus far. Help in testing and improving sqlpython's functions against those databases is welcome. Support for Microsoft SQL Server and sqlite will be available as soon as those databases are added to the Gerald project, and volunteers -for Gerald will benefit sqlpython as well. - -As of 1.7.0, sqlpython sessions can only "see" metadata within the named schema belonging -to the currently connected user. A fix for this is expected for 1.7.1. \ No newline at end of file +for Gerald will benefit sqlpython as well. \ No newline at end of file diff -r 1feb33957443 -r c36e0aa695a4 sqlpython/connections.py --- a/sqlpython/connections.py Mon Nov 01 22:31:49 2010 -0400 +++ b/sqlpython/connections.py Wed Nov 03 07:47:17 2010 -0400 @@ -235,9 +235,10 @@ curs = self.connection.cursor() dbapiext.execute_f(curs, self.all_object_qry, **identifier) return curs - def columns(self, target, opts): + def columns(self, target, table_name, opts): target = self.sql_format_wildcards(target) - identifier = {'column_name': target} + table_name = self.sql_format_wildcards(table_name) + identifier = {'column_name': target, 'table_name': table_name} if opts.all: identifier['owner'] = '%' else: @@ -312,6 +313,7 @@ JOIN information_schema.tables t ON (c.table_schema = t.table_schema AND c.table_name = t.table_name) WHERE ( (c.table_schema %(owner_op)s %(owner)S) OR (c.table_schema = 'public')) + AND c.table_name %(table_name_op)s %(table_name)S AND c.column_name %(column_name_op)s %(column_name)S""" source_qry = """SELECT r.routine_schema, r.routine_type, r.routine_name, 0 AS line, r.routine_definition FROM information_schema.routines r @@ -357,6 +359,7 @@ JOIN information_schema.tables t ON (c.table_schema = t.table_schema AND c.table_name = t.table_name) WHERE ( (c.table_schema %(owner_op)s %(owner)S) OR (c.table_schema = 'public')) + AND c.table_name %(table_name_op)s %(table_name)S AND c.column_name %(column_name_op)s %(column_name)S""" source_qry = """SELECT r.routine_schema, r.routine_type, r.routine_name, 0 AS line, r.routine_definition FROM information_schema.routines r @@ -413,6 +416,7 @@ FROM all_tab_columns atc JOIN all_objects ao ON (atc.table_name = ao.object_name AND atc.owner = ao.owner) WHERE atc.owner %(owner_op)s %(owner)S + AND atc.table_name %(table_name_op)s %(table_name)S AND atc.column_name %(column_name_op)s %(column_name)S """ source_qry = """SELECT owner, type, name, line, text FROM all_source diff -r 1feb33957443 -r c36e0aa695a4 sqlpython/pagila_test.txt --- a/sqlpython/pagila_test.txt Mon Nov 01 22:31:49 2010 -0400 +++ b/sqlpython/pagila_test.txt Wed Nov 03 07:47:17 2010 -0400 @@ -148,3 +148,34 @@ Foreign key "film_actor_actor_id_fkey": columns (actor_id) in table "actor" Foreign key "film_actor_film_id_fkey": columns (film_id) in table "film" Primary key "film_actor_pkey": (film_id,actor_id) +0:pagila@pagila> grep United country +BASE TABLE public.country + +country_id country last_update +---------- -------------------- ------------------- +101 United Arab Emirates /.*/ +102 United Kingdom /.*/ +103 United States /.*/ + +3 rows selected. + +0:pagila@pagila> grep united country +BASE TABLE public.country + +No rows Selected. + +0:pagila@pagila> grep -i united country +BASE TABLE public.country + +country_id country last_update +---------- -------------------- ------------------- +101 United Arab Emirates /.*/ +102 United Kingdom /.*/ +103 United States /.*/ + +3 rows selected. + +0:pagila@pagila> find -c country +BASE TABLE public.country.country +VIEW public.customer_list.country +VIEW public.staff_list.country diff -r 1feb33957443 -r c36e0aa695a4 sqlpython/sqlpyPlus.py --- a/sqlpython/sqlpyPlus.py Mon Nov 01 22:31:49 2010 -0400 +++ b/sqlpython/sqlpyPlus.py Wed Nov 03 07:47:17 2010 -0400 @@ -163,6 +163,10 @@ Looked for these programs: %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList]))) +class Dummy_Options(object): + all = None +dummy_options = Dummy_Options() + softwareLists = { 'diff/merge': [ ('/usr/bin/meld',"%s %s %s"), @@ -588,16 +592,10 @@ r'select\s+(.*)from', re.IGNORECASE | re.DOTALL | re.MULTILINE) def completedefault(self, text, line, begidx, endidx): - (username, gerald_schema) = self.metadata() - segment = completion.whichSegment(line) - text = text.upper() + segment = completion.whichSegment(line) if segment in ('select', 'where', 'having', 'set', 'order by', 'group by'): - 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 + completions = [c[-1] for c in self.current_instance.columns(text + '%', '%', dummy_options)] elif segment in ('from', 'update', 'insert into'): - # print schemas[username].table_names - # TODO: from postgres, these table names are jrrt.fishies, etc. completions = [t for t in schemas[username].table_names if t.startswith(text)] elif segment == 'beginning': completions = [n for n in self.get_names() if n.startswith('do_')] + [ @@ -1011,7 +1009,7 @@ def do_find(self, arg, opts): """Finds argument in source code or (with -c) in column definitions.""" if opts.col: - for (owner, object_type, table_name, column_name) in self.current_instance.columns(arg, opts): + for (owner, object_type, table_name, column_name) in self.current_instance.columns(arg, '%', opts): self.poutput('%s %s.%s.%s' % (object_type, owner, table_name, column_name)) else: for (owner, object_type, name, line_number, txt) in self.current_instance.source(arg, opts):