OK, this is probably just an over-cautious MySQL question.
All of the examples I look at for setting up per-user prefs using SQL show creating a table that looks like:
username pref value
So, if I want to allow users to control 5 values I would have a table that looks like thsi:
user1 pref1 value1 user1 pref2 value2 user1 pref3 value3 user1 pref4 value4 user1 pref5 value5 user2 pref1 value1 user2 pref2 value2 user2 pref3 value3 user2 pref4 value4 user2 pref5 value5 user3 ..... etc.
When talking about importing a userbase of 6000+ that's gonna be a TALL table really fast.
Is there no way to do it with a table that looks like this?:
username pref1 value1 pref2 value2 pref3 value3 pref4 value4 pref5 value5
So we'd end up with:
user1 pref1 value1 pref2 value2 pref3 value3 pref4 value4 pref5 value5 user2 pref1 value1 pref2 value2 pref3 value3 pref4 value4 pref5 value5 user3 pref1 value1 pref2 value2 pref3 value3 pref4 value4 pref5 value5 user4 pref1 value1 pref2 value2 pref3 value3 pref4 value4 pref5 value5 etc.
Only one row per username rather than one row for each pref for each username.
The only prefs I'm not sure about working in this fashion are things like white/black lists and allowed language choices. Things that can have multiple entries that should be additive and can have a different number of entries for each user.
And to add to the fun, I'm going to have to create a flat file with all of the info to import current customer data into each table. I'm exporting to comma delimited from an Access database. Massaging in Excel to remove extra columns and add default data to newly inserted columns. Moving to the linux box and importing to MySql with LOAD DATA LOCAL INFILE.
And I need to have this operational by the week of May 30th. Cool beans. I haven't even figured out how to do Bayes in SQL yet -- or how to have my users be able to TRAIN bayes since most will be downloading with POP3. How the heck are they going to get the messages back into a place where sa-learn can hit them?
Postfix 2.1.5 SA 3.0.2 MySQL 4.0.24
and, to make life difficult on the "using SQL" end
Amavisd-new 20030616p10
Ideas or a more complete walkthrough for setup.
TIA
Gerald
check out (watch for word wrap) http://wiki.apache.org/spamassassin/UsingSQL
As far as the white/black lists, they work in sql, however, you need to do this:
| %nmgi.com | whitelist_from | [EMAIL PROTECTED] |
per the wiki.
I use the sql userprefs, and know several people who use the userprefs in the sql as well. The reason the prefs are in 'long' opposed to 'wide' is because think of how many options to SA there are to preferences. Almost (not all) settings can be set via the sql table. It is a bit more efficient to have a long format than it is wide, because you will only have a few of the total configurable options in sa in use at any one time. For the domain at work, there are about 200rows of prefs for black/whitelist. etc.
It does seem like a huge pain at first, but then you could grab one of the prepackaged web interfaces (watch for wrap):
http://wiki.apache.org/spamassassin/WebUserInterfaces
As far as bayes training, if you are using sql, you have the -u (user) flag in sa-learn to learn a message as a specified user. This, has made my life much easier. My users are on a windows mail server, and I have created a script that first removes added markup from their mail clients (one client in my case) from the forward. Then after this has run (all scheduled tasks/cron jobs) the Linux side grabs the mail, trains as spam/ham then compresses and sends back to a different folder (in case i need to re-train again later.)
http://wiki.apache.org/spamassassin/BayesFeedbackViaForwarding
-- Thanks, JamesDR
smime.p7s
Description: S/MIME Cryptographic Signature
