SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you TDD database activity?

    So I'm starting to get a handle on TDD and how I can use it, and I have one nagging question....how do you make it work with database interaction?? I know with SimpleTest you have the setUp and tearDown functions, but what's a best practice for using them?

    For example, I have a User class. I want to store the User in the database so I create a test and then proceed to create a save method that does the database insert. Now my database has the record, and I need to clean up after myself.

    What's the general thought here? Do you manually remove the records created from the database via tearDown? Or maybe you have a seperate test database that mimics your database scheme and then you empty out the tables on setUp? Maybe you go as far as creating the tables on setUp via some .sql or scheme file and emptying the database on tearDown? If so, do you have a seperate config.testing.php that you load your database info with or do you overwrite it some other way?

    This is one part of TDD I haven't seen much discussed and am really looking for some good "best practice" info.

    Thanks!

  2. #2
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am just getting started and what I've done so far is to add an array of sql strings in the setup and add the table and insert a pair of records.

    I then drop the tables in a single sql statement in the teardown. This way the db is the same on each testing function.

    I then go and perform my tests in an order. I make sure I can load a single or list of records first. Having two records in the db already I can test that I get the right counts and such with the hard coded known data.

    Then the later tests are the ones like saving a new record so that when I get to there I know the loading functions work and I can use them a bit in the tests for saving. (i.e. I can save a new records, get all the records in the db and make sure I now have 3 records returned)

    I wanted to try to remove the need to use one function I am testing from another, but I can't think of it so I find that if I put them in order it works out ok.

    HTH
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  3. #3
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.sitepoint.com/forums/show...46&postcount=3

    I found this a good suggestion - though haven't tried it yet.

  4. #4
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also see other posts in the parent topic for the above.

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Norway - Oslo
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tests of database content is something i still havent worked out anything usefull for.
    Keeping the schema definition in the test is to fragile, someone will forget to update it there.

    I imagine a decent way would be to get the schema from the live table, create that table under a new temporary name, in a new random database (dont want to temper with live stuff), and run the tests there. Then tear it all down after.

    If it only wasnt at the high initiation cost to try it. Last time i was thinking about it i found some faults in that plan aswell, i just cant remember them now.

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Findus
    I imagine a decent way would be to get the schema from the live table, create that table under a new temporary name, in a new random database (dont want to temper with live stuff), and run the tests there. Then tear it all down after.
    That's an interesting thought....to expand on it and maybe reduce overhead. Lets say you have your production DB and your test DB. When you do setup of the tests, you dump the production DB scheme and hash it. Compare that to the scheme of the test DB. If they match, proceede, otherwise make them match (dump all tables and import the production dump). On teardown, rather then trash all the tables, just truncate them, leaving the structure in place to be compared on future tests.

    Maybe..maybe..?

  7. #7
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by foofoonet
    http://www.sitepoint.com/forums/show...46&postcount=3

    I found this a good suggestion - though haven't tried it yet.
    Off Topic:

    sorry I didnt realise linking to a post isolated it, I thought it worked like a bookmark, thanks for putting me right MgCruff
    Last edited by foofoonet; Apr 12, 2006 at 07:58.

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

    For conditions that are difficult to test, such as DB failures, you will need mock objects. For anything involving SQL, you are best off using a real DB. Once you get above the SQL layer, you can use mocks again.

    On development boxes we use a real database most of the time. The setUp usually drops the database and rebuilds it, to make sure tests start clean. Obviously this is faster if you can secretly switch to an in memory database, but you may have trouble testing transactions.

    So that tests can run on live servers, we have a second identical database. During rollout the tests run on the shadow DB until passed, then it switches to the live one.

    I find half measures don't really work well with testing. Yo get much cleaner tests if you can start with a bare set up.

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

  9. #9
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    The setUp usually drops the database and rebuilds it, to make sure tests start clean.
    What's the best way of doing this, then? Do you read the needed sql commands from an extenal file, or are they hard coded into your tests? What's the best way of executing a bunch of queries contained in an external file, anyway?

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A SimpleTest UnitTestCase extension supporting database fixtures may be of interest. It was posted to the SimpleTest support mailing list a few weeks ago.

  11. #11
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > Do you read the needed sql commands from an extenal file, or are they hard coded into your tests?

    I just import an sql dump via setup and the via teardown, you drop the whole lot

    > The setUp usually drops the database and rebuilds it,...

    Yes... But with the sql dump, the database server does this for you since it's already declared within the dump it's self

    Maybe this is what you are talking about yourself huh?
    Last edited by Dr Livingston; Apr 12, 2006 at 14:32. Reason: ...

  12. #12
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston
    I just import an sql dump via setup and the via teardown, you drop the whole lot
    so what's the best way of doing that, using a php function to read in the sql dump and feed the commands into the database server one by one? Or simply exec'ing the mysql command line?

  13. #13
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a simple sample from one of my tests for a login class:

    PHP Code:
     function setUp() {
             
    $dbconn dataSourceInstance::getConnection();
     
             
    $sql = array();
     
             
    // create the database needed for testing permissions
             
    $sql[] = "CREATE TABLE `login` (
                   `Username` varchar(15) NOT NULL default '',
                   `Password` varchar(32) NOT NULL default '',
                   `Email` varchar(50) NOT NULL default '',
                   PRIMARY KEY  (`Username`)
                 ) ENGINE=MyISAM"
    ;
     
             
    $sql[] = "INSERT INTO `login`
                     ( `Username` , `Password` , `Email` )
                     VALUES (
                     'testuser', 'testing, 'testuser@msufame.msu.edu'
                     ), (
                     'testuser2', 'testing', 'testuser2@msufame.msu.edu'
                     );"
    ;
     
             
    $sql[] = "CREATE TABLE `login_permissions` (
                       `PermissionID` bigint(20) unsigned NOT NULL auto_increment,
                       `Username` varchar(50) NOT NULL default '',
                       `Permission` varchar(20) NOT NULL default '',
                       PRIMARY KEY  (`PermissionID`)
                     ) ENGINE=MyISAM"
    ;
     
             
    $sql[] = "INSERT INTO `login_permissions`
                         ( `PermissionID` , `Username` , `Permission` )
                         VALUES (
                                 '', 'testuser', 'Admin'
                             ), (
                                 '', 'testuser', 'SuperAdmin'
                             ), (
                                 '', 'testuser2', 'SuperAdmin'
                             ), (
                                 '', 'testuser2', 'Research'
                             );"
    ;
     
             
    $sql[] = "CREATE TABLE `login_permission_list` (
                       `Permission` varchar(30) NOT NULL default '',
                       PRIMARY KEY  (`Permission`)
                     ) ENGINE=MyISAM"
    ;
     
             
    $sql[] = "INSERT INTO `login_permission_list` VALUES ('Admin');";
             
    $sql[] = "INSERT INTO `login_permission_list` VALUES ('SuperAdmin');";
             
    $sql[] = "INSERT INTO `login_permission_list` VALUES ('Research');";
     
             foreach (
    $sql as $query) {
                 
    $res $dbconn->Execute($query);
             }
         }
     
         function 
    tearDown() {
             
    $dbconn dataSourceInstance::getConnection();
     
             
    $sql = array();
             
    $sql[] = "DROP TABLE `login_permissions`";
             
    $sql[] = "DROP TABLE `login`";
             
    $sql[] = "DROP TABLE `login_permission_list`";
     
             foreach (
    $sql as $query) {
                 
    $res $dbconn->Execute($query);
             }
         } 
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  14. #14
    SitePoint Zealot Serberus's Avatar
    Join Date
    Oct 2005
    Location
    Herts, UK
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jason E Sweat's book "php|architect's Guide to PHP Design Patterns" is strongly orientated around TDD and has some good examples of this in the chapters on the Active Record, Table Gateway and Data Mapper patterns.

    No I don't get kick backs

  15. #15
    SitePoint Enthusiast silicate's Avatar
    Join Date
    Nov 2004
    Location
    Toronto
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey there,

    Personally I don't test my individual queries against the database. I run one query that checks the fields in the database against my expectations. If those fields exists then the table itself should be fine.

    After that I test purely in mocks. You don't need to see the query execute successfully against the actual database, you can just make sure that the SQL statements are generated. And even then that would only really be in tests of a query generator. Otherwise I assume that everything is working based on my tests of these lower-level components and run tests like this:
    PHP Code:
    function TestSomeDBInteraction () {
      
    $db = new MockDatabaseConnection();

      
    $TestData = array (
        
    'id'=>1,
        
    'name'=>'Test my Unit'
      
    );

      
    $db->expectOnce('getArray');
      
    $db->setReturnValue('getArray'$TestData);
      
    $o->SomeModel($db);
      
    $Result $o->getFoo();

      
    $this->assertEqual($Result$TestData);

    // end TestSomeDBInteraction 
    That's it. I guess I do it like this is because I write my tests in the database command line client (ie mysql or psql).

    Later,

    Matthew.

  16. #16
    SitePoint Guru OfficeOfTheLaw's Avatar
    Join Date
    Apr 2004
    Location
    Quincy
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just as an aside, we use PLUnit for unit testing oracle procedures at work.

    James Carr, Software Engineer


    assertEquals(newXPJob, you.ask(officeOfTheLaw));


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
  •