Back to table of content

Introduction

This section describe the new event model for OBM 2.2. The main change is the merge of calendar events and tasks in one table. Others changes are to follow the standard iCalendar (RFC 2445)

Table 'Event' (used for Calendar events and tasks)

CREATE TABLE Event (
  event_id           serial,
  event_domain_id    integer NOT NULL,
  event_timeupdate   timestamp,
  event_timecreate   timestamp,
  event_userupdate   integer,
  event_usercreate   integer,
  event_ext_id       varchar(255) default '',
  event_type         enum(VEVENT, VTODO, VJOURNAL, VFREEBUSY) DEFAULT VEVENT,
  event_origin       varchar(255) NOT NULL,
  event_owner        integer default NULL,
  event_timezone     varchar(255) default 'GMT',
  event_opacity      enum(OPAQUE, TRANSPARENT) default OPAQUE,
  event_title        varchar(255) default NULL,
  event_location     varchar(100) default NULL,
  event_category1_id integer default NULL,
  event_priority     integer,
  event_privacy      integer,
  event_date         timestamp NOT NULL,
  event_duration     integer NOT NULL default 0,
  event_allday       BOOLEAN default FALSE,
  event_repeatkind   varchar(20) default NULL,
  event_repeatfrequence  integer default NULL,
  event_repeatdays   varchar(7) default NULL,
  event_endrepeat    timestamp default NULL,
  event_color        varchar(7),
  event_completed    timestamp,
  event_url          text,
  event_description  text,
  event_properties   text,
  PRIMARY KEY (event_id)
);
Field Comment
event_type (VEVENT, VTODO, VJOURNAL, VFREE-BUSY)
event_origin ('ui_ajax', 'ui', 'funis', 'tb', 'otlk', 'ics', 'sync', 'minig') + perso
event_opacity event opacity for free/busy info (OPAQUE, TRANSPARENT)
event_completed termination date (timestamp), if present, indicates the task is completed

Event Migration

new fields above +
event_ext_id  -> varchar(255)

Table 'EventEntity'

CREATE TABLE EventEntity (
  evententity_timeupdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  evententity_timecreate timestamp NOT NULL default '0000-00-00 00:00:00',
  evententity_userupdate int(8) default NULL,
  evententity_usercreate int(8) default NULL,
  evententity_event_id   int(8) NOT NULL,
  evententity_entity_id  int(8) NOT NULL,
  evententity_entity     varchar(32) NOT NULL default '0',
  evententity_state      enum(NEEDS-ACTION, ACCEPTED, DECLINED, TENTATIVE, DELEGATED, COMPLETED, IN-PROCESS) NOT NULL default NEEDS-ACTION,
  evententity_required   enum(CHAIR, REQ, OPT, NON) NOT NULL default REQ,
  evententity_percent    float default '0'
);

EventEntity Migration

evententity_state -> 'A'=>ACCEPTED, 'W'=>NEEDS-ACTION, 'R'=>DECLINED
evententity_required -> REQ-PARTICIPANT

Table 'EventAlert' (was 'CalendarAlert')

CREATE TABLE EventAlert (
  eventalert_timeupdate  timestamp,
  eventalert_timecreate  timestamp,
  eventalert_userupdate  integer default NULL,
  eventalert_usercreate  integer default NULL,
  eventalert_event_id    integer,
  eventalert_user_id     integer,
  eventalert_duration    integer NOT NULL default 0
);
CREATE INDEX idx_eventalert_user ON EventAlert (eventalert_user_id);

Table 'EventException' (was 'CalendarException')

CREATE TABLE EventException (
  eventexception_timeupdate   timestamp,
  eventexception_timecreate   timestamp,
  eventexception_userupdate   integer default NULL,
  eventexception_usercreate   integer default NULL,
  eventexception_event_id     integer,
  eventexception_date         timestamp NOT NULL,
  PRIMARY KEY (eventexception_event_id,eventexception_date)
);

Table 'DeletedEvent' (was 'DeletedCalendarEvent')

CREATE TABLE DeletedEvent (
  deletedevent_id        int(8) NOT NULL auto_increment,
  deletedevent_event_id  int(8) default NULL,
  deletedevent_user_id   int(8) default NULL,
  deletedevent_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY (deletedevent_id),
  KEY idx_dce_event (deletedevent_event_id),
  KEY idx_dce_user (deletedevent_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table Todo removed, data migrated to Event, with event_type=VTODO

Todo Migration

todo_domain_id -> event_domain_id
todo_user      -> external link (event_entity, check all joins, add event_type)
todo_privacy   -> event_privacy
todo_date      -> Do nothing (=datecreate in OBM 2.1)
todo_deadline  -> event_date + event_duration=0
todo_dateend   -> Do nothing => event_completed (but not filled in 2.1)
todo_priority  -> event_priority
todo_percent   -> evententity_percent
todo_title     -> event_title
todo_status    -> Do nothing (not filled in OBM)
todo_webpage   -> Do nothing (not filled in OBM)
todo_content   -> event_description

DeletedTodo

?? Keep or insert in DeletedEvent and adding a type in DeletedEvent (VEVENT, VTODO,..) ??

Validate

  • Pierre: OK, but to validate : DeletedTodo (see above) and parent_id in Event
  • Mehdi: OK The exception model can be enhanced by using a parent_id in Event.
  • Thomas:
  • Nicolas:
specification/db/event.txt · Last modified: 2009/11/03 13:57 (external edit)
Driven by DokuWiki