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 [email protected] http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
