You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spamassassin.apache.org by mm...@apache.org on 2009/09/16 17:14:33 UTC
svn commit: r815828 - in /spamassassin/trunk/sql: README.awl awl_mysql.sql
awl_pg.sql
Author: mmartinec
Date: Wed Sep 16 15:14:30 2009
New Revision: 815828
URL: http://svn.apache.org/viewvc?rev=815828&view=rev
Log:
sql/README.awl, sql/awl_*.sql: extend awl.ip field from
10 to 16 characters to be able to store an IPv6 /48
network address; provide an example of an ALTER command
to change an existing database
Modified:
spamassassin/trunk/sql/README.awl
spamassassin/trunk/sql/awl_mysql.sql
spamassassin/trunk/sql/awl_pg.sql
Modified: spamassassin/trunk/sql/README.awl
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/README.awl?rev=815828&r1=815827&r2=815828&view=diff
==============================================================================
--- spamassassin/trunk/sql/README.awl (original)
+++ spamassassin/trunk/sql/README.awl Wed Sep 16 15:14:30 2009
@@ -9,7 +9,7 @@
In order to activate the SQL based auto-whitelist you have to
configure spamassassin and spamd to use a different whitelist factory.
-This is done with the auto_whitelist_factory config variable, like
+This is done with the auto_whitelist_factory config variable, like
so:
auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList
@@ -25,11 +25,11 @@
will be used to create the connection to your SQL server. It MUST be
in the format as listed above. <driver> should be the DBD driver that
you have installed to access your database (initially tested with
-MySQL, PostgreSQL and SQLite). <database> must be the name of the
-database that you created to store the auto-whitelist table.
-<hostname> is the name of the host that contains the SQL database
-server. <port> is the optional port number where your database server
-is listening.
+MySQL (driver is 'mysql'), PostgreSQL ('Pg') and SQLite ('SQLite')).
+<database> must be the name of the database that you created to store
+the auto-whitelist table. <hostname> is the name of the host that contains
+the SQL database server. <port> is the optional port number where your
+database server is listening.
user_awl_dsn DBI:mysql:spamassassin:localhost
@@ -54,14 +54,14 @@
In order for SpamAssassin to work with your SQL database, you must have
the perl DBI module installed, AS WELL AS the DBD driver/module for your
specific database. For example, if using MySQL as your RDBMS, you must have
-the Msql-Mysql module installed. Check CPAN for the latest versions of DBI
-and your database driver/module.
+the Msql-Mysql (DBD::mysql) module installed. Check CPAN for the latest
+versions of DBI and your database driver/module.
We are currently using:
-DBI-1.20
-Msql-Mysql-modules-1.2219
-perl v5.6.1
+DBI-1.60.9
+DBD-mysql-4.012
+perl v5.10.1
But older versions should work fine.
@@ -74,15 +74,15 @@
username varchar(100) # this is the username whose e-mail is being filtered
email varchar(200) # this is the address key
- ip varchar(45) # this is the ip key (IPv4 or IPv6)
+ ip varchar(16) # this is the ip key (fits IPv4/24 or IPv6/48)
count int(11) # this is the message counter
totscore float # this is the total calculated score
- signedby varchar(255) # a DKIM or DomainKeys signing id
+ signedby varchar(255) # a DKIM or DomainKeys signing domain(s)
You can add as many other fields you wish as long as the above fields are
contained in the table.
-The 'signedby' field was introduced in version 3.3 and is only needed
+The 'signedby' field was introduced in version 3.3.0 and is only needed
if auto_whitelist_distinguish_signed is true, e.g. (in local.cf):
auto_whitelist_distinguish_signed 1
and is only useful if a plugin DKIM is enabled. If the setting is off
@@ -93,10 +93,10 @@
where most of the spam claiming to be from such domain does not come from
a freemail provider and therefore can not carry a valid signature.
-Included is a default table that can be safely used in your own setup. To use
-the default table, you must first create a database, and a username/password
-that can access that database. (See "Creating A Database", in "sql/README", if
-you don't have a suitable database ready.)
+Included is a default table that can be safely used in your own setup.
+To use the default table, you must first create a database, and a
+username/password that can access that database. (See "Creating A Database",
+in "sql/README", if you don't have a suitable database ready.)
To install the table, use the following command:
@@ -108,13 +108,14 @@
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
email varchar(255) NOT NULL default '',
- ip varchar(45) NOT NULL default '',
+ ip varchar(16) NOT NULL default '',
count int(11) NOT NULL default '0',
totscore float NOT NULL default '0',
signedby varchar(255) NOT NULL default '',
PRIMARY KEY (username,email,signedby,ip)
) TYPE=MyISAM;
+
For PostgreSQL, use the following:
psql -U <username> -f awl_pg.sql <databasename>
@@ -135,10 +136,17 @@
newly added field 'signedby' :
auto_whitelist_distinguish_signed 1
+To extend a field awl.ip on an existing table to be able to fit
+an IPv6 addresses (its /48 network part) or an IPv4 address (/24):
+under MySQL:
+ ALTER TABLE awl MODIFY ip varchar(16);
+under PostgreSQL:
+ ALTER TABLE awl ALTER ip TYPE varchar(16);
-Once you have created the database and added the table, just add the required
-lines to your global configuration file (local.cf). Note that you
-must specify the proper whitelist factory in the config file in order
+
+Once you have created the database and added the table, just add the
+required lines to your global configuration file (local.cf). Note that
+you must specify the proper whitelist factory in the config file in order
for this to work and the current username must be passed to spamd.
Testing SpamAssassin/SQL
@@ -156,8 +164,8 @@
SQL Based AWL: Connected to <your dsn>
-If you do not see the above text, then the SQL query was not successful, and
-you should consult any error messages reported.
+If you do not see the above text, then the SQL query was not successful,
+and you should consult any error messages reported.
This code has been tested using MySQL as the RDBMS, with basic tests
against PostgreSQL and SQLite. It has been written with the utmost
@@ -166,4 +174,3 @@
of an expression (ie update foo set bar = bar + 1) should work with
little or no problems. If you find a driver that has issues, please
report them to the SADev list.
-
Modified: spamassassin/trunk/sql/awl_mysql.sql
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/awl_mysql.sql?rev=815828&r1=815827&r2=815828&view=diff
==============================================================================
--- spamassassin/trunk/sql/awl_mysql.sql (original)
+++ spamassassin/trunk/sql/awl_mysql.sql Wed Sep 16 15:14:30 2009
@@ -1,7 +1,7 @@
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
email varchar(255) NOT NULL default '',
- ip varchar(10) NOT NULL default '',
+ ip varchar(16) NOT NULL default '',
count int(11) NOT NULL default '0',
totscore float NOT NULL default '0',
signedby varchar(255) NOT NULL default '',
Modified: spamassassin/trunk/sql/awl_pg.sql
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/awl_pg.sql?rev=815828&r1=815827&r2=815828&view=diff
==============================================================================
--- spamassassin/trunk/sql/awl_pg.sql (original)
+++ spamassassin/trunk/sql/awl_pg.sql Wed Sep 16 15:14:30 2009
@@ -1,7 +1,7 @@
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
email varchar(200) NOT NULL default '',
- ip varchar(10) NOT NULL default '',
+ ip varchar(16) NOT NULL default '',
count bigint NOT NULL default '0',
totscore float NOT NULL default '0',
signedby varchar(255) NOT NULL default '',