Project

General

Profile

Revision cf862eed

IDcf862eeda4b0e07de7ebb03d4ce04d26e6df740f
Parent b153def6
Child e793a739

Added by Andreas Müller 12 months ago

ref #6794 FeatureTreeMover first implementation (not yet ready)

View differences:

cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v55_58/FeatureTreeMover.java
1
/**
2
* Copyright (C) 2019 EDIT
3
* European Distributed Institute of Taxonomy
4
* http://www.e-taxonomy.eu
5
*
6
* The contents of this file are subject to the Mozilla Public License Version 1.1
7
* See LICENSE.TXT at the top of this package for the full license terms.
8
*/
9
package eu.etaxonomy.cdm.database.update.v55_58;
10

  
11
import java.sql.ResultSet;
12
import java.sql.SQLException;
13
import java.util.ArrayList;
14
import java.util.List;
15

  
16
import org.apache.log4j.Logger;
17

  
18
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
19
import eu.etaxonomy.cdm.database.ICdmDataSource;
20
import eu.etaxonomy.cdm.database.update.CaseType;
21
import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
22
import eu.etaxonomy.cdm.database.update.SchemaUpdateResult;
23
import eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase;
24

  
25
/**
26
 * @author a.mueller
27
 * @since 20.06.2019
28
 */
29
public class FeatureTreeMover extends SchemaUpdaterStepBase {
30

  
31

  
32
    @SuppressWarnings("unused")
33
    private static final Logger logger = Logger.getLogger(FeatureTreeMover.class);
34

  
35
    private static final String stepName = "Move FeatureTree to TermCollection";
36

  
37
    /**
38
     * @param stepName
39
     */
40
    protected FeatureTreeMover() {
41
        super(stepName);
42
    }
43

  
44
    public static final FeatureTreeMover NewInstance(List<ISchemaUpdaterStep> stepList){
45
        FeatureTreeMover result = new FeatureTreeMover();
46
        stepList.add(result);
47

  
48
        return result;
49
    }
50

  
51

  
52
    /**
53
     * {@inheritDoc}
54
     */
55
    @Override
56
    public void invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
57
            SchemaUpdateResult result) throws SQLException {
58

  
59
        int maxIdTermVoc = getMaxIdTermVoc(datasource, monitor, caseType, result);
60
        List<Integer> featureTreeIds = getFeatureTreeIds(datasource, monitor, caseType, result);
61
        for (Integer featureTreeId : featureTreeIds){
62
            maxIdTermVoc = moveFeatureTree(featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result);
63
        }
64
    }
65

  
66
    /**
67
     * @param featureTreeId
68
     * @param maxIdTermVoc
69
     * @param datasource
70
     * @param monitor
71
     * @param caseType
72
     * @param result
73
     * @return
74
     * @throws SQLException
75
     */
76
    private int moveFeatureTree(Integer featureTreeId, int maxIdTermVoc, ICdmDataSource datasource,
77
            IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) throws SQLException {
78

  
79
        maxIdTermVoc++;
80
        String attributes = "termType, created, updated, uuid, lsid_authority, lsid_lsid, lsid_namespace, lsid_object, lsid_revision, "
81
                + "protectedTitleCache, titleCache, createdBy_id, updatedBy_id, root_id, allowDuplicates";
82
        String sql = "INSERT INTO @@TermCollection@@ (DTYPE,"+attributes+",id, isFlat, orderRelevant) "
83
                + " SELECT 'TermTree', "+attributes+","+ maxIdTermVoc +",0,1 FROM @@FeatureTree@@ WHERE id="+featureTreeId;
84
        datasource.executeUpdate(caseType.replaceTableNames(sql));
85

  
86
        //AUD
87
        attributes = attributes + ",REV,REVTYPE";
88
        sql = "INSERT INTO @@TermCollection_AUD@@ (DTYPE,"+attributes+",id, isFlat, orderRelevant) "
89
                + " SELECT 'TermTree', "+attributes+","+ maxIdTermVoc +",0,1 FROM @@FeatureTree_AUD@@ WHERE id="+featureTreeId;
90
        datasource.executeUpdate(caseType.replaceTableNames(sql));
91

  
92
        updateSupplement("Annotation", "annotations_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, false);
93
        updateSupplement("Credit", "credits_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, true);
94
        updateSupplement("Extension", "extensions_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, false);
95
        updateSupplement("Identifier", "identifiers_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, true);
96
        updateSupplement("Marker", "markers_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, false);
97
        updateSupplement("OriginalSourceBase", "sources_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, false);
98
        updateSupplement("Representation", "representations_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, false);
99
        updateSupplement("RightsInfo", "rights_id", featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result, false);
100

  
101
        updateDescriptiveSystem(featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result);
102
        updateTermTreeNode(featureTreeId, maxIdTermVoc, datasource, monitor, caseType, result);
103

  
104
        xx  treeIndex update;
105

  
106

  
107
        return maxIdTermVoc;
108
    }
109

  
110
    /**
111
     * @param featureTreeId
112
     * @param maxIdTermVoc
113
     * @param datasource
114
     * @param monitor
115
     * @param caseType
116
     * @param result
117
     * @throws SQLException
118
     */
119
    private void updateTermTreeNode(Integer featureTreeId, int maxIdTermVoc, ICdmDataSource datasource,
120
            IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) throws SQLException {
121
        String update = "UPDATE @@TermRelation@@ "
122
                + " SET graph_id = " + maxIdTermVoc
123
                + " WHERE featureTree_id =" + featureTreeId;
124

  
125
        datasource.executeUpdate(caseType.replaceTableNames(update));
126

  
127
        update = "UPDATE @@TermRelation_AUD@@ "
128
                + " SET graph_id = " + maxIdTermVoc
129
                + " WHERE featureTree_id =" + featureTreeId;
130

  
131
        datasource.executeUpdate(caseType.replaceTableNames(update));
132

  
133
    }
134

  
135
    /**
136
     * @param featureTreeId
137
     * @param maxIdTermVoc
138
     * @param datasource
139
     * @param monitor
140
     * @param caseType
141
     * @param result
142
     * @throws SQLException
143
     */
144
    private void updateDescriptiveSystem(Integer featureTreeId, int maxIdTermVoc, ICdmDataSource datasource,
145
            IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) throws SQLException {
146
        String update = "UPDATE @@DescriptiveDataSet@@ "
147
                + " SET descriptiveSystem_id = " + maxIdTermVoc
148
                + " WHERE descriptiveSystemOld_id =" + featureTreeId;
149

  
150
        datasource.executeUpdate(caseType.replaceTableNames(update));
151

  
152
        update = "UPDATE @@DescriptiveDataSet_AUD@@ "
153
                + " SET descriptiveSystem_id = " + maxIdTermVoc
154
                + " WHERE descriptiveSystemOld_id =" + featureTreeId;
155

  
156
        datasource.executeUpdate(caseType.replaceTableNames(update));
157

  
158
    }
159

  
160
    /**
161
     * @param string
162
     * @param featureTreeId
163
     * @param maxIdTermVoc
164
     * @param datasource
165
     * @param monitor
166
     * @param caseType
167
     * @param result
168
     * @param withSortIndex
169
     * @throws SQLException
170
     */
171
    private void updateSupplement(String supplement, String attr, Integer featureTreeId, int maxIdTermVoc, ICdmDataSource datasource,
172
            IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result, boolean withSortIndex) throws SQLException {
173

  
174
        if(withSortIndex){
175
            attr = attr + ",sortIndex";
176
        }
177
        String sql = "INSERT INTO @@TermCollection_"+supplement+"@@ (TermCollection_id, "+attr+") "
178
                + " SELECT FeatureTree_id, "+attr+" FROM @@FeatureTree_"+supplement+"@@ WHERE FeatureTree_id="+featureTreeId;
179
        datasource.executeUpdate(caseType.replaceTableNames(sql));
180

  
181
        sql = "INSERT INTO @@TermCollection_"+supplement+"_AUD@@ (TermCollection_id, "+attr+",REV,REVTYPE) "
182
                + " SELECT FeatureTree_id, "+attr+",REV,REVTYPE FROM @@FeatureTree_"+supplement+"_AUD@@ WHERE FeatureTree_id="+featureTreeId;
183
        datasource.executeUpdate(caseType.replaceTableNames(sql));
184

  
185

  
186
    }
187

  
188
    /**
189
     * @param datasource
190
     * @param monitor
191
     * @param caseType
192
     * @param result
193
     * @return
194
     * @throws SQLException
195
     */
196
    private List<Integer> getFeatureTreeIds(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
197
            SchemaUpdateResult updateResult) throws SQLException {
198

  
199
        List<Integer> result = new ArrayList<>();
200
        String sql = "SELECT DISTINCT id FROM " +caseType.transformTo("FeatureTree") + " ORDER BY id";
201
        ResultSet rs = datasource.executeQuery(sql);
202
        while (rs.next()){
203
            Integer id = rs.getInt("id");
204
            result.add(id);
205
        }
206

  
207
        sql = "SELECT DISTINCT id FROM " +caseType.transformTo("FeatureTree_AUD") + " ORDER BY id";
208
        rs = datasource.executeQuery(sql);
209
        while (rs.next()){
210
            Integer id = rs.getInt("id");
211
            if (!result.contains(id)){
212
                result.add(id);
213
            }
214
        }
215

  
216
        return result;
217
    }
218

  
219
    /**
220
     * @param datasource
221
     * @param monitor
222
     * @param caseType
223
     * @param result
224
     * @return
225
     * @throws SQLException
226
     * @throws NumberFormatException
227
     */
228
    private int getMaxIdTermVoc(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
229
            SchemaUpdateResult result) throws NumberFormatException, SQLException {
230
        String sql = "SELECT max(id) FROM " +caseType.transformTo("TermCollection");
231
        int maxId = Integer.valueOf(datasource.getSingleValue(sql).toString());
232
        sql = "SELECT max(id) FROM " +caseType.transformTo("TermCollection_AUD");
233
        int maxIdAud = Integer.valueOf(datasource.getSingleValue(sql).toString());
234
        return Math.max(maxId, maxIdAud);
235
    }
236

  
237
}
cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v55_58/SchemaUpdater_55_58.java
90 90
       stepList.add(step);
91 91

  
92 92
       //#6794 change featuretree_id to graph_id
93
       stepName = "change featuretree_id to graph_id";
94
       tableName = "TermRelation";
95
       String oldColumnName = "featureTree_id";
96
       newColumnName = "graph_id";
97
       step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
98
       stepList.add(step);
93
//       stepName = "change featuretree_id to graph_id";
94
//       tableName = "TermRelation";
95
//       String oldColumnName = "featureTree_id";
96
//       newColumnName = "graph_id";
97
//       step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
98
//       stepList.add(step);
99

  
99 100

  
100 101
       //#6794 change feature_id to term_id
101 102
       stepName = "change feature_id to term_id";
102 103
       tableName = "TermRelation";
103
       oldColumnName = "feature_id";
104
       String oldColumnName = "feature_id";
104 105
       newColumnName = "term_id";
105 106
       step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
106 107
       stepList.add(step);
......
140 141
       //#6794
141 142
       renameTermVocToTermCollection(stepList);
142 143

  
144

  
143 145
       //#6794 add root_id column to TermCollection
144 146
       stepName = "add root_id column to TermCollection";
145 147
       tableName = "TermCollection";
......
148 150
       stepList.add(step);
149 151

  
150 152
       //#6794
153
       stepName = "add graph_id column to TermRelation";
154
       tableName = "TermRelation";
155
       newColumnName = "graph_id";
156
       step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, !NOT_NULL, "TermCollection");
157
       stepList.add(step);
158

  
159
       //#6794
160
       stepName = "change descriptiveSystem_id to descriptiveSystemOld_id";
161
       tableName = "DescriptiveDataSet";
162
       oldColumnName = "descriptiveSystem_id";
163
       newColumnName = "descriptiveSystemOld_id";
164
       step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
165
       stepList.add(step);
166

  
167
       //#6794
168
       stepName = "add descriptiveSystem_id column to DescriptiveDataSet";
169
       tableName = "DescriptiveDataSet";
170
       newColumnName = "descriptiveSystem_id";
171
       step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, !NOT_NULL, "TermCollection");
172
       stepList.add(step);
173

  
174
       //#6794
151 175
       addBooleansToTermVocabulary(stepList);
152 176

  
177
       //#6794
178
       FeatureTreeMover.NewInstance(stepList);
179

  
153 180
       //#7470
154 181
       updateFreeTextTypeDesignation(stepList);
155 182

  

Also available in: Unified diff

Add picture from clipboard (Maximum size: 40 MB)