1. Beatrix Willius
  2. Valentina Database ADK
  3. Monday, June 08 2015, 06:24 AM
  4.  Subscribe via email
Got a bit of an edge case for sorting. The sorting works in Xojo, but not quite in Valentina.

The data is similar to:

a
a:::b
a:::b:::c

Now if the data is

a
a:::b
a:::b-2
a:::b:::c

sorting doesn't work. a:::b:::c should be following a:::b but it's after a:::b-2 . In Xojo I use the trick to replace ":::" with chr(1). This is why I asked for the chr function some days ago.

Why does't Valentina sort correctly? I even converted the data to hex because I can't view the data as binary and Xojo sorts the result just fine.

Here is the function I'm using:

CREATE OR REPLACE

FUNCTION PathSort(thePath STRING) RETURNS STRING BEGIN CASE

WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'inbox' THEN RETURN concat('1:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent' THEN RETURN concat('2:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent messages' THEN RETURN concat('2:', REPLACE(thePath, ':::', chr(1)));
WHEN thePath = 'Trash' THEN RETURN concat('3:', REPLACE(thePath, ':::', chr(1)));
WHEN thePath = 'Spam' THEN RETURN concat('4:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'views' THEN RETURN concat('5:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'Mailboxes' THEN RETURN concat('6:', REPLACE(thePath, ':::', chr(1)));
ELSE RETURN concat('7:', REPLACE(thePath, ':::', chr(1)));
END CASE END

The SQL using this function is:

SELECT RecID, MailboxPath, PathSort(MailboxPath) FROM mailbox ORDER BY 3

PS: I'd use the Xojo sort implementation if I had a good idea how to add the database specific sorting to a database-agnostic control that simply displays a cursor.

Regards

Beatrix Willius
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
Hi Beatrix,

1) So you try to use UDF which returns string ...
and do ORDER BY on such generated on fly data ...
which is not very good idea, if you have a lots of records and do this often.


2) It will be better define Method Field that is indexed. In this way, INDEX will be built only once and reused many times later.


3) a:::b-2 this is not minus, but just a symbol '-' in the string?


4)

sorting doesn't work. a:::b:::c should be following a:::b but it's after a:::b-2 .
In Xojo I use the trick to replace ":::" with chr(1).


I do not see why this trick is needed even in Xojo ?
What happens without this replacement?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Beatrix Willius Accepted Answer
Hi Ruslan,

1+2: no and no, the data in this table is a couple of hundred entries at most. I don't think that I have ever seen 1000 records. This may need to change in the future, but not right now.

3: some concrete data from myself

Mailboxes:::Importiert
Mailboxes:::Importiert:::Ablage
Mailboxes:::Importiert:::Realbasic
Mailboxes:::Importiert:::Test Mails

Then I added some mailboxes via importing to Mail. For instance I added:

Mailboxes:::Importiert-2

4: The trick is needed because of the binary values of ":" and "-". The chr(1) always sorts first and so the sub-mailboxes come before the mailbox with the similar name.

The correct sort order is

Mailboxes:::Importiert
Mailboxes:::Importiert:::Ablage
Mailboxes:::Importiert:::Realbasic
Mailboxes:::Importiert:::Test Mails
Mailboxes:::Importiert-2

Valentina sorts this

Mailboxes:::Importiert
Mailboxes:::Importiert-2
Mailboxes:::Importiert:::Ablage
Mailboxes:::Importiert:::Realbasic
Mailboxes:::Importiert:::Test Mails

This is the same sort order as without the chr(1) trick even though I have used it.

Mit freundlichen Grüßen/Regards

Trixi Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Jochen Peters Accepted Answer
The correct sort order is

Mailboxes:::Importiert
Mailboxes:::Importiert-2
Mailboxes:::Importiert:::Ablage
...
and VDB sort it properly in accordance with the IBM ICU library.
Similarly sorts SQLite, MySQL (MariaDB).
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Beatrix Willius Accepted Answer
The correct sort order is as you have shown. However, when I exchange the ":::" with a chr(1) then the correct sort order is

Mailboxes:::Importiert
Mailboxes:::Importiert:::Ablage
Mailboxes:::Importiert-2

because the chr(1) always comes first. Why does this work in Xojo and not in Valentina?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
Jochen Peters Accepted Answer
Yes, you are right
Maybe you can help

SELECT * FROM
(SELECT RecID, CASE list_nthitem(MailboxPath, 1, ':::')
WHEN 'inbox' THEN concat('1:', REPLACE(MailboxPath, ':::', ' '))
WHEN 'sent' THEN concat('2:', REPLACE(MailboxPath, ':::', ' '))
WHEN 'sent messages' THEN concat('2:', REPLACE(MailboxPath, ':::', ' '))
WHEN 'Trash' THEN concat('3:', REPLACE(MailboxPath, ':::', ' '))
WHEN 'Spam' THEN concat('4:', REPLACE(MailboxPath, ':::', ' '))
WHEN 'views' THEN concat('5:', REPLACE(MailboxPath, ':::', ' '))
WHEN 'Mailboxes' THEN concat('6:', REPLACE(MailboxPath, ':::', ' '))
ELSE concat('7:', REPLACE(MailboxPath, ':::', ' '))
END as MailboxPathSort
FROM mailbox ORDER BY 2)

Return

RecID MailboxPathSort
1 6:Mailboxes Importiert
2 6:Mailboxes Importiert Ablage
3 6:Mailboxes Importiert Realbasic
4 6:Mailboxes Importiert Test Mails
5 6:Mailboxes Importiert-2

This result you want to get?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 5
Beatrix Willius Accepted Answer
Thanks, I'll try that out. What is the difference to my solution?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 6
Jochen Peters Accepted Answer
What is the difference?
I exchange the ":::" with a <space>
The function <list_nthitem ()> is enough to cause once.
Perhaps your solution through the UDF is more efficient since it does not create a temporary table

Update:
Sorry, nested query is extra
It leads to the creation of the temporary table.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 7
Beatrix Willius Accepted Answer
Ah, I hadn't seen that. This doesn't work because mailboxes also could contain spaces. Now I tried

SELECT * FROM
(SELECT RecID, CASE list_nthitem(MailboxPath, 1, ':::')
WHEN 'inbox' THEN concat('1:', REPLACE(MailboxPath, ':::', chr(1)))
WHEN 'sent' THEN concat('2:', REPLACE(MailboxPath, ':::', chr(1)))
WHEN 'sent messages' THEN concat('2:', REPLACE(MailboxPath, ':::', chr(1)))
WHEN 'Trash' THEN concat('3:', REPLACE(MailboxPath, ':::', chr(1)))
WHEN 'Spam' THEN concat('4:', REPLACE(MailboxPath, ':::', chr(1)))
WHEN 'views' THEN concat('5:', REPLACE(MailboxPath, ':::', chr(1)))
WHEN 'Mailboxes' THEN concat('6:', REPLACE(MailboxPath, ':::', chr(1)))
ELSE concat('7:', REPLACE(MailboxPath, ':::', chr(1)))
END AS MailboxPathSort
FROM mailbox ORDER BY 2)

and this finally works. So what is the difference between using direct SQL and the procedure?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 8
Ruslan Zasukhin Accepted Answer
Fast guess -- may be LOCALE ...

I think it is a good idea to put into Mantis
a) DB on which you test
b) procedure in this db
c) query from your last comment
d) ref to this thread

So Ivan will be able to DEBUG this to see where exactly difference is.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 9
Beatrix Willius Accepted Answer
Done: http://www.valentina-db.com/bt/view.php?id=7138

The 500k limit of file uploads for Mantis is very dated. It also wasn't possible to unselect my upload.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 10
Ruslan Zasukhin Accepted Answer
Ivan Smahin:

Well, I think I finally get your problem.

Yes, you should replace '::' with the smallest char. But it must be "sortable" symbol. The collator ignores some symbols for obvious reasons (it is not binary sort order) Probably it will work out with some "binary collation setting" but I suggest to choose smallest meaningful symbol like chr(10) (in assumption that your data has no such a symbol).
----------

Issue in Mantis will be closed. No changes.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 11
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories