Provided by: sympa_6.2.40~dfsg-1_amd64 bug

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

       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 separated list of datasource that contain this subscriber

       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

       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. Note that included_admin and
           subscribed_admin can both value 1

       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 Administration Manual.  <https://sympa-community.github.io/manual/>.