1   package com.workingdogs.village;
2   
3   /*
4    * Licensed to the Apache Software Foundation (ASF) under one
5    * or more contributor license agreements.  See the NOTICE file
6    * distributed with this work for additional information
7    * regarding copyright ownership.  The ASF licenses this file
8    * to you under the Apache License, Version 2.0 (the
9    * "License"); you may not use this file except in compliance
10   * with the License.  You may obtain a copy of the License at
11   *
12   *   http://www.apache.org/licenses/LICENSE-2.0
13   *
14   * Unless required by applicable law or agreed to in writing,
15   * software distributed under the License is distributed on an
16   * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17   * KIND, either express or implied.  See the License for the
18   * specific language governing permissions and limitations
19   * under the License.
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         //      testDeleteSomeRecords();
153         //      testTableDataSet();
154         //      testQueryDataSet();
155         //      testTableDataSet2();
156         //      testTableDataSet3();
157         //      testTableDataSet4();
158         //      testRemoveRecord();
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             // add some records
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             // get those records
182             tds.fetchRecords();
183 
184             for (int i = 0; i < tds.size(); i++)
185             {
186                 Record rec = tds.getRecord(i);
187 
188                 // delete those records
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             // add a new record
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             // save it (causing an INSERT to happen)
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             // mark it for deletion
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             // save it (causing a DELETE to happen and also remove the records from the TDS)
381             addRec.save();
382             test(TDS, tds.size(), 0);
383 
384             tds.close();
385 
386             // Start a new TableDataSet, this is to test the Record.refresh() method
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             // add a new record
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             // save it (causing an INSERT to happen)
449             addRec.save();
450             tds.close();
451 
452             // get a QDS
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()); // should be 1
460 
461             Record rec = qds.getRecord(0);
462             debug(QDS, "rec.size()", rec.size()); // should be 24
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             // this tests to make sure that "d" was assigned properly
470             // there was a bug where wasNull() was being checked and this wasn't
471             // being setup correctly.
472             test(QDS, rec.getValue("d").asString(), "0");
473             qds.close();
474 
475             // delete the record
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 }