1-- author Fred Toussi (fredt@users dot sourceforge.net) version 2.3.3
2
3/*lob_schema_definition*/
4CREATE SCHEMA SYSTEM_LOBS AUTHORIZATION DBA
5 CREATE TABLE BLOCKS(BLOCK_ADDR INT, BLOCK_COUNT INT NOT NULL, TX_ID BIGINT NOT NULL,
6   CONSTRAINT BLOCKS_PK PRIMARY KEY(BLOCK_ADDR))
7 CREATE INDEX BLOCKS_IDX1 ON BLOCKS(BLOCK_COUNT)
8 CREATE TABLE LOBS(BLOCK_ADDR INT NOT NULL, BLOCK_COUNT INT NOT NULL, BLOCK_OFFSET INT, LOB_ID BIGINT,
9   CONSTRAINT LOBS_PK PRIMARY KEY(LOB_ID, BLOCK_OFFSET),
10   CONSTRAINT LOBS_UQ1 UNIQUE(BLOCK_ADDR) )
11 CREATE TABLE PARTS(BLOCK_COUNT INT NOT NULL, BLOCK_OFFSET INT, PART_OFFSET BIGINT NOT NULL, PART_LENGTH BIGINT NOT NULL, PART_BYTES BIGINT NOT NULL, LOB_ID BIGINT,
12   CONSTRAINT PARTS_PK PRIMARY KEY(LOB_ID, BLOCK_OFFSET))
13 CREATE TABLE LOB_IDS(LOB_ID BIGINT, LOB_LENGTH BIGINT NOT NULL, LOB_USAGE_COUNT INT DEFAULT 0 NOT NULL, LOB_TYPE SMALLINT NOT NULL,
14   CONSTRAINT LOB_IDS_PK PRIMARY KEY(LOB_ID))
15 CREATE INDEX LOBS_IDX2 ON LOB_IDS(LOB_USAGE_COUNT)
16 CREATE SEQUENCE LOB_ID AS BIGINT START WITH 1
17
18 CREATE PROCEDURE CONVERT_BLOCK(B_ADDR INT, B_COUNT INT, B_OFFSET INT, L_ID BIGINT)
19  MODIFIES SQL DATA BEGIN ATOMIC
20  DELETE FROM BLOCKS WHERE BLOCK_ADDR = B_ADDR;
21  INSERT INTO LOBS VALUES(B_ADDR, B_COUNT, B_OFFSET, L_ID);
22 END
23
24 CREATE PROCEDURE DELETE_LOB(L_ID BIGINT, TX_ID BIGINT)
25  MODIFIES SQL DATA BEGIN ATOMIC
26  INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,TX_ID FROM LOBS WHERE LOBS.LOB_ID = L_ID);
27  DELETE FROM LOBS WHERE LOBS.LOB_ID = L_ID;
28  DELETE FROM PARTS WHERE LOB_ID = L_ID;
29  DELETE FROM LOB_IDS WHERE LOB_IDS.LOB_ID = L_ID;
30 END
31
32 CREATE PROCEDURE DELETE_UNUSED(L_IDS BIGINT ARRAY)
33  MODIFIES SQL DATA BEGIN ATOMIC
34  DECLARE TABLE LIVE_IDS (TEMP_ID INT);
35  DECLARE TABLE TEMP_IDS (TEMP_ID INT);
36  DECLARE TEMP_COUNT INT DEFAULT 1;
37
38  WHILE TEMP_COUNT <= CARDINALITY(L_IDS) DO
39    INSERT INTO LIVE_IDS VALUES L_IDS[TEMP_COUNT];
40    SET TEMP_COUNT = TEMP_COUNT + 1;
41  END WHILE;
42
43  SET TEMP_COUNT = 0;
44
45  REPEAT
46   INSERT INTO TEMP_IDS SELECT LOB_IDS.LOB_ID FROM LOB_IDS WHERE LOB_USAGE_COUNT < 1 AND LOB_IDS.LOB_ID NOT IN (SELECT * FROM LIVE_IDS) LIMIT 1000;
47   INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,0 FROM LOBS WHERE LOBS.LOB_ID
48    IN (SELECT TEMP_ID FROM TEMP_IDS));
49   DELETE FROM LOBS WHERE LOBS.LOB_ID
50    IN (SELECT TEMP_ID FROM TEMP_IDS);
51   DELETE FROM PARTS WHERE LOB_ID
52    IN (SELECT TEMP_ID FROM TEMP_IDS);
53   DELETE FROM LOB_IDS WHERE LOB_IDS.LOB_ID IN (SELECT TEMP_ID FROM TEMP_IDS);
54   GET DIAGNOSTICS TEMP_COUNT = ROW_COUNT;
55   DELETE FROM TEMP_IDS;
56  UNTIL TEMP_COUNT < 1000 END REPEAT;
57 END
58
59 CREATE PROCEDURE DELETE_UNUSED_LOBS(IN LIMIT_ID BIGINT, OUT TOTAL_COUNT INT)
60  MODIFIES SQL DATA BEGIN ATOMIC
61  DECLARE TABLE TEMP_IDS (TEMP_ID INT);
62  DECLARE TEMP_COUNT INT DEFAULT 0;
63  DECLARE TOTAL INT DEFAULT 0;
64  REPEAT
65   INSERT INTO TEMP_IDS SELECT LOB_IDS.LOB_ID FROM LOB_IDS WHERE LOB_USAGE_COUNT = 0 AND LOB_IDS.LOB_ID < LIMIT_ID LIMIT 1000;
66   INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,0 FROM LOBS WHERE LOBS.LOB_ID
67    IN (SELECT TEMP_ID FROM TEMP_IDS));
68   DELETE FROM LOBS WHERE LOBS.LOB_ID
69    IN (SELECT TEMP_ID FROM TEMP_IDS);
70   DELETE FROM PARTS WHERE LOB_ID
71    IN (SELECT TEMP_ID FROM TEMP_IDS);
72   DELETE FROM LOB_IDS WHERE LOB_IDS.LOB_ID IN (SELECT TEMP_ID FROM TEMP_IDS);
73   GET DIAGNOSTICS TEMP_COUNT = ROW_COUNT;
74   SET TOTAL = TOTAL + TEMP_COUNT;
75   DELETE FROM TEMP_IDS;
76  UNTIL TEMP_COUNT < 1000 END REPEAT;
77  SET TOTAL_COUNT = TOTAL;
78 END
79
80
81 CREATE PROCEDURE DELETE_BLOCKS(L_ID BIGINT, B_OFFSET INT, B_LIMIT INT, TX_ID BIGINT)
82  MODIFIES SQL DATA BEGIN ATOMIC
83
84  INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,TX_ID FROM LOBS
85   WHERE LOBS.LOB_ID = L_ID AND BLOCK_OFFSET >= B_OFFSET AND BLOCK_OFFSET < B_LIMIT);
86  DELETE FROM LOBS
87   WHERE LOBS.LOB_ID = L_ID AND BLOCK_OFFSET >= B_OFFSET AND BLOCK_OFFSET < B_LIMIT;
88 END
89
90 CREATE PROCEDURE CREATE_EMPTY_BLOCK(INOUT B_ADDR INT, IN B_COUNT INT)
91  MODIFIES SQL DATA BEGIN ATOMIC
92  DECLARE TEMP_COUNT INT DEFAULT NULL;
93  DECLARE TEMP_ADDR INT DEFAULT NULL;
94  SET (TEMP_ADDR, TEMP_COUNT) = (SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS WHERE BLOCK_COUNT > B_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);
95
96  IF TEMP_ADDR IS NULL THEN
97   SIGNAL SQLSTATE '45000';
98  END IF;
99
100  UPDATE BLOCKS SET BLOCK_COUNT = B_COUNT WHERE BLOCK_ADDR = TEMP_ADDR;
101  INSERT INTO BLOCKS VALUES (TEMP_ADDR + B_COUNT, TEMP_COUNT - B_COUNT, 0);
102  SET B_ADDR = TEMP_ADDR;
103 END
104
105 CREATE PROCEDURE DIVIDE_BLOCK(B_OFFSET INT, L_ID BIGINT)
106  MODIFIES SQL DATA BEGIN ATOMIC
107  DECLARE BL_ADDR INT DEFAULT NULL;
108  DECLARE BL_COUNT INT DEFAULT NULL;
109  DECLARE BL_OFFSET INT DEFAULT NULL;
110
111  SET (BL_ADDR, BL_COUNT, BL_OFFSET) = (SELECT BLOCK_ADDR, BLOCK_COUNT, BLOCK_OFFSET FROM LOBS WHERE LOBS.LOB_ID = L_ID AND B_OFFSET > BLOCK_OFFSET AND B_OFFSET < BLOCK_OFFSET + BLOCK_COUNT);
112
113   IF BL_ADDR IS NULL THEN
114    SIGNAL SQLSTATE '45000';
115   END IF;
116  DELETE FROM LOBS WHERE BLOCK_ADDR = BL_ADDR;
117  INSERT INTO LOBS VALUES (BL_ADDR, B_OFFSET - BL_OFFSET, BL_OFFSET, L_ID);
118  INSERT INTO LOBS VALUES (BL_ADDR + B_OFFSET - BL_OFFSET, BL_OFFSET + BL_COUNT - B_OFFSET, B_OFFSET, L_ID);
119
120 END
121
122 CREATE PROCEDURE ALLOC_BLOCKS (IN B_COUNT INT, IN B_OFFSET INT, IN L_ID BIGINT)
123  MODIFIES SQL DATA BEGIN ATOMIC
124
125  DECLARE LOB_ADDR INT DEFAULT NULL;
126  DECLARE REMAINING_COUNT INT DEFAULT 0;
127  DECLARE BL_ADDR INT DEFAULT NULL;
128  DECLARE TEMP_COUNT INT DEFAULT 0;
129  DECLARE BL_OFFSET INT DEFAULT 0;
130
131  SET REMAINING_COUNT = B_COUNT;
132  SET BL_OFFSET = B_OFFSET;
133
134  MAIN_LOOP: LOOP
135
136   SET BL_ADDR = (SELECT BLOCK_ADDR FROM BLOCKS WHERE BLOCK_COUNT = REMAINING_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);
137
138   IF BL_ADDR IS NOT NULL THEN
139
140    CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);
141
142    IF LOB_ADDR IS NULL THEN
143     SET LOB_ADDR = BL_ADDR;
144    END IF;
145
146    LEAVE MAIN_LOOP;
147
148   END IF;
149
150   SET (BL_ADDR, TEMP_COUNT) = (SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS WHERE BLOCK_COUNT < REMAINING_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);
151
152   IF BL_ADDR IS NOT NULL THEN
153
154    CALL CONVERT_BLOCK (BL_ADDR, TEMP_COUNT, BL_OFFSET, L_ID);
155
156    IF LOB_ADDR IS NULL THEN
157      SET LOB_ADDR = BL_ADDR;
158    END IF;
159
160    SET REMAINING_COUNT = REMAINING_COUNT - TEMP_COUNT;
161    SET BL_OFFSET = BL_OFFSET + TEMP_COUNT;
162    SET BL_ADDR = NULL;
163    SET TEMP_COUNT = 0;
164
165   ELSE
166
167    CALL CREATE_EMPTY_BLOCK (BL_ADDR, REMAINING_COUNT);
168    CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);
169
170    IF LOB_ADDR IS NULL THEN
171     SET LOB_ADDR = BL_ADDR;
172
173    END IF;
174    LEAVE MAIN_LOOP;
175   END IF;
176
177  END LOOP MAIN_LOOP;
178 END
179
180 CREATE PROCEDURE ALLOC_SINGLE_BLOCK (IN B_COUNT INT, IN B_OFFSET INT, IN L_ID BIGINT)
181  MODIFIES SQL DATA BEGIN ATOMIC
182
183  DECLARE BL_ADDR INT DEFAULT NULL;
184
185  SET BL_ADDR = (SELECT BLOCK_ADDR FROM BLOCKS WHERE BLOCK_COUNT = B_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);
186
187  IF BL_ADDR IS NULL THEN
188    CALL CREATE_EMPTY_BLOCK (BL_ADDR, B_COUNT);
189  END IF;
190
191  CALL CONVERT_BLOCK (BL_ADDR, B_COUNT, B_OFFSET, L_ID);
192 END
193
194 CREATE PROCEDURE MERGE_EMPTY_BLOCKS ()
195  MODIFIES SQL DATA BEGIN ATOMIC
196
197  DECLARE BL_BASE_ADDR INT DEFAULT -1;
198  DECLARE BL_BASE_COUNT INT;
199  DECLARE BL_ADDR INT;
200  DECLARE BL_COUNT INT;
201  DECLARE BL_MERGE BOOLEAN;
202
203  REPEAT
204   SET BL_BASE_COUNT = NULL;
205   SET (BL_BASE_ADDR, BL_BASE_COUNT) = (SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS
206   WHERE BLOCK_ADDR > BL_BASE_ADDR
207   ORDER BY BLOCK_ADDR FETCH 1 ROW ONLY);
208
209   IF BL_BASE_COUNT IS NOT NULL THEN
210    SET BL_MERGE = FALSE;
211
212    REPEAT
213     SET BL_ADDR = NULL;
214     SET (BL_ADDR, BL_COUNT) =
215     (SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS
216      WHERE BLOCK_ADDR = BL_BASE_ADDR + BL_BASE_COUNT);
217
218     IF BL_ADDR IS NOT NULL THEN
219      SET BL_BASE_COUNT = BL_BASE_COUNT + BL_COUNT;
220      SET BL_MERGE = TRUE;
221     END IF;
222    UNTIL BL_ADDR IS NULL END REPEAT;
223
224    IF BL_MERGE THEN
225     DELETE FROM BLOCKS WHERE
226      BLOCK_ADDR >= BL_BASE_ADDR AND BLOCK_ADDR < BL_BASE_ADDR + BL_BASE_COUNT;
227
228     INSERT INTO BLOCKS VALUES BL_BASE_ADDR, BL_BASE_COUNT, 0;
229    END IF;
230   END IF;
231  UNTIL BL_BASE_COUNT IS NULL END REPEAT;
232 END
233;
234