Proposed Testing Protocol

<!-- = Drizzle Testing Protocol Proposal =

Goals

 * Making it more modular
 * Making it more readable
 * Make it easier for people to contribute
 * Provide better coverage (by re-building with a plan for coverage, bugs and use cases)

Requirements
There are at least three different areas needed in drizzle testing. Each area is actually dependent and built on subsequent areas listed.


 * 1) Testing SQL commands/syntax
 * 2) Testing necessary configurations (instances/connections) required for Testing SQL commands
 * 3) Testing of OS commands and options (which includes generating/running different configurations)

Proposal
The Drizzle TP (Test Protocol) is to replace mysql-test (augment initially until it has traction)

A new syntax for tests, that follows the more traditional Unit Test syntax (e.g. Suites of Test Cases each with a number of Tests) The Test has the conditions, setup, teardown, operations, assertions and results all in one file (*)

The TP is a human readable language agnostic syntax. There is a need to have translation of the TP to a language (e.g. lua, python, perl etc etc) These is a needed core infrastructure that can interact with a native driver, to execute statements and OS commands, and also provide access to the results closer to the protocol level. One such implementation could be:


 * A C infrastructure with embedded DPM engine (already working proxy with c/lua support).
 * The use of DPM to speak protocol and integrate resultset support at a more detailed level. (helps in choice of Lua)
 * The choice of language for tests is Lua, with a meta simplified syntax for humans.

TP Syntax
Initialize: master_only_simple SetUp: USE test; DROP TABLE IF EXISTS t1; TearDown: DROP TABLE IF EXISTS t1; Test: SELECT 1 AS col rows=1 md5=.... Test: SELECT 2 AS col rows=1 md5=999999999999999

While keywords (Configuration,SetUp,TearDown,Test) are needed, ':' and indentation are completely optional right now (Just pythonized for readability) and open for input.

Q: Do we identify SQL with syntax so we know what is SQL and what is TP. e.g. {SELECT 1}

Initialize
Syntax: Initialize:

Runs a series of commands once off for the TestCase, and generally includes a test case call (e.g. master_only_simple) which creates the necessary instances and connections global variables, matching as the names represent.

Can provide support for a success of a previous TestCase (i.e. prerequisite) Any TestCase global directives (e.g. expectnoerror # This will produce an implied noerror after each SQL Statement)

Instances is a map of keys and all settings etc e.g. m1, m2, s1, s2, s3, etc m1 = {host:localhost,port=9900, ...} connections is a map of connections to given instances e.g. c1, c2, c3 etc

c1 = {instance=m1,user=root,password=,schema=x}

By default you specify the Instance & Connection globally, or maybe the first in the map is the default

instance=m1 connection=c1

This level of instances and connections is necessary to test replication/scale out and concurrency for example.

Other options to consider may be commands to execute after every SQL statement. One example could be substitution or abstraction of engine name (Innodb,MyISAM) etc, so that testing results is engine agnostic.

SetUp
Syntax: SetUp

Commands executed before each test

TearDown
Sytnax: TearDown

Commands executed after each test

Test
Syntax:  Test  [timeout=x] [testname]

connection=? # Support for concurrency testing rows=N noerror error NNNN

The human readable version is more a macro to expanded language syntax At any time when you want to do a really specific thing, just embedd the language syntax (e.g. lua0

{SELECT NOW AS date1} sleep(0.001) data1=rows[1][1] {SELECT NOW AS data2} data1 != rows[1][1]

e.g.

SELECT 1       is  rs=SQL("SELECT 1") rows=N         is  assertEquals(rs.rows, N) noerror           is  assertNull(c.error) error NNNN    is  assertEquals(c.error, NNNN)

Output
The minimum you need in output is

testname (999 ms)  pass|fail

I have not spent an time focusing on the output format, it seems an agreement to use TAP - http://en.wikipedia.org/wiki/Test_Anything_Protocol

Providing the infrastructure exposes the following information as listed in 'Test Infrastructure Variables' and level of reporting is possible.

Test Infrastructure Variables
I would like to see the following variables available for the TP, and the reporting can then be produced on all of these.

RunResult SuccessFail totalTime NoSuites NoTestCases NoTests SuiteResult[]
 * 1) Global Structure

SuiteResult SuiteName NoTestsCases SuccessFail totalTime TestCaseResult[]
 * 1) For each Suite Executed

TestCaseResult testCaseName NoTests SuccessFail totalTime TestResult[]
 * 1) for each TestCase Executed

TestResult testName (defaults to testNNNN) SuccesFail time
 * 1) for each Test Executed

Creating Tests

 * A Large number of code-coverage tests (475 test, 200k test case lines) can be easily re-generated (work I've done previously)
 * Parsing of existing mysql-test cases to produce basic tests (e.g. Test: SQL,rows=,md5 could be easily produced)
 * Test Submission webpage, simply cut/paste SQL (with results), optionally data. Script to parse into  Drizzle  TP.

Implementation
The choice of language has not been decided. It could easily be Perl, Python or Lua for example, the following is just an example option with Lua. The decision of the language will come down to who does the work. It was proposed at the Drizzle Bof at OSCON that we can easily have multiple language implementations and see that the strongest survives.

Choosing a Test Protocol Interpreter
You need the capacity to encapsulate the language syntax into the test cases

Transforming the human test language to a programming language Execution.

On option is Lua. It provides a unit testing syntax (as shown below) which is simpler to other languages (so neither here or there). It's small to integrate It's the in thing By plugging into dpm proxy, you get to leverage the important goals of the tests accessing the protocol results. dpm proxy is at http://consoleninja.net/code/dpm/README.html

Lua Unit Testing Syntax
The Testing Protocol needs to go through a translation from (easily human readable) to lua. A possible translation would look similar to (note, this is just typed syntax, not proven)

Lua unit test syntax is similar to Java. Docs at http://lua-users.org/wiki/UnitTesting

-- Unit testing starts require('luaunit')

TestCase = {} --class function TestCase:setUp -- this function is run before each test end

function TestCase:tearDown -- this function is executed after each test end

function TestCase:test1 rs = SQL("SELECT 1") assertEquals( rs.rows, 1 ) assertEquals( md5(rs.???, "c4c43a9efab8cc70ee1fd9580693e1f2")   assertEquals( type(rs.rows[1][1]), 'number' )    assertEquals( rs.rows[1][1], 1)  end

luaUnit:run

Q & A
Q: How do I solve the non-deterministic nature (e.g. EXPLAIN for Innodb) A: Your assertions is not a byte by byte comparision (it can be via md5), you can do particular pattern matching

Existing MySQL Test Suite
The Old Way

t/select.test -- SELECT 1 AS col --

r/select.result -- col 1 --

Issues

 * Two files per test
 * As results size grows, results files grow
 * Poor support for different engines
 * Arbitrary sorting not supported (a result of engines)
 * Non deterministic values not supported (e.g. CURDATE)
 * Many unique types of additional commands with -- syntax

How other unit testing works?
Using Junit (www.junit.org) as an example of unit testing and the suite of tools that provide many layers of testing on top of this.


 * A Suite has one or more Test Cases
 * A Test Case (individual file) has one or more tests (@Test is annotation used)
 * A Test Case may have an optional setUp (@Before) and tearDown(@After) block that executes for each test in the test case
 * Each Test has a number of commands, and a number of assertions (all in the test), e.g. AssertEquals(x,1), AssertNotNull(var) etc
 * There are additional options, for example timeout, error handling etc

For the purposes of the following I'm going to define components as per this syntax. i.e. (Suite, TestCase, SetUp, TearDown, Test, Assert)

Building a new testing protocol I want to step through the basis of defining a syntax from simplest to more complex to understand why issues/decisions result in more syntax requirements.

The New Way (using a psuedo English language to start with). We including the test, and the comparison of test results in one file.

What is must support

 * Connection Management
 * Concurrency
 * arbitrary sorting (i.e. results differ from engines, but contain same rows)
 * pattern abstraction  (e.g. ENGINE=MyISAM|Innodb)

The old way to over come this was let $ENGINE=`select variable_value from information_schema.global_variables where variable_name='STORAGE_ENGINE'`; --replace_result $ENGINE ENGINE

Some problems
We don't generally keep the full output of the test results. If there for example is a md5 difference, and certain tests only perform other assertions that do match, we have lost the underlying result set data. I forsee the reason of keeping a serialized version of the actual test results, for comparison, so there may need to be a testcase.data file for each testcase.

Proposta per un nuovo protocollo di test (TP) -->