Exact word matches don't get sorted first

elezar

I'm having problems with SQL Prompt 3, because something that matches exactly what I've typed isn't moved to the top of the Candidate List. I have space as one of the completion keys, as this is how I'm used to working in Visual Studio, and other editors that have auto-complete.

This is especially causing a problem for me with the FROM and ON keywords. I have several columns that start with "from", and a table that starts with "on". So, when I type

SELECT ColumnA, ColumnB FROM

it has the FromAddress column at the top of the Candidate List. Once I hit space, it auto-completes my "FROM" to FromAddress, and I have to go back and change it. Similarly, with ON, when I type

SELECT * FROM Table1 JOIN Table2 ON

it has the OnCall table at the top of the Candidate List, auto completes to that, and I have to go back and change it.

To me, it seems there are 2 problems with this. First, when there's something in the list that exactly matches what I've typed, that should be moved to the top of the list. For short words, especially, I'll usually have the word typed out before I even think about using auto-completion, so I don't want it to be changed to something else.

The other problem is that it doesn't seem to determine what category of candidates are most likely to be used, very well. Since I don't have a comma before the word FROM, then it seems pretty unlikely that I would be adding another column name there, and extremely likely that I would be putting FROM there, so it would make more sense for it to be at the top. The same with ON. After the second table name in the Join statement, ON is almost always the next word, so should be at the top of the list. I realize that the candidate sort ordering is somewhat subjective, but I don't think I'm in the minority at least with these examples.

I know that I can increase the Candidate List delay, so it doesn't display before I type the space after from/on. However, I type pretty fast, so I need the delay low enough that it shows up before I'm done typing longer words as well. Also, I would lose the auto-capitalization from SQL Prompt, in that case.

Don't get me wrong. Version 3 is leaps and bounds better than 2 was (I love the concept, but I couldn't even stand 2 long enough to keep it for the entire trial period). For the most part, I've loved what I've seen of it in the past 2 days, and while I do agree with some of the other request I've seen in these forums, I can live without them. However, this problem that I've listed is becoming a deal breaker for me. It's actually causing me more work, and slowing down my work-flow, because I keep having to go back and undo what SQL Prompt did.

Wow, this ended up being a long post. Sorry about that, but I wanted to make sure my issue was clear.

Bart Read

Hi Jesse,


Don't worry about the long post - we're aware of the issue and have been discussing exactly how to address it, and be assured, we have a cunning plan (or actually two cunning plans) to put it right. I suspect this will be in the first or second point release. If it's in the second it will be because we've had to get the first point release out very quickly to fix some horrendous problem we're as yet unaware of, otherwise it'll probably be the first.

I think you're right about candidate list ordering: it is very subjective, and even in the cases you've listed it's not as straightforward as it first appears. We try to pop up the right stuff, but we are also trying to avoid being too prescriptive as this makes SQL Prompt feel very restrictive in use. I think there's definitely room for improvement, but we just ran out of time.

I think it's fair to expect that when you type SELECT, FROM, GO etc and hit SPACE or ENTER or TAB then what should be inserted is the closest matching candidate, and this is certainly something we'll be looking to address very soon.

Last point, and then I'm taking a week off: you can increase the completion popup delay without losing your auto-casing behaviour. The two are entirely independent so don't be afraid of changing this setting.


Thanks,
Bart

elezar

Alright, thanks for letting me know. I'll keep an eye out for the first couple point releases, and will look at evaluating it again, once this is resolved.

And, it is good to know that the capitalization does still happen even if the candidate list doesn't display. However, that doesn't seem to be quite perfect, either. SQL Keywords are getting capitalized with no problems, but db objects don't get changed to match how they're actually capitalized within the db itself, unless the candidate list pops up.

When testing that, I realized that the auto-aliasing doesn't work, either, without the candidate list coming up. Plus the fact that even with the delay set lower, there are still times when I become distracted while typing it in or something and end up taking longer than the delay, and then have to deal with the same problem.

So, I really wasn't able to get it to a work in a way that was really helpful (Or at least not harmful) to my workflow. As I said, I'll keep an eye out for when this is fixed, though, and try it again.

Thanks for the response.

Tilman

Hi,

SQL Keywords are getting capitalized with no problems, but db objects don't get changed to match how they're actually capitalized within the db itself, unless the candidate list pops up.

I've added a suggestion to our list.

When testing that, I realized that the auto-aliasing doesn't work, either, without the candidate list coming up.

Likewise.

Regards,

Tilman

Bart Read

Hi there,


We've added a fix to SQL Prompt 3.1 to ensure exact matches are now preferred. You can grab an early access release by following the instructions at:

http://www.red-gate.com/MessageBoard/vi ... php?t=4487

Hope that helps.


Thanks,
Bart