Hi guys. I’m trying to figure out how to get a stored audit from the database. Could you help me? In which tables are the results of the audits stored?
Ok finally i understood how the database works…
1 Like
@nivak91, great !
For other contributors, can you explain what you understood?
Did you look at the engine Conceptual Data Model ?
https://doc.asqatasun.org/en/30_Contributor_doc/Engine/data_model_asqatasun_V3.0.png
Yes but i actually understood how things work, when i made a connection to asqatasun database with MySQL Workbench and applied some queries. It helped quite a lot understanding how the data of the audit are stored!
I have noticed that there are no test statistics for Web Resource with DTYPE=PageImpl, but only for SiteImpl…Why is that happening? What if i want to get for every individual page of a site Audit the test statistics? Should i get this information from the Criterion Statistics?
To get Statistics, you should have a look at the WebResourceStatistics Table, in which they are the number of passed, the number of failed, and so on… for any WebResource, whether page or site.
Regards
Koj
1 Like
Just one more question @koj …Nb_Passed Nb_Failed etc in web resource statistics refer to the number of different tests aplied or to the number of tested cases (for example a particular test of a success criterion could be tested several times in a web resource)? Also what about the same columns in test statistics and criterion statistics?
In a new Asqatasun (WEB_RESOURCE_STATISTICS table is empty),
I run 1 page audit.
Results of audit (scope=page)
- Passed 8
- Failed 10
- Not Applicable 113
- Pre-qualified 20
- Not tested 123
SQL request
SELECT * FROM WEB_RESOURCE_STATISTICS ;
SQL result
| Id_Web_Resource_Statistics | Mark | Raw_Mark | Nb_Passed | Nb_Failed | Nb_Nmi | Nb_Na | Nb_Suspected | Nb_Detected | Nb_Not_Tested | Weighted_Passed | Weighted_Failed | Weighted_Nmi | Weighted_Na | Nb_Failed_Occurrences | Nb_Invalid_Test | Id_Audit | Id_Web_Resource | Http_Status_Code | Manual_Audit | +----------------------------+---------+----------+-----------+-----------+--------+-------+--------------+-------------+---------------+-----------------+-----------------+--------------+-------------+-----------------------+-----------------+----------+-----------------+------------------+--------------+ | 1 | 48.7535 | 44.44 | 8 | 10 | 20 | 113 | 0 | 0 | 123 | 8.0 | 10.0 | 20.0 | 113.0 | 121 | 10 | 1 | 1 | 200 | 0 | +----------------------------+---------+----------+-----------+-----------+--------+-------+--------------+-------------+---------------+-----------------+-----------------+--------------+-------------+-----------------------+-----------------+----------+-----------------+------------------+--------------+ 1 row in set (0.00 sec)
- Nb_Failed = 10
- Nb_Failed_Occurrences = 121
- 19 elements Failed ----> 1.1.1 Does each image (img tag) have an alt attribute?
- 17 elements Failed ----> 3.3.1 Failed More on 3.3.1
On each Web page, do non-bold texts (…) - (…)
Ok thanks! And what about criterion statistics table?
1 Like
Ok i get it about Nb_Failed_Occurences, but where is the total number of tested cases?
Statistic tables
SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'asqatasun' AND TABLE_NAME LIKE '%STATISTICS%' ; +-------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +-------------------------+------------+ | CRITERION_STATISTICS | 102 | | TEST_STATISTICS | 0 | | THEME_STATISTICS | 13 | | WEB_RESOURCE_STATISTICS | 1 | +-------------------------+------------+
@mfaure, @koj, why TEST_STATISTICS is empty ?
View by criteria
SELECT count(*) FROM CRITERION_STATISTICS WHERE Criterion_Result = 'FAILED' +----------+ | count(*) | +----------+ | 8 | +----------+
View by tests
SELECT sum(Nb_Failed) FROM CRITERION_STATISTICS WHERE Criterion_Result = 'FAILED' +----------------+ | sum(Nb_Failed) | +----------------+ | 10 | +----------------+
SELECT c.Cd_Criterion, c.Label, s.Nb_Failed, s.Criterion_Result, c.Description FROM CRITERION_STATISTICS AS s, CRITERION AS c WHERE s.Id_Criterion = c.Id_Criterion AND Criterion_Result = 'FAILED' ; +--------------+-------+-----------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Cd_Criterion | Label | Nb_Failed | Criterion_Result | Description | +--------------+-------+-----------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Rgaa30-8-1 | 8.1 | 1 | FAILED | Chaque page Web est-elle définie par un type de document ? | | Rgaa30-8-4 | 8.4 | 1 | FAILED | Pour chaque page Web ayant une langue par défaut, le code de langue est-il pertinent ? | | Rgaa30-6-5 | 6.5 | 1 | FAILED | Dans chaque page Web, chaque lien, à lexception des ancres, a-t-il un intitulé ? | | Rgaa30-9-1 | 9.1 | 2 | FAILED | Dans chaque page Web, linformation est-elle structurée par lutilisation appropriée de titres ? | | Rgaa30-8-7 | 8.7 | 1 | FAILED | Dans chaque page Web, chaque changement de langue est-il indiqué dans le code source (hors cas particuliers) ? | | Rgaa30-8-9 | 8.9 | 1 | FAILED | Dans chaque page Web, les balises ne doivent pas être utilisées uniquement à des fins de présentation. Cette règle est-elle respectée ? | | Rgaa30-1-1 | 1.1 | 1 | FAILED | Chaque image a-t-elle une alternative textuelle ? | | Rgaa30-3-3 | 3.3 | 2 | FAILED | Dans chaque page Web, le contraste entre la couleur du texte et la couleur de son arrière-plan est-il suffisamment élevé (hors cas particuliers) ? | +--------------+-------+-----------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)
In Asqatasun : results of audit > view by tests
- Passed 8
- Failed 10
- Not Applicable 113
- Pre-qualified 20
- Not tested 123
8+10+113+20+123 = 274 (Level AA / RGAA 3.0)
In database
SELECT * FROM WEB_RESOURCE_STATISTICS WHERE Id_Web_Resource_Statistics = 1; | Id_Web_Resource_Statistics | Mark | Raw_Mark | Nb_Passed | Nb_Failed | Nb_Nmi | Nb_Na | Nb_Suspected | Nb_Detected | Nb_Not_Tested | Weighted_Passed | Weighted_Failed | Weighted_Nmi | Weighted_Na | Nb_Failed_Occurrences | Nb_Invalid_Test | Id_Audit | Id_Web_Resource | Http_Status_Code | Manual_Audit | +----------------------------+---------+----------+-----------+-----------+--------+-------+--------------+-------------+---------------+-----------------+-----------------+--------------+-------------+-----------------------+-----------------+----------+-----------------+------------------+--------------+ | 1 | 48.7535 | 44.44 | 8 | 10 | 20 | 113 | 0 | 0 | 123 | 8.0 | 10.0 | 20.0 | 113.0 | 121 | 10 | 1 | 1 | 200 | 0 | +----------------------------+---------+----------+-----------+-----------+--------+-------+--------------+-------------+---------------+-----------------+-----------------+--------------+-------------+-----------------------+-----------------+----------+-----------------+------------------+--------------+
- Nb_Passed = 8
- Nb_Failed = 10
- Nb_Na = 113
- Nb_Nmi = 20
- Nb_Not_Tested = 123
8+10+113+20+123 = 274 (Level AA / RGAA 3.0)
But these are the number of tests not the number of tested cases aren’t they? How could i know how many times each of these tests was tested in order to compute the failure rate Nb_Failed_Occurences/NumberOf TestedCases?
PROCESS_RESULT table
SELECT * FROM PROCESS_RESULT WHERE Id_Web_Resource = 1; +--------------------+-------------------+-----------------+----------------+------------------+-----------------------+---------------------+--------------------------+-----------------+---------+-----------------------+----------------------+ | DTYPE | Id_Process_Result | Element_Counter | Definite_Value | Indefinite_Value | Id_Audit_Gross_Result | Id_Audit_Net_Result | Id_Process_Result_Parent | Id_Web_Resource | Id_Test | Manual_Definite_Value | Manual_Audit_Comment | +--------------------+-------------------+-----------------+----------------+------------------+-----------------------+---------------------+--------------------------+-----------------+---------+-----------------------+----------------------+ | DefiniteResultImpl | 2 | 0 | NOT_APPLICABLE | NULL | 1 | 1 | NULL | 1 | 1064 | NULL | NULL | | DefiniteResultImpl | 3 | 0 | NOT_APPLICABLE | NULL | 1 | 1 | NULL | 1 | 936 | NULL | NULL | | DefiniteResultImpl | 4 | 80 | NEED_MORE_INFO | NULL | 1 | 1 | NULL | 1 | 946 | NULL | NULL | | DefiniteResultImpl | 5 | 0 | NOT_APPLICABLE | NULL | 1 | 1 | NULL | 1 | 1113 | NULL | NULL | | DefiniteResultImpl | 6 | 189 | FAILED | NULL | 1 | 1 | NULL | 1 | 884 | NULL | NULL | (...) +--------------------+-------------------+-----------------+----------------+------------------+-----------------------+---------------------+--------------------------+-----------------+---------+-----------------------+----------------------+ 151 rows in set (0.00 sec)
151 rows = 8+10+113+20
- Nb_Passed = 8
- Nb_Failed = 10
- Nb_Na = 113
- Nb_Nmi = 20
SELECT Id_Web_Resource, Id_Process_Result, Id_Test, Definite_Value, Element_Counter FROM PROCESS_RESULT WHERE Id_Web_Resource = 1 AND Definite_Value = 'FAILED'; +-----------------+-------------------+---------+----------------+-----------------+ | Id_Web_Resource | Id_Process_Result | Id_Test | Definite_Value | Element_Counter | +-----------------+-------------------+---------+----------------+-----------------+ | 1 | 6 | 884 | FAILED | 189 | | 1 | 15 | 883 | FAILED | 148 | | 1 | 24 | 996 | FAILED | 20 | | 1 | 69 | 982 | FAILED | 0 | | 1 | 71 | 966 | FAILED | 149 | | 1 | 73 | 987 | FAILED | 1 | | 1 | 82 | 807 | FAILED | 19 | | 1 | 118 | 998 | FAILED | 20 | | 1 | 121 | 990 | FAILED | 348 | | 1 | 139 | 993 | FAILED | 988 | +-----------------+-------------------+---------+----------------+-----------------+ 10 rows in set (0.00 sec)
PROCESS_REMARK table
SELECT * FROM PROCESS_REMARK WHERE Issue = 'FAILED'; +----------------------+-------------------+--------+--------------+------------------+----------------------+--------------------+-------------+--------+-------------------------------------------------------------------------------------------------+-------------------+ | DTYPE | Id_Process_Remark | Issue | Message_Code | Selected_Element | Selection_Expression | Character_Position | Line_Number | Target | Snippet | Id_Process_Result | +----------------------+-------------------+--------+--------------+------------------+----------------------+--------------------+-------------+--------+-------------------------------------------------------------------------------------------------+-------------------+ | SourceCodeRemarkImpl | 81 | FAILED | BadContrast | NULL | NULL | 0 | 94 | a | <a class="btn btn-default active" data-status="open">8 Open</a> | 6 | | SourceCodeRemarkImpl | 82 | FAILED | BadContrast | NULL | NULL | 0 | 95 | a | <a class="btn btn-default" data-status="closed">0 Closed</a> | 6 | | SourceCodeRemarkImpl | 83 | FAILED | BadContrast | NULL | NULL | 0 | 99 | span | <span class="pull-left">Title</span> | 6 | (...) +----------------------+-------------------+--------+--------------+------------------+----------------------+--------------------+-------------+--------+-------------------------------------------------------------------------------------------------+-------------------+ 121 rows in set (0.01 sec)
compute the failure rate Nb_Failed_Occurences/NumberOf TestedCases
SELECT * FROM PROCESS_REMARK WHERE Issue = 'FAILED' AND Id_Process_Result = '6'; +----------------------+-------------------+--------+--------------+------------------+----------------------+--------------------+-------------+--------+-------------------------------------------------------------------------------------------------+-------------------+ | DTYPE | Id_Process_Remark | Issue | Message_Code | Selected_Element | Selection_Expression | Character_Position | Line_Number | Target | Snippet | Id_Process_Result | +----------------------+-------------------+--------+--------------+------------------+----------------------+--------------------+-------------+--------+-------------------------------------------------------------------------------------------------+-------------------+ | SourceCodeRemarkImpl | 81 | FAILED | BadContrast | NULL | NULL | 0 | 94 | a | <a class="btn btn-default active" data-status="open">8 Open</a> | 6 | | SourceCodeRemarkImpl | 82 | FAILED | BadContrast | NULL | NULL | 0 | 95 | a | <a class="btn btn-default" data-status="closed">0 Closed</a> | 6 | | SourceCodeRemarkImpl | 83 | FAILED | BadContrast | NULL | NULL | 0 | 99 | span | <span class="pull-left">Title</span> | 6 | | SourceCodeRemarkImpl | 98 | FAILED | BadContrast | NULL | NULL | 0 | 652 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 99 | FAILED | BadContrast | NULL | NULL | 0 | 737 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 100 | FAILED | BadContrast | NULL | NULL | 0 | 815 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 101 | FAILED | BadContrast | NULL | NULL | 0 | 896 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 102 | FAILED | BadContrast | NULL | NULL | 0 | 974 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 103 | FAILED | BadContrast | NULL | NULL | 0 | 1053 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 104 | FAILED | BadContrast | NULL | NULL | 0 | 1124 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 105 | FAILED | BadContrast | NULL | NULL | 0 | 1203 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 106 | FAILED | BadContrast | NULL | NULL | 0 | 1279 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 107 | FAILED | BadContrast | NULL | NULL | 0 | 1350 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 108 | FAILED | BadContrast | NULL | NULL | 0 | 1428 | span | <span class="counter down">0</span> | 6 | | SourceCodeRemarkImpl | 109 | FAILED | BadContrast | NULL | NULL | 0 | 1502 | span | <span class="counter up">0</span> | 6 | | SourceCodeRemarkImpl | 110 | FAILED | BadContrast | NULL | NULL | 0 | 1508 | span | <span class="counter down">0</span> | 6 | +----------------------+-------------------+--------+--------------+------------------+----------------------+--------------------+-------------+--------+-------------------------------------------------------------------------------------------------+-------------------+ 16 rows in set (0.00 sec)
SELECT Id_Web_Resource, Id_Test, Definite_Value, Element_Counter FROM PROCESS_RESULT WHERE Id_Web_Resource = 1 AND Definite_Value = 'FAILED' AND Id_Process_Result = '6' ; +-----------------+---------+----------------+-----------------+ | Id_Web_Resource | Id_Test | Definite_Value | Element_Counter | +-----------------+---------+----------------+-----------------+ | 1 | 884 | FAILED | 189 | +-----------------+---------+----------------+-----------------+
SELECT * FROM TEST WHERE Id_Test = '884' +---------+--------------+-------------+-------+------+--------+-------------------+---------------------------------------------+-------------------------------------------------------------------------+--------------+-------------------+----------+---------+----------+------------+ | Id_Test | Cd_Test | Description | Label | Rank | Weight | Rule_Archive_Name | Rule_Class_Name | Rule_Design_Url | Id_Criterion | Id_Decision_Level | Id_Level | Id_Rule | Id_Scope | No_Process | +---------+--------------+-------------+-------+------+--------+-------------------+---------------------------------------------+-------------------------------------------------------------------------+--------------+-------------------+----------+---------+----------+------------+ | 884 | Rgaa30-3-3-2 | | 3.3.2 | 78 | 1.0 | rgaa30 | org.asqatasun.rules.rgaa30.Rgaa30Rule030302 | http://doc.asqatasun.org/en/90_Rules/rgaa3.0/03.Colours/Rule-3-3-2.html | 348 | NULL | 2 | NULL | 1 | | +---------+--------------+-------------+-------+------+--------+-------------------+---------------------------------------------+-------------------------------------------------------------------------+--------------+-------------------+----------+---------+----------+------------+ 1 row in set (0.00 sec)