1SYMPA_DATABASE(5) sympa 6.2.64 SYMPA_DATABASE(5)
2
3
4
6 sympa_database - Structure of Sympa core database
7
9 Core database of Sympa is based on SQL. In following list of tables
10 and indexes, data types are based on MySQL/MariaDB. Corresponding
11 types are used by other platforms (PostgreSQL, SQLite, ...).
12
13 Tables
14 subscriber_table
15
16 This table store subscription, subscription option etc.
17
18 Fields:
19
20 user_subscriber varchar(100)
21 (Primary key)
22
23 email of subscriber
24
25 list_subscriber varchar(50)
26 (Primary key)
27
28 list name of a subscription
29
30 robot_subscriber varchar(80)
31 (Primary key)
32
33 robot (domain) of the list
34
35 reception_subscriber varchar(20)
36 reception format option of subscriber (digest, summary, etc.)
37
38 suspend_subscriber int(1)
39 boolean set to 1 if subscription is suspended
40
41 suspend_start_date_subscriber int(11)
42 the Unix time when message reception is suspended
43
44 suspend_end_date_subscriber int(11)
45 the Unix time when message reception should be restored
46
47 bounce_subscriber varchar(35)
48 FIXME
49
50 bounce_score_subscriber smallint(6)
51 FIXME
52
53 bounce_address_subscriber varchar(100)
54 FIXME
55
56 date_epoch_subscriber int(11) not null
57 date of subscription
58
59 update_epoch_subscriber int(11)
60 the last time when subscription is confirmed by subscriber
61
62 inclusion_subscriber int(11)
63 the last time when list user is synchronized with data source
64
65 inclusion_ext_subscriber int(11)
66 the last time when list user is synchronized with external data
67 source
68
69 inclusion_label_subscriber varchar(50)
70 name of data source
71
72 comment_subscriber varchar(150)
73 free form name
74
75 number_messages_subscriber int(5) not null
76 the number of message the subscriber sent
77
78 visibility_subscriber varchar(20)
79 FIXME
80
81 topics_subscriber varchar(200)
82 topic subscription specification
83
84 subscribed_subscriber int(1)
85 boolean set to 1 if subscriber comes from ADD or SUB
86
87 custom_attribute_subscriber text
88 FIXME
89
90 Indexes:
91
92 subscriber_user_index
93 user_subscriber
94
95 user_table
96
97 The user_table is mainly used to manage login from web interface. A
98 subscriber may not appear in the user_table if they never log through
99 the web interface.
100
101 Fields:
102
103 email_user varchar(100)
104 (Primary key)
105
106 email of user
107
108 password_user varchar(64)
109 password are stored as finger print
110
111 gecos_user varchar(150)
112 display name of user
113
114 last_login_date_user int(11)
115 Unix time of last login, printed in login result for security
116 purpose
117
118 last_login_host_user varchar(60)
119 host of last login, printed in login result for security purpose
120
121 wrong_login_count_user int(11)
122 login attempt count, used to prevent brute force attack
123
124 last_active_date_user int(11)
125 the last Unix time when this user was confirmed their activity by
126 purge_user_table task
127
128 cookie_delay_user int(11)
129 FIXME
130
131 lang_user varchar(10)
132 user language preference
133
134 attributes_user text
135 FIXME
136
137 data_user text
138 FIXME
139
140 inclusion_table
141
142 Inclusion table is used in order to manage lists included from /
143 including subscribers of other lists.
144
145 Fields:
146
147 target_inclusion varchar(131)
148 (Primary key)
149
150 list ID of including list
151
152 role_inclusion enum('member','owner','editor')
153 (Primary key)
154
155 role of included user
156
157 source_inclusion varchar(131)
158 (Primary key)
159
160 list ID of included list
161
162 update_epoch_inclusion int(11)
163 the date this entry was created or updated
164
165 exclusion_table
166
167 Exclusion table is used in order to manage unsubscription for
168 subscriber included from an external data source.
169
170 Fields:
171
172 list_exclusion varchar(57)
173 (Primary key)
174
175 FIXME
176
177 robot_exclusion varchar(80)
178 (Primary key)
179
180 FIXME
181
182 user_exclusion varchar(100)
183 (Primary key)
184
185 FIXME
186
187 family_exclusion varchar(50)
188 (Primary key)
189
190 FIXME
191
192 date_exclusion int(11)
193 FIXME
194
195 session_table
196
197 Management of HTTP session.
198
199 Fields:
200
201 id_session varchar(30)
202 (Primary key)
203
204 the identifier of the database record
205
206 prev_id_session varchar(30)
207 previous identifier of the database record
208
209 start_date_session int(11) not null
210 the date when the session was created
211
212 date_session int(11) not null
213 Unix time of the last use of this session. It is used in order to
214 expire old sessions
215
216 refresh_date_session int(11)
217 Unix time of the last refresh of this session. It is used in order
218 to refresh available sessions
219
220 remote_addr_session varchar(60)
221 the IP address of the computer from which the session was created
222
223 robot_session varchar(80)
224 the virtual host in which the session was created
225
226 email_session varchar(100)
227 the email associated to this session
228
229 hit_session int(11)
230 the number of hit performed during this session. Used to detect
231 crawlers
232
233 data_session text
234 parameters attached to this session that don't have a dedicated
235 column in the database
236
237 Indexes:
238
239 session_prev_id_index
240 prev_id_session
241
242 one_time_ticket_table
243
244 One time ticket are random value used for authentication challenge. A
245 ticket is associated with a context which look like a session.
246
247 Fields:
248
249 ticket_one_time_ticket varchar(30)
250 (Primary key)
251
252 FIXME
253
254 email_one_time_ticket varchar(100)
255 FIXME
256
257 robot_one_time_ticket varchar(80)
258 FIXME
259
260 date_one_time_ticket int(11)
261 FIXME
262
263 data_one_time_ticket varchar(200)
264 FIXME
265
266 remote_addr_one_time_ticket varchar(60)
267 FIXME
268
269 status_one_time_ticket varchar(60)
270 FIXME
271
272 notification_table
273
274 Used for message tracking feature. If the list is configured for
275 tracking, outgoing messages include a delivery status notification
276 request and optionally a message disposition notification request. When
277 DSN and MDN are received by Sympa, they are stored in this table in
278 relation with the related list and message ID.
279
280 Fields:
281
282 pk_notification bigint(20) auto_increment
283 (Primary key)
284
285 autoincrement key
286
287 message_id_notification varchar(100)
288 initial message-id. This field is used to search DSN and MDN
289 related to a particular message
290
291 recipient_notification varchar(100)
292 email address of recipient for which a DSN or MDN was received
293
294 reception_option_notification varchar(20)
295 the subscription option of the subscriber when the related message
296 was sent to the list. Useful because some recipient may have option
297 such as //digest// or //nomail//
298
299 status_notification varchar(100)
300 value of notification
301
302 arrival_date_notification varchar(80)
303 reception date of latest DSN or MDN
304
305 arrival_epoch_notification int(11)
306 reception date of latest DSN or MDN
307
308 type_notification enum('DSN', 'MDN')
309 type of the notification (DSN or MDN)
310
311 list_notification varchar(50)
312 the listname the message was issued for
313
314 robot_notification varchar(80)
315 the robot the message is related to
316
317 date_notification int(11) not null
318 FIXME
319
320 logs_table
321
322 Each important event is stored in this table. List owners and
323 listmaster can search entries in this table using web interface.
324
325 Fields:
326
327 user_email_logs varchar(100)
328 e-mail address of the message sender or email of identified web
329 interface user (or soap user)
330
331 date_logs int(11) not null
332 date when the action was executed
333
334 usec_logs int(6)
335 subsecond in microsecond when the action was executed
336
337 robot_logs varchar(80)
338 name of the robot in which context the action was executed
339
340 list_logs varchar(50)
341 name of the mailing-list in which context the action was executed
342
343 action_logs varchar(50) not null
344 name of the Sympa subroutine which initiated the log
345
346 parameters_logs varchar(100)
347 comma-separated list of parameters. The amount and type of
348 parameters can differ from an action to another
349
350 target_email_logs varchar(100)
351 e-mail address (if any) targeted by the message
352
353 msg_id_logs varchar(255)
354 identifier of the message which triggered the action
355
356 status_logs varchar(10) not null
357 exit status of the action. If it was an error, it is likely that
358 the error_type_logs field will contain a description of this error
359
360 error_type_logs varchar(150)
361 name of the error string - if any - issued by the subroutine
362
363 client_logs varchar(100)
364 IP address of the client machine from which the message was sent
365
366 daemon_logs varchar(10) not null
367 name of the Sympa daemon which ran the action
368
369 stat_table
370
371 Statistics item are stored in this table, Sum average and so on are
372 stored in stat_counter_table.
373
374 Fields:
375
376 date_stat int(11) not null
377 FIXME
378
379 email_stat varchar(100)
380 FIXME
381
382 operation_stat varchar(50) not null
383 FIXME
384
385 list_stat varchar(50)
386 FIXME
387
388 daemon_stat varchar(20)
389 FIXME
390
391 user_ip_stat varchar(100)
392 FIXME
393
394 robot_stat varchar(80) not null
395 FIXME
396
397 parameter_stat varchar(50)
398 FIXME
399
400 read_stat tinyint(1) not null
401 FIXME
402
403 Indexes:
404
405 stats_user_index
406 email_stat
407
408 stat_counter_table
409
410 Used in conjunction with stat_table for users statistics.
411
412 Fields:
413
414 end_date_counter int(11)
415 FIXME
416
417 beginning_date_counter int(11) not null
418 FIXME
419
420 data_counter varchar(50) not null
421 FIXME
422
423 robot_counter varchar(80) not null
424 FIXME
425
426 list_counter varchar(50)
427 FIXME
428
429 count_counter int
430 FIXME
431
432 admin_table
433
434 This table is an internal cash where list admin roles are stored. It is
435 just a cash and it does not need to be saved. You may remove its
436 content if needed. It will just make next Sympa startup slower.
437
438 Fields:
439
440 user_admin varchar(100)
441 (Primary key)
442
443 list admin email
444
445 list_admin varchar(50)
446 (Primary key)
447
448 list name
449
450 robot_admin varchar(80)
451 (Primary key)
452
453 list domain
454
455 role_admin enum('listmaster','owner','editor')
456 (Primary key)
457
458 a role of this user for this list (editor, owner or listmaster
459 which a kind of list owner too)
460
461 profile_admin enum('privileged','normal')
462 privilege level for this owner, value //normal// or //privileged//.
463 The related privilege are listed in edit_list.conf.
464
465 date_epoch_admin int(11) not null
466 date this user become a list admin
467
468 update_epoch_admin int(11)
469 last update time
470
471 inclusion_admin int(11)
472 the last time when list user is synchronized with data source
473
474 inclusion_ext_admin int(11)
475 the last time when list user is synchronized with external data
476 source
477
478 inclusion_label_admin varchar(50)
479 name of data source
480
481 reception_admin varchar(20)
482 email reception option for list management messages
483
484 visibility_admin varchar(20)
485 admin user email can be hidden in the list web page description
486
487 comment_admin varchar(150)
488 FIXME
489
490 subscribed_admin int(1)
491 set to 1 if user is list admin by definition in list config file
492
493 info_admin varchar(150)
494 private information usually dedicated to listmasters who needs some
495 additional information about list owners
496
497 Indexes:
498
499 admin_user_index
500 user_admin
501
502 netidmap_table
503
504 FIXME
505
506 Fields:
507
508 netid_netidmap varchar(100)
509 (Primary key)
510
511 FIXME
512
513 serviceid_netidmap varchar(100)
514 (Primary key)
515
516 FIXME
517
518 robot_netidmap varchar(80)
519 (Primary key)
520
521 FIXME
522
523 email_netidmap varchar(100)
524 FIXME
525
526 conf_table
527
528 FIXME
529
530 Fields:
531
532 robot_conf varchar(80)
533 (Primary key)
534
535 FIXME
536
537 label_conf varchar(80)
538 (Primary key)
539
540 FIXME
541
542 value_conf varchar(300)
543 the value of parameter //label_conf// of robot //robot_conf//.
544
545 list_table
546
547 The list_table holds cached list config and some items to help
548 searching lists.
549
550 Fields:
551
552 name_list varchar(50)
553 (Primary key)
554
555 name of the list
556
557 robot_list varchar(80)
558 (Primary key)
559
560 name of the robot (domain) the list belongs to
561
562 family_list varchar(50)
563 name of the family the list belongs to
564
565 status_list
566 enum('open','closed','pending','error_config','family_closed')
567 status of the list
568
569 creation_email_list varchar(100)
570 email of user who created the list
571
572 creation_epoch_list int(11)
573 UNIX time when the list was created
574
575 update_email_list varchar(100)
576 email of user who updated the list
577
578 update_epoch_list int(11)
579 UNIX time when the list was updated
580
581 searchkey_list varchar(255)
582 case-folded list subject to help searching
583
584 web_archive_list tinyint(1)
585 if the list has archives
586
587 topics_list varchar(255)
588 topics of the list, separated and enclosed by commas
589
590 total_list int(7)
591 estimated number of subscribers
592
594 Sympa Administration Manual.
595 <https://sympa-community.github.io/manual/>.
596
597
598
5996.2.64 2021-07-15 SYMPA_DATABASE(5)