oracle and spamassassin 3.0

[Travis Freeland <tr...@deakin.edu.au>]

G'day..

The sql addition to spamassassin is great but I'm quite fond of oracle rac
so here is some info to help you use an oracle database with spamassassin.

For to bayes to work (because oracle doesn't have the 'serial' 
datatype) you also need to change the line 1649 in 
lib/Mail/SpamAssassin/BayesStore/SQL.pm .. There is probably a much cooler
way to do this with a trigger which will keep compatibility with postgres
and mysql (hopefully someone who knows a nice way to do this will reply
with it)..  a trigger would also remove the need for the change to SQL.pm.

Change from: 
  my $sqlinsert = "INSERT INTO bayes_vars (username) VALUES (?)";
to:
  my $sqlinsert = "INSERT INTO bayes_vars (id, username) VALUES (bayes_vars_id_seq.nextval, ?)";

What follows is an oracle schema sql file which has the awl, bayes, and 
userpref tables/indexes:

-- begin of oracle_sql.sql

drop table awl;
drop index awl_pkey;
drop table bayes_expire;
drop index bayes_expire_idx1;
drop table bayes_global_vars;
drop table bayes_seen;
drop table bayes_token;
drop sequence bayes_vars_id_seq;
drop table bayes_vars;
drop index bayes_vars_idx1;
drop table userpref;
drop index userpref_username_idx;
drop sequence user_prefid_seq;
commit;


CREATE TABLE awl (
  username varchar(100) default '' NOT NULL,
  email varchar(200) default '' NOT NULL,
  ip varchar(10) default '' NOT NULL,
  count number(32) default '0',
  totscore float default '0'
);
CREATE UNIQUE INDEX awl_pkey ON awl (username,email,ip);


CREATE TABLE bayes_expire (
  id number default '0' NOT NULL,
  runtime number default '0' NOT NULL
);

CREATE INDEX bayes_expire_idx1 ON bayes_expire (id);

CREATE TABLE bayes_global_vars (
  variable varchar(30) default '' NOT NULL,
  value varchar(200) default '' NOT NULL,
  PRIMARY KEY  (variable)
);

INSERT INTO bayes_global_vars VALUES ('VERSION','3');

CREATE TABLE bayes_seen (
  id number default '0' NOT NULL,
  msgid varchar(200) default '' NOT NULL,
  flag character(1) default '' NOT NULL,
  PRIMARY KEY  (id,msgid)
);

CREATE TABLE bayes_token (
  id number default '0' NOT NULL,
  token char(5) default '' NOT NULL,
  spam_count number default '0' NOT NULL,
  ham_count number default '0' NOT NULL,
  atime number default '0' NOT NULL,
  PRIMARY KEY  (id,token)
);

CREATE SEQUENCE bayes_vars_id_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE bayes_vars (
  id number NOT NULL,
  username varchar(200) default '' NOT NULL,
  spam_count number default '0' NOT NULL,
  ham_count number default '0' NOT NULL,
  token_count number default '0' NOT NULL,
  last_expire number default '0' NOT NULL,
  last_atime_delta number default '0' NOT NULL,
  last_expire_reduce number default '0' NOT NULL,
  oldest_token_age number default '2147483647' NOT NULL,
  newest_token_age number default '0' NOT NULL,
  PRIMARY KEY  (id)
);

CREATE INDEX bayes_vars_idx1 ON bayes_vars (username);

CREATE SEQUENCE user_prefid_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE userpref (
  prefid number(32) NOT NULL unique,
  username varchar(100) NOT NULL,
  preference varchar(30) NOT NULL,
  value varchar(100) NOT NULL
);
CREATE INDEX userpref_username_idx ON userpref(username);

commit;

--end of oracle_sql.sql

Travis Freeland

IT Architect, ITS, Deakin University
http://www.deakin.edu.au/~travis/sig.html

Re: oracle and spamassassin 3.0

[Michael Parker <pa...@pobox.com>]

On Thu, Sep 23, 2004 at 01:31:13AM +1000, Travis Freeland wrote:
> 
> For to bayes to work (because oracle doesn't have the 'serial' 
> datatype) you also need to change the line 1649 in 
> lib/Mail/SpamAssassin/BayesStore/SQL.pm .. There is probably a much cooler
> way to do this with a trigger which will keep compatibility with postgres
> and mysql (hopefully someone who knows a nice way to do this will reply
> with it)..  a trigger would also remove the need for the change to SQL.pm.
> 

This is the method I recommend.  If someone wanted to work out a
bayes_oracle.sql scheme that included the needed trigger magic I'd be
happy to add it to the sql/ directory.

Michael