Z9B9VK9p

1#stores the actual node data
2CREATE TABLE nodes(
3    nodeId MEDIUMINT NOT NULL AUTO_INCREMENT,
4    nodeType VARCHAR(10) NOT NULL, #'leaf', 'parallel' or 'sequential'
5    nodeValue MEDIUMINT NOT NULL DEFAULT 0,
6    PRIMARY KEY (nodeId) 
7);
8
9#stores the parent/child relationships
10CREATE TABLE parents(
11    nodeId MEDIUMINT NOT NULL,
12    parentId MEDIUMINT NULL, # null if it is the root node
13    PRIMARY KEY (nodeId),
14    INDEX (parentId),
15    FOREIGN KEY (nodeId) REFERENCES nodes(nodeId)
16);
17	
18DROP PROCEDURE IF EXISTS setLeafNodeValue;
19DELIMITER $$
20CREATE PROCEDURE setLeafNodeValue(id MEDIUMINT, val MEDIUMINT)
21proc:BEGIN
22    # assume validation checks to ensure it exists and is a leaf node have already been done
23    DECLARE currentNodeId MEDIUMINT DEFAULT id;
24    DECLARE currentNodesNewValue MEDIUMINT DEFAULT 0;
25    DECLARE currentNodesOldValue MEDIUMINT DEFAULT 0;
26    DECLARE currentNodeType VARCHAR(10) DEFAULT NULL;
27    DECLARE prevNodesOldValue MEDIUMINT DEFAULT 0;
28    DECLARE prevNodesNewValue MEDIUMINT DEFAULT 0;
29
30    START TRANSACTION;
31
32        WHILE currentNodeId IS NOT NULL DO
33
34            SELECT nodeType, nodeValue INTO currentNodeType, currentNodesOldValue FROM nodes WHERE nodeId = currentNodeId;
35
36            CASE currentNodeType
37                WHEN 'leaf' THEN
38                    SET currentNodesNewValue = val;
39                WHEN 'parallel' THEN
40                    SET currentNodesNewValue = (SELECT MAX(n.nodeValue) FROM nodes n JOIN parents p ON n.nodeId = p.nodeId WHERE p.parentId = currentNodeId);
41                WHEN 'sequential' THEN
42                    SET currentNodesNewValue = currentNodesOldValue + (prevNodesNewValue - prevNodesOldValue);
43            END CASE;
44
45            IF currentNodesNewValue = currentNodesOldValue THEN
46                COMMIT;
47                LEAVE proc; #no change is being made
48            END IF;
49
50            UPDATE nodes SET nodeValue = currentNodesNewValue WHERE nodeId = currentNodeId;
51
52            SET prevNodesOldValue = currentNodesOldValue;
53
54            SET prevNodesNewValue = currentNodesNewValue;
55
56            SELECT parentId INTO currentNodeId FROM parents WHERE nodeId = currentNodeId;
57
58        END WHILE;
59
60    COMMIT;
61END$$
62DELIMITER ;
63
64DROP PROCEDURE IF EXISTS setBranchNodeType;
65DELIMITER $$
66CREATE PROCEDURE setBranchNodeType(id MEDIUMINT, newNodeType VARCHAR(10))
67proc:BEGIN
68    # assume validation checks to ensure it exists and is a branch node have already been done
69    # and that newNodeType is either 'parallel' or 'sequential'
70    DECLARE currentNodeId MEDIUMINT DEFAULT id;
71    DECLARE currentNodesNewValue MEDIUMINT DEFAULT 0;
72    DECLARE currentNodesOldValue MEDIUMINT DEFAULT 0;
73    DECLARE currentNodeType VARCHAR(10) DEFAULT NULL;
74    DECLARE prevNodesOldValue MEDIUMINT DEFAULT 0;
75    DECLARE prevNodesNewValue MEDIUMINT DEFAULT 0;
76
77    SELECT nodeType, nodeValue INTO currentNodeType, currentNodesOldValue FROM nodes WHERE nodeId = currentNodeId;
78
79    IF currentNodeType = newNodeType THEN
80        LEAVE proc;
81    END IF;
82
83    START TRANSACTION;
84
85        CASE newNodeType
86            WHEN 'parallel' THEN
87                SET currentNodesNewValue = (SELECT MAX(n.nodeValue) FROM nodes n JOIN parents p ON n.nodeId = p.nodeId WHERE p.parentId = currentNodeId);
88            WHEN 'sequential' THEN
89                SET currentNodesNewValue = (SELECT SUM(n.nodeValue) FROM nodes n JOIN parents p ON n.nodeId = p.nodeId WHERE p.parentId = currentNodeId);
90        END CASE;
91
92        UPDATE nodes SET nodeType = newNodeType, nodeValue = currentNodesNewValue WHERE nodeId = currentNodeId;
93
94        IF currentNodesNewValue = currentNodesOldValue THEN
95            COMMIT;
96            LEAVE proc;
97        END IF;
98
99        SET prevNodesOldValue = currentNodesOldValue;
100
101        SET prevNodesNewValue = currentNodesNewValue;
102
103        SELECT parentId INTO currentNodeId FROM parents WHERE nodeId = currentNodeId;
104
105        WHILE currentNodeId IS NOT NULL DO
106
107            SELECT nodeType, nodeValue INTO currentNodeType, currentNodesOldValue FROM nodes WHERE nodeId = currentNodeId;
108
109            CASE currentNodeType
110                WHEN 'parallel' THEN
111                    SET currentNodesNewValue = (SELECT MAX(n.nodeValue) FROM nodes n JOIN parents p ON n.nodeId = p.nodeId WHERE p.parentId = currentNodeId);
112                WHEN 'sequential' THEN
113                    SET currentNodesNewValue = currentNodesOldValue + (prevNodesNewValue - prevNodesOldValue);
114            END CASE;
115
116            IF currentNodesNewValue = currentNodesOldValue THEN
117                COMMIT;
118                LEAVE proc;
119            END IF;
120
121            UPDATE nodes SET nodeValue = currentNodesNewValue WHERE nodeId = currentNodeId;
122
123            SET prevNodesOldValue = currentNodesOldValue;
124
125            SET prevNodesNewValue = currentNodesNewValue;
126
127            SELECT parentId INTO currentNodeId FROM parents WHERE nodeId = currentNodeId;
128
129        END WHILE;
130
131    COMMIT;
132END$$
133DELIMITER ;
134	
135(p)4
136|
137|__(s)3
138|   |
139|   |__1
140|   |
141|   |__2
142|
143|__(p)4
144    |
145    |__3
146    |
147    |__4
148	
149(p)4
150|
151|__(p)3
152|   |
153|   |__3
154|   |
155|   |__2
156|
157|__(p)4
158    |
159    |__3
160    |
161    |__4