1 package com.workingdogs.village;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.sql.Connection;
23 import java.sql.DriverManager;
24
25 /***
26 * This class is used for testing the functionality of this product. While creating this code, I have closed many potential bugs,
27 * but I'm sure that others still exist. Thus, if you find a bug in Village, please add to this test suite so that the bug will be
28 * sure to be fixed in future versions.
29 *
30 * <P>
31 * In order to do the testing, you will need to be able to connect via JDBC to your database. Since I use MySQL <A
32 * HREF="http://www.mysql.com/">http://www.mysql.com/</A> , this testing suite is best for that database. I also use the mm MySQL
33 * drivers <A HREF="http://www.worldserver.com/mm.mysql/">http://www.worldserver.com/mm.mysql/</A> because it is the best driver
34 * that I have found for MySQL.
35 * </p>
36 *
37 * <P>
38 * Note that Village should work with <strong>any</strong> JDBC compliant driver.
39 * </p>
40 *
41 * <P>
42 * Here is the schema that this test expects ( you should be able to copy and paste it into your MySQL database that you want to
43 * use ):
44 * <pre>
45 * CREATE TABLE test
46 * (
47 * a TINYINT null,
48 * b SMALLINT null,
49 * c MEDIUMINT null,
50 * d INT null,
51 * e INTEGER null,
52 * f BIGINT null,
53 * g REAL null,
54 * h DOUBLE null,
55 * i FLOAT null,
56 * j DECIMAL(8,1) null,
57 * k NUMERIC(8,1) null,
58 * l CHAR(255) null,
59 * m VARCHAR(255) null,
60 * n DATE null,
61 * o TIME null,
62 * p TIMESTAMP null,
63 * q DATETIME null,
64 * r TINYBLOB null,
65 * s BLOB null,
66 * t MEDIUMBLOB null,
67 * u LONGBLOB null,
68 * v TINYTEXT null,
69 * w TEXT null,
70 * x MEDIUMTEXT null
71 * );
72 * </pre>
73 * </p>
74 *
75 * @author <a href="mailto:jon@latchkey.com">Jon S. Stevens</a>
76 * @version $Revision: 565 $
77 */
78 public class TestMySQL
79 {
80 /*** The database connection */
81 static Connection conn;
82
83 /*** This is the name of the database. Created with mysqladmin create */
84 private static String DB_NAME = "village";
85
86 /*** This is the name of the table in the DB_NAME */
87 private static String DB_TABLE = "test";
88
89 /*** This is the name of the machine that is hosting the MySQL server */
90 private static String DB_HOST = "localhost";
91
92 /***
93 * This is the user to log into the database as. For this test, the user must have insert/update/delete access to the database.
94 */
95 private static String DB_USER = "";
96
97 /*** the password for the user */
98 private static String DB_PASS = "";
99
100 /*** mm MySQL Driver setup */
101 private static String DB_DRIVER = "org.gjt.mm.mysql.Driver";
102
103 /*** mm MySQL Driver setup */
104 private static String DB_CONNECTION = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?user=" + DB_USER + "&password=" + DB_PASS;
105
106 /*** used for debugging */
107 private static boolean debugging = true;
108
109 /*** used for debugging */
110 private static int num = 1;
111
112 /*** used for debugging */
113 private static int testCount = 1;
114
115 /*** used for debugging */
116 private static int TDS = 1;
117
118 /*** used for debugging */
119 private static int QDS = 2;
120
121 /*** used for debugging */
122 private static int PASSED = 1;
123
124 /*** used for debugging */
125 private static int FAILED = 2;
126
127 /***
128 * @TODO DOCUMENT ME!
129 *
130 * @param argv @TODO DOCUMENT ME!
131 */
132 public static void main(String [] argv)
133 {
134 if ((argv.length > 0) && (argv.length < 5))
135 {
136 System.out.println("Format: TestMySQL <DB_NAME> <DB_TABLE> <DB_HOST> <DB_USER> <DB_PASS>");
137
138 return;
139 }
140 else if (argv.length == 5)
141 {
142 DB_NAME = argv[0];
143 DB_TABLE = argv[1];
144 DB_HOST = argv[2];
145 DB_USER = argv[3];
146 DB_PASS = argv[4];
147 DB_CONNECTION = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?user=" + DB_USER + "&password=" + DB_PASS;
148 }
149
150 getConnection();
151
152
153
154
155
156
157
158
159 }
160
161 /***
162 * This test verifies that deleting multiple records actually works. after execution, there should be no more records in the
163 * database.
164 */
165 public static void testDeleteSomeRecords()
166 {
167 try
168 {
169 KeyDef kd = new KeyDef().addAttrib("e");
170 TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
171 tds.where("e > 100");
172
173
174 Record newRec = tds.addRecord();
175 newRec.setValue("e", "200");
176
177 Record newRec2 = tds.addRecord();
178 newRec2.setValue("e", "300");
179 tds.save();
180
181
182 tds.fetchRecords();
183
184 for (int i = 0; i < tds.size(); i++)
185 {
186 Record rec = tds.getRecord(i);
187
188
189 rec.markToBeDeleted();
190 System.out.println("here " + i + ": " + rec.toString());
191 }
192
193 tds.save();
194 tds.close();
195 }
196 catch (Exception e)
197 {
198 debug(TDS, e);
199 }
200 }
201
202 /***
203 * This test will throw a DataSetException. The first getRecord will succeed and the second one will fail.
204 */
205 public static void testRemoveRecord()
206 {
207 try
208 {
209 TableDataSet tds = new TableDataSet(conn, DB_TABLE);
210 tds.addRecord();
211
212 Record rec = tds.getRecord(0);
213 tds.removeRecord(rec);
214
215 Record foo = tds.getRecord(0);
216 tds.close();
217 }
218 catch (Exception e)
219 {
220 debug(TDS, e);
221 }
222 }
223
224 /***
225 * @TODO DOCUMENT ME!
226 */
227 public static void testTableDataSet2()
228 {
229 try
230 {
231 TableDataSet tds = new TableDataSet(conn, DB_TABLE);
232 Record rec = tds.addRecord();
233 rec.setValue("b", 2);
234 tds.save();
235 tds.close();
236 }
237 catch (Exception e)
238 {
239 debug(TDS, e);
240 }
241 }
242
243 /***
244 * @TODO DOCUMENT ME!
245 */
246 public static void testTableDataSet3()
247 {
248 try
249 {
250 TableDataSet tds = new TableDataSet(conn, DB_TABLE);
251 Record rec = tds.addRecord();
252 rec.setValue("b", 2);
253 rec.save();
254 tds.close();
255 }
256 catch (Exception e)
257 {
258 debug(TDS, e);
259 }
260 }
261
262 /***
263 * @TODO DOCUMENT ME!
264 */
265 public static void testTableDataSet4()
266 {
267 try
268 {
269 KeyDef kd = new KeyDef().addAttrib("b");
270 TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
271 Record rec = tds.addRecord();
272 rec.setValueNull("b");
273 System.out.println(rec.getSaveString());
274 rec.save();
275 rec.markToBeDeleted();
276 System.out.println(rec.getSaveString());
277 rec.save();
278 tds.close();
279 }
280 catch (Exception e)
281 {
282 debug(TDS, e);
283 }
284 }
285
286 /***
287 * @TODO DOCUMENT ME!
288 */
289 public static void testTableDataSet()
290 {
291 try
292 {
293 KeyDef kd = new KeyDef().addAttrib("a");
294 TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
295 tds.order("a");
296 tds.fetchRecords();
297
298 int size = tds.size();
299
300 debug(TDS, "size of fetchRecords", size);
301 debug(TDS, "getSelectString()", tds.getSelectString());
302 test(TDS, tds.getSelectString(), "SELECT * FROM test ORDER BY a");
303
304
305 Record addRec = tds.addRecord();
306 addRec.setValue("a", 1);
307 addRec.setValue("b", 2);
308 addRec.setValue("c", 2343);
309 addRec.setValue("d", 33333);
310 addRec.setValue("e", 22222);
311 addRec.setValue("f", 234324);
312 addRec.setValue("g", 3434);
313 addRec.setValue("h", 2343.30);
314 addRec.setValue("i", 2343.22);
315 addRec.setValue("j", 333.3);
316 addRec.setValue("k", 333.3);
317 addRec.setValue("l", "lskdfsd");
318 addRec.setValue("m", "lksdflkjsldf");
319 addRec.setValue("n", new java.util.Date());
320 addRec.setValue("o", new java.util.Date());
321 addRec.setValue("p", new java.util.Date());
322 addRec.setValue("q", new java.util.Date());
323 addRec.setValue("r", "lksdflkjsldf");
324 addRec.setValue("s", "lksdflkjsldf");
325 addRec.setValue("t", "lksdflkjsldf");
326 addRec.setValue("u", "lksdflkjsldf");
327 addRec.setValue("v", "lksdflkjsldf");
328 addRec.setValue("w", "lksdflkjsldf");
329 addRec.setValue("x", "lksdflkjsldf");
330
331 debug(TDS, "getSaveString() for insert", addRec.getSaveString());
332 test(TDS, addRec.getSaveString(),
333 "INSERT INTO test ( a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
334
335
336 addRec.save();
337
338 debug(TDS, "size of TDS after save()", tds.size());
339 test(TDS, size + 1, tds.size());
340
341 Record updateRec = tds.getRecord(0);
342 updateRec.setValue("b", 234);
343 updateRec.setValue("c", 4);
344 updateRec.setValue("d", 4);
345 updateRec.setValue("e", 5);
346 updateRec.setValue("f", 6);
347 updateRec.setValue("g", 3);
348 updateRec.setValue("h", 3.4);
349 updateRec.setValue("i", 33.44);
350 updateRec.setValue("j", 33.55);
351 updateRec.setValue("k", 3333.7);
352 updateRec.setValue("l", "qweqwe");
353 updateRec.setValue("m", "qweqwe");
354 updateRec.setValue("n", new java.util.Date());
355 updateRec.setValue("o", new java.util.Date());
356 updateRec.setValue("p", new java.util.Date());
357 updateRec.setValue("q", new java.util.Date());
358 updateRec.setValue("r", "qweqwe");
359 updateRec.setValue("s", "qweqwe");
360 updateRec.setValue("t", "qweqwe");
361 updateRec.setValue("u", "qweqwe");
362 updateRec.setValue("v", "qweqwe");
363 updateRec.setValue("w", "qweqwe");
364 updateRec.setValue("x", "qweqwe");
365
366 debug(TDS, "updateRec.getRefreshQueryString()", updateRec.getRefreshQueryString());
367
368 debug(TDS, "updateRec.getSaveString() for update", updateRec.getSaveString());
369 test(TDS, updateRec.getSaveString(),
370 "UPDATE test SET b = ?, c = ?, d = ?, e = ?, f = ?, g = ?, h = ?, i = ?, j = ?, k = ?, l = ?, m = ?, n = ?, o = ?, p = ?, q = ?, r = ?, s = ?, t = ?, u = ?, v = ?, w = ?, x = ? WHERE a = ?");
371
372 updateRec.save();
373
374
375 addRec.markToBeDeleted();
376
377 debug(TDS, "addRec.getSaveString() for delete", addRec.getSaveString());
378 test(TDS, addRec.getSaveString(), "DELETE FROM test WHERE a = ?");
379
380
381 addRec.save();
382 test(TDS, tds.size(), 0);
383
384 tds.close();
385
386
387 tds = new TableDataSet(conn, DB_TABLE, kd);
388 tds.fetchRecords();
389 addRec = tds.addRecord();
390 addRec.setValue("a", 1);
391 addRec.save();
392
393 tds = new TableDataSet(conn, DB_TABLE, kd);
394 tds.fetchRecords();
395
396 Record getRec = tds.getRecord(0);
397
398 debug(TDS, "getRec.asString() 1a:", getRec.getValue("a").asString());
399 test(TDS, getRec.getValue("a").asString(), "1");
400 debug(TDS, "getRec.asString() 1b:", getRec.getValue("b").asString());
401 test(TDS, getRec.getValue("b").asString(), "0");
402
403 getRec.setValue("b", 5);
404
405 debug(TDS, "getRec.asString() 2b:", getRec.getValue("b").asString());
406 test(TDS, getRec.getValue("b").asString(), "5");
407
408 getRec.refresh(conn);
409
410 debug(TDS, "getRec.asString() 3b:", getRec.getValue("b").asString());
411 test(TDS, getRec.getValue("b").asString(), "0");
412 debug(TDS, "getRec.asString() 2a:", getRec.getValue("a").asString());
413 test(TDS, getRec.getValue("a").asString(), "1");
414
415 getRec.markToBeDeleted();
416 getRec.save();
417
418 System.out.println(tds.toString());
419 System.out.println(getRec.toString());
420 System.out.println(tds.schema().toString());
421
422 tds.close();
423 }
424 catch (Exception e)
425 {
426 debug(TDS, e);
427 }
428 }
429
430 /***
431 * @TODO DOCUMENT ME!
432 */
433 public static void testQueryDataSet()
434 {
435 try
436 {
437 KeyDef kd = new KeyDef().addAttrib("a");
438 TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
439 tds.fetchRecords();
440
441
442 Record addRec = tds.addRecord();
443 addRec.setValue("a", 1);
444 addRec.setValue("b", 2);
445 debug(TDS, "addRec.getSaveString()", addRec.getSaveString());
446 test(TDS, addRec.getSaveString(), "INSERT INTO test ( a, b ) VALUES ( ?, ? )");
447
448
449 addRec.save();
450 tds.close();
451
452
453 QueryDataSet qds = new QueryDataSet(conn, "SELECT * FROM " + DB_TABLE);
454 qds.fetchRecords();
455
456 debug(QDS, "qds.getSelectString()", qds.getSelectString());
457 test(QDS, qds.getSelectString(), "SELECT * FROM test");
458
459 debug(QDS, "qds.size()", qds.size());
460
461 Record rec = qds.getRecord(0);
462 debug(QDS, "rec.size()", rec.size());
463
464 debug(QDS, "rec.getValue(\"a\").asString()", rec.getValue("a").asString());
465 debug(QDS, "rec.getValue(\"b\").asString()", rec.getValue("b").asString());
466 debug(QDS, "rec.getValue(\"c\").asString()", rec.getValue("c").asString());
467 debug(QDS, "rec.getValue(\"d\").asString()", rec.getValue("d").asString());
468
469
470
471
472 test(QDS, rec.getValue("d").asString(), "0");
473 qds.close();
474
475
476 kd = new KeyDef().addAttrib("a");
477 tds = new TableDataSet(conn, DB_TABLE, kd);
478 tds.fetchRecords();
479
480 Record getRec = tds.getRecord(0);
481 getRec.markToBeDeleted();
482 getRec.save();
483 tds.close();
484 }
485 catch (Exception e)
486 {
487 debug(TDS, e);
488 }
489 }
490
491 /***
492 * @TODO DOCUMENT ME!
493 */
494 public static void getConnection()
495 {
496 try
497 {
498 Class.forName(DB_DRIVER);
499 conn = DriverManager.getConnection(DB_CONNECTION);
500 }
501 catch (Exception e)
502 {
503 System.out.println("\n\nConnection failed : " + e.getMessage());
504 }
505 }
506
507 /***
508 * @TODO DOCUMENT ME!
509 *
510 * @param type @TODO DOCUMENT ME!
511 * @param e @TODO DOCUMENT ME!
512 */
513 public static void debug(int type, Exception e)
514 {
515 debug(TDS, e.getMessage());
516 e.printStackTrace();
517 System.out.println("\n");
518 }
519
520 /***
521 * @TODO DOCUMENT ME!
522 *
523 * @param type @TODO DOCUMENT ME!
524 * @param method @TODO DOCUMENT ME!
525 */
526 public static void debug(int type, String method)
527 {
528 debug(type, method, null);
529 }
530
531 /***
532 * @TODO DOCUMENT ME!
533 *
534 * @param type @TODO DOCUMENT ME!
535 * @param method @TODO DOCUMENT ME!
536 * @param value @TODO DOCUMENT ME!
537 */
538 public static void debug(int type, String method, int value)
539 {
540 debug(type, method, String.valueOf(value));
541 }
542
543 /***
544 * @TODO DOCUMENT ME!
545 *
546 * @param type @TODO DOCUMENT ME!
547 * @param test @TODO DOCUMENT ME!
548 * @param value @TODO DOCUMENT ME!
549 */
550 public static void test(int type, int test, int value)
551 {
552 if (debugging)
553 {
554 String name = "";
555
556 if (type == TDS)
557 {
558 name = "TableDataSet";
559 }
560 else
561 {
562 name = "QueryDataSet";
563 }
564
565 String val = "";
566
567 if (test == value)
568 {
569 val = "Passed";
570 }
571 else
572 {
573 val = "Failed";
574 }
575
576 System.out.print("[" + num++ + "] Test " + testCount++ + " - " + val + "!\n");
577
578 System.out.flush();
579 }
580 }
581
582 /***
583 * @TODO DOCUMENT ME!
584 *
585 * @param type @TODO DOCUMENT ME!
586 * @param test @TODO DOCUMENT ME!
587 * @param value @TODO DOCUMENT ME!
588 */
589 public static void test(int type, String test, String value)
590 {
591 if (debugging)
592 {
593 String name = "";
594
595 if (type == TDS)
596 {
597 name = "TableDataSet";
598 }
599 else
600 {
601 name = "QueryDataSet";
602 }
603
604 String val = "";
605
606 if (test.equals(value))
607 {
608 val = "Passed";
609 }
610 else
611 {
612 val = "Failed";
613 }
614
615 System.out.print("[" + num++ + "] Test " + testCount++ + " - " + val + "!\n");
616
617 System.out.flush();
618 }
619 }
620
621 /***
622 * @TODO DOCUMENT ME!
623 *
624 * @param type @TODO DOCUMENT ME!
625 * @param method @TODO DOCUMENT ME!
626 * @param value @TODO DOCUMENT ME!
627 */
628 public static void debug(int type, String method, String value)
629 {
630 if (debugging)
631 {
632 String name = "";
633
634 if (type == TDS)
635 {
636 name = "TableDataSet";
637 }
638 else
639 {
640 name = "QueryDataSet";
641 }
642
643 if (value != null)
644 {
645 System.out.print("[" + num++ + "] " + name + " - " + method + " = " + value + "\n");
646 }
647 else
648 {
649 System.out.print("[" + num++ + "] " + name + " - " + method + "\n");
650 }
651
652 System.out.flush();
653 }
654 }
655 }