FreeBSD Manual Pages
SYMPA_DATABASE(5) sympa 6.2.76 SYMPA_DATABASE(5) 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://www.sympa.community/manual/>. 6.2.76 2025-04-17 SYMPA_DATABASE(5)
NAME | DECRIPTION | SEE ALSO
Want to link to this manual page? Use this URL:
<https://man.freebsd.org/cgi/man.cgi?query=sympa_database&sektion=5&manpath=FreeBSD+Ports+14.3.quarterly>