The ECDB class provides an API for initializing and manipulating a relational database representing an event calendar. In addition to methods that select, insert, update, or delete table entries, ECDB can generate iCalendar appointments and send them via email. Calendar appointments include the starting date, starting time, ending date, and ending time for an event, whether there is some activity before an event and when that starts, and two optional alarms, with the times set on a per-user basis. The class will also send messages with or without calendar appointments to users to either an email address or to the user's SMS service. Each calendar appointment is tagged with fields that allow a previously sent calendar appointment to be updated.

The following tables are provided (the types shown are Java types, not relational-database types):

  • CountryPrefix. The columns are countryPrefix (an instance of String) and label (an instance of String). The primary key is countryPrefix.
  • Carrier. The columns are carrierID (an int) and carrier (a String). The primary key is carrierID.
  • CarrierMap. The columns are countryPrefix (an instance of String), carrierID (an int), and idomain (an instance of String). The primary key consists of countryPrefix and carrierID.
  • CellPhoneEmail. The columns are countryPrefix (an instance of String), cellNumber (an instance of String), emailAddr (an instance of String), and setByCarrier (a boolean). This table is used as a cache that maps a cell-phone number to the email address of its SMS gateway. The primary key consists of countryPrefix and cellNumber.
  • UserInfo. The columns are userID (an int), firstName (an instance of String), lastName (an instance of String), lastNameFirst (a boolean), title (an instance of String), emailAddr (an instance of String), countryPrefix (an instance of String), cellNumber (an instance of String), carrierID (an instance of String), and status (an instance of String). The primary key is userID.
  • Owner. The columns are ownerID (an int), label (an instance of String), summary (an instance of String). The primary key is ownerID.
  • PreEventDefault. The columns are userID (an int), ownerID (an int), useDefault (a boolean). The primary key consists of userID and ownerID.
  • Location. The columns are locationID (an int), label (an instance of String), location (an instance of String). The primary key is locationID.
  • FirstAlarm. The columns are userID (an int), ownerID (an int), locationID (an int), eventTime (an instance of Time), weekday (a boolean), alarmTime (an instance of Time), forEmail (a boolean), and forPhone (a boolean). The primary key consists of userID, ownerID, and locationID.
  • SecondAlarm. The columns are userID (an int), ownerID (an int), locationID (an int), offset (an int), forEmail (a boolean), and forPhone (a boolean). The primary key consists of userID, ownerID, and locationID.
  • Event. The columns are eventID (an int), ownerID (an int), label (an instance of String), and description (an instance of String). The primary key is eventID.
  • EventInstance. The columns are instanceID (an int), eventID (an int), locationID (an int) preEventType (an instance of String), preEventOffset (a non-negative int), startDate (an instance of Date), startTime (an instance of Time), endDate (an instance of Date), endTime (an instance of Time), and status (an instance of String). The primary key is instanceID.
  • Series. The columns are seriesID (an int), ownerID (an int), and label (an instance of String). The primarykey is seriesID.
  • SeriesEventInstances. The columns are seriesID (an int) and instanceID (an int). The primary key consists of seriesID and instanceID.
  • Attendee. The columns are userID (an int), instanceID (an int), attendeeState (an instance of String), attendingPreEvent (a boolean), and seriesID (an Integer). The columns emailSeqno (an int) and phoneSeqNo (an int) are maintained by ECDB so that updates to calendar appointments will have an appropriate sequence-number field. The primary key consists of userID and instanceID.
Some of the table have columns that are maintained automatically using triggers. These are typically timestamps, and are used to generate unique identifiers and sequence numbers for iCalendar objects.

This database also uses the following roles (there is an option for whether roles are configured or not):

  • ECADMIN: SELECT, INSERT, UPDATE, and DELETE are granted on all tables.
  • ECOWNER: SELECT is granted on all tables. INSERT, UPDATE, and DELETE are granted on CellPhoneEmail, Owner, Location, Event, EventInstance, Series, SeriesInstance
  • ECUSER: SELECT is granted to all tables. INSERT, UPDATE, and DELETE are granted on UnserInf, PreEventDefault, FirstAlarm, SecondAlarm, and Attendee.
  • PUBLIC: This is a standard role. SELECT is granted to PUBLIC for CountryPrefix, Carrier, and CarrierMap.