Next: 9. WWSympa, Sympa's web interface Up: Sympa Mailing Lists Management Software version Previous: 7. sympa.conf parameters Contents Index
Subsections

- 8.1 Prerequisites
- 8.2 Installing PERL modules
- 8.3 Creating a sympa DataBase
  - 8.3.1 Database structure
  - 8.3.2 Database creation
- 8.4 Setting database privileges
- 8.5 Importing subscribers data
  - 8.5.1 Importing data from a text file
  - 8.5.2 Importing data from subscribers files
- 8.6 Management of the include cache
- 8.7 Extending database table format
- 8.8 Sympa configuration

#
8. Sympa and its database

Most basic feature of Sympa 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, DBI (DataBase Interface), has DBD (DataBase Drivers) for many RDBMS.

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

- User preferences and passwords are stored in the user_table table

- List subscription informations are stored in the subscriber_table table, along with subscription options. This table also contains the cache for included users (if using include2 mode).

- List administrative informations are stored in the admin_table table if using include2 mode, along with owner and editor options. This table also contains the cache for included owners and editors.

# 8.1 Prerequisites

You need to have a DataBase System installed (not necessarily on the same host as Sympa), and the client libraries for that Database installed on the Sympa host ; provided, of course, that a PERL DBD (DataBase Driver) is available for your chosen RDBMS! Check the `DBI` Module Availability.

# 8.2 Installing PERL modules

Sympa 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 3.2.3, page for installation details of these modules.

# 8.3 Creating a sympa DataBase

## 8.3.1 Database structure

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

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.

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).

## 8.3.2 Database creation

The `create_db` script below will create the sympa database for you. You can find it in the `script/` directory of the distribution (currently scripts are available for MySQL, SQLite, PostgreSQL, Oracle and Sybase).

- MySQL database creation script

      ## 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)
      # );

- SQLiteL database creation script

      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);

- PostgreSQL database creation script

      -- 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);

- Sybase database creation script

      /* Sybase Database creation script 2.5.2 */
      /* Thierry Charles <tcharles@electron-libre.com> */
      /* 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

- Oracle database creation script

      ## Oracle Database creation script
      ## Fabien Marquois <fmarquoi@univ-lr.fr>

      /Bases/oracle/product/7.3.4.1/bin/sqlplus loginsystem/passwdoracle <<-!
       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 <<-!
      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)
      );

      !

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

Example:

    # mysql  < create_db.mysql

# 8.4 Setting database privileges

We strongly recommend you restrict access to sympa database. You will then set `db_user` and `db_passwd` in `sympa.conf`.

With MySQL :

    grant all on sympa.* to sympa@localhost identified by 'your_password';
    flush privileges;

# 8.5 Importing subscribers data

## 8.5.1 Importing data from a text file

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.

Example:

    ## Data to be imported
    ## email        gecos
    john.steward@some.company.com           John - accountant
    mary.blacksmith@another.company.com     Mary - secretary

To import data into the database :

    cat /tmp/my_import_file | sympa.pl --import=my_list

(see 4.1, page ).

## 8.5.2 Importing data from subscribers files

If a mailing list was previously setup to store subscribers into `subscribers` 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 WWSympa (this requires listmaster privileges) and change the data source from file to database ; subscribers data will be loaded into the database at the same time.

If the subscribers file is big, a timeout may occur during the FastCGI execution (Note that you can set a longer timeout with the `-idle-timeout` option of the `FastCgiServer` Apache configuration directive). In this case, or if you have not installed WWSympa, you should use the `load_subscribers.pl` script.

#
8.6 Management of the include cache

You may dynamically add a list of subscribers, editors or owners to a list with Sympa's include2 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.

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

- Sympa processes are smaller when dealing with big mailing lists (in include mode)

- Cache update is now performed regularly by a dedicated process, the task manager

- Mixed lists (included + subscribed users) can now be created

- Sympa can now provide reception options for included members

- Bounces information can be managed for included members

- Sympa keeps track of the data sources of a member (available on the web REVIEW page)

- included members can also subscribe to the list. It allows them to remain in the list though they might no more be included.

# 8.7 Extending database table format

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

Moreover you can access these database fields from within Sympa (in templates), as far as you list these additional fields in `sympa.conf` (See 7.10.11, page and 7.10.12, page ).

# 8.8 Sympa configuration

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

You define the database source in `sympa.conf` : `db_type`, `db_name`, `db_host`, `db_user`, `db_passwd`.

If you are interfacing Sympa with an Oracle database, `db_name` is the SID.

All your lists are now configured to use the database, unless you set list parameter `user_data_source` to file or include.

Sympa will now extract and store user information for this list using the database instead of the `subscribers` file. Note however that subscriber information is dumped to `subscribers.db.dump` 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.

Next: 9. WWSympa, Sympa's web interface Up: Sympa Mailing Lists Management Software version Previous: 7. sympa.conf parameters Contents Index