Hi, I recommend that the table structures of be changed:

  1. webform_submitted_data.name field be dropped
  2. webform_submitted_data.cid field be added
  3. the Webform_submitted_data.name types:
    • '__userid'
    • '__remotehost'
    • '__timestamp'
    • '__useragent'

    Should be dropped in place of components of the same name. These components would have specific CIDs within webform_component and would be added to all sessions with a given SID at session.

  4. there should be no arrays in webform_submitted_data.data, rather it should have multiple records with the same CID where that CID allowed multiple records, ie checboxes.

These changes whilst not 'necessary' would certainly improve the efficiency of the database lookups and would make it easier for other contributers to work with.

Comments

ullgren’s picture

ullgren CreditAttribution: ullgren commented

Sounds like a good idea.
You've allready started on the implementation of this or should I ?
I guess there is not much use to continue with the report part untill this migration is done ?

jnt’s picture

jnt CreditAttribution: jnt commented

No, I have not changed it yet. I thought it might be best to produce the functionality with the current database and decompose some of the work into accessor methods in webform.inc Then we could change the database and the body of the accessor functions in the background so as to leave current datasets functional. A lazy migration for the users.

Also, lets discuss and formalize it first.

I see the main advantage of this proposed change is that it makes the database much more user friendly including outside of drupal/php. If this is a desireable aim, then it might also pay not to serialize the recordset data for multiple selects but include them as multiple instances of the component within a given session ID.

ie:

Record # 999: sid = 99, component = 201 (A textarea), data = "I recommend this database structure"
Record # 1000: sid = 99, component = 203 (Component ID of multiple select), data = "It is efficient"
Record # 1001: sid = 99, component = 203 (Component ID of multiple select), data = "It is easy to read"
Record # 1002: sid = 99, component = 203 (Component ID of multiple select), data = "It is fast"
Record # 1003: ...

What do you think.

This would also continue to address the issue of orphans which your current structure does, ie when the administrator changes the select items in a component. The orphans would not be lost.

I am currently travelling till late next week so I will only be online sporadically.

jnt’s picture

jnt CreditAttribution: jnt commented

The field name 'confirm' is the same as a form variable index of the same name. I have added a patch that renames everything except the field name to 'confirmation'.

I recommend the field name be changed to 'confirmation' as well.

Perhaps this and other database modes can be done as part of the 4.6 release.

ullgren’s picture

ullgren CreditAttribution: ullgren commented

I've started work on this issue.
I will check in changes in the CVS as soon as I get a something stable.

ullgren’s picture

ullgren CreditAttribution: ullgren commented

Ok, a new and better(?) database structure is now in CVS.
Analysis and file download does not work as it is now.

cjm’s picture

cjm CreditAttribution: cjm commented

if you're going to redesign the database tables, i would love for you to make them and therefore the whole module postgres compatiable

any chance?

ullgren’s picture

ullgren CreditAttribution: ullgren commented

The new structure will be postgre compliant.
Interested parties can now check-out the CVS and test it on test site (I wouldn't put it in production just yet).
Please leave comments on the new structure.

stevryn’s picture

stevryn CreditAttribution: stevryn commented

I understand the reasoning behind changing the structure for the database fields. Perhaps because I am not used to seeing all my fields on seperate lines or maybe just because this is all new for me anyways, but..

How the heck do I extract that data, and put it all back into one "row"? I have run into this with webform and flexinode content. I want to be able to show the submitted data on various other pages and do other really useful things, but havent a clue how to take all those seperate table entries and put them back together into one nice row. Im just learning how to create queries in MySql. Just a little info to get me started please :)

Thanks

quicksketch’s picture

quicksketch
he/him
CreditAttribution: quicksketch commented
Status: Active » Closed (fixed)

These changes have been implemented in the 4.7 release version.