{"id":4036,"date":"2011-10-20T19:55:04","date_gmt":"2011-10-20T17:55:04","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4036"},"modified":"2011-10-20T19:55:04","modified_gmt":"2011-10-20T17:55:04","slug":"test-driven-sql-development","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/test-driven-sql-development","title":{"rendered":"Test-driven SQL development"},"content":{"rendered":"<p>I&#8217;m having a lot of fun writing <a href=\"http:\/\/code.google.com\/p\/common-schema\/\" rel=\"nofollow\">common_schema<\/a>, an SQL project which includes views, tables and stored routines.<\/p>\n<p>As the project grows (and it&#8217;s taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I&#8217;ve turned the development on <em>common_schema<\/em> to be <em>test driven<\/em>.<\/p>\n<p>Now, just how do you test drive an SQL project?<\/p>\n<p>Well, much like the way you test any other project in your favorite programming language. If its functions you&#8217;re testing, that&#8217;s all too familiar: functions get some input and provide some output. Hmmm, they might be changing SQL data during that time. With procedures it&#8217;s slightly more complex, since they do not directly return output but result sets.<\/p>\n<p>Here&#8217;s the testing scheme I use:<!--more--><\/p>\n<ul>\n<li>Tests are divided to families. For example, there is a family of tests for the <em>eval()<\/em> function.<\/li>\n<li>Each test in a family is responsible for checking the simplest, most &#8220;atomic&#8221; issue. This means many small tests.<\/li>\n<li>Each test can have a <em>&#8220;pre-test&#8221;<\/em> step, which prepares the ground (for example, create a table and populate it)<\/li>\n<li>Likewise, a test can have a <em>&#8220;post-test&#8221;<\/em> step, which is typically just cleanup code (since the test is already complete by the time the post step is invoked).<\/li>\n<li>Each test is an SQL file: a set of commands to be executed.<\/li>\n<li>A test may have an <em>&#8220;expected output&#8221;<\/em> file.<\/li>\n<\/ul>\n<ul>\n<li>If no explicit <em>expected<\/em> exists, the test is expected to return <strong>&#8220;1&#8221;<\/strong> (just as the most basic <em>JUnit<\/em> test assumes an &#8220;assert true&#8221;)<\/li>\n<li>A test family may also have <em>pre-<\/em> and <em>post-<\/em> steps.<\/li>\n<li>Any failure in any step fails the entire process. Failures may include:\n<ul>\n<li>Failure to prepare the grounds for a test or family of tests<\/li>\n<li>Failure in executing the test<\/li>\n<li>Mismatch between test&#8217;s output and expected result.<\/li>\n<li>Failure in executing the <em>post-<\/em> step (may indicate yet invalid test result not intercepted by the test)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4>An example<\/h4>\n<p>The following image presents a single test family: the <em>eval<\/em> family, testing the <em>eval()<\/em> routine.<\/p>\n<blockquote><p><a href=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2011\/10\/test-driven-sql-development-01.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-4205 alignnone\" title=\"test-driven-sql-development-01\" src=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2011\/10\/test-driven-sql-development-01.png\" alt=\"Test driven SQL development - sample\" width=\"198\" height=\"258\" \/><\/a><\/p><\/blockquote>\n<ul>\n<li>In this family, there are two tests.<\/li>\n<li>In both tests, we have a <em>pre-test<\/em> step, and a test.<\/li>\n<li>We have no <em>post-test<\/em> here.<\/li>\n<li>Nor do we have an <em>expected-output<\/em> sample, which means the tests expect to return with <strong>&#8220;1&#8221;<\/strong>.<\/li>\n<\/ul>\n<h4>Implementation<\/h4>\n<p>But how are tests conducted? Via <em>mysql<\/em>, of course. While tests are plain SQL text file, they are being executed against a running MySQL server using the <em>mysql<\/em> client. It is given the test files as input, and its output is directed to file as well.<\/p>\n<p>This makes it very easy to code the test using a simple shell script. It takes a small measure of file iteration, process invocation, exit code check, and <em>diff<\/em> execution.<\/p>\n<p>For example, to test <em>eval()<\/em>&#8216;s <strong>01<\/strong> test, we first execute <em>mysql<\/em> with <strong>01\/pre.sql<\/strong> as input. Assuming success, we execute <em>mysql<\/em> again, this time with <strong>01\/test.sql<\/strong>. We capture the output of this execution, and compare it with <em>expected-output<\/em>, or with <strong>&#8220;1&#8221;<\/strong> when no <em>expected-output<\/em> specified.<\/p>\n<h4>Tests pass, or no code!<\/h4>\n<p>Some <strong>12<\/strong> years ago, I worked with a less-known version system called <a href=\"http:\/\/aegis.sourceforge.net\/documents.html\">aegis<\/a>. The thing I remember most from <em>aegis<\/em> was that it had a good tests infrastructure. Long before &#8220;test-driven development&#8221; was coined, or was even commonly practiced, <em>aegis<\/em> supported tests right into your version control. &#8220;Right into&#8221;, in the sense that <em>you could not merge your code back to the baseline<\/em> if it didn&#8217;t pass all of the tests.<\/p>\n<p>I work with SVN for <em>common_schema<\/em>, and I do not know of such an option in SVN. But I also use <em>ant<\/em> to build this project, and the dependency is clear there: <strong>ant dist<\/strong>, my target which creates the distribution files, is dependent on <strong>ant test<\/strong>, the target which works out the tests.<\/p>\n<p>That is, you cannot generate the distribution files when tests fail.<\/p>\n<h4>Further notes<\/h4>\n<p>Since I&#8217;m now retroactively patching tests for already existing functionality, calling it <em>test-driven<\/em> development is an overstatement; nevertheless new tests are already proving invaluable when I keep changing and improving existing code. Suddenly dependent functionality no longer works as expected. What fun!<\/p>\n<p><a href=\"http:\/\/code.google.com\/p\/common-schema\/source\/browse\/trunk\/common_schema\/tests\/test_all.sh\">The code<\/a> for the testing suite is actually much shorter than this blog post.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m having a lot of fun writing common_schema, an SQL project which includes views, tables and stored routines. As the project grows (and it&#8217;s taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I&#8217;ve turned the development [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[53,5],"tags":[72,67,120,119,50,59],"class_list":["post-4036","post","type-post","status-publish","format-standard","hentry","category-development","category-mysql","tag-ant","tag-common_schema","tag-development","tag-linux","tag-scripts","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-136","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4036","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4036"}],"version-history":[{"count":39,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4036\/revisions"}],"predecessor-version":[{"id":4235,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4036\/revisions\/4235"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4036"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4036"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4036"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}