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: 1403 $ |
---|
13 | * $Author: business@keesvanbochove.nl $ |
---|
14 | * $Date: 2011-01-18 09:46:12 +0000 (di, 18 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 | */ |
---|
40 | public static void changeStudyDescription(sql, db) { |
---|
41 | // check if we need to perform this upgrade |
---|
42 | if (sql.firstRow("SELECT count(*) as total FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").total > 0) { |
---|
43 | // grom that we are performing the upgrade |
---|
44 | "performing database upgrade: study description".grom() |
---|
45 | |
---|
46 | // database upgrade required |
---|
47 | try { |
---|
48 | // get the template field id |
---|
49 | def id = sql.firstRow("SELECT id FROM template_field WHERE templatefieldentity='dbnp.studycapturing.Study' AND templatefieldname='Description'").id |
---|
50 | |
---|
51 | // iterate through all obsolete study descriptions |
---|
52 | sql.eachRow("SELECT study_id, template_text_fields_elt as description FROM study_template_text_fields WHERE template_text_fields_idx='Description'") { row -> |
---|
53 | // migrate the template description to the study object itself |
---|
54 | // so we don't have to bother with sql injections, etc |
---|
55 | def study = Study.findById( row.study_id ) |
---|
56 | study.setFieldValue('description', row.description) |
---|
57 | if (!(study.validate() && study.save())) { |
---|
58 | throw new Exception("could not save study with id ${row.study_id}") |
---|
59 | } |
---|
60 | } |
---|
61 | |
---|
62 | // delete all obsolete descriptions |
---|
63 | sql.execute("DELETE FROM study_template_text_fields WHERE template_text_fields_idx='Description'") |
---|
64 | |
---|
65 | // find all template id's where this field is used |
---|
66 | sql.eachRow("SELECT DISTINCT template_fields_id, fields_idx FROM template_template_field WHERE template_field_id=${id}") { row -> |
---|
67 | // delete the template_template_field reference |
---|
68 | sql.execute("DELETE FROM template_template_field WHERE template_field_id=${id} AND template_fields_id=${row.template_fields_id}") |
---|
69 | |
---|
70 | // and lower the idx-es of the remaining fields |
---|
71 | 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}") |
---|
72 | } |
---|
73 | |
---|
74 | // and delete the obsolete template field |
---|
75 | sql.execute("DELETE FROM template_field WHERE id=${id}") |
---|
76 | } catch (Exception e) { |
---|
77 | "changeStudyDescription database upgrade failed: " + e.getMessage() |
---|
78 | } |
---|
79 | } |
---|
80 | } |
---|
81 | |
---|
82 | /** |
---|
83 | * execute database change r1327 if required |
---|
84 | * @param sql |
---|
85 | */ |
---|
86 | public static void changeStudyDescriptionToText(sql, db) { |
---|
87 | // are we running postgreSQL ? |
---|
88 | if (db == "org.postgresql.Driver") { |
---|
89 | // check if column 'description' in table 'study' is not of type 'text' |
---|
90 | 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) { |
---|
91 | // grom that we are performing the upgrade |
---|
92 | "performing database upgrade: study description to text".grom() |
---|
93 | |
---|
94 | // database upgrade required |
---|
95 | try { |
---|
96 | // change the datatype of study::description to text |
---|
97 | sql.execute("ALTER TABLE study ALTER COLUMN description TYPE text") |
---|
98 | } catch (Exception e) { |
---|
99 | "changeStudyDescriptionToText database upgrade failed: " + e.getMessage() |
---|
100 | } |
---|
101 | } |
---|
102 | } |
---|
103 | } |
---|
104 | |
---|
105 | public static void changeTemplateTextFieldSignatures(sql, db) { |
---|
106 | if (db == "org.postgresql.Driver") { |
---|
107 | // check if any TEXT template fields are of type 'text' |
---|
108 | sql.eachRow("SELECT columns.table_name FROM information_schema.columns WHERE columns.table_schema::text = 'public'::text AND column_name='template_text_fields_elt' AND data_type != 'text';") |
---|
109 | { row -> |
---|
110 | "performing database upgrade: ${row.table_name} template_text_fields_string/elt to text".grom() |
---|
111 | try { |
---|
112 | // change the datatype of study::description to text |
---|
113 | sql.execute("ALTER TABLE ${row.table_name} ALTER COLUMN template_text_fields_elt TYPE text") |
---|
114 | sql.execute("ALTER TABLE ${row.table_name} ALTER COLUMN template_text_fields_string TYPE text") |
---|
115 | |
---|
116 | } catch (Exception e) { |
---|
117 | "changeTemplateTextFieldSignatures database upgrade failed: " + e.getMessage() |
---|
118 | } |
---|
119 | } |
---|
120 | } |
---|
121 | } |
---|
122 | } |
---|