USE testdb;

DROP TABLE IF EXISTS ANY_TABLE;
CREATE TABLE IF NOT EXISTS ANY_TABLE (
#OWNER_UID   BIGINT NOT NULL,
ENTITY_UID    BIGINT NOT NULL,
ENTITY_NAME   VARCHAR(40),
PRIMARY KEY (ENTITY_UID),
#KEY (OWNER_UID),
KEY (ENTITY_NAME)
);


DROP TABLE IF EXISTS SECURITY_PATH;
CREATE TABLE IF NOT EXISTS SECURITY_PATH(
origid BIGINT UNSIGNED NOT NULL, 
destid BIGINT UNSIGNED NOT NULL, 

weight DOUBLE NOT NULL, 
PRIMARY KEY (origid, destid), 
KEY (destid)
);

DROP VIEW IF EXISTS ENTITY_ACCESS;
CREATE VIEW IF NOT EXISTS ENTITY_ACCESS (
ENTITY_UID,
OWNER_UID 
) AS
SELECT SP1.origid,
       SP6.destid
FROM SECURITY_PATH SP1 
JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid 
JOIN SECURITY_PATH SP3 ON SP2.destid = SP3.origid
JOIN SECURITY_PATH SP4 ON SP3.destid = SP4.origid
JOIN SECURITY_PATH SP5 ON SP4.destid = SP5.origid  
JOIN SECURITY_PATH SP6 ON SP5.destid = SP6.origid 
;

DELIMITER //


#######################################################################
# SP_EXAMPLE_SELECT
#
#######################################################################
//
DROP PROCEDURE IF EXISTS SP_EXAMPLE_SELECT//

CREATE PROCEDURE IF NOT EXISTS SP_EXAMPLE_SELECT ( 
IN  V_SOURCE_UID                     BIGINT,
IN  V_ENTITY_UID                     BIGINT,
OUT V_ENTITY_NAME                    VARCHAR(40),
OUT V_STATUS                         INTEGER )
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
   
   DECLARE V_SESSION_USER      VARCHAR(100);
   DECLARE V_SQLSTATE          INTEGER;
   DECLARE V_ERRORNO           INTEGER;
   DECLARE V_MSG_TEXT          VARCHAR(200);
   DECLARE V_PROC_NAME         VARCHAR(40);
   DECLARE V_NUMBER            INTEGER;
   
   DECLARE CONTINUE HANDLER
      FOR 1146
   BEGIN
      SET V_STATUS = 1;
   END;

   DECLARE CONTINUE HANDLER
      FOR NOT FOUND
   BEGIN
      SET V_STATUS = 2;
   END;

   SET V_STATUS = 0;
   
   
   SELECT ENTITY_NAME
   INTO V_ENTITY_NAME
   FROM ANY_TABLE AT1
   WHERE AT1.ENTITY_UID = V_ENTITY_UID
   AND   EXISTS ( SELECT *
                  FROM SECURITY_PATH SP1   
                  JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid     
                  JOIN SECURITY_PATH SP3 ON SP2.destid = SP3.origid     
                  JOIN SECURITY_PATH SP4 ON SP3.destid = SP4.origid      
                  JOIN SECURITY_PATH SP5 ON SP4.destid = SP5.origid     
                  JOIN SECURITY_PATH SP6 ON SP5.destid = SP6.origid
                  WHERE AT1.ENTITY_UID = SP1.origid
                  AND   SP6.destid = V_SOURCE_UID );
   
END
//
#######################################################################
# SP_EXAMPLE_SELECT2
#
#######################################################################
//
DROP PROCEDURE IF EXISTS SP_EXAMPLE_SELECT2//

CREATE PROCEDURE IF NOT EXISTS SP_EXAMPLE_SELECT2 ( 
IN  V_SOURCE_UID                     BIGINT,
IN  V_ENTITY_UID                     BIGINT,
OUT V_ENTITY_NAME                    VARCHAR(40),
OUT V_STATUS                         INTEGER )
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
   
   DECLARE V_SESSION_USER      VARCHAR(100);
   DECLARE V_SQLSTATE          INTEGER;
   DECLARE V_ERRORNO           INTEGER;
   DECLARE V_MSG_TEXT          VARCHAR(200);
   DECLARE V_PROC_NAME         VARCHAR(40);
   DECLARE V_NUMBER            INTEGER;
   
   DECLARE CONTINUE HANDLER
      FOR 1146
   BEGIN
      SET V_STATUS = 1;
   END;

   DECLARE CONTINUE HANDLER
      FOR NOT FOUND
   BEGIN
      SET V_STATUS = 2;
   END;

   SET V_STATUS = 0;
   
   
   SELECT ENTITY_NAME
   INTO V_ENTITY_NAME
   FROM ANY_TABLE AT1
   WHERE AT1.ENTITY_UID = V_ENTITY_UID
   AND   EXISTS ( SELECT *
                  FROM ENTITY_ACCESS EA
                  WHERE AT1.ENTITY_UID = EA.ENTITY_UID
                  AND   EA.OWNER_UID   = V_SOURCE_UID );
   
END
//

DELIMITER ;


SELECT AT1.ENTITY_UID, SP6.destid
FROM SECURITY_PATH SP1   
JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid     
JOIN SECURITY_PATH SP3 ON SP2.destid = SP3.origid     
JOIN SECURITY_PATH SP4 ON SP3.destid = SP4.origid      
JOIN SECURITY_PATH SP5 ON SP4.destid = SP5.origid     
JOIN SECURITY_PATH SP6 ON SP5.destid = SP6.origid
JOIN ANY_TABLE AT1 ON AT1.ENTITY_UID = SP1.origid
#AND   SP6.destid = V_SOURCE_UID
ORDER BY SP6.destid
;

SET @V_SOURCE_UID = 75073;
SET @V_ENTITY_UID = 86767;
CALL SP_EXAMPLE_SELECT ( 
@V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS );

SET @V_SOURCE_UID = 23;
SET @V_ENTITY_UID = 75003;
CALL SP_EXAMPLE_SELECT ( 
@V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS );
SELECT @V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS;

SET @V_SOURCE_UID = 75073;
SET @V_ENTITY_UID = 78195;
CALL SP_EXAMPLE_SELECT ( 
@V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS );

SELECT @V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS;

SET @V_SOURCE_UID = 23;
SET @V_ENTITY_UID = 75003;
CALL SP_EXAMPLE_SELECT2 ( 
@V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS );
SELECT @V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS;

SET @V_SOURCE_UID = 75073;
SET @V_ENTITY_UID = 78195;
CALL SP_EXAMPLE_SELECT2 ( 
@V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS );

SELECT @V_SOURCE_UID,
@V_ENTITY_UID,
@V_ENTITY_NAME,
@V_STATUS;