Project

General

Profile

Revision acc45005

IDacc45005d1f84381588de5f907dd47cf3ae5c52f
Parent 69584a18
Child 62e9b5fa

Added by Andreas Müller about 1 year ago

ref #7446 deduplicate common name language references in E+M

View differences:

app-import/src/main/java/eu/etaxonomy/cdm/app/berlinModelImport/EuroMedActivator.java
77 77
	private static final Logger logger = Logger.getLogger(EuroMedActivator.class);
78 78

  
79 79
	//database validation status (create, update, validate ...)
80
	static DbSchemaValidation hbm2dll = DbSchemaValidation.CREATE;
80
	static DbSchemaValidation hbm2dll = DbSchemaValidation.VALIDATE;
81 81
//    static final Source berlinModelSource = BerlinModelSources.euroMed_Pub2();
82 82
	static final Source berlinModelSource = BerlinModelSources.euroMed_BGBM42();
83 83
//	static final Source berlinModelSource = BerlinModelSources.euroMed_PESI3();
84 84
//
85
  static final ICdmDataSource cdmDestination = CdmDestinations.localH2();
86
//	static final ICdmDataSource cdmDestination = CdmDestinations.cdm_test_euroMed();
85
//  static final ICdmDataSource cdmDestination = CdmDestinations.localH2();
86
	static final ICdmDataSource cdmDestination = CdmDestinations.cdm_test_euroMed();
87 87

  
88 88
//	static final ICdmDataSource cdmDestination = CdmDestinations.cdm_local_euromed();
89 89

  
90 90

  
91 91
    //check - import
92
    static final CHECK check = CHECK.CHECK_ONLY;
92
    static final CHECK check = CHECK.IMPORT_WITHOUT_CHECK;
93 93

  
94 94
	static final boolean includePesiExport = false;
95 95

  
......
143 143
	static String occurrenceSourceFilter = " occurrenceFk IN ( SELECT occurrenceId FROM v_cdm_exp_occurrenceAll )";
144 144
	static String commonNameFilter = " commonNameId IN ( SELECT commonNameId FROM v_cdm_exp_commonNamesAll )";
145 145
	static String webMarkerFilter = " TableNameFk <> 500 OR ( RIdentifierFk IN (SELECT RIdentifier FROM v_cdm_exp_taxaAll)) ";
146
	static String authorTeamFilter = /* null;  //*/ " authorTeamId IN (SELECT authorTeamId FROM v_cdm_exp_authorTeamsAll) ";
147
	static String authorFilter = /* null;  //*/ " authorId IN (SELECT authorId FROM v_cdm_exp_authorsAll) ";
146
	static String authorTeamFilter = null;  //*/ " authorTeamId IN (SELECT authorTeamId FROM v_cdm_exp_authorTeamsAll) ";
147
	static String authorFilter =  null;  //*/ " authorId IN (SELECT authorId FROM v_cdm_exp_authorsAll) ";
148 148

  
149 149

  
150 150

  
......
166 166

  
167 167
	//taxa
168 168
	static final boolean doTaxa = true;
169
	static final boolean doFacts = true;
169
	static final boolean doFacts = false;
170 170
	static final boolean doCommonNames = false;
171
	static final boolean doOccurences = true;
172
	static final boolean doRelTaxa = true;
171
	static final boolean doOccurences = false;
172
	static final boolean doRelTaxa = false;
173 173
	static final boolean doRunTransmissionEngine = (hbm2dll == DbSchemaValidation.VALIDATE);
174 174

  
175 175
	//etc.
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelCommonNamesImport.java
355 355

  
356 356
				}
357 357

  
358

  
359 358
				//reference extensions
360 359
				if (reference != null){
361
					if (StringUtils.isNotBlank(refLanguage)){
360
					if (isNotBlank(refLanguage) && !reference.hasExtension(REFERENCE_LANGUAGE_STRING_UUID, refLanguage)){
362 361
						ExtensionType refLanguageExtensionType = getExtensionType( state, REFERENCE_LANGUAGE_STRING_UUID, "reference language","The language of the reference","ref. lang.");
363 362
						Extension.NewInstance(reference, refLanguage, refLanguageExtensionType);
364 363
					}
365 364

  
366
					if (StringUtils.isNotBlank(refLanguageIso639_2)){
365
					if (isNotBlank(refLanguageIso639_2) && !reference.hasExtension(REFERENCE_LANGUAGE_ISO639_2_UUID, refLanguage)){
367 366
						ExtensionType refLanguageIsoExtensionType = getExtensionType( state, REFERENCE_LANGUAGE_ISO639_2_UUID, "reference language iso 639-2","The iso 639-2 code of the references language","ref. lang. 639-2");
368 367
						Extension.NewInstance(reference, refLanguageIso639_2, refLanguageIsoExtensionType);
369 368
					}
......
419 418

  
420 419
	}
421 420

  
422
	/**
421
    /**
423 422
	 * @param iso6392Map
424 423
	 * @param iso639_2
425 424
	 * @param languageString
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelImportConfigurator.java
135 135
				, BerlinModelNameFactsImport.class
136 136
				, BerlinModelTypesImport.class
137 137
				, BerlinModelTaxonImport.class
138
				, BerlinModelCommonNamesImport.class
139 138
				, BerlinModelFactsImport.class
139
				, BerlinModelCommonNamesImport.class
140 140
				, BerlinModelOccurrenceImport.class
141 141
				, BerlinModelOccurrenceSourceImport.class
142 142
				, BerlinModelWebMarkerCategoryImport.class
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/validation/BerlinModelCommonNamesImportValidator.java
1 1
/**
2 2
* Copyright (C) 2007 EDIT
3
* European Distributed Institute of Taxonomy 
3
* European Distributed Institute of Taxonomy
4 4
* http://www.e-taxonomy.eu
5
* 
5
*
6 6
* The contents of this file are subject to the Mozilla Public License Version 1.1
7 7
* See LICENSE.TXT at the top of this package for the full license terms.
8 8
*/
......
30 30
	@Override
31 31
	public boolean validate(BerlinModelImportState state) {
32 32
		boolean result = true;
33
		result &= checkUnreferredNameUsedInSource(state.getConfig());
33
		result &= checkNameUsedInSourceMinusOne(state.getConfig());
34
        result &= checkUnreferredNameUsedInSource(state.getConfig());
34 35
		result &= checkUnreferredLanguageRefFk(state.getConfig());
36

  
35 37
		result &= checkTaxonIsAccepted(state.getConfig());
36
		
38

  
37 39
		return result;
38 40
	}
39
	
41

  
42
    private boolean checkNameUsedInSourceMinusOne(BerlinModelImportConfigurator config){
43
        try {
44
            boolean result = true;
45
            Source source = config.getSource();
46
            String strQuery = "SELECT Count(*) as n " +
47
                    " FROM emCommonName " +
48
                    " WHERE (emCommonName.NameInSourceFk = - 1) ";
49

  
50
            if (StringUtils.isNotBlank(config.getCommonNameFilter())){
51
                strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ;
52
            }
53

  
54
            ResultSet rs = source.getResultSet(strQuery);
55
            rs.next();
56
            int count = rs.getInt("n");
57
            if (count > 0){
58
                System.out.println("========================================================");
59
                System.out.println("There are " + count + " common names that have a name usedInSourceFk = -1.");
60
                System.out.println("========================================================");
61
            }
62
            String sql =
63
                " SELECT DISTINCT emCommonName.CommonNameId, emCommonName.NameInSourceFk, emCommonName.CommonName, PTaxon.PTNameFk, PTaxon.PTRefFk," +
64
                    " Name.FullNameCache, PTaxon.RIdentifier " +
65
                " FROM emCommonName INNER JOIN " +
66
                    " PTaxon ON emCommonName.PTNameFk = PTaxon.PTNameFk AND emCommonName.PTRefFk = PTaxon.PTRefFk INNER JOIN " +
67
                    " Name ON PTaxon.PTNameFk = Name.NameId " +
68
                " WHERE (emCommonName.NameInSourceFk = - 1) ";
69
            if (StringUtils.isNotBlank(config.getCommonNameFilter())){
70
                sql += String.format(" AND (%s) ", config.getCommonNameFilter()) ;
71
            }
72

  
73
            rs = source.getResultSet(sql);
74
            int i = 0;
75
            while (rs.next()){
76
                i++;
77
                int commonNameId = rs.getInt("CommonNameId");
78
                String fullNameCache = rs.getString("FullNameCache");
79
                String commonName = rs.getString("CommonName");
80
                int rIdentifier = rs.getInt("RIdentifier");
81
                int nameFk = rs.getInt("PTNameFk");
82
                int refFk = rs.getInt("PTRefFk");
83
                int nameInSourceFk = rs.getInt("NameInSourceFk");
84

  
85
                System.out.println("CommonName: " + commonName + "\n  CommonNameId: " + commonNameId + "\n Taxon Name:" + fullNameCache + "\n  TaxonNameFk: " + nameFk +
86
                        "\n  TaxonRefFk: " + refFk + "\n  TaxonId" + rIdentifier + "\n NameInSourceFk: " + nameInSourceFk + "\n");
87
            }
88
            if (i > 0){
89
                System.out.println(" ");
90
            }
91

  
92

  
93
            return result;
94
        } catch (SQLException e) {
95
            e.printStackTrace();
96
            return false;
97
        }
98

  
99
    }
100

  
40 101
	private boolean checkUnreferredNameUsedInSource(BerlinModelImportConfigurator config){
41 102
		try {
42 103
			boolean result = true;
43 104
			Source source = config.getSource();
44 105
			String strQuery = "SELECT Count(*) as n " +
45 106
					" FROM emCommonName " +
46
					" WHERE (emCommonName.NameInSourceFk NOT IN " + 
47
							"(SELECT NameId FROM Name AS Name_1)) AND " + 
107
					" WHERE (emCommonName.NameInSourceFk NOT IN " +
108
							"(SELECT NameId FROM Name AS Name_1)) AND " +
48 109
						"(emCommonName.NameInSourceFk <> - 1) ";
49
			
110

  
50 111
			if (StringUtils.isNotBlank(config.getCommonNameFilter())){
51
				strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ; 
112
				strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ;
52 113
			}
53
			
114

  
54 115
			ResultSet rs = source.getResultSet(strQuery);
55 116
			rs.next();
56 117
			int count = rs.getInt("n");
57 118
			if (count > 0){
58 119
				System.out.println("========================================================");
59 120
				System.out.println("There are " + count + " common names that have a name used in source which can not be found in the database.");
60
				
121

  
61 122
				System.out.println("========================================================");
62 123
			}
63
			String sql = 
64
				" SELECT DISTINCT emCommonName.CommonNameId, emCommonName.NameInSourceFk, emCommonName.CommonName, PTaxon.PTNameFk, PTaxon.PTRefFk," + 
124
			String sql =
125
				" SELECT DISTINCT emCommonName.CommonNameId, emCommonName.NameInSourceFk, emCommonName.CommonName, PTaxon.PTNameFk, PTaxon.PTRefFk," +
65 126
					" Name.FullNameCache, PTaxon.RIdentifier " +
66 127
				" FROM emCommonName INNER JOIN " +
67 128
					" PTaxon ON emCommonName.PTNameFk = PTaxon.PTNameFk AND emCommonName.PTRefFk = PTaxon.PTRefFk INNER JOIN " +
68 129
					" Name ON PTaxon.PTNameFk = Name.NameId " +
69
				" WHERE (emCommonName.NameInSourceFk NOT IN " + 
70
						"(SELECT NameId FROM Name AS Name_1)) AND " + 
130
				" WHERE (emCommonName.NameInSourceFk NOT IN " +
131
						"(SELECT NameId FROM Name AS Name_1)) AND " +
71 132
					"(emCommonName.NameInSourceFk <> - 1)";
72
			
133

  
73 134
			rs = source.getResultSet(sql);
74 135
			int i = 0;
75 136
			while (rs.next()){
......
81 142
				int nameFk = rs.getInt("PTNameFk");
82 143
				int refFk = rs.getInt("PTRefFk");
83 144
				int nameInSourceFk = rs.getInt("NameInSourceFk");
84
				
85
				System.out.println("CommonName: " + commonName + "\n  CommonNameId: " + commonNameId + "\n Taxon Name:" + fullNameCache + "\n  TaxonNameFk: " + nameFk + 
145

  
146
				System.out.println("CommonName: " + commonName + "\n  CommonNameId: " + commonNameId + "\n Taxon Name:" + fullNameCache + "\n  TaxonNameFk: " + nameFk +
86 147
						"\n  TaxonRefFk: " + refFk + "\n  TaxonId" + rIdentifier + "\n NameInSourceFk: " + nameInSourceFk + "\n");
87 148
			}
88 149
			if (i > 0){
89 150
				System.out.println(" ");
90 151
			}
91
			
92
			
152

  
153

  
93 154
			return result;
94 155
		} catch (SQLException e) {
95 156
			e.printStackTrace();
......
97 158
		}
98 159

  
99 160
	}
100
	
161

  
101 162
	private boolean checkUnreferredLanguageRefFk(BerlinModelImportConfigurator config){
102 163
		try {
103 164
			boolean result = true;
104 165
			Source source = config.getSource();
105 166
			String strQueryArticlesWithoutJournal = "SELECT Count(*) as n " +
106 167
					" FROM emCommonName " +
107
					" WHERE (emCommonName.LanguageRefFk NOT IN " + 
108
							"(SELECT ReferenceId FROM emLanguageReference)) AND " + 
168
					" WHERE (emCommonName.LanguageRefFk NOT IN " +
169
							"(SELECT ReferenceId FROM emLanguageReference)) AND " +
109 170
						"(emCommonName.LanguageRefFk is NOT NULL)";
110 171
			ResultSet rs = source.getResultSet(strQueryArticlesWithoutJournal);
111 172
			rs.next();
......
119 180
			if (count > 0){
120 181
				System.out.println(" ");
121 182
			}
122
			
123
			
183

  
184

  
124 185
			return result;
125 186
		} catch (SQLException e) {
126 187
			e.printStackTrace();
......
128 189
		}
129 190

  
130 191
	}
131
	
192

  
132 193
	private static boolean checkTaxonIsAccepted(BerlinModelImportConfigurator config){
133 194
		try {
134 195
			boolean result = true;
135 196
			Source source = config.getSource();
136
			String strQuery = "SELECT cn.CommonNameId, cn.CommonName, pt.StatusFk, n.FullNameCache, s.Status, pt.PTRefFk, r.RefCache " + 
197
			String strQuery = "SELECT cn.CommonNameId, cn.CommonName, pt.StatusFk, n.FullNameCache, s.Status, pt.PTRefFk, r.RefCache " +
137 198
						" FROM emCommonName cn " +
138
							" INNER JOIN PTaxon pt ON cn.PTNameFk = pt.PTNameFk AND cn.PTRefFk = pt.PTRefFk " + 
199
							" INNER JOIN PTaxon pt ON cn.PTNameFk = pt.PTNameFk AND cn.PTRefFk = pt.PTRefFk " +
139 200
			                " INNER JOIN Name n ON pt.PTNameFk = n.NameId " +
140 201
			                " INNER JOIN Status s ON pt.StatusFk = s.StatusId " +
141
			                " LEFT OUTER JOIN Reference r ON pt.PTRefFk = r.RefId " + 
202
			                " LEFT OUTER JOIN Reference r ON pt.PTRefFk = r.RefId " +
142 203
						" WHERE (pt.StatusFk NOT IN ( 1, 5))  ";
143 204

  
144 205
			if (StringUtils.isNotBlank(config.getOccurrenceFilter())){
145
				strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ; 
206
				strQuery += String.format(" AND (%s) ", config.getCommonNameFilter()) ;
146 207
			}
147 208

  
148
			
209

  
149 210
			ResultSet resulSet = source.getResultSet(strQuery);
150 211
			boolean firstRow = true;
151 212
			while (resulSet.next()){
......
160 221
				String fullNameCache = resulSet.getString("FullNameCache");
161 222
				String ptRefFk = resulSet.getString("PTRefFk");
162 223
				String ptRef = resulSet.getString("RefCache");
163
				
164
				System.out.println("CommonNameId: " + commonNameId + "\n CommonName: " + commonName + 
165
						"\n  Status: " + status + 
224

  
225
				System.out.println("CommonNameId: " + commonNameId + "\n CommonName: " + commonName +
226
						"\n  Status: " + status +
166 227
						"\n  FullNameCache: " + fullNameCache +  "\n  ptRefFk: " + ptRefFk +
167 228
						"\n  sec: " + ptRef );
168
				
229

  
169 230
				result = firstRow = false;
170 231
			}
171
			
232

  
172 233
			return result;
173 234
		} catch (SQLException e) {
174 235
			e.printStackTrace();
175 236
			return false;
176 237
		}
177 238
	}
178
	
239

  
179 240

  
180 241
}
app-import/src/main/java/eu/etaxonomy/cdm/io/redlist/gefaesspflanzen/RedListGefaesspflanzenImportClassification.java
188 188
                if(family.getTaxonNodes().isEmpty()){
189 189
                    gesamtListeClassification.addChildTaxon(family, null, null);
190 190
                    //do not add empty families to checklist classification
191
                    if(!getClassificationService().listChildNodesOfTaxon(family.getUuid(), RedListUtil.checkListClassificationUuid, null, null, null).isEmpty()){
191
                    if(!getClassificationService().listChildNodesOfTaxon(family.getUuid(), RedListUtil.checkListClassificationUuid,
192
                            true, null, null, null).isEmpty()){
192 193
                        checklistClassification.addChildTaxon(family, null, null);
193 194
                    }
194 195
                }

Also available in: Unified diff

Add picture from clipboard (Maximum size: 40 MB)