v031_to_v032.sql

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

create table dbo.QX_MAIL_PROVIDER (
    PKEY int not null,
    PROVIDER_ID varchar(50),
    PROVIDER_NAME varchar(50),

    constraint PK_QX_MAIL_PROVIDER primary key (PKEY),
)
go

DECLARE @id int
EXEC dbo.QX_NEXT_KEY 'qx_mail_provider', 1, @id OUTPUT
INSERT INTO QX_MAIL_PROVIDER VALUES (@id, 'pop3', 'POP3 Provider')
EXEC dbo.QX_NEXT_KEY 'qx_mail_provider', 1, @id OUTPUT
INSERT INTO QX_MAIL_PROVIDER VALUES (@id, 'imap', 'IMAP4 Provider')
go

create table dbo.QX_ACCOUNT (
    PKEY int not null,
	OWNER_ID int null,
	WORKGROUP_ID int null,
	ACCOUNT_NAME varchar(255) not null,
	ACCOUNT_PROVIDER int not null,
	EMAIL_SERVER varchar(255) not null,
	EMAIL_DOMAIN varchar(255) null,
	EMAIL_SERVER_PORT varchar(50) null,
	EMAIL_SERVER_FOLDER varchar(50) null,
	EMAIL_ACCOUNT_NAME varchar(255) not null,
	EMAIL_PASSWORD varchar(255) not null,
	EMAIL_BOX_NAME varchar(255) null,
	LASTRECEIVE_DATE datetime,
	MAX_MAIL_SIZE int null,
	MAX_INBOX_SIZE int null,
	DELETE_EMAIL_FLAG int null,
	UNREAD_EMAIL_FLAG int null,
	UNREAD_OUTEMAIL_FLAG int null,
	ACTIVE_ACC int null,
	VALID_ACC int null,
	DELAY int null,
	NEXT_CHECK_DATE datetime,
	NOTES text,
	AUTOREPLY_ID int null,
	CREATED_BY int not null,
	CREATED_DATE datetime,

	constraint PK_QX_ACCOUNT primary key (PKEY),
    constraint FK_ACCOUNT_QX_WORKGROUP foreign key (WORKGROUP_ID) references QX_WORKGROUP (PKEY),
    constraint FK_ACCOUNT_QX_MAIL_PROVIDER foreign key (ACCOUNT_PROVIDER) references QX_MAIL_PROVIDER (PKEY),
    constraint FK_ACCOUNT_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY),
    constraint FK_ACCOUNT_OWNER_QX_USERS foreign key (OWNER_ID) references QX_USERS (PKEY),
    constraint FK_ACCOUNT_PROVIDER_ACCOUNT_PROVIDER foreign key (ACCOUNT_PROVIDER) references QX_MAIL_PROVIDER (PKEY),
)ON [PRIMARY]
go

create table dbo.QX_EMAIL_ROUTING_RULES (
    PKEY int not null,
    OWNER_ID int null,
    WORKGROUP_ID int null,
    RULE_NAME varchar(50) not null,
    KEY_WORD varchar(255) not null,
    FROM_ADDRESS int null,
    TO_ADDRESS int null,
    SUBJECT int null,
    BODY int null,
    AUTOREPLY_ID int null,
	CREATED_BY int not null,
	CREATED_DATE datetime,

	constraint PK_QX_EMAIL_ROUTING_RULES primary key (PKEY),
	constraint FK_ROUTING_QX_WORKGROUP foreign key (WORKGROUP_ID) references QX_WORKGROUP (PKEY),
	constraint FK_ROUTING_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY),
    constraint FK_ROUTING_OWNER_QX_USERS foreign key (OWNER_ID) references QX_USERS (PKEY),
)
go

create table dbo.QX_EMAIL_ROUTING (
    ACCOUNT_ID int not null,
    ROUTING_RULE int not null,
    constraint PK_QX_EMAIL_ROUTING primary key (ACCOUNT_ID, ROUTING_RULE),
    constraint FK_QX_EMAIL_ROUTING_QX_ACCOUNT foreign key (ACCOUNT_ID) references QX_ACCOUNT(PKEY),
    constraint FK_QX_EMAIL_ROUTING_QX_EMAIL_ROUTING_RULES foreign key (ROUTING_RULE) references QX_EMAIL_ROUTING_RULES(PKEY)
)
go

create table dbo.QX_EMAIL_FILTER (
    PKEY int not null,
    NAME varchar(50) not null,
    FROM_ADDRESS int null,
    TO_ADDRESS int null,
    SUBJECT int null,
    BODY int null,
    WORD varchar(255) not null,
	CREATED_BY int not null,
	CREATED_DATE datetime,

	constraint PK_QX_EMAIL_FILTER primary key (PKEY),
	constraint FK_EMAIL_FILTER_CREATED_BY_QX_USERS foreign key (CREATED_BY) references QX_USERS (PKEY),
)
go

create table dbo.QX_FILTER_ACCOUNT (
    ACCOUNT_ID int not null,
    EMAIL_FILTER int not null,

    constraint PK_QX_FILTER_ACCOUNT primary key (ACCOUNT_ID, EMAIL_FILTER),
    constraint FK_QX_FILTER_ACCOUNT_QX_ACCOUNT foreign key (ACCOUNT_ID) references QX_ACCOUNT(PKEY),
    constraint FK_QX_FILTER_ACCOUNT_QX_EMAIL_FILTER foreign key (EMAIL_FILTER) references QX_EMAIL_FILTER(PKEY)
)
go

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