Ensure consistent database schema for migrations

[johanib]

Current situation
Currently, in devconf (core/scripts/init.sh) and in the test scripts, the database is created using doctrine:schema:create.

Also, there are migrations.

The problem is that the schema created by doctrine:schema:create, changes over time, but the migrations assume a steady base.
So currently, they are not compatible with each other.

When you first run doctrine:schema:create, and then run the migrations, the following error occurs:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'eb_test.sso_provider_roles' doesn't exist

What this means is:

• In development/pipeline, the tests are not executed on the same database schema as used in production
• Development is not done on the same schema as used in production.
• When a developer creates a migration, it is probably based on a schema that is currently not in production!

Proposed solution:
Never use doctrine:schema:create, always execute the migrations.

Changes needed
Ensure the doctrine:migrations:migrate command can be executed on an empty database.

This can be done by:

• Adding a migration at the beginning of the migration that sets the base state.
• OR
• Squash current migrations: Take the current production database schema (ensure all migrations are executed, including Version20230824090020), delete all old migrations, and create a new migration that creates the schema based on that export.

In both scenario's, the migration should not be executed on production environments, and be set to executed manually.

Related
This is not applied on production? #1262

[baszoetekouw]

For reference, here's the current database structure for prod:

CREATE DATABASE  `eb` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */;
CREATE TABLE `consent` (
  `consent_date` datetime NOT NULL,
  `hashed_user_id` varchar(80) NOT NULL,
  `service_id` varchar(255) NOT NULL,
  `attribute` varchar(80) NOT NULL,
  `consent_type` varchar(20) DEFAULT 'explicit',
  `deleted_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`hashed_user_id`,`service_id`,`deleted_at`),
  KEY `hashed_user_id` (`hashed_user_id`),
  KEY `service_id` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `migration_versions` (
  `version` varchar(255) NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE `saml_persistent_id` (
  `persistent_id` char(40) NOT NULL COMMENT 'SHA1 of service_provider_uuid + user_uuid',
  `user_uuid` char(36) NOT NULL,
  `service_provider_uuid` char(36) NOT NULL,
  PRIMARY KEY (`persistent_id`),
  KEY `user_uuid` (`user_uuid`,`service_provider_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci COMMENT='Look up table for persistent_ids we hand out';

CREATE TABLE `service_provider_uuid` (
  `uuid` char(36) NOT NULL,
  `service_provider_entity_id` varchar(1024) NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `service_provider_entity_id` (`service_provider_entity_id`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci COMMENT='Lookup table for UUIDs for Service Providers, provides a lev';

CREATE TABLE `sso_provider_roles_eb5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` varchar(255) NOT NULL,
  `name_nl` varchar(255) NOT NULL,
  `name_en` varchar(255) NOT NULL,
  `name_pt` varchar(255) NOT NULL,
  `description_nl` varchar(255) NOT NULL,
  `description_en` varchar(255) NOT NULL,
  `description_pt` varchar(255) NOT NULL,
  `display_name_nl` varchar(255) NOT NULL,
  `display_name_en` varchar(255) NOT NULL,
  `display_name_pt` varchar(255) NOT NULL,
  `logo` longtext NOT NULL COMMENT '(DC2Type:object)',
  `organization_nl_name` text DEFAULT NULL COMMENT '(DC2Type:object)',
  `organization_en_name` text DEFAULT NULL COMMENT '(DC2Type:object)',
  `organization_pt_name` text DEFAULT NULL COMMENT '(DC2Type:object)',
  `keywords_nl` varchar(255) NOT NULL,
  `keywords_en` varchar(255) NOT NULL,
  `keywords_pt` varchar(255) NOT NULL,
  `certificates` text NOT NULL COMMENT '(DC2Type:array)',
  `workflow_state` varchar(255) NOT NULL,
  `contact_persons` text NOT NULL COMMENT '(DC2Type:array)',
  `name_id_format` varchar(255) DEFAULT NULL,
  `name_id_formats` text NOT NULL COMMENT '(DC2Type:array)',
  `single_logout_service` text DEFAULT NULL COMMENT '(DC2Type:object)',
  `requests_must_be_signed` tinyint(1) NOT NULL,
  `manipulation` text NOT NULL,
  `type` varchar(255) NOT NULL,
  `attribute_release_policy` text DEFAULT NULL COMMENT '(DC2Type:array)',
  `assertion_consumer_services` text DEFAULT NULL COMMENT '(DC2Type:array)',
  `allowed_idp_entity_ids` mediumtext DEFAULT NULL COMMENT '(DC2Type:array)',
  `allow_all` tinyint(1) DEFAULT NULL,
  `requested_attributes` text DEFAULT NULL COMMENT '(DC2Type:array)',
  `enabled_in_wayf` tinyint(1) DEFAULT NULL,
  `single_sign_on_services` text DEFAULT NULL COMMENT '(DC2Type:array)',
  `shib_md_scopes` text DEFAULT NULL COMMENT '(DC2Type:array)',
  `support_url_en` varchar(255) DEFAULT NULL,
  `support_url_pt` varchar(255) DEFAULT NULL,
  `support_url_nl` varchar(255) DEFAULT NULL,
  `consent_settings` longtext DEFAULT NULL COMMENT '(DC2Type:json_array)',
  `coins` longtext NOT NULL COMMENT '(DC2Type:engineblock_metadata_coins)',
  `mdui` longtext NOT NULL COMMENT '(DC2Type:engineblock_metadata_mdui)',
  `idp_discoveries` longtext DEFAULT NULL COMMENT '(DC2Type:json)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sso_provider_roles_entity_id_type` (`type`,`entity_id`),
  KEY `idx_sso_provider_roles_type` (`type`),
  KEY `idx_sso_provider_roles_entity_id` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=62655 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE `user` (
  `collab_person_id` varchar(255) NOT NULL COMMENT '(DC2Type:engineblock_collab_person_id)',
  `uuid` char(36) NOT NULL COMMENT '(DC2Type:engineblock_collab_person_uuid)',
  PRIMARY KEY (`collab_person_id`),
  KEY `idx_user_uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;