iCalendar“字段"列表(用于基于iCalendar标准的数据库架构) [英] iCalendar "Field" list (for database schema based on iCalendar standard)

问题描述

我的应用程序必须处理日历信息(包括单次发生，重复发生等).为了轻松地与其他应用程序交互，我认为直接基于iCalendar格式(字段，关系，约束)创建数据库架构是一个好主意，这样我就可以通过ORM获得iCalendar兼容的对象，当需要.

my application has to deal with calendar information (incl. single occurrence, recurrence, etc.). In order to easily interface with other applications I thought that it would be a good idea to create my database schema based on the iCalendar format (fields, relationships, constraints) directly so that I get iCalendar compatible objects via ORM that I can easily expose when needed.

我知道RFC是可用的，但由于目前我不使用其中的所有其他信息，它有点复杂.

I know that the RFC is available but it's kind of complicated because of all the additional information in it that I don't use at the moment.

有人可以让我指向一个更简单的资源来创建基于iCal标准的数据库架构(这意味着字段/字段名称及其与iCal条目的关系的列表)吗?

Could somebody point me to an easier source to create a database schema based on the iCal standard (meaning a list of fields/fieldnames and their relationship for iCal entries)?

谢谢！

推荐答案

我已经完成了此操作(仅适用于VEvent，不支持TODO项目或Journal整体或类似内容).我的实现如下所示(删除了不特定于该问题的列之后):

I've done this (for VEvents only, not supporting TODO items or Journal entires or anything like that). My implementation looks like this (after removing columns that are not specific to the question):

-- One table for each event.  An event may have multiple rRules.
Create Table [vEvent]
    (vEventID Integer Identity(1, 1) Not Null
     Constraint [vEvent.pk]
     Primary Key
     Clustered
    ,title nVarChar(200) Not Null);

-- One table for rRules.
-- My application does NOT support the "bySetPos" rule, so that is not included.
Create Table [rRule]
    (rRuleID Integer Identity(1, 1) Not Null
     Constraint [rRule.pk]
     Primary Key
     Clustered
    ,vEventID Integer Not Null
     Constraint [fk.vEvent.rRules]
     Foreign Key
     References [vEvent] (vEventID)
     On Update Cascade
     On Delete Cascade
    ,[class]            varChar(  12) Not Null Default('public')
    ,[created]         DateTime       Not Null Default(getUTCDate())
    ,[description]     nVarChar(max)      Null
    ,[dtStart]         DateTime       Not Null
    ,[dtEnd]           DateTime           Null
    ,[duration]         varChar(  20)     Null
    ,[geoLat]          Float              Null
    ,[geoLng]          Float              Null
    ,[lastModified]    DateTime       Not Null Default(getUTCDate())
    ,[location]        nVarChar(max)      Null
    ,[organizerCN]     nVarChar(  50)     Null
    ,[organizerMailTo] nVarChar( 100)     Null
    ,[seq]             Integer        Not Null Default(0)
    ,[status]           varChar(   9) Not Null Default('confirmed')
    ,[summary]         nVarChar(  75)     Null
    ,[transparent]     Bit            Not Null Default(0)
    ,[freq]             varChar(   8) Not Null Default('daily')
    ,[until]           DateTime           Null
    ,[count]           Integer            Null
    ,[interval]        Integer        Not Null Default(1)
    ,[bySecond]         varChar( 170)     Null
    ,[byMinute]         varChar( 170)     Null
    ,[byHour]           varChar(  61)     Null
    ,[byDay]            varChar(  35)     Null
    ,[byMonthDay]       varChar( 200)     Null
    ,[byYearDay]        varChar(3078)     Null
    ,[byWeekNo]         varChar( 353)     Null
    ,[byMonth]          varChar(  29)     Null
    ,[wkSt]             Char   (   2)     Null Default('mo'));

-- Class must be one of "Confidential", "Private", or "Public"
Alter Table [rRule]
Add Constraint [rRule.ck.Class]
Check ([class] In ('confidential', 'private', 'public'));

-- Start date must come before End date
Alter Table [rRule]
Add Constraint [rRule.ck.dtStart]
Check ([dtEnd] Is Null Or [dtStart] <= [dtEnd]);

-- dtEnd and duration may not both be present
Alter Table [rRule]
Add Constraint [rRule.ck.duration]
Check (Not ([dtEnd] Is Not Null And [duration] Is Not Null));

-- Check valid values for [freq]. Note that 'single' is NOT in the RFC;
-- it is an optimization for my particular iCalendar calculation engine.
-- I use it as a clue that this pattern has only a single date (dtStart),
-- and there is no need to perform extra calculations on it.
Alter Table [rRule]
Add Constraint [rRule.ck.freq]
Check ([freq] In
    ('yearly'
    ,'monthly'
    ,'weekly'
    ,'daily'
    ,'hourly'
    ,'minutely'
    ,'secondly'
    ,'single')); -- Single is NOT part of the spec!

-- If there is a latitude, there must be a longitude, and vice versa.
Alter Table [rRule]
Add Constraint [rRule.ck.geo]
Check (([geoLat] Is Null And [geoLng] Is Null)
       Or ([geoLat] Is Not Null And [geoLng] Is Not Null));

-- Interval must be positive.
Alter Table [rRule]
Add Constraint [rRule.ck.interval]
Check ([interval] > 0);

-- Status has a set of defined values.
Alter Table [rRule]
Add Constraint [rRule.ck.status]
Check ([status] In ('cancelled', 'confirmed', 'tentative'));

-- Until and Count may not coexist in the same rule.
Alter Table [rRule]
Add Constraint [rRule.ck.until and count]
Check (Not ([until] Is Not Null And [count] Is Not Null));


-- One table for exceptions to rRules.  In my application, this covers both
-- exDate and rDate.  I do NOT support extended rule logic here;  The RFC says
-- you should support the same sort of date calculations here as are supported
-- in rRules: exceptions can recur, etc.  I don't do that; mine is simply a
-- set of dates that are either "exceptions" (dates which don't appear, even
-- if the rule otherwise says they should) or "extras" (dates which do appear,
-- even if the rule otherwise wouldn't include them).  This has proved
-- sufficient for my application, and something that can be exported into a
-- valid iCalendar file--even if I can't import an iCalendar file that makes
-- use of recurring rules for exceptions to recurring rules.
Create Table [exDate]
    (exDateID Integer Identity(1, 1) Not Null
     Constraint [exDate.pk]
     Primary Key
     Clustered
    ,rRuleID Integer Not Null
     Constraint [fk.rRule.exDates]
     Foreign Key
     References [rRule] (rRuleID)
     On Update Cascade
     On Delete Cascade
    ,[date] DateTime Not Null
    ,[type] varChar(6) Not Null);  -- Type = "exDate" or "rDate" for me; YMMV.

与此同时，我有几个SQL Server 2005+ CLR函数，可用于计算各种事件的日期.我发现以下形式非常有用:

To go along with this, I have several SQL Server 2005+ CLR functions that can be used to calculate the dates for various events. I have found the following forms to be very useful:

Select * From dbo.getDatesByVEventID(@id, @startDate, @endDate)
Select * From dbo.getEventsByDateRange(@startDate, @endDate, @maxCount)

执行上述操作真是太有趣了！

Implementation of the above is darn fun to figure out!

这篇关于iCalendar“字段"列表(用于基于iCalendar标准的数据库架构)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！