External String IDs

Summary

Back-end implementation for caosdb-server!78 (merged)

Focus

• Externally used entities ids and internal ids are mapped in the new entity_ids table.
• Internal_ids are beeing generatated by the auto_increment feature of the entities table.
• Most of the changes have the pattern: Replace any occurrence of SELECT ... FROM entities WHERE id = ? with SELECT ... FROM entities JOIN entity_ids ON entities.id = entity_ids.internal_id WHERE entity_ids.id = ?
• Some of the procedure now take a EntityID VARCHAR(255) instead of ÈntityID UNSIGNED INT. These belong to the public api which is used by the server. The remaining procedure where the parameter is names InternalEntityID UNSIGNED INT now, are part of the implementation.
• There should be only one case where internal ids are being leaked into the public api, that is as replacement ids. In that case the internal ids are then being replaced by the server during the transformation from the flat property internal representation to the deep public one.
• Some of the new procedures are just old code from the server, copied and refactored.
• Also, some unrelated refactoring and clean-up was done.

Test Environment

No manual testing.

Check List for the Author

Please, prepare your MR for a review. Be sure to write a summary and a focus and create gitlab comments for the reviewer. They should guide the reviewer through the changes, explain your changes and also point out open questions. For further good practices have a look at our review guidelines

• All automated tests pass
• Reference related issues
• Up-to-date CHANGELOG.md (or not necessary)
• Annotations in code (Gitlab comments)
  • Intent of new code
  • Problems with old code
  • Why this implementation?

Check List for the Reviewer

• I understand the intent of this MR
• All automated tests pass
• Up-to-date CHANGELOG.md (or not necessary)
• The test environment setup works and the intended behavior is reproducible in the test environment
• In-code documentation and comments are up-to-date.
• Check: Are there specifications? Are they satisfied?

For further good practices have a look at our review guidelines.

patches/patch20221122-6.0-SNAPSHOT/create_entity_ids_table.sql

@@ -6,6 +6,8 @@ DROP PROCEDURE IF EXISTS getInfo;
 DROP PROCEDURE IF EXISTS getRole;
 DROP PROCEDURE IF EXISTS setPassword;
 DROP PROCEDURE IF EXISTS initAutoIncrement;
+DELETE FROM name_data WHERE entity_id=50;
+DELETE FROM entities WHERE id=50;
 DELETE FROM entities WHERE id=99;
 
 
@@ -16,13 +18,13 @@ CREATE TABLE `entity_ids` (
     `internal_id` int(10) unsigned NOT NULL COMMENT 'Internal ID of an entity. This id is used internally in the *_data tables and elsewhere. This ID is never exposed via the CaosDB API.',
     PRIMARY KEY `entity_ids_pk` (`id`),
     CONSTRAINT `entity_ids_internal_id` FOREIGN KEY (`internal_id`) REFERENCES `entities` (`id`)
-) ENGINE=InnoDB;
+) ENGINE=InnoDB COLLATE utf8mb4_bin;
 
 -- fill all existing entities into the new entity_ids table.
-INSERT INTO entity_ids (id, internal_id) SELECT id, id FROM entities WHERE id>0 AND role!="DOMAIN" AND id!=50;
+INSERT INTO entity_ids (id, internal_id) SELECT id, id FROM entities WHERE id>0 AND role!="DOMAIN";
 
-ALTER TABLE transaction_log MODIFY COLUMN `entity_id` VARCHAR(255) NOT NULL;
+ALTER TABLE transaction_log MODIFY COLUMN `entity_id` VARCHAR(255) COLLATE utf8mb4_bin NOT NULL;
 
 ALTER TABLE user_info DROP CONSTRAINT `subjects_ibfk_1`;
-ALTER TABle user_info MODIFY COLUMN `entity` VARCHAR(255) DEFAULT NULL;
+ALTER TABle user_info MODIFY COLUMN `entity` VARCHAR(255) COLLATE utf8mb4_bin DEFAULT NULL;
 ALTER TABLE user_info ADD CONSTRAINT `subjects_ibfk_2` FOREIGN KEY (`entity`) REFERENCES `entity_ids` (`id`);