added database-description( imported from WIKI; added some global
[cacert-codedocs.git] / source / database.rst
1 ==================
2 Database structure
3 ==================
4
5 This part of the documentation describes the database schema of the CAcert
6 web application.
7
8
9 Miscellaneous Tables
10 ====================
11
12
13 .. _schema_version:
14
15 --------------------
16 table schema_version
17 --------------------
18
19 This table holds the current webdb revision. This table was introduced in Nov 2011 by Software-Assessment project https://bugs.cacert.org/view.php?id=976. Current revision: '1' , November 23, 2011
20
21 .. csv-table::
22 :header-rows: 1
23 :align: left
24 :widths: 1,1,2
25
26 "Field", "Type", "Comment"
27 "id", "int(11)", "PRIMARY KEY auto_increment"
28 version, int(11), "NOT NULL UNIQUE"
29 when, datetime, "NOT NULL"
30
31
32 .. _AdminLog:
33
34 --------------
35 table AdminLog
36 --------------
37
38 Records changes of the DOB or the name executed by Support Engineers.
39 A record is also written when an account is "deleted" as a result of an Arbitration.
40
41 .. csv-table::
42 :header-rows: 1
43 :align: left
44
45 Field, Type, NULL, Key, Default, Extras
46 when, datetime , NO , , ,
47 old-lname, varchar(255), NO, , ,
48 old-dob, date, NO, , , ,
49 new-lname, varchar(255), NO, , ,
50 new-dob, date, NO, , , ,
51 uid, int(11), NO, , , ,
52 adminid, int(11), NO, , ,
53 type, varchar(50), NO, ,NULL, See https://bugs.cacert.org/view.php?id=1135. New in Schema Version 4 (or 3?)
54 information, varchar(50), NO, , NULL, See https://bugs.cacert.org/view.php?id=1135. New in Schema Version 4 (or 3?)
55 actiontypeid, int(11), NO ?, , 1 ?, "Found this in notary.inc.php of commit 00f5b2872a, write_se_log. Nore documentation needed! Added this manually to testserver database."
56
57
58 .. _Advertising:
59
60 -----------------
61 table Advertising
62 -----------------
63
64 Holds information about the advertising data, used for the Advertising on the WebDB
65
66 .. csv-table::
67 :header-rows: 1
68 :align: left
69
70 "Field","Type","NULL","Key","Default","Extras"
71 "id","int(10) unsigned","NO","auto_increment, primary","",""
72 "replaceid","int(10) unsigned","NO","","",""
73 "replaced","tinyint(3) unsigned","NO","","",""
74 "orderid","tinyint(3) unsigned","NO","","",""
75 "link","varchar(255)","NO","","",""
76 "title","varchar(255)","NO","","",""
77 "months","tinyint(3) unsigned","NO","","",""
78 "who","int(10) unsigned","NO","","",""
79 "when","datetime","NO","","",""
80 "active","tinyint(3) unsigned","NO","","",""
81 "approvedby","int(10) unsigned","NO","","",""
82 "expires","datetime","NO","","",""
83
84
85 .. _Alerts:
86
87 ------------
88 table Alerts
89 ------------
90
91 Stores information about the alert settings of a user
92
93 .. csv-table::
94 :header-rows: 1
95 :align: left
96
97 "Field","Type","NULL","Key","Default","Extras"
98 "memid","int(11)","NO","primary key","0",""
99 "general","tinyint(1)","NO","","0",""
100 "country","tinyint(1)","NO","","0",""
101 "regional","tinyint(1)","NO","","0",""
102 "radius","tinyint(1)","NO","","0",""
103
104
105 .. _BadDomains:
106
107 ----------------
108 table BadDomains
109 ----------------
110
111 Should store information about domains that are not allowed to be used to add domains to a personal account
112
113 .. csv-table::
114 :header-rows: 1
115 :align: left
116
117 "Field","Type","NULL","Key","Default","Extras"
118 "domain","varchar(255)","NO","","",""
119
120
121 .. _DisputeDomain:
122
123 -------------------
124 table DisputeDomain
125 -------------------
126
127 An entry in this table is created if a user selects the menu item "Disputes/Abuses -> Domain Dispute".
128
129 .. csv-table::
130 :header-rows: 1
131 :align: left
132
133 "Field","Type","NULL","Key","Default","Extras"
134 "memid","int(11)","NO","","0",""
135 "oldmemid","int(11)","NO","","0",""
136 "domain","varchar(255)","NO","","",""
137 "created","datetime","NO","","'0000-00-00 00:00:00'",""
138 "hash","varchar(50)","NO","","‘’",""
139 "attempts","int(1)","NO","","0",""
140 "action","enum('accept','reject','failed')","NO","","accept",""
141
142
143 .. _DisputeEmail:
144
145 ------------------
146 table DisputeEmail
147 ------------------
148
149 An entry in this table is created if a user selects the menu item "Disputes/Abuses -> Email Dispute".
150
151 .. csv-table::
152 :header-rows: 1
153 :align: left
154
155 "Field","Type","Comment"
156 "ID","int(11)","Primary Key"
157 "memid","int(11)","ID of the user account that initiated the 'dispute'"
158 "oldmemid","int(11)","ID of the user account owning the disputed mail address"
159 "email","varchar(255)","Mail address which is disputed"
160 "created","datetime",""
161 "hash","varchar(50)","Hash used to identify this dispute in deep links. This will be cleared if the dispute is answered by the owner of the disputed address"
162 "attempts","int(1)",""
163 "action","enum('accept','reject','failed')","Reply of the owner to the dispute, initialized to 'accept'"
164 "IP","varchar(20)","Remote IP address of machine from where the dispute was initiated"
165
166
167 .. _GPG:
168
169 ---------
170 table GPG
171 ---------
172
173 .. csv-table::
174 :header-rows: 1
175 :align: left
176
177 "Field","Type","NULL","Key","Default","Extra"
178 "id","int(11)","NO","","auto_increment",""
179 "memid","int(11)","NO","","'0'",""
180 "email","varchar(255)","NO","","''",""
181 "level","int(1)","NO","","'0'",""
182 "multiple","tinyint(1)","NO","","'0'",""
183 "expires","tinyint(1)","NO","","'0'",""
184 "csr","varchar(255)","NO","","''",""
185 "crt","varchar(255)","NO","","''",""
186 "issued","datetime","NO","","'0000-00-00 00:00:00'",""
187 "expire","datetime","NO","","'0000-00-00 00:00:00'",""
188 "keyid","char(18)","","","NULL",""
189 "warning","tinyint(1)","NO","","'0'",""
190 "description","varchar(100)","NO","","''","[[https://bugs.cacert.org/view.php?id=782|bug #782]]"
191
192
193 .. _LocAlias:
194
195 --------------
196 table LocAlias
197 --------------
198
199
200 .. _News:
201
202 ----------
203 table News
204 ----------
205
206
207 .. _OrgAdminLog:
208
209 -----------------
210 table OrgAdminLog
211 -----------------
212
213 Structure identical to [[#AdminLog|AdminLog]], see [[https://bugs.cacert.org/view.php?id=1135|Bug#1135]].
214
215
216 .. _OTPHashes:
217
218 ---------------
219 table OTPHashes
220 ---------------
221
222
223 .. _PingLog:
224
225 -------------
226 table PingLog
227 -------------
228
229
230 .. _Root_Certs:
231
232 ----------------
233 table Root_Certs
234 ----------------
235
236 Just a simple list connecting the root cert's CN to an ID for efficient storage and reference. This table is needed in translations from certs serial numbers to user accounts. Also a request to this table is required in certs login procedure.
237
238 .. csv-table::
239 :header-rows: 1
240 :align: left
241
242 "Field","Type","Comment"
243 "ID","int(2)","Primary Key"
244 "Cert_Text","varchar(255)","CN as stored in the certificate"
245
246
247 .. _StampCache:
248
249 ----------------
250 table StampCache
251 ----------------
252
253
254 .. _Tickets:
255
256 -------------
257 table Tickets
258 -------------
259
260
261 Language related Tables
262 =======================
263
264
265 .. _AddLang:
266
267 -------------
268 table AddLang
269 -------------
270
271 secondary languages list (account.php?id=41), a few rows
272
273 .. csv-table::
274 :header-rows: 1
275 :align: left
276
277 "Field","Type","Comment"
278 "ID","int(?)","Primary Key"
279 "userid","int(?)","a Users Id, who selected a secondary language"
280 "lang","varchar(?)","language code, relates to table Languages locale, i.e. en_US, de_AT"
281
282
283 .. _Languages:
284
285 ---------------
286 table Languages
287 ---------------
288
289 primary languages list (account.php?id=41), approx 99 records, fixed content
290
291 .. csv-table::
292 :header-rows: 1
293 :align: left
294
295 "Field","Type","Comment"
296 "locale","varchar(?)","i.e. en_US, de_AT, de_CH"
297 "en_co","varchar(?)","i.e. Austria, Germany, Switzerland"
298 "en_lang","varchar(?)","i.e. German, Danish, German"
299 "country","varchar(?)","i.e. Österreich, Danmark, Schweiz"
300 "lang","varchar(?)","i.e. Deutsch, dansk, Deutsch"
301
302
303 Geographical Tables
304 ===================
305
306
307 .. _Countries:
308
309 ---------------
310 table Countries
311 ---------------
312
313 List of Countries, fixed content
314
315 .. csv-table::
316 :header-rows: 1
317 :align: left
318
319 "Field","Type","Comment"
320 "id","int(3)","Primary Key"
321 "name","varchar(50)","country name"
322 "acount","integer","how many assurers in this country?"
323
324
325 .. _Locations:
326
327 ---------------
328 table Locations
329 ---------------
330
331 List of Cities
332
333 .. csv-table::
334 :header-rows: 1
335 :align: left
336
337 "Field","Type","Comment"
338 "id","int(7)","Primary Key"
339 "regid","int(4)","city relates to this region"
340 "ccid","int(3)","city relates to this country"
341 "name","varchar(50)","city name"
342 "lat","double(6,3)","latitude of the city"
343 "long","double(6,3)","longitude of the city"
344 "acount","integer","how many assurers in this city?"
345
346
347 .. _Regions:
348
349 -------------
350 table Regions
351 -------------
352
353 List of Regions, fixed content
354
355 .. csv-table::
356 :header-rows: 1
357 :align: left
358
359 "Field","Type","Comment"
360 "id","int(5)","Primary Key"
361 "ccid","int(3)","region relates to this country"
362 "name","varchar(50)","region name"
363 "acount","integer","how many assurers in this region?"
364
365
366 User Data
367 =========
368
369
370 .. _DomainCerts:
371
372 -----------------
373 table DomainCerts
374 -----------------
375
376 contains server certificates
377
378 .. csv-table::
379 :header-rows: 1
380 :align: left
381
382 "Field","Type","Null","Key","Default","Extra"
383 "id","int(11)","NO","PRI","NULL","auto_increment"
384 "domid","int(11)","NO","","0","reference to table domain.id"
385 "serial","varchar(50)","NO","","",""
386 "CN","varchar(255)","NO","","",""
387 "subject","text","NO","","",""
388 "csr_name","varchar(255)","NO","","",""
389 "crt_name","varchar(255)","NO","","","contains the filename of the certificate (see pages/account/15.php)"
390 "created","datetime","NO","","0000-00-00 00:00:00",""
391 "modified","datetime","NO","","0000-00-00 00:00:00",""
392 "revoked","datetime","NO","","0000-00-00 00:00:00","Is set to '1970-01-01 10:00:01' if the certificate shall be revoked. Acts as trigger for server process to do the revocation and to insert the current timestamp here."
393 "expire","datetime","NO","","0000-00-00 00:00:00",""
394 "warning","tinyint(1)","NO","","0",""
395 "renewed","tinyint(1)","NO","","0",""
396 "rootcert","int(2)","NO","","1",""
397 "md","enum('md5','sha1','sha256','sha512')","NO","","sha512","[[https://bugs.cacert.org/view.php?id=1237|bug#1237]]"
398 "type","tinyint(4)","YES","","NULL",""
399 "pkhash","varchar(40)","YES","MUL","NULL",""
400 "certhash","varchar(40)","YES","","NULL",""
401 "coll_found","tinyint(1)","NO","","0",""
402 "description","varchar(100)","NO","","''","[[https://bugs.cacert.org/view.php?id=782|bug #782]]"
403
404
405 .. _Domains:
406
407 -------------
408 table Domains
409 -------------
410
411 .. csv-table::
412 :header-rows: 1
413 :align: left
414
415 "Field","Type","Null","Key","Default","Extra"
416 "id","int(11)","NO","PRI","NULL","auto_increment"
417 "memid","int(11)","NO","MUL","0","reference to owner of the domain (users.id)"
418 "domain","varchar(255)","NO","","",""
419 "created","datetime","NO","","0000-00-00 00:00:00",""
420 "modified","datetime","NO","","0000-00-00 00:00:00",""
421 "deleted","datetime","NO","","0000-00-00 00:00:00",""
422 "hash","varchar(50)","NO","","",""
423 "attempts","int(1)","NO","","0",""
424
425
426 .. _DomLink:
427
428 -------------
429 table DomLink
430 -------------
431
432 similiar to !EmailLink
433
434 This one seems to link !DomainCerts to Domains.
435
436 There is an N:M relation between Domains and !DomainCerts, that is a certificate can contain multiple Domains (???) and there also can be multiple !DomainCerts for each Domain.
437
438 .. csv-table::
439 :header-rows: 1
440 :align: left
441
442 "Field","Type","Null","Key","Default","Extra"
443 "certid","int(11)","NO","","0",""
444 "domid","int(11)","NO","","0",""
445
446
447 .. _EmailCerts:
448
449 ----------------
450 table EmailCerts
451 ----------------
452
453 contains client certificates? Details to be verified!
454
455 .. csv-table::
456 :header-rows: 1
457 :align: left
458
459 "Field","Type","Null","Key","Default","Extra"
460 "id","int(11)","NO","PRI","NULL","auto_increment"
461 "memid","int(11)","NO","","0",""
462 "serial","varchar(50)","NO","","",""
463 "CN","varchar(255)","NO","","",""
464 "subject","text","NO","","",""
465 "keytype","char(2)","NO","","NS",""
466 "codesign","tinyint(1)","NO","","0",""
467 "csr_name","varchar(255)","NO","","",""
468 "crt_name","varchar(255)","NO","","",""
469 "created","datetime","NO","","0000-00-00 00:00:00",""
470 "modified","datetime","NO","","0000-00-00 00:00:00",""
471 "revoked","datetime","NO","","0000-00-00 00:00:00","Is set to '1970-01-01 10:00:01' if the certificate shall be revoked. Acts as trigger for server process to do the revocation and to insert the current timestamp here."
472 "expire","datetime","NO","","0000-00-00 00:00:00",""
473 "warning","tinyint(1)","NO","","0",""
474 "renewed","tinyint(1)","NO","","0",""
475 "rootcert","int(2)","NO","","1",""
476 "md","enum('md5','sha1','sha256','sha512')","NO","","sha512","[[https://bugs.cacert.org/view.php?id=1237|bug#1237]]"
477 "type","tinyint(4)","YES","","NULL",""
478 "disablelogin","int(1)","NO","","0","If set to 0 login using this certificate is allowed, checked in get_user_id_from_cert(). Set to 1 if login is not allowed."
479 "pkhash","varchar(40)","YES","MUL","NULL",""
480 "certhash","varchar(40)","YES","","NULL",""
481 "coll_found","tinyint(1)","NO","","0",""
482 "description","varchar(100)","NO","","''","[[https://bugs.cacert.org/view.php?id=782|bug #782]]"
483
484
485 .. _EmailLink:
486
487 ---------------
488 table EmailLink
489 ---------------
490
491 This one seems to link !EmailCerts to Emails.
492
493 There is an N:M relation between Emails and !EmailCerts, that is a certificate can contain multiple emails and there also can be multiple !EmailCerts for each Email.
494
495 .. csv-table::
496 :header-rows: 1
497 :align: left
498
499 "Field","Type","Null","Key","Default","Extra"
500 "emailcertsid","int(11)","NO","MUL","0",""
501 "emailid","int(11)","NO","","0",""
502
503
504 .. _Email:
505
506 -----------
507 table Email
508 -----------
509
510 Contains a list of all mail adresses (including the primary one named in the Users table) associated to user accounts.
511
512 .. csv-table::
513 :header-rows: 1
514 :align: left
515
516 "Field","Type","Comment"
517 "ID","int(11)","Primary Key, autoincrement"
518 "memid","int(11)","Foreign key to table Users, associated account"
519 "email","varchar(255)",""
520 "created","datetime",""
521 "modified","datetime",""
522 "deleted","datetime","timestamp of deletion, is set if the user deletes the mail address from his/her account."
523 "hash","varchar(50)","If a new mail address is added the verification hash is stored here until the mail address has been verified. So ''email.hash = ' ' '' is a restriction that finds only verified mails."
524 "attempts","int(1)","for verification process?"
525
526
527 .. _notary:
528
529 ------------
530 table notary
531 ------------
532
533 This table contains all data for events which award Trust Points: Assurances, TTP, Thawte Point transfers etc.
534
535 .. csv-table::
536 :header-rows: 1
537 :align: left
538 :widths: 1,1,2
539
540 Field,Type,Comment
541 ID,int(11),"Primary Key, autoincrement"
542 From,int(11),"Foreign key to users, user awarding the Trust Points"
543 To,int(11),"Foreign key to users, user receiving the Trust Points"
544 Awarded,int(3),Number of points the Assurer awarded
545 Points,int(3),"Number of points credited to the receiver, may be less than awarded if receiver already has 100 points. With bug-1042 (new point calculation) installed, points will normally be set to 0 and ignored in calculation of assurance/experience points"
546 Method,enum,"Kind of event, definitions see below"
547 Location,varchar(255),Free text
548 Date,varchar(255),Date as entered in the Assure Someone application (free text)
549 When,datetime,"Timestamp of form completion, recorded automatically"
550 Expire,datetime,? Expiry timestamp of temporary awarded points?
551 Sponsor,int(11),is Sponsor if value != 0 and Points=200
552 deleted ,datetime ,"NOT NULL, DEFAULT '0000-00-00 00:00:00'"
553
554 methods
555 -------
556
557 * '''[Face to Face Meeting]''' (Default) (Common Assurance)
558 * '''[Trusted Third Parties]''' (Trusted 3rd Parties /pages/wot/6.php definition (https://bugs.cacert.org/view.php?id=855)), see comment below and https://bugs.cacert.org/view.php?id=1207!
559 * '''[Thawte Points Transfer]'''
560 * '''[Administrative Increase]''' 2 points for assuring someone else ("old" points counting) -or- "old" Super-Assurer increase
561 * '''[CT Magazine - Germany]''' (deprecated)
562 * '''[Temporary Increase]''' (raise +x experience points to give 35 pts)
563 * '''[Unknown]''' ("old" undef state)
564 * '''[TOPUP]''' (new TTP TOPUP program, https://bugs.cacert.org/view.php?id=863, https://bugs.cacert.org/view.php?id=864, https://bugs.cacert.org/view.php?id=888)
565 * '''[TTP-Assisted]''', TTP assisted assurance according to new policy, the different name to [Trusted Third Party] from above is chosen to separate the two TTP programm from each other.
566
567
568 .. _Cats_Passed:
569
570 -----------------
571 table Cats_Passed
572 -----------------
573
574 Lists all the tests passed by a user.
575
576 .. csv-table::
577 :header-rows: 1
578 :align: left
579
580 "Field","Type","Comment"
581 "id","int(11)","Primary Key, autoincrement"
582 "user_id","int(11)","Foreign key to table users. User that has passed this test"
583 "variant_id","int(11)","Foreign key to table Cats_variant. Exact kind of test passed."
584 "pass_date","timestamp","Timestamp of passing the test"
585
586
587 .. _Cats_Type:
588
589 ---------------
590 table Cats_Type
591 ---------------
592
593 Contains all the different kind of tests, currently the Assurer Challenge.
594 Another planned type is the Test for Organisation Assurers.
595 This defines what a test is good for.
596
597 .. csv-table::
598 :header-rows: 1
599 :align: left
600
601 "Field","Type","Comment"
602 "id","int(11)","Primary Key, autoincrement"
603 "type_text","varchar(255)","Short description of the test type"
604
605
606 .. _Cats_Variant:
607
608 ------------------
609 table Cats_Variant
610 ------------------
611
612 Describes the variants of each cats_type, like translation in different languages.
613 This defines exactly which test has been passed.
614
615 .. csv-table::
616 :header-rows: 1
617 :align: left
618
619 "Field","Type","Comment"
620 "id","int(11)","Primary Key, autoincrement"
621 "type_id","int(11)","Foreign key to Cats_type"
622 "test_text","varchar(255)","Short description of the test variant"
623
624
625 .. _TVerify:
626
627 -------------
628 table TVerify
629 -------------
630
631 Tables TVerify and TVerify-Vote are related to the TVerify program, which now is history since the Thawte Freemal program has been canceled quite some time ago.
632
633 TVerify contains one record per user request to be TVerified.
634
635 .. csv-table::
636 :header-rows: 1
637 :align: left
638
639 "Field","Type","Comment"
640 "id","int(11)","Primary Key, autoincrement"
641 "memid","int(11)","Foreign key to table Users, identifying the user which has submitted the contained data for review by TVerify Admins"
642 "photoid","varchar(255)","Path to uploaded image of a photo id by the user"
643 "URL","text","URL of the user in Thawte Freemail's Notary directory"
644 "CN","text","(probably)The Common Name contained in the requesting user's Thawte Freemail certificate"
645 "created","datetime","Timestamp when the request was initiated"
646 "modified","datetime","Timestamp when the request is completed, that is, has 8 positive or 4 negative votes"
647
648
649 .. _TVerify-Vote:
650
651 ------------------
652 table TVerify-Vote
653 ------------------
654
655 Table contains one record for each vote made by a TVerify Admin
656
657 .. csv-table::
658 :header-rows: 1
659 :align: left
660
661 "Field","Type","Comment"
662 "tverify","int(11)","Foreign key to table TVerify, identifying the record that's voted"
663 "memid","int(11)","Foreign key to table Users, identifying the TVerify Admin who has made this vote"
664 "when","datetime","Timestamp when the vote was made"
665 "vote","tinyint(1)","Result of the vote, 1 for agree, -1 for disagree"
666 "comment","varchar255","A free text comment by the TVerify Admin. It is included in the notification mail to the requesting user if the request is accepted or rejected."
667
668
669 .. _UserLocations:
670
671 -------------------
672 table UserLocations
673 -------------------
674
675 ... seems to store the user's location...
676
677 Currently it is not used anywhere in the code, but it seems to be prepared to hold multiple locations per user.
678
679 .. csv-table::
680 :header-rows: 1
681 :align: left
682
683 "Field","Type","Null","Key","Default","Extra"
684 "id","int(11)","NO","PRI","NULL","auto_increment"
685 "memid","int(11)","NO","","0",""
686 "ccid","int(11)","NO","","0",""
687 "regid","int(11)","NO","","0",""
688 "locid","int(11)","NO","","0",""
689
690
691 .. _users:
692
693 -----------
694 table users
695 -----------
696
697 Contains one record for each registered user.
698
699 .. csv-table::
700 :header-rows: 1
701 :align: left
702 :widths: 1,1,2
703
704 Field,Type,Comment
705 id,int(11),"Primary Key, autoincrement"
706 email,varchar(255),primary email address of the account
707 password,varchar(255),encrypted
708 fname,varchar(255),first name
709 mname,varchar(255),middle name
710 lname,varchar(255),last name
711 suffix,varchar(50),name suffix
712 dob,date,Date of Birth
713 verified,int(1),1 if probe mail answered
714 ccid,int(3),country: pointer to countries.id
715 regid,int(5),region: pointer to regions.id
716 locid,int(7),location: pointer to locations.id
717 listme,int(1),1 if published in Assurer List
718 codesign,int(1),1 if allowed to request code signing certs
719 1024bit,tinyint(1),?
720 contactinfo,varchar(255),?
721 admin,tinyint(1),1 if user is admin
722 ttpadmin,tinyint(1),"1 if user is TTP admin, it allows to set the Assurance Method to 'Trusted 3rd Parties' and leave some of those checkboxes on the Assurance page unchecked. It does not allow to issue more than the usual maximum points"
723 orgadmin,tinyint(1),1 if user is Org admin
724 board,tinyint(1),"1 if user has additional privileged of CAcert's board. In addition with ttpadmin allows to set all Assurance methods ('Face to Face Meeting', 'Trusted 3rd Parties', 'Thawte Points Transfer', 'Administrative Increase', 'CT Magazine - Germany'). Allows issuance of temporary increases if a sponsor (another user with board-flag set) is named."
725 tverify,tinyint(1),1 if user is tverify admin (?)
726 locadmin,tinyint(1),1 if user can administer the location database
727 language,varchar(5),preferred language (?)
728 Q1,varchar(255),Lost Password Question 1
729 Q2,varchar(255),Lost Password Question 2
730 Q3,varchar(255),Lost Password Question 3
731 Q4,varchar(255),Lost Password Question 4
732 Q5,varchar(255),Lost Password Question 5
733 A1,varchar(255),Lost Password Answer 1
734 A2,varchar(255),Lost Password Answer 2
735 A3,varchar(255),Lost Password Answer 3
736 A4,varchar(255),Lost Password Answer 4
737 A5,varchar(255),Lost Password Answer 5
738 created,datetime,timestamp of account creation (?)
739 modified,datetime,timestamp of last account modification (?)
740 deleted,datetime,"timestamp of account deletion, is set when the account is 'deleted' from the support interface"
741 locked,tinyint(1),"1 if account is locked; prevents user to login with this account, to create, revoke or update certs, to do assurances"
742 otppin,smallint(4),something with OneTimePassword? (eg http://www.freeauth.org/ (WIP))
743 uniqueID,varchar(255),"This is the 'SSO-ID' which is included in client certificates if the 'Add Single Sign On ID Information' button is selected during certificate creation. This ID is calculated during account creation (INSERT INTO Users) as a hash of the creation time and 64 byes of random. It is not guaranteed to be unique, but de facto collisions are extremly improbable."
744 orphash,varchar(16),something with OneTimePassword?
745 adadmin,tinyint(1), "0 = none, 1 = submit, 2 = approve"
746 assurer,int(2),"1 if user is Assurer (100 Assurance Points plus Challenge). This field is caching only, if performance does not forbid try to select the underlying data instead."
747 assurer_blocked,tinyint(1),1 if user may not become assurer
748 lastLoginAttempt,datetime,when the last failed login attempt for this user was
749
750
751 .. _User_Agreements:
752
753 ---------------------
754 table User_Agreements
755 ---------------------
756
757 Table to record instances when a user agreed to a specific agreement, currently only the CCA.
758
759 .. csv-table::
760 :header-rows: 1
761 :align: left
762
763 "Field","Type","Comment"
764 "ID","int(11) NOT NULL","Primary Key, autoincrement"
765 "memid","int(11)","Member for which the agreement is recorded"
766 "secmemid","int(11)","user that is involved in the agreement (e.g. Assurer) / ID of another member involved, like the counterpart in an Assurance"
767 "document","varchar(50)","Kind of agreement which got accepted, e.g. 'CCA'"
768 "date","datetime","Time the agreement was recorded"
769 "active","int(1)","whether the user actively agreed or if the agreement took place via an indirect process (e.g. Assurance)"
770 "method","varchar(100)","in which process did the agreement take place (e.g. certificate issuance, account creation, assurance)"
771 "comment","varchar(100)","user comment, Describes the circumstances, currently one of 'Assuring', 'Being assured', 'GPG', 'called from ...', depending on which action the user wanted to do when accepting the agreement."
772
773
774 Organisations Data
775 ==================
776
777
778 .. _OrgDomainCerts:
779
780 --------------------
781 table OrgDomainCerts
782 --------------------
783
784 Contains Org server certificates
785
786 .. csv-table::
787 :header-rows: 1
788 :align: left
789
790 "Field","Type","Null","Key","Default","Extra"
791 "id","int(11)","NO","PRI","NULL","auto_increment"
792 "orgid","int(11)","NO","","0",""
793 "subject","text","NO","","",""
794 "serial","varchar(50)","NO","","",""
795 "CN","varchar(255)","NO","","",""
796 "csr_name","varchar(255)","NO","","",""
797 "crt_name","varchar(255)","NO","","",""
798 "created","datetime","NO","","0000-00-00 00:00:00",""
799 "modified","datetime","NO","","0000-00-00 00:00:00",""
800 "revoked","datetime","NO","","0000-00-00 00:00:00","Is set to '1970-01-01 10:00:01' if the certificate shall be revoked. Acts as trigger for server process to do the revocation and to insert the current timestamp here."
801 "expire","datetime","NO","","0000-00-00 00:00:00",""
802 "renewed","tinyint(1)","NO","","0",""
803 "rootcert","int(2)","NO","","1",""
804 "md","enum('md5','sha1','sha256','sha512')","NO","","sha512","[[https://bugs.cacert.org/view.php?id=1237|bug#1237]]"
805 "type","tinyint(4)","YES","","NULL",""
806 "warning","tinyint(1)","NO","","0",""
807 "pkhash","varchar(40)","YES","MUL","NULL",""
808 "certhash","varchar(40)","YES","","NULL",""
809 "coll_found","tinyint(1)","NO","","0",""
810 "description","varchar(100)","NO","","''","[[https://bugs.cacert.org/view.php?id=782|bug #782]]"
811
812
813 .. _OrgDomains:
814
815 ----------------
816 table OrgDomains
817 ----------------
818
819 The domains associated to an Organisation (?)
820
821 .. csv-table::
822 :header-rows: 1
823 :align: left
824
825 "Field","Type","Null","Key","Default","Extra"
826 "id","int(11)","NO","PRI","NULL","auto_increment"
827 "orgid","int(11)","NO","","0",""
828 "domain","varchar(255)","NO","","",""
829
830
831 .. _OrgDomLink:
832
833 ----------------
834 table OrgDomLink
835 ----------------
836
837 Cross-table linking Org(server?)certs with corresponding domain (?)
838
839 .. csv-table::
840 :header-rows: 1
841 :align: left
842
843 "Field","Type","Null","Key","Default","Extra"
844 "orgcertid","int(11)","NO","PRI","0",""
845 "orgdomid","int(11)","NO","PRI","0",""
846
847
848 .. _OrgEmailCerts:
849
850 -------------------
851 table OrgEmailCerts
852 -------------------
853
854 Contains Org client certificates
855
856 .. csv-table::
857 :header-rows: 1
858 :align: left
859
860 "Field","Type","Null","Key","Default","Extra"
861 "id","int(11)","NO","PRI","NULL","auto_increment"
862 "orgid","int(11)","NO","","0",""
863 "serial","varchar(50)","NO","","",""
864 "CN","varchar(255)","NO","","",""
865 "subject","text","NO","","",""
866 "keytype","char(2)","NO","","NS",""
867 "csr_name","varchar(255)","NO","","",""
868 "crt_name","varchar(255)","NO","","",""
869 "created","datetime","NO","","0000-00-00 00:00:00",""
870 "modified","datetime","NO","","0000-00-00 00:00:00",""
871 "revoked","datetime","NO","","0000-00-00 00:00:00","Is set to '1970-01-01 10:00:01' if the certificate shall be revoked. Acts as trigger for server process to do the revocation and to insert the current timestamp here."
872 "expire","datetime","NO","","0000-00-00 00:00:00",""
873 "renewed","tinyint(1)","NO","","0",""
874 "rootcert","int(2)","NO","","1",""
875 "md","enum('md5','sha1','sha256','sha512')","NO","","sha512","[[https://bugs.cacert.org/view.php?id=1237|bug#1237]]"
876 "type","tinyint(4)","YES","","NULL",""
877 "codesign","tinyint(1)","NO","","0",""
878 "warning","tinyint(1)","NO","","0",""
879 "pkhash","varchar(40)","YES","MUL","NULL",""
880 "certhash","varchar(40)","YES","","NULL",""
881 "coll_found","tinyint(1)","NO","","0",""
882 "description","varchar(100)","NO","","''","[[https://bugs.cacert.org/view.php?id=782|bug #782]]"
883
884
885 .. _OrgEmailLink:
886
887 ------------------
888 table OrgEmailLink
889 ------------------
890
891 Cross-table linking Org(client?)certs with corresponding OrgDomain (?)
892
893 .. csv-table::
894 :header-rows: 1
895 :align: left
896
897 "Field","Type","Null","Key","Default","Extra"
898 "emailcertsid","int(11)","NO","MUL","0",""
899 "domid","int(11)","NO","","0",""
900
901
902 .. _OrgInfo:
903
904 -------------
905 table OrgInfo
906 -------------
907
908 One record for every registered organisation.
909
910 .. csv-table::
911 :header-rows: 1
912 :align: left
913
914 "Field","Type","Null","Key","Default","Extra"
915 "id","int(11)","NO","PRI","NULL","auto_increment"
916 "contact","varchar(255)","NO","","","email address"
917 "O","varchar(255)","NO","","","Org name"
918 "L","varchar(255)","NO","","","Org location (City)"
919 "ST","varchar(255)","NO","","","Org state (?)"
920 "C","char(2)","NO","","","Org country (e.g. 'DE') ISO-Alpha-2-Code?"
921 "comments","text","NO","","",""
922 "creator_id","int(11)","NO","","'0'","which Organisation Assurer entered the organisation?"
923 "created","datetime","NO","","'0000-00-00 00:00:00'","when was the organisation entered?"
924 "deleted","datetime","NO","","'0000-00-00 00:00:00'","allow for marking as deleted instead of really deleting"
925
926
927 .. _Org:
928
929 ---------
930 table Org
931 ---------
932
933 Links Organisations and corresponding OrgAdmins
934
935 .. csv-table::
936 :header-rows: 1
937 :align: left
938
939 "Field","Type","Null","Key","Default","Extra"
940 "orgid","int(11)","NO","PRI","0","References OrgInfo.id"
941 "memid","int(11)","NO","PRI","0","References Users.id"
942 "OU","varchar(255)","NO","","",""
943 "masteracc","int(1)","NO","","0",""
944 "comments","text","NO","","",""
945 "creator_id","int(11)","NO","","'0'","which Organisation Assurer assigned the Organisation Admin?"
946 "created","datetime","NO","","'0000-00-00 00:00:00'","when was the Organisation Admin assigned?"
947 "deleted","datetime","NO","","'0000-00-00 00:00:00'","allow for marking as deleted instead of really deleting"
948