Ceci est une ancienne révision du document !
Table des matières
Structure de la base
Liste des tables
show tables;
Description de la table
SHOW COLUMNS FROM <table>;
Description complète de la base v6
Version de sympa pour la base : ~sympa/etc/data_structure
admin_table
Propriétaires et modérateurs d'une liste <columns>
+-----------------------+-------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------------------------------+------+-----+---------+-------+
| comment_admin | varchar(150) | YES | | NULL | |
| date_admin | datetime | NO | | | |
| include_sources_admin | varchar(50) | YES | | NULL | |
| included_admin | int(1) | YES | | NULL | |
| info_admin | varchar(150) | YES | | NULL | |
| list_admin | varchar(50) | NO | PRI | | |
| profile_admin | enum('privileged','normal') | YES | | NULL | |
| reception_admin | varchar(20) | YES | | NULL | |
| robot_admin | varchar(80) | NO | PRI | | |
| role_admin | enum('listmaster','owner','editor') | NO | PRI | | |
| subscribed_admin | int(1) | YES | | NULL | |
| update_admin | datetime | YES | | NULL | |
| user_admin | varchar(100) | NO | PRI | | |
| visibility_admin | varchar(20) | YES | | NULL | |
+-----------------------+-------------------------------------+------+-----+---------+-------+
<newcolumn>
+-------------------------+-------------------------+ | Exemples | | +-------------------------+-------------------------+ | NULL | NULL | | 2008-09-10 10:15:25 | 2008-09-15 16:03:31 | | NULL | a386b2d3 | | 0 | 1 | | NULL | NULL | | dsi-test | spip.sos | | normal | normal | | mail | mail | | inpg.fr | inpg.fr | | owner | editor | | 1 | 0 | | 2008-09-10 10:15:25 | 2008-09-15 16:03:31 | | cath@inpg.fr | cath@inpg.fr | | noconceal | noconceal | +-------------------------+-------------------------+
</columns>
Remarque : aucun enregistrement avec role_admin = 'listmaster'
bulkmailer_table
<columns>
+-----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+--------------+------+-----+---------+-------+ | delivery_date_bulkmailer | int(11) | YES | | NULL | | | listname_bulkmailer | varchar(50) | YES | | NULL | | | lock_bulkmailer | varchar(30) | YES | | NULL | | | messagekey_bulkmailer | varchar(80) | NO | PRI | NULL | | | packetid_bulkmailer | varchar(33) | NO | PRI | NULL | | | priority_message_bulkmailer | smallint(10) | YES | | NULL | | | priority_packet_bulkmailer | smallint(10) | YES | | NULL | | | receipients_bulkmailer | text | YES | | NULL | | | reception_date_bulkmailer | int(11) | YES | | NULL | | | returnpath_bulkmailer | varchar(100) | YES | | NULL | | | robot_bulkmailer | varchar(80) | YES | | NULL | | | verp_bulkmailer | int(1) | YES | | NULL | | | merge_bulkmailer | int(1) | YES | | NULL | | | messageid_bulkmailer | varchar(200) | YES | | NULL | | +-----------------------------+--------------+------+-----+---------+-------+
<newcolumn>
+----------------------------------------------------------------+ | Exemples | +----------------------------------------------------------------+ | 1403850253 | | dsi | | crocus.inpg.fr4045 | | 0dd0889d972ac87606835d94da536f61 | | a998528df1ca5e9f28a7279dda3cf346 | | 5 | | 10 | | toto@inp.fr,titi@inp.fr,tata@inp.fr | | 1403850253 | | dsi-owner@grenoble-inp.fr | | listes.inp.fr | | 0 | | 0 | | <801bf83a7389b15e001fc6c8718ad4a6@www.supramarket.fr> | +----------------------------------------------------------------+
</columns>
bulkspool_table
<columns>
+----------------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+---------------+------+-----+---------+-------+ | lock_bulkspool | int(1) | YES | | NULL | | | message_bulkspool | longtext | YES | | NULL | | | messagekey_bulkspool | varchar(33) | NO | PRI | NULL | | | dkim_d_bulkspool | varchar(50) | YES | | NULL | | | dkim_header_list_bulkspool | varchar(500) | YES | | NULL | | | dkim_i_bulkspool | varchar(100) | YES | | NULL | | | dkim_privatekey_bulkspool | varchar(1000) | YES | | NULL | | | dkim_selector_bulkspool | varchar(50) | YES | | NULL | | | messageid_bulkspool | varchar(200) | YES | | NULL | | +----------------------------+---------------+------+-----+---------+-------+
<newcolumn>
+----------------------------------------------------------------+ | Exemples | +----------------------------------------------------------------+ | 1 | | TUlNRS1WZXJzaW9 ..........OiB0ZXh0L3BsYWluOyBjaGFyc2V0PSJVVEYt | | 5ce15bcf0f13acf3a256e90243583206 | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +----------------------------------------------------------------+
</columns>
conf_table
<columns>
+------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | label_conf | varchar(80) | NO | PRI | | | | robot_conf | varchar(80) | NO | PRI | | | | value_conf | varchar(300) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+
<newcolumn>
+----------------------------+ | Exemples | +----------------------------+ | color_0 | | listestest.inp.fr | | #DEE7F7 | +----------------------------+
</columns>
Remarque : table vide
exclusion_table
<columns>
+------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | date_exclusion | int(11) | YES | | NULL | | | list_exclusion | varchar(57) | NO | PRI | | | | user_exclusion | varchar(100) | NO | PRI | NULL | | | robot_exclusion | varchar(80) | NO | PRI | | | | family_exclusion | varchar(50) | NO | PRI | | | +------------------+--------------+------+-----+---------+-------+
<newcolumn>
+----------------------------+ + Exemples | +----------------------------+ | 1330001530 | | test | | catherine@inp.fr | | | | | +----------------------------+
</columns>
inclusion_table
+------------------------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------------------+------+-----+---------+-------+
| role_inclusion | enum('member','owner','editor') | NO | PRI | member | |
| source_inclusion | varchar(131) | NO | PRI | | | liste incluse
| target_inclusion | varchar(131) | NO | PRI | | | liste qui inclue
| update_epoch_inclusion | int(11) | YES | | NULL | |
+------------------------+---------------------------------+------+-----+---------+-------+
list_table
<columns>
+---------------------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------------------------+------+-----+---------+-------+
| creation_email_list | varchar(100 | NO | | NULL | |
| creation_epoch_list | datetime | YES | | NULL | |
| editors_list | varchar(100) | NO | | NULL | |
| name_list | varchar(100) | NO | PRI | NULL | |
| owners_list | varchar(100) | NO | | NULL | |
| path_list | varchar(100) | NO | | NULL | |
| robot_list | varchar(100) | NO | PRI | NULL | |
| status_list | enum('open','closed','pending', | NO | | NULL | |
| | 'error_config','family_closed') | | | | |
| subject_list | varchar(100) | NO | | NULL | |
| topics_list | varchar(100) | NO | | NULL | |
| web_archive_list | tinyint(1) | NO | | NULL | |
| family_list | varchar(50) | YES | | NULL | |
| searchkey_list | varchar(255) | YES | | NULL | |
| total_list | int(7) | YES | | NULL | |
| update_email_list | varchar(100) | YES | | NULL | |
| update_epoch_list | int(11) | YES | | NULL | |
+---------------------+------------------------------------+------+-----+---------+-------+
<newcolumn>
+--------------------------------------+ | Exemples | +--------------------------------------+ | | | NULL | | catherine@inp.fr | | dsi-test | | catherine@inp.fr | | | | liste_test.inp.fr | | open | | | | test pour la DSI | | dsi/zmen | | 1 | | NULL | | NULL | | NULL | | NULL | | NULL | +--------------------------------------+
</columns>
logs_table
Actions sur la liste <columns>
+-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | action_logs | varchar(50) | NO | | | | | client_logs | varchar(100) | YES | | NULL | | | daemon_logs | varchar(10) | NO | | | | | date_logs | int(11) | NO | | | | | error_type_logs | varchar(150) | YES | | NULL | | | id_logs | bigint(20) | NO | PRI | | | | list_logs | varchar(50) | YES | | NULL | | | msg_id_logs | varchar(255) | YES | | NULL | | | parameters_logs | varchar(100) | YES | | NULL | | | robot_logs | varchar(80) | YES | | NULL | | | status_logs | varchar(10) | NO | | | | | target_email_logs | varchar(100) | YES | | NULL | | | user_email_logs | varchar(100) | YES | | NULL | | | usec_logs | int(6) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+
<newcolumn>
+--------------------------------------------------------------------+ | Exemples | +-------------------------+----------------------+-------------------+ | DoMessage | DoMessage | get_bounce | | NULL | NULL | NULL | | sympa | sympa | bounced | | 1220980694 | 1220980466 | 1220994974 | | rejected_authorization | | 5.0.0 | | 1220980694344066 | 1220980466616659 | 12209949741875 | | dsi-test | dsi-test | dsi-test | | 171806.D1C001@orange.fr | 1422.000A1@orange.fr | | | 171806.D1C001@orange.fr | 1422.000A1@orange.fr | NULL | | inpg.fr | inpg.fr | inpg.fr | | error | success | error | | | | skeltryx@yahoo.fr | | prima.espana@gmail.com | grace@libero.it | anonymous | | 0 | 2645 | 0 | +-------------------------+----------------------+-------------------+
</columns>
action_logs :
DoMessage - DoCommand - DoForward - DoFile - set - sso_login - login - sso_login_succeeded - logout - add - del - sendMessage - distribute - reject - get_bounce -
netidmap_table
<columns>
+--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | email_netidmap | varchar(100) | YES | | NULL | | | netid_netidmap | varchar(100) | NO | PRI | | | | robot_netidmap | varchar(80) | NO | PRI | | | | serviceid_netidmap | varchar(100) | NO | PRI | | | +--------------------+--------------+------+-----+---------+-------+
</columns>
Remarque : table vide
notification_table
à partir de la 6.2 <columns>
+-------------------------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+-------------------+------+-----+---------+----------------+
| arrival_date_notification | varchar(80) | YES | | NULL | |
| arrival_epoch_notification | int(11) | YES | | NULL | |
| date_notification | int(11) | YES | | NULL | |
| list_notification | varchar(50) | YES | | NULL | |
| message_id_notification | varchar(100) | YES | | NULL | |
| pk_notification | bigint(20) | NO | PRI | NULL | auto_increment |
| reception_option_notification | varchar(20) | YES | | NULL | |
| recipient_notification | varchar(100) | YES | | NULL | |
| robot_notification | varchar(80) | YES | | NULL | |
| status_notification | varchar(100) | YES | | NULL | |
| type_notification | enum('DSN','MDN') | YES | | NULL | |
+-------------------------------+-------------------+------+-----+---------+----------------+
<newcolumn>
+-------------------------------+ | Field | +-------------------------------+ | NULL | | NULL | | 1761810753 | | test-dsi | | 6056332@francetoner.user.com | | 581001 | | mail | | catherine@inpg.fr | | listes.inp.fr | | NULL | | NULL | +-------------------------------+
</columns>
one_time_ticket_table
<columns>
+-----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+--------------+------+-----+---------+-------+ | data_one_time_ticket | varchar(200) | YES | | NULL | | | date_one_time_ticket | int(11) | YES | | NULL | | | email_one_time_ticket | varchar(100) | YES | | NULL | | | remote_addr_one_time_ticket | varchar(60) | YES | | NULL | | | robot_one_time_ticket | varchar(80) | YES | | NULL | | | status_one_time_ticket | varchar(60) | YES | | NULL | | | ticket_one_time_ticket | varchar(30) | NO | PRI | | | +-----------------------------+--------------+------+-----+---------+-------+
<newcolumn>
+-----------------------------------------------------------+ | Exemples | +-----------------------------+-----------------------------+ | modindex/spip.sos | get_pending_lists | | 1760939434 | 1760939434 | | catherine@inp.fr | catherine@inp.fr | | mail | NULL | | listes.inp.fr | listes.inp.fr | | open | 199.119.999.995 | | 32591115995440 | 32591115995440 | +-----------------------------+-----------------------------+
</columns>
session_table
<columns>
+----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------+-------+ | data_session | text | YES | | NULL | | | date_session | int(11) | NO | | NULL | | | email_session | varchar(100) | YES | | NULL | | | hit_session | int(11) | YES | | NULL | | | id_session | varchar(30) | NO | PRI | NULL | | | remote_addr_session | varchar(60) | YES | | NULL | | | robot_session | varchar(80) | YES | | NULL | | | start_date_session | int(11) | NO | | NULL | | | prev_id_session | varchar(30) | YES | | NULL | | | refresh_date_session | int(11) | YES | | NULL | | +----------------------+--------------+------+-----+---------+-------+
<newcolumn>
+------------------------------+ | Exemples | +------------------------------+ | ;auth="cas";data="" | | 1221048277 | | catherine@inpg.fr | | 2 | | 53763951170962 | | 199.199.199.199 | | inpg.fr | | 1221048270 | | 89437353428664 | | 1449494412 | +------------------------------+
</columns>
stat_counter_table
à partir de la 6.2 <columns>
+------------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-------------+------+-----+---------+-------+ | beginning_date_counter | int(11) | YES | | NULL | | | count_counter | int(11) | YES | | NULL | | | data_counter | varchar(50) | YES | | NULL | | | end_date_counter | int(11) | YES | | NULL | | | list_counter | varchar(50) | YES | | NULL | | | robot_counter | varchar(80) | YES | | NULL | | +------------------------+-------------+------+-----+---------+-------+
<newcolumn>
+----------------------------+ | Exemples | +----------------------------+ | 1446649200 | | 5 | | add_or_subscribe | | 1446652800 | | liste-test | | listestest.inp.fr | +----------------------------+
</columns>
stat_table
à partir de la 6.2 <columns>
+----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | daemon_stat | varchar(20) | YES | | NULL | | | date_stat | int(11) | YES | | NULL | | | email_stat | varchar(100) | YES | MUL | NULL | | | list_stat | varchar(50) | YES | | NULL | | | operation_stat | varchar(50) | YES | | NULL | | | parameter_stat | varchar(50) | YES | | NULL | | | read_stat | tinyint(1) | YES | | NULL | | | robot_stat | varchar(80) | YES | | NULL | | | user_ip_stat | varchar(100) | YES | | NULL | | +----------------+--------------+------+-----+---------+-------+
<newcolumn>
+-----------------------------+ | Exemples | +-----------------------------+ | task_manager | | 1441095868 | | marie.toto@inp.org | | dsi.test | | add_or_subscribe | | | | 1 | | listes.inp.fr | | NULL | +-----------------------------+
</columns>
daemon_stat : sympa_msg - task_manager - wwsympa
subscriber_table
Information sur les abonnés d'une liste <columns>
+-------------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+--------------+------+-----+---------+-------+ | bounce_address_subscriber | varchar(100) | YES | | NULL | | | bounce_score_subscriber | smallint(6) | YES | | NULL | | | bounce_subscriber | varchar(35) | YES | | NULL | | | comment_subscriber | varchar(150) | YES | | NULL | | | include_sources_subscriber | varchar(50) | YES | | NULL | | | included_subscriber | int(1) | YES | | NULL | | | list_subscriber | varchar(50) | NO | PRI | | | | reception_subscriber | varchar(20) | YES | | NULL | | | robot_subscriber | varchar(80) | NO | PRI | | | | subscribed_subscriber | int(1) | YES | | NULL | | | topics_subscriber | varchar(200) | YES | | NULL | | | update_subscriber | datetime | YES | | NULL | | | user_subscriber | varchar(100) | NO | PRI | | | | visibility_subscriber | varchar(20) | YES | | NULL | | | custom_attribute_subscriber | text | YES | | NULL | | | suspend_end_date_subscriber | int(11) | YES | | NULL | | | suspend_start_date_subscriber | int(11) | YES | | NULL | | | suspend_subscriber | int(1) | YES | | NULL | | | number_messages_subscriber | int(5) | YES | | NULL | | | date_epoch_subscriber | int(11) | YES | | NULL | | | update_epoch_subscriber | int(11) | YES | | NULL | | +-------------------------------+--------------+------+-----+---------+-------+
<note>Quand les abonnés d'une liste ne proviennent pas d'une autre liste :
- included_subscriber est à 0 ( 1 si proviennent d'une autre liste )
- include_sources_subscriber est vide ou à NULL
</note> <newcolumn>
+--------------------------------------------------------------------------------+ + Exemples | +--------------------------------------------------------------------------------+ | NULL | | 15 | | NULL | | NULL | | NULL | | 0 | | dsi-test | | mail | | listestest.inp.fr | | 1 | | NULL | | 2008-02-12 11:03:24 | | catherine@inpg.fr | | noconceal | | NULL | | NULL | | NULL | | NULL | | NULL | +--------------------------------------------------------------------------------+
</columns>
user_table
<columns>
+------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+--------------+------+-----+---------+-------+ | attributes_user | text | YES | | NULL | | | cookie_delay_user | int(11) | YES | | NULL | | | email_user | varchar(100) | NO | PRI | | | | gecos_user | varchar(150) | YES | | NULL | | | lang_user | varchar(10) | YES | | NULL | | | password_user | varchar(40) | YES | | NULL | | | data_user | text | YES | | NULL | | | last_login_date_user | int(11) | YES | | NULL | | | last_login_host_user | varchar(60) | YES | | NULL | | | wrong_login_count_user | int(11) | YES | | NULL | | | last_active_date_user | int(11) | YES | | NULL | | +------------------------+--------------+------+-----+---------+-------+
<newcolumn>
+---------------------------------------+ + Exemples | +---------------------------------------+ | NULL | | NULL | | catherine@inpg.fr | | NULL | | NULL | | NULL | | ;shared_mode="expert";arc_mode="thrd" | | 1444204201 | | 195.83.75.160 | | 1 | | 1449064749 | +---------------------------------------+
</columns>
