-- initialise a new DB
-- adds cnfg table, procs and exceptions
-- to support DB versioning with updb

SET BAIL ON;
SET TERM ^;

CREATE OR ALTER EXCEPTION EX_VERSION 'wrong database version'^

-- check cnfg table does not exist

EXECUTE BLOCK AS BEGIN
    IF (EXISTS (SELECT 1 FROM rdb$relations WHERE rdb$relation_name = 'CNFG')) THEN
        EXCEPTION EX_VERSION;
END^

CREATE TABLE cnfg (
    nm VARCHAR(20),
    vl VARCHAR(100),
    PRIMARY KEY(nm)
)^
INSERT INTO cnfg VALUES ('name', 'test')^
INSERT INTO cnfg VALUES ('version', '0')^
INSERT INTO cnfg VALUES ('broken', '0')^
INSERT INTO cnfg VALUES ('vaneer', '0')^
COMMIT^

COMMENT ON TABLE cnfg IS 'config, version, and patch log'^

CREATE EXCEPTION EX_BROKEN 'database is in a broken state'^
CREATE EXCEPTION EX_HAS_VANEER 'database vaneer is present'^
CREATE EXCEPTION EX_NO_VANEER 'database vaneer is not present'^

CREATE PROCEDURE sp_check_vaneer(
    vaneer INTEGER
)
AS
-- check not broken, and vaneer state
BEGIN

    IF (EXISTS (
        SELECT 1 FROM cnfg WHERE nm = 'broken' AND vl <> 0
    )) THEN
        EXCEPTION EX_BROKEN;

    IF (:vaneer = 0) THEN
    BEGIN
        IF (EXISTS (
            SELECT 1 FROM cnfg WHERE nm = 'vaneer' AND vl <> '0'
        )) THEN
            EXCEPTION EX_HAS_VANEER;
    END
    ELSE
    BEGIN
        IF (EXISTS (
            SELECT 1 FROM cnfg WHERE nm = 'vaneer' AND vl = '0'
        )) THEN
            EXCEPTION EX_NO_VANEER;
    END
END^

CREATE PROCEDURE sp_set_vaneer(
    vaneer VARCHAR(50)
)
AS
-- set the vaneer version, or 0 -> dropped
BEGIN
    UPDATE cnfg SET vl = :vaneer WHERE nm = 'vaneer';
    IF (vaneer <> '') THEN
	    UPDATE cnfg SET vl = '0' WHERE nm = 'broken';
END^

CREATE PROCEDURE sp_patch_from(
    version INTEGER
)
AS
-- check not broken, no vaneer, and right version
BEGIN

    EXECUTE PROCEDURE sp_check_vaneer(0);

    IF (EXISTS (
        SELECT 1 FROM cnfg WHERE nm = 'version' AND vl <> :version
    )) THEN
        EXCEPTION EX_VERSION;

    UPDATE cnfg SET vl = '1' WHERE nm = 'broken';
END^

CREATE PROCEDURE sp_patch_to(
    version INTEGER,
    comment VARCHAR(100)
)
AS
-- set new version and log description
DECLARE version_s VARCHAR(100);
BEGIN
    UPDATE cnfg SET vl = :version WHERE nm = 'version';
    UPDATE cnfg SET vl = '0' WHERE nm = 'broken';
    version_s = :version;
    WHILE (CHAR_LENGTH(version_s) < 5) DO
        version_s = '0' || version_s;
    INSERT INTO cnfg (
        nm,
        vl
    ) VALUES (
        'patch_' || :version_s || '_comment',
        :comment
    );
END^

SET TERM ;^
