Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

The MessageBoardPlugin TWiki Plugin

by monsieur_champs (Curate)
on Jun 17, 2004 at 19:16 UTC ( [id://367736]=CUFP: print w/replies, xml ) Need Help??

I did it again.

Here is the MessageBoardPlugin, a TWiki Plugin for posting messages like a coffee message board, where users can post anything they need to. Hope that the fellows like reading and using this.

Code follows:

Filename: MessageBoardPlugin.txt 

%META:TOPICINFO{author="lcampos" date="1087408800" format="1.0" versio
+n="1.9"}%
---+ Message Board Plugin

This is a database based message board plugin for the <nop>TWiki syste
+m. Users can post and read messages from a database as if it was a me
+ssage board fixed on the wall.

---++ Syntax Rules

Use the TWiki Tag =%<nop>MESSAGE_BOARD%= with options:

| *Option Name* | *Value Range* | *Description* |
| =displayOnly= | ='0'= or ='1'= | When set to =1= causes Message Boar
+d to just display messages, not allowing users to post new messages. 
+When set to =0= will display posted messages and allow users to post 
+new messages. Defaults to =0=. |
| =messageOrder= | ='ASC'=, ='DESC'= | Chooses the order of messages, 
+=ASC= meaning newer messages showing first, =DESC= meaning older mess
+ages showing first. Defaults to =ASC=. |

---++ <nop>%TOPIC% Global Settings

    * One line description, shown in the %TWIKIWEB%.TextFormattingRule
+s topic:
        * Set SHORTDESCRIPTION = Database-Based message board plugin.

    * Color Settings
        * Set TABLE_HEAD_COLOR = %WEBCOLOR%

    * Icons and Image Settings
        * Set TRASH_CAN_ICON_LINK = <img src="%PUBURL%/%TWIKIWEB%/Mess
+ageBoardPlugin/trashcan.png" alt="Remove" border="0" />
        * Set PENCIL_ICON_LINK = <img src="%PUBURL%/%TWIKIWEB%/Message
+BoardPlugin/editicon.png" alt="Edit" border="0" />

    * Database Settings
        * Set DB_DRIVER = mysql
        * Set DB_SERVER = _IP or hostname here_
        * Set DB_SERVER_PORT = 3306
        * Set DB_DATABASE = message_board
        * Set DB_TABLE = message
        * Set DB_USER = board
        * Set DB_PASSWORD = _password_

    * Plugin Messages (so you can internationalize them):
    * Error opening connection to the database
        * Set MSG_DB_CONNECT_ERROR = %X% %RED% Error connecting to the
+ database%ENDCOLOR%
    * Error closing connection to the database
        * Set MSG_DB_CLOSE_ERROR = %X% %RED% Error closing the databas
+e connection%ENDCOLOR%
    * Error during query preparation
        * Set MSG_DB_PREPARE_ERROR = %X% %RED% Error preparing query%E
+NDCOLOR%
    * Error during data fetch
        * Set MSG_DB_FETCH_ERROR = %X% %RED% Error fetching data from 
+database%ENDCOLOR%
    * Error during query execution
        * Set MSG_DB_EXECUTE_ERROR = %X% %RED% Error executing query%E
+NDCOLOR%
    * No data found error
        * Set MSG_DB_NO_DATA_ERROR = %X% %RED% No data found%ENDCOLOR%
    * Update error message
        * Set MSG_DB_UPDATE_ERROR = %X% %RED% Error updating data%ENDC
+OLOR%
    * Insert error message 
        * Set MSG_DB_INSERT_ERROR = %X% %RED% Error inserting data%END
+COLOR%

    * Debug plugin: (See output in =data/debug.txt=)
        * Set DEBUG = 0

---++ Plugin Installation Instructions

__Note:__ You do not need to install anything on the browser to use th
+is plugin. The following instructions are for the administrator who i
+nstalls the plugin on the server where TWiki is running.

    * Download the ZIP file from the Plugin web (see below)
    * Unzip ==%TOPIC%.zip== in your twiki installation directory. Cont
+ent:
      | *File:* | *Description:* |
      | ==data/TWiki/%TOPIC%.txt== | Plugin topic |
      | ==data/TWiki/%TOPIC%.txt,v== | Plugin topic repository |
      | ==lib/TWiki/Plugins/%TOPIC%.pm== | Plugin Perl module |
      | ==pub/TWiki/MessageBoardPlugin/trashcan.png== | Trash Can Icon
+ |
      | ==pub/TWiki/MessageBoardPlugin/editicon.png== | Pencil and Pap
+er Icon |
    * Create a table into your database using (this is mysql SQL, the 
+only DB supported at the moment)
<pre>
         CREATE DATABASE message_board;

     CREATE TABLE message(
        id INT PRIMARY KEY AUTO_INCREMENT,
        author VARCHAR(50) NOT NULL,
        due DATETIME NOT NULL,
        posted DATETIME NOT NULL,
        msg TEXT NOT NULL,
        dropped ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N'
     )
</pre>
    * Create a new user and give it appropriated permissions:
<pre>
        GRANT SELECT, INSERT, UPDATE, DELETE
          ON messsage_board.message
          TO board IDENTIFIED BY 'b0aRd'
</pre>
    * Test if the plugin is correctly installed:
        * Open a Sandbox.MessageBoardTestArea and insert <code>%<nop>M
+ESSAGE_BOARD{ messageOrder="DESC" }%

[download]
into the topic. Preview, save and try to post a message. ---++ Plugin Info | Plugin Author: | TWiki:LuisCamposDeCarvalho | | Plugin Version: | 15/06/2004 (V1.000) | | Change History: |   | | 15 Jun 2004: | Initial version | | CPAN Dependencies: | [http://search.cpan.org/author/TIMB/DBI-1.42/DBI.pmDBI] [http://search.cpan.org/author/LDS/CGI.pm-3.05/CGI.pmCGI] | | Other Dependencies: | none | | Perl Version: | 5.6.1 | | Plugin Home: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC% | | Feedback: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC%Dev | __Related Topics:__ %TWIKIWEB%.TWikiPreferences, %TWIKIWEB%.TWikiPlugins, DefaultPlugin -- TWiki:LuisCamposDeCarvalho - 15 Jun 2004 %META:FILEATTACHMENT{name="trashcan.png" attr="" comment="" date="1087408473" path="C:\Documents and Settings\lcampos\Desktop\trashcan.png" size="779" user="lcampos" version="1.4"}% %META:FILEATTACHMENT{name="editicon.png" attr="" comment="" date="1087408517" path="C:\Documents and Settings\lcampos\Desktop\editicon.png" size="2017" user="lcampos" version="1.2"}% </code>

Filename: MessageBoardPlugin.pm 

# Plugin for TWiki Collaboration Platform, http://TWiki.org/
#
# Copyright (C) 2004 Luis Campos de Carvalho, monsieur_champs@yahoo.co
+m.br
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details, published at 
# http://www.gnu.org/copyleft/gpl.html
#

# =========================
package TWiki::Plugins::MessageBoardPlugin;
use strict;
use warnings;
use DBI;

# =========================
use vars qw(
        $web $topic $user $installWeb $VERSION $pluginName
        $debug %db %i18nMessage %color
    );

$VERSION = '1.000';
$pluginName = 'MessageBoardPlugin';

# =========================
sub initPlugin
{
    ( $topic, $web, $user, $installWeb ) = @_;

    # check for Plugins.pm versions
    if( $TWiki::Plugins::VERSION < 1 ) {
        TWiki::Func::writeWarning( "Version mismatch between $pluginNa
+me and Plugins.pm" );
        return 0;
    }

    # Get plugin debug flag
    $debug = TWiki::Func::getPreferencesFlag( "\U$pluginName\E_DEBUG" 
+);

    # Get plugin color settings
    %color =
      map { $_->[0] => &TWiki::Prefs::getPreferencesValue( $_->[1] ) }
    ( [ TABLE_HEAD   => "\U$pluginName\E_TABLE_HEAD_COLOR" ],
      [ REVERSE_LINE => "\U$pluginName\E_REVERSE_LINE_COLOR" ],
    );

    # Get plugin messages (so you can internationalize and customize
    # them at will )
    %i18nMessage =
      map { $_->[0] => &TWiki::Prefs::getPreferencesValue( $_->[1] ) }
    ( [ DB_CONNECT_ERROR => "\U$pluginName\E_MSG_DB_CONNECT_ERROR" ],
      [ DB_CLOSE_ERROR   => "\U$pluginName\E_MSG_DB_CLOSE_ERROR" ],
      [ DB_PREPARE_ERROR => "\U$pluginName\E_MSG_DB_PREPARE_ERROR" ],
      [ DB_FETCH_ERROR   => "\U$pluginName\E_MSG_DB_FETCH_ERROR" ],
      [ DB_EXECUTE_ERROR => "\U$pluginName\E_MSG_DB_EXECUTE_ERROR" ],
      [ DB_NO_DATA_ERROR => "\U$pluginName\E_MSG_DB_NO_DATA_ERROR" ],
      [ DB_UPDATE_ERROR => "\U$pluginName\E_MSG_DB_UPDATE_ERROR" ],
      [ DB_INSERT_ERROR => "\U$pluginName\E_MSG_DB_INSERT_ERROR" ],
    );

    # Get plugin database meta-data
    %db =
      map { $_->[0] => &TWiki::Prefs::getPreferencesValue( $_->[1] ) }
    ( [ driver   => "\U$pluginName\E_DB_DRIVER" ],
      [ host     => "\U$pluginName\E_DB_SERVER" ],
      [ port     => "\U$pluginName\E_DB_SERVER_PORT" ],
      [ database => "\U$pluginName\E_DB_DATABASE" ],
      [ table    => "\U$pluginName\E_DB_TABLE" ],
      [ user     => "\U$pluginName\E_DB_USER" ],
      [ passwd   => "\U$pluginName\E_DB_PASSWORD" ],
    );

    $db{dbh} = eval{
      DBI->connect( 'dbi:'.$db{driver}.
            ':database='.$db{database}.
            ';hostname='.$db{host}.
            ';port='.$db{port},
            $db{user},
            $db{passwd},
            { RaiseError => 1, PrintError => 0 } )
    };
    $db{connection_error} = $@ if $@;

    # Plugin correctly initialized
    # TWiki::Func::writeDebug( "- TWiki::Plugins::${pluginName}::initP
+lugin( $web.$topic ) is OK" ) if $debug;
    return 1;
}

# =========================
sub commonTagsHandler{
  ### my ( $text, $topic, $web ) = @_;
  # do not uncomment, use $_[0], $_[1]... instead

  # TWiki::Func::writeDebug("- ${pluginName}::commonTagsHandler( $_[2]
+.$_[1] )") if $debug;

  # This is the place to define customized tags and variables
  # Called by sub handleCommonTags, after %INCLUDE:"..."%

  $_[0] =~ s/(%MESSAGE_BOARD(?:{[^}]+})?%)/&board($1)/ge;
}

# =========================
sub display{
  my $msgOrder = shift;

  my $sth = eval {
    $db{dbh}->prepare( q{ SELECT id
                               , author
                               , DATE_FORMAT( due, '%T<br />%d/%m/%Y' 
+) as due
                               , DATE_FORMAT( posted, '%T<br />%d/%m/%
+Y' ) as posted
                               , msg
              FROM message
              WHERE dropped = 'N'
              AND due >= NOW()
              ORDER BY id
            } . uc $msgOrder
             );
  };
  return $i18nMessage{DB_PREPARE_ERROR}. ": $@." if $@;
  eval { $sth->execute; };
  return $i18nMessage{DB_EXECUTE_ERROR}. ": $@." if $@;
  my $result = eval { $sth->fetchall_arrayref( {} ); };
  return $i18nMessage{DB_FETCH_ERROR}. ": $@." if $@;

  my $trash_icon = &TWiki::Func::expandCommonVariables( &TWiki::Prefs:
+:getPreferencesValue( "\U$pluginName\E_TRASH_CAN_ICON_LINK" ), $topic
+, $web );
  my $pencil_icon = &TWiki::Func::expandCommonVariables( &TWiki::Prefs
+::getPreferencesValue( "\U$pluginName\E_PENCIL_ICON_LINK" ), $topic, 
+$web );

  # Build Message Board Table and return it.
  return
    qq{<table align="center" border="1" cellpadding="5" cellspacing="0
+">
  <tr valign="middle" align="center" bgcolor="$color{TABLE_HEAD}">
    <td> <b> Due&nbsp;Date </b> </td>
    <td> <b> Posted&nbsp;Date </b> </td>
    <td> <b> Author </b> </td>
    <td> <b> Drop &amp;<br />Change </b> </td>
    <td> <b> Message </b> </td>
  </tr> } .
    ( $result ? join "\n",
      map( '<tr valign="top" align="center"><td>' .
       $_->{due} . '</td><td>' . $_->{posted} . '</td><td> ' .
       $_->{author} . ' </td><td><a href="' .
       &TWiki::Func::getViewUrl( $web, $topic ) . '?message_id=' .
       $_->{id} . ';action=edit">' . $pencil_icon .
       '</a><a href="' . &TWiki::Func::getViewUrl( $web, $topic ) .
       '?message_id=' . $_->{id} . ';action=remove">' . $trash_icon .
       '</a></td><td align="left">' . $_->{msg} . '</td></tr>',
       @$result
     )
      : $i18nMessage{DB_NO_DATA_ERROR}
    ) . '</table>';
}

sub inputBox{
  # Don't uncomment, use @_ instead
  #   my $action_url = shift; # $_[0]
  #   my $msgid      = shift; # $_[1]
  #   my $action     = shift; # $_[2]
  return
qq{<form action="$_[0]" method="post">\n}.
($_[1]? qq{  <input type="hidden" name="message_id" value="$_[1]" />\n
+}:'').
qq{  <input type="hidden" name="action" value="$_[2]" />
  <input type="hidden" name="commit" value="1" />
  <table align="center" border="1" cellpadding="5" cellspacing="0">
    <tr valign="middle" align="center" bgcolor="$color{TABLE_HEAD}">
      <td colspan="2"> <b>}
  .($_[2] eq 'edit'? "Edit Message #$_[1]" : 'Compose New Message' ).
  qq{ </b> </td>
    </tr>
    <tr valign="middle" align="left" >
      <td> Author: </td>
      <td>
}.( $_[2] eq 'edit'? q{        <input type="text" name="author" size="
+40" }.
  ( $_[3]? qq{value="$_[3]->{author}"} : &TWiki::Func::getWikiName() )
 . qq{ />} : &TWiki::Func::getWikiName() ) . q{
      </td>
    </tr>
    <tr valign="middle" align="left">
      <td>Due Date: </td>
      <td>} . &gen_date_selector( 'due', ( $_[3]? @{$_[3]}{'day','mont
+h','year','hour','minute'} : (localtime)[3], (localtime)[4]+1, (local
+time)[5]+1900, 23, 59 ) ) . qq{
      </td>
    </tr>
    <tr valign="middle" align="left">
      <td>Message: </td>
      <td>
        <textarea rows="5" name="msg" cols="50">}.($_[3]? $_[3]->{msg}
+ : '' ).qq{</textarea>
      </td>
    </tr>
    <tr valign="middle" align="left">
      <td>
        <input type="checkbox" name="dropped" value="Y" }.($_[3] && $_
+[3]->{dropped} eq 'Y'? 'CHECKED':'' ).q{>&nbsp;Dropped.
      </td>
      <td align="right">
        } . ( $_[2] eq 'edit' ? qq{<a href="$_[0]">Cancel Edition</a> 
+&nbsp;&nbsp;&nbsp; } : '' ) . qq{<input type="submit" name="change" v
+alue="}. ( $_[2] eq 'edit' ? 'Change' : 'Post' ) . q{ Message">
      </td>
    </tr>
  </table>
</form>
};
}

# =========================
sub board{
  # Deal with database errors
  if( ! $db{dbh} && $db{connection_error} ){
    return $i18nMessage{DB_CONNECT_ERROR} . ": " . $db{connection_erro
+r};
  }

  # Recover tag, extract parameters
  # my $tag = shift;
  my( $displayOnly, $messageOrder ) =
    ( &TWiki::Func::extractNameValuePair( $_[0], 'displayOnly' ),
      &TWiki::Func::extractNameValuePair( $_[0], 'messageOrder' ) || '
+ASC' );

  my $q = &TWiki::Func::getCgiQuery();

  my $msgid = $q->param( 'message_id' );
  my $action = $q->param( 'action' );

  if( $action eq 'remove' ){
    ##################################################
    # Remove action
    ##################################################
    eval{
      $db{dbh}->do( q{ UPDATE message
               SET dropped = 'Y'
               WHERE id = ? },
            { RaiseError => 1 },
            $msgid
          )
    };
    return $i18nMessage{DB_EXECUTE_ERROR} . ': ' . $db{dbh}->errstr if
+ $@;

  }elsif( $action eq 'edit' ){
    ##################################################
    # Edit Action
    ##################################################
    if( $q->param( 'commit' ) ){
      ##################################################
      # Commit previous started edition
      ##################################################
      eval{ $db{dbh}->do( # DBI::do( $sql, \%attr, @bind )
             q{UPDATE message
               SET author  = ?
                 , due     = ?
                 , msg     = ?
                 , dropped = ?
               WHERE id = ?},
             { RaiseError => 1 },
             $q->param( 'author' ),
             sprintf( '%04d-%02d-%02d %02d:%02d:00',
                  map( $q->param( $_ ),
                       qw( due_year due_month due_day
                       due_hour due_minute )
                     )
                ),
             $q->param( 'msg' ),
             $q->param( 'dropped' )? 'Y' : 'N',
             $msgid
            )
      };
      return $i18nMessage{DB_UPDATE_ERROR}.': '.$@ if $@;
    }else{
      ##################################################
      # Start new edition
      ##################################################
      my $data;
      # recover data from existing record...
      eval{
    my $sth = $db{dbh}->prepare( # DBI::prepare( $sql )
                    q{SELECT author
                           , EXTRACT(DAY FROM due) AS day
                           , EXTRACT(MONTH FROM due) AS month
                           , EXTRACT(YEAR FROM due) AS year
                           , EXTRACT(HOUR FROM due) AS hour
                           , EXTRACT(MINUTE FROM due) AS minute
                           , msg
                           , dropped
                      FROM message
                      WHERE id = ?}
                   );
    my $result = $sth->execute( $msgid ); # DBI::execute( @bind )
    $data = $sth->fetchrow_hashref();
      };
      return $i18nMessage{DB_FETCH_ERROR} . ': ' . $@ if $@;

      # Build HTML
      return &inputBox( &TWiki::Func::getViewUrl( $web, $topic ),
            $msgid, 'edit', $data );
    }
  }elsif( $action eq 'new' ){
    ##################################################
    # Post New Message
    ##################################################
    if( $q->param( 'commit' ) ){
      ##################################################
      # Commit previous started edition
      ##################################################
      eval{
    $db{dbh}->do( # DBI::do( $sql, \%attr, @bind )
             q{INSERT INTO message( author, due, posted, msg, dropped 
+)
               VALUES( ?, ?, NOW(), ?, ? ) },
             { RaiseError => 1 },
             &TWiki::Func::getWikiName(),
             sprintf( '%04d-%02d-%02d %02d:%02d:00',
                  map( $q->param( $_ ),
                   qw( due_year due_month due_day
                       due_hour due_minute )
                 )
                ),
             $q->param( 'msg' ),
             $q->param( 'dropped' )? 'Y' : 'N',
            )
      };
      return $i18nMessage{DB_INSERT_ERROR}.': '.$@ if $@;
    }
  }

  ##################################################
  # Execute display action
  ##################################################
  return '<table align="center" border="0" cellpadding="0" cellspacing
+="2">'.
    '<tr align="center">'.
      '<td>'.&display( $messageOrder ).'</td></tr>'.
    ( $displayOnly? '' : '<tr align="center"><td>' . &inputBox( &TWiki
+::Func::getViewUrl( $web, $topic ), undef, 'new', undef ) . '</td></t
+r>' ).
      '</table>';
}

sub gen_date_selector{
  # my $name = shift;
  # # Those are for the 'selected' HTML tag:
  # my $ref_day = shift;    # numeric current day (1..31)
  # my $ref_month = shift;  # numeric current month (1..12)
  # my $ref_year = shift;   # numeric current year (0..MAX_BIGINT)
  # my $ref_hour = shift;   # numeric current hour (0..23)
  # my $ref_minute = shift; # numeric current minute (0..59)
  join '',
    ( qq{<select name="$_[0]_day">\n },
      (
       # generate day 'option' tags
       map {
     my $d = sprintf '%02d', $_;
     qq'<option value="$d"' .
       ( $d == $_[1] ? ' selected' : '') .
         ">$d</option>\n";
       } 1..31
      ),
      qq{ </select> / <select name="$_[0]_month">\n},
      (
       # generate month 'option' tags
       map {
     my $m = sprintf '%02d', $_;
     qq'<option value="$m"' .
       ( $m == $_[2] ? ' selected' : '' ) .
         ">$m</option>\n";
       } 1..12
      ),
      qq{ </select> / <select name="$_[0]_year">\n},
      (
       # generate year 'option' tags
       map {
     my $y = sprintf '%04d', $_;
     qq'<option value="$y"' .
       ( $y == $_[3] ? ' selected' : '' ) .
         ">$y</option>\n";
       } ( (localtime)[5]+1900 ) .. ( (localtime)[5]+1902 )
      ),
      qq{</select>&nbsp;<select name="$_[0]_hour">},
      (
       # generate hour 'option' tags
       map{
     my $h = sprintf '%02d', $_;
     qq'<option value="$h"' .
       ( $h == $_[4] ? ' selected' : '' ) .
         ">$h</option>\n";
       } 0..23
      ),
      qq{</select>:<select name="$_[0]_minute">},
      (
       # generate minute 'option' tags
       map{
     my $m = sprintf '%02d', $_;
     qq'<option value="$m"' .
       ( $m == $_[5] ? ' selected' : '' ) .
         ">$m</option>\n";
       } 0..59
      ),
      qq{</select>:00},
    );
}
1;

[download]

Threre are also two images that are needed to display query results properly. You can fetch them from TWiki's website.

-- monsieur_champs

Back to Cool Uses for Perl

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: CUFP [id://367736]
Approved by neniro
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others scrutinizing the Monastery: (2)
As of 2024-04-19 01:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found