DROP VIEW IF EXISTS V_ENTITY_HIERARCHY;
CREATE VIEW V_ENTITY_HIERARCHY
AS
    WITH RECURSIVE AllEntity
    (EntityID, BaseEntityIndex, FullName, depth) 
    AS
    (
        SELECT e.EntityID, e.EntityID BaseEntity, e.Schema || '.' || e.EntName, 0 depth
        FROM VCLib.T_ENTITIES e
        UNION ALL 
        SELECT e.EntityID, e2.BaseEntityIndex, e.Schema || '.' || e.EntName, depth + 1
        FROM VCLib.T_ENTITIES e
        inner join AllEntity e2 On e.BaseEntity = e2.EntityID
    )
    SELECT EntityID, BaseEntityIndex BaseEntity, FullName, depth
    FROM AllEntity;
