1.framework-objects_v068.sql

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

-- ------------ --
-- Domain model --
-- ------------ --

--
-- SYSTEM tables
--
create table dbo.QX_KEYS (
  TABLENAME        VARCHAR(240)    NOT NULL,
  KEYVALUE        INT        NOT NULL,
constraint PK_QX_KEYS primary key (TABLENAME)
)
go

create table dbo.QX_FIELDINFO (
  TABLE_NAME        VARCHAR(18)    NOT NULL,
  COLUMN_NAME        VARCHAR(18)    NOT NULL,
  COLUMN_TYPE        NUMERIC(2)    NOT NULL,
  ENUM_VALUE        NUMERIC(2),
  ENUM_LABEL        VARCHAR(128)
)
go

create unique index IDX_QX_FIELDINFO on dbo.QX_FIELDINFO (
  TABLE_NAME,
  COLUMN_NAME,
  ENUM_VALUE
)
go

create table dbo.QX_SYS_PROP (
  PKEY            INT    NOT NULL,
  PROP_ID        VARCHAR(255)    NOT NULL,
  PROP_VAL        VARCHAR(255),
  PROP_TEXT        TEXT,
  PROP_DATA        IMAGE,
  NEED_RESTART        NUMERIC                   DEFAULT 0,
  DESCRIPTION        VARCHAR(255),
constraint PK_QX_SYS_PROP primary key (PKEY)
)
go

create table dbo.QX_LANGUAGE (
  PKEY          INT          NOT NULL,
  LANGUAGE_ID   CHAR(2)      NOT NULL,
  NAME          VARCHAR(255) NOT NULL,
constraint PK_QX_LANGUAGE primary key (LANGUAGE_ID)
)
go

create table dbo.QX_TIMEZONE (
  PKEY           INT          NOT NULL,
  TIMEZONE_ID    VARCHAR(255) NOT NULL,
  NAME           VARCHAR(255) NOT NULL,
  OFFSET         INT          NOT NULL,
constraint PK_QX_TIMEZONE primary key (PKEY)
)
go

create table dbo.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),
constraint PK_QX_COUNTRY primary key (PKEY),
--constraint FK_QX_COUNTRY_QX_LANGUAGE    foreign key (DEF_LANG_ID)    references QX_LANGUAGE (LANGUAGE_ID),
--constraint FK_QX_COUNTTRY_QX_TIMEZONE    foreign key (DEF_TIMEZONE_ID)    references QX_TIMEZONE (TIMEZONE_ID)
)
go

create index IDX_QX_COUNTRY_LANGUAGE on dbo.QX_COUNTRY (DEF_LANG_ID)
create index IDX_QX_COUNTRY_TIMEZONE on dbo.QX_COUNTRY (DEF_TIMEZONE_ID)
go

CREATE TABLE dbo.QX_TIME_PATTERN (
  PKEY             INT          NOT NULL,
  TIME_PATTERN_ID  VARCHAR(255) NOT NULL
constraint PK_QX_TIME_PATTERN primary key (PKEY),
)
GO

CREATE TABLE dbo.QX_DATE_PATTERN (
  PKEY             INT          NOT NULL,
  DATE_PATTERN_ID  VARCHAR(255) NOT NULL
constraint PK_QX_DATE_PATTERN primary key (PKEY),
)
GO

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

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

--
-- ENTITY/FOCUS
--

create table dbo.QX_ENTITY (
  ENTITY_ID            VARCHAR(255)    NOT NULL,
  ENTITYDATA           IMAGE,
constraint PK_QX_ENTITY primary key (ENTITY_ID)
)
go

--
-- SECURITY
--

create table dbo.QX_VIEW_OBJECT_TYPES (
    pkey                int             not null,
    type_description    varchar(255)    not null,

    constraint PK_QX_VIEW_OBJECT_TYPES primary key (pkey)
)
go

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

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

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

--    constraint PK_QX_FORM_SETTINGS primary key (view_object_id),
    constraint FK_QX_FORM_SETTINGS_TO_VIEW_OBJECTS foreign key (view_object_id) references QX_VIEW_OBJECTS(pkey)
)
go

create table dbo.QX_ROLE (
    role_id           int             not null,
    default_focus_id  int             null,
    name              varchar(255)    not null,
    description       varchar(255)    null,

    constraint PK_QX_ROLE primary key (role_id),
    constraint FK_QX_ROLE_TO_FOCUS foreign key (default_focus_id) references QX_VIEW_OBJECTS(pkey)
)
go

create table dbo.QX_ACCESSLEVEL (
    pkey    int             not null,
    name    varchar(255)    not null,

    constraint PK_QX_ACCESSLEVEL primary key (pkey)
)
go

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

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

create table dbo.QX_USER_TYPE (
    USER_TYPE_ID         INT            NOT NULL,
    NAME                 VARCHAR(255)   NOT NULL,

    constraint PK_QX_USER_TYPE primary key (USER_TYPE_ID)
)
go

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

    constraint PK_QX_USERS primary key (PKEY),
    constraint FK_QX_USERS_TO_USERTYPE foreign key (USER_TYPE) references qx_user_type(USER_TYPE_ID)
)
go

create unique index IDX_USERS_LOGINNAME on dbo.QX_USERS (LOGINNAME)
go


create table dbo.QX_WORKGROUP (
    pkey                int             not null,
    name                varchar(40)     not null,
    description         varchar(80),
    notificationaddr    varchar(40),
    notifymethod        numeric(2),

    constraint PK_QX_WORKGROUP primary key (PKEY)
)
go

create table dbo.QX_GROUPMEMBER (
  PKEY              INT     NOT NULL,
  EMPLOYEEGROUP     INT     NOT NULL,
  WORKGROUPGROUP    INT     NOT NULL,
  NOTIFYMETHOD      NUMERIC(2),
  TIER              INT     DEFAULT 0,

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

create index IDX_QX_GROUPMEMBER_EMPLOYEE  on dbo.QX_GROUPMEMBER (EMPLOYEEGROUP)
create index IDX_QX_GROUPMEMBER_WORKGROUP on dbo.QX_GROUPMEMBER (WORKGROUPGROUP)
go

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

create table dbo.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_FIRST_WORKGROUP_FK foreign key (workgroup_id) references QX_WORKGROUP(pkey),
    constraint FK_QX_WORKGROUP_TO_ROLE_TO_SECOND_ROLE_FK foreign key (role_id) references QX_ROLE(role_id)
)
go

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

--
-- USERS, WORKGROUPS, ROLES
--
create view dbo.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')
go

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

create table dbo.QX_USER_PROP (
  PROP_ID              VARCHAR(512)    NOT NULL,
  USER_ID              INT        NOT NULL,
  USER_TYPE_ID         INT        NOT NULL,
  PROP_VAL             VARCHAR(2048),
constraint PK_QX_USER_PROP 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)
)
go

create index IDX_QX_USER_PROP_USER      on dbo.QX_USER_PROP (USER_ID)
create index IDX_QX_USER_PROP_USER_TYPE on dbo.QX_USER_PROP (USER_TYPE_ID)
go

create table dbo.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,
constraint PK_USER_SETTINGS primary key (PKEY)
)
go

create unique index IDX_USER_SETTINGS_USER_ID on dbo.QX_USER_SETTINGS (USER_ID)
go


--
-- View
--
create table dbo.QX_HEADER (
  FORM_ID              VARCHAR(255) NOT NULL,
  HEADER_ORDER         INT NOT NULL,
  CLIENT_WIDTH         INT,
constraint PK_QX_HEADER primary key (FORM_ID, HEADER_ORDER)
)

create table dbo.QX_ROW (
  FORM_ID              VARCHAR(255) NOT NULL,
  ROW_ORDER            INT NOT NULL,
constraint PK_QX_ROW primary key (FORM_ID, ROW_ORDER)
)

create table dbo.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,
constraint PK_QX_COL primary key (ROW_ORDER, FORM_ID, COL_ORDER)
)  

create table dbo.QX_BUTTON (
  FORM_ID              VARCHAR(255) NOT NULL,
  BUTTON_ID            VARCHAR(255) NOT NULL,
  BUTTON_ORDER         INT,
  PERMISSION           INT,
constraint PK_QX_BUTTON primary key (FORM_ID, BUTTON_ID)
)

create table dbo.QX_HTML_ELEMENT (
  FORM_ID              VARCHAR(255) NOT NULL,
  HTML_ELEMENT_ID      VARCHAR(255) NOT NULL,
  HTML_ELEMENT_ORDER   INT,
constraint PK_QX_HTML_ELEMENT primary key (FORM_ID, HTML_ELEMENT_ID)
)

create table dbo.QX_HIDDENCONTROL (
  FORM_ID              VARCHAR(255) NOT NULL,
  FIELD_ID            VARCHAR(255) NOT NULL,
constraint PK_QX_HIDDENCONTROL primary key (FORM_ID, FIELD_ID)
)

create table dbo.QX_LINK (
  FORM_ID              VARCHAR(255) NOT NULL,
  FIELD_ID             VARCHAR(255) NOT NULL,
  LINKED_FORM_ID       VARCHAR(255) NOT NULL,
constraint PK_QX_LINK primary key (FORM_ID, FIELD_ID)
)

create table dbo.QX_EXTERNALFIELD (
  EXTERNALFORM_ID      VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
  EXTERNALFIELD        VARCHAR(255),
  SOURCEFIELD          VARCHAR(255),
constraint PK_QX_EXTERNALFIELD primary key (EXTERNALFORM_ID, FORM_ID)
)
go

create index IDX_QX_EXTERNALFIELD_FORM  on dbo.QX_EXTERNALFIELD (FORM_ID)
create index IDX_QX_EXTERNALFIELD_FIELD on dbo.QX_EXTERNALFIELD (EXTERNALFIELD)
go

create table dbo.QX_EXTERNALFORM (
  EXTERNALFORM_ID      VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
constraint PK_QX_EXTERNALFORM primary key (EXTERNALFORM_ID, FORM_ID)
)
go

create index IDX_QX_EXTERNALFORM_FORM on dbo.QX_EXTERNALFORM (FORM_ID)
go



create table dbo.QX_EXTERNALSET (