Item1228: Sort table with IP adresses in column does not work

Hello,

if I have a simple table like this

IP	Room
9.152.50.6	1
9.152.50.23	2
9.152.50.11	3
9.152.52.7	4
9.152.52.45	5
9.152.52.144	6

and I try to sort this by IP it always shows the same order (as it was typed). I see nothing in the log files. This worked under TWiki 4.0.4.

-- TobiasVonDerKrone - 09 Mar 2009

> This worked under TWiki 4.0.4.

Really? It has been an in-frequently asked question for a while now (e.g., TWiki:Support/FormattingIp). Perhaps your data used to be zero-padded to three figures in each quad?

You could finesse this with a client-side sort of the fully-rendered table. E.g., http://www.workingwith.me.uk/articles/scripting/standardista_table_sorting

-- SeanMorgan - 10 Mar 2009

The bug is in TablePlugin, and is due to a rather simplistic approach to detecting numbers for a numerical sort.

-- CrawfordCurrie - 10 Mar 2009

In the meantime, here's a work-around to zero-pad each entry in the dotted quad to three digits so the addresses can be sorted logically. without additional javascript.

The formula is a bit cumbersome, so I shown it built up in steps. "Angle-brackets" (<>) were used here simply to make whitespace characters visible.

1. Get the value in column 1:
   • Set MYFORMULA = $T(R$ROW():C$COLUMN(-1))
2. Convert "dotted quad" form to CSV so it can be interpreted as a list:
   • Set MYFORMULA = $TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma)
   • Sample output at this step: < 9,152,50,6 >
3. Trim it:
   • Set MYFORMULA = $TRIM($TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma))
   • Sample output at this step: <9,152,50,6>
4. Prepend 'OO' to each list item, and then replace all but the last three characters with null.
   The SpreadsheetPlugin interprets some numbers pre-pended with zero as octal (i.e., 0[0-7]*), which messes with the calculation of LENGTH. As a work-around, temporarily used the letter 'O' instead:
   • Set MYFORMULA = $LISTMAP($REPLACE(OO$item,1,$EVAL($LENGTH($item)-1),),$TRIM($TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma)))
   • Sample output at this step: <OO9, 152, O50, OO6>
5. Join the list with dots, and change letter O to number 0.
   • Set MYFORMULA = $TRANSLATE($LISTJOIN(.,$LISTMAP($REPLACE(OO$item,1,$EVAL($LENGTH($item)-1),),$TRIM($TRANSLATE($T(R$ROW():C$COLUMN(-1)), ., $comma)))),O,0)

IP	Calc
9.152.50.6	<009.152.050.006>
9.152.50.23	<009.152.050.023>
9.152.50.11	<009.152.050.011>
9.152.52.7	<009.152.052.007>
9.152.52.45	<009.152.052.045>
009.152.052.144	<009.152.052.144>

The last entry shows that this method can handle the case where only some of the data is already zero-padded.

You can then use a style tag to hide the redundant data, like so: <div style=display:none>IP</div>

-- SeanMorgan - 10 Mar 2009

If I add

<style type="text/css">
<!--
.foswikiFirstCol {display:none;}
-->
</style>

to a page all first columns of all tables on that page are hidden.

How do I add individual table styling on a per table basis by using TABLE attributes?

-- FranzJosefGigler - 11 Mar 2009

Well, you can't (TWiki:Support.TablePluginSettngsOverrideJavaScript):

But we might also think of a way to add cell-specific styling to TablePlugin.

-- ArthurClemens - 07 Sep 2008

-- SeanMorgan - 13 Mar 2009

This sorting did'nt work in TWiki 4.0.4 I asked the previous Wiki Admin who told me he changed the Core.pm of the TablePlugin. Here is a diff:

62c62
<         'NUMBER', 'number', 'UNDEFINED', 'undefined', 'IP', 'ip'
---
>         'NUMBER', 'number', 'UNDEFINED', 'undefined'
509c509
<     $text = _stripHtml( $text );
---
>     $text = _stripHtml($text);
513d512
<     my $ip   = undef;
515c514,515
<         return (0,0,0,1);
---
>         $num  = 0;
>         $date = 0;
518c518,521
<     if ( $text =~ m|^\s*([0-9]{1,2})[-\s/]*([A-Z][a-z][a-z])[-\s/]*([0-9]{4})\s*-\s*([0-9][0-9]):([0-9][0-9])| ) {
---
>     if ( $text =~
> m|^\s*([0-9]{1,2})[-\s/]*([A-Z][a-z][a-z])[-\s/]*([0-9]{4})\s*-\s*([0-9][0-9]):([0-9][0-9])|
>       )
>     {
524c527,529
<     elsif ( $text =~ m|^\s*([0-9]{1,2})[-\s/]([A-Z][a-z][a-z])[-\s/]([0-9]{2,4})\s*$| ) {
---
>     elsif ( $text =~
>         m|^\s*([0-9]{1,2})[-\s/]([A-Z][a-z][a-z])[-\s/]([0-9]{2,4})\s*$| )
>     {
532,534d536
<     elsif ( $text =~ /^\s*(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\s*$/ ) {
<         $ip = sprintf("%03d%03d%03d%03d",$1,$2,$3,$4);
<     }
548c550
<     return( $num, $date, $ip, 0 );
---
>     return ( $num, $date );
746,748c748,749
<     my $isDate        = 0;
<     my $isNum         = 0;
<     my $isIP          = 0;
---
>     my $isDate        = 1;
>     my $isNum         = 1;
751,752d751
<     my $ip            = '';
<     my $null          = 0;
759,768c758,763
<         ( $num, $date, $ip, $null ) = _convertToNumberAndDate( $row->[$col]->{text} );
<         if (! $null) {
<             $isDate = 1 if( defined( $date ) );
<             $isNum  = 1 if( defined( $num ) );
<             $isIP  = 1 if( defined( $ip ) );
<             if( $isDate + $isNum + $isIP > 1) {
<                 return $columnType{'TEXT'};
<             }
<         }
<         $row->[$col]->{date} = $date;
---
>         ( $num, $date ) = _convertToNumberAndDate( $row->[$col]->{text} );
> 
>         $isDate = 0 if ( !defined($date) );
>         $isNum  = 0 if ( !defined($num) );
>         last if ( !$isDate && !$isNum );
>         $row->[$col]->{date}   = $date;
770d764
<         $row->[$col]->{ip} = $ip;
780,782d773
<     elsif ( $isIP ) {
<         $type = $columnType{'IP'};
<     }

Maybe it helps to improve the plugin.

-- TobiasVonDerKrone - 16 Mar 2009

While we're at it, it would be nice to be able to define a custom sort order on the fly, like I can do in MS Excel, or in Perl with <=> (which always reminds me of an ASCII art representation of a Tie Fighter...).

For example, priorities of 'High', 'Medium', 'Low' don't sort well either.

-- SeanMorgan - 20 Mar 2009

See proposal GeneralSortingMechanism

-- ArthurClemens - 20 Mar 2009