root/trunk/grails-app/conf/DatabaseUpgrade.groovy @ 1786

Revision 1786, 15.5 KB (checked in by robert@…, 3 years ago)

Added Environmental Ontology (1069) to the subject.species field, to fix issue #353.

  • Property svn:keywords set to Rev Author Date
Line 
1import groovy.sql.Sql
2import dbnp.studycapturing.Study
3import 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$
13 * $Author$
14 * $Date$
15 */
16class 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)                    // r1490
35                dropMappingColumnNameConstraint(sql, db)                // r1525
36                makeMappingColumnValueNullable(sql, db)                 // r1525
37                alterStudyAndAssay(sql, db)                                             // r1594
38                fixDateCreatedAndLastUpdated(sql, db)
39                dropAssayModulePlatform(sql, db)                                // r1689
40                makeStudyTitleAndTemplateNamesUnique(sql, db)   // #401, #406
41        }
42
43        /**
44         * execute database change r1245 / r1246 if required
45         * @param sql
46         * @param db
47         */
48        public static void changeStudyDescription(sql, db) {
49                // check if we need to perform this upgrade
50                if (sql.firstRow("SELECT count(*) as total FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").total > 0) {
51                        // grom that we are performing the upgrade
52                        if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: study description".grom()
53
54                        // database upgrade required
55                        try {
56                                // get the template field id
57                                def id = sql.firstRow("SELECT id FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").id
58
59                                // iterate through all obsolete study descriptions
60                                sql.eachRow("SELECT study_id, template_text_fields_elt as description FROM study_template_text_fields WHERE template_text_fields_idx='Description'") { row ->
61                                        // migrate the template description to the study object itself
62                                        // so we don't have to bother with sql injections, etc
63                                        def study = Study.findById(row.study_id)
64                                        study.setFieldValue('description', row.description)
65                                        if (!(study.validate() && study.save())) {
66                                                throw new Exception("could not save study with id ${row.study_id}")
67                                        }
68                                }
69
70                                // delete all obsolete descriptions
71                                sql.execute("DELETE FROM study_template_text_fields WHERE template_text_fields_idx='Description'")
72
73                                // find all template id's where this field is used
74                                sql.eachRow("SELECT DISTINCT template_fields_id, fields_idx FROM template_template_field WHERE template_field_id=${id}") { row ->
75                                        // delete the template_template_field reference
76                                        sql.execute("DELETE FROM template_template_field WHERE template_field_id=${id} AND template_fields_id=${row.template_fields_id}")
77
78                                        // and lower the idx-es of the remaining fields
79                                        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}")
80                                }
81
82                                // and delete the obsolete template field
83                                sql.execute("DELETE FROM template_field WHERE id=${id}")
84                        } catch (Exception e) {
85                                println "changeStudyDescription database upgrade failed: " + e.getMessage()
86                        }
87                }
88        }
89
90        /**
91         * execute database change r1327 if required
92         * @param sql
93         * @param db
94         */
95        public static void changeStudyDescriptionToText(sql, db) {
96                // are we running postgreSQL ?
97                if (db == "org.postgresql.Driver") {
98                        // check if column 'description' in table 'study' is not of type 'text'
99                        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) {
100                                // grom that we are performing the upgrade
101                                if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: study description to text".grom()
102
103                                // database upgrade required
104                                try {
105                                        // change the datatype of study::description to text
106                                        sql.execute("ALTER TABLE study ALTER COLUMN description TYPE text")
107                                } catch (Exception e) {
108                                        println "changeStudyDescriptionToText database upgrade failed: " + e.getMessage()
109                                }
110                        }
111                }
112        }
113
114        /**
115         * it appears that some TEXT template fields are not of type 'text'
116         * due to an issue in how Grails' GORM works with domain inheritance
117         * (see http://jira.codehaus.org/browse/GRAILS-6754)
118         * @param sql
119         * @param db
120         */
121        public static void changeTemplateTextFieldSignatures(sql, db) {
122                if (db == "org.postgresql.Driver") {
123                        // check if any TEXT template fields are of type 'text'
124                        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';")
125                                { row ->
126                                        if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: ${row.tablename} template_text_fields_string/elt to text".grom()
127                                        try {
128                                                // change the datatype of text fields to text
129                                                sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN template_text_fields_elt TYPE text", row.tablename))
130                                                sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN template_text_fields_string TYPE text", row.tablename))
131
132                                        } catch (Exception e) {
133                                                println "changeTemplateTextFieldSignatures database upgrade failed: " + e.getMessage()
134                                        }
135                                }
136                }
137        }
138
139        /**
140         * The fields 'notify' and 'openInFrame' have been added to AssayModule. However, there
141         * seems to be no method to setting the default values of these fields in the database. They
142         * are set to NULL by default, so all existing fields have 'NULL' set.
143         * This method sets the default values
144         * @param sql
145         * @param db
146         */
147        public static void setAssayModuleDefaultValues(sql, db) {
148                // do we need to perform this upgrade?
149                if ((db == "org.postgresql.Driver" || db == "com.mysql.jdbc.Driver") &&
150                        (sql.firstRow("SELECT * FROM assay_module WHERE notify IS NULL") || sql.firstRow("SELECT * FROM assay_module WHERE open_in_frame IS NULL"))
151                ) {
152                        if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: assay_module default values for boolean fields".grom()
153
154                        try {
155                                sql.execute("UPDATE assay_module SET notify=" + ((db == "org.postgresql.Driver") ? 'FALSE' : '0') + " WHERE notify IS NULL")
156                        } catch (Exception e) {
157                                println "setAssayModuleDefaultValues database upgrade failed, notify field couldn't be set to default value: " + e.getMessage()
158                        }
159
160                        // set open_in_frame?
161                        try {
162                                sql.execute("UPDATE assay_module SET open_in_frame=" + ((db == "org.postgresql.Driver") ? 'TRUE' : '1') + " WHERE open_in_frame IS NULL")
163                        } catch (Exception e) {
164                                // Maybe gdt plugin is not updated yet after revision 109 ?
165                                println "setAssayModuleDefaultValues database upgrade failed, openInFrame field couldn't be set to default value: " + e.getMessage()
166                        }
167                }
168        }
169
170        /**
171         * Drop the unique constraint for the "name" column in the MappingColumn domain
172         *
173         * @param sql
174         * @param db
175         */
176        public static void dropMappingColumnNameConstraint(sql, db) {
177                // are we running postgreSQL ?
178                if (db == "org.postgresql.Driver") {
179                        if (sql.firstRow("SELECT * FROM pg_constraint WHERE contype='mapping_column_name_key'")) {
180                                if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: mapping column name constraint".grom()
181                                try {
182                                        // Check if constraint still exists
183                                        sql.execute("ALTER TABLE mapping_column DROP CONSTRAINT mapping_column_name_key")
184                                } catch (Exception e) {
185                                        println "dropMappingColumnNameConstraint database upgrade failed, `name` field unique constraint couldn't be dropped: " + e.getMessage()
186                                }
187                        }
188                }
189        }
190
191        /**
192         * the importer requires the value field to be nullable
193         * @param sql
194         * @param db
195         */
196        public static void makeMappingColumnValueNullable(sql, db) {
197                // are we running postgreSQL?
198                if (db == "org.postgresql.Driver") {
199                        // do we need to perform this update?
200                        if (sql.firstRow("SELECT * FROM information_schema.columns WHERE columns.table_name='mapping_column' AND columns.column_name='value' AND is_nullable='NO'")) {
201                                if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: making mapping_column::value nullable".grom()
202
203                                try {
204                                        sql.execute("ALTER TABLE mapping_column ALTER COLUMN value DROP NOT NULL")
205                                } catch (Exception e) {
206                                        println "makeMappingColumnValueNullable database upgrade failed: " + e.getMessage()
207                                }
208                        }
209                }
210        }
211
212        /**
213         * The field study.code has been set to be nullable
214         * The field assay.externalAssayId has been removed
215         * @param sql
216         * @param db
217         */
218        public static void alterStudyAndAssay(sql, db) {
219                def updated = false
220
221                // are we running postgreSQL ?
222                if (db == "org.postgresql.Driver") {
223                        // see if table assay contains a column external_assayid
224                        if (sql.firstRow("SELECT * FROM information_schema.columns WHERE columns.table_name='assay' AND columns.column_name='external_assayid'")) {
225                                if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: dropping column 'external_assayid' from table 'assay'".grom()
226
227                                try {
228                                        sql.execute("ALTER TABLE assay DROP COLUMN external_assayid")
229                                        updated = true
230                                } catch (Exception e) {
231                                        println "alterStudyAndAssay database upgrade failed, externalAssayId could not be removed from assay: " + e.getMessage()
232                                }
233
234                        }
235
236                        // see if table study contains a column code which is not nullable
237                        if (sql.firstRow("SELECT * FROM information_schema.columns WHERE columns.table_name='study' AND columns.column_name='code' AND is_nullable='NO'")) {
238                                if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: dropping column 'code' from table 'study'".grom()
239
240                                try {
241                                        sql.execute("ALTER TABLE study ALTER COLUMN code DROP NOT NULL")
242                                        updated = true
243                                } catch (Exception e) {
244                                        println "alterStudyAndAssay database upgrade failed, study.code could not be set to accept null values: " + e.getMessage()
245                                }
246                        }
247
248                        // Load all studies and save them again. This prevents errors on saving later
249                        if (updated) {
250                                if (String.metaClass.getMetaMethod("grom")) "re-saving studies...".grom()
251
252                                Study.list().each { study ->
253                                        if (String.metaClass.getMetaMethod("grom")) "re-saving study: ${study}".grom()
254                                        study.save()
255                                }
256                        }
257                }
258        }
259
260        /**
261         * make sure all date_created and last_updated columns are NOT nullable, and
262         * set values to now() of they are null
263         * @param sql
264         * @param db
265         */
266        public static void fixDateCreatedAndLastUpdated(sql, db) {
267                // are we running PostgreSQL?
268                if (db == "org.postgresql.Driver") {
269                        // see if we need to modify anything?
270                        sql.eachRow("SELECT table_name,column_name FROM information_schema.columns WHERE column_name IN ('last_updated', 'date_created') AND is_nullable='YES'") { row ->
271                                // grom what we are doing
272                                if (String.metaClass.getMetaMethod("grom")) "fixing nullable for ${row.table_name}:${row.column_name}".grom()
273
274                                // fix database
275                                try {
276                                        // setting all null values to now()
277                                        sql.execute(sprintf("UPDATE %s SET %s=now() WHERE %s IS NULL",row.table_name,row.column_name,row.column_name))
278
279                                        // and alter the table to disallow null values
280                                        sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN %s SET NOT NULL",row.table_name,row.column_name))
281                                } catch (Exception e) {
282                                        println "fixDateCreatedAndLastUpdated database upgrade failed: " + e.getMessage()
283                                }
284                        }
285                }
286        }
287       
288       
289        /**
290         * drops the field platform from assay modules
291         * @param sql
292         * @param db
293         */
294        public static void dropAssayModulePlatform(sql, db) {
295                // are we running postgreSQL?
296                if (db == "org.postgresql.Driver") {
297                        // do we need to perform this update?
298                        if (sql.firstRow("SELECT * FROM information_schema.columns WHERE columns.table_name='assay_module' AND columns.column_name='platform'")) {
299                                if (String.metaClass.getMetaMethod("grom")) "performing database upgrade: removing assayModule platform".grom()
300
301                                try {
302                                        sql.execute("ALTER TABLE assay_module DROP COLUMN platform")
303                                } catch (Exception e) {
304                                        println "dropAssayModulePlatform database upgrade failed: " + e.getMessage()
305                                }
306                        }
307                }
308        }
309
310        /**
311         * Make sure the study title, template name and template field names are unique
312         * @param sql
313         * @param db
314         */
315        public static void makeStudyTitleAndTemplateNamesUnique(sql, db) {
316                def titleCount,title,newTitle,templateFieldTypeCount,templateFieldUnitCount
317                def grom = false
318
319                // are we running postgreSQL?
320                if (db == "org.postgresql.Driver") {
321                        // yes, find all duplicate study titles
322                        sql.eachRow("   SELECT DISTINCT a.title, 'study' as tablename, '' as entity FROM study a WHERE (SELECT count(*) FROM study b WHERE b.title=a.title) > 1\
323                                                        UNION SELECT DISTINCT d.name as title, 'template' as tablename, d.entity FROM template d WHERE (SELECT count(*) FROM template c WHERE d.name=c.name AND d.entity=c.entity) > 1\
324                                                        UNION SELECT DISTINCT e.templatefieldname as title, 'template_field' as tablename, e.templatefieldentity as entity FROM template_field e WHERE (SELECT count(*) FROM template_field f WHERE e.templatefieldname=f.templatefieldname AND e.templatefieldentity=f.templatefieldentity) > 1\
325                                                        ") { row ->
326
327                                // grom what we are doing
328                                if (String.metaClass.getMetaMethod("grom") && !grom) {
329                                        "making study titles, template names and template_field names unique".grom()
330                                        grom = true
331                                }
332
333                                // set work variables
334                                titleCount      = 1
335                                title           = row.title.replace("'","\'")
336
337                                // check what we are updating
338                                switch (row.tablename) {
339                                        case "study":
340                                                // update study titles
341                                                sql.eachRow(sprintf("SELECT id FROM study WHERE title='%s'", title)) { studyRow ->
342                                                        newTitle = title + ((titleCount>1) ? " - ${titleCount}" : "")
343                                                        sql.execute(sprintf("UPDATE study SET title='%s' WHERE id=%d",newTitle,studyRow.id))
344                                                        titleCount++
345                                                }
346                                                break
347                                        case "template":
348                                                // update template names
349                                                sql.eachRow(sprintf("SELECT id FROM template WHERE name='%s' AND entity='%s'", title, row.entity)) { templateRow ->
350                                                        newTitle = title + ((titleCount>1) ? " - ${titleCount}" : "")
351                                                        sql.execute(sprintf("UPDATE template SET name='%s' WHERE id=%d",newTitle,templateRow.id))
352                                                        titleCount++
353                                                }
354                                                break
355                                        case "template_field":
356                                                templateFieldTypeCount = [:]
357                                                templateFieldUnitCount = [:]
358
359                                                // update template_field names
360                                                sql.eachRow(sprintf("SELECT id,templatefieldunit as unit,templatefieldtype as type FROM template_field WHERE templatefieldname='%s' AND templatefieldentity='%s'", title, row.entity)) { templateFieldRow ->
361                                                        if (templateFieldRow.unit) {
362                                                                templateFieldUnitCount[ templateFieldRow.unit ] = (templateFieldUnitCount[ templateFieldRow.unit ]) ? templateFieldUnitCount[ templateFieldRow.unit ]+1 : 1
363                                                                newTitle = "${title} (${templateFieldRow.unit})" + ((templateFieldUnitCount[ templateFieldRow.unit ]>1) ? " - ${templateFieldUnitCount[ templateFieldRow.unit ]}" : "")
364                                                        } else {
365                                                                templateFieldTypeCount[ templateFieldRow.type ] = (templateFieldTypeCount[ templateFieldRow.type ]) ? templateFieldTypeCount[ templateFieldRow.type ]+1 : 1
366                                                                newTitle = "${title} (${templateFieldRow.type})" + ((templateFieldTypeCount[ templateFieldRow.type ]>1) ? " - ${templateFieldTypeCount[ templateFieldRow.type ]}" : "")
367                                                                titleCount++
368                                                        }
369                                                        sql.execute(sprintf("UPDATE template_field SET templatefieldname='%s' WHERE id=%d",newTitle,templateFieldRow.id))
370                                                }
371                                                break
372                                        default:
373                                                // this shouldn't happen
374                                                break
375                                }
376                        }
377                }
378        }
379}
Note: See TracBrowser for help on using the browser.