Retrieve data from asqatasun database

audit
sql
mysql-workbench
statistics
database
Tags: #<Tag:0x00007fe6a3f90588> #<Tag:0x00007fe6a3f90448> #<Tag:0x00007fe6a3f90240> #<Tag:0x00007fe6a3f900d8> #<Tag:0x00007fe6a3f97f40>

#1

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?


#2

Ok finally i understood how the database works…


#3

@nivak91, great !
For other contributors, can you explain what you understood?

Did you look at the engine Conceptual Data Model ?


#4

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!


#5

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?


#6

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


#7

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?


#8

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 (…)
    • (…)


#9

Ok thanks! And what about criterion statistics table?


#10

Ok i get it about Nb_Failed_Occurences, but where is the total number of tested cases?


#11

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)

#12

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)


#13

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?


#14

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      | &lt;a class=&quot;btn btn-default active&quot; data-status=&quot;open&quot;&gt;8 Open&lt;/a&gt; |                 6 |
| SourceCodeRemarkImpl |                82 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |          95 | a      | &lt;a class=&quot;btn btn-default&quot; data-status=&quot;closed&quot;&gt;0 Closed&lt;/a&gt;    |                 6 |
| SourceCodeRemarkImpl |                83 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |          99 | span   | &lt;span class=&quot;pull-left&quot;&gt;Title&lt;/span&gt;                                      |                 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      | &lt;a class=&quot;btn btn-default active&quot; data-status=&quot;open&quot;&gt;8 Open&lt;/a&gt; |                 6 |
| SourceCodeRemarkImpl |                82 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |          95 | a      | &lt;a class=&quot;btn btn-default&quot; data-status=&quot;closed&quot;&gt;0 Closed&lt;/a&gt;    |                 6 |
| SourceCodeRemarkImpl |                83 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |          99 | span   | &lt;span class=&quot;pull-left&quot;&gt;Title&lt;/span&gt;                                      |                 6 |
| SourceCodeRemarkImpl |                98 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |         652 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |                99 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |         737 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               100 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |         815 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               101 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |         896 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               102 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |         974 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               103 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1053 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               104 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1124 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               105 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1203 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               106 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1279 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               107 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1350 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               108 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1428 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 6 |
| SourceCodeRemarkImpl |               109 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1502 | span   | &lt;span class=&quot;counter up&quot;&gt;0&lt;/span&gt;                                         |                 6 |
| SourceCodeRemarkImpl |               110 | FAILED | BadContrast  | NULL             | NULL                 |                  0 |        1508 | span   | &lt;span class=&quot;counter down&quot;&gt;0&lt;/span&gt;                                       |                 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)

#15

Ok thanks a lot @fabrice your info was really helpfull!