cloudfile/sql/view.sql

-- 创建视图
DROP VIEW  IF EXISTS V_DOC_DIR;
CREATE VIEW V_DOC_DIR AS
SELECT DIR_ID,
       SPACE_ID,
       DIR_TYPE,
       DIR_ROLE,
       DIR_NAME,
       PARENT_ID,
       IS_ENCRYPT,
       ENCRYPT_LEVEL,
       ACCESS_CODE,
       CREATE_BY,
       CREATE_TIME,
       UPDATE_BY,
       UPDATE_TIME,
       REMARK,
       (WITH RECURSIVE VDIR AS (SELECT DIR_ID, DIR_NAME, PARENT_ID, CAST(DIR_NAME AS CHAR(4000)) AS DPATH
                                FROM DOC_DIR
                                WHERE DIR_ID = A.DIR_ID
                                UNION ALL
                                SELECT C.DIR_ID, C.DIR_NAME, C.PARENT_ID, CONCAT(C.DIR_NAME, '/', P.DPATH)ATH
                                FROM DOC_DIR C
                                         JOIN VDIR P ON C.DIR_ID = P.PARENT_ID)
        SELECT DPATH
        FROM VDIR
        WHERE PARENT_ID = 0) AS DIR_PATH
FROM DOC_DIR A