[xwiki-users] Forum system that integrates with XWiki?

[Trevor Cox]

My procmail mailing list does not require any modification to XWiki.  It 
uses the existing capability of the mail plug-in to display messages 
from an IMAP store. When messages arrive, I chose to access the database 
directly using JDBC rather than invoke something like "wget 
http://www.mywiki.com/..." for performance and reliability. I have seen 
other people are interested in how to access the database from outside 
of XWiki, so maybe this would be a useful example.


The command line java program is invoked with this shell script:
 #!/bin/sh
 java -cp 
/usr/local/tomcat/common/lib/mysql-connector-java-3.1.7-bin.jar:. \
    XWikiMail $1 $2 $3 $4


The command-line java program:

//
// A command-line program to extract the list of email addresses for an 
XWiki Group.
// Trevor Cox
// Nov 9, 2005
// www.skahasoftware.com
//

import java.net.*;
import java.io.*;
import java.util.*;
import java.sql.*;

public class XWikiMail {

    public XWikiMail() {}

    public static void main(String[] params)
        throws java.lang.Exception
    {
        if(params.length != 2) {
            System.out.println(
                "Usage: xwikimail <database> <group>\n");
                throw new java.lang.Exception("Incorrect parameters");
        }

        String site=params[0];
        String group=params[1];

        String url   = "jdbc:mysql://localhost/" + site;
        String dbuser = "xwiki";
        String dbpwd =  "xwiki";

        String query =
            "select xws_value from xwikiobjects join xwikistrings on 
xwo_id=xws_id "
            + " where xwo_name='XWiki." + group + "' and "
            + " xwo_classname='XWiki.XWikiGroups';";
                       
        Class.forName  ("com.mysql.jdbc.Driver");

        Connection con = DriverManager.getConnection(url, dbuser, dbpwd);

        Statement stmt = con.createStatement ();
        ResultSet rs = stmt.executeQuery (query);

        while ( rs.next() ) {
            String member = rs.getString(1);

            String query2 =
        "select xws_value from xwikiobjects join xwikistrings on xwo_id 
= xws_id"
        + " where xwo_name='" + member + "' and 
xwo_classname='XWiki.XWikiUsers' and"
        + " xws_name='email';";

            Statement stmt2 = con.createStatement ();
            ResultSet rs2 = stmt2.executeQuery (query2);
            while ( rs2.next() ) {
                System.out.print(rs2.getString(1)+" ");
            }
        }

        rs.close();
        stmt.close();
        con.close();
    }
} 

Here's a procmail rule that distributes mail sent to the mailing list
<group>@<wiki>.xwiki.com (I've simplified my original script for clarity).

:0
* ^(To|Cc):.*\/[a-z]+@[a-z]+\.xwiki\.com
{
        GROUP=`echo $MATCH | sed s/@.*//`
        SITE=`echo $MATCH | perl -pe "s/.*@([^\.]*).*/\1/;"`
        XWIKISENDMAIL=`xwikimail $SITE $GROUP`

        :0 c
        $SITE.$GROUP           # Store messages in folder like 
mywiki.mygroup

        :0
        ! $XWIKISENDMAIL
}


Trevor Cox
Skaha Software


jeremi joslin wrote:

> Hi,
> Nice, can you send us also a diff with your modification on the mail
> plugin. Can we integrate them in Xwiki?
>
> Jeremi
>
>
> On 7/17/06, Trevor Cox <xwiki at skahasoftware.com> wrote:
>
>> I have integrated a mailing list with XWiki. The mailing list is built
>> with procmail, which invokes a command line Java program to extract the
>> email addresses from the XWiki database and forward each message. All
>> the emails are archived in a IMAP folder which is viewed by this
>> template code:
>>
>>
>> <h3 class="heading-1">Mail Archive</h3>
>>
>> #set($user = "xxx")
>> #set($pass = "xxx")
>> #set($fldr = "${context.database}.listname")
>>
>> #set($store = $xwiki.mail.getStore("imap"))
>> $store.connect("localhost", $user, $pass)
>> #set($fldr = $store.getFolder($fldr))
>> $fldr.open(1)
>>
>> #set($msgnum = $parameterParser.getInt("msg",0))
>> #if($msgnum != 0)
>>
>> <a href="?">Message Index</a>
>>
>> \\
>>
>> #set($mail = $fldr.getMessage($msgnum))
>>
>> From:
>> #foreach($from in $mail.getHeader("from")) 
>> $xwiki.getFormEncoded($from) #end
>> \\
>> To:
>> #foreach($from in $mail.getHeader("to")) $xwiki.getFormEncoded($from) 
>> #end
>> \\
>> Subject: $xwiki.getFormEncoded($mail.subject) \\
>> Message:
>>
>>  #if($mail.isMimeType("text/plain") || $mail.isMimeType("text/html"))
>>   $mail.getContent()
>>  #else
>>   (Unable to display)
>>  #end
>>
>> #else
>>
>> The following mail was sent to the list
>> during the past year:
>>
>> #set($count = $fldr.messageCount)
>> Showing $count of $count messages:
>>
>> <table class="wiki-table" cellpadding="0" cellspacing="0" border="0">
>> <tr class="table-odd">
>> <th>Subject</th><th>From</th><th>Date</th>
>> </tr>
>> #foreach ($i in [$fldr.messageCount..1])
>> #set($mail = $fldr.getMessage($i))
>> <tr>
>> <td>
>> <a
>> href="?msg=$mail.getMessageNumber()">$xwiki.getFormEncoded($mail.subject)</a> 
>>
>> </td><td>
>> #set($froms = $mail.getHeader("from"))
>> #foreach($from in $froms) $xwiki.getFormEncoded($from) #end
>> </td><td>
>> #if($!mail.sentDate)
>> $formatter.formatShortDateTime($!mail.sentDate)
>> #end
>> </td>
>> </tr>
>> #end
>> </table>
>>
>> #end
>>
>> $store.close()
>>
>>
>>
>> Adrian Hall wrote:
>>
>> > Would you care to share the code?
>> >
>> >
>> >
>> > Thanks,
>> >
>> >
>> >
>> > -Adrian
>> >
>>
>>
>> -- 
>> Trevor Cox
>> skahasoftware.com
>> Vancouver, Canada
>>
>>
>>
>>
>>
>> -- 
>> You receive this message as a subscriber of the 
>> xwiki-users at objectweb.org mailing list.
>> To unsubscribe: mailto:xwiki-users-unsubscribe at objectweb.org
>> For general help: mailto:sympa at objectweb.org?subject=help
>> ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>>
>>
>>
>
>
>------------------------------------------------------------------------
>
>
>--
>You receive this message as a subscriber of the xwiki-users at objectweb.org mailing list.
>To unsubscribe: mailto:xwiki-users-unsubscribe at objectweb.org
>For general help: mailto:sympa at objectweb.org?subject=help
>ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
>  
>


-- 
Trevor Cox
skahasoftware.com
Vancouver, Canada

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.xwiki.org/pipermail/users/attachments/20060717/57e59d9e/attachment-0001.html