Revision eb191b81
Added by Andreas Müller almost 12 years ago
| cdm-pesi/src/main/resources/sql/Check_Import_Master.sql | ||
|---|---|---|
| 306 | 306 |
ISNULL(bm_t.TaxonStatusCache, '') <> ISNULL(cdm_t.TaxonStatusCache, '') |
| 307 | 307 |
*/ |
| 308 | 308 |
|
| 309 |
-- Parents |
|
| 310 |
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL |
|
| 311 |
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE ParentTaxonFk 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 parent = ' + @str_n_bm)
|
|
| 320 |
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)
|
|
| 321 |
|
|
| 322 |
SELECT @n = COUNT(*) -- in both databases parents 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.ParentTaxonFk = bm_pt.TaxonId INNER JOIN |
|
| 327 |
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = 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 parents have the same parent')
|
|
| 332 |
END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different parents')
|
|
| 333 |
/* |
|
| 334 |
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 |
|
| 335 |
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN |
|
| 336 |
[CDM_EM2PESI].[DBO].TAXON cdm_t ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN |
|
| 337 |
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN |
|
| 338 |
[ CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId |
|
| 339 |
WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '') |
|
| 340 |
*/ |
|
| 341 |
SELECT @n_bm = COUNT(*) -- taxa with parent only in EM2PESI |
|
| 342 |
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN |
|
| 343 |
[CDM_EM2PESI].[DBO].TAXON cdm_t |
|
| 344 |
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN |
|
| 345 |
[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId |
|
| 346 |
WHERE NOT EXISTS |
|
| 347 |
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON |
|
| 348 |
WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, '')) |
|
| 349 |
SET @str_n_bm = Cast(@n_bm AS NVARCHAR) |
|
| 350 |
IF @n_bm > 0 BEGIN |
|
| 351 |
PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have parents in EM2PESI but not in CDM_EM2PESI')
|
|
| 352 |
END ELSE PRINT ('All identical taxa that have parents in EM2PESI do have parents in CDM_EM2PESI')
|
|
| 353 |
|
|
| 354 |
/* |
|
| 355 |
SELECT cdm_t.Fullname ChildName, bm_pt.Fullname as Parent_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI' |
|
| 356 |
FROM [EM2PESI].[DBO].TAXON bm_t |
|
| 357 |
INNER JOIN [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId |
|
| 358 |
INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_t ON ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') |
|
| 359 |
|
|
| 360 |
WHERE cdm_t.ParentTaxonFk IS NULL AND NOT EXISTS |
|
| 361 |
(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON cdm_pt |
|
| 362 |
WHERE (cdm_pt.IdInSource = bm_pt.IdInSource OR cdm_pt.IdInSource IS NULL AND bm_pt.IdInSource IS NULL) |
|
| 363 |
AND ISNULL(cdm_pt.GUID, '') = ISNULL(bm_pt.GUID, '') |
|
| 364 |
AND cdm_t.ParentTaxonFk = cdm_pt.TaxonId) |
|
| 365 |
ORDER BY cdm_t.Fullname |
|
| 366 |
|
|
| 367 |
*/ |
|
| 368 |
SELECT @n_cdm = COUNT(*) -- taxa with parent only in CDM_EM2PESI |
|
| 369 |
FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN |
|
| 370 |
[CDM_EM2PESI].[DBO].TAXON cdm_t |
|
| 371 |
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN |
|
| 372 |
[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId |
|
| 373 |
WHERE NOT EXISTS |
|
| 374 |
(SELECT * FROM [EM2PESI].[DBO].TAXON |
|
| 375 |
WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, '')) |
|
| 376 |
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR) |
|
| 377 |
IF @n_cdm > 0 BEGIN |
|
| 378 |
PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have parents in CDM_EM2PESI but not in EM2PESI')
|
|
| 379 |
END ELSE PRINT('All identical taxa that have parents in CDM_EM2PESI do have parents in EM2PESI')
|
|
| 380 |
|
|
| 381 |
/* |
|
| 382 |
SELECT bm_t.Fullname ChildName, cdm_pt.Fullname as Parent_CDM, 'in CDM_EM2PESI but not in EM2PESI' |
|
| 383 |
FROM [CDM_EM2PESI].[DBO].TAXON cdm_t |
|
| 384 |
INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId |
|
| 385 |
INNER JOIN [EM2PESI].[DBO].TAXON bm_t ON ISNULL(cdm_t.GUID, '') = ISNULL(bm_t.GUID, '') |
|
| 386 |
|
|
| 387 |
WHERE bm_t.ParentTaxonFk IS NULL AND NOT EXISTS |
|
| 388 |
(SELECT * FROM [EM2PESI].[DBO].TAXON bm_pt |
|
| 389 |
WHERE (bm_pt.IdInSource = cdm_pt.IdInSource OR bm_pt.IdInSource IS NULL AND cdm_pt.IdInSource IS NULL) |
|
| 390 |
AND ISNULL(bm_pt.GUID, '') = ISNULL(cdm_pt.GUID, '') |
|
| 391 |
AND bm_t.ParentTaxonFk = bm_pt.TaxonId) |
|
| 392 |
ORDER BY bm_t.Fullname |
|
| 393 |
*/ |
|
| 394 |
IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN |
|
| 395 |
PRINT ('All identical taxa have the same parent')
|
|
| 396 |
END |
|
| 397 |
|
|
| 398 |
-- TreeIndex |
|
| 399 |
/* This is not checked. This field should be created by the PESI-Procedure recalculateallstoredpaths. |
|
| 400 |
Actually checking the parents of the same taxa is enough to ensure the compatibility of the taxonomical tree as a whole. |
|
| 401 |
See the Parent section |
|
| 402 |
*/ |
|
| 403 |
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL |
|
| 404 |
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL |
|
| 405 |
SET @n = @n_bm - @n_cdm |
|
| 406 |
|
|
| 407 |
SET @str_n_bm = Cast(@n_bm AS NVARCHAR) |
|
| 408 |
SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR) |
|
| 409 |
SET @str_n = Cast(@n AS NVARCHAR) |
|
| 410 |
|
|
| 411 |
IF @n = 0 BEGIN |
|
| 412 |
PRINT ('Both databases have the same number of taxa which have a tree index = ' + @str_n_bm)
|
|
| 413 |
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)
|
|
| 414 |
|
|
| 415 |
|
|
| 416 | 309 |
-- Types |
| 417 | 310 |
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL |
| 418 | 311 |
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL |
| ... | ... | |
| 590 | 483 |
convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00')) |
| 591 | 484 |
*/ |
| 592 | 485 |
|
| 486 |
|
|
| 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 |
|
|
| 593 | 597 |
------------------------------------------- RelTaxon ------------------------------- |
| 594 | 598 |
PRINT ' ' |
| 595 | 599 |
PRINT 'RELATIONSHIP' |
| ... | ... | |
| 795 | 799 |
|
| 796 | 800 |
------------------------------------------- AdditionalTaxonSource ------------------------------- |
| 797 | 801 |
PRINT ' ' |
| 798 |
PRINT 'AdditionalTaxonSource'
|
|
| 802 |
PRINT 'ADDITIONAL TAXON SOURCE'
|
|
| 799 | 803 |
|
| 800 | 804 |
SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].AdditionalTaxonSource |
| 801 | 805 |
SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource |
| ... | ... | |
| 816 | 820 |
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') |
| 817 | 821 |
WHERE NOT EXISTS |
| 818 | 822 |
(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource |
| 819 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'') |
|
| 823 |
WHERE TaxonFk = cdm_t.TaxonId |
|
| 824 |
AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'') |
|
| 820 | 825 |
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'') |
| 821 | 826 |
AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'') |
| 822 | 827 |
) |
| ... | ... | |
| 833 | 838 |
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') |
| 834 | 839 |
WHERE NOT EXISTS |
| 835 | 840 |
(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource |
| 836 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'') |
|
| 841 |
WHERE TaxonFk = cdm_t.TaxonId |
|
| 842 |
AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'') |
|
| 837 | 843 |
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'') |
| 838 | 844 |
AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'') |
| 839 | 845 |
) |
| 846 |
ORDER BY bm_t.Fullname, bm_ats.SourceUseCache, bm_ats.SourceNameCache |
|
| 847 |
|
|
| 840 | 848 |
*/ |
| 841 | 849 |
SELECT @n_cdm = COUNT(*) -- additional sources only in CDM_EM2PESI |
| 842 | 850 |
FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource cdm_ats INNER JOIN |
| ... | ... | |
| 845 | 853 |
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') |
| 846 | 854 |
WHERE NOT EXISTS |
| 847 | 855 |
(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource |
| 848 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'') |
|
| 856 |
WHERE TaxonFk = bm_t.TaxonId |
|
| 857 |
AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'') |
|
| 849 | 858 |
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'') |
| 850 | 859 |
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'') |
| 851 | 860 |
) |
| ... | ... | |
| 862 | 871 |
ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') |
| 863 | 872 |
WHERE NOT EXISTS |
| 864 | 873 |
(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource |
| 865 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'') |
|
| 874 |
WHERE TaxonFk = bm_t.TaxonId |
|
| 875 |
AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'') |
|
| 866 | 876 |
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'') |
| 867 | 877 |
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'') |
| 868 | 878 |
) |
| 879 |
ORDER BY bm_t.Fullname, cdm_ats.SourceUseCache, cdm_ats.SourceNameCache |
|
| 869 | 880 |
*/ |
| 870 | 881 |
IF @n_cdm = 0 AND @n_bm = 0 BEGIN |
| 871 | 882 |
PRINT ('All additional sources are identical in both databases')
|
| ... | ... | |
| 970 | 981 |
WHERE NOT EXISTS |
| 971 | 982 |
(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource |
| 972 | 983 |
ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk |
| 973 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'') |
|
| 984 |
WHERE TaxonFk = cdm_t.TaxonId |
|
| 985 |
AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'') |
|
| 974 | 986 |
AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'') |
| 975 | 987 |
AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'') |
| 976 | 988 |
AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'') |
| 977 | 989 |
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'') |
| 978 | 990 |
AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'') |
| 979 |
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_n.LastActionDate,'00:00:00')
|
|
| 991 |
AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
|
|
| 980 | 992 |
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'') |
| 981 | 993 |
AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'') |
| 982 | 994 |
) |
| ... | ... | |
| 985 | 997 |
PRINT ('All note * notesource results in EM2PESI exist also in CDM_EM2PESI')
|
| 986 | 998 |
END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing note * notesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
|
| 987 | 999 |
/* |
| 1000 |
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 |
|
|
| 988 | 1005 |
SELECT bm_t.Fullname, bm_n.Note_1, bm_n.Note_2, bm_n.NoteCategoryCache, bm_n.LanguageCache, |
| 989 | 1006 |
bm_n.SpeciesExpertName, bm_n.LastAction, bm_n.LastActionDate, bm_ns.SourceNameCache, bm_ns.SourceDetail, |
| 990 | 1007 |
'in EM2PESI but not in CDM_EM2PESI' |
| ... | ... | |
| 996 | 1013 |
WHERE NOT EXISTS |
| 997 | 1014 |
(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource |
| 998 | 1015 |
ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk |
| 999 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'') |
|
| 1016 |
WHERE TaxonFk = cdm_t.TaxonId |
|
| 1017 |
AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'') |
|
| 1000 | 1018 |
AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'') |
| 1001 | 1019 |
AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'') |
| 1002 | 1020 |
AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'') |
| 1003 | 1021 |
AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'') |
| 1004 | 1022 |
AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'') |
| 1005 |
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_n.LastActionDate,'00:00:00')
|
|
| 1023 |
AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
|
|
| 1006 | 1024 |
AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'') |
| 1007 | 1025 |
AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'') |
| 1008 | 1026 |
) |
| 1027 |
ORDER BY bm_n.NoteCategoryFk, bm_t.Fullname, bm_n.Note_1 |
|
| 1009 | 1028 |
*/ |
| 1010 | 1029 |
SELECT @n_cdm = COUNT(*) -- note * notesource results only in CDM_EM2PESI |
| 1011 | 1030 |
FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN |
| ... | ... | |
| 1016 | 1035 |
WHERE NOT EXISTS |
| 1017 | 1036 |
(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource |
| 1018 | 1037 |
ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk |
| 1019 |
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'') |
|
| 1038 |
WHERE TaxonFk = bm_t.TaxonId |
|
| 1039 |
AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'') |
|
| 1020 | 1040 |
AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'') |
| 1021 | 1041 |
AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'') |
| 1022 | 1042 |
AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'') |
| 1023 | 1043 |
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'') |
| 1024 | 1044 |
AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'') |
| 1025 |
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_n.LastActionDate,'00:00:00')
|
|
| 1045 |
AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
|
|
| 1026 | 1046 |
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'') |
| 1027 | 1047 |
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'') |
| 1028 | 1048 |
) |
| ... | ... | |
| 1042 | 1062 |
WHERE NOT EXISTS |
| 1043 | 1063 |
(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource |
| 1044 | 1064 |
ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk |
| 1045 |
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'') |
|
| 1065 |
WHERE TaxonFk = bm_t.TaxonId |
|
| 1066 |
AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'') |
|
| 1046 | 1067 |
AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'') |
| 1047 | 1068 |
AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'') |
| 1048 | 1069 |
AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'') |
| 1049 | 1070 |
AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'') |
| 1050 | 1071 |
AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'') |
| 1051 |
AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_n.LastActionDate,'00:00:00')
|
|
| 1072 |
AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
|
|
| 1052 | 1073 |
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'') |
| 1053 | 1074 |
AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'') |
| 1054 | 1075 |
) |
| 1076 |
ORDER BY cdm_n.NoteCategoryFk, cdm_t.Fullname,cdm_n.Note_1 |
|
| 1077 |
|
|
| 1055 | 1078 |
*/ |
| 1056 | 1079 |
IF @n_cdm = 0 AND @n_bm = 0 BEGIN |
| 1057 | 1080 |
PRINT ('All note * notesource results are identical in both databases')
|
| ... | ... | |
| 1111 | 1134 |
WHERE NOT EXISTS |
| 1112 | 1135 |
(SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource |
| 1113 | 1136 |
ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk |
| 1114 |
WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'') |
|
| 1137 |
WHERE TaxonFk = cdm_t.TaxonId |
|
| 1138 |
AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'') |
|
| 1115 | 1139 |
AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'') |
| 1116 | 1140 |
AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'') |
| 1117 | 1141 |
AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'') |
| ... | ... | |
| 1121 | 1145 |
AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'') |
| 1122 | 1146 |
AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'') |
| 1123 | 1147 |
) |
| 1148 |
ORDER BY bm_t.Fullname, AreaNameCache |
|
| 1124 | 1149 |
*/ |
| 1125 | 1150 |
SELECT @n_cdm = COUNT(*) -- occurrence * occurrencesource results only in CDM_EM2PESI |
| 1126 | 1151 |
FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN |
| ... | ... | |
| 1147 | 1172 |
PRINT ('All occurrence * occurrencesource results in CDM_EM2PESI exist also in EM2PESI')
|
| 1148 | 1173 |
END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing occurrence * occurrencesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
|
| 1149 | 1174 |
/* |
| 1150 |
SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, bm_t.Fullname, cdm_o.TaxonFullNameCache, cdm_o.AreaNameCache, cdm_o.OccurrenceStatusCache, cdm_o.Notes,
|
|
| 1175 |
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,
|
|
| 1151 | 1176 |
cdm_o.SpeciesExpertName, cdm_o.LastAction, cdm_o.LastActionDate, cdm_os.SourceNameCache, cdm_os.OldTaxonName, |
| 1152 | 1177 |
'in CDM_EM2PESI but not in EM2PESI' |
| 1153 | 1178 |
FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN |
| ... | ... | |
| 1158 | 1183 |
WHERE NOT EXISTS |
| 1159 | 1184 |
(SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource |
| 1160 | 1185 |
ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk |
| 1161 |
WHERE TaxonFk = bm_t.TaxonId AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'') |
|
| 1186 |
WHERE TaxonFk = bm_t.TaxonId |
|
| 1187 |
AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'') |
|
| 1162 | 1188 |
AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'') |
| 1163 | 1189 |
AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'') |
| 1164 | 1190 |
AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'') |
| ... | ... | |
| 1168 | 1194 |
AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'') |
| 1169 | 1195 |
AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'') |
| 1170 | 1196 |
) |
| 1197 |
ORDER BY bm_t.Fullname, AreaNameCache |
|
| 1171 | 1198 |
*/ |
| 1172 | 1199 |
IF @n_cdm = 0 AND @n_bm = 0 BEGIN |
| 1173 | 1200 |
PRINT ('All occurrence * occurrencesource results are identical in both databases')
|
Also available in: Unified diff
update for check master