CATS Software Developer and Maintenance 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...

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

referts to the type of question in table questiontype

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

answers_incorrect

learnprogress

question_description

questions

questiontype

Obsolete, initially here the clear text names of questiontypes were stored. Now table questiontype_v2 is used for this.

questiontype_v2

statistics

topics

Table topics contains the list of available tests.

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.

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.

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.

Sourcecode

The sourcecode is located in the CATS repository on GitHub. See the INSTALL.TXT there for installing instructions.

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.

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.

Step by Step Instructions

  INSERT INTO questions(qt_id, t_id, question, active, description, ref_q_id) 
    SELECT qt_id, <ID of test>, question, active, description, q_id FROM questions WHERE t_id=1;
  INSERT INTO question_description(q_id, description) 
    SELECT q1.q_id, d2.description FROM questions q1, question_description d2 
      WHERE q1.t_id=<ID of test> AND q1.ref_q_id=d2.q_id;
  INSERT INTO answers(q_id, answer, correct) 
    SELECT q1.q_id, a2.answer, a2.correct FROM questions q1, answers a2 
      WHERE q1.t_id=<ID of test> AND q1.ref_q_id=a2.q_id;

Now the translator can log in and edit the texts in "her" test.

Adding a new Translator

Since there's no specific translator's interface yet, translators are added as admin accounts to the CATS testserver.

Note that the CATS Testserver uses certificates from the CAcert testsystem, so probably there will be warning messages about an unknown certificate issuer when trying to access the service.

update user set CN_name='<User's name or well known nickname>', admin=1, email='<User's contact address>' where user_id='<User cert's serial number>';

The meta information (CN_name and email) are not essential for the function but help the admins to keep track on who's who.

Moving a finished translation to the Production System

ToDo...

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);