Provided by: sympa_6.2.66~dfsg-2_amd64 
NAME
sympa_database - Structure of Sympa core database
DECRIPTION
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, ...).
Tables
subscriber_table
This table store subscription, subscription option etc.
Fields:
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
date_epoch_subscriber int(11) not null
date of subscription
update_epoch_subscriber int(11)
the last time when subscription is confirmed by subscriber
inclusion_subscriber int(11)
the last time when list user is synchronized with data source
inclusion_ext_subscriber int(11)
the last time when list user is synchronized with external data source
inclusion_label_subscriber varchar(50)
name of data source
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
custom_attribute_subscriber text
FIXME
Indexes:
subscriber_user_index
user_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 they never log through the web interface.
Fields:
email_user varchar(100)
(Primary key)
email of user
password_user varchar(64)
password are stored as finger 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 language preference
attributes_user text
FIXME
data_user text
FIXME
inclusion_table
Inclusion table is used in order to manage lists included from / including subscribers of
other lists.
Fields:
target_inclusion varchar(131)
(Primary key)
list ID of including list
role_inclusion enum('member','owner','editor')
(Primary key)
role of included user
source_inclusion varchar(131)
(Primary key)
list ID of included list
update_epoch_inclusion int(11)
the date this entry was created or updated
exclusion_table
Exclusion table is used in order to manage unsubscription for subscriber included from an
external data source.
Fields:
list_exclusion varchar(57)
(Primary key)
FIXME
robot_exclusion varchar(80)
(Primary key)
FIXME
user_exclusion varchar(100)
(Primary key)
FIXME
family_exclusion varchar(50)
(Primary key)
FIXME
date_exclusion int(11)
FIXME
session_table
Management of HTTP session.
Fields:
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
Indexes:
session_prev_id_index
prev_id_session
one_time_ticket_table
One time ticket are random value used for authentication challenge. A ticket is associated
with a context which look like a session.
Fields:
ticket_one_time_ticket varchar(30)
(Primary key)
FIXME
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
notification_table
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.
Fields:
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
logs_table
Each important event is stored in this table. List owners and listmaster can search
entries in this table using web interface.
Fields:
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
stat_table
Statistics item are stored in this table, Sum average and so on are stored in
stat_counter_table.
Fields:
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
Indexes:
stats_user_index
email_stat
stat_counter_table
Used in conjunction with stat_table for users statistics.
Fields:
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
admin_table
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.
Fields:
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 edit_list.conf.
date_epoch_admin int(11) not null
date this user become a list admin
update_epoch_admin int(11)
last update time
inclusion_admin int(11)
the last time when list user is synchronized with data source
inclusion_ext_admin int(11)
the last time when list user is synchronized with external data source
inclusion_label_admin varchar(50)
name of data source
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
info_admin varchar(150)
private information usually dedicated to listmasters who needs some additional
information about list owners
Indexes:
admin_user_index
user_admin
netidmap_table
FIXME
Fields:
netid_netidmap varchar(100)
(Primary key)
FIXME
serviceid_netidmap varchar(100)
(Primary key)
FIXME
robot_netidmap varchar(80)
(Primary key)
FIXME
email_netidmap varchar(100)
FIXME
conf_table
FIXME
Fields:
robot_conf varchar(80)
(Primary key)
FIXME
label_conf varchar(80)
(Primary key)
FIXME
value_conf varchar(300)
the value of parameter //label_conf// of robot //robot_conf//.
list_table
The list_table holds cached list config and some items to help searching lists.
Fields:
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
SEE ALSO
Sympa Administration Manual. <https://sympa-community.github.io/manual/>.