<html><body>
<p><tt><font size="2">Adding [horizon] to the title line to get feedback from the Horizon team.</font></tt><br>
<br>
<tt><font size="2">Thanks,</font></tt><br>
<tt><font size="2">Steven Kaufer</font></tt><br>
<br>
<br>
<br>
<tt><font size="2">Hi Steven, thx for the detailed email. Some comments inline...<br>
<br>
On Wed, 2014-04-23 at 13:59 -0500, Steven Kaufer wrote:<br>
> I am trying to address the following use case:<br>
> <br>
> - Assume that the REST APIs support returning data based on a<br>
> user-defined sort key (assuming that this get approved:<br>
> </font></tt><tt><font size="2"><a href="https://review.openstack.org/#/c/84451/">https://review.openstack.org/#/c/84451/</a></font></tt><tt><font size="2">)<br>
> - UI contains a table showing items (servers, volumes, etc.) and their<br>
> status (as a sortable column) and uses pagination to get only a page<br>
> of data<br>
> - UI is translated into a non-English language<br>
> - User wants to sort the table by status<br>
> <br>
> In this case, the sorting by status is done against the English key<br>
> values in the database (active, error, etc.). The UI will then<br>
> translate the status values into the user's locale and (from the<br>
> user's perspective) the data will not be in sorted order -- thus<br>
> confusing and frustrating the user. Note that UI cannot do the sort<br>
> client-side since pagination is used so it the client only has a<br>
> sub-set of the total data.<br>
<br>
Indeed, this is a problem.<br>
<br>
> I have prototyped a "sort by case" solution that would allow status to<br>
> be sorted by severity. In SQL the "case" statement can be used to map<br>
> a string to an int and then sort the rows based on the int value (ie,<br>
> error=0, building=1, active=2, etc.). Using this approach, sorting by<br>
> status would result in an enum-like sort (based on severity) instead<br>
> of an alphabetical sort based on the English key values. This<br>
> solution allows enum-like data to be sorted in a consistent way across<br>
> all locales -- the solution is generic and can be applied to any<br>
> column where the values are a known set.<br>
> <br>
> The case processing would need to be done in the common paginate_query<br>
> function:<br>
> </font></tt><tt><font size="2"><a href="https://github.com/openstack/oslo-incubator/blob/master/openstack/common/db/sqlalchemy/utils.py#L62">https://github.com/openstack/oslo-incubator/blob/master/openstack/common/db/sqlalchemy/utils.py#L62</a></font></tt><tt><font size="2"><br>
> <br>
> This type of sort would not be the default behavior for a status<br>
> column (or any enum-like column) and the caller would need to specify<br>
> a unique sort direction key for it (ie, 'asc_case' or 'desc_case').<br>
> In theory, this type of sorting support could also be globally<br>
> enabled/disabled by a deployer (default would be disabled) to further<br>
> reduce impact.<br>
> <br>
> Lastly, I have some performance data and sorting the status by case<br>
> (vs. alphabetical) has a minimal impact on performance.<br>
> <br>
> Before I create a proposal for juno I wanted to get some early<br>
> feedback on the high-level approach. Please reply with feedback on<br>
> this solution.<br>
<br>
So, I feel that the above solution (while innovative certainly! :) ) is<br>
not actually addressing the underlying source of the problem here, and<br>
that is that statuses are stored in the database as English-language<br>
strings instead of integer code values in a lookup table.<br>
<br>
By addressing the underlying source of the problem -- by changing the<br>
instances.{vm,task,power}_state columns to an integer value and using a<br>
lookup table in the cases when translation from code to display is<br>
needed -- we both solve the problem of i18n sorting and increase the<br>
database performance, since queries on these state columns will use an<br>
index on a datatype with a much slimmer width.<br>
<br>
Best,<br>
-jay<br>
<br>
<br>
_______________________________________________<br>
OpenStack-dev mailing list<br>
OpenStack-dev@lists.openstack.org<br>
</font></tt><tt><font size="2"><a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a></font></tt></body></html>