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