Test names of DBs/Tables/Views/Functions/SPs/Columns with RegEx

[FuegoArtificial]

Hi Seddryck,

I would like to ask for a feature: Testing of name patterns of DBs/tables/views/functions/stored procedures/columns with regular expressions. One approach could be to enable the "matchpattern"-tag to assert it against a structure. However, there might be more viable approaches.

Best regards, Tilo

P.S. It's useful to easily enforce naming conventions throughout a project.

[Seddryck]

Shouldn't be too difficult to implement in NBi but I'd go for SSDT and the code analysis feature to implement this on my own projects.

[FuegoArtificial]

It would be great to have such integration tests in one spot (NBi), just as the name pattern-tests for SSAS, and not in different kinds of projects (as it was without NBi before...). It would be easy to migrate between projects and have the same way of reporting (the results), too :)

Additional thought: If it's just testing of strings returned as cell-information from DMVs or from xpath-queries against reporting services-RDL-information or... you name it, it would be very generically applicable :)

Sample: The load-test cases-query gets all table names, genbi generates a test for each table name and asserting the match pattern-regular expression. Instead of table names it could be any string, then.

Otherwise I would build it with a query test-template and use the LIKE-operator to test if it returns an ok. This operator is not as good as the RegEx-function, but it would be a workaround :)

Just some thoughts, please take it more like a question, not as statements as I wrote it :)

[Seddryck]

As a developer, IMO, I'd prefer my project doesn't compile if I'm not respecting naming conventions than having to run tests and going back to my development. I also think that it's easier to re-use code analysis rules than tests: It's a personal opinion.

Now, as I said previously, I consider this a valid feature-request and it should be easy to implement. I'll also consider the case of a generic approach and perform the assertion on a List returned by something else.

[FuegoArtificial]

Agreed, if the project doesn't compile it's even better! Thanks for your consideration.

[Seddryck]

This is not too difficult to implement and it would be a good task for someone who has never taken a look to NBi codebase.

Task list:

1. The xml syntax is already supported, you can skip it.
2. Create a new Builder into NBi.NUnit.Builder for a Structure and a MatchPattern. This class should transform the xml syntax into real objects. You should take a look to other builders to get some inspiration.
3. Register the new builder into the factory at NBi.NUnit.TestCaseFactory
4. Create a new constraint MatchPatternConstraint into the namespace NBi.NUnit.Structure based on MatchPatternConstraint found into NBi.NUnit.Query
5. Add tests at least in NBi.Testing.Acceptance to be sure that your new test is correctly handled (at least a positive test).

[FuegoArtificial]

Hi Seddryck, I have tried to implement the new test-approach. I have some questions:

• How do I need to implement the constraint correctly. Right now, I get exceptions. I can go into detail here, if it helps?
• Would the test query & match pattern actually already achieve the goal to regex-test tables etc.?
• How do you test new developments effectively? Right now, with each build I'm copying the entire framework to a nbi-testbench with a test I built and want to get working. However, this seems to be very time consuming in the long run.

I didn't "hardcore code" for a little while and am not up to date with the most recent patterns, please be indulgent :) I committed the current state of my development into my branch for #106. Is this accessible for you? Generally said, I didn't work with GitHub before, so there might be mistakes going with the appropriate workflow. If you have any link on how to really correctly approach this, I'd be glad. I've found many different guides on the internet, but it didn't get clear for me yet. Is there a way to ask this kind of (sometimes stupid) questions in a non-public way via this platform? So I don't spam people with too-easy-to-answer-questions?

Cheers

[FuegoArtificial]

I just tested the way to use a query-match-test instead of a structure-match-test. That's even easier. What do you think about a short documentation about it? I could support you. On my way I just discovered this way diving into the code. :) From my point of view, the structure-match-test is unnecessary, right now.

[Seddryck]

Feel free to blog about it but it's not something that I'll document. as said previously, I think that it's a bit too late to implement this check at testing time, I'd go for a test at compile time.

If you want to see how to do this check at compile time, you should check my project "Tibre", the project Code Anslysis Rule.

On 26 Oct 2015, at 14:59, FuegoArtificial notifications@github.com wrote:

I just tested the way to use a query-match-test instead of a structure-match-test. That's even easier. What do you think about a short documentation about it? I could support you. On my way I just discovered this way diving into the code. :) From my point of view, the structure-match-test is unnecessary, right now.

— Reply to this email directly or view it on GitHub.

[Seddryck]

oups sorry missed the point that you had effectively worked in this. You should create a pull request with from your repository to mine.

I will check your code and probably add a test if missing then commit it to the main repository.

You can also add the documentation in the gh-pages branch.

I will check your code asap!

[FuegoArtificial]

Thanks! Because of the existing solution with the query-match-test, I'd stop developing on the structure-match-test. Doesn't make much sense, perhaps? I could still provide the latest commit, but it's far from ready :( I could still provide a documentation part for the query-match-test instead, if you'd support it. You are definitely right with your compile-time-approach. It's just that we don't use that yet and with this test-time-approach we can achieve very easy checking on the dev-system with the help of the deployed nbi-framework even - in some cases - without being the developer myself. It just helps a lot already, it's a win for me. :)

[Seddryck]

I'll check your implementation and if I have time I'll continue it.

But I won't support the query-match-test, it's a bit too complex to write this kind of queries and it's quickly difficult to maintain for standard users.

[FuegoArtificial]

I might missunderstand, what do you mean with complex? For me it's very easy to write the test compared to all other kinds of tests.

Just as a sample:

  <test name="mein Test">
    <category>meine Kategorie</category>
    <system-under-test>
      <execution>
        <query connectionString="Data Source=DEVELOPMENT;Initial Catalog=NBI;Integrated Security=SSPI">SELECT TABLE_NAME FROM information_schema.tables</query>
      </execution>
    </system-under-test>
    <assert>
      <matchPattern>
        <regex>Query[A-Za-z0-9_]</regex>
      </matchPattern>
    </assert>
  </test>

Do I understand something wrong?

[Seddryck]

No, I just think that having to know about information_schema.tables is complex. I mean it's not something that a guy graduated from university will know.

On 27 Oct 2015, at 13:36, FuegoArtificial notifications@github.com wrote:

I might missunderstand, what do you mean with complex? For me it's very easy to write the test compared to all other kinds of tests.

Just as a sample:

meine Kategorie SELECT TABLE_NAME FROM information_schema.tables Query[A-Za-z0-9_]

Do I understand something wrong?

— Reply to this email directly or view it on GitHub.

[FuegoArtificial]

I don't know what else is behind it, I just googled it and can now test for conventions or data patterns. Could you leave the possibility of this kind of test within the code, please? No documentation needed for me :)

[FuegoArtificial]

Another thought: How would you test for patterns within data, for example e-mail-adresses or other expected string patterns? Of course, you could do it within ETL already. Could this be an example of non-metadata-tests and a rather non-complex kind of sample?

[Seddryck]

I don't plan to remove the possibility to query sys tables but I'll not recommend or document it :-)

For your second question can't you use the matchPattern assertion?

[FuegoArtificial]

That's good! :D I believe, I brought up a misunderstanding. Assume, it's not about regex-tests of tables, views, etc. (metadata) but of "any" data, like the sample with the e-mail-addresses. Would this kind of test-possibility be worth being documented? Because the matchPattern assertion is just documented for the cube-element tests but not for queries, if I'm not mistaken. At least, it's great I now know, it's possible for more than cube elements, so I can use the matchPattern assertion in this case which you asked me about. :) I should've asked the question about documentation with another heading beforehand to clarify, that with "documentation" I didn't mean the use of systables. It was just an example of the possibility to assert patterns to query results. Useful discovery! :) I'm very sorry for the confusion! I hope I could distinguish more what I meant with the proposal of "documentation". Thanks for your patience!

[Seddryck]

Oups, I missed your point that matchPattern for a query was not documented, I'm checking the code and eventually the tests before documenting that :-)

[Seddryck]

IMO, it's documented there: http://www.nbi.io/docs/resultset-format/

[FuegoArtificial]

well, I guess, you are right! next time you can tell me "read the f... manual!" haha :D