One of the most popular modules we have released so far is Smart Forms module. 95% of our customers have adopted this module within the first month of the release. This certainly took us by surprise and within the first 6 moths we have captured hundreds of thousands of responses from the field. To enhance the user experience, we have given our customers ability to merge their form results from the field, with their internal document templates. We did that by integrating our solution with a popular tool called WebMerge.
Due to a very large number of responses that we captured from the field, and with an increasing popularity of this feature, we have started to optimise the way we store this data in our MySQL database. The initial problem was that each response to an individual question, was stored in the database in a separate row. In other words, if a form had 150 questions and one person submitted that form, we would create 150 rows in the database just for that one form submission. As you can imagine, after thousands of submissions and growing, the size of the MySQL table storing these responses is going to be very big very quickly. We have had to find a better way to store the responses and still be able to maintain backwards compatibility.
Currently, there are no tools on the market that can help us with this optimisation exercise. After some careful R&D we have come up with the following approach to migrating to the new schema:
- Design the new schema that would create a single row in the database per response, regardless of the number of questions in a form.
- Update the codebase so that each new form submission writes the responses in both formats: the old format and the new format.
- Create a DB migration script which we would run after the code in Step 2 was released. This would ensure that all previous responses were converted to the new schema version.
- After a few weeks of careful testing, remove the code from Step 2 that was writing to the schema in an old format.
Here is a small example of the test script that tests for the new changes that are being implemented:
<?php
// note to use this script, you must have diffchecker and jq installed:
// brew install jq
// npm install -g diffchecker
// -- create a free account on diffchecker as well for it to work..
require("../src/Assignar/Utilities/FormUpgrader.php");
function writePretty($json, $sort = null) {
$file = tmpfile();
$metadatas = stream_get_meta_data($file);
$filename = $metadatas['uri'];
fwrite($file, $json."\n");
$filter = '.';
if ($sort) {
$filter = "'sort_by(.$sort)'";
}
$pretty = shell_exec("jq -S $filter $filename");
rewind($file);
fwrite($file, $pretty);
return array(
"handle" => $file,
"name" => $filename,
);
}
function diff($json1, $json2, $sort1 = null, $sort2 = null) {
$json1 = is_string($json1) ? $json1 : json_encode($json1);
$json2 = is_string($json2) ? $json1 : json_encode($json2);
$f1 = writePretty($json1, $sort1);
$f2 = writePretty($json2, $sort2);
shell_exec("diffchecker --expires day " . $f1["name"] . " " . $f2["name"]);
fclose($f1["handle"]);
fclose($f2["handle"]);
}
$result_raw = '[{"field_number":1,"value":"SINGLE 1","label":null,"attachment":null},{"field_number":2,"value":"MULTI 1::MULTI 2::MULTI 3","label":null,"attachment":null},{"field_number":3,"value":"DROPDOWN 3","label":null,"attachment":null},{"field_number":4,"value":"12345","label":null,"attachment":null},{"field_number":5,"value":"text","label":null,"attachment":null},{"field_number":6,"value":"box\nbox\nbox","label":null,"attachment":null},{"field_number":7,"value":"Image","label":null,"attachment":"\/fileupload\/attachments\/form_211_58d20cc402612.png"},{"field_number":8,"value":"Image","label":null,"attachment":["\/fileupload\/attachments\/form_211_58d20cc4a87c8.jpg"]},{"field_number":10,"value":"2017-12-31","label":null,"attachment":null},{"field_number":11,"value":"12:59","label":null,"attachment":null},{"field_number":12,"value":"2017-01-01 1:00","label":null,"attachment":null},{"field_number":13,"value":545,"label":"Ana Prusevic","attachment":null},{"field_number":14,"value":139,"label":"Mack 10T Tipper - Tipper Truck 10 Tonne","attachment":null},{"field_number":15,"value":"asdf","label":null,"attachment":null},{"field_number":16,"value":"fdsa","label":null,"attachment":null},{"field_number":17,"value":"TICK ME","label":null,"attachment":null},{"field_number":18,"value":"ASDF 1::ASDF 2::qwer","label":null,"attachment":null},{"field_number":19,"value":"SHOW CONDITIONAL 1","label":null,"attachment":null},{"field_number":20,"value":"dsaf","label":null,"attachment":null},{"field_number":22,"value":"ANY 1","label":null,"attachment":null},{"field_number":23,"value":"fsafas","label":null,"attachment":null},{"field_number":25,"value":"HOSPITAL","label":null,"attachment":null},{"field_number":26,"value":"NEEDS ACTION::URGENT","label":null,"attachment":null}]';
$form_raw = '{"fields":[{"id":1,"type":"radio","required":true,"choices":[{"text":null,"value":"SINGLE 1","checked":false,"other":false,"form_change_status":"","color":"f44336"},{"text":null,"value":"SINGLE 2","checked":false,"other":false,"form_change_status":"","color":"795548"},{"text":null,"value":"SINGLE 3","checked":false,"other":false,"form_change_status":"","color":"009688"}],"question":"SINGLE OPTION RESPONSE","condition":null,"conditional":null},{"id":15,"type":"radio","required":true,"choices":[{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"question":"SINGLE OPTION RESPONSE (OTHER)","condition":null,"conditional":null},{"id":17,"type":"radio","required":true,"choices":[{"text":null,"value":"TICK ME","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"TICK ME 2","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"question":"SINGLE OPTION RESPONSE (OTHER 2)","condition":null,"conditional":null,"change_status":false},{"id":2,"type":"checkbox","required":true,"choices":[{"text":null,"value":"MULTI 1","checked":false,"other":false,"form_change_status":"","color":"f44336"},{"text":null,"value":"MULTI 2","checked":false,"other":false,"form_change_status":"","color":"795548"},{"text":null,"value":"MULTI 3","checked":false,"other":false,"form_change_status":"","color":"009688"}],"question":"MULTI OPTION RESPONSE","condition":null,"conditional":null},{"id":16,"type":"checkbox","required":true,"choices":[{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"question":"MULTI OPTION RESPONSE (OTHER)","condition":null,"conditional":null},{"id":18,"type":"checkbox","required":false,"choices":[{"text":null,"value":"ASDF 1","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"ASDF 2","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"question":"MULTI OPTION RESPONSE (OTHER 2)"},{"id":3,"type":"dropdown","required":true,"choices":[{"text":null,"value":"DROPDOWN 1","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"DROPDOWN 2","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"DROPDOWN 3","checked":false,"other":false,"form_change_status":""}],"question":"DROPDOWN SELECT","condition":null,"conditional":null},{"id":4,"type":"number","required":true,"condition":null,"conditional":null,"question":"NUMBER FIELD"},{"id":5,"type":"text","required":true,"question":"TEXT FIELD","condition":null,"conditional":null},{"id":6,"type":"textarea","required":true,"condition":null,"conditional":null,"question":"TEXT BOX"},{"id":7,"type":"signature","required":true,"question":"SIGNATURE","condition":null,"conditional":null},{"id":8,"type":"imagecapture","required":true,"question":"IMAGE CAPTURE","condition":null,"conditional":null},{"id":9,"type":"heading","required":false,"question":"HEADING","url_required":true,"url":"http:\/\/assignar.com","url_label":"Link Label goes here"},{"id":10,"type":"date","required":true,"question":"DATE","condition":null,"conditional":null},{"id":11,"type":"time","required":true,"question":"TIME","condition":null,"conditional":null},{"id":12,"type":"datetime","required":true,"condition":null,"conditional":null,"question":"DATE AND TIME"},{"id":13,"type":"worker","parentType":"dropahead","required":true,"tags_permission":[],"condition":null,"conditional":null,"question":"WORKER"},{"id":14,"type":"asset","parentType":"dropahead","required":true,"tags_permission":[],"question":"ASSET","condition":null,"conditional":null},{"id":19,"type":"radio","required":true,"choices":[{"text":null,"value":"SHOW CONDITIONAL 1","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"SHOW CONDITIONAL 2","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"SHOW NEITHER","checked":false,"other":false,"form_change_status":""}],"condition":null,"conditional":null,"question":"CONDITIONAL SPECIFIC SINGLE QUESTION"},{"id":20,"type":"radio","required":false,"choices":[{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"question":"CONDITIONAL SINGLE 1","conditional":1,"condition":{"display":"1","question":19,"hasvalue":"specific","value":"SHOW CONDITIONAL 1"}},{"id":21,"type":"radio","required":false,"choices":[{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"conditional":1,"condition":{"display":"1","question":19,"hasvalue":"specific","value":"SHOW CONDITIONAL 2"},"question":"CONDITIONAL SINGLE 2"},{"id":22,"type":"checkbox","required":false,"choices":[{"text":null,"value":"ANY 1","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"ANY 2","checked":false,"other":false,"form_change_status":""},{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":""}],"question":"CONDITIONAL ANY MULTI QUESTION"},{"id":23,"type":"text","required":false,"conditional":1,"condition":{"display":"1","question":22,"hasvalue":"any"},"question":"CONDITIONAL MULTI 1"},{"id":24,"type":"text","required":false,"conditional":1,"question":"CONDITIONAL MULTI 2","condition":{"display":"0","question":22,"hasvalue":"any"}},{"id":25,"type":"radio","required":true,"choices":[{"text":null,"value":"HOSPITAL","checked":false,"other":false,"form_change_status":"6","change_status":true},{"text":null,"value":"NEAR MISS","checked":false,"other":false,"form_change_status":"4","change_status":true},{"text":null,"value":"Other","checked":false,"other":true,"form_change_status":"5","change_status":true}],"condition":null,"conditional":null,"change_status":true,"question":"SET STATUS SINGLE"},{"id":26,"type":"checkbox","required":true,"choices":[{"text":null,"value":"NEEDS ACTION","checked":false,"other":false,"form_change_status":"3","change_status":true},{"text":null,"value":"URGENT","checked":false,"other":false,"form_change_status":"7","change_status":true}],"condition":null,"conditional":null,"question":"SET STATUS MULTI","change_status":true}]}';
$result_json = json_decode($result_raw, true);
$form_json = json_decode($form_raw, true);
$upgrader = new Assignar\Utilities\FormUpgrader();
$upgradedResult = $upgrader->upgradeFormResultData($result_json, $form_json);
$downgradedResult = $upgrader->downgradeFormResultData($upgradedResult, $form_json);
//echo "result_json\n";
//echo $result_raw;
//echo "\n\nupgraded_result\n";
//echo $upgradedResult;
//echo "\n\ndowngraded_result\n";
//echo $downgradedResult;
//echo "\n\n";
diff($result_json, $downgradedResult, 'field_number', 'field_number');
$upgradedForm = $upgrader->upgradeFormMetadata($form_json);
$downgradedForm = $upgrader->downgradeFormMetadata($upgradedForm);
//echo "form_json\n";
//echo $form_raw;
//echo "\n\nnupgraded_form\n";
//echo $upgradedForm;
//echo "\n\ndowngraded_form\n";
//echo $downgradedForm;
//echo "\n\n";