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-5.0.1/create_entity_ids_table.sql

+-- a little bit of house keeping
+DROP PROCEDURE IF EXISTS retrieveSubEntity;
+DROP PROCEDURE IF EXISTS retrieveDatatype;
+DROP PROCEDURE IF EXISTS retrieveGroup;
+
+-- new entity_ids table
+DROP TABLE IF EXISTS `entity_ids`;
+CREATE TABLE `entity_ids` (
+    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+    `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;
+
+-- 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;
+INSERT INTO entity_ids (id, internal_id) SELECT id, id FROM entities WHERE id=0;
+UPDATE entity_ids SET id = internal_id;
+-- ALTER TABLE entity_ids CHANGE id id int(10) unsigned NOT NULL AUTO_INCREMENT  COMMENT 'External ID of an entity. This is the id of an entity which is exposed via the CaosDB API.';