useraccount.pgsql revision cf22150eaeeb72431bf1cf65c309a431454fb22b
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- VBox Test Manager Database Stored Procedures - UserAccounts.
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- Copyright (C) 2012-2014 Oracle Corporation
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- This file is part of VirtualBox Open Source Edition (OSE), as
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- available from http://www.virtualbox.org. This file is free software;
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- you can redistribute it and/or modify it under the terms of the GNU
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- General Public License (GPL) as published by the Free Software
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- Foundation, in version 2 as it comes in the "COPYING" file of the
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- The contents of this file may alternatively be used under the terms
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- of the Common Development and Distribution License Version 1.0
fcae7923a3c756b333f1e33eba002edf4448fb54vboxsync-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- VirtualBox OSE distribution, in which case the provisions of the
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- CDDL are applicable instead of those of the GPL.
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- You may elect to license modified versions of this file under the
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- terms and conditions of either the GPL or the CDDL or both.
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync\set ON_ERROR_STOP 1
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync\connect testmanager;
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- Checks if the user name and login name are unique, ignoring a_uidIgnore.
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- Raises exception if duplicates are found.
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync-- @internal
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsyncCREATE OR REPLACE FUNCTION UserAccountLogic_checkUniqueUser(a_sUsername TEXT, a_sLoginName TEXT, a_uidIgnore INTEGER)
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync RETURNS VOID AS $$
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync v_cRows INTEGER;
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync -- sUserName
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync SELECT COUNT(*) INTO v_cRows
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync WHERE sUsername = a_sUsername
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync AND tsExpire = 'infinity'::TIMESTAMP
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync AND uid <> a_uidIgnore;
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync IF v_cRows <> 0 THEN
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync RAISE EXCEPTION 'Duplicate user name "%" (% times)', a_sUsername, v_cRows;
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync -- sLoginName
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync SELECT COUNT(*) INTO v_cRows
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync WHERE sLoginName = a_sLoginName
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync AND tsExpire = 'infinity'::TIMESTAMP
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync AND uid <> a_uidIgnore;
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync IF v_cRows <> 0 THEN
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync RAISE EXCEPTION 'Duplicate login name "%" (% times)', a_sUsername, v_cRows;
d2236df2116a3cbe8b17c567f4a9c2281733a956vboxsync$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION UserAccountLogic_historizeEntry(a_uid INTEGER, a_tsExpire TIMESTAMP WITH TIME ZONE)
RAISE EXCEPTION 'Integrity error in UserAccounts: % current rows with uid=%d', v_cUpdatedRows, a_uid;
CREATE OR REPLACE FUNCTION UserAccountLogic_addEntry(a_uidAuthor INTEGER, a_sUsername TEXT, a_sEmail TEXT, a_sFullName TEXT,
CREATE OR REPLACE FUNCTION UserAccountLogic_editEntry(a_uidAuthor INTEGER, a_uid INTEGER, a_sUsername TEXT, a_sEmail TEXT,
CREATE OR REPLACE FUNCTION UserAccountLogic_delEntry(a_uidAuthor INTEGER, a_uid INTEGER) RETURNS VOID AS $$
IF v_Row.tsEffective < v_tsEffective THEN
v_Row.tsEffective = v_tsEffective;
v_Row.tsExpire = CURRENT_TIMESTAMP;