1 | import groovy.sql.Sql |
---|
2 | import dbnp.studycapturing.Study |
---|
3 | import org.codehaus.groovy.grails.commons.ConfigurationHolder |
---|
4 | |
---|
5 | /** |
---|
6 | * A script to automatically perform database changes |
---|
7 | * |
---|
8 | * @Author Jeroen Wesbeek |
---|
9 | * @Since 20101209 |
---|
10 | * |
---|
11 | * Revision information: |
---|
12 | * $Rev: 1534 $ |
---|
13 | * $Author: t.w.abma@umcutrecht.nl $ |
---|
14 | * $Date: 2011-02-17 14:29:16 +0000 (do, 17 feb 2011) $ |
---|
15 | */ |
---|
16 | class DatabaseUpgrade { |
---|
17 | /** |
---|
18 | * handle database upgrades |
---|
19 | * |
---|
20 | * @param dataSource |
---|
21 | */ |
---|
22 | public static void handleUpgrades(dataSource) { |
---|
23 | // get a sql instance |
---|
24 | groovy.sql.Sql sql = new groovy.sql.Sql(dataSource) |
---|
25 | |
---|
26 | // get configuration |
---|
27 | def config = ConfigurationHolder.config |
---|
28 | def db = config.dataSource.driverClassName |
---|
29 | |
---|
30 | // execute per-change check and upgrade code |
---|
31 | changeStudyDescription(sql, db) // r1245 / r1246 |
---|
32 | changeStudyDescriptionToText(sql, db) // r1327 |
---|
33 | changeTemplateTextFieldSignatures(sql, db) // prevent Grails issue, see http://jira.codehaus.org/browse/GRAILS-6754 |
---|
34 | setAssayModuleDefaultValues(sql, db) // 1490 |
---|
35 | dropMappingColumnNameConstraint(sql, db) |
---|
36 | } |
---|
37 | |
---|
38 | /** |
---|
39 | * execute database change r1245 / r1246 if required |
---|
40 | * @param sql |
---|
41 | * @param db |
---|
42 | */ |
---|
43 | public static void changeStudyDescription(sql, db) { |
---|
44 | // check if we need to perform this upgrade |
---|
45 | if (sql.firstRow("SELECT count(*) as total FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").total > 0) { |
---|
46 | // grom that we are performing the upgrade |
---|
47 | "performing database upgrade: study description".grom() |
---|
48 | |
---|
49 | // database upgrade required |
---|
50 | try { |
---|
51 | // get the template field id |
---|
52 | def id = sql.firstRow("SELECT id FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").id |
---|
53 | |
---|
54 | // iterate through all obsolete study descriptions |
---|
55 | sql.eachRow("SELECT study_id, template_text_fields_elt as description FROM study_template_text_fields WHERE template_text_fields_idx='Description'") { row -> |
---|
56 | // migrate the template description to the study object itself |
---|
57 | // so we don't have to bother with sql injections, etc |
---|
58 | def study = Study.findById( row.study_id ) |
---|
59 | study.setFieldValue('description', row.description) |
---|
60 | if (!(study.validate() && study.save())) { |
---|
61 | throw new Exception("could not save study with id ${row.study_id}") |
---|
62 | } |
---|
63 | } |
---|
64 | |
---|
65 | // delete all obsolete descriptions |
---|
66 | sql.execute("DELETE FROM study_template_text_fields WHERE template_text_fields_idx='Description'") |
---|
67 | |
---|
68 | // find all template id's where this field is used |
---|
69 | sql.eachRow("SELECT DISTINCT template_fields_id, fields_idx FROM template_template_field WHERE template_field_id=${id}") { row -> |
---|
70 | // delete the template_template_field reference |
---|
71 | sql.execute("DELETE FROM template_template_field WHERE template_field_id=${id} AND template_fields_id=${row.template_fields_id}") |
---|
72 | |
---|
73 | // and lower the idx-es of the remaining fields |
---|
74 | 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}") |
---|
75 | } |
---|
76 | |
---|
77 | // and delete the obsolete template field |
---|
78 | sql.execute("DELETE FROM template_field WHERE id=${id}") |
---|
79 | } catch (Exception e) { |
---|
80 | "changeStudyDescription database upgrade failed: " + e.getMessage() |
---|
81 | } |
---|
82 | } |
---|
83 | } |
---|
84 | |
---|
85 | /** |
---|
86 | * execute database change r1327 if required |
---|
87 | * @param sql |
---|
88 | * @param db |
---|
89 | */ |
---|
90 | public static void changeStudyDescriptionToText(sql, db) { |
---|
91 | // are we running postgreSQL ? |
---|
92 | if (db == "org.postgresql.Driver") { |
---|
93 | // check if column 'description' in table 'study' is not of type 'text' |
---|
94 | if (sql.firstRow("SELECT count(*) as total FROM information_schema.columns WHERE columns.table_schema::text = 'public'::text AND columns.table_name='study' AND column_name='description' AND data_type != 'text'").total > 0) { |
---|
95 | // grom that we are performing the upgrade |
---|
96 | "performing database upgrade: study description to text".grom() |
---|
97 | |
---|
98 | // database upgrade required |
---|
99 | try { |
---|
100 | // change the datatype of study::description to text |
---|
101 | sql.execute("ALTER TABLE study ALTER COLUMN description TYPE text") |
---|
102 | } catch (Exception e) { |
---|
103 | "changeStudyDescriptionToText database upgrade failed: " + e.getMessage() |
---|
104 | } |
---|
105 | } |
---|
106 | } |
---|
107 | } |
---|
108 | |
---|
109 | /** |
---|
110 | * it appears that some TEXT template fields are not of type 'text' |
---|
111 | * due to an issue in how Grails' GORM works with domain inheritance |
---|
112 | * (see http://jira.codehaus.org/browse/GRAILS-6754) |
---|
113 | * @param sql |
---|
114 | * @param db |
---|
115 | */ |
---|
116 | public static void changeTemplateTextFieldSignatures(sql, db) { |
---|
117 | if (db == "org.postgresql.Driver") { |
---|
118 | // check if any TEXT template fields are of type 'text' |
---|
119 | sql.eachRow("SELECT columns.table_name as tablename FROM information_schema.columns WHERE columns.table_schema::text = 'public'::text AND column_name='template_text_fields_elt' AND data_type != 'text';") |
---|
120 | { row -> |
---|
121 | "performing database upgrade: ${row.tablename} template_text_fields_string/elt to text".grom() |
---|
122 | try { |
---|
123 | // change the datatype of text fields to text |
---|
124 | sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN template_text_fields_elt TYPE text", row.tablename)) |
---|
125 | sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN template_text_fields_string TYPE text", row.tablename)) |
---|
126 | |
---|
127 | } catch (Exception e) { |
---|
128 | "changeTemplateTextFieldSignatures database upgrade failed: " + e.getMessage() |
---|
129 | } |
---|
130 | } |
---|
131 | } |
---|
132 | } |
---|
133 | |
---|
134 | /** |
---|
135 | * The fields 'notify' and 'openInFrame' have been added to AssayModule. However, there |
---|
136 | * seems to be no method to setting the default values of these fields in the database. They |
---|
137 | * are set to NULL by default, so all existing fields have 'NULL' set. |
---|
138 | * This method sets the default values |
---|
139 | * @param sql |
---|
140 | * @param db |
---|
141 | */ |
---|
142 | public static void setAssayModuleDefaultValues(sql, db) { |
---|
143 | "performing database upgrade: assay_module default values for boolean fields".grom() |
---|
144 | |
---|
145 | // are we running postgreSQL ? |
---|
146 | if (db == "org.postgresql.Driver") { |
---|
147 | try { |
---|
148 | sql.execute("UPDATE assay_module SET notify = FALSE WHERE notify IS NULL") |
---|
149 | } catch (Exception e) { |
---|
150 | println "setAssayModuleDefaultValues notify field couldn't be set to default value: " + e.getMessage() |
---|
151 | } |
---|
152 | try { |
---|
153 | sql.execute("UPDATE assay_module SET open_in_frame = TRUE WHERE open_in_frame IS NULL") |
---|
154 | } catch (Exception e) { |
---|
155 | println "setAssayModuleDefaultValues openInFrame field couldn't be set to default value: " + e.getMessage() |
---|
156 | println "Maybe gdt plugin is not updated yet after revision 109" |
---|
157 | } |
---|
158 | } |
---|
159 | |
---|
160 | // Are we running MySQL |
---|
161 | if( db == "com.mysql.jdbc.Driver" ) { |
---|
162 | try { |
---|
163 | sql.execute("UPDATE assay_module SET notify = 0 WHERE notify IS NULL") |
---|
164 | } catch (Exception e) { |
---|
165 | println "setAssayModuleDefaultValues notify field couldn't be set to default value: " + e.getMessage() |
---|
166 | } |
---|
167 | try { |
---|
168 | sql.execute("UPDATE assay_module SET open_in_frame = 1 WHERE open_in_frame IS NULL") |
---|
169 | } catch (Exception e) { |
---|
170 | println "setAssayModuleDefaultValues openInFrame field couldn't be set to default value: " + e.getMessage() |
---|
171 | println "Maybe gdt plugin is not updated yet after revision 109" |
---|
172 | } |
---|
173 | } |
---|
174 | } |
---|
175 | |
---|
176 | /** |
---|
177 | * Drop the unique constraint for the "name" column in the MappingColumn domain |
---|
178 | * |
---|
179 | * @param sql |
---|
180 | * @param db |
---|
181 | */ |
---|
182 | public static void dropMappingColumnNameConstraint(sql, db) { |
---|
183 | // are we running postgreSQL ? |
---|
184 | if (db == "org.postgresql.Driver") { |
---|
185 | try { |
---|
186 | // Check if constraint still exists |
---|
187 | if (sql.firstRow("SELECT * FROM pg_constraint WHERE contype='mapping_column_name_key")) |
---|
188 | sql.execute("ALTER TABLE mapping_column DROP CONSTRAINT mapping_column_name_key") |
---|
189 | else |
---|
190 | println "changeMappingColumnNameConstraint exiting, constraint doesn't exist" |
---|
191 | |
---|
192 | } catch (Exception e) { |
---|
193 | println "changeMappingColumnNameConstraint `name` field unique constraint couldn't be dropped: " + e.getMessage() |
---|
194 | } |
---|
195 | } |
---|
196 | } |
---|
197 | } |
---|