Événements récurrents/récurrents du calendrier - Meilleure méthode de stockage

Question

Je suis en train de construire un système d'événements personnalisés, et si vous avez un événement répétitif qui ressemble à ceci :

L'événement A se répète tous les 4 jours à partir du 3 mars 2011.

ou

L'événement B se répète toutes les 2 semaines, le mardi, à partir du 1er mars 2011.

Comment puis-je stocker ces données dans une base de données de manière à ce qu'elles soient faciles à consulter ? Je ne veux pas avoir de problèmes de performance s'il y a un grand nombre d'événements et que je doive passer par chacun d'entre eux lors du rendu du calendrier.

Answer 1

Stockage de motifs répétitifs "simples".

Pour mon calendrier basé sur PHP/MySQL, je voulais stocker les informations sur les événements répétés/récurrents aussi efficacement que possible. Je ne voulais pas avoir un grand nombre de lignes, et je voulais pouvoir consulter facilement tous les événements qui ont lieu à une date spécifique.

La méthode ci-dessous est idéale pour stocker des informations répétitives qui se produisent à intervalles réguliers, par exemple tous les jours, toutes les semaines ou tous les mois. n jours, chaque semaine, chaque mois, chaque année, etc etc. Cela inclut également les modèles de type "tous les mardis et jeudis", car ils sont stockés séparément comme "toutes les semaines commençant un mardi" et "toutes les semaines commençant un jeudi".

Supposons que j'ai deux tables, l'une appelée events comme ça :

ID    NAME
1     Sample Event
2     Another Event

Et une table appelée events_meta comme ça :

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000
2     1             repeat_interval_1  432000

Avec repeat_start étant une date sans heure comme un timestamp unix, et repeat_interval une quantité en secondes entre les intervalles (432000 est 5 jours).

repeat_interval_1 va avec repeat_start de l'ID 1. Ainsi, si j'ai un événement qui se répète tous les mardis et tous les jeudis, le repeat_interval sera de 604800 (7 jours), et il y aura 2 repeat_starts et 2 repeat_intervals. La table ressemblerait à ceci :

ID    event_id      meta_key           meta_value
1     1             repeat_start       1298959200 -- This is for the Tuesday repeat
2     1             repeat_interval_1  604800
3     1             repeat_start       1299132000 -- This is for the Thursday repeat
4     1             repeat_interval_3  604800
5     2             repeat_start       1299132000
6     2             repeat_interval_5  1          -- Using 1 as a value gives us an event that only happens once

Ensuite, si vous avez un calendrier qui passe en boucle tous les jours, en saisissant les événements du jour où il se trouve, la requête ressemblerait à ceci :

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.`meta_value` )
          END
        ) / EM2.`meta_value`
    ) = 1
LIMIT 0 , 30

Remplacement de {current_timestamp} avec l'horodatage unix de la date actuelle (moins l'heure, donc les valeurs heure, minute et seconde seront définies à 0).

J'espère que cela aidera quelqu'un d'autre !

---

Stockage de motifs répétitifs "complexes

Cette méthode est mieux adaptée au stockage de motifs complexes tels que

Event A repeats every month on the 3rd of the month starting on March 3, 2011

ou

Event A repeats Friday of the 2nd week of the month starting on March 11, 2011

Je vous recommande de le combiner avec le système ci-dessus pour une plus grande flexibilité. Les tables pour cela devraient ressembler à :

ID    NAME
1     Sample Event
2     Another Event

Et une table appelée events_meta comme ça :

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000 -- March 3rd, 2011
2     1             repeat_year_1      *
3     1             repeat_month_1     *
4     1             repeat_week_im_1   2
5     1             repeat_weekday_1   6

repeat_week_im représente la semaine du mois en cours, qui peut être comprise entre 1 et 5 potentiellement. repeat_weekday dans le jour de la semaine, 1-7.

Maintenant, en supposant que vous boucliez les jours/semaines pour créer une vue du mois dans votre calendrier, vous pourriez composer une requête comme celle-ci :

SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = 'repeat_start'
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( 'repeat_year_', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( 'repeat_month_', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( 'repeat_week_im_', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( 'repeat_weekday_', EM1.id )
WHERE (
  EM2.meta_value =2011
  OR EM2.meta_value = '*'
)
AND (
  EM3.meta_value =4
  OR EM3.meta_value = '*'
)
AND (
  EM4.meta_value =2
  OR EM4.meta_value = '*'
)
AND (
  EM5.meta_value =6
  OR EM5.meta_value = '*'
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30

Cette méthode, combinée à la méthode ci-dessus, pourrait être utilisée pour couvrir la plupart des schémas d'événements répétitifs/récurrents. Si j'ai oublié quelque chose, veuillez laisser un commentaire.

Answer 2

Bien que la réponse actuellement acceptée m'ait été d'une grande aide, je voulais partager quelques modifications utiles qui simplifient les requêtes et augmentent également les performances.

---

"Événements répétitifs "simples

Pour gérer les événements qui se répètent à intervalles réguliers, tels que :

Repeat every other day 

ou

Repeat every week on Tuesday 

Vous devez créer deux tables, l'une appelée events comme ça :

ID    NAME
1     Sample Event
2     Another Event

Et une table appelée events_meta comme ça :

ID    event_id      repeat_start       repeat_interval
1     1             1369008000         604800            -- Repeats every Monday after May 20th 2013
1     1             1369008000         604800            -- Also repeats every Friday after May 20th 2013

Avec repeat_start étant une date d'horodatage unix sans heure (1369008000 correspond au 20 mai 2013), et repeat_interval une quantité en secondes entre les intervalles (604800 est 7 jours).

En bouclant sur chaque jour du calendrier, vous pouvez obtenir des événements répétés en utilisant cette simple requête :

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE  (( 1299736800 - repeat_start) % repeat_interval = 0 )

Il suffit de remplacer l'horodatage unix (1299736800) pour chaque date de votre calendrier.

Notez l'utilisation du modulo (signe %). Ce symbole est comme une division ordinaire, mais renvoie le ''reste'' au lieu du quotient, et en tant que tel, il vaut 0 chaque fois que la date actuelle est un multiple exact de l'intervalle repeat_interval à partir du repeat_start.

Comparaison des performances

C'est beaucoup plus rapide que la réponse basée sur les "meta_keys" proposée précédemment, qui était la suivante :

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.`meta_value` )
          END
        ) / EM2.`meta_value`
    ) = 1

Si vous exécutez EXPLAIN cette requête, vous constaterez qu'elle a nécessité l'utilisation d'un tampon de jointure :

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                          |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
|  1 | SIMPLE      | EM1   | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using where                    |
|  1 | SIMPLE      | EV    | eq_ref | PRIMARY       | PRIMARY | 4       | bcs.EM1.event_id |    1 |                                |
|  1 | SIMPLE      | EM2   | ALL    | NULL          | NULL    | NULL    | NULL             |    2 | Using where; Using join buffer |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+

La solution avec 1 jointure ci-dessus ne nécessite pas un tel tampon.

---

"Motifs "complexes

Vous pouvez ajouter des types plus complexes pour prendre en charge ces types de règles de répétition :

Event A repeats every month on the 3rd of the month starting on March 3, 2011

ou

Event A repeats second Friday of the month starting on March 11, 2011

Votre table d'événements peut avoir exactement le même aspect :

ID    NAME
1     Sample Event
2     Another Event

Ensuite, pour ajouter le support de ces règles complexes, ajoutez des colonnes à events_meta comme ça :

ID    event_id      repeat_start       repeat_interval    repeat_year    repeat_month    repeat_day    repeat_week    repeat_weekday
1     1             1369008000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Monday after May 20, 2013
1     1             1368144000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Friday after May 10, 2013
2     2             1369008000         NULL               2013           *               *             2              5                -- Repeats on Friday of the 2nd week in every month    

Notez que vous devez simplement spécifier un repeat_interval ou un ensemble de repeat_year , repeat_month , repeat_day , repeat_week y repeat_weekday données.

Cela rend la sélection des deux types simultanément très simple. Il suffit de parcourir chaque jour en boucle et de remplir les valeurs correctes (1370563200 pour le 7 juin 2013, puis l'année, le mois, le jour, le numéro de semaine et le jour de la semaine comme suit) :

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE  (( 1370563200 - repeat_start) % repeat_interval = 0 )
  OR ( 
    (repeat_year = 2013 OR repeat_year = '*' )
    AND
    (repeat_month = 6 OR repeat_month = '*' )
    AND
    (repeat_day = 7 OR repeat_day = '*' )
    AND
    (repeat_week = 2 OR repeat_week = '*' )
    AND
    (repeat_weekday = 5 OR repeat_weekday = '*' )
    AND repeat_start <= 1370563200
  )

Cela renvoie tous les événements qui se répètent le vendredi de la deuxième semaine, ainsi que tous les événements qui se répètent tous les vendredis, il renvoie donc les deux événements ID 1 et 2 :

ID    NAME
1     Sample Event
2     Another Event

*Sidenote dans le SQL ci-dessus j'ai utilisé Date du PHP les index par défaut des jours de la semaine, donc "5" pour le vendredi

---

J'espère que cela aidera d'autres personnes autant que la réponse originale m'a aidé !

Answer 3

Amélioration : remplacer le timestamp par la date

Une petite amélioration à la réponse acceptée, qui a été affinée par ahoffner, est qu'il est possible d'utiliser un format de date plutôt qu'un timestamp. Les avantages sont les suivants :

1. des dates lisibles dans la base de données
2. pas de problème avec les années > 2038 et l'horodatage
3. Les suppressions doivent être prudentes avec les horodatages qui sont basés sur des dates corrigées des variations saisonnières. Par exemple, au Royaume-Uni, le 28 juin commence une heure plus tôt que le 28 décembre, donc dériver un horodatage à partir d'une date peut casser l'algorithme de récursion.

Pour ce faire, modifiez la base de données repeat_start à enregistrer comme type "date" et repeat_interval tiennent désormais compte des jours plutôt que des secondes, c'est-à-dire 7 pour une répétition de 7 jours.

changez la ligne sql :

WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )

à :

WHERE ( DATEDIFF( '2013-6-7', repeat_start ) % repeat_interval = 0)

tout le reste reste inchangé. C'est simple !

Answer 4

Pour tous ceux que cela intéresse, il vous suffit désormais de copier-coller pour vous lancer en quelques minutes. J'ai suivi les conseils dans les commentaires du mieux que j'ai pu. Faites-moi savoir si j'ai oublié quelque chose.

"VERSION COMPLEXE" :

événements

+----------+----------------+
| ID       | NAME           | 
+----------+----------------+
| 1        | Sample event 1 |
| 2        | Second  event  |
| 3        | Third event    |
+----------+----------------+

événements_meta

+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
| ID | event\_id | repeat\_start | repeat\_interval  | repeat\_year | repeat\_month | repeat\_day | repeat\_week | repeat\_weekday |
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
| 1  | 1        | 2014-07-04   | 7                | NULL        | NULL         | NULL       | NULL        | NULL           |
| 2  | 2        | 2014-06-26   | NULL             | 2014        | \*            | \*          | 2           | 5              |
| 3  | 3        | 2014-07-04   | NULL             | \*           | \*            | \*          | \*           | 5              |
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+

Code SQL :

CREATE TABLE IF NOT EXISTS `events` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`ID`, `NAME`) VALUES
(1, 'Sample event'),
(2, 'Another event'),
(3, 'Third event...');

CREATE TABLE IF NOT EXISTS `events_meta` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `repeat_start` date NOT NULL,
  `repeat_interval` varchar(255) NOT NULL,
  `repeat_year` varchar(255) NOT NULL,
  `repeat_month` varchar(255) NOT NULL,
  `repeat_day` varchar(255) NOT NULL,
  `repeat_week` varchar(255) NOT NULL,
  `repeat_weekday` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `events_meta`
--

INSERT INTO `events_meta` (`ID`, `event_id`, `repeat_start`, `repeat_interval`, `repeat_year`, `repeat_month`, `repeat_day`, `repeat_week`, `repeat_weekday`) VALUES
(1, 1, '2014-07-04', '7', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL'),
(2, 2, '2014-06-26', 'NULL', '2014', '*', '*', '2', '5'),
(3, 3, '2014-07-04', 'NULL', '*', '*', '*', '*', '1');

également disponible en tant que Exportation MySQL (pour un accès facile)

Exemple de code PHP index.php :

<?php
    require 'connect.php';    

    $now = strtotime("yesterday");

    $pushToFirst = -11;
    for($i = $pushToFirst; $i < $pushToFirst+30; $i++)
    {
        $now = strtotime("+".$i." day");
        $year = date("Y", $now);
        $month = date("m", $now);
        $day = date("d", $now);
        $nowString = $year . "-" . $month . "-" . $day;
        $week = (int) ((date('d', $now) - 1) / 7) + 1;
        $weekday = date("N", $now);

        echo $nowString . "<br />";
        echo $week . " " . $weekday . "<br />";

        $sql = "SELECT EV.*
                FROM `events` EV
                RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
                WHERE ( DATEDIFF( '$nowString', repeat_start ) % repeat_interval = 0 )
                OR ( 
                    (repeat_year = $year OR repeat_year = '*' )
                    AND
                    (repeat_month = $month OR repeat_month = '*' )
                    AND
                    (repeat_day = $day OR repeat_day = '*' )
                    AND
                    (repeat_week = $week OR repeat_week = '*' )
                    AND
                    (repeat_weekday = $weekday OR repeat_weekday = '*' )
                    AND repeat_start <= DATE('$nowString')
                )";
        foreach ($dbConnect->query($sql) as $row) {
            print $row['ID'] . "\t";
            print $row['NAME'] . "<br />";
        }

        echo "<br /><br /><br />";
    }
?>

Exemple de code PHP connect.php :

<?
// ----------------------------------------------------------------------------------------------------
//                                       Connecting to database
// ----------------------------------------------------------------------------------------------------
// Database variables
$username = "";
$password = "";
$hostname = ""; 
$database = ""; 

// Try to connect to database and set charset to UTF8
try {
    $dbConnect = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8", $username, $password);
    $dbConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
// ----------------------------------------------------------------------------------------------------
//                                      / Connecting to database
// ----------------------------------------------------------------------------------------------------
?>

Le code php est également disponible ici (pour une meilleure lisibilité) :
index.php
y
connecter.php
La mise en place de ce système ne devrait vous prendre que quelques minutes. Pas des heures. :)

Answer 5

Pourquoi ne pas utiliser un mécanisme similaire aux tâches cron d'Apache ? http://en.wikipedia.org/wiki/Cron

Pour le calendrier \scheduling J'utiliserais des valeurs légèrement différentes pour les "bits" afin de tenir compte des événements de réoccurence du calendrier standard - au lieu de [jour de la semaine (0 - 7), mois (1 - 12), jour du mois (1 - 31), heure (0 - 23), min (0 - 59)]

-- J'utiliserais quelque chose comme [Année (répétée toutes les N années), mois (1 - 12), jour du mois (1 - 31), semaine du mois (1-5), jour de la semaine (0 - 7)]

J'espère que cela vous aidera.