Sympa Logo
Translations of this page:

Database

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.

subscriber_table

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

user_table

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

spool_table

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

bulkmailer_table

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

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

session_table

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_table

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)

notification_table

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

logs_table

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

stat_table

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)

stat_counter_table

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

admin_table

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

netidmap_table

FIXME.

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

conf_tables

FIXME.

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.txt · Last modified: 2011/08/16 15:51 by serge.aumont@cru.fr

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