A list with a basic description of SUP 2.2 internal tables

Title: 

A list with a basic description of  SUP 2.2 internal tables.

Purpose: 

Show the table schema and a description of the Sybase Unwired Platform  2.2 internal tables to help in troubleshooting.

Overview: 

SUP 2.2 uses 4 databases containing  161 internal tables( CDB DB contains 101, Cluster DB 30, Montior DB 13, Domain logging DB 17). These tables are used to track  user registration,  usernames, applications, device id's,  MMS administrators, Security and much more.   These tables are also used to store SUP Monitoring, domain logging and clustering information.
Note that not every table below has a description.at this time.

Solution:

 ******** Default CDB (default.db) *********

1) create table mms_reg_users (
user_id integer not null default autoincrement, // User ID, an identity column
user_name varchar(128) not null, // The real username of device client application
security_domain varchar(36) not null, // Security Configuration Name
reg_time timestamp not null, // Registration time for the user
last_login_time timestamp not null, // Last login time of the user
is_locked integer not null, // Identify whether the user is locked
primary key (user_id),
unique (user_name ) )
--Table: mms_reg_users, mainly used for the user registration. All the user information will be stored in this table.

2) create table mms_devices_users_info (
id integer not null default autoincrement,
user_name varchar(128) not null,
deviceid varchar(200) not null,
primary key(id), )
--This table is used to save the related deviceid information for each username.

3) create table mms_apps_users_info (
id integer not null default autoincrement,
user_name varchar(128) not null,
app varchar(200) null,
deviceid varchar(200) not null,
script_version varchar(128) null,
primary key(id),)
--This table is used to save the username, app, deviceid and package script version information

4) create table mms_domain_users_info (
domain varchar(36) not null,
user_name varchar(128) not null,
primary key (domain,user_name) )
--This table is used to save the domain, user_name relation information

5) create table mms_domain_devices_info (
domain varchar(36) not null,
deviceid varchar(48) not null,
unique (domain,deviceid) )
--This table is used to save the domain and deviceid relation information

6) create table mms_reg_users_lock (
id integer not null ,
"lock" integer not null,
primary key (id) )
--This table is used to save the user lock information.

7) create table mms_license_table_oper (
num integer not null ,
primary key (num) )
--This table is used to save a flag during the iMO2MMS sync when the SUP server starts up.

8) create table personalization_keys (
key_id integer not null default autoincrement,
key_name varchar(64) not null,
key_description varchar(255) ,
package_name varchar(100) ,
isProtected integer,
default_value LONG VARCHAR null,
data_type varchar(16) ,
created_date datetime NOT NULL DEFAULT current timestamp,
primary key (key_id),
unique (key_name,package_name) )
--This table is used to save the personalization keys information of all the packages in the SUP server.

9) create table "personalization_key_values" (
"value" LONG VARCHAR null,
"user_defined" BIT not null,
"key_name" VARCHAR(64) not null,
"user_name" VARCHAR(128) not null,
"package_name" VARCHAR(100) not null,
"domain_name" VARCHAR(36) not null,
"package_script_version" VARCHAR(128) not null,
"LOGICAL_DEL" BIT not null,
"LMD" TIMESTAMP not null,
primary key ("key_name", "user_name", "package_name", "domain_name"),
constraint "CKC_LOGICAL_DEL_personalization_key_values" check ("LOGICAL_DEL" between 0 and 1))
--This table is used to save the values of personalization keys of all the packages in the SUP server.

10) create table "mms_domain" (
id integer not null default autoincrement,
domain_name varchar(36) not null,
tenant_name varchar(36) not null,
status varchar(20) not null,
active_sync_count int not null,
domain_version int not null,
primary key (id),
unique (domain_name))
--This table is used to save all the domain name information in the SUP server.

11) create table "mms_administrators" (
admin_user varchar(36) not null,
first_name varchar(255) null,
last_name varchar(255) null,
company varchar(255) null,
primary key (admin_user))
--This table is used to save all the MMS administrators information in the SUP server.

12) create table "mms_domain_admin" (
admin_user varchar(36) not null references mms_administrators(admin_user),
domain_id integer not null references mms_domain(id),
primary key (admin_user, domain_id)
)
--This table is used to save all the MMS domain administrator information in the SUP server.

13) create table "mms_domain_package" (
package varchar(100) not null,
domain_id integer not null references mms_domain(id),
script_version varchar(128) not null,
primary key (package, domain_id)
)
--This table is used to save all the package and its related domain information. The column script_version is the package script version of the full package name. e.g. package name: customer.security:2.0,if its domain id is: 1, then the script_version will be: d1_customer_security.2_0.

14) create table "mms_domain_security" (
security_configuration varchar(36) not null,
domain_id integer not null references mms_domain(id),
is_default integer not null default 0,
primary key (security_configuration, domain_id))

15) create table mms_licensed_device (
logic_id integer not null,
device_id varchar(200),
locked BIT not null ,
rbs_status integer not null,
mbs_status integer not null,
licensed BIT not null ,
reg_time timestamp,
last_connect timestamp,
platform varchar(50),
primary key (device_id))
--This table is used to save the device lock information for all the devices.

16) create table mms_application_info (
applicationId varchar( 64 ) not null,
displayName varchar( 80 ) not null,
description varchar( 255 ) null,
primary key( applicationId ))
--This table is used to save all the application id information in the SUP server

17) create table mms_application_package_info (
id integer not null default autoincrement,
packageName varchar( 100 ) not null,
applicationId varchar( 64 ) not null,
domainName varchar( 64 ) not null,
primary key( id ))
--This table is used to save the relation information about packageName, appId and domainName.

18) create table mms_application_domain_info (
id integer not null default autoincrement,
applicationId varchar( 64 ) not null,
domainName varchar( 64 ) not null,
primary key( id ))
--This table is used to save all the appId and domainName relation information.

19) create table mms_application_users_info (
id integer not null default autoincrement,
applicationId varchar( 64 ) not null,
user_name varchar( 128 ) not null,
security_domain varchar( 64 ) not null,
reg_time timestamp not null default current timestamp,
primary key( id )
)
--This table is used to save all the application user information, including the security configuration and registration time.

20) create table mms_application_connection_info (
id integer not null default autoincrement, -- id
applicationId varchar( 64 ) not null, -- appId
appConnId varchar( 200 ) null, -- application connection id (<deviceId>_appId)
logicalId integer null, -- logicalId of the device
pid varchar( 200 ) null, -- physical Id of the device
user_name varchar( 128 ) not null,
security_domain varchar( 64 ) not null,
locked BIT not null default 0,
reg_time timestamp not null default current timestamp,
device_type varchar(10) null, -- device type (windows, android ,etc)
device_model varchar(64) null,
logical_role varchar(32) null,
domain_name varchar(32) null,
valcode_exp_dt timestamp null,
client_version varchar(32) null,
template_id integer null,
template_name varchar(64) null,
support_hwa bit not null default 1,
support_password_policy bit not null default 1,
support_client_callable bit not null default 1,
device_connection_id integer null,
primary key( id ))
--This table is used to save all the application connection information.

21) create table mms_metadata_container (
appId VARCHAR( 64 ) NOT NULL,
name VARCHAR( 100 ) NOT NULL,
version VARCHAR( 50 ) NOT NULL,
zipFile LONG BINARY NOT NULL,
primary key( appId, name, version ))
--This table is used to save the metadata of the target application.

22) create table mms_assigned_widget_info (
logic_id integer not null,
device_id varchar(200),
count integer not null,
primary key (logic_id))
--This table is used to save the assigned widget information for a target device.

23) create table mms_playback_history (
id integer not null default autoincrement,
"package" VARCHAR(128) not null,
"mbo" LONG VARCHAR not null,
"operation" VARCHAR(64) not null,
"timestamp" timestamp default getdate() not null,
"errorMessage" LONG VARCHAR,
"lastGoodPlayback" timestamp,
"badPlaybackCount" integer not null,
primary key (id))
--This table is used to save all the playback history information in the SUP server.

24) create table mms_domain_logging_configuration (
id integer not null default autoincrement,
"domain" VARCHAR( 36 ) not null,
"conf_name" VARCHAR( 36 ) not null,
"description" VARCHAR( 500) not null,
"app_filter" long VARCHAR not null,
"pkg_filter" long VARCHAR not null,
"mbo_filter" long VARCHAR not null,
"op_filter" long VARCHAR not null,
"sec_filter" long VARCHAR not null,
"endpoint_filter" long VARCHAR not null,
"user_filter" long VARCHAR not null,
"appConn_filter" long VARCHAR not null,
"enabled" BIT not null default 0,
"payload_enabled" VARCHAR( 400) not null,
primary key( id ) )
--This table is used to save all the domain log configuration information in the SUP server. It mainly stores all kinds of filter content for the domain log.

25) create table mms_domain_logging_filter (
id integer not null default autoincrement,
"domain" VARCHAR( 36 ) not null,
"name" VARCHAR( 36 ) null,
"description" VARCHAR( 255 ) null,
"content" long VARCHAR null,
"filterXml" long VARCHAR null,
"correlationEnabled" BIT not null default 0,
primary key( id ) )
--The table is used to save all the domain filter information.

26) create table sup_sis_subscription
(subscription_id int not null default autoincrement,
domain varchar(36) not null,
pkg varchar(100) not null,
sync_group varchar(100) not null,
cid varchar(36) not null,
device_id varchar(200) not null,
user_name varchar(128) not null,
appname varchar(100) not null,
enable BIT not null default 0,
admin_lock BIT not null default 0,
last_synched datetime not null default current timestamp,
last_notified datetime not null default current timestamp,
num_syncs integer not null default 0,
interval integer not null default 0,
protocol varchar(100) not null default 'LIGHTWEIGHTPULL',
address varchar(300) null,
LOGICAL_DEL BIT NOT NULL default 0,
LMD datetime NOT NULL default current timestamp,
primary key (domain, pkg, sync_group, cid),
unique( subscription_id ),
constraint CKC_LOGICAL_DEL_sup_sis_subscription check(LOGICAL_DEL between 0 and 1))
--This table is used to save all the SIS subscription information.

27) create table sup_sis_notification
(
notification_id int not null default autoincrement,
gateway varchar(128) null,
subject varchar(128) not null,
content varchar(512) not null,
address varchar(128) not null,
resend_minute varchar(30) not null,
minute_to_live varchar(30) not null,
subscription_id int not null,
delivery_time timestamp not null default current timestamp,
primary key (notification_id),
foreign key (subscription_id) references sup_sis_subscription(subscription_id) match full )
--This table is used to save all the SIS notification.

28) create table sup_sis_mbo_change_detection
(pkg varchar(128) not null,
mbo varchar(64) not null,
pub varchar(64) not null,
chg tinyint not null,
primary key (pkg, mbo) )
--This table is used to save all the SIS mbo change detection information.

29) CREATE TABLE mms_rbs_subscription
(pkg varchar(128) NOT NULL
,remoteId varchar(36) NOT NULL
,deviceId varchar(200) NULL
,app varchar(255) NOT NULL
,userName varchar(128) NOT NULL
,applicationId varchar(64) NULL
,mboMap long varchar NULL
,pqTableMetaData long varchar NULL
,PRIMARY KEY (pkg,remoteId,app)
)
--This table is used to save all the RBS subscription metadata information.

30) CREATE TABLE mms_upgrade_bookkeeping_info
(pkg_script_version varchar(128) NOT NULL
,table_info long varchar NULL
,operation_info long varchar NULL
,PRIMARY KEY (pkg_script_version)
)--This table is used to save all the table mapping and operation replay information for all the packages.

31) CREATE TABLE "sup_ss" (
"pkg" varchar(128) NOT NULL
,"pub" varchar(64) NOT NULL
,"cid" varchar(36) NOT NULL
,"ldt" "datetime" NULL
,"enable" tinyint NULL
,"pid" varchar(200) NULL
,PRIMARY KEY ("pkg","pub","cid") )
--This table is used to save the publication information of the packages.

32) CREATE TABLE DBA.mms_lock (
name VARCHAR(64) NOT NULL,
PRIMARY KEY ("name") )
--This table is used to save the lock name. Used in the cluster lock acquiring.

33) CREATE TABLE "DBA"."mms_mbs_dd" (
"name" varchar(64) NOT NULL, -- store the message key
"datetime" timestamp NOT NULL DEFAULT current timestamp,
"processed" varchar(1) NOT NULL DEFAULT 'n',
PRIMARY KEY ("name" ASC) )
--This table is used to save/flag the messages whether they have been processed.

34) create table AMP_MODULES
(MODULE_ID unsigned bigint not null default autoincrement,
MODULE_NAME char(100) not null,
constraint PK_AMP_MODULES primary key (MODULE_ID));
--This table is used to store the AMP modules.

35) create table AMP_MODULE_VERSIONS
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
CLIENT_ICON_IDX integer null,
MODULE_DESC LONG VARCHAR null,
DISPLAY_NAME LONG VARCHAR not null,
INVOKE_ON_CLIENT BIT not null,
MARK_MESSAGE_PROCESSED BIT null,
DELETE_MESSAGE_AFTER_PROCESSING BIT null,
SAVE_RESPONSE_IN_SENT_ITEMS BIT null,
SUPPORT_CREDENTIALS_CACHE BIT null,
CREDENTIALS_KEY LONG VARCHAR null,
ACTIVATION_REQUIRED BIT null,
PROCESS_UPDATES BIT null,
ACTIVATION_KEY LONG VARCHAR null,
IS_LOCKED BIT null,
INSTALLED_PACKAGE long binary null,
SUPPORT_SHARED_STORAGE BIT null,
SHARED_STORAGE_KEY LONG VARCHAR null,
CUSTOM_ICONS long binary null,
DESIGNER_VERSION LONG VARCHAR null,
constraint PK_AMP_MODULE_VERSIONS primary key (MODULE_ID, VERSION),
constraint FK_AMP_MODU_REFERENCE_AMP_MODU foreign key (MODULE_ID)
references AMP_MODULES (MODULE_ID)
on update restrict
on delete cascade
);
--This table is used to save the version information of the AMP modules.

36) create table AMP_CONTEXT_VARIABLES
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
VARIABLE_NAME char(256) not null,
VARIABLE_VALUE LONG BINARY null,
IS_PASSWORD BIT null,
IS_CERTIFICATE BIT null,
constraint PK_AMP_CONTEXT_VARIABLES primary key (MODULE_ID, VERSION, VARIABLE_NAME),
constraint FK_AMP_CONT_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade);
--This table is used to save the AMP context variables information.

37) create table DEVICES
(DEVICE_ID unsigned bigint not null default autoincrement,
DEVICE_NAME char(128) null,
DEVICE_TYPE char(10) null,
LAST_SETTINGS_UPD_DT TIMESTAMP null,
LOCK_DT TIMESTAMP null,
LOCK_USER char(36) null,
SOFT_DELETE_DT TIMESTAMP null,
DEVICE_STATUS integer null,
CLUSTER_NODE_ID integer null,
constraint PK_DEVICES primary key (DEVICE_ID));
--This table is used to save all the devices information.

38) create table AMP_DEVICE_MODULE_ASSIGNMENT
(MODULE_ID unsigned bigint not null,
DEVICE_ID unsigned bigint not null,
VERSION integer not null,
SENT_TO_CLIENT BIT not null,
SOFT_DELETE_DT TIMESTAMP null,
INSTALLED_ON_CLIENT BIT not null,
DELETE_SENT_TO_CLIENT BIT not null,
DELETED_ON_CLIENT BIT not null,
IS_DEFAULT bit null,
constraint PK_AMP_DEVICE_MODULE_ASSIGNMEN primary key (MODULE_ID, DEVICE_ID, VERSION),
constraint FK_AMP_DEVI_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_AMP_DEVI_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade);
--This table is used to save all the AMP module and device relation information.

39) create table AMP_ERRORS
(ERROR_ID unsigned bigint not null default autoincrement,
MODULE_ID unsigned bigint null,
ERROR_DESC LONG BINARY not null,
ERROR_CODE integer null,
ERROR_DT TIMESTAMP not null,
RESPONSE_DATA LONG BINARY null,
SERVER_REC_UID char(64) null,
EMAIL_SUBJECT LONG BINARY null,
EMAIL_FROM LONG BINARY null,
EMAIL_RECEIVED_DT TIMESTAMP null,
VERSION integer null,
DEVICE_ID unsigned bigint null,
constraint PK_AMP_ERRORS primary key (ERROR_ID),
constraint FK_AMP_ERRO_REFERENCE_AMP_MODU foreign key (MODULE_ID)
references AMP_MODULES (MODULE_ID)
on update restrict
on delete cascade);
--This table is used to store all the AMP errors information.

40) create table AMP_MATCH_RULES
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
SUBJECT_REG_EXP LONG BINARY null,
TO_REG_EXP LONG BINARY null,
FROM_REG_EXP LONG BINARY null,
CC_REG_EXP LONG BINARY null,
BODY_REG_EXP LONG BINARY null,
CUSTOM_FIELD_NUM integer null,
CUSTOM_FIELD_REG_EXP LONG BINARY null,
SUBJECT_ADMIN_HINT LONG BINARY null,
TO_ADMIN_HINT LONG BINARY null,
FROM_ADMIN_HINT LONG BINARY null,
CC_ADMIN_HINT LONG BINARY null,
BODY_ADMIN_HINT LONG BINARY null,
CUSTOM_FIELD_ADMIN_HINT LONG BINARY null,
constraint PK_AMP_MATCH_RULES primary key (MODULE_ID, VERSION),
constraint FK_AMP_MATC_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade);
--This table is used to store all the AMP module match rules information.

41) create table AMP_MODULE_DEPLOYMENT
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
DEVICE_TYPE char(10) not null,
APPLICATION_TYPE integer not null,
BINARY_FILE_NAME char(256) null,
XML_FILE_NAME char(256) null,
ASSEMBLY_CLASS_NAME char(256) null,
CREDENTIALS_FORM char(256) null,
ALREADY_PROCESSED_FORM char(256) null,
ACTIVATION_FORM char(256) null,
HTML_FILE_SET LONG BINARY null,
XML_FILE_NAME_RELATIVE_PATH char(256) null,
BINARY_FILE_NAME_RELATIVE_PATH char(256) null,
constraint PK_AMP_MODULE_DEPLOYMENT primary key (MODULE_ID, VERSION, DEVICE_TYPE),
constraint FK_AMP_MODU_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade);
--This table is used to store the AMP module deployment information.

42) create table AMP_PROCESSED_IDS
(SERVER_REC_UID char(64) not null,
DEVICE_ID unsigned bigint not null,
MODULE_ID unsigned bigint null,
VERSION integer null,
PROCESSING_MODE integer null,
constraint PK_AMP_PROCESSED_IDS primary key (SERVER_REC_UID, DEVICE_ID),
constraint FK_AMP_PROC_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_AMP_PROC_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade);
--This table is used to save all the AMP processed ids information.

43) create table AMP_RESPONSE_QUEUE
(RESPONSE_QUEUE_ID unsigned bigint not null default autoincrement,
MODULE_ID unsigned bigint not null,
DEVICE_ID unsigned bigint not null,
VERSION integer not null,
RESPONSE_DATA LONG BINARY not null,
CREATE_DT TIMESTAMP not null,
RETRY_DT TIMESTAMP null,
SERVER_REC_UID char(64) null,
BACK_END_USER_NAME LONG BINARY null,
BACK_END_PASSWORD LONG BINARY null,
IS_AWAITING_CREDENTIALS BIT null,
QUID char(32) null,
HTTP_HEADERS long varchar null,
WAIT_STATE integer null,
constraint PK_AMP_RESPONSE_QUEUE primary key (RESPONSE_QUEUE_ID));
--This table is used to save all the AMP response queue information.

44) create table AMP_SERVER_PLUGINS
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
TRANSFORM_OBJECT_NAME char(100) not null,
TRANSFORM_ASSEMBLY_DLL char(256) null,
RESPONSE_OBJECT_NAME char(100) not null,
RESPONSE_ASSEMBLY_DLL char(256) null,
PERSIST_APP_DOMAIN integer null,
TRANSFORM_DLL_RELATIVE_PATH char(256) null,
RESPONSE_DLL_RELATIVE_PATH char(256) null,
TRANSFORM_DLL_RELATIVE_ROOT char null,
RESPONSE_DLL_RELATIVE_ROOT char null,
constraint PK_AMP_SERVER_PLUGINS primary key (MODULE_ID, VERSION),
constraint FK_AMP_SERV_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade);
--This table is used to save all the AMP server plugins information.

45) create table AMP_TRANSFORM_QUEUE
(TRANSFORM_QUEUE_ID unsigned bigint not null default autoincrement,
MODULE_ID unsigned bigint not null,
DEVICE_ID unsigned bigint not null,
VERSION integer not null,
RECORD_DATA LONG BINARY not null,
CREATE_DT TIMESTAMP not null,
XML_FIELDS LONG BINARY not null,
TRANSFORM_TEXT LONG BINARY null,
IS_NEW_EMAIL BIT not null,
DS_TYPE integer not null,
SERVER_REC_VERSION integer not null,
IS_DELETE BIT not null,
SERVER_REC_UID char(64) not null,
RETRY_DT TIMESTAMP null,
PROCESSING_MODE integer null,
BACK_END_USER_NAME LONG BINARY null,
BACK_END_PASSWORD LONG BINARY null,
IS_AWAITING_CREDENTIALS BIT null,
QUID char(32) null,
WAIT_STATE integer null,
constraint PK_AMP_TRANSFORM_QUEUE primary key (TRANSFORM_QUEUE_ID));
--This table is used to store all the transform queue information.

46) create table USERS
(USER_ID unsigned bigint not null default autoincrement,
USER_NAME char(255) not null,
NOTES_USER_NAME LONG BINARY null,
USER_NAME_DISPLAY char(255) not null,
NOTES_USER_NAME_DISPLAY LONG BINARY null,
constraint PK_USERS primary key (USER_ID));
--This table is used to store all the iMO user information.

47) create table USER_DEVICE
(USER_DEVICE_ID unsigned bigint not null default autoincrement,
USER_ID unsigned bigint not null,
DEVICE_ID unsigned bigint not null,
VALCODE char(32) not null,
VALCODE_EXP_DT TIMESTAMP null,
IS_SERVER BIT null,
ADMIN_USER_TYPE integer null,
constraint PK_USER_DEVICE primary key (USER_DEVICE_ID),
constraint FK_USER_DEV_REFERENCE_USERS foreign key (USER_ID)
references USERS (USER_ID)
on update restrict
on delete cascade,
constraint FK_USER_DEV_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--This table is used to store all the user and device information in iMO.

48) create table DEVICE_CONNECTIONS
(DEVICE_CONNECTION_ID unsigned bigint not null default autoincrement,
USER_DEVICE_ID unsigned bigint not null,
CONNECTION_NAME char(32) null,
IS_CONNECTED BIT not null,
COMPUTER_NAME char(15) not null,
LAST_DELIVERY_DT TIMESTAMP null,
QUEUE_COUNT integer not null default 0,
LAST_QUEUE_ADD_DT TIMESTAMP null,
MOCA_SIGNATURE char(32) null,
constraint PK_DEVICE_CONNECTIONS primary key (DEVICE_CONNECTION_ID),
constraint FK_DEVICE_C_REFERENCE_USER_DEV foreign key (USER_DEVICE_ID)
references USER_DEVICE (USER_DEVICE_ID)
on update restrict
on delete cascade
);
--This table is used to save all the device connection, user device relation information.

49) create table AWAITING_CLIENTS
(
AWAITING_CLIENT_ID unsigned bigint not null default autoincrement,
DEVICE_CONNECTION_ID unsigned bigint not null,
SOURCE_URL LONG VARCHAR not null,
DESTINATION_URL LONG VARCHAR not null,
CODE_PAGE integer not null,
MO_PROTOCOL_VERSION integer not null,
constraint PK_AWAITING_CLIENTS primary key (AWAITING_CLIENT_ID),
constraint FK_AWAITING_REFERENCE_DEVICE_C foreign key (DEVICE_CONNECTION_ID)
references DEVICE_CONNECTIONS (DEVICE_CONNECTION_ID)
on update restrict
on delete cascade);
--This table is used to save all the awaiting client information.

50) create table CFG_TEMPLATES
(TEMPLATE_ID unsigned bigint not null default autoincrement,
TEMPLATE_NAME char(50) not null,
TEMPLATE_DESC char(100) null,
constraint PK_CFG_TEMPLATES primary key (TEMPLATE_ID));
--This table is used to save all the configuration templates information. It’s a template id and template name list.

51) create table CFG_IDS
(CONFIG_ID unsigned bigint not null default autoincrement,
TEMPLATE_ID unsigned bigint null,
DEVICE_ID unsigned bigint null,
PARENT_TEMPLATE_ID unsigned bigint null,
LAST_UPD_DT TIMESTAMP not null,
constraint PK_CFG_IDS primary key (CONFIG_ID),
constraint FK_CFG_IDS_REFERENCE_CFG_TEMP foreign key (TEMPLATE_ID)
references CFG_TEMPLATES (TEMPLATE_ID)
on update restrict
on delete cascade,
constraint FK_CFG_IDS_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_CFG_IDS_REFERENCE_CFG_PARENT foreign key (PARENT_TEMPLATE_ID)
references CFG_TEMPLATES (TEMPLATE_ID)
on update restrict
on delete set null);
--This table is used to save all the config ids information for all the device id.

52) create table CFG_PROP_VALIDATION
(VALIDATION_ID integer not null,
MAX_LENGTH integer null,
MAX_VALUE integer null,
MIN_VALUE integer null,
ENUM_VALUES LONG VARCHAR null,
MIN_LENGTH integer null,
constraint PK_CFG_PROP_VALIDATION primary key (VALIDATION_ID));
--This table is used to store the validation rule for the configuration props.

53) create table CFG_PROP_DEFS
(PROP_ID integer not null,
VALIDATION_ID integer null,
PROP_CATEGORY char(50) not null,
PROP_NAME char(50) not null,
PROP_TYPE integer not null,
PROP_DESC LONG VARCHAR null,
DEFAULT_VALUE LONG BINARY null,
DEVICE_PLATFORM integer not null,
READ_ONLY BIT not null,
SYNC_TO_DEVICE BIT not null,
HIDDEN BIT not null,
MULTI_LINE BIT not null,
GROUPWARE_PLATFORM integer not null,
"ENCRYPTED" BIT not null,
DEFAULT_DISPLAY_MODE integer not null,
constraint PK_CFG_PROP_DEFS primary key (PROP_ID),
constraint FK_CFG_PROP_REFERENCE_CFG_PROP foreign key (VALIDATION_ID)
references CFG_PROP_VALIDATION (VALIDATION_ID)
on update restrict
on delete cascade);
--This table is used to store all the configuration props.

56) create table CFG_PROP_VALUES
(PROP_ID integer not null,
CONFIG_ID unsigned bigint not null,
PROP_VALUE LONG BINARY null,
LAST_UPD_DT TIMESTAMP not null,
DISPLAY_MODE integer not null,
constraint PK_CFG_PROP_VALUES primary key (PROP_ID, CONFIG_ID),
constraint FK_CFG_PROP_REFERENCE_CFG_IDS foreign key (CONFIG_ID)
references CFG_IDS (CONFIG_ID)
on update restrict
on delete cascade,
constraint FK_CFG_PROP_REFERENCE_CFG_PROP foreign key (PROP_ID)
references CFG_PROP_DEFS (PROP_ID)
on update restrict
on delete cascade);
--This table is used to save all the configuration property values.

57) create table CFG_SUBFOLDER_PROP_VALUES
(PROP_ID integer not null,
CONFIG_ID unsigned bigint not null,
SUBFOLDER_NAME binary(512) not null,
PROP_VALUE LONG BINARY null,
LAST_UPD_DT TIMESTAMP not null,
DISPLAY_MODE integer not null,
constraint PK_CFG_SUBFOLDER_PROP_VALUES primary key (PROP_ID, CONFIG_ID, SUBFOLDER_NAME),
constraint FK_CFG_SUBF_REFERENCE_CFG_PROP foreign key (PROP_ID)
references CFG_PROP_DEFS (PROP_ID)
on update restrict
on delete cascade,
constraint FK_CFG_SUBF_REFERENCE_CFG_IDS foreign key (CONFIG_ID)
references CFG_IDS (CONFIG_ID)
on update restrict
on delete cascade);
--This table is used to save all the subfolder properties.

58) create table CFG_SYS_PROP_VALUES
(SYS_PROP_ID integer not null,
PROP_TYPE integer not null,
IS_ENCRYPTED BIT not null,
PROP_NAME char(50) null,
PROP_VALUE LONG BINARY null,
constraint PK_CFG_SYS_PROP_VALUES primary key (SYS_PROP_ID));
--This table is used to save all the sys property values.

59) create table CFG_TRACE
(TRACE_MODULE_NAME CHAR(128) not null,
TRACE_PROP_NAME CHAR(128) not null,
VALUE CHAR(500) null,
constraint PK_CFG_TRACE primary key clustered (TRACE_MODULE_NAME, TRACE_PROP_NAME));
--This table is used to save all the TRACE level property for all the trace modules.

60) create table CFG_TRACE_VERSION
(CUR_VERSION unsigned bigint not null,
constraint PK_CFG_TRACE_VERSION primary key clustered (CUR_VERSION));
--This table is used to store the config trace version.

61) create table EXCH_MAILBOX_INFO
(EXCH_MAILBOX_INFO_ID unsigned bigint not null default autoincrement,
DEVICE_ID unsigned bigint not null,
MAILBOX_LM_DT TIMESTAMP null,
MAILBOX_DN char(255) null,
USER_ALIAS char(64) not null,
EXCH_SERVER char(64) null,
EXCH_SERVER_DN char(255) null,
LAST_UPD_DT TIMESTAMP not null,
constraint PK_EXCH_MAILBOX_INFO primary key (EXCH_MAILBOX_INFO_ID),
constraint FK_EXCH_MAI_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
-- deprecated table

62) create table EXCH_FOLDER_INFO
(EXCH_FOLDER_INFO_ID unsigned bigint not null default autoincrement,
EXCH_MAILBOX_INFO_ID unsigned bigint not null,
FOLDER_NAME LONG BINARY null,
DS_TYPE integer not null,
"CHECKPOINT" LONG BINARY null,
constraint PK_EXCH_FOLDER_INFO primary key (EXCH_FOLDER_INFO_ID),
constraint FK_EXCH_FOL_REFERENCE_EXCH_MAI foreign key (EXCH_MAILBOX_INFO_ID)
references EXCH_MAILBOX_INFO (EXCH_MAILBOX_INFO_ID)
on update restrict
on delete cascade);
--deprecated table 

63) create table EXCH_FOLDER_INFO
(EXCH_FOLDER_INFO_ID unsigned bigint not null default autoincrement,
EXCH_MAILBOX_INFO_ID unsigned bigint not null,
FOLDER_NAME LONG BINARY null,
DS_TYPE integer not null,
"CHECKPOINT" LONG BINARY null,
constraint PK_EXCH_FOLDER_INFO primary key (EXCH_FOLDER_INFO_ID),
constraint FK_EXCH_FOL_REFERENCE_EXCH_MAI foreign key (EXCH_MAILBOX_INFO_ID)
references EXCH_MAILBOX_INFO (EXCH_MAILBOX_INFO_ID)
on update restrict
on delete cascade);
--deprecated table  

64) create table EXCH_IMO_RECORD_WRITES
(DEVICE_ID unsigned bigint not null,
RECORD_LUID char(56) not null,
MOD_INFO char(32) null,
constraint PK_EXCH_IMO_RECORD_WRITES primary key (DEVICE_ID, RECORD_LUID),
constraint FK_EXCH_IMO_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--deprecated table  

65) create table EXCH_RECORD_QUEUE
(EXCH_RECORD_QUEUE_ID unsigned bigint not null default autoincrement,
EXCH_FOLDER_INFO_ID unsigned bigint not null,
RECORD_LUID char(56) not null,
MOD_INFO char(32) null,
STATUS_FLAGS integer not null,
LAST_UPD_DT TIMESTAMP not null,
constraint PK_EXCH_RECORD_QUEUE primary key (EXCH_RECORD_QUEUE_ID),
constraint FK_EXCH_REC_REFERENCE_EXCH_FOL foreign key (EXCH_FOLDER_INFO_ID)
references EXCH_FOLDER_INFO (EXCH_FOLDER_INFO_ID)
on update restrict
on delete cascade);
--deprecated table  

66) create table FT_CAB_VERSIONS
(DEVICE_ID unsigned bigint not null,
CAB_TYPE integer not null,
LAST_VERSION_SENT LONG VARCHAR null,
ATTEMPTS integer not null,
LAST_UPD_DT TIMESTAMP not null,
constraint PK_FT_CAB_VERSIONS primary key (DEVICE_ID, CAB_TYPE),
constraint FK_FT_CAB_V_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--deprecated table  

67) create table IMAP_IDS
(DEVICE_ID unsigned bigint not null,
UID char(16) not null,
constraint PK_IMAP_IDS primary key (DEVICE_ID, UID),
constraint FK_IMAP_IDS_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--deprecated table  

68) create table IMP_CONFIG
(IMP_CONFIG_ID unsigned bigint not null default autoincrement,
IS_ENABLED BIT not null,
PROTOCOL integer not null,
SERVER_NAME char(256) not null,
PORT integer not null,
USE_SSL BIT not null,
MASTER_USER_NAME LONG BINARY not null,
MASTER_PASSWORD LONG BINARY not null,
TRUNCATION_LIMIT integer not null,
POLL_SECONDS integer not null,
constraint PK_IMP_CONFIG primary key (IMP_CONFIG_ID));
--deprecated table  

69) create table IMP_CONFIG_RULE
(IMP_CONFIG_ID unsigned bigint not null,
IMP_RULE_ID integer not null,
EMAIL_ATTRIBUTE integer not null,
OPERATION integer not null,
DEVICE_ATTRIBUTE integer null,
REG_EXP LONG BINARY null,
IS_CASE_SENSITIVE BIT not null,
constraint PK_IMP_CONFIG_RULE primary key (IMP_CONFIG_ID, IMP_RULE_ID),
constraint FK_IMP_CONF_REFERENCE_IMP_CONF foreign key (IMP_CONFIG_ID)
references IMP_CONFIG (IMP_CONFIG_ID)
on update restrict
on delete cascade);
--deprecated table  

70) create table IMP_PROCESSED_EMAILS
(UID char(100) not null,
constraint PK_IMP_PROCESSED_EMAILS primary key (UID));
--deprecated table  

71) create table INJECTION_OUTBOX
(OUTBOX_ID unsigned bigint not null default autoincrement,
DEVICE_ID unsigned bigint not null,
MESSAGE_ID char(32) not null,
OPERATION integer not null,
MESSAGE_VERSION integer null,
MAIL_CONTENT long binary null,
MODULE_ID unsigned bigint null,
MODULE_VERSION integer null,
TRANSFORM_DATA long binary null,
constraint PK_INJECTION_OUTBOX primary key clustered (OUTBOX_ID),
constraint FK_INJECTIO_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_INJECTIO_REFERENCE_AMP_MODU foreign key (MODULE_ID, MODULE_VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete restrict);

72) create table IPHONE_PUSH_COUNT
(DEVICE_ID unsigned bigint not null,
APP_NAME char(128) not null,
LAST_PUSH_SENT_DT TIMESTAMP null,
COUNTER integer not null,
LAST_COUNTER integer null,
constraint PK_IPHONE_PUSH_COUNT primary key (DEVICE_ID, APP_NAME),
constraint FK_IPHONE_P_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--To allows external process other than listener to inject an email into the messaging server system that will be delivered directly to the designated device, external process can insert mail into this table. 

73) create table LB_NODES
(NODE_ID unsigned bigint not null default autoincrement,
COMPUTER_NAME char(15) not null,
COMPUTER_ADDRESS char(256) not null,
PORT integer not null,
STATUS integer not null,
LAST_STATUS_UPD_DT TIMESTAMP not null,
LAST_STATUS_UPD_BY char(15) not null,
IS_CONTROLLER BIT not null,
constraint PK_LB_NODES primary key (NODE_ID));
--Contains information about Load balancer nodes, such as computer name, current status, if it is the controller, etc. 

74) create table LOCK_USER_DEVICE
(USER_DEVICE_ID unsigned bigint not null,
OWNER char(50) not null,
constraint PK_LOCK_USER_DEVICE primary key (USER_DEVICE_ID),
constraint FK_LOCK_USE_REFERENCE_USER_DEV foreign key (USER_DEVICE_ID)
references USER_DEVICE (USER_DEVICE_ID)
on update restrict
on delete cascade);
--???? 

75) create table MOMS_ROLE
(ROLE_ID integer not null,
ROLE_NAME char(100) not null,
constraint PK_MOMS_ROLE primary key (ROLE_ID));
-- The role definations.

76) create table MOMS_DEVICE_ROLE
(DEVICE_ID unsigned bigint not null,
ROLE_ID integer not null,
constraint PK_MOMS_DEVICE_ROLE primary key (DEVICE_ID, ROLE_ID),
constraint FK_MOMS_DEV_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_MOMS_DEV_REFERENCE_MOMS_ROL foreign key (ROLE_ID)
references MOMS_ROLE (ROLE_ID)
on update restrict
on delete cascade);
-- stores the security roles of the devices.

77) create table MOMS_FUNCTIONAL_AREA
(FUNCTIONAL_AREA_ID integer not null,
FUNCTIONAL_AREA_NAME char(100) not null,
constraint PK_MOMS_FUNCTIONAL_AREA primary key (FUNCTIONAL_AREA_ID));
-- The functional area definitions of the roles.

78) create table MOMS_FUNCTIONAL_AREA_METHOD
(FUNCTIONAL_AREA_ID integer not null,
METHOD_NAME char(50) not null,
OBJECT_NAME char(150) not null,
constraint PK_MOMS_FUNCTIONAL_AREA_METHOD primary key (METHOD_NAME, OBJECT_NAME, FUNCTIONAL_AREA_ID),
constraint FK_MOMS_FUN_REFERENCE_MOMS_FUN foreign key (FUNCTIONAL_AREA_ID)
references MOMS_FUNCTIONAL_AREA (FUNCTIONAL_AREA_ID)
on update restrict
on delete cascade);
-- Mapping of the functional area and module methods. 

79) create table MOMS_ROLE_FUNCTIONAL_AREA
(ROLE_ID integer not null,
FUNCTIONAL_AREA_ID integer not null,
constraint PK_MOMS_ROLE_FUNCTIONAL_AREA primary key (ROLE_ID, FUNCTIONAL_AREA_ID),
constraint FK_MOMS_ROL_REFERENCE_MOMS_ROL foreign key (ROLE_ID)
references MOMS_ROLE (ROLE_ID)
on update restrict
on delete cascade,
constraint FK_MOMS_ROL_REFERENCE_MOMS_FUN foreign key (FUNCTIONAL_AREA_ID)
references MOMS_FUNCTIONAL_AREA (FUNCTIONAL_AREA_ID)
on update restrict
on delete cascade);
-- The mapping of MOMS_ROLE and MOMS_FUNCATION_AREA 

80) create table QUEUED_MESSAGES
(QUEUED_MESSAGE_ID unsigned bigint not null default autoincrement,
DEVICE_CONNECTION_ID unsigned bigint not null,
OBJECT_NAME char(150) not null,
METHOD_NAME char(50) not null,
PRIORITY integer not null,
TIMEOUT TIMESTAMP not null,
PARAMS LONG BINARY null,
PARAMCOUNT integer not null,
CALLBACK_OBJECT_NAME char(150) null,
CALLBACK_METHOD_NAME char(50) null,
STATE integer not null,
DELIVERED_COUNT integer not null,
SERIALIZATION_HINT char(15) null,
PARAMS_CRC_LOW integer null,
PARAMS_CRC_HIGH integer null,
ON_DELIVERY_FAILURE integer null,
CID integer null,
DO_NOT_NOTIFY bit not null default 0,
constraint PK_QUEUED_MESSAGES primary key (QUEUED_MESSAGE_ID),
constraint FK_QUEUED_M_REFERENCE_DEVICE_C foreign key (DEVICE_CONNECTION_ID)
references DEVICE_CONNECTIONS (DEVICE_CONNECTION_ID)
on update restrict
on delete cascade);
--The  Queued_messages table, is used to keep the pending messages to be processed by client.
When client is online, server will send the messages in this table to the client one by one through the S2D connection channel.

81) create table MO_BINARY_STREAM
(MO_BINARY_STREAM_ID unsigned bigint not null default autoincrement,
DEVICE_ID unsigned bigint not null,
QUEUED_MESSAGE_ID unsigned bigint null,
CREATE_DT timestamp not null,
EXPIRATION_DT timestamp null,
constraint PK_MO_BINARY_STREAM primary key clustered (MO_BINARY_STREAM_ID),
constraint FK_MO_BINAR_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_MO_BINAR_REFERENCE_QUEUED_M foreign key (QUEUED_MESSAGE_ID)
references QUEUED_MESSAGES (QUEUED_MESSAGE_ID)
on update restrict
on delete set null);
-- ????

82) create table MO_BINARY_STREAM_DATA
(MO_BINARY_STREAM_ID unsigned bigint not null,
START_IDX int not null,
STREAM_DATA long binary not null,
constraint PK_MO_BINARY_STREAM_DATA primary key clustered (MO_BINARY_STREAM_ID, START_IDX),
constraint FK_MO_BINAR_REFERENCE_MO_BINAR foreign key (MO_BINARY_STREAM_ID)
references MO_BINARY_STREAM (MO_BINARY_STREAM_ID)
on update restrict
on delete cascade);
--???? 

83) create table MO_D2S_STREAM_DATA
(DEVICE_ID unsigned bigint not null,
GUID char(32) not null,
SEQ_NUM integer not null,
STREAM_DATA long binary not null,
constraint PK_MO_D2S_STREAM_DATA primary key clustered (DEVICE_ID, GUID, SEQ_NUM),
constraint FK_MO_D2S_S_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete restrict);
-- Save the D2S data fragment which was already processed. 

84) create table NL_USER_DS
(DS_TYPE integer not null,
DEVICE_ID integer not null,
LAST_DB_MOD BINARY(8) null,
LAST_POLL_DT TIMESTAMP null,
STATUS integer null,
STATE_INFO LONG BINARY null,
STATE_INFO_LEN integer null,
USER_ID integer not null,
constraint PK_NL_USER_DS primary key (DS_TYPE, DEVICE_ID));
-- ????? 

85) create table NOTES_LB_SYNC
(SERVER char(64) not null,
STATUS integer not null,
LAST_POLLED_DT TIMESTAMP not null,
constraint PK_NOTES_LB_SYNC primary key (SERVER));
--???? 

86) create table NOTIFICATION_BATCHING
(DEVICE_ID unsigned bigint not null,
DEVICE_CONNECTION_ID unsigned bigint not null,
NEXT_NOTIFICATION_DUE_DT TIMESTAMP not null,
LAST_NOTIFICATION_QUEUED_DT TIMESTAMP not null,
LAST_QUEUED_MESSAGE_ID integer not null,
DEVICE_NAME char(128) null,
NOTIFICATION_PENDING bit null default 0,
constraint PK_NOTIFICATION_BATCHING primary key (DEVICE_ID),
constraint FK_NOTIFICA_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_NOTIFICA_REFERENCE_DEVICE_C foreign key (DEVICE_CONNECTION_ID)
references DEVICE_CONNECTIONS (DEVICE_CONNECTION_ID)
on update restrict
on delete cascade);
-- ????

87) create table NOTIFICATION_QUEUE
(NOTIFICATION_QUEUE_ID unsigned bigint not null default autoincrement,
EVENT_TYPE integer not null,
EVENT_DATA LONG BINARY not null,
CREATE_DT TIMESTAMP not null,
DEVICE_ID unsigned bigint null,
RETRY_DT TIMESTAMP null,
constraint PK_NOTIFICATION_QUEUE primary key (NOTIFICATION_QUEUE_ID),
constraint FK_NOTIFICA_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--???? 

88) create table ONLINE_HISTORY (
USER_DEVICE_ID unsigned bigint not null,
EVENT_DT TIMESTAMP not null,
EVENT_TYPE integer not null,
constraint PK_ONLINE_HISTORY primary key (USER_DEVICE_ID),
constraint FK_ONLINE_H_REFERENCE_USER_DEV foreign key (USER_DEVICE_ID)
references USER_DEVICE (USER_DEVICE_ID)
on update restrict
on delete cascade);
-- Deprecated table

89) create table POP_IDS
(DEVICE_ID unsigned bigint not null,
UID char(70) not null,
HASHED_UID BINARY(32) not null,
MAIL_INFO BINARY(4) not null,
constraint PK_POP_IDS primary key (DEVICE_ID, UID),
constraint FK_POP_IDS_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
--???? 

90) create table PUBLISHED_CLIENTS
(COMPONENT_NAME char(100) not null,
DEVICE_NAME char(128) not null,
CONNECTION_NAME char(32) not null,
USER_NAME char(255) not null,
NODE_ID integer not null,
constraint PK_PUBLISHED_CLIENTS primary key (COMPONENT_NAME, NODE_ID));
-- Stores the published information about the MOCA clients so server  objects can address it.  

91) create table STORED_RESPONSES
(DEVICE_CONNECTION_ID unsigned bigint not null,
REQUEST_ID integer not null,
PARAMS LONG BINARY not null,
PARAMCOUNT integer not null,
RETRY_COUNTER integer null,
constraint PK_STORED_RESPONSES primary key (DEVICE_CONNECTION_ID, REQUEST_ID),
constraint FK_STORED_R_REFERENCE_DEVICE_C foreign key (DEVICE_CONNECTION_ID)
references DEVICE_CONNECTIONS (DEVICE_CONNECTION_ID)
on update restrict
on delete cascade);
--???? 

92) create table SYNCHRONOUS_RESPONSES
(REQUEST_GUID char(32) not null,
PARAMS LONG BINARY null,
PARAMCOUNT integer not null,
RESPONSE_DT TIMESTAMP not null,
ERROR_CODE integer null,
ERROR_MSG long varchar null,
constraint PK_SYNCHRONOUS_RESPONSES primary key (REQUEST_GUID));
-- ???? 

93) create table SYNC_ID_MAP
(SERVER_REC_UID char(64) not null,
DEVICE_ID unsigned bigint not null,
DS_TYPE integer not null,
DEVICE_REC_UID char(40) not null,
SERVER_VER integer not null,
DEVICE_VER integer not null,
IS_INJECTED BIT null,
constraint PK_SYNC_ID_MAP primary key (SERVER_REC_UID, DEVICE_ID, DS_TYPE),
constraint FK_SYNC_ID__REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
-- ???? 

94) create table SYNC_REPEAT_INFO
(SYNC_REPEAT_INFO_ID unsigned bigint not null default autoincrement,
DEVICE_ID unsigned bigint not null,
DATA LONG BINARY not null,
REPEAT_GUID char(32) not null,
constraint PK_SYNC_REPEAT_INFO primary key (SYNC_REPEAT_INFO_ID),
constraint FK_SYNC_REP_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
-- ????

95) create table SYNC_REPEAT_INSTANCE
(SYNC_REPEAT_INSTANCE_ID unsigned bigint not null default autoincrement,
SYNC_REPEAT_INFO_ID unsigned bigint not null,
ORIGINAL_DT TIMESTAMP not null,
INSTANCE_GUID char(32) not null,
constraint PK_SYNC_REPEAT_INSTANCE primary key (SYNC_REPEAT_INSTANCE_ID),
constraint FK_SYNC_REP_REFERENCE_SYNC_REP foreign key (SYNC_REPEAT_INFO_ID)
references SYNC_REPEAT_INFO (SYNC_REPEAT_INFO_ID)
on update restrict
on delete cascade);
-- Stores the sync repeat instance 

96) create table SYNC_REQUESTS
(SYNC_REQUEST_ID unsigned bigint not null default autoincrement,
SERVER_VER integer not null,
DEVICE_VER integer not null,
REQUEST_TYPE char(1) not null,
IS_PAUSED BIT not null,
SERVER_REC_UID char(64) null,
DEVICE_REC_UID char(40) null,
QUEUED_MESSAGE_ID unsigned bigint not null,
DEVICE_ID unsigned bigint not null,
DS_TYPE integer not null,
IS_INJECTED BIT null,
constraint PK_SYNC_REQUESTS primary key (SYNC_REQUEST_ID),
constraint FK_SYNC_REQ_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade,
constraint FK_SYNC_REQ_REFERENCE_QUEUED_M foreign key (QUEUED_MESSAGE_ID)
references QUEUED_MESSAGES (QUEUED_MESSAGE_ID)
on update restrict
on delete cascade);
 – Store the requests to be processed by sync engine.

97) create table TEST_DBLAYER
(VAL_INT integer null,
VAL_CHAR char(50) null,
VAL_CICHAR char(50) null,
VAL_DATE date null,
VAL_TIMESTAMP timestamp null,
VAL_TIME time null,
VAL_SHORTINT smallint null,
VAL_DOUBLE double null,
VAL_AUTOINC unsigned bigint null default autoincrement,
VAL_MEMO long varchar null,
VAL_MONEY money null,
VAL_LOGICAL bit null,
VAL_CURDOUBLE double null,
VAL_BINARY long binary null,
VAL_IMAGE image null,
VAL_RAW binary(50) null,
VAL_ID integer null);
--???? 

98) create table TM_RESPONSE_CONTINUATION
(DEVICE_ID unsigned bigint not null,
TOKEN char(32) not null,
RESPONSE_DATA LONG BINARY not null,
CREATE_DT TIMESTAMP not null,
constraint PK_TM_RESPONSE_CONTINUATION primary key (TOKEN, DEVICE_ID),
constraint FK_TM_RESPO_REFERENCE_DEVICES foreign key (DEVICE_ID)
references DEVICES (DEVICE_ID)
on update restrict
on delete cascade);
-- Store data to support BlackBerry-specific  response portioning in TM 

99) create table USER_DEVICE_STATUS
(USER_DEVICE_ID unsigned bigint not null,
LAST_CONNECT_DT TIMESTAMP null,
PENDING_NOTIFICATIONS integer not null,
IS_ONLINE BIT not null,
constraint PK_USER_DEVICE_STATUS primary key (USER_DEVICE_ID),
constraint FK_USER_DEV_REFERENCE_USER_DEV foreign key (USER_DEVICE_ID)
references USER_DEVICE (USER_DEVICE_ID)
on update restrict
on delete cascade);
--???? 

100) create table AMP_TEMPLATE_MODULE
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
TEMPLATE_ID unsigned bigint not null,
IS_DEFAULT bit not null,
NOTIFY_COMPLETE bit not null,
constraint FK_AMP_TEMP_REFERENCE_AMP_MODU foreign key (MODULE_ID, VERSION)
references AMP_MODULE_VERSIONS (MODULE_ID, VERSION)
on update restrict
on delete cascade,
constraint FK_AMP_TEMP_REFERENCE_CFG_TEMP foreign key (TEMPLATE_ID)
references CFG_TEMPLATES (TEMPLATE_ID)
on update restrict
on delete cascade);
--???? 

101) create table AMP_METADATA
(MODULE_ID unsigned bigint not null,
VERSION integer not null,
ITEM_NAME char(256) not null,
ITEM_VALUE long binary null);
--???? 

102)*_ client_messages Table
*_subscriptions
*_client_message_counters
*_client_messages
*_pending_searches
*_pending_sync
*_props
*_subscription_props
*_server_message_counters
*_server_message_audit
-DOEC MBO Table created to keep track of the DOE Subscription IDs, used to tie the DOE
 SOAP Messages to the SUP Jason messages.  The Subscription ID id created by SUP and
 passed top DOE,  the table is created by the SUP DOEC component.

****** Cluster DB (clusterdb.db) ****
1) create table CLUSTER
(CLUSTERID numeric(18) not null default autoincrement,
CLUSTERNAME varchar(255) not null,
constraint PK_CLUSTER primary key clustered (CLUSTERID),
constraint AK_KEY_2_CLUSTER unique (CLUSTERNAME))
--This table is used to save all the cluster list of the system.

2) create table CLUSTER_INSTALLATION
(INSTALLID numeric(18) not null default autoincrement,
CLUSTERID numeric(18) not null,
HOSTNAME varchar(255) not null,
DIRECTORY varchar(255) not null,
constraint PK_CLUSTER_INSTALLATION primary key clustered (INSTALLID))
--This table is used to save all the installation information for the cluster node.

3) create table CLUSTER_MEMBER
(MEMBERID numeric(18) not null default autoincrement,
CLUSTERID numeric(18) not null,
NAME varchar(255) not null,
MEMBERTYPE numeric(18) not null,
INSTALLID numeric(18) not null,
constraint PK_CLUSTER_MEMBER primary key (MEMBERID),
constraint AK_KEY_2_CLUSTER_ unique clustered (CLUSTERID, NAME))
--This table is used to save all the member node information of the cluster.

4) create table CLUSTER_PROP
(NAME varchar(255) not null,
VALUE varchar(32767) null,
CLUSTERID numeric(18) not null,
TYPE varchar(32) not null default 'CLUSTER',
constraint PK_CLUSTER_PROP primary key clustered (NAME, CLUSTERID))
--This table is used to save all the cluster wide properties.

5) create table CLUSTER_RESOURCE
(CLUSTERID numeric(18) not null,
NAME varchar(255) not null,
OWNER numeric(18) null,
LEASEEXPIRES timestamp null,
LEASETIME int default 60000 not null,
RESOURCETYPE numeric(18) not null,
constraint PK_CLUSTER_RESOURCE primary key clustered (CLUSTERID, NAME))
--This table is used to save the cluster owner information in the current time.

6) create table ENTITY_TYPE
(TYPEID numeric(18) not null,
NAME varchar(255) not null,
constraint PK_ENTITY_TYPE primary key (TYPEID),
constraint AK_KEY_2_ENTITY_T unique clustered (NAME))
--This table is used to save all the entity type and type name.

7) create table MEMBER_PROP
(MEMBERID numeric(18) not null,
NAME varchar(255) not null,
VALUE1 varchar(4096) null,
VALUE2 varchar(255) null,
VALUE3 varchar(255) null,
VALUE4 varchar(255) null,
VALUE5 long varchar null,
VALUE6 long binary null,
PROPKEY numeric(18) not null default autoincrement,
constraint PK_MEMBER_PROP primary key clustered (PROPKEY))
--This table is used to save all the member node wide properties.

8) create table SYNC_DATA
(DOMAINID numeric(18) not null,
VERSION numeric(18) not null,
DATA long binary null,
CREATE_TIME datetime NOT NULL DEFAULT current timestamp,
USER_NAME varchar(128) not null,
constraint PK_SYNC_DATA primary key clustered (DOMAINID, VERSION))
--This table is used to save the synchronization bundles for the domain sync and cluster sync.

9) create table endpoint_prop
(domain_name varchar(32) not null default '',
name varchar(128) not null,
type varchar(32) not null,
template bit not null default 0,
props long varchar not null,
constraint pk_endpoint_prop primary key clustered (domain_name, name, type))
--This table is used to save all the endpoint properties,  cluster wide.

10) CREATE TABLE RELAY_SERVER (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
HOST varchar(1024) NOT NULL, PORT int NOT NULL,
SECURE_PORT int NOT NULL,
URL_SUFFIX varchar(1024) NOT NULL,
DESCRIPTION varchar(2048) NULL,
UNIQUE (Host,Port))
--This table is used to save the relayserver information.

11) CREATE TABLE RELAY_SERVER_HTTP_CREDENTIAL (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
RELAY_SERVER_ID int NOT NULL REFERENCES Relay_Server (ID) ON DELETE CASCADE,
USER_NAME varchar(1024) NOT NULL,
PASSWORD varchar(1024) NOT NULL,
UNIQUE (USER_NAME,RELAY_SERVER_ID)
)
--This table is used to save the http credentials for the target relayserver.

12) CREATE TABLE FARM_AND_PORT_TYPE (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
TYPE varchar(100) NOT NULL UNIQUE,
)
--This table is used to save the farm and port type for the relayserver. There are 3 types currently (replication, messaging, webservices)

13) CREATE TABLE BACKEND_FARM (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
NAME varchar(255) NOT NULL,
DESCRIPTION varchar(2048) NULL,
FARM_TYPE_ID int NOT NULL REFERENCES Farm_and_Port_Type (ID),
RELAY_SERVER_ID int NOT NULL REFERENCES Relay_Server (ID) ON DELETE CASCADE,
UNIQUE (Name,Relay_Server_ID))
--This table is used to save the backend farm configuration information.

14) CREATE TABLE SUP_SERVER_PORT (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
PORT int NOT NULL,
ENABLE bit NOT NULL DEFAULT 1,
CLUSTER_MEMBER_ID numeric(18) NOT NULL REFERENCES CLUSTER_MEMBER (MEMBERID) ON DELETE CASCADE)
--This table is used to save the SUP server port information.

15) CREATE TABLE BACKEND_SERVER (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
NAME varchar(100) NOT NULL,
TOKEN varchar(1024) NOT NULL,
BACKEND_FARM_ID int NOT NULL REFERENCES Backend_Farm (ID) ON DELETE CASCADE,
UNIQUE (Name,Backend_Farm_ID))
--This table is used to save all the backend server information for the relayserver configuration.

16) CREATE TABLE RSOE_PROXY (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
HOST varchar(1024) NOT NULL,
PORT int NOT NULL,
UNIQUE (Host,Port))
--This table is used to save the RSOE proxy information.

17) CREATE TABLE RSOE_PROXY_USER (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
USER_NAME varchar(1024) NOT NULL,
PASSWORD varchar(1024) NOT NULL,
PROXY_SERVER_ID int NOT NULL REFERENCES RSOE_PROXY (ID) ON DELETE CASCADE,
UNIQUE (USER_NAME,PROXY_SERVER_ID))
--This table is used to save the proxy user information for the target RSOE proxy.

18) CREATE TABLE RSOE (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
USE_HTTPS_PORT bit NOT NULL DEFAULT 0,
BACKEND_SERVER_ID int NOT NULL UNIQUE REFERENCES Backend_Server (ID) ON DELETE CASCADE,
SUP_SERVER_PORT_ID int NOT NULL REFERENCES SUP_Server_Port (ID) ON DELETE CASCADE,
START_OPTIONS varchar(2048) NULL,
HTTP_CREDENTIAL_ID int NULL REFERENCES RELAY_SERVER_HTTP_CREDENTIAL (ID) ON DELETE SET NULL,
PROXY_USER_ID int NULL REFERENCES RSOE_PROXY_USER (ID) ON DELETE SET NULL,
PROXY_SERVER_ID int NULL REFERENCES RSOE_PROXY (ID) ON DELETE SET NULL,
CERTIFICATE_FILE varchar(255) NULL,
TLS_TYPE varchar(3) NULL,
CN varchar(1024) NULL, -- 'Common name field of the certificate.'
O varchar(255) NULL, -- 'Organization name field of the certificate.'
OU varchar(255) NULL, -- 'Organization unit field of the certificate.'
STOPPED_BY_API bit DEFAULT 1 -- 'This column is used to show that if this RSOE is normally stopped(i.e. stopped by API) or abnormally stopped.
If the RSOE is stopped but the value of this column is 0, it means the RSOE is abnormally stopped.
This column should NOT be used to detect the RSOE runing or not.')
--This table is used to store the RSOE configuration information.

19) CREATE TABLE DBA.async_queue_prop (
pass INT NOT NULL,
count INT NULL,
PRIMARY KEY (pass) )
--This table is used to save the async queue properties.

20) CREATE TABLE SLD (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
HOST varchar(2083) NOT NULL,
PORT int NOT NULL constraint CKC_SLD_PORT check (PORT between 0 and 65535) ,
USE_HTTPS bit NOT NULL default 0,
USER_NAME varchar(1024) NOT NULL,
PASSWORD varchar(1024) NOT NULL,
UNIQUE (Host,Port))
--This table is used to save the SLD information.

21) CREATE TABLE SLD_SUPSERVER_INFO (
ID int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
SERVER_INFO TEXT NOT NULL,
CLUSTER_MEMBER_ID numeric(18) NOT NULL REFERENCES CLUSTER_MEMBER (MEMBERID) ON DELETE CASCADE)
--This table stores SUP server information which will be used to generate SLD payload.

22) CREATE TABLE cache_config(
id int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
name varchar(32),
data long varchar)
--This table is used to save the global cache configuration. (‘default’, ‘push’)

23) CREATE TABLE cache_addr_mapping(
id int NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
host_name varchar(128) not null,
ip_addr varchar(512) not null,
unique(host_name))
--This table is used to store cache configuration host name and ip address mapping.

24) create table SUP_PUSH_CONFIG
(id integer not null default autoincrement primary key,
name varchar(128) not null,
applicationid varchar(128) not null,
domain varchar(64) not null,
push_type varchar(4) not null, -- (APNS, BES, BIS, GCM)
unique(name, applicationid, domain))
--This table is used to save all the SUP push config information.

25) create table SUP_APNS_CONFIG
(id integer not null primary key,
p12cert long binary null,
password varchar(128) null,
server_addr varchar(512) not null,
port int not null,
feedback_addr varchar(512) null,
feedback_port int null
)
--This table is used to save all the APNS configuration information.

26) create table SUP_GCM_CONFIG
(
id integer not null primary key,
server_addr varchar(512) not null,
port int not null,
api_key varchar(512) not null)
--This table is used to save all the GCM configuration information.

27) create table SUP_BES_CONFIG
(id integer not null primary key,
server_url varchar(512) not null,
username varchar(64) null,
password varchar(128) null,
use_pap int not null default 0,)
--This table is used to save all the BES configuration information.

28) create table SUP_BIS_CONFIG
(id integer not null primary key,
server_url varchar(512), -- BlackBerry Push API server url address
password varchar(128) not null,)
--This table is used to save all the BIS configuration information.

29) create table SUP_CLUSTER_CONFIG
(name varchar(32) not null,
value varchar(128) not null,
primary key (name))
--This table is used to save all the cluster name and value.

30) create table djc_pc (
pc_cid decimal(20, 0),
pc_partition varchar(255) ,
pc_cycle integer ,
pc_owner varchar(255) ,
pc_timeout decimal(20, 0),
primary key(pc_cid, pc_artition))
--This table is used to save the internal EAServer cluster partition information.

****** Monitor DB (monitordb.db) **********

1) CREATE TABLE "mms_rbs_request" (
"id" integer not null default autoincrement
,"summaryId" varchar(50) NOT NULL
,"deviceId" varchar(255) NULL
,"applicationId" varchar(100) NULL
,"userName" varchar(255) NULL
,"packageName" varchar(255) NULL
,"domain" varchar(255) NULL
,"startTime" timestamp NULL
,"endTime" timestamp NULL
,"syncTime" integer NULL
,"sendRows" integer NULL
,"isError" bit NULL
,"sentBytes" integer NULL
,"receivedBytes" integer NULL
,"syncPhase" varchar(20) NULL
,"mboNames" varchar(500) NULL
,"operationNames" varchar(500) NULL
,"operationReplays" integer NULL
,"isMonitored" bit NULL
,"isLogged" bit NULL
,primary key( id ))
-- This table is used to save the detail history information of Replication-based Synchronization
-- id: the identification column
-- summaryId: the id value of realted summary info
-- deviceId: the physical id of the device
-- userName: the user name of the MBO data sync or Operation replay activity
-- packageName: the package name of the MBO data sync or Operation replay activity
-- domain: the domain name of the MBO data sync or Operation replay activity
-- startTime: the Start time of the MBO data sync or Operation replay activity
-- endTime: the Finish time of the MBO data sync or Operation replay activity
-- syncTime: the total time of the MBO data sync or Operation replay activity. Its unit is millisecond
-- sendRows: the Downloaded rows count (MBO data sync) or 1 if operation replay
-- isError: 1 (if any exceptions) or 0
-- sentBytes: byte size of downloaded
-- receivedBytes: byte size of uploaded
-- syncPhase: Upload or Download, means upload phase or download phase.
-- mboNames: the names of all mbo downloaded
-- operationNames: the names of all operation replays.
-- operationReplays: the count of operation replays. 0 if download phase.
-- isMonitored: 1 means monitoring data, otherwise not.
-- isLogged: 1 means domain logging data, otherwise not.

2) CREATE TABLE "mms_rbs_request_summary" (
"id" varchar(50) NOT NULL
,"packageName" varchar(255) NULL
,"domain" varchar(255) NULL
,"userName" varchar(255) NULL
,"deviceId" varchar(255) NULL
,"applicationId" varchar(100) NULL
,"startTime" timestamp NULL
,"endTime" timestamp NULL
,"syncTime" integer NULL
,"totalSentRows" integer NULL
,"totalReceivedRows" integer NULL
,"totalErrors" integer NULL
,"totalSentBytes" integer NULL
,"totalReceivedBytes" integer NULL
,"totalOperationReplays" integer NULL
,"isMonitored" bit NULL
,"isLogged" bit NULL
,"mbo" varchar(500) NULL,
primary key( id ))
-- This table is used to save the summary history information of Replication-based Synchronization. 
-- id: the identification column
-- deviceId: the physical id of the device
-- userName: the user name of the rbs request
-- packageName: the package name of the rbs request
-- domain: the domain name of the rbs request
-- startTime: the Start time of the rbs request 
-- endTime: the Finish time of the rbs request
-- syncTime: the total time of the rbs request. Its unit is millisecond
-- totalSentRows: the cout of downloaded rows (from server to client)
-- totalReceivedRows: is always 1.
-- totalErrors: the count of all exceptions during this rbs request.
-- totalSentBytes: the count of all downloaded bytes
-- totalReceivedBytes: the count of all uploaded bytes
-- totalOperationReplays: the count of all operation replays in the rbs request.
-- isMonitored: 1 means monitoring data, otherwise not.
-- isLogged: 1 means domain logging data, otherwise not.

4) CREATE TABLE "mms_data_change_notification" (
"id" integer not null default autoincrement
,"packageName" varchar(255) NULL
,"mboName" varchar(255) NULL
,"publicationName" varchar(255) NULL
,"domain" varchar(255) NULL
,"notificationTime" timestamp NULL
,"processingTime" integer NULL
,"isMonitored" bit NULL
,"isLogged" bit NULL
,primary key( id ) )
-- This table is used to save DCN information
-- packageName: the package name of the DCN
-- domain: the domain name of the DCN
-- publicationName: the publication of the DCN.
-- notificationTime: the time of the DCN.
-- processingTime: the total time of the DCN. Its unit is millisecond.
-- affectedRows: the affected rows of the DCN
-- isMonitored: 1 means monitoring data, otherwise not.
-- isLogged: 1 means domain logging data, otherwise not.

5) CREATE TABLE "mms_rbs_operation_replay" (
"id" integer not null default autoincrement
,"deviceId" varchar(255) NULL
,"userName" varchar(255) NULL
,"packageName" varchar(255) NULL
,"domain" varchar(255) NULL
,"applicationId" varchar(100) NULL
,"startTime" timestamp NULL
,"endTime" timestamp NULL
,"processTime" integer NULL
,"mbo" varchar(255) NULL
,"operation" varchar(255) NULL
,"isError" bit NULL
,"isMonitored" bit NULL
,"isLogged" bit NULL
,primary key( id ))
-- This table is used to save the mbo synchronization information.
-- deviceId: the physical id of the device
-- userName: the user name of the operation replay
-- packageName: the package name of the operation replay
-- domain: the domain name of the operation replay
-- startTime: the Start time of the operation replay
-- endTime: the Finish time of the operation replay
-- processTime: the total time of the operation replay. Its unit is millisecond
-- mbo: the mbo name of the operation replay.
-- operation: the operation name of the operation replay.
-- isError: 1 (if any exceptions) or 0
-- isMonitored: 1 means monitoring data, otherwise not.
-- isLogged: 1 means domain logging data, otherwise not.

6) CREATE TABLE "mms_mbs_message" (
"id" integer not null default autoincrement
,"deviceId" varchar(255) NULL
,"userName" varchar(255) NULL
,"packageName" varchar(255) NULL
,"domain" varchar(255) NULL
,"applicationId" varchar(100) NULL
,"receiveTime" timestamp NULL
,"pushTime" timestamp NULL
,"startTime" timestamp NULL
,"finishTime" timestamp NULL
,"processTime" integer NULL
,"mboName" varchar(255) NULL
,"operationName" varchar(255) NULL
,"messageType" varchar(50) NULL
,"isError" bit NULL
,"payloadSize" integer NULL
,"isPushMsg" bit NULL
,"isRequestMsg" bit NULL
,"isSubscription" bit NULL
,"isOperationReplay" bit NULL
,"sentPayloadSize" integer NULL
,"receivedPayloadSize" integer NULL
,"isMonitored" bit NULL
,"isLogged" bit NULL
,primary key( id ))
-- This table is used to save the mbs message information
-- deviceId: the physical id of the device
-- userName: the user name of the mbs message
-- packageName: the package name of the mbs message
-- domain: the domain name of the mbs message
-- receiveTime: the received time of inbound message. It is not meaningful for outbound message.
-- pushTime: the pushed time of outbound message. It is not meaningful for inbound message.
-- startTime: the Start time of the message
-- finishTime: the Finish time of the message
-- processTime: the total time of the message. Its unit is millisecond
-- mboName: the mbo name of outbound message or operation replay message
-- operationName: the operation name of operation replay message
-- messageType: the following value for every kind of message:
-- SUBSCRIBE,
-- UNSUBSCRIBE,
-- OPERATION_REPLAY,
-- RECOVER,
-- SUSPEND,
-- RESUME,
-- RESUMENOREPLAY,
-- IMPORT_DATA,
-- DATA_RESET,
-- LOGIN,
-- UNKNOWN_TYPE
-- isError: 1 (if any exceptions) or 0
-- payloadSize: the size of pay load of the message
-- isPushMsg: 1 if it is outbound message, otherwise 0
-- isRequestMsg: 1 if it is inbound message, otherwise 0
-- isSubscription: 1 if it is subscribe message, otherwise 0
-- isOperationReplay: 1 if it is operation replay message, otherwise 0
-- sentPayloadSize: the payloadsize of outbound message. It is not meaningful for inbound message
-- receivedPayloadSize: the payloadsize of inbound message. It is not meaningful for outbound message
-- isMonitored: 1 means monitoring data, otherwise not.
-- isLogged: 1 means domain logging data, otherwise not.

7) CREATE TABLE "mms_security_access" (
"id" integer not null default autoincrement
,"userName" varchar(255) NULL
,"domain" varchar(255) NULL
,"packageName" varchar(255) NULL
,"securityConfiguration" varchar(255) NULL
,"deviceId" varchar(255) NULL
,"applicationId" varchar(100) NULL
,"access_time" timestamp NULL
,"outcome" bit NULL
,"reason" LONG VARCHAR NULL
,primary key( id ))
-- This table is used to save security access information.
-- deviceId: the physical id of the device
-- userName: the user name of the authentication.
-- packageName: the package name of the authentication
-- domain: the domain name of the authentication
-- securityConfiguration: the security configuration name of the authentication.
-- access_time: the access time of the authentication.
-- outcome: the outcome of the authentication. 1 means success, 0 means fail.
-- reason: the reason for fail

8) CREATE TABLE "mms_queue_info" (
"id" integer not null default autoincrement
,"queueName" varchar(255) NULL
,"pendingItem" integer NULL
,"curTime" timestamp NULL
,primary key( id ))
-- This table is used to save queue information
-- queueName: the queue name
-- pendingItem: the count of the pending item of the queue.
-- curTime: the current time

9) CREATE TABLE "mms_concurrent_user_info" (
"id" integer not null default autoincrement
,"userName" varchar(255) NULL
,"packageName" varchar(255) NULL
,"curTime" timestamp NULL
,"domain" varchar(255) NULL
,"type" integer NULL
,primary key( id ))
-- This table is used to save concurrent user information
-- userName: the username of the rbs request or mbs message
-- packageName: the package name of the rbs request or mbs message
-- curTime: the current time
-- domain: the domain name of the rbs request or mbs message
-- type: 0 means rbs request, 1 means mbs message

10) CREATE TABLE "mms_sampling_time" (
"id" integer not null default autoincrement
,"sampling_time" timestamp NULL)
-- This table is used to save the sampling time sampling_time: the sampling time

11) CREATE TABLE "mms_rbs_mbo_sync_info" (
"id" integer not null default autoincrement
,"domain" varchar(255) NULL
,"packageName" varchar(255) NULL
,"mboName" varchar(255) NULL
,"startTime" timestamp NULL
,"endTime" timestamp NULL
,"syncTime" integer NULL
,"isError" bit NULL
,primary key( id ))
-- This table is used to save the MBO synchronization information.
-- id: the identification column
-- domain: the domain name of the mbo sync
-- packageName: the package name of the mbo sync
-- mboName: the name of mbo of the mbo sync.
-- startTime: the Start time of the mbo sync
-- endTime: the Finish time of the mbo sync
-- syncTime: the total time of the mbo sync. Its unit is millisecond
-- isError: 1 (if any exceptions) or 0

12) CREATE TABLE "mms_rbs_outbound_notification" (
"id" integer not null default autoincrement
,"packageName" varchar(255) NULL
,"publicationName" varchar(255) NULL
,"domain" varchar(255) NULL
,"deviceId" varchar(255) NULL
,"applicationId" varchar(100) NULL
,"notificationTime" timestamp NULL
,"subscriptionId" integer NULL
,"userName" varchar(255) NULL
,"subscriptionEnabled" bit NOT NULL
,"isMonitored" bit NULL
,"isLogged" bit NULL
,primary key( id ) )
-- This table is used to save outbound notification information
-- deviceId: the physical id of the device
-- userName: the user name of the outbound notification.
-- packageName: the package name of the outbound notification
-- domain: the domain name of the outbound notification
-- publicationName: the publication name of the outbound notification.
-- notificationTime: the time of the outbound notification.
-- subscriptionId: the subscription id of the outbound notification.
-- subscriptionEnabled: whether subscription is enabled
-- isMonitored: 1 means monitoring data, otherwise not.
-- isLogged: 1 means domain logging data, otherwise not.

13) CREATE TABLE other_log_info (
Id integer not null default autoincrement,
deviceId VARCHAR(100) NULL,
mboName VARCHAR(100) NULL,
operationName VARCHAR(100) NULL,
packageName VARCHAR(100) NULL,
startTime datetime NULL,
userName VARCHAR(128) NULL,
loglevel VARCHAR(10) NULL,
logMessage LONG VARCHAR NULL,
domain VARCHAR(100) NULL
,primary key( id ))
-- This table is used to save the other log information
-- id: the identification column
-- deviceId: the physical id of the device
-- mboName: the name of mbo of the mbo sync.
-- operationName: : the operation name of operation replay message
-- packageName: the package name of the outbound notification
-- startTime: the Start time of the mbo sync
-- userName: the user name of the outbound notification.
-- logLevel: the log level
-- logMessage: the detail log message content
-- domain: the domain name of the outbound notification

***** DomainLog DB (domainlogdb.db) ****
The internal tables metadata of DomainLog DB looks similar with that of monitorDB.
1) CREATE TABLE "operation_replay_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"mboName" varchar(64) NULL
,"operationName" varchar(64) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"payload" long varchar NULL
,"type" varchar(25) NULL
,"stage" varchar(10) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the operation replay information for the target domain.

2) CREATE TABLE "datasync_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"mboName" varchar(64) NULL
,"deviceId" varchar(200) NULL
,"syncGroup" long varchar NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"deleteCount" integer NOT NULL
,"upsertCount" integer NOT NULL
,"payload" long varchar NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"type" varchar(25) NULL
,"stage" varchar(10) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the synchronization information of the target domain.

3) CREATE TABLE "subscription_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"subscriptionId" varchar(36) NULL
,"enabled" bit NULL
,"subscription_type" varchar(36) NULL
,"syncGroup" varchar(64) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"payload" long varchar NULL
,"type" varchar(25) NULL
,"stage" varchar(10) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id )
)
-- This table is used to save the subscription information of the target domain
.
4) CREATE TABLE "dcn_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"mboName" varchar(64) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"payload" long varchar NULL
,"type" varchar(25) NULL
,"stage" varchar(10) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the DCN information of the target domain.

5) CREATE TABLE "wfdcn_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"securityConfiguration" varchar(36) NULL
,"workflowID" varchar(36) NULL
,"op" varchar(64) NULL
,"subject" varchar(128) NULL
,"from" varchar(256) NULL
,"to" varchar(256) NULL
,"body" varchar(16384) NULL
,"errorMessage" long varchar NULL
,"hasError" bit NULL
,"payload" long varchar NULL
,"type" varchar(25) NULL
,"stage" varchar(10) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the Workflow DCN information of the target domain.

6) CREATE TABLE "security_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"requestId" varchar(50) NULL
,"userName" varchar(128) NULL
,"securityConfiguration" varchar(36) NULL
,"outcome" varchar(36) NULL
,"reason" varchar(255) NULL
,"mboName" varchar(64) NULL
,"methodName" varchar(64) NULL
,"errorMessage" long varchar NULL
,"hasError" bit NULL
,"type" varchar(25) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the security configuration information.

7) CREATE TABLE error_info (
id integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" VARCHAR(200) NULL
,"requestId" varchar(50) NULL
,"mboName" VARCHAR(64) NULL
,"operationName" VARCHAR(64) NULL
,"userName" VARCHAR(128) NULL
,"hasError" bit NULL
,"errorMessage" LONG VARCHAR NULL
,"type" varchar(25) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the error information related with the target domain.

8) CREATE TABLE "proxy_request_response_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"logLevel" varchar(20) NULL
,"requestType" varchar(50) NULL
,"requestURL" varchar(500) NULL
,"httpEndPoint" varchar(100) NULL
,"postData" long varchar NULL
,"responseBody" long varchar NULL
,"requestHeaderFields" long varchar NULL
,"responseHeaderFields" long varchar NULL
,"responseCode" varchar(20) NULL
,"errorMessage" long varchar NULL
,"hasError" bit NULL
,"type" varchar(25) NULL
,"source" varchar(100) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"securityConfiguration" varchar(36) NULL
,"phase" varchar(10) NULL
,"requestBodySize" int NULL
,"responseBodySize" int NULL
,"requestHeaderSize" int NULL
,"responseHeaderSize" int NULL
,primary key( id ))
-- This table is used to save the proxy request/response information. It’s mainly related with the ODP request/response which uses Proxy Endpoint.

9) CREATE TABLE "proxy_push_info" (
"id" integer not NULL default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"logLevel" varchar(20) NULL
,"MessageBody" long varchar NULL
,"URN" varchar(100) NULL
,"errorMessage" long varchar NULL
,"hasError" bit NULL
,"type" varchar(25) NULL
,"source" varchar(100) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"deviceType" varchar(32) NULL
,"notificationType" varchar(4) NULL
,"receivedTime" timestamp NULL
,"processingStartedTime" timestamp NULL
,"sendStartedTime" timestamp NULL
,"sendFinishedTime" timeStamp NULL
,"notificationHeaderFields" long varchar NULL
,"notificationPayload" long varchar NULL
,primary key( id ))
-- This table is used to save the proxy push information.

10) CREATE TABLE "sis_notification_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"syncGroup" varchar(64) NULL
,"mboName" varchar(64) NULL
,"payload" long varchar NULL
,"errorMessage" long varchar NULL
,"hasError" bit NULL
,"type" varchar(25) NULL
,"miscInfo" varchar(100) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,primary key( id ))
-- This table is used to save the SIS (server initialized synchronization) notification information.

11) CREATE TABLE sup_server_log (
id integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"requestId" varchar(50) NULL
,"userName" varchar(36) NULL
,"hasError" bit NULL
,"type" varchar(25) NULL
,"logLevel" varchar(16) NULL
,"bucketName" varchar(16) NULL
,"category" varchar(256) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"logMessage" long varchar NULL
,primary key( id ))
-- This table is used to save the SUP server log information.

12) CREATE TABLE "dispatcher_tm_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"type" varchar(25) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"securityConfiguration" varchar(36) NULL
,"requestType" varchar(50) NULL
,"requestURL" varchar(500) NULL
,"responseCode" varchar(20) NULL
,"phase" varchar(10) NULL
,"requestHeaderSize" int NULL
,"responseHeaderSize" int NULL
,"requestBodySize" int NULL
,"responseBodySize" int NULL
,"requestHeaderFields" long varchar NULL
,"responseHeaderFields" long varchar NULL
,"requestBody" long binary NULL
,"responseBody" long binary NULL
,primary key( id ))
-- This table is used to save TM handler request/response information.

14) CREATE TABLE "dispatcher_tm2_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"type" varchar(25) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"securityConfiguration" varchar(36) NULL
,"requestType" varchar(50) NULL
,"requestURL" varchar(500) NULL
,"responseCode" varchar(20) NULL
,"phase" varchar(10) NULL
,"requestHeaderSize" int NULL
,"responseHeaderSize" int NULL
,"requestBodySize" int NULL
,"responseBodySize" int NULL
,"requestHeaderFields" long varchar NULL
,"responseHeaderFields" long varchar NULL
,"requestBody" long binary NULL
,"responseBody" long binary NULL
,primary key( id ))
-- This table is used to save the TM2 handler request/response information.

15) CREATE TABLE "dispatcher_ml_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"type" varchar(25) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"securityConfiguration" varchar(36) NULL
,"requestType" varchar(50) NULL
,"requestURL" varchar(500) NULL
,"responseCode" varchar(20) NULL
,"phase" varchar(10) NULL
,"requestHeaderSize" int NULL
,"responseHeaderSize" int NULL
,"requestBodySize" int NULL
,"responseBodySize" int NULL
,"requestHeaderFields" long varchar NULL
,"responseHeaderFields" long varchar NULL
,"requestBody" long binary NULL
,"responseBody" long binary NULL
,primary key( id ))
-- This table is used to save the ml Handler request/response information.

16) CREATE TABLE "application_registrations_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"type" varchar(25) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"isAutoRegistration" bit NULL
,"securityConfiguration" varchar(36) NULL
,"template" varchar(50) NULL
,primary key( id ))
-- This table is used to save the application registration information.

17) CREATE TABLE "application_settings_info" (
"id" integer not null default autoincrement
,"nodeId" varchar(36) NULL
,"createTime" timestamp NULL
,"threadId" integer NOT NULL
,"applicationId" varchar(64) NULL
,"packageName" varchar(100) NULL
,"domain" varchar(36) NULL
,"deviceId" varchar(200) NULL
,"userName" varchar(128) NULL
,"requestId" varchar(50) NULL
,"hasError" bit NULL
,"errorMessage" long varchar NULL
,"type" varchar(25) NULL
,"transactionId" varchar(32) NULL
,"rootContextId" varchar(32) NULL
,"securityConfiguration" varchar(36) NULL
,"requestType" varchar(50) NULL
,"requestURL" varchar(500) NULL
,"responseCode" varchar(20) NULL
,"phase" varchar(10) NULL
,"requestHeaderSize" int NULL
,"responseHeaderSize" int NULL
,"requestBodySize" int NULL
,"responseBodySize" int NULL
,"requestHeaderFields" long varchar NULL
,"responseHeaderFields" long varchar NULL
,"requestBody" long varchar NULL
,"responseBody" long varchar NULL
,primary key( id )
)
-- This table is used to save the application settings information.

***** Thank you to SAP SMP Development  for the help