source: trunk/grails-app/conf/DatabaseUpgrade.groovy @ 1255

Last change on this file since 1255 was 1255, checked in by work@…, 10 years ago
  • fixed an issue with the automated database upgrade where the idx's needed to be updated (idx=idx-1 where idx>X) which resulted in 'null' objects in template lists... Database upgrade should now perform properly in other environments.
  • Property svn:keywords set to Date Author Rev
File size: 2.7 KB
Line 
1import groovy.sql.Sql
2import dbnp.studycapturing.Study
3
4/**
5 * A script to automatically perform database changes
6 *
7 * @Author      Jeroen Wesbeek
8 * @Since       20101209
9 *
10 * Revision information:
11 * $Rev: 1255 $
12 * $Author: work@osx.eu $
13 * $Date: 2010-12-09 16:49:30 +0000 (do, 09 dec 2010) $
14 */
15class DatabaseUpgrade {
16        /**
17         * handle database upgrades
18         *
19         * @param dataSource
20         */
21        public static void handleUpgrades(dataSource) {
22                // get a sql instance
23                groovy.sql.Sql sql = new groovy.sql.Sql(dataSource)
24
25                // execute per-change check and upgrade code
26                changeStudyDescription(sql)                     // r1245 / r1246
27        }
28
29        /**
30         * execute database change r1245 / r1246 if required
31         * @param sql
32         */
33        public static void changeStudyDescription(sql) {
34                // check if we need to perform this upgrade
35                if (sql.firstRow("SELECT count(*) as total FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").total > 0) {
36                        // grom that we are performing the upgrade
37                        "performing database upgrade: study description".grom()
38
39                        // database upgrade required
40                        try {
41                                // get the template field id
42                                def id = sql.firstRow("SELECT id FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").id
43
44                                // iterate through all obsolete study descriptions
45                                sql.eachRow("SELECT study_id, template_text_fields_elt as description FROM study_template_text_fields WHERE template_text_fields_idx='Description'") { row ->
46                                        // migrate the template description to the study object itself
47                                        // so we don't have to bother with sql injections, etc
48                                        def study = Study.findById( row.study_id )
49                                        study.setFieldValue('description', row.description)
50                                        if (!(study.validate() && study.save())) {
51                                                throw new Exception("could not save study with id ${row.study_id}")
52                                        }
53                                }
54
55                                // delete all obsolete descriptions
56                                sql.execute("DELETE FROM study_template_text_fields WHERE template_text_fields_idx='Description'")
57
58                                // find all template id's where this field is used
59                                sql.eachRow("SELECT DISTINCT template_fields_id, fields_idx FROM template_template_field WHERE template_field_id=${id}") { row ->
60                                        // delete the template_template_field reference
61                                        sql.execute("DELETE FROM template_template_field WHERE template_field_id=${id} AND template_fields_id=${row.template_fields_id}")
62
63                                        // and lower the idx-es of the remaining fields
64                                        sql.execute("UPDATE template_template_field SET fields_idx=fields_idx-1 WHERE fields_idx>${row.fields_idx} AND template_fields_id=${row.template_fields_id}")
65                                }
66
67                                // and delete the obsolete template field
68                                sql.execute("DELETE FROM template_field WHERE id=${id}")
69                        } catch (Exception e) {
70                                "changeStudyDescription database upgrade failed: " + e.getMessage()
71                        }
72                }
73        }
74}
Note: See TracBrowser for help on using the repository browser.