## Please edit system and help pages ONLY in the master wiki!
## For more information, please see MoinMoin:MoinDev/Translation.
## IMPORTANT NOTE:
## When you use this page as a template for creating your project page:
##  * please remove all lines starting with two hashes (##)
##  * except the acl line, please keep that, but remove one hash, so it reads #acl ...
##  * fix the acl line so it has the correct page instead of the sample Project/...Group
##acl Project/AdminGroup:admin,read,write,delete,revert Project/ReadWriteGroup:read,write Project/ReadGroup:read
##master-page:Unknown-Page
##master-date:Unknown-Date
#format wiki
#language en

 . '''To [[Brain/Study/EducationTraining/Campus|Education Campus]]''' - '''To [[Brain/Study/EducationTraining/CATSTranslation|CATS Translation]]'''

----

= 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...

 Table of Contents  <<TableOfContents(3)>>


== Database tables ==

The database can be accessed on the development system by 

mysql --user=cats_user --password=''PASSWORD'' cats_db

See also the [[http://cats.cacert.org/doku/developer.html|documentation included the CATS]]

=== answers ===

 || ''Field '' || ''Type'' || ''Null'' || ''Key'' || ''Default'' || ''Extra'' || ''Comment'' ||
 || a_id || int(11) || NO || PRIMARY KEY || auto_increment ||  ||  ||
 || q_id || int(11) || NO ||  ||  '0' ||  ||  ||
 || answer || text || NO ||  ||   ||  ||  ||
 || correct || tinyint(1) || NOT NULL ||  ||  '0' ||  ||  ||
 || ref_a_id || int(11) || YES ||  ||  ||  || Reference to the "master answer" (the english one). This is used in the translation process. ||

=== 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' ||  || Refers the q_id of the corresponding question in table questions. ||
 || description || text || NOT NULL ||  ||   ||  || ||

=== 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.


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

=== schema_version ===

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

 || ''Field '' || ''Type'' || ''Null'' || ''Key'' || ''Default'' || ''Extra'' || ''Comment'' ||
 || id || int(11) || NO || PRIMARY KEY || auto_increment ||  ||  ||
 || version || int(11) || NO || UNIQUE ||  ||  || The record with the highest version field describes the current schema version ||
 || when || datetime || NO ||  ||  ||  || Holds the time when the update to this version number was installed ||

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

=== 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.

 || ''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 ==

The sourcecode is located in the [[https://github.com/CAcertOrg/cats|CATS repository on GitHub]]. See the [[https://github.com/CAcertOrg/cats/blob/release/INSTALL.txt|INSTALL.TXT]] there for installing instructions.

=== Adding a new user interface language ===

Once the translation process itself, which is outlined in [[Brain/Study/EducationTraining/CATSTranslation|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 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 ====

 * Log in to the [[https://cats1.it-sls.de:14843|CATS testsystem]] with an admin account. See [[#Adding a new Translator]] on how to create an admin account.
 * Go to "Topic" and use "Create new topic"
 * The name should be "Assurer's challenge (<Language code according to [[https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes|ISO 639-1]]>)", for sake of uniformity set questions/test to 25 and requirement to 80.
 * Find out the ID of the new test: {{{ SELECT * FROM topics; }}} or {{{ SELECT * FROM topics WHERE topic like '%<Language code>%'; }}}
 * Set information not available in the user interface: {{{ UPDATE topics SET lang='<Language code>', type_id=1 WHERE t_id=<ID of the test>; }}}
 * Copy the english questions, descriptions and answers to the new test:
{{{
  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 [[https://cacert1.it-sls.de/|CAcert testsystem]], so probably there will be warning messages about an unknown certificate issuer when trying to access the service.

 * The new user must log in to the [[https://cats1.it-sls.de:14843/|CATS testsystem]] with one of his client certificates. Certificates from the CAcert testsystem should work as well as "normal" CAcert client certificates.
 * The new user then sends the serial number of her certificate to someone having shell access to the CATS testserver.
 * Set the admin flag for the user's account and store some meta information: 
{{{
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 ==

 . [[https://cats.test.cacert.org:14843/]]
 . CATS test is hosted on mgr.test.cacert.org, SSH deamon for mgr.test.cacert.org is running on port 14822

=== Using the CATS Test Systems ===

Traditionally the CATS Test System uses a certificates issued by the [[SystemAdministration/Systems/Test|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 [[SystemAdministration/Systems/Test|CAcert Test System]].

=== Known issues and differences to the production system ===

 * If you want to test the upload of results into the main database (of the CAcert Test System) you have to log in with a certificate created on the Test System.
 * The automatic CATS upload is running as a cronjob for user root, see the crontab for more details
 * The result upload often does not work, usually the necessary client certificate for the upload is expired.

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

----
 . CategoryAssuranceTraining
 . [[CategorySoftwareCATS]]