Hi all
I'm not disappeared
Yesterday I finally managed to compact all the logic within a single SQL (I'm a db guy after all) getting rid of the PHP part except for the JSON extraction (too sad MySQL doesn't offer an out-of-the-box solution for returning JSON directly yet).
Moreover I wrote a little bit cleaner and reusable code, without the hardcoded character positions (the "X" of the SGQA format in the column names).
Here it is an early release of the SQL:
Code:
DROP PROCEDURE IF EXISTS spLime_NormalizeIt;
DELIMITER //
CREATE PROCEDURE spLime_NormalizeIt (survey_code INT)
BEGIN
/*
Author: Raffaele Turra - SyntegraDMC
Created: 2014-02-08
Create an UNPIVOT from survey results
considering only the columns containing the questions
Note: LS creates acolumn for each question, in the sidXgidXqid format
and values are answers' codes
*/
SET group_concat_max_len=15000;
SET @r_query = NULL;
# SET survey_code = 869765;
SET @drop_stmt = CONCAT("DROP TABLE IF EXISTS lime_survey_", survey_code, "_normalized;");
SET @create_stmt = CONCAT("CREATE TABLE lime_survey_", survey_code, "_normalized LIKE lime_survey__normalized;");
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
"SELECT LS.token
, LS.datestamp
, \"", RIGHT(g.group_name, 5), "\" AS codice_reato
, \"", q.Title, "\" AS codice_domanda
, LA.qid
, LS.`", c.column_name, "` AS answer_code
, LA.assessment_value
from lime_answers AS LA
INNER JOIN `lime_survey_", survey_code , "` AS LS
ON LA.code = LS.`", c.column_name, "`
AND LA.qid = substring_index(\"", c.column_name, "\", 'X', -1)"
) SEPARATOR " UNION ALL "
) INTO @r_query
from information_schema.columns c
left join lime_groups AS g
ON g.gid = substring_index(substring_index(column_name, 'X',2) , 'X', -1)
left join lime_questions AS q
ON g.gid = q.gid
and q.qid = substring_index(column_name, 'X', -1)
where substring_index(column_name, 'X', 1) = substring_index(table_name, '_', -1)
AND substring_index(column_name, 'X', 1) = CAST(survey_code AS char(10))
ORDER BY q.question_order;
SET @r_query = CONCAT("INSERT INTO lime_survey_"
, survey_code
, "_normalized
"
, @r_query);
PREPARE dropstmt FROM @drop_stmt;
EXECUTE dropstmt;
DEALLOCATE PREPARE dropstmt;
PREPARE createstmt FROM @create_stmt;
EXECUTE createstmt;
DEALLOCATE PREPARE createstmt;
PREPARE selstmt FROM @r_query;
EXECUTE selstmt;
DEALLOCATE PREPARE selstmt;
END //
DELIMITER ;
CALL spLime_NormalizeIt (869765);
This code basically creates a stored procedure which accepts the survey sid as an input, and creates a "normalized" table using an existing one as a template, here the CREATE statement:
Code:
DROP TABLE IF EXISTS lime_survey__normalized;
CREATE TABLE lime_survey__normalized (
token varchar(50)
, `datestamp` datetime NOT NULL
, codice_reato varchar(50)
, codice_domanda varchar(50)
, qid INT
, answer_code varchar(5)
, assessment_value int
);
Note: "codice_domanda" is italian for "question_code" and it refers to the "question title" as named in LS;
"codice_reato" is specific for my LS application, and corresponds to the code entered for the question group.
HTH
