Hi,
I have created a small patch to integrate management of SpamAssassin preferences into RC. The preferences must be stored in a MySQL database (it will probably work with any database supported by RC but MySQL is what I use).
The patch works with rev1652 (the latest SVN right now) and adds a 'Spam' tab to the 'Personal Settings'. It has been tested with SpamAssassin version 3.2.3. I have attached a screenshot as well as the patch.
Install
======
To apply the patch run `patch -ul -d /path/to/roundcubemail/ -p1 < /path/to/patch`. This should be all you need to do to install the patch.
Config
======
The config file is located in plugins/sauserprefs/config.inc.php
* You must set the database connection string
* Enter the table name, name of the username field, preference field, and value field
* Enable/disable the preferences you want your users to be able to change in $sauserprefs_config['settings']
20081112: New versions of the patch added; one for the 0.2beta release and one (r1940) which works with the SVN.
THIS PATCH IS NO LONGER SUPPORTED. IT HAS NOW BEEN CONVERTED TO USE THE PLUGIN API. FURTHER DISCUSSION CAN BE FOUND HERE http://www.roundcubeforum.net/api-based-plugins/4879-spamassassin-mysql-plugin.html
I installed the patch discovered that in the config.inc.php file there is a
$changepasswd_config['db_dsnw'] = ....
where it should be
$sauserprefs__config['db_dsnw'] = ....
I also cannot start up the plugin.
Is there something that needs to be added elsewhere to start up the plugin?
-- Neil
doh! sorry, the line should be $sauserprefs_config['db_dsnw'] = .... I have updated the attached file.
It also appears that
if ($RCMAIL->action=='sauserprefs')
include('program/steps/settings/sauserprefs.inc');
if ($RCMAIL->action=='sauserprefs')
include('program/steps/settings/sauserprefs.inc');
should be added into index.php
around line 280
No, that is not missing. This patch is for rev1652, the need to include that code in index.php was removed some time ago.
This plugin seems to have a problem with Safari on the Mac. It works in IE and on FF, but clicking the 'Spam' link from within Safari doesn't do anything.
Has anyone had a chance to upgrade this patch to a later version from SVN. I love the functionality of this patch but I need a later version of roundcube for my needs.
Hi,
Sorry, i probably should have updated this thread. what SVN version do you have?
Could you update the patch to make it work with roundcube 2 beta?
Cheers!!!
done. please see first post.
sorry, which is the release r1940? Does this patch works with RC 0.1.1?
1940 is the SVN version from about 6 weeks ago.
sorry no, i dont have a version of this code that works with 0.1.1
Thank you so much! It works great. Is it possible some how add a block/allow button/link to the email message so a filter is automatically added to the database?
You could add something, I guess next to the little + for adding addresses to the address book, which adds and address to the whitelist/blacklist. Is that what you had in mind? I think its the kind of feature that should wait until the plugin api is done, at the moment I am trying to keep the number of files that have to be patched to a minimum.
Yeah that is exactly what I was thinking.
Waiting is probably a good idea, however I'd love to have that feature :P I suppose I'll just have to enter them manually until the plugin api is completed.
Lastly, I think it might be a good idea to change the Score threshold to text box instead of a drop down (this is minor though and just a suggestion).
I'm not sure about a textbox for the score, I don't see the need for any sort of freeform input. I was thinking of perhaps putting a slider rather than the drop down though, it might just look nicer.
I was thinking a bit more about whitelist/blacklist buttons. I have some code which syncs the RC addressbook and the whitelist may be that would be better. But then may be it would still need a "Block this sender button".
Well I think for the most part "Block this sender" is ineffecent because spammers are always using different domains and email addresses. Personally I really would just like to add some whitelists. If you wouldn't mind sharing the code you have, that'd be great!
As for the score, the reason I suggested the textbox, was due to the fact that I use a score of 3.5. However, because 3.5 is not a valid opition it defaults to 1, thus any changes I make would the score would reduce to 1. I added extra values in for myself, to fix the problem. Is there some way you could read in the current score and keep it?
Greetings,
i have installed the patch on 0.2 beta RC and i have created database, the spam module writes the data in the database but i don`t known how to make spamassasin to access the database and read the settings for every user.
Thanks in advance!
hi, the internet is your friend... UsingSQL - Spamassassin Wiki (http://wiki.apache.org/spamassassin/UsingSQL)
New BETA version based on SVN version 2090
Attached is a beta version of the SpamAssassin MySQL patch built against SVN version 2090.
This version includes a new config option to set the step size by which the spam score options are incremented.
This version also includes a synchronisation function to copy any inserts, updates or deletes from the Roundcube address book to the SpamAssassin whitelist.
NOTE: The synchronisation function requires your SpamAssassin prefs table includes a field in which the contact_id from the Roundcube address book can be stored, the default value for this field (used for any address not in the address book should be -1). For example:
ALTER TABLE `userpref` ADD `contact_id` INT NOT NULL DEFAULT '-1';
The sync will only work for addresses entered into your address book while the code is active, it will not add anything that is already in your address book. You have to do this yourself.
I have only just finished this code and its kinda late here so there could be a few bugs which is why I have called it beta, please report any any issues to this thread and I will try and fix them.
*FIXED a bug with data typing that sometimes stopped the correct spam score from being selected on page load, updated patch attached.
*UPDATE added the ability to configure the order of the fieldsets in the UI and also added a bit of text to tell users when whitlist/addressbook sync is enabled. New file attached sauserprefs.beta2.r2099.patch includes these changes, built against SVN 2099 and will work with 2101.
**UPDATE 20090102**
I have added a new version of the patch, sauserprefs.BETA.0_2.patch. This version works with the 0.2 stable release of RoundCube. I have also made a number of improvements to the script.
NOTE: the config variable to change the order of the fieldsets added in the last version of this patch has been remove, the order of the field sets is now controlled in the template file (skins/default/templates/sauserprefs.html)
**UPDATE 20090108**
sauserprefs.0_2stable.patch - works with the download from the RC site
sauserprefs.r2198.patch - works with the SVN version
I installed the patch, but I'm not sure how to add the sql code. I don't hav e atable called userpref.
How do I go about applying the sql update? I'm a sql noob :P
The field needs to be added to the table which stores your SpamAssasin settings so 'userpref' should be what ever that table is called. Do you have phpmyadmin? login and goto the database which contains the SA prefs table then select that table and add the field.
sorry its a bit of a rubbish explaination i know but i am not quite sure how to explain it.
Thanks JohnDoh for the update. This seems to work great. I've doing some testing and everything seems to be working great. No problems.
The only change I would like to ask for, is to be able to change the order of the sections. I would like to put the white/black list section higher on the page so my users can understand what they need to do better. I have had a few questions on this.
Thanks again for the update!
-Matt
Hi Matt. Great idea! I have added it to the latest version of the patch - see my post above about the new beta version ... you want beta2.
This update looks like (Beta2) The added feature works well for me, thanks.
Everything is working well for me, but I haven't tried the auto whitelist feature yet.
Thanks
-Matt
Hi! What you can say about Amavisd-mysql and this patch? May be MySQL schema in clear SpamAssassin and amavisd are similar?
I know nothing about amavis so i have nothing to say about it and this patch.
Please, would you be so kind and post the necessary icons (.png). I don't know a Win32 application to handle unix patch files.
Hi, No problem, images attached. Patch for Windows (http://gnuwin32.sourceforge.net/packages/patch.htm) can read and apply the patch file... something like
c:\path\to\patch.exe --binary -ul -d c:\path\to\roundcubemail\ -p1 < c:\path\to\patch
but it doesn't seem to like the images :/
Thanks, great hint ...
I tried to patch it, but when i try the command, it says that the patch command is unknown. I have a ubuntu 6.06 version with php 5.2.6 and plesk 8.4!
I tried to patch it, but when i try the command, it says that the patch command is unknown. I have a ubuntu 6.06 version with php 5.2.6 and plesk 8.4!
PS: Maybe you can combine this with the SPAM/Ham Report Addon (http://www.roundcubeforum.net/plug-ins/2535-spam-ham-report-addon.html).
sounds like you dont have patch installed. Patch is a bit of software that allows you to apply patch/diff.
Can you update this to work with release two?
Thanks!
Hi, there is a version that works with 0.2stable from the SVN and I will update it to work with the version from the RC website when i have sometime, hopefully this weekend. Its only the compressed app.js file that is different, if you swap app.js for app.js.src the patch should work as is.
added a patch for the release version from the RC website, please see earlier post.
Thanks! It works great, however it seems that you got rid of the 0.5 increments :-/
the option is still there, look in plugins/sauserperfs/config.inc.php for $sauserprefs_config['score_inc'].
I installed the patch, but it doesn't work! Because my Spamassasin comes with Plesk, and Plesk has only the user id in the userpref table and has the username in a diffrent table.
userpref table = spamfilter preferences
table with username = spamfilter
So is there a plesk version comming up!
There is no plesk version but it should be easy to fix, just change the SQL statements in sauerprefs.inc and save_sauserprefs.inc.
I tried it! As soon I use $user_db->fetch_row or $user_db->result it shows me a blank page.
Sorry pretty new to songbird programming. I know where to change it and that i have to get the id from the other table by searching for the username there. It just doesn't work!
UPDATE:
I managed to solve the problem, I used now fetch_assoc to do it. Obviously he didn't knew the functions above.
thx for this great addon, i have installed and works fine,
i have a bug or problem ? by to add a address from an incoming email on click add to address book will not work
thats the address add to whitelist via whitelist sync can you help me is this a bug or not yet implement ?
thx for help
Thanks for this excellent add-on! I suggest to add this as a component to Roundcube - maybe as an optional extension that can be enabled through the main config file.
Good job, have been looking for something like that for a while!
Now a question: I am trying to use the whitelist_sync feature but somehow it doesn't seem to work - or it works differently than I expect it to.
If I added a user to my addressbook, should it be added as a whitelist user to the userprefs? I did add the contact_id field for the table but it's always -1 no matter where I add the user (addressbook, add via + symbol at email, whitelist rule).
I can live without this feature, everything else works great! Just wondering what this feature will add to RC and the Whitelists :)
EDIT: Never mind, without me doing anything the sync now works when adding something to the addressbook. Which makes this thing even BETTER! :P
At the moment the sync only works between actions done in the addressbook and the whitelist. I am working on a better system that will sync with the + as well as any other addressbook related action.
That's good news, glad to see this is actively developed. Keep it up, will fetch the new version as soon as it becomes available and test it out.
I have added a new version for the latest SVN to the site. It has the new address sync code that will work with the + sign as well as the addressbook.
many thx works fine !!!
Hi,
I installed the plugin, now i get the spam screen in the user preferences section, but whenever i make a change to the spam settings i get an error that the changes cannot be made, and they are not saved.
A red error msg at the top of the screen saying: Cannot change spam settings.
Prolly missed something somewhere... any ideas ?
double check everything in your config (plugins/sauserprefs/config.inc.php), make sure all the table and field names are correct. or may be add some debug statements to program/steps/settings/save_sauserprefs.inc, then you should be able to see the SQL queries its executing and hopefully where it is going wrong.
Thx for the reply, i made the mistake of setting the roundcube db in the settings, changed it into the spamassassin db and it works, i can see and change the settings.
I still get another error however.
DB Error in /home/www/mail/program/include/rcube_mdb2.php (255): _doQuery: [Error message: Could not execute statement]
[Last executed query: PREPARE mdb2_statement_mysql_3b6f80edc47aba5da41b5709a5637bdd739cafda7 FROM 'SELECT 1 FROM session\n WHERE sess_id=?']
[Native code: 1146]
[Native message: Table 'spamassassin.session' doesn't exist]
SERVICE CURRENTLY NOT AVAILABLE!
_doQuery: [Error message: Could not execute statement] [Last executed query: PREPARE mdb2_statement_mysql_3b6f80edc47aba5da41b5709a5637bdd739cafda7 FROM 'SELECT 1 FROM session\n WHERE sess_id=?'] [Native code: 1146] [Native message: Table 'spamassassin.session' doesn't exist]
:S i'm not sure what has happened there, is it possible that you have got `$rcmail_config['db_dsnw']` and `$sauserprefs_config['db_dsnw']` mixed up? it looks like it is trying to use the spamassassin db when it should be using the roundcube one.
Hi, i thought the same, but i checked....
$sauserprefs_config['db_dsnw'] = 'mysql://spam:xxx@localhost/spamassassin';
$rcmail_config['db_dsnw'] = 'mysql://roundcube:xxx@localhost/roundcubemail';
Seems ok, so somwhere the plugin is not correctly switching back to the correct db. The weird thing is that except for the error message it works, the settings are saved.
ok i found the prob. you cannot use the same user for both db's, i added another user and it works fine now.
I can't see why that is happening I will have to do some more debugging, it only occurs when the username and pass are the same for both connections. Thanks for figuring out what the problem was.
I installed this plugin n now when i click user settings tab, it gives me a blank page n the logs show;
PHP Warning: include_once(textfield.php) [
function.include-once]: failed to open stream: No such file or directory in /home/myserver/public_html/mail/program/include/iniset.php on line 93
[17-Feb-2009 07:52:28] PHP Warning: include_once() [
function.include]: Failed opening 'textfield.php' for inclusion (include_path='/home/myserver/public_html/mail/:/home/myserver/public_html/mail/program:/home/myserver/public_html/mail/program/lib:/home/myserver/public_html/mail/program/include:.:/usr/lib/php:/usr/local/lib/php') in /home/myserver/public_html/mail/program/include/iniset.php on line 93
[17-Feb-2009 07:52:28] PHP Fatal error: Class 'textfield' not found in /home/myserver/public_html/mail/program/steps/settings/func.inc on line 43
is there a way i can uninstall the patch?
hmmm thats a bit odd, I'm sorry about that. The patch doesn't make any changes to program/steps/settings/func.inc which is apparently where the problem is. Did the patch apply cleanly? What version of RC are you using?
To reverse a patch just run the command again with -R.
Hello, thx by the patch.
I had that modified the settingstabs.html file, adding this lines:
Because i didnt see the tab "Spam", its right ?
Thx
Gabriel