1.framework-objects_v068.sql

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

DELIMITER //
-- ------------ --
-- Domain model --
-- ------------ --

-- 
-- SYSTEM tables
-- 

-- use qwoss
-- //

SET FOREIGN_KEY_CHECKS = 0
//

SET storage_engine=InnoDB
//

CREATE TABLE `QX_KEYS` (
  `TABLENAME` 		VARCHAR(240) 	NOT NULL,
  `KEYVALUE` 		INT(10) 	NOT NULL,
  PRIMARY KEY (`TABLENAME`)
)
//

create table `QX_FIELDINFO` (
  `TABLE_NAME`		VARCHAR(128)	NOT NULL,
  `COLUMN_NAME`		VARCHAR(128)	NOT NULL,
  `COLUMN_TYPE`		DECIMAL(2)	NOT NULL,
  `ENUM_VALUE`		DECIMAL(2),
  `ENUM_LABEL`		VARCHAR(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`         VARCHAR(255)  NOT NULL,
  `PROP_VAL`        VARCHAR(255),
  `PROP_TEXT`       LONGTEXT,
  `PROP_DATA`       LONGBLOB,
  `NEED_RESTART`    INT           DEFAULT 0,
  `DESCRIPTION`     VARCHAR(255),
primary key (`PKEY`)
)
//

create table `QX_LANGUAGE` (
  `PKEY`            INT          NOT NULL,
  `LANGUAGE_ID`     CHAR(2)      NOT NULL,
  `NAME`            VARCHAR(255) NOT NULL,
primary key (`LANGUAGE_ID`)
)
//


create table `QX_TIMEZONE` (
  `PKEY`            INT           NOT NULL,
  `TIMEZONE_ID`     VARCHAR(255)  NOT NULL,
  `NAME`            VARCHAR(255)  NOT NULL,
  `OFFSET`          INT           NOT NULL,
primary key (`PKEY`)
)
//


create table `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),
primary key (`PKEY`)
)
//


create table `QX_TIME_PATTERN` (
  `PKEY`            INT          NOT NULL,
  `TIME_PATTERN_ID` VARCHAR(255) NOT NULL,
primary key (`PKEY`)
)
//


create table `QX_DATE_PATTERN` (
  `PKEY`            INT          NOT NULL,
  `DATE_PATTERN_ID` VARCHAR(255) NOT NULL,
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`  VARCHAR(255) NOT NULL,
  `ENTITYDATA` LONGBLOB,
primary key (`ENTITY_ID`)
)
//


--
-- SECURITY
--

create table `QX_VIEW_OBJECT_TYPES` (
  `pkey`             INT          NOT NULL,
  `type_description` VARCHAR(255) NOT NULL,
primary key (`pkey`)
)
//


create table `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,
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`)
)
//


create table `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 `FK_QX_FORM_SETTINGS_TO_VIEW_OBJECTS` 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`             VARCHAR(255) NOT NULL,
  `description`      VARCHAR(255) NULL,
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` VARCHAR(255) NOT NULL,
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,
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`)
)
//


create table QX_USER_TYPE (
  `USER_TYPE_ID`         INT		NOT NULL,
  `NAME`               VARCHAR(255)	NOT NULL,
primary key (`USER_TYPE_ID`)
)
//


create table 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,
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`             VARCHAR(40) NOT NULL,
  `DESCRIPTION`      VARCHAR(80),
  `NOTIFICATIONADDR` VARCHAR(40),
  `NOTIFYMETHOD`     DECIMAL(2),
primary key (`PKEY`)
)
//


create table QX_GROUPMEMBER (
  `PKEY`           INT         NOT NULL,
  `EMPLOYEEGROUP`  INT         NOT NULL,
  `WORKGROUPGROUP` INT         NOT NULL,
  `NOTIFYMETHOD`   DECIMAL(2),
  `TIER`           INT         DEFAULT 0,
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_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`)
)
//


create table `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_SECOND_ROLE_FK` foreign key (`role_id`) references `QX_ROLE` (`role_id`),
constraint `FK_QX_WORKGROUP_TO_ROLE_TO_FIRST_WORKGROUP_FK` foreign key (`workgroup_id`) references `QX_WORKGROUP` (`pkey`)
)
//


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              VARCHAR(255) NOT NULL,
  USER_ID              INT          NOT NULL,
  USER_TYPE_ID         INT          NOT NULL,
  PROP_VAL             VARCHAR(255),
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,
primary key (PKEY)
)
//
create unique index IDX_USER_SETTINGS_USER_ID on QX_USER_SETTINGS (USER_ID)
//


--
-- View
--

create table QX_HEADER (
  FORM_ID              VARCHAR(255) NOT NULL,
  HEADER_ORDER         INT NOT NULL,
  CLIENT_WIDTH         INT,
primary key (FORM_ID, HEADER_ORDER)
)
//


create table QX_ROW (
  FORM_ID              VARCHAR(255) NOT NULL,
  ROW_ORDER            INT NOT NULL,
primary key (FORM_ID, ROW_ORDER)
)
//


create table 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,
primary key (ROW_ORDER, FORM_ID, COL_ORDER)
)  
//


create table QX_BUTTON (
  FORM_ID              VARCHAR(255) NOT NULL,
  BUTTON_ID            VARCHAR(255) NOT NULL,
  BUTTON_ORDER         INT,
  PERMISSION           INT,
primary key (FORM_ID, BUTTON_ID)
)
//


create table QX_HTML_ELEMENT (
  FORM_ID              VARCHAR(255) NOT NULL,
  HTML_ELEMENT_ID      VARCHAR(255) NOT NULL,
  HTML_ELEMENT_ORDER   INT,
primary key (FORM_ID, HTML_ELEMENT_ID)
)
//


create table QX_HIDDENCONTROL (
  FORM_ID             VARCHAR(255) NOT NULL,
  FIELD_ID            VARCHAR(255) NOT NULL,
primary key (FORM_ID, FIELD_ID)
)
//


create table QX_LINK (
  FORM_ID              VARCHAR(255) NOT NULL,
  FIELD_ID             VARCHAR(255) NOT NULL,
  LINKED_FORM_ID       VARCHAR(255) NOT NULL,
primary key (FORM_ID, FIELD_ID)
)
//


create table QX_EXTERNALFIELD (
  EXTERNALFORM_ID      VARCHAR(255)    NOT NULL,
  FORM_ID              VARCHAR(255)    NOT NULL,
  EXTERNALFIELD        VARCHAR(255),
  SOURCEFIELD          VARCHAR(255) NULL,
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 (