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,
constraint PK_QX_EXTERNALSET primary key (EXTERNALFORM_ID, FORM_ID)
)
go

create index IDX_QX_EXTERNALSET_FORM on dbo.QX_EXTERNALSET (FORM_ID)
go



create table dbo.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 (ENTITY_ID, DATASET_ID)
)
go

create index IDX_QX_LINKEDDATASET_FORM on dbo.QX_LINKEDDATASET (FORM_ID)
go


--
-- Localization
--

create table dbo.QX_LOCALIZATION_TYPE (
  LOCALIZATION_TYPE_ID   INT             NOT NULL,
  NAME                   VARCHAR(255)    NOT NULL,
constraint PK_QX_LOCALIZATION_TYPE primary key (LOCALIZATION_TYPE_ID)
)
go

create table dbo.QX_LOCALIZED_OBJECT_TYPE (
  OBJECT_TYPE_ID  INT               NOT NULL,
  NAME            VARCHAR(255)      NOT NULL,
constraint PK_QX_LOCALIZED_OBJECT_TYPE primary key (OBJECT_TYPE_ID)
)
go

create table dbo.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,
constraint PK_QX_LOCALIZATION 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)
)
go

create index IDX_QX_LOCALIZATION_LANGUAGE on dbo.QX_LOCALIZATION (LANGUAGE_ID)
-- [MVT] WARNING! Don't try to create index on the
--                LOCALIZATION_TYPE_ID column - appication hangs
--create index IDX_QX_LOCALIZATION_TYPE on dbo.QX_LOCALIZATION (LOCALIZATION_TYPE_ID)
create index IDX_QX_LOCALIZATION_OBJECT on dbo.QX_LOCALIZATION (OBJECT_TYPE_ID)
go


--
-- Context menu
--

create table dbo.QX_CONTEXTMENU (
  CONTEXTMENU_ID    VARCHAR(255)    NOT NULL,
constraint PK_QX_CONTETXMENU primary key (CONTEXTMENU_ID)
)
go

create table dbo.QX_MENUITEM (
  MENUITEM_ID        VARCHAR(255)    NOT NULL,
  CONTEXTMENU_ID    VARCHAR(255)    NOT NULL,
  MENUITEM_ORDER    INT,
constraint PK_QX_MENUITEM primary key (MENUITEM_ID)
)
go

create index IDX_QX_MENUITEM_CONTEXTMENU on dbo.QX_MENUITEM (CONTEXTMENU_ID)
create index IDX_QX_MENUITEM_ORDER on dbo.QX_MENUITEM (MENUITEM_ORDER)
go

create table dbo.QX_CONTEXTMENU_FORM (
  FORM_ID            VARCHAR(255)    NOT NULL,
  CONTEXTMENU_ID    VARCHAR(255)    NOT NULL,
constraint PK_QX_CONTETXMENU_FORM primary key (FORM_ID, CONTEXTMENU_ID)
)
go

--
-- ALERTS
--

create table dbo.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                 NUMERIC(2),
  TO_ALL               NUMERIC(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             NUMERIC(2)    NOT NULL,
  DATEPOSTED           DATETIME        NOT NULL,
  CREATOR_ID           INT        NOT NULL,
  CREATOR_TYPE         INT        NOT NULL,
constraint PK_QX_ALERT 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)
)
go

create index IDX_QX_ALERT_SENDER         on dbo.QX_ALERT (SENDER_ID)
create index IDX_QX_ALERT_SENDER_TYPE    on dbo.QX_ALERT (SENDER_TYPE)
create index IDX_QX_ALERT_RECIPIENT      on dbo.QX_ALERT (RECIPIENT_ID)
create index IDX_QX_ALERT_RECIPIENT_TYPE on dbo.QX_ALERT (RECIPIENT_TYPE)
create index IDX_QX_ALERT_CREATOR        on dbo.QX_ALERT (CREATOR_ID)
create index IDX_QX_ALERT_CREATOR_TYPE   on dbo.QX_ALERT (CREATOR_TYPE)
create index IDX_QX_ALERT_WORKGROUP      on dbo.QX_ALERT (WORKGROUP_ID)
create index IDX_QX_ALERT_FOCUS          on dbo.QX_ALERT (FOCUS_ID)
create index IDX_QX_ALERT_TAB            on dbo.QX_ALERT (TAB_ID)
create index IDX_QX_ALERT_FORM           on dbo.QX_ALERT (FORM_ID)
go



create table dbo.QX_ALERT_BLOCK (
  ALERT_BLOCK_ID       INT        NOT NULL,
  ALERT_ID             INT        NOT NULL,
  RECIPIENT_ID         INT        NOT NULL,
  RECIPIENT_TYPE       INT        NOT NULL,
constraint PK_QX_ALERT_BLOCK 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)
)
go

create unique index IDX_ALERT_BLOCK on dbo.QX_ALERT_BLOCK (
  ALERT_ID,
  RECIPIENT_ID,
  RECIPIENT_TYPE
)
go

create index IDX_QX_ALERT_BLOCK_RECIPIENT      on dbo.QX_ALERT_BLOCK (RECIPIENT_ID)
create index IDX_QX_ALERT_BLOCK_RECIPIENT_TYPE on dbo.QX_ALERT_BLOCK (RECIPIENT_TYPE)
go



create view dbo.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')
go



--
-- ATTACHMENTS
--

create table dbo.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            IMAGE,
  USER_ID        INT,
constraint PK_QX_ATTACHMENT primary key (ATTACHMENT_ID),
)
go

create index IDX_QX_ATTACHMENT_USER      on dbo.QX_ATTACHMENT (USER_ID)
go



create table dbo.QX_TEMP_ATTACH (
  TEMP_ATTACH_ID    INT        NOT NULL,
  PROCESS_ID        BIGINT,
  FILENAME        VARCHAR(255),
  CREATED        DATETIME    NOT NULL,
  DATA            IMAGE,
  FILETYPE        VARCHAR(50)               DEFAULT 'application/octet-stream',
  FILEEXT        VARCHAR(10),
  USER_TYPE_ID        INT,
  USER_ID        INT,
constraint PK_QX_TEMP_ATTACH 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)
)
go

create index IDX_QX_TEMP_ATTACH_USER_TYPE on dbo.QX_TEMP_ATTACH (USER_TYPE_ID)
create index IDX_QX_TEMP_ATTACH_USER      on dbo.QX_TEMP_ATTACH (USER_ID)
go

create table dbo.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)
)
go


--
-- IMAGES
--

create table dbo.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),
)
go

create index IDX_QX_IMAGE_MODIFIER      on dbo.QX_IMAGE (MODIFIER_ID)
create index IDX_QX_IMAGE_CREATOR       on dbo.QX_IMAGE (CREATOR_ID)
go



create table dbo.QX_IMAGE_FOLDER (
  FOLDER_ID            INT        NOT NULL,
  FOLDER               VARCHAR(255)    NOT NULL,
constraint PK_QX_IMAGE_FOLDER primary key (FOLDER_ID)
)
go



create table dbo.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,
constraint PK_QX_IMAGE_USAGE primary key (IMAGE_USAGE_ID),
constraint FK_QX_IMAGE_USAGE_QX_IMAGE foreign key (IMAGE_ID) references QX_IMAGE (IMAGE_ID)
)
go

create index IDX_QX_IMAGE_USAGE_IMAGE on dbo.QX_IMAGE_USAGE (IMAGE_ID)
go



--
-- LOGGING
--

create table dbo.QX_LOG_TYPE (
  LOG_TYPE_ID          INT        NOT NULL,
  NAME                 VARCHAR(50)    NOT NULL,
constraint PK_QX_LOG_TYPE primary key (LOG_TYPE_ID)
)
go



create table dbo.QX_LOG_CLASS (
  LOG_CLASS_ID         INT        NOT NULL,
  NAME                 VARCHAR(50)    NOT NULL,
  TABLE_NAME           VARCHAR(50)    NOT NULL,
constraint PK_QX_LOG_CLASS primary key (LOG_CLASS_ID)
)
go



create table dbo.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,
constraint PK_QX_LOG 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)
)
go

create index IDX_QX_LOG_USER_TYPE on dbo.QX_LOG (USER_TYPE_ID)
create index IDX_QX_LOG_USER      on dbo.QX_LOG (USER_ID)
create index IDX_QX_LOG_LOG_TYPE  on dbo.QX_LOG (LOG_TYPE_ID)
create index IDX_QX_LOG_LOG_CLASS on dbo.QX_LOG (LOG_CLASS_ID)
go



create table dbo.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        NUMERIC(2)               DEFAULT 0,
constraint PK_QX_ERROR_CFG primary key (ERROR_CFG_ID)
)
go



create table dbo.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,
constraint PK_QX_USER_LOG 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)
)
go

create index IDX_QX_USER_LOG_USER      on dbo.QX_USER_LOG (USER_ID)
create index IDX_QX_USER_LOG_USER_TYPE on dbo.QX_USER_LOG (USER_TYPE_ID)
go

--
-- REPORTS
--

create table dbo.QX_REPORT (
    REPORT_ID        INT        NOT NULL,
    NAME            VARCHAR(255)    NOT NULL,
    OWNER_ID        INT,
    PUBLIC_REPORT        NUMERIC(2)               DEFAULT 0,
    TIMESTAMP        DATETIME    NOT NULL,
    BODY            TEXT        NOT NULL,
    constraint PK_QX_REPORT primary key (REPORT_ID)
)
go

create index IDX_QX_REPORT_OWNER         on dbo.QX_REPORT (OWNER_ID)
create index IDX_QX_REPORT_PUBLIC_REPORT on dbo.QX_REPORT (PUBLIC_REPORT)
go

create table dbo.QX_REPORT_SCHED (
  REPORT_SCHED_ID    INT        NOT NULL,
  REPORT_ID        INT        NOT NULL,
  PERIOD        INT        NOT NULL,
  PERIOD_UNIT        INT        NOT NULL,
  ACTIVE        NUMERIC(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,
constraint PK_QX_REPORT_SCHED primary key (REPORT_SCHED_ID),
constraint FK_QX_REPSCHED_QX_USER_TYPE foreign key (CREATOR_TYPE) references QX_USER_TYPE (USER_TYPE_ID)
)
go

create index IDX_REPORT_SCHED on dbo.QX_REPORT_SCHED (
  NEXT_DATE,
  ACTIVE
)
go

create index IDX_QX_REPORT_SCHED_REPORT       on dbo.QX_REPORT_SCHED (REPORT_ID)
create index IDX_QX_REPORT_SCHED_CREATED_BY   on dbo.QX_REPORT_SCHED (CREATED_BY)
create index IDX_QX_REPORT_SCHED_CREATOR_TYPE on dbo.QX_REPORT_SCHED (CREATOR_TYPE)
go



create view dbo.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')
go


--