316 lines
15 KiB
SQL
316 lines
15 KiB
SQL
-- =====================================================================
|
||
-- PIVOT 1.1.0 – CENTRALE database (structuur schooljaar 2026-27)
|
||
--
|
||
-- Bevat wat voor ALLE scholen geldt:
|
||
-- Scholen de scholen (+ hun koppelingen, versleuteld)
|
||
-- Gebruikers één account per persoon, voor alle scholen
|
||
-- Veiligheidsgroepen 1 = Administrator, 2 = Gebruiker
|
||
-- Onderdelen de onderdelen van PIVOT waarop je rechten kan geven
|
||
-- Gebruiker_rechten wie mag welk onderdeel, in welke school
|
||
-- Logs bezochte pagina's en acties
|
||
-- Bugs, Status gemelde bugs
|
||
--
|
||
-- De naam van deze database staat in config.ini › [database] › centrale_database.
|
||
-- Dit bestand is gegenereerd uit tenancy.py en rechten.py; PIVOT maakt
|
||
-- dezelfde tabellen ook zelf aan bij het opstarten (python beheer.py init).
|
||
-- =====================================================================
|
||
|
||
-- USE `pivot`;
|
||
|
||
-- Veiligheidscontrole: stop als deze database nog OUDE tabellen met dezelfde naam bevat
|
||
-- (bv. 'scholen' van PIVOT 1.1.0). MySQL op Windows maakt geen onderscheid tussen
|
||
-- hoofdletters en kleine letters, dus 'Scholen' en 'scholen' zijn dezelfde tabel.
|
||
DROP PROCEDURE IF EXISTS pivot_controle_oude_tabellen;
|
||
DELIMITER //
|
||
CREATE PROCEDURE pivot_controle_oude_tabellen()
|
||
BEGIN
|
||
DECLARE oud TEXT;
|
||
SELECT GROUP_CONCAT(n.tabel SEPARATOR ', ') INTO oud
|
||
FROM (SELECT 'scholen' AS tabel, 'school_id' AS kolom UNION ALL
|
||
SELECT 'gebruikers', 'gebruiker_id' UNION ALL SELECT 'logs', 'log_id' UNION ALL
|
||
SELECT 'status', 'bugstatus_id' UNION ALL SELECT 'bugs', 'bug_id') n
|
||
WHERE EXISTS (SELECT 1 FROM information_schema.TABLES t
|
||
WHERE t.TABLE_SCHEMA = DATABASE() AND LOWER(t.TABLE_NAME) = n.tabel)
|
||
AND NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS c
|
||
WHERE c.TABLE_SCHEMA = DATABASE() AND LOWER(c.TABLE_NAME) = n.tabel
|
||
AND LOWER(c.COLUMN_NAME) = n.kolom);
|
||
IF oud IS NOT NULL THEN
|
||
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT =
|
||
'STOP: deze database bevat nog oude PIVOT-tabellen (scholen/gebruikers/logs/status/bugs). Kies een nieuwe, lege database.';
|
||
END IF;
|
||
END //
|
||
DELIMITER ;
|
||
CALL pivot_controle_oude_tabellen();
|
||
DROP PROCEDURE pivot_controle_oude_tabellen;
|
||
|
||
CREATE TABLE IF NOT EXISTS Scholen (
|
||
School_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Code VARCHAR(32) NOT NULL,
|
||
School_naam VARCHAR(255) NOT NULL,
|
||
Smartschool_platform_url VARCHAR(255) NULL,
|
||
Adres VARCHAR(255) NULL,
|
||
Instellingsnummer VARCHAR(20) NULL,
|
||
Informat_instellingsnummer VARCHAR(500) NULL,
|
||
Onderwijsniveau VARCHAR(20) NULL,
|
||
Domein VARCHAR(500) NOT NULL DEFAULT '',
|
||
Hostnamen VARCHAR(500) NOT NULL DEFAULT '',
|
||
Actief TINYINT NOT NULL DEFAULT 1,
|
||
Db_naam VARCHAR(128) NOT NULL,
|
||
Instellingen MEDIUMTEXT NULL,
|
||
Google_sleutel MEDIUMTEXT NULL,
|
||
Leerlingenaantal INT NULL,
|
||
Leerlingenaantal_sync DATETIME NULL,
|
||
Leerlingenaantal_detail TEXT NULL,
|
||
Bijgewerkt DATETIME(6) NULL,
|
||
Bijgewerkt_door VARCHAR(255) NULL,
|
||
UNIQUE KEY uq_scholen_code (Code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Veiligheidsgroepen (
|
||
Veiligheidsgroep TINYINT NOT NULL PRIMARY KEY,
|
||
Naam VARCHAR(50) NOT NULL,
|
||
Omschrijving VARCHAR(255) NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Gebruikers (
|
||
Gebruiker_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Gebruikersnaam VARCHAR(100) NOT NULL,
|
||
Veiligheidsgroep TINYINT NOT NULL DEFAULT 2,
|
||
Passwoordhash VARCHAR(255) NULL,
|
||
Emailadres VARCHAR(255) NULL,
|
||
Last_login DATETIME NULL,
|
||
IP_laatste_login VARCHAR(45) NULL,
|
||
Laatste_school VARCHAR(32) NULL,
|
||
Actief TINYINT NOT NULL DEFAULT 1,
|
||
UNIQUE KEY uq_gebruikersnaam (Gebruikersnaam),
|
||
UNIQUE KEY uq_emailadres (Emailadres),
|
||
CONSTRAINT fk_gebruikers_groep FOREIGN KEY (Veiligheidsgroep)
|
||
REFERENCES Veiligheidsgroepen (Veiligheidsgroep)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Onderdelen (
|
||
Onderdeel_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Code VARCHAR(50) NOT NULL,
|
||
Naam VARCHAR(255) NOT NULL,
|
||
Categorie VARCHAR(50) NOT NULL,
|
||
Volgorde INT NOT NULL DEFAULT 0,
|
||
UNIQUE KEY uq_onderdeel_code (Code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Gebruiker_rechten (
|
||
Gebruiker_ID INT NOT NULL,
|
||
School_ID INT NOT NULL,
|
||
Onderdeel_ID INT NOT NULL,
|
||
Toegekend_op DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
Toegekend_door VARCHAR(100) NULL,
|
||
PRIMARY KEY (Gebruiker_ID, School_ID, Onderdeel_ID),
|
||
KEY idx_rechten_school (School_ID),
|
||
KEY idx_rechten_onderdeel (Onderdeel_ID),
|
||
CONSTRAINT fk_rechten_gebruiker FOREIGN KEY (Gebruiker_ID)
|
||
REFERENCES Gebruikers (Gebruiker_ID) ON DELETE CASCADE,
|
||
CONSTRAINT fk_rechten_school FOREIGN KEY (School_ID)
|
||
REFERENCES Scholen (School_ID) ON DELETE CASCADE,
|
||
CONSTRAINT fk_rechten_onderdeel FOREIGN KEY (Onderdeel_ID)
|
||
REFERENCES Onderdelen (Onderdeel_ID) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Handleidingen (
|
||
Handleiding_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Slug VARCHAR(100) NOT NULL,
|
||
Titel VARCHAR(200) NOT NULL,
|
||
Categorie VARCHAR(100) NULL,
|
||
Samenvatting VARCHAR(300) NULL,
|
||
Inhoud MEDIUMTEXT NULL,
|
||
Zichtbaar VARCHAR(10) NOT NULL DEFAULT 'iedereen',
|
||
Volgorde INT NOT NULL DEFAULT 0,
|
||
Gewijzigd_op DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
Gewijzigd_door VARCHAR(100) NULL,
|
||
UNIQUE KEY uq_handleiding_slug (Slug)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Handleidingen_versies (
|
||
Versie_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Handleiding_ID INT NOT NULL,
|
||
Titel VARCHAR(200) NOT NULL,
|
||
Inhoud MEDIUMTEXT NULL,
|
||
Opgeslagen_op DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
Door VARCHAR(100) NULL,
|
||
KEY idx_versie_handleiding (Handleiding_ID, Opgeslagen_op),
|
||
CONSTRAINT fk_versie_handleiding FOREIGN KEY (Handleiding_ID)
|
||
REFERENCES Handleidingen (Handleiding_ID) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Leerlingenaantal_tellingen (
|
||
Telling_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
School_ID INT NOT NULL,
|
||
Peildatum DATE NOT NULL,
|
||
Schooljaar VARCHAR(10) NOT NULL,
|
||
Aantal INT NOT NULL,
|
||
Detail TEXT NULL,
|
||
Opgevraagd_op DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
Door VARCHAR(100) NULL,
|
||
UNIQUE KEY uq_telling (School_ID, Peildatum),
|
||
KEY idx_telling_datum (Peildatum),
|
||
CONSTRAINT fk_telling_school FOREIGN KEY (School_ID)
|
||
REFERENCES Scholen (School_ID) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Personeelsaantal_tellingen (
|
||
Telling_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
School_ID INT NOT NULL,
|
||
Peildatum DATE NOT NULL,
|
||
Schooljaar VARCHAR(10) NOT NULL,
|
||
Aantal INT NOT NULL,
|
||
Detail TEXT NULL,
|
||
Opgevraagd_op DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
Door VARCHAR(100) NULL,
|
||
UNIQUE KEY uq_ptelling (School_ID, Peildatum),
|
||
KEY idx_ptelling_datum (Peildatum),
|
||
CONSTRAINT fk_ptelling_school FOREIGN KEY (School_ID)
|
||
REFERENCES Scholen (School_ID) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Personeelsaantal_huidig (
|
||
School_ID INT NOT NULL PRIMARY KEY,
|
||
Aantal INT NOT NULL,
|
||
Detail TEXT NULL,
|
||
Gesynchroniseerd DATETIME NOT NULL,
|
||
CONSTRAINT fk_phuidig_school FOREIGN KEY (School_ID)
|
||
REFERENCES Scholen (School_ID) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS PIVOT_instellingen (
|
||
Sleutel VARCHAR(50) NOT NULL PRIMARY KEY,
|
||
Waarde VARCHAR(255) NULL,
|
||
Bijgewerkt DATETIME NULL,
|
||
Door VARCHAR(100) NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS IP_blokkades (
|
||
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Reeks VARCHAR(64) NOT NULL,
|
||
Soort VARCHAR(10) NOT NULL DEFAULT 'blokkeren',
|
||
Opmerking VARCHAR(255) NULL,
|
||
Scholen VARCHAR(1000) NULL,
|
||
Aangemaakt DATETIME NOT NULL,
|
||
Door VARCHAR(100) NULL,
|
||
Gebruiker_ID INT NULL,
|
||
Aantal_geweigerd INT NOT NULL DEFAULT 0,
|
||
Laatst_geweigerd DATETIME NULL,
|
||
UNIQUE KEY uq_ip_reeks (Reeks, Soort)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS GoPro_verzameltabel (
|
||
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
School_code VARCHAR(32) NOT NULL,
|
||
School_naam VARCHAR(255) NULL,
|
||
Person_ID VARCHAR(64) NOT NULL,
|
||
Voornaam VARCHAR(100) NULL,
|
||
Achternaam VARCHAR(100) NULL,
|
||
Email VARCHAR(255) NULL,
|
||
UPN VARCHAR(255) NULL,
|
||
Instellingsnummer VARCHAR(20) NULL,
|
||
Functie VARCHAR(100) NULL,
|
||
Rol VARCHAR(100) NULL,
|
||
Startdatum DATE NULL,
|
||
Einddatum DATE NULL,
|
||
Telefoon VARCHAR(50) NULL,
|
||
Toegevoegd DATETIME NOT NULL,
|
||
Door VARCHAR(255) NULL,
|
||
Soort VARCHAR(10) NOT NULL DEFAULT 'groen', -- groen = in de CSV, genegeerd = rode lijst, verzonden = al naar GO! Pro
|
||
Verzonden DATETIME NULL,
|
||
Verzonden_door VARCHAR(255) NULL,
|
||
UNIQUE KEY uq_gopro_verz (School_code, Person_ID),
|
||
KEY idx_gopro_verz_email (Email),
|
||
KEY idx_gopro_verz_soort (Soort, School_naam)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Koppeling_tests (
|
||
School_ID INT NOT NULL,
|
||
Onderdeel VARCHAR(50) NOT NULL,
|
||
Ok TINYINT NULL,
|
||
Detail VARCHAR(500) NULL,
|
||
Getest_op DATETIME NOT NULL,
|
||
PRIMARY KEY (School_ID, Onderdeel),
|
||
CONSTRAINT fk_ktest_school FOREIGN KEY (School_ID) REFERENCES Scholen (School_ID) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Logs (
|
||
Log_ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
Bezochte_url VARCHAR(2048) NOT NULL,
|
||
Gebruiker_ID INT NULL,
|
||
School_ID INT NULL,
|
||
Actie VARCHAR(255) NULL,
|
||
IP VARCHAR(45) NULL,
|
||
KEY idx_logs_tijd (Timestamp),
|
||
KEY idx_logs_gebruiker (Gebruiker_ID),
|
||
CONSTRAINT fk_logs_gebruiker FOREIGN KEY (Gebruiker_ID)
|
||
REFERENCES Gebruikers (Gebruiker_ID) ON DELETE SET NULL,
|
||
CONSTRAINT fk_logs_school FOREIGN KEY (School_ID)
|
||
REFERENCES Scholen (School_ID) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Status (
|
||
Bugstatus_ID TINYINT NOT NULL PRIMARY KEY,
|
||
Omschrijving VARCHAR(50) NOT NULL,
|
||
UNIQUE KEY uq_status_omschrijving (Omschrijving)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE IF NOT EXISTS Bugs (
|
||
Bug_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
Timestamp DATETIME NOT NULL,
|
||
Omschrijving TEXT NOT NULL,
|
||
Foutcode VARCHAR(100) NULL,
|
||
Bugstatus_ID TINYINT NOT NULL DEFAULT 1,
|
||
Gebruiker_ID INT NULL,
|
||
Gebruikersnaam VARCHAR(255) NULL,
|
||
School_ID INT NULL,
|
||
Gemeld_op DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
KEY idx_bugs_status (Bugstatus_ID),
|
||
CONSTRAINT fk_bugs_status FOREIGN KEY (Bugstatus_ID) REFERENCES Status (Bugstatus_ID),
|
||
CONSTRAINT fk_bugs_gebruiker FOREIGN KEY (Gebruiker_ID)
|
||
REFERENCES Gebruikers (Gebruiker_ID) ON DELETE SET NULL,
|
||
CONSTRAINT fk_bugs_school FOREIGN KEY (School_ID)
|
||
REFERENCES Scholen (School_ID) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
INSERT IGNORE INTO Veiligheidsgroepen (Veiligheidsgroep, Naam, Omschrijving) VALUES
|
||
(1, 'Administrator', 'Alle rechten in alle scholen; beheert gebruikers, rechten en scholen'),
|
||
(2, 'Gebruiker', 'Enkel de rechten die per school zijn aangevinkt');
|
||
|
||
INSERT IGNORE INTO Onderdelen (Code, Naam, Categorie, Volgorde) VALUES
|
||
('eigendom_bekijken', 'Eigendom bekijken', 'Chromebooks', 10),
|
||
('toestellen_uitdelen', 'Toestellen uitdelen en inleveren', 'Chromebooks', 20),
|
||
('toestellen_beheren', 'Toestelleninventaris beheren', 'Chromebooks', 30),
|
||
('toestel_blokkeren', 'Chromebook blokkeren / deblokkeren', 'Chromebooks', 40),
|
||
('herstellingen', 'Herstellingen', 'Chromebooks', 50),
|
||
('signpost', 'Signpost-toestellen', 'Chromebooks', 60),
|
||
('leerlingen_bekijken', 'Leerlingen bekijken en bewerken', 'Leerlingen', 70),
|
||
('leerlingen_synchroniseren', 'Leerlingen toevoegen en synchroniseren', 'Leerlingen', 80),
|
||
('google_wachtwoord', 'Google-wachtwoord resetten', 'Google', 90),
|
||
('google_accounts', 'Google-accounts aanmaken', 'Google', 100),
|
||
('tweestaps_codes', '2FA: back-upcodes tonen / aanmaken', 'Google', 110),
|
||
('tweestaps_uitzetten', '2FA tijdelijk uitzetten (uitzondering)', 'Google', 120),
|
||
('google_licenties', 'Google-licenties', 'Google', 130),
|
||
('gmail_delegatie', 'Mailbox-delegatie', 'Google', 140),
|
||
('smartschool', 'Smartschool-wachtwoorden & info', 'Smartschool', 150),
|
||
('smartschool_activiteit', 'Activiteit co-accounts & berichten', 'Smartschool', 160),
|
||
('afwezigheden', 'Afwezigheden leerlingen (B-codes)', 'Smartschool', 170),
|
||
('skore', 'Skore-synchronisatie (starten en plannen)', 'Smartschool', 180),
|
||
('personeel_directie', 'Personeel: aanvragen goedkeuren (directie)', 'Personeel', 190),
|
||
('personeel_secretariaat', 'Personeel: dagoverzicht (secretariaat)', 'Personeel', 200),
|
||
('qr_codes', 'QR-code generator', 'Tools', 210),
|
||
('co2', 'CO2-sensoren', 'IoT', 220),
|
||
('leerlingenaantal', 'Leerlingenaantallen (alle scholen)', 'Scholengroep', 230),
|
||
('bugs_bekijken', 'Bugs bekijken', 'Beheer', 240),
|
||
('bugs_beheren', 'Status van bugs wijzigen', 'Beheer', 250),
|
||
('nieuw_schooljaar', 'Nieuw schooljaar (accounts & toestellen)', 'Beheer', 260),
|
||
('gebruikersbeheer', 'Gebruikers & rechten van deze school', 'PIVOT', 270),
|
||
('koppelingen', 'Koppelingen van deze school', 'PIVOT', 280);
|
||
|
||
INSERT IGNORE INTO Status (Bugstatus_ID, Omschrijving) VALUES
|
||
(1, 'Nieuw'),
|
||
(2, 'In behandeling'),
|
||
(3, 'Fixed');
|