[Bug 53657] New: Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

Bug ID: 53657
           Summary: Natural version sort
           Product: WineHQ Apps Database
           Version: unspecified
          Hardware: Other
                OS: other
            Status: UNCONFIRMED
          Severity: minor
          Priority: P2
         Component: appdb-unknown
          Assignee: [e-mail]
          Reporter: [e-mail]

Please use it to show applications, e.g.

https://appdb.winehq.org/objectManager.php?sClass=application&iId=3156
https://appdb.winehq.org/objectManager.php?sClass=application&iId=1035

Both sorted incorrectly.

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #1 from Artem S. Tashkinov  ---
If you're using MariaDB 10.7 or higher, then:

https://gitlab.winehq.org/winehq/appdb/-/blob/master/include/application.php

Line 118:

$sQuery = "SELECT versionId FROM appVersion WHERE
                appId = '?'$sExtraTerms ORDER by versionName";

Should become

$sQuery = "SELECT versionId FROM appVersion WHERE
                appId = '?'$sExtraTerms ORDER by NATURAL_SORT_KEY(versionName);

No idea how to do that in MySQL.

This solution may not work:
https://www.copterlabs.com/natural-sorting-in-mysql/

Maybe you could use PHP's natsort()  instead.

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

Joerg Schiermeier  changed:

What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[e-mail]

--- Comment #2 from Joerg Schiermeier  ---
Do you mean this:
https://stackoverflow.com/a/17626898

Demo:
http://sqlfiddle.com/#!9/efb0d6/2

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #3 from Joerg Schiermeier  ---
A fast shot into the night:
===========================

This comes closer but still needs work:

TEST data:

drop table if exists app;
create table if not exists app
(
    Version char(40),
    Description char(100),
    Latest_Rating char(10),
    Latest_Wine_version_tested char(10),
    Test_results int,
    Comments int,
    PRIMARY KEY (Version)
);

insert into app values
('1.0 (DOS)', 'Acrobat Reader 1.0 for DOS','Gold','1.5.12',4,0),
('10.x','Adobe Reader X','Silver','1.7.55',18,0),
('11.x','Introduces Extended tools, improved PDF Annotator, improved
eSignatures.','Silver','3.11',26,0),
('2.x','Acrobat Reader 2','Garbage','5.0',3,0),
('3.x (16-bit)','Acrobat Reader 3 for Windows 3.1','Bronze','1.3.16',7,0),
('3.x (32-bit)','Acrobat Reader 3 for Windows 95 and
later','Platinum','1.9.14',10,0),
('4.x','Acrobat Reader 4','Platinum','1.9.14',8,0),
('5.x','Acrobat Reader 5','Gold','1.9.14',6,0),
('6.x','Adobe Reader 6 for Windows 98SE, NT4 SP6 and
later','Platinum','1.9.14',5,0),
('7.x','Adobe Reader 7 for Windows NT4 SP6 and later','Silver','5.0',7,0),
('8.x','Adobe Reader 8.  Later versions include support for Windows
Vista.','Platinum','1.9.14',14,0),
('9.x','Adobe Reader 9','Platinum','2.0.2',9,0),
('DC','Adobe Acrobat Reader DC for Windows 7 and later','Gold','7.0',16,17);

Here is the query adapted to the result table - not original the code from
AppDB:
select * from app order by cast(substring(version,locate(' ',version)+1) as
signed), substring(version,-1);

See this:
http://sqlfiddle.com/#!9/e1c8c8/4/0

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #4 from Artem S. Tashkinov  ---
(In reply to Joerg Schiermeier from comment #2)
> Do you mean this:
> https://stackoverflow.com/a/17626898
>
> Demo:
> http://sqlfiddle.com/#!9/efb0d6/2

I mean you could do it however you see fit ;-)

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #5 from Joerg Schiermeier  ---
Created attachment 73207
  --> https://bugs.winehq.org/attachment.cgi?id=73207
The SQL command results in this table

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

Joerg Schiermeier  changed:

What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #73207|The SQL command results in  |The SQL command from #3
        description|this table                  |results in this table

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #6 from Artem S. Tashkinov  ---
(In reply to Joerg Schiermeier from comment #5)
> Created attachment 73207 [details]
> The SQL command from #3 results in this table

DC looks out of order, otherwise it's fine.

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #7 from Joerg Schiermeier  ---
New URL to SQL-fiddler:

https://sqlfiddle.com/mariadb/online-compiler?id=024d4cc5-4a7e-4ffa-a14a-ffe...

[Bug 53657] Natural version sort

[WineHQ Bugzilla]

https://bugs.winehq.org/show_bug.cgi?id=53657

--- Comment #8 from Joerg Schiermeier  ---
This may work:

https://sqlfiddle.com/mariadb/online-compiler?id=c59bd961-7f72-4d07-b1b1-952...

The used SQL string:

select * from app order by cast(version as signed), substring(version,-1);