sorting of tablenames

The newest revisions of Heidi have a subtle difference with older versions: the table names in the right pane are now sorted without regard for the case. In older versions it would first order the tablenames with a capital and then the lowercase ones. now they are mixed.
I'm much more happy with the old way as i tended to give camelcase names to my own tables and lowercase names to tables of third-party software. That way they were seperated in heidi and easy to distinguish. Is it possible to switch back to the old sorting somehow? (the left pane still does it this way, btw)

The reason why you saw this order in an older version is that it was not sorted by Heidi at all, only by the server which gives the list somehow sorted in the way you want it to be. HeidiSQL uses the Delphi method AnsiCompareText for comparing two nodes which has a slightly different logic. I think I'll implement the proposed ordering logic as the default ascending mode, as it is what the server already does, and Heidi should imitate that.

Uhm, I have a patch which does exactly what you want but I'm hesitating to commit that. It would also affect the way other columns than "Table name" will be sorted:
ABC1
ABC3
abc2

Even if that would be somehow wanted for the table name column in ListTables, would it be also wanted for the "Comment" column if you click on its header? I don't think so. The Windows Explorer has exactly the same sorting logic than Heidi does currently, it's case insensitive:
ABC1
abc2
ABC3

The latter logic looks somehow better and more intuitive to me. Opinions?

I just thought about such an ordering logic:
ABC
abc
DEF
def

So, a small "a" gets sorted after the upper "A" but before the upper "B" . Quite tricky to implement though.

Nah, forget my last posting. That's the current behaviour of AnsiCompareText . Nothing new.

I think I'll implement the proposed ordering logic as the default ascending mode, as it is what the server already does, and Heidi should imitate that.

That would be great

The latter logic looks somehow better and more intuitive to me. Opinions?

Personally i still prefer the default server setting: first uppercase, then lowercase:

A
B
C
X
Y
Z
a
b
c
x
y
z

This will probably only happen when tables of two different applications are put together in the same database and in that case the distincition can improve readability.
But, someone else might argue differently, so maybe the best option would be to make the sorting configurable?

Even if that would be somehow wanted for the table name column in ListTables, would it be also wanted for the "Comment" column if you click on its header? I don't think so.

i don't recal every having sorted the comments so i'm not sure what is intuitive for me here :)

Hello, it's my first post here, so at the beginning I would like to say that i really like this application and I use it much at work and home. But.. (and now we go to the point)

In my job I have to deal with many tables in database (now it's 589). They're all named like this: "2009_02_16_id_1334d" (year_month_day_id_idnr). In 5.1 version sorting was proper, but in v6 there's something really annoying. I have sorting like this (I will short it some kind):

2007_10_15_id_743 (...)
2008_12_18_id_1289 (...)
2009_12_03_id_1693 (...)
2010_11_25_id_2035 (and now it's something strange)
2007_05_18_id_606_a59 (...)
2008_12_18_id_1289_2 (...)
2009_12_04_id_1693_2

and so on..

As you see problem is in "stages" ("waves" of project). Every table, which is second or next wave of project (has _2 etc at the end) is sorted in bad way. Would be really, really good if tables could sort in proper way, like this:

2007_05_18_id_606_a59 (...)
2007_10_15_id_743 (...)
2008_12_18_id_1289 (...)
2008_12_18_id_1289_2 (...)
2009_12_03_id_1693 (...)
2009_12_04_id_1693_2 (...)
2010_11_25_id_2035

Thanks for thinking this out.

so maybe the best option would be to make the sorting configurable?

I agree with that

PS: Is it possible to make database filter work "inside" names? You know, when I need db for project id=1693 it whould be much easier and quicker to filter it by typing 1693, then table with name "2009_12_03_id_1693" (and other waves) should appear.

Cheers!

Confirmed. This sorting logic is caused by the fact that HeidiSQL compares these as numbers because the first character is a number. Guess such strings should not be handled as numbers, only when they're looking like byte strings, fx "12.3 KB".

Ok, but what's the conclusion?
And what about my PS? If filtering would work as I wrote, tables sorting wouldn't be necessary... What's easier to implement?

Table sorting is surely buggy now and should be fixed, even if filtering would help in some way. The conclusion is for me to fix that stuff somehow.

Gues you want filters to apply to table names or?

Hmmmm I don't know why, but now filtering doesn't work in any way. When I write "2010" in my database's filter input I don't get any "2010_..." tables on the list. I tried with letters too, but everything I try to filter returns empty list and make input red. (6.0.0.3621)

For now filtering should fix my problems. Besides, I will use this feature even if sorting would be proper, because it's quicker than scrolling 500 tables :)

Soooo, if it's possible, think about "global" filtering table names, please. It should work as I wrote earlier, for example when someone want to find table with "users" and apply that filter, in tables list should appear forum_users, blog_users and others, not only users_something.

But maybe it's supposed to work that way now? I don't know, as I said, filtering now does nothing :(

Damn, I'm dumbass - it's filtering of databases, not tablenames

So yes - I really would like to see input for filtering tablenames :)

But tables are already filtered using Ctrl+Alt+F - did you see that?

No, shame on me. Nevermind that.

Hi anse,

I've the same problem like Wirone. We have a platform called 3min, that has table prefixes "3min_". Since Version 6 all these tables seem to be unsorted. I think that's because of the leading number. It would help a lot if you could fix this or revert to the old sorting.

Heidi is the best sql tool I've ever seenThanks for that!

Robert

Hi,

same here, I am using v6, and have tables with leading numbers, because of similar names
1_a_b
1_a_c
1_d_f
2_a_e
2_d_g
2_h_i

but on the left pane I have sorting with no visible logic, in my example like this:

1_r_s
1_r_w
1_r_p
1_r_q1
1_r_q2

So what gonna happen next regarding this topic?
Thanks.

The left tree should sort tables in the same way as the right panel does, since I recently introduced a sorting function which is called from both sides. If it's differently sorted I guess you have an old build - please update (Help > Check for updates)

Hello Anse,

No, it's not a old version problem. I have build 3733 (newest) and list of table names is not sorted properly on both, left and ride side.

for example:

2010_08_03_id_1930
2010_08_04_id_1931
2010_08_05_id_1932_comments
2010_08_05_id_1932
2010_08_06_id_1933
...
2011_03_15_id_2128
2011_03_15_id_2129
...
2010_08_09_id_1935_2
2010_08_10_id_1936_2
...

As you see, 2 last tables (and many, many others in our database) are far behind newest tables (2011_03_15) on the list. It's strange, because they have 2010 in front and should be where other tables from this year are.

I recognize this is a problem with last part of table name. if it's _letters it's listed properly (like 2010_08_05_id_1932_comments above). But if last part is numeric (like _2) it "flies away".

I use filter panel, so it's not a big problem for me, but it is not that way you said. Sorting simply works wrong.

> list of table names is not sorted properly on both, left and ride side

Well, at least both sides are equally sorted - that's what doinkxs said differently.

The sorting logic behind both list and tree does some magic to get datetime columns and KB/MB columns sorted as you would expect it. What it does is to take all numbers in it and compare them as one huge number:

2010_08_10_id_1936_2 is compared as 2010081019362
2011_03_15_id_2129 is compared as 201103152129

I had started to tweak that a bit so numbers are just taken as long as I don't hit a letter. But I guess that would also be not sufficient from your point of view, would it?

Hmmm, so with that tweak names would be sorted only by "2010_08_10" part? Then it would be better a lot, but as I said, it's not really problem for me since you enlighted me with filter panel ;)

Two questions:
1) how Heidi sort tables when every table name doesn't have numbers, but contains only letters?
2) why don't you use external libraries to perform native sorting (like does natsort in php)? Is there any which could do the thing?

1) If both values at a comparison action is evaluated to a number, the above described comparision is used. If one does not contain any number, there is simple call to CompareText() which compares just case insensitively.
2) External libraries for such a simple thing is overkill. This issue is rather cosmetic, and external libraries can have quite some impact on maintenance effort

Sorry Anse, that's definitly not cosmetic.
We have a big software with 82 tables. Because of a table prefix with a leading number (that we can't change) they are all mixed up. It's very hard to switch between these tables even if they are named nearly similar, cause they mix up over the hole list.
The only solution for me is to have one instance with version 5 :-(
That happens only because of one single leading number. I think there should be a better way of sorting so that we can enjoy all your very good work since version 5.

Please make suggestions for some implementation.

Here's the current code:

function CompareAnyNode(Text1, Text2: String): Integer;
var
Number1, Number2 : Extended;
begin
Result := 0;
// Apply different comparisons for numbers and text
if (StrToIntDef(Copy(Text1, 0, 1), -1) <> -1) and (StrToIntDef(Copy(Text2, 0, 1), -1) <> -1) then begin
// Assuming numeric values
Number1 := MakeFloat(Text1);
Number2 := MakeFloat(Text2);
if Number1 > Number2 then
Result := 1
else if Number1 = Number2 then
Result := 0
else if Number1 < Number2 then
Result := -1;
end else begin
// Compare Strings
Result := CompareText(Text1, Text2);
end;
end;

Note that MakeFloat() is doing the number stuff here. MakeFloat internally calls CleanupNumber, which contains the discussed stuff from above:

function CleanupNumber(Str: String): String;
var
i: Integer;
HasDecimalSep: Boolean;
begin
// Ensure the passed string contains a valid number, which is convertable by StrToFloat afterwards
// Return it as string again, as there are callers which need to handle unsigned bigint's somehow -
// there is no unsigned 64 bit integer type in Delphi.
Result := '';
// Unformatted float coming in? Detect by order of thousand and decimal char
if ((Pos(',', Str) > 0) and (Pos(',', Str) < Pos('.', Str)))
or ((Pos('.', Str) > 0) and (Pos('.', ReverseString(Str)) <> 4))
then begin
Str := StringReplace(Str, '.', '*', [rfReplaceAll]);
Str := StringReplace(Str, ',', FormatSettings.ThousandSeparator, [rfReplaceAll]);
Str := StringReplace(Str, '*', FormatSettings.DecimalSeparator, [rfReplaceAll]);
end;
HasDecimalSep := False;
for i:=1 to Length(Str) do begin
if CharInSet(Str[i], ['0'..'9', FormatSettings.DecimalSeparator]) or ((Str[i] = '-') and (Result='')) then
begin
// Avoid confusion and AV in StrToFloat()
if (FormatSettings.ThousandSeparator = FormatSettings.DecimalSeparator) and (Str[i] = FormatSettings.DecimalSeparator) then
continue;
// Ensure only 1 decimalseparator is left
if (Str[i] = FormatSettings.DecimalSeparator) and HasDecimalSep then
continue;
if Str[i] = FormatSettings.DecimalSeparator then
HasDecimalSep := True;
Result := Result + Str[i];
end;
end;
if (Result = '') or (Result = '-') then
Result := '0';
end;

I wrote no Delphi for long years, but I'll try

The main problem seems to be, that funtion CompareAnyNode decides it's comparison mode by the type of the first character. So if the leading char is a number (that's my case with "3min_...") everything would be compared numeric. But the result of MakeFloat would be the same for every string and 3min_... is a string, not a float. That's why result is 0 every time and nothing will be sorted. Do you see, what I mean?

May be, thinking more simple would help:

function CompareAnyNode(Text1, Text2: String): Integer;
var
Number1, Number2 : Extended;
begin
Result := 0;
// Apply different comparisons for numbers and text
try
//trying comparison of floats (and implicitly integer)
//if one TextX can'T be converted, we fall back to string comparison in except section
Number1 := StrToFloat(Text1);
Number2 := StrToFloat(Text2);
if Number1 > Number2 then
Result := 1
else if Number1 = Number2 then
Result := 0
else if Number1 < Number2 then
Result := -1;
except
// Compare Strings
Result := CompareText(Text1, Text2);
end;
end;

I'm not sure if it trades every possible variety of input data and I don't know if that works, because I can't test it, but may be ...

I'll be offline since monday, but I'm looking forward to your response ;-)

Robert