1 |
d68b0de2
|
Andreas Müller
|
BEGIN
|
2 |
|
|
|
3 |
|
|
DECLARE @n INT
|
4 |
|
|
DECLARE @n_cdm INT
|
5 |
|
|
DECLARE @n_bm INT
|
6 |
|
|
DECLARE @str_n NVARCHAR (7)
|
7 |
|
|
DECLARE @str_n_cdm NVARCHAR (7)
|
8 |
|
|
DECLARE @str_n_bm NVARCHAR (7)
|
9 |
|
|
|
10 |
|
|
|
11 |
|
|
------------------------------------------- Taxon -------------------------------
|
12 |
|
|
|
13 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON
|
14 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON
|
15 |
|
|
SET @n = @n_bm - @n_cdm
|
16 |
|
|
|
17 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
18 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
19 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
20 |
|
|
|
21 |
|
|
IF @n = 0 BEGIN
|
22 |
|
|
PRINT ('Both databases have the same number of taxa = ' + @str_n_bm)
|
23 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
24 |
|
|
SELECT @n = COUNT(*)
|
25 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
26 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
27 |
|
|
ON (bm_t.IdInSource = cdm_t.IdInSource OR bm_t.IdInSource IS NULL AND cdm_t.IdInSource IS NULL)
|
28 |
|
|
AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
29 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
30 |
|
|
IF @n = @n_bm BEGIN
|
31 |
|
|
PRINT ('To each taxon from one database there is an identical taxon in the other one (IdInSource, GUID)')
|
32 |
|
|
END ELSE PRINT ('WARNING: Only ' + @str_n + ' taxa are identical (IdInSource, GUID) in both databases')
|
33 |
|
|
|
34 |
|
|
/*
|
35 |
|
|
SELECT cdm_t.Fullname, cdm_t.IdInSource, cdm_t.GUID, 'in CDM_EM2PESI but not in EM2PESI'
|
36 |
|
|
FROM [CDM_EM2PESI].[DBO].TAXON cdm_t
|
37 |
|
|
WHERE NOT EXISTS
|
38 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON WHERE (IdInSource = cdm_t.IdInSource OR IdInSource IS NULL AND cdm_t.IdInSource IS NULL)
|
39 |
|
|
AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
|
40 |
|
|
ORDER BY cdm_t.IdInSource
|
41 |
|
|
|
42 |
|
|
SELECT bm_t.Fullname, bm_t.IdInSource, bm_t.GUID, 'in EM2PESI but not in CDM_EM2PESI'
|
43 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t
|
44 |
|
|
WHERE NOT EXISTS
|
45 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON WHERE (IdInSource = bm_t.IdInSource OR IdInSource IS NULL AND bm_t.IdInSource IS NULL)
|
46 |
|
|
AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
|
47 |
|
|
ORDER BY bm_t.IdInSource
|
48 |
|
|
|
49 |
|
|
*/
|
50 |
|
|
|
51 |
|
|
-- Source
|
52 |
|
|
/* Does not make much sense since the source for taxa is almost always the database itself which must be created as source
|
53 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE SourceFk IS NOT NULL
|
54 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE SourceFk IS NOT NULL
|
55 |
|
|
SET @n = @n_bm - @n_cdm
|
56 |
|
|
|
57 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
58 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
59 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
60 |
|
|
|
61 |
|
|
IF @n = 0 BEGIN
|
62 |
|
|
PRINT ('Both databases have the same number of taxa which have a source = ' + @str_n_bm)
|
63 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a source, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
64 |
|
|
|
65 |
|
|
SELECT @n = COUNT(*) -- in both databases sources exist but are different
|
66 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
67 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
68 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
69 |
|
|
[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId INNER JOIN
|
70 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
|
71 |
|
|
WHERE bm_s.RefIdInSource <> cdm_s.RefIdInSource OR ISNULL(bm_s.OriginalDB, '') <> ISNULL(cdm_s.OriginalDB, '')
|
72 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
73 |
|
|
IF @n = 0 BEGIN
|
74 |
|
|
PRINT ('All identical taxa that have sources have the same source')
|
75 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different sources')
|
76 |
|
|
/-*
|
77 |
|
|
SELECT cdm_t.Fullname, cdm_s.RefIdInSource as Source_CDM_EM2PESI, bm_s.RefIdInSource as Source_EM2PESI
|
78 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
79 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
80 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
81 |
|
|
[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId INNER JOIN
|
82 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
|
83 |
|
|
WHERE bm_s.RefIdInSource <> cdm_s.RefIdInSource OR ISNULL(bm_s.OriginalDB, '') <> ISNULL(cdm_s.OriginalDB, '')
|
84 |
|
|
*-/
|
85 |
|
|
SELECT @n_bm = COUNT(*) -- taxa with source only in EM2PESI
|
86 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
87 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
88 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
89 |
|
|
[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId
|
90 |
|
|
WHERE NOT EXISTS
|
91 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON INNER JOIN
|
92 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_s ON SourceFk = SourceId
|
93 |
|
|
WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
|
94 |
|
|
AND IdInSource = bm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
|
95 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
96 |
|
|
IF @n_bm > 0 PRINT ('WARNING: ' + @str_n_bm + ' taxa have sources in EM2PESI but not in CDM_EM2PESI')
|
97 |
|
|
/-*
|
98 |
|
|
SELECT cdm_t.Fullname, bm_s.Fullname as source_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
|
99 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
100 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
101 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
102 |
|
|
[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId
|
103 |
|
|
WHERE NOT EXISTS
|
104 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON INNER JOIN
|
105 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_s ON SourceFk = SourceId
|
106 |
|
|
WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
|
107 |
|
|
AND IdInSource = bm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
|
108 |
|
|
*-/
|
109 |
|
|
SELECT @n_cdm = COUNT(*) -- taxa with source only in CDM_EM2PESI
|
110 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
111 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
112 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
113 |
|
|
[CDM_EM2PESI].[DBO].SOURCE cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
|
114 |
|
|
WHERE NOT EXISTS
|
115 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON INNER JOIN
|
116 |
|
|
[EM2PESI].[DBO].TAXON bm_s ON SourceFk = SourceId
|
117 |
|
|
WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
|
118 |
|
|
AND IdInSource = cdm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
|
119 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
120 |
|
|
IF @n_cdm > 0 PRINT ('WARNING: ' + @str_n_cdm + ' taxa have sources in CDM_EM2PESI but not in EM2PESI')
|
121 |
|
|
/-*
|
122 |
|
|
SELECT cdm_t.Fullname, cdm_s.Fullname as source_CDM_EM2PESI, 'in CDM_EM2PESI but not in EM2PESI'
|
123 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
124 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
125 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
126 |
|
|
[CDM_EM2PESI].[DBO].SOURCE cdm_s ON bm_t.SourceFk = cdm_s.SourceId
|
127 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON INNER JOIN
|
128 |
|
|
[EM2PESI].[DBO].TAXON bm_s ON SourceFk = SourceId
|
129 |
|
|
WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
|
130 |
|
|
AND IdInSource = cdm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
|
131 |
|
|
*-/
|
132 |
|
|
IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
|
133 |
|
|
PRINT ('All identical taxa have the same source')
|
134 |
|
|
END
|
135 |
|
|
|
136 |
|
|
*/
|
137 |
|
|
|
138 |
|
|
-- Authors
|
139 |
|
|
SELECT @n = COUNT(*)
|
140 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
141 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
142 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
143 |
|
|
WHERE ISNULL(bm_t.AuthorString, '') <> ISNULL(cdm_t.AuthorString, '')
|
144 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
145 |
|
|
IF @n = 0 BEGIN
|
146 |
|
|
PRINT ('All identical taxa have the same authors')
|
147 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same authors')
|
148 |
|
|
|
149 |
|
|
/*
|
150 |
|
|
SELECT cdm_t.TaxonId as ID_CDM, bm_t.TaxonId as ID_SQL, cdm_t.FullName as FullName_CDM, bm_t.FullName as FullName_SQL,
|
151 |
|
|
cdm_t.AuthorString as AuthorString_CDM, bm_t.AuthorString as AuthorString_SQL
|
152 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
153 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
154 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
155 |
|
|
WHERE ISNULL(bm_t.AuthorString, '') <> ISNULL(cdm_t.AuthorString, '')
|
156 |
|
|
*/
|
157 |
|
|
|
158 |
|
|
-- Epithets
|
159 |
|
|
SELECT @n = COUNT(*)
|
160 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
161 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
162 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
163 |
|
|
WHERE ISNULL(bm_t.GenusOrUninomial, '') <> ISNULL(cdm_t.GenusOrUninomial, '') OR
|
164 |
|
|
ISNULL(bm_t.InfraGenericEpithet, '') <> ISNULL(cdm_t.InfraGenericEpithet, '') OR
|
165 |
|
|
ISNULL(bm_t.SpecificEpithet, '') <> ISNULL(cdm_t.SpecificEpithet, '') OR
|
166 |
|
|
ISNULL(bm_t.InfraSpecificEpithet, '') <> ISNULL(cdm_t.InfraSpecificEpithet, '')
|
167 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
168 |
|
|
IF @n = 0 BEGIN
|
169 |
|
|
PRINT ('All identical taxa have the same epithets')
|
170 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same epithets')
|
171 |
|
|
|
172 |
|
|
/*
|
173 |
|
|
SELECT cdm_t.GenusOrUninomial as Genus_CDM, bm_t.GenusOrUninomial as Genus_SQL,
|
174 |
|
|
cdm_t.InfraGenericEpithet as Infragenus_CDM, bm_t.InfraGenericEpithet as Infragenus_SQL,
|
175 |
|
|
cdm_t.SpecificEpithet as SpecificEpithet_CDM, bm_t.SpecificEpithet as SpecificEpithet_SQL,
|
176 |
|
|
cdm_t.InfraSpecificEpithet as Infraspec_CDM, bm_t.InfraSpecificEpithet as Infraspec_SQL
|
177 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
178 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
179 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
180 |
|
|
WHERE ISNULL(bm_t.GenusOrUninomial, '') <> ISNULL(cdm_t.GenusOrUninomial, '') OR
|
181 |
|
|
ISNULL(bm_t.InfraGenericEpithet, '') <> ISNULL(cdm_t.InfraGenericEpithet, '') OR
|
182 |
|
|
ISNULL(bm_t.SpecificEpithet, '') <> ISNULL(cdm_t.SpecificEpithet, '') OR
|
183 |
|
|
ISNULL(bm_t.InfraSpecificEpithet, '') <> ISNULL(cdm_t.InfraSpecificEpithet, '')
|
184 |
|
|
*/
|
185 |
|
|
|
186 |
|
|
-- Names
|
187 |
|
|
SELECT @n = COUNT(*)
|
188 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
189 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
190 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
191 |
|
|
WHERE ISNULL(bm_t.WebSearchName, '') <> ISNULL(cdm_t.WebSearchName, '') OR
|
192 |
|
|
ISNULL(bm_t.WebShowName, '') <> ISNULL(cdm_t.WebShowName, '') OR
|
193 |
|
|
ISNULL(bm_t.DisplayName, '') <> ISNULL(cdm_t.DisplayName, '')
|
194 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
195 |
|
|
IF @n = 0 BEGIN
|
196 |
|
|
PRINT ('All identical taxa have the same scientific name')
|
197 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same scientific name')
|
198 |
|
|
/*
|
199 |
|
|
SELECT cdm_t.WebSearchName as WebSearchName_CDM_EM2PESI, bm_t.WebSearchName as WebSearchName_EM2PESI,
|
200 |
|
|
cdm_t.WebShowName as WebShowName_CDM_EM2PESI, bm_t.WebShowName as WebShowName_EM2PESI,
|
201 |
|
|
cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
|
202 |
|
|
cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
|
203 |
|
|
cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
|
204 |
|
|
cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
|
205 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
206 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
207 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
208 |
|
|
WHERE ISNULL(bm_t.WebSearchName, '') <> ISNULL(cdm_t.WebSearchName, '') OR
|
209 |
|
|
ISNULL(bm_t.WebShowName, '') <> ISNULL(cdm_t.WebShowName, '') OR
|
210 |
|
|
ISNULL(bm_t.DisplayName, '') <> ISNULL(cdm_t.DisplayName, '')
|
211 |
|
|
*/
|
212 |
|
|
|
213 |
|
|
-- Full Name
|
214 |
|
|
SELECT @n = COUNT(*)
|
215 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
216 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
217 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
218 |
|
|
WHERE ISNULL(bm_t.FullName, '') <> ISNULL(cdm_t.FullName, '')
|
219 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
220 |
|
|
IF @n = 0 BEGIN
|
221 |
|
|
PRINT ('All identical taxa have the same full name')
|
222 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same full name')
|
223 |
|
|
|
224 |
|
|
/*
|
225 |
|
|
SELECT cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
|
226 |
|
|
cdm_t.WebSearchName as WebSearchName_CDM_EM2PESI, bm_t.WebSearchName as WebSearchName_EM2PESI,
|
227 |
|
|
cdm_t.WebShowName as WebShowName_CDM_EM2PESI, bm_t.WebShowName as WebShowName_EM2PESI,
|
228 |
|
|
cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
|
229 |
|
|
cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
|
230 |
|
|
cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
|
231 |
|
|
cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
|
232 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
233 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
234 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
235 |
|
|
WHERE ISNULL(bm_t.FullName, '') <> ISNULL(cdm_t.FullName, '')
|
236 |
|
|
ORDER BY cdm_t.FullName
|
237 |
|
|
*/
|
238 |
|
|
|
239 |
|
|
-- Nom Ref
|
240 |
|
|
|
241 |
|
|
SELECT @n = COUNT(*)
|
242 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
243 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
244 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
245 |
|
|
WHERE ISNULL(bm_t.NomRefString, '') <> ISNULL(cdm_t.NomRefString, '')
|
246 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
247 |
|
|
IF @n = 0 BEGIN
|
248 |
|
|
PRINT ('All identical taxa have the same nomenclatural reference')
|
249 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same nomenclatural reference')
|
250 |
|
|
/*
|
251 |
|
|
SELECT cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
|
252 |
|
|
cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
|
253 |
|
|
cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
|
254 |
|
|
cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
|
255 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
256 |
|
|
CDM_EM2PESI.[DBO].TAXON cdm_t
|
257 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
258 |
|
|
WHERE ISNULL(bm_t.NomRefString, '') <> ISNULL(cdm_t.NomRefString, '')
|
259 |
|
|
ORDER BY cdm_t.NomRefString
|
260 |
|
|
*/
|
261 |
|
|
|
262 |
|
|
-- Ranks
|
263 |
|
|
SELECT @n = COUNT(*)
|
264 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
265 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
266 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
267 |
|
|
WHERE ISNULL(bm_t.KingdomFk, 0) <> ISNULL(cdm_t.KingdomFk, 0) OR
|
268 |
|
|
ISNULL(bm_t.RankFk, 0) <> ISNULL(cdm_t.RankFk, 0) OR
|
269 |
|
|
ISNULL(bm_t.RankCache, '') <> ISNULL(cdm_t.RankCache, '')
|
270 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
271 |
|
|
IF @n = 0 BEGIN
|
272 |
|
|
PRINT ('All identical taxa have the same rank (KingdomFk, RankFk, RankCache)')
|
273 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same rank (KingdomFk, RankFk, RankCache)')
|
274 |
|
|
/*
|
275 |
|
|
SELECT cdm_t.Fullname, cdm_t.KingdomFk as Kingdom_CDM_EM2PESI, bm_t.KingdomFk as Kingdom_EM2PESI,
|
276 |
|
|
cdm_t.RankFk as RankFk_CDM_EM2PESI, bm_t.RankFk as RankFk_EM2PESI,
|
277 |
|
|
cdm_t.RankCache as Rank_CDM_EM2PESI, bm_t.RankCache as Rank_EM2PESI
|
278 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
279 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
280 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
281 |
|
|
WHERE ISNULL(bm_t.KingdomFk, 0) <> ISNULL(cdm_t.KingdomFk, 0) OR
|
282 |
|
|
ISNULL(bm_t.RankFk, 0) <> ISNULL(cdm_t.RankFk, 0) OR
|
283 |
|
|
ISNULL(bm_t.RankCache, '') <> ISNULL(cdm_t.RankCache, '')
|
284 |
|
|
*/
|
285 |
|
|
|
286 |
|
|
-- Status
|
287 |
|
|
SELECT @n = COUNT(*)
|
288 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
289 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
290 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
291 |
|
|
WHERE ISNULL(bm_t.NameStatusCache, '') <> ISNULL(cdm_t.NameStatusCache, '') OR
|
292 |
|
|
ISNULL(bm_t.TaxonStatusCache, '') <> ISNULL(cdm_t.TaxonStatusCache, '')
|
293 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
294 |
|
|
IF @n = 0 BEGIN
|
295 |
|
|
PRINT ('All identical taxa have the same status (NameStatusCache, TaxonStatusCache)')
|
296 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same status (NameStatusCache, TaxonStatusCache)')
|
297 |
|
|
/*
|
298 |
|
|
SELECT cdm_t.Fullname, cdm_t.NameStatusFk as NameStatusFk_CDM_EM2PESI, bm_t.NameStatusFk as NameStatusFk_EM2PESI,
|
299 |
|
|
cdm_t.NameStatusCache as NameStatus_CDM_EM2PESI, bm_t.NameStatusCache as NameStatus_EM2PESI,
|
300 |
|
|
cdm_t.TaxonStatusFk as TaxonStatusFk_CDM_EM2PESI, bm_t.TaxonStatusFk as TaxonStatusFk_EM2PESI,
|
301 |
|
|
cdm_t.TaxonStatusCache as TaxonStatus_CDM_EM2PESI, bm_t.TaxonStatusCache as TaxonStatus_EM2PESI
|
302 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
303 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
304 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
305 |
|
|
WHERE ISNULL(bm_t.NameStatusCache, '') <> ISNULL(cdm_t.NameStatusCache, '') OR
|
306 |
|
|
ISNULL(bm_t.TaxonStatusCache, '') <> ISNULL(cdm_t.TaxonStatusCache, '')
|
307 |
|
|
*/
|
308 |
|
|
|
309 |
|
|
-- Types
|
310 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
|
311 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
|
312 |
|
|
SET @n = @n_bm - @n_cdm
|
313 |
|
|
|
314 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
315 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
316 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
317 |
|
|
|
318 |
|
|
IF @n = 0 BEGIN
|
319 |
|
|
PRINT ('Both databases have the same number of taxa which have a type = ' + @str_n_bm)
|
320 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a type, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
321 |
|
|
|
322 |
|
|
SELECT @n = COUNT(*) -- in both databases types exist but are different
|
323 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
324 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
325 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
326 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId INNER JOIN
|
327 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
|
328 |
|
|
WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
|
329 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
330 |
|
|
IF @n = 0 BEGIN
|
331 |
|
|
PRINT ('All identical taxa that have types have the same type')
|
332 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different types')
|
333 |
|
|
/*
|
334 |
|
|
SELECT cdm_t.Fullname, cdm_pt.Fullname as Type_CDM_EM2PESI, bm_pt.Fullname as Type_EM2PESI
|
335 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
336 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
337 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
338 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId INNER JOIN
|
339 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
|
340 |
|
|
WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
|
341 |
|
|
ORDER BY cdm_t.Fullname
|
342 |
|
|
*/
|
343 |
|
|
SELECT @n_bm = COUNT(*) -- taxa with type only in EM2PESI
|
344 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
345 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
346 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
347 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId
|
348 |
|
|
WHERE NOT EXISTS
|
349 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
|
350 |
|
|
WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
|
351 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
352 |
|
|
IF @n_bm > 0 PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have types in EM2PESI but not in CDM_EM2PESI')
|
353 |
|
|
/*
|
354 |
|
|
SELECT cdm_t.Fullname, bm_pt.Fullname as Type_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
|
355 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
356 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
357 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
358 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId
|
359 |
|
|
WHERE NOT EXISTS
|
360 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
|
361 |
|
|
WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
|
362 |
|
|
ORDER BY cdm_t.Fullname
|
363 |
|
|
*/
|
364 |
|
|
SELECT @n_cdm = COUNT(*) -- taxa with type only in CDM_EM2PESI
|
365 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
366 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
367 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
368 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
|
369 |
|
|
WHERE NOT EXISTS
|
370 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON
|
371 |
|
|
WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
|
372 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
373 |
|
|
IF @n_cdm > 0 PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have types in CDM_EM2PESI but not in EM2PESI')
|
374 |
|
|
/*
|
375 |
|
|
SELECT cdm_t.Fullname, cdm_pt.Fullname as Type_CDM_EM2PESI, 'in CDM_EM2PESI but not in EM2PESI'
|
376 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
377 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
378 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
379 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
|
380 |
|
|
WHERE NOT EXISTS
|
381 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON
|
382 |
|
|
WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
|
383 |
|
|
*/
|
384 |
|
|
IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
|
385 |
|
|
PRINT ('All identical taxa have the same type')
|
386 |
|
|
END
|
387 |
|
|
|
388 |
|
|
-- QualityStatus
|
389 |
|
|
SELECT @n = COUNT(*)
|
390 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
391 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
392 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
393 |
|
|
WHERE ISNULL(bm_t.QualityStatusCache, '') <> ISNULL(cdm_t.QualityStatusCache, '')
|
394 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
395 |
|
|
IF @n = 0 BEGIN
|
396 |
|
|
PRINT ('All identical taxa have the same quality status (QualityStatusCache)')
|
397 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same quality status (QualityStatusCache)')
|
398 |
|
|
/*
|
399 |
|
|
SELECT cdm_t.Fullname, cdm_t.QualityStatusCache as QualityStatus_CDM_EM2PESI, bm_t.QualityStatusCache as QualityStatus_EM2PESI
|
400 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
401 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
402 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
403 |
|
|
WHERE ISNULL(bm_t.QualityStatusCache, '') <> ISNULL(cdm_t.QualityStatusCache, '')
|
404 |
|
|
*/
|
405 |
|
|
|
406 |
|
|
-- Experts
|
407 |
|
|
SELECT @n = COUNT(*)
|
408 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
409 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
410 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
411 |
|
|
WHERE ISNULL(bm_t.ExpertName, '') <> ISNULL(cdm_t.ExpertName, '') OR
|
412 |
|
|
ISNULL(bm_t.SpeciesExpertName, '') <> ISNULL(cdm_t.SpeciesExpertName, '')
|
413 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
414 |
|
|
IF @n = 0 BEGIN
|
415 |
|
|
PRINT ('All identical taxa have the same experts (ExpertName, SpeciesExpertName)')
|
416 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same experts (ExpertName, SpeciesExpertName)')
|
417 |
|
|
/*
|
418 |
|
|
SELECT cdm_t.Fullname, cdm_t.ExpertName as ExpertName_CDM_EM2PESI, bm_t.ExpertName as ExpertName_EM2PESI,
|
419 |
|
|
cdm_t.SpeciesExpertName as SpeciesExpertName_CDM_EM2PESI, bm_t.SpeciesExpertName as SpeciesExpertName_EM2PESI
|
420 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
421 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
422 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
423 |
|
|
WHERE ISNULL(bm_t.ExpertName, '') <> ISNULL(cdm_t.ExpertName, '') OR
|
424 |
|
|
ISNULL(bm_t.SpeciesExpertName, '') <> ISNULL(cdm_t.SpeciesExpertName, '')
|
425 |
|
|
*/
|
426 |
|
|
|
427 |
|
|
-- Citation
|
428 |
|
|
SELECT @n = COUNT(*)
|
429 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
430 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
431 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
432 |
|
|
WHERE ISNULL(bm_t.CacheCitation, '') <> ISNULL(cdm_t.CacheCitation, '')
|
433 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
434 |
|
|
IF @n = 0 BEGIN
|
435 |
|
|
PRINT ('All identical taxa have the same CacheCitation')
|
436 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same CacheCitation')
|
437 |
|
|
/*
|
438 |
|
|
SELECT cdm_t.Fullname, cdm_t.CacheCitation as CacheCitation_CDM_EM2PESI, bm_t.CacheCitation as CacheCitation_EM2PESI
|
439 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
440 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
441 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
442 |
|
|
WHERE ISNULL(bm_t.CacheCitation, '') <> ISNULL(cdm_t.CacheCitation, '')
|
443 |
|
|
*/
|
444 |
|
|
|
445 |
|
|
-- Original database
|
446 |
|
|
SELECT @n = COUNT(*)
|
447 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
448 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
449 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
450 |
|
|
WHERE ISNULL(bm_t.OriginalDB, '') <> ISNULL(cdm_t.OriginalDB, '')
|
451 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
452 |
|
|
IF @n = 0 BEGIN
|
453 |
|
|
PRINT ('All identical taxa have the same OriginalDB')
|
454 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same OriginalDB')
|
455 |
|
|
/*
|
456 |
|
|
SELECT cdm_t.Fullname, cdm_t.OriginalDB as OriginalDB_CDM_EM2PESI, bm_t.OriginalDB as OriginalDB_EM2PESI
|
457 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
458 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
459 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
460 |
|
|
WHERE ISNULL(bm_t.OriginalDB, '') <> ISNULL(cdm_t.OriginalDB, '')
|
461 |
|
|
*/
|
462 |
|
|
|
463 |
|
|
-- Action
|
464 |
|
|
SELECT @n = COUNT(*)
|
465 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
466 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
467 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
468 |
|
|
WHERE ISNULL(bm_t.LastAction, '') <> ISNULL(cdm_t.LastAction, '') OR
|
469 |
|
|
convert(smalldatetime, ISNULL(bm_t.LastActionDate, '00:00:00')) <>
|
470 |
|
|
convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
|
471 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
472 |
|
|
IF @n = 0 BEGIN
|
473 |
|
|
PRINT ('All identical taxa have the same last actions (LastAction, LastActionDate)')
|
474 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the last actions (LastAction, LastActionDate)')
|
475 |
|
|
/*
|
476 |
|
|
SELECT cdm_t.Fullname, cdm_t.LastAction as LastAction_CDM_EM2PESI, bm_t.LastAction as LastAction_EM2PESI,
|
477 |
|
|
cdm_t.LastActionDate as LastActionDate_CDM_EM2PESI, bm_t.LastActionDate as LastActionDate_EM2PESI
|
478 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
479 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
480 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
481 |
|
|
WHERE ISNULL(bm_t.LastAction, '') <> ISNULL(cdm_t.LastAction, '') OR
|
482 |
|
|
convert(smalldatetime, ISNULL(bm_t.LastActionDate, '00:00:00')) <>
|
483 |
|
|
convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
|
484 |
|
|
*/
|
485 |
|
|
|
486 |
eb191b81
|
Andreas Müller
|
|
487 |
|
|
|
488 |
|
|
-- Parents
|
489 |
|
|
|
490 |
|
|
PRINT ' '
|
491 |
|
|
PRINT 'PARENTS'
|
492 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
|
493 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
|
494 |
|
|
SET @n = @n_bm - @n_cdm
|
495 |
|
|
|
496 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
497 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
498 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
499 |
|
|
|
500 |
|
|
IF @n = 0 BEGIN
|
501 |
|
|
PRINT ('Both databases have the same number of taxa which have a parent = ' + @str_n_bm)
|
502 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a parent, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
503 |
|
|
|
504 |
|
|
SELECT @n = COUNT(*) -- in both databases parents exist but are different
|
505 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
506 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
507 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
508 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
|
509 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
|
510 |
|
|
WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
|
511 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
512 |
|
|
IF @n = 0 BEGIN
|
513 |
|
|
PRINT ('All identical taxa that have parents have the same parent')
|
514 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different parents')
|
515 |
|
|
/*
|
516 |
|
|
SELECT cdm_t.FullName as Child_CDM, cdm_pt.Fullname as Parent_CDM_EM2PESI, bm_t.FullName as child_sql, bm_pt.Fullname as Parent_EM2PESI
|
517 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
518 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
519 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
|
520 |
|
|
[ CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
|
521 |
|
|
WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
|
522 |
|
|
*/
|
523 |
|
|
SELECT @n_bm = COUNT(*) -- taxa with parent only in EM2PESI
|
524 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
525 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
526 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
527 |
|
|
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
|
528 |
|
|
WHERE NOT EXISTS
|
529 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
|
530 |
|
|
WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
|
531 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
532 |
|
|
IF @n_bm > 0 BEGIN
|
533 |
|
|
PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have parents in EM2PESI but not in CDM_EM2PESI')
|
534 |
|
|
END ELSE PRINT ('All identical taxa that have parents in EM2PESI do have parents in CDM_EM2PESI')
|
535 |
|
|
|
536 |
|
|
/*
|
537 |
|
|
SELECT cdm_t.Fullname ChildName, bm_pt.Fullname as Parent_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
|
538 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t
|
539 |
|
|
INNER JOIN [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
|
540 |
|
|
INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_t ON ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
541 |
|
|
|
542 |
|
|
WHERE cdm_t.ParentTaxonFk IS NULL AND NOT EXISTS
|
543 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON cdm_pt
|
544 |
|
|
WHERE (cdm_pt.IdInSource = bm_pt.IdInSource OR cdm_pt.IdInSource IS NULL AND bm_pt.IdInSource IS NULL)
|
545 |
|
|
AND ISNULL(cdm_pt.GUID, '') = ISNULL(bm_pt.GUID, '')
|
546 |
|
|
AND cdm_t.ParentTaxonFk = cdm_pt.TaxonId)
|
547 |
|
|
ORDER BY cdm_t.Fullname
|
548 |
|
|
|
549 |
|
|
*/
|
550 |
|
|
SELECT @n_cdm = COUNT(*) -- taxa with parent only in CDM_EM2PESI
|
551 |
|
|
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
|
552 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
553 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
|
554 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
|
555 |
|
|
WHERE NOT EXISTS
|
556 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON
|
557 |
|
|
WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
|
558 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
559 |
|
|
IF @n_cdm > 0 BEGIN
|
560 |
|
|
PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have parents in CDM_EM2PESI but not in EM2PESI')
|
561 |
|
|
END ELSE PRINT('All identical taxa that have parents in CDM_EM2PESI do have parents in EM2PESI')
|
562 |
|
|
|
563 |
|
|
/*
|
564 |
|
|
SELECT bm_t.Fullname ChildName, cdm_pt.Fullname as Parent_CDM, 'in CDM_EM2PESI but not in EM2PESI'
|
565 |
|
|
FROM [CDM_EM2PESI].[DBO].TAXON cdm_t
|
566 |
|
|
INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
|
567 |
|
|
INNER JOIN [EM2PESI].[DBO].TAXON bm_t ON ISNULL(cdm_t.GUID, '') = ISNULL(bm_t.GUID, '')
|
568 |
|
|
|
569 |
|
|
WHERE bm_t.ParentTaxonFk IS NULL AND NOT EXISTS
|
570 |
|
|
(SELECT * FROM [EM2PESI].[DBO].TAXON bm_pt
|
571 |
|
|
WHERE (bm_pt.IdInSource = cdm_pt.IdInSource OR bm_pt.IdInSource IS NULL AND cdm_pt.IdInSource IS NULL)
|
572 |
|
|
AND ISNULL(bm_pt.GUID, '') = ISNULL(cdm_pt.GUID, '')
|
573 |
|
|
AND bm_t.ParentTaxonFk = bm_pt.TaxonId)
|
574 |
|
|
ORDER BY bm_t.Fullname
|
575 |
|
|
*/
|
576 |
|
|
IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
|
577 |
|
|
PRINT ('All identical taxa have the same parent')
|
578 |
|
|
END
|
579 |
|
|
|
580 |
|
|
-- TreeIndex
|
581 |
|
|
/* This is not checked. This field should be created by the PESI-Procedure recalculateallstoredpaths.
|
582 |
|
|
Actually checking the parents of the same taxa is enough to ensure the compatibility of the taxonomical tree as a whole.
|
583 |
|
|
See the Parent section
|
584 |
|
|
*/
|
585 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
|
586 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
|
587 |
|
|
SET @n = @n_bm - @n_cdm
|
588 |
|
|
|
589 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
590 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
591 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
592 |
|
|
|
593 |
|
|
IF @n = 0 BEGIN
|
594 |
|
|
PRINT ('Both databases have the same number of taxa which have a tree index = ' + @str_n_bm)
|
595 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a tree index, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
596 |
|
|
|
597 |
d68b0de2
|
Andreas Müller
|
------------------------------------------- RelTaxon -------------------------------
|
598 |
|
|
PRINT ' '
|
599 |
|
|
PRINT 'RELATIONSHIP'
|
600 |
|
|
|
601 |
|
|
-- taxonomical relationships between taxa and nomenclatural relationships between names
|
602 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].RELTAXON
|
603 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].RELTAXON
|
604 |
|
|
SET @n = @n_bm - @n_cdm
|
605 |
|
|
|
606 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
607 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
608 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
609 |
|
|
|
610 |
|
|
IF @n = 0 BEGIN
|
611 |
|
|
PRINT ('Both databases have the same number of relationship records = ' + @str_n_bm)
|
612 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of relationship records, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
613 |
|
|
|
614 |
|
|
SELECT @n_bm = COUNT(*) -- relationships only in EM2PESI
|
615 |
|
|
FROM [EM2PESI].[DBO].RELTAXON bm_rt INNER JOIN
|
616 |
|
|
[EM2PESI].[DBO].TAXON bm_t1 ON bm_rt.TaxonFk1 = bm_t1.TaxonId INNER JOIN
|
617 |
|
|
[EM2PESI].[DBO].TAXON bm_t2 ON bm_rt.TaxonFk2 = bm_t2.TaxonId INNER JOIN
|
618 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t1
|
619 |
|
|
ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
|
620 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t2
|
621 |
|
|
ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
|
622 |
|
|
WHERE NOT EXISTS
|
623 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].RELTAXON
|
624 |
|
|
WHERE TaxonFk1 = cdm_t1.TaxonId AND TaxonFk2 = cdm_t2.TaxonId AND RelQualifierCache = bm_rt.RelQualifierCache)
|
625 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
626 |
|
|
IF @n_bm = 0 BEGIN
|
627 |
|
|
PRINT ('All relationships in EM2PESI exist also in CDM_EM2PESI')
|
628 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing relationships for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
629 |
|
|
/*
|
630 |
|
|
SELECT bm_t1.Fullname, bm_rt.RelQualifierCache, bm_t2.Fullname, 'in EM2PESI but not in CDM_EM2PESI'
|
631 |
|
|
FROM [EM2PESI].[DBO].RELTAXON bm_rt INNER JOIN
|
632 |
|
|
[EM2PESI].[DBO].TAXON bm_t1 ON bm_rt.TaxonFk1 = bm_t1.TaxonId INNER JOIN
|
633 |
|
|
[EM2PESI].[DBO].TAXON bm_t2 ON bm_rt.TaxonFk2 = bm_t2.TaxonId INNER JOIN
|
634 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t1
|
635 |
|
|
ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
|
636 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t2
|
637 |
|
|
ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
|
638 |
|
|
WHERE NOT EXISTS
|
639 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].RELTAXON
|
640 |
|
|
WHERE TaxonFk1 = cdm_t1.TaxonId AND TaxonFk2 = cdm_t2.TaxonId AND RelQualifierCache = bm_rt.RelQualifierCache)
|
641 |
|
|
|
642 |
|
|
SELECT [RelTaxonQualifierFk]
|
643 |
|
|
,[RelQualifierCache]
|
644 |
|
|
, COUNT(*) as n
|
645 |
|
|
FROM [CDM_EM2PESI].[dbo].[RelTaxon] rel INNER JOIN [CDM_EM2PESI].[dbo].[Taxon] t1 ON t1.TaxonId = rel.TaxonFk1
|
646 |
|
|
INNER JOIN [CDM_EM2PESI].[dbo].[Taxon] t2 ON t2.TaxonId = rel.TaxonFk2
|
647 |
|
|
GROUP BY RelTaxonQualifierFk, RelQualifierCache
|
648 |
|
|
ORDER BY RelTaxonQualifierFk, COUNT(*) DESC
|
649 |
|
|
|
650 |
|
|
SELECT [RelTaxonQualifierFk]
|
651 |
|
|
,[RelQualifierCache]
|
652 |
|
|
, COUNT(*) as n
|
653 |
|
|
FROM [EM2PESI].[dbo].[RelTaxon]
|
654 |
|
|
GROUP BY RelTaxonQualifierFk, RelQualifierCache
|
655 |
|
|
ORDER BY RelTaxonQualifierFk, COUNT(*) DESC
|
656 |
|
|
|
657 |
|
|
SELECT t1.QualifierId, t1.Qualifier, t1.n as n_cdm, t2.n as n_bm, t1.n - t2.n as diff
|
658 |
|
|
FROM (
|
659 |
|
|
|
660 |
|
|
SELECT rtq.QualifierId , rtq.Qualifier, COUNT(rel.RelTaxonId) as n
|
661 |
|
|
FROM RelTaxonQualifier rtq LEFT OUTER JOIN [CDM_EM2PESI].[dbo].[RelTaxon] rel ON rel.RelTaxonQualifierFk = rtq.QualifierId
|
662 |
|
|
LEFT OUTER JOIN [CDM_EM2PESI].[dbo].[Taxon] t1 ON t1.TaxonId = rel.TaxonFk1
|
663 |
|
|
LEFT OUTER JOIN [CDM_EM2PESI].[dbo].[Taxon] t2 ON t2.TaxonId = rel.TaxonFk2
|
664 |
|
|
GROUP BY QualifierId, Qualifier
|
665 |
|
|
) t1 INNER JOIN (
|
666 |
|
|
SELECT rtq2.QualifierId , rtq2.Qualifier, COUNT(rel.RelTaxonId) as n
|
667 |
|
|
FROM RelTaxonQualifier rtq2 LEFT OUTER JOIN [EM2PESI].[dbo].[RelTaxon] rel ON rel.RelTaxonQualifierFk = rtq2.QualifierId
|
668 |
|
|
LEFT OUTER JOIN [EM2PESI].[dbo].[Taxon] t1 ON t1.TaxonId = rel.TaxonFk1
|
669 |
|
|
LEFT OUTER JOIN [EM2PESI].[dbo].[Taxon] t2 ON t2.TaxonId = rel.TaxonFk2
|
670 |
|
|
GROUP BY QualifierId, Qualifier
|
671 |
|
|
) t2 ON t1.QualifierId = t2.QualifierId AND t1.n <> t2.n
|
672 |
|
|
ORDER BY t1.QualifierId DESC
|
673 |
|
|
|
674 |
|
|
*/
|
675 |
|
|
SELECT @n_cdm = COUNT(*) -- relationships only in CDM_EM2PESI
|
676 |
|
|
FROM [CDM_EM2PESI].[DBO].RELTAXON cdm_rt INNER JOIN
|
677 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t1 ON cdm_rt.TaxonFk1 = cdm_t1.TaxonId INNER JOIN
|
678 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t2 ON cdm_rt.TaxonFk2 = cdm_t2.TaxonId INNER JOIN
|
679 |
|
|
[EM2PESI].[DBO].TAXON bm_t1
|
680 |
|
|
ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
|
681 |
|
|
[EM2PESI].[DBO].TAXON bm_t2
|
682 |
|
|
ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
|
683 |
|
|
WHERE NOT EXISTS
|
684 |
|
|
(SELECT * FROM [EM2PESI].[DBO].RELTAXON
|
685 |
|
|
WHERE TaxonFk1 = bm_t1.TaxonId AND TaxonFk2 = bm_t2.TaxonId AND RelQualifierCache = cdm_rt.RelQualifierCache)
|
686 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
687 |
|
|
IF @n_cdm = 0 BEGIN
|
688 |
|
|
PRINT ('All relationships in CDM_EM2PESI exist also in EM2PESI')
|
689 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing relationships for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
690 |
|
|
/*
|
691 |
|
|
SELECT cdm_t1.Fullname, cdm_rt.RelQualifierCache, cdm_t2.Fullname, 'in CDM_EM2PESI but not in CDM_EM2PESI'
|
692 |
|
|
FROM [CDM_EM2PESI].[DBO].RELTAXON cdm_rt INNER JOIN
|
693 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t1 ON cdm_rt.TaxonFk1 = cdm_t1.TaxonId INNER JOIN
|
694 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t2 ON cdm_rt.TaxonFk2 = cdm_t2.TaxonId INNER JOIN
|
695 |
|
|
[EM2PESI].[DBO].TAXON bm_t1
|
696 |
|
|
ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
|
697 |
|
|
[EM2PESI].[DBO].TAXON bm_t2
|
698 |
|
|
ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
|
699 |
|
|
WHERE NOT EXISTS
|
700 |
|
|
(SELECT * FROM [EM2PESI].[DBO].RELTAXON
|
701 |
|
|
WHERE TaxonFk1 = bm_t1.TaxonId AND TaxonFk2 = bm_t2.TaxonId AND RelQualifierCache = cdm_rt.RelQualifierCache)
|
702 |
|
|
*/
|
703 |
|
|
IF @n_cdm = 0 AND @n_bm = 0 BEGIN
|
704 |
|
|
PRINT ('All relationships are identical in both databases')
|
705 |
|
|
END
|
706 |
|
|
|
707 |
|
|
|
708 |
|
|
------------------------------------------- CommonName -------------------------------
|
709 |
|
|
PRINT ' '
|
710 |
|
|
PRINT 'COMMON NAMES'
|
711 |
|
|
|
712 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].CommonName
|
713 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].CommonName
|
714 |
|
|
SET @n = @n_bm - @n_cdm
|
715 |
|
|
|
716 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
717 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
718 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
719 |
|
|
|
720 |
|
|
IF @n = 0 BEGIN
|
721 |
|
|
PRINT ('Both databases have the same number of common names = ' + @str_n_bm)
|
722 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of common names, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
723 |
|
|
|
724 |
|
|
SELECT @n_bm = COUNT(*) -- common names only in EM2PESI
|
725 |
|
|
FROM [EM2PESI].[DBO].CommonName bm_cn INNER JOIN
|
726 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_cn.TaxonFk = bm_t.TaxonId INNER JOIN
|
727 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
728 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
729 |
|
|
WHERE NOT EXISTS
|
730 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].CommonName
|
731 |
|
|
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(bm_cn.CommonName,'')
|
732 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(bm_cn.LanguageCache,'')
|
733 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_cn.SourceNameCache,'')
|
734 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_cn.SpeciesExpertName,'')
|
735 |
|
|
AND ISNULL(LastAction,'') = ISNULL(bm_cn.LastAction,'')
|
736 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_cn.LastActionDate,'00:00:00')
|
737 |
|
|
)
|
738 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
739 |
|
|
IF @n_bm = 0 BEGIN
|
740 |
|
|
PRINT ('All common names in EM2PESI exist also in CDM_EM2PESI')
|
741 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing common names for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
742 |
|
|
/*
|
743 |
|
|
SELECT bm_t.Fullname, bm_cn.CommonName, bm_cn.LanguageCache,
|
744 |
|
|
bm_cn.SourceNameCache, bm_cn.SpeciesExpertName, bm_cn.LastAction,
|
745 |
|
|
bm_cn.LastActionDate, 'in EM2PESI but not in CDM_EM2PESI'
|
746 |
|
|
FROM [EM2PESI].[DBO].CommonName bm_cn INNER JOIN
|
747 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_cn.TaxonFk = bm_t.TaxonId INNER JOIN
|
748 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
749 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
750 |
|
|
WHERE NOT EXISTS
|
751 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].CommonName
|
752 |
|
|
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(bm_cn.CommonName,'')
|
753 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(bm_cn.LanguageCache,'')
|
754 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_cn.SourceNameCache,'')
|
755 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_cn.SpeciesExpertName,'')
|
756 |
|
|
AND ISNULL(LastAction,'') = ISNULL(bm_cn.LastAction,'')
|
757 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_cn.LastActionDate,'00:00:00')
|
758 |
|
|
)
|
759 |
|
|
*/
|
760 |
|
|
SELECT @n_cdm = COUNT(*) -- common names only in CDM_EM2PESI
|
761 |
|
|
FROM [CDM_EM2PESI].[DBO].CommonName cdm_cn INNER JOIN
|
762 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_cn.TaxonFk = cdm_t.TaxonId INNER JOIN
|
763 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
764 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
765 |
|
|
WHERE NOT EXISTS
|
766 |
|
|
(SELECT * FROM [EM2PESI].[DBO].CommonName
|
767 |
|
|
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(cdm_cn.CommonName,'')
|
768 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(cdm_cn.LanguageCache,'')
|
769 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_cn.SourceNameCache,'')
|
770 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_cn.SpeciesExpertName,'')
|
771 |
|
|
AND ISNULL(LastAction,'') = ISNULL(cdm_cn.LastAction,'')
|
772 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_cn.LastActionDate,'00:00:00')
|
773 |
|
|
)
|
774 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
775 |
|
|
IF @n_cdm = 0 BEGIN
|
776 |
|
|
PRINT ('All common names in CDM_EM2PESI exist also in EM2PESI')
|
777 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing common names for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
778 |
|
|
/*
|
779 |
|
|
SELECT bm_t.Fullname, cdm_cn.CommonName, cdm_cn.LanguageCache,
|
780 |
|
|
cdm_cn.SourceNameCache, cdm_cn.SpeciesExpertName, cdm_cn.LastAction,
|
781 |
|
|
cdm_cn.LastActionDate, 'in CDM_EM2PESI but not in EM2PESI'
|
782 |
|
|
FROM [CDM_EM2PESI].[DBO].CommonName cdm_cn INNER JOIN
|
783 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_cn.TaxonFk = cdm_t.TaxonId INNER JOIN
|
784 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
785 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
786 |
|
|
WHERE NOT EXISTS
|
787 |
|
|
(SELECT * FROM [EM2PESI].[DBO].CommonName
|
788 |
|
|
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(cdm_cn.CommonName,'')
|
789 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(cdm_cn.LanguageCache,'')
|
790 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_cn.SourceNameCache,'')
|
791 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_cn.SpeciesExpertName,'')
|
792 |
|
|
AND ISNULL(LastAction,'') = ISNULL(cdm_cn.LastAction,'')
|
793 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_cn.LastActionDate,'00:00:00')
|
794 |
|
|
)
|
795 |
|
|
*/
|
796 |
|
|
IF @n_cdm = 0 AND @n_bm = 0 BEGIN
|
797 |
|
|
PRINT ('All common names are identical in both databases')
|
798 |
|
|
END
|
799 |
|
|
|
800 |
|
|
------------------------------------------- AdditionalTaxonSource -------------------------------
|
801 |
|
|
PRINT ' '
|
802 |
eb191b81
|
Andreas Müller
|
PRINT 'ADDITIONAL TAXON SOURCE'
|
803 |
d68b0de2
|
Andreas Müller
|
|
804 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].AdditionalTaxonSource
|
805 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
|
806 |
|
|
SET @n = @n_bm - @n_cdm
|
807 |
|
|
|
808 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
809 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
810 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
811 |
|
|
|
812 |
|
|
IF @n = 0 BEGIN
|
813 |
|
|
PRINT ('Both databases have the same number of additional sources = ' + @str_n_bm)
|
814 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of additional sources, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
815 |
|
|
|
816 |
|
|
SELECT @n_bm = COUNT(*) -- additional sources only in EM2PESI
|
817 |
|
|
FROM [EM2PESI].[DBO].AdditionalTaxonSource bm_ats INNER JOIN
|
818 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_ats.TaxonFk = bm_t.TaxonId INNER JOIN
|
819 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
820 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
821 |
|
|
WHERE NOT EXISTS
|
822 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
|
823 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = cdm_t.TaxonId
|
824 |
|
|
AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
|
825 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'')
|
826 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'')
|
827 |
|
|
)
|
828 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
829 |
|
|
IF @n_bm = 0 BEGIN
|
830 |
|
|
PRINT ('All additional sources in EM2PESI exist also in CDM_EM2PESI')
|
831 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing additional sources for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
832 |
|
|
/*
|
833 |
|
|
SELECT bm_t.Fullname, bm_ats.SourceUseCache, bm_ats.SourceNameCache,
|
834 |
|
|
bm_ats.SourceDetail, 'in EM2PESI but not in CDM_EM2PESI'
|
835 |
|
|
FROM [EM2PESI].[DBO].AdditionalTaxonSource bm_ats INNER JOIN
|
836 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_ats.TaxonFk = bm_t.TaxonId INNER JOIN
|
837 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
838 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
839 |
|
|
WHERE NOT EXISTS
|
840 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
|
841 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = cdm_t.TaxonId
|
842 |
|
|
AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
|
843 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'')
|
844 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'')
|
845 |
|
|
)
|
846 |
eb191b81
|
Andreas Müller
|
ORDER BY bm_t.Fullname, bm_ats.SourceUseCache, bm_ats.SourceNameCache
|
847 |
|
|
|
848 |
d68b0de2
|
Andreas Müller
|
*/
|
849 |
|
|
SELECT @n_cdm = COUNT(*) -- additional sources only in CDM_EM2PESI
|
850 |
|
|
FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource cdm_ats INNER JOIN
|
851 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_ats.TaxonFk = cdm_t.TaxonId INNER JOIN
|
852 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
853 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
854 |
|
|
WHERE NOT EXISTS
|
855 |
|
|
(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource
|
856 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = bm_t.TaxonId
|
857 |
|
|
AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
|
858 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'')
|
859 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'')
|
860 |
|
|
)
|
861 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
862 |
|
|
IF @n_cdm = 0 BEGIN
|
863 |
|
|
PRINT ('All additional sources in CDM_EM2PESI exist also in EM2PESI')
|
864 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing additional sources for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
865 |
|
|
/*
|
866 |
|
|
SELECT bm_t.Fullname, cdm_ats.SourceUseCache, cdm_ats.SourceNameCache,
|
867 |
|
|
cdm_ats.SourceDetail, 'in EM2PESI but not in CDM_EM2PESI'
|
868 |
|
|
FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource cdm_ats INNER JOIN
|
869 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_ats.TaxonFk = cdm_t.TaxonId INNER JOIN
|
870 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
871 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
872 |
|
|
WHERE NOT EXISTS
|
873 |
|
|
(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource
|
874 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = bm_t.TaxonId
|
875 |
|
|
AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
|
876 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'')
|
877 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'')
|
878 |
|
|
)
|
879 |
eb191b81
|
Andreas Müller
|
ORDER BY bm_t.Fullname, cdm_ats.SourceUseCache, cdm_ats.SourceNameCache
|
880 |
d68b0de2
|
Andreas Müller
|
*/
|
881 |
|
|
IF @n_cdm = 0 AND @n_bm = 0 BEGIN
|
882 |
|
|
PRINT ('All additional sources are identical in both databases')
|
883 |
|
|
END
|
884 |
|
|
|
885 |
|
|
------------------------------ Image (currently exist only in ERMS) -------------------------------
|
886 |
|
|
PRINT ' '
|
887 |
|
|
PRINT 'IMAGES'
|
888 |
|
|
|
889 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Image
|
890 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Image
|
891 |
|
|
SET @n = @n_bm - @n_cdm
|
892 |
|
|
|
893 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
894 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
895 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
896 |
|
|
|
897 |
|
|
IF @n = 0 BEGIN
|
898 |
|
|
PRINT ('Both databases have the same number of images = ' + @str_n_bm)
|
899 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of images, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
900 |
|
|
|
901 |
|
|
SELECT @n_bm = COUNT(*) -- images only in EM2PESI
|
902 |
|
|
FROM [EM2PESI].[DBO].Image bm_i INNER JOIN
|
903 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_i.TaxonFk = bm_t.TaxonId INNER JOIN
|
904 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
905 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
906 |
|
|
WHERE NOT EXISTS
|
907 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].Image
|
908 |
|
|
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(bm_i.img_thumb,'')
|
909 |
|
|
AND ISNULL(img_url,'') = ISNULL(bm_i.img_url,'')
|
910 |
|
|
)
|
911 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
912 |
|
|
IF @n_bm = 0 BEGIN
|
913 |
|
|
PRINT ('All images in EM2PESI exist also in CDM_EM2PESI')
|
914 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing images for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
915 |
|
|
/*
|
916 |
|
|
SELECT bm_t.Fullname, bm_i.img_thumb, bm_i.img_url, 'in EM2PESI but not in CDM_EM2PESI'
|
917 |
|
|
FROM [EM2PESI].[DBO].Image bm_i INNER JOIN
|
918 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_i.TaxonFk = bm_t.TaxonId INNER JOIN
|
919 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
920 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
921 |
|
|
WHERE NOT EXISTS
|
922 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].Image
|
923 |
|
|
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(bm_i.img_thumb,'')
|
924 |
|
|
AND ISNULL(img_url,'') = ISNULL(bm_i.img_url,'')
|
925 |
|
|
)
|
926 |
|
|
*/
|
927 |
|
|
SELECT @n_cdm = COUNT(*) -- images only in CDM_EM2PESI
|
928 |
|
|
FROM [CDM_EM2PESI].[DBO].Image cdm_i INNER JOIN
|
929 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_i.TaxonFk = cdm_t.TaxonId INNER JOIN
|
930 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
931 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
932 |
|
|
WHERE NOT EXISTS
|
933 |
|
|
(SELECT * FROM [EM2PESI].[DBO].Image
|
934 |
|
|
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(cdm_i.img_thumb,'')
|
935 |
|
|
AND ISNULL(img_url,'') = ISNULL(cdm_i.img_url,'')
|
936 |
|
|
)
|
937 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
938 |
|
|
IF @n_cdm = 0 BEGIN
|
939 |
|
|
PRINT ('All images in CDM_EM2PESI exist also in EM2PESI')
|
940 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing images for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
941 |
|
|
/*
|
942 |
|
|
SELECT bm_t.Fullname, cdm_i.img_thumb, cdm_i.img_url, 'in CDM_EM2PESI but not in EM2PESI'
|
943 |
|
|
FROM [CDM_EM2PESI].[DBO].Image cdm_i INNER JOIN
|
944 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_i.TaxonFk = cdm_t.TaxonId INNER JOIN
|
945 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
946 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
947 |
|
|
WHERE NOT EXISTS
|
948 |
|
|
(SELECT * FROM [EM2PESI].[DBO].Image
|
949 |
|
|
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(cdm_i.img_thumb,'')
|
950 |
|
|
AND ISNULL(img_url,'') = ISNULL(cdm_i.img_url,'')
|
951 |
|
|
)
|
952 |
|
|
*/
|
953 |
|
|
IF @n_cdm = 0 AND @n_bm = 0 BEGIN
|
954 |
|
|
PRINT ('All images are identical in both databases')
|
955 |
|
|
END
|
956 |
|
|
|
957 |
|
|
------------------------------------------- Note -------------------------------
|
958 |
|
|
PRINT ' '
|
959 |
|
|
PRINT 'NOTES'
|
960 |
|
|
|
961 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN
|
962 |
|
|
[EM2PESI].[DBO].NoteSource ON NoteFk = NoteId
|
963 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN
|
964 |
|
|
[CDM_EM2PESI].[DBO].NoteSource ON NoteFk = NoteId
|
965 |
|
|
SET @n = @n_bm - @n_cdm
|
966 |
|
|
|
967 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
968 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
969 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
970 |
|
|
|
971 |
|
|
IF @n = 0 BEGIN
|
972 |
|
|
PRINT ('Both databases have the same number of note * notesource results = ' + @str_n_bm)
|
973 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of note * notesource results, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
974 |
|
|
|
975 |
|
|
SELECT @n_bm = COUNT(*) -- note * notesource results only in EM2PESI
|
976 |
|
|
FROM [EM2PESI].[DBO].Note bm_n INNER JOIN
|
977 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_n.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
|
978 |
|
|
[EM2PESI].[DBO].NoteSource bm_ns ON bm_ns.NoteFk = bm_n.NoteId INNER JOIN
|
979 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
980 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
981 |
|
|
WHERE NOT EXISTS
|
982 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
|
983 |
|
|
ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
|
984 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = cdm_t.TaxonId
|
985 |
|
|
AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
|
986 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
|
987 |
|
|
AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
|
988 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
|
989 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
|
990 |
|
|
AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
|
991 |
eb191b81
|
Andreas Müller
|
AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
|
992 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
|
993 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
|
994 |
|
|
)
|
995 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
996 |
|
|
IF @n_bm = 0 BEGIN
|
997 |
|
|
PRINT ('All note * notesource results in EM2PESI exist also in CDM_EM2PESI')
|
998 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing note * notesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
999 |
|
|
/*
|
1000 |
eb191b81
|
Andreas Müller
|
Typical Problems:
|
1001 |
|
|
Duplicate entries during E+M SQL import for taxa using the same name object.
|
1002 |
|
|
Missing source info by E+M SQL import
|
1003 |
|
|
Last Action date automatically created (but wrong) by CDM import
|
1004 |
|
|
|
1005 |
d68b0de2
|
Andreas Müller
|
SELECT bm_t.Fullname, bm_n.Note_1, bm_n.Note_2, bm_n.NoteCategoryCache, bm_n.LanguageCache,
|
1006 |
|
|
bm_n.SpeciesExpertName, bm_n.LastAction, bm_n.LastActionDate, bm_ns.SourceNameCache, bm_ns.SourceDetail,
|
1007 |
|
|
'in EM2PESI but not in CDM_EM2PESI'
|
1008 |
|
|
FROM [EM2PESI].[DBO].Note bm_n INNER JOIN
|
1009 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_n.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
|
1010 |
|
|
[EM2PESI].[DBO].NoteSource bm_ns ON bm_ns.NoteFk = bm_n.NoteId INNER JOIN
|
1011 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
1012 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1013 |
|
|
WHERE NOT EXISTS
|
1014 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
|
1015 |
|
|
ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
|
1016 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = cdm_t.TaxonId
|
1017 |
|
|
AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
|
1018 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
|
1019 |
|
|
AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
|
1020 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
|
1021 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
|
1022 |
|
|
AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
|
1023 |
eb191b81
|
Andreas Müller
|
AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
|
1024 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
|
1025 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
|
1026 |
|
|
)
|
1027 |
eb191b81
|
Andreas Müller
|
ORDER BY bm_n.NoteCategoryFk, bm_t.Fullname, bm_n.Note_1
|
1028 |
d68b0de2
|
Andreas Müller
|
*/
|
1029 |
|
|
SELECT @n_cdm = COUNT(*) -- note * notesource results only in CDM_EM2PESI
|
1030 |
|
|
FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
|
1031 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_n.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
|
1032 |
|
|
[CDM_EM2PESI].[DBO].NoteSource cdm_ns ON cdm_ns.NoteFk = cdm_n.NoteId INNER JOIN
|
1033 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
1034 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1035 |
|
|
WHERE NOT EXISTS
|
1036 |
|
|
(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
|
1037 |
|
|
ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
|
1038 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = bm_t.TaxonId
|
1039 |
|
|
AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
|
1040 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
|
1041 |
|
|
AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
|
1042 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
|
1043 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
|
1044 |
|
|
AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
|
1045 |
eb191b81
|
Andreas Müller
|
AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
|
1046 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
|
1047 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
|
1048 |
|
|
)
|
1049 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
1050 |
|
|
IF @n_cdm = 0 BEGIN
|
1051 |
|
|
PRINT ('All note * notesource results in CDM_EM2PESI exist also in EM2PESI')
|
1052 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing note * notesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
1053 |
|
|
/*
|
1054 |
|
|
SELECT bm_t.Fullname, cdm_n.Note_1, cdm_n.Note_2, cdm_n.NoteCategoryCache, cdm_n.LanguageCache,
|
1055 |
|
|
cdm_n.SpeciesExpertName, cdm_n.LastAction, cdm_n.LastActionDate, cdm_ns.SourceNameCache, cdm_ns.SourceDetail,
|
1056 |
|
|
'in CDM_EM2PESI but not in EM2PESI'
|
1057 |
|
|
FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
|
1058 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_n.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
|
1059 |
|
|
[CDM_EM2PESI].[DBO].NoteSource cdm_ns ON cdm_ns.NoteFk = cdm_n.NoteId INNER JOIN
|
1060 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
1061 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1062 |
|
|
WHERE NOT EXISTS
|
1063 |
|
|
(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
|
1064 |
|
|
ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
|
1065 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = bm_t.TaxonId
|
1066 |
|
|
AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
|
1067 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
|
1068 |
|
|
AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
|
1069 |
|
|
AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
|
1070 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
|
1071 |
|
|
AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
|
1072 |
eb191b81
|
Andreas Müller
|
AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
|
1073 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
|
1074 |
|
|
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
|
1075 |
|
|
)
|
1076 |
eb191b81
|
Andreas Müller
|
ORDER BY cdm_n.NoteCategoryFk, cdm_t.Fullname,cdm_n.Note_1
|
1077 |
|
|
|
1078 |
d68b0de2
|
Andreas Müller
|
*/
|
1079 |
|
|
IF @n_cdm = 0 AND @n_bm = 0 BEGIN
|
1080 |
|
|
PRINT ('All note * notesource results are identical in both databases')
|
1081 |
|
|
END
|
1082 |
|
|
|
1083 |
|
|
------------------------------------------- Occurrence -------------------------------
|
1084 |
|
|
PRINT ' '
|
1085 |
|
|
PRINT 'OCCURRENCES'
|
1086 |
|
|
|
1087 |
|
|
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN
|
1088 |
|
|
[EM2PESI].[DBO].OccurrenceSource ON OccurrenceFk = OccurrenceId
|
1089 |
|
|
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN
|
1090 |
|
|
[CDM_EM2PESI].[DBO].OccurrenceSource ON OccurrenceFk = OccurrenceId
|
1091 |
|
|
SET @n = @n_bm - @n_cdm
|
1092 |
|
|
|
1093 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
1094 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
1095 |
|
|
SET @str_n = Cast(@n AS NVARCHAR)
|
1096 |
|
|
|
1097 |
|
|
IF @n = 0 BEGIN
|
1098 |
|
|
PRINT ('Both databases have the same number of occurrence * occurrencesource results = ' + @str_n_bm)
|
1099 |
|
|
END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of occurrence * occurrencesource results, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
|
1100 |
|
|
|
1101 |
|
|
SELECT @n_bm = COUNT(*) -- occurrence * occurrencesource results only in EM2PESI
|
1102 |
|
|
FROM [EM2PESI].[DBO].Occurrence bm_o INNER JOIN
|
1103 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_o.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
|
1104 |
|
|
[EM2PESI].[DBO].OccurrenceSource bm_os ON bm_os.OccurrenceFk = bm_o.OccurrenceId INNER JOIN
|
1105 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
1106 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1107 |
|
|
WHERE NOT EXISTS
|
1108 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
|
1109 |
|
|
ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
|
1110 |
|
|
WHERE TaxonFk = cdm_t.TaxonId
|
1111 |
|
|
AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
|
1112 |
|
|
AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
|
1113 |
|
|
AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
|
1114 |
|
|
AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
|
1115 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_o.SpeciesExpertName,'')
|
1116 |
|
|
AND ISNULL(LastAction,'') = ISNULL(bm_o.LastAction,'')
|
1117 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_o.LastActionDate,'00:00:00')
|
1118 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
|
1119 |
|
|
AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
|
1120 |
|
|
)
|
1121 |
|
|
SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
|
1122 |
|
|
IF @n_bm = 0 BEGIN
|
1123 |
|
|
PRINT ('All occurrence * occurrencesource results in EM2PESI exist also in CDM_EM2PESI')
|
1124 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing occurrence * occurrencesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
1125 |
|
|
/*
|
1126 |
|
|
SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, bm_t.Fullname, bm_o.TaxonFullNameCache, bm_o.AreaNameCache, bm_o.OccurrenceStatusCache, bm_o.Notes,
|
1127 |
|
|
bm_o.SpeciesExpertName, bm_o.LastAction, bm_o.LastActionDate, bm_os.SourceNameCache, bm_os.OldTaxonName,
|
1128 |
|
|
'in EM2PESI but not in CDM_EM2PESI'
|
1129 |
|
|
FROM [EM2PESI].[DBO].Occurrence bm_o INNER JOIN
|
1130 |
|
|
[EM2PESI].[DBO].TAXON bm_t ON bm_o.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
|
1131 |
|
|
[EM2PESI].[DBO].OccurrenceSource bm_os ON bm_os.OccurrenceFk = bm_o.OccurrenceId INNER JOIN
|
1132 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t
|
1133 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1134 |
|
|
WHERE NOT EXISTS
|
1135 |
|
|
(SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
|
1136 |
|
|
ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
|
1137 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = cdm_t.TaxonId
|
1138 |
|
|
AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
|
1139 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
|
1140 |
|
|
AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
|
1141 |
|
|
AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
|
1142 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_o.SpeciesExpertName,'')
|
1143 |
|
|
AND ISNULL(LastAction,'') = ISNULL(bm_o.LastAction,'')
|
1144 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_o.LastActionDate,'00:00:00')
|
1145 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
|
1146 |
|
|
AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
|
1147 |
|
|
)
|
1148 |
eb191b81
|
Andreas Müller
|
ORDER BY bm_t.Fullname, AreaNameCache
|
1149 |
d68b0de2
|
Andreas Müller
|
*/
|
1150 |
|
|
SELECT @n_cdm = COUNT(*) -- occurrence * occurrencesource results only in CDM_EM2PESI
|
1151 |
|
|
FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
|
1152 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_o.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
|
1153 |
|
|
[CDM_EM2PESI].[DBO].OccurrenceSource cdm_os ON cdm_os.OccurrenceFk = cdm_o.OccurrenceId INNER JOIN
|
1154 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
1155 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1156 |
|
|
WHERE NOT EXISTS
|
1157 |
|
|
(SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
|
1158 |
|
|
ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
|
1159 |
|
|
WHERE TaxonFk = bm_t.TaxonId
|
1160 |
|
|
AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
|
1161 |
|
|
AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
|
1162 |
|
|
AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
|
1163 |
|
|
AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
|
1164 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_o.SpeciesExpertName,'')
|
1165 |
|
|
AND ISNULL(LastAction,'') = ISNULL(cdm_o.LastAction,'')
|
1166 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_o.LastActionDate,'00:00:00')
|
1167 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
|
1168 |
|
|
AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
|
1169 |
|
|
)
|
1170 |
|
|
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
|
1171 |
|
|
IF @n_cdm = 0 BEGIN
|
1172 |
|
|
PRINT ('All occurrence * occurrencesource results in CDM_EM2PESI exist also in EM2PESI')
|
1173 |
|
|
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing occurrence * occurrencesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
1174 |
|
|
/*
|
1175 |
eb191b81
|
Andreas Müller
|
SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, cdm_t.Fullname, cdm_o.TaxonFullNameCache, cdm_o.AreaNameCache, cdm_o.OccurrenceStatusCache, cdm_o.Notes,
|
1176 |
d68b0de2
|
Andreas Müller
|
cdm_o.SpeciesExpertName, cdm_o.LastAction, cdm_o.LastActionDate, cdm_os.SourceNameCache, cdm_os.OldTaxonName,
|
1177 |
|
|
'in CDM_EM2PESI but not in EM2PESI'
|
1178 |
|
|
FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
|
1179 |
|
|
[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_o.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
|
1180 |
|
|
[CDM_EM2PESI].[DBO].OccurrenceSource cdm_os ON cdm_os.OccurrenceFk = cdm_o.OccurrenceId INNER JOIN
|
1181 |
|
|
[EM2PESI].[DBO].TAXON bm_t
|
1182 |
|
|
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
|
1183 |
|
|
WHERE NOT EXISTS
|
1184 |
|
|
(SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
|
1185 |
|
|
ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
|
1186 |
eb191b81
|
Andreas Müller
|
WHERE TaxonFk = bm_t.TaxonId
|
1187 |
|
|
AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
|
1188 |
d68b0de2
|
Andreas Müller
|
AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
|
1189 |
|
|
AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
|
1190 |
|
|
AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
|
1191 |
|
|
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_o.SpeciesExpertName,'')
|
1192 |
|
|
AND ISNULL(LastAction,'') = ISNULL(cdm_o.LastAction,'')
|
1193 |
|
|
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_o.LastActionDate,'00:00:00')
|
1194 |
|
|
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
|
1195 |
|
|
AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
|
1196 |
|
|
)
|
1197 |
eb191b81
|
Andreas Müller
|
ORDER BY bm_t.Fullname, AreaNameCache
|
1198 |
d68b0de2
|
Andreas Müller
|
*/
|
1199 |
|
|
IF @n_cdm = 0 AND @n_bm = 0 BEGIN
|
1200 |
|
|
PRINT ('All occurrence * occurrencesource results are identical in both databases')
|
1201 |
|
|
END
|
1202 |
|
|
|
1203 |
|
|
|
1204 |
|
|
PRINT ('End of check')
|
1205 |
|
|
|
1206 |
|
|
|
1207 |
|
|
END
|