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: 1430 $ |
---|
13 | * $Author: s.h.sikkema@gmail.com $ |
---|
14 | * $Date: 2011-01-21 20:05:36 +0000 (vr, 21 jan 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 | } |
---|
35 | |
---|
36 | /** |
---|
37 | * execute database change r1245 / r1246 if required |
---|
38 | * @param sql |
---|
39 | * @param db |
---|
40 | */ |
---|
41 | public static void changeStudyDescription(sql, db) { |
---|
42 | // check if we need to perform this upgrade |
---|
43 | if (sql.firstRow("SELECT count(*) as total FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").total > 0) { |
---|
44 | // grom that we are performing the upgrade |
---|
45 | "performing database upgrade: study description".grom() |
---|
46 | |
---|
47 | // database upgrade required |
---|
48 | try { |
---|
49 | // get the template field id |
---|
50 | def id = sql.firstRow("SELECT id FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").id |
---|
51 | |
---|
52 | // iterate through all obsolete study descriptions |
---|
53 | sql.eachRow("SELECT study_id, template_text_fields_elt as description FROM study_template_text_fields WHERE template_text_fields_idx='Description'") { row -> |
---|
54 | // migrate the template description to the study object itself |
---|
55 | // so we don't have to bother with sql injections, etc |
---|
56 | def study = Study.findById( row.study_id ) |
---|
57 | study.setFieldValue('description', row.description) |
---|
58 | if (!(study.validate() && study.save())) { |
---|
59 | throw new Exception("could not save study with id ${row.study_id}") |
---|
60 | } |
---|
61 | } |
---|
62 | |
---|
63 | // delete all obsolete descriptions |
---|
64 | sql.execute("DELETE FROM study_template_text_fields WHERE template_text_fields_idx='Description'") |
---|
65 | |
---|
66 | // find all template id's where this field is used |
---|
67 | sql.eachRow("SELECT DISTINCT template_fields_id, fields_idx FROM template_template_field WHERE template_field_id=${id}") { row -> |
---|
68 | // delete the template_template_field reference |
---|
69 | sql.execute("DELETE FROM template_template_field WHERE template_field_id=${id} AND template_fields_id=${row.template_fields_id}") |
---|
70 | |
---|
71 | // and lower the idx-es of the remaining fields |
---|
72 | 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}") |
---|
73 | } |
---|
74 | |
---|
75 | // and delete the obsolete template field |
---|
76 | sql.execute("DELETE FROM template_field WHERE id=${id}") |
---|
77 | } catch (Exception e) { |
---|
78 | "changeStudyDescription database upgrade failed: " + e.getMessage() |
---|
79 | } |
---|
80 | } |
---|
81 | } |
---|
82 | |
---|
83 | /** |
---|
84 | * execute database change r1327 if required |
---|
85 | * @param sql |
---|
86 | * @param db |
---|
87 | */ |
---|
88 | public static void changeStudyDescriptionToText(sql, db) { |
---|
89 | // are we running postgreSQL ? |
---|
90 | if (db == "org.postgresql.Driver") { |
---|
91 | // check if column 'description' in table 'study' is not of type 'text' |
---|
92 | 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) { |
---|
93 | // grom that we are performing the upgrade |
---|
94 | "performing database upgrade: study description to text".grom() |
---|
95 | |
---|
96 | // database upgrade required |
---|
97 | try { |
---|
98 | // change the datatype of study::description to text |
---|
99 | sql.execute("ALTER TABLE study ALTER COLUMN description TYPE text") |
---|
100 | } catch (Exception e) { |
---|
101 | "changeStudyDescriptionToText database upgrade failed: " + e.getMessage() |
---|
102 | } |
---|
103 | } |
---|
104 | } |
---|
105 | } |
---|
106 | |
---|
107 | /** |
---|
108 | * it appears that some TEXT template fields are not of type 'text' |
---|
109 | * due to an issue in how Grails' GORM works with domain inheritance |
---|
110 | * (see http://jira.codehaus.org/browse/GRAILS-6754) |
---|
111 | * @param sql |
---|
112 | * @param db |
---|
113 | */ |
---|
114 | public static void changeTemplateTextFieldSignatures(sql, db) { |
---|
115 | if (db == "org.postgresql.Driver") { |
---|
116 | // check if any TEXT template fields are of type 'text' |
---|
117 | 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';") |
---|
118 | { row -> |
---|
119 | "performing database upgrade: ${row.tablename} template_text_fields_string/elt to text".grom() |
---|
120 | try { |
---|
121 | // change the datatype of text fields to text |
---|
122 | sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN template_text_fields_elt TYPE text", row.tablename)) |
---|
123 | sql.execute(sprintf("ALTER TABLE %s ALTER COLUMN template_text_fields_string TYPE text", row.tablename)) |
---|
124 | |
---|
125 | } catch (Exception e) { |
---|
126 | "changeTemplateTextFieldSignatures database upgrade failed: " + e.getMessage() |
---|
127 | } |
---|
128 | } |
---|
129 | } |
---|
130 | } |
---|
131 | } |
---|