Open Stack

Jay,

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.

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.

Is there an existing layer that would perform the mapping between the enum
values in the DB and the String keys?

Thanks,
Steven Kaufer

Jay Pipes <jaypipes at gmail.com> wrote on 04/23/2014 02:56:14 PM:

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