source: trunk/grails-app/conf/dbnp/configuration/DatabaseUpgrade.groovy @ 1957

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