SQL iCalendar“字段”列表（用于基于 iCalendar 标准的数据库架构）

Question

提问by Alex

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.

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

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.

我知道 RFC 是可用的，但它有点复杂，因为其中包含我目前不使用的所有附加信息。

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)?

有人能给我指出一个更简单的来源来创建基于 iCal 标准的数据库模式（意味着字段/字段名称列表及其与 iCal 条目的关系）？

Thanks!

谢谢！

Answer 1

回答by Chris Nielsen

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):

我已经这样做了（仅适用于 VEvents，不支持 TODO 项目或日志整体或类似的东西）。我的实现看起来像这样（在删除不特定于问题的列之后）：

-- 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.

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:

为此，我有几个 SQL Server 2005+ CLR 函数可用于计算各种事件的日期。我发现以下表格非常有用：

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

Implementation of the above is darn fun to figure out!

搞清楚上面的实现真是太有趣了！

Answer 2

回答by MatthewMartin

Yes, sort of. Sunbird (the opensource mozilla calendar) is based on sqlite and I just downloaded and unzipped their source code. It has .sql files in it.

是的，有点。Sunbird（开源的 mozilla 日历）基于 sqlite，我刚刚下载并解压了他们的源代码。它有 .sql 文件。

ftp://ftp.mozilla.org/pub/mozilla.org/calendar/sunbird/releases/0.9/source/

mozilla\calendar\providers\storage\schema-7.sql --this is the schema that sunbird uses to make valid iCal files, so it can't be too bad.

mozilla\calendar\providers\storage\schema-7.sql -- 这是sunbird 用来制作有效iCal 文件的模式，所以它不会太糟糕。

Answer 3

回答by user1906416

Thanks so much Chris Nielsen for his great solution above. However, I had some problems with it so I modified it. Please note that the solution above is in python sqlalchemy. I will convert it very soon.

非常感谢 Chris Nielsen 的上述出色解决方案。但是，我遇到了一些问题，所以我对其进行了修改。请注意，上面的解决方案是在 python sqlalchemy 中的。我会很快转换它。

My main difficulties with Chris's solution (and they might not apply to everyone) are

我对 Chris 解决方案的主要困难（它们可能不适用于所有人）是

I didn't need many of the columns in his solution. I only needed columns which would help me with Events and Recurrences. This is the fault of the iCalendar spec, not Chris's. My solution below only considers recurrence rules in terms of their calendar restrictions and their sequence restrictions.
Certain columns -- most importantly dtStart and dtEnd -- belong to VEVENT, not to RRULE, but Chris placed them in RRULE. This was confusing to me. VEVENT: https://tools.ietf.org/html/rfc5545#section-3.6.1RRULE: https://tools.ietf.org/html/rfc5545#section-3.3.10
I also needed to figure out how to contain a schedule which might have a variety of patterns. For example, an event might happen every week on Friday from 6PM-9PM but also all day on May Day. This requires flexibility with dtStart and dtEnd. For this reason, I created a containing Table "SCHEDULE" which maintains a many-to-many relationship with EVENTS, whereas EVENTS have a containment relationship with RRULES.

我不需要他的解决方案中的很多列。我只需要可以帮助我处理事件和重复的列。这是 iCalendar 规范的错，而不是 Chris 的错。我下面的解决方案只考虑了日历限制和顺序限制方面的重复规则。
某些列——最重要的是 dtStart 和 dtEnd——属于 VEVENT，而不是 RRULE，但 Chris 将它们放在 RRULE 中。这让我很困惑。VEVENT：https://tools.ietf.org/html/rfc5545#section-3.6.1 RRULE：https: //tools.ietf.org/html/rfc5545#section-3.3.10
我还需要弄清楚如何包含可能具有多种模式的时间表。例如，一个事件可能会在每周星期五下午 6 点到晚上 9 点之间发生，但也可能在五一节全天发生。这需要 dtStart 和 dtEnd 的灵活性。为此，我创建了一个包含表“SCHEDULE”，它与 EVENTS 保持多对多关系，而 EVENTS 与 RRULES 具有包含关系。

Below is my solution in sqlalchemy. I will convert this to SQL ASAP.

以下是我在 sqlalchemy 中的解决方案。我将尽快将其转换为 SQL。

from app import db
from sqlalchemy import CheckConstraint
from sqlalchemy.ext.associationproxy import association_proxy


class Schedule(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    subtypes_relation = db.relationship('Event', secondary=schedule_event_association,
                                        backref=db.backref('Schedule', lazy='dynamic'))

schedule_event_association = db.Table(
    'schedule_event_association',
    db.Column('schedule_id', db.Integer, db.ForeignKey('schedule.id')),
    db.Column('event_id', db.Integer, db.ForeignKey('event.id')))

class Event(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    dt_start = db.Column(db.DateTime)  # start time
    dt_end = db.Column(db.DateTime) # end time
    tz_id = db.Column(db.String) # Time Zone

    recurrence_rule = db.Column('RecurrenceRule_id',  db.Integer, db.ForeignKey('RecurrenceRule.id'))

# Start date must come before End date
    CheckConstraint('dtEnd is NULL OR dtStart <= dtEnd', name='Valid: Time Period')

class RecurrenceRule(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    # Frequency Type
    freq = db.Column(db.String(8), nullable=False, default='weekly') # type of recurrence

    # Calendar-Based Rules
    byDay = db.Column(db.String(35))   # List of Day of the Week
                                        # "mo,tu,we" for weekly
                                        # "+2MO, -1MO" = second monday, last monday for yearly or monthly
    byMonthDay = db.Column(db.String(200)) # List of Day of the Month
                                            # +1,-1"
                                            # Only for Monthly or Yearly
    byYearDay = db.Column(db.String(3078)) # List Day of the Year
                                            #"+1, -1"
                                            # Only for yearly
                                            # Take care with leap years
    byWeekNo = db.Column(db.String(353)) # Which week of Mon`enter code here`th
                                            # "+5, -3" for fifth and third-to-last
                                            # Only for yearly
    byMonth = db.Column(db.String(29))   # Month of year.

    # Sequence-Based Rules
    until = db.Column(db.DateTime)   # last day of occurence
    count = db.Column(db.Integer)    # number of occurences
    interval = db.Column(db.Integer, nullable=False, default=1) # interval between recurrences
    bysetpos = db.Column(db.String()) # Specifies specific instances of recurrence


# Valid Values
    CheckConstraint(freq in ('yearly', 'monthly', 'weekly', 'daily', 'single'),
                    name='Valid: Frequency Value')
    CheckConstraint(interval > 0, name='Valid: Positive Interval')
    CheckConstraint(byDay is not None and freq in ('daily', 'yearly', 'monthly'))
    CheckConstraint(byWeekNo is not None and freq in ('yearly', 'monthly'))
    CheckConstraint(byYearDay is not None and freq == 'yearly')

# Until and Count may not coexist in the same rule.
    CheckConstraint(not (until is not None and count is not None),
                    name='Valid: Not Both Until and Count')

Answer 4

回答by psychoclerk

you can try this docu for apple icalendar. you can replicate the fields directly into database tables. http://developer.apple.com/library/mac/#DOCUMENTATION/AppleApplications/Reference/SyncServicesSchemaRef/Articles/Calendars.html
if you use java, ical4j provides the glue between the schema and the interface. If you do not use java, the algorithms in the source code for generation of occurrences and structure will provide good help for implementation.

你可以试试这个苹果 icalendar 的文档。您可以将字段直接复制到数据库表中。 http://developer.apple.com/library/mac/#DOCUMENTATION/AppleApplications/Reference/SyncServicesSchemaRef/Articles/Calendars.html
如果你使用 java，ical4j 提供了模式和接口之间的粘合剂。如果不使用java，源代码中的出现次数和结构生成算法将为实现提供很好的帮助。

Answer 5

回答by Alex Martelli

iCal is an Apple application that follows the standard currently known as Icalendar (the successor to the previous Vcalendar). I think the wikipedia entryhas all info you need for your purposes, and in a simple and easy-to-follow format, but, feel free to ask for more guidance if needed!!!

iCal 是一款 Apple 应用程序，它遵循当前称为 Icalendar（先前 Vcalendar 的继任者）的标准。我认为维基百科条目包含您所需的所有信息，并且采用简单易懂的格式，但是，如果需要，请随时寻求更多指导！！！

SQL iCalendar“字段”列表（用于基于 iCalendar 标准的数据库架构）

提问by Alex

回答by Chris Nielsen

回答by MatthewMartin

回答by user1906416

回答by psychoclerk

回答by Alex Martelli

相关推荐

最近更新

标签

SQL iCalendar“字段”列表（用于基于 iCalendar 标准的数据库架构）

提问by Alex

回答by Chris Nielsen

回答by MatthewMartin

回答by user1906416

回答by psychoclerk

回答by Alex Martelli

相关推荐

SQL 查询teradata中的用户定义变量

SQL IBM DB2 中的数据类型转换：BIGINT 到 VARCHAR

SQL to_char 货币格式

在 SQL Server 中存储 IP 地址最合适的数据类型是什么？

相关推荐

最近更新

标签