001: package com.workingdogs.village;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.sql.Connection;
023: import java.sql.DriverManager;
024: import java.sql.PreparedStatement;
025: import java.sql.SQLException;
026:
027: import junit.framework.TestCase;
028:
029: /**
030: * This class is used for testing the functionality of this product. While
031: * creating this code, I have closed many potential bugs, but I'm sure that
032: * others still exist. Thus, if you find a bug in Village, please add to this
033: * test suite so that the bug will be sure to be fixed in future versions.
034: *
035: * <p>
036: * In order to do the testing, you will need to be able to connect via JDBC to
037: * your database. Since I use <a href="http://www.mysql.com/">MySQL</a>, this
038: * testing suite is best for that database. I also use the mm MySQL drivers
039: * <a href="http://mmmysql.sourceforge.net/">mm MySQL drivers</a> because it
040: * is the best driver that I have found for MySQL.
041: * </p>
042: *
043: * <P>
044: * Note that Village should work with <strong>any</strong> JDBC compliant driver.
045: * </p>
046: *
047: * <p>
048: * Here is the schema that this test expects (you should be able to copy and
049: * paste it into your MySQL database that you want to use):
050: * <pre>
051: * CREATE TABLE test
052: * (
053: * a TINYINT null,
054: * b SMALLINT null,
055: * c MEDIUMINT null,
056: * d INT null,
057: * e INTEGER null,
058: * f BIGINT null,
059: * g REAL null,
060: * h DOUBLE null,
061: * i FLOAT null,
062: * j DECIMAL(8,1) null,
063: * k NUMERIC(8,1) null,
064: * l CHAR(255) null,
065: * m VARCHAR(255) null,
066: * n DATE null,
067: * o TIME null,
068: * p TIMESTAMP null,
069: * q DATETIME null,
070: * r TINYBLOB null,
071: * s BLOB null,
072: * t MEDIUMBLOB null,
073: * u LONGBLOB null,
074: * v TINYTEXT null,
075: * w TEXT null,
076: * x MEDIUMTEXT null
077: * );
078: * </pre>
079: * </p>
080: *
081: * <p>
082: * Note that presently this class is hardcoded to use a MySQL database named
083: * "village" with a username of "village" and a password of "village". It is
084: * a modified version of Jon's original Unit test that apparently pre-dated
085: * JUnit!
086: * </p>
087: *
088: * @author <a href="mailto:jon@latchkey.com">Jon S. Stevens</a>
089: * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
090: * @version $Revision: 565 $
091: */
092: public class TestMySQL extends TestCase {
093: /** The database connection */
094: static Connection conn;
095:
096: /** This is the name of the database. Created with mysqladmin create */
097: private static String DB_NAME = "village";
098:
099: /** This is the name of the table in the DB_NAME */
100: private static String DB_TABLE = "test";
101:
102: /** This is the name of the machine that is hosting the MySQL server */
103: private static String DB_HOST = "localhost";
104:
105: /**
106: * This is the user to log into the database as. For this test, the user
107: * must have insert/update/delete access to the database.
108: */
109: private static String DB_USER = "village";
110:
111: /** the password for the user */
112: private static String DB_PASS = "village";
113:
114: /** mm MySQL Driver setup */
115: private static String DB_DRIVER = "org.gjt.mm.mysql.Driver";
116: //private static String DB_DRIVER = "com.mysql.jdbc.Driver";
117:
118: /** mm MySQL Driver setup */
119: private static String DB_CONNECTION = "jdbc:mysql://" + DB_HOST
120: + "/" + DB_NAME + "?user=" + DB_USER + "&password="
121: + DB_PASS;
122:
123: /** used for debugging */
124: private static boolean debugging = true;
125:
126: /** used for debugging */
127: private static int num = 1;
128:
129: /** used for debugging */
130: private static int TDS = 1;
131:
132: /** used for debugging */
133: private static int QDS = 2;
134:
135: /** used for debugging */
136: private static int PASSED = 1;
137:
138: /** used for debugging */
139: private static int FAILED = 2;
140:
141: /** The number of times to hit the schema to try and reach a connection
142: * limit. */
143: private static int SCHEMA_LOOPS = 2000;
144:
145: /**
146: * Creates a new instance.
147: *
148: * @param name the name of the test case to run
149: */
150: public TestMySQL(String name) {
151: super (name);
152: }
153:
154: // /**
155: // * @TODO DOCUMENT ME!
156: // *
157: // * @param argv
158: // */
159: // public static void main(String [] argv)
160: // {
161: // if ((argv.length > 0) && (argv.length < 5))
162: // {
163: // System.out.println("Format: TestMySQL <DB_NAME> <DB_TABLE> <DB_HOST> <DB_USER> <DB_PASS>");
164: //
165: // return;
166: // }
167: // else if (argv.length == 5)
168: // {
169: // DB_NAME = argv[0];
170: // DB_TABLE = argv[1];
171: // DB_HOST = argv[2];
172: // DB_USER = argv[3];
173: // DB_PASS = argv[4];
174: // DB_CONNECTION = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?user=" + DB_USER + "&password=" + DB_PASS;
175: // }
176: //
177: // getConnection();
178: //
179: // testDeleteSomeRecords();
180: // testTableDataSet();
181: // testQueryDataSet();
182: // testSchemaResultSet();
183: // testTableDataSet2();
184: // testTableDataSet3();
185: // testTableDataSet4();
186: // testRemoveRecord();
187: // }
188:
189: public void setUp() {
190: try {
191: getConnection();
192: } catch (ClassNotFoundException e) {
193: System.out.println("\n\nConnection failed : "
194: + e.getMessage());
195: } catch (SQLException e) {
196: System.out.println("\n\nConnection failed : "
197: + e.getMessage());
198: }
199: }
200:
201: protected void tearDown() throws Exception {
202: // Empty the database.
203: PreparedStatement ps = conn
204: .prepareStatement("delete from test");
205: ps.execute();
206:
207: conn.close();
208: }
209:
210: /**
211: * This test verifies that deleting multiple records actually works. after
212: * execution, there should be no more records in the database.
213: */
214: public static void testDeleteSomeRecords() throws SQLException,
215: DataSetException {
216: KeyDef kd = new KeyDef().addAttrib("e");
217: TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
218: tds.where("e > 100");
219:
220: // add some records
221: Record newRec = tds.addRecord();
222: newRec.setValue("e", "200");
223:
224: Record newRec2 = tds.addRecord();
225: newRec2.setValue("e", "300");
226: tds.save();
227:
228: // get those records
229: tds.fetchRecords();
230:
231: for (int i = 0; i < tds.size(); i++) {
232: Record rec = tds.getRecord(i);
233:
234: // delete those records
235: rec.markToBeDeleted();
236: System.out.println("here " + i + ": " + rec.toString());
237: }
238:
239: tds.save();
240: tds.close();
241: }
242:
243: /**
244: * This test checks that a DataSetException is thrown when appropriate.
245: */
246: public static void testRemoveRecord() throws SQLException,
247: DataSetException {
248: TableDataSet tds = new TableDataSet(conn, DB_TABLE);
249: tds.addRecord();
250:
251: Record rec = tds.getRecord(0);
252: tds.removeRecord(rec);
253:
254: try {
255: Record foo = tds.getRecord(0);
256: } catch (DataSetException e) {
257: // expected
258: }
259: tds.close();
260: }
261:
262: public static void testTableDataSet2() throws SQLException,
263: DataSetException {
264: TableDataSet tds = new TableDataSet(conn, DB_TABLE);
265: Record rec = tds.addRecord();
266: rec.setValue("b", 2);
267: tds.save();
268: tds.close();
269: }
270:
271: public static void testTableDataSet3() throws SQLException,
272: DataSetException {
273: TableDataSet tds = new TableDataSet(conn, DB_TABLE);
274: Record rec = tds.addRecord();
275: rec.setValue("b", 2);
276: rec.save();
277: tds.close();
278: }
279:
280: public static void testTableDataSet4() throws SQLException,
281: DataSetException {
282: KeyDef kd = new KeyDef().addAttrib("b");
283: TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
284: Record rec = tds.addRecord();
285: rec.setValueNull("b");
286: System.out.println(rec.getSaveString());
287: rec.save();
288: rec.markToBeDeleted();
289: System.out.println(rec.getSaveString());
290: rec.save();
291: tds.close();
292: }
293:
294: public static void testTableDataSet() throws SQLException,
295: DataSetException {
296: KeyDef kd = new KeyDef().addAttrib("a");
297: TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
298: tds.order("a");
299: tds.fetchRecords();
300:
301: int size = tds.size();
302: assertEquals(0, size);
303:
304: debug(TDS, "size of fetchRecords", size);
305: debug(TDS, "getSelectString()", tds.getSelectString());
306: assertEquals("SELECT * FROM test ORDER BY a", tds
307: .getSelectString());
308:
309: // add a new record
310: Record addRec = tds.addRecord();
311: addRec.setValue("a", 1);
312: addRec.setValue("b", 2);
313: addRec.setValue("c", 2343);
314: addRec.setValue("d", 33333);
315: addRec.setValue("e", 22222);
316: addRec.setValue("f", 234324);
317: addRec.setValue("g", 3434);
318: addRec.setValue("h", 2343.30);
319: addRec.setValue("i", 2343.22);
320: addRec.setValue("j", 333.3);
321: addRec.setValue("k", 333.3);
322: addRec.setValue("l", "lskdfsd");
323: addRec.setValue("m", "lksdflkjsldf");
324: addRec.setValue("n", new java.util.Date());
325: addRec.setValue("o", new java.util.Date());
326: addRec.setValue("p", new java.util.Date());
327: addRec.setValue("q", new java.util.Date());
328: addRec.setValue("r", "lksdflkjsldf");
329: addRec.setValue("s", "lksdflkjsldf");
330: addRec.setValue("t", "lksdflkjsldf");
331: addRec.setValue("u", "lksdflkjsldf");
332: addRec.setValue("v", "lksdflkjsldf");
333: addRec.setValue("w", "lksdflkjsldf");
334: addRec.setValue("x", "lksdflkjsldf");
335:
336: debug(TDS, "getSaveString() for insert", addRec.getSaveString());
337: assertEquals(
338: "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 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )",
339: addRec.getSaveString());
340:
341: // save it (causing an INSERT to happen)
342: addRec.save();
343:
344: debug(TDS, "size of TDS after save()", tds.size());
345: assertEquals(1, tds.size());
346:
347: Record updateRec = tds.getRecord(0);
348: updateRec.setValue("b", 234);
349: updateRec.setValue("c", 4);
350: updateRec.setValue("d", 4);
351: updateRec.setValue("e", 5);
352: updateRec.setValue("f", 6);
353: updateRec.setValue("g", 3);
354: updateRec.setValue("h", 3.4);
355: updateRec.setValue("i", 33.44);
356: updateRec.setValue("j", 33.55);
357: updateRec.setValue("k", 3333.7);
358: updateRec.setValue("l", "qweqwe");
359: updateRec.setValue("m", "qweqwe");
360: updateRec.setValue("n", new java.util.Date());
361: updateRec.setValue("o", new java.util.Date());
362: updateRec.setValue("p", new java.util.Date());
363: updateRec.setValue("q", new java.util.Date());
364: updateRec.setValue("r", "qweqwe");
365: updateRec.setValue("s", "qweqwe");
366: updateRec.setValue("t", "qweqwe");
367: updateRec.setValue("u", "qweqwe");
368: updateRec.setValue("v", "qweqwe");
369: updateRec.setValue("w", "qweqwe");
370: updateRec.setValue("x", "qweqwe");
371:
372: debug(TDS, "updateRec.getRefreshQueryString()", updateRec
373: .getRefreshQueryString());
374:
375: debug(TDS, "updateRec.getSaveString() for update", updateRec
376: .getSaveString());
377: assertEquals(
378: "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 = ?",
379: updateRec.getSaveString());
380:
381: updateRec.save();
382: assertEquals(1, tds.size());
383:
384: // mark it for deletion
385: addRec.markToBeDeleted();
386: assertEquals(1, addRec.getValue(1).asInt());
387:
388: debug(TDS, "addRec.getSaveString() for delete", addRec
389: .getSaveString());
390: assertEquals("DELETE FROM test WHERE a = ?", addRec
391: .getSaveString());
392:
393: // save it (causing a DELETE to happen and also remove the records from the TDS)
394: assertEquals(1, addRec.save());
395: // The save() no longer deletes the record, but rather marks it for deletion.
396: assertEquals(Enums.ZOMBIE, addRec.getSaveType());
397: assertEquals(1, tds.size());
398: // Remove the zombie records.
399: tds.save();
400: assertEquals(0, tds.size());
401:
402: tds.close();
403:
404: // Start a new TableDataSet, this is to test the Record.refresh() method
405: tds = new TableDataSet(conn, DB_TABLE, kd);
406: tds.fetchRecords();
407: addRec = tds.addRecord();
408: addRec.setValue("a", 1);
409: addRec.save();
410: assertEquals(1, tds.size());
411:
412: tds = new TableDataSet(conn, DB_TABLE, kd);
413: tds.fetchRecords();
414: assertEquals(1, tds.size());
415:
416: Record getRec = tds.getRecord(0);
417:
418: debug(TDS, "getRec.asString() 1a:", getRec.getValue("a")
419: .asString());
420: assertEquals("1", getRec.getValue("a").asString());
421: debug(TDS, "getRec.asString() 1b:", getRec.getValue("b")
422: .asString());
423: // TODO Check that smallint is supposed to return null now
424: assertNull(getRec.getValue("b").asString());
425:
426: // Since there is no getRec.save() before the .refresh() the value being
427: // set here will not end up in the database.
428: getRec.setValue("b", 5);
429:
430: debug(TDS, "getRec.asString() 2b:", getRec.getValue("b")
431: .asString());
432: assertEquals("5", getRec.getValue("b").asString());
433:
434: // While the JavaDoc for Record.refresh() indicates that it cannot be
435: // done for a modified record, it certainly allows it and in fact seems
436: // to be the intended purpose of the method.
437: getRec.refresh(conn);
438:
439: debug(TDS, "getRec.asString() 3b:", getRec.getValue("b")
440: .asString());
441: // TODO Check that smallint is supposed to return null now
442: assertNull(getRec.getValue("b").asString());
443: debug(TDS, "getRec.asString() 2a:", getRec.getValue("a")
444: .asString());
445: assertEquals("1", getRec.getValue("a").asString());
446:
447: getRec.markToBeDeleted();
448: getRec.save();
449:
450: System.out.println(tds.toString());
451: System.out.println(getRec.toString());
452: System.out.println(tds.schema().toString());
453:
454: tds.close();
455: }
456:
457: /**
458: * @throws DataSetException
459: * @throws SQLException
460: */
461: public static void testQueryDataSet() throws SQLException,
462: DataSetException {
463: KeyDef kd = new KeyDef().addAttrib("a");
464: TableDataSet tds = new TableDataSet(conn, DB_TABLE, kd);
465: tds.fetchRecords();
466:
467: // add a new record
468: Record addRec = tds.addRecord();
469: addRec.setValue("a", 1);
470: addRec.setValue("b", 2);
471: debug(TDS, "addRec.getSaveString()", addRec.getSaveString());
472: assertEquals("INSERT INTO test ( a, b ) VALUES ( ?, ? )",
473: addRec.getSaveString());
474:
475: // save it (causing an INSERT to happen)
476: addRec.save();
477: tds.close();
478:
479: // get a QDS
480: QueryDataSet qds = new QueryDataSet(conn, "SELECT * FROM "
481: + DB_TABLE);
482: qds.fetchRecords();
483:
484: debug(QDS, "qds.getSelectString()", qds.getSelectString());
485: assertEquals("SELECT * FROM test", qds.getSelectString());
486:
487: debug(QDS, "qds.size()", qds.size()); // should be 1
488:
489: Record rec = qds.getRecord(0);
490: debug(QDS, "rec.size()", rec.size()); // should be 24
491:
492: debug(QDS, "rec.getValue(\"a\").asString()", rec.getValue("a")
493: .asString());
494: debug(QDS, "rec.getValue(\"b\").asString()", rec.getValue("b")
495: .asString());
496: debug(QDS, "rec.getValue(\"c\").asString()", rec.getValue("c")
497: .asString());
498: debug(QDS, "rec.getValue(\"d\").asString()", rec.getValue("d")
499: .asString());
500:
501: // this tests to make sure that "d" was assigned properly
502: // there was a bug where wasNull() was being checked and this wasn't
503: // being setup correctly.
504: // TODO Check that tinyint is supposed to return null now
505: //assertEquals("0", rec.getValue("d").asString());
506: assertNull(rec.getValue("d").asString());
507: qds.close();
508:
509: // delete the record
510: kd = new KeyDef().addAttrib("a");
511: tds = new TableDataSet(conn, DB_TABLE, kd);
512: tds.fetchRecords();
513:
514: Record getRec = tds.getRecord(0);
515: getRec.markToBeDeleted();
516: getRec.save();
517: tds.close();
518: }
519:
520: /**
521: * This is a test for TORQUE-8.
522: *
523: * @throws DataSetException
524: * @throws SQLException
525: */
526: public static void testSchemaResultSet() throws SQLException,
527: DataSetException {
528: for (int i = 0; i < SCHEMA_LOOPS; i++) {
529: System.out.println("testSchemaResultSet() run " + i
530: + " of " + SCHEMA_LOOPS);
531: Schema schema = new Schema().schema(conn, "test", "a");
532: assertEquals(schema.getTableName(), "test");
533: }
534: }
535:
536: /**
537: * Get a connection.
538: */
539: public static void getConnection() throws ClassNotFoundException,
540: SQLException {
541: Class.forName(DB_DRIVER);
542: conn = DriverManager.getConnection(DB_CONNECTION);
543: }
544:
545: /**
546: * Print some debug info.
547: *
548: * @param type
549: * @param e
550: */
551: public static void debug(int type, Exception e) {
552: debug(TDS, e.getMessage());
553: e.printStackTrace();
554: System.out.println("\n");
555: }
556:
557: /**
558: * Print some debug info.
559: *
560: * @param type
561: * @param method
562: */
563: public static void debug(int type, String method) {
564: debug(type, method, null);
565: }
566:
567: /**
568: * Print some debug info.
569: *
570: * @param type
571: * @param method
572: * @param value
573: */
574: public static void debug(int type, String method, int value) {
575: debug(type, method, String.valueOf(value));
576: }
577:
578: /**
579: * Print some debug info.
580: *
581: * @param type
582: * @param method
583: * @param value
584: */
585: public static void debug(int type, String method, String value) {
586: if (debugging) {
587: String name = "";
588:
589: if (type == TDS) {
590: name = "TableDataSet";
591: } else {
592: name = "QueryDataSet";
593: }
594:
595: if (value != null) {
596: System.out.print("[" + num++ + "] " + name + " - "
597: + method + " = " + value + "\n");
598: } else {
599: System.out.print("[" + num++ + "] " + name + " - "
600: + method + "\n");
601: }
602:
603: System.out.flush();
604: }
605: }
606: }
|