1.framework-objects_v062.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 QX_KEYS (
  TABLENAME        VARCHAR2(240)   NOT NULL,
  KEYVALUE         INT             NOT NULL,
constraint PK_QX_KEYS primary key (TABLENAME)
);



create table QX_FIELDINFO (
  TABLE_NAME        VARCHAR2(18)   NOT NULL,
  COLUMN_NAME       VARCHAR2(18)   NOT NULL,
  COLUMN_TYPE       NUMBER(2)     NOT NULL,
  ENUM_VALUE        NUMBER(2),
  ENUM_LABEL        VARCHAR2(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         VARCHAR2(255)  NOT NULL,
  PROP_VAL        VARCHAR2(255),
  PROP_TEXT       CLOB,
  PROP_DATA       BLOB,
  NEED_RESTART    NUMBER(1)        DEFAULT 0,
  DESCRIPTION     VARCHAR2(255),
constraint PK_QX_SYS_PROP primary key (PKEY)
);



create table QX_LANGUAGE (
  PKEY          INT           NOT NULL,
  LANGUAGE_ID   CHAR(2)       NOT NULL,
  NAME          VARCHAR2(255) NOT NULL,
constraint PK_QX_LANGUAGE primary key (LANGUAGE_ID)
);



create table QX_TIMEZONE (
  PKEY           INT           NOT NULL,
  TIMEZONE_ID    VARCHAR2(255) NOT NULL,
  NAME           VARCHAR2(255) NOT NULL,
  OFFSET         INT           NOT NULL,
constraint PK_QX_TIMEZONE primary key (PKEY)
);



create table QX_COUNTRY (
  PKEY                 INT           NOT NULL,
  COUNTRY_ID           VARCHAR2(3)   NOT NULL,
  NAME                 VARCHAR2(255) NOT NULL,
  DEF_LANG_ID          CHAR(2),
  DEF_TIMEZONE_ID      VARCHAR2(255),
constraint PK_QX_COUNTRY primary key (PKEY)
);

create index IDX_QX_COUNTRY_LANGUAGE on QX_COUNTRY (DEF_LANG_ID);
create index IDX_QX_COUNTRY_TIMEZONE on QX_COUNTRY (DEF_TIMEZONE_ID);


CREATE TABLE QX_TIME_PATTERN (
  PKEY             INT           NOT NULL,
  TIME_PATTERN_ID  VARCHAR2(255) NOT NULL,
constraint PK_QX_TIME_PATTERN primary key (PKEY)
);

CREATE TABLE QX_DATE_PATTERN (
  PKEY             INT           NOT NULL,
  DATE_PATTERN_ID  VARCHAR2(255) NOT NULL,
constraint PK_QX_DATE_PATTERN 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            VARCHAR2(255)    NOT NULL,
  ENTITYDATA           BLOB,
constraint PK_QX_ENTITY primary key (ENTITY_ID)
);

--
-- SECURITY
--

create table QX_VIEW_OBJECT_TYPES (
    pkey                int             not null,
    type_description    VARCHAR2(255)   not null,
constraint PK_QX_VIEW_OBJECT_TYPES primary key (pkey)
);

create table QX_VIEW_OBJECTS (
    pkey                        int             not null,
    name                        VARCHAR2(255)   not null,
    type                        int             not null,
    parent_object               int             null,
    order_in_group              int             default 0 not null,
    icon                        VARCHAR2(255)   null,
    inframelinks				number(1)       default 0 not null,
    grid				        number(1)       default 1 not null,
constraint PK_QX_VIEW_OBJECTS primary key (pkey),
constraint FK_QX_VIEW_OBJ_TO_OBJ_TYPES foreign key (type) references QX_VIEW_OBJECT_TYPES(pkey),
constraint FK_QX_VIEW_OBJ_TO_PARENT_OBJ foreign key (parent_object) references QX_VIEW_OBJECTS(pkey)
);

create table QX_FORM_SETTINGS (
    view_object_id          int         not null,
    entity_id               VARCHAR2(255),
    grid                    int,
    use_default_actions     number(1)         default 1,
    labels_layout           number(1)         default 0,
    myqueweb           	    number(1)         default 1 not null,
    autosearch           	number(1)         default 0 not null,
constraint FK_QX_FORM_SET_TO_VIEW_OBJ 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              VARCHAR2(255)   not null,
    description       VARCHAR2(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)
);

create table QX_ACCESSLEVEL (
    pkey    int             not null,
    name    VARCHAR2(255)   not null,
constraint PK_QX_ACCESSLEVEL 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,
constraint PK_QX_PERMISSION primary key (permission_id),
constraint FK_QX_PERM_TO_ACCESS_LVL foreign key (access_level) references qx_accesslevel(pkey),
constraint FK_QX_PERM_TO_ROLE foreign key (role_id) references qx_role(role_id),
constraint FK_QX_PERM_TO_PERM_OBJ_TYPES foreign key (permission_object_type) references QX_VIEW_OBJECT_TYPES(pkey),
constraint FK_QX_PERM_TO_QX_VIEW_OBJ foreign key (object_id) references QX_VIEW_OBJECTS(pkey)
);

create table QX_USER_TYPE (
    USER_TYPE_ID         INT            NOT NULL,
    NAME                 VARCHAR2(255)  NOT NULL,
constraint PK_QX_USER_TYPE primary key (USER_TYPE_ID)
);

create table QX_USERS (
    PKEY                    INT             NOT NULL,
    LOGINNAME               VARCHAR2(255)   NOT NULL,
    FULLNAME                VARCHAR2(255)   NULL,
    PASSWORD                VARCHAR2(255)   NULL,
    EMAIL                   VARCHAR2(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)
);

create unique index IDX_USERS_LOGINNAME on QX_USERS (LOGINNAME);


create table QX_WORKGROUP (
    pkey                int             not null,
    name                VARCHAR2(40)    not null,
    description         VARCHAR2(80),
    notificationaddr    VARCHAR2(40),
    notifymethod        NUMBER(2),
constraint PK_QX_WORKGROUP primary key (PKEY)
);

create table QX_GROUPMEMBER (
  PKEY              INT     NOT NULL,
  EMPLOYEEGROUP     INT     NOT NULL,
  WORKGROUPGROUP    INT     NOT NULL,
  NOTIFYMETHOD      NUMBER(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)
);

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_ROLE_FK foreign key (role_id) references QX_ROLE(role_id),
constraint FK_QX_ROLE_TO_USER_TO_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_WGROUP_ROLE_TO_WGROUP_FK foreign key (workgroup_id) references QX_WORKGROUP(pkey),
constraint FK_QX_WGROUP_ROLE_TO_ROLE_FK foreign key (role_id) references QX_ROLE(role_id)
);

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          VARCHAR2(512) NOT NULL,
  USER_ID          INT           NOT NULL,
  USER_TYPE_ID     INT           NOT NULL,
  PROP_VAL         VARCHAR2(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)
);

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,
constraint PK_USER_SETTINGS primary key (PKEY)
);

create unique index IDX_USER_SETTINGS_USER_ID on QX_USER_SETTINGS (USER_ID);


--
-- View
--
create table QX_HEADER (
  FORM_ID              VARCHAR2(255) NOT NULL,
  HEADER_ORDER         INT NOT NULL,
  CLIENT_WIDTH         INT,
constraint PK_QX_HEADER primary key (FORM_ID, HEADER_ORDER)
);

create table QX_ROW (
  FORM_ID              VARCHAR2(255) NOT NULL,
  ROW_ORDER            INT NOT NULL,
constraint PK_QX_ROW primary key (FORM_ID, ROW_ORDER)
);

create table QX_COL (
  ROW_ORDER            VARCHAR2(255) NOT NULL,
  FORM_ID              VARCHAR2(255) NOT NULL,
  COL_ORDER            INT NOT NULL,
  FIELD_ID             VARCHAR2(255),
  ROWSPAN              INT,
  COLSPAN              INT,
constraint PK_QX_COL primary key (ROW_ORDER, FORM_ID, COL_ORDER)
);

create table QX_BUTTON (
  FORM_ID              VARCHAR2(255) NOT NULL,
  BUTTON_ID            VARCHAR2(255) NOT NULL,
  BUTTON_ORDER         INT,
  PERMISSION           INT,
constraint PK_QX_BUTTON primary key (FORM_ID, BUTTON_ID)
);

create table QX_HTML_ELEMENT (
  FORM_ID              VARCHAR2(255) NOT NULL,
  HTML_ELEMENT_ID      VARCHAR2(255) NOT NULL,
  HTML_ELEMENT_ORDER   INT,
constraint PK_QX_HTML_ELEMENT primary key (FORM_ID, HTML_ELEMENT_ID)
);

create table QX_HIDDENCONTROL (
  FORM_ID              VARCHAR2(255) NOT NULL,
  FIELD_ID            VARCHAR2(255) NOT NULL,
constraint PK_QX_HIDDENCONTROL primary key (FORM_ID, FIELD_ID)
);

create table QX_LINK (
  FORM_ID              VARCHAR2(255) NOT NULL,
  FIELD_ID             VARCHAR2(255) NOT NULL,
  LINKED_FORM_ID       VARCHAR2(255) NOT NULL,
constraint PK_QX_LINK primary key (FORM_ID, FIELD_ID)
);

create table QX_EXTERNALFIELD (
  EXTERNALFORM_ID      VARCHAR2(255)    NOT NULL,
  FORM_ID              VARCHAR2(255)    NOT NULL,
  EXTERNALFIELD        VARCHAR2(255),
  SOURCEFIELD          VARCHAR2(255),
constraint PK_QX_EXTERNALFIELD 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      VARCHAR2(255)    NOT NULL,
  FORM_ID              VARCHAR2(255)    NOT NULL,
constraint PK_QX_EXTERNALFORM primary key (EXTERNALFORM_ID, FORM_ID)
);