View Issue Details

IDProjectCategoryView StatusLast Update
15021Feature requests[All Projects] Otherpublic2019-07-08 11:00
ReporterolleharAssigned To 
PrioritynoneSeverityfeature 
Status newResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary15021: Use VIEW to cirumvent the database column limit
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

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'

Postgres might be stricter still:

The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view.

Source: http://www.postgresqltutorial.com/postgresql-updatable-views/

This script shows that a VIEW has no limit on number of columns

<?php

$mysqli = mysqli_connect("localhost", "root", "", "c1www");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$res = mysqli_query($mysqli, "DROP TABLE four");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$res = mysqli_query($mysqli, "DROP TABLE five");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$res = mysqli_query($mysqli, "DROP VIEW view2");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$columns = [];
for ($i = 0; $i < 1000; $i++) {
    $columns[] = " somelongcolumnnamethatisnottoolongthough$i INT(1) ";
}
$columns = implode(',', $columns);
$query = "CREATE TABLE four (
    id INT AUTO_INCREMENT PRIMARY KEY,
    $columns
) ENGINE MyISAM";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$columns = [];
for ($i = 0; $i < 1000; $i++) {
    $columns[] = " fomelongcolumnnamethatisnottoolongthough$i INT(1) ";
}
$columns = implode(',', $columns);
$query = "CREATE TABLE five (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    $columns
) ENGINE MyISAM";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$query = "CREATE VIEW view2 AS SELECT * FROM four, five";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

BUT: Should be key/value store instead
BUT: key/value store is not efficient for a stats module (depending on how the queries look) - performance test it?

TagsNo tags attached.

Relationships

related to 15035 new Bug reports Lost expression manager alert about deprecated question code 

Activities

ollehar

ollehar

2019-07-03 14:48

administrator   ~52691

@DenisChenu Thoughts?

DenisChenu

DenisChenu

2019-07-03 15:46

developer   ~52693

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

I think more of something in config.xml

Here i don't understand …

ollehar

ollehar

2019-07-03 15:51

administrator   ~52694

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).

DenisChenu

DenisChenu

2019-07-03 15:56

developer   ~52695

Last edited: 2019-07-03 15:57

View 4 revisions

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 …

ollehar

ollehar

2019-07-03 16:04

administrator   ~52697

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).

ollehar

ollehar

2019-07-03 16:05

administrator   ~52698

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.

DenisChenu

DenisChenu

2019-07-03 16:09

developer   ~52700

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 …).

ollehar

ollehar

2019-07-03 16:32

administrator   ~52702

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:

DenisChenu

DenisChenu

2019-07-03 17:42

developer   ~52709

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 ;)

ollehar

ollehar

2019-07-03 20:42

administrator   ~52719

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.

ollehar

ollehar

2019-07-04 16:35

administrator   ~52732

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.

DenisChenu

DenisChenu

2019-07-04 16:40

developer   ~52733

Last edited: 2019-07-04 16:42

View 2 revisions

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 ;)

DenisChenu

DenisChenu

2019-07-04 16:42

developer   ~52734

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 ?

ollehar

ollehar

2019-07-04 17:07

administrator   ~52735

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.

DenisChenu

DenisChenu

2019-07-04 17:18

developer   ~52737

Last edited: 2019-07-04 17:23

View 7 revisions

<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.

ollehar

ollehar

2019-07-04 17:30

administrator   ~52739

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)).

ollehar

ollehar

2019-07-04 17:31

administrator   ~52740

<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?).

DenisChenu

DenisChenu

2019-07-04 18:07

developer   ~52741

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

DenisChenu

DenisChenu

2019-07-04 18:32

developer   ~52742

Last edited: 2019-07-04 18:33

View 2 revisions

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

ollehar

ollehar

2019-07-04 21:34

administrator   ~52743

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

13k questions in one survey?

Mazi

Mazi

2019-07-05 08:32

partner   ~52745

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!

ollehar

ollehar

2019-07-05 11:30

administrator   ~52748

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

ollehar

ollehar

2019-07-05 11:31

administrator   ~52749

@Mazi Are QCODES unique?

DenisChenu

DenisChenu

2019-07-05 11:40

developer   ~52750

Last edited: 2019-07-05 11:40

View 2 revisions

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

Mazi

Mazi

2019-07-05 12:13

partner   ~52756

Yes, those codes should always be unique.

DenisChenu

DenisChenu

2019-07-05 12:36

developer   ~52758

@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

Mazi

Mazi

2019-07-05 13:18

partner   ~52760

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?

DenisChenu

DenisChenu

2019-07-05 14:28

developer   ~52763

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

ollehar

ollehar

2019-07-05 15:04

administrator   ~52765

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?

Mazi

Mazi

2019-07-06 17:19

partner   ~52777

@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.

ollehar

ollehar

2019-07-08 10:31

administrator   ~52782

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

DenisChenu

DenisChenu

2019-07-08 10:41

developer   ~52783

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

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



survey_archive_brokenEMCode.lsa (4,008 bytes)
DenisChenu

DenisChenu

2019-07-08 10:51

developer   ~52784

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

Mazi

Mazi

2019-07-08 10:59

partner   ~52785

@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.

Issue History

Date Modified Username Field Change
2019-07-03 14:46 ollehar New Issue
2019-07-03 14:48 ollehar Note Added: 52691
2019-07-03 14:50 ollehar Description Updated View Revisions
2019-07-03 15:07 ollehar Description Updated View Revisions
2019-07-03 15:36 ollehar Description Updated View Revisions
2019-07-03 15:46 DenisChenu Note Added: 52693
2019-07-03 15:51 ollehar Note Added: 52694
2019-07-03 15:56 DenisChenu Note Added: 52695
2019-07-03 15:56 DenisChenu Note Edited: 52695 View Revisions
2019-07-03 15:57 DenisChenu Note Edited: 52695 View Revisions
2019-07-03 15:57 DenisChenu Note Edited: 52695 View Revisions
2019-07-03 16:04 ollehar Note Added: 52697
2019-07-03 16:05 ollehar Note Added: 52698
2019-07-03 16:09 DenisChenu Note Added: 52700
2019-07-03 16:32 ollehar Note Added: 52702
2019-07-03 16:47 ollehar Description Updated View Revisions
2019-07-03 17:42 DenisChenu Note Added: 52709
2019-07-03 20:42 ollehar Note Added: 52719
2019-07-04 16:35 ollehar Note Added: 52732
2019-07-04 16:40 DenisChenu Note Added: 52733
2019-07-04 16:42 DenisChenu Note Added: 52734
2019-07-04 16:42 DenisChenu Note Edited: 52733 View Revisions
2019-07-04 17:07 ollehar Note Added: 52735
2019-07-04 17:18 DenisChenu Note Added: 52737
2019-07-04 17:18 DenisChenu Note Edited: 52737 View Revisions
2019-07-04 17:20 DenisChenu Note Edited: 52737 View Revisions
2019-07-04 17:21 DenisChenu Note Edited: 52737 View Revisions
2019-07-04 17:22 DenisChenu Note Edited: 52737 View Revisions
2019-07-04 17:22 DenisChenu Note Edited: 52737 View Revisions
2019-07-04 17:23 DenisChenu Note Edited: 52737 View Revisions
2019-07-04 17:28 ollehar Description Updated View Revisions
2019-07-04 17:29 ollehar Description Updated View Revisions
2019-07-04 17:30 ollehar Note Added: 52739
2019-07-04 17:31 ollehar Note Added: 52740
2019-07-04 18:07 DenisChenu Note Added: 52741
2019-07-04 18:32 DenisChenu Note Added: 52742
2019-07-04 18:33 DenisChenu Note Edited: 52742 View Revisions
2019-07-04 21:34 ollehar Note Added: 52743
2019-07-04 21:37 ollehar Summary Use VIEW to cirumvent the column database column limit => Use VIEW to cirumvent the database column limit
2019-07-05 08:32 Mazi Note Added: 52745
2019-07-05 11:30 ollehar Note Added: 52748
2019-07-05 11:31 ollehar Note Added: 52749
2019-07-05 11:40 DenisChenu Note Added: 52750
2019-07-05 11:40 DenisChenu Note Edited: 52750 View Revisions
2019-07-05 12:13 Mazi Note Added: 52756
2019-07-05 12:36 DenisChenu Note Added: 52758
2019-07-05 13:18 Mazi Note Added: 52760
2019-07-05 14:28 DenisChenu Note Added: 52763
2019-07-05 15:04 ollehar Note Added: 52765
2019-07-06 17:19 Mazi Note Added: 52777
2019-07-08 10:31 ollehar Note Added: 52782
2019-07-08 10:41 DenisChenu File Added: survey_archive_brokenEMCode.lsa
2019-07-08 10:41 DenisChenu Note Added: 52783
2019-07-08 10:50 DenisChenu Relationship added related to 15035
2019-07-08 10:51 DenisChenu Note Added: 52784
2019-07-08 10:59 Mazi Note Added: 52785