File: node9.html

package info (click to toggle)
sympa 5.2.3-1.2%2Betch1
links: PTS
area: main
in suites: etch
size: 21,384 kB
ctags: 3,566
sloc: perl: 27,368; sh: 3,990; makefile: 1,366; ansic: 226; php: 105; lisp: 32
file content (830 lines) | stat: -rw-r--r-- 28,337 bytes
parent folder | download | duplicates (2)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">

<!--Converted with LaTeX2HTML 2002-2-1 (1.70)
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>8. Sympa and its database</TITLE>
<META NAME="description" CONTENT="8. Sympa and its 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 v2002-2-1">
<META HTTP-EQUIV="Content-Style-Type" CONTENT="text/css">

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

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

<BODY TEXT="#000000" BGCOLOR="#ffffff">
<!--Navigation Panel-->
<A NAME="tex2html1031"
  HREF="node10.html">
<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A> 
<A NAME="tex2html1025"
  HREF="sympa.html">
<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A> 
<A NAME="tex2html1019"
  HREF="node8.html">
<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A> 
<A NAME="tex2html1027"
  HREF="node1.html">
<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A> 
<A NAME="tex2html1029"
  HREF="node30.html">
<IMG WIDTH="43" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="index" SRC="index.png"></A> 
<BR>
<B> Next:</B> <A NAME="tex2html1032"
  HREF="node10.html">9. WWSympa, Sympa's web interface</A>
<B> Up:</B> <A NAME="tex2html1026"
  HREF="sympa.html">Sympa Mailing Lists Management Software version</A>
<B> Previous:</B> <A NAME="tex2html1020"
  HREF="node8.html">7. sympa.conf parameters</A>
 &nbsp; <B>  <A NAME="tex2html1028"
  HREF="node1.html">Contents</A></B> 
 &nbsp; <B>  <A NAME="tex2html1030"
  HREF="node30.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="tex2html1033"
  HREF="node9.html#SECTION00910000000000000000">8.1 Prerequisites</A>
<LI><A NAME="tex2html1034"
  HREF="node9.html#SECTION00920000000000000000">8.2 Installing PERL modules</A>
<LI><A NAME="tex2html1035"
  HREF="node9.html#SECTION00930000000000000000">8.3 Creating a sympa DataBase</A>
<UL>
<LI><A NAME="tex2html1036"
  HREF="node9.html#SECTION00931000000000000000">8.3.1 Database structure</A>
<LI><A NAME="tex2html1037"
  HREF="node9.html#SECTION00932000000000000000">8.3.2 Database creation</A>
</UL>
<BR>
<LI><A NAME="tex2html1038"
  HREF="node9.html#SECTION00940000000000000000">8.4 Setting database privileges</A>
<LI><A NAME="tex2html1039"
  HREF="node9.html#SECTION00950000000000000000">8.5 Importing subscribers data</A>
<UL>
<LI><A NAME="tex2html1040"
  HREF="node9.html#SECTION00951000000000000000">8.5.1 Importing data from a text file</A>
<LI><A NAME="tex2html1041"
  HREF="node9.html#SECTION00952000000000000000">8.5.2 Importing data from subscribers files</A>
</UL>
<BR>
<LI><A NAME="tex2html1042"
  HREF="node9.html#SECTION00960000000000000000">8.6 Management of the include cache</A>
<LI><A NAME="tex2html1043"
  HREF="node9.html#SECTION00970000000000000000">8.7 Extending database table format</A>
<LI><A NAME="tex2html1044"
  HREF="node9.html#SECTION00980000000000000000">8.8 <I>Sympa</I> configuration</A>
</UL>
<!--End of Table of Child-Links-->
<HR>

<H1><A NAME="SECTION00900000000000000000"></A>
<A NAME="sec-rdbms"></A>
<BR>
8. <I>Sympa</I> and its database
</H1>

<P>
Most basic feature of <I>Sympa</I> will work without a RDBMS, but WWSympa and bounced require a relational database. 
Currently you can use one of the following RDBMS : MySQL, SQLite, PostgreSQL, Oracle, Sybase. Interfacing with other RDBMS
requires only a few changes in the code, since the API used, <A NAME="tex2html18"
  HREF="http://www.symbolstone.org/technology/perl/DBI/">DBI</A>
(DataBase Interface), has DBD (DataBase Drivers) for many RDBMS.

<P>
Sympa stores three kind of information in the database, each in one table :

<UL>
<LI>User preferences and passwords are stored in the <A NAME="8604"></A>user_table  table

<P>
</LI>
<LI>List subscription informations are stored in the <A NAME="8605"></A>subscriber_table  table, along with subscription options.
  This table also contains the cache for included users (if using include2 mode).

<P>
</LI>
<LI>List administrative informations are stored in the <A NAME="8606"></A>admin_table  table if using include2 mode, along with owner and editor options. This table also contains the cache for included owners and editors.

<P>
</LI>
</UL>

<P>

<H1><A NAME="SECTION00910000000000000000">
8.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="tex2html19"
  HREF="http://www.symbolstone.org/technology/perl/DBI/"><TT>DBI</TT> Module Availability</A>.

<P>

<H1><A NAME="SECTION00920000000000000000">
8.2 Installing PERL modules</A>
</H1>

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

<P>

<H1><A NAME="SECTION00930000000000000000">
8.3 Creating a sympa DataBase</A>
</H1>

<P>

<H2><A NAME="SECTION00931000000000000000">
8.3.1 Database structure</A>
</H2>

<P>
The sympa database structure is slightly different from the
structure of a <A NAME="8619"></A><TT>subscribers</TT> file. A <A NAME="8622"></A><TT>subscribers</TT>
file is a text file based on paragraphs (similar to 
the <A NAME="8625"></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>
The table concerning owners and editors, the admin_table, is made on the same way as 
the subscriber_table but is used only in include2 mode. It constains owner and editor 
options (list concerned, administrative role, date of ``subscription'', reception option, 
private info, gecos and profile option for owners).

<P>

<H2><A NAME="SECTION00932000000000000000">
8.3.2 Database creation</A>
</H2>

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

<P>

<UL>
<LI>MySQL database creation script
<BR>	<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),
	attributes_user		varchar(255),
	PRIMARY KEY (email_user)
);

CREATE TABLE subscriber_table (
  	list_subscriber       	varchar (50) NOT NULL,
	user_subscriber		varchar (100) NOT NULL,
	robot_subscriber	varchar (80) NOT NULL,
	date_subscriber		datetime NOT NULL,
	update_subscriber	datetime,
	visibility_subscriber	varchar (20),
	reception_subscriber	varchar (20),
	topics_subscriber	varchar (200),
	bounce_subscriber	varchar (35),
	bounce_score_subscriber smallint (6),
	bounce_address_subscriber	varchar (100),
	comment_subscriber	varchar (150),
	subscribed_subscriber 	int(1),
	included_subscriber 	int(1),
	include_sources_subscriber varchar(50),
	PRIMARY KEY (list_subscriber, user_subscriber, robot_subscriber),
	INDEX (user_subscriber,list_subscriber,robot_subscriber)
);

CREATE TABLE admin_table (
	list_admin 		varchar(50) NOT NULL,
 	user_admin 		varchar(100) NOT NULL,
 	robot_admin 		varchar(80) NOT NULL,
	role_admin 		enum('listmaster','owner','editor') NOT NULL,
	date_admin 		datetime NOT NULL,
	update_admin 		datetime,
	reception_admin 	varchar(20),
	comment_admin 		varchar(150),
	subscribed_admin  	int(1),
	included_admin  	int(1),
	include_sources_admin  	varchar(50),
	info_admin   		varchar(150),
	profile_admin  		enum('privileged','normal'),
	PRIMARY KEY (list_admin, user_admin, robot_admin, role_admin),
	INDEX (list_admin, user_admin,robot_admin,role_admin)
);
 
CREATE TABLE netidmap_table (
        netid_netidmap              varchar (100) NOT NULL,
	serviceid_netidmap	    varchar (100) NOT NULL,
	robot_netidmap	            varchar (80) NOT NULL,
        email_netidmap              varchar (100),
        PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
);

# CREATE TABLE log_table (
# 	id			INT UNSIGNED DEFAULT 0 NOT NULL auto_increment,
# 	date			int NOT NULL,
# 	pid			int,
# 	process			enum ('task','archived','sympa','wwsympa','bounced'),
#   	email_user          	varchar (100),
# 	auth			enum ('smtp','md5','smime','null'),	
# 	ip			varchar (15),
#   	operation		varchar (40),
# 	list			varchar (50),
# 	robot			varchar (60),
# 	arg			varchar (100),
# 	status			varchar (100),
# 	subscriber_count	int,
# 	PRIMARY KEY (id),
# 	INDEX (date),	
# 	INDEX (robot),
# 	INDEX (list),
# 	INDEX (email_user)
# );
</PRE>

<P>
</LI>
<LI>SQLiteL database creation script
<BR>	<PRE>
CREATE TABLE user_table (
  	email_user          	varchar (100) NOT NULL,
  	gecos_user          	varchar (150),
  	password_user		varchar (40),
	cookie_delay_user	integer,
	lang_user		varchar (10),
	attributes_user		varchar(255),
	PRIMARY KEY (email_user)
);

CREATE TABLE subscriber_table (
  	list_subscriber       	varchar (50) NOT NULL,
	user_subscriber		varchar (100) NOT NULL,
	robot_subscriber	varchar (80) NOT NULL,
	date_subscriber		timestamp NOT NULL,
	update_subscriber	timestamp,
	visibility_subscriber	varchar (20),
	reception_subscriber	varchar (20),
	topics_subscriber       varchar (200),
	bounce_subscriber	varchar (35),
	bounce_address_subscriber	varchar (100),
	comment_subscriber	varchar (150),
	subscribed_subscriber 	boolean,
	included_subscriber 	boolean,
	include_sources_subscriber varchar(50),
	bounce_score_subscriber integer,
	PRIMARY KEY (list_subscriber, user_subscriber, robot_subscriber)
);
CREATE INDEX subscriber_idx ON subscriber_table (user_subscriber,list_subscriber,robot_subscriber);

CREATE TABLE admin_table (
	list_admin 		varchar(50) NOT NULL,
 	user_admin 		varchar(100) NOT NULL,
 	robot_admin 		varchar(80) NOT NULL,
	role_admin 		varchar(15) NOT NULL,
	date_admin 		timestamp NOT NULL,
	update_admin 		timestamp,
	reception_admin 	varchar(20),
	comment_admin 		varchar(150),
	subscribed_admin  	boolean,
	included_admin  	boolean,
	include_sources_admin  	varchar(50),
	info_admin   		varchar(150),
	profile_admin  		varchar(15),
	PRIMARY KEY (list_admin, user_admin, robot_admin, role_admin)
);
CREATE	INDEX admin_idx ON admin_table(list_admin, user_admin, robot_admin, role_admin);

CREATE TABLE netidmap_table (
        netid_netidmap              varchar (100) NOT NULL,
	serviceid_netidmap	    varchar (100) NOT NULL,
	robot_netidmap	            varchar (80) NOT NULL,
        email_netidmap              varchar (100),
        PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
);
CREATE	INDEX netidmap_idx ON netidmap_table(netid_netidmap, serviceid_netidmap, robot_netidmap);
</PRE>

<P>
</LI>
<LI>PostgreSQL database creation script
<BR>	<PRE>
-- PostgreSQL Database creation script

CREATE DATABASE sympa;

-- Connect to DB 
\connect sympa

DROP TABLE user_table;
CREATE TABLE user_table (
  	email_user          	varchar (100) NOT NULL,
  	gecos_user          	varchar (150),
	cookie_delay_user       int4,
        password_user		varchar (40),
        lang_user               varchar (10),
	attributes_user		varchar (255),
	CONSTRAINT ind_user PRIMARY KEY (email_user)
);

DROP TABLE subscriber_table;
CREATE TABLE subscriber_table (
  	list_subscriber       	varchar (50) NOT NULL,
	user_subscriber		varchar (100) NOT NULL,
	robot_subscriber	varchar (80) NOT NULL,
	date_subscriber		timestamp with time zone NOT NULL,
	update_subscriber 	timestamp with time zone,
	visibility_subscriber	varchar (20),
	reception_subscriber	varchar (20),
	topics_subscriber	varchar (200),
	bounce_subscriber	varchar (35),
	bounce_score_subscriber int4,
	bounce_address_subscriber	varchar (100),
	comment_subscriber	varchar (150),
	subscribed_subscriber 	smallint,
	included_subscriber 	smallint,
	include_sources_subscriber varchar(50),
	CONSTRAINT ind_subscriber PRIMARY KEY (list_subscriber, user_subscriber, robot_subscriber)
);
CREATE INDEX subscriber_idx ON subscriber_table (user_subscriber,list_subscriber,robot_subscriber);

DROP TABLE admin_table;
CREATE TABLE admin_table (
	list_admin 		varchar(50) NOT NULL,
 	user_admin 		varchar(100) NOT NULL,
 	robot_admin 		varchar(80) NOT NULL,
	role_admin 		varchar(15) NOT NULL,
	date_admin 		timestamp with time zone NOT NULL,
	update_admin 		timestamp with time zone,
	reception_admin 	varchar(20),
	comment_admin 		varchar(150),
	subscribed_admin  	smallint,
	included_admin  	smallint,
	include_sources_admin  	varchar(50),
	info_admin   		varchar(150),
	profile_admin  		varchar(15),
        CONSTRAINT ind_admin PRIMARY KEY (list_admin, user_admin, robot_admin, role_admin)
);
CREATE	INDEX admin_idx ON admin_table(list_admin, user_admin,robot_admin, role_admin);
 
DROP TABLE netidmap_table;
CREATE TABLE netidmap_table (
        netid_netidmap              varchar (100) NOT NULL,
	serviceid_netidmap	    varchar (100) NOT NULL,
	robot_netidmap	            varchar (80) NOT NULL,
        email_netidmap              varchar (100),
        CONSTRAINT ind_netidmap PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
);
CREATE	INDEX netidmap_idx ON netidmap_table(netid_netidmap, serviceid_netidmap, robot_netidmap);
</PRE>

<P>
</LI>
<LI>Sybase database creation script
<BR>	<PRE>
/* Sybase Database creation script 2.5.2 */
/* Thierry Charles &lt;tcharles@electron-libre.com&gt; */
/* 15/06/01 : extend password_user */

/* sympa database must have been created */
/* eg: create database sympa on your_device_data=10 log on your_device_log=4 */
use sympa
go

create table user_table
(
    email_user             varchar(100)           not null,
    gecos_user             varchar(150)           null    ,
    password_user          varchar(40)            null    ,
    cookie_delay_user      numeric                null    ,
    lang_user              varchar(10)            null    ,
    attributes_user	   varchar(255)           null    ,
    constraint ind_user primary key (email_user)
)
go

create index email_user_fk on user_table (email_user)
go


create table subscriber_table
(
    list_subscriber        varchar(50)            not null,
    user_subscriber        varchar(100)           not null,
    robot_subscriber       varchar(80)           not null,
    date_subscriber        datetime               not null,
    update_subscriber      datetime               null,
    visibility_subscriber  varchar(20)            null    ,
    reception_subscriber   varchar(20)            null    ,
    topics_subscriber	   varchar(200)           null,
    bounce_subscriber      varchar(35)            null    ,
    bounce_score_subscriber numeric               null    ,
    comment_subscriber     varchar(150)           null    ,
    subscribed_subscriber  numeric		  null    ,
    included_subscriber    numeric                null    ,
    include_sources_subscriber varchar(50)        null    ,
    constraint ind_subscriber primary key (list_subscriber, user_subscriber, robot_subscriber)
)
go

create index list_subscriber_fk on subscriber_table (list_subscriber)
go

create index user_subscriber_fk on subscriber_table (user_subscriber)
go

create index robot_subscriber_fk on subscriber_table (robot_subscriber)
go

create table admin_table 
(
	list_admin 		varchar(50)       not null,
 	user_admin 		varchar(100) 	  not null,
 	robot_admin 		varchar(80) 	  not null,
	role_admin 		varchar(15)       not null,
	date_admin 		datetime 	  not null,
	update_admin 		datetime          null,
	reception_admin 	varchar(20)       null,
	comment_admin 		varchar(150)	  null,
	subscribed_admin  	numeric           null,
	included_admin  	numeric           null,
	include_sources_admin  	varchar(50)       null,
	info_admin   		varchar(150)      null,
	profile_admin  		varchar(15)       null,
       constraint ind_admin primary key (list_admin, user_admin,robot_admin,role_admin)
)
go

create index list_admin_fk on admin_table (list_admin)
go

create index user_admin_fk on admin_table (user_admin)
go

create index robot_admin_fk on admin_table (robot_admin)
go

create index role_admin_fk on admin_table (role_admin)
go

create table netidmap_table 
(
        netid_netidmap             varchar (100) NOT NULL,
	serviceid_netidmap	   varchar (100) NOT NULL,
	robot_netidmap	           varchar (80) NOT NULL,
        email_netidmap             varchar (100),
        constraint ind_netidmap primary key (netid_netidmap, serviceid_netidmap, robot_netidmap)
)
go

create index netid_netidmap_fk on admin_table (netid_netidmap)
go

create index serviceid_netidmap_fk on admin_table (serviceid_netidmap)
go

create index robot_netidmap_fk on admin_table (robot_netidmap)
go
</PRE>

<P>
</LI>
<LI>Oracle database creation script
<BR>	<PRE>
## Oracle Database creation script
## Fabien Marquois &lt;fmarquoi@univ-lr.fr&gt;

/Bases/oracle/product/7.3.4.1/bin/sqlplus loginsystem/passwdoracle &lt;&lt;-!
 create user SYMPA identified by SYMPA default tablespace TABLESP
temporary tablespace TEMP;
 grant create session to SYMPA;
 grant create table to SYMPA;
 grant create synonym to SYMPA;
 grant create view to SYMPA;
 grant execute any procedure to SYMPA;
 grant select any table to SYMPA;
 grant select any sequence to SYMPA;
 grant resource to SYMPA;
!

/Bases/oracle/product/7.3.4.1/bin/sqlplus SYMPA/SYMPA &lt;&lt;-!
CREATE TABLE user_table (
        email_user              varchar2(100) NOT NULL,
        gecos_user              varchar2(150),
        password_user           varchar2(40),
        cookie_delay_user       number,
        lang_user               varchar2(10),
	attributes_user		varchar2(500),
        CONSTRAINT ind_user PRIMARY KEY (email_user)
);
CREATE TABLE subscriber_table (
        list_subscriber         varchar2(50) NOT NULL,
        user_subscriber         varchar2(100) NOT NULL,
        robot_subscriber         varchar2(80) NOT NULL,
        date_subscriber         date NOT NULL,
	update_subscriber	date,
        visibility_subscriber   varchar2(20),
        reception_subscriber    varchar2(20),
	topics_subscriber	varchar2(200),
 	bounce_subscriber       varchar2 (35),
	bounce_score_subscriber number,
	bounce_address_subscriber       varchar2 (100),
 	comment_subscriber      varchar2 (150),
	subscribed_subscriber 	number NULL    constraint  cons_subscribed_subscriber CHECK (subscribed_subscriber in (0,1)),
	included_subscriber 	number NULL    constraint  cons_included_subscriber CHECK (included_subscriber in (0,1)),
	include_sources_subscriber varchar2(50),
        CONSTRAINT ind_subscriber PRIMARY KEY (list_subscriber,user_subscriber,robot_subscriber)
);
CREATE TABLE admin_table (
	list_admin 		varchar2(50) NOT NULL,
 	user_admin 		varchar2(100) NOT NULL,
 	robot_admin 		varchar2(80) NOT NULL,
	role_admin 		varchar2(20) NOT NULL,
	date_admin 		date NOT NULL,
	update_admin 		date,
	reception_admin 	varchar2(20),
	comment_admin 		varchar2(150),
	subscribed_admin  	number NULL    constraint  cons_subscribed_admin CHECK (subscribed_admin in (0,1)),
	included_admin  	number NULL    constraint  cons_included_admin CHECK (included_admin in (0,1)),
	include_sources_admin  	varchar2(50),
	info_admin   		varchar2(150),
	profile_admin  		varchar2(20),
       CONSTRAINT ind_admin PRIMARY KEY (list_admin,user_admin,robot_admin,role_admin)
);

CREATE TABLE netidmap_table (
        netid_netidmap              varchar (100) NOT NULL,
	serviceid_netidmap	    varchar (100) NOT NULL,
	robot_netidmap	            varchar (80) NOT NULL,
        email_netidmap              varchar (100),
        CONSTRAINT ind_netidmap PRIMARY KEY (netid_netidmap, serviceid_netidmap, robot_netidmap)
);

!
</PRE>

<P>
</LI>
</UL>

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

<P>
Example:

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

<P>

<H1><A NAME="SECTION00940000000000000000">
8.4 Setting database privileges</A>
</H1>

<P>
We strongly recommend you restrict access to <I>sympa</I> database. You will
then set <A NAME="8634"></A><TT>db_user</TT> and <A NAME="8637"></A><TT>db_passwd</TT> in <A NAME="8640"></A><TT>sympa.conf</TT>.

<P>
With <A NAME="8643"></A>MySQL :<PRE>
grant all on sympa.* to sympa@localhost identified by 'your_password';
flush privileges;
</PRE>

<P>

<H1><A NAME="SECTION00950000000000000000">
8.5 Importing subscribers data</A>
</H1>

<P>

<H2><A NAME="SECTION00951000000000000000">
8.5.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="node5.html#sympa.pl">4.1</A>, page&nbsp;<A HREF="node5.html#sympa.pl"><IMG  ALIGN="BOTTOM" BORDER="1" ALT="[*]" SRC="crossref.png"></A>).

<P>

<H2><A NAME="SECTION00952000000000000000">
8.5.2 Importing data from subscribers files</A>
</H2>

<P>
If a mailing list was previously setup to store subscribers into 
<A NAME="8644"></A><TT>subscribers</TT> file (the default mode in versions older then 2.2b) 
you can load subscribers data into the sympa database. The easiest way
is to edit the list configuration using <A NAME="8647"></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 big, a timeout may occur during the FastCGI execution
(Note that you can set a longer timeout with the <A NAME="8650"></A><TT>-idle-timeout</TT> option of
the <TT>FastCgiServer</TT> Apache configuration directive). In this case, or if you have not installed <A NAME="8653"></A><I>WWSympa</I>, you should use the <A NAME="8656"></A><TT>load_subscribers.pl</TT> script.

<P>

<H1><A NAME="SECTION00960000000000000000"></A>
<A NAME="include2-cache"></A>
<BR>
8.6 Management of the include cache
</H1>

<P>
You may dynamically add a list of subscribers, editors or owners to a list with Sympa's <B>include2</B> user data source. Sympa is able to query
multiple data sources (RDBMS, LDAP directory, flat file, a local list, a remote list) to build a mailing list. 

<P>
Sympa used to manage the cache of such <I>included</I> subscribers in a DB File (<B>include</B> mode) but now stores
subscribers, editors and owners in the database (<B>include2</B> mode). These changes brought the following advantages :

<UL>
<LI>Sympa processes are smaller when dealing with big mailing lists (in include mode)

<P>
</LI>
<LI>Cache update is now performed regularly by a dedicated process, the task manager

<P>
</LI>
<LI>Mixed lists (included + subscribed users) can now be created

<P>
</LI>
<LI>Sympa can now provide reception options for <I>included</I> members

<P>
</LI>
<LI>Bounces information can be managed for <I>included</I> members

<P>
</LI>
<LI>Sympa keeps track of the data sources of a member (available on the web REVIEW page)

<P>
</LI>
<LI><I>included</I> members can also subscribe to the list. It allows them to remain in the list though they might no more be included.

<P>
</LI>
</UL>

<P>

<H1><A NAME="SECTION00970000000000000000">
8.7 Extending database table format</A>
</H1>

<P>
You can easily add other fields to the three tables, they will not disturb <I>Sympa</I> because it lists
explicitely the field it expects in SELECT queries.

<P>
Moreover you can access these database fields from within <I>Sympa</I> 
(in templates), as far as you list these additional fields in
<A NAME="8661"></A><TT>sympa.conf</TT> (See <A HREF="node8.html#db-additional-subscriber-fields">7.10.11</A>, page&nbsp;<A HREF="node8.html#db-additional-subscriber-fields"><IMG  ALIGN="BOTTOM" BORDER="1" ALT="[*]" SRC="crossref.png"></A>
and <A HREF="node8.html#db-additional-user-fields">7.10.12</A>, page&nbsp;<A HREF="node8.html#db-additional-user-fields"><IMG  ALIGN="BOTTOM" BORDER="1" ALT="[*]" SRC="crossref.png"></A>).

<P>

<H1><A NAME="SECTION00980000000000000000">
8.8 <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="8666"></A><TT>sympa.conf</TT> :
<A NAME="8669"></A><TT>db_type</TT>, <A NAME="8672"></A><TT>db_name</TT>, 
<A NAME="8675"></A><TT>db_host</TT>, <A NAME="8678"></A><TT>db_user</TT>, 
<A NAME="8681"></A><TT>db_passwd</TT>.

<P>
If you are interfacing <I>Sympa</I> with an Oracle database, 
<A NAME="8685"></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="8688"></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="8692"></A><TT>subscribers</TT> file. Note however that subscriber information is 
dumped to <A NAME="8695"></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="tex2html1031"
  HREF="node10.html">
<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A> 
<A NAME="tex2html1025"
  HREF="sympa.html">
<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A> 
<A NAME="tex2html1019"
  HREF="node8.html">
<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A> 
<A NAME="tex2html1027"
  HREF="node1.html">
<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A> 
<A NAME="tex2html1029"
  HREF="node30.html">
<IMG WIDTH="43" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="index" SRC="index.png"></A> 
<BR>
<B> Next:</B> <A NAME="tex2html1032"
  HREF="node10.html">9. WWSympa, Sympa's web interface</A>
<B> Up:</B> <A NAME="tex2html1026"
  HREF="sympa.html">Sympa Mailing Lists Management Software version</A>
<B> Previous:</B> <A NAME="tex2html1020"
  HREF="node8.html">7. sympa.conf parameters</A>
 &nbsp; <B>  <A NAME="tex2html1028"
  HREF="node1.html">Contents</A></B> 
 &nbsp; <B>  <A NAME="tex2html1030"
  HREF="node30.html">Index</A></B> 
<!--End of Navigation Panel-->
<ADDRESS>
root
2006-10-20
</ADDRESS>
</BODY>
</HTML>