1.framework-objects_v068.sql

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

-- CHARTS
--

create table dbo.QX_CHART (
    CHART_ID        INT             NOT NULL,
    NAME            VARCHAR(255)    NOT NULL,
    OWNER_ID        INT,
    PUBLIC_CHART    INT             NOT NULL  DEFAULT 0,
    SYSTEM_CHART    INT             NOT NULL  DEFAULT 0,
    TIMESTAMP       DATETIME        NOT NULL  DEFAULT getdate(),
    BODY            VARCHAR(2048)   NOT NULL,
    constraint PK_QX_CHART primary key (CHART_ID)
)
go

create index IDX_QX_CHART_OWNER         on dbo.QX_CHART (OWNER_ID)
create index IDX_QX_CHART_PUBLIC_CHART on dbo.QX_CHART (PUBLIC_CHART)
create index IDX_QX_CHART_SYSTEM_CHART on dbo.QX_CHART (SYSTEM_CHART)
go


--
-- HISTORY
--

create table dbo.QX_HIS_TABLE (
  HIS_TABLE_ID         INT        NOT NULL,
  NAME                 VARCHAR(255)    NOT NULL,
constraint PK_QX_HIS_TABLE primary key (HIS_TABLE_ID)
)
go


create table dbo.QX_HIS_FIELD (
  HIS_FIELD_ID         INT        NOT NULL,
  HIS_TABLE_ID         INT        NOT NULL,
  NAME                 VARCHAR(255)    NOT NULL,
constraint PK_QX_HIS_FIELD 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
)
go

create index IDX_QX_HIS_FIELD_TABLE on dbo.QX_HIS_FIELD (HIS_TABLE_ID)
go


create table dbo.QX_HIS_TBLFIELD (
  HIS_TABLE_ID         INT    NOT NULL,
  HIS_FIELD_ID         INT    NOT NULL,
constraint PK_QX_HIS_TBLFIELD primary key (HIS_TABLE_ID, HIS_FIELD_ID),
constraint FK_QX_HIS_TBLFIELD_TABLE    foreign key (HIS_TABLE_ID)            references QX_HIS_TABLE,
constraint FK_QX_HIS_TBLFIELD_FIELD    foreign key (HIS_FIELD_ID, HIS_TABLE_ID)    references QX_HIS_FIELD
)
go

create index IDX_QX_HIS_TBLFIELD_FIELD on dbo.QX_HIS_TBLFIELD (HIS_FIELD_ID)
go


create table dbo.QX_HISTORY (
  qx_historyid int not null,
  qx_history text null,
  qx_object varchar(255) null,
  qx_objectid int null,
  qx_datemodified datetime null
)
go


--
-- TASKS
--

create table dbo.QX_TASK_DELAYUNIT (
  TASK_DELAYUNIT_ID    INT        NOT NULL,
  TASK_DELAYUNIT_NAME    VARCHAR(255)    NOT NULL,
constraint PK_QX_TASK_DELAYUNIT primary key (TASK_DELAYUNIT_ID)
)
go



create table dbo.QX_TASK_STATUS (
  TASK_STATUS_ID    INT        NOT NULL,
  TASK_STATUS_NAME    VARCHAR(255)    NOT NULL,
constraint PK_QX_TASK_STATUS primary key (TASK_STATUS_ID)
)
go


create table dbo.QX_SCRIPT (
  SCRIPT_ID            INT           NOT NULL,
  SCRIPT_NAME          VARCHAR(255)  NOT NULL,
  SCRIPT_DESCRIPTION   VARCHAR(1022) NULL,
  SCRIPT_CLASS_NAME    VARCHAR(255)  NOT NULL,
  CREATOR_ID           INT           NOT NULL,
  DATE_MODIFIED        DATETIME      NOT NULL,
constraint PK_QX_SCRIPT primary key (SCRIPT_ID)
)
go

create unique index IDX_QX_SCRIPT_NAME       on dbo.QX_SCRIPT (SCRIPT_NAME)
create unique index IDX_QX_SCRIPT_CLASS_NAME on dbo.QX_SCRIPT (SCRIPT_CLASS_NAME)
go

create index IDX_QX_SCRIPT_CREATOR      on dbo.QX_SCRIPT (CREATOR_ID)
go



create table dbo.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         NUMERIC(2)   DEFAULT 0,
  AUTO_START          NUMERIC(2)   DEFAULT 0,
  DELAY               INT,
  TASK_DELAYUNIT_ID   INT,
  LOG_PROCESS_ID      BIGINT,
constraint PK_QX_TASK 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)
)
go

create index IDX_QX_TASK_CREATOR      on dbo.QX_TASK (CREATOR_ID)
create index IDX_QX_TASK_STATUS       on dbo.QX_TASK (TASK_STATUS_ID)
create index IDX_QX_TASK_DELAYUNIT    on dbo.QX_TASK (TASK_DELAYUNIT_ID)
create index IDX_QX_TASK_SCRIPT       on dbo.QX_TASK (SCRIPT_ID)
go



--
-- LOCKING
--

create table dbo.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,
constraint PK_QX_LOCK primary key (PKEY),
constraint FK_QX_LOCK_QX_USER_TYPE foreign key (USER_TYPE_ID) references QX_USER_TYPE (USER_TYPE_ID)
)
go

create unique index IDX_LOCK_TABLE_RECORDS on dbo.QX_LOCK (
  TABLE_NAME,
  RECORD_ID,
  RECORD_ID2,
  RECORD_ID3,
  RECORD_ID4
)
go

create index IDX_QX_LOCK_SESSION   on dbo.QX_LOCK (SESSION_ID)
create index IDX_QX_LOCK_FOCUS     on dbo.QX_LOCK (FOCUS_ID)
create index IDX_QX_LOCK_USER      on dbo.QX_LOCK (USER_ID)
create index IDX_QX_LOCK_USER_TYPE on dbo.QX_LOCK (USER_TYPE_ID)
go

--
-- SEARCH
--

create table dbo.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            TEXT        NULL,
constraint PK_QX_SEARCH primary key (SEARCH_ID)
)
go

create index IDX_QX_SEARCH_OWNER on dbo.QX_SEARCH (OWNER_ID)
create index IDX_QX_SEARCH_FOCUS on dbo.QX_SEARCH (FOCUS_ID)
create index IDX_QX_SEARCH_TAB   on dbo.QX_SEARCH (TAB_ID)
create index IDX_QX_SEARCH_FORM  on dbo.QX_SEARCH (FORM_ID)
go

--
-- MISC
--
create table dbo.QX_STATES (
    pkey              int             not null,
    name              varchar(255)    not null,
    state_id          varchar(2)      not null,
    constraint PK_QX_STATES primary key (pkey)
)
go

create table dbo.QX_MAIL_PROVIDER (
    PKEY int not null,
    PROVIDER_ID varchar(50),
    PROVIDER_NAME varchar(50),

    constraint PK_QX_MAIL_PROVIDER primary key (PKEY),
)
go

create table dbo.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),
)ON [PRIMARY]
go

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

    constraint PK_QX_ACCOUNT 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 dbo.QX_AUTO_REPLY (PKEY)
)ON [PRIMARY]
go

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

    constraint PK_QX_EMAIL_ROUTING_RULES 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)
)
go

create table dbo.QX_EMAIL_ROUTING (
    ACCOUNT_ID int not null,
    ROUTING_RULE int not null,
    constraint PK_QX_EMAIL_ROUTING primary key (ACCOUNT_ID, ROUTING_RULE),
    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)
)
go

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

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

create table dbo.QX_FILTER_ACCOUNT (
    ACCOUNT_ID int not null,
    EMAIL_FILTER int not null,

    constraint PK_QX_FILTER_ACCOUNT primary key (ACCOUNT_ID, EMAIL_FILTER),
    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)
)
go

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

    constraint PK_QX_INBOX 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),
)
go

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

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
go

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

create table dbo.QX_HELPLINK (
  TAB_ID varchar(255),
  LINK varchar(255),
  constraint PK_QX_HELPLINK primary key (TAB_ID)
)
go