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
|
--Garbage users for testing
INSERT INTO vmdb.Users (CIP, Name, Surname)
VALUES
('ploe1701', 'Éric', 'Plourde'),
('boof2101', 'François', 'Boone,'),
('farg2101', 'Ghania Sarah', 'Farhi,'),
('amae2901', 'Élodie', 'Amar'),
('robw1901', 'William', 'Roberge'),
('guea0902', 'Alexis', 'Guérard'),
('arnp3401', 'Prescilla', 'Arnould'),
('durp2003', 'Philippe', 'Durette'),
('laft1301', 'Tristan', 'Lafontaine'),
('puic2701', 'Chantal', 'Puig'),
('boua2511', 'Audrey', 'Boucher-Genesse'),
('sehk2201', 'Kenza', 'Sehnani'),
('brel0901', 'Laurent', 'Breton'),
('sinn1901', 'Newton', 'Sindayigaya-Simon'),
('cany2101', 'Yorman Esneider', 'Cansimance Castro'),
('caua1101', 'Arnaud', 'Cauchon'),
('cyrm2101', 'Marc-André', 'Cyr'),
('dufd2802', 'Denis', 'Dufresne'),
('tria1001', 'Alexandre', 'Trial'),
('labg0902', 'Gabriel', 'Labrecque'),
('lavd2311', 'Daniel', 'Lavallée'),
('gell3101', 'Louis-Xavier', 'Gélinas'),
('trew1501', 'William', 'Tremblay'),
('boie0601', 'Emile', 'Bois'),
('labc0301', 'Cédric', 'Labrecque'),
('gerz0501', 'Zachari', 'Gervais'),
('hofm2702', 'Max', 'Hofheinz'),
('cake0801', 'Enes', 'Caku'),
('houy2303', 'Youlian', 'Houehounou'),
('bele0801', 'Étienne', 'Bellerive-Blais'),
('bild2707', 'Dannick', 'Bilodeau'),
('stds2101', 'Sébastien', 'St-Denis'),
('roua0701', 'Alexandre', 'Rousseau'),
('keib3201', 'Brenda', 'Keimgou Tchio'),
('hasa3302', 'Ali', 'Hassoun'),
('turv5324', 'Victor', 'Turcotte'),
('carv0701', 'Vincent', 'Caron-Thibault'),
('aubo1502', 'Olivier', 'Aubry'),
('bils2704', 'Samuel', 'Bilodeau'),
('clof1603', 'Félix-Olivier', 'Cloutier'),
('sowa0801', 'Ali', 'Sow'),
('laus1801', 'Sylvain', 'Lauzier'),
('ronk2602', 'Kevin', 'Rondeau'),
('trus1706', 'Simon', 'Trudeau'),
('maif1401', 'Frédéric', 'Mailhot'),
('lals1003', 'Simon', 'Lalancette'),
('cars1804', 'Serge', 'Caron'),
('lede2401', 'Eric', 'Leduc'),
('aubj1202', 'Joseanne', 'Aubut'),
('kilv1201', 'Vincent', 'Kilanowski'),
('gaud1401', 'Daniel', 'Gaucher'),
('cotr3901', 'Roxanne', 'Côté'),
('beab1802', 'Bernard', 'Beaulieu'),
('dufj2908', 'Joël', 'Dufault'),
('stao0901', 'Olivier', 'St-Amand'),
('lant1401', 'Tristan', 'Langis'),
('barr1306', 'Raphaël', 'Barrette'),
('lamg0502', 'Gabriel', 'Lamontagne'),
('rerm1001', 'Malik', 'Rerhaib'),
('lavm1927', 'Michaël', 'Lavigne'),
('lavm2134', 'Mathias', 'Lavoie-Rivard'),
('pagm1302', 'Maxime', 'Pagé'),
('laby1302', 'Youcef', 'Labassi'),
('lanj2131', 'Jordan', 'Lanouette'),
('thip0901', 'Pape Malick', 'Thioye'),
('keif1201', 'Fatoumata', 'Keita'),
('bele1103', 'Émile', 'Bélanger'),
('sevm1802', 'Mathieu', 'Sévégny'),
('pald2501', 'Domingo', 'Palao Munoz'),
('canb1801', 'Benjamin', 'Canuel'),
('jace1402', 'Emile', 'Jacques-Tighe'),
('jans2001', 'Simon', 'Janelle-Bombardier'),
('lebg2708', 'Gabriel', 'Leblanc-Fontaine'),
('bour0703', 'Raphaël', 'Bouchard');
--Garbage classes for testing
INSERT INTO vmdb.Class (ClassNb, Title)
VALUES
('GEN240', 'General Physics'),
('GIF350', 'Graphic Design Fundamentals'),
('INF400', 'Advanced Database Systems'),
('MAT215', 'Linear Algebra'),
('CHE110', 'Chemistry for Beginners'),
('ENG301', 'English Literature'),
('HIS202', 'World History'),
('GEN132', 'CAD & CAM Design'),
('GEN246', 'Circuit Design'),
('GIF392', 'Cryptography and Computer Security'),
('GIF356', 'Design Patterns and Java Development'),
('GIF298', 'UI Development with & Test Driven Development'),
('GIF291', 'Distributed Systems and web development'),
('MAT391', 'Group Theory and data recovery'),
('GIF232', 'Containerization and linux administration');
-- Create VMs
INSERT INTO vmdb.VMs (vmID, imageName, Name, OS, Description, ForExams)
VALUES
(1, 'win10-01', 'WindowsVM1', 'Windows 10', '3D modeling environment', FALSE),
(2, 'win10-04', 'WinAltium', 'Windows 10', 'Circuit design platform', TRUE),
(3, 'knoppix-01', 'KnoppixVM1', 'Knoppix', 'Penetration testing sandbox', FALSE),
(4, 'debian-01', 'DebianVM1', 'Debian 12', 'Development environment', TRUE),
(5, 'win10-02', 'WindowsVM2', 'Windows 10', 'Software testing platform', FALSE),
(6, 'ubuntu-02', 'UbuntuVM2', 'Ubuntu 22', 'Web development environment', TRUE),
(7, 'knoppix-02', 'KnoppixVM2', 'Knoppix', 'Data recovery interface', FALSE),
(8, 'debian-02', 'DebianVM2', 'Debian 12', 'System administration setup', TRUE),
(9, 'win10-gen240', 'PhysicsVM', 'Windows 10', 'Physics simulations', FALSE),
(10, 'ubuntu-gif350', 'DesignVM', 'Ubuntu 22', 'Graphic design projects', TRUE),
(11, 'debian-inf400', 'DatabaseVM', 'Debian 12', 'Database development', TRUE),
(12, 'ubuntu-mat215', 'MathVM', 'Ubuntu 22', 'Linear algebra calculations', FALSE),
(13, 'knoppix-che110', 'ChemistryVM', 'Knoppix', 'Chemistry simulations', FALSE),
(14, 'win10-eng301', 'EnglishVM', 'Windows 10', 'Literary analysis', TRUE),
(15, 'ubuntu-his202', 'HistoryVM', 'Ubuntu 22', 'Historical research', FALSE);
-- Assign VMs to classes
INSERT INTO vmdb.BelongsTo (ID, ClassNb)
VALUES
(1, 'GEN132'), -- WindowsVM1 for Solidworks
(2, 'GEN246'), -- winAltium for circuit design
(3, 'GIF392'), -- knoppix for pen-testing
(4, 'GIF356'), -- DebianVM1 for Java development
(5, 'GIF298'), -- WindowsVM2 for QT development
(6, 'GIF291'), -- UbuntuVM2 for web development
(7, 'MAT391'), -- KnoppixVM2 for data recovery
(8, 'GIF232'), -- DebianVM2 for linux administration
(9, 'GEN240'), -- PhysicsVM for General Physics
(10, 'GIF350'), -- DesignVM for Graphic Design Fundamentals
(11, 'INF400'), -- DatabaseVM for Advanced Database Systems
(12, 'MAT215'), -- MathVM for Linear Algebra
(13, 'CHE110'), -- ChemistryVM for Chemistry for Beginners
(14, 'ENG301'), -- EnglishVM for English Literature
(15, 'HIS202'); -- HistoryVM for World History
-- First, create a temporary table to hold random class assignments for each user
CREATE TEMP TABLE temp_user_class AS
SELECT DISTINCT ON (u.CIP, c.ClassNb) u.CIP, c.ClassNb
FROM vmdb.Users u
CROSS JOIN LATERAL (
SELECT ClassNb
FROM vmdb.Class
ORDER BY random() -- Randomly select classes
LIMIT 5 -- Limit to 5 random classes per user
) c;
-- Now, insert the data from the temporary table into UserAttendance
INSERT INTO vmdb.UserAttendance (CIP, ClassNb)
SELECT CIP, ClassNb
FROM temp_user_class;
-- Clean up the temporary table
DROP TABLE temp_user_class;
-- Insert me
INSERT INTO vmdb.Users (CIP, Name, Surname)
VALUES ('chab1704', 'Benjamin', 'Chausse');
-- Insert me into the UserAttendance table for all classes (so I can see everything while testing)
INSERT INTO vmdb.UserAttendance (CIP, ClassNb)
SELECT 'chab1704', ClassNb
FROM vmdb.Class;
|
