Description of the ECDB module
This module provides a single package, org.bzdev.ecdb, that 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, instances of
{@link org.bzdev.ecdb.ECDB} can generate
iCalendar
appointments and send them via email.
- Cell-phone information. The database contains several tables
that are used, perhaps in conjunction with other services, to map
cell phone numbers to the email addresses for MMS gateways, so that
calendar appointments can be sent via MMS to cell phones. The software
is configured with a list of gateways for common cell-phone carriers.
These have a gateway associated with them is such that the user name in an
email address is the cell-phone number itself concatenated with an "@"
character and then a Internet domain name for the gateway.
- User data. The user data provides a list of "users" who will
attend events, including email addresses and optionally cell phone numbers.
- Event data. Each event has an "owner" described by an owner table.
There is a table for events, providing a description of the event and
whether some activity precedes the event (for example, a cocktail hour or
an introductory lecture). There can be several instances of an event. An
instance is essentially the event with a specific date, time, and location.
A location table defines the possible locations.
- Timing data. There are two tables associated with timing: the
first alarm table and the second alarm table. The first alarm table
assigns an alarm time to a starting time, with weekdays and weekends
distinguished. The software will interpolate for times not explicitly
provided. The table's key includes the user, owner, and location. This
table is used to set an alarm that will allow for travel time to an
event. The second alarm provides a short warning before the event, or
some activity preceding the event, starts. This assumes one is within
walking distance of an event and simply needs a reminder to check the
clock or head over to the event. Which alarms are used are configurable.
- Attendee data.
The attendee data indicates which users will attend which event instances.
As an aid to configuring this table, one can also define a table containing
'series' A series is associated with an owner. A user who 'subscribes' to
a series can then be automatically added to the attendee table for each
instance in the series.
- Pre-event defaults. There is also a pre-event default table.
This indicates if a user's default preference is to attend an activity
before an event or not. It can be set for a user independently for
each event owner.
In the following the owner description is called a "summary" and the
event description is simply called a "description". The "summary" should
be shorter than the description. ECDB will pick the appropriate field
(summary or description) based on the calendar appointment being created.
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 MMS service. Each calendar
appointment is tagged with fields that allow a previously sent
calendar appointment to be updated.
All of the tables use a common convention for column names:
- alarmTime. A column with this name contains a possible time
for an alarm.
- attend. A column with this name contains a boolean value of
true if a user would by default attend some activity before the event;
false if not. The appears in the preEventDefault table and in none of
the other tables.
- attendeeState. A column with this name contains the state
of a row in the attendee table. The attendee state can be
'ACTIVE', 'CANCELLING' or 'CANCELLED'.
- carrier. A column with this name contains the name of a
wireless carrier
- carrierID. A column with this name contains an integer ID
assigned to a carrier.
- cellNumber. A column with this name contains a cell phone number,
not including the country prefix.
- countryPrefix. A column with this name contains a cellphone
number's country prefix (1 for the U.S.).
- createTime. A column with this name contains the time at which
a table row was created.
- description. A column with this name contains a description of
an event.
- emailAddr. A column with this name contains the email address
of a user.
- emailSeqno. A column with this name contains the sequence
number used in calendar appointments, and is incremented after each
change.
- endDate. A column with this name contains the date at which
an event ends.
- endTime. A column with this name contains the time at which
an event ends. This is a time of day and does not include the date.
- eventID. A column with this name contains an integer ID
assigned to an event.
- eventTime. A column with this name contains a possible time
at which an event starts. This is used in the first alarm table, which
lists alarm times for various event times.
- firstName. A column with this name contains a user's first name.
- forEmail. A column with this name contains a flag indicating if
an alarm is intended for a user's email address.
- forPhone. A column with this name contains a flag indicating if
an alarm is intended for a user's cell phone (sent via an MMS gateway).
- idomain. A column with this name contains an internet domain.
Depending on the table, it may be used as the domain name for an MMS
gateway or for a domain name associated with an owner, in which case it
will be used as part of a unique identifier.
- instanceID. A column with this name contains an integer ID
for rows in the event-instance table.
- label. A column with this name contains a short string naming
a row (something short enough to use in a pull-down menu).
- lastEmailTime. A column with this name contains a timestamp
providing the last time at which an email was sent for a user and an
event instance.
- lastName. A column with this name contains a user's last name.
- lastNameFirst. A column with this name contains a boolean
flag indicating if the user prefers his last name (his/her surname) to
be first or not.
- lastPhoneTime. A column with this name contains a timestamp
providing the last time at which an MMS was sent containing a calendar
appointment for the a user and event instance.
- location. A column with this name contains a description of
an event location
- locationID. A column with this name contains an integer ID
labeling a location.
- modTime. A column with this name contains a timestamp indicating
when a table row as modified.
- offset. A column with this name contains an integer giving the
difference in minutes between a second alarm and the time at which
a event or pre-event activity starts.
- ownerID. A column with this name contains an integer ID for an
owner.
- phoneSeqno. A column with this name contains an integer that
provides a sequence number for calendar appointments sent to cell
phones.
- preEventOffset. A column with this name contains the offset in
minutes between a pre-event activity and an event. A value of 0 indicates
the lack of a pre-event activity.
- preEventType. A column with this name contains a name for
a pre-event activity.
- seriesID. A column with this name contains an ID for a series.
Each series is associated with an owner and has a label providing a
textual name.
- setByCarrier. A column with this name contains a boolean value
that indicates if an email address for an MMS gateway was set explicitly
or inferred based on the user's wireless carrier.
- startDate. A column with this name contains the starting date
for an event instance.
- startTime. A column with this name contains the starting time
for an event instance. This is the time of day and does not include the
date.
- status. A column with this name contains a status string.
The values for this string are ACTIVE, NOTACTIVE, or CANCELLED for
user-info rows, and TENTATIVE, CONFIRMED, or CANCELLED for event-instance
rows.
- summary. A column with this name contains a short description
of an owner.
- title. A column with this name contains the title of a user.
A null value indicates no title.
- uid. A column with this name contains a timestamp used in
generating a unique identifier.
- weekday. A column with this name contains boolean values.
A value of true indicates that the row describes a weekday and a
value of false indicates that the row describes a weekend. This
field is used in the first-alarm table.
The database tables can be grouped into four categories. These are
shown in the following four diagrams. In these diagrams, column names
followed with "(PK)" indicate a primary key, and those whose column
names are followed with "(FK)" denote foreign keys. The types are Java
types, not SQL types. The Java types, which are documented
because of the use of JDBC, are the following:
- int. The corresponding SQL type is an integer and the
column cannot contain null values.
- Integer. The corresponding SQL type is an integer type and
the column can contain null values.
- boolean. The corresponding SQL type is a boolean and the
column cannot contain null values
- Boolean. The corresponding SQL type is a boolean and the
column can contain null values
- String. The corresponding SQL type contains character data.
- Date. The Java type is {@link java.sql.Date} and the SQL
type is a date.
- Time. The Java type is {@link java.sql.Time} and the SQL
type is a date.
The tables are shown below in a series of diagrams.
In some diagrams, tables shown in blue (or gray if printed with a black
and white printer) are references to tables from another diagram.
The first diagram shows the tables for user data and some tables
used to map cell phone numbers to the email address used by an MMS
gateway provided by the user's wireless carrier:
The second diagram shows the tables for event owners, locations,
events, and event instances:
The third diagram shows the tables for alarms and for a default
choice for attending activities before an event:
The fourth diagram shows the attendee table and tables defining
a series:
There is a default implementation of these
tables, represented as a set of Java properties saved in the standard
XML format described in the documentation for the class
{@link java.util.Properties}. An ECDB configuration file, desribed in
the manual page ecdb(5), allows a file with this format to be loaded
as well as the default. When both implement the same key, the one
provided in the user-defined file is used.
Each property's key consists of a token naming some text, and
optionally a token representing a database implementation, with these
keys separated by a period. The SQL keys are
- sqlShutdownState. Some databases throw an error when the
database is being shut down. This lists the names of these errors.
- init. By default, this property is called once when a database
is being created. If null, it is ignored. Any SQL statements provided
by this property will be called at the start of the process of
creating database.
- initForAuth. By default, this property is called once
when a database is being created and configured for user
authorization. If null, it is ignored.
- addUser. The value is a format string for generating an
SQL statement to add a user, with the user's password, to the
database. This property is used only if the database was
configured for user authentication and after the
initForAuth SQL statement (if any) was executed.
- initForRoles. By default, this property is called once
when a database is being created and configured to use roles.
If null, it is ignored.
- hasRoles. This property is an SQL statement that will
return a non-empty row set if roles have been configured and an
empty result set otherwise.
- roles. This property is a sequence of SQL statements that
will define the database roles that ECDB uses. It is not called if
roles are not being configured.
- hasSchemas. This is called when tables are being created.
It is an SQL statement that will return a non-empty row set if
the ECDB schema exists and an empty row set if the schema does not
exist. It is used to ensure that an attempt to create the tables
twice will gracefully abort.
- schemas. This is called when tables are being created. It
contains an SQL statement that will create ECDB's schema.
- tables. This is called when tables are being created. It
creates all the tables used by ECDB.
- addCountryPrefixes. This is a sequence of SQL statements
that initializes the CountryPrefixes table. It is called after
the tables are created.
- initialCarriers. This is a sequence of SQL statements
that initializes the Carriers table. It is called after
the tables are created.
- initialCarrierMap. This is a sequence of SQL statements
that initializes the CarrierMap table. It is called after
the tables are created and after the country prefixes and carriers
have been added to the database.
- grants. This is is a sequence of SQL statements that
grants various types of access to tables. It is used as the last
step in creating ECDB tables and applies only if the database uses
roles.
The SQL keys for manipulating the data base are the following (these
should not have to be modified unless the tables were modified in an
incompatible way or a database does not recognize the use of '?' in
prepared statements). Two general keys are the following:
- theSchema. This property provides the name of the
event-calendar schema. It should not be modified.
- setselectFormat. This property provides a format string used to
set a column to the value returned by a select statement.
The next set of keys are for the carrier, carrier mapping, and cellphone
email tables:
- listCarriers. This property provides an SQL statement that
will list all carriers.
- listCarrier. This property provides an SQL statement that
will list one row from the carrier table given its carrier ID.
- listCarriersMatching.This property provides an SQL
statement that will list rows from the carrier table whose name (its
carrier column) matches an SQL pattern.
- addCarrier. This property provides an SQL statement that will
create a new row in the carrier table.
- findCarrier. This property provides an SQL statement that
will create a row set matching a carrier specified by name. This must
be an exact match.
- deleteCarrierByID. This property provides an SQL statement that
will delete a carrier given its ID.
- deleteCarrierMatching. This property provides an SQL
statement that will delete a carrier matching an SQL pattern.
- updateCarrier. This property provides an SQL statement that
will modify a carrier's name given its carrier ID.
- addToCarrierMap. This property provides an SQL statement that
will add a row to the carrier-map table given the values in the idomain
column, the countryPrefix column, and carrierID column in that order.
- updateCarrierDomain. This property provides an SQL statement that
will change the idomain column of a carrier-map table row with a
specified countryPrefix and carrier ID.
- deleteFromCarrierMap. This property provides an SQL statement that
will delete a row from the carrier-map table given the country prefix
and carrierID for that row.
- sortedCarrierMap. This property provides an SQL statement that
will generate a list of the rows in a carrier-map table sorted by the values
in the country prefix and carrier columns.
- findCarrierMap. This property provides an SQL statement that
will generate a list of country prefixes, carrier IDs, carriers, and
an internet domain given patterns matching a country prefix and a carrier.
- findCarrierMapByID. This property provides an SQL statement that
ill generate a list of country prefixes, carrier IDs, carriers, and
an internet domain given patterns matching a country prefix and a carrier
ID.
- getCarrierMapDomain. This property provides an SQL statement that
will generate a column of Internet domain names given a country prefix
and a carrier ID.
- addCellPhoneEmail. This property provides an SQL
statement that will insert a country prefix, a cellphone number,
a cell-phone email address, and a flag indicating if this entry was
set on the basis of a cell-phone carrier or not.
- updateCellPhoneEmail. This property provides an SQL statement that
will update an email address and set-by-carrier flag for a specified
country prefix and cellphone number.
- deleteCellPhoneEmail. This property provides an SQL statement that
will delete a mapping from a country prefix and cellphone number to the
email address of an MMS gateway.
- getCellPhoneEmail. This property provides an SQL statement that
will return a row containing the email address of an MMS gateway, the
set-by-carrier flag, and the modification time of a mapping given the
mapping's country prefix and cell-phone number.
The next set of mappings are for the user-info table:
- insertUserInfo. This property provides an SQL statement that
will add a new user to the user-info table.
- deleteUserInfoByID. This property provides an SQL statement that
will delete a user from the user-info table given a user ID.
- setUserData. This property provides a format string for an
SQL statement that will set the columns for a row with a specified user ID.
- getUserInfoData. This property provides an SQL statement that
will generate a row set containing all columns for a specified user ID
- getUserInfoDataForKeymap. This property provides an SQL
statement that will provide a row set with a row containing the
columns firstName, lastName, lastNameFirst and title for a specified
user ID.
- findUserInfo. This property provides an SQL statement that
will produce a row set with a userID column for rows whose first name,
last name, email address, or cell-phone number matches a pattern.
- listUserInfo. This property provides an SQL statement that
will produce a row set whose columns are userID, firstName, lastName,
lastNameFirst, title, emailAddr, countryPrefix, cellNumber, carrier,
and status for all rows for which the status is not CANCELLED.
- listUserInfoForID. This property provides an SQL statement that
will produce a row set whose columns are userID, firstName, lastName,
lastNameFirst, title, emailAddr, countryPrefix, cellNumber, carrier,
and status for a specific user ID (the primary key so the row set will
contain at most one row). If the status is 'CANCELLED' the row is ignored.
- listUserInfoMatching. This property provides an SQL statement that
will produce a row set whose columns are userID, firstName, lastName,
lastNameFirst, title, emailAddr, countryPrefix, cellNumber, carrier,
and status for rows whose status is not CANCELLED and whose
firstName, lastName, or emailAddr match a pattern.
- listUserInfoMatchingCell. This property provides an SQL
statement that will produce a row set whose columns are userID,
firstName, lastName, lastNameFirst, title, emailAddr,
countryPrefix, cellNumber, carrier, and status for rows whose
status is not CANCELLED and whose cell-phone number matches a pattern.
- listUserInfoMatchingIntlCell. This property provides an
SQL statement that will produce a row set whose columns are
userID, firstName, lastName, lastNameFirst, title, emailAddr,
countryPrefix, cellNumber, carrier, and status for rows whose
status is not CANCELLED and whose countryPrefix concatenated with
its cell-phone number is a string that matches a given pattern.
- findUserInfoMatching. This property provides an SQL
statement that will produce a row set containing the user IDs of
those rows whose status is not CANCELLED and whose firstName, lastName,
or emailAddr columns match a given pattern
- findUserInfoMatchingCell. This property provides an SQL
statement that will produce a row set containing the user IDs of
those rows whose status is not CANCELLED and whose cellNumber field
matches a given pattern.
- findUserInfoMatchingIntlCell. This property provides an
SQL statement that will produce a row set containing the user IDs of
those rows whose status is not CANCELLED and for which the concatenation
of its countryPrefix and cellNumber match a given pattern.
The next set of mappings are for the owner table:
- insertOwner. This property provides an SQL statement that
will add a row to the owner table, providing the row's label, summary,
and idomain columns.
- deleteOwnerByID. This property provides an SQL statement that
will delete a row from the owner table given the row's owner ID.
- listOwners. This property provides an SQL statement that
will list all rows in the owner table
- listOwnersMatching. This property provides an SQL statement that
will produce a rowset containing all the owner-table rows whose label
matches a given pattern
- listOwnersByID. This property provides an SQL statement that
will produce a rowset containing the owner-table row whose owner ID
matches a given pattern (or an empty row set if there is no such row).
- setOwnerData. This property provides a format string for
an SQL statement that will update a column of the owner table for the
row with the given owner ID.
- getOwnerData. This property provides a format string for
an SQL statement that will produce a row set with specific columns
and where the owner ID has a given value (an empty row set if there is
no match).
- findOwnerMatching. This property provides an SQL statement that
will produce a row set whose rows contain only the ownerID column and
whose label matches a given pattern
The next set of mappings are for the location table:
- insertLocation. This property provides an SQL statement that
will add a row to the location table, providing the row's label and
location columns.
- deleteLocationByID. This property provides an SQL statement that
will delete a row from the location table for a given location ID.
- listLocations. This property provides an SQL statement that
will produce a row set that contains all columns for all rows in the
location table.
- listLocationsMatching. This property provides an SQL
statement that will produce a row set containing all columns of the
location table such that a row in the label column matches a pattern.
- listLocationsByID. This property provides an SQL statement that
will produce a row set containing all columns of the
location table with a given location ID.
- setLocationData. This property provides a format string
for an SQL statement that will update columns in the location table
for a given location ID.
- getLocationData. This property provides a format string
for an SQL statement that will select given columns from the location
table for a given location ID. The rowset returned will have 0 or 1
rows.
- findLocationMatching. This property provides an SQL statement that
will produce a row sets containing the locationID column for those rows
in the location table for which the label column matches a given pattern.
The next set of mappings are for the alarm and pre-event tables:
- insertFirstAlarm. This property provides an SQL statement
that will insert a new row into the first alarm table specifying
values for the userID, ownerID, locationID, eventTime, weekday,
alarmTime, forEmail, and forPhone columns.
- deleteFirstAlarm. This property provides an SQL statement
that will delete a row from the firstAlarm table given values for
the userID, ownerID, locationID, eventTime, and weekday columns.
- listFirstALarms. This property provides a format string
for an SQL statement that will produce a row set for rows with a
given userID and additional criteria.
- setFirstAlarmData. This property provides a format string
for an SQL statement that will set fields for a row in the first alarm
table given its primary key.
- insertSecondAlarm. This property provides an SQL statement that
will insert a new row into the second alarm table given values for the
userID, ownerID, locationID, offset, forEmail, and forPhone columns.
- listSecondAlarm. This property provides a format string
for an SQL statement that will produce a row set, each row of which
contains the userID, ownerID, locationID, offset, forEmail, and forPhone
columns of the second alarm table for a particular userID and with
constraints on some of the other fields.
- deleteSecondAlarm. This property provides an SQL statement that
will delete a row from the second alarm table for a given userID,
ownerID, and locationID.
- deleteSecondAlarms. This property provides a format string for
an SQL statement that will delete rows from the second alarm table with
a given userID and with values for other columns.
- setSecondAlarm. This property provides a format string
for an SQL statement that will update columns in a selected row from
the second alarm table. The row is selected by providing the values
of its userID, ownerID, and locationID columns.
- insertPreEventDefault. This property provides an SQL
statement that will add a new row to the pre-event default table.
The columns whose values are set for the new row are the userID,
ownerID, and 'attend' columns.
- listPreEventDefaults. This property provides a format
string for an SQL statement that will produce a row set containing
the userID, ownerID, and 'attend' columns of the pre-event default
table
- getPreEventDefault. This property provides an SQL
statement that will produce the value in the 'append' column of
the pre-event-default table given the values of the userID and
ownerID columns.
- deletePreEventDefault. This property provides a format
string for an SQL statement that will delete a row in the
pre-event-default table given a user ID and some other criteria.
- setPreEventDefault. This property provides an SQL
statement that will set the value in the 'append' column of
the pre-event-default table given the values of the userID and
ownerID columns.
The next set of mappings are for the event table:
- insertEvent. This property provides an SQL
statement that will add a new row to the event table,
given the values for the ownerID, label, and description columns.
- deleteEventByID. This property provides an SQL
statement that will delete a row from the event table given the
value for the row's eventID column.
- listEvents. This property provides an SQL
statement that will produce a row set containing all the rows
from the event table.
- listEventsForOwner. This property provides an SQL
statement that will produce a row set containing all the rows
from the event table with a given value in the row's ownerID column.
- listEventsMatching. This property provides an SQL
statement that will produce a row set containing all the rows
from the event table that have a value in their label column that
matches a given pattern.
- listEventsByID. This property provides an SQL
statement that will produce a row set containing all the row
from the event table with a given value in its eventID column. The
row set will be empty if there is no match.
- setEventData. This property provides a format string for an SQL
statement that will set the value of some columns given the value for
the eventID column.
- getEventData. This property provides a format string for an SQL
statement that will get the value in specified columns for a given value
for the eventID column.
- findEventMatching. This property provides a format string
for an SQL statement that will produce a row set with only an eventID
column given various criteria.
The next set of mappings are for the event-instance table:
- insertEventInstance. This property provides an SQL
statement that will add a row to the event-instance table, given values
for the columns eventID, locationID, preEventType, preEventOffset,
startDate, startTime, endDate, endTime, status.
- deleteEventInstanceByID. This property provides an SQL
statement that will delete a row from the event-instance table given
the instanceID and provided that the status is 'CANCELLED'
- deleteCancelledEventInstances. This property provides an SQL
statement that will delete all rows from the event-instance table
whose status column has the value CANCELLED.
- setEventInstanceData. This property provides a format
string for an SQL statement that will set the value of various
columns for a given instance ID.
- listEventInstance. This property provides an SQL
statement that will produce a row set with the row (if any) matching
a given instance ID.
- listEventInstances. This property provides an SQL
statement that will produce a row set with all columns in the
event-instance table.
- listEventInstancesMatching. This property provides a
format string for an SQL statement that will produce a row set
containing rows in the event-instance table that satisfy some
constraints. The constraints appear in a WHERE clause that can
use the aliases tbli for the event-instance table and tble for
the event table. The WHERE clause will start with
tbli.eventID = tble.eventID, so that selections from either table
share the same event ID.
- findEventInstancesMatching. This property provides a
format string for an SQL statement that will produce a row set
containing an instanceID column. The WHERE clause is supplied by
the user of this property.
The next set of mappings are for the series tables:
- insertSeries.This property provides an SQL
statement that will add a row to the series table, given an
owner ID and a label for the series.
- deleteSeriesByID. This property provides an SQL
statement that will delete a series given its series ID.
- listSeries. This property provides a format string for an SQL
statement that will produce a row set for which each row contains all
the columns in the series table and for which the rows satisfy
constraints provided as a boolean expression for a WHERE clause.
- listSeriesByID. This property provides an SQL
statement that will produce a row set containing the row with the
given series ID.
- setSeries. This property provides a format string for an SQL
statement that will update fields for a specified series ID.
- insertSeriesInstance. This property provides an SQL
statement that will add a row to the series-instance table given a
series ID and an instance ID.
- deleteSeriesInstance. This property provides a format
string for an SQL statement that will delete rows from the
series-instance table given a series of constraints added to the
WHERE clause.
- listSeriesInstance. This property provides a format
string for an SQL statement that will produce row sets. The
caller must supply the WHERE clause.
- listSeriesInstanceByOwner. This property provides a
format string an SQL statement that will produce row sets whose
columns are seriesID and instanceID. The alias stbl refers to the
series table, the alias itbl refers to the event-instance table,
and the alias etbl refers to the event table. There is a constraint
that stbl.instanceID = itbl.instanceID and itbl.eventID = etbl.eventID.
The user can add additional constraints as boolean expressions that
will appear in a WHERE clause.
The next set of mappings are for the attendee table:
- insertAttendee. This property provides an SQL
statement that will add a row to the attendee table given the values
for the userID, instanceID, attendingPreEvent, and seriesID columns.
The attendingPreEvent and seriesID values may be null.
- deleteAttendee. This property provides an SQL statement
that will delete a row in the attendee table given values for the
row's userID and instanceID columns.
- listAttendees. This property provides a format string for
an SQL statement that will produce a row set containing all rows
that meet some criteria. The format string contains a "%s" that would
typically be replaced with a "WHERE" clause.
- setAttendeeData. This property provides a format string
for an SQL statement that will set the values for various columns
of a row given the row's userID and instanceID values. The "%s" in
the format string provides a "%s" between the SET and WHERE tokens
in an SQL UPDATE statement.
- getLastEmailTime. This property provides an SQL
statement that will produce a row set with 0 or 1 rows and only
the attendee tables lastEmailTime column given the values for the
userID and instanceID columns.
- getLastPhoneTime. This property provides an SQL
statement that will produce a row set with 0 or 1 rows and only
the attendee tables lastPhoneTime column given the values for the
userID and instanceID columns.
The next set of mappings are provided for supporting various operations:
- applySeries1. This property provides an SQL
statement that will set the attendee state to CANCELLED for those
rows in the attendee table with a given user ID and series ID
when the instanceID is not in the series-instance table for the
given series ID. This handles a case in which the series-instance
table's entries for a series was changed and where the series was
applied previously.
- applySeries2. This property provides an SQL
statement that will set the attendee state to ACTIVE for rows
in the attendee table with the given userID and seriesID when
the current state for the row is not ACTIVE and the instance ID
for the row is such that there is a corresponding row in the
series-instance table with the given series ID and this instance ID.
This handles the case where an existing entry was cancelled and the
series then reapplied.
- applySeries3. This property provides an SQL
statement that will add a new row to the attendee table with the
specified userID and series ID for all matching instances in the
series-instance table provided that such a row does not already
exist.
- userfields. This property provides an SQL
statement that will produce a row set whose columns are
fullName, emailAddr, countryPrefix, cellNumber, and carrierID for
a specified user ID. The rows are obtained from the user-info table
with the full name set to the concatenation (separated by a space)
of the first and last names, with the name order determined by the
value in the lastNameFirst column.
- calendarData. This property provides a format string for
an SQL statement that will produce a row set containing the
columns userID, ownerID, locationID, instanceID, uid, summary,
idomain, description, preEventType, preEventOffset, startDate,
startTime, endDate, endTime, emailSeqno, phoneSeqno, location,
attendingPreEvent, modtimeO, modtimeL, modtimeE, modTimeL, modtimeA
createTime, lastEmailTime, lastPhoneTime. The format string contains
"%s", which will be replaced with a boolean SQL expression and that
ends with the token AND. This boolean expression can use the following
alias for tables: utbl for the user table, o table for the owner table,
etable for the event table, itbl for the event-instance table, ltbl
for the location table, and atbl for the attendee table.
The createTime column is taken from the attendee table and the
uid column is the concatenation of utbl.uid, otbl.createTime, etable.
createTime, and itbl.createTime. The column idomain is taken from
the owner table. For the modtime columns, the last characters
O, L, E, I, and A refer to the owner table, the location table, the
event table, the instance table, and the attendee table respectively.
- calFirstAlarm. This property provides an SQL
statement that will produce a row set whose columns are eventTime,
alarmTime, forEmail, forPhone, modTime given values in the userID,
ownerID, location ID, weekday columns.
- calSecondAlarm. This property provides an SQL
statement that will produce a row set whose columns are offset,
forEmail, forPhone, and modTime given values in the userID, ownerID
and locationID columns.
- nonAttendees. This property provides a format string for
an SQL statement that will produce a row set whose single column
is named userID, and that lists users who are not in a select
statement that selects users that satisfy constraints provided by
a WHERE clause. The "%s" directive in the format string follows
the token WHERE and must end in the token AND. It can use the
aliases utbl, otbl, etbl, itbl, and atbl that refer to the
user-info table, the owner table, the event table, the
event-instance table and the attendee table
respectively. Additional constraints require that rows from these
tables share the same instance ID, event ID, and owner ID when
those columns are in multiple tables, and also that the starting
date for an event instance is less than the current date.
- getInstanceCount. This property provides a format string
for an SQL statement that will count the number of instance IDs in
the attendee table given some constraints. The constraints are
substituted for "%s" in the format string and must be boolean SQL
expressions that end with the token "AND". The SQL statement supplies
the aliases atbl, itbl, etbl, otbl, and ltbl for the attendee table,
the event-instance table, the event table, the owner table, and the
location table respectively.