Code Insight GPL/LGPL/AGPL License Data Cleanup Project

Background

There are few licenses in Code Insight namely GPL-1.0, GPL-1.0+, GPL-2.0, GPL-2.0+, GPL-3.0, GPL-3.0+, AGPL-1.0, AGPL-3.0, LGPL-2.0, LGPL-2.0+, LGPL-2.1, LGPL-2.1+, LGPL-3.0, LGPL-3.0+.

The short-names, names and URL of the above licenses are now updated in our data library to keep the licenses in sync with the SPDX licenses.

We noticed that the license mapping is applicable for LGPL-2.1+, AGPL-1.0 and AGPL-3.0. This is being resolved via an electronic update where the mappings are corrected and for existing projects that need mappings change, a script will be provided.

Problem Details

There are three issues we are addressing as part of this GPL-LGPL-AGPL License data cleanup project:

Example: forms 7.1.3 (AGPL-3.0)

Here AGPL-3.0 is the license with the short name associated with the component forms.

1. Short Name Change

When a particular license short name is changed and released as part of an electronic update, the short name is not automatically propagated to the inventory items with that selected license. For example, when we change the short name of license id 229 from "AGPL-3.0” to “AGPL-3.0-only” in an electronic update, the existing inventory items names with that selected license will not be updated.

2. Component to License Mapping Change

When the component to license mapping is changed, for example, prototreeview 1.0.0  is mapped with "LGPL-2.1-or-later (2097)" and license id is updated to 704 in the electronic update, then this new mapping wouldn’t be propagated to existing inventory items. This results in inconsistency between the license mapping, existing inventory items, and future inventory items using the new license mapping.

3. Duplicate entry cleanup

After running the cleanup scripts, there are possibility of having duplicate entries for the licenses which had mappings in component table and versions table. In our case, we have mappings for 3 licenses, i.e LGPL-2.1-or-later(License_id=704), AGPL-1.0-only(License_id=1654) and AGPL-3.0-only(License_id=229).

Solution

1. Solution for Short Name Change

We need to update the names of existing inventory items with impacted selected licenses to include the new short name.

Example:

• Before update – forms 7.1.3 (AGPL-3.0)
• After update without solution – forms 7.1.3 (AGPL-3.0)
• After update with solution – forms 7.1.3 (AGPL-3.0-only)

2. Solution for Component to License Mapping Changes

We need to update the selected license of existing inventory items with impacted licenses as per the new component to license mappings.

Example:

• Component jquery is remapped from LGPL-2.1-or-later (ID: 2097) to LGPL-2.1-or-later (ID: 704).
• Before update – prototreeview 1.0.0 (LGPL-2.1-or-later)
• Selected License: LGPL-2.1-or-later (ID: 2097)
• Possible Licenses: LGPL-2.1-or-later (ID: 2097)
• After update without solution –prototreeview 1.0.0 (LGPL-2.1-or-later)
• Selected License: LGPL-2.1-or-later (ID: 2097)
• Possible Licenses: LGPL-2.1-or-later (ID: 704)
• After update with solution – prototreeview 1.0.0 (LGPL-2.1-or-later)
• Selected License: LGPL-2.1-or-later (ID: 704)
• Possible Licenses: LGPL-2.1-or-later (ID: 704)

3. Solution for duplicate entries

We need to update the existing inventory items with impacted selected licenses and remove the duplicate entries.

Example:

• Before update – forms 7.1.3 (AGPL-3.0)
• After running gpl-lgpl-agpl-cleanupqueries without solution

– forms 7.1.3 (AGPL-3.0)

–forms 7.1.3 (AGPL-3.0)

• After running duplicate entry script with solution –forms 7.1.3 (AGPL-3.0-only)

Solution for customers taking GPL-LGPL-AGPL PDL Update prior to Code Insight 2021 R4 release (i.e. 2021R3, 2021R2 etc.,):

• Download the gpl-lgpl-agpl-cleanupqueries package from PLC with the name – gpl-lgpl-agpl-cleanupqueries.zip. It contains duplicate-entry-script package also.
• gpl-lgpl-agpl-cleanupqueries package can be found in PLC at the location : gpl-lgpl-agpl-cleanupqueries.zip
• Immediately after running the GPL-LGPL-AGPL Electronic update, customers should run the gpl-lgpl-agpl-cleanupqueries script – “gpl-lgpl-agpl-cleanupqueries-mysql.sql” and “gpl-lgpl-agpl-cleanupqueries-sqlserver.sql”, to ensure that the latest mappings are reflected in the already scanned projects.
• Immediately after running the gpl-lgpl-agpl-cleanupqueries script, customers should run duplicate-entry-script – “gpl-lgpl-agpl-mysql-procedure.sql” and “gpl-lgpl-agpl-sqlserver-procedure.sql”. Please refer to the ‘Important Notes’ section at the bottom of this article to understand the impact, if the script is not run immediately after the electronic update is run.

Solution for customers taking GPL-LGPL-AGPL PDL Update after Code Insight 2021 R4 and later releases (2021R4, 2022R1, 2022R2 etc.,):

• No Action needed for customers who are in 2021 R4. Product solution delivered as part of 2021 R4 and later releases takes care of the remappings on the already scanned projects.
• In case of import scenario's respective to older projects which are exported before PDL Update, the customer needs to run the Script after importing the project. Steps and prerequisites are mentioned in the readme-script.txt shared as part of gpl-lgpl-agpl-cleanupqueries package.

*Tables impacted by the queries:

• PAS_REPOSITORY_ITEM
• PSE_INVENTORY_GROUPS

Solution for customers taking GPL-LGPL-AGPL PDL Update in Code Insight v6:

• No action required for customers using Code Insight v6. A solution was delivered as part of an electronic update. This solution contains a groovy script that executes the required queries to handle the re-mapping of already scanned projects.
• In case of import scenario's respective to older projects which are exported before PDL Update, the customer needs to run the script after importing the project.

*Tables impacted by the queries:

• PAS_REQUEST_INSTANCE
• PAS_POLICY
• PSE_GROUPS
• PSE_GROUP_LICENSES

ACTION REQUIRED:

For customers taking the electronic update with the GPL-LGPL-AGPL License data cleanup after installing the Code Insight 2021 R4 (or any later) release:

• Step 1: Take a complete old database backup.
• Step 2: Apply the electronic update with the GPL-LGPL-AGPL License data cleanup.
• No further action is needed.

For customers taking the electronic update with the GPL-LGPL-AGPL License data cleanup before installing the Code Insight 2021 R4 (or any previous) release:

Step 1: Take a complete old database backup.

• Step 2: Apply the electronic update with the GPL-LGPL-AGPL License data cleanup.
• Step 3: Immediately after the electronic update completes, and before any other operations are performed (scan, import, etc.), run the provided SQL script (gpl-lgpl-agpl-cleanupqueries and duplicate-entry-script).

For customers taking the electronic update with the GPL-LGPL-AGPL License data cleanup in Code Insight v6:

• Step 1: Take a complete backup of the database before applying electronic update.
• Step 2: Apply the electronic update with the GPL-LGPL-AGPL License data cleanup.
• No further action is needed.

Project Import Scenarios in Code Insight v7:

To import the old project data (exported before the GPL-LGPL-AGPL License data cleanup electronic update was processed), into a project after the GPL-LGPL-AGPL License data cleanup electronic update was run, follow the steps below to avoid inconsistencies in the project inventories:

• Step 1: Import the old project export JSON file into the target project.
• Step 2: Run the provided SQL script and Duplicate entry script.
• Step 3: Select "On data import or rescan, delete inventory with no associated files" option from Summary Screen -> Manage Project -> Edit Project -> Under General Tab.
• Step 4: Upload the project codebase and schedule the scan.

Project Import Scenarios in Code Insight v6:

To import the old project data (exported before the GPL-LGPL-AGPL License data cleanup electronic update was processed), into a project after the GPL-LGPL-AGPL License data cleanup electronic update was run, follow the steps below to avoid inconsistencies in the project inventories:

• Step 1: Import the old project export XML file into the target project.
• Step 2: Run the SQL script present in the electronic update package:
  <CodeInsight_InstallFolder>/tomcat/temp/palamida_update/scripts/sql
  (In case the palamida_update folder is cleaned up in the above-mentioned location, please download the scripts from PLC.)
  
  For MySQL, execute gpl-lgpl-agpl-mysql-script.sql
  For Oracle, execute gpl-lgpl-agpl-oracle-script.sql
  For SqlServer, execute gpl-lgpl-agpl-sqlserver-script.sql

NOTE: Projects which are exported after the GPL-LGPL-AGPL License data cleanup electronic update do not require the SQL script to be run.

IMPORTANT NOTES:

Users must run the script “gpl-lgpl-agpl-cleanupqueries” after the PDL update is run and before initiating any scans. In case any scans are triggered before running the “gpl-lgpl-agpl-cleanupqueries” script on the database, the below issues arise and are explained in detail. Also, the issues do not impact any manual inventory created or any inventory created by scan and updated by users.

1. Short Name change per the electronic update is not reflected for existing inventory items. Meaning, instead of "forms 7.1.3 (AGPL-3.0-only)", inventory name is still retained as "forms 7.1.3 (AGPL-3.0)".
2. Component-License remapping would not be performed on existing inventories from license ID 2097 (LGPL-2.1-or-later) to license ID 704 (LGPL-2.1+)
3. Duplicate entries will remain as it is.

Example: forms 7.1.3 (AGPL-3.0-only)(ID: 229)

If we perform a full rescan of the project, then we may end with duplicate inventory items with the two license short name variants:

forms 7.1.3 (AGPL-3.0) – old inventory item

forms 7.1.3 (AGPL-3.0-only) – new inventory item

Note :

• gpl-lgpl-agpl-cleanupqueries package can be found in PLC : gpl-lgpl-agpl-cleanupqueries.zip
• Download package name License Cleanup .