• Home
• org.hsqldb
• sqltool
• 2.5.1
• source code
• lob-schema.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

org.hsqldb.resources.lob-schema.sql Maven / Gradle / Ivy

-- 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
;