globalresource.pgsql revision cf22150eaeeb72431bf1cf65c309a431454fb22b
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- VBox Test Manager Database Stored Procedures.
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- Copyright (C) 2006-2014 Oracle Corporation
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- This file is part of VirtualBox Open Source Edition (OSE), as
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- available from http://www.virtualbox.org. This file is free software;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- you can redistribute it and/or modify it under the terms of the GNU
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- General Public License (GPL) as published by the Free Software
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- Foundation, in version 2 as it comes in the "COPYING" file of the
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- The contents of this file may alternatively be used under the terms
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- of the Common Development and Distribution License Version 1.0
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- VirtualBox OSE distribution, in which case the provisions of the
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- CDDL are applicable instead of those of the GPL.
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- You may elect to license modified versions of this file under the
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- terms and conditions of either the GPL or the CDDL or both.
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync\set ON_ERROR_STOP 1
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync\connect testmanager;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- Args: uidAuthor, sName, sDescription, fEnabled
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsyncCREATE OR REPLACE function add_globalresource(integer, text, text, bool) RETURNS integer AS $$
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _idGlobalRsrc integer;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _uidAuthor ALIAS FOR $1;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _sName ALIAS FOR $2;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _sDescription ALIAS FOR $3;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _fEnabled ALIAS FOR $4;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Check if Global Resource name is unique
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync IF EXISTS(SELECT * FROM GlobalResources
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync WHERE sName=_sName AND
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync tsExpire='infinity'::timestamp) THEN
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync RAISE EXCEPTION 'Duplicate Global Resource name';
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync INSERT INTO GlobalResources (uidAuthor, sName, sDescription, fEnabled)
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync VALUES (_uidAuthor, _sName, _sDescription, _fEnabled) RETURNING idGlobalRsrc INTO _idGlobalRsrc;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync RETURN _idGlobalRsrc;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync$$ LANGUAGE plpgsql;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- Args: uidAuthor, idGlobalRsrc
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsyncCREATE OR REPLACE function del_globalresource(integer, integer) RETURNS VOID AS $$
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _uidAuthor ALIAS FOR $1;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _idGlobalRsrc ALIAS FOR $2;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Check if record exist
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync IF NOT EXISTS(SELECT * FROM GlobalResources WHERE idGlobalRsrc=_idGlobalRsrc AND tsExpire='infinity'::timestamp) THEN
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync RAISE EXCEPTION 'Global resource (%) does not exist', _idGlobalRsrc;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Historize record: GlobalResources
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync UPDATE GlobalResources
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync SET tsExpire=CURRENT_TIMESTAMP,
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync uidAuthor=_uidAuthor
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync WHERE idGlobalRsrc=_idGlobalRsrc AND
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync tsExpire='infinity'::timestamp;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Delete record: GlobalResourceStatuses
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync DELETE FROM GlobalResourceStatuses WHERE idGlobalRsrc=_idGlobalRsrc;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Historize record: TestCaseGlobalRsrcDeps
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync UPDATE TestCaseGlobalRsrcDeps
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync SET tsExpire=CURRENT_TIMESTAMP,
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync uidAuthor=_uidAuthor
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync WHERE idGlobalRsrc=_idGlobalRsrc AND
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync tsExpire='infinity'::timestamp;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync$$ LANGUAGE plpgsql;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync-- Args: uidAuthor, idGlobalRsrc, sName, sDescription, fEnabled
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsyncCREATE OR REPLACE function update_globalresource(integer, integer, text, text, bool) RETURNS VOID AS $$
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _uidAuthor ALIAS FOR $1;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _idGlobalRsrc ALIAS FOR $2;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _sName ALIAS FOR $3;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _sDescription ALIAS FOR $4;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync _fEnabled ALIAS FOR $5;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Hostorize record
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync UPDATE GlobalResources
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync SET tsExpire=CURRENT_TIMESTAMP
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync WHERE idGlobalRsrc=_idGlobalRsrc AND
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync tsExpire='infinity'::timestamp;
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Check if Global Resource name is unique
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync IF EXISTS(SELECT * FROM GlobalResources
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync WHERE sName=_sName AND
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync tsExpire='infinity'::timestamp) THEN
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync RAISE EXCEPTION 'Duplicate Global Resource name';
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync -- Add new record
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync INSERT INTO GlobalResources(uidAuthor, idGlobalRsrc, sName, sDescription, fEnabled)
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync VALUES (_uidAuthor, _idGlobalRsrc, _sName, _sDescription, _fEnabled);
cf22150eaeeb72431bf1cf65c309a431454fb22bvboxsync$$ LANGUAGE plpgsql;