Open Stack

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