@DenisChenu Thoughts?

Arg … you mean development => database column by Question «object»

I think more of something in config.xml

Here i don't understand …

Nothing like that. Instead of creating a table lime_answers_12345 we will create subtables lime_answers_12345_a and lime_answers_12345_b and then combine them into a view called lime_answers_12345. This will circumvent the column limitation (number of bytes in column definitions).

Ah OK …

Personally : don't really like it … : because i don't see how to make it automatically inside Response model

But don't have real better idea …

Maybe : survey_XXX_group_YYY table for response ?
Then when update answer on Q ZZZ : we update survey_{Q->sid}_group_{Q->gid} (and this can be done in Response::model ) ?

This not fix group with a lot of column …

The Response model will be untouched. The only thing that changes is activate survey logic - instead of creating a table, it will create multiple tables and one view (if the columns are too many).

All models will remain untouched. The response insert in EM will have to change, probably, to make sure a row in each table is inserted.

Since EM use Response::model(sid)->save() in 4 : we can use model.

I am surprised that the models of Yii can use the views without adaptation?

My question is more : thi must be the most automatically possible and the most «soon» than possible. Since Yii DB access must be done by model : in my option it's better to use directly model update (and no another helper etc …).

Ah crap, that will break. See this code above:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'

Can't insert into two tables at the same time via a view. D:

Then : if break : need an update on Response::model too …

Then need to know at what database THIS column refer …
Using group : column are the gid ;)

Yeah, the save() and update() methods need to be specially implemented.

If we split by group it's easier to know which table to use. But it implies multiple queries for each save (possibly). Maybe not such a big difference, and only a performance hit if the number of columns is higher than allowed in one table.

Another thing that can be done at the same time is changing the column name from sgqa to just qa (assuming survey id and group id are in the table name). This will save even more space.

Yes, we can «easily» update column name
And if we remove the Group id : <strike>we (maybe) can allow reorder question inside survey after actvivation.</strike> Not if we use different table for each group …

But : if we update column name : maybe best is to use EM code for column name

1. No optimization : 20caracter_20caracter_20caracter max
2. Disable updating title after activation for question (for subquestion it's already done)

BUT : this make table very more clear ;)

If we split by group it's easier to know which table to use. But it implies multiple queries for each save (possibly). Maybe not such a big difference, and only a performance hit if the number of columns is higher than allowed in one table.

I don't know if current 4.0 EM-Save update all values or only updated values ?

If the column names are <questionid> or <questionid>_<answerid>, it will make sure there won't be any naming conflicts when creating the view. TODO: Check order question type answer columns.

I don't know if current 4.0 EM-Save update all values or only updated values ?

That's up to Yii? If we do Response::save(). In any case, it need to be split into multiple saves. Maybe a new model called ResponseGroupTable.

<questionid>[_<subYquestionid>][_<subXquestionid>] ?

Currently <questionid>[<subYquestioncode>][_<subXquestioncode>] …

But alt solution
<questioncode>[_<subYquestioncode>][_<subXquestioncode>]

Make it easy to understand DB without LimeSurvey. I think there are a feature request by @Mazi about such column name.

Updated desc above. It's possible to create a VIEW with 2'000 columns with large names (would not fit into one table; all columns have INT(1)).

<questionid>[<subYquestionid>][<subXquestionid>] ?

Right, subquestions... It's possible to have longer names like that, but it would limit nr of questions in one group (still OK, and maybe ID become very long too?).

My last dev setup (less than one year : 13570 for last qid ;) )

Another solution : one table by question \o/ \o\ /o/ \o\ /o/ \o/ /o\

My last dev setup (less than one year : 13570 for last qid ;) )

13k questions in one survey?

Interesting approach. When using the VIEW we could even switch to more readable column names, see https://www.limesurvey.org/de/foren/development/105894-qcode-database-view#140098

Question is if it makes sense spending time on this when you are planning for more object orientated question modelling at LS 5 using inheritance and the like. Because that would be the step at which we should start splitting the old lime_survey_123456 tables into something like (just an example)
lime_survey_123456_numeric
lime_survey_123456_text
lime_survey_123456_date
...

And ideally we would be able to use question codes as column headings at the new design!

It would be possible to create multiple views. One can even create a plugin that creates views that makes external reporting easier. :)

@Mazi Are QCODES unique?

@ollehar : for survey created after 2.05 yes.
But more EM code are unique.

Yes, those codes should always be unique.

@Mazi : it can be multiple with survey before 2.06 or 2.05 …
See https://github.com/LimeSurvey/LimeSurvey/blob/387fe4d16f87675dd860cd46e1b74e856eef778d/application/models/Question.php#L156

Correct. When importing such surveys, codes get adjusted. So question is: What happens to duplicate or invalid (using e.g. underscores) codes if you do a DB update?

Not with LSA if i remind : https://github.com/LimeSurvey/LimeSurvey/blob/387fe4d16f87675dd860cd46e1b74e856eef778d/application/models/Question.php#L168

What happens to duplicate or invalid (using e.g. underscores) codes if you do a DB update?

Why would a db update change the content of the data?

@ollehar, if you are updating a very old 2.00 version which uses duplicate question codes there can be issues if these codes don't get adjusted and you are using the codes as columns names. There could be two columns with name "age" for example. So such details would have to be checked and have to be adjusted.

@Mazi, is there a bug report about any such issue? Or just personal experience? ;)

@ollehar : current system us sidXgidXqid : then the issue can't happen

Since we don't update question code : import joined LSA to check it :)

Adding related issue 15035: Lost expression manager alert about deprecated question code

@ollehar: As Denis pointed out, this can't happen with the current setup. But with the mentioned adjustments this, theoretically, could cause problems. I only pointed to that because before starting to develop something like that, we should consider the additional effort it may take to make sure codes are unique when updating from older versions or when importing old LSS/LSA files.

Using views is not a solution since the number of fields that can be put into a join is also limited.