CAcert Code Development list.

[Bernhard Fröhlich <ted AT convey.de>]

Hi there,

is there any (non-sourcecode) documentation of the CAcert database 
structure? Or other non-sourcecode documentation on the CAcert 
application anyway? I did browse the SVN server and could not find 
anything relevant, so during development and code review I tried to make 
some notes on my own for the tables I stumbled upon.

The result is attached to this mail, of course it is far from being 
complete, but is there anything more complete? If yes, where can I find 
it, if no, where should I post my results so other people can contribute 
to it?

Ted
;)

Title: Database structure of the CAcert database

Database structure of the CAcert database

Note that all table names are lowercase in the database. The names are
capitalized here for easier reading.
Foreign key relations are listed as an information, the database does
not enforce referential integrity.

Note that this is not a defining document, it mostly contains results of re-engeneering and code review.
It may not be correct and is far from being complete, so before relying on information from this document please do some research and tests of your own.

AbuserReports	AddLang	Advertising	AdminLog	Alerts	BadDomains
Countries	DisputeDomain	DisputeEmail	DomLink	DomainCerts	Domains
Email	EmailCerts	EmailLink	GPG	Languages	LocAlias
Locations	News	Notary	OTPHashes	Org	OrgDomLink
OrgDomainCerts	OrgDomains	OrgEmailCerts	OrgEmailLink	OrgInfo	PingLog
Regions	Root_Certs	StampCache	Cats_Passed	Cats_Type	Cats_Variant
TVerify	TVerify-Vote	Tickets	UserLocations	Users	Users_Agreements

Miscellaneous Tables

AbuserReports

AddLang

AdminLog

Advertising

Alerts

BadDomains

Countries

DisputeDomain

DisputeEmail

GPG

Languages

LocAlias

Locations

News

OTPHashes

PingLog

Regions

Root_Certs

Just a simple list connecting the root cert's CN to an ID for efficient
storage and reference.

Field	Type	Comment
ID	int(2)	Primary Key
Cert_Text	varchar(255)	CN as stored in the certificate

StampCache

Tickets

User Data

DomainCerts

Domains

DomLink

EmailCerts

EmailLink

Email

Notary

This table contains all data for events which award Trust Points:
Assurances, TTP, Thawte Point transfers etc.

Field	Type	Comment
ID	int(11)	Primary Key, autoincrement
From	int(11)	Foreign key to users, user awarding the Trust Points
To	int(11)	Foreign key to users, user receiving the Trust Points
Awarded	int(3)	Number of points the Assurer awarded
Points	int(3)	Number of points credited to the receiver, may be less than awarded if receiver already has 100 points.
Method	enum	Kind of event, some examples: Face to Face Meeting ("Normal" Assurance) Administrative Increase (2 points for assuring someone else) Thawte Points Transfer
Location	varchar(255)	Free text
Date	varchar(255)	Date as entered in the Assure Someone application (free text)
When	datetime	Timestamp of form completion, recorded automatically
Expire	datetime	? Expiry timestamp of temporary awarded points?
Sponsor	int(11)	?

So the current point value of a user with ID 19 is calculated by
SELECT SUM(Points) FROM notary N WHERE N.To='19' AND Points IS NOT NULL AND (Expire IS NULL OR Expire > CURRENT_TIMESTAMP());

Cats_Passed

Lists all the tests passed by a user.

Field	Type	Comment
id	int(11)	Primary Key, autoincrement
user_id	int(11)	Foreign key to table users. User that has passed this test
variant_id	int(11)	Foreign key to table Cats_variant. Exact kind of test passed.
pass_date	timestamp	Timestamp of passing the test

Cats_Type

Contains all the different kind of tests, currently the Assurer
Challenge.
Another planned type is the Test for Organisation Assurers.
This defines what a test is good for.

Field	Type	Comment
id	int(11)	Primary Key, autoincrement
type_text	varchar(255)	Short description of the test type

Cats_Variant

Describes the variants of each cats_type, like translation in different
languages.
This defines exactly which test has been passed.

Field	Type	Comment
id	int(11)	Primary Key, autoincrement
type_id	int(11)	Foreign key to Cats_type
test_text	varchar(255)	Short description of the test variant

TVerify

TVerify-Vote

UserLocations

Users

Contains one record for each registered user.

Field	Type	Comment
id	int(11)	Primary Key, autoincrement
email	varchar(255)	primary email address of the account
password	varchar(255)	encrypted
fname	varchar(255)	first name
mname	varchar(255)	middle name
lname	varchar(255)	last name
suffix	varchar(50)	name suffix
dob	date	Date of Birth
verified	int(1)	1 if probe mail answered
ccid	int(3)	country?
regid	int(5)	region?
locid	int(7)	location?
listme	int(1)	1 if published in Assurer List
codesign	int(1)	1 if allowed to request code signing certs
1024bit	tinyint(1)	?
contactinfo	varchar(255)	?
admin	tinyint(1)	1 if user is admin
ttpadmin	tinyint(1)	1 if user is TTP admin
orgadmin	tinyint(1)	1 if user is Org admin
board	tinyint(1)	1 if user is member of CAcert's board (?)
tverify	tinyint(1)	1 if user is tverify admin (?)
locadmin	tinyint(1)	1 if user can administer the location database
language	varchar(5)	preferred language (?)
Q1	varchar(255)	Lost Password Question 1
Q2	varchar(255)	Lost Password Question 2
Q3	varchar(255)	Lost Password Question 3
Q4	varchar(255)	Lost Password Question 4
Q5	varchar(255)	Lost Password Question 5
A1	varchar(255)	Lost Password Answer 1
A2	varchar(255)	Lost Password Answer 2
A3	varchar(255)	Lost Password Answer 3
A4	varchar(255)	Lost Password Answer 4
A5	varchar(255)	Lost Password Answer 5
created	datetime	timestamp of account creation (?)
modified	datetime	timestamp of last account modification (?)
deleted	datetime	timestamp of account deletion (?)
locked	tinyint(1)	1 if account is locked (?)
otppin	smallint(4)	something with OneTimePassword?
uniqueID	varchar(255)	?
orphash	varchar(16)	something with OneTimePassword?
adadmin	tinyint(1)	1 if user may administrate advertisement (?)
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.
assurer_blocked	tinyint(1)	1 if user may not become assurer

Users_Agreements

Table to record instances when a user agreed to a specific agreement, currently only the CCA.

Field	Type	Comment
ID	int(11) NOT NULL	Primary Key, autoincrement
memid	int(11)	Member for which the agreement is recorded
document	varchar(50)	Kind of agreement which got accepted, e.g. "CCA"
date	datetime	Time the agreement was recorded
type	varchar(10)	"Active" if the user herself did set the checkbox, "Passive" if someone else set it, like the Assurer for the Assuree during an Assurance
comment	varchar(500)	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.
secondary_memid	int(11)	ID of another member involved, like the counterpart in an Assurance

Organisations Data

OrgDomainCerts

OrgDomains

OrgDomLink

OrgEmailCerts

OrgEmailLink

OrgInfo

Org

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature