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