CAcert Software Database Structure Defined

Miscellaneous Tables

schema_version

This table holds the current webdb revision. This table was introduced in Nov 2011 by Software-Assessment project bug #976. Current revision: '1' , November 23, 2011

Field

Type

Comment

id

int(11)

PRIMARY KEY auto_increment

version

int(11)

NOT NULL UNIQUE

when

datetime

NOT NULL

AbuserReports

This table probably was intended to be used by the (currently disabled?) menu item "Disputes/Abuses -> Report Abuse"...

AdminLog

Records changes of the DOB or the name executed by Support Engineers. A record is also written when an account is "deleted" as a result of an Arbitration.

Field

Type

NULL

Key

Default

Extras

when

datetime

NO

old-lname

varchar(255)

NO

old-dob

date

NO

new-lname

varchar(255)

NO

new-dob

date

NO

uid

int(11)

NO

adminid

int(11)

NO

type

varchar(50)

NO

NULL

See Bug#1135. New in Schema Version 4 (or 3?)

information

varchar(50)

NO

NULL

See Bug#1135. New in Schema Version 4 (or 3?)

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.

Advertising

Holds information about the advertising data, used for the Advertising on the WebDB

Field

Type

NULL

Key

Default

Extras

id

int(10) unsigned

NO

auto_increment, primary

replaceid

int(10) unsigned

NO

replaced

tinyint(3) unsigned

NO

orderid

tinyint(3) unsigned

NO

link

varchar(255)

NO

title

varchar(255)

NO

months

tinyint(3) unsigned

NO

who

int(10) unsigned

NO

when

datetime

NO

active

tinyint(3) unsigned

NO

approvedby

int(10) unsigned

NO

expires

datetime

NO

Alerts

Stores information about the alert settings of a user

Field

Type

NULL

Key

Default

Extras

memid

int(11)

NO

primary key

0

general

tinyint(1)

NO

0

country

tinyint(1)

NO

0

regional

tinyint(1)

NO

0

radius

tinyint(1)

NO

0

BadDomains

Should store information about domains that are not allowed to be used to add domains to a personal account

Field

Type

NULL

Key

Default

Extras

domain

varchar(255)

NO

DisputeDomain

An entry in this table is created if a user selects the menu item "Disputes/Abuses -> Domain Dispute".

Field

Type

NULL

Key

Default

Extras

memid

int(11)

NO

0

oldmemid

int(11)

NO

0

domain

varchar(255)

NO

created

datetime

NO

'0000-00-00 00:00:00'

hash

varchar(50)

NO

‘’

attempts

int(1)

NO

0

action

enum('accept','reject','failed')

NO

accept

DisputeEmail

An entry in this table is created if a user selects the menu item "Disputes/Abuses -> Email Dispute".

Field

Type

Comment

ID

int(11)

Primary Key

memid

int(11)

ID of the user account that initiated the "dispute"

oldmemid

int(11)

ID of the user account owning the disputed mail address

email

varchar(255)

Mail address which is disputed

created

datetime

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

attempts

int(1)

action

enum('accept','reject','failed')

Reply of the owner to the dispute, initialized to 'accept'

IP

varchar(20)

Remote IP address of machine from where the dispute was initiated

GPG

Field

Type

NULL

Key

Default

Extra

id

int(11)

NO

auto_increment

memid

int(11)

NO

'0'

email

varchar(255)

NO

level

int(1)

NO

'0'

multiple

tinyint(1)

NO

'0'

expires

tinyint(1)

NO

'0'

csr

varchar(255)

NO

crt

varchar(255)

NO

issued

datetime

NO

'0000-00-00 00:00:00'

expire

datetime

NO

'0000-00-00 00:00:00'

keyid

char(18)

NULL

warning

tinyint(1)

NO

'0'

description

varchar(100)

NO

bug #782

LocAlias

News

OrgAdminLog

Structure identical to AdminLog, see Bug#1135.

OTPHashes

PingLog

Root_Certs

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.

Field

Type

Comment

ID

int(2)

Primary Key

Cert_Text

varchar(255)

CN as stored in the certificate

StampCache

Tickets

AddLang

Field

Type

Comment

ID

int(?)

Primary Key

userid

int(?)

a Users Id, who selected a secondary language

lang

varchar(?)

language code, relates to table Languages locale, i.e. en_US, de_AT

Languages

Field

Type

Comment

locale

varchar(?)

i.e. en_US, de_AT, de_CH

en_co

varchar(?)

i.e. Austria, Germany, Switzerland

en_lang

varchar(?)

i.e. German, Danish, German

country

varchar(?)

i.e. Österreich, Danmark, Schweiz

lang

varchar(?)

i.e. Deutsch, dansk, Deutsch

Geographical Tables

Countries

List of Countries, fixed content

Field

Type

Comment

id

int(3)

Primary Key

name

varchar(50)

country name

acount

integer

how many assurers in this country?

Locations

List of Cities

Field

Type

Comment

id

int(7)

Primary Key

regid

int(4)

city relates to this region

ccid

int(3)

city relates to this country

name

varchar(50)

city name

lat

double(6,3)

latitude of the city

long

double(6,3)

longitude of the city

acount

integer

how many assurers in this city?

Sample for selecting cities circle around a defined distance using lat and long
// $maxdist = 200;   // km
  $maxdist = 450;   //  300 miles * 1,5 => km
// location      location.ID
  $locid = 2096325;        // San Diego
  $city = "San Diego CA, 12.5.2009";

  $query = "select * from `locations` where `id`='$locid'";
  $loc = mysql_fetch_assoc(mysql_query($query));

// `users`.`assurer`=1 => has passed assurer challenge
  $query = "SELECT ROUND(6378.137 * ACOS(0.9999999*((SIN(PI() * $loc[lat] / 180)
               * SIN(PI() * `locations`.`lat` / 180)) +
               (COS(PI() * $loc[lat] / 180 ) * COS(PI() * `locations`.`lat` / 180) *
               COS(PI() * `locations`.`long` / 180 - PI() * $loc[long] / 180)))), -1)
               AS `distance`
            FROM `locations`
            inner join `users`  on `users`.`locid` = `locations`.`id`
            HAVING `distance` <= '$maxdist'
            ORDER BY `distance` ";

Regions

List of Regions, fixed content

Field

Type

Comment

id

int(5)

Primary Key

ccid

int(3)

region relates to this country

name

varchar(50)

region name

acount

integer

how many assurers in this region?

User Data

DomainCerts

... contains server certificates

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

domid

int(11)

NO

0

reference to table domain.id

serial

varchar(50)

NO

CN

varchar(255)

NO

subject

text

NO

csr_name

varchar(255)

NO

crt_name

varchar(255)

NO

contains the filename of the certificate (see pages/account/15.php)

created

datetime

NO

0000-00-00 00:00:00

modified

datetime

NO

0000-00-00 00:00:00

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.

expire

datetime

NO

0000-00-00 00:00:00

warning

tinyint(1)

NO

0

renewed

tinyint(1)

NO

0

rootcert

int(2)

NO

1

md

enum('md5','sha1','sha256','sha512')

NO

sha512

bug#1237

type

tinyint(4)

YES

NULL

pkhash

varchar(40)

YES

MUL

NULL

certhash

varchar(40)

YES

NULL

coll_found

tinyint(1)

NO

0

description

varchar(100)

NO

bug #782

Domains

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

memid

int(11)

NO

MUL

0

reference to owner of the domain (users.id)

domain

varchar(255)

NO

created

datetime

NO

0000-00-00 00:00:00

modified

datetime

NO

0000-00-00 00:00:00

deleted

datetime

NO

0000-00-00 00:00:00

hash

varchar(50)

NO

attempts

int(1)

NO

0

similiar to EmailLink

This one seems to link DomainCerts to Domains.

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.

Field

Type

Null

Key

Default

Extra

certid

int(11)

NO

0

domid

int(11)

NO

0

EmailCerts

... contains client certificates? Details to be verified!

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

memid

int(11)

NO

0

serial

varchar(50)

NO

CN

varchar(255)

NO

subject

text

NO

keytype

char(2)

NO

NS

codesign

tinyint(1)

NO

0

csr_name

varchar(255)

NO

crt_name

varchar(255)

NO

created

datetime

NO

0000-00-00 00:00:00

modified

datetime

NO

0000-00-00 00:00:00

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.

expire

datetime

NO

0000-00-00 00:00:00

warning

tinyint(1)

NO

0

renewed

tinyint(1)

NO

0

rootcert

int(2)

NO

1

md

enum('md5','sha1','sha256','sha512')

NO

sha512

bug#1237

type

tinyint(4)

YES

NULL

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.

pkhash

varchar(40)

YES

MUL

NULL

certhash

varchar(40)

YES

NULL

coll_found

tinyint(1)

NO

0

description

varchar(100)

NO

bug #782

This one seems to link EmailCerts to Emails.

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.

Field

Type

Null

Key

Default

Extra

emailcertsid

int(11)

NO

MUL

0

emailid

int(11)

NO

0

Email

Contains a list of all mail adresses (including the primary one named in the Users table) associated to user accounts.

Field

Type

Comment

ID

int(11)

Primary Key, autoincrement

memid

int(11)

Foreign key to table Users, associated account

email

varchar(255)

created

datetime

modified

datetime

deleted

datetime

timestamp of deletion, is set if the user deletes the mail address from his/her account.

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.

attempts

int(1)

for verification process?

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. With bug-1042 (new point calculation) installed, points will normally be set to 0 and ignored in calculation of assurance/experience points

Method

enum

Kind of event, definitions see below

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)

is Sponsor if value != 0 and Points=200

deleted

datetime

NOT NULL, DEFAULT '0000-00-00 00:00:00'

Methods


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

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.

TVerify contains one record per user request to be TVerified.

Field

Type

Comment

id

int(11)

Primary Key, autoincrement

memid

int(11)

Foreign key to table Users, identifying the user which has submitted the contained data for review by TVerify Admins

photoid

varchar(255)

Path to uploaded image of a photo id by the user

URL

text

URL of the user in Thawte Freemail's Notary directory

CN

text

(probably)The Common Name contained in the requesting user's Thawte Freemail certificate

created

datetime

Timestamp when the request was initiated

modified

datetime

Timestamp when the request is completed, that is, has 8 positive or 4 negative votes

TVerify-Vote

Table contains one record for each vote made by a TVerify Admin

Field

Type

Comment

tverify

int(11)

Foreign key to table TVerify, identifying the record that's voted

memid

int(11)

Foreign key to table Users, identifying the TVerify Admin who has made this vote

when

datetime

Timestamp when the vote was made

vote

tinyint(1)

Result of the vote, 1 for agree, -1 for disagree

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.

UserLocations

... seems to store theuser's location...

Currently it is not used anywhere in the code, but it seems to be prepared to hold multiple locations per user.

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

memid

int(11)

NO

0

ccid

int(11)

NO

0

regid

int(11)

NO

0

locid

int(11)

NO

0

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: pointer to countries.id

regid

int(5)

region: pointer to regions.id

locid

int(7)

location: pointer to locations.id

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, 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

orgadmin

tinyint(1)

1 if user is Org admin

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.

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, is set when the account is "deleted" from the support interface

locked

tinyint(1)

1 if account is locked; prevents user to login with this account, to create, revoke or update certs, to do assurances

otppin

smallint(4)

something with OneTimePassword? x1)

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.

orphash

varchar(16)

something with OneTimePassword?

adadmin

tinyint(1)

0 = none, 1 = submit, 2 = approve

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

lastLoginAttempt

datetime

when the last failed login attempt for this user was

User_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

secmemid

int(11)

user that is involved in the agreement (e.g. Assurer) / ID of another member involved, like the counterpart in an Assurance

document

varchar(50)

Kind of agreement which got accepted, e.g. "CCA"

date

datetime

Time the agreement was recorded

active

int(1)

whether the user actively agreed or if the agreement took place via an indirect process (e.g. Assurance)

method

varchar(100)

in which process did the agreement take place (e.g. certificate issuance, account creation, assurance)

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.

Organisations Data

OrgDomainCerts

Contains Org server certificates

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

orgid

int(11)

NO

0

subject

text

NO

serial

varchar(50)

NO

CN

varchar(255)

NO

csr_name

varchar(255)

NO

crt_name

varchar(255)

NO

created

datetime

NO

0000-00-00 00:00:00

modified

datetime

NO

0000-00-00 00:00:00

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.

expire

datetime

NO

0000-00-00 00:00:00

renewed

tinyint(1)

NO

0

rootcert

int(2)

NO

1

md

enum('md5','sha1','sha256','sha512')

NO

sha512

bug#1237

type

tinyint(4)

YES

NULL

warning

tinyint(1)

NO

0

pkhash

varchar(40)

YES

MUL

NULL

certhash

varchar(40)

YES

NULL

coll_found

tinyint(1)

NO

0

description

varchar(100)

NO

bug #782

OrgDomains

The domains associated to an Organisation (?)

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

orgid

int(11)

NO

0

domain

varchar(255)

NO

Cross-table linking Org(server?)certs with corresponding domain (?)

Field

Type

Null

Key

Default

Extra

orgcertid

int(11)

NO

PRI

0

orgdomid

int(11)

NO

PRI

0

OrgEmailCerts

Contains Org client certificates

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

orgid

int(11)

NO

0

serial

varchar(50)

NO

CN

varchar(255)

NO

subject

text

NO

keytype

char(2)

NO

NS

csr_name

varchar(255)

NO

crt_name

varchar(255)

NO

created

datetime

NO

0000-00-00 00:00:00

modified

datetime

NO

0000-00-00 00:00:00

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.

expire

datetime

NO

0000-00-00 00:00:00

renewed

tinyint(1)

NO

0

rootcert

int(2)

NO

1

md

enum('md5','sha1','sha256','sha512')

NO

sha512

bug#1237

type

tinyint(4)

YES

NULL

codesign

tinyint(1)

NO

0

warning

tinyint(1)

NO

0

pkhash

varchar(40)

YES

MUL

NULL

certhash

varchar(40)

YES

NULL

coll_found

tinyint(1)

NO

0

description

varchar(100)

NO

bug #782

Cross-table linking Org(client?)certs with corresponding OrgDomain (?)

Field

Type

Null

Key

Default

Extra

emailcertsid

int(11)

NO

MUL

0

domid

int(11)

NO

0

OrgInfo

One record for every registered organisation.

Field

Type

Null

Key

Default

Extra

id

int(11)

NO

PRI

NULL

auto_increment

contact

varchar(255)

NO

email address

O

varchar(255)

NO

Org name

L

varchar(255)

NO

Org location (City)

ST

varchar(255)

NO

Org state (?)

C

char(2)

NO

Org country (e.g. "DE") ISO-Alpha-2-Code?

comments

text

NO

creator_id

int(11)

NO

'0'

which Organisation Assurer entered the organisation?

created

datetime

NO

'0000-00-00 00:00:00'

when was the organisation entered?

deleted

datetime

NO

'0000-00-00 00:00:00'

allow for marking as deleted instead of really deleting

Org

Links Organisations and corresponding OrgAdmins

Field

Type

Null

Key

Default

Extra

orgid

int(11)

NO

PRI

0

References OrgInfo.id

memid

int(11)

NO

PRI

0

References Users.id

OU

varchar(255)

NO

masteracc

int(1)

NO

0

comments

text

NO

creator_id

int(11)

NO

'0'

which Organisation Assurer assigned the Organisation Admin?

created

datetime

NO

'0000-00-00 00:00:00'

when was the Organisation Admin assigned?

deleted

datetime

NO

'0000-00-00 00:00:00'

allow for marking as deleted instead of really deleting

related pages

For the user defined function with in the software see Software/UsedFunctions.
For the usage of the x.php files see Software/UsedFiles.

Scripts

A script to create an "empty" database can be downloaded at https://test.cacert.org/testserver_dbstructure.sql.gz.

Note that this database has no content for the #Locations table, which is quite big and seldom used. If you want to import it into your database you may download it at https://test.cacert.org/Locations.sql.gz.

The structure of the productive database can be downloaded at https://www.cacert.org/sqldump.php. That scripts includes no data at all, so you might have to guess a bit (or check the testserver download above) which tables include important initial data.

Also Jan has provided some scripts in his repository:

I have "synthezized" a set of database initializiation scripts in my
database rework branch at
https://github.com/jandd/cacert-devel/tree/replace_deprecated_mysql_api/scripts/db_migrations

Maybe you can build on that. I also have a docker based setup to get
everything (signer, webdb application, MariaDB, Webserver, CATS) running in
multiple docker containers locally at
https://git.dittberner.info/jan/cacert-devsetup.

Documentation is a bit scarce but
https://github.com/jandd/cacert-devel/blob/replace_deprecated_mysql_api/README.md
is already a lot better than what we have in the current release branch.


Software/Database/StructureDefined (last edited 2021-08-14 13:10:02 by BernhardFröhlich)