Merge branch 'sql-boilerplate-for-keycloak' into 'main'main

CIPs match with Keycloak

See merge request dev/vdi!9

3 files changed, 110 insertions, 36 deletions

diff --git a/database/boilerplate.sql b/database/boilerplate.sql
index 5e8771b..c820757 100644
--- a/database/boilerplate.sql
+++ b/database/boilerplate.sql
@@ -1,17 +1,83 @@
 --Garbage users for testing
-INSERT INTO Users (CIP, Name, Surname)
+INSERT INTO vmdb.Users (CIP, Name, Surname)
 VALUES
-  ('wilj5678', 'William',   'Johnson'),
-  ('ands4321', 'Sandra',    'Anderson'),
-  ('patr9876', 'Robert',    'Pattinson'),
-  ('watk5555', 'Katherine', 'Watson'),
-  ('smib1704', 'Bill',      'Smith'),
-  ('hemc3852', 'Chris',     'Hemsworth'),
-  ('doej1234', 'John',      'Doe'),
-  ('johm5678', 'Mary',      'Johnson');
+  ('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 Class (ClassNb, Title)
+INSERT INTO vmdb.Class (ClassNb, Title)
 VALUES
   ('GEN240', 'General Physics'),
   ('GIF350', 'Graphic Design Fundamentals'),
@@ -30,7 +96,7 @@ VALUES
   ('GIF232', 'Containerization and linux administration');
 
 -- Create VMs
-INSERT INTO VMs (vmID, imageName, Name, OS, Description, ForExams)
+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),
@@ -50,7 +116,7 @@ VALUES
 
 
 -- Assign VMs to classes
-INSERT INTO BelongsTo (ID, ClassNb)
+INSERT INTO vmdb.BelongsTo (ID, ClassNb)
 VALUES
   (1,  'GEN132'), -- WindowsVM1 for Solidworks
   (2,  'GEN246'), -- winAltium for circuit design
@@ -68,26 +134,30 @@ VALUES
   (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;
 
--- Add users to random classes
-INSERT INTO UserAttendance (CIP, ClassNb)
-VALUES
-  ('wilj5678', 'GEN240'),
-  ('wilj5678', 'MAT215'),
-  ('ands4321', 'GIF350'),
-  ('patr9876', 'INF400'),
-  ('watk5555', 'CHE110'),
-  ('smib1704', 'ENG301'),
-  ('hemc3852', 'INF400'),
-  ('doej1234', 'HIS202'),
-  ('johm5678', 'ENG301');
+-- 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 Users (CIP, Name, Surname)
+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 UserAttendance (CIP, ClassNb)
+INSERT INTO vmdb.UserAttendance (CIP, ClassNb)
 SELECT 'chab1704', ClassNb
-FROM Class;
+FROM vmdb.Class;
diff --git a/database/init.sql b/database/init.sql
index cdef935..9b9631c 100644
--- a/database/init.sql
+++ b/database/init.sql
@@ -1,17 +1,19 @@
-CREATE TABLE Users (
+create schema if not exists vmdb;
+
+CREATE TABLE vmdb.Users (
   CIP CHAR(8) NOT NULL,
   Name VARCHAR NOT NULL,
   Surname VARCHAR NOT NULL,
   PRIMARY KEY (CIP)
 );
 
-CREATE TABLE Class (
+CREATE TABLE vmdb.Class (
   ClassNb CHAR(6) NOT NULL,
   Title VARCHAR NOT NULL,
   PRIMARY KEY (ClassNb)
 );
 
-CREATE TABLE VMs (
+CREATE TABLE vmdb.VMs (
   vmID INT NOT NULL,
   imageName VARCHAR NOT NULL,
   Name VARCHAR NOT NULL,
@@ -22,19 +24,19 @@ CREATE TABLE VMs (
   UNIQUE (imageName)
 );
 
-CREATE TABLE UserAttendance (
+CREATE TABLE vmdb.UserAttendance (
   CIP CHAR(8) NOT NULL,
   ClassNb CHAR(6) NOT NULL,
   PRIMARY KEY (CIP, ClassNb),
-  FOREIGN KEY (CIP) REFERENCES Users(CIP),
-  FOREIGN KEY (ClassNb) REFERENCES Class(ClassNb)
+  FOREIGN KEY (CIP) REFERENCES vmdb.Users(CIP),
+  FOREIGN KEY (ClassNb) REFERENCES vmdb.Class(ClassNb)
 );
 
-CREATE TABLE BelongsTo (
+CREATE TABLE vmdb.BelongsTo (
   ID INT NOT NULL,
   ClassNb CHAR(6) NOT NULL,
   PRIMARY KEY (ID, ClassNb),
-  FOREIGN KEY (ID) REFERENCES VMs(vmID),
-  FOREIGN KEY (ClassNb) REFERENCES Class(ClassNb)
+  FOREIGN KEY (ID) REFERENCES vmdb.VMs(vmID),
+  FOREIGN KEY (ClassNb) REFERENCES vmdb.Class(ClassNb)
 );
 
diff --git a/docker-compose.yml b/docker-compose.yml
index 1bdd506..b1877cb 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -28,6 +28,8 @@ services:
       context: ./database
     networks:
       - virtlab-net
+    ports:
+      - 5432:5432
     volumes:
       - virtlab-db:/var/lib/postgres/data