This is an old revision of the document!
Database
Work in progress. All the tables are described already though. Now this doc is produce by a script using Sympa::DatabaseDesccription
This chapter describes the Sympa database.
subscriber_table
This table store subscription, subscription option etc..
| Fields | Type | Properties | Usage |
|---|---|---|---|
| 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 date (epoch) when message reception is suspended | |
| suspend_end_date_subscriber | int(11) | The date (epoch) when message reception should be restored | |
| bounce_subscriber | varchar(35) |  |
|
| bounce_score_subscriber | smallint(6) | |
|
| custom_attribute_subscriber | text | |
|
| bounce_address_subscriber | varchar(100) | |
|
| date_subscriber | datetime | 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) | the number of message the subscriber sent | |
| visibility_subscriber | varchar(20) | |
|
| 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 |
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 | Type | Properties | Usage |
|---|---|---|---|
| email_user | varchar(100) | primary key | email user is the key |
| attributes_user | text | |
|
| data_user | text | |
|
| cookie_delay_user | int(11) | |
|
| lang_user | varchar(10) | user langage preference | |
| password_user | varchar(40) | password are stored as fringer print | |
| gecos_user | varchar(150) | ||
| last_login_date_user | int(11) | date epoch from 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 brut force attack |
spool_table
This table is created in version 6.3. It replace most of spools on file system forclustering purpose.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| messagekey_spool | bigint(20) | primary key | autoincrement key |
| spoolname_spool | enum('msg','auth','mod','digest','archive','bounce','subscribe','topic','bulk','validated','task') | the spool name | |
| list_spool | varchar(50) | ||
| robot_spool | varchar(80) | ||
| priority_spool | varchar(2) | priority (list priority, owner pririty etc) | |
| date_spool | int(11) | the date a message is copied in spool table | |
| message_spool | longtext | message as string b64 encoded | |
| messagelock_spool | varchar(90) | a unique string for each process : $$@hostname | |
| lockdate_spool | int(11) | the date a lock is set. Used in order detect old locks | |
| message_status_spool | enum('ok','bad') | if problem when processed entries have bad status | |
| message_diag_spool | text | the reason why a message is moved to bad | |
| type_spool | varchar(15) | list, list-request,, sympa robot or other rcp | |
| authkey_spool | varchar(33) | authentication key for email chalenge | |
| headerdate_spool | varchar(80) | the message header date | |
| create_list_if_needed_spool | int(1) | set to 1 if message is related to a dynamic list, set to 0 if list as been created or if list is static | |
| subject_spool | varchar(110) | subject of the message stored to list spool content faster | |
| sender_spool | varchar(110) | this info is stored to browse spool content faster | |
| messageid_spool | varchar(300) | stored to list spool content faster | |
| spam_status_spool | varchar(12) | spamstatus scenario result | |
| size_spool | int(11) | info stored in order to browse spool content faster | |
| task_date_spool | int(11) | date for a task | |
| task_label_spool | varchar(20) | label for a task | |
| task_model_spool | varchar(40) | model of related task | |
| task_object_spool | varchar(50) | object of related task | |
| dkim_privatekey_spool | varchar(1000) | DKIM parameter stored for bulk daemon because bulk ignore list parameters, private key to sign message | |
| dkim_selector_spool | varchar(50) | DKIM parameter stored for bulk daemon because bulk ignore list parameters, DKIM selector to sign message | |
| dkim_d_spool | varchar(50) | DKIM parameter stored for bulk daemon because bulk ignore list parameters, the d DKIM parameter | |
| dkim_i_spool | varchar(100) | DKIM parameter stored for bulk daemon because bulk ignore list parameters, DKIM i signature parameter | |
| dkim_header_list_spool | varchar(500) | DKIM parameter stored for bulk daemon because bulk ignore list parameters, the list of headers included in signature |
bulkmailer_table
storage of receipients with a ref to a message in spool_table. So a very simple process can distribute them.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| messagekey_bulkmailer | varchar(80) | primary key | A pointer to a message in spool_table.It must be a value of a line in table spool_table with same value as messagekey_spool |
| packetid_bulkmailer | varchar(33) | primary key | An id for the packet |
| messageid_bulkmailer | varchar(200) | The message Id | |
| receipients_bulkmailer | text | the comma separated list of receipient email for this message | |
| returnpath_bulkmailer | varchar(100) | the return path value that must be set when sending the message | |
| robot_bulkmailer | varchar(80) | ||
| listname_bulkmailer | varchar(50) | ||
| verp_bulkmailer | int(1) | A boolean to specify if VERP is requiered, in this cas return_path will be formated using verp form | |
| tracking_bulkmailer | enum('mdn','dsn') | Is DSN or MDM requiered when sending this message? | |
| merge_bulkmailer | int(1) | Boolean, if true, the message is to be parsed as a TT2 template foreach receipient | |
| priority_message_bulkmailer | smallint(10) | |
|
| priority_packet_bulkmailer | smallint(10) | |
|
| reception_date_bulkmailer | int(11) | The date where the message was received | |
| delivery_date_bulkmailer | int(11) | The date the message was sent | |
| lock_bulkmailer | varchar(30) | A lock. It is set as process-number @ hostname so multiple bulkmailer can handle this spool |
exclusion_table
exclusion table is used in order to manage unsubscription for subsceriber inclued from an external data source.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| user_exclusion | varchar(100) | primary key | |
| date_exclusion | int(11) | ||
| list_exclusion | varchar(50) | primary key |
session_table
managment of http session.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| id_session | varchar(30) | primary key | the identifier of the database record |
| start_date_session | int(11) | the date when the session was created | |
| date_session | int(11) | date epoch of the last use of this session. It is used in order to expire old 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 use for authentication chalenge. A ticket is associated with a context which look like a session.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| data_one_time_ticket | varchar(200) | ||
| status_one_time_ticket | varchar(60) | ||
| remote_addr_one_time_ticket | varchar(60) | ||
| ticket_one_time_ticket | varchar(30) | primary key | |
| date_one_time_ticket | int(11) | ||
| robot_one_time_ticket | varchar(80) | ||
| email_one_time_ticket | varchar(100) |
notification_table
used for message tracking feature. If the list is configured for tracking, outgoing messages include a delivery status notification request and optionnaly a return receipt request.When DSN MDN are received by Syamp, they are store in this table in relation with the related list and message_id.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| date_notification | int(11) | |
|
| pk_notification | bigint(20) | primary key | Autoincrement key |
| message_id_notification | varchar(100) | initial message-id. This feild is used to search DSN and MDN related to a particular message | |
| recipient_notification | varchar(100) | email adresse of receipient for which a DSN or MDM was received | |
| reception_option_notification | varchar(20) | The subscription option of the subscriber when the related message was sent to the list. Ussefull because some receipient 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 MDM | |
| type_notification | enum('DSN', 'MDN') | Type of the notification (DSN or MDM) | |
| message_notification | longtext | The DSN or the MDN itself | |
| list_notification | varchar(50) | The listname the messaage was issued for | |
| robot_notification | varchar(80) | The robot the message is related to |
logs_table
Each important event is stored in this table. List owners and listmaster can search entries in this table using web interface..
| Fields | Type | Properties | Usage |
|---|---|---|---|
| id_logs | bigint(20) | primary key | Unique log's identifier |
| 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) | date 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) | name of the Sympa subroutine which initiated the log | |
| parameters_logs | varchar(100) | List of commas-separated 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) | 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) | name of the Sympa daemon which ran the action |
stat_table
Statistic item are store in this table, Sum average etc are stored in Stat_counter_table.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| daemon_stat | varchar(10) | ||
| parameter_stat | varchar(50) | ||
| list_stat | varchar(150) | ||
| id_stat | bigint(20) | primary key | |
| operation_stat | varchar(50) | ||
| email_stat | varchar(100) | ||
| user_ip_stat | varchar(100) | ||
| read_stat | tinyint(1) | ||
| date_stat | int(11) | ||
| robot_stat | varchar(80) |
stat_counter_table
Use in conjunction with stat_table for users statistics.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| list_counter | varchar(150) | ||
| end_date_counter | int(11) | ||
| robot_counter | varchar(80) | ||
| id_counter | bigint(20) | primary key | |
| beginning_date_counter | int(11) | ||
| data_counter | varchar(50) | ||
| variation_counter | int | ||
| total_counter | int |
admin_table
This table is a internal cash where list admin roles are stored. It is just a cash and and it does not need to saved. You may remove its content if needed. It will just make next Sympa start slower..
| Fields | Type | Properties | Usage |
|---|---|---|---|
| user_admin | varchar(100) | primary key | List admin email |
| list_admin | varchar(50) | primary key | Listname |
| 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 | date this user become a list admin | |
| update_admin | datetime | last update timestamp | |
| reception_admin | varchar(20) | email reception option for list managment messages | |
| visibility_admin | varchar(20) | admin user email can be hidden in the list web page description | |
| comment_admin | varchar(150) | ||
| 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 |
netidmap_table
.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| netid_netidmap | varchar(100) | primary key | |
| email_netidmap | varchar(100) | ||
| robot_netidmap | varchar(80) | primary key | |
| serviceid_netidmap | varchar(100) | primary key |
conf_tables
.
| Fields | Type | Properties | Usage |
|---|---|---|---|
| value_conf | varchar(300) | the value of parameter label_conf of robot robot_conf. | |
| robot_conf | varchar(80) | primary key | |
| label_conf | varchar(80) | primary key |