Re-evaluate schema for several queries

Currently each and every test-run (including all re-runs because of flakiness) is stored in a very simple manner, as an individual row in a table, with a "result" column that can be pass/fail/xpass/xfail etc.

Queries

Evaluate how efficient this schema is for answering several different queries:

• When did a certain test passed? Failed?
• When did a certain test failed hard, even after all re-runs from testrunner?
• When did a certain test needed more than 1 re-run to pass (i.e. flaked)?
• Show all flaky tests in the last day.
• When did a test crashed?
• Show all crashed tests in the last day.

Stored information

Now every single test run or re-run is stored in the huge TABLE test_runs_raw.
We rarely care about most of this, and accessing what we care for is very slow because of the total size.

Post-process the data with the ultimate goal of answering the above queries fast.
For example, for the non-passing tests store the final test outcome

• test_outcome => flaky-pass,fail,crash,xpass

What about BFAIL/BPASS? These are too frequent.

TODO think more how to post-process the data and store efficiently.

misc thoughts

• What about SKIPped tests? (Are they recorded properly in the XML? QTBUG-93487 )
• What if a test crashes and the passes? Is it just FLAKY? How do I store that in the database, and how do I query such incidents?
  • Possible solution: separate TABLE with entries only for the test_runs that re-ran; for each one store an ARRAY of testresults, for example [CRASH,PASS]
• It is possible that a test writes PASS to the XML file, but then crashes!
  • This is up to the testrunner (qt-testrunner or ctest) to identify, and save it in a file. QTQAINFRA-5146
  • How to store that in the database?
• What if Coin "landed on its edge" before even starting?
• What if Coin timed out a test?
  • The API should tell us the workitem failure reason
• What if XML file was corrupt?
  • Use --output-junit FILENAME in ctest >= 3.21 to get the total list of results from ctest's POV.