v024_to_v025.sql

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

--drop old tables set
alter table dbo.QX_ROLE drop constraint FK_QX_ROLE_TO_FOCUS;
drop view dbo.qx_permission_objects
drop table dbo.qx_focus;
drop table dbo.qx_subfocus;
drop table dbo.qx_tab;
drop table dbo.qx_nat_forms;
drop table dbo.qx_forms;
drop table dbo.qx_form;
go

-- create new tables set
alter table dbo.QX_PERMISSION drop constraint FK_QX_PERMISSION_TO_PERMISSION_OBJECT_TYPES;
go
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
insert into dbo.QX_VIEW_OBJECT_TYPES select * from qx_permission_object_types
go
drop table qx_permission_object_types
go
alter table dbo.QX_PERMISSION add
constraint FK_QX_PERMISSION_TO_PERMISSION_OBJECT_TYPES foreign key (permission_object_type) references QX_VIEW_OBJECT_TYPES(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,

    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

--alter table dbo.QX_PERMISSION drop column object_id;
--truncate table dbo.QX_PERMISSION;
--select * from dbo.QX_PERMISSION
--alter table dbo.QX_PERMISSION add object_id int not null;
--alter table dbo.QX_PERMISSION add
--constraint FK_QX_PERMISSION_TO_QX_VIEW_OBJECTS foreign key (object_id) references QX_VIEW_OBJECTS(pkey)
truncate table dbo.QX_PERMISSION;
go
drop table qx_permission;
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

--insert initial data
insert into QX_VIEW_OBJECTS([pkey], [name], [type], [parent_object], [order_in_group]) values(0, 'admin', 0, null, 0)

insert into qx_permission([permission_id], [role_id], [access_level], [permission_object_type], [object_id])
values(0, 0, 3, 0, 0)
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,

--    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

alter table dbo.QX_ROLE add constraint FK_QX_ROLE_TO_FOCUS foreign key (default_focus_id) references QX_VIEW_OBJECTS(pkey);

UPDATE QX_SYS_PROP SET PROP_VAL = '025' WHERE (PROP_ID = 'DB_CORE_VERSION');
go