To Education Campus - To CATS Translation
CATS Software Developer Documentation
This page is intended for developers who want to extend (or understand) the CATS software which ist used for the AssurerChallenge and other tests. As usual it is incomplete and maybe not up-to-date. If you find something worth correcting, feel free...
Table of Contents
Database tables
The database can be accessed on the development system by
mysql --user=cats_user --password=PASSWORD cats_db
See also the documentation included the CATS
Questions
You guessed it, it's the table containing the questions.
Field |
Type |
Null |
Key |
Default |
Extra |
Comment |
q_id |
int(11) |
NO |
PRI |
NULL |
auto_increment |
|
qt_id |
int(11) |
NO |
|
0 |
|
|
t_id |
int(11) |
NO |
|
0 |
|
refers to the corresponding record in the topics table |
question |
text |
NO |
|
NULL |
|
|
active |
enum('1','0') |
NO |
|
0 |
|
Flag, 1 for "Question may be used in tests" |
description |
enum('1','0') |
NO |
|
0 |
|
Flag, 1 for "has a detailed comment in table (which one?)" |
ref_q_id |
int(11) |
YES |
|
NULL |
|
Refers to the "Master Question" in case of a translated question |
translationstatus |
int(11) |
YES |
|
NULL |
|
To support translation. NULL is for not initialized, 1 is for completed, 2 is for "in work", 3 is for "Master Question modified", 4 is for "new question". Questions with status 4 may later be modified/overwritten automatically in case the master question is modified. |
Currently 'ref_q_id' and 'translationstatus' are not supported in the sourcecode, so they are set manually by SQL statement and are not really reliable.
Topics
This table contains one record per available (language dependent) test.
Field |
Type |
Null |
Key |
Default |
Extra |
Comment |
t_id |
int(11) |
NO |
PRI |
NULL |
auto_increment |
|
topic |
varchar(50) |
NO |
UNI |
|
|
|
active |
tinyint(1) |
NO |
|
0 |
|
|
numOfQu |
tinyint(4) |
NO |
|
0 |
|
|
percentage |
tinyint(4) |
NO |
|
0 |
|
|
lang |
varchar(42) |
YES |
|
NULL |
|
|
type_id |
int(11) |
YES |
|
NULL |
|
|
answers
Field
Type
Null
Key
Default
Extra
Comment
a_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
q_id
int(11)
NOT NULL
'0'
answer
text
NOT NULL
correct
tinyint(1)
NOT NULL
'0'
answers_incorrect
Field
Type
Null
Key
Default
Extra
Comment
lp_id
int(11)
NOT NULL
PRIMARY KEY
'0'
q_id
int(11)
NOT NULL
PRIMARY KEY
'0'
learnprogress
Field
Type
Null
Key
Default
Extra
Comment
lp_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
user_id
varchar(15)
NOT NULL
'0'
root
set('CA Cert Signing Authority','CAcert Class 3 Root')
NOT NULL
date
datetime
NOT NULL
'0000-00-00 00:00:00'
date and time
t_id
int(11)
NOT NULL
'0'
topic ID
number
int(11)
NOT NULL
'0'
number of questions
correct
int(11)
NOT NULL
'0'
number of correct answers
wrong
int(11)
NOT NULL
'0'
number of incorret answers
percentage
decimal(5,0)
NULL
uploaded
tinyint(1)
NULL
NULL - not uploaded, 1 - succesfull upload, 2 - failed upload due to not matching cert
question_description
Field
Type
Null
Key
Default
Extra
Comment
q_id
int(11)
NOT NULL
PRIMARY KEY
'0'
description
text
NOT NULL
questions
Field
Type
Null
Key
Default
Extra
Comment
q_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
qt_id
int(11)
NOT NULL
'0'
question type
t_id
int(11)
NOT NULL
'0'
topic id
question
text
NOT NULL
question
active
enum('1','0')
NOT NULL
'0'
description
enum('1','0')
NOT NULL
'0'
questiontype
Obsolete, initially here the clear text names of questiontypes were stored. Now table questiontype_v2 is used for this.
Field
Type
Null
Key
Default
Extra
Comment
qt_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
questiontype key
DE
varchar(25)
NOT NULL
question type
EN
varchar(25)
NOT NULL
questiontype_v2
Field
Type
Null
Key
Default
Extra
Comment
qt_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
Type id which is referenced in the questions table
lang
varchar(5)
NOT NULL
PRIMARY KEY
Language of the descriptive text
qt_desc
varchar(25)
NOT NULL
Descriptive text for this type of question in the language stored in lang
statistics
Field
Type
Null
Key
Default
Extra
Comment
stat_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
q_id
int(11)
NOT NULL
'0'
question ID
count
int(11)
NOT NULL
'0'
count of answers
topics
Table topics contains the list of available tests.
Field
Type
Null
Key
Default
Extra
Comment
t_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
topic
varchar(50)
NOT NULL
UNIQUE KEY
Name of the test as shown in the dropdown list
active
tinyint(1)
NOT NULL
'0'
1 identifies a test which may be started
numOfQu
tinyint(4)
NOT NULL
'0'
The number of questions which shall be asked in a started test
percentage
tinyint(4)
NOT NULL
'0'
The percentage of correct answers necessary to pass a test
lang
varchar(42)
NULL
NULL
The language of the test. Currently this is not used in code.
type_id
int(11)
NULL
NULL
Reference to topic_type.type_id
topic_type
topic_type defines what a test is good for (Assurer Challenge, Triage Challenge, ...). All topics of the same type_id are equal in effect when passed. For example a passed test of type Assurer Challenge qualifies as Assurer.
Field
Type
Null
Key
Default
Extra
Comment
type_id
int(11)
NOT NULL
PRIMARY KEY
auto_increment
ID for reference purposes
text
varchar(255)
NOT NULL
Descriptive text. This is used in uploads of results to the main CAcert database as type identification, so when this is changed the effects of the corresponding tests are changed!
user
The list of user accounts. A user account currently is identified by the serial number and issuer of the certificate which was used to log in.
Field
Type
Null
Key
Default
Extra
Comment
user_id
varchar(10)
NOT NULL
PRIMARY KEY
'0'
The serial number of the identifying certificate
CN_name
varchar(100)
NOT NULL
Currently not set
lang
char(2)
NOT NULL
The most recently selected by the user
admin
enum('1','0')
NOT NULL
'0'
A 1 in this column identifies an administrative account
email
varchar(100)
NOT NULL
currently not set.
sendCert
set('no','email','post')
NOT NULL
'no'
currently unused
root
set('CA Cert Signing Authority','CAcert Class 3 Root')
NOT NULL
PRIMARY KEY
The issuer of the identifying certificate
user_address
This table was intended to store postal addresses for users who want to request printed certificates of achievement. Due to privacy concerns this feature was replaced by an informal mail communication, so the table currently is obsolete.
Field
Type
Null
Key
Default
Extra
Comment
user_id
varchar(10)
NOT NULL
PRIMARY KEY
'0'
root
set('CA Cert Signing Authority','CAcert Class 3 Root')
NOT NULL
PRIMARY KEY
firstname
varchar(25)
NOT NULL
lastname
varchar(25)
NOT NULL
street
varchar(50)
NOT NULL
housenumber
varchar(5)
NOT NULL
zipcode
varchar(10)
NOT NULL
city
varchar(30)
NOT NULL
state
varchar(50)
NOT NULL
country
varchar(50)
NOT NULL
Sourcecode
Adding a new user interface language
Once the translation process itself, which is outlined in CATS Translation, is completed a new UI language have to be supported in the PHP code.
Install the new <language>.php in the lang directory
Add support for the new language in the file functions/translation.php
Add the new language in file index.php.template, for the future
These steps may be prepared in advance, even while the translation is still not finished. Installing the modified PHP files with SVN will not make the new (probably incomplete) translation available.
Once everything is finished you have to add the new language in file index.php, add an additional option to the <select> control with name 'language'. This will make the new language available.
Test languages
The english test should be the master test. Every question should exist at least in the english test, so everyone can judge the correctness of the question and the answers. The english question should then be translated into the other languages, so during translation there should be no discussion about content, just about correct translation.
Currently translation is not well supported by the CATS admin interface, so some conventions and SQL statements are necessary during the translation process.
First of all the ID of the english question is used as "content id", all translations refer to the english question concerning the content. The content id is used as a prefix to the question text in sqare brackets ("[73]How often may you try this test?", "[73]Wie oft kannst du diesen Test durchführen?"), so it's possible for the end user to identify the content id in case of unclear, wrong or outdated questions.
When creating a test for a new language the questions table is filled by an SQL statement with copies of the english questions and ref_q_id set to the corresponding master question.
Test System
The machine is managed by AndreasBäß and MichaelTänzer (and potentialy all SA's)
Production System
See SystemAdministration/Systems/CATS
Samples, Bits and Pieces
Statements for Statistics
These scripts are used to extract statistics from the database for the Education Team Report.
Total number of tests in interval:
select count(*) from learnprogress where date between '2012-07-01' and '2013-07-01';
Numbers per test type:
select t.topic, count(*) from learnprogress lp, topics t where lp.t_id=t.t_id and date between '2012-06-01' and '2013-06-01' group by t.topic;
Number of passed tests (improvements are left as an exercise for the reader):
select count(*) from learnprogress lp where date between '2012-06-01' and '2013-06-01' and lp.t_id in (1,2) and lp.percentage >= 80 and correct > 0;
Number of users that did not pass:
select count(distinct u.user_id) from user u, learnprogress lp where date between '2012-06-01' and '2013-06-01' and lp.t_id in (1,2) and lp.user_id=u.user_id and not exists(select 1 from learnprogress lp2 where lp2.user_id=u.user_id and lp2.t_id in (1,2) and lp2.percentage >= 80 and lp2.correct > 0);
Average number of (failed) tests before first successful test:
select avg((select count(*) from learnprogress lp3 where lp3.user_id=lp.user_id and lp3.date < lp.date and lp3.date >= '2012-06-01')) from user u, learnprogress lp where date between '2012-06-01' and '2013-06-01' and lp.t_id in (1,2) and lp.user_id=u.user_id and lp.percentage >= 80 and lp.correct > 0 and not exists(select 1 from learnprogress lp2 where lp2.user_id=u.user_id and lp2.t_id in (1,2) and lp2.percentage >= 80 and lp2.correct > 0 and lp2.date < lp.date);