1 /*=========================================================================
2
3 Program: Visualization Toolkit
4 Module: TestMySQLDatabase.cxx
5
6 Copyright (c) Ken Martin, Will Schroeder, Bill Lorensen
7 All rights reserved.
8 See Copyright.txt or http://www.kitware.com/Copyright.htm for details.
9
10 This software is distributed WITHOUT ANY WARRANTY; without even
11 the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
12 PURPOSE. See the above copyright notice for more information.
13
14 =========================================================================*/
15 /*-------------------------------------------------------------------------
16 Copyright 2008 Sandia Corporation.
17 Under the terms of Contract DE-AC04-94AL85000 with Sandia Corporation,
18 the U.S. Government retains certain rights in this software.
19 -------------------------------------------------------------------------*/
20 // .SECTION Thanks
21 // Thanks to Andrew Wilson and Philippe Pebay from Sandia National Laboratories
22 // for implementing this test.
23 //
24 // You should set VTK_MYSQL_TEST_URL to the address of a MySQL
25 // database that you can use for testing. For example, if you have
26 // MySQL running on foo.mycompany.com, your test user is called
27 // 'vtktest' and the database is called 'elephant', the URL looks like
28 // 'mysql://vtktest@foo.mycompany.com/elephant'.
29 //
30 // We recommend creating a user and a 'vtktest' database, then giving
31 // the user in question rights *only* to that database. The password
32 // 'vtktest' is hard-coded into this file for the moment until we
33 // decide whether it's a good idea to put any kind of password in
34 // CMake.
35
36 #include "DatabaseSchemaWith2Tables.h"
37 #include "vtkIOMySQLTestingCxxConfigure.h"
38 #include "vtkMySQLDatabase.h"
39 #include "vtkRowQueryToTable.h"
40 #include "vtkSQLDatabaseSchema.h"
41 #include "vtkSQLQuery.h"
42 #include "vtkStdString.h"
43 #include "vtkTable.h"
44 #include "vtkTimePointUtility.h"
45 #include "vtkVariant.h"
46 #include "vtkVariantArray.h"
47
48 #include <vector>
49
TestMySQLDatabase(int,char ** const)50 int TestMySQLDatabase(int, char** const)
51 {
52 vtkMySQLDatabase* db =
53 vtkMySQLDatabase::SafeDownCast(vtkSQLDatabase::CreateFromURL(VTK_MYSQL_TEST_URL));
54
55 // Temp code to for linkage ...
56 vtkMySQLDatabase* tmp = vtkMySQLDatabase::New();
57
58 bool status = db->Open("vtktest");
59
60 if (!status)
61 {
62 cerr << "Couldn't open database.\n";
63 return 1;
64 }
65
66 vtkSQLQuery* query = db->GetQueryInstance();
67 vtkStdString createQuery(
68 "CREATE TABLE IF NOT EXISTS people (name TEXT, age INTEGER, weight FLOAT)");
69 cout << createQuery << endl;
70 query->SetQuery(createQuery.c_str());
71 if (!query->Execute())
72 {
73 cerr << "Create query failed" << endl;
74 return 1;
75 }
76
77 for (int i = 0; i < 40; ++i)
78 {
79 char insertQuery[200];
80 snprintf(insertQuery, sizeof(insertQuery), "INSERT INTO people VALUES('John Doe %d', %d, %d)",
81 i, i, 10 * i);
82 cout << insertQuery << endl;
83 query->SetQuery(insertQuery);
84 if (!query->Execute())
85 {
86 cerr << "Insert query " << i << " failed" << endl;
87 return 1;
88 }
89 }
90
91 const char* queryText = "SELECT name, age, weight FROM people WHERE age <= 20";
92 query->SetQuery(queryText);
93 cerr << endl << "Running query: " << query->GetQuery() << endl;
94
95 cerr << endl << "Using vtkSQLQuery directly to execute query:" << endl;
96 if (!query->Execute())
97 {
98 cerr << "Query failed" << endl;
99 return 1;
100 }
101
102 for (int col = 0; col < query->GetNumberOfFields(); ++col)
103 {
104 if (col > 0)
105 {
106 cerr << ", ";
107 }
108 cerr << query->GetFieldName(col);
109 }
110 cerr << endl;
111 while (query->NextRow())
112 {
113 for (int field = 0; field < query->GetNumberOfFields(); ++field)
114 {
115 if (field > 0)
116 {
117 cerr << ", ";
118 }
119 cerr << query->DataValue(field).ToString().c_str();
120 }
121 cerr << endl;
122 }
123
124 cerr << endl << "Using vtkSQLQuery to execute query and retrieve by row:" << endl;
125 if (!query->Execute())
126 {
127 cerr << "Query failed" << endl;
128 return 1;
129 }
130 for (int col = 0; col < query->GetNumberOfFields(); ++col)
131 {
132 if (col > 0)
133 {
134 cerr << ", ";
135 }
136 cerr << query->GetFieldName(col);
137 }
138 cerr << endl;
139 vtkVariantArray* va = vtkVariantArray::New();
140 while (query->NextRow(va))
141 {
142 for (int field = 0; field < va->GetNumberOfValues(); ++field)
143 {
144 if (field > 0)
145 {
146 cerr << ", ";
147 }
148 cerr << va->GetValue(field).ToString().c_str();
149 }
150 cerr << endl;
151 }
152 va->Delete();
153
154 cerr << endl << "Using vtkRowQueryToTable to execute query:" << endl;
155 vtkRowQueryToTable* reader = vtkRowQueryToTable::New();
156 reader->SetQuery(query);
157 reader->Update();
158 vtkTable* table = reader->GetOutput();
159 for (vtkIdType col = 0; col < table->GetNumberOfColumns(); ++col)
160 {
161 table->GetColumn(col)->Print(cerr);
162 }
163 cerr << endl;
164 for (vtkIdType row = 0; row < table->GetNumberOfRows(); ++row)
165 {
166 for (vtkIdType col = 0; col < table->GetNumberOfColumns(); ++col)
167 {
168 vtkVariant v = table->GetValue(row, col);
169 cerr << "row " << row << ", col " << col << " - " << v.ToString() << " ( "
170 << vtkImageScalarTypeNameMacro(v.GetType()) << " )" << endl;
171 }
172 }
173
174 query->SetQuery("DROP TABLE people");
175 if (!query->Execute())
176 {
177 cerr << "DROP TABLE people query failed" << endl;
178 return 1;
179 }
180
181 reader->Delete();
182 query->Delete();
183 db->Delete();
184
185 // ----------------------------------------------------------------------
186 // Testing transformation of a schema into a MySQL database
187
188 // 1. Create the schema
189 DatabaseSchemaWith2Tables schema;
190
191 // 2. Convert the schema into a MySQL database
192 cerr << "@@ Converting the schema into a MySQL database...";
193
194 db = vtkMySQLDatabase::SafeDownCast(vtkSQLDatabase::CreateFromURL(VTK_MYSQL_TEST_URL));
195 status = db->Open("vtktest");
196
197 if (!status)
198 {
199 cerr << "Couldn't open database.\n";
200 return 1;
201 }
202
203 status = db->EffectSchema(schema.GetSchema());
204 if (!status)
205 {
206 cerr << "Could not effect test schema.\n";
207 return 1;
208 }
209 cerr << " done." << endl;
210
211 // 3. Count tables of the newly created database
212 cerr << "@@ Counting tables of the newly created database... ";
213
214 query = db->GetQueryInstance();
215 query->SetQuery("SHOW TABLES");
216 if (!query->Execute())
217 {
218 cerr << "Query failed" << endl;
219 return 1;
220 }
221
222 std::vector<vtkStdString> tables;
223 while (query->NextRow())
224 {
225 tables.push_back(query->DataValue(0).ToString());
226 }
227
228 int numTbl = tables.size();
229
230 if (numTbl != schema->GetNumberOfTables())
231 {
232 cerr << "Found an incorrect number of tables: " << numTbl
233 << " != " << schema->GetNumberOfTables() << endl;
234 return 1;
235 }
236
237 cerr << numTbl << " found.\n";
238
239 // 4. Inspect these tables
240 cerr << "@@ Inspecting these tables..."
241 << "\n";
242
243 vtkStdString queryStr;
244 int tblHandle = schema.GetTableBHandle();
245
246 for (tblHandle = 0; tblHandle < numTbl; ++tblHandle)
247 {
248 vtkStdString tblName(schema->GetTableNameFromHandle(tblHandle));
249 cerr << " Table: " << tblName << "\n";
250
251 if (tblName != tables[tblHandle])
252 {
253 cerr << "Fetched an incorrect name: " << tables[tblHandle] << " != " << tblName << endl;
254 return 1;
255 }
256
257 // 4.1 Check columns
258 queryStr = "DESCRIBE ";
259 queryStr += tblName;
260 query->SetQuery(queryStr);
261 if (!query->Execute())
262 {
263 cerr << "Query failed" << endl;
264 return 1;
265 }
266
267 int numFields = query->GetNumberOfFields();
268 int colHandle = 0;
269 for (; query->NextRow(); ++colHandle)
270 {
271 for (int field = 0; field < numFields; ++field)
272 {
273 if (field)
274 {
275 cerr << ", ";
276 }
277 else // if ( field )
278 {
279 vtkStdString colName(schema->GetColumnNameFromHandle(tblHandle, colHandle));
280 if (colName != query->DataValue(field).ToString())
281 {
282 cerr << "Found an incorrect column name: " << query->DataValue(field).ToString()
283 << " != " << colName << endl;
284 return 1;
285 }
286 cerr << " Column: ";
287 }
288 cerr << query->DataValue(field).ToString().c_str();
289 }
290 cerr << endl;
291 }
292
293 if (colHandle != schema->GetNumberOfColumnsInTable(tblHandle))
294 {
295 cerr << "Found an incorrect number of columns: " << colHandle
296 << " != " << schema->GetNumberOfColumnsInTable(tblHandle) << endl;
297 return 1;
298 }
299
300 // 4.2 Check indices
301 queryStr = "SHOW INDEX FROM ";
302 queryStr += tblName;
303 query->SetQuery(queryStr);
304 if (!query->Execute())
305 {
306 cerr << "Query failed" << endl;
307 return 1;
308 }
309
310 numFields = query->GetNumberOfFields();
311 int idxHandle = -1;
312 while (query->NextRow())
313 {
314 int cnmHandle = atoi(query->DataValue(3).ToString()) - 1;
315 if (!cnmHandle)
316 {
317 ++idxHandle;
318 }
319
320 vtkStdString colName(schema->GetIndexColumnNameFromHandle(tblHandle, idxHandle, cnmHandle));
321 for (int field = 0; field < numFields; ++field)
322 {
323 if (field)
324 {
325 cerr << ", ";
326 }
327 else // if ( field )
328 {
329 cerr << " Index: ";
330 }
331 cerr << query->DataValue(field).ToString().c_str();
332 }
333 cerr << endl;
334
335 if (colName != query->DataValue(4).ToString())
336 {
337 cerr << "Fetched an incorrect column name: " << query->DataValue(4).ToString()
338 << " != " << colName << endl;
339 return 1;
340 }
341 }
342
343 if (idxHandle + 1 != schema->GetNumberOfIndicesInTable(tblHandle))
344 {
345 cerr << "Found an incorrect number of indices: " << idxHandle + 1
346 << " != " << schema->GetNumberOfIndicesInTable(tblHandle) << endl;
347 return 1;
348 }
349 }
350
351 // 5. Populate these tables using the trigger mechanism
352 cerr << "@@ Populating table atable...";
353
354 queryStr = "INSERT INTO atable (somename,somenmbr) VALUES ( 'Bas-Rhin', 67 )";
355 query->SetQuery(queryStr);
356 if (!query->Execute())
357 {
358 cerr << "Query failed" << endl;
359 query->Delete();
360 db->Delete();
361 return 1;
362 }
363
364 queryStr = "INSERT INTO atable (somename,somenmbr) VALUES ( 'Hautes-Pyrenees', 65 )";
365 query->SetQuery(queryStr);
366 if (!query->Execute())
367 {
368 cerr << "Query failed" << endl;
369 query->Delete();
370 db->Delete();
371 return 1;
372 }
373
374 queryStr = "INSERT INTO atable (somename,somenmbr) VALUES ( 'Vosges', 88 )";
375 query->SetQuery(queryStr);
376 if (!query->Execute())
377 {
378 cerr << "Query failed" << endl;
379 query->Delete();
380 db->Delete();
381 return 1;
382 }
383
384 cerr << " done." << endl;
385
386 // 6. Check that the trigger-dependent table has indeed been populated
387 cerr << "@@ Checking trigger-dependent table btable...\n";
388
389 queryStr = "SELECT somevalue FROM btable ORDER BY somevalue DESC";
390 query->SetQuery(queryStr);
391 if (!query->Execute())
392 {
393 cerr << "Query failed" << endl;
394 query->Delete();
395 db->Delete();
396 return 1;
397 }
398
399 cerr << " Entries in column somevalue of table btable, in descending order:\n";
400 static const char* dpts[] = { "88", "67", "65" };
401 int numDpt = 0;
402 for (; query->NextRow(); ++numDpt)
403 {
404 if (query->DataValue(0).ToString() != dpts[numDpt])
405 {
406 cerr << "Found an incorrect value: " << query->DataValue(0).ToString()
407 << " != " << dpts[numDpt] << endl;
408 query->Delete();
409 db->Delete();
410 return 1;
411 }
412 cerr << " " << query->DataValue(0).ToString() << "\n";
413 }
414
415 if (numDpt != 3)
416 {
417 cerr << "Found an incorrect number of entries: " << numDpt << " != " << 3 << endl;
418 query->Delete();
419 db->Delete();
420 return 1;
421 }
422
423 cerr << " done." << endl;
424
425 // 7. Test EscapeString.
426 cerr << "@@ Escaping a naughty string...";
427
428 queryStr = "INSERT INTO atable (somename,somenmbr) VALUES ( " +
429 query->EscapeString(vtkStdString("Str\"ang'eS\ntring"), true) + ", 2 )";
430 query->SetQuery(queryStr);
431 if (!query->Execute())
432 {
433 cerr << "Query failed" << endl;
434 query->Delete();
435 db->Delete();
436 return 1;
437 }
438
439 cerr << " done." << endl;
440
441 // 8. Read back the escaped string to verify it worked.
442 cerr << "@@ Reading it back... <";
443
444 queryStr = "SELECT somename FROM atable WHERE somenmbr=2";
445 query->SetQuery(queryStr);
446 if (!query->Execute())
447 {
448 cerr << "Query failed" << endl;
449 query->Delete();
450 db->Delete();
451 return 1;
452 }
453
454 if (!query->NextRow())
455 {
456 cerr << "Query returned no results" << endl;
457 query->Delete();
458 db->Delete();
459 return 1;
460 }
461
462 cerr << query->DataValue(0).ToString().c_str() << "> ";
463 cerr << " done." << endl;
464
465 // 8. Drop tables
466 cerr << "@@ Dropping these tables...";
467
468 for (std::vector<vtkStdString>::iterator it = tables.begin(); it != tables.end(); ++it)
469 {
470 queryStr = "DROP TABLE ";
471 queryStr += *it;
472 query->SetQuery(queryStr);
473
474 if (!query->Execute())
475 {
476 cerr << "Query failed" << endl;
477 return 1;
478 }
479 }
480
481 cerr << " done." << endl;
482
483 // ----------------------------------------------------------------------
484 // Testing time values in database
485 cerr << "@@ Testing time values" << endl;
486 query->SetQuery("create table if not exists time (_date DATE, _time TIME, _timestamp TIMESTAMP, "
487 "_datetime DATETIME, _year YEAR);");
488 cerr << query->GetQuery() << endl;
489 if (!query->Execute())
490 {
491 cerr << "Time table creation failed" << endl;
492 return 1;
493 }
494 query->SetQuery("insert into time values ('2008-01-01', '01:23:45', '2008-01-01 01:23:45', "
495 "'2008-01-01 01:23:45', 2008);");
496 cerr << query->GetQuery() << endl;
497 if (!query->Execute())
498 {
499 cerr << "Time table insert failed" << endl;
500 return 1;
501 }
502 query->SetQuery("select * from time");
503 cerr << query->GetQuery() << endl;
504 if (!query->Execute())
505 {
506 cerr << "Time table select failed" << endl;
507 return 1;
508 }
509 query->NextRow();
510 vtkTypeUInt64 date = vtkTimePointUtility::ISO8601ToTimePoint(query->DataValue(0).ToString());
511 vtkTypeUInt64 time = vtkTimePointUtility::ISO8601ToTimePoint(query->DataValue(1).ToString());
512 vtkTypeUInt64 timestamp = vtkTimePointUtility::ISO8601ToTimePoint(query->DataValue(2).ToString());
513 vtkTypeUInt64 datetime = vtkTimePointUtility::ISO8601ToTimePoint(query->DataValue(3).ToString());
514 int month, day, year, hour, minute, second, msec;
515 vtkTimePointUtility::GetDate(date, year, month, day);
516 if (year != 2008 || month != 1 || day != 1)
517 {
518 cerr << "Date read incorrectly" << endl;
519 return 1;
520 }
521 vtkTimePointUtility::GetTime(time, hour, minute, second, msec);
522 if (hour != 1 || minute != 23 || second != 45)
523 {
524 cerr << "Time read incorrectly" << endl;
525 return 1;
526 }
527 vtkTimePointUtility::GetDateTime(timestamp, year, month, day, hour, minute, second, msec);
528 if (year != 2008 || month != 1 || day != 1 || hour != 1 || minute != 23 || second != 45)
529 {
530 cerr << "Timestamp read incorrectly" << endl;
531 return 1;
532 }
533 vtkTimePointUtility::GetDateTime(datetime, year, month, day, hour, minute, second, msec);
534 if (year != 2008 || month != 1 || day != 1 || hour != 1 || minute != 23 || second != 45)
535 {
536 cerr << "Datetime read incorrectly" << endl;
537 return 1;
538 }
539 year = query->DataValue(4).ToInt();
540 if (year != 2008)
541 {
542 cerr << "Year read incorrectly" << endl;
543 return 1;
544 }
545 query->SetQuery("drop table time;");
546 cerr << query->GetQuery() << endl;
547 if (!query->Execute())
548 {
549 cerr << "Time table drop failed" << endl;
550 return 1;
551 }
552
553 // Clean up
554 db->Delete();
555 query->Delete();
556
557 return 0;
558 }
559