Revert "[4.0] First draft at foreign keys in Joomla CMS" #1100
Description
New language relevant PR in upstream repo: joomla/joomla-cms#29275 Here are the upstream changes:
diff --git a/administrator/components/com_admin/sql/updates/mysql/4.0.0-2020-04-08.sql b/administrator/components/com_admin/sql/updates/mysql/4.0.0-2020-04-08.sql
deleted file mode 100644
index 41968cbf906f..000000000000
--- a/administrator/components/com_admin/sql/updates/mysql/4.0.0-2020-04-08.sql
+++ /dev/null
@@ -1,8 +0,0 @@
---
--- Change the column type before adding the foreign keys. We're adding the unsigned attribute to users and changing
--- the user_id column in user group mapping from 10 to 11 length.
---
-ALTER TABLE `#__users` MODIFY `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
-ALTER TABLE `#__user_usergroup_map` MODIFY `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
-ALTER TABLE `#__user_usergroup_map` ADD CONSTRAINT `fk_user_group_user_id` FOREIGN KEY (`user_id`) REFERENCES `#__users` (`id`) ON DELETE CASCADE;
-ALTER TABLE `#__user_usergroup_map` ADD CONSTRAINT `fk_user_group_group_id` FOREIGN KEY (`group_id`) REFERENCES `#__usergroups` (`id`) ON DELETE CASCADE;
diff --git a/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2020-04-08.sql b/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2020-04-08.sql
deleted file mode 100644
index 15c8fdfd77c4..000000000000
--- a/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2020-04-08.sql
+++ /dev/null
@@ -1,10 +0,0 @@
---
--- Change the column types before adding foreign keys
---
-ALTER TABLE "#__user_usergroup_map" ALTER COLUMN "user_id" TYPE integer;
-ALTER TABLE "#__user_usergroup_map" ALTER COLUMN "group_id" TYPE integer;
-ALTER TABLE "#__user_usergroup_map" ALTER COLUMN "user_id" DROP DEFAULT;
-ALTER TABLE "#__user_usergroup_map" ALTER COLUMN "group_id" DROP DEFAULT;
-
-ALTER TABLE `#__user_usergroup_map` ADD CONSTRAINT "fk_user_group_user_id" FOREIGN KEY ("user_id") REFERENCES "#__users" ("id") ON DELETE CASCADE;
-ALTER TABLE `#__user_usergroup_map` ADD CONSTRAINT "fk_user_group_group_id" FOREIGN KEY ("group_id") REFERENCES "#__usergroups" ("id") ON DELETE CASCADE;
diff --git a/administrator/language/en-GB/com_installer.ini b/administrator/language/en-GB/com_installer.ini
index 5826f38f92e9..531a1018306f 100644
--- a/administrator/language/en-GB/com_installer.ini
+++ b/administrator/language/en-GB/com_installer.ini
@@ -107,7 +107,6 @@ COM_INSTALLER_MINIMUM_STABILITY_RC="Release Candidate"
COM_INSTALLER_MINIMUM_STABILITY_STABLE="Stable"
COM_INSTALLER_MSG_DATABASE="This screen allows to you check that your database table structure is up to date with changes from the previous versions."
COM_INSTALLER_MSG_DATABASE_ADD_COLUMN="Table %2$s does not have column %3$s. (From file %1$s.)"
-COM_INSTALLER_MSG_DATABASE_ADD_FOREIGN_KEY="Table %2$s does not have foreign key %3$s. (From file %1$s.)"
COM_INSTALLER_MSG_DATABASE_ADD_INDEX="Table %2$s does not have index %3$s. (From file %1$s.)"
COM_INSTALLER_MSG_DATABASE_CHANGE_COLUMN_TYPE="Table %2$s has the wrong type or attributes for column %3$s with type %4$s. (From file %1$s.)"
COM_INSTALLER_MSG_DATABASE_CHECKED_OK="%s database changes were checked."
diff --git a/installation/sql/mysql/base.sql b/installation/sql/mysql/base.sql
index dfc26827eb62..0e03fc63620f 100644
--- a/installation/sql/mysql/base.sql
+++ b/installation/sql/mysql/base.sql
@@ -913,7 +913,7 @@ INSERT INTO `#__usergroups` (`id`, `parent_id`, `lft`, `rgt`, `title`) VALUES
--
CREATE TABLE IF NOT EXISTS `#__users` (
- `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(400) NOT NULL DEFAULT '',
`username` varchar(150) NOT NULL DEFAULT '',
`email` varchar(100) NOT NULL DEFAULT '',
@@ -1002,11 +1002,9 @@ CREATE TABLE IF NOT EXISTS `#__user_profiles` (
--
CREATE TABLE IF NOT EXISTS `#__user_usergroup_map` (
- `user_id` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'Foreign Key to #__users.id',
+ `user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Foreign Key to #__users.id',
`group_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Foreign Key to #__usergroups.id',
- PRIMARY KEY (`user_id`,`group_id`),
- CONSTRAINT `fk_user_group_user_id` FOREIGN KEY (`user_id`) REFERENCES `#__users` (`id`) ON DELETE CASCADE,
- CONSTRAINT `fk_user_group_group_id` FOREIGN KEY (`group_id`) REFERENCES `#__usergroups` (`id`) ON DELETE CASCADE
+ PRIMARY KEY (`user_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
diff --git a/installation/sql/postgresql/base.sql b/installation/sql/postgresql/base.sql
index 719edc15d58b..fc525821c9e5 100644
--- a/installation/sql/postgresql/base.sql
+++ b/installation/sql/postgresql/base.sql
@@ -1024,11 +1024,9 @@ COMMENT ON TABLE "#__user_profiles" IS 'Simple user profile storage table';
--
CREATE TABLE IF NOT EXISTS "#__user_usergroup_map" (
- "user_id" integer NOT NULL,
- "group_id" integer NOT NULL,
- PRIMARY KEY ("user_id", "group_id"),
- CONSTRAINT "fk_user_group_user_id" FOREIGN KEY ("user_id") REFERENCES "#__users" ("id") ON DELETE CASCADE,
- CONSTRAINT "fk_user_group_group_id" FOREIGN KEY ("group_id") REFERENCES "#__usergroups" ("id") ON DELETE CASCADE
+ "user_id" bigint DEFAULT 0 NOT NULL,
+ "group_id" bigint DEFAULT 0 NOT NULL,
+ PRIMARY KEY ("user_id", "group_id")
);
COMMENT ON COLUMN "#__user_usergroup_map"."user_id" IS 'Foreign Key to #__users.id';
diff --git a/libraries/src/Schema/ChangeItem/MysqlChangeItem.php b/libraries/src/Schema/ChangeItem/MysqlChangeItem.php
index cd05f670a015..d786463bfb0a 100644
--- a/libraries/src/Schema/ChangeItem/MysqlChangeItem.php
+++ b/libraries/src/Schema/ChangeItem/MysqlChangeItem.php
@@ -10,7 +10,6 @@
\defined('JPATH_PLATFORM') or die;
-use Joomla\CMS\Factory;
use Joomla\CMS\Schema\ChangeItem;
/**
@@ -133,27 +132,6 @@ protected function buildCheckQuery()
$this->checkQueryExpected = 0;
$this->msgElements = array($this->fixQuote($wordArray[2]), $index);
}
- elseif ($alterCommand === 'ADD CONSTRAINT')
- {
- $constraintType = strtoupper($wordArray[6] . ' ' . $wordArray[7]);
- $constraintName = $this->fixQuote($wordArray[5]);
-
- if (strpos($constraintType, 'FOREIGN KEY') !== false)
- {
- $dbName = Factory::getApplication()->get('db');
-
- /**
- * TODO: We should improve this to check what column/tables names are in the constraint
- * and which are referred to
- */
- $result = 'SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = ' .
- $this->db->quote($dbName) . ' AND CONSTRAINT_NAME =' . $constraintName;
-
- $this->queryType = 'ADD_FOREIGN_KEY';
- $this->checkQueryExpected = 1;
- $this->msgElements = array($this->fixQuote($wordArray[2]), $constraintName);
- }
- }
elseif (strtoupper($wordArray[3]) === 'MODIFY')
{
// Kludge to fix problem with "integer unsigned"
diff --git a/libraries/src/Schema/ChangeItem/PostgresqlChangeItem.php b/libraries/src/Schema/ChangeItem/PostgresqlChangeItem.php
index c018a9ec149f..2c19f69fcc72 100644
--- a/libraries/src/Schema/ChangeItem/PostgresqlChangeItem.php
+++ b/libraries/src/Schema/ChangeItem/PostgresqlChangeItem.php
@@ -10,7 +10,6 @@
\defined('JPATH_PLATFORM') or die;
-use Joomla\CMS\Factory;
use Joomla\CMS\Schema\ChangeItem;
/**
@@ -116,32 +115,6 @@ protected function buildCheckQuery()
$this->fixQuote($wordArray[5])
);
}
- elseif ($alterCommand === 'ADD CONSTRAINT')
- {
- $constraintType = strtoupper($wordArray[6] . ' ' . $wordArray[7]);
- $constraintName = $this->fixQuote($wordArray[5]);
-
- if (strpos($constraintType, 'FOREIGN KEY') !== false)
- {
- /**
- * TODO: We should improve this to check what column/tables names are in the constraint
- * and which are referred to. In postgres constraint name does not have to be unique
- */
- $result = 'SELECT tc.*'
- . ' FROM information_schema.table_constraints AS tc'
- . ' JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name ='
- . ' kcu.constraint_name AND tc.table_schema = kcu.table_schema'
- . ' JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name ='
- . ' tc.constraint_name AND ccu.table_schema = tc.table_schema'
- . ' WHERE tc.constraint_type=\'FOREIGN KEY\''
- . ' AND tc.constraint_name=' . $constraintName;
- $this->db->setQuery($result);
- $this->checkQueryExpected = 1;
-
- $this->queryType = 'ADD_FOREIGN_KEY';
- $this->msgElements = array($this->fixQuote($wordArray[2]), $constraintName);
- }
- }
elseif ($alterCommand === 'ALTER COLUMN')
{
$alterAction = strtoupper($wordArray[6]);