Technical upgrade guide: Elements v6.4 Reporting Database changes

With the release of Elements v6.4, as a part of improving the performance of Elements, the way related journal information is represented for publications in the Reporting Database has been changed.

Note: These changes were announced in advance as Upcoming Changes to Journal-related Columns in Publication Tables in v6.4

As with all other correspondences between data entities in Elements, correspondences between publications and journals are, and always have been, maintained at the publication level. This work reinforces that approach by removing columns describing additional competing references to journals from the various source-specific metadata records within a publication, simplifying the product.

This has been prompted by the need to make some under the bonnet refactoring of the way journals are used in the system easier to implement.

The following Elements-calculated columns have been removed from the [Publication Record] table:

• [Journal ID] - the ID of the Elements-determined journal object relating to the metadata record

• [Canonical Journal Title] - the title of the journal with the above ID

• [Journal Data Source] - the journal data source whose title variant was copied into the [Canonical Journal Title] column

• [Sherpa Romeo Data] - the SHERPA/RoMEO JSON data relating to the above journal

Please note that the [journal] and [issn] source metadata columns have not been removed. These will continue to reflect the name of the journal and the ISSN of the related journal, as provided by the relevant publication data source.

The following Elements-calculated columns have been removed from the [Publication] and [Publication (Display Names)] tables:

• [Journal Data Source] - the journal data source whose title variant was copied into the [Canonical Journal Title] column

• [Sherpa Romeo Data] - the SHERPA/RoMEO JSON data relating to the above journal

Please note that the [Journal ID] and [Canonical Journal Title] calculated columns have not been removed from the [Publication] or [Publication (Display Names)] table.

The [Sherpa Romeo Data] column value has been moved to a new column of the same name in the [Journal] table. You can therefore still find the SHERPA/RoMEO data of your publication by looking for this data on the related journal object. You can do this by joining to the [Journal] table's ID column using the [Publication].[Journal ID] value.

Please note that the "(cropped)" columns for the above-mentioned (where available) are also removed.

If your scripts make use of the [Journal ID] or [Canonical Journal Title] columns of the [Publication Record] table, you will have created SQL queries prior to v6.4 which look like the following:

SELECT  pr.ID, pr.[Publication ID], pr.[Journal ID], pr.[Canonical Journal Title]
FROM  [Publication Record] AS pr
WHERE  pr.[Publication ID] = 3000

To retrieve similar information from v6.4 onwards, join to the parent [Publication] table to select related journal information from there.

SELECT  pr.ID, pr.[Publication ID], p.[Journal ID], p.[Canonical Journal Title]
FROM  [Publication Record] AS pr
    JOIN [Publication] p ON p.ID = pr.[Publication ID]
WHERE  pr.[Publication ID] = 3000

If your scripts make use of the [Journal Data Source] or [Sherpa Romeo Data] columns of the [Publication Record] table, or the columns of the same name on the [Publication] or [Publication (Display Names)] table, you will have created SQL queries prior to v6.4 which look like the following:

SELECT  pr.ID, pr.[Publication ID], pr.[Journal Data Source], pr.[Sherpa Romeo Data]
FROM  [Publication Record] AS pr
WHERE  pr.[Publication ID] = 3000

or

SELECT  p.ID, p.[Journal Data Source], p.[Sherpa Romeo Data]
FROM  [Publication] AS p
WHERE  p.ID = 3000

To find the same information on the related journal, you can join to the [Journal] table as follows. Note that before, as after, the returned [Journal Data Source] value is the name of the "highest precedence" journal record in the related journal.

SELECT	pr.ID, pr.[Publication ID], j.[Data Source] AS [Journal Data Source], j.[Sherpa Romeo Data]
FROM	[Publication Record] AS pr
		JOIN [Publication] p ON p.ID = pr.[Publication ID]
		LEFT JOIN [Journal] j ON j.ID = p.[Journal ID]
WHERE	pr.[Publication ID] = 3000

or

SELECT  p.ID, j.[Data Source] AS [Journal Data Source], j.[Sherpa Romeo Data]
FROM  [Publication] p
    LEFT JOIN [Journal] j ON j.ID = p.[Journal ID]
WHERE  p.ID = 3000

Please note that this article only covers changes to journal-related publication data in version 6.4.0. For a more complete description of changes to the Reporting Database in 6.4.0 and other versions, please see the changelog in the 6.4.0 Reporting Database guide. (Pending)