Sophie

Sophie

distrib > Mandriva > 8.2 > i586 > media > contrib > by-pkgid > 968fb36a1d13190d5ed9d2ee3bf64b87 > files > 92

sympa-3.2.1-4mdk.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">

<!--Converted with LaTeX2HTML 99.2beta8 (1.42)
original version by:  Nikos Drakos, CBLU, University of Leeds
* revised and updated by:  Marcus Hennecke, Ross Moore, Herb Swan
* with significant contributions from:
  Jens Lippmann, Marek Rouchal, Martin Wilck and others -->
<HTML>
<HEAD>
<TITLE>6. Using Sympa with a relational database</TITLE>
<META NAME="description" CONTENT="6. Using Sympa with a relational database">
<META NAME="keywords" CONTENT="sympa">
<META NAME="resource-type" CONTENT="document">
<META NAME="distribution" CONTENT="global">

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="LaTeX2HTML v99.2beta8">
<META HTTP-EQUIV="Content-Style-Type" CONTENT="text/css">

<LINK REL="STYLESHEET" HREF="sympa.css">

<LINK REL="next" HREF="node8.html">
<LINK REL="previous" HREF="node6.html">
<LINK REL="up" HREF="sympa.html">
<LINK REL="next" HREF="node8.html">
</HEAD>

<BODY TEXT="#000000" BGCOLOR="#ffffff">
<!--Navigation Panel-->
<A NAME="tex2html571"
  HREF="node8.html">
<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A> 
<A NAME="tex2html565"
  HREF="sympa.html">
<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A> 
<A NAME="tex2html559"
  HREF="node6.html">
<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A> 
<A NAME="tex2html567"
  HREF="node1.html">
<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A> 
<A NAME="tex2html569"
  HREF="node17.html">
<IMG WIDTH="43" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="index" SRC="index.png"></A> 
<BR>
<B> Next:</B> <A NAME="tex2html572"
  HREF="node8.html">7. Sympa with S/MIME and HTTPS</A>
<B> Up:</B> <A NAME="tex2html566"
  HREF="sympa.html">Sympa Mailing Lists Management Software</A>
<B> Previous:</B> <A NAME="tex2html560"
  HREF="node6.html">5. WWSympa</A>
 &nbsp <B>  <A NAME="tex2html568"
  HREF="node1.html">Contents</A></B> 
 &nbsp <B>  <A NAME="tex2html570"
  HREF="node17.html">Index</A></B> 
<BR>
<BR>
<!--End of Navigation Panel-->
<!--Table of Child-Links-->
<A NAME="CHILD_LINKS"><STRONG>Subsections</STRONG></A>

<UL>
<LI><A NAME="tex2html573"
  HREF="node7.html#SECTION00710000000000000000">6.1 Prerequisites</A>
<LI><A NAME="tex2html574"
  HREF="node7.html#SECTION00720000000000000000">6.2 Installing PERL modules</A>
<LI><A NAME="tex2html575"
  HREF="node7.html#SECTION00730000000000000000">6.3 Creating a sympa DataBase</A>
<UL>
<LI><A NAME="tex2html576"
  HREF="node7.html#SECTION00731000000000000000">6.3.1 Database structure</A>
<LI><A NAME="tex2html577"
  HREF="node7.html#SECTION00732000000000000000">6.3.2 Database creation</A>
</UL>
<LI><A NAME="tex2html578"
  HREF="node7.html#SECTION00740000000000000000">6.4 Importing subscribers data</A>
<UL>
<LI><A NAME="tex2html579"
  HREF="node7.html#SECTION00741000000000000000">6.4.1 Importing data from a text file</A>
<LI><A NAME="tex2html580"
  HREF="node7.html#SECTION00742000000000000000">6.4.2 Importing data from subscribers files</A>
</UL>
<LI><A NAME="tex2html581"
  HREF="node7.html#SECTION00750000000000000000">6.5 <I>Sympa</I> configuration</A>
</UL>
<!--End of Table of Child-Links-->
<HR>

<H1><A NAME="SECTION00700000000000000000"></A>
<A NAME="sec-rdbms"></A>
<BR>
6. Using <I>Sympa</I> with a relational database
</H1>

<P>
It is possible for <I>Sympa</I> to store its user information using a
relational database. Currently you can use one of the following
RDBMS : MySQL, PostgreSQL, Oracle, Sybase. Interfacing with other RDBMS
requires only a few changes in the code, since the API used, 
<A NAME="tex2html24"
  HREF="http://www.symbolstone.org/technology/perl/DBI/">DBI</A>
(DataBase Interface), has DBD (DataBase Drivers) for many RDBMS.

<P>

<H1><A NAME="SECTION00710000000000000000">
6.1 Prerequisites</A>
</H1>

<P>
You need to have a DataBase System installed (not necessarily 
on the same host as <I>Sympa</I>), and the client libraries for that
Database installed on the <I>Sympa</I> host ; provided, of course, that
a PERL DBD (DataBase Driver) is available for your chosen RDBMS!
Check the <A NAME="tex2html25"
  HREF="http://www.symbolstone.org/technology/perl/DBI/">DBI Module Availability</A>.

<P>

<H1><A NAME="SECTION00720000000000000000">
6.2 Installing PERL modules</A>
</H1>

<P>
<I>Sympa</I> will use DBI to communicate with the database system and
therefore requires the DBD for your database system. DBI and 
DBD::YourDB (Msql-Mysql-modules for MySQL) are distributed as 
CPAN modules. Refer to &nbsp;<A HREF="#Install_other_required_CPAN_modules"><IMG  ALIGN="BOTTOM" BORDER="1" ALT="[*]" SRC="crossref.png"></A>, page&nbsp;<A HREF="#Install_other_required_CPAN_modules"><IMG  ALIGN="BOTTOM" BORDER="1" ALT="[*]" SRC="crossref.png"></A> for installation
details of these modules.

<P>

<H1><A NAME="SECTION00730000000000000000">
6.3 Creating a sympa DataBase</A>
</H1>

<P>

<H2><A NAME="SECTION00731000000000000000">
6.3.1 Database structure</A>
</H2>

<P>
The sympa database structure is slightly different from the
structure of a <A NAME="3728"></A><TT>subscribers</TT> file. A <A NAME="3731"></A><TT>subscribers</TT>
file is a text file based on paragraphs (similar to 
the <A NAME="3734"></A><TT>config</TT> file) ; each paragraph completely describes 
a subscriber. If somebody is subscribed to two lists, he/she 
will appear in both subscribers files.

<P>
The DataBase distinguishes information relative to a person (e-mail,
real name, password) and his/her subscription options (list
concerned, date of subscription, reception option, visibility 
option). This results in a separation of the data into two tables :
the user_table and the subscriber_table, linked by a user/subscriber e-mail.

<P>

<H2><A NAME="SECTION00732000000000000000">
6.3.2 Database creation</A>
</H2>

<P>
The <A NAME="3737"></A><TT>create_db</TT> script below will create the sympa database for 
you. You can find it in the <A NAME="3740"></A><TT>script/</TT> directory of the 
distribution (currently scripts are available for MySQL, PostgreSQL, Oracle and Sybase).

<P><PRE>
## MySQL Database creation script
CREATE DATABASE sympa;

## Connect to DB 
\r sympa

CREATE TABLE user_table (
  	email_user          	varchar (100) NOT NULL,
  	gecos_user          	varchar (150),
  	password_user		varchar (40),
	cookie_delay_user	int,
	lang_user		varchar (10),
	PRIMARY KEY (email_user)
);

CREATE TABLE subscriber_table (
  	list_subscriber       	varchar (50) NOT NULL,
	user_subscriber		varchar (100) NOT NULL,
	date_subscriber		datetime NOT NULL,
	update_subscriber	datetime,
	visibility_subscriber	varchar (20),
	reception_subscriber	varchar (20),
	bounce_subscriber	varchar (30),
	comment_subscriber	varchar (150),
	PRIMARY KEY (list_subscriber, user_subscriber),
	INDEX (user_subscriber,list_subscriber)
);
</PRE>

<P>
You can execute the script using a simple SQL shell such as
mysql or psql.

<P>
Example:

<P><PRE>
# mysql  &lt; create_db.mysql
</PRE>

<P>

<H1><A NAME="SECTION00740000000000000000">
6.4 Importing subscribers data</A>
</H1>

<P>

<H2><A NAME="SECTION00741000000000000000">
6.4.1 Importing data from a text file</A>
</H2>

<P>
You can import subscribers data into the database from a text file having
one entry per line : the first field is an e-mail address, the second (optional) 
field is the free form name.  Fields are spaces-separated.

<P>
Example:<PRE>
## Data to be imported
## email        gecos
john.steward@some.company.com           John - accountant
mary.blacksmith@another.company.com     Mary - secretary
</PRE>

<P>
To import data into the database :

<P><PRE>
cat /tmp/my_import_file | sympa.pl --import=my_list
</PRE>

<P>
(see <A HREF="node4.html#sympa.pl">3.6</A>, page&nbsp;<A HREF="node4.html#sympa.pl"><IMG  ALIGN="BOTTOM" BORDER="1" ALT="[*]" SRC="crossref.png"></A>).

<P>

<H2><A NAME="SECTION00742000000000000000">
6.4.2 Importing data from subscribers files</A>
</H2>

<P>
If a mailing list was previously setup to store subscribers into 
<A NAME="3743"></A><TT>subscribers</TT> file (the default mode in versions older then 2.2b) 
you can load subscribers data into the sympa database. The simple way
is to edit the list configuration using <A NAME="3746"></A><I>WWSympa</I> (this requires listmaster 
privileges) and change the data source from <B>file</B> to <B>database</B>
; subscribers data will be loaded into the database at the same time.

<P>
If the subscribers file is too big, a timeout may occur with the FastCGI
(You can set longer timeout with <TT>-idle-timeout</TT> option of
<TT>FastCgiServer</TT> Apache configuration directive). Then you should
use <A NAME="3749"></A><TT>load_subscribers.pl</TT> script.

<P>

<H1><A NAME="SECTION00750000000000000000">
6.5 <I>Sympa</I> configuration</A>
</H1>

<P>
To store subscriber information in your newly created
database, you first need to tell <I>Sympa</I> what kind of
database to work with, then you must configure
your list to access the database.

<P>
You define the database source in <A NAME="3754"></A><TT>sympa.conf</TT> :
<A NAME="3757"></A><TT>db_type</TT>, <A NAME="3760"></A><TT>db_name</TT>, 
<A NAME="3763"></A><TT>db_host</TT>, <A NAME="3766"></A><TT>db_user</TT>, 
<A NAME="3769"></A><TT>db_passwd</TT>.

<P>
If you are interfacing <I>Sympa</I> with an Oracle database, 
<A NAME="3773"></A><TT>db_name</TT> is the SID.

<P>
All your lists are now configured to use the database,
unless you set list parameter <A NAME="3776"></A><TT>user_data_source</TT> 
to <B>file</B> or <B>include</B>. 

<P>
<I>Sympa</I> will now extract and store user
information for this list using the database instead of the
<A NAME="3780"></A><TT>subscribers</TT> file. Note however that subscriber information is 
dumped to <A NAME="3783"></A><TT>subscribers.db.dump</TT> at every shutdown, 
to allow a manual rescue restart (by renaming subscribers.db.dump to
subscribers and changing the user_data_source parameter), if ever the
database were to become inaccessible.

<P>

<HR>
<!--Navigation Panel-->
<A NAME="tex2html571"
  HREF="node8.html">
<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A> 
<A NAME="tex2html565"
  HREF="sympa.html">
<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A> 
<A NAME="tex2html559"
  HREF="node6.html">
<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A> 
<A NAME="tex2html567"
  HREF="node1.html">
<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A> 
<A NAME="tex2html569"
  HREF="node17.html">
<IMG WIDTH="43" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="index" SRC="index.png"></A> 
<BR>
<B> Next:</B> <A NAME="tex2html572"
  HREF="node8.html">7. Sympa with S/MIME and HTTPS</A>
<B> Up:</B> <A NAME="tex2html566"
  HREF="sympa.html">Sympa Mailing Lists Management Software</A>
<B> Previous:</B> <A NAME="tex2html560"
  HREF="node6.html">5. WWSympa</A>
 &nbsp <B>  <A NAME="tex2html568"
  HREF="node1.html">Contents</A></B> 
 &nbsp <B>  <A NAME="tex2html570"
  HREF="node17.html">Index</A></B> 
<!--End of Navigation Panel-->
<ADDRESS>
root
2001-06-05
</ADDRESS>
</BODY>
</HTML>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional