昨天用了一天的时间终于搞定MDaemon9.5.1+Oracle9.2.0.1的用户帐户集成,在此作为经验与大家分享一下。
起因:
为了集成用户的e-Mail和其他应用,必须将用户的e-Mail帐户信息存储于Oracle数据库中,方便进行用户单点登录验证身份。
然而,MDaemon自带的数据库脚本中没有for Oracle的,只能自力更生了。
步骤:
首先,我想到尝试修改SQLServer的脚本,但是打开脚本后发现其使用了自增序列的数据类型字段,可是Oracle中没有此数据类型!于是我尝试用触发器+序列解决此问题,原理:当对表进行插入操作时用Before Insert触发器获取序列值自动生成ID。
于是开始建立数据库结构,并在Oracle下进行了测试,似乎一切都很顺利,实现了DomainID与UserID的自增序列。可是,当建立ODBC连接到Oracle后,在MDaemon转换帐户存储时,总是提示“无法转换”而报错。检查了数据结构,估计可能是在转换过程中,MDaemon对该帐号进行了多次追加操作所致,因为我发现当取消Userlist表的MailBox和DomainID的联合主键以后,虽然能够成功转换,但是该表中会出现多条MDaemon的帐号纪录!并且在MDaemon的控制台中看到的帐号列表中也有多个MDaemon的信息。我们知道该帐户是系统管理的帐户,如果出现重复会有不可预知的后果,难道就没有其它办法了吗?
后来经过多次转换,我发现MDaemon会在转换过程中多次插入MDaemon的帐户信息,但是密码却不相同,于是我就有了一个新的想法,可以使用instead of类型的触发器在MDaemon对Userlist表进行操作时来个瞒天过海,在保证MDaemon帐户唯一的前提下实现其逻辑。但是我们知道Oracle中不支持表对象的instead of触发器,因此只能建议一个视图来实现。
建立Domains及其before insert触发器和domainid所用的序列;建立userlisttbl表,该表保持原userlist的结构,并建立基于该表的userlist视图,然后在该视图上建立触发器,最后建立userid用的序列。
下面是全部脚本,已经成功在Oracle9.2.0.1下测试通过。大家可以在做好帐户备份的前提下试试。
drop table USERLISTTBL;
create table USERLISTTBL
(
USERID NUMBER(10) not null,
MAILBOX VARCHAR2(60) not null,
FULLNAME VARCHAR2(100) not null,
MAILDIR VARCHAR2(255) not null,
PASSWORD VARCHAR2(32) not null,
AUTODECODE NUMBER(1) not null,
ISFORWARDING NUMBER(1) not null,
ALLOWACCESS NUMBER(10) not null,
ALLOWCHANGEVIAEMAIL NUMBER(1) not null,
KEEPFORWARDEDMAIL NUMBER(1) not null,
HIDEFROMEVERYONE NUMBER(1) not null,
ENCRYPTMAIL NUMBER(1) not null,
APPLYQUOTAS NUMBER(1) not null,
ENABLEMULTIPOP NUMBER(1) not null,
CANMODIFYGAB NUMBER(1) not null,
CALENDARONLY NUMBER(1) not null,
MAXMESSAGECOUNT NUMBER(10) not null,
MAXDISKSPACE NUMBER(10) not null,
DOMAINID NUMBER(10) not null
);
alter table USERLISTTBL
add constraint PK_USERLIST primary key (USERID)
using index;
alter table USERLISTTBL
add constraint UQ_MAILBOX_DOMAINID unique (MAILBOX, DOMAINID)
using index;
drop table domains;
create table DOMAINS
(
DOMAINNAME VARCHAR2(66) not null,
DOMAINID NUMBER(10) not null
);
alter table DOMAINS
add constraint PK_DOMAINS primary key (DOMAINID)
using index ;
alter table DOMAINS
add constraint UQ_DOMAINNAME unique (DOMAINNAME)
using index ;
alter table USERLISTTBL
add constraint FK_DOMAINID foreign key (DOMAINID)
references DOMAINS (DOMAINID);
drop sequence seq_userlist;
create sequence SEQ_USERLIST
minvalue 1
maxvalue 999999999
start with 1
increment by 1
nocache;
drop sequence seq_domains;
create sequence SEQ_DOMAINS
minvalue 1
maxvalue 999999999
start with 1
increment by 1
nocache;
create or replace view userlist as
select "USERID","MAILBOX","FULLNAME","MAILDIR","PASSWORD","AUTODECODE","ISFORWARDING","ALLOWACCESS","ALLOWCHANGEVIAEMAIL","KEEPFORWARDEDMAIL","HIDEFROMEVERYONE","ENCRYPTMAIL","APPLYQUOTAS","ENABLEMULTIPOP","CANMODIFYGAB","CALENDARONLY","MAXMESSAGECOUNT","MAXDISKSPACE","DOMAINID"
from USERLISTTBL
/
create or replace trigger TRI_DOMAINS
before insert on domains
for each row
declare
begin
SELECT SEQ_DOMAINS.NEXTVAL INTO :NEW.DOMAINID FROM DUAL;
end TRI_DOMAINS;
/
create or replace trigger TRI_USERLIST
instead of insert on userlist
for each row
declare
V_USERID NUMBER(10);
v_usercount number(10);
begin
select count(1) into v_usercount from userlisttbl where mailbox='MDaemon' and domainid=:new.domainid;
IF :NEW.MAILBOX='MDaemon' and v_usercount>0 THEN
update userlisttbl set
mailbox=:new.mailbox,
fullname=:new.fullname,
maildir=:new.maildir,
password=:new.password,
autodecode=:new.autodecode,
isforwarding=:new.isforwarding,
allowaccess=:new.allowaccess,
ALLOWCHANGEVIAEMAIL=:new.allowchangeviaemail,
KEEPFORWARDEDMAIL=:new.keepforwardedmail,
HIDEFROMEVERYONE=:new.HIDEFROMEVERYONE,
ENCRYPTMAIL=:new.ENCRYPTMAIL,
APPLYQUOTAS=:new.APPLYQUOTAS,
ENABLEMULTIPOP=:new.ENABLEMULTIPOP,
CANMODIFYGAB=:new.CANMODIFYGAB,
CALENDARONLY=:new.CALENDARONLY,
MAXMESSAGECOUNT=:new.MAXMESSAGECOUNT,
MAXDISKSPACE=:new.MAXDISKSPACE,
DOMAINID=:new.domainid
where mailbox='MDaemon' and domainid=:new.domainid;
else
SELECT SEQ_USERLIST.NEXTVAL INTO V_USERID FROM DUAL;
INSERT INTO USERLISTTBL VALUES(V_USERID,:NEW.MAILBOX,:NEW.FULLNAME,:NEW.MAILDIR,
:NEW.PASSWORD,:NEW.AUTODECODE,:NEW.ISFORWARDING,:NEW.ALLOWACCESS,
:NEW.ALLOWCHANGEVIAEMAIL,:NEW.KEEPFORWARDEDMAIL,:NEW.HIDEFROMEVERYONE,
:NEW.ENCRYPTMAIL,:NEW.APPLYQUOTAS,:NEW.ENABLEMULTIPOP,:NEW.CANMODIFYGAB,
:NEW.CALENDARONLY,:NEW.MAXMESSAGECOUNT,:NEW.MAXDISKSPACE,:NEW.DOMAINID);
END IF;
end TRI_USERLIST;
/
最后需要说明一点的是,使用数据库存储帐户信息时,用户密码均是明文,安全性差一些。
[本帖最后由 winrich 于 2007-1-21 11:01 编辑]