SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unit Testing - Sql Expectations

    Hi, first sorry for my poor English.
    I'm developing an O/R solution (yet another..) using SimpleTest for my test-cases, mocking the connection and the ResultSet's too. For example for an embedded value (PoEAA) mapper insert test:
    PHP Code:
    function test_embedded()
    {
        
    $currecy = new currecy('USD''Dollar');
        
    $product = new product('753df''Monitor', new money($currecy35.5));
        
        
    $sql "INSERT INTO products (id_product, name, stock, amount, currency_simbol, currency_name) ".
                
    "VALUES ('753df', 'Monitor', 0, 35.50, 'USD', 'Dollar')";
        
    $sql_expectation = array( new sql_expectation($sql));
        
        
    $this->connection->setReturnValue('executeUpdate'1);
        
    $this->connection->expectOnce('executeUpdate'$sql_expectation);
        
        
    $this->mapper->insert($product);
        
        
    $this->connection->tally();

    Now I don't like to do a strict comparison in the expectation (for example 35.50 is equal to 35.5 for the db engine! or the spaces are trimmed) so I wrote this sql_expectation using PEAR Sql-Parser (http://pear.php.net/package/SQL_Parser).
    PHP Code:
    require_once("Sql/Parser.php");

    class 
    sql_expectation extends SimpleExpectation
    {
        protected 
    $sql_expected;

        function 
    __construct($sql$message '%s')
        {
            
    parent::__construct($message);
            
    $this->sql_expected $sql;
            
    $this->parser = new Sql_parser();        
        } 
        function 
    test($sql)
        {
            if (
    $this->check_sql($sql))
                return 
    true;
            else
                return 
    false;
        } 

        function 
    testMessage($sql)
        {
            if (
    $this->test($sql))
                return 
    "Valid SQL";
            else
            {
                
    $message "<br>INVALID SQL<br>";
                
    $message .= "<pre>Actual:   $sql\n";
                
    $message .= "Expected: ".$this->sql_expected."</pre>";
                return 
    $message;
            } 
        } 

        function 
    check_sql($sql_actual)
        {
            
    $actual $this->parser->parse($sql_actual);
            
    $expected $this->parser->parse($this->sql_expected);

            
    ksort($actual);
            
    ksort($expected);

            
    $string_actual serialize($actual);
            
    $string_expected serialize($expected);
            if (
    $string_actual === $string_expected)
                return 
    true;
            else
                return 
    false;
        }    
        

    There is another way to do this kind of assertion?
    For example I want validating without caring about the order in clauses, I believe that the only way is to do a more particular diff on the result of the parsing, the question is how!.

    Edit: The Parse method returns an array:
    PHP Code:
    array(
        
    'command' => 'insert',
        
    'table_names' => array(
            
    => 'products'
            
    ),
        
    'column_names' => array(
            
    => 'id_product'
            
    ....
            ),
        
    'values' => array(
            
    => array(
                
    'value' => '753df',
                
    'type' => 'text_val'
                
    ),
            ....
            )
        ) 

  2. #2
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by k7k0
    Hi, first sorry for my poor English.
    I'm developing an O/R solution (yet another..) using SimpleTest for my test-cases, mocking the connection and the ResultSet's too.
    Cool.

    Quote Originally Posted by k7k0
    Now I don't like to do a strict comparison in the expectation (for example 35.50 is equal to 35.5 for the db engine! or the spaces are trimmed) so I wrote this sql_expectation using PEAR Sql-Parser
    You have done a fantastic job of getting to grips with the innards of SimpleTest . Unfortunately I think I might have sent you off on the wrong direction .

    See http://www.sitepoint.com/forums/show...7&postcount=18

    Now a confession . In using the database as an example of mock objects I think I made a big mistake. It can be done, but it's really not appropriate for testing SQL statements. After all, you are really testing not just the mechanics of your iterator, but the connection between the data and the SQL statement. I've been down this road and here is the problem I eventually ran into.

    It all goes great at first, your tests run fast and (given the mental agility of someone like Jason) quick and easy to write. The problem is that the tests are brittle. If you change the table name, say, you will have to go back and edit every single test. When mocks span a translation layer, and you are acting as a human compiler trying to translate things by hand, I'd get suspicious. By all means make a test of the iterator mechanics this way (mocks are good at that), but for anything depending on query text I'd use a real database. You can place these tests in a separate test suite so the unite tests don't depend on installed components and run quicker. I now place the database mapping tests into integration tests.

    Which makes my old PHP|Architect article on the subject a little misleading . What can I say? You live and learn.
    Ahem . It comes down to testing across marshalls being a bad thing. In fetching an object you have to test the query was correct (one translation) and the data was correct (second translation back the other way). These two translations are already tightly coupled and you expose them just where you are writing tests. Test code is verbose, so it's actually the worst place to cut it.

    You get the same thing with parsers. It's tempting to mock the tokeniser when testing the parser that uses it. This means you end up sending simulated token streams into the parser and changing the tokeniser forces you to edit all of these mock token streams. Worse, if you want to refactor the parser, you usually want to refactor the tokeniser to, so you end up wanting to change the mocks. This is just extra work. The Parser/Tokeniser pair should be a "unit" once the basic mechanics are up.

    So I think you should just write out real data. Once you have that safely hidden your persistent objects, those persistent objects will make great mocks.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  3. #3
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the link and the analysis.
    So I think you should just write out real data. Once you have that safely hidden your persistent objects, those persistent objects will make great mocks.
    I'm a newbie to unit test, I have problems to determine the borders of the "Units". In this case I understood that was the Mapper's responsibility execute the right SQL. If I write out the real data in the DB, How I validate it?, Doing a query? The coupling is still present!!. I expect that in a integration test and not in a unit test.
    Like you say in the other thread, this test was quick and easy to write, I don't fully understand how to test that the data was written correctly.

    After write this test I notice that the mapper don't have to execute the command, instead it have only to pull the right properties from the object, possibly construct a SQL command, and pass it to a kind of Unit of Work. After recollecting all the commands needed by the transaction the "Unit of Work thing" executes them.

    So, I refactor all the mappers test to this (except the queries that are executed immediately):
    PHP Code:
    function test_embedded() 

        
    $currecy = new currecy('USD''Dollar'); 
        
    $product = new product('753df''Monitor', new money($currecy35.5)); 
         
        
    $expected "INSERT INTO products (id_product, name, stock, amount, currency_simbol, currency_name) "
                    
    "VALUES ('753df', 'Monitor', 0, 35.50, 'USD', 'Dollar')"
        
         
        
    $sql $this->mapper->insert($product); 
        
    $this->assertSql($sql$expected);

    Not using the mocks but I still coupled to the command generated.

  4. #4
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by k7k0
    I'm a newbie to unit test, I have problems to determine the borders of the "Units".
    We all are. Only now are people starting to look at testing patterns.

    Quote Originally Posted by k7k0
    In this case I understood that was the Mapper's responsibility execute the right SQL. If I write out the real data in the DB, How I validate it?, Doing a query?
    I have spent some time writing and testing just such a layer recently (I am hoping to release it soon). After a couple of tests using mocks to bootstrap the process, the solution was to write objects out and read them back. No SQL appears in the tests at all, I've just tested the objects are persistent. I figure it is successful storage I am after and that I don't care about the form it takes.

    Quote Originally Posted by k7k0
    Like you say in the other thread, this test was quick and easy to write, I don't fully understand how to test that the data was written correctly.
    I definitely think reading it back is the only sure way.

    Quote Originally Posted by k7k0
    So, I refactor all the mappers test to this (except the queries that are executed immediately):
    How about making the query a SQL object rather than a SQL string? You could have methods like addConstraint() and setField() that you could expect on the mocks. Meanwhile the rendering to string form could happen in the SQL class tests themselves, keeping the string manipulation out of the main tests.

    I've used this trick a couple of times and it's a relatively painless way to keep DB dependencies out of the main code.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  5. #5
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Argentina
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After a couple of tests using mocks to bootstrap the process, the solution was to write objects out and read them back. No SQL appears in the tests at all, I've just tested the objects are persistent. I figure it is successful storage I am after and that I don't care about the form it takes.
    Why did I not think about that before? Using the mapper to write the objects and read them back it's a great test case!. I had made three separate test: one for reading, one for writings and another one for the reading/writing. But all of them are made using the mocks, now I can make a single one using the real DB.

    Thanks lastcraft.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •