The following sql scripts are available to set up the database schema for Oracle. They can be found in <install_dir>/cmt.
Script Description newuser.sql Typical set-up for a user/password of redline/redline. This should be amended as necessary and executed as Oracle database administrator. create_tables.sql Create the database tables. This should be executed as the database user, e.g. redline/redline. populate.sql Minimal group and user table population. This should be edited as necessary and executed as the database user, e.g. redline/redline. drop_tables.sql Script to drop all tables in case you need to re-populate.
The first 3 scripts should be executed in sequence, the last one is for contingencies.
The schema itself is as follows ( columns shown in green can be used as primary keys):
Table: RL_ACCOUNTS
| Column | Type | Xref | Description |
| user_id | numeric unique not null | User id | |
| user_name | varchar(20) unique not null | User login name | |
| user_pwd | varchar(20) not null | User login password | |
| user_display_name | varchar(50) | User display name |
Table: RL_USERGROUPS
| Column | Type | Xref | Description |
| group_id | numeric unique not null | Group id | |
| group_name | varchar(20) unique not null | Group name | |
| description | varchar(128) | Group description |
Table: RL_GROUPMEMBERS
| Column | Type | Xref | Description |
| group_id | numeric not null | RL_USERGROUPS/group_id | Group id |
| user_id | numeric not null | RL_ACCOUNTS/user_id | User id |
| rights | numeric default 1 not null | User access rights |
Table: RL_CONNECTIONS
| Column | Type | Xref | Description |
| user_id | numeric not null | RL_ACCOUNTS/user_id | User id |
| machine | varchar(64) default '' not null | Client host name/ip | |
| port | numeric default 0 not null | Client callback port |
Table: RL_SESSIONS
| Column | Type | Xref | Description |
| session_id | numeric unique not null | Session id | |
| session_name | varchar(32) unique not null | Session name | |
| user_id | numeric not null | RL_ACCOUNTS/user_id | Session initiator |
| group_id | numeric not null | RL_USERGROUPS/group_id | Session initiator |
| record_events | numeric(1) not null | Whether events are recorded | |
| session_type | numeric default 0 not null | RL_SESSION_TYPE/session_type | Session type |
| start_ticks | numeric not null | When the session started | |
| finish_ticks | numeric default 0 not null | When the session finished |
Table: RL_SESSION_EVENTS
| Column | Type | Xref | Description |
| event_id | numeric not null | Event id | |
| session_id | numeric not null | RL_SESSIONS/session_id | Session id |
| user_id | numeric not null | RL_ACCOUNTS/user_id | User associated with the event |
| ticks | numeric not null | When the event occurred | |
| event_type | numeric not null | RL_EVENT_TYPE/event_type | Event type |
| message_id | numeric not null | RL_MESSAGES/message_id | Message id |
Table: RL_MESSAGES
| Column | Type | Xref | Description |
| message_id | numeric unique not null | Message id | |
| session_id | numeric not null | RL_SESSIONS/session_id | Session id |
| user_id | numeric not null | RL_ACCOUNTS/user_id | Message sender |
| ticks | numeric not null | When the message was sent | |
| message_name | varchar(32) not null | Message name | |
| message_type | numeric not null | RL_MESSAGE_TYPE/message_type | Message type |
| immed | numeric(1) default 0 not null | Whether the sender wanted the message to be executed immediately | |
| description | varchar(128) | Message description | |
| func | varchar(128) | Application macro to process the message | |
| meat | blob default empty_blob() | Message meat |
Table: RL_EVENT_TYPE
| Column | Type | Xref | Description |
| event_type | numeric unique not null | Event type index | |
| description | varchar(128) not null | Event type description |
Values for event_type are as follows:
1 - Session was created/started
2 - Session was resumed
3 - Session was finished
4 - User joined a session
5 - User left a session
6 - User sent a message
Table: RL_MESSAGE_TYPE
| Column | Type | Xref | Description |
| message_type | numeric unique not null | Message type index | |
| description | varchar(128) not null | Message type description |
Table: RL_SESSION_TYPE
| Column | Type | Xref | Description |
| session_type | numeric unique not null | Session type index | |
| description | varchar(128) not null | Session type description |
Table: RL_RIGHTS
| Column | Type | Xref | Description |
| rights | numeric unique not null | Rights mask value | |
| description | varchar(128) not null | Event description |
Mask values for rights are as follows:
0 - User has no rights
1 - User has participate rights, i.e. can participate in sessions, send and receive messages
2 - User has control rights, i.e. can initiate and terminate sessions
4 - User has admin rights, i.e. can modify user/group tables
8 - User has receive rights, i.e. can receive messages