I am currently developing a small project of mine that generates SQL calls in a dynamic way to be used by an other software. The SQL calls are not known beforehand and therefore I would like to be able to unit test the object that generates the SQL.
我目前正在开发一个我的小项目，它以动态方式生成SQL调用以供其他软件使用。 SQL调用事先是未知的，因此我希望能够对生成SQL的对象进行单元测试。
Do you have a clue of how would be the best approach to do this? Bear in mind that there is no possible way to know all the possible SQL calls to be generated.
您是否了解如何才能做到这一点的最佳方法？请记住，没有可能的方法来了解要生成的所有可能的SQL调用。
Currently the only idea I have is to create test cases of the accepted SQL from the db using regex and make sure that the SQL will compile, but this does not ensure that the call returns the expected result.
目前我唯一的想法是使用regex从db创建接受的SQL的测试用例，并确保SQL将编译，但这不能确保调用返回预期的结果。
Edited: Adding more info:
编辑：添加更多信息：
My project is an extension of Boo that will allow the developer to tag his properties with a set of attributes. This attributes are used to identify how the developers wants to store the object in the DB. For example:
我的项目是Boo的扩展，允许开发人员使用一组属性标记他的属性。此属性用于标识开发人员如何将对象存储在DB中。例如：
# This attribute tells the Boo compiler extension that you want to
# store the object in a MySQL db. The boo compiler extension will make sure that you meet
# the requirements
[Storable(MySQL)]
class MyObject():
# Tells the compiler that name is the PK
[PrimaryKey(Size = 25)]
[Property(Name)]
private name as String
[TableColumn(Size = 25)]
[Property(Surname)]
private surname as String
[TableColumn()]
[Property(Age)]
private age as int
The great idea is that the generated code wont need to use reflection, but that it will added to the class in compile time. Yes the compilation will take longer, but there won't be a need to use Reflection at all. I currently have the code working generating the required methods that returns the SQL at compile time, they are added to the object and can be called but I need to test that the generated SQL is correct :P
好主意是生成的代码不需要使用反射，但它会在编译时添加到类中。是的，编译需要更长时间，但根本不需要使用Reflection。我目前有代码工作生成所需的方法，在编译时返回SQL，它们被添加到对象并可以调用，但我需要测试生成的SQL是否正确：P
6 个解决方案
#1
2
This seems like a hen-egg situation. You aren't sure what the generator will spit out and you have a moving target to test against (the real database). So you need to tie the loose ends down.
这似乎是一个鸡蛋情况。你不确定生成器会吐出什么，你有一个移动的目标来测试（真正的数据库）。所以你需要将松散的末端系下来。
Create a small test database (for example with HSQLDB or Derby). This database should use the same features as the real one, but don't make a copy! You will want to understand what each thing in the test database is for and why it is there, so invest some time to come up with some reasonable test cases. Use your code generator against this (static) test database, save the results as fixed strings in your test cases. Start with a single feature. Don't try to build the perfect test database as step #1. You will get there.
创建一个小型测试数据库（例如使用HSQLDB或Derby）。此数据库应使用与真实数据库相同的功能，但不要复制！您将需要了解测试数据库中的每个内容及其原因，因此请花一些时间来提出一些合理的测试用例。将代码生成器用于此（静态）测试数据库，将结果保存为测试用例中的固定字符串。从单一功能开始。不要尝试在步骤＃1中构建完美的测试数据库。你会到达那里。
When you change the code generator, run the tests. They should only break in the expected places. If you find a bug, replicate the feature in question in your test database. Create a new test, check the result. Does it look correct? If you can see the error, fix the expected output in the test. After that, fix the generator so it will create the correct result. Close the bug and move on.
更改代码生成器时，请运行测试。他们应该只在预期的地方打破。如果发现错误，请在测试数据库中复制相关功能。创建一个新测试，检查结果。它看起来是否正确？如果您可以看到错误，请在测试中修复预期的输出。之后，修复生成器，以便创建正确的结果。关闭bug并继续前进。
This way, you can build more and more safe ground in a swamp. Do something you know, check whether it works (ignore everything else). If you are satisfied, move on. Don't try to tackle all the problems at once. One step at a time. Tests don't forget, so you can forget about everything that is being tested and concentrate on the next feature. The test will make sure that your stable foundation keeps growing until you can erect your skyscraper on it.
这样，您就可以在沼泽中建立越来越多的安全地面。做你知道的事情，检查它是否有效（忽略其他一切）。如果您满意，继续前进。不要试图立即解决所有问题。一步一步来。测试不要忘记，因此您可以忘记正在测试的所有内容并专注于下一个功能。测试将确保您的稳定基础不断增长，直到您可以在其上架设摩天大楼。
#2
3
The whole point of unit testing is that you know the answer to compare the code results to. You have to find a way to know the SQL calls before hand.
单元测试的重点是你知道比较代码结果的答案。您必须先找到一种方法来了解SQL调用。
To be honest, as other answerers have suggested, your best approach is to come up with some expected results, and essentially hard-code those in your unit tests. Then you can run your code, obtain the result, and compare against the hard-coded expected value.
说实话，正如其他回答者所建议的那样，你最好的方法是提出一些预期的结果，并在你的单元测试中进行硬编码。然后，您可以运行代码，获取结果，并与硬编码的预期值进行比较。
Maybe you can record the actual SQL generated, rather than executing it and comparing the results, too?
也许您可以记录生成的实际SQL，而不是执行它并比较结果？
#3
1
regex
正则表达式
I think that the grammar of SQL is non-regular, but context-free; subexpressions being the key to realize this. You may want to write a context-free parser for SQL to check for syntax errors.
我认为SQL的语法是非常规的，但是没有上下文;子表达式是实现这一目标的关键。您可能希望为SQL编写无上下文解析器以检查语法错误。
But ask yourself: what is it you want to test for? What are your correctness criteria?
但问问自己：你想测试什么？你的正确性标准是什么？
#4
1
If you are generating the code, why not also generate the tests?
如果要生成代码，为什么不生成测试呢？
Short of that, I would test/debug generated code in the same way you would test/debug any other code without unit tests (i.e. by reading it, running it and/or having it reviewed by others).
除此之外，我将以与测试/调试任何其他代码相同的方式测试/调试生成的代码，而无需单元测试（即通过阅读，运行和/或让其他人审阅）。
#5
0
You don't have to test all cases. Make a collection of example calls, be sure to include as many of the difficult aspects that the function will have to handle as possible, then look if the generated code is correct.
您不必测试所有案例。制作示例调用的集合，确保包含尽可能多的函数必须处理的困难方面，然后查看生成的代码是否正确。
#6
0
I would have a suite of tests that put in a known input and check that the generated SQL is as expected.
我将有一套测试放入已知输入并检查生成的SQL是否符合预期。
You're never going to be able to write a test for every scenario but if you write enough to cover at least the most regular patterns you can be fairly confident your generator is working as expected.
您永远无法为每个场景编写测试，但如果您编写的内容足以涵盖至少最常规的模式，您可以相当自信您的生成器正在按预期工作。
If you find it doesn't work in a specific scenario, write another test for that scenario and fix it.
如果您发现它在特定方案中不起作用，请为该方案编写另一个测试并进行修复。
#1
2
This seems like a hen-egg situation. You aren't sure what the generator will spit out and you have a moving target to test against (the real database). So you need to tie the loose ends down.
这似乎是一个鸡蛋情况。你不确定生成器会吐出什么，你有一个移动的目标来测试（真正的数据库）。所以你需要将松散的末端系下来。
Create a small test database (for example with HSQLDB or Derby). This database should use the same features as the real one, but don't make a copy! You will want to understand what each thing in the test database is for and why it is there, so invest some time to come up with some reasonable test cases. Use your code generator against this (static) test database, save the results as fixed strings in your test cases. Start with a single feature. Don't try to build the perfect test database as step #1. You will get there.
创建一个小型测试数据库（例如使用HSQLDB或Derby）。此数据库应使用与真实数据库相同的功能，但不要复制！您将需要了解测试数据库中的每个内容及其原因，因此请花一些时间来提出一些合理的测试用例。将代码生成器用于此（静态）测试数据库，将结果保存为测试用例中的固定字符串。从单一功能开始。不要尝试在步骤＃1中构建完美的测试数据库。你会到达那里。
When you change the code generator, run the tests. They should only break in the expected places. If you find a bug, replicate the feature in question in your test database. Create a new test, check the result. Does it look correct? If you can see the error, fix the expected output in the test. After that, fix the generator so it will create the correct result. Close the bug and move on.
更改代码生成器时，请运行测试。他们应该只在预期的地方打破。如果发现错误，请在测试数据库中复制相关功能。创建一个新测试，检查结果。它看起来是否正确？如果您可以看到错误，请在测试中修复预期的输出。之后，修复生成器，以便创建正确的结果。关闭bug并继续前进。
This way, you can build more and more safe ground in a swamp. Do something you know, check whether it works (ignore everything else). If you are satisfied, move on. Don't try to tackle all the problems at once. One step at a time. Tests don't forget, so you can forget about everything that is being tested and concentrate on the next feature. The test will make sure that your stable foundation keeps growing until you can erect your skyscraper on it.
这样，您就可以在沼泽中建立越来越多的安全地面。做你知道的事情，检查它是否有效（忽略其他一切）。如果您满意，继续前进。不要试图立即解决所有问题。一步一步来。测试不要忘记，因此您可以忘记正在测试的所有内容并专注于下一个功能。测试将确保您的稳定基础不断增长，直到您可以在其上架设摩天大楼。
#2
3
The whole point of unit testing is that you know the answer to compare the code results to. You have to find a way to know the SQL calls before hand.
单元测试的重点是你知道比较代码结果的答案。您必须先找到一种方法来了解SQL调用。
To be honest, as other answerers have suggested, your best approach is to come up with some expected results, and essentially hard-code those in your unit tests. Then you can run your code, obtain the result, and compare against the hard-coded expected value.
说实话，正如其他回答者所建议的那样，你最好的方法是提出一些预期的结果，并在你的单元测试中进行硬编码。然后，您可以运行代码，获取结果，并与硬编码的预期值进行比较。
Maybe you can record the actual SQL generated, rather than executing it and comparing the results, too?
也许您可以记录生成的实际SQL，而不是执行它并比较结果？
#3
1
regex
正则表达式
I think that the grammar of SQL is non-regular, but context-free; subexpressions being the key to realize this. You may want to write a context-free parser for SQL to check for syntax errors.
我认为SQL的语法是非常规的，但是没有上下文;子表达式是实现这一目标的关键。您可能希望为SQL编写无上下文解析器以检查语法错误。
But ask yourself: what is it you want to test for? What are your correctness criteria?
但问问自己：你想测试什么？你的正确性标准是什么？
#4
1
If you are generating the code, why not also generate the tests?
如果要生成代码，为什么不生成测试呢？
Short of that, I would test/debug generated code in the same way you would test/debug any other code without unit tests (i.e. by reading it, running it and/or having it reviewed by others).
除此之外，我将以与测试/调试任何其他代码相同的方式测试/调试生成的代码，而无需单元测试（即通过阅读，运行和/或让其他人审阅）。
#5
0
You don't have to test all cases. Make a collection of example calls, be sure to include as many of the difficult aspects that the function will have to handle as possible, then look if the generated code is correct.
您不必测试所有案例。制作示例调用的集合，确保包含尽可能多的函数必须处理的困难方面，然后查看生成的代码是否正确。
#6
0
I would have a suite of tests that put in a known input and check that the generated SQL is as expected.
我将有一套测试放入已知输入并检查生成的SQL是否符合预期。
You're never going to be able to write a test for every scenario but if you write enough to cover at least the most regular patterns you can be fairly confident your generator is working as expected.
您永远无法为每个场景编写测试，但如果您编写的内容足以涵盖至少最常规的模式，您可以相当自信您的生成器正在按预期工作。
If you find it doesn't work in a specific scenario, write another test for that scenario and fix it.
如果您发现它在特定方案中不起作用，请为该方案编写另一个测试并进行修复。