source: vbox/trunk/src/VBox/ValidationKit/testmanager/core/testbox.pgsql@ 61478

-- $Id: testbox.pgsql 61468 2016-06-05 02:55:32Z vboxsync $
--- @file
-- VBox Test Manager Database Stored Procedures - TestBoxes.
--

--
-- Copyright (C) 2012-2016 Oracle Corporation
--
-- This file is part of VirtualBox Open Source Edition (OSE), as
-- available from http://www.virtualbox.org. This file is free software;
-- you can redistribute it and/or modify it under the terms of the GNU
-- General Public License (GPL) as published by the Free Software
-- Foundation, in version 2 as it comes in the "COPYING" file of the
-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the
-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
--
-- The contents of this file may alternatively be used under the terms
-- of the Common Development and Distribution License Version 1.0
-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the
-- VirtualBox OSE distribution, in which case the provisions of the
-- CDDL are applicable instead of those of the GPL.
--
-- You may elect to license modified versions of this file under the
-- terms and conditions of either the GPL or the CDDL or both.
--


---
-- Checks if the test box name is unique, ignoring a_idTestCaseIgnore.
-- Raises exception if duplicates are found.
--
-- @internal
--
CREATE OR REPLACE FUNCTION TestBoxLogic_checkUniqueName(a_sName TEXT, a_idTestBoxIgnore INTEGER)
    RETURNS VOID AS $$
    DECLARE
        v_cRows INTEGER;
    BEGIN
        SELECT  COUNT(*) INTO v_cRows
        FROM    TestBoxes
        WHERE   sName      =  a_sName
            AND tsExpire   =  'infinity'::TIMESTAMP
            AND idTestBox <> a_idTestBoxIgnore;
        IF v_cRows <> 0 THEN
            RAISE EXCEPTION 'Duplicate test box name "%" (% times)', a_sName, v_cRows;
        END IF;
    END;
$$ LANGUAGE plpgsql;


---
-- Historize a row.
-- @internal
--
CREATE OR REPLACE FUNCTION TestBoxLogic_historizeEntry(a_idGenTestBox INTEGER, a_tsExpire TIMESTAMP WITH TIME ZONE)
    RETURNS VOID AS $$
    DECLARE
        v_cUpdatedRows INTEGER;
    BEGIN
        UPDATE  TestBoxes
          SET   tsExpire        = a_tsExpire
          WHERE idGenTestBox    = a_idGenTestBox
            AND tsExpire        = 'infinity'::TIMESTAMP;
        GET DIAGNOSTICS v_cUpdatedRows = ROW_COUNT;
        IF v_cUpdatedRows <> 1 THEN
            IF v_cUpdatedRows = 0 THEN
                RAISE EXCEPTION 'Test box generation ID % is no longer valid', a_idGenTestBox;
            END IF;
            RAISE EXCEPTION 'Integrity error in TestBoxes: % current rows with idGenTestBox=%', v_cUpdatedRows, a_idGenTestBox;
        END IF;
    END;
$$ LANGUAGE plpgsql;


---
-- Translate string via the string table.
--
-- @returns NULL if a_sValue is NULL, otherwise a string ID.
--
CREATE OR REPLACE FUNCTION TestBoxLogic_lookupOrFindString(a_sValue TEXT)
    RETURNS INTEGER AS $$
    DECLARE
        v_idStr        INTEGER;
        v_cRows        INTEGER;
    BEGIN
        IF a_sValue IS NULL THEN
            RETURN NULL;
        END IF;

        SELECT      idStr
            INTO    v_idStr
            FROM    TestBoxStrTab
            WHERE   sValue = a_sValue;
        GET DIAGNOSTICS v_cRows = ROW_COUNT;
        IF v_cRows = 0 THEN
            INSERT INTO TestBoxStrTab (sValue)
                VALUES (a_sValue)
                RETURNING idStr INTO v_idStr;
        END IF;
        RETURN v_idStr;
    END;
$$ LANGUAGE plpgsql;


---
-- Only adds the user settable parts of the row, i.e. not what TestBoxLogic_updateOnSignOn touches.
--
CREATE OR REPLACE function TestBoxLogic_addEntry(a_uidAuthor            INTEGER,
                                                 a_ip                   inet,
                                                 a_uuidSystem           uuid,
                                                 a_sName                TEXT,
                                                 a_sDescription         TEXT,
                                                 a_idSchedGroup         INTEGER,
                                                 a_fEnabled             BOOLEAN,
                                                 a_enmLomKind           LomKind_T,
                                                 a_ipLom                inet,
                                                 a_pctScaleTimeout      INTEGER,  -- Actually smallint, but default typing fun.
                                                 a_sComment             TEXT,
                                                 a_enmPendingCmd        TestBoxCmd_T,
                                                 OUT r_idTestBox        INTEGER,
                                                 OUT r_idGenTestBox     INTEGER,
                                                 OUT r_tsEffective      TIMESTAMP WITH TIME ZONE
                                                 ) AS $$
    DECLARE
         v_idStrDescription INTEGER;
         v_idStrComment     INTEGER;
    BEGIN
        PERFORM TestBoxLogic_checkUniqueName(a_sName, -1);

        SELECT TestBoxLogic_lookupOrFindString(a_sDescription) INTO v_idStrDescription;
        SELECT TestBoxLogic_lookupOrFindString(a_sComment)     INTO v_idStrComment;

        INSERT INTO TestBoxes (
                    tsEffective,         -- 1
                    uidAuthor,           -- 2
                    ip,                  -- 3
                    uuidSystem,          -- 4
                    sName,               -- 5
                    idStrDescription,    -- 6
                    idSchedGroup,        -- 7
                    fEnabled,            -- 8
                    enmLomKind,          -- 9
                    ipLom,               -- 10
                    pctScaleTimeout,     -- 11
                    idStrComment,        -- 12
                    enmPendingCmd )      -- 13
            VALUES (CURRENT_TIMESTAMP,   -- 1
                    a_uidAuthor,         -- 2
                    a_ip,                -- 3
                    a_uuidSystem,        -- 4
                    a_sName,             -- 5
                    v_idStrDescription,  -- 6
                    a_idSchedGroup,      -- 7
                    a_fEnabled,          -- 8
                    a_enmLomKind,        -- 9
                    a_ipLom,             -- 10
                    a_pctScaleTimeout,   -- 11
                    v_idStrComment,      -- 12
                    a_enmPendingCmd )    -- 13
            RETURNING idTestBox, idGenTestBox, tsEffective INTO r_idTestBox, r_idGenTestBox, r_tsEffective;
    END;
$$ LANGUAGE plpgsql;

---
-- Only adds the user settable parts of the row, i.e. not what TestBoxLogic_updateOnSignOn touches.
--
CREATE OR REPLACE function TestBoxLogic_editEntry(a_uidAuthor           INTEGER,
                                                  a_idTestBox           INTEGER,
                                                  a_ip                  inet,
                                                  a_uuidSystem          uuid,
                                                  a_sName               TEXT,
                                                  a_sDescription        TEXT,
                                                  a_idSchedGroup        INTEGER,
                                                  a_fEnabled            BOOLEAN,
                                                  a_enmLomKind          LomKind_T,
                                                  a_ipLom               inet,
                                                  a_pctScaleTimeout     INTEGER, -- Actually smallint, but default typing fun.
                                                  a_sComment            TEXT,
                                                  a_enmPendingCmd       TestBoxCmd_T,
                                                  OUT r_idGenTestBox    INTEGER,
                                                  OUT r_tsEffective     TIMESTAMP WITH TIME ZONE
                                                  ) AS $$
    DECLARE
        v_Row               TestBoxes%ROWTYPE;
        v_idStrDescription  INTEGER;
        v_idStrComment      INTEGER;
    BEGIN
        PERFORM TestBoxLogic_checkUniqueName(a_sName, a_idTestBox);

        SELECT TestBoxLogic_lookupOrFindString(a_sDescription) INTO v_idStrDescription;
        SELECT TestBoxLogic_lookupOrFindString(a_sComment)     INTO v_idStrComment;

        -- Fetch and historize the current row - there must be one.
        UPDATE      TestBoxes
            SET     tsExpire  = CURRENT_TIMESTAMP
            WHERE   idTestBox = a_idTestBox
                AND tsExpire  = 'infinity'::TIMESTAMP
            RETURNING * INTO STRICT v_Row;

        -- Modify the row with the new data.
        v_Row.uidAuthor         := a_uidAuthor;
        v_Row.ip                := a_ip;
        v_Row.uuidSystem        := a_uuidSystem;
        v_Row.sName             := a_sName;
        v_Row.idStrDescription  := v_idStrDescription;
        v_Row.idSchedGroup      := a_idSchedGroup;
        v_Row.fEnabled          := a_fEnabled;
        v_Row.enmLomKind        := a_enmLomKind;
        v_Row.ipLom             := a_ipLom;
        v_Row.pctScaleTimeout   := a_pctScaleTimeout;
        v_Row.idStrComment      := v_idStrComment;
        v_Row.enmPendingCmd     := a_enmPendingCmd;
        v_Row.tsEffective       := v_Row.tsExpire;
        r_tsEffective           := v_Row.tsExpire;
        v_Row.tsExpire          := 'infinity'::TIMESTAMP;

        -- Get a new generation ID.
        SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
        r_idGenTestBox  := v_Row.idGenTestBox;

        -- Insert the modified row.
        INSERT INTO TestBoxes VALUES (v_Row.*);
    END;
$$ LANGUAGE plpgsql;


DROP FUNCTION IF EXISTS TestBoxLogic_removeEntry(INTEGER, INTEGER, BOOLEAN);
CREATE OR REPLACE FUNCTION TestBoxLogic_removeEntry(a_uidAuthor INTEGER, a_idTestBox INTEGER, a_fCascade BOOLEAN)
    RETURNS VOID AS $$
    DECLARE
        v_Row           TestBoxes%ROWTYPE;
        v_tsEffective   TIMESTAMP WITH TIME ZONE;
        v_Rec           RECORD;
        v_sErrors       TEXT;
    BEGIN
        --
        -- Check preconditions.
        --
        IF a_fCascade <> TRUE THEN
            -- @todo implement checks which throws useful exceptions.
        ELSE
            RAISE EXCEPTION 'CASCADE test box deletion is not implemented';
        END IF;

        --
        -- To preserve the information about who deleted the record, we try to
        -- add a dummy record which expires immediately.  I say try because of
        -- the primary key, we must let the new record be valid for 1 us. :-(
        --
        SELECT  * INTO STRICT v_Row
        FROM    TestBoxes
        WHERE   idTestBox  = a_idTestBox
            AND tsExpire   = 'infinity'::TIMESTAMP;

        v_tsEffective := CURRENT_TIMESTAMP - INTERVAL '1 microsecond';
        IF v_Row.tsEffective < v_tsEffective THEN
            PERFORM TestBoxLogic_historizeEntry(v_Row.idGenTestBox, v_tsEffective);

            v_Row.tsEffective   := v_tsEffective;
            v_Row.tsExpire      := CURRENT_TIMESTAMP;
            v_Row.uidAuthor     := a_uidAuthor;
            SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
            INSERT INTO TestBoxes VALUES (v_Row.*);
        ELSE
            PERFORM TestBoxLogic_historizeEntry(v_Row.idGenTestBox, CURRENT_TIMESTAMP);
        END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'Test box with ID % does not currently exist', a_idTestBox;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'Integrity error in TestBoxes: Too many current rows for %', a_idTestBox;
    END;
$$ LANGUAGE plpgsql;


---
-- Sign on update
--
CREATE OR REPLACE function TestBoxLogic_updateOnSignOn(a_idTestBox          INTEGER,
                                                       a_ip                 inet,
                                                       a_sOs                TEXT,
                                                       a_sOsVersion         TEXT,
                                                       a_sCpuVendor         TEXT,
                                                       a_sCpuArch           TEXT,
                                                       a_sCpuName           TEXT,
                                                       a_lCpuRevision       bigint,
                                                       a_cCpus              INTEGER, -- Actually smallint, but default typing fun.
                                                       a_fCpuHwVirt         boolean,
                                                       a_fCpuNestedPaging   boolean,
                                                       a_fCpu64BitGuest     boolean,
                                                       a_fChipsetIoMmu      boolean,
                                                       a_fRawMode           boolean,
                                                       a_cMbMemory          bigint,
                                                       a_cMbScratch         bigint,
                                                       a_sReport            TEXT,
                                                       a_iTestBoxScriptRev  INTEGER,
                                                       a_iPythonHexVersion  INTEGER,
                                                       OUT r_idGenTestBox   INTEGER
                                                       ) AS $$
    DECLARE
        v_Row               TestBoxes%ROWTYPE;
        v_idStrOs           INTEGER;
        v_idStrOsVersion    INTEGER;
        v_idStrCpuVendor    INTEGER;
        v_idStrCpuArch      INTEGER;
        v_idStrCpuName      INTEGER;
        v_idStrReport       INTEGER;
    BEGIN
        SELECT TestBoxLogic_lookupOrFindString(a_sOs)           INTO v_idStrOs;
        SELECT TestBoxLogic_lookupOrFindString(a_sOsVersion)    INTO v_idStrOsVersion;
        SELECT TestBoxLogic_lookupOrFindString(a_sCpuVendor)    INTO v_idStrCpuVendor;
        SELECT TestBoxLogic_lookupOrFindString(a_sCpuArch)      INTO v_idStrCpuArch;
        SELECT TestBoxLogic_lookupOrFindString(a_sCpuName)      INTO v_idStrCpuName;
        SELECT TestBoxLogic_lookupOrFindString(a_sReport)       INTO v_idStrReport;

        -- Fetch and historize the current row - there must be one.
        UPDATE      TestBoxes
            SET     tsExpire     = CURRENT_TIMESTAMP
            WHERE   idTestBox    = a_idTestBox
                AND tsExpire     = 'infinity'::TIMESTAMP
            RETURNING * INTO STRICT v_Row;

        -- Modify the row with the new data.
        v_Row.uidAuthor             := NULL;
        v_Row.ip                    := a_ip;
        v_Row.idStrOs               := v_idStrOs;
        v_Row.idStrOsVersion        := v_idStrOsVersion;
        v_Row.idStrCpuVendor        := v_idStrCpuVendor;
        v_Row.idStrCpuArch          := v_idStrCpuArch;
        v_Row.idStrCpuName          := v_idStrCpuName;
        v_Row.lCpuRevision          := a_lCpuRevision;
        v_Row.cCpus                 := a_cCpus;
        v_Row.fCpuHwVirt            := a_fCpuHwVirt;
        v_Row.fCpuNestedPaging      := a_fCpuNestedPaging;
        v_Row.fCpu64BitGuest        := a_fCpu64BitGuest;
        v_Row.fChipsetIoMmu         := a_fChipsetIoMmu;
        v_Row.fRawMode              := a_fRawMode;
        v_Row.cMbMemory             := a_cMbMemory;
        v_Row.cMbScratch            := a_cMbScratch;
        v_Row.idStrReport           := v_idStrReport;
        v_Row.iTestBoxScriptRev     := a_iTestBoxScriptRev;
        v_Row.iPythonHexVersion     := a_iPythonHexVersion;
        v_Row.tsEffective           := v_Row.tsExpire;
        v_Row.tsExpire              := 'infinity'::TIMESTAMP;

        -- Get a new generation ID.
        SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
        r_idGenTestBox  := v_Row.idGenTestBox;

        -- Insert the modified row.
        INSERT INTO TestBoxes VALUES (v_Row.*);
    END;
$$ LANGUAGE plpgsql;


---
-- Set new command.
--
CREATE OR REPLACE function TestBoxLogic_setCommand(a_uidAuthor          INTEGER,
                                                   a_idTestBox          INTEGER,
                                                   a_enmOldCmd          TestBoxCmd_T,
                                                   a_enmNewCmd          TestBoxCmd_T,
                                                   a_sComment           TEXT,
                                                   OUT r_idGenTestBox   INTEGER,
                                                   OUT r_tsEffective    TIMESTAMP WITH TIME ZONE
                                                   ) AS $$
    DECLARE
        v_Row               TestBoxes%ROWTYPE;
        v_idStrComment      INTEGER;
    BEGIN
        SELECT TestBoxLogic_lookupOrFindString(a_sComment) INTO v_idStrComment;

        -- Fetch and historize the current row - there must be one.
        UPDATE      TestBoxes
            SET     tsExpire      = CURRENT_TIMESTAMP
            WHERE   idTestBox     = a_idTestBox
                AND tsExpire      = 'infinity'::TIMESTAMP
                AND enmPendingCmd = a_enmOldCmd
            RETURNING * INTO STRICT v_Row;

        -- Modify the row with the new data.
        v_Row.enmPendingCmd         := a_enmNewCmd;
        IF v_idStrComment IS NOT NULL THEN
            v_Row.idStrComment      := v_idStrComment;
        END IF;
        v_Row.tsEffective           := v_Row.tsExpire;
        r_tsEffective               := v_Row.tsExpire;
        v_Row.tsExpire              := 'infinity'::TIMESTAMP;

        -- Get a new generation ID.
        SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
        r_idGenTestBox  := v_Row.idGenTestBox;

        -- Insert the modified row.
        INSERT INTO TestBoxes VALUES (v_Row.*);
    END;
$$ LANGUAGE plpgsql;