This document describes structure of SQL database used by Callchange system. Database store data with longer durability while
dynamic data are stored in memory. Database consists of these tables:
Table CC_LINES - list of lines
| ID varchar (16) |
Identification line number |
| LINE_NAME varchar (150) |
Stand name |
| LINE_NUMBER varchar (50) |
Phone number |
| DESCRIPTION varchar (254) |
Popis linky |
| ENABLED varchar (1) |
Monitoring turned on. |
| LINE_TYPE varchar (5) |
Type of line |
| DIAL_AFTER_INITIATE varchar (1) |
0/1 = before dialing on this line (do not) show dialog asking for pick up the phone |
| LICENCED varchar (1) |
is line counted into number of licenses? |
back to top
Table CC_LINE_RIGHTS - access right for lines
| IDENT varchar (100) |
User identification in form M:machine_name (M:name), or U:user_name (NT user: U:domain\user).
% can be used as a wildcard. |
| LINE_NAME varchar (100) |
Stand name. % can be used as a wildcard. |
| LINE_RIGHT varchar (20) |
Rights: m (monitor), o (operate), or mo for both. |
back to top
Table CC_CONFIG - global configuration
Global configuration is stored in this table. For real operation only ITEM and VAL items are necessary; others are used only for
information and for configuration application.
| ITEM varchar (100) |
Config item name. |
| VAL varchar (512) |
Item value. |
| DESCRIPTION varchar(256) |
Item description / help. |
| GROUP_ID varchar(150) |
Group name, which is item member of. |
| ORDER_ID varchar(150) |
Sequence in group. |
| V_TYPE varchar(10) |
Item content type. |
| V_RANGE varchar(254) |
Allowed item content range. |
| VISIBLE varchar(1) |
"1" = visible in administrative application, "0" = invisible. |
V_TYPE item defines, what data type can be used on the given place; V_RANGE defines allowed range / valid values.
| V_TYPE |
V_RANGE |
Description |
| TEXT |
|
Text value of any length, even empty |
| TEXT |
N|M (e.g. 1|10) |
Text value with given minimal and maximal length (i.e. request for non-empty text can be entered as 1|512) |
| CRYPT_TEXT |
|
Encrypted text value, e.g. for storing password |
| INT |
|
Integer of any size, non-empty, both positive and negative |
| INT |
N|M (e.g. -5|15) |
Integer in given range (include range limits) |
| COMBO |
text1=val1|text2=val2 |text3=val3|...(e.g. No=0|Yes=1) |
Combobox, which offers individual items from V_RANGE (before = sign) and appropriate value is stored into DB (after = sign) |
| HEADING |
|
Displays text from DESCRIPTION as a heading (big letters). VAL and V_RANGE aren't taken into consideration. |
| LABEL |
|
Displays text from DESCRIPTION as a text (normal letters). VAL and V_RANGE aren't taken into consideration. |
back to top
Table CC_CALLS - finished calls archive
| ID varchar (16) |
Call ID (key into CC_OBJ_ATR and CC_EVENTS) |
| LINE varchar (50) |
Line where call took place. |
Line where call took place. See Call (memory store and database) |
| CONNECTED varchar (1) |
Was call connected to the operator? |
| DIRECTION varchar (3) |
Call direction. |
| REMNUM varchar (20) |
Other party number. |
| TIM_NEW varchar (17) |
Time of arrival / creation. |
| TIM_CN varchar (17) |
Time of connection to the operator. |
| TIM_DCN varchar (17) |
Hangup time. |
| TIM_ACT varchar (17) |
Last update time of the record. |
| RC varchar(5) |
Call result (0 = error, 1 = success). |
| RC2 varchar(50) |
End call code. |
| STATE varchar (20) |
|
| LEN varchar (6) |
Call duration. |
| CCCALL varchar(30) |
Callcenter call number to which is record related to. |
| CC varchar(5) |
It is Callcenter call (1) or physical call (0) |
| AGENT_ID varchar(16) |
Agent's ID, which processed this call. |
| CALL_GROUP_ID varchar(16) |
|
| CC_SCRIPT_NAME varchar(30) |
|
| GROUP_ID numeric(18, 0) |
|
back to top
Table CC_EVENTS - messages for finished calls
| ID varchar (16) |
Record ID |
| OBJ_ID varchar (16) |
Object (call) ID, which is record related to. |
| EV_TIME varchar (17) |
Timestamp. |
| EV_TYPE varchar (16) |
Message type. |
| EV_VAL varchar (20) |
Message content. |
| EV_DESCR ntext(16) |
Message description. |
back to top
Table CC_OBJ_ATR - other properties for finished calls
| OBJ_ID varchar (16) |
Object (call) ID, which is record related to. |
| ATR_NAME varchar (50) |
Attribute name. |
| ATR_VALUE varchar (50) |
Attribute value. |
back to top
Table CC_AGENTS - list of agents
CallCenter edition only.
| ID numeric (18,0) |
Record ID. |
| IDENT varchar (50) |
Logon name. |
| DESCRIPTION varchar (254) |
Description - used for groups. |
| DISPLAYNAME varchar (50) |
Display name. |
| EMAIL varchar (50) |
E-mail address. |
| PWD varchar (50) |
Password. At this time in plain text. |
| LASTLOGON varchar (17) |
Last logon date/time. |
| LOGGED varchar (1) |
Is currently logged in? "y"/"n". |
| ISAGENT varchar (1) |
Can be logged on as an agent? "y"/"n". |
| ISSUPERAGENT varchar (1) |
If it is agent - can change its group assignment? "y"/"n". |
| ISAGENTADMIN varchar (1) |
Is it agent administrator - can see agent's load and to assign agents? "y"/"n". |
| ISCCADMIN varchar (1) |
CallCenter admin - can change configuration etc. "y"/"n". |
| ISSTATS varchar (1) |
Is allowed to see statistics. "y"/"n". |
| LAST_CALL_END varchar (17) |
last call end time |
| SESS_ID varchar (16) |
_ |
back to top
Table CC_KNOWLEGDE - types of available knowledge
CallCenter edition only.
| ID varchar (16) |
Record ID. |
| NAME varchar (24) |
Knowledge name. |
| DESCRIPTION varchar (254) |
Description. |
back to top
Table CC_KNLG_ASSIGN - knowledge and operator assignment
CallCenter edition only.
| KNOWLEDGE_ID varchar (16) |
Knowledge ID. |
| AGENT_ID numeric (18,0) |
Agent ID. |
| KNLEVEL int |
Knowledge level 0-100; 0 = nothing, 100 = maximum. |
back to top
Table CC_BLACKLIST - blacklists and whitelists
Enterprise and CallCenter editions.
| ID numeric (18,0) |
Record ID. |
| LNAME varchar (16) |
Blacklist name. By default "BLACK" and "WHITE" only. |
| PHNUMBER varchar (30) |
Normalized phone number. |
| TMS_INSERT varchar (17) |
Timestamp of insert. |
| TMS_EXPIRY varchar (17) |
Timestamp of expiration; if it doesn't expire, set 9.9.9999 00:00. |
| AGENT_ID varchar (16) |
ID of agent, which created the record. |
| DESCRIPTION varchar (60) |
Description - e.g. reason of creating. |
back to top
Table CC_ACD_QUEUE - callcenter input lines
CallCenter edition only.
| ID bigint IDENTITY |
Record ID. |
| NAME varchar (30) |
Input queue name. |
| DESCRIPTION varchar (254) |
Description. |
| STAND varchar (30) |
Physical line name, where calls are directed to. |
| SCRIPT varchar (30) |
Handler script name. |
back to top
Table CC_ASSIGN - operator assignment to agent groups
CallCenter edition only.
| ID numeric (18,0) |
Record ID. |
| AGENT_ID numeric (18,0) |
Agent ID. |
| GROUP_ID numeric (18,0) |
Agent group ID. |
| CONNECTED varchar (1) |
1 = agent is connected to this group, 0 = agent is not connected. |
back to top
Table CC_OPHISTORY - history of individual operators
CallCenter edition only. Contains operator's history - working time, pauses, etc.
| ID varchar (16) |
Record ID. |
| AGENT_ID varchar (16) |
Agent ID. |
| ACODE varchar (8) |
Code of action: LOGON - operator's logon. Closed via LOGOFF.
SLEEP - operator has a pause. Closed via AWAKE. |
| ASUBCODE varchar (8) |
Detailed action description. Related mainly to SLEEP - pause reason; defined by customer application. |
| TSM_START varchar (17) |
Timestamp of start. |
| TSM_END varchar (17) |
Timestamp of end. Filled in at end of action. |
| T_LENGTH numeric(18, 0) |
Action duration in seconds. Filled in at end of action. |
| ISOPEN varchar (1) |
1 = not closed yet; 0 = closed. |
| WORK_LENGTH numeric(18, 0) |
"LOGON" record type only - total working time (i.e. when agent wasn't in SLEEP state). Filled in at end of action. |
| SLEEP_LENGTH numeric(18, 0) |
"LOGON" record type only - total time of being in SLEEP state. Filled in at end of action. |
back to top
Table CC_SCRIPTS - scripts for individual ACD groups
CallCenter edition only.
| ID bigint |
Record ID. |
| NAME varchar (30) |
Script name. |
| DESCRIPTION varchar (254) |
Script description. |
| VERSION int |
Script version. Higher = newer. |
| TMS_CHANGE varchar (17) |
Date of saving. |
| NAME_CHANGE varchar (30) |
User name who saved this version. |
| CONTENT text(16) |
Script content. |
back to top
Table CC_CHANGED - information about configuration changes
CallCenter edition only. This table contains information about last change time of any configuration item. It is used by server
and router to read current information.
| NAME varchar (16) |
Name of changed configuration. Defined names: CONFIG - CC configuration
ACD - settings of individual ACD
SCRIPTS - scripts
ASSIGN - operators assignment to operator groups. |
| TMS varchar (17) |
Timestamp of change. |
back to top
Table CC_BL_APP - special application for blacklist
In all editions. Used by application for blacklist. Contains configuration of operation which are carried out in dependency
on caller's number and number being called. Used for configurationm of more applications.
| ID numeric(18,0) IDENTITY |
Record identifier, autoincrement |
| APP varchar(50) |
Application name. |
| CALLED_LINE varchar(50) |
Called line number or range of numbers. |
| CHECK_TYPE varchar(16) |
Manner in which numbers are checked. Possible values:
|
| CHECK_PARAM varchar(150) |
Number checking parameters. For CHECK_WHITE and CHECK_BLACK contains list name (LNAME) from CC_BLACKLIST table. |
| OPERATION varchar(50) |
Operation type, which is necessary to carry out:
- DIVERT - divert the call to line number specified in OP_PARAM
- HANGUP - hang up
|
| OP_PARAM varchar(150) |
Parameters for OPERATION. |
back to top
Table CC_CALENDAR_NAMES - list of calendars
CallCenter edition only. Contains list of calendars.
| ID int IDENTITY |
Record identifier, autoincrement |
| NAME varchar(20) |
Calendar name. |
| DESCRIPTION varchar(100) |
Description. |
back to top
Table CC_CALENDAR_STD - standard calendar
CallCenter edition only. Contains application's standard calendar.
| ID int IDENTITY |
Record identifier, autoincrement |
| ID_CAL int |
Calendar ID, which is record related to. |
| DAY_WEEK tinyint |
Day of week 1-7, 1 = Monday. |
| TIME_START char(4) |
Start of working hours in form hhmm. |
| TIME_END char(4) |
End of working hours in form hhmm. |
back to top
Table CC_CALENDAR_EXC - exclusions from calendar
CallCenter edition only. Contains exlusions from application's standard calendar - for example 23.12. we work only to 12:00
and 24.12. is a holiday.
| ID int IDENTITY |
Record identifier, autoincrement |
| ID_CAL int |
Calendar ID, which is record related to. |
| YEARMONTHDAY char(8) |
Date in form YYYYMMDD. |
| DAYTYPE char(1) |
F = free time, W = working hours. |
| TIME_START char(4) |
Start of exclusion in form hhmm. |
| TIME_END char(4) |
End of exclusion in form hhmm. |
back to top
Table CC_CFG_CLASS - records for "configuration classes"
Configuration class is something like item list or form to fill in. Example: agent.pause
| CLASS varchar(150) |
class name |
| DESCRIPTION varchar(150) |
description |
| ENABLE_ADD char(1) |
adding new instances (UNIT) of this class enabled 0/1 |
| ENABLE_DELETE char(1) |
deleting instances (UNIT) of this class enabled 0/1 |
| ORDER_ID varchar(50) |
sorting field of this table |
back to top
Table CC_CFG_UNITS - list of "configuration units"
Example can be list of operator pause types. Every record contains configuration class name (CLASS) and instance name (UNIT).
Example: CLASS='agent.pause', UNIT='lunch'
| CLASS varchar(150) |
class name |
| UNIT varchar(150) |
instance name |
When records are added to CC_CFG_UNITS table, trigger is run which creates new records in CC_CONFIG corresponding to newly created instance
of UNIT name. Is is done by copying configuration template stored also in CC_CONFIG table.
Template records have ITEM field in this format: CFG_TEMPLATE.[class name].[item name]
Example: CFG_TEMPLATE.agent.pause.maxlen
Trigger creates a new record from template with ITEM field in this format:
[class name].[UNIT].[item name]
Example: agent.pause.lunch.maxlen
GROUP_ID field is created like this:
[template GROUP_ID][class name].[UNIT]
Other items are copied. Substrings '%CLASS%' and '%UNIT%' in VAL and DESCRIPTION fields are replaced by items of record being created
in CC_CFG_UNITS table.
Record deletion is done (if enabled) indirectly by deleting record in CC_CFG_UNITS table. Trigger in this table ensures deletion of
corresponding records in CC_CONFIG table.
back to top
Table CC_APPS - list of L3 client applications handled by L2CC
| NAME varchar(50) |
application name |
back to top
Table CC_GRPS - agent groups handling individual campaigns
Agent can be concurrently a member of more groups.
| ID numeric(18, 0) IDENTITY |
record ID |
| IDENT varchar (50) |
short text identifier of the group |
| DESCRIPTION varchar(254) |
group description |
| DISPLAYNAME varchar (50) |
display name |
| RTYPE varchar (1) |
handling of incoming/outgoing calls |
| CTIME int |
clericle time (time necesary for call administration after its finishing |
| PRIO int |
priority |
| CLIENT_APP varchar (50) |
default L3 application name |
| MIN_FREE_AGENTS int |
how many agents is reserved to handle this campaign |
| ANSWER_TIMEOUT int |
time for phone pick up |
back to top
CC_GRPS_APPS - assignment of L3 applications to agent groups
| GRPS_ID numeric(18, 0) |
group ID |
| APP_NAME varchar (50) |
application name |
back to top
Table CC_GRPS_RULES - assignment of rights to agent groups
| GRPS_ID numeric(18, 0) |
agent group ID |
| RULE_ACTION varchar (50) |
action name being enabled |
| RULE_PARAM varchar (50) |
action parameter |
back to top
Table CC_LINE_ASSIGN_RECORDER - assignment of recorders to lines
| LINE_NAME varchar (150) |
line name, may contain wildcards |
| RECORDER_NAME varchar (50) |
recorder name |
| PRIORITY int |
reservation for requests of given priority |
back to top
Table CC_LIST_LIST - list of list names of dedicated phne numbers
| LNAME varchar (16) |
list name |
| DESCRIPTION varchar (150) |
list description |
back to top
Table CC_RECORDER_TYPES - list of call recorder types
| RECORDER_TYPE varchar (50) |
recorder type |
back to top
Table CC_RECORDERS - list of installed call recorders
| RECORDER_NAME varchar (50) |
recorder name |
| RECORDER_TYPE varchar (50) |
recorder type |
| INIT_STRING varchar (250) |
initialization parameters for recorder |
| CAPACITY int |
maximum number of concurrently recorded calls |
| MAX_USE_LOW int |
how many calls of low priority maximaly can be recorded |
| MAX_USE_NORMAL int |
how many calls of normal priority maximaly can be recorded |
back to top
Table CC_RULES - CC right list
| RULE_ACTION varchar (50) |
action name |
| DESCRIPTION varchar (150) |
action description |
back to top
Table CC_SEC_GRP_MEMBERS - list of security groups members
| AGENT_ID numeric(18, 0) |
agent identifier |
| SEC_GRP_NAME varchar (50) |
security group name |
back to top
Table CC_SEC_GRP_RULES - rights asigned to security groups
| SEC_GRP_NAME varchar (50) |
security group name |
| RULE_ACTION varchar (50) |
name of allowed action |
| RULE_PARAM varchar (50) |
action parameter |
back to top
Table CC_SEC_GRPS - CC security group list
| SEC_GRP_NAME varchar (50) |
group name |
| DESCRIPTION varchar (150) |
group description |
back to top
Table CC_RECORDS -
| RECORD_ID varchar (16) |
- |
| CALL_ID varchar (16) |
- |
| START_TIME varchar (17) |
- |
| LENGTH varchar (8) |
- |
| URL varchar (280) |
- |
back to top
