org.hsqldb.resources.lob-schema.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of hsqldb Show documentation
Show all versions of hsqldb Show documentation
HSQLDB - Lightweight 100% Java SQL Database Engine
-- author Fred Toussi (fredt@users dot sourceforge.net) version 2.3.3
/*lob_schema_definition*/
CREATE SCHEMA SYSTEM_LOBS AUTHORIZATION DBA
CREATE TABLE BLOCKS(BLOCK_ADDR INT, BLOCK_COUNT INT NOT NULL, TX_ID BIGINT NOT NULL,
CONSTRAINT BLOCKS_PK PRIMARY KEY(BLOCK_ADDR))
CREATE INDEX BLOCKS_IDX1 ON BLOCKS(BLOCK_COUNT)
CREATE TABLE LOBS(BLOCK_ADDR INT NOT NULL, BLOCK_COUNT INT NOT NULL, BLOCK_OFFSET INT, LOB_ID BIGINT,
CONSTRAINT LOBS_PK PRIMARY KEY(LOB_ID, BLOCK_OFFSET),
CONSTRAINT LOBS_UQ1 UNIQUE(BLOCK_ADDR) )
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,
CONSTRAINT PARTS_PK PRIMARY KEY(LOB_ID, BLOCK_OFFSET))
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,
CONSTRAINT LOB_IDS_PK PRIMARY KEY(LOB_ID))
CREATE INDEX LOBS_IDX2 ON LOB_IDS(LOB_USAGE_COUNT)
CREATE SEQUENCE LOB_ID AS BIGINT START WITH 1
CREATE PROCEDURE CONVERT_BLOCK(B_ADDR INT, B_COUNT INT, B_OFFSET INT, L_ID BIGINT)
MODIFIES SQL DATA BEGIN ATOMIC
DELETE FROM BLOCKS WHERE BLOCK_ADDR = B_ADDR;
INSERT INTO LOBS VALUES(B_ADDR, B_COUNT, B_OFFSET, L_ID);
END
CREATE PROCEDURE DELETE_LOB(L_ID BIGINT, TX_ID BIGINT)
MODIFIES SQL DATA BEGIN ATOMIC
INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,TX_ID FROM LOBS WHERE LOBS.LOB_ID = L_ID);
DELETE FROM LOBS WHERE LOBS.LOB_ID = L_ID;
DELETE FROM PARTS WHERE LOB_ID = L_ID;
DELETE FROM LOB_IDS WHERE LOB_IDS.LOB_ID = L_ID;
END
CREATE PROCEDURE DELETE_UNUSED(L_IDS BIGINT ARRAY)
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE TABLE LIVE_IDS (TEMP_ID INT);
DECLARE TABLE TEMP_IDS (TEMP_ID INT);
DECLARE TEMP_COUNT INT DEFAULT 1;
WHILE TEMP_COUNT <= CARDINALITY(L_IDS) DO
INSERT INTO LIVE_IDS VALUES L_IDS[TEMP_COUNT];
SET TEMP_COUNT = TEMP_COUNT + 1;
END WHILE;
SET TEMP_COUNT = 0;
REPEAT
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;
INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,0 FROM LOBS WHERE LOBS.LOB_ID
IN (SELECT TEMP_ID FROM TEMP_IDS));
DELETE FROM LOBS WHERE LOBS.LOB_ID
IN (SELECT TEMP_ID FROM TEMP_IDS);
DELETE FROM PARTS WHERE LOB_ID
IN (SELECT TEMP_ID FROM TEMP_IDS);
DELETE FROM LOB_IDS WHERE LOB_IDS.LOB_ID IN (SELECT TEMP_ID FROM TEMP_IDS);
GET DIAGNOSTICS TEMP_COUNT = ROW_COUNT;
DELETE FROM TEMP_IDS;
UNTIL TEMP_COUNT < 1000 END REPEAT;
END
CREATE PROCEDURE DELETE_UNUSED_LOBS(IN LIMIT_ID BIGINT, OUT TOTAL_COUNT INT)
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE TABLE TEMP_IDS (TEMP_ID INT);
DECLARE TEMP_COUNT INT DEFAULT 0;
DECLARE TOTAL INT DEFAULT 0;
REPEAT
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;
INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,0 FROM LOBS WHERE LOBS.LOB_ID
IN (SELECT TEMP_ID FROM TEMP_IDS));
DELETE FROM LOBS WHERE LOBS.LOB_ID
IN (SELECT TEMP_ID FROM TEMP_IDS);
DELETE FROM PARTS WHERE LOB_ID
IN (SELECT TEMP_ID FROM TEMP_IDS);
DELETE FROM LOB_IDS WHERE LOB_IDS.LOB_ID IN (SELECT TEMP_ID FROM TEMP_IDS);
GET DIAGNOSTICS TEMP_COUNT = ROW_COUNT;
SET TOTAL = TOTAL + TEMP_COUNT;
DELETE FROM TEMP_IDS;
UNTIL TEMP_COUNT < 1000 END REPEAT;
SET TOTAL_COUNT = TOTAL;
END
CREATE PROCEDURE DELETE_BLOCKS(L_ID BIGINT, B_OFFSET INT, B_LIMIT INT, TX_ID BIGINT)
MODIFIES SQL DATA BEGIN ATOMIC
INSERT INTO BLOCKS (SELECT BLOCK_ADDR,BLOCK_COUNT,TX_ID FROM LOBS
WHERE LOBS.LOB_ID = L_ID AND BLOCK_OFFSET >= B_OFFSET AND BLOCK_OFFSET < B_LIMIT);
DELETE FROM LOBS
WHERE LOBS.LOB_ID = L_ID AND BLOCK_OFFSET >= B_OFFSET AND BLOCK_OFFSET < B_LIMIT;
END
CREATE PROCEDURE CREATE_EMPTY_BLOCK(INOUT B_ADDR INT, IN B_COUNT INT)
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE TEMP_COUNT INT DEFAULT NULL;
DECLARE TEMP_ADDR INT DEFAULT NULL;
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);
IF TEMP_ADDR IS NULL THEN
SIGNAL SQLSTATE '45000';
END IF;
UPDATE BLOCKS SET BLOCK_COUNT = B_COUNT WHERE BLOCK_ADDR = TEMP_ADDR;
INSERT INTO BLOCKS VALUES (TEMP_ADDR + B_COUNT, TEMP_COUNT - B_COUNT, 0);
SET B_ADDR = TEMP_ADDR;
END
CREATE PROCEDURE DIVIDE_BLOCK(B_OFFSET INT, L_ID BIGINT)
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE BL_ADDR INT DEFAULT NULL;
DECLARE BL_COUNT INT DEFAULT NULL;
DECLARE BL_OFFSET INT DEFAULT NULL;
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);
IF BL_ADDR IS NULL THEN
SIGNAL SQLSTATE '45000';
END IF;
DELETE FROM LOBS WHERE BLOCK_ADDR = BL_ADDR;
INSERT INTO LOBS VALUES (BL_ADDR, B_OFFSET - BL_OFFSET, BL_OFFSET, L_ID);
INSERT INTO LOBS VALUES (BL_ADDR + B_OFFSET - BL_OFFSET, BL_OFFSET + BL_COUNT - B_OFFSET, B_OFFSET, L_ID);
END
CREATE PROCEDURE ALLOC_BLOCKS (IN B_COUNT INT, IN B_OFFSET INT, IN L_ID BIGINT)
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE LOB_ADDR INT DEFAULT NULL;
DECLARE REMAINING_COUNT INT DEFAULT 0;
DECLARE BL_ADDR INT DEFAULT NULL;
DECLARE TEMP_COUNT INT DEFAULT 0;
DECLARE BL_OFFSET INT DEFAULT 0;
SET REMAINING_COUNT = B_COUNT;
SET BL_OFFSET = B_OFFSET;
MAIN_LOOP: LOOP
SET BL_ADDR = (SELECT BLOCK_ADDR FROM BLOCKS WHERE BLOCK_COUNT = REMAINING_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);
IF BL_ADDR IS NOT NULL THEN
CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);
IF LOB_ADDR IS NULL THEN
SET LOB_ADDR = BL_ADDR;
END IF;
LEAVE MAIN_LOOP;
END IF;
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);
IF BL_ADDR IS NOT NULL THEN
CALL CONVERT_BLOCK (BL_ADDR, TEMP_COUNT, BL_OFFSET, L_ID);
IF LOB_ADDR IS NULL THEN
SET LOB_ADDR = BL_ADDR;
END IF;
SET REMAINING_COUNT = REMAINING_COUNT - TEMP_COUNT;
SET BL_OFFSET = BL_OFFSET + TEMP_COUNT;
SET BL_ADDR = NULL;
SET TEMP_COUNT = 0;
ELSE
CALL CREATE_EMPTY_BLOCK (BL_ADDR, REMAINING_COUNT);
CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);
IF LOB_ADDR IS NULL THEN
SET LOB_ADDR = BL_ADDR;
END IF;
LEAVE MAIN_LOOP;
END IF;
END LOOP MAIN_LOOP;
END
CREATE PROCEDURE ALLOC_SINGLE_BLOCK (IN B_COUNT INT, IN B_OFFSET INT, IN L_ID BIGINT)
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE BL_ADDR INT DEFAULT NULL;
SET BL_ADDR = (SELECT BLOCK_ADDR FROM BLOCKS WHERE BLOCK_COUNT = B_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);
IF BL_ADDR IS NULL THEN
CALL CREATE_EMPTY_BLOCK (BL_ADDR, B_COUNT);
END IF;
CALL CONVERT_BLOCK (BL_ADDR, B_COUNT, B_OFFSET, L_ID);
END
CREATE PROCEDURE MERGE_EMPTY_BLOCKS ()
MODIFIES SQL DATA BEGIN ATOMIC
DECLARE BL_BASE_ADDR INT DEFAULT -1;
DECLARE BL_BASE_COUNT INT;
DECLARE BL_ADDR INT;
DECLARE BL_COUNT INT;
DECLARE BL_MERGE BOOLEAN;
REPEAT
SET BL_BASE_COUNT = NULL;
SET (BL_BASE_ADDR, BL_BASE_COUNT) = (SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS
WHERE BLOCK_ADDR > BL_BASE_ADDR
ORDER BY BLOCK_ADDR FETCH 1 ROW ONLY);
IF BL_BASE_COUNT IS NOT NULL THEN
SET BL_MERGE = FALSE;
REPEAT
SET BL_ADDR = NULL;
SET (BL_ADDR, BL_COUNT) =
(SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS
WHERE BLOCK_ADDR = BL_BASE_ADDR + BL_BASE_COUNT);
IF BL_ADDR IS NOT NULL THEN
SET BL_BASE_COUNT = BL_BASE_COUNT + BL_COUNT;
SET BL_MERGE = TRUE;
END IF;
UNTIL BL_ADDR IS NULL END REPEAT;
IF BL_MERGE THEN
DELETE FROM BLOCKS WHERE
BLOCK_ADDR >= BL_BASE_ADDR AND BLOCK_ADDR < BL_BASE_ADDR + BL_BASE_COUNT;
INSERT INTO BLOCKS VALUES BL_BASE_ADDR, BL_BASE_COUNT, 0;
END IF;
END IF;
UNTIL BL_BASE_COUNT IS NULL END REPEAT;
END
;