3 * Copyright (C) 2009 EDIT
4 * European Distributed Institute of Taxonomy
5 * http://www.e-taxonomy.eu
7 * The contents of this file are subject to the Mozilla Public License Version 1.1
8 * See LICENSE.TXT at the top of this package for the full license terms.
10 package eu
.etaxonomy
.cdm
.database
.update
;
12 import java
.sql
.ResultSet
;
13 import java
.sql
.SQLException
;
14 import java
.util
.UUID
;
16 import org
.apache
.log4j
.Logger
;
18 import eu
.etaxonomy
.cdm
.common
.IProgressMonitor
;
19 import eu
.etaxonomy
.cdm
.database
.ICdmDataSource
;
20 import eu
.etaxonomy
.cdm
.model
.description
.Feature
;
27 public class SingleTermUpdater
extends SchemaUpdaterStepBase
{
28 @SuppressWarnings("unused")
29 private static final Logger logger
= Logger
.getLogger(SingleTermUpdater
.class);
31 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
){
32 return new SingleTermUpdater(stepName
, uuidTerm
, description
, label
, abbrev
, dtype
, uuidVocabulary
, uuidLanguage
, isOrdered
, uuidAfterTerm
);
35 private UUID uuidTerm
;
36 private String description
;
38 private String abbrev
;
40 private UUID uuidVocabulary
;
41 private boolean isOrdered
;
42 private UUID uuidAfterTerm
;
43 private UUID uuidLanguage
;
46 private SingleTermUpdater(String stepName
, UUID uuidTerm
, String description
, String label
, String abbrev
, String dtype
, UUID uuidVocabulary
, UUID uuidLanguage
, boolean isOrdered
, UUID uuidAfterTerm
) {
49 this.description
= description
;
52 this.isOrdered
= isOrdered
;
53 this.uuidTerm
= uuidTerm
;
54 this.uuidVocabulary
= uuidVocabulary
;
55 this.uuidAfterTerm
= uuidAfterTerm
;
56 this.uuidLanguage
= uuidLanguage
;
61 public Integer
invoke(ICdmDataSource datasource
, IProgressMonitor monitor
) throws SQLException
{
62 String sqlCheckTermExists
= " SELECT count(*) as n FROM DefinedTermBase WHERE uuid = '" + uuidTerm
+ "'";
63 Long n
= (Long
)datasource
.getSingleValue(sqlCheckTermExists
);
65 monitor
.warning("Term already exists: " + label
+ "(" + uuidTerm
+ ")");
71 String sqlVocId
= " SELECT id FROM TermVocabulary WHERE uuid = '" + uuidVocabulary
+ "'";
72 ResultSet rs
= datasource
.executeQuery(sqlVocId
);
74 vocId
= rs
.getInt("id");
76 String warning
= "Vocabulary ( "+ uuidVocabulary
+" ) for term does not exist!";
77 monitor
.warning(warning
);
82 String sqlMaxId
= " SELECT max(id)+1 as maxId FROM DefinedTermBase";
83 rs
= datasource
.executeQuery(sqlMaxId
);
85 termId
= rs
.getInt("maxId");
87 String warning
= "No defined terms do exist yet. Can't update terms!";
88 monitor
.warning(warning
);
92 String id
= Integer
.toString(termId
);
93 String created
= "2010-09-16 10:15:00";
94 String defaultColor
= "null";
95 String protectedTitleCache
= getBoolean(false, datasource
);
98 orderIndex
= getOrderIndex(datasource
, vocId
, monitor
);
102 String titleCache
= label
!= null ? label
: (abbrev
!= null ? abbrev
: description
);
103 String sqlInsertTerm
= " INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)" +
104 "VALUES ('" + dtype
+ "', " + id
+ ", '" + uuidTerm
+ "', '" + created
+ "', " + protectedTitleCache
+ ", '" + titleCache
+ "', " + orderIndex
+ ", " + defaultColor
+ ", " + vocId
+ ")";
105 datasource
.executeUpdate(sqlInsertTerm
);
107 updateFeatureTerms(termId
, datasource
, monitor
);
110 // INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)
111 // 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
112 // FROM DefinedTermBase ;
117 String sqlLangId
= " SELECT id FROM DefinedTermBase WHERE uuid = '" + uuidLanguage
+ "'";
118 rs
= datasource
.executeQuery(sqlLangId
);
120 langId
= rs
.getInt("id");
122 String warning
= "Term for default language (English) not does not exist!";
123 monitor
.warning(warning
);
129 sqlMaxId
= " SELECT max(id)+1 as maxId FROM Representation";
130 rs
= datasource
.executeQuery(sqlMaxId
);
132 repId
= rs
.getInt("maxId");
134 String warning
= "No representations do exist yet. Can't update terms!";
135 monitor
.warning(warning
);
139 UUID uuidRepresentation
= UUID
.randomUUID();
140 String sqlInsertRepresentation
= " INSERT INTO Representation (id, created, uuid, text, abbreviatedlabel, label, language_id) " +
141 "VALUES (" + repId
+ ", '" + created
+ "', '" + uuidRepresentation
+ "', '" + description
+ "', '" + label
+ "', '" + abbrev
+ "', " + langId
+ ")";
143 datasource
.executeUpdate(sqlInsertRepresentation
);
145 String sqlInsertMN
= "INSERT INTO DefinedTermBase_Representation (DefinedTermBase_id, representations_id) " +
146 " VALUES ("+ termId
+"," +repId
+ " )";
148 datasource
.executeUpdate(sqlInsertMN
);
155 private void updateFeatureTerms(Integer termId
, ICdmDataSource datasource
, IProgressMonitor monitor
) {
156 if (dtype
.equals(Feature
.class.getSimpleName())){
157 String sqlUpdate
= "UPDATE DefinedTermBase SET " +
158 " supportscategoricaldata = " + getBoolean(false, datasource
) + ", " +
159 " supportscommontaxonname = " + getBoolean(false, datasource
) + ", " +
160 " supportsdistribution = " + getBoolean(false, datasource
) + ", " +
161 " supportsindividualassociation = " + getBoolean(false, datasource
) + ", " +
162 " supportsquantitativedata = " + getBoolean(false, datasource
) + ", " +
163 " supportstaxoninteraction = " + getBoolean(false, datasource
) + ", " +
164 " supportstextdata = " + getBoolean(true, datasource
) + " " +
165 " WHERE id = " + termId
;
166 datasource
.executeUpdate(sqlUpdate
);
178 * @throws SQLException
180 private String
getOrderIndex(ICdmDataSource datasource
, int vocId
, IProgressMonitor monitor
) throws SQLException
{
182 Integer intOrderIndex
= null;
183 String sqlOrderIndex
= " SELECT orderindex FROM DefinedTermBase WHERE uuid = '"+uuidAfterTerm
+"' AND vocabulary_id = "+vocId
+"";
184 rs
= datasource
.executeQuery(sqlOrderIndex
);
186 intOrderIndex
= rs
.getInt("orderindex") + 1;
188 String sqlUpdateLowerTerms
= "UPDATE DefinedTermBase SET orderindex = orderindex + 1 WHERE vocabulary_id = " + vocId
+ " AND orderindex >= " + intOrderIndex
;
189 datasource
.executeUpdate(sqlUpdateLowerTerms
);
191 String warning
= "The previous term has not been found in vocabulary. Put term to the end";
192 monitor
.warning(warning
);
195 if (intOrderIndex
== null){
196 String sqlMaxOrderIndex
= " SELECT max(orderindex) FROM DefinedTermBase WHERE vocabulary_id = " + vocId
+ "";
197 intOrderIndex
= (Integer
)datasource
.getSingleValue(sqlMaxOrderIndex
);
198 if (intOrderIndex
!= null){
201 String warning
= "No term was found in vocabulary or vocabulary does not exist. Use order index '0'.";
202 monitor
.warning(warning
);
207 return intOrderIndex
.toString();
208 // -- absence term max orderindex
209 // SELECT (@maxAbsenceOrderIndex := max(orderindex)) AS b FROM DefinedTermBase WHERE DTYPE = 'AbsenceTerm';
211 // -- native reported in error
212 // SELECT (@presenceOrderIndex := orderindex) AS a FROM DefinedTermBase WHERE uuid = '4ba212ef-041e-418d-9d43-2ebb191b61d8';
213 // UPDATE DefinedTermBase SET uuid = '61cee840-801e-41d8-bead-015ad866c2f1', DTYPE = 'AbsenceTerm', vocabulary_id = 18, orderindex = @maxAbsenceOrderIndex + 1 WHERE uuid = '4ba212ef-041e-418d-9d43-2ebb191b61d8';
214 // UPDATE DefinedTermBase SET orderindex = orderindex -1 WHERE DTYPE = 'PresenceTerm' AND orderindex > @presenceOrderIndex ;