-- 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.';