sympa_database(5)

1SYMPA_DATABASE(5)                sympa 6.2.64                SYMPA_DATABASE(5)
2
3
4

NAME

6       sympa_database - Structure of Sympa core database
7

DECRIPTION

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

NAME

DECRIPTION

SEE ALSO