TTTTTESTING RAW T-SQL QUERIES

Captain

What are my options for testing raw T-SQL queries? I want to pass the harness a query, and ID's and constraints to test against.

What do you guys like?

Captain

@Captain Giving this a quick ping and some more detail:

My job involves "translating" forms from Java to SSRS. Part of this is straight-forward, but there are no documented business rules anywhere.

I need a way to test the query behind my form/report. My hope is that when we find bugs (either because I did a mistake, or because a new example of what the report looks like comes up), then I can add a new test, fix it, and run all the tests again.

All my queries are reading from the DB -- I am only pulling data from the DB and comparing to known good values.

Gribnit

@Captain I like to use a simulated database that emulates T-SQL, something like h2, although I've never tried to use it for T-SQL.

EDIT: Given how hard it is in practice to make sure your test data are going to be stable in whatever DB you are testing against this may be a way to go, though seeding it with the known good test data in advance may feel like a bit of a circlejerk.

EDIT EDIT: Maybe you could get a test copy of the database stood up somewhere. You really can't use a live one, though, not even a dev one, b/c your tests' stability will then be only as good as that db's permanence.

Captain

@Gribnit Yeah, I do have a copy of the live database, and I have access to a read only mirror of production.

I don't mind the tests being somewhat impermanent, as there could be changes in other divisions that I'd like/need to know about in my reports anyway.

Honestly I think I want to test against live.RO or my test db. I'm taking a look at tSQLt, but that seems to have a different focus, and is definitely not suitable for live.RO.

Unperverted Vixen

@Captain If you're using SSRS, I assume you're using Visual Studio. What edition?

If it's Professional or Enterprise, SQL Server Unit Tests seem like they might fit the bill for you if there's existing data that you can use. (I've found having them create the data to be more trouble than it's worth in terms of making tests rerunnable.)

Captain

@Unperverted-Vixen I'm on VSPro2013 for now. Supposedly we're migrating to 2015 by 2020.

Looks interesting.

dcon

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

@Unperverted-Vixen I'm on VSPro2013 for now. Supposedly we're migrating to 2015 by 2020.

We were on 2010 until about 2 years ago. Thankfully we just jumped to 2017 at that time.

Captain

OK, the Sql Server Unit Testing thing by Microsoft is somewhat interesting, but I'm really not seeing how to hook into it to do what I want.

Indeed, you're apprently supposed to type the SQL to test "in" the design view, and there does not seem to be a way for me to "open" my .sql file to get it somewhere I can run it (other than copy/paste, but that defeats the point).

HMMMM

Unperverted Vixen

@Captain Is the query that you’re testing a sproc in the database? If not, sounds like you’re already copy-pasting (from RDL to this magic SQL file for testing)...?

If you have the query as a sproc, then in the unit test designer you’ll set up the calls to it and the expected responses for verification. Seems like this other file could then be disposed of.

If you really want to have it just run a script from a .sql file... if you set up a database unit test, you’ll see that it’s pulling the script it uses from a resx file. You should be able to edit the resx file or the generated test harness code (not sure which is safer) to use a .sql file on disk instead of the resource.

Polygeekery

Captain

@Polygeekery Woh thanks.

Polygeekery

@Captain you can thank blakey on Discord if you wish. He just accused me of threatening him (god knows why) so I won't help him anymore.

Captain

@Unperverted-Vixen said in TTTTTESTING RAW T-SQL QUERIES:

@Captain Is the query that you’re testing a sproc in the database? If not, sounds like you’re already copy-pasting (from RDL to this magic SQL file for testing)...?

No, the query really is JUST a select. And no, I import a .SQL file into my RDL. :-)

The thing is, I have to use this query in (potentially many) ssrs reports. So I need to make sure there is a unique representation of it to import into reports, tests, etc.

And I need to make sure the queries match the stuff that is already in production. So testing against live.RO is okay and probably a good idea. My ultimate goal is to make it REALLY easy to run my query and compare it against known-good results. Stuff like, "If I pass in key 1118374, (that triggers scenario B), so we expect a column Foo to contain..."

I can theoretically use C#, but... it's not ideal unless it's just really simple fragments to set up a new test. I like how the design view for a SQL Server Unit test looks and works, except that it won't seem to let me import a dang file!

@Polygeekery said in TTTTTESTING RAW T-SQL QUERIES:

@Captain you can thank blakey on Discord if you wish. He just accused me of threatening him (god knows why) so I won't help him anymore.

Yeah, I'll thank him too. But you coordinated, and it helped. :-)

Polygeekery

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

Yeah, I'll thank him too. But you coordinated, and it helped. :-)

No worries. As soon as I read it I realized it was a simple solution to your problem and I wanted to make sure he got credit.

I have no intention on making that mistake again.

Unperverted Vixen

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

And no, I import a .SQL file into my RDL. :-)

Wow. I... didn't even know that was possible. TIL. :)

Captain

@Unperverted-Vixen Yeah, when you make or edit a dataset, you can pick "From File".

Captain

OK, I got fed up with this, and after going to training yesterday, I hacked this together today:

using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using Microsoft.VisualStudio.TestTools.UnitTesting;


namespace Product.Forms.SQL
{
  public class SqlTest
  {
    public static string LoadSqlFile(string name)
    {

      string path =  Path.Combine( Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
                                 , @"sql\" +  name
                                 );

      return File.ReadAllText(path);
    }


    public static void UniqueByReportId(string sql, int key, IDictionary<string, string> expected)
    {
      using (var conn = new SqlConnection("anonymized connection string"))
      {
        conn.Open();

        var result = (IDictionary<string, object>) conn.Query(sql, new { ReportId = key } ).First();

        // algorithm I want:
        // loop through the expected dictionary
        //   make sure the expected matches the result
        //   skipping over any result keys we didn't put in `expected`


        foreach (string k in expected.Keys)
        {
           Assert.AreEqual(expected[k], (string) result[k]);
        }
        conn.Dispose();
      }
    }
  }
}

I know I will be making improvements to help my hacked together solution fit in our enterprisey environment.

This pretty much does everything I'd like. Call this function to construct a test, which pulls a query from a file, load it, run it, apply parameters, and compare to known good values. Awesome.

Random question: what happens if I tag UniqueByReportId with [TestMethod]?

I'm definitely going to have to set this up to take in JSON files in a directory.

Unperverted Vixen

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

Random question: what happens if I tag UniqueByReportId with [TestMethod]?

By itself? Probably nothing. The class also has to be tagged with [TestClass] before it'll get picked up.

If you add both attributes, I expect it to fail, either because it's a static method and/or because it has parameters.

I would expect you to have multiple (non-static) methods with no parameters, one per test case. Each would have the [TestMethod] attribute, and call UniqueByReportId with the parameters for that scenario.

(MSTest does support parameterized tests, but I don't know if that works with dictionaries. Even if it does, the attributes to define the test cases will be ugly. Having unique methods also gives you unique names in the error report for each case, which can be easier to track down failures.)

You need to call result[k].ToString(), rather than casting to string. And there's no reason to call conn.Dispose() inside its using statement.

Captain

@Unperverted-Vixen said in TTTTTESTING RAW T-SQL QUERIES:

I would expect you to have multiple (non-static) methods with no parameters, one per test case.

Yeah, that's how I was taught, but I think I'd have to get into metaprogramming to do it the easy way (where we have the other analysts write JSON instead of C#).

Edit: Also surprised "ToString()" even worked, considering that I asked Dapper to give me an object.

pie_flavor

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

conn.Dispose();

Auto-disposing is the point of a using statement; you should never need to call it yourself.

dkf

@pie_flavor said in TTTTTESTING RAW T-SQL QUERIES:

you should never need to call it yourself.

I could see calling it directly from inside another Dispose method, the idea being that that outer resource is owning the subresource.

pie_flavor

@dkf You should never need to call it yourself inside a using statement was my point.

Captain

OK, time for my daily C# question:

I got my UniqueByReportId test builder working. If you recall, it looped through the "expected" data (that I gave to the test) and the live data I just pulled, and compares them, ignoring fields that are not in the expected data.

That's pretty simple, so I factored that into its own method for reuse:

public static void Satisfies(IDictionary<string, object> result, IDictionary<string, string> expected )
{
  foreach (string k in expected.Keys)
  {
     Assert.AreEqual(expected[k], (string) result[k]);
  }
}

OK, but now I want to apply this method as a zip, like:

results.Zip(expected, ( r, e ) => Satisfies(r ,e));

But I'm getting a really stupid type error which I don't know how to fix. The error is:

Error 5 The type arguments for method 'System.Linq.Enumerable.Zip<TFirst,TSecond,TResult>(System.Collections.Generic.IEnumerable<TFirst>, System.Collections.Generic.IEnumerable<TSecond>, System.Func<TFirst,TSecond,TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

OK, but this is a void function. So what type do I set for TResult? void didn't work.

Edit: Stackoverflow says I'm dumb for expecting smart Haskell things to work in C# and that I need to unpack and repack my own objects at least to C# 7.0.

pie_flavor

@Captain What do you mean apply as a zip? Unless zipping means something I don't know about, you can't zip against a void method because there is nothing to zip with. {a, b} zipped with {b, c} produces {(a, c), (b, d)} - where does void apply there?
If you want to apply Satisfies to each element of results, use a foreach statement or the ForEach function. If you want to zip it with a boolean result of whether or not the results are satisfied, then make the function return bool instead of void.

Captain

@pie_flavor Haskell lets me do:

f :: a -> b -> m () -- note `f` is void "because" it always returns the unique value `()` -- i.e., the output yields no information.
f a b =  do
  putStrLn $  (show a) ++ " " ++  (show b)
  return () -- actually unnecessary, because putStrLn already returns `()`

zipWith f [1..5] ['a'..'e']

which gets evaluated to [(), (), (), (), ()] while still doing the printing (the side effect). It's useful PRECISELY for this case, so I don't need to bend over backwards to apply the Zip idiom.

So I'd write my function as

zipWith satisfies results expected

(And the "real" zip is the function which maps two lists to a list of pairs)

Instead of doing it the nice way, I ended up having to write a bunch of noise to get the C# to work. It came out like (don't mind the syntax errors, this is from memory without a compiler)

Enumerate.Zip(results, expected, (r, e) => new { Results = r, Expected e })
         .Select(o => Assert.AreEquals(o.Results, o.Expected));

This is actually a little nicer than what I wrote at work. But the anonymous object is there only to give names to things that don't need them.

pie_flavor

@Captain Actually, we have value tuples and patterns now, so you can say

xs.Zip(ys, (x, y) => (x, y)).Select((x, y) => SomeFunction(x, y))

You can also do:

from x in xs from y in ys select SomeFunction(x, y)

But the takeaway here is that void is not a singleton type but the lack of any type. You can emulate Unit with an empty tuple (()), but a void function can't be used in a parameter expecting a value. And in fact IEnumerable is missing a ForEach function which would accomplish this specifically because this is not Haskell and your actual enumeration of elements should look like one.

Zecc

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

Path.Combine( Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
            , @"sql\" +  name
            );

Path.Combine() has an overload accepting three arguments, just FYI.

Zecc

Thanks for making me look at Path.Combine()'s documentation. TIL:

var pathIThoughtWasSafe = Path.Combine(@"C:\My Application's Sandbox",   
                                       @"C:\Windows\System32");
// "However, if an argument other than the first contains a rooted path,
// any previous path components are ignored, and the returned string
// begins with that rooted path component"

pie_flavor

@Zecc I mean, it's not like you couldn't force that anyway with ... Always check if Path.GetFullPath(path2).StartsWith(Path.GetFullPath(path1)).

Zecc

@pie_flavor

Thanks for that. I'm a .Net noob myself. I was already doing that sort of check, but in a much more complicated way:

static bool IsFileInDirectory(string filePath, string dirPath)
    {
        var d1 = new DirectoryInfo(new FileInfo(filePath).DirectoryName + "\\");
        var d2 = new DirectoryInfo(dirPath + "\\");
        return d1.FullName == d2.FullName;
    }

Still, I'd change that to Path.GetFullPath(path2).StartsWith(Path.GetFullPath(path1+@"\")) just to make it more "slash-variation resistant".

pie_flavor

@Zecc But what if the full path already ends in a trailing slash?

Zecc

@pie_flavor

string path = Path.GetFullPath(@"C:\\Users\\\\\\\\\\\\");
// Value of path is "C:\Users\"

pie_flavor

@Zecc Right. So, when you say + @"\", you ensure that it ends in two backslashes, which the file path you're comparing it against does not contain.

Zecc

@pie_flavor I see the problem. I was thinking of path2 as being a path to a file, not a directory.

Captain

Thanks for all the input everybody. I have incorporated fixes. I'll post more code in a little while. Today, I write T4 templates so I can slurp up my test definitions and give each test a unique name.

Captain

@pie_flavor and your actual enumeration of elements should look like one.

What does that mean? I'm not sure why [() .. ()] isn't an enumeration. It is literally an enumeration on an enumerable type.

Captain

Turns out string processing and templating is a huge pain in the ass without reliable functional idioms. Who knew? :-(

I've had to parse JSON to write a textual representation of a dictionary in C# source code. Lots of fun without things like Zips, when I'm having to cast and convert every flipping json object in a T4 template.

Zecc

@pie_flavor said in TTTTTESTING RAW T-SQL QUERIES:

@Zecc Right. So, when you say + @"\", you ensure that it ends in two backslashes, which the file path you're comparing it against does not contain.

Wait, what?

Path.GetFullPath(path2).StartsWith(Path.GetFullPath(path1+@"\"))
                                   \__________________________/
                                         ↓
             Will end with a single backslash regardless of
                  whether path1 had one originally

Zecc

@Captain Does this help?

LINQ to JSON

Or this?

Querying JSON with SelectToken

Captain

@Zecc I went with another route. The biggest problem I had is how ugly T4 is, and how VS2013 doesn't do any syntax highlighting. And how the error messages suck. So my normal approach of bashing it until it works is a lot harder.

pie_flavor

@Zecc Oh, my brain misplaced the parenthesis. Still, I've no idea why you think you need the + @"\".

Captain

Really love it when this so-called type-safe language shits its pants after building.

Also love it how every library seems to define its own dictionary type and I can't seem to convert between them.

Captain

I have a method which accepts a List<IDictionary<string, string>> as an argument.

public static void RowsByReportId(string sql, int key, List< IDictionary<string,string> > expected)
{
  using (var conn = new SqlConnection("blah blah blah"))
  {
    conn.Open();

    var results = conn.Query(sql, new { ReportId = key });


    var items = Enumerable.Zip(results, expected, (r, e) => new { Result = r, Expected = e });
    foreach (var item in items)
    {
       Satisfies(item.Result, item.Expected);
    }
  }
}

But it fails when I pass in a List<Dictionary<string, string>>. That's crazy, right?

pie_flavor

@Captain No, not really. If you are holding onto a List<Dictionary<,>>, only you think it's a List<IDictionary<,>>, what happens when you try to insert something that's an IDictionary<,> but isn't a Dictionary<,>? However if it accepts IEnumerable<IDictionary<,>> that works fine because IEnumerable<> is covariant (since it's read-only).

Captain

@pie_flavor said in TTTTTESTING RAW T-SQL QUERIES:

that works fine because IEnumerable<> is covariant (since it's read-only).

Yep, having it accept an IEnumerable fixed it.

Captain

@pie_flavor said in TTTTTESTING RAW T-SQL QUERIES:

@Captain No, not really. If you are holding onto a List<Dictionary<,>>, only you think it's a List<IDictionary<,>>, what happens when you try to insert something that's an IDictionary<,> but isn't a Dictionary<,>?

Well, I would imagine I'd get a type error at the site where I tried that insert.

pie_flavor

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

@pie_flavor said in TTTTTESTING RAW T-SQL QUERIES:

@Captain No, not really. If you are holding onto a List<Dictionary<,>>, only you think it's a List<IDictionary<,>>, what happens when you try to insert something that's an IDictionary<,> but isn't a Dictionary<,>?

Well, I would imagine I'd get a type error at the site where I tried that insert.

Runtime type errors? What year is it?

Captain

You can have static errors at the site...

As it stands, it "seems" like a Dictionary implements the IDictionary interface, so it should just be allowed to pass.

In Haskell, we can do...

class IDict x where
   iDictMethod :: x -> String

genericMethod :: IDict x => x -> Whatever
genericMethod = undefined -- some algorithm that uses `iDictMethod`

The point being, that genericMethod will work on any type that implements IDict.

It's really painful to go back.

Tsaukpaetra

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

how ugly T4 is,

I fear from what I've heard about T4...

pie_flavor

@Captain said in TTTTTESTING RAW T-SQL QUERIES:

You can have static errors at the site...

What, a static error when you use a setter method? How? If you say List and somebody passes MyList which extends List, it's not like your library knows that it'll get passed that.

As it stands, it "seems" like a Dictionary implements the IDictionary interface, so it should just be allowed to pass.

It does! Which is why IEnumerable<IDictionary<,>> can absolutely be assigned a List<Dictionary<,>>, because IEnumerable<> is covariant. However, List<> is not covariant because, again, setters. But IReadOnlyList<> is covariant. So your method could also instead be declared as taking IReadOnlyList<IDictionary<,>> and it would work. But from here it looks like you don't actually need indexed access, just iteration, and so you should take an IEnumerable<>.

In Haskell, we can do...
class IDict x where
   iDictMethod :: x -> String

genericMethod :: IDict x => x -> Whatever
genericMethod = undefined -- some algorithm that uses `iDictMethod`
The point being, that genericMethod will work on any type that implements IDict.

It's really painful to go back.

It's not the dictionary. The dictionary is fine. IDictionary<string> d = new Dictionary<string>() works perfectly fine. It's the list that's the problem.