#27 Better Dynamic Completion

[K.-M. Hansche]

Hi Martin!

This adds even more dynamic completion. it went into beta-testing over here today and we are pleased right now. If you want some changes or have input, please tell me so I can set up a github-project for this extension.

Kind Regards

Niki

Readme:

SQSH – Better Dynamic Completion

This is an extension to sqsh’s dynamic completion that scans the active
sql-buffer and allows completion on all used tables/views/aliases.

Build

Un-targz this archive in sqsh/src and cd to sqsh_parser.
In sqsh_parser type make. This will generate a static library
sqsh_parser.a.

If you want to regenerate tsql.c and tsql.yy.c you need
flex and the lemon parser.

Apply use_parser.patch in sqsh/src/. And add sqsh_parser.a to the
LIBS-variable in the Makefile. E. g change

LIBS           = $(SYBASE_LIBS) $(X_LIBS) $(READLINE_LIBS)

to

LIBS           = $(SYBASE_LIBS) $(X_LIBS) $(READLINE_LIBS) sqsh_parser/sqsh_parser.a

Make as usual.

Usage

Afterwards sqsh can complete on aliases as well as on tablenames.
(Type select * from table t where t.<tab><tab>)

Documentation

The interface is described in sqsh_parser.h.

Todo

1. Change the way sqsh generates a list of all possible completions. Let it
   prefer tables used in the statement.
2. Make it thread-safe. (There’s a global tree structure of struct t_tdef's right now.)
3. Get rid of tdestroy which is GNU-only.

[Martin Wesdorp]

Hi Niki,

That looks very interesting indeed.

I am going on vacation for the next 3 weeks and I will have a closer look into this when I return.
As you may have noticed, I have just released sqsh-2.3 yesterday, so this new code would then go into the next release 2.4, or so. I would prefer an option to choose which method of completion to use, the old one or the new one, or maybe a mix of both if that would be possible at all.

Thanks,
Martin.

[K.-M. Hansche]

Hi Martin,

It is an extension to the existing code and falls back to the original behaviour if it doesn’t find anything. ((void) DynColnameLoad ( (tn!=NULL)?tn:objname, objname );
.)

However it would be easy to write an if around the new code in sqsh_generator.

I attach the up to date version, which got rid of a GNUism and added a way to iterate over all table/alias-pairs (not needed in sqsh).

Have a nice day,

Niki

[Martin Wesdorp]

Hi Niki,

At first I assumed you implemented a complete new readline tab completion function. Today I had a look at it and I understand now it is a enhancement to the current code to enable alias completion as well. This is great stuff. Thanks. I submitted the source code in CVS but still have to change Makefile generation in sqsh in order to create the archive.

Best regards.
Martin.

[Martin Wesdorp]

Hi Niki,

I have just released sqsh version 2.4 that contains your code. I also made some changes to sqsh_readline.c to enable cross database object column name auto-completion. For example:

[101] JUPITER.sa.tempdb.1> select * from master..syssrvroles r where r.<Tab><Tab>
r.logincount r.name r.password r.pwdate r.srid r.status
[101] JUPITER.sa.tempdb.1> select * from master..syssrvroles r where r.

It works like a charm. Hope you like it as much as I do. :-)

Thanks and best regards,
Martin.

[K.-M. Hansche]

I like it a lot!

Thank you very much

Niki

[Martin Wesdorp]

Hi Niki,

I noticed that the sqsh_parser is not handling the ANSI join syntax correctly. For example when I write the following query in the SQL buffer:

select dbname = d.name
, devname = sd.name
, segmap = case when u.segmap = 3 then 'Data'
when u.segmap = 4 then 'Log'
when u.segmap = 7 then 'Mixed'
else 'User segment'
end
, "size (MB)" = sum(u.size)/512
from master..sysdatabases d
inner join
master..sysusages u on d.dbid = u.dbid
inner join
master..sysdevices sd on u.vdevno = sd.vdevno
group by d.name, sd.name, u.segmap

then sqsh_parser recognizes alias 'd' but not the aliases 'u' and 'sd'. So I can only expand the columns of master..sysdatabases through the alias, but not the columns of sysusages and sysdevices. Would it be possible to add the ANSI join syntax with inner, left and right outer joins as well?

Thanks,
Martin.

[K.-M. Hansche]

Sorry, obviously nobody in my department writes "inner" joins. I attached a fix.

BTW, I’m still working on derived tables, but that might take a while.

Regards,

Niki