<html><body>
<p><font size="2" face="sans-serif">Jay,</font><br>
<br>
<font size="2" face="sans-serif">Thanks for the reply. I agree that changing the datamodel would be the ideal solution. But, to be honest, the scope of that change frightens me.</font><br>
<br>
<font size="2" face="sans-serif">How would you recommend that a change like this would be handled (in addition to the DB migration work)? We obviously cannot break existing codepaths that assume that the existing English key values would be returned from the DB.</font><br>
<br>
<font size="2" face="sans-serif">Is there an existing layer that would perform the mapping between the enum values in the DB and the String keys?</font><br>
<br>
<font size="2" face="sans-serif">Thanks,</font><br>
<font size="2" face="sans-serif">Steven Kaufer</font><br>
<br>
<tt><font size="2">Jay Pipes <jaypipes@gmail.com> wrote on 04/23/2014 02:56:14 PM:<br>
<br>
> From: Jay Pipes <jaypipes@gmail.com></font></tt><br>
<tt><font size="2">> To: openstack-dev@lists.openstack.org, </font></tt><br>
<tt><font size="2">> Date: 04/23/2014 02:56 PM</font></tt><br>
<tt><font size="2">> Subject: Re: [openstack-dev] [Globalization] REST API sorting by <br>
> status severity vs. alphabetical status key</font></tt><br>
<tt><font size="2">> <br>
> 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>
> > <a href="https://review.openstack.org/#/c/84451/">https://review.openstack.org/#/c/84451/</a>)<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>
> > <a href="https://github.com/openstack/oslo-incubator/blob/master/">https://github.com/openstack/oslo-incubator/blob/master/</a><br>
> openstack/common/db/sqlalchemy/utils.py#L62<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>
> <a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
> <br>
</font></tt></body></html>