Вопрос по sql, icalendar – Список «Поле» iCalendar (для схемы базы данных на основе стандарта iCalendar)

31

моя заявка имеет дело с информацией календаря (в том числе единичное вхождение, повторение и т. д.). Чтобы легко взаимодействовать с другими приложениями, я подумал, что было бы неплохо создать мою схему базы данных на основе формата iCalendar (поля, отношения, ограничения) напрямую, чтобы я мог получить объекты, совместимые с iCalendar через ORM, которые я могу легко представить при необходимо.

Я знаю, что RFC доступен, но он довольно сложен из-за всей дополнительной информации, которую я в данный момент не использую.

Может ли кто-нибудь указать мне на более простой источник для создания схемы базы данных на основе стандарта iCal (имеется в виду список полей / имен полей и их взаимосвязь для записей iCal)?

Спасибо!

Ваш Ответ

5   ответов
1

Большое спасибо Крису Нильсену за его великолепное решение выше. Однако у меня были некоторые проблемы с этим, поэтому я изменил его. Обратите внимание, что решение выше в Python sqlalchemy. Я преобразую это очень скоро.

Мои основные трудности с решением Криса (и они могут не относиться ко всем)

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

  2. 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.1 RRULE: https://tools.ietf.org/html/rfc5545#section-3.3.10

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

Ниже мое решение в 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')
12

Да вроде. Sunbird (календарь Mozilla с открытым исходным кодом) основан на sqlite, и я только что скачал и разархивировал их исходный код. В нем есть файлы .sql.

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

mozilla \ calendar \ provider \ storage \ schema-7.sql - это схема, которую Sunbird использует для создания действительных файлов iCal, поэтому она не может быть слишком плохой.

Похоже, эта ссылка не работает, попробуйте:archive.mozilla.org/pub/calendar/sunbird/releases/1.0b1/source/…
0

которое соответствует стандарту, известному в настоящее время как Icalendar (преемник предыдущего Vcalendar). Я думаю википедиязапись имеет всю информацию, необходимую для ваших целей, и в простом и понятном формате, но не стесняйтесь обращаться за дополнительной помощью, если это необходимо !!!

1

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

Примечание для новых посетителей: ссылка на документацию для разработчиков Apple больше не работает.
40

Я сделал это (только для 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.

Для этого у меня есть несколько функций CLR в SQL Server 2005+, которые можно использовать для вычисления дат для различных событий. Я нашел следующие формы очень полезными:

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

Реализация вышеперечисленного чертовски весело выяснить

Я знаю, что ему больше 7 лет, это все еще то, как вы подходите к этому, и дают ли более новая версия SQL лучшие варианты запросов?

Похожие вопросы