example database structure for rules and configs

[Steve Freegard]

On 06/10/10 14:25, Gavin Silver wrote:
> Is anyone using the SQL functionality successfully?

Yes - as I wrote and contributed the code for this.

Here's something to get you started; you'll need to create a database 
table called 'config' with three columns:

hostname
option
value
external

Next - pick an option that you want to override from the database; for 
example 'Non Spam Actions'.

MailScanner stores two versions of this variable; the name of the option 
in MailScanner.conf is the external version with the spaces removed and 
lowercased, so 'Non Spam Actions' becomes 'nonspamactions'.

To find the internal version of the option name you have to look in 
ConfigDefs.pl in /usr/lib/MailScanner at the translation table.  If the 
internal variable is different then it will appear within this 
translation list; otherwise the internal is the same as the external 
variable - in this case 'nonspamactions' = 'hamactions'.

So you would add the following values to your database table:

hostname = <your machine hostname (e.g. output of `hostname -f`)>
option = 'hamactions'
value = 'deliver store'
external = 'nonspamactions'

Then you'll need to add a dummy serial number option (this is used to 
cause the children to restart when you increment the number):

hostname = <you machine hostname (as above)>
option = 'confserialnumber'
value = 1
external = 'confserialnumber'

In MailScanner.conf you would then set-up the following options:

DB DSN = <settings to connect to your chosen database>
DB Username = <username>
DB Password = <password>
SQL Serial Number = SELECT value FROM config WHERE option='confserialnumber'
SQL Quick Peek = SELECT value FROM config WHERE external=? AND hostname=?
SQL Cofnig = SELECT option, value FROM config WHERE hostname=?
SQL Debug = yes

Then if you run 'MailScanner --lint' you will be able to see the 
database functions running and overriding the value read from 
MailScanner.conf for 'Non Spam Actions' along with it picking up the 
serial number.

To override *all* settings; you'll need to write some sort of script to 
parse both MailScanner.conf and ConfigDefs.pl (you can rip most of this 
code from Config.pm) to gather all of the configuration options and the 
internal => external translations and create your 'config' table from them.

Hopefully that should get you started.

Kind regards,
Steve.