Example of storing recurring events as separate records.. please

Recurrence can get very, very complex. Don't expect any easy solution here. Just a lot of work.

You can follow these steps:

1. store the original event with the rule in a special DB field
2. generate the occurrences from the rule
3. store these occurrences as standalone records, add a special field that will identify the record as a "cached occurrence"
4. don't forget to merge these records with any exceptions from the rule (if they are defined)
5. you need to update the cached occurrences whenever the rule changes
6. you don't specify DataRecurrenceField in this case - the occurrences look like normal events to DayPilot
7. you might need a special DB field that will let you recognize the record as part of a recurring series

If you use the DayPilot built-in recurrence helpers than the following can help:

#1 - DayPilot requires one varchar field for storing the recurrence rule
#2 - you can generate the occurrences using RecurrenceExpander.Expand() (see an example of RecurrenceExpander use in Demo/Calendar/RecurrentEvents.aspx.cs)

Dan,

Currently, the application I'm developing/testing is using the built in recurrence functionality.

I want to switch over to using single records per occurrence...

So, in following your steps, I am with you up until point 1... (sorry for my ignorance here)

Further to point 2 and beyond..
How do I create the occurrences from the rule as far as the database is concerned, to put me to point 3..

To be honest, I am expecting some kind of example ..
As you say, "You can store the recurring events as separate records, no problem. In fact, this is the only solution that scales." ... I sincerely hope that there is an example available..

Thanks

Dave

I'm sorry that I can't provide a simple example here - that's because it's not simple. Using the built-in recurrence (with dynamic expanding) is described in the following tutorial:

http://www.daypilot.org/calendar-tutorial-recurring-events/

However, that's as far as I can go here without reflecting the needs of a specific application.

> Further to point 2 and beyond..
> How do I create the occurrences from the rule as far as the database is concerned, to put me to point 3..

You need to use the RecurrenceExpander.Expand(). See an example of RecurrenceExpander use in Demo/Calendar/RecurrentEvents.aspx.cs. You feed it with a DataTable with records that include the rules and you will get a DataTable with occurrences.

Then you need to make several architectural decisions:

1. How to store the records (in the same table as the normal records, in a separate table; or use another "cache" table that will copy the normal events and the occurrences).

2. How to identify the master record (probably using the master event id), how to identify the occurrence (you will need to store the original start and also the modified start in case that) so you can replace the event in case you have to re-generate the occurrences.

3. Will you support exceptions from the rule? Another headache - you will need to add different logic to all event-related event handlers (event moving, resizing, editing, deleting) that will take the recurrence series into account.

When you decide to store the occurrences as individual records it becomes simple from the DayPilot Calendar point of view (hence "You can store the recurring events as separate records, no problem."). But it will move all the logic to the backend.

Dan,

Thanks for coming back to me and your words of advice.

As far as the architectural decisions go, initial instincts tell me to put all fields in the existing datatable that I have to hand and see how things go from there.

As far as the master record is concerned, that will follow similar principles to what you already have..

I am thinking of implementing a doubly linked list (both in .Net for the front end and within the database) to hold the "chain" of recurrences..

Each event will have its own start date, end date, start time, end time etc, with the addage of recording the previous and next occurrence of the relevant event..

Have you any thoughts on this proposal?

Further to setting things up, how would I get the results of the table returned from the RecurrenceExpander.Expand() call into the database or is it as simple as recursively adding each event to the table populating the values as it goes?

If you plan to reuse the existing table (which is fine) I would probably add the following fields (some of them are not strictly required because you can read the information from another field):

• isRecurrent (boolean)
• rule (string) - the rule definition, only used in the master record
• masterId (id of the master event) - reference to the master
• occurrenceStart (datetime) - allows identification of the occurrence
• isModified (boolean) - true if the "start" is different from "occurrenceStart" (exception from the rule)
• isDeleted (boolean) - true if this occurrence is deleted (exception from the rule)

Plus you will keep the standard required fields:

• id
• start
• end
• text

I don't think it is necessary to maintain links to the next and previous occurrences - I would probably avoid that. You should be able to look it up using masterId + occurrenceStart.

Example data:

1. Master (daily meeting at 2pm)

• id: 1
• start: 2016-02-10T14:00:00
• end: 2016-02-10T15:00:00
• text: Event 1
• isRecurrent: true
• rule: the string generated by RecurrenceRule.Encode()
• masterId: null
• occurrenceStart: null
• isModified: null/false
• isDeleted: null/false

2. Regular occurrence

• id: 2
• start: 2016-02-11T14:00:00
• end: 2016-02-11T15:00:00
• text: Event 2
• isRecurrent: true
• rule: null
• masterId: 1
• occurrenceStart: 2016-02-11T14:00:00
• isModified: false
• isDeleted: false

Note that occurrenceStart equals start.

3. Modified occurrence (today the meeting will start at 3pm instead of 2pm as defined by the master)

• id: 3
• start: 2016-02-12T15:00:00
• end: 2016-02-12T16:00:00
• text: Event 3
• isRecurrent: true
• rule: null
• masterId: 1
• occurrenceStart: 2016-02-12T14:00:00
• isModified: true
• isDeleted: false

4. Deleted occurrence (today the meeting is canceled)

• id: 4
• start: null
• end: null
• text: null
• isRecurrent: true
• rule: null
• masterId: 1
• occurrenceStart: 2016-02-13T14:00:00
• isModified: false
• isDeleted: true

You can also keep the original start/end/text value if you plan to "undelete" it eventually.

> Further to setting things up, how would I get the results of the table returned from the RecurrenceExpander.Expand() call into the database or is it as simple as recursively adding each event to the table populating the values as it goes?

Yes, simply iterate through the Rows collection and create the records in the DB. Depending on the logic, you may want to make sure that the particular record isn't there already (use masterId + occurrenceStart to identify it).

One more thing to consider when storing the occurrences is the "repeat indefinitely" rule. You will not want to kill your DB by generating the occurrences in a endless loop. You need to stop at some point - but make sure that you know later that you need to generate another batch.

Dan,

Wow... Many thanks, Helped enormously...

Is there a way of migrating from using the in-built functionality of recurrence to that of recurring events as separate records to save me having to start from scratch?

I guess I'm asking if I should have an external process of generating the separate records?

Dan,

I am trying to follow the recurrenceexpander.expand example..

Functionality doesn't seem to be working?

I do notice that the sample and my code are different (yes, understandably) but I am picking up in the recurrenteventedit webform = on creating a new event then applying a rule then in the save button I run the recurrenceexpander.expand call ... I have created an empty table with the columns added..

                   DataTable expandedRuleTable = new DataTable("ExpandedRuleTable");
                    expandedRuleTable.Columns.Add("start", typeof(DateTime));
                    expandedRuleTable.Columns.Add("end", typeof(DateTime));
                    expandedRuleTable.Columns.Add("name", typeof(string));
                    expandedRuleTable.Columns.Add("id", typeof(string));
                    expandedRuleTable.Columns.Add("recurrence", typeof(string)); 

RecurrenceExpander.Expand(expandedRuleTable, "recurrence", "start", "end", "id", "master", start, end);

On running the expand call, the expandedRuleTable contains no (zero) rows ..??

.. whats the problem, please advise...

Does the expandedRuleTable contain any data? It's supposed to hold the records where the "recurrence" field contains the encoded rules. It can be just one record, but you need at least one.

Dan,

There table doesn't contain any data post the call?
...The expandedRuleTable rows count is zero..

Dan,

I think I see my mistake ... My bad, sorry..

The application I'm currently working on doesn't directly work off of datatables, it works with lists of objects and dataitems which have previously been defined.

Are you suggesting that I have to work with datatables now to use this functionality?

Please advise.

Dave