Pull Request for Issue # .

Summary of Changes

Allow to use "varchar" as synonym for "character varying" in update SQL scripts for PostgreSQL like it is already used in the installation SQL scripts.

Currently we use "varchar" as data type in our installation SQL scripts for PostgreSQL, which is a synonym for "character varying".

But in the update SQL scripts for PostgreSQL we have to use "character varying", otherwise the database checker (System - Information - Database) detects (false) errors which can't be fixed.

The reason is that in the information schema this data type is saved as "character varying".

This has been a source of mistakes in past many times and has required critical fixes on old update SQL scripts for production releases even, because contributors creating new database tables except that they can use the same SQL as in the installation script, but that's not the case.

This Pull Request (PR) here fixes that by allowing both synonyms in update SQL scripts (but of course still querying the information schema for "character varying" in both cases).

This makes it possible to use the same SQL for creating tables in the installation and the update SQL scripts for PostgreSQL and so increase pretty much maintainability of the update scripts. Less secret knowledge needed for that with this PR.

Testing Instructions

Requirements

The test requires an installation of a current 4.0-dev branch or a latest nightly build or a 4.0 Beta 1 using a PostgreSQL database.

There is no need to make a new installation, you can use an existing one.

Testing Procedure

1. In backend, go to "System - Information - Database" and verify that there are no database errors shown for the CMS.

2. Find some update SQL script for PostgreSQL which contains some "CREATE TABLE" or "ALTER TABLE" statement containing a column with data type "character varying", and change this to "varchar" (Important: Type names always lowercase for PostgreSQL!!!).
   For example in file https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2020-05-21.sql#L5 , change

ALTER TABLE "#__history" ALTER COLUMN "item_id" TYPE character varying(50);

to

ALTER TABLE "#__history" ALTER COLUMN "item_id" TYPE varchar(50);

3. Go to "System - Information - Database" or refresh the page if still there.

Result: There is one problem shown for the CMS.

4. Move the mouse over the badge for the problem.

Result: A popup shows details. There is one error shown for the column which has been modified in step 2 in the update SQL script.

5. Select the check box for the row with the CMS error and use the "Update Structure" button.

Result: No change, the error can't be fixed.

6. Apply the patch of this PR.

7. Go again to "System - Information - Database" or refresh the page if still there.

Result: No database problem found, all ok.

8. Using a tool like e.g. phpPgAdmin, modify the definition of the column which has been modified in step 2 in the update SQL script, e.g. change the length to a smaller value:
   

9. Go again to "System - Information - Database" or refresh the page if still there.

Result: One database problem found, the details in the popup show it is related to the type of the previously modified column.

8. Select the check box for the row with the CMS error and use the "Update Structure" button.

Result: No database problem found, all ok.

Expected result

Like in the installation SQL scripts for PostgreSQL it is possible to use "varchar" as synonym for "character varying" in the update SQL scripts for that database type.

No false database errors which can't be fixed are detected by the database schema checker when using "varchar" as data type for a column.

Real database errors for such columns are detected and can be fixed by using the "Update Structure" button.

Actual result

When using "varchar" as synonym for "character varying" in the update SQL scripts for PostgreSQL like it is used in the installation SQL scripts for that database type, a false error is detected by the database schema checker, and the error can't be fixed by using the "Update Structure" button.

Documentation Changes Required

None.