Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Contact Us
Online Help
Domain Status
Man Pages

Virtual Servers

Topology Map

Server Agreement
Year 2038

USA Flag



Man Pages

Manual Reference Pages  -  SYMPA_DATABASE (5)

.ds Aq ’


sympa_database - Strutcure of Sympa core database



Core database of Sympa is based on SQL. In following list of tables and indexes, data types are based on MySQL/MariaDB. Corresponding types are used by other platforms (PostgreSQL, SQLite, ...).



This table store subscription, subscription option etc.

user_subscriber varchar(100) (Primary key)

email of subscriber

list_subscriber varchar(50) (Primary key)

list name of a subscription

robot_subscriber varchar(80) (Primary key)

robot (domain) of the list

reception_subscriber varchar(20) reception format option of subscriber (digest, summary, etc.)
suspend_subscriber int(1) boolean set to 1 if subscription is suspended
suspend_start_date_subscriber int(11) the Unix time when message reception is suspended
suspend_end_date_subscriber int(11) the Unix time when message reception should be restored
bounce_subscriber varchar(35) FIXME
bounce_score_subscriber smallint(6) FIXME
bounce_address_subscriber varchar(100) FIXME
custom_attribute_subscriber text FIXME
date_subscriber datetime not null date of subscription
update_subscriber datetime the latest date where subscription is confirmed by subscriber
comment_subscriber varchar(150) free form name
number_messages_subscriber int(5) not null the number of message the subscriber sent
visibility_subscriber varchar(20) FIXME
topics_subscriber varchar(200) topic subscription specification
subscribed_subscriber int(1) boolean set to 1 if subscriber comes from ADD or SUB
included_subscriber int(1) boolean, set to 1 is subscriber comes from an external datasource. Note that included_subscriber and subscribed_subscriber can both value 1
include_sources_subscriber varchar(50) comma seperated list of datasource that contain this subscriber
subscriber_user_index user_subscriber

The user_table is mainly used to manage login from web interface. A subscriber may not appear in the user_table if he never log through the web interface.

email_user varchar(100) (Primary key)

email of user

password_user varchar(40) password are stored as fringer print
gecos_user varchar(150) display name of user
last_login_date_user int(11) Unix time of last login, printed in login result for security purpose
last_login_host_user varchar(60) host of last login, printed in login result for security purpose
wrong_login_count_user int(11) login attempt count, used to prevent brute force attack
last_active_date_user int(11) the last Unix time when this user was confirmed their activity by purge_user_table task
cookie_delay_user int(11) FIXME
lang_user varchar(10) user langage preference
attributes_user text FIXME
data_user text FIXME

Exclusion table is used in order to manage unsubscription for subsceriber inclued from an external data source.

list_exclusion varchar(57) (Primary key)


robot_exclusion varchar(80) (Primary key)


user_exclusion varchar(100) (Primary key)


family_exclusion varchar(50) (Primary key)


date_exclusion int(11) FIXME

Management of HTTP session.

id_session varchar(30) (Primary key)

the identifier of the database record

prev_id_session varchar(30) previous identifier of the database record
start_date_session int(11) not null the date when the session was created
date_session int(11) not null Unix time of the last use of this session. It is used in order to expire old sessions
refresh_date_session int(11) Unix time of the last refresh of this session. It is used in order to refresh available sessions
remote_addr_session varchar(60) the IP address of the computer from which the session was created
robot_session varchar(80) the virtual host in which the session was created
email_session varchar(100) the email associated to this session
hit_session int(11) the number of hit performed during this session. Used to detect crawlers
data_session text parameters attached to this session that don’t have a dedicated column in the database

One time ticket are random value used for authentication challenge. A ticket is associated with a context which look like a session.

ticket_one_time_ticket varchar(30) (Primary key)


email_one_time_ticket varchar(100) FIXME
robot_one_time_ticket varchar(80) FIXME
date_one_time_ticket int(11) FIXME
data_one_time_ticket varchar(200) FIXME
remote_addr_one_time_ticket varchar(60) FIXME
status_one_time_ticket varchar(60) FIXME

Used for message tracking feature. If the list is configured for tracking, outgoing messages include a delivery status notification request and optionally a message disposition notification request. When DSN and MDN are received by Sympa, they are stored in this table in relation with the related list and message ID.

pk_notification bigint(20) auto_increment (Primary key)

autoincrement key

message_id_notification varchar(100) initial message-id. This field is used to search DSN and MDN related to a particular message
recipient_notification varchar(100) email address of recipient for which a DSN or MDN was received
reception_option_notification varchar(20) the subscription option of the subscriber when the related message was sent to the list. Useful because some recipient may have option such as //digest// or //nomail//
status_notification varchar(100) value of notification
arrival_date_notification varchar(80) reception date of latest DSN or MDN
arrival_epoch_notification int(11) reception date of latest DSN or MDN
type_notification enum(’DSN’, ’MDN’) type of the notification (DSN or MDN)
list_notification varchar(50) the listname the message was issued for
robot_notification varchar(80) the robot the message is related to
date_notification int(11) not null FIXME

Each important event is stored in this table. List owners and listmaster can search entries in this table using web interface.

user_email_logs varchar(100) e-mail address of the message sender or email of identified web interface user (or soap user)
date_logs int(11) not null date when the action was executed
usec_logs int(6) subsecond in microsecond when the action was executed
robot_logs varchar(80) name of the robot in which context the action was executed
list_logs varchar(50) name of the mailing-list in which context the action was executed
action_logs varchar(50) not null name of the Sympa subroutine which initiated the log
parameters_logs varchar(100) comma-separated list of parameters. The amount and type of parameters can differ from an action to another
target_email_logs varchar(100) e-mail address (if any) targeted by the message
msg_id_logs varchar(255) identifier of the message which triggered the action
status_logs varchar(10) not null exit status of the action. If it was an error, it is likely that the error_type_logs field will contain a description of this error
error_type_logs varchar(150) name of the error string - if any - issued by the subroutine
client_logs varchar(100) IP address of the client machine from which the message was sent
daemon_logs varchar(10) not null name of the Sympa daemon which ran the action

Statistics item are stored in this table, Sum average and so on are stored in stat_counter_table.

date_stat int(11) not null FIXME
email_stat varchar(100) FIXME
operation_stat varchar(50) not null FIXME
list_stat varchar(50) FIXME
daemon_stat varchar(20) FIXME
user_ip_stat varchar(100) FIXME
robot_stat varchar(80) not null FIXME
parameter_stat varchar(50) FIXME
read_stat tinyint(1) not null FIXME
stats_user_index email_stat

Used in conjunction with stat_table for users statistics.

end_date_counter int(11) FIXME
beginning_date_counter int(11) not null FIXME
data_counter varchar(50) not null FIXME
robot_counter varchar(80) not null FIXME
list_counter varchar(50) FIXME
count_counter int FIXME

This table is an internal cash where list admin roles are stored. It is just a cash and it does not need to be saved. You may remove its content if needed. It will just make next Sympa startup slower.

user_admin varchar(100) (Primary key)

list admin email

list_admin varchar(50) (Primary key)

list name

robot_admin varchar(80) (Primary key)

list domain

role_admin enum(’listmaster’,’owner’,’editor’) (Primary key)

a role of this user for this list (editor, owner or listmaster which a kind of list owner too)

profile_admin enum(’privileged’,’normal’) privilege level for this owner, value //normal// or //privileged//. The related privilege are listed in editlist.conf.
date_admin datetime not null date this user become a list admin
update_admin datetime last update timestamp
reception_admin varchar(20) email reception option for list management messages
visibility_admin varchar(20) admin user email can be hidden in the list web page description
comment_admin varchar(150) FIXME
subscribed_admin int(1) set to 1 if user is list admin by definition in list config file
included_admin int(1) set to 1 if user is admin by an external data source
include_sources_admin varchar(50) name of external datasource
info_admin varchar(150) private information usually dedicated to listmasters who needs some additional information about list owners
admin_user_index user_admin


netid_netidmap varchar(100) (Primary key)


serviceid_netidmap varchar(100) (Primary key)


robot_netidmap varchar(80) (Primary key)


email_netidmap varchar(100) FIXME


robot_conf varchar(80) (Primary key)


label_conf varchar(80) (Primary key)


value_conf varchar(300) the value of parameter //label_conf// of robot //robot_conf//.

The list_table holds cached list config and some items to help searching lists.

name_list varchar(50) (Primary key)

name of the list

robot_list varchar(80) (Primary key)

name of the robot (domain) the list belongs to

family_list varchar(50) name of the family the list belongs to
status_list enum(’open’,’closed’,’pending’,’error_config’,’family_closed’) status of the list
creation_email_list varchar(100) email of user who created the list
creation_epoch_list int(11) UNIX time when the list was created
update_email_list varchar(100) email of user who updated the list
update_epoch_list int(11) UNIX time when the list was updated
searchkey_list varchar(255) case-folded list subject to help searching
web_archive_list tinyint(1) if the list has archives
topics_list varchar(255) topics of the list, separated and enclosed by commas
total_list int(7) estimated number of subscribers


Sympa and its database. <>.
Search for    or go to Top of page |  Section 5 |  Main Index

6.2.14 SYMPA_DATABASE (5) 2016-04-14

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.