Een andere benadering van database-automatisering zijn de scheduled events, welke op bepaalde tijden geïnitieerd worden. Zij voorzien een kader waarin een of meer SQL bewerkingen op een tijd gebaseerd schema uitgevoerd zullen worden.

  • Altijd gekoppeld aan een bepaalde tafel
  • Kan ingesteld worden om eenmalig uitgevoerd te worden of herhaaldelijk op ingestelde intervallen

Dit kan van pas komen voor taken die moeten periodiek plaatsvinden, zoals een log rotatie, het genereren van statistieken, of om updates te verwerken.

Een eenvoudig voorbeeld: het archiveren van oude passagiersgegevens

Alle passagiers automatisch verplaatst naar vluchten die 30 dagen oud uit de pax tafel en in een ander archief tabel:

MSSQL

Command(s) completed successfully.

MySQL

DELIMITER //
CREATE EVENT pax_day
ON SCHEDULE EVERY 1 DAY
STARTS '2009-01-14 22:45:00'
ENABLE
DO
BEGIN
    INSERT INTO paxarchive
    SELECT * FROM pax
    WHERE 
        FlightDate <= DATE_SUB(CURRENT_DATE(), 
        INTERVAL 30 DAY);
    DELETE FROM pax 
    WHERE
        FlightDate <= DATE_SUB(CURRENT_DATE(),
        INTERVAL 30 DAY);
END //

(0 row(s) affected)

Om een scheduled event te definiëren, biedt MySQL het commando CREATE EVENT. Deze opdracht moet worden gevolgd door:

  • De naam van de event
  • De planning van de event, (bijvoorbeeld ON SCHEDULE EVERY 1 DAY & STARTS date and time)
  • Een actieve / inactieve vlag, en (inschakelen / uitschakelen)
  • Het hoofdgedeelte, welke de SQL-instructies bevatten die uitgevoerd moeten worden wanneer de event optreedt (de DO-clausule, bevat ofwel één SQL-instructie of meerdere SQL-instructies die omsloten worden door een BEGIN … END blok)

Standaard is de event scheduling engine van MySQL uitgeschakeld. Met de volgende deze instructie start u de globale event scheduler, welke periodiek controleert of er scheduled events zijn die uitgevoerd moeten worden.

SET GLOBAL event_scheduler = ON;

(0 row(s) affected)

Om een scheduled event te maken, moet een gebruiker het EVENT privilege hebben. Om de globale event scheduler in- of uit te schakelen, moet een gebruiker het SUPER privilege hebben.

Om een schedule event te wijzigen, gebruikt u de opdracht ALTER EVENT en voorziet u nieuwe parameters voor het event. Hier is een voorbeeld, die de vorige event aanpast zodat het om de twee uur uitgevoerd wordt:

MSSQL

Command(s) completed successfully.

MySQL

DELIMITER //
ALTER EVENT pax_day
ON SCHEDULE EVERY 2 HOUR
STARTS '2009-01-14 22:45:00'
ENABLE
DO
BEGIN
    INSERT INTO paxarchive
    SELECT * FROM pax
    WHERE 
        FlightDate <= DATE_SUB(CURRENT_DATE(), 
        INTERVAL 30 DAY);
    DELETE FROM pax 
    WHERE
        FlightDate <= DATE_SUB(CURRENT_DATE(),
        INTERVAL 30 DAY);
END //

(0 row(s) affected)

Drop scheduled events, disable scheduled events

Standaard wordt, zodra een gebeurtenis is voltooid, deze automatisch door de event scheduler verwijderd van de event wachtrij. U kunt deze ook op elk gewenst moment handmatig verwijderen door gebruik te maken van de DROP EVENT instructie gevolgd door de naam van de event:

MSSQL

Command(s) completed successfully.

MySQL

DROP EVENT pax_day;

(0 row(s) affected)

Om te voorkomen dat een event automatisch wordt verwijderd uit de event wachtrij als het eenmaal is voltooid (voor redenen zoald audit enz.), voegt u een ON COMPLETION PRESERVE clausule toe aan de CREATE EVENT opdracht.

Als u alle scheduled events uit te schakelen, stopt u de globale event scheduler met de volgende instructie.

SET GLOBAL event_scheduler = OFF;

(0 row(s) affected)

U kan een specifieke scheduled event uitschakelen (uitgeschakeld events zullen in het geheel niet optreden) door gebruik te maken van volgende instructie.

MSSQL

Command(s) completed successfully.

MySQL

ALTER EVENT pax_day DISABLE;

(0 row(s) affected)

Om het hoofdegedeelte van een bepaalde event te bekijken, gebruikt u de SHOW CREATE EVENT commando gevolgd door de de naam van de gebeurtenis.

MSSQL

Command(s) completed successfully.

MySQL

SHOW CREATE EVENT pax_day;

(1 row(s) returned)

Om een lijst van alle event die op de server staan te bekijken, gebruikt u de opdracht SHOW EVENTS.

MSSQL

Command(s) completed successfully.

HTML clipboard

RecordIDByUserNoteEventTime
11DOMAIN\
domainuser		Record added: flight2016-04-22 21:51:45.0500000

MySQL

SHOW EVENTS;

(1 row(s) returned)

HTML clipboard

RecordIDByUserNoteEventTime
11root@%Record added: flight2016-04-22 21:04:06

Event Security: DEFINER

De CREATE EVENT instructie ondersteunt een DEFINER clausule, waarmee aangegeven wordt welke privileges van het gebruikers-account moeten worden gebruikt bij het uitvoeren van de event code. Zodat een event met success uitgevoerd kan worden dient de gebruiker over de nodige rechten te bezitten die nodig zijn om de instructies te kunnen uitvoeren die opgenomen zijn in het hoofdgedeelte van de event. Standaard stelt MySQL de waarde van de DEFINER in op gebruiker die het evenement gecreëerd heeft.

MSSQL

Command(s) completed successfully.

MySQL

DELIMITER //
CREATE
DEFINER = 'someone@example.com'
EVENT pax_day
ON SCHEDULE EVERY 1 DAY
STARTS '2009-01-14 22:45:00'
ENABLE
DO
BEGIN
    INSERT INTO paxarchive
    SELECT * FROM pax
    WHERE 
        FlightDate <= DATE_SUB(CURRENT_DATE(), 
        INTERVAL 30 DAY);
    DELETE FROM pax 
    WHERE
        FlightDate <= DATE_SUB(CURRENT_DATE(),
        INTERVAL 30 DAY);
END //
(0 row(s) affected)

Recurring Events

Laten we de terugkerende events nader bekijken. Zoals in de vorige paragraaf geïllustreerd is, bevat een terugkerend event de EVERY clausule in de event definitie; deze clausule geeft aan dat MySQL het event “elke XX tijdeenheden” moet herhalen. De EVERY clausule bevat ook het herhalings interval, deze bestaat uit een nummer en een sleutelwoord die de tijdseenheid aangeeft. Geldige tijdseenheden zijn onder meer YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, WEEK en SECOND.

Hier is een voorbeeld, waarbij elk uur voor elke vlucht het percentage van de zetels wordt gecontroleerd die gereserveerd zijn en vluchten die meer dan 80 procent vol zijn registereert.

MSSQL

Command(s) completed successfully.

MySQL

DELIMITER //
CREATE EVENT util_hour
ON SCHEDULE EVERY 1 HOUR
ENABLE
DO
BEGIN
    DECLARE fid INT;
    DECLARE fdate DATE;
    DECLARE str TEXT DEFAULT '';
    DECLARE util FLOAT;
    DECLARE c CURSOR FOR
    SELECT s.FlightID, s.FlightDate, 1-
        (SUM(s.CurrSeats) /
        (SELECT SUM(fc.MaxSeats)
        FROM flightclass AS fc
        WHERE fc.FlightID = s.FlightID
        GROUP BY FlightID))
        AS u
    FROM stats AS s
    GROUP BY s.FlightID, s.FlightDate
    HAVING u > 0.80;
    OPEN c;
    l: LOOP
        FETCH c INTO fid,fdate,util;
        SET str = CONCAT
            ('Flight # ', fid, ' on ', fdate, ": ", 
            ROUND(util*100), '%');
        INSERT INTO log (ByUser, Note, EventTime)
        VALUES (CURRENT_USER(), str, NOW());
    END LOOP l;
    CLOSE c;
END //

(0 row(s) affected)

Terugkerende events die nieuwe gegevens naar een tabel schrijven die geen vastomlijnde eindtijd bevatten kunnen een mogelijk risico vormen, omdat ze de doeltabel aanzienlijk in grootte kunnen laten groeien zonder dat dit op een bepaald moment zal stoppen.

Vermijd het gebruik van deze zoveel als mogelijk (het vorige voorbeeld is slechts illustratief en moet niet worden gebruikt in een productieomgeving), en als je het zou moeten doen, dient u zich ervan te vergewissen dat u altijd een eindtijd en eventueel nog extra beperkingen meegeeft die de acties van de event beperkt.

U kunt het event ook zo configureren dat het zal optreden binnen een bepaalde tijdspanne door het opgeven van optionele STARTS en ENDS clausules, waarbij de begin- en eindtijden voor het event worden opgegeven. Hier is een herziening van het vorige voorbeeld, waarin de event zo is geconfigureerd dat het zal optreden tijdens een bepaalde maand:

MSSQL

Command(s) completed successfully.

MySQL

DELIMITER //
CREATE EVENT util_hour
ON SCHEDULE EVERY 1 HOUR
STARTS '2009-04-01 00:00:01'
ENDS '2009-04-30 23:59:01'
ENABLE
DO
BEGIN
DECLARE fid INT;
DECLARE fdate DATE;
DECLARE str TEXT DEFAULT '';
DECLARE util FLOAT;
DECLARE c CURSOR FOR
SELECT s.FlightID, s.FlightDate, 1-(SUM(s.CurrSeats) /
    (SELECT SUM(fc.MaxSeats)
    FROM flightclass AS fc
    WHERE fc.FlightID = s.FlightID
    GROUP BY FlightID))
    AS u
FROM stats AS s
GROUP BY s.FlightID, s.FlightDate
HAVING u > 0.80;
OPEN c;
l: LOOP
    FETCH c INTO fid,fdate,util;
    SET str = CONCAT
    ('Flight # ', fid, ' on ', fdate, ": ", 
    ROUND(util*100), '%');
    INSERT INTO log (ByUser, Note, EventTime)
    VALUES (CURRENT_USER(), str, NOW());
END LOOP l;
CLOSE c;
END//

(0 row(s) affected)

One-Off Events

Hoewel de event scheduler van MySQL zeer geschikt is voor het opzetten van terugkerende events, ondersteunt het ook events die slechts eenmaal, op een vooraf ingestelde tijd en datum moet optreden. Voor het instellen van een dergelijke event, dient men de EVERY clausule in de CREATE EVENT instructie te vervangen door een AT clausule die de datum en het tijdstip moet bevatten waarop het evenement moet optreden. Hier is een voorbeeld, waarin een evenement optreedt op 1 april 2009 om 1:25.

MSSQL

Command(s) completed successfully.

MySQL

CREATE EVENT log_onetime
ON SCHEDULE AT '2009-04-01 01:25'
ENABLE
DO
INSERT INTO log (ByUser, Note, EventTime)
VALUES (CURRENT_USER(), 'Updating all accounts', NOW());

(0 row(s) affected)

Als u een event onmiddellijk wil laten optreden op het moment dat het is gemaakt, gebruikt u de functie NOW() in de AT clausule in plaats van een tijdstempel.