src/AltSpider/Resources/shares.sql

   1 \r
   2 --\r
   3 -- Database structure for database 'share_index'\r
   4 --\r
   5 \r
   6 \r
   7 DROP DATABASE IF EXISTS `share_index`;\r
   8 CREATE DATABASE IF NOT EXISTS `share_index` DEFAULT CHARACTER SET utf8 ;\r
   9 \r
  10 USE `share_index`;\r
  11 \r
  12 --\r
  13 -- Table structure for table 'node'\r
  14 --\r
  15 \r
  16 CREATE TABLE IF NOT EXISTS `node` (\r
  17     `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,\r
  18     `parent_id` INT(10) UNSIGNED DEFAULT NULL,\r
  19     `lvl` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',\r
  20     `name` VARCHAR(244) NOT NULL,\r
  21     `modified_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',\r
  22     `created_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r
  23     PRIMARY KEY (`id`),\r
  24     UNIQUE KEY `unique` (`parent_id`,`name`),\r
  25     FOREIGN KEY (`parent_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE\r
  26 ) ENGINE=InnoDB;\r
  27 \r
  28 CREATE TRIGGER `tr_node_after_update` \r
  29         BEFORE UPDATE ON `node` FOR EACH ROW\r
  30         SET NEW.modified_on = CURRENT_TIMESTAMP;\r
  31 \r
  32 --\r
  33 -- Dumping data for table 'node'\r
  34 --\r
  35 \r
  36 LOCK TABLES `node` WRITE;\r
  37 \r
  38 INSERT INTO `node` (`id`, `parent_id`, `lvl`, `name`, `modified_on`, `created_on`) VALUES\r
  39         ('1',NULL,0,'root',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);\r
  40 \r
  41 UNLOCK TABLES;\r
  42 \r
  43 \r
  44 --\r
  45 -- Table structure for table 'relations'\r
  46 --\r
  47 \r
  48 CREATE TABLE IF NOT EXISTS `relations` (\r
  49     `child_id` INT(10) UNSIGNED NOT NULL,\r
  50     `parent_id` INT(3) UNSIGNED NOT NULL,\r
  51         PRIMARY KEY (`child_id`, `parent_id`),\r
  52         FOREIGN KEY (`child_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,\r
  53         FOREIGN KEY (`parent_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE\r
  54 ) ENGINE=InnoDB;\r
  55 \r
  56 \r
  57 --\r
  58 -- Table structure for table 'file_info'\r
  59 --\r
  60 \r
  61 CREATE TABLE IF NOT EXISTS `file_info` (\r
  62     `node_id` INT(10) UNSIGNED NOT NULL,\r
  63     `file_size` BIGINT(20) UNSIGNED DEFAULT NULL,\r
  64     PRIMARY KEY (`id`),\r
  65     UNIQUE KEY `uniq_file_info` (`node_id`),\r
  66     FOREIGN KEY (`node_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE\r
  67 ) ENGINE=InnoDB;\r
  68 \r
  69 \r
  70 -- Reset data\r
  71 \r
  72 DELETE FROM `file_info`;\r
  73 DELETE FROM `node` WHERE `name` != 'root';\r
  74 DELETE FROM `relations`;\r
  75 \r
  76 DROP PROCEDURE IF EXISTS `proc_add_node`;\r
  77 \r
  78 DELIMITER //\r
  79 \r
  80 CREATE PROCEDURE `proc_add_node` ( name VARCHAR(244), parent_id INT, OUT new_id INT )\r
  81 BEGIN\r
  82     SELECT lvl + 1 INTO @newlvl FROM node WHERE id = parent_id;\r
  83 \r
  84     INSERT INTO node (name, parent_id, lvl, modified_on) \r
  85         VALUES (name, parent_id, @newlvl, CURRENT_TIMESTAMP);\r
  86 \r
  87     SET new_id = @@identity;        \r
  88 \r
  89     WHILE parent_id IS NOT NULL DO\r
  90         INSERT INTO relations VALUES (new_id, parent_id);\r
  91         SELECT parent_id INTO parent_id FROM node WHERE id = parent_id;\r
  92     END WHILE;\r
  93 END//\r
  94 \r
  95 DELIMITER ;\r
  96 \r
  97 \r
  98 DROP PROCEDURE IF EXISTS proc_get_path;\r
  99 \r
 100 DELIMITER //\r
 101 \r
 102 CREATE PROCEDURE proc_get_path ( id INT, OUT path VARCHAR (266) )\r
 103 BEGIN\r
 104     \r
 105 END//\r
 106 \r
 107 DELIMITER ;\r
 108 \r
 109 DROP FUNCTION IF EXISTS get_path;\r
 110 \r
 111 DELIMITER //\r
 112 \r
 113 CREATE FUNCTION get_path(node_id INT)\r
 114 RETURNS VARCHAR(266)\r
 115 READS SQL DATA\r
 116 BEGIN\r
 117     SELECT CONCAT('/', name) INTO @val FROM node WHERE id = node_id;\r
 118 \r
 119     SELECT IFNULL(CONCAT('/', GROUP_CONCAT(parent.name SEPARATOR '/'), @val), @val)\r
 120         INTO @val\r
 121         FROM relations\r
 122         INNER JOIN node AS parent ON relations.parent_id = parent.id\r
 123         WHERE relations.child_id = node_id AND parent.parent_id IS NOT NULL \r
 124         ORDER BY parent.lvl DESC;\r
 125 \r
 126     RETURN @val;\r
 127 END//\r
 128 \r
 129 DELIMITER ;\r
 130 \r
 131 DROP VIEW IF EXISTS `view_file_system`;\r
 132 \r
 133 CREATE VIEW `view_file_system` AS\r
 134 SELECT id, name, get_path(id) as path\r
 135 , (SELECT COUNT(42) FROM relations WHERE parent_id = id) AS children\r
 136 , (SELECT COUNT(42) FROM relations WHERE child_id = id) AS parents\r
 137 FROM node;\r
 138 \r
 139 \r
 140 SET @id = 0;\r
 141 \r
 142 CALL proc_add_node ( 'windows', 1, @id );\r
 143 CALL proc_add_node ( 'system32', @id, @id );\r
 144 CALL proc_add_node ( 'driver', @id, @id );\r
 145 CALL proc_add_node ( 'etc', @id, @id );\r
 146 \r
 147 CALL proc_add_node ( 'documents and settings', 1, @id );\r
 148 CALL proc_add_node ( 'alpha', @id, @id );\r
 149 CALL proc_add_node ( 'roaming data', @id, @id );\r
 150 CALL proc_add_node ( 'mycorp', @id, @id );\r
 151 \r
 152 -- TODO: CALL proc_add_path ( 'windows/system32/drivers/etc/hosts' );\r
 153 \r
 154 \r
 155 SELECt * FROM `view_file_system`;\r
 156 SELECT COUNT(1) FROM relations;\r
 157 DELETE FROM node WHERE id = 7;\r
 158 SELECT COUNT(1) FROM relations;\r