1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
|
-- $Id: useraccount.pgsql $
--- @file
-- VBox Test Manager Database Stored Procedures - UserAccounts.
--
--
-- Copyright (C) 2012-2023 Oracle and/or its affiliates.
--
-- This file is part of VirtualBox base platform packages, as
-- available from https://www.virtualbox.org.
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation, in version 3 of the
-- License.
--
-- This program is distributed in the hope that it will be useful, but
-- WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, see <https://www.gnu.org/licenses>.
--
-- The contents of this file may alternatively be used under the terms
-- of the Common Development and Distribution License Version 1.0
-- (CDDL), a copy of it is provided in the "COPYING.CDDL" file included
-- in the VirtualBox 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.
--
-- SPDX-License-Identifier: GPL-3.0-only OR CDDL-1.0
--
\set ON_ERROR_STOP 1
\connect testmanager;
---
-- Checks if the user name and login name are unique, ignoring a_uidIgnore.
-- Raises exception if duplicates are found.
--
-- @internal
--
CREATE OR REPLACE FUNCTION UserAccountLogic_checkUniqueUser(a_sUsername TEXT, a_sLoginName TEXT, a_uidIgnore INTEGER)
RETURNS VOID AS $$
DECLARE
v_cRows INTEGER;
BEGIN
-- sUserName
SELECT COUNT(*) INTO v_cRows
FROM Users
WHERE sUsername = a_sUsername
AND tsExpire = 'infinity'::TIMESTAMP
AND uid <> a_uidIgnore;
IF v_cRows <> 0 THEN
RAISE EXCEPTION 'Duplicate user name "%" (% times)', a_sUsername, v_cRows;
END IF;
-- sLoginName
SELECT COUNT(*) INTO v_cRows
FROM Users
WHERE sLoginName = a_sLoginName
AND tsExpire = 'infinity'::TIMESTAMP
AND uid <> a_uidIgnore;
IF v_cRows <> 0 THEN
RAISE EXCEPTION 'Duplicate login name "%" (% times)', a_sUsername, v_cRows;
END IF;
END;
$$ LANGUAGE plpgsql;
---
-- Check that the user account exists.
-- Raises exception if it doesn't.
--
-- @internal
--
CREATE OR REPLACE FUNCTION UserAccountLogic_checkExists(a_uid INTEGER) RETURNS VOID AS $$
DECLARE
v_cUpdatedRows INTEGER;
BEGIN
IF NOT EXISTS( SELECT *
FROM Users
WHERE uid = a_uid
AND tsExpire = 'infinity'::TIMESTAMP ) THEN
RAISE EXCEPTION 'User with ID % does not currently exist', a_uid;
END IF;
END;
$$ LANGUAGE plpgsql;
---
-- Historize a row.
-- @internal
--
CREATE OR REPLACE FUNCTION UserAccountLogic_historizeEntry(a_uid INTEGER, a_tsExpire TIMESTAMP WITH TIME ZONE)
RETURNS VOID AS $$
DECLARE
v_cUpdatedRows INTEGER;
BEGIN
UPDATE Users
SET tsExpire = a_tsExpire
WHERE uid = a_uid
AND tsExpire = 'infinity'::TIMESTAMP;
GET DIAGNOSTICS v_cUpdatedRows = ROW_COUNT;
IF v_cUpdatedRows <> 1 THEN
IF v_cUpdatedRows = 0 THEN
RAISE EXCEPTION 'User with ID % does not currently exist', a_uid;
END IF;
RAISE EXCEPTION 'Integrity error in UserAccounts: % current rows with uid=%d', v_cUpdatedRows, a_uid;
END IF;
END;
$$ LANGUAGE plpgsql;
---
-- Adds a new user.
--
CREATE OR REPLACE FUNCTION UserAccountLogic_addEntry(a_uidAuthor INTEGER, a_sUsername TEXT, a_sEmail TEXT, a_sFullName TEXT,
a_sLoginName TEXT, a_fReadOnly BOOLEAN)
RETURNS VOID AS $$
DECLARE
v_cRows INTEGER;
BEGIN
PERFORM UserAccountLogic_checkUniqueUser(a_sUsername, a_sLoginName, -1);
INSERT INTO Users(uidAuthor, sUsername, sEmail, sFullName, sLoginName)
VALUES (a_uidAuthor, a_sUsername, a_sEmail, a_sFullName, a_sLoginName);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION UserAccountLogic_editEntry(a_uidAuthor INTEGER, a_uid INTEGER, a_sUsername TEXT, a_sEmail TEXT,
a_sFullName TEXT, a_sLoginName TEXT, a_fReadOnly BOOLEAN)
RETURNS VOID AS $$
BEGIN
PERFORM UserAccountLogic_checkExists(a_uid);
PERFORM UserAccountLogic_checkUniqueUser(a_sUsername, a_sLoginName, a_uid);
PERFORM UserAccountLogic_historizeEntry(a_uid, CURRENT_TIMESTAMP);
INSERT INTO Users (uid, uidAuthor, sUsername, sEmail, sFullName, sLoginName, fReadOnly)
VALUES (a_uid, a_uidAuthor, a_sUsername, a_sEmail, a_sFullName, a_sLoginName, a_fReadOnly);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION UserAccountLogic_delEntry(a_uidAuthor INTEGER, a_uid INTEGER) RETURNS VOID AS $$
DECLARE
v_Row Users%ROWTYPE;
v_tsEffective TIMESTAMP WITH TIME ZONE;
BEGIN
--
-- 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 Users
WHERE uid = a_uid
AND tsExpire = 'infinity'::TIMESTAMP;
v_tsEffective := CURRENT_TIMESTAMP - INTERVAL '1 microsecond';
IF v_Row.tsEffective < v_tsEffective THEN
PERFORM UserAccountLogic_historizeEntry(a_uid, v_tsEffective);
v_Row.tsEffective = v_tsEffective;
v_Row.tsExpire = CURRENT_TIMESTAMP;
INSERT INTO Users VALUES (v_Row.*);
ELSE
PERFORM UserAccountLogic_historizeEntry(a_uid, CURRENT_TIMESTAMP);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'User with ID % does not currently exist', a_uid;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'Integrity error in UserAccounts: Too many current rows for %', a_uid;
END;
$$ LANGUAGE plpgsql;
|
