KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > jimm > datavision > test > QueryTest


1 package jimm.datavision.test;
2 import jimm.datavision.*;
3 import jimm.datavision.source.sql.SQLQuery;
4 import jimm.datavision.layout.CharSepLE;
5 import java.io.*;
6 import junit.framework.TestCase;
7 import junit.framework.TestSuite;
8 import junit.framework.Test;
9
10 /**
11  * Reads a report from an XML file, tests its structure, and tests various
12  * pieces like parameter and formula substitution.
13  *
14  * @author Jim Menard, <a HREF="mailto:jimm@io.com">jimm@io.com</a>
15  */
16 public class QueryTest extends TestCase {
17
18 protected static final File EXAMPLE_REPORT =
19     new File(AllTests.testDataFile("test.xml"));
20 protected static final File PARAMETER_XML_FILE_NAME =
21     new File(AllTests.testDataFile("test_parameters.xml"));
22 protected static final File OUT_FILE =
23     new File(System.getProperty("java.io.tmpdir"),
24          "datavision_query_test_out.txt");
25
26 protected SQLQuery query;
27 protected Report report;
28
29 public static Test suite() {
30     return new TestSuite(QueryTest.class);
31 }
32
33 public QueryTest(String JavaDoc name) {
34     super(name);
35 }
36
37 public void setUp() throws Exception JavaDoc {
38     report = new Report();
39     report.setDatabasePassword("");
40     report.read(EXAMPLE_REPORT); // Must come after setting password
41 report.setParameterXMLInput(PARAMETER_XML_FILE_NAME);
42     query = (SQLQuery)report.getDataSource().getQuery();
43 }
44
45 protected void preparedQueryTest(String JavaDoc whereClause, String JavaDoc answer) {
46     query.setEditableWhereClause(whereClause);
47     String JavaDoc sql = query.toPreparedStatementString();
48     assertTrue("expected \"" + answer + "\" in where clause; sql = " + sql,
49            sql.indexOf(answer) >= 0);
50 }
51
52 public void testQueryRangeSubstitution() {
53     String JavaDoc answer = " jobs.\"ID\" between ? and ?";
54     String JavaDoc notAnswer = " jobs.\"ID\" not between ? and ?";
55
56     preparedQueryTest("{jobs.ID} in {?Number Range}", answer);
57     preparedQueryTest("{jobs.ID} in{?Number Range}", answer);
58     preparedQueryTest("{jobs.ID} between {?Number Range}", answer);
59     preparedQueryTest("{jobs.ID} = {?Number Range}", answer);
60     preparedQueryTest("{jobs.ID}={?Number Range}", answer);
61     preparedQueryTest("{jobs.ID} ={?Number Range}", answer);
62     preparedQueryTest("{jobs.ID}= {?Number Range}", answer);
63
64     preparedQueryTest("{jobs.ID} not in {?Number Range}", notAnswer);
65     preparedQueryTest("{jobs.ID} not in{?Number Range}", notAnswer);
66     preparedQueryTest("{jobs.ID} not between {?Number Range}", notAnswer);
67     preparedQueryTest("{jobs.ID} != {?Number Range}", notAnswer);
68     preparedQueryTest("{jobs.ID}!={?Number Range}", notAnswer);
69     preparedQueryTest("{jobs.ID} !={?Number Range}", notAnswer);
70     preparedQueryTest("{jobs.ID}!= {?Number Range}", notAnswer);
71     preparedQueryTest("{jobs.ID} <> {?Number Range}", notAnswer);
72     preparedQueryTest("{jobs.ID}<>{?Number Range}", notAnswer);
73
74     answer = "jobs.ID between ? and ?";
75     notAnswer = "jobs.ID not between ? and ?";
76
77     preparedQueryTest("jobs.ID in {?Number Range}", answer);
78     preparedQueryTest("jobs.ID in{?Number Range}", answer);
79     preparedQueryTest("jobs.ID between {?Number Range}", answer);
80     preparedQueryTest("jobs.ID = {?Number Range}", answer);
81     preparedQueryTest("jobs.ID={?Number Range}", answer);
82     preparedQueryTest("jobs.ID ={?Number Range}", answer);
83     preparedQueryTest("jobs.ID= {?Number Range}", answer);
84
85     preparedQueryTest("jobs.ID not in {?Number Range}", notAnswer);
86     preparedQueryTest("jobs.ID not in{?Number Range}", notAnswer);
87     preparedQueryTest("jobs.ID not between {?Number Range}", notAnswer);
88     preparedQueryTest("jobs.ID != {?Number Range}", notAnswer);
89     preparedQueryTest("jobs.ID!={?Number Range}", notAnswer);
90     preparedQueryTest("jobs.ID !={?Number Range}", notAnswer);
91     preparedQueryTest("jobs.ID!= {?Number Range}", notAnswer);
92     preparedQueryTest("jobs.ID <> {?Number Range}", notAnswer);
93     preparedQueryTest("jobs.ID<>{?Number Range}", notAnswer);
94 }
95
96 public void testQueryListSubstitution() {
97     String JavaDoc answer = " jobs.\"ID\" in (?,?,?)";
98     String JavaDoc notAnswer = " jobs.\"ID\" not in (?,?,?)";
99
100     preparedQueryTest("{jobs.ID} in {?Pick One}", answer);
101     preparedQueryTest("{jobs.ID} = {?Pick One}", answer);
102     preparedQueryTest("{jobs.ID}={?Pick One}", answer);
103     preparedQueryTest("{jobs.ID} not in {?Pick One}", notAnswer);
104     preparedQueryTest("{jobs.ID} != {?Pick One}", notAnswer);
105     preparedQueryTest("{jobs.ID} <> {?Pick One}", notAnswer);
106
107     answer = "jobs.ID in (?,?,?)";
108     notAnswer = "jobs.ID not in (?,?,?)";
109
110     preparedQueryTest("jobs.ID in {?Pick One}", answer);
111     preparedQueryTest("jobs.ID = {?Pick One}", answer);
112     preparedQueryTest("jobs.ID={?Pick One}", answer);
113     preparedQueryTest("jobs.ID not in {?Pick One}", notAnswer);
114     preparedQueryTest("jobs.ID != {?Pick One}", notAnswer);
115     preparedQueryTest("jobs.ID <> {?Pick One}", notAnswer);
116 }
117
118 protected void displayQueryTest(String JavaDoc whereClause, String JavaDoc answer) {
119     query.setEditableWhereClause(whereClause);
120     String JavaDoc sql = query.toString();
121     assertTrue("expected \"" + answer + "\" in where clause; sql = " + sql,
122            sql.indexOf(answer) >= 0);
123 }
124
125 public void testQueryWhereClauseDisplay() {
126     displayQueryTest("{jobs.ID} < 100", " jobs.\"ID\" < 100");
127     displayQueryTest("{jobs.ID} = {?Number Range}",
128              " jobs.\"ID\" between {?Number Range} and {?Number Range}");
129     displayQueryTest("{office.name}={?String Param}",
130              " office.name = {?String Param}");
131     displayQueryTest("{office.name}!={?String Param}",
132              " office.name != {?String Param}");
133     displayQueryTest("{office.name}<>{?String Param}",
134              " office.name <> {?String Param}");
135     displayQueryTest("{office.name}is{?String Param}",
136              " office.name is {?String Param}");
137     displayQueryTest("{office.name} is not {?String Param}",
138              " office.name is not {?String Param}");
139     displayQueryTest("{office.name}is not{?String Param}",
140              " office.name is not {?String Param}");
141 }
142
143 public void testQueryDateParam() {
144     preparedQueryTest("jobs.post_date >= {?Date}", "jobs.post_date >= ?");
145     preparedQueryTest("{jobs.post_date} >= {?Date}", " jobs.post_date >= ?");
146 }
147
148 public void testQueryUserColPrep() {
149     String JavaDoc answer = "substr( jobs.title , 1, 8)";
150     UserColumn uc = new UserColumn(null, report, "my user col",
151                    "substr({jobs.title}, 1, 8)");
152     assertEquals(answer, uc.getSelectString(query));
153 }
154
155 public void testWhereClauseContainsParam() {
156     Parameter p = report.findParameter("1");
157     assertNotNull(p);
158
159     // Make sure we're not imagining things.
160 query.setEditableWhereClause("");
161     assertTrue(!query.containsReferenceTo(p));
162
163     // It's easy to find this one.
164 query.setEditableWhereClause("{office.name} = {?String Param}");
165     assertTrue(query.containsReferenceTo(p));
166
167     // Create a formula that refers to the parameter.
168 Formula f = report.findFormula("3");
169     assertNotNull(f);
170     f.setEditableExpression("{?String Param}");
171     assertTrue(f.refersTo(p));
172
173     // Put the formula into the where clause, then look for it. Don't let
174 // the formula name decieve you (it's "contains usercol").
175 query.setEditableWhereClause("{office.name} = " + f.designLabel());
176     assertTrue(query.containsReferenceTo(p));
177 }
178
179 public void testManualParameter() throws Exception JavaDoc {
180     report = new Report();
181     report.setDatabasePassword("");
182     report.read(EXAMPLE_REPORT); // Must come after setting password
183 // Do not call report.setParameterXMLFile()
184
185     Parameter p = report.findParameter("1");
186     assertNotNull(p);
187
188     query = (SQLQuery)report.getDataSource().getQuery();
189     query.setEditableWhereClause("office.name = {?String Param}");
190
191     report.parametersSetManually(true);
192     p.setValue(0, "Chicago");
193
194     OUT_FILE.deleteOnExit();
195     PrintWriter out = new PrintWriter(new FileWriter(OUT_FILE));
196     report.setLayoutEngine(new CharSepLE(out, '\t'));
197
198     try {
199     report.runReport();
200     }
201     catch (Exception JavaDoc e) {
202     e.printStackTrace();
203     fail("Exception seen: " + e);
204     }
205 }
206
207 public static void main(String JavaDoc[] args) {
208     junit.textui.TestRunner.run(suite());
209     System.exit(0);
210 }
211
212 }
213
Popular Tags