   CONSTRAINT `fd_fk_rdk` FOREIGN KEY (`relationship_definition_keyname`) REFERENCES `relationship_definition` (`keyname`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci;
 
--- Create syntax for TABLE 'entity'
+
+
+# Dump of table entity
+# ------------------------------------------------------------
+
+DROP TABLE IF EXISTS `entity`;
+
 CREATE TABLE `entity` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `entity_definition_keyname` varchar(25) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,

  KEY `deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci;

-- Create syntax for TABLE 'dag_entity'
CREATE TABLE `dag_entity` (
  `entity_id` int(11) unsigned NOT NULL DEFAULT '0',
  `related_entity_id` int(11) unsigned NOT NULL DEFAULT '0',
  `distance` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`entity_id`,`related_entity_id`),
  KEY `ed_fk_re` (`related_entity_id`),
  CONSTRAINT `de_fk_e` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ed_fk_re` FOREIGN KEY (`related_entity_id`) REFERENCES `entity` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci;

-- Create syntax for TABLE 'dag_formula'
CREATE TABLE `dag_formula` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `property_id` int(11) unsigned DEFAULT NULL,

  `picture` varchar(200) COLLATE utf8_estonian_ci DEFAULT NULL,
  `language` varchar(10) COLLATE utf8_estonian_ci DEFAULT NULL,
  `session` varchar(100) COLLATE utf8_estonian_ci DEFAULT NULL,
  `access_token` varchar(500) COLLATE utf8_estonian_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `provider` (`provider`,`provider_id`),
  KEY `session` (`session`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci;




--
-- Dumping routines (PROCEDURE) for database 'develop-mihkel'
--
DELIMITER ;;

# Dump of PROCEDURE FillDagEntity
# ------------------------------------------------------------

/*!50003 DROP PROCEDURE IF EXISTS `FillDagEntity` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`michelek`@`localhost`*/ /*!50003 PROCEDURE `FillDagEntity`()
BEGIN
  DECLARE no_more_childs, entity_id, related_entity_id, distance INT DEFAULT 0;

  DECLARE cur_childs CURSOR FOR
    SELECT de.entity_id, de2.related_entity_id AS related_entity_id, de.distance+de2.distance AS distance
    FROM dag_entity de
    LEFT JOIN dag_entity de2 ON de2.entity_id = de.related_entity_id
    WHERE de2.entity_id IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_childs = 1;

  OPEN cur_childs;
    FETCH cur_childs INTO entity_id, related_entity_id, distance;
    WHILE no_more_childs = 0 DO
      INSERT INTO dag_entity (entity_id, related_entity_id, distance) VALUES (entity_id, related_entity_id, distance)
      ON DUPLICATE KEY UPDATE dag_entity.distance=least(dag_entity.distance, distance);

      FETCH cur_childs INTO entity_id, related_entity_id, distance;
    END WHILE;

  CLOSE cur_childs;

END */;;

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;
# Dump of PROCEDURE GrantRecursiveViewer
# ------------------------------------------------------------

/*!50003 DROP PROCEDURE IF EXISTS `GrantRecursiveViewer` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`michelek`@`localhost`*/ /*!50003 PROCEDURE `GrantRecursiveViewer`(IN rootEntity_in INT(11), IN viewer_in INT(11))
BEGIN
  DECLARE no_more_childs, child_id INT DEFAULT 0;
  DECLARE viewer_rd INT;

  DECLARE cur_childs CURSOR FOR
    SELECT r.related_entity_id
    FROM relationship r
    WHERE r.entity_id = rootEntity_in AND r.relationship_definition_keyname = 'child' AND r.deleted IS NULL;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_childs = 1;

  -- Grant viewer rights
  INSERT IGNORE INTO relationship SET created=now(), created_by='GrantRecursiveViewer' ,relationship_definition_keyname = 'viewer', entity_id = rootEntity_in, related_entity_id = viewer_in
  ON DUPLICATE KEY UPDATE relationship_definition_keyname = 'viewer', entity_id = rootEntity_in, related_entity_id = viewer_in;

  OPEN cur_childs;
    FETCH cur_childs INTO child_id;
    WHILE no_more_childs = 0 DO
      CALL GrantRecursiveViewer(child_id, viewer_in);
      FETCH cur_childs INTO child_id;
    END WHILE;

  CLOSE cur_childs;

END */;;

/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;;
DELIMITER ;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
