[Contrib: Evaluation System] Database migration from Evaluation System 1.3.0 to 1.4.1 (WAS: Re: Mismatch in SQL migration scripts?)

[Daniel Merino]

Hi all.

Answering myself, this script eval-1-3_to_eval-1-4.sql is not only 
erroneus, also is incomplete, as a table creation and 53 new indexes are 
not included in it. These sentences, placed at 
impl/src/ddl/mysql/evaluation.sql, are not launched with autoddl set to 
true neither.

At the end of this mail I will paste all the missing SQL sentences. I 
hope that future migrators will find them in GMane.

However I must warn them that, after migration is completed, I have got 
random NullPointerExceptions in EvaluationSettingsProducer.java when 
editing evaluations. And also in all the reporting options 
(CSVReportExporter.java and PDFReportExporter.java). Seems like 
EvalEvaluation null objects are sent.

Too many issues for me. I think that my best option is to stay at 
Evaluation System 1.3.0.

Regards.

Daniel Merino escribió:
> Hi everybody.
>
> I'm currently starting to test Evalsys 1.4.1 upgrading it from 1.3.0.
>
> Looking for SQL migration scripts, I think that there is a mismatch in 
> impl/src/ddl/conversion/mysql/eval-1-3_to_eval-1-4.sql :
>
> alter table EVAL_ASSIGN_GROUP add column AVAILABLE_EMAIL_SENT datetime 
> DEFAULT NULL;
> alter table EVAL_ASSIGN_GROUP add column REMINDER_EMAIL_SENT datetime 
> DEFAULT NULL;
> create index ASSIGN_USER_AES_IDX on EVAL_ASSIGN_USER (AVAILABLE_EMAIL_SENT);
> create index ASSIGN_USER_RES_IDX on EVAL_ASSIGN_USER (REMINDER_EMAIL_SENT);
>
> I think that these two indexes must be referred to EVAL_ASSIGN_GROUP and 
> not to EVAL_ASSIGN_USER, as this second table is created in the previous 
> script (eval-1-2_to_eval-1-3.sql) and AVAILABLE_EMAIL_SENT doesn't exist 
> in it, neither REMINDER_EMAIL_SENT.
>
> Hope it helps.
> Best regards.
>   
List of non-included SQL sentences that worked for me:

create table EVAL_ADMIN (
ID bigint not null auto_increment,
USER_ID varchar(255) not null,
ASSIGN_DATE datetime not null,
ASSIGNOR_USER_ID varchar(255) not null,
primary key (ID)
);

create index eval_ahgroup_owner on EVAL_ADHOC_GROUP (OWNER);
create index eval_ahuser_type on EVAL_ADHOC_USER (USER_TYPE);
create index eval_eval_admin_user_id on EVAL_ADMIN (USER_ID);
create index eval_answer_num on EVAL_ANSWER (NUM_ANSWER);
create index eval_assign_hier_nodeid on EVAL_ASSIGN_HIERARCHY (NODE_ID);
create index eval_asgnuser_userid on EVAL_ASSIGN_USER (USER_ID);
create index eval_asgnuser_eid on EVAL_ASSIGN_USER (EID);
create index eval_asgnuser_reminderSent on EVAL_ASSIGN_USER 
(REMINDER_EMAIL_SENT);
create index eval_asgnuser_status on EVAL_ASSIGN_USER (ASSIGN_STATUS);
create index eval_asgnuser_groupid on EVAL_ASSIGN_USER (GROUP_ID);
create index eval_asgnuser_type on EVAL_ASSIGN_USER (ASSIGN_TYPE);
create index eval_asgnuser_availableSent on EVAL_ASSIGN_USER 
(AVAILABLE_EMAIL_SENT);
create index eval_config_name on EVAL_CONFIG (NAME);
create index eval_templ_owner on EVAL_EMAIL_TEMPLATE (OWNER);
create index eval_templ_type on EVAL_EMAIL_TEMPLATE (TEMPLATE_TYPE);
create index eval_templ_eid on EVAL_EMAIL_TEMPLATE (EID);
create index eval_eval_state on EVAL_EVALUATION (STATE);
create index eval_eval_viewdate on EVAL_EVALUATION (VIEW_DATE);
create index eval_eval_category on EVAL_EVALUATION (EVAL_CATEGORY);
create index eval_eval_term on EVAL_EVALUATION (TERM_ID);
create index eval_eval_type on EVAL_EVALUATION (EVAL_TYPE);
create index eval_eval_owner on EVAL_EVALUATION (OWNER);
create index eval_eval_duedate on EVAL_EVALUATION (DUE_DATE);
create index eval_eval_startdate on EVAL_EVALUATION (START_DATE);
create index eval_evaluation_eid on EVAL_EVALUATION (EID);
create index eval_group_nodeid on EVAL_GROUPNODES (NODE_ID);
create index eval_item_owner on EVAL_ITEM (OWNER);
create index eval_item_sharing on EVAL_ITEM (SHARING);
create index eval_item_eid on EVAL_ITEM (EID);
create index eval_item_expert on EVAL_ITEM (EXPERT);
create index eval_itemgroup_owner on EVAL_ITEMGROUP (OWNER);
create index eval_itemgroup_type on EVAL_ITEMGROUP (type);
create index eval_itemgroup_expert on EVAL_ITEMGROUP (EXPERT);
create index eval_lock_name on EVAL_LOCK (NAME);
create index eval_response_groupid on EVAL_RESPONSE (GROUP_ID);
create index eval_response_owner on EVAL_RESPONSE (OWNER);
create index eval_scale_owner on EVAL_SCALE (OWNER);
create index eval_scale_mode on EVAL_SCALE (SCALE_MODE);
create index eval_scale_sharing on EVAL_SCALE (SHARING);
create index eval_scale_eid on EVAL_SCALE (EID);
create index eval_tags_tag on EVAL_TAGS (TAG);
create index eval_tagsmeta_owner on EVAL_TAGS_META (OWNER);
create index eval_tagsmeta_tag on EVAL_TAGS_META (TAG);
create index eval_template_sharing on EVAL_TEMPLATE (SHARING);
create index eval_template_eid on EVAL_TEMPLATE (EID);
create index eval_template_owner on EVAL_TEMPLATE (OWNER);
create index eval_templateitem_blockid on EVAL_TEMPLATEITEM (BLOCK_ID);
create index eval_templateitem_eid on EVAL_TEMPLATEITEM (EID);
create index eval_templateitem_owner on EVAL_TEMPLATEITEM (OWNER);
create index eval_trans_field on EVAL_TRANSLATION (FIELD_NAME);
create index eval_trans_langcode on EVAL_TRANSLATION (LANGUAGE_CODE);
create index eval_trans_class on EVAL_TRANSLATION (OBJECT_CLASS);
create index eval_trans_objectid on EVAL_TRANSLATION (OBJECT_ID);

-- 
Daniel Merino Echeverría
daniel.merino at unavarra.es
Gestor de teleformación - Centro Superior de Innovación Educativa.
Tfno: 948-168489 - Universidad Pública de Navarra.