root/trunk/grails-app/conf/dbnp/configuration/DatabaseUpgrade.groovy @ 2089

Revision 2089, 17.6 KB (checked in by work@…, 2 years ago)

- fix shibboleth user issue (where null)

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