Old CalendarDatabaseSchema
Calendar Database Schema informations
This page contains all informations about the internal structure of appointments and related object and some other useful informations about the content of the tables use by the calendar module.
Table prg_dates
The main table of appointments a detailed description which information are stored in this table is described in the table prg_dates structure.
| Field | Type | Description |
|---|---|---|
| created_from | Text | UID of the user who created this appointment (e.g. 'sally') |
| creating_date | Timestamp with time zone | The date when this appointment was created |
| changed_from | Text | UID of user who modified this appointment last (e.g. 'peter') |
| changing_date | Timestamp with time zone | The date when this appointment was modified last |
| user_right | Text | Owner of the of the appointment.
Is normally the same like the UID in created_from except in a private shared folder when the appointment was created by another user that is not the owner of the shared folder. |
| group_right | Text | Possible values are "s" for appointment with one use and "g" for group appointment |
| sid | Text | Unused |
| tid | Text | The folder id where the appointment was saved (i.e. the ID of the calendar-folder) |
| fid | Number | The folder id where the appointment was saved.
If the appointment was stored in a private folder the folder id is always "-1". If the appointment was stored in a public folder the folder id is the id of the public folder and therefore equals with tid |
| order_crit | Text | Not unused anymore |
| timestampfield01 | Timestamp with time zone | starting time |
| timestampfield02 | timestamp with time zone | ending time |
| intfield01 | Integer | The unique id of the appointment |
| intfield02 | Integer | Only used if the appointment was a recurrence or an exception of an recurrence appointment.
There are three diffenrent cases: Case one is that is it a normal appointment. In this case is the value of intfield02 NULL. Case two is the main recurrence appointment. In this case is the value of intfield02 the same like intfield01. Case three is an exception of a recurrence appointment. In this case is the value of intfield02 the value of intfield01 from the main recurrence appointment. |
| intfield03 | Integer | Unused |
| intfield04 | Integer | The duration of the appointment in days.
Is used if the appointment is a recurrence because in this case the field timestampfield02 stores end date of recurrence |
| intfield05 | Integer | The recurrence position of an exception from a recurrence appointment.
Must be > 1 or NULL if it is a normal appointment. |
| intfield06 | Integer | Not used anymore |
| intfield07 | Integer | Unused |
| field01 | Text | The subject of the appointment. This field must not be null. |
| field02 | Text | Location |
| field03 | Text | 'Display As'-Type holds possible values are:
date1 = Reserved date2 = Temporary date3 = Absent date4 = free |
| field04 | Text | Comment/Note |
| field05 | Text | Possible values are 'gtz' if the appointment is stored as a full time appointment or NULL if it is a normal appointment. |
| field06 | Text | Holds a string representation of the recurrence information.
All attributes in this string are seperated with a "|". A detailed description of the attributes is descriped in Recurrence field attributes. This field must not be NULL. |
| field07 | Text | Comma-separated list of sequence numbers.
Every number is the position of a deleted appointment of the recurrence appointment. |
| field08 | Text | Comma-separated list of a id's.
Every id is a reference of a modified exception of the recurrence appointment. |
| field09 | Text | Unused |
| field10 | Text | Completely unused |
| field11 | Text | Completely unused |
| field12 | Text | Completely unused |
| field13 | Text | Completely unused |
| field14 | Text | Completely unused |
| field15 | Text | Completely unused |
| Attribute | Type | Description |
|---|---|---|
| ds | Text | Possible values no, daily, weekly, monthly or yearly |
| ds_ends | Long | Milliseconds from 01.01.1970 in UTC.
The long descripes the end date of the recurrence appointment. The end date must be a date without times. |
| ds_start | Long | Milliseconds from 01.01.1970 in UTC.
The long descripes the start date of the recurrence appointment. The start date must be a date without times. |
| daily_value | Integer | The recurrence interval for daily appointments |
| weekly_value | Integer | The recurrence interval for weekly appointments |
| weekly_day_1 | Integer | Can only be the value "1".
If this attributes exists the sunday if enabled for the weekly recurrence. |
| weekly_day_2 | Integer | Can only be the value "2".
If this attributes exists the monday if enabled for the weekly recurrence. |
| weekly_day_3 | Integer | Can only be the value "3".
If this attributes exists the tuesday if enabled for the weekly recurrence. |
| weekly_day_4 | Integer | Can only be the value "4".
If this attributes exists the wednesday if enabled for the weekly recurrence. |
| weekly_day_5 | Integer | Can only be the value "5".
If this attributes exists the thursday if enabled for the weekly recurrence. |
| weekly_day_6 | Integer | Can only be the value "6".
If this attributes exists the friday if enabled for the weekly recurrence. |
| weekly_day_7 | Integer | Can only be the value "7".
If this attributes exists the saturday if enabled for the weekly recurrence. |
| monthly_value_month | Integer | The recurrence interval for monthly appointments.
Is used in comination with "monthly_value_day" |
| monthly_value_day | Integer | Day in month when the appointment is shown |
| monthly2_value_month | Integer | The recurrence interval for monthly appointments.
Is used in comination with "monthly2_day" and "monthly_recurrency" |
| monthly2_day | Integer | Possible values are:
1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thurday 6 = Friday 7 = Saturday -1 = Day -2 = Weekday -3 = Weekend day |
| monthly2_reccurency | Integer | Possible values are:
1 = First occurrence 2 = Second occurrence 3 = Third occurrence 4 = Forth occurrence -1 = Last occurrence |
| yearly_value_day | Integer | Day in month when the appointment is shown.
Only in combination with ds=yearly and yearly_month |
| yearly_month | Integer | The month when the appointment is shown. Possible values are:
0 = January 1 = February 2 = March 3 = April 4 = May 5 = June 6 = July 7 = August 8 = September 9 = October 10 = November 11 = December |
| yearly2_reccurency | Integer | Possible values are:
1 = First occurrence 2 = Second occurrence 3 = Third occurrence 4 = Forth occurrence -1 = Last occurrence |
| yearly2_day | Integer | Possible values are:
1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thurday 6 = Friday 7 = Saturday -1 = Day -2 = Weekday -3 = Weekend day |
| yearly2_month | Integer | Possible values are:
0 = January 1 = February 2 = March 3 = April 4 = May 5 = June 6 = July 7 = August 8 = September 9 = October 10 = November 11 = December |
Table: prg_dates_members
This table contains all user participant of the appointment with the attributes displayname, confirm, confirm reason and the personal folder id. Detailed informations about all fields are described in the table below prg_dates_member structure.
| Field | Type | Description |
|---|---|---|
| object_id | Integer | A reference to the appointment id (prg_dates.intfield01) |
| member_uid | Text | The UID of user participant or an email address if the participant is an external participant. |
| member_name | Text | The display name of the user participant or NULL if the participant is an external participant. |
| confirm | Text | Possible values are:
n = no confirmation t = accepted f = declined |
| reason | Text | The confirm reason if the user has accepted or declined |
| pfid | Integer | The personal folder of the user or a -99 if the participant is an external participant. |
Table: prg_dates_resources
This table contains all resource or resource group participants of an appointment. Detailed informations about all fields in the table prg_dates_resources are described in the table below prg_dates_resources structure.
| Field | Type | Description |
|---|---|---|
| object_id | Integer | A reference to the appointment id (prg_dates.intfield01) |
| group_right | Text | The resource group id. If this value is set the field user_right must be null. |
| user_right | Text | The resource id. If this value is set the field group_right must be null. |
Table: prg_date_rights
This table contains the user and group participant of an appointment without resolving the members of the group. Detailed informations about all fields in the table prg_date_rights are described in the table below prg_date_rights structure.
| Field | Type | Description |
|---|---|---|
| object_id | Integer | A reference to the appointment id (prg_dates.intfield01) |
| group_right | Text | The group id. If this value is set the field user_right must be null. |
| user_right | Text | The user id (UID). If this value is set the field group_right must be null. |
Table: prg_date_notification
This table contains the alarm for all user participants of the appointment. Detailed informations about all fields in the table prg_date_notification are described in the table below prg_date_notification structure.
| Field | Type | Description |
|---|---|---|
| object_id | Integer | A reference to the appointment id (prg_dates.intfield01) |
| member_uid | Text | The user id (UID). |
| notification | Text | The alarm in minutes before the start of the appointment. |
Table: prg_dates_times
This table is not used anymore