SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    simpletest vs. database

    I was wondering how you people get around testing code, witch interacts with the database.

    I can basically see two methods for doing this :
    Either I mock the database, and check that the SQL sent to the mock matches what I expect. Or I use a test-database, and have a seperate connection to the database, witch I use to doublecheck that the data has actually been written as it should. I just don't think either way sounds very appealing to me, since it adds a lot of dependency to the tests, witch in return could be failing. It also makes my tests dependent on the underlying storage-engine, since SQL varies slightly between vendors.

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both!

    I use mock objects to simulate the database connection, and check for both the correct SQL and the correct bind values.

    In development, you can flushed the objects and repopulate between test, so you know you have a known stable configuration and you are executing an end-to-end transaction.

    I also sometimes will perform read-only queries agaist production data, and use this as a validations check that the system is up and alive.

    I usually place the first directly into the unit test for the code I am working on, and the latter two in a test case with "Integration" in the name.

    HTH
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd probably look at string tests first but drop it quickly if it's getting at all awkward in favour of a real db created and deleted by the test. Making assertions about the SQL string can be tricky, as you've probably found. To mysql "SELECT * WHERE foo = 1 and bar = 2" and "SELECT * WHERE bar = 2 and foo = 1 " are the same query but they're not the same in a string comparison. There's a hacked-together assertIdenticalArrayValues method on the SimpleTest mailing list which might help. It performs an order-independent comparison of array elements.

    For testing against a db, Marcus posted this class a while back, which might be useful to you:

    PHP Code:
      class FullTextTestCase extends UnitTestCase 
      
    {
          function 
    FullTextTestCase() 
          {
              
    $this->UnitTestCase();
              
    $this->_connection mysql_connect('localhost''root''');
              
    $this->_execute('USE playpen');
          }
          
          
    /*
              param $row (array) $row[$col name] = $col_value
          */
          
    function setData($row)
          {
              
    $create_table 'create table test (';
              
    $create_table .= implode(' text, 'array_keys($row));
              
    $create_table .= ' text)';
              
    $this->_execute($create_table);
              
    $insert 'insert into test (';
              
    $insert .= implode(', 'array_keys($row));
              
    $insert .= ") values ('";
              
    $insert .= implode("', '"array_values($row));
              
    $insert .= "')";
              
    $this->_execute($insert);
          }
          
          function 
    clearData() 
          {
              
    $this->_execute('drop table if exists test');
          }
          
          function 
    _execute($sql
          {
              
    $result mysql_query($sql$this->_connection);
              if (! 
    $result
              {
                  
    trigger_error(mysql_error($this->_connection));
              }
              return 
    $result;
          }
          
          function 
    assertFound($clause$message '%s'
          {
              
    $message sprintf($message'Should find at least one row');
              
    $result $this->_execute("select * from test where $clause");
              
    $this->assertTrue(mysql_fetch_assoc($result), $message);
              
    mysql_free_result($result);
          }
          
          function 
    assertNotFound($clause$message '%s'
          {
              
    $message sprintf($message'Should not find any rows');
              
    $result $this->_execute("select * from test where $clause");
              
    $this->assertFalse(mysql_fetch_assoc($result), $message);
              
    mysql_free_result($result);
          }
      } 
    If you need the SQL to work across different db's I guess you'll have to test against each db you want to support. Probably the majority of your SQL will be simple enough not to require this.

  4. #4
    SitePoint Guru BerislavLopac's Avatar
    Join Date
    Sep 2004
    Location
    Zagreb, Croatia
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by McGruff
    If you need the SQL to work across different db's I guess you'll have to test against each db you want to support.
    Or just use ADOdb and test against its classes.

  5. #5
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by BerislavLopac
    Or just use ADOdb and test against its classes.
    Carefully here. I use ADOdb every day as a database access layer, but who is me if I try to use my beloved $conn->executeCursor() method on a MySQL database. Oracle stored proceedures aside, ADOdb can't protect you from using server specific SQL in you statements, so testing against every target db is probably a reasonable measure.

  6. #6
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is true, for individual databases.

    What PHP actually needs is one query langauge, and individual drivers on a per database server level that take the SQL and query the database based on that commonality. Think this point has been discussed before though?

    Back on topic though

  7. #7
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies. It seems like there isn't a simple answer to this problem, witch I also foresaw. Currently I'm testing against a test-database, witch I flush between tests, so I can make asserions about what it will contain. My connection-layer is linked to a logger, witch I check from my test-scripts. And for some tests I will doublecheck by pulling actual data from the db. So that's more or less the same thing you are working by ?

    In order to make more protable tests, I would need some sort of database-abstraction-layer (as Linigston suggests), but as I see it, it's a checken-and-egg situation. Since you would depend on this layer to be coherent - so how would you get around testing that ?

  8. #8
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Someone might suggest something like MetaStorage (or is it MetaBase??) but you are correct in thinking that it'd be unwielding to unit test, in an (semi)automated manner at least.

    Would be interesting in knowing what the author of MetaStorage/MetaBase has to say about this (Meta*) in regards to unit testing now.

  9. #9
    ********* 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 kyberfabrikken
    I would need some sort of database-abstraction-layer (as Linigston suggests), but as I see it, it's a checken-and-egg situation. Since you would depend on this layer to be coherent - so how would you get around testing that ?
    Actually an O/R style layer actually helps here, either DataMapper or DataAccessor style, if it's code generated (from XML?). That way you only have to test a few example cases using real databases. When you code generate the objects (RowDataGateways or whatever) you can mock these because they will have access methods rather than SQL. In fact they make an ideal mocking point.

    The tests posted with the Changes library (sorry, lost the URL again) were an (incomplete) example of this. Once you have thoroughly tested the mapping layer, you can then trust that it works for your specific DAOs and move on. Intense testing of a small critical component, rather than general testing of data access across the whole app.

    Of course you have to have an O/R layer to begin with

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

  10. #10
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    Of course you have to have an O/R layer to begin with
    Yes, well it's a datamapper that I'm testing. So your points are perfectly valid.


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
  •