MDaemon9.5.1+Oracle9.2.0.1的用户帐户集成

winrich

昨天用了一天的时间终于搞定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@mydomain.com>”而报错。检查了数据结构，估计可能是在转换过程中，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 编辑 ]

大碗豆浆

asionliang

原帖由 大碗豆浆 于 2007-1-21 11:41 发表

多种选择毕竟是好的
技术不分前后，达者为师！

lwz_08

感谢分享

PS:但如果换我做,就用MS SQL算了,不过技术上的探讨还是有必要的,可作借鉴

job21

感谢分享成功经验，学习了！

ceeo123456

不过就是看不太懂  有点深

kks168

收藏一下，谢分享。

howard324

oracle 跨平台的，性能上MSSQL是无法比拟的

如果要结合某些应用的确是不错的选择

yangwulong

不错,学习了,谢谢....