# Roundcube Community Forum

## Third Party Contributions => Old Style Plug-Ins => Topic started by: JohnDoh on August 16, 2008, 11:44:16 AM

Title: SpamAssassin MySQL patch
Post by: JohnDoh on August 16, 2008, 11:44:16 AM
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 Title: Incorrect Declaration Post by: Grandmaster_Bofhy on August 24, 2008, 06:05:24 AM 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 Title: SpamAssassin MySQL patch Post by: JohnDoh on August 24, 2008, 06:35:17 AM doh! sorry, the line should be$sauserprefs_config['db_dsnw'] = .... I have updated the attached file.
Title: SpamAssassin MySQL patch
Post by: Grandmaster_Bofhy on August 24, 2008, 07:51:55 AM
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
Title: SpamAssassin MySQL patch
Post by: JohnDoh on August 24, 2008, 09:55:49 AM
No, that is not missing. This patch is for rev1652, the need to include that code in index.php was removed some time ago.
Title: SpamAssassin MySQL patch
Post by: rgalps on September 06, 2008, 04:06:38 PM
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.
Title: SpamAssassin MySQL patch
Post by: mdr on November 02, 2008, 01:27:25 AM
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.
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 06, 2008, 03:34:25 AM
Hi,

Sorry, i probably should have updated this thread. what SVN version do you have?
Title: SpamAssassin MySQL patch
Post by: pyther24 on November 09, 2008, 03:10:00 PM
Could you update the patch to make it work with roundcube 2 beta?

Cheers!!!
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 12, 2008, 03:21:30 AM
done. please see first post.
Title: SpamAssassin MySQL patch
Post by: zuluh on November 16, 2008, 03:11:22 PM
sorry, which is the release r1940? Does this patch works with RC 0.1.1?
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 17, 2008, 06:15:55 AM
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
Title: SpamAssassin MySQL patch
Post by: pyther24 on November 23, 2008, 07:40:53 PM
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?
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 24, 2008, 06:30:13 AM
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.
Title: SpamAssassin MySQL patch
Post by: pyther24 on November 24, 2008, 08:37:29 AM
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).
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 26, 2008, 06:15:31 AM
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".
Title: SpamAssassin MySQL patch
Post by: pyther24 on November 26, 2008, 07:09:35 AM
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?
Title: SpamAssassin MySQL patch
Post by: matrixn on November 26, 2008, 08:12:43 PM
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 dont known how to make spamassasin to access the database and read the settings for every user.

Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 27, 2008, 07:38:53 AM
hi, the internet is your friend... UsingSQL - Spamassassin Wiki (http://wiki.apache.org/spamassassin/UsingSQL)
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 27, 2008, 03:36:03 PM
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:
Code: [Select]
 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
Title: SpamAssassin MySQL patch
Post by: pyther24 on November 28, 2008, 10:59:51 AM
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
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 28, 2008, 12:44:09 PM
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.
Title: Thanks JohnDoh
Post by: mdr on November 28, 2008, 07:56:57 PM
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
Title: SpamAssassin MySQL patch
Post by: JohnDoh on November 30, 2008, 05:54:51 AM
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.
Title: Nice Update
Post by: mdr on November 30, 2008, 11:19:10 PM
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
Title: SpamAssassin MySQL patch
Post by: Sava on December 01, 2008, 03:40:06 PM
Hi! What you can say about Amavisd-mysql and this patch? May be MySQL schema in clear SpamAssassin and amavisd are similar?
Title: SpamAssassin MySQL patch
Post by: JohnDoh on December 02, 2008, 04:38:54 AM
I know nothing about amavis so i have nothing to say about it and this patch.
Title: SpamAssassin MySQL patch
Post by: rosali on December 03, 2008, 02:06:50 PM
Please, would you be so kind and post the necessary icons (.png). I don't know a Win32 application to handle unix patch files.
Title: SpamAssassin MySQL patch
Post by: JohnDoh on December 04, 2008, 03:23:18 AM
Hi, No problem, images attached. Patch for Windows (http://gnuwin32.sourceforge.net/packages/patch.htm) can read and apply the patch file... something like
Code: [Select]
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 :/
Title: SpamAssassin MySQL patch
Post by: rosali on December 04, 2008, 05:42:36 PM
Thanks, great hint ...
Title: SpamAssassin MySQL patch
Post by: horfic on December 08, 2008, 10:13:06 AM
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!
Title: SpamAssassin MySQL patch
Post by: horfic on December 08, 2008, 10:14:36 AM
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!

Title: SpamAssassin MySQL patch
Post by: JohnDoh on December 09, 2008, 03:34:59 AM
sounds like you dont have patch installed. Patch is a bit of software that allows you to apply patch/diff.
Title: SpamAssassin MySQL patch
Post by: pyther24 on January 07, 2009, 10:36:31 PM
Can you update this to work with release two?
Thanks!
Title: SpamAssassin MySQL patch
Post by: JohnDoh on January 08, 2009, 03:34:07 AM
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.
Title: SpamAssassin MySQL patch
Post by: JohnDoh on January 08, 2009, 05:04:34 PM
added a patch for the release version from the RC website, please see earlier post.
Title: SpamAssassin MySQL patch
Post by: pyther24 on January 10, 2009, 02:19:13 PM
Thanks! It works great, however it seems that you got rid of the 0.5 increments :-/
Title: SpamAssassin MySQL patch
Post by: JohnDoh on January 11, 2009, 05:17:11 AM
the option is still there, look in plugins/sauserperfs/config.inc.php for $sauserprefs_config['score_inc']. Title: SpamAssassin MySQL patch Post by: horfic on January 11, 2009, 03:05:53 PM 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! Title: SpamAssassin MySQL patch Post by: JohnDoh on January 12, 2009, 08:08:24 AM There is no plesk version but it should be easy to fix, just change the SQL statements in sauerprefs.inc and save_sauserprefs.inc. Title: SpamAssassin MySQL patch Post by: horfic on January 12, 2009, 12:52:30 PM 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. Title: SpamAssassin MySQL patch Post by: lacri on January 14, 2009, 07:22:08 AM 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 Title: Thanks Post by: siodseraph on January 14, 2009, 03:33:34 PM 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 Title: SpamAssassin MySQL patch Post by: JohnDoh on January 15, 2009, 03:33:24 AM 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. Title: SpamAssassin MySQL patch Post by: siodseraph on January 16, 2009, 02:26:44 PM 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. Title: SpamAssassin MySQL patch Post by: JohnDoh on January 18, 2009, 07:44:44 AM 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. Title: SpamAssassin MySQL patch Post by: lacri on January 19, 2009, 03:52:39 AM many thx works fine !!! Title: SpamAssassin MySQL patch Post by: maxxim on February 09, 2009, 12:11:49 PM 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 ? Title: SpamAssassin MySQL patch Post by: JohnDoh on February 09, 2009, 04:06:16 PM 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. Title: SpamAssassin MySQL patch Post by: maxxim on February 10, 2009, 06:32:51 AM 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. Code: [Select] 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] Title: SpamAssassin MySQL patch Post by: JohnDoh on February 10, 2009, 09:26:21 AM :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. Title: SpamAssassin MySQL patch Post by: maxxim on February 10, 2009, 10:09:13 AM 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.
Title: SpamAssassin MySQL patch
Post by: JohnDoh on February 11, 2009, 03:37:02 AM
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.
Title: SpamAssassin MySQL patch
Post by: sobia on February 17, 2009, 08:56:57 AM
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?
Title: SpamAssassin MySQL patch
Post by: JohnDoh on February 17, 2009, 10:36:34 AM
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.
Title: SpamAssassin MySQL patch
Post by: gmusco on March 08, 2009, 09:45:52 PM
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
Title: SpamAssassin MySQL patch
Post by: JohnDoh on March 09, 2009, 02:39:12 PM
Its
Code: [Select]
<span id=&quot;settingstabsauserprefs&quot; class=&quot;tablink&quot;><roundcube:button command=&quot;sauserprefs&quot; type=&quot;link&quot; label=&quot;sauserprefs&quot; title=&quot;sauserprefssettings&quot; class=&quot;tablink&quot; /></span>`
The title attribute is different from what you put but thats it, and you dont need that is in the file already.
Title: SpamAssassin MySQL patch
Post by: winnetou on September 29, 2011, 12:25:53 PM
Hello everybody ;)

I'm new here. I'm using SA-user-prefs plugin - great thing and I've found some typo and misspellings in localization pl_PL.inc.
I've made some corretions and new version is available at my SVN repo

http://svn://valhalla.org.pl/sa-user-prefs

Didn't know where I should report this so I choose forum ;)

Best regards from Poland