DELIMITER //
-- ------------ --
-- Domain model --
-- ------------ --

-- 
-- SYSTEM tables
-- 

-- use qwoss
-- //

SET FOREIGN_KEY_CHECKS = 0
//

SET storage_engine=InnoDB
//

CREATE TABLE `QX_KEYS` (
  `TABLENAME` 		VARCHAR(240) 	NOT NULL,
  `KEYVALUE` 		INT(10) 	NOT NULL,
  PRIMARY KEY (`TABLENAME`)
)
//

create table `QX_FIELDINFO` (
  `TABLE_NAME`		VARCHAR(128)	NOT NULL,
  `COLUMN_NAME`		VARCHAR(128)	NOT NULL,
  `COLUMN_TYPE`		DECIMAL(2)	NOT NULL,
  `ENUM_VALUE`		DECIMAL(2),
  `ENUM_LABEL`		VARCHAR(128)
)
//

create unique index `IDX_QX_FIELDINFO` on `QX_FIELDINFO` (
  `TABLE_NAME`,
  `COLUMN_NAME`,
  `ENUM_VALUE`
)
//

create table `QX_SYS_PROP` (
  `PKEY`            INT           NOT NULL,
  `PROP_ID`         VARCHAR(255)  NOT NULL,
  `PROP_VAL`        VARCHAR(255),
  `PROP_TEXT`       LONGTEXT,
  `PROP_DATA`       LONGBLOB,
  `NEED_RESTART`    INT           DEFAULT 0,
  `DESCRIPTION`     VARCHAR(255),
primary key (`PKEY`)
)
//

create table `QX_LANGUAGE` (
  `PKEY`            INT          NOT NULL,
  `LANGUAGE_ID`     CHAR(2)      NOT NULL,
  `NAME`            VARCHAR(255) NOT NULL,
primary key (`LANGUAGE_ID`)
)
//


create table `QX_TIMEZONE` (
  `PKEY`            INT           NOT NULL,
  `TIMEZONE_ID`     VARCHAR(255)  NOT NULL,
  `NAME`            VARCHAR(255)  NOT NULL,
  `OFFSET`          INT           NOT NULL,
primary key (`PKEY`)
)
//


create table `QX_COUNTRY` (
  `PKEY`            INT          NOT NULL,
  `COUNTRY_ID`      VARCHAR(3)   NOT NULL,
  `NAME`            VARCHAR(255) NOT NULL,
  `DEF_LANG_ID`     CHAR(2),
  `DEF_TIMEZONE_ID` VARCHAR(255),
primary key (`PKEY`)
)
//


create table `QX_TIME_PATTERN` (
  `PKEY`            INT          NOT NULL,
  `TIME_PATTERN_ID` VARCHAR(255) NOT NULL,
primary key (`PKEY`)
)
//


create table `QX_DATE_PATTERN` (
  `PKEY`            INT          NOT NULL,
  `DATE_PATTERN_ID` VARCHAR(255) NOT NULL,
primary key (`PKEY`)
)
//


create view QX_DD_DATEPOS as
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as `NAME`
    from QX_FIELDINFO
    where (TABLE_NAME = 'QX_USER_SETTINGS') and (COLUMN_NAME = 'DATE_POSITION')
//


create view `QX_DD_YESNO` as 
    select
        `ENUM_VALUE` as `PKEY`,
        `ENUM_LABEL` as `NAME`
    from `QX_FIELDINFO`
    where (`TABLE_NAME` = '_FAKE_YES_NO') and (`COLUMN_NAME` = 'FAKE_COLUMN')
//


--
-- ENTITY/FOCUS
--

create table `QX_ENTITY` (
  `ENTITY_ID`  VARCHAR(255) NOT NULL,
  `ENTITYDATA` LONGBLOB,
primary key (`ENTITY_ID`)
)
//


--
-- SECURITY
--

create table `QX_VIEW_OBJECT_TYPES` (
  `PKEY`             INT          NOT NULL,
  `TYPE_DESCRIPTION` VARCHAR(255) NOT NULL,
primary key (`PKEY`)
)
//


create table `QX_VIEW_OBJECTS` (
  `PKEY`           INT          NOT NULL,
  `NAME`           VARCHAR(255) NOT NULL,
  `TYPE`           INT          NOT NULL,
  `PARENT_OBJECT`  INT          NULL,
  `ORDER_IN_GROUP` INT          NOT NULL DEFAULT 0,
  `ICON`           VARCHAR(255) NULL,
  `INFRAMELINKS`   BIT          NOT NULL DEFAULT 0,
  `GRID`           BIT          NOT NULL DEFAULT 1,
primary key (`PKEY`),
constraint `FK_QX_VIEW_OBJECTS_TO_OBJECT_TYPES` foreign key (`TYPE`) references `QX_VIEW_OBJECT_TYPES` (`PKEY`),
constraint `FK_QX_VIEW_OBJECTS_TO_PARENT_OBJECT` foreign key (`PARENT_OBJECT`) references `QX_VIEW_OBJECTS` (`PKEY`)
)
//


create table `QX_FORM_SETTINGS` (
  `VIEW_OBJECT_ID`      INT          NOT NULL,
  `ENTITY_ID`           VARCHAR(255),
  `GRID`                INT,
  `USE_DEFAULT_ACTIONS` BIT DEFAULT 1,
  `LABELS_LAYOUT`       BIT DEFAULT 0,
  `MYQUEWEB`            BIT NOT NULL DEFAULT 1,
  `AUTOSEARCH`          BIT NOT NULL DEFAULT 0,
  IS_VISIBLE            BIT DEFAULT 1,
constraint `FK_QX_FORM_SETTINGS_TO_VIEW_OBJECTS` foreign key (`VIEW_OBJECT_ID`) references `QX_VIEW_OBJECTS` (`PKEY`)
)
//


create table `QX_ROLE` (
  `ROLE_ID`          INT          NOT NULL,
  `DEFAULT_FOCUS_ID` INT          NULL,
  `NAME`             VARCHAR(255) NOT NULL,
  `DESCRIPTION`      VARCHAR(255) NULL,
primary key (`ROLE_ID`),
constraint `FK_QX_ROLE_TO_FOCUS` foreign key (`DEFAULT_FOCUS_ID`) references `QX_VIEW_OBJECTS` (`PKEY`)
)
//


create table `QX_ACCESSLEVEL` (
  `PKEY` INT          NOT NULL,
  `NAME` VARCHAR(255) NOT NULL,
primary key (`PKEY`)
)
//


create table `QX_PERMISSION` (
  `PERMISSION_ID`          INT NOT NULL,
  `ROLE_ID`                INT NOT NULL,
  `ACCESS_LEVEL`           INT NOT NULL,
  `PERMISSION_OBJECT_TYPE` INT NOT NULL,
  `OBJECT_ID`              INT NOT NULL,
primary key (`PERMISSION_ID`),
constraint `FK_QX_PERMISSION_TO_ACCESS_LEVEL` foreign key (`ACCESS_LEVEL`) references `QX_ACCESSLEVEL` (`PKEY`),
constraint `FK_QX_PERMISSION_TO_ROLE` foreign key (`ROLE_ID`) references `QX_ROLE` (`ROLE_ID`),
constraint `FK_QX_PERMISSION_TO_PERMISSION_OBJECT_TYPES` foreign key (`PERMISSION_OBJECT_TYPE`) references `QX_VIEW_OBJECT_TYPES` (`PKEY`),
constraint `FK_QX_PERMISSION_TO_QX_VIEW_OBJECTS` foreign key (`OBJECT_ID`) references `QX_VIEW_OBJECTS` (`PKEY`)
)
//


create table QX_USER_TYPE (
  `USER_TYPE_ID`         INT		NOT NULL,
  `NAME`               VARCHAR(255)	NOT NULL,
primary key (`USER_TYPE_ID`)
)
//


create table QX_USERS (
  `PKEY`      INT          NOT NULL,
  `LOGINNAME` VARCHAR(255) NOT NULL,
  `FULLNAME`  VARCHAR(255) NULL,
  `PASSWORD`  VARCHAR(255) NULL,
  `EMAIL`     VARCHAR(255) NULL,
  `USER_TYPE` INT          NOT NULL,
primary key (`PKEY`),
constraint `FK_QX_USERS_TO_USERTYPE` foreign key (`USER_TYPE`) references `QX_USER_TYPE` (`USER_TYPE_ID`)
)
//
create unique index `IDX_USERS_LOGINNAME` on `QX_USERS` (`LOGINNAME`)
//


create table QX_WORKGROUP (
  `PKEY`             INT         NOT NULL,
  `NAME`             VARCHAR(40) NOT NULL,
  `DESCRIPTION`      VARCHAR(80),
  `NOTIFICATIONADDR` VARCHAR(40),
  `NOTIFYMETHOD`     DECIMAL(2),
primary key (`PKEY`)
)
//


create table QX_GROUPMEMBER (
  `PKEY`           INT         NOT NULL,
  `EMPLOYEEGROUP`  INT         NOT NULL,
  `WORKGROUPGROUP` INT         NOT NULL,
  `NOTIFYMETHOD`   DECIMAL(2),
  `TIER`           INT         DEFAULT 0,
primary key (`PKEY`),
constraint FK_QX_GROUPMEMBER_EMPLOYEE foreign key (`EMPLOYEEGROUP`) references QX_USERS (`PKEY`),
constraint FK_QX_GROUPMEMBER_WORKGROUP foreign key (`WORKGROUPGROUP`) references QX_WORKGROUP(`PKEY`)
)
//
create index IDX_QX_GROUPMEMBER_EMPLOYEE on QX_GROUPMEMBER (`EMPLOYEEGROUP`)
//
create index IDX_QX_GROUPMEMBER_WORKGROUP on QX_GROUPMEMBER (`WORKGROUPGROUP`)
//


create table `QX_ROLE_TO_USERS` (
  `PKEY`     INT NOT NULL,
  `USER_ID`  INT NOT NULL,
  `ROLE_ID`  INT NOT NULL,
constraint `FK_QX_ROLE_TO_USER_TO_FIRST_ROLE_FK` foreign key (`ROLE_ID`) references `QX_ROLE` (`ROLE_ID`),
constraint `FK_QX_ROLE_TO_USER_TO_SECOND_USER_FK` foreign key (`USER_ID`) references `QX_USERS` (`PKEY`)
)
//


create table `QX_WORKGROUP_TO_ROLE` (
  `PKEY`          INT NOT NULL,
  `ROLE_ID`       INT NOT NULL,
  `WORKGROUP_ID`  INT NOT NULL,
constraint `FK_QX_WORKGROUP_TO_ROLE_TO_SECOND_ROLE_FK` foreign key (`ROLE_ID`) references `QX_ROLE` (`ROLE_ID`),
constraint `FK_QX_WORKGROUP_TO_ROLE_TO_FIRST_WORKGROUP_FK` foreign key (`WORKGROUP_ID`) references `QX_WORKGROUP` (`PKEY`)
)
//


create view `QX_USER_PERMISSIONS` as
    select u.PKEY as USER_ID, p.*, vo.NAME as OBJECT_NAME
    from QX_PERMISSION p
		inner join QX_ROLE_TO_USERS r2u on p.ROLE_ID = r2u.ROLE_ID
		inner join QX_USERS u on u.PKEY = r2u.USER_ID
		inner join QX_VIEW_OBJECTS vo on p.OBJECT_ID = vo.PKEY
    union
    select u.PKEY as USER_ID, p.*, vo.NAME as OBJECT_NAME
    from QX_PERMISSION p
		inner join QX_WORKGROUP_TO_ROLE w2r on p.ROLE_ID = w2r.ROLE_ID
		inner join QX_WORKGROUP w on w.PKEY = w2r.WORKGROUP_ID
		inner join QX_GROUPMEMBER gm on gm.WORKGROUPGROUP = w.PKEY
		inner join QX_USERS u on u.PKEY = gm.EMPLOYEEGROUP
		inner join QX_VIEW_OBJECTS vo on p.OBJECT_ID = vo.PKEY
//


--
-- USERS, WORKGROUPS, ROLES
--

create view QX_DD_WORKGROUPNOTIFYMETHOD as
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as `NAME`
    from QX_FIELDINFO
    where (TABLE_NAME = 'qx_workgroup') and (COLUMN_NAME = 'notifymethod')
//


create view QX_DD_GROUPMEMBERNOTIFYMETHOD as
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as `NAME`
    from QX_FIELDINFO
    where (TABLE_NAME = 'qx_groupmember') and (COLUMN_NAME = 'notifymethod')
//


create table QX_USER_PROP (
  PROP_ID              VARCHAR(255) NOT NULL,
  USER_ID              INT          NOT NULL,
  USER_TYPE_ID         INT          NOT NULL,
  PROP_VAL             VARCHAR(255),
primary key (PROP_ID, USER_ID, USER_TYPE_ID),
constraint FK_QX_USER_PROP_QX_USER foreign key (USER_TYPE_ID) references QX_USER_TYPE (USER_TYPE_ID)
)
//
create index IDX_QX_USER_PROP_USER      on QX_USER_PROP (USER_ID)
//
create index IDX_QX_USER_PROP_USER_TYPE on QX_USER_PROP (USER_TYPE_ID)
//


create table QX_USER_SETTINGS (
  PKEY        INT  NOT NULL,
  USER_ID     INT  NOT NULL,
  LANG        INT  NULL,
  COUNTRY     INT  NULL,
  TIMEZONE    INT  NULL,
  DPATTERN    INT  NULL,
  TPATTERN    INT  NULL,
  DPOS        INT  NULL,
primary key (PKEY)
)
//
create unique index IDX_USER_SETTINGS_USER_ID on QX_USER_SETTINGS (USER_ID)
//


--
-- View
--

create table QX_HEADER (
  FORM_ID              VARCHAR(255) NOT NULL,
  HEADER_ORDER         INT NOT NULL,
  CLIENT_WIDTH         INT,
primary key (FORM_ID, HEADER_ORDER)
)
//


create table QX_ROW (
  FORM_ID              VARCHAR(255) NOT NULL,
  ROW_ORDER            INT NOT NULL,
primary key (FORM_ID, ROW_ORDER)
)
//


create table QX_COL (
  ROW_ORDER            VARCHAR(255) NOT NULL,
  FORM_ID              VARCHAR(255) NOT NULL,
  COL_ORDER            INT NOT NULL,
  FIELD_ID             VARCHAR(255),
  ROWSPAN              INT,
  COLSPAN              INT,
primary key (ROW_ORDER, FORM_ID, COL_ORDER)
)  
//


create table QX_BUTTON (
  FORM_ID              VARCHAR(255) NOT NULL,
  BUTTON_ID            VARCHAR(255) NOT NULL,
  BUTTON_ORDER         INT,
  PERMISSION           INT,
primary key (FORM_ID, BUTTON_ID)
)
//


create table QX_HTML_ELEMENT (
  FORM_ID              VARCHAR(255) NOT NULL,
  HTML_ELEMENT_ID      VARCHAR(255) NOT NULL,
  HTML_ELEMENT_ORDER   INT,
primary key (FORM_ID, HTML_ELEMENT_ID)
)
//


create table QX_HIDDENCONTROL (
  FORM_ID             VARCHAR(255) NOT NULL,
  FIELD_ID            VARCHAR(255) NOT NULL,
primary key (FORM_ID, FIELD_ID)
)
//


create table QX_LINK (
  FORM_ID              VARCHAR(255) NOT NULL,
  FIELD_ID             VARCHAR(255) NOT NULL,
  LINKED_FORM_ID       VARCHAR(255) NOT NULL,
primary key (FORM_ID, FIELD_ID)
)
//


create table QX_EXTERNALFIELD (
  EXTERNALFORM_ID      VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
  EXTERNALFIELD        VARCHAR(255),
  SOURCEFIELD          VARCHAR(255) NULL,
primary key (EXTERNALFORM_ID, FORM_ID)
)
//
create index IDX_QX_EXTERNALFIELD_FORM  on QX_EXTERNALFIELD (FORM_ID)
//
create index IDX_QX_EXTERNALFIELD_FIELD on QX_EXTERNALFIELD (EXTERNALFIELD)
//


create table QX_EXTERNALFORM (
  EXTERNALFORM_ID      VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
primary key (EXTERNALFORM_ID, FORM_ID)
)
//
create index IDX_QX_EXTERNALFORM_FORM on QX_EXTERNALFORM (FORM_ID)
//


create table QX_EXTERNALSET (
  EXTERNALFORM_ID      VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
primary key (EXTERNALFORM_ID, FORM_ID)
)
//
create index IDX_QX_EXTERNALSET_FORM on QX_EXTERNALSET (FORM_ID)
//


create table QX_LINKEDDATASET (
  ENTITY_ID            VARCHAR(255)    NOT NULL,
  DATASET_ID           VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
constraint PK_QX_LINKEDDATASET primary key (FORM_ID, DATASET_ID)
)
//
create index IDX_QX_LINKEDDATASET_FORM on QX_LINKEDDATASET (FORM_ID)
//


--
-- Localization
--

create table QX_LOCALIZATION_TYPE (
  LOCALIZATION_TYPE_ID   INT             NOT NULL,
  NAME                   VARCHAR(255)    NOT NULL,
primary key (LOCALIZATION_TYPE_ID)
)
//


create table QX_LOCALIZED_OBJECT_TYPE (
  OBJECT_TYPE_ID  INT               NOT NULL,
  NAME            VARCHAR(255)      NOT NULL,
primary key (OBJECT_TYPE_ID)
)
//


create table QX_LOCALIZATION (
  LOCALIZATION_ID	    VARCHAR(255)      NOT NULL,
  LOCALIZATION_TYPE_ID      INT               NOT NULL,
  OBJECT_TYPE_ID	    INT               NOT NULL,
  LANGUAGE_ID		    CHAR(2)           NOT NULL,
  CONTENT               NVARCHAR(4000)        NOT NULL,
primary key (LOCALIZATION_ID, LOCALIZATION_TYPE_ID, LANGUAGE_ID, OBJECT_TYPE_ID),
constraint FK_QX_LOCALIZATION_QX_LANGUAGE foreign key (LANGUAGE_ID) references QX_LANGUAGE (LANGUAGE_ID),
constraint FK_QX_LOCALIZATION_QX_LOCALIZATION_TYPE foreign key (LOCALIZATION_TYPE_ID) references QX_LOCALIZATION_TYPE (LOCALIZATION_TYPE_ID),
constraint FK_QX_LOCALIZATION_QX_LOCALIZED_OBJECT_TYPE foreign key (OBJECT_TYPE_ID) references QX_LOCALIZED_OBJECT_TYPE (OBJECT_TYPE_ID)
)
//
create index IDX_QX_LOCALIZATION_LANGUAGE on QX_LOCALIZATION (LANGUAGE_ID)
//
create index IDX_QX_LOCALIZATION_OBJECT on QX_LOCALIZATION (OBJECT_TYPE_ID)
//


--
-- Context menu
--

create table QX_CONTEXTMENU (
  CONTEXTMENU_ID    VARCHAR(255)    NOT NULL,
primary key (CONTEXTMENU_ID)
)
//


create table QX_MENUITEM (
  MENUITEM_ID       VARCHAR(255)    NOT NULL,
  CONTEXTMENU_ID    VARCHAR(255)    NOT NULL,
  MENUITEM_ORDER    INT,
primary key (MENUITEM_ID)
)
//
create index IDX_QX_MENUITEM_CONTEXTMENU on QX_MENUITEM (CONTEXTMENU_ID)
//
create index IDX_QX_MENUITEM_ORDER on QX_MENUITEM (MENUITEM_ORDER)
//


create table QX_CONTEXTMENU_FORM (
  FORM_ID            VARCHAR(255)    NOT NULL,
  CONTEXTMENU_ID    VARCHAR(255)    NOT NULL,
primary key (FORM_ID, CONTEXTMENU_ID)
)
//



--
-- ALERTS
--

create table QX_ALERT (
  ALERT_ID             INT		NOT NULL,
  SENDER_ID            INT		NOT NULL,
  SENDER_TYPE          INT		NOT NULL,
  RECIPIENT_ID         INT,
  RECIPIENT_TYPE       INT,
  WORKGROUP_ID         INT,
  TIER                 DECIMAL(2),
  TO_ALL               DECIMAL(2)	           DEFAULT 0,
  FOCUS_ID             VARCHAR(255),
  TAB_ID               VARCHAR(255),
  FORM_ID              VARCHAR(255),
  RECORD_ID            VARCHAR(100),
  RECORD_ID2           VARCHAR(100),
  RECORD_ID3           VARCHAR(100),
  RECORD_ID4           VARCHAR(100),
  MESSAGE              VARCHAR(1022)	NOT NULL,
  SEVERITY             DECIMAL(2)	NOT NULL,
  DATEPOSTED           DATETIME		NOT NULL,
  CREATOR_ID           INT		NOT NULL,
  CREATOR_TYPE         INT		NOT NULL,
primary key (ALERT_ID),
constraint FK_QX_ALERT_QX_USER_TYPE1	foreign key (SENDER_TYPE)	references QX_USER_TYPE (USER_TYPE_ID),
constraint FK_QX_ALERT_QX_USER_TYPE2	foreign key (RECIPIENT_TYPE)	references QX_USER_TYPE (USER_TYPE_ID),
constraint FK_QX_ALERT_QX_USER_TYPE3	foreign key (CREATOR_TYPE)	references QX_USER_TYPE (USER_TYPE_ID)
)
//
create index IDX_QX_ALERT_SENDER         on QX_ALERT (SENDER_ID)
//
create index IDX_QX_ALERT_SENDER_TYPE    on QX_ALERT (SENDER_TYPE)
//
create index IDX_QX_ALERT_RECIPIENT      on QX_ALERT (RECIPIENT_ID)
//
create index IDX_QX_ALERT_RECIPIENT_TYPE on QX_ALERT (RECIPIENT_TYPE)
//
create index IDX_QX_ALERT_CREATOR        on QX_ALERT (CREATOR_ID)
//
create index IDX_QX_ALERT_CREATOR_TYPE   on QX_ALERT (CREATOR_TYPE)
//
create index IDX_QX_ALERT_WORKGROUP      on QX_ALERT (WORKGROUP_ID)
//
create index IDX_QX_ALERT_FOCUS          on QX_ALERT (FOCUS_ID)
//
create index IDX_QX_ALERT_TAB            on QX_ALERT (TAB_ID)
//
create index IDX_QX_ALERT_FORM           on QX_ALERT (FORM_ID)
//


create table QX_ALERT_BLOCK (
  ALERT_BLOCK_ID       INT		NOT NULL,
  ALERT_ID             INT		NOT NULL,
  RECIPIENT_ID         INT		NOT NULL,
  RECIPIENT_TYPE       INT		NOT NULL,
primary key (ALERT_BLOCK_ID),
constraint FK_QX_ALERT_BLOCK_QX_ALERT		foreign key (ALERT_ID)		references QX_ALERT (ALERT_ID),
constraint FK_QX_ALERT_BLOCK_QX_USER_TYPE	foreign key (RECIPIENT_TYPE)	references QX_USER_TYPE (USER_TYPE_ID)
)
//
create unique index IDX_ALERT_BLOCK on QX_ALERT_BLOCK (
  ALERT_ID,
  RECIPIENT_ID,
  RECIPIENT_TYPE
)
//
create index IDX_QX_ALERT_BLOCK_RECIPIENT      on QX_ALERT_BLOCK (RECIPIENT_ID)
//
create index IDX_QX_ALERT_BLOCK_RECIPIENT_TYPE on QX_ALERT_BLOCK (RECIPIENT_TYPE)
//


create view QX_DD_ALERTSEVERITY as 
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as `NAME`
    from QX_FIELDINFO
    where (TABLE_NAME = 'qx_alert') and (COLUMN_NAME = 'severity')
//


--
-- ATTACHMENTS
--

create table QX_ATTACHMENT (
  ATTACHMENT_ID        INT        NOT NULL,
  PROCESS_ID        BIGINT,
  FILENAME        VARCHAR(255) NOT NULL,
  CREATED        DATETIME    NOT NULL,
  INDEXED        CHAR(1),
  DESCRIPTION        VARCHAR(250),
  FILETYPE        VARCHAR(50) DEFAULT 'application/octet-stream',
  FILEEXT        VARCHAR(10),
  DATA            LONGBLOB,
  USER_ID        INT,
constraint PK_QX_ATTACHMENT primary key (ATTACHMENT_ID)
)
//

create index IDX_QX_ATTACHMENT_USER      on QX_ATTACHMENT (USER_ID)
//



create table QX_TEMP_ATTACH (
  TEMP_ATTACH_ID	INT		NOT NULL,
  PROCESS_ID		BIGINT,
  FILENAME		VARCHAR(255),
  CREATED		DATETIME	NOT NULL,
  `DATA`		LONGBLOB, 
  FILETYPE		VARCHAR(50)	           DEFAULT 'application/octet-stream',
  FILEEXT		VARCHAR(10),
  USER_TYPE_ID		INT,
  USER_ID		INT,
primary key (TEMP_ATTACH_ID),
constraint FK_QX_TEMP_ATTACH_QX_USER_TYPE foreign key (USER_TYPE_ID) references QX_USER_TYPE (USER_TYPE_ID)
)
//

create index IDX_QX_TEMP_ATTACH_USER_TYPE on QX_TEMP_ATTACH (USER_TYPE_ID)
//
create index IDX_QX_TEMP_ATTACH_USER      on QX_TEMP_ATTACH (USER_ID)
//


--
-- IMAGES
--

create table QX_IMAGE (
  IMAGE_ID             INT        NOT NULL,
  FILE_SIZE            INT,
  CREATED              DATETIME        NOT NULL,
  MODIFIER_ID          INT        NOT NULL,
  MODIFIED             DATETIME        NOT NULL,
  FILENAME             VARCHAR(255)    NOT NULL,
  FOLDER               VARCHAR(255),
  CREATOR_ID           INT        NOT NULL,
constraint PK_QX_IMAGE primary key (IMAGE_ID)
)
//

create index IDX_QX_IMAGE_MODIFIER      on QX_IMAGE (MODIFIER_ID)
//
create index IDX_QX_IMAGE_CREATOR       on QX_IMAGE (CREATOR_ID)
//


create table QX_IMAGE_FOLDER (
  FOLDER_ID            INT		NOT NULL,
  FOLDER               VARCHAR(255)	NOT NULL,
primary key (FOLDER_ID)
)
//


create table QX_IMAGE_USAGE (
  IMAGE_USAGE_ID       INT		NOT NULL,
  IMAGE_ID             INT		NOT NULL,
  RECORD_KEY           INT		NOT NULL,
  TABLE_NAME           VARCHAR(50)	NOT NULL,
primary key (IMAGE_USAGE_ID),
constraint FK_QX_IMAGE_USAGE_QX_IMAGE foreign key (IMAGE_ID) references QX_IMAGE (IMAGE_ID)
)
//

create index IDX_QX_IMAGE_USAGE_IMAGE on QX_IMAGE_USAGE (IMAGE_ID)
//


--
-- LOGGING
--

create table QX_LOG_TYPE (
  LOG_TYPE_ID          INT		NOT NULL,
  `NAME`               VARCHAR(50)	NOT NULL,
primary key (LOG_TYPE_ID)
)
//


create table QX_LOG_CLASS (
  LOG_CLASS_ID         INT		NOT NULL,
  `NAME`               VARCHAR(50)	NOT NULL,
  TABLE_NAME           VARCHAR(50)	NOT NULL,
primary key (LOG_CLASS_ID)
)
//


create table QX_LOG (
  LOG_ID               INT		NOT NULL,
  LOG_PROCESS_ID       BIGINT		NOT NULL,
  LOG_TYPE_ID          INT		NOT NULL,
  LOG_CLASS_ID         INT		NOT NULL,
  USER_TYPE_ID         INT		NOT NULL,
  USER_ID              INT		NOT NULL,
  DESCRIPTION          VARCHAR(1022),
  STAMP                DATETIME		NOT NULL,
primary key (LOG_ID),
constraint FK_QX_LOG_QX_USER_TYPE	foreign key (USER_TYPE_ID)	references QX_USER_TYPE (USER_TYPE_ID),
constraint FK_QX_LOG_QX_LOG_TYPE	foreign key (LOG_TYPE_ID)	references QX_LOG_TYPE (LOG_TYPE_ID),
constraint FK_QX_LOG_QX_LOG_CLASS	foreign key (LOG_CLASS_ID)	references QX_LOG_CLASS (LOG_CLASS_ID)
)
//

create index IDX_QX_LOG_USER_TYPE on QX_LOG (USER_TYPE_ID)
//
create index IDX_QX_LOG_USER      on QX_LOG (USER_ID)
//
create index IDX_QX_LOG_LOG_TYPE  on QX_LOG (LOG_TYPE_ID)
//
create index IDX_QX_LOG_LOG_CLASS on QX_LOG (LOG_CLASS_ID)
//


create table QX_ERROR_CFG (
  ERROR_CFG_ID		INT		NOT NULL,
  TARGET_CLASS_MASK	VARCHAR(255)	NOT NULL,
  MESSAGE_HND_CLASS	VARCHAR(255)	NOT NULL,
  EMAILS		VARCHAR(1024),
  EMPLOYEE_ID		INT,
  WORKGROUP_ID		INT,
  `DISABLE`		DECIMAL(2)	           DEFAULT 0,
primary key (ERROR_CFG_ID)
)
//


create table QX_USER_LOG (
  USER_LOG_ID          INT		NOT NULL,
  USER_ID              INT		NOT NULL,
  USER_TYPE_ID         INT		NOT NULL,
  SESSION_ID           VARCHAR(255)	NOT NULL,
  FULLNAME             VARCHAR(255),
  HOST                 VARCHAR(255),
  REMOTE_ADDRESS       VARCHAR(15),
  LOGIN_TIME           DATETIME		NOT NULL,
  LOGOUT_TIME          DATETIME,
primary key (USER_LOG_ID),
constraint FK_QX_USER_LOG_QX_USER_TYPE foreign key (USER_TYPE_ID) references QX_USER_TYPE (USER_TYPE_ID)
)
//
create index IDX_QX_USER_LOG_USER      on QX_USER_LOG (USER_ID)
//
create index IDX_QX_USER_LOG_USER_TYPE on QX_USER_LOG (USER_TYPE_ID)
//


--
-- REPORTS
--

create table QX_REPORT (
  REPORT_ID		INT		NOT NULL,
  `NAME`		VARCHAR(255)	NOT NULL,
  OWNER_ID		INT,
  OWNER_TYPE 		INT,
  PUBLIC_REPORT		DECIMAL(2)	           DEFAULT 0,
  `TIMESTAMP`		DATETIME	NOT NULL,
  FOCUS_ID		VARCHAR(255)	NOT NULL,
  BODY			LONGTEXT	NOT NULL,
  STYLE			LONGTEXT,
primary key (REPORT_ID),
constraint FK_QX_REPORT_QX_USER_TYPE foreign key (OWNER_TYPE) references QX_USER_TYPE (USER_TYPE_ID)
)
//
create index IDX_QX_REPORT_OWNER         on QX_REPORT (OWNER_ID)
//
create index IDX_QX_REPORT_OWNER_TYPE    on QX_REPORT (OWNER_TYPE)
//
create index IDX_QX_REPORT_PUBLIC_REPORT on QX_REPORT (PUBLIC_REPORT)
//
create index IDX_QX_REPORT_FOCUS         on QX_REPORT (FOCUS_ID)
//


create table QX_REPORT_SCHED (
  REPORT_SCHED_ID	INT		NOT NULL,
  REPORT_ID		INT		NOT NULL,
  PERIOD		INT		NOT NULL,
  PERIOD_UNIT		INT		NOT NULL,
  ACTIVE		DECIMAL(2)	           DEFAULT 1,
  CREATED_BY		INT,
  CREATOR_TYPE		INT,
  NEXT_DATE		DATETIME,
  TO_ADDR		VARCHAR(255)	NOT NULL,
  CC_ADDR		VARCHAR(255),
  `SUBJECT`		VARCHAR(255)	NOT NULL,
primary key (REPORT_SCHED_ID),
constraint FK_QX_REPSCHED_QX_USER_TYPE foreign key (CREATOR_TYPE) references QX_USER_TYPE (USER_TYPE_ID)
)
//
create index IDX_REPORT_SCHED on QX_REPORT_SCHED (
  NEXT_DATE,
  ACTIVE
)
//
create index IDX_QX_REPORT_SCHED_REPORT       on QX_REPORT_SCHED (REPORT_ID)
//
create index IDX_QX_REPORT_SCHED_CREATED_BY   on QX_REPORT_SCHED (CREATED_BY)
//
create index IDX_QX_REPORT_SCHED_CREATOR_TYPE on QX_REPORT_SCHED (CREATOR_TYPE)
//


create view QX_DD_REPORT_SCHED_PERIODUNITS AS 
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as `NAME`
    from QX_FIELDINFO
    where (TABLE_NAME = 'qx_report_sched') and (COLUMN_NAME = 'period_unit')
//


--
-- CHARTS
--

create table QX_CHART (
    CHART_ID        INT             NOT NULL,
    `NAME`          VARCHAR(255)    NOT NULL,
    OWNER_ID        INT,
    PUBLIC_CHART    DECIMAL(2)      NOT NULL  DEFAULT 0,
    SYSTEM_CHART    DECIMAL(2)      NOT NULL  DEFAULT 0,
    CHART_POSITION  INT,
    `TIMESTAMP`     DATETIME        NOT NULL,
    BODY            LONGTEXT        NOT NULL,
	primary key (CHART_ID)
)
//

//
create index IDX_QX_CHART_OWNER         on QX_CHART (OWNER_ID)
//
create index IDX_QX_CHART_PUBLIC_CHART  on QX_CHART (PUBLIC_CHART)
//
create index IDX_QX_CHART_SYSTEM_CHART  on QX_CHART (SYSTEM_CHART)
//


--
-- HISTORY
--

create table QX_HIS_TABLE (
  HIS_TABLE_ID         INT		NOT NULL,
  `NAME`                 VARCHAR(255)	NOT NULL,
primary key (HIS_TABLE_ID)
)
//


create table QX_HIS_FIELD (
  HIS_FIELD_ID         INT		NOT NULL,
  HIS_TABLE_ID         INT		NOT NULL,
  `NAME`               VARCHAR(255)	NOT NULL,
primary key (HIS_FIELD_ID, HIS_TABLE_ID),
constraint FK_QX_HIS_FIELD_QX_HIS_TAB foreign key (HIS_TABLE_ID) references `QX_HIS_TABLE` (`HIS_TABLE_ID`)
)
//
create index IDX_QX_HIS_FIELD_TABLE on QX_HIS_FIELD (HIS_TABLE_ID)
//

create table QX_HIS_TBLFIELD (
  PKEY                 INT    NOT NULL,
  HIS_TABLE_ID         INT	NOT NULL,
  HIS_FIELD_ID         INT	NOT NULL,
primary key (PKEY),
constraint FK_QX_HIS_TBLFIELD_TABLE	foreign key (HIS_TABLE_ID)		 references QX_HIS_TABLE (HIS_TABLE_ID),
constraint FK_QX_HIS_TBLFIELD_FIELD	foreign key (HIS_FIELD_ID, HIS_TABLE_ID) references QX_HIS_FIELD (HIS_FIELD_ID, HIS_TABLE_ID)
)
//
create index IDX_QX_HIS_TBLFIELD_FIELD on QX_HIS_TBLFIELD (HIS_FIELD_ID)
//


create table QX_HISTORY (
  QX_HISTORYID int not null,
  QX_HISTORY text null,
  QX_OBJECT varchar(255) null,
  QX_OBJECTID int null,
  QX_DATEMODIFIED datetime null
)
//


--
-- TASKS
--

create table QX_TASK_DELAYUNIT (
  TASK_DELAYUNIT_ID	INT		NOT NULL,
  TASK_DELAYUNIT_NAME	VARCHAR(255)	NOT NULL,
primary key (TASK_DELAYUNIT_ID)
)
//


create table QX_TASK_STATUS (
  TASK_STATUS_ID	INT		NOT NULL,
  TASK_STATUS_NAME	VARCHAR(255)	NOT NULL,
primary key (TASK_STATUS_ID)
)
//


create table QX_SCRIPT (
  SCRIPT_ID            INT		NOT NULL,
  SCRIPT_NAME          VARCHAR(255)	NOT NULL,
  SCRIPT_DESCRIPTION   VARCHAR(1022),
  SCRIPT_CLASS_NAME    VARCHAR(255)	NOT NULL,
  CREATOR_ID           INT		NOT NULL,
  DATE_MODIFIED        DATETIME		NOT NULL,
primary key (SCRIPT_ID)
)
//
create unique index IDX_QX_SCRIPT_NAME       on QX_SCRIPT (SCRIPT_NAME)
//
create unique index IDX_QX_SCRIPT_CLASS_NAME on QX_SCRIPT (SCRIPT_CLASS_NAME)
//
create index IDX_QX_SCRIPT_CREATOR      on QX_SCRIPT (CREATOR_ID)
//


create table QX_TASK (
  TASK_ID              INT		NOT NULL,
  TASK_NAME            VARCHAR(255)	NOT NULL,
  TASK_STATUS_ID       INT		NOT NULL,
  SCRIPT_ID            INT		NOT NULL,
  CREATOR_ID           INT		NOT NULL,
  DATE_CREATED         DATETIME		NOT NULL,
  FIRST_START          DATETIME,
  NEXT_START           DATETIME,
  REPEAT_SIGN          DECIMAL(2)	           DEFAULT 0,
  AUTO_START 	       DECIMAL(2)	           DEFAULT 0,
  DELAY                INT,
  TASK_DELAYUNIT_ID    INT,
  LOG_PROCESS_ID       BIGINT,
primary key (TASK_ID),
constraint FK_QX_TASK_QX_TASK_STATUS	foreign key (TASK_STATUS_ID)	references QX_TASK_STATUS (TASK_STATUS_ID),
constraint FK_QX_TASK_QX_TDU		foreign key (TASK_DELAYUNIT_ID)	references QX_TASK_DELAYUNIT (TASK_DELAYUNIT_ID),
constraint FK_QX_TASK_QX_SCRIPT		foreign key (SCRIPT_ID)		references QX_SCRIPT (SCRIPT_ID)
)
//
create index IDX_QX_TASK_CREATOR      on QX_TASK (CREATOR_ID)
//
create index IDX_QX_TASK_STATUS       on QX_TASK (TASK_STATUS_ID)
//
create index IDX_QX_TASK_DELAYUNIT    on QX_TASK (TASK_DELAYUNIT_ID)
//
create index IDX_QX_TASK_SCRIPT       on QX_TASK (SCRIPT_ID)
//



--
-- LOCKING
--

create table QX_LOCK (
  PKEY                 INT		NOT NULL,
  TABLE_NAME           VARCHAR(255)	NOT NULL,
  RECORD_ID            VARCHAR(100)	NOT NULL,
  RECORD_ID2           VARCHAR(100)	NOT NULL,
  RECORD_ID3           VARCHAR(100)	NOT NULL,
  RECORD_ID4           VARCHAR(100)	NOT NULL,
  SESSION_ID           VARCHAR(255),
  FOCUS_ID             VARCHAR(255),
  FOCUS_INSTANCE       INT,
  USER_ID              INT,
  USER_TYPE_ID         INT,
  CREATED              DATETIME,
primary key (PKEY),
constraint FK_QX_LOCK_QX_USER_TYPE foreign key (USER_TYPE_ID) references QX_USER_TYPE (USER_TYPE_ID)
)
//

create unique index IDX_LOCK_TABLE_RECORDS on QX_LOCK (
  TABLE_NAME,
  RECORD_ID,
  RECORD_ID2,
  RECORD_ID3,
  RECORD_ID4
)
//

create index IDX_QX_LOCK_SESSION   on QX_LOCK (SESSION_ID)
//
create index IDX_QX_LOCK_FOCUS     on QX_LOCK (FOCUS_ID)
//
create index IDX_QX_LOCK_USER      on QX_LOCK (USER_ID)
//
create index IDX_QX_LOCK_USER_TYPE on QX_LOCK (USER_TYPE_ID)
//


--
-- SEARCH
--

create table QX_SEARCH (
  SEARCH_ID		INT		NOT NULL,
  `NAME`		VARCHAR(255)	NOT NULL,
  OWNER_ID		INT,
  `TIMESTAMP`		DATETIME	NOT NULL,
  FOCUS_ID		VARCHAR(255)	NOT NULL,
  TAB_ID		VARCHAR(255)	NOT NULL,
  FORM_ID		VARCHAR(255)	NOT NULL,
  BODY			LONGTEXT,
primary key (SEARCH_ID)
)
//

create index IDX_QX_SEARCH_OWNER on QX_SEARCH (OWNER_ID)
//
create index IDX_QX_SEARCH_FOCUS on QX_SEARCH (FOCUS_ID)
//
create index IDX_QX_SEARCH_TAB   on QX_SEARCH (TAB_ID)
//
create index IDX_QX_SEARCH_FORM  on QX_SEARCH (FORM_ID)
//


--
-- MISC
--
create table QX_STATES (
    PKEY              int             not null,
    NAME              varchar(255)    not null,
    STATE_ID          varchar(2)      not null,
primary key (PKEY)
)
//


create table QX_MAIL_PROVIDER (
    PKEY int not null,
    PROVIDER_ID varchar(50),
    PROVIDER_NAME varchar(50),
primary key (PKEY)
)
//


create table QX_ACCOUNT (
    PKEY int not null,
    OWNER_ID int null,
    WORKGROUP_ID int null,
    ACCOUNT_NAME varchar(255) not null,
    ACCOUNT_PROVIDER int not null,
    EMAIL_SERVER varchar(255) not null,
    EMAIL_SERVER_PORT varchar(50) null,
    EMAIL_SERVER_FOLDER varchar(50) null,
    EMAIL_PASSWORD varchar(255) not null,
    EMAIL_BOX_NAME varchar(255) null,
    LOGIN_NAME varchar(50) not null,
    LASTRECEIVE_DATE datetime,
    MAX_MAIL_SIZE int null,
    MAX_INBOX_SIZE int null,
    DELETE_EMAIL_FLAG int null,
    UNREAD_EMAIL_FLAG int null,
    UNREAD_OUTEMAIL_FLAG int null,
    ACTIVE_ACC int null,
    VALID_ACC int null,
    DELAY int null,
    NEXT_CHECK_DATE datetime,
    NOTES text,
    AUTOREPLY_ID int null,
    CREATED_BY int not null,
    CREATED_DATE datetime,
    REPLY_EMAIL varchar(255) not null,
    LASTUID varchar(512),
    LASTMESSAGE_DIGEST varchar(512),
primary key (PKEY),
constraint FK_ACCOUNT_QX_WORKGROUP foreign key (WORKGROUP_ID) references QX_WORKGROUP (PKEY),
constraint FK_ACCOUNT_QX_MAIL_PROVIDER foreign key (ACCOUNT_PROVIDER) references QX_MAIL_PROVIDER (PKEY),
constraint FK_ACCOUNT_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY),
constraint FK_ACCOUNT_PROVIDER_ACCOUNT_PROVIDER foreign key (ACCOUNT_PROVIDER) references QX_MAIL_PROVIDER (PKEY),
constraint FK_ACCOUNT_OWNER_QX_USERS foreign key (OWNER_ID) references QX_USERS (PKEY),
constraint FK_ACCOUNT_AUTOREPLY_QX_AUTO_REPLY foreign key (AUTOREPLY_ID) references QX_AUTO_REPLY (PKEY)
)
//

create table QX_EMAIL_ROUTING_RULES (
    PKEY int not null,
    OWNER_ID int null,
    WORKGROUP_ID int null,
    RULE_NAME varchar(50) not null,
    KEY_WORD varchar(255) not null,
    FROM_ADDRESS int null,
    TO_ADDRESS int null,
    SUBJECT int null,
    BODY int null,
    AUTOREPLY_ID int null,
    CREATED_BY int not null,
    CREATED_DATE datetime,
primary key (PKEY),
constraint FK_ROUTING_QX_WORKGROUP foreign key (WORKGROUP_ID) references QX_WORKGROUP (PKEY),
constraint FK_ROUTING_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY),
constraint FK_ROUTING_OWNER_QX_USERS foreign key (OWNER_ID) references QX_USERS (PKEY)
)
//


create table QX_EMAIL_ROUTING (
    PKEY int not null,
    ACCOUNT_ID int not null,
    ROUTING_RULE int not null,
primary key (PKEY),
constraint FK_QX_EMAIL_ROUTING_QX_ACCOUNT foreign key (ACCOUNT_ID) references QX_ACCOUNT(PKEY),
constraint FK_QX_EMAIL_ROUTING_QX_EMAIL_ROUTING_RULES foreign key (ROUTING_RULE) references QX_EMAIL_ROUTING_RULES(PKEY)
)
//


create table QX_EMAIL_FILTER (
    PKEY int not null,
    NAME varchar(50) not null,
    FROM_ADDRESS int null,
    TO_ADDRESS int null,
    SUBJECT int null,
    BODY int null,
    WORD varchar(255) not null,
    CREATED_BY int not null,
    CREATED_DATE datetime,

primary key (PKEY),
constraint FK_EMAIL_FILTER_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY)
)
//


create table QX_FILTER_ACCOUNT (
    PKEY int not null,
    ACCOUNT_ID int not null,
    EMAIL_FILTER int not null,
    constraint PK_QX_FILTER_ACCOUNT primary key (PKEY),
    constraint FK_QX_FILTER_ACCOUNT_QX_ACCOUNT foreign key (ACCOUNT_ID) REFERENCES QX_ACCOUNT(PKEY),
    constraint FK_QX_FILTER_ACCOUNT_QX_EMAIL_FILTER foreign key (EMAIL_FILTER) REFERENCES QX_EMAIL_FILTER(PKEY)
)
//


create table QX_INBOX (
    PKEY int not null,
    MESSAGE_TYPE int not null default 0, -- 0 is email message, 1 - alert
    MESSAGE_SENDER int null,
    MESSAGE_SENDER_EMAIL varchar(1024),
    RECIPIENT_ID int null,
    WORKGROUP_ID int null,
    EMAIL_TO varchar(1024) null,
    EMAIL_CC varchar(1024) null,
    SUBJECT varchar(1024) null,
    MESSAGE text,
    SENT_DATE datetime,
    SERVER_RECEIVED_DATE datetime,
    RECEIVED_DATE datetime,
    OBJECT_TYPE int null,
    OBJECT_ID int null,
    REPLIED_DATE datetime,
    PROCESS_ID int null,
    ACCOUNT_ID int null,
    DIGEST_SRC varchar(512),
    DELETE_FLAG int not null default 0, -- 0 existed, 1 - deleted
    READ_FLAG bit default 0,
    primary key (PKEY),
    constraint FK_QX_INBOX_SENDER_QX_USERS foreign key (MESSAGE_SENDER) references QX_USERS(PKEY),
    constraint FK_QX_INBOX_RECIPIENT_QX_USERS foreign key (RECIPIENT_ID) references QX_USERS(PKEY),
    constraint FK_QX_INBOX_WORKGROUP_QX_WORKGROUP foreign key (WORKGROUP_ID) references QX_WORKGROUP(PKEY),
    constraint FK_QX_INBOX_OBJECT_TYPE_QX_VIEW_OBJECT_TYPES foreign key (OBJECT_TYPE) references QX_VIEW_OBJECT_TYPES(PKEY),
    constraint FK_QX_INBOX_ACCOUNT_ID_QX_ACCOUNT foreign key (ACCOUNT_ID) references QX_ACCOUNT(PKEY)
)
//

create table QX_ATTACHMENT_OBJECTS (
  PKEY             INT NOT NULL,
  ATTACHMENT_ID    INT,
  OBJECT_ID        INT,
  OBJECT_TYPE      INT,
constraint PK_QX_ATTACHMENT_OBJECTS primary key (PKEY),
constraint FK_QX_ATTACHMENT_OBJECTS_QX_ATTACHMENT foreign key (ATTACHMENT_ID) references QX_ATTACHMENT (ATTACHMENT_ID)
)
//

create view QX_DD_OBJECT_TYPE as
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as NAME
    from QX_FIELDINFO
    where (TABLE_NAME = 'qx_inbox') and (COLUMN_NAME = 'object_type')
//

create view QX_USER_ROLES as
    select distinct r.ROLE_ID, u.USER_ID, r.DEFAULT_FOCUS_ID
        from QX_ROLE r, QX_ROLE_TO_USERS u
        where r.ROLE_ID = u.ROLE_ID
union all
    select r.ROLE_ID, g.EMPLOYEEGROUP, r.DEFAULT_FOCUS_ID
        from QX_ROLE r, QX_WORKGROUP_TO_ROLE wr, QX_GROUPMEMBER g
        where r.ROLE_ID = wr.ROLE_ID
        and wr.WORKGROUP_ID = g.WORKGROUPGROUP
//

create table QX_EMAIL_SERVICE_LOG (
	PKEY int NOT NULL ,
	MESSAGE_ID int NULL ,
	MESSAGE_TYPE int NULL ,
	E_TO varchar(255) NULL ,
	E_FROM varchar(255) NULL ,
	E_SUBJECT varchar(1022) NULL ,
	MESSAGE_LEVEL int NULL ,
	CREATED datetime NULL ,
	OWNER_ID int NULL ,
	WORKGROUP_ID int NULL ,
	OBJECT_TYPE int null,
	OBJECT_ID int null,
	FILTER_ID int NULL ,
	ROUTING_ID int NULL ,
	DESCRIPTION varchar(1022) NULL ,
	ACCOUNT_ID int NULL ,
	constraint PK_QX_EMAIL_SERVICE_LOG primary key (PKEY)
)
//


create table QX_AUTO_REPLY (
    PKEY int not null,
    NAME varchar(255) not null,
    DESCRIPTION varchar(255) null,
    SUCCESSFUL_SUBJECT varchar(255) not null,
    SUCCESSFUL_BODY text null,
    UPDATE_SUBJECT varchar(255) not null,
    UPDATE_BODY text null,
    FAIL_SUBJECT varchar(255) not null,
    FAIL_BODY text null,
    CREATED_BY int not null,
    CREATED_DATE datetime,
    REPLY_EMAIL varchar(255) not null,

    constraint PK_QX_AUTO_REPLY primary key (PKEY),
    constraint FK_AUTO_REPLY_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY)
)

//

create table QX_HELPLINK (
  TAB_ID varchar(255),
  LINK varchar(255),
  constraint PK_QX_HELPLINK primary key (TAB_ID)
)
//

create table QX_PORTAL_CONFIG (
  NAME varchar(255) not null,
  CSS_FILE varchar(1024) null,
  SHOW_FOCUS_MENU bit default 1,
  SHOW_SUBFOCUS_MENU bit default 1,
  constraint PK_QX_PORTAL_CONFIG primary key(NAME)
)
//

create table QX_PORTAL_TOOLBAR (
  PORTAL_CONFIG_NAME varchar(255) not null,
  ORDER_ID int not null,
  CONTROL_NAME varchar(255) not null,
  constraint PK_QX_PORTAL_TOOLBAR primary key(PORTAL_CONFIG_NAME, ORDER_ID),
  constraint FK_QX_PORTAL_TOOLBAR_PORTAL_CONFIG foreign key (PORTAL_CONFIG_NAME) references QX_PORTAL_CONFIG(NAME)
)
//

create table QX_FORM_FILTER (
  FORM_ID              VARCHAR(250) NOT NULL,
  FIELD_ID             VARCHAR(250) NOT NULL,
  BY_FORM_ID           VARCHAR(250) NOT NULL,
  BY_FIELD_ID          VARCHAR(250) NOT NULL,
  constraint PK_QX_FORM_FILTER primary key (FORM_ID, FIELD_ID, BY_FORM_ID, BY_FIELD_ID)
)
//