DatabaseDescription.pm 43.3 KB
Newer Older
sikeda's avatar
sikeda committed
1
2
3
# -*- indent-tabs-mode: nil; -*-
# vim:ft=perl:et:sw=4
# $Id$
4

5
# Sympa - SYsteme de Multi-Postage Automatique
6
7
8
9
#
# Copyright (c) 1997, 1998, 1999 Institut Pasteur & Christophe Wolfhugel
# Copyright (c) 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005,
# 2006, 2007, 2008, 2009, 2010, 2011 Comite Reseau des Universites
10
# Copyright (c) 2011, 2012, 2013, 2014, 2015, 2016, 2017 GIP RENATER
11
12
13
14
15
16
17
18
19
20
21
22
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
23
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
24
25

package Sympa::DatabaseDescription;
26

27
use strict;
28
use warnings;
29

30
31
32
33
34
35
use Sympa::Constants;

my $email_struct  = sprintf 'varchar(%d)', Sympa::Constants::EMAIL_LEN();
my $family_struct = sprintf 'varchar(%d)', Sympa::Constants::FAMILY_LEN();
my $list_struct   = sprintf 'varchar(%d)', Sympa::Constants::LIST_LEN();
my $robot_struct  = sprintf 'varchar(%d)', Sympa::Constants::ROBOT_LEN();
36
37
my $list_id_struct = sprintf 'varchar(%d)',
    Sympa::Constants::LIST_LEN() + 1 + Sympa::Constants::ROBOT_LEN();
38

39
40
my %full_db_struct = (
    'subscriber_table' => {
41
42
        'fields' => {
            'user_subscriber' => {
43
                'struct'   => $email_struct,
44
45
46
47
48
49
                'doc'      => 'email of subscriber',
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 1
            },
            'list_subscriber' => {
50
                'struct'   => $list_struct,
51
52
53
54
55
56
                'doc'      => 'list name of a subscription',
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 2
            },
            'robot_subscriber' => {
57
                'struct'   => $robot_struct,
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
                'doc'      => 'robot (domain) of the list',
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 3
            },
            'reception_subscriber' => {
                'struct' => 'varchar(20)',
                'doc' =>
                    'reception format option of subscriber (digest, summary, etc.)',
                'order' => 4,
            },
            'suspend_subscriber' => {
                'struct' => 'int(1)',
                'doc'    => 'boolean set to 1 if subscription is suspended',
                'order'  => 5,
            },
            'suspend_start_date_subscriber' => {
                'struct' => 'int(11)',
sikeda's avatar
sikeda committed
76
                'doc' => 'the Unix time when message reception is suspended',
77
78
79
80
81
                'order' => 6,
            },
            'suspend_end_date_subscriber' => {
                'struct' => 'int(11)',
                'doc' =>
sikeda's avatar
sikeda committed
82
                    'the Unix time when message reception should be restored',
83
84
85
86
87
88
89
90
91
92
93
94
95
                'order' => 7,
            },
            'bounce_subscriber' => {
                'struct' => 'varchar(35)',
                'doc'    => 'FIXME',
                'order'  => 8,
            },
            'bounce_score_subscriber' => {
                'struct' => 'smallint(6)',
                'doc'    => 'FIXME',
                'order'  => 9,
            },
            'bounce_address_subscriber' => {
96
                'struct' => $email_struct,
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
                'doc'    => 'FIXME',
                'order'  => 10,
            },
            'date_subscriber' => {
                'struct'   => 'datetime',
                'doc'      => 'date of subscription',
                'not_null' => 1,
                'order'    => 11,
            },
            'update_subscriber' => {
                'struct' => 'datetime',
                'doc' =>
                    'the latest date where subscription is confirmed by subscriber',
                'order' => 12,
            },
            'comment_subscriber' => {
                'struct' => 'varchar(150)',
sikeda's avatar
sikeda committed
114
                'doc'    => 'free form name',
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
                'order'  => 13,
            },
            'number_messages_subscriber' => {
                'struct'   => 'int(5)',
                'doc'      => 'the number of message the subscriber sent',
                'not_null' => 1,
                'order'    => 5,
                'order'    => 14,
            },
            'visibility_subscriber' => {
                'struct' => 'varchar(20)',
                'doc'    => 'FIXME',
                'order'  => 15,
            },
            'topics_subscriber' => {
                'struct' => 'varchar(200)',
                'doc'    => 'topic subscription specification',
                'order'  => 16,
            },
            'subscribed_subscriber' => {
                'struct' => 'int(1)',
                'doc' =>
                    'boolean set to 1 if subscriber comes from ADD or SUB',
                'order' => 17,
            },
            'included_subscriber' => {
                'struct' => 'int(1)',
                'doc' =>
                    'boolean, set to 1 is subscriber comes from an external datasource. Note that included_subscriber and subscribed_subscriber can both value 1',
                'order' => 18,
            },
            'include_sources_subscriber' => {
                'struct' => 'varchar(50)',
                'doc' =>
149
                    'comma separated list of datasource that contain this subscriber',
150
151
152
153
154
                'order' => 19,
            },
            'custom_attribute_subscriber' => {
                'struct' => 'text',
                'doc'    => 'FIXME',
Chris Lamb's avatar
Chris Lamb committed
155
                'order'  => 20,
156
            },
157

158
159
160
        },
        'doc'   => 'This table store subscription, subscription option etc.',
        'order' => 1,
161
    },
162
163
164
    'user_table' => {
        'fields' => {
            'email_user' => {
165
                'struct'   => $email_struct,
sikeda's avatar
sikeda committed
166
                'doc'      => 'email of user',
167
168
                'primary'  => 1,
                'not_null' => 1,
sikeda's avatar
sikeda committed
169
                'order'    => 1,
170
171
172
            },
            'gecos_user' => {
                'struct' => 'varchar(150)',
sikeda's avatar
sikeda committed
173
                'doc'    => 'display name of user',
174
175
176
177
178
179
180
181
182
183
                'order'  => 3,
            },
            'password_user' => {
                'struct' => 'varchar(40)',
                'doc'    => 'password are stored as fringer print',
                'order'  => 2,
            },
            'last_login_date_user' => {
                'struct' => 'int(11)',
                'doc' =>
sikeda's avatar
sikeda committed
184
                    'Unix time of last login, printed in login result for security purpose',
185
186
187
188
189
190
191
192
193
194
195
                'order' => 4,
            },
            'last_login_host_user' => {
                'struct' => 'varchar(60)',
                'doc' =>
                    'host of last login, printed in login result for security purpose',
                'order' => 5,
            },
            'wrong_login_count_user' => {
                'struct' => 'int(11)',
                'doc' =>
196
                    'login attempt count, used to prevent brute force attack',
197
198
                'order' => 6,
            },
199
200
201
            'last_active_date_user' => {
                'struct' => 'int(11)',
                'doc' =>
sikeda's avatar
sikeda committed
202
                    'the last Unix time when this user was confirmed their activity by purge_user_table task',
203
204
                'order' => 7,
            },
205
206
207
            'cookie_delay_user' => {
                'struct' => 'int(11)',
                'doc'    => 'FIXME',
sikeda's avatar
sikeda committed
208
                'order'  => 8,
209
210
211
            },
            'lang_user' => {
                'struct' => 'varchar(10)',
212
                'doc'    => 'user language preference',
sikeda's avatar
sikeda committed
213
                'order'  => 9,
214
215
216
217
            },
            'attributes_user' => {
                'struct' => 'text',
                'doc'    => 'FIXME',
sikeda's avatar
sikeda committed
218
                'order'  => 10,
219
220
221
222
            },
            'data_user' => {
                'struct' => 'text',
                'doc'    => 'FIXME',
sikeda's avatar
sikeda committed
223
                'order'  => 11,
224
225
226
227
228
            },
        },
        'doc' =>
            '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.',
        'order' => 2,
229
    },
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
    #'bulkspool_table' => {
    #    'fields' => {
    #        'messagekey_bulkspool' => {
    #            'struct'   => 'varchar(33)',
    #            'doc'      => 'primary key',
    #            'primary'  => 1,
    #            'not_null' => 1,
    #            'order'    => 1,
    #        },
    #        'message_bulkspool' => {
    #            'struct' => 'longtext',
    #            'doc'    => 'message as string b64 encoded',
    #            'order'  => 2,
    #        },
    #        #'messageid_bulkspool' => {
    #        #    'struct' => 'varchar(300)',
    #        #    'doc'    => 'stored to list spool content faster',
    #        #    'order'  => 4,
    #        #},
    #        'lock_bulkspool' => {
    #            'struct' => 'int(1)',
    #            'doc' =>
    #                'when set to 1, this field prevents Sympa from processing the message',
    #            'order' => 5,
    #        },
    #        #'dkim_privatekey_bulkspool' => {
    #        #    'struct' => 'varchar(2000)',
    #        #    'doc' =>
    #        #        'DKIM parameter stored for bulk daemon because bulk ignore list parameters, private key to sign message',
    #        #    'order' => 6,
    #        #},
    #        #'dkim_selector_bulkspool' => {
    #        #    'struct' => 'varchar(50)',
    #        #    'doc' =>
    #        #        'DKIM parameter stored for bulk daemon because bulk ignore list parameters, DKIM selector to sign message',
    #        #    'order' => 7,
    #        #},
    #        #'dkim_d_bulkspool' => {
    #        #    'struct' => 'varchar(50)',
    #        #    'doc' =>
    #        #        'DKIM parameter stored for bulk daemon because bulk ignore list parameters, the d DKIM parameter',
    #        #    'order' => 8,
    #        #},
    #        #'dkim_i_bulkspool' => {
    #        #    'struct' => $email_struct,
    #        #    'doc' =>
    #        #        'DKIM parameter stored for bulk daemon because bulk ignore list parameters, DKIM i signature parameter',
    #        #    'order' => 9,
    #        #},
    #    },
    #    'doc'   => 'This table contains the messages to be sent by bulk.pl',
    #    'order' => 3,
    #},
    #'bulkmailer_table' => {
    #    'fields' => {
    #        'messagekey_bulkmailer' => {
    #            'struct' => 'varchar(80)',
    #            'doc' =>
    #                '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_bulkspool',
    #            'primary'  => 1,
    #            'not_null' => 1,
    #            'order'    => 1,
    #        },
    #        'packetid_bulkmailer' => {
    #            'struct'   => 'varchar(33)',
    #            'doc'      => 'An id for the packet',
    #            'primary'  => 1,
    #            'not_null' => 1,
    #            'order'    => 2,
    #        },
    #        #'messageid_bulkmailer' => {
    #        #    'struct' => 'varchar(200)',
    #        #    'doc'    => 'The message Id',
    #        #    'order'  => 3,
    #        #},
    #        ##FIXME: column name is "recEipients_bulkmailer"
    #        'receipients_bulkmailer' => {
    #            'struct' => 'text',
    #            'doc' =>
    #                'the comma separated list of recipient email for this message',
    #            'order' => 4,
    #        },
    #        #'returnpath_bulkmailer' => {
    #        #    'struct' => $email_struct,
    #        #    'doc' =>
    #        #        'the return path value that must be set when sending the message',
    #        #    'order' => 5,
    #        #},
    #        'robot_bulkmailer' => {
    #            'struct' => $robot_struct,
    #            'doc'    => '',
    #            'order'  => 6,
    #        },
    #        'listname_bulkmailer' => {
    #            'struct' => $list_struct,
    #            'doc'    => '',
    #            'order'  => 7,
    #        },
    #        #'verp_bulkmailer' => {
    #        #    'struct' => 'int(1)',
    #        #    'doc' =>
    #        #        'A boolean to specify if VERP is requiered, in this case return_path will be formatted using VERP form',
    #        #    'order' => 8,
    #        #},
    #        #'tracking_bulkmailer' => {
    #        #    'struct' => "enum('mdn','dsn')",
    #        #    'doc' => 'Is DSN or MDN requiered when sending this message?',
    #        #    'order' => 9,
    #        #},
    #        #'merge_bulkmailer' => {
    #        #    'struct' => 'int(1)',
    #        #    'doc' =>
    #        #        'Boolean, if true, the message is to be parsed as a TT2 template foreach recipient',
    #        #    'order' => 10,
    #        #},
    #        'priority_message_bulkmailer' => {
    #            'struct' => 'smallint(10)',
    #            'doc'    => 'FIXME',
    #            'order'  => 11,
    #        },
    #        'priority_packet_bulkmailer' => {
    #            'struct' => 'smallint(10)',
    #            'doc'    => 'FIXME',
    #            'order'  => 12,
    #        },
    #        'reception_date_bulkmailer' => {
    #            'struct' => 'double',
    #            'doc'    => 'The date where the message was received',
    #            'order'  => 13,
    #        },
    #        'delivery_date_bulkmailer' => {
    #            'struct' => 'int(11)',
    #            'doc'    => 'The date the message was sent',
    #            'order'  => 14,
    #        },
    #        'lock_bulkmailer' => {
    #            'struct' => 'varchar(30)',
    #            'doc' =>
    #                'A lock. It is set as process-number @ hostname so multiple bulkmailer can handle this spool',
    #            'order' => 15,
    #        },
    #        'tag_bulkmailer' => {
    #            'struct' => 'varchar(10)',
    #            'doc'    => 'Additional tag used to sort packets',
    #            'order'  => 16,
    #        },
    #    },
    #    'doc' =>
    #        'storage of recipients with a ref to a message in spool_table. So a very simple process can distribute them',
    #    'order' => 4,
    #},
381
    'exclusion_table' => {
382
383
        'fields' => {
            'list_exclusion' => {
384
385
386
387
                # "family:" and family name.
                'struct' => sprintf(
                    'varchar(%d)', Sympa::Constants::FAMILY_LEN() + 7
                ),
sikeda's avatar
sikeda committed
388
                'doc'      => 'FIXME',
389
390
391
392
393
                'order'    => 1,
                'primary'  => 1,
                'not_null' => 1,
            },
            'robot_exclusion' => {
394
                'struct'   => $robot_struct,
sikeda's avatar
sikeda committed
395
                'doc'      => 'FIXME',
396
397
398
399
400
                'order'    => 2,
                'primary'  => 1,
                'not_null' => 1,
            },
            'user_exclusion' => {
401
                'struct'   => $email_struct,
sikeda's avatar
sikeda committed
402
                'doc'      => 'FIXME',
403
404
405
406
407
                'order'    => 3,
                'primary'  => 1,
                'not_null' => 1,
            },
            'family_exclusion' => {
408
                'struct'  => $family_struct,
sikeda's avatar
sikeda committed
409
                'doc'     => 'FIXME',
410
411
412
413
414
                'primary' => 1,
                'order'   => 4,
            },
            'date_exclusion' => {
                'struct' => 'int(11)',
sikeda's avatar
sikeda committed
415
                'doc'    => 'FIXME',
416
417
418
419
                'order'  => 5,
            },
        },
        'doc' =>
420
            'Exclusion table is used in order to manage unsubscription for subsceriber included from an external data source.',
421
        'order' => 5,
422
    },
423
424
    'inclusion_table' => {
        'fields' => {
425
426
427
            'target_inclusion' => {
                'struct'   => $list_id_struct,
                'doc'      => 'list ID of including list',
428
429
430
431
                'order'    => 1,
                'primary'  => 1,
                'not_null' => 1,
            },
432
433
434
            'role_inclusion' => {
                'struct'   => "enum('member','owner','editor')",
                'doc'      => 'role of included user',
435
436
437
438
                'order'    => 2,
                'primary'  => 1,
                'not_null' => 1,
            },
439
440
441
442
            'source_inclusion' => {
                'struct'   => $list_id_struct,
                'doc'      => 'list ID of included list',
                'order'    => 3,
443
444
445
446
447
448
                'primary'  => 1,
                'not_null' => 1,
            },
            'update_epoch_inclusion' => {
                'struct' => 'int(11)',
                'doc'    => 'the date this entry was created or updated',
449
                'order'  => 4,
450
451
452
            },
        },
        'doc' =>
453
            'Inclusion table is used in order to manage lists included from / including subscribers of other lists.',
454
455
        'order' => 4,
    },
456
    'session_table' => {
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
        'fields' => {
            'id_session' => {
                'struct'   => 'varchar(30)',
                'doc'      => 'the identifier of the database record',
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 1,
            },
            'prev_id_session' => {
                'struct' => 'varchar(30)',
                'doc'    => 'previous identifier of the database record',
                'order'  => 2,
            },
            'start_date_session' => {
                'struct'   => 'int(11)',
                'doc'      => 'the date when the session was created',
                'not_null' => 1,
                'order'    => 3,
            },
            'date_session' => {
                'struct' => 'int(11)',
                'doc' =>
sikeda's avatar
sikeda committed
479
                    'Unix time of the last use of this session. It is used in order to expire old sessions',
480
481
482
483
484
485
                'not_null' => 1,
                'order'    => 4,
            },
            'refresh_date_session' => {
                'struct' => 'int(11)',
                'doc' =>
sikeda's avatar
sikeda committed
486
                    'Unix time of the last refresh of this session.  It is used in order to refresh available sessions',
487
488
489
490
491
                'order' => 5,
            },
            'remote_addr_session' => {
                'struct' => 'varchar(60)',
                'doc' =>
sikeda's avatar
sikeda committed
492
                    'the IP address of the computer from which the session was created',
493
494
495
                'order' => 6,
            },
            'robot_session' => {
496
                'struct' => $robot_struct,
sikeda's avatar
sikeda committed
497
                'doc' => 'the virtual host in which the session was created',
498
499
500
                'order' => 7,
            },
            'email_session' => {
501
                'struct' => $email_struct,
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
                'doc'    => 'the email associated to this session',
                'order'  => 8,
            },
            'hit_session' => {
                'struct' => 'int(11)',
                'doc' =>
                    'the number of hit performed during this session. Used to detect crawlers',
                'order' => 9,
            },
            'data_session' => {
                'struct' => 'text',
                'doc' =>
                    'parameters attached to this session that don\'t have a dedicated column in the database',
                'order' => 10,
            },
        },
518
        'doc'   => 'Management of HTTP session.',
519
        'order' => 6,
520
521
    },
    'one_time_ticket_table' => {
522
523
524
        'fields' => {
            'ticket_one_time_ticket' => {
                'struct'  => 'varchar(30)',
sikeda's avatar
sikeda committed
525
                'doc'     => 'FIXME',
526
                'primary' => 1,
sikeda's avatar
sikeda committed
527
                'order'   => 1,
528
529
            },
            'email_one_time_ticket' => {
530
                'struct' => $email_struct,
sikeda's avatar
sikeda committed
531
532
                'doc'    => 'FIXME',
                'order'  => 2,
533
534
            },
            'robot_one_time_ticket' => {
535
                'struct' => $robot_struct,
sikeda's avatar
sikeda committed
536
537
                'doc'    => 'FIXME',
                'order'  => 3,
538
539
540
            },
            'date_one_time_ticket' => {
                'struct' => 'int(11)',
sikeda's avatar
sikeda committed
541
542
                'doc'    => 'FIXME',
                'order'  => 4,
543
544
545
            },
            'data_one_time_ticket' => {
                'struct' => 'varchar(200)',
sikeda's avatar
sikeda committed
546
547
                'doc'    => 'FIXME',
                'order'  => 5,
548
549
550
            },
            'remote_addr_one_time_ticket' => {
                'struct' => 'varchar(60)',
sikeda's avatar
sikeda committed
551
552
                'doc'    => 'FIXME',
                'order'  => 6,
553
554
555
            },
            'status_one_time_ticket' => {
                'struct' => 'varchar(60)',
sikeda's avatar
sikeda committed
556
557
                'doc'    => 'FIXME',
                'order'  => 7,
558
559
560
            },
        },
        'doc' =>
561
            'One time ticket are random value used for authentication challenge. A ticket is associated with a context which look like a session.',
562
        'order' => 7,
563
564
    },
    'notification_table' => {
565
566
567
        'fields' => {
            'pk_notification' => {
                'struct'        => 'bigint(20)',
sikeda's avatar
sikeda committed
568
                'doc'           => 'autoincrement key',
569
570
571
572
573
574
575
576
                'autoincrement' => 1,
                'primary'       => 1,
                'not_null'      => 1,
                'order'         => 1,
            },
            'message_id_notification' => {
                'struct' => 'varchar(100)',
                'doc' =>
577
                    'initial message-id. This field is used to search DSN and MDN related to a particular message',
578
579
580
                'order' => 2,
            },
            'recipient_notification' => {
581
                'struct' => $email_struct,
582
                'doc' =>
583
                    'email address of recipient for which a DSN or MDN was received',
584
585
586
587
588
                'order' => 3,
            },
            'reception_option_notification' => {
                'struct' => 'varchar(20)',
                'doc' =>
589
                    'the subscription option of the subscriber when the related message was sent to the list. Useful because some recipient may have option such as //digest// or //nomail//',
590
591
592
593
                'order' => 4,
            },
            'status_notification' => {
                'struct' => 'varchar(100)',
sikeda's avatar
sikeda committed
594
                'doc'    => 'value of notification',
595
596
597
598
                'order'  => 5,
            },
            'arrival_date_notification' => {
                'struct' => 'varchar(80)',
599
                'doc'    => 'reception date of latest DSN or MDN',
600
601
                'order'  => 6,
            },
602
            'arrival_epoch_notification' => {
603
604
605
606
                'struct' => 'int(11)',
                'doc'    => 'reception date of latest DSN or MDN',
                'order'  => 7,
            },
607
608
            'type_notification' => {
                'struct' => "enum('DSN', 'MDN')",
sikeda's avatar
sikeda committed
609
                'doc'    => 'type of the notification (DSN or MDN)',
610
                'order'  => 8,
611
612
            },
            'list_notification' => {
613
                'struct' => $list_struct,
614
                'doc'    => 'the listname the message was issued for',
615
                'order'  => 9,
616
617
            },
            'robot_notification' => {
618
                'struct' => $robot_struct,
sikeda's avatar
sikeda committed
619
                'doc'    => 'the robot the message is related to',
620
                'order'  => 10,
621
622
623
624
            },
            'date_notification' => {
                'struct'   => 'int(11)',
                'doc'      => 'FIXME',
sikeda's avatar
sikeda committed
625
626
                'not_null' => 1,
                'order'    => 11,
627
628
629
            },
        },
        'doc' =>
630
            'Used for message tracking feature. If the list is configured for tracking, outgoing messages include a delivery status notification request and optionally a message disposition notification request. When DSN and MDN are received by Sympa, they are stored in this table in relation with the related list and message ID.',
631
        'order' => 8,
632
633
    },
    'logs_table' => {
634
        'fields' => {
635
636
637
638
639
640
641
            #'id_logs' => {
            #    'struct'   => 'bigint(20)',
            #    'doc'      => 'unique log\'s identifier',
            #    'primary'  => 1,
            #    'not_null' => 1,
            #    'order'    => 1,
            #},
642
            'user_email_logs' => {
643
                'struct' => $email_struct,
644
645
646
647
648
649
650
651
652
653
                'doc' =>
                    'e-mail address of the message sender or email of identified web interface user (or soap user)',
                'order' => 2,
            },
            'date_logs' => {
                'struct'   => 'int(11)',
                'doc'      => 'date when the action was executed',
                'not_null' => 1,
                'order'    => 3,
            },
654
            'usec_logs' => {
sikeda's avatar
sikeda committed
655
656
657
658
                'struct' => 'int(6)',
                'doc' =>
                    'subsecond in microsecond when the action was executed',
                'order' => 3.5,
659
            },
660
            'robot_logs' => {
661
                'struct' => $robot_struct,
662
663
664
665
666
                'doc' =>
                    'name of the robot in which context the action was executed',
                'order' => 4,
            },
            'list_logs' => {
667
                'struct' => $list_struct,
668
669
670
671
672
673
674
675
676
677
678
679
680
681
                'doc' =>
                    'name of the mailing-list in which context the action was executed',
                'order' => 5,
            },
            'action_logs' => {
                'struct' => 'varchar(50)',
                'doc' =>
                    'name of the Sympa subroutine which initiated the log',
                'not_null' => 1,
                'order'    => 6,
            },
            'parameters_logs' => {
                'struct' => 'varchar(100)',
                'doc' =>
sikeda's avatar
sikeda committed
682
                    'comma-separated list of parameters. The amount and type of parameters can differ from an action to another',
683
684
685
                'order' => 7,
            },
            'target_email_logs' => {
686
                'struct' => $email_struct,
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
                'doc'    => 'e-mail address (if any) targeted by the message',
                'order'  => 8,
            },
            'msg_id_logs' => {
                'struct' => 'varchar(255)',
                'doc' =>
                    'identifier of the message which triggered the action',
                'order' => 9,
            },
            'status_logs' => {
                'struct' => 'varchar(10)',
                'doc' =>
                    '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',
                'not_null' => 1,
                'order'    => 10,
            },
            'error_type_logs' => {
                'struct' => 'varchar(150)',
                'doc' =>
706
                    'name of the error string - if any - issued by the subroutine',
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
                'order' => 11,
            },
            'client_logs' => {
                'struct' => 'varchar(100)',
                'doc' =>
                    'IP address of the client machine from which the message was sent',
                'order' => 12,
            },
            'daemon_logs' => {
                'struct'   => 'varchar(10)',
                'doc'      => 'name of the Sympa daemon which ran the action',
                'not_null' => 1,
                'order'    => 13,
            },
        },
        'doc' =>
            'Each important event is stored in this table. List owners and listmaster can search entries in this table using web interface.',
        'order' => 9,
725
726
    },
    'stat_table' => {
727
        'fields' => {
728
729
            #'id_stat' => {
            #    'struct'   => 'bigint(20)',
sikeda's avatar
sikeda committed
730
            #    'doc'      => 'FIXME',
731
732
733
734
            #    'order'    => 1,
            #    'primary'  => 1,
            #    'not_null' => 1,
            #},
735
736
            'date_stat' => {
                'struct'   => 'int(11)',
sikeda's avatar
sikeda committed
737
                'doc'      => 'FIXME',
738
739
740
741
                'order'    => 2,
                'not_null' => 1,
            },
            'email_stat' => {
742
                'struct' => $email_struct,
sikeda's avatar
sikeda committed
743
                'doc'    => 'FIXME',
744
745
746
747
                'order'  => 3,
            },
            'operation_stat' => {
                'struct'   => 'varchar(50)',
sikeda's avatar
sikeda committed
748
                'doc'      => 'FIXME',
749
750
751
752
                'order'    => 4,
                'not_null' => 1,
            },
            'list_stat' => {
753
                'struct' => $list_struct,
sikeda's avatar
sikeda committed
754
                'doc'    => 'FIXME',
755
756
757
                'order'  => 5,
            },
            'daemon_stat' => {
758
                'struct' => 'varchar(20)',
sikeda's avatar
sikeda committed
759
                'doc'    => 'FIXME',
760
761
762
763
                'order'  => 6,
            },
            'user_ip_stat' => {
                'struct' => 'varchar(100)',
sikeda's avatar
sikeda committed
764
                'doc'    => 'FIXME',
765
766
767
                'order'  => 7,
            },
            'robot_stat' => {
768
                'struct'   => $robot_struct,
sikeda's avatar
sikeda committed
769
                'doc'      => 'FIXME',
770
771
772
773
774
                'order'    => 8,
                'not_null' => 1,
            },
            'parameter_stat' => {
                'struct' => 'varchar(50)',
sikeda's avatar
sikeda committed
775
                'doc'    => 'FIXME',
776
777
778
779
                'order'  => 9,
            },
            'read_stat' => {
                'struct'   => 'tinyint(1)',
sikeda's avatar
sikeda committed
780
                'doc'      => 'FIXME',
781
782
783
784
785
                'order'    => 10,
                'not_null' => 1,
            },
        },
        'doc' =>
sikeda's avatar
sikeda committed
786
            'Statistics item are stored in this table, Sum average and so on are stored in stat_counter_table.',
787
        'order' => 10,
788
789
    },
    'stat_counter_table' => {
790
        'fields' => {
791
792
            #'id_counter' => {
            #    'struct'   => 'bigint(20)',
sikeda's avatar
sikeda committed
793
            #    'doc'      => 'FIXME',
794
795
796
797
            #    'order'    => 1,
            #    'primary'  => 1,
            #    'not_null' => 1,
            #},
798
799
            'beginning_date_counter' => {
                'struct'   => 'int(11)',
sikeda's avatar
sikeda committed
800
                'doc'      => 'FIXME',
801
802
803
804
805
                'order'    => 2,
                'not_null' => 1,
            },
            'end_date_counter' => {
                'struct' => 'int(11)',
sikeda's avatar
sikeda committed
806
                'doc'    => 'FIXME',
807
808
809
810
                'order'  => 1,
            },
            'data_counter' => {
                'struct'   => 'varchar(50)',
sikeda's avatar
sikeda committed
811
                'doc'      => 'FIXME',
812
813
814
815
                'not_null' => 1,
                'order'    => 3,
            },
            'robot_counter' => {
816
                'struct'   => $robot_struct,
sikeda's avatar
sikeda committed
817
                'doc'      => 'FIXME',
818
819
820
821
                'not_null' => 1,
                'order'    => 4,
            },
            'list_counter' => {
822
                'struct' => $list_struct,
sikeda's avatar
sikeda committed
823
                'doc'    => 'FIXME',
824
                'order'  => 5,
825
826
827
            },
            #'variation_counter' => {
            #    'struct' => 'int',
sikeda's avatar
sikeda committed
828
            #    'doc'    => 'FIXME',
829
830
831
832
            #    'order'  => 6,
            #},
            #'total_counter' => {
            #    'struct' => 'int',
sikeda's avatar
sikeda committed
833
            #    'doc'    => 'FIXME',
834
835
836
            #    'order'  => 7,
            #},
            'count_counter' => {
837
                'struct' => 'int',
sikeda's avatar
sikeda committed
838
                'doc'    => 'FIXME',
839
                'order'  => 8,
840
841
            },
        },
sikeda's avatar
sikeda committed
842
        'doc' => 'Used in conjunction with stat_table for users statistics.',
843
        'order' => 11,
844
    },
845

846
    'admin_table' => {
847
        'fields' => {
848

849
            'user_admin' => {
850
                'struct'   => $email_struct,
851
852
                'primary'  => 1,
                'not_null' => 1,
sikeda's avatar
sikeda committed
853
                'doc'      => 'list admin email',
854
855
856
                'order'    => 1,
            },
            'list_admin' => {
857
                'struct'   => $list_struct,
858
859
                'primary'  => 1,
                'not_null' => 1,
sikeda's avatar
sikeda committed
860
                'doc'      => 'list name',
861
862
863
                'order'    => 2,
            },
            'robot_admin' => {
864
                'struct'   => $robot_struct,
865
866
                'primary'  => 1,
                'not_null' => 1,
sikeda's avatar
sikeda committed
867
                'doc'      => 'list domain',
868
869
870
871
872
873
                'order'    => 3,
            },
            'role_admin' => {
                'struct'  => "enum('listmaster','owner','editor')",
                'primary' => 1,
                'doc' =>
sikeda's avatar
sikeda committed
874
                    'a role of this user for this list (editor, owner or listmaster which a kind of list owner too)',
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
                'order' => 4,
            },
            'profile_admin' => {
                'struct' => "enum('privileged','normal')",
                'doc' =>
                    'privilege level for this owner, value //normal// or //privileged//. The related privilege are listed in editlist.conf. ',
                'order' => 5,
            },
            'date_admin' => {
                'struct'   => 'datetime',
                'doc'      => 'date this user become a list admin',
                'not_null' => 1,
                'order'    => 6,
            },
            'update_admin' => {
                'struct' => 'datetime',
                'doc'    => 'last update timestamp',
                'order'  => 7,
            },
            'reception_admin' => {
                'struct' => 'varchar(20)',
896
897
                'doc' =>
                    'email reception option for list management messages',
898
899
900
901
902
903
904
905
906
907
                'order' => 8,
            },
            'visibility_admin' => {
                'struct' => 'varchar(20)',
                'doc' =>
                    'admin user email can be hidden in the list web page description',
                'order' => 9,
            },
            'comment_admin' => {
                'struct' => 'varchar(150)',
sikeda's avatar
sikeda committed
908
                'doc'    => 'FIXME',
909
910
911
912
913
                'order'  => 10,
            },
            'subscribed_admin' => {
                'struct' => 'int(1)',
                'doc' =>
sikeda's avatar
sikeda committed
914
                    'set to 1 if user is list admin by definition in list config file',
915
916
917
918
919
                'order' => 11,
            },
            'included_admin' => {
                'struct' => 'int(1)',
                'doc' =>
sikeda's avatar
sikeda committed
920
                    'set to 1 if user is admin by an external data source',
921
922
923
924
925
926
927
928
929
930
931
932
933
                'order' => 12,
            },
            'include_sources_admin' => {
                'struct' => 'varchar(50)',
                'doc'    => 'name of external datasource',
                'order'  => 13,
            },
            'info_admin' => {
                'struct' => 'varchar(150)',
                'doc' =>
                    'private information usually dedicated to listmasters who needs some additional information about list owners',
                'order' => 14,
            },
934

935
936
        },
        'doc' =>
937
            'This table is an internal cash where list admin roles are stored. It is just a cash and it does not need to be saved. You may remove its content if needed. It will just make next Sympa startup slower.',
938
        'order' => 12,
939
940
    },
    'netidmap_table' => {
941
942
943
        'fields' => {
            'netid_netidmap' => {
                'struct'   => 'varchar(100)',
sikeda's avatar
sikeda committed
944
                'doc'      => 'FIXME',
945
946
947
948
949
950
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 1,
            },
            'serviceid_netidmap' => {
                'struct'   => 'varchar(100)',
sikeda's avatar
sikeda committed
951
                'doc'      => 'FIXME',
952
953
954
955
956
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 2,
            },
            'email_netidmap' => {
957
                'struct' => $email_struct,
sikeda's avatar
sikeda committed
958
                'doc'    => 'FIXME',
959
960
961
                'order'  => 4,
            },
            'robot_netidmap' => {
962
                'struct'   => $robot_struct,
sikeda's avatar
sikeda committed
963
                'doc'      => 'FIXME',
964
965
966
967
968
969
970
                'primary'  => 1,
                'not_null' => 1,
                'order'    => 3,
            },
        },
        'order' => 13,
        'doc'   => 'FIXME',
971
972
    },
    'conf_table' => {
973
974
        'fields' => {
            'robot_conf' => {
975
                'struct'  => $robot_struct,
sikeda's avatar
sikeda committed
976
                'doc'     => 'FIXME',
977
978
979
980
981
                'primary' => 1,
                'order'   => 1,
            },
            'label_conf' => {
                'struct'  => 'varchar(80)',
sikeda's avatar
sikeda committed
982
                'doc'     => 'FIXME',
983
984
985
986
987
988
989
990
991
992
993
994
                'primary' => 1,
                'order'   => 2,
            },
            'value_conf' => {
                'struct' => 'varchar(300)',
                'doc' =>
                    'the value of parameter //label_conf// of robot //robot_conf//.',
                'order' => 3,
            },
        },
        'doc'   => 'FIXME',
        'order' => 14,
995
996
    },
    'list_table' => {
997
        'fields' => {
998
            ## Identification
999
            'name_list' => => {
1000
                'struct'   => $list_struct,