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/.