· 7 months ago · Feb 24, 2025, 10:15 PM
1
2public const string DatabaseName = "DatabaseName";
3
4enum DatabaseType
5{
6 Production,
7 TestingInProdDb,
8 TestingInTestDb
9}
10
11class Database
12{
13 DatabaseType dbType;
14 uint runId;
15 bool tempDb;
16
17 this(DatabaseType ty)
18 {
19 this.dbType = ty;
20
21 // Figure out what random number to append to test tables.
22 switch ( this.dbType )
23 {
24 case DatabaseType.Production:
25 this.runId = 0;
26 break;
27
28 case DatabaseType.TestingInProdDb:
29 case DatabaseType.TestingInTestDb:
30 // Pseudocode assumes `rand()` returns unsigned integer between `0` and `uint.max`
31 // Note that negative values and floats would be bad here:
32 // this has to be something that can be embedded into names.
33 this.runId = rand();
34 break;
35
36 default:
37 throw Exception("Unknown database type {0}", this.dbType);
38 }
39
40 // Because Database creation requires admin priveleges on the SQL server,
41 // we also just look for whether the test database exists already,
42 // and if it does, we just use that. (That way, a sysadmin could
43 // grant restricted priveleges just for the testrunner.)
44 //
45 // It's also perfectly valid to place the test tables in the same
46 // database as the target/production tables that the program will
47 // be using, as long as they are uniquely named*. That database is
48 // guaranteed to exist, however, it does mean the testrunner needs
49 // potentially invasive access rights to that database (ex: create
50 // and drop tables).
51 //
52 // It's all tradeoffs, and what works best is going to be situational.
53 //
54 // * As commented below, the test-tables-in-production also has
55 // this strong disadvantage: if any tables in this program's code don't
56 // annotate/affix their table names correctly, then the test code
57 // would end up accidentally messing with production tables!
58 //
59 this.tempDb = false;
60 if ( !dbExists(String.Format("SELECT * FROM sys.databases where Name='{0}'", DatabaseName)) ) {
61 createDb(this.name()); // Requires admin priveleges.
62 this.tempDb = true;
63 }
64 }
65
66 void teardown()
67 {
68 if ( tempDb ) {
69 deleteDb(TestDbName);
70 }
71 }
72
73 string name()
74 {
75 switch ( this.dbType )
76 {
77 case DatabaseType.Production: return DatabaseName;
78 case DatabaseType.TestingInProdDb: return DatabaseName;
79 case DatabaseType.TestingInTestDb: return String.Format("TEST_{0}_{1}", DatabaseName, this.runId);
80 default: throw Exception("Unknown database type {0}", this.dbType);
81 }
82 }
83
84 // If testing ever gets performed by using test tables that are colocated
85 // in the production database, then something like this method MUST be
86 // used EVERYWHERE in the program's code.
87 //
88 // This does lend a strong recommendation for creating separate test databases:
89 // even if some part of code mistakenly uses the wrong table name, then
90 // it wouldn't mess with production data!
91 string affixTableName(string tableName)
92 {
93 switch ( this.dbType )
94 {
95 case DatabaseType.Production: return tableName;
96 case DatabaseType.TestingInProdDb: return String.Format("TEST_{0}_{1}", tableName, this.runId);
97 case DatabaseType.TestingInTestDb: return tableName;
98 default: throw Exception("Unknown database type {0}", this.dbType);
99 }
100 }
101
102 void createTestTables()
103 {
104 exec("CREATE TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableFoo"));
105 exec("CREATE TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBar"));
106 exec("CREATE TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBaz"));
107 }
108
109 void dropTestTables()
110 {
111 exec("DROP TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableFoo"));
112 exec("DROP TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBar"));
113 exec("DROP TABLE {0}.dbo.{1} ...", this.name, this.affixTableName("MyTableBaz"));
114 }
115}
116
117class Tester
118{
119 Client test1Client;
120 string[string] test1Data;
121
122 Client test2Client;
123 string[string] test2Data;
124
125 void populateData()
126 {
127 // Assumption: Clients are "stateless" and do not
128 test1Client = new TypeOfTest1Client();
129 test1Data["id"] = "12345";
130 test1Data["status"] = "initial";
131 test1Data["description"] = "first test";
132 ...
133
134 test2Client = new TypeOfTest2Client();
135 test2Data["id"] = "54321";
136 test2Data["status"] = "sent";
137 test2Data["description"] = "foo, bar, baz";
138 ...
139 }
140
141 void runTests()
142 {
143 Database db = setupDatabase(TestingInTestDb.Testing);
144
145 // From Microsoft Docs:
146 // ```
147 // When an exception is handled by a catch block,
148 // the finally block is executed after execution of that catch block
149 // (even if another exception occurs during execution of the catch block).
150 // ```
151 // source: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/statements/exception-handling-statements
152 // Notably, if you just need something equivalent to `scope(exit)`,
153 // then you can also elide the catch(...) block entirely and
154 // use a `finally` block by itself. It will always get executed, regardless
155 // of whether exceptions get thrown or not.
156 try
157 runTestsWithDb(db);
158 finally
159 db.teardown(db);
160 }
161
162 void runTestsWithDb(Database db)
163 {
164 Client c;
165
166 c = test1Client;
167 testEmptyCsv(db, c);
168 testValidCsv(db, c, test1Data, CsvFlavor.NeverQuote);
169 testValidCsv(db, c, test1Data, CsvFlavor.AlwaysQuote);
170 testValidCsv(db, c, test1Data, CsvFlavor.QuoteEveryOther);
171 testValidCsv(db, c, test1Data, CsvFlavor.QuoteOnlyWhenNeeded);
172
173 c = test2Client;
174 testEmptyCsv(db, c);
175 testMalformedCsv(db, c, test2Data, CsvFlavor.NeverQuote);
176 testValidCsv( db, c, test2Data, CsvFlavor.AlwaysQuote);
177 testMalformedCsv(db, c, test2Data, CsvFlavor.QuoteEveryOther);
178 testValidCsv( db, c, test2Data, CsvFlavor.QuoteOnlyWhenNeeded);
179
180 // Another possible variance in client data:
181 // Positional columns vs columns with headers
182 }
183
184 void testValidCsv(Database db, Client client, string[string] testData, CsvFlavor csvFlavor)
185 {
186 db.createTestTables();
187 try {
188 // Ugly name reflects ugliness of try-catch-finally lol.
189 actuallyTestValidCsv(db, client, testData, csvFlavor);
190 }
191 finally {
192 db.dropTestTables();
193 }
194
195
196 }
197
198 void actuallyTestValidCsv(Database db, Client client, string[string] testData, CsvFlavor csvFlavor)
199 {
200 // This method will probably be client-specific, because it would need
201 // to emit the data in that client's particular layout.
202 filename = writeCsvFile(client, client.csvDirectoryPath, testData, csvFlavor);
203
204 // If you're testing a program's ability to watch for files to appear
205 // in a directory (like `client.csvDirectoryPath` in above line), then
206 // you might not want to call this directly.
207 //
208 // Instead:
209 // * If the program deletes files after processing them, then you might
210 // have a loop or system call that waits for the file to disappear
211 // (assuming you've also confirmed that it was created in the first place...
212 // somehow).
213 // * If the program doesn't delete files after processing them, then
214 // the tester has to have some other way of knowing when the
215 // code for processing has completed. (Left as exercise for reader...)
216 //
217 // In either case, it would need a timeout, so that indefinite waits
218 // could be caught and registered as test failure.
219 client.ProcessFileData(db, filename);
220
221 // Assumption: At this point in execution, we KNOW that the program
222 // has finished processing the CSV file and has already
223 // created all database records. If it couldn't, then it has already
224 // thrown an exception or otherwise errored-out.
225
226 // You would probably test more than 1 row in most cases, but I'm just
227 // handling the n=1 case here to keep the example simple.
228 row = exec("SELECT ... FROM {0}.dbo.{1} foo WHERE foo.id = {2}",
229 db.name, db.affixTableName(DatabaseName), testData["id"]);
230
231 assert(row.exists());
232 assert(row["status"] == testData["status"]);
233 assert(row["description"] == testData["description"]);
234 ...
235
236 // We're still dealing with just 1 (CSV) row of sample data, but what if
237 // that one line in the CSV file resulted in multiple SQL rows somehow?
238 // Well, it might look like this:
239 lines = exec("SELECT ... FROM {0}.dbo.{1} bar WHERE bar.id = {2}"
240 db.name, db.affixTableName(DatabaseName), testData["id"]);
241 assert(lines.exists);
242 foreach(line in lines)
243 {
244 assert(line["status"] == testData["status"]);
245 assert(line["xyz"] == testData[String.format("xyz{0}", line.number)]);
246 }
247
248 // Then there'd be some code for handling the "baz" table...
249 // etc.
250 }
251}
252