====== Structure de la base ====== Liste des tables show tables; Description de la table SHOW COLUMNS FROM ; ou desc
; [[https://www.sympa.community/gpldoc/man/sympa_database.5.html|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 +-----------------------+-------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-------------------------------------+------+-----+---------+-------+ | comment_admin | varchar(150) | YES | | NULL | | | 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 | | | date_epoch_admin | int(11) | YES | | NULL | | | update_epoch_admin | int(11) | YES | | NULL | | +-----------------------+-------------------------------------+------+-----+---------+-------+ +-------------------------+-------------------------+ | Exemples | | +-------------------------+-------------------------+ | NULL | NULL | | 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 | | | | | | | +-------------------------+-------------------------+ Remarque : aucun enregistrement avec role_admin = 'listmaster' ===== bulkmailer_table ===== +-----------------------------+--------------+------+-----+---------+-------+ | 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 | | +-----------------------------+--------------+------+-----+---------+-------+ +----------------------------------------------------------------+ | 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> | +----------------------------------------------------------------+ ===== bulkspool_table ===== +----------------------------+---------------+------+-----+---------+-------+ | 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 | | +----------------------------+---------------+------+-----+---------+-------+ +----------------------------------------------------------------+ | Exemples | +----------------------------------------------------------------+ | 1 | | TUlNRS1WZXJzaW9 ..........OiB0ZXh0L3BsYWluOyBjaGFyc2V0PSJVVEYt | | 5ce15bcf0f13acf3a256e90243583206 | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +----------------------------------------------------------------+ ===== conf_table ===== +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | label_conf | varchar(80) | NO | PRI | | | | robot_conf | varchar(80) | NO | PRI | | | | value_conf | varchar(300) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ +----------------------------+ | Exemples | +----------------------------+ | color_0 | | listestest.inp.fr | | #DEE7F7 | +----------------------------+ Remarque : table vide ===== exclusion_table ===== +------------------+--------------+------+-----+---------+-------+ | 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 | | | +------------------+--------------+------+-----+---------+-------+ +----------------------------+ + Exemples | +----------------------------+ | 1330001530 | | test | | catherine@inp.fr | | | | | +----------------------------+ ===== 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 ===== +---------------------+------------------------------------+------+-----+---------+-------+ | 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 | | +---------------------+------------------------------------+------+-----+---------+-------+ +--------------------------------------+ | 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 | +--------------------------------------+ ===== logs_table ===== Actions sur la liste +-------------------+--------------+------+-----+---------+-------+ | 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 | | +-------------------+--------------+------+-----+---------+-------+ +--------------------------------------------------------------------+ | 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 | +-------------------------+----------------------+-------------------+ **action_logs** : DoMessage - DoCommand - DoForward - DoFile - set - sso_login - login - sso_login_succeeded - logout - add - del - sendMessage - distribute - reject - get_bounce - ===== netidmap_table ===== +--------------------+--------------+------+-----+---------+-------+ | 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 | | | +--------------------+--------------+------+-----+---------+-------+ Remarque : table vide ===== notification_table ===== à partir de la 6.2 +-------------------------------+-------------------+------+-----+---------+----------------+ | 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 | | +-------------------------------+-------------------+------+-----+---------+----------------+ +-------------------------------+ | Field | +-------------------------------+ | NULL | | NULL | | 1761810753 | | test-dsi | | 6056332@francetoner.user.com | | 581001 | | mail | | catherine@inpg.fr | | listes.inp.fr | | NULL | | NULL | +-------------------------------+ ===== one_time_ticket_table ===== +-----------------------------+--------------+------+-----+---------+-------+ | 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 | | | +-----------------------------+--------------+------+-----+---------+-------+ +-----------------------------------------------------------+ | 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 | +-----------------------------+-----------------------------+ ===== session_table ===== +----------------------+--------------+------+-----+---------+-------+ | 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 | | +----------------------+--------------+------+-----+---------+-------+ +------------------------------+ | Exemples | +------------------------------+ | ;auth="cas";data="" | | 1221048277 | | catherine@inpg.fr | | 2 | | 53763951170962 | | 199.199.199.199 | | inpg.fr | | 1221048270 | | 89437353428664 | | 1449494412 | +------------------------------+ ===== stat_counter_table ===== à partir de la 6.2 +------------------------+-------------+------+-----+---------+-------+ | 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 | | +------------------------+-------------+------+-----+---------+-------+ +----------------------------+ | Exemples | +----------------------------+ | 1446649200 | | 5 | | add_or_subscribe | | 1446652800 | | liste-test | | listestest.inp.fr | +----------------------------+ ===== stat_table ===== à partir de la 6.2 +----------------+--------------+------+-----+---------+-------+ | 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 | | +----------------+--------------+------+-----+---------+-------+ +-----------------------------+ | Exemples | +-----------------------------+ | task_manager | | 1441095868 | | marie.toto@inp.org | | dsi.test | | add_or_subscribe | | | | 1 | | listes.inp.fr | | NULL | +-----------------------------+ **daemon_stat** : sympa_msg - task_manager - wwsympa ===== subscriber_table ===== Information sur les abonnés d'une liste +-------------------------------+--------------+------+-----+---------+-------+ | 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 | | +-------------------------------+--------------+------+-----+---------+-------+ 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 +--------------------------------------------------------------------------------+ + Exemples | +--------------------------------------------------------------------------------+ | NULL | | 15 | | 1695055638 1757431985 11 5.1.1 | | 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 | | 0 | | 1681984984 | | 1766141362 | +--------------------------------------------------------------------------------+ ===== user_table ===== +------------------------+--------------+------+-----+---------+-------+ | 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 | | +------------------------+--------------+------+-----+---------+-------+ +---------------------------------------+ + Exemples | +---------------------------------------+ | NULL | | NULL | | catherine@inpg.fr | | NULL | | NULL | | NULL | | ;shared_mode="expert";arc_mode="thrd" | | 1444204201 | | 195.83.75.160 | | 1 | | 1449064749 | +---------------------------------------+