Tables

subscriber_table

This table stores subscription, subscription options, 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 was suspended

suspend_end_date_subscriber int(11)

the Unix time when message reception should be restored

bounce_subscriber varchar(35)

unix time of first bounce / unix time of last bounce / nb of bounces / error code

bounce_score_subscriber smallint(6)

bounce score for this subscriber

bounce_address_subscriber varchar(100)

VERP bounce address

date_epoch_subscriber int(11) not null

date of subscription

update_epoch_subscriber int(11)

last time subscription was confirmed by subscriber

inclusion_subscriber int(11)

last time subscribers' list was synchronized with data source

inclusion_ext_subscriber int(11)

last time subscribers' list was synchronized with external data source

inclusion_label_subscriber varchar(50)

name of data source

comment_subscriber varchar(150)

display name of subscriber

number_messages_subscriber int(5) not null

the number of messages the subscriber sent

visibility_subscriber varchar(20)

visibility of subscriber in list review page

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

custom attribute(s) of subscriber

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)

user cookie max age in minutes (0 is session)

lang_user varchar(10)

user language preference

attributes_user text

user custom attributes

data_user text

user misc WUI preferences

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)

name of the list

robot_exclusion varchar(80)

(Primary key)

robot (domain) of the list

user_exclusion varchar(100)

(Primary key)

excluded subscriber email address

family_exclusion varchar(50)

(Primary key)

family of the list

date_exclusion int(11)

the date this entry was created or updated

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)

random value of the one time ticket

email_one_time_ticket varchar(100)

email associated with one time ticket

robot_one_time_ticket varchar(80)

robot (domain) of the one time ticket

date_one_time_ticket int(11)

the date this entry was created or updated

data_one_time_ticket varchar(200)

data associated with one time ticket

remote_addr_one_time_ticket varchar(60)

Remote address associated with one time ticket

status_one_time_ticket varchar(60)

status of one time ticket

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

the date this entry was created or updated

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

unix epoch this entry was created or updated

email_stat varchar(100)

user email who ran the task

operation_stat varchar(50) not null

operation thas was run

list_stat varchar(50)

list name against which the task was run

daemon_stat varchar(20)

daemon which ran the task

user_ip_stat varchar(100)

remote ip of user

robot_stat varchar(80) not null

domain (robot) of the list

parameter_stat varchar(50)

unused except to store message size on send_mail

read_stat tinyint(1) not null

data was aggregated in stat_counter_table

Indexes:

stats_user_index

email_stat

stat_counter_table

Used in conjunction with stat_table for users statistics.

Fields:

end_date_counter int(11)

unix time for the end of the stats

beginning_date_counter int(11) not null

unix time for the beginning of the stats

data_counter varchar(50) not null

type of operation

robot_counter varchar(80) not null

domain (robot)

list_counter varchar(50)

list

count_counter int

number of occurences

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)

email of admin

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)

display name of admin

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

This table stores information for users login with SSO

Fields:

netid_netidmap varchar(100)

(Primary key)

user netid

serviceid_netidmap varchar(100)

(Primary key)

SSO service

robot_netidmap varchar(80)

(Primary key)

robot (domain)

email_netidmap varchar(100)

user email

conf_table

The conf table stores the parameters values for robots

Fields:

robot_conf varchar(80)

(Primary key)

robot (domain) of the conf

label_conf varchar(80)

(Primary key)

parameter name of the conf

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