Sympa Logo
Translations of this page:

Sympa and its database

Sympa requires a RDBMS to run. Currently you can use one of the following RDBMS: MySQL or MeriaDB, SQLite, PostgreSQL, Oracle Database, 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's use of the database and the schema are described in the Internals Document Database Section.

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 the RDBMS you chose! Check the ''DBI'' Module Availability.

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 (for example DBD-mysql for MySQL) are distributed as CPAN modules. Refer to Installing PERL and CPAN modules for installation details of these modules.

Creating a Sympa DataBase

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 between information relating to a person (email, real name, password) and his/her subscription options (list concerned, date of subscription, delivery mode, 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 email.

The table concerning owners and editors, the admin_table, is built on the same model as the subscriber_table. It contains owner and editor options (list concerned, administrative role, date of “subscription”, delivery mode, private information, gecos and profile option for owners).

Database automatic creation and update

At startup, the sympa.pl process will check if the database (configured in sympa;conf) is available and if it has the expected structure. If not, sympa.pl process will create the database or update its structure for you. Note however that this feature is not available with Sybase, integration for other RDBMS is less complete. Note that this automated process requires privileges to the db_user you've declared in sympa.conf.

MySQL or MariaDB

  • MySQL 4.1.1 or later, and any releases of MariaDB will work.
  • Install DBD-mysql package.
  • Ensure that sympa.conf includes appropriate values for these parameters: db_type, db_name, db_host, db_user and db_passwd.
  • MyISAM or Aria, and InnoDB or XtraDB storage engines may work. Check default-storage-engine and default-table-type options of your MySQL / MariaDB server.
  • Create database and database user:
  $ mysql
  mysql> CREATE DATABASE sympa CHARACTER SET utf8;
  mysql> GRANT ALL PRIVILEGES ON sympa.* TO <db_user>@<client host>
      -> IDENTIFIED BY '<db_passwd>';
  mysql> QUIT
  • Create table structure:
  # sympa.pl --health_check

MySQL/MariaDB 5.5.3 or later provides utf8mb4 character set which covers full range of Unicode including such as chinese ideographs used for persons' names. As of Sympa-6.2a.33 r8753, both utf8 and utf8mb4 character sets are supported. To use utf8mb4 charcter set, you might want to replace utf8 in SQL statement above with utf8mb4.

Oracle

  • Oracle 7 or later may work. Oracle 9i or later is recommended.
  • Install DBD-Oracle package.
  • Ensure that sympa.conf includes appropriate values for these parameters: db_type, db_name, db_host, db_user, db_passwd and db_env.
    • db_name must be same as Oracle SID.
    • db_env should include definition of NLS_LANG and ORACLE_HOME.
  • Create database user:
  $ NLS_LANG=American_America.AL32UTF8; export NLS_LANG
  $ ORACLE_HOME=<Oracle home>; export ORACLE_HOME
  $ ORACLE_SID=<Oracle SID>; export ORACLE_SID
  $ sqlplus <system login>/<password>
  SQL> CREATE USER <db_user> IDENTIFIED BY <db_passwd>
    2  DEFAULT TABLESPACE <tablespace>
    3  TEMPORARY TABLESPACE <tablespace>;
  SQL> GRANT CREATE SESSION TO <db_user>;
  SQL> GRANT CREATE TABLE TO <db_user>;
  SQL> GRANT CREATE SYNONYM TO <db_user>;
  SQL> GRANT CREATE VIEW TO <db_user>;
  SQL> GRANT EXECUTE ANY PROCEDURE TO <db_user>;
  SQL> GRANT SELECT ANY TABLE TO <db_user>;
  SQL> GRANT SELECT ANY SEQUENCE TO <db_user>;
  SQL> GRANT RESOURCE TO <db_user>;
  SQL> QUIT
  • Create table structure:
  # sympa.pl --health_check

PostgreSQL

  • PostgreSQL 7.4 or later is required.
  • Install DBD-Pg package.
  • Ensure that sympa.conf includes appropriate values for these parameters: db_type, db_name, db_host, db_user, db_passwd.
  • Create database and role:
  $ psql
  postgres=# CREATE ROLE <db_user> NOSUPERUSER NOCREATEDB NOCREATEROLE
  postgres=# NOINHERIT LOGIN ENCRYPTED PASSWORD '<db_passwd>';
  postgres=# CREATE DATABASE sympa OWNER <db_user> ENCODING 'UNICODE';
  postgres=# \q
  • Create table structure:
  # sympa.pl --health_check

SQLite

As of Sympa 6.2a.33, SQLite 2.x and earlier are no longer supported. See documentation of SQLite to upgrade database from 2.x to 3.x.

  • Install DBD-SQLite package.
  • Ensure that db_name in sympa.conf is absolute path to database file you want to create.
  • Create database file and table structure:
  # touch <db_name>
  # chown sympa:sympa <db_name>
  # sympa.pl --health_check

Database manual 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 Sybase).

  • Sybase database creation script:

Setting database privileges

We strongly recommend that you restrict access to the 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;

Importing subscriber data

Importing data from a text file

You can import subscribtion data into the database from a text file having one entry per line: the first field is an email address, the second (optional) field is the free form name. Fields are space-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 sympa.pl).

Importing data from subscribers files

If a mailing list was previously set up to store subscribers into a subscribers file (the default mode in versions older then 2.2b), you can load subscriber 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; subscriber data will be loaded into the database at the same time.

If the subscribers file is large, 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.

Extending database table format

You can easily add other fields to the three tables, they will not disturb Sympa because it lists explicitly 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 db_additional_subscriber_fields and db_additional_user_fields).

Sympa logs in the database

The logs_table database table has been introduced with release 5.3 of Sympa. This DB table gathers some kind of logs/journals that Sympa want to keep track of. Only events that changes Sympa's state are logged ; this includes member subscription/removal, message distribution/moderation, bounces handling, user authentication on the web interface. The content of the database can then be searched by list owners ; privacy is enforced to prevent a list owner to access information from other lists.

The logs_table table is purged to prevent DB size to diverge. The retention period of log entries is defined by the logs_expiration_period parameter

Below is a description of each field of the logs_table table :

  • id_logs: primary key for the table.
  • date_logs: epoch date representing the time when the action was performed.
  • robot_logs: the robot the action relates to. It may be empty if no specific virtual host is concerned.
  • list_logs: the list the action refers to. It may be undefined if the action does not refer to a specific list (like authentication related actions).
  • action_logs: an identifier for the action performed. Each action belongs to a group of actions (authentication related, subscription related, etc). The list of actions and the group they relate to is defined in the Log.pm perl module.
  • parameters_logs: parameters of the action. It is an optional comma-separated list of parameters.
  • user_email_logs: this field tells who is performing the action, if authenticated.
  • target_email_logs: this parameter represents the email address that is mainly concerned by the action ; it may be empty. If a list owner adds a new member to his list, the target_email_logs field will contain the new member email address.
  • client_logs: IP address of the user performing the action, may be empty if action is performed via the mail interface.
  • msg_id_logs: Message-ID of the message, if the action applies to a message.
  • status_logs: the status of the action ; it will contain one of the following values : success, error.
  • error_type_logs: If the action failed, this field an error identifier representing the error.
  • daemon_logs: tells which process has performed the action. It may be one of bounced, sympa, wwsympa

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'll need to tell Sympa where its database is located through the related sympa.conf parameters : db_type, db_name, db_host, db_user, db_passwd.

If you are interfacing Sympa with an Oracle database, note that :

  1. the db_name corresponds to the Oracle SID.
  2. you'll need to set the ORACLE_HOME environment variable through the db_env sympa.conf parameter
  3. On Oracle 9 or later, also setting the NLS_LANG environment variable is highly recommended.

All your lists are now configured to use the database, unless you set the 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), in case the database were to become inaccessible. FIXME - Is this still true?

manual_6.0/database.txt · Last modified: 2015/06/16 15:48 (external edit)

The Sympa software is provided by RENATER
Faq | News | Contact | Legal Notices