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.

procedures/query/applyIDFilter.sql

@@ -55,6 +55,7 @@ IDFILTER_LABEL: BEGIN
 DECLARE data VARCHAR(20000) DEFAULT NULL;
 DECLARE aggVal VARCHAR(255) DEFAULT NULL;
 DECLARE direction CHAR(4) DEFAULT NULL;
+DECLARE entity_id_type VARCHAR(255) DEFAULT "eids.id ";
 
 #-- get aggVal if possible
 IF agg IS NOT NULL THEN
@@ -82,6 +83,10 @@ IF agg IS NOT NULL THEN
     SET aggVal = @sAggVal;
 END IF;
 
+IF o = ">" OR o = ">=" OR o = "<" or o = "<=" THEN
+    SET entity_id_type = "CAST(eids.id AS UNSIGNED INT) ";
+END IF;
+
 #-- generate stmt string
 IF targetSet IS NULL OR targetSet = sourceSet THEN
     SET data = CONCAT(
@@ -90,7 +95,8 @@ IF targetSet IS NULL OR targetSet = sourceSet THEN
         "` WHERE ",
         IF(o IS NULL OR EntityID IS NULL,
             "1=1",
-            CONCAT("NOT EXISTS (SELECT 1 FROM entity_ids AS eids WHERE eids.id ",
+            CONCAT("NOT EXISTS (SELECT 1 FROM entity_ids AS eids WHERE ",
+                entity_id_type,
                 o,
                 ' "',
                 EntityID,
@@ -111,7 +117,7 @@ ELSEIF versioned AND sourceSet = "entities" THEN
         '` (id, _iversion) SELECT e.id, _get_head_iversion(e.id) FROM `entities` AS e JOIN entity_ids AS eids ON (e.id = eids.internal_id) WHERE ',
         IF(o IS NULL OR EntityID IS NULL,
             "1=1",
-            CONCAT("eids.id ",
+            CONCAT(entity_id_type,
                 o,
                 ' "',
                 EntityID,
@@ -124,7 +130,7 @@ ELSEIF versioned AND sourceSet = "entities" THEN
         ' UNION SELECT e.id, _iversion FROM `archive_entities` AS e JOIN entity_ids AS eids ON (e.id = eids.internal_id) WHERE ',
         IF(o IS NULL OR EntityID IS NULL,
             "1=1",
-            CONCAT("eids.id ",
+            CONCAT(entity_id_type,
                 o,
                 ' "',
                 EntityID,
@@ -147,7 +153,7 @@ ELSE
         "` AS data JOIN entity_ids AS eids ON (eids.internal_id = data.id) WHERE ",
         IF(o IS NULL OR EntityID IS NULL,
             "1=1",
-            CONCAT("eids.id",
+            CONCAT(entity_id_type,
                 o,
                 ' "',
                 EntityID,