SYMPA_DATABASE(5) sympa 6.2.11 SYMPA_DATABASE(5)NAMEsympa_database - Strutcure 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
custom_attribute_subscriber text
FIXME
bounce_address_subscriber varchar(100)
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
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 he never log through the
web interface.
Fields:
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
Exclusion table is used in order to manage unsubscription for
subsceriber inclued 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
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 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
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 and its database. <https://www.sympa.org/manual/database>.
6.2.11 2016-02-18 SYMPA_DATABASE(5)
