title: automating Cucumber test scenarios for MySQL
body: <p>I've built an important MySQL database, with a lot of view, triggers, functions and procedures.</p>
  
  <p>It's very hard to test, and to not forget anything, so, I've written Cucumber scenarios for all of the features of my DB (Insert, Select, etc., request on functions an procedures etc., and views)</p>
  
  <p>This help us a lot when we test the behavior of all this, and even before writing view and other code, it's very helpful to determinate want we really want to do.</p>
  
  <p>My problem is: after writing Cucumber features, we all test by hand in a MySQL Shell.</p>
  
  <p>I'm new in BDD/TDD and Agile methods, but I've done some search to know how to make some automation, but found nothing very interesting for my case.</p>
  
  <p>Is there somebody who can provide some interesting way to create automation for this?</p>
  
  <p>I don't know Ruby, but by example, is it possible to use RSPec directly with MySQL (with some examples)?</p>
  
  <p>Or in another language, or any solution you can think of!</p>
  
  <p>Thanks in advance!</p>
  
  <p><strong>[EDIT]</strong></p>
  
  <hr>
  
  <p>If found some interesting things with RSpec and MySQL:</p>
  
  <p><a href="http://morethanseven.net/2010/03/07/Mysql-support-for-cucumber-nagios.html" rel="noreferrer">Mysql Support For Cucumber Nagios</a></p>
  
  <p><a href="https://github.com/garethr/cucumber-nagios/blob/master/lib/generators/project/features/steps/mysql_steps.rb" rel="noreferrer">mysql_steps.rb</a></p>
  
  <hr>
  
  <p>My problem is: I don't have any knoledge with Ruby, RSPec, etc.</p>
  
  <p>I'm working on it with the excellent "Pick Axe" book, and RSPec book from PragProg</p>
  
  <p>But I will be very grateful for a little example of of RSpec steps given the code below:</p>
  
  <hr>
  
  <p>The MySQL Procedure</p>
  
  <pre><code>DELIMITER $$
  
  CREATE PROCEDURE `prc_liste_motif` (
      IN texte TEXT,
      IN motif VARCHAR(255),
      OUT nb_motif INT(9),
      OUT positions TEXT)
  BEGIN
      DECLARE ER_SYNTAXE CONDITION FOR SQLSTATE '45000';
      DECLARE sousChaine TEXT;
      DECLARE positionActuelle INT(9) DEFAULT 1;
      DECLARE i INT(9) DEFAULT 1;
  
      IF
          LENGTH(motif) &gt; LENGTH(texte)
      THEN
          SIGNAL ER_SYNTAXE
              SET MESSAGE_TEXT =
                'Bad Request: Le motif est plus long que le texte.',
                MYSQL_ERRNO = 400;
      END IF;
  
      SET positions = '';
      SET nb_motif = 0;
  
      REPEAT
  
          SET sousChaine = SUBSTRING_INDEX(texte, motif, i);
  
          SET positionActuelle = LENGTH(sousChaine) + 1;
  
          IF
            positionActuelle &lt; LENGTH(texte) + 1
          THEN
  
              IF
                LENGTH(positions) &gt; 0
              THEN
                  SET positions = CONCAT(positions, ',');
              END IF;
  
              SET positions = CONCAT(positions, positionActuelle);
  
              SET nb_motif = nb_motif + 1;
  
          END IF;
  
          SET i = i + 1;
  
      UNTIL LENGTH(sousChaine) &gt;= LENGTH(texte)
      END REPEAT;
  
  END$$
  </code></pre>
  
  <hr>
  
  <p>The Cucumber feature:</p>
  
  <pre><code>Feature: Procedure prc_liste_motif
    In order to precess a string according to a given unit
    I want to know the number of units present in the chain and their positions
    Knowing that the index starts at 1
  
    Background: the database mydatabase in our SGBDR server
      Given I have a MySQL server on 192.168.0.200
      And I use the username root
      And I use the password xfe356
      And I use the database mydatabase
  
    Scenario Outline: Using the procedure with good values in parameters
      Given I have a procedure prc_liste_motif
      And I have entered &lt;texte&gt; for the first parameter
      And I have entered &lt;motif&gt; for the second parameter
      And I have entered &lt;nb_motif&gt; for the third parameter
      And I have entered &lt;positions&gt; for the fourth parameter
      When I call prc_liste_motif
      Then I should have &lt;out_nb_motif&gt; instead of &lt;nb_motif&gt;
      Then I should have &lt;out_positions&gt; instead of &lt;positions&gt;
  
      Exemples:
        | texte         | motif | nb_motif | positions | out_nb_motif | out_positions |
        | Le beau chien | e     |          |           | 3            | 2,5,12        |
        | Allo          | ll    |          |           | 1            | 2             |
        | Allo          | w     |          |           | 0            |               |
  </code></pre>
  
  <hr>
  
  <p>An exemple of passed test by hand in MySQL:</p>
  
  <pre><code>$ mysql -h 192.168.0.200 -u root -p xfe356
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 1
  Server version: 5.5.9 MySQL Community Server (GPL)
  
  Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
  
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  mysql&gt; USE mydatabase
  Database changed
  mysql&gt; SET @texte = 'Le beau chien';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SET @motif = 'e';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SET @nb_motif = NULL;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SET @positions = NULL;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SET @out_nb_motif = 3;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SET @out_positions = '2,5,12';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; CALL prc_liste_motif(@texte, @motif, @nb_motif, @positions);
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SELECT @nb_motif = @out_nb_motif AND @positions = @out_positions;
  +-----------------------------------------------------------+
  | @nb_motif = @out_nb_motif AND @positions = @out_positions |
  +-----------------------------------------------------------+
  |                                                         1 |
  +-----------------------------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <hr>
  
  <p>thanks in advance for your help !</p>