Sympa requires a RDBMS to run. Currently you can use one of the following RDBMS: MySQL and its forks (MariaDB, Percona), 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
sympa_database(5) manual page.
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.
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.
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).
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
default-table-typeoptions of your database server.
$ 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
# sympa.pl --health_check
utf8mb4character set which covers full range of Unicode including such as chinese ideographs used for persons' names. As of Sympa-6.2a.33 r8753, both
utf8mb4character sets are supported. To use
utf8mb4charcter set, you might want to replace
utf8in SQL statement above with
db_namemust be same as Oracle SID.
db_envshould include definition of NLS_LANG and ORACLE_HOME.
$ 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
# sympa.pl --health_check
$ 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
# sympa.pl --health_check
db_namein sympa.conf is absolute path to database file you want to create.
# touch <db_name> # chown sympa:sympa <db_name> # sympa.pl --health_check
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).
We strongly recommend that you restrict access to the Sympa database. You will then set
grant all on sympa.* to sympa@localhost identified by 'your_password'; flush privileges;
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.
## Data to be imported ## email gecos email@example.com John - accountant firstname.lastname@example.org Mary - secretary
To import data into the database:
cat /tmp/my_import_file | sympa.pl --import=my_list
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
database; subscriber data will be loaded into the database at the same time.
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
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).
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.
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 :
the target_email_logsfield will contain the new member email address.
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 :
If you are interfacing Sympa with an Oracle database, note that :
db_namecorresponds to the Oracle SID.
ORACLE_HOMEenvironment variable through the db_env sympa.conf parameter
NLS_LANGenvironment variable is highly recommended.
All your lists are now configured to use the database, unless you set the list parameter
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 and changing the
user_data_source parameter), in case the database were to become inaccessible. - Is this still true?