001: package jimm.datavision.test;
002:
003: import jimm.datavision.*;
004: import jimm.datavision.source.sql.SQLQuery;
005: import jimm.datavision.layout.CharSepLE;
006: import java.io.*;
007: import junit.framework.TestCase;
008: import junit.framework.TestSuite;
009: import junit.framework.Test;
010:
011: /**
012: * Reads a report from an XML file, tests its structure, and tests various
013: * pieces like parameter and formula substitution.
014: *
015: * @author Jim Menard, <a href="mailto:jimm@io.com">jimm@io.com</a>
016: */
017: public class QueryTest extends TestCase {
018:
019: protected static final File EXAMPLE_REPORT = new File(AllTests
020: .testDataFile("test.xml"));
021: protected static final File PARAMETER_XML_FILE_NAME = new File(
022: AllTests.testDataFile("test_parameters.xml"));
023: protected static final File OUT_FILE = new File(System
024: .getProperty("java.io.tmpdir"),
025: "datavision_query_test_out.txt");
026:
027: protected SQLQuery query;
028: protected Report report;
029:
030: public static Test suite() {
031: return new TestSuite(QueryTest.class);
032: }
033:
034: public QueryTest(String name) {
035: super (name);
036: }
037:
038: public void setUp() throws Exception {
039: report = new Report();
040: report.setDatabasePassword("");
041: report.read(EXAMPLE_REPORT); // Must come after setting password
042: report.setParameterXMLInput(PARAMETER_XML_FILE_NAME);
043: query = (SQLQuery) report.getDataSource().getQuery();
044: }
045:
046: protected void preparedQueryTest(String whereClause, String answer) {
047: query.setEditableWhereClause(whereClause);
048: String sql = query.toPreparedStatementString();
049: assertTrue("expected \"" + answer
050: + "\" in where clause; sql = " + sql, sql
051: .indexOf(answer) >= 0);
052: }
053:
054: public void testQueryRangeSubstitution() {
055: String answer = " jobs.\"ID\" between ? and ?";
056: String notAnswer = " jobs.\"ID\" not between ? and ?";
057:
058: preparedQueryTest("{jobs.ID} in {?Number Range}", answer);
059: preparedQueryTest("{jobs.ID} in{?Number Range}", answer);
060: preparedQueryTest("{jobs.ID} between {?Number Range}", answer);
061: preparedQueryTest("{jobs.ID} = {?Number Range}", answer);
062: preparedQueryTest("{jobs.ID}={?Number Range}", answer);
063: preparedQueryTest("{jobs.ID} ={?Number Range}", answer);
064: preparedQueryTest("{jobs.ID}= {?Number Range}", answer);
065:
066: preparedQueryTest("{jobs.ID} not in {?Number Range}", notAnswer);
067: preparedQueryTest("{jobs.ID} not in{?Number Range}", notAnswer);
068: preparedQueryTest("{jobs.ID} not between {?Number Range}",
069: notAnswer);
070: preparedQueryTest("{jobs.ID} != {?Number Range}", notAnswer);
071: preparedQueryTest("{jobs.ID}!={?Number Range}", notAnswer);
072: preparedQueryTest("{jobs.ID} !={?Number Range}", notAnswer);
073: preparedQueryTest("{jobs.ID}!= {?Number Range}", notAnswer);
074: preparedQueryTest("{jobs.ID} <> {?Number Range}", notAnswer);
075: preparedQueryTest("{jobs.ID}<>{?Number Range}", notAnswer);
076:
077: answer = "jobs.ID between ? and ?";
078: notAnswer = "jobs.ID not between ? and ?";
079:
080: preparedQueryTest("jobs.ID in {?Number Range}", answer);
081: preparedQueryTest("jobs.ID in{?Number Range}", answer);
082: preparedQueryTest("jobs.ID between {?Number Range}", answer);
083: preparedQueryTest("jobs.ID = {?Number Range}", answer);
084: preparedQueryTest("jobs.ID={?Number Range}", answer);
085: preparedQueryTest("jobs.ID ={?Number Range}", answer);
086: preparedQueryTest("jobs.ID= {?Number Range}", answer);
087:
088: preparedQueryTest("jobs.ID not in {?Number Range}", notAnswer);
089: preparedQueryTest("jobs.ID not in{?Number Range}", notAnswer);
090: preparedQueryTest("jobs.ID not between {?Number Range}",
091: notAnswer);
092: preparedQueryTest("jobs.ID != {?Number Range}", notAnswer);
093: preparedQueryTest("jobs.ID!={?Number Range}", notAnswer);
094: preparedQueryTest("jobs.ID !={?Number Range}", notAnswer);
095: preparedQueryTest("jobs.ID!= {?Number Range}", notAnswer);
096: preparedQueryTest("jobs.ID <> {?Number Range}", notAnswer);
097: preparedQueryTest("jobs.ID<>{?Number Range}", notAnswer);
098: }
099:
100: public void testQueryListSubstitution() {
101: String answer = " jobs.\"ID\" in (?,?,?)";
102: String notAnswer = " jobs.\"ID\" not in (?,?,?)";
103:
104: preparedQueryTest("{jobs.ID} in {?Pick One}", answer);
105: preparedQueryTest("{jobs.ID} = {?Pick One}", answer);
106: preparedQueryTest("{jobs.ID}={?Pick One}", answer);
107: preparedQueryTest("{jobs.ID} not in {?Pick One}", notAnswer);
108: preparedQueryTest("{jobs.ID} != {?Pick One}", notAnswer);
109: preparedQueryTest("{jobs.ID} <> {?Pick One}", notAnswer);
110:
111: answer = "jobs.ID in (?,?,?)";
112: notAnswer = "jobs.ID not in (?,?,?)";
113:
114: preparedQueryTest("jobs.ID in {?Pick One}", answer);
115: preparedQueryTest("jobs.ID = {?Pick One}", answer);
116: preparedQueryTest("jobs.ID={?Pick One}", answer);
117: preparedQueryTest("jobs.ID not in {?Pick One}", notAnswer);
118: preparedQueryTest("jobs.ID != {?Pick One}", notAnswer);
119: preparedQueryTest("jobs.ID <> {?Pick One}", notAnswer);
120: }
121:
122: protected void displayQueryTest(String whereClause, String answer) {
123: query.setEditableWhereClause(whereClause);
124: String sql = query.toString();
125: assertTrue("expected \"" + answer
126: + "\" in where clause; sql = " + sql, sql
127: .indexOf(answer) >= 0);
128: }
129:
130: public void testQueryWhereClauseDisplay() {
131: displayQueryTest("{jobs.ID} < 100", " jobs.\"ID\" < 100");
132: displayQueryTest("{jobs.ID} = {?Number Range}",
133: " jobs.\"ID\" between {?Number Range} and {?Number Range}");
134: displayQueryTest("{office.name}={?String Param}",
135: " office.name = {?String Param}");
136: displayQueryTest("{office.name}!={?String Param}",
137: " office.name != {?String Param}");
138: displayQueryTest("{office.name}<>{?String Param}",
139: " office.name <> {?String Param}");
140: displayQueryTest("{office.name}is{?String Param}",
141: " office.name is {?String Param}");
142: displayQueryTest("{office.name} is not {?String Param}",
143: " office.name is not {?String Param}");
144: displayQueryTest("{office.name}is not{?String Param}",
145: " office.name is not {?String Param}");
146: }
147:
148: public void testQueryDateParam() {
149: preparedQueryTest("jobs.post_date >= {?Date}",
150: "jobs.post_date >= ?");
151: preparedQueryTest("{jobs.post_date} >= {?Date}",
152: " jobs.post_date >= ?");
153: }
154:
155: public void testQueryUserColPrep() {
156: String answer = "substr( jobs.title , 1, 8)";
157: UserColumn uc = new UserColumn(null, report, "my user col",
158: "substr({jobs.title}, 1, 8)");
159: assertEquals(answer, uc.getSelectString(query));
160: }
161:
162: public void testWhereClauseContainsParam() {
163: Parameter p = report.findParameter("1");
164: assertNotNull(p);
165:
166: // Make sure we're not imagining things.
167: query.setEditableWhereClause("");
168: assertTrue(!query.containsReferenceTo(p));
169:
170: // It's easy to find this one.
171: query.setEditableWhereClause("{office.name} = {?String Param}");
172: assertTrue(query.containsReferenceTo(p));
173:
174: // Create a formula that refers to the parameter.
175: Formula f = report.findFormula("3");
176: assertNotNull(f);
177: f.setEditableExpression("{?String Param}");
178: assertTrue(f.refersTo(p));
179:
180: // Put the formula into the where clause, then look for it. Don't let
181: // the formula name decieve you (it's "contains usercol").
182: query.setEditableWhereClause("{office.name} = "
183: + f.designLabel());
184: assertTrue(query.containsReferenceTo(p));
185: }
186:
187: public void testManualParameter() throws Exception {
188: report = new Report();
189: report.setDatabasePassword("");
190: report.read(EXAMPLE_REPORT); // Must come after setting password
191: // Do not call report.setParameterXMLFile()
192:
193: Parameter p = report.findParameter("1");
194: assertNotNull(p);
195:
196: query = (SQLQuery) report.getDataSource().getQuery();
197: query.setEditableWhereClause("office.name = {?String Param}");
198:
199: report.parametersSetManually(true);
200: p.setValue(0, "Chicago");
201:
202: OUT_FILE.deleteOnExit();
203: PrintWriter out = new PrintWriter(new FileWriter(OUT_FILE));
204: report.setLayoutEngine(new CharSepLE(out, '\t'));
205:
206: try {
207: report.runReport();
208: } catch (Exception e) {
209: e.printStackTrace();
210: fail("Exception seen: " + e);
211: }
212: }
213:
214: public static void main(String[] args) {
215: junit.textui.TestRunner.run(suite());
216: System.exit(0);
217: }
218:
219: }
|