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

answers

answers_incorrect

learnprogress

question_description

questions

You guessed it, it's the table containing the questions.

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.

questiontype

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

questiontype_v2

schema_version

This is intended to ease upgrades of the database schema, it is currently not used by the application itself.

statistics

TEMP

Tables temp and TEMP (and all other variations in different cases) are temporarily used for scripts. You may drop any of those tables any time, and, correspondingly, you should not assume that such table survives your logout...

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

  CREATE TABLE TEMP(q_id_en INTEGER, t_id INTEGER);
  INSERT INTO TEMP SELECT q_id, <ID of test> FROM questions q
    WHERE q.t_id=1 AND NOT EXISTS(SELECT 1 FROM questions q2 WHERE q2.t_id=<ID of test> AND q2.ref_q_id=q.q_id);

  INSERT INTO questions(qt_id, t_id, question, active, description, ref_q_id) 
    SELECT q.qt_id, t.t_id, q.question, q.active, q.description, t.q_id_en 
      FROM questions q, TEMP t
      WHERE q.t_id=1 AND t.q_id_en=q.q_id;
  INSERT INTO question_description(q_id, description) 
    SELECT q1.q_id, d2.description 
      FROM questions q1, question_description d2, TEMP t 
      WHERE q1.ref_q_id=t.q_id_en AND q1.t_id=t.t_id AND q1.ref_q_id=d2.q_id;
  INSERT INTO answers(q_id, answer, correct, ref_a_id) 
    SELECT q1.q_id, a2.answer, a2.correct, a2.a_id 
      FROM questions q1, answers a2, TEMP t 
      WHERE q1.ref_q_id=t.q_id_en AND q1.t_id=t.t_id 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...

Based on 

mysqldump  -u cats_user -p<password> cats_db questions --where="t_id=10"

Test System

Using the CATS Test Systems

Traditionally the CATS Test System uses a certificates issued by the CAcert Test System, so your browser should complain about an invalid certificate. You have to convince your browser to make an exception for this server. Please do not install the Test System Root certificates, unless you really know what you are doing! This is only an option for a dedicated test machine which is not used for "real" work.

To log in to the CATS Test System you may use normal CAcert client certificates or a certificate issued by the CAcert Test System.

Known issues and differences to the production system

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

Software/CATS/Developer (last edited 2021-08-16 14:01:05 by AlesKastner)