Page History

...

Install the MSDE database engine. You can get it on the SQL 2000 service pack 4 download page: http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en. You want the SQL2000.MSDE-KB884525-SP4-x86-ENU.EXE file near the bottom of the page. Extract it and follow the installation instructions. 2. Create a new database. Create a new login. Give the login rights to the database. I use the tools that came with SQL Server 2000 to do this. I don't know that Microsoft still has the tools available for MSDE 2000 since they want you to use SQL Express 2005 now. I haven't tested SQL Express 2005 mainly because our environment uses SQL Server 2000. 3. Create the tables in the database.

No Format

CREATE TABLE [dbo].[awl] (
	[username] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[email] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[count] [int] NOT NULL ,
	[totscore] [float] NOT NULL 
) ON [PRIMARY];

CREATE UNIQUE CLUSTERED
  INDEX [PK_awl] ON [dbo].[awl] ([username], [email], [ip])
WITH
    DROP_EXISTING
ON [PRIMARY];


CREATE TABLE [dbo].[bayes_vars] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[username] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[spam_count] [int] NOT NULL ,
	[ham_count] [int] NOT NULL ,
	[token_count] [int] NOT NULL ,
	[last_expire] [int] NOT NULL ,
	[last_atime_delta] [int] NOT NULL ,
	[last_expire_reduce] [int] NOT NULL ,
	[oldest_token_age] [int] NOT NULL ,
	[newest_token_age] [int] NOT NULL 
) ON [PRIMARY];

CREATE UNIQUE CLUSTERED
  INDEX [PK_bayes_vars] ON [dbo].[bayes_vars] ([id])
WITH
    DROP_EXISTING
ON [PRIMARY];

CREATE UNIQUE 
  INDEX [bayes_vars_idx1] ON [dbo].[bayes_vars] ([username])
WITH
    IGNORE_DUP_KEY
    ,DROP_EXISTING
ON [PRIMARY];


CREATE TABLE [dbo].[bayes_expire] (
	[id] [int] NOT NULL ,
	[runtime] [int] NOT NULL 
) ON [PRIMARY];

CREATE UNIQUE CLUSTERED
  INDEX [PK_bayes_expire] ON [dbo].[bayes_expire] ([id])
WITH
    DROP_EXISTING
ON [PRIMARY];


CREATE TABLE [dbo].[bayes_global_vars] (
	[variable] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[value] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY];

CREATE UNIQUE CLUSTERED
  INDEX [PK_bayes_global_vars] ON [dbo].[bayes_global_vars] ([variable])
WITH
    DROP_EXISTING
ON [PRIMARY];

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


CREATE TABLE [dbo].[bayes_seen] (
	[id] [int] NOT NULL ,
	[msgid] [varbinary] (200) NOT NULL ,
	[flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY];

CREATE UNIQUE CLUSTERED
  INDEX [PK_bayes_seen] ON [dbo].[bayes_seen] ([id], [msgid])
WITH
    DROP_EXISTING
ON [PRIMARY];


CREATE TABLE [dbo].[bayes_token] (
	[id] [int] NOT NULL ,
	[token] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[spam_count] [int] NOT NULL ,
	[ham_count] [int] NOT NULL ,
	[atime] [int] NOT NULL 
) ON [PRIMARY];

CREATE UNIQUE CLUSTERED
  INDEX [PK_bayes_token] ON [dbo].[bayes_token] ([id], [token])
WITH
    DROP_EXISTING
ON [PRIMARY];

CREATE 
  INDEX [bayes_token_idx1] ON [dbo].[bayes_token] ([token])
WITH
    DROP_EXISTING
ON [PRIMARY];

CREATE 
  INDEX [bayes_token_idx2] ON [dbo].[bayes_token] ([id], [atime])
WITH
    DROP_EXISTING
ON [PRIMARY];

4. Install the DBI and ODBC database modules.

No Format
ppm install DBI ppm install DBD-ODBC

4. Edit local.cf to change the settings for AWL

No Format

# Use SQL
auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
# Use ODBC connector
user_awl_dsn DBI:ODBC:Driver={SQL Server};Server=localhost;Database=database-name
user_awl_sql_username database-user
user_awl_sql_password database-user-password
# Use this for global AWL
user_awl_sql_override_username global

5. Edit local.cf to change the settings for bayes

No Format

# Use SQL
bayes_store_module          Mail::SpamAssassin::BayesStore::SQL
# Use ODBC connector
bayes_sql_dsn               DBI:ODBC:Driver={SQL Server};Server=MAIL;Database=MailData
bayes_sql_username          MailServer
bayes_sql_password          mail*data
# Use this for global bayes
bayes_sql_override_username global

Child pages

Versions Compared

Old Version 4

New Version 5

Key