I would like to convert Select Lists and Grids to a new data storage mechanism. Right now we have (and have always had) some serious data integrity problems. Right now you may experience data loss or result fragmentation by any of the following situations:

- Not using key|value pairs and renaming a select list option.
- Not using key|value pairs and renaming a grid option.
- Reordering or adding any questions to grid lists.

To solve these problems I'd like to suggest the following changes:

1. Any option that is not given an explicit "key" is automatically assigned a numeric key. We convert all existing select list options without keys to use numeric values in the database. Display of values is of course unaffected.
2. We use the Options Element module (currently part of Form Builder, see demo: http://quicksketch.org/demos/form-builder-example) to hide these numeric keys from users that don't even know that they've been given numeric keys.
3. We convert the entire data storage of Webform's "no" column from being an auto-counted column to a varchar key. So rather than having keys like 0, 1, 2, etc. for every grid question, we'd actually put the grid questions as keys in the "no" column. This would allow reordering of grid options.

I expect this change (especially the database schema change) is going to run into some unexpected problems, but I think this is a crucial set of changes to make Webform's data structure resistant to inadvertent user changes.

CommentFileSizeAuthor
#3 webform_data_upgrade7.patch29.25 KBquicksketch
#2 webform_data_upgrade.patch33.04 KBquicksketch

Comments

quicksketch’s picture

quicksketch
he/him
commented

We should also incorporate #301428: Grid Component: add key/value pairs to Questions text area during these changes.

quicksketch’s picture

quicksketch
he/him
commented
Version: » 6.x-3.x-dev
StatusFileSize
new webform_data_upgrade.patch33.04 KB

This patch makes a start on this new schema. It does not yet introduce the use of the Options Element module (which I've split into a separate project). I don't really want to make Webform dependent on Options Element, but I do want to ensure that we never have problems with renaming labels.

So to fix this short-term, all options in select lists and grids now require a key in the key|value syntax. When we add in Options Element (which will remain optional), then it will be responsible for automatic key names. So in short:

- Use Options Element if you don't want your users to worry about keys.
- Don't use Options Element if you're anti-extra-modules. Or you always specify keys anyway already.

This patch makes some major database changes, but opens up all kinds of possibilities like #284738: Possible for Grid to have text field? A new testGrid component..

quicksketch’s picture

quicksketch
he/him
commented
Status: Active » Fixed
StatusFileSize
new webform_data_upgrade7.patch29.25 KB

I've committed the above patch to Drupal 6 and this patch to Drupal 7. This should open the door for #709854: Convert date component database storage to use ISO 8601 strings (similar to timestamps) and #712352: Convert time component to use seconds in the day (timestamps), and finish up our data migrations for Webform 3.

quicksketch’s picture

quicksketch
he/him
commented

I opened a followup for integration with options element: #743428: Add support for Options Element module.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

roball’s picture

roball commented
Status: Closed (fixed) » Active

Hm, when updating Webform 6.x-3.0 from beta2 to beta3 or higher, existing select fields that did not have key|value pair lines but only value lines, these lines will be auto-converted into key|value pair lines, where key is a number, beginning at 0 and incremented by 1.

Unfortunately, this breaks the table views and exported .xls files which now contain a number instead of the value for all that select fields :-( Is there any chance to get the value back in the display? Would not have been converting value lines into key|value where key == value a better approach, to ensure backward-compatibilty?

quicksketch’s picture

quicksketch
he/him
commented
Status: Active » Fixed

Unfortunately, this breaks the table views and exported .xls files which now contain a number instead of the value for all that select fields

roball: I think that exact issue is being addressed in #762574: Select and grid components show key (or nothing) instead of values in emails, CSV, table, etc..

Would not have been converting value lines into key|value where key == value a better approach, to ensure backward-compatibilty?

In most situations the existing value can not be converted to a key reliably. Often times these labels are entire sentences, contain punctuation, or duplicate each other. In addition, the auto-numeric approach is what will happen if you use the Options Element module to hide keys entirely, so I figured it was the approach that would be most consistent with what Webform would do in the future. It's definitely not intentional that the exports, e-mails, and other places started using the keys when they should be using values. Let's open new issues for any further questions, though I think #762574 addresses your main concern.

roball’s picture

roball commented

You are right. Continued at #762574: Select and grid components show key (or nothing) instead of values in emails, CSV, table, etc..

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

sdsheridan’s picture

sdsheridan
he/him/his
commented

Realise this is closed, but just want to subscribe to it, and also note that this improved data structure is important for a piece of work that I may be doing with a client where they wish to do some automated scoring, which will likely be a custom module I will build for them. Without discrete key values for questions that appear in a grid structure that I can map to weights, this would be nearly impossible. For example, they may change the wording of a question slightly to make it clearer, or reposition the order of questions to reduce bias in the survey. Without a non-mutable key travelling with the question, there would be no way for me to link it to a weighting scheme, and do the scoring.

Is this now included and 'stable' in the 6.x-3.0-beta5 version? Let me know if there's any way I can help!

Thx, Shawn