v017_to_v018.sql

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

--drop old tables
if exists (select * from dbo.sysobjects where id = object_id(N'QX_RIGHT') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.QX_RIGHT
go

if exists (select * from dbo.sysobjects where id = object_id(N'QX_DD_ACCESSLEVEL') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.QX_DD_ACCESSLEVEL
go

if exists (select * from dbo.sysobjects where id = object_id(N'QX_DD_FOCUSES') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.QX_DD_FOCUSES
go

if exists (select * from dbo.sysobjects where id = object_id(N'QX_FORMSET') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.QX_FORMSET
go

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QX_WORKGROUP_QX_SECURITY]') AND type = 'F')
ALTER TABLE dbo.QX_WORKGROUP
    DROP CONSTRAINT FK_QX_WORKGROUP_QX_SECURITY

DROP INDEX dbo.QX_WORKGROUP.IDX_QX_WORKGROUP_SECURITY

ALTER TABLE dbo.QX_WORKGROUP
    DROP COLUMN SECURITY_ID

drop table dbo.QX_SECURITY
drop table dbo.QX_PERMISSION

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QX_USERS_TO_USERTYPE]') AND type = 'F')
ALTER TABLE dbo.QX_USERS
    ADD constraint FK_QX_USERS_TO_USERTYPE foreign key (USER_TYPE) references qx_user_type(USER_TYPE_ID)

drop table dbo.QX_FOCUS
go

--create new tables
create table dbo.QX_FOCUS (
--    PKEY                 INT             NOT NULL,
    FOCUS_ID             VARCHAR(255)    NOT NULL,
    FOCUS_ORDER          INT,
    ICON                 VARCHAR(255),

--    constraint PK_QX_FOCUS primary key (PKEY)
    constraint PK_QX_FOCUS primary key (FOCUS_ID)
)
go

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

    constraint PK_QX_ROLE primary key (role_id)
--    , constraint FK_QX_ROLE_TO_FOCUS foreign key (default_focus_id) references qx_focus(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_OBJECT_TYPES (
    pkey                int             not null,
    type_description    varchar(255)    not null,

    constraint PK_QX_PERMISSION_OBJECT_TYPES 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               varchar(255),

    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_permission_object_types(pkey)
)
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_permission_objects as
    select
        focus_id as object_id,
        (select pkey from qx_permission_object_types where type_description='Focus') as object_type
    from qx_focus
    union
    select
        subfocus_id as object_id,
        (select pkey from qx_permission_object_types where type_description='SubFocus') as object_type
    from qx_subfocus
    union
    select
        tab_id as object_id,
        (select pkey from qx_permission_object_types where type_description='Tab') as object_type
    from qx_tab
    union
    select
        form_id as object_id,
        (select pkey from qx_permission_object_types where type_description='Form') as object_type
    from qx_form
go

--creates initial data
insert into QX_ACCESSLEVEL ([pkey], [name]) VALUES (0, 'Read');
insert into QX_ACCESSLEVEL ([pkey], [name]) VALUES (1, 'Write');
insert into QX_ACCESSLEVEL ([pkey], [name]) VALUES (2, 'Owner');
insert into QX_ACCESSLEVEL ([pkey], [name]) VALUES (3, 'Full Control');

insert into QX_ROLE([role_id], [default_focus_id], [name]) VALUES(0, NULL, 'This role can manage other users.')
insert into QX_ROLE([role_id], [default_focus_id], [name]) VALUES(1, NULL, 'Common user.')

INSERT INTO QX_PERMISSION_OBJECT_TYPES ([pkey], [type_description]) VALUES (0, 'Focus');
INSERT INTO QX_PERMISSION_OBJECT_TYPES ([pkey], [type_description]) VALUES (1, 'SubFocus');
INSERT INTO QX_PERMISSION_OBJECT_TYPES ([pkey], [type_description]) VALUES (2, 'Tab');
INSERT INTO QX_PERMISSION_OBJECT_TYPES ([pkey], [type_description]) VALUES (3, 'Form');
INSERT INTO QX_PERMISSION_OBJECT_TYPES ([pkey], [type_description]) VALUES (4, 'Field');

UPDATE QX_USERS SET USER_TYPE = 0, PASSWORD= ''
GO

UPDATE QX_LOCK SET USER_TYPE_ID = 0
GO

UPDATE QX_USER_PROP SET USER_TYPE_ID = 0
GO

UPDATE QX_USER_LOG SET USER_TYPE_ID = 0
GO

UPDATE QX_REPORT SET OWNER_TYPE = 0
GO

DELETE FROM QX_USER_TYPE WHERE USER_TYPE_ID = 1
GO

UPDATE QX_USER_TYPE SET NAME = 'database_login' WHERE USER_TYPE_ID = 0
GO

DECLARE @id int

EXEC dbo.QX_NEXT_KEY 'qx_users', 1, @id OUTPUT

INSERT INTO QX_USERS                ([PKEY], [LOGINNAME], [FULLNAME], [USER_TYPE], [PASSWORD], [EMAIL])
VALUES (@id, 'system', 'System', 0, '', '[e-mail]')

INSERT INTO QX_WORKGROUP_TO_ROLE    ([pkey], [role_id], [workgroup_id])     VALUES (0, 0, 0)

INSERT INTO QX_PERMISSION([permission_id], [role_id], [access_level], [permission_object_type], [object_id]) VALUES(0, 0, 3, 0, 'Admin')

delete from qx_sys_prop where pkey in (1, 2)
insert into QX_SYS_PROP (PKEY, PROP_ID, PROP_VAL, DESCRIPTION, NEED_RESTART) values (1, 'AdminId', '0', 'Administrator employee ID', 1);--admin id
insert into QX_SYS_PROP (PKEY, PROP_ID, PROP_VAL, DESCRIPTION, NEED_RESTART) values (2, 'ReporterId', CAST(@id AS VARCHAR(20)), 'Reporter employee ID', 1);--system id


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