1.framework-objects_v068.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

  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,
primary key (ENTITY_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,