This is an old revision of the document!


Work in progress. All the tables are described already though. Now this doc is produce by a script using Sympa::DatabaseDesccription

This chapter describes the Sympa database.


This table store subscription, subscription option etc..

Fields Type Properties Usage
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 date (epoch) when message reception is suspended
suspend_end_date_subscriber int(11) The date (epoch) when message reception should be restored
bounce_subscriber varchar(35) FIXME
bounce_score_subscriber smallint(6) FIXME
custom_attribute_subscriber text FIXME
bounce_address_subscriber varchar(100) FIXME
date_subscriber datetime 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) 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


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

Fields Type Properties Usage
email_user varchar(100) primary key email user is the key
attributes_user text FIXME
data_user text FIXME
cookie_delay_user int(11) FIXME
lang_user varchar(10) user langage preference
password_user varchar(40) password are stored as fringer print
gecos_user varchar(150)
last_login_date_user int(11) date epoch from 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 brut force attack


This table is created in version 6.3. It replace most of spools on file system forclustering purpose.

Fields Type Properties Usage
messagekey_spool bigint(20) primary key autoincrement key
spoolname_spool enum('msg','auth','mod','digest','archive','bounce','subscribe','topic','bulk','validated','task') the spool name
list_spool varchar(50)
robot_spool varchar(80)
priority_spool varchar(2) priority (list priority, owner pririty etc)
date_spool int(11) the date a message is copied in spool table
message_spool longtext message as string b64 encoded
messagelock_spool varchar(90) a unique string for each process : $$@hostname
lockdate_spool int(11) the date a lock is set. Used in order detect old locks
message_status_spool enum('ok','bad') if problem when processed entries have bad status
message_diag_spool text the reason why a message is moved to bad
type_spool varchar(15) list, list-request,, sympa robot or other rcp
authkey_spool varchar(33) authentication key for email chalenge
headerdate_spool varchar(80) the message header date
create_list_if_needed_spool int(1) set to 1 if message is related to a dynamic list, set to 0 if list as been created or if list is static
subject_spool varchar(110) subject of the message stored to list spool content faster
sender_spool varchar(110) this info is stored to browse spool content faster
messageid_spool varchar(300) stored to list spool content faster
spam_status_spool varchar(12) spamstatus scenario result
size_spool int(11) info stored in order to browse spool content faster
task_date_spool int(11) date for a task
task_label_spool varchar(20) label for a task
task_model_spool varchar(40) model of related task
task_object_spool varchar(50) object of related task
dkim_privatekey_spool varchar(1000) DKIM parameter stored for bulk daemon because bulk ignore list parameters, private key to sign message
dkim_selector_spool varchar(50) DKIM parameter stored for bulk daemon because bulk ignore list parameters, DKIM selector to sign message
dkim_d_spool varchar(50) DKIM parameter stored for bulk daemon because bulk ignore list parameters, the d DKIM parameter
dkim_i_spool varchar(100) DKIM parameter stored for bulk daemon because bulk ignore list parameters, DKIM i signature parameter
dkim_header_list_spool varchar(500) DKIM parameter stored for bulk daemon because bulk ignore list parameters, the list of headers included in signature


storage of receipients with a ref to a message in spool_table. So a very simple process can distribute them.

Fields Type Properties Usage
messagekey_bulkmailer varchar(80) primary key A pointer to a message in spool_table.It must be a value of a line in table spool_table with same value as messagekey_spool
packetid_bulkmailer varchar(33) primary key An id for the packet
messageid_bulkmailer varchar(200) The message Id
receipients_bulkmailer text the comma separated list of receipient email for this message
returnpath_bulkmailer varchar(100) the return path value that must be set when sending the message
robot_bulkmailer varchar(80)
listname_bulkmailer varchar(50)
verp_bulkmailer int(1) A boolean to specify if VERP is requiered, in this cas return_path will be formated using verp form
tracking_bulkmailer enum('mdn','dsn') Is DSN or MDM requiered when sending this message?
merge_bulkmailer int(1) Boolean, if true, the message is to be parsed as a TT2 template foreach receipient
priority_message_bulkmailer smallint(10) FIXME
priority_packet_bulkmailer smallint(10) FIXME
reception_date_bulkmailer int(11) The date where the message was received
delivery_date_bulkmailer int(11) The date the message was sent
lock_bulkmailer varchar(30) A lock. It is set as process-number @ hostname so multiple bulkmailer can handle this spool


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

Fields Type Properties Usage
user_exclusion varchar(100) primary key
date_exclusion int(11)
list_exclusion varchar(50) primary key


managment of http session.

Fields Type Properties Usage
id_session varchar(30) primary key the identifier of the database record
start_date_session int(11) the date when the session was created
date_session int(11) date epoch of the last use of this session. It is used in order to expire old 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 use for authentication chalenge. A ticket is associated with a context which look like a session.

Fields Type Properties Usage
data_one_time_ticket varchar(200)
status_one_time_ticket varchar(60)
remote_addr_one_time_ticket varchar(60)
ticket_one_time_ticket varchar(30) primary key
date_one_time_ticket int(11)
robot_one_time_ticket varchar(80)
email_one_time_ticket varchar(100)


used for message tracking feature. If the list is configured for tracking, outgoing messages include a delivery status notification request and optionnaly a return receipt request.When DSN MDN are received by Syamp, they are store in this table in relation with the related list and message_id.

Fields Type Properties Usage
date_notification int(11) FIXME
pk_notification bigint(20) primary key Autoincrement key
message_id_notification varchar(100) initial message-id. This feild is used to search DSN and MDN related to a particular message
recipient_notification varchar(100) email adresse of receipient for which a DSN or MDM was received
reception_option_notification varchar(20) The subscription option of the subscriber when the related message was sent to the list. Ussefull because some receipient 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 MDM
type_notification enum('DSN', 'MDN') Type of the notification (DSN or MDM)
message_notification longtext The DSN or the MDN itself
list_notification varchar(50) The listname the messaage was issued for
robot_notification varchar(80) The robot the message is related to


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

Fields Type Properties Usage
id_logs bigint(20) primary key Unique log's identifier
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) date 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) name of the Sympa subroutine which initiated the log
parameters_logs varchar(100) List of commas-separated 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) 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) name of the Sympa daemon which ran the action


Statistic item are store in this table, Sum average etc are stored in Stat_counter_table.

Fields Type Properties Usage
daemon_stat varchar(10)
parameter_stat varchar(50)
list_stat varchar(150)
id_stat bigint(20) primary key
operation_stat varchar(50)
email_stat varchar(100)
user_ip_stat varchar(100)
read_stat tinyint(1)
date_stat int(11)
robot_stat varchar(80)


Use in conjunction with stat_table for users statistics.

Fields Type Properties Usage
list_counter varchar(150)
end_date_counter int(11)
robot_counter varchar(80)
id_counter bigint(20) primary key
beginning_date_counter int(11)
data_counter varchar(50)
variation_counter int
total_counter int


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

Fields Type Properties Usage
user_admin varchar(100) primary key List admin email
list_admin varchar(50) primary key Listname
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 date this user become a list admin
update_admin datetime last update timestamp
reception_admin varchar(20) email reception option for list managment messages
visibility_admin varchar(20) admin user email can be hidden in the list web page description
comment_admin varchar(150)
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



Fields Type Properties Usage
netid_netidmap varchar(100) primary key
email_netidmap varchar(100)
robot_netidmap varchar(80) primary key
serviceid_netidmap varchar(100) primary key



Fields Type Properties Usage
value_conf varchar(300) the value of parameter label_conf of robot robot_conf.
robot_conf varchar(80) primary key
label_conf varchar(80) primary key
  • internals/database.1313502712.txt.gz
  • Last modified: 2011/08/16 15:51
  • by