Project

General

Profile

Revision 4010183a

ID4010183ae94630e236ee90181e7d5a8123eb557f
Parent 98da6d7e
Child eed1ac3d

Added by Andreas Müller almost 2 years ago

fix #7334 update script for pro parte synonyms to concept relationships

View differences:

cdmlib-model/src/main/java/eu/etaxonomy/cdm/model/taxon/TaxonRelationshipType.java
68 68
	private static final UUID uuidMisappliedNameFor = UUID.fromString("1ed87175-59dd-437e-959e-0d71583d8417");
69 69
	private static final UUID uuidProParteMisappliedNameFor = UUID.fromString("b59b4bd2-11ff-45d1-bae2-146efdeee206");
70 70
	private static final UUID uuidPartialMisappliedNameFor = UUID.fromString("859fb615-b0e8-440b-866e-8a19f493cd36");
71
	private static final UUID uuidProParteSynonymFor = UUID.fromString("8a896603-0fa3-44c6-9cd7-df2d8792e577");
72
	private static final UUID uuidPartialSynonymFor = UUID.fromString("9d7a5e56-973c-474c-b6c3-a1cb00833a3c");
71
	public static final UUID uuidProParteSynonymFor = UUID.fromString("8a896603-0fa3-44c6-9cd7-df2d8792e577");
72
	public static final UUID uuidPartialSynonymFor = UUID.fromString("9d7a5e56-973c-474c-b6c3-a1cb00833a3c");
73 73
	private static final UUID uuidInvalidDesignationFor = UUID.fromString("605b1d01-f2b1-4544-b2e0-6f08def3d6ed");
74 74

  
75 75
	private static final UUID uuidContradiction = UUID.fromString("a8f03491-2ad6-4fae-a04c-2a4c117a2e9b");
cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SingleTermUpdater.java
36 36
	 */
37 37
	@Deprecated
38 38
	public static final SingleTermUpdater NewInstance(String stepName, UUID uuidTerm, String description,  String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm){
39
		return new SingleTermUpdater(stepName, null, uuidTerm, null, description, label, abbrev, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
39
		return new SingleTermUpdater(stepName, null, uuidTerm, null, null, description, label, abbrev, null, null, null, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
40 40
	}
41 41

  
42
	public static final SingleTermUpdater NewInstance(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String description,  String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm){
43
		return new SingleTermUpdater(stepName, termType, uuidTerm, idInVocabulary, description, label, abbrev, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
42
	public static final SingleTermUpdater NewInstance(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String symbol,
43
	        String description,  String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm){
44
		return new SingleTermUpdater(stepName, termType, uuidTerm, idInVocabulary, symbol,
45
		        description, label, abbrev, null, null, null, dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
44 46
	}
45 47

  
48
	   public static final SingleTermUpdater NewReverseInstance(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String symbol,
49
	           String description,  String label, String abbrev, String reverseDescription, String reverseLabel, String reverseAbbrev,
50
	           String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm){
51
	        return new SingleTermUpdater(stepName, termType, uuidTerm, idInVocabulary,symbol,
52
	                description, label, abbrev, reverseDescription, reverseLabel, reverseAbbrev,
53
	                dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
54
	    }
55

  
46 56

  
47 57
	private final UUID uuidTerm ;
48 58
	private final String description;
......
61 71
	private final String idInVocabulary;
62 72
	private boolean symmetric = false;
63 73
	private boolean transitive = false;
74
	private String symbol;
64 75

  
65 76

  
66 77

  
67
	private SingleTermUpdater(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String description, String label, String abbrev, String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm) {
78
	private SingleTermUpdater(String stepName, TermType termType, UUID uuidTerm, String idInVocabulary, String symbol,
79
	        String description, String label, String abbrev, String reverseDescription, String reverseLabel, String reverseAbbrev,
80
	        String dtype, UUID uuidVocabulary, UUID uuidLanguage, boolean isOrdered, UUID uuidAfterTerm) {
68 81
		super(stepName);
69 82
		this.termType = termType;
70 83
		this.idInVocabulary = idInVocabulary;
84
		this.symbol = symbol;
71 85
		this.abbrev = abbrev;
72 86
		this.description = description;
73 87
		this.dtype = dtype;
......
77 91
		this.uuidVocabulary = uuidVocabulary;
78 92
		this.uuidAfterTerm = uuidAfterTerm;
79 93
		this.uuidLanguage = uuidLanguage;
94
		this.reverseDescription = reverseDescription;
95
		this.reverseLabel = reverseLabel;
96
		this.reverseAbbrev = reverseAbbrev;
80 97
	}
81 98

  
82 99
    @Override
......
132 149
		}
133 150
		String titleCache = label != null ? label : (abbrev != null ? abbrev : description );
134 151
		String idInVocStr = idInVocabulary == null ? "NULL" : "'" + idInVocabulary + "'";
135
		String sqlInsertTerm = " INSERT INTO @@DefinedTermBase@@ (DTYPE, id, uuid, created, termtype, idInVocabulary, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)" +
136
				"VALUES ('" + dtype + "', " + id + ", '" + uuidTerm + "', '" + created + "', '" + termType.getKey() + "', " + idInVocStr +  ", " + protectedTitleCache + ", '" + titleCache + "', " + orderIndex + ", " + defaultColor + ", " + vocId + ")";
137

  
152
		String symbol = this.symbol == null ? "NULL" : "'" + this.symbol + "'";
153
		String sqlInsertTerm = " INSERT INTO @@DefinedTermBase@@ (DTYPE, id, uuid, created, termtype, idInVocabulary, symbol, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)" +
154
				"VALUES ('" + dtype + "', " + id + ", '" + uuidTerm + "', '" + created + "', '" + termType.getKey() + "', " + idInVocStr + ", " + symbol +  ", " + protectedTitleCache + ", '" + titleCache + "', " + orderIndex + ", " + defaultColor + ", " + vocId + ")";
155
		sqlInsertTerm = caseType.replaceTableNames(sqlInsertTerm);
138 156
		datasource.executeUpdate(caseType.replaceTableNames(sqlInsertTerm));
139 157

  
140 158
		updateFeatureTerms(termId, datasource, monitor, caseType);
141 159
		updateRelationshipTerms(termId, datasource, monitor, caseType);
142 160
		updateRanks(termId, datasource, monitor, caseType);
143 161

  
144
//
145
//		INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)
146
//		SELECT 'ReferenceSystem' ,  (@defTermId := max(id)+1)  as maxId , '1bb67042-2814-4b09-9e76-c8c1e68aa281', '2010-06-01 10:15:00', b'0', 'Google Earth', null, null, @refSysVocId
147
//		FROM DefinedTermBase ;
148
//
149

  
150 162
		//language id
151 163
		Integer langId = getLanguageId(uuidLanguage, datasource, monitor, caseType);
152 164
		if (langId == null){
......
189 201

  
190 202
			datasource.executeUpdate(caseType.replaceTableNames(sqlInsertReverseRepresentation));
191 203

  
192
			String sqlReverseInsertMN = "INSERT INTO @@RelationshipTermBase_inverseRepresentation@@ (DefinedTermBase_id, inverserepresentations_id) " +
204
			String sqlReverseInsertMN = "INSERT INTO @@TermBase_inverseRepresentation@@ (term_id, inverserepresentations_id) " +
193 205
					" VALUES ("+ termId +"," +reverseRepId+ " )";
194 206

  
195 207
			datasource.executeUpdate(caseType.replaceTableNames(sqlReverseInsertMN));
cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v47_50/ProParteSynonymUpdater.java
1
/**
2
* Copyright (C) 2009 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.v47_50;
10

  
11
import java.sql.ResultSet;
12
import java.sql.SQLException;
13
import java.util.List;
14
import java.util.UUID;
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
import eu.etaxonomy.cdm.database.update.SingleTermUpdater;
25
import eu.etaxonomy.cdm.model.common.Language;
26
import eu.etaxonomy.cdm.model.common.TermType;
27
import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
28

  
29

  
30
/**
31
 * Updates the pro parte and partial synonyms. Makes them real concept relationships
32
 * #7334
33
 *
34
 * @author a.mueller
35
 * @since 11.05.2018
36
 */
37
public class ProParteSynonymUpdater extends SchemaUpdaterStepBase {
38
    @SuppressWarnings("unused")
39
    private static final Logger logger = Logger.getLogger(ProParteSynonymUpdater.class);
40

  
41
	private static final String stepName = "Make pro parte synonyms concept relationships";
42

  
43

  
44
// **************************** STATIC METHODS ********************************/
45

  
46
	public static final ProParteSynonymUpdater NewInstance(List<ISchemaUpdaterStep> stepList){
47
		ProParteSynonymUpdater result = new ProParteSynonymUpdater();
48
		stepList.add(result);
49

  
50
		return result;
51
	}
52

  
53
	private ProParteSynonymUpdater() {
54
		super(stepName);
55
	}
56

  
57
    @Override
58
    public void invoke(ICdmDataSource datasource, IProgressMonitor monitor,
59
            CaseType caseType, SchemaUpdateResult result) throws SQLException {
60

  
61
        int proParteId = createProParteRelType(datasource, monitor, caseType, result);
62
        int partialId = createPartialRelType(datasource, monitor, caseType, result);
63

  
64
        try{
65
            invokeSingle(false, proParteId, datasource, monitor, caseType, result);
66
            invokeSingle(true, partialId, datasource, monitor, caseType, result);
67

  
68
        } catch (Exception e) {
69
            String message = e.getMessage();
70
            monitor.warning(message, e);
71
            result.addException(e, message, this, "invoke");
72
        }
73

  
74
	    return;
75
	}
76

  
77
    private int createProParteRelType(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
78
            SchemaUpdateResult result) throws SQLException {
79

  
80
        UUID uuidTerm = TaxonRelationshipType.uuidProParteSynonymFor;
81
        String symbol = "p.p.";
82
        String description = "Pro parte synonym for";
83
        UUID uuidAfterTerm = UUID.fromString("605b1d01-f2b1-4544-b2e0-6f08def3d6ed");
84
        String reverseDescription = "Has pro parte synonym";
85
        String reverseAbbrev = "⊃p.p.";
86

  
87
        return createRelType(datasource, monitor, caseType, result,
88
                uuidTerm, symbol, description, uuidAfterTerm,
89
                reverseDescription, reverseAbbrev);
90
    }
91

  
92
    private int createPartialRelType(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
93
            SchemaUpdateResult result) throws SQLException {
94

  
95
        UUID uuidTerm = TaxonRelationshipType.uuidPartialSynonymFor;
96
        String symbol = "partial";
97
        String description = "Partial synonym for";
98
        UUID uuidAfterTerm = UUID.fromString("8a896603-0fa3-44c6-9cd7-df2d8792e577");
99
        String reverseDescription = "Has partial synonym";
100
        String reverseAbbrev = "⊃partim";
101

  
102
        return createRelType(datasource, monitor, caseType, result,
103
                uuidTerm, symbol, description, uuidAfterTerm,
104
                reverseDescription, reverseAbbrev);
105

  
106
    }
107

  
108
    private int createRelType(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType,
109
            SchemaUpdateResult result, UUID uuidTerm, String symbol,
110
            String description, UUID uuidAfterTerm,
111
            String reverseDescription, String reverseAbbrev) throws SQLException {
112

  
113
        String inverseSymbol = "⊃" + symbol;
114
        String query = "SELECT count(*) n FROM @@DefinedTermBase@@ dtb WHERE dtb.uuid = '%s'";
115
        query = String.format(query, uuidTerm);
116
        query = caseType.replaceTableNames(query);
117
        Long n = (Long)datasource.getSingleValue(query);
118
        if (n == 0){
119

  
120
            //create type term
121
            String idInVocabulary = symbol;
122
            String label = description;
123
            String abbrev = idInVocabulary;
124
            String reverseLabel = reverseDescription;
125
            String dtype = "TaxonRelationshipType";
126
            UUID uuidVocabulary = UUID.fromString("15db0cf7-7afc-4a86-a7d4-221c73b0c9ac");
127
            UUID uuidLanguage = Language.uuidEnglish;
128
            boolean isOrdered = true;
129
            ISchemaUpdaterStep step = SingleTermUpdater.NewReverseInstance(stepName, TermType.TaxonRelationshipType,
130
                    uuidTerm, idInVocabulary, symbol,
131
                    description, label, abbrev, reverseDescription, reverseLabel, reverseAbbrev,
132
                    dtype, uuidVocabulary, uuidLanguage, isOrdered, uuidAfterTerm);
133
            step.invoke(datasource, monitor, caseType, result);
134

  
135
//            //reverse  representation
136
//            step = TermRepresentationAdder.NewReverseInstance(dtype, uuidTerm, reverseDescription, reverseLabel, reverseAbbrev, uuidLanguage);
137
//            step.invoke(datasource, monitor, caseType, result);
138
        }
139

  
140
        //update some fields
141
        query = " UPDATE @@DefinedTermBase@@ dtb " +
142
                " SET symbol = '%s', inverseSymbol = '%s', transitive = @FALSE@ " +
143
                " WHERE uuid = '%s'";
144
        query = String.format(query, symbol, inverseSymbol, uuidTerm.toString());
145
        query = doReplacements(query, caseType, datasource);
146
        datasource.executeUpdate(query);
147

  
148
        //select id
149
        query = "SELECT id n FROM @@DefinedTermBase@@ dtb WHERE dtb.uuid = '%s'";
150
        query = String.format(query, uuidTerm);
151
        query = doReplacements(query, caseType, datasource);
152
        Integer id = (Integer)datasource.getSingleValue(query);
153

  
154
        return id;
155
    }
156

  
157

  
158
    private void invokeSingle(boolean isPartial, int typeId, ICdmDataSource datasource, IProgressMonitor monitor,
159
            CaseType caseType, SchemaUpdateResult result) throws SQLException {
160

  
161
        //get maxId from taxonRelationship
162
        Integer maxId = 0;
163
        String query = "SELECT max(id) id FROM @@TaxonRelationship@@";
164
        query = doReplacements(query, caseType, datasource);
165
        ResultSet rs = datasource.executeQuery(query);
166
        if (rs.next()){
167
            maxId = rs.getInt("id");
168
        }
169

  
170
        //
171
        String attributeName = isPartial? "partial" : "proParte";
172
        query = " SELECT tb.id synId, titleCache " +
173
                " FROM @@TaxonBase@@ tb  " +
174
                " WHERE tb.%s = @TRUE@ ";
175
        query = String.format(query, attributeName);
176
        query = doReplacements(query, caseType, datasource);
177
        rs = datasource.executeQuery(query);
178
        while (rs.next()){
179
            maxId++;
180
            Integer synId = rs.getInt("synId");
181
            String insert = "INSERT INTO @@TaxonRelationship@@ (id, uuid, relatedFrom_id, relatedTo_id, "
182
                    +       " doubtful, citation_id, citationMicroReference, type_id, "
183
                    +       " created, updated, createdBy_id, updatedBy_id )"
184
                    + " SELECT %d, '%s', syn.id, acceptedTaxon_id,  "
185
                    +       " syn.doubtful, syn.sec_id secId, syn.secMicroReference, %d, "
186
                    +       " syn.created, syn.updated, syn.createdBy_id, syn.updatedBy_id "
187
                    + " FROM TaxonBase syn "
188
                    + " WHERE syn.id = %d ";
189
            insert = String.format(insert, maxId, UUID.randomUUID(), typeId, synId );
190
            insert = doReplacements(insert, caseType, datasource);
191
            datasource.executeUpdate(insert);
192

  
193
            String titleCache = rs.getString("titleCache");
194
            titleCache = normalizeTitleCache(titleCache);
195
            String update = "UPDATE @@TaxonBase@@ "
196
                    + " SET DTYPE = 'Taxon', sec_id = null, secMicroReference = null,"
197
                    + "     %s = null, titleCache = '%s', taxonStatusUnknown = @FALSE@,"
198
                    + "     doubtful = @FALSE@, acceptedTaxon_id = null, type_id = null "
199
                    + " WHERE id = %d ";
200
            update = String.format(update, attributeName, titleCache, synId);
201
            update = doReplacements(update, caseType, datasource);
202
            datasource.executeUpdate(update);
203
        }
204

  
205
    }
206

  
207

  
208
    /**
209
     * @param titleCache
210
     * @return
211
     */
212
    private String normalizeTitleCache(String titleCache) {
213
        if(titleCache == null){
214
            return "";
215
        }
216
        int index = titleCache.indexOf("syn. sec.");
217
        if (index < 0){
218
            return titleCache;
219
        }else{
220
            titleCache = titleCache.substring(0, index) + "sec. ???";
221
            return titleCache;
222
        }
223
    }
224

  
225
    private String doReplacements(String query, CaseType caseType, ICdmDataSource datasource) {
226
        query = caseType.replaceTableNames(query);
227
        query = query.replaceAll("@FALSE@", getBoolean(false, datasource));
228
        query = query.replaceAll("@TRUE@", getBoolean(true, datasource));
229
        return query;
230
    }
231

  
232
}
cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v47_50/SchemaUpdater_47_50.java
253 253
        step = MnTableCreator.NewMnInstance(stepName, firstTableName, null, secondTableName, null, attributeName, INCLUDE_AUDIT, isList, IS_1_TO_M);
254 254
        stepList.add(step);
255 255

  
256
        //#7334 Make pro parte/partial concept relationships
257
        step = ProParteSynonymUpdater.NewInstance(stepList);
258

  
256 259

  
257 260
//        //7276  Make User.emailAddress a unique field
258 261
//        //TODO H2 / PostGreSQL / SQL Server

Also available in: Unified diff

Add picture from clipboard (Maximum size: 40 MB)