|
1
21
22 package com.jaspersoft.jasperserver.api.metadata;
23
24 import java.io.BufferedInputStream  ;
25 import java.io.BufferedReader ;
26 import java.io.FileInputStream ;
27 import java.io.FileNotFoundException ;
28 import java.io.IOException ;
29 import java.io.InputStream ;
30 import java.io.InputStreamReader ;
31 import java.util.Properties ;
32 import java.util.Date ;
33
34 import org.apache.commons.logging.Log;
35 import org.apache.commons.logging.LogFactory;
36
37 import junit.framework.TestCase;
38 import junit.textui.TestRunner;
39
40 import org.springframework.orm.hibernate3.HibernateTemplate;
41 import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
42 import org.springframework.context.support.ClassPathXmlApplicationContext;
43
44 import com.jaspersoft.jasperserver.api.metadata.common.domain.Folder;
45 import com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoFolder;
46 import com.jaspersoft.jasperserver.api.metadata.common.service.RepositoryService;
47 import com.jaspersoft.jasperserver.api.metadata.common.domain.FileResourceData;
48 import com.jaspersoft.jasperserver.api.metadata.common.domain.Resource;
49 import com.jaspersoft.jasperserver.api.metadata.common.domain.FileResource;
50 import com.jaspersoft.jasperserver.api.metadata.common.domain.ResourceLookup;
51 import com.jaspersoft.jasperserver.api.metadata.common.domain.ResourceReference;
52 import com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JdbcReportDataSource;
53 import com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JndiJdbcReportDataSource;
54 import com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.ReportDataSource;
55 import com.jaspersoft.jasperserver.api.metadata.olap.domain.MondrianConnection;
56 import com.jaspersoft.jasperserver.api.metadata.olap.domain.OlapClientConnection;
57 import com.jaspersoft.jasperserver.api.metadata.olap.domain.OlapUnit;
58 import com.jaspersoft.jasperserver.api.metadata.olap.domain.MondrianXMLADefinition;
59 import com.jaspersoft.jasperserver.api.metadata.olap.domain.XMLAConnection;
60 import com.jaspersoft.jasperserver.api.metadata.olap.service.OlapConnectionService;
61 import com.jaspersoft.jasperserver.api.metadata.olap.service.impl.OlapConnectionServiceImpl;
62 import com.jaspersoft.jasperserver.api.metadata.view.domain.FilterCriteria;
63 import com.jaspersoft.jasperserver.api.common.domain.ExecutionContext;
64 import com.jaspersoft.jasperserver.api.common.domain.ValidationResult;
65 import com.jaspersoft.jasperserver.api.common.domain.impl.ValidationDetailImpl;
66 import com.jaspersoft.jasperserver.api.common.domain.impl.ValidationResultImpl;
67
68 import mondrian.olap.DriverManager;
69 import com.tonbeller.jpivot.olap.model.OlapModel;
70 import com.tonbeller.jpivot.olap.model.OlapException;
71 import com.tonbeller.jpivot.olap.model.Result;
72 import com.tonbeller.jpivot.mondrian.MondrianResult;
73
74 import com.jaspersoft.jasperserver.api.metadata.xml.domain.impl.ResourceDescriptor;
75
76
79
80 public class OlapConnectionTest extends TestCase {
81
82 private static final Log log = LogFactory.getLog(OlapConnectionTest.class);
83
84 protected final String foodMartSchemaURI = "/queries/FoodMart.xml";
85 protected final String sugarCRMSchemaURI = "/queries/SugarCRMOpps.xml";
86
87 private Properties jdbcProps;
88
89 public static void main(String [] args) {
90 TestRunner.run(OlapConnectionTest.class);
91 }
92
93 protected void setUp() throws Exception {
94
95 ClassPathXmlApplicationContext appContext
96 = new ClassPathXmlApplicationContext(new String [] {"hibernateConfig.xml", "viewService.xml"});
97
98 setRepository( (RepositoryService)appContext.getBean("repoService") );
99 setConnectionService( (OlapConnectionService)appContext.getBean("olapConnectionService") );
100
101 loadJdbcProps();
102 }
103
104 protected Properties loadJdbcProps() throws IOException , FileNotFoundException {
105 jdbcProps = new Properties ();
106 String jdbcPropFile = System.getProperty("test.hibernate.jdbc.properties");
107 BufferedInputStream is = new BufferedInputStream (new FileInputStream (jdbcPropFile));
108 jdbcProps.load(is);
109 is.close();
110 return jdbcProps;
111 }
112
113 protected void createFoodmartSchemaResource() {
114 RepositoryService rep = getRepository();
115 FileResource schemaResource = (FileResource) rep.newResource( null, FileResource.class );
116 schemaResource.setName( "FoodmartSchema" );
117 schemaResource.setLabel( "Foodmart Schema" );
118 schemaResource.setDescription( "OLAP Foodmart Schema" );
119 schemaResource.setFileType(ResourceDescriptor.TYPE_MONDRIAN_SCHEMA);
120 InputStream in = getClass().getResourceAsStream( foodMartSchemaURI );
121 schemaResource.readData(in);
122 getConnectionService().saveResource( null, "/olap/schemas/", schemaResource );
123 }
124
125 protected void createSugarCRMSchemaResource() {
126 RepositoryService rep = getRepository();
127 FileResource schemaResource = (FileResource) rep.newResource( null, FileResource.class );
128 schemaResource.setName( "SugarCRMOppsSchema" );
129 schemaResource.setLabel( "SugarCRM Opportunities DataMart" );
130 schemaResource.setDescription( "SugarCRM Opportunities Data Mart Schema" );
131 schemaResource.setFileType(ResourceDescriptor.TYPE_MONDRIAN_SCHEMA);
132 InputStream in = getClass().getResourceAsStream( sugarCRMSchemaURI );
133 schemaResource.readData(in);
134 getConnectionService().saveResource(null, "/olap/schemas/", schemaResource );
135 }
136
137 protected void createFoodmartDataSourceResource() {
138 RepositoryService rep = getRepository();
139 JdbcReportDataSource dataSourceResource
140 = (JdbcReportDataSource) rep.newResource( null, JdbcReportDataSource.class );
141 dataSourceResource.setName( "FoodmartDataSource" );
142 dataSourceResource.setLabel( "Foodmart Data Source" );
143 dataSourceResource.setDescription( "Foodmart Data Source" );
144 dataSourceResource.setDriverClass(jdbcProps.getProperty("foodmart.jdbc.driverClassName"));
145 dataSourceResource.setConnectionUrl(jdbcProps.getProperty("foodmart.jdbc.url"));
146 dataSourceResource.setUsername(jdbcProps.getProperty("foodmart.jdbc.username"));
147 dataSourceResource.setPassword(jdbcProps.getProperty("foodmart.jdbc.password"));
148 getConnectionService().saveResource( null, "/olap/datasources/", dataSourceResource );
149 }
150
151 protected void createSugarCRMDataSourceResource() {
152 RepositoryService rep = getRepository();
153 JdbcReportDataSource dataSourceResource
154 = (JdbcReportDataSource) rep.newResource( null, JdbcReportDataSource.class );
155 dataSourceResource.setName( "SugarCRMDataSource" );
156 dataSourceResource.setLabel( "SugarCRM Data Source" );
157 dataSourceResource.setDescription( "SugarCRM Data Source" );
158 dataSourceResource.setDriverClass(jdbcProps.getProperty("test.jdbc.driverClassName"));
159 dataSourceResource.setConnectionUrl(jdbcProps.getProperty("test.jdbc.url"));
160 dataSourceResource.setUsername(jdbcProps.getProperty("test.jdbc.username"));
161 dataSourceResource.setPassword(jdbcProps.getProperty("test.jdbc.password"));
162 getConnectionService().saveResource( null, "/olap/datasources/", dataSourceResource );
163 }
164
165 protected void createFoodmartMondrianConnectionResource() {
166 RepositoryService rep = getRepository();
167 MondrianConnection connection = (MondrianConnection) rep.newResource( null, MondrianConnection.class );
168 connection.setName("Foodmart");
169 connection.setLabel("Foodmart Mondrian Connection");
170 connection.setDescription("Foodmart Olap Connection");
171 connection.setSchemaReference("/olap/schemas/FoodmartSchema");
172 connection.setDataSourceReference("/olap/datasources/FoodmartDataSource");
173 getConnectionService().saveResource( null, "/olap/connections/", connection );
174 }
175
176 protected void createSugarCRMMondrianConnectionResource() {
177 RepositoryService rep = getRepository();
178 MondrianConnection connection = (MondrianConnection) rep.newResource( null, MondrianConnection.class );
179 connection.setName("SugarCRM");
180 connection.setLabel("SugarCRM OLAP Connection");
181 connection.setDescription("SugarCRM OLAP Connection: only opportunities");
182 connection.setSchemaReference("/olap/schemas/SugarCRMOppsSchema");
183 connection.setDataSourceReference("/datasources/JServerJNDIDS"); getConnectionService().saveResource(null, "/olap/connections/", connection );
185 }
186
187 protected void createSugarCRMTestMondrianConnectionResource() {
188 RepositoryService rep = getRepository();
189 MondrianConnection connection = (MondrianConnection) rep.newResource( null, MondrianConnection.class );
190 connection.setName("SugarCRMTest");
191 connection.setLabel("SugarCRM OLAP Connection");
192 connection.setDescription("SugarCRM OLAP Connection: only opportunities");
193 connection.setSchemaReference("/olap/schemas/SugarCRMOppsSchema");
194 connection.setDataSourceReference("/olap/datasources/SugarCRMDataSource");
195 getConnectionService().saveResource(null, "/olap/connections/", connection );
196 }
197
198 protected void createFoodmartXMLAConnectionResource() {
199 RepositoryService rep = getRepository();
200 XMLAConnection connection = (XMLAConnection) rep.newResource( null, XMLAConnection.class );
201 connection.setName("FoodmartXmlaConnection");
202 connection.setLabel("Foodmart XMLA Connection");
203 connection.setDescription("Foodmart XML/A Connection");
204 connection.setCatalog("Foodmart");
205 connection.setDataSource("Provider=Mondrian;DataSource=Foodmart;");
206 connection.setURI("http://localhost:8080/jasperserver/xmla");
207 connection.setUsername("tomcat");
209 connection.setPassword("tomcat");
210 getConnectionService().saveResource( null, "/olap/connections/", connection );
211 }
212
213 protected void createSugarCRMXMLAConnectionResource() {
214 RepositoryService rep = getRepository();
215 XMLAConnection connection = (XMLAConnection) rep.newResource( null, XMLAConnection.class );
216 connection.setName("SugarCRMXmlaConnection");
217 connection.setLabel("SugarCRM XMLA Connection");
218 connection.setDescription("SugarCRM XML/A Connection");
219 connection.setCatalog("SugarCRM");
220 connection.setDataSource("Provider=Mondrian;DataSource=SugarCRM;");
221 connection.setURI("http://localhost:8080/jasperserver/xmla");
222 connection.setUsername("tomcat");
224 connection.setPassword("tomcat");
225 getConnectionService().saveResource(null, "/olap/connections/", connection );
226 }
227
228 protected void createFoodmartMondrianXMLADefinitionResource() {
229 RepositoryService rep = getRepository();
230 MondrianXMLADefinition def = (MondrianXMLADefinition) rep.newResource( null, MondrianXMLADefinition.class );
231 def.setName("FoodmartXmlaDefinition");
232 def.setLabel("Foodmart XMLA Connection");
233 def.setDescription("Foodmart XML/A Connection");
234 def.setCatalog("Foodmart");
235 def.setMondrianConnectionReference("/olap/connections/Foodmart");
236
237 getConnectionService().saveResource( null, "/olap/xmla/definitions/", def );
238 }
239
240 protected void createSugarCRMMondrianXMLADefinitionResource() {
241 RepositoryService rep = getRepository();
242 MondrianXMLADefinition def = (MondrianXMLADefinition) rep.newResource( null, MondrianXMLADefinition.class );
243 def.setName("SugarCRMXmlaDefinition");
244 def.setLabel("SugarCRM XMLA Connection");
245 def.setDescription("SugarCRM XML/A Connection");
246 def.setCatalog("SugarCRM");
247 def.setMondrianConnectionReference("/olap/connections/SugarCRMTest");
248
249 getConnectionService().saveResource( null, "/olap/xmla/definitions/", def );
250 }
251
252 private String SAMPLE_FOODMART_MDX_QUERY =
253 "SELECT {[Measures].[Unit Sales]} ON COLUMNS, " +
254 "{[Time].[1997].[Q1]} ON ROWS FROM [Sales]";
255
256
257 protected void createFoodmartOlapUnit() {
258 RepositoryService rep = getRepository();
259 OlapUnit view = (OlapUnit) rep.newResource( null, OlapUnit.class );
260 view.setName("Foodmart_sample_unit_1");
261 view.setLabel("Foodmart Sample Olap Unit 1");
262 view.setDescription("Sample Olap Unit 1: 1997 Q1 Foodmart Unit Sales");
263 view.setOlapClientConnectionReference("/olap/connections/Foodmart");
264 view.setMdxQuery(SAMPLE_FOODMART_MDX_QUERY);
265 getConnectionService().saveResource( null, "/olap/views/", view );
266 OlapUnit cycledView = (OlapUnit)rep.getResource(null,
267 "/olap/views/Foodmart_sample_unit_1");
268
271 }
276
277 protected void createSugarCRMXmlaOlapUnit() {
278 RepositoryService rep = getRepository();
279 OlapUnit view = (OlapUnit) rep.newResource( null, OlapUnit.class );
280 view.setName("SugarCRM_xmla_sample_unit_2");
281 view.setLabel("SugarCRM Sample XMLA Olap Unit 2");
282 view.setDescription("Sample XMLA Olap Unit 2: 1997 Q1 SugarCRM Unit Sales");
283 view.setOlapClientConnectionReference("/olap/connections/SugarCRMXmlaConnection");
284 view.setMdxQuery(SAMPLE_SUGAR_CRM_MDX_QUERY);
285 getConnectionService().saveResource( null, "/olap/views/", view );
286 }
287
288 private String SAMPLE_SUGAR_CRM_MDX_QUERY =
289 "select {[Measures].[Total Sale Amount], [Measures].[Number of Sales], [Measures].[Avg Sale Amount], [Measures].[Avg Time To Close (Days)], [Measures].[Avg Close Probablility]} ON COLUMNS, " +
290 " NON EMPTY {([Account Categorization].[All Accounts], [Close Period].[All Periods])} ON ROWS " +
291 " from [SalesAnalysis] " +
292 " where [Sale State].[All Types].[Closed Won]";
293
294 protected void createSugarCRMOlapUnit() {
295 RepositoryService rep = getRepository();
296 OlapUnit view = (OlapUnit) rep.newResource( null, OlapUnit.class );
297 view.setName("SugarCRM_sample_unit_1");
298 view.setLabel("SugarCRM sample unit 1");
299 view.setDescription("SugarCRM Sample Olap Unit 1: Sales Performance by Industry/Account");
300 view.setOlapClientConnectionReference("/olap/connections/SugarCRM");
301 view.setMdxQuery(SAMPLE_SUGAR_CRM_MDX_QUERY);
302 getConnectionService().saveResource(null, "/olap/views/", view );
303 }
311
312 protected void createSugarCRMTestOlapUnit() {
313 RepositoryService rep = getRepository();
314 OlapUnit view = (OlapUnit) rep.newResource( null, OlapUnit.class );
315 view.setName("SugarCRM_sample_unit_2");
316 view.setLabel("SugarCRM sample unit 2");
317 view.setDescription("SugarCRM Sample Olap Unit 2: Sales Performance by Industry/Account");
318 view.setOlapClientConnectionReference("/olap/connections/SugarCRMTest");
319 view.setMdxQuery(SAMPLE_SUGAR_CRM_MDX_QUERY);
320 getConnectionService().saveResource(null, "/olap/views/", view );
321 OlapUnit cycledView = (OlapUnit)rep.getResource(null,
322 "/olap/views/SugarCRM_sample_unit_2");
323 assertTrue( "validation of SugarCRM Olap Unit failed",
326 getConnectionService().validate(null, cycledView).
327 getValidationState() != ValidationResult.STATE_ERROR );
328 }
329
330 protected void executeSampleFoodmartQuery() {
331 assertQueryShape( "/olap/connections/Foodmart",
332 SAMPLE_FOODMART_MDX_QUERY,
333 "", 0, 0);
334 }
335
336 protected void executeSampleSugarQuery() {
337 assertQueryShape( "/olap/connections/SugarCRM",
338 SAMPLE_SUGAR_CRM_MDX_QUERY,
339 "Total Sale Amount", 2, 5);
340 }
341
342 protected void executeSampleSugarTestQuery() {
343 assertQueryShape( "/olap/connections/SugarCRMTest",
344 SAMPLE_SUGAR_CRM_MDX_QUERY,
345 "Total Sale Amount", 2, 5);
346 }
347
348 protected void assertQueryShape( String connResourceName,
349 String mdxQuery,
350 String expectedCaption,
351 int expectedNumAxes,
352 int expectedNumPositions) {
353 mondrian.olap.Connection monConnection
354 = ((OlapConnectionServiceImpl)getConnectionService())
355 .getMondrianConnection( null, connResourceName );
356
357 mondrian.olap.Query monQuery = monConnection.parseQuery(mdxQuery);
359 mondrian.olap.Result monResult = monConnection.execute(monQuery);
360 mondrian.olap.Axis[] axes = monResult.getAxes();
361 mondrian.olap.Position[] positions = axes[0].positions;
362 mondrian.olap.Member m0 = positions[0].members[0];
363 String caption = m0.getCaption();
364
365 log.debug("caption = " + caption + ", " +
366 "axes.length=" + axes.length + ", " +
367 "positions.length= " + positions.length);
368
369 assertEquals( caption, expectedCaption );
370 assertEquals( axes.length, expectedNumAxes );
371 assertEquals( positions.length, expectedNumPositions );
372 }
373
374 public void testOlapConnection() {
375 createFoodmartSchemaResource();
377 createSugarCRMSchemaResource();
378 createFoodmartDataSourceResource();
379 createSugarCRMDataSourceResource();
380 createFoodmartMondrianConnectionResource();
381 createFoodmartXMLAConnectionResource();
382 createSugarCRMMondrianConnectionResource();
383 createSugarCRMTestMondrianConnectionResource();
384 createSugarCRMXMLAConnectionResource();
385 createFoodmartOlapUnit();
386 createSugarCRMOlapUnit();
387
388 createSugarCRMTestOlapUnit();
389 createSugarCRMXmlaOlapUnit();
390 createFoodmartMondrianXMLADefinitionResource();
391 createSugarCRMMondrianXMLADefinitionResource();
392
393 executeSampleSugarTestQuery();
397
398 OlapModel sampleSugarModel2 = getModel( "SugarCRM_sample_unit_2" );
402 OlapModel sampleSugarXmlaModel2 = getModel( "SugarCRM_xmla_sample_unit_2" );
403
404 assertOlapModelResult( sampleSugarModel2, 5 );
406
407
411 }
413
414 private void assertOlapModelResult( OlapModel m, int expectedNumCells ) {
415 try {
416 m.initialize();
417 Result r = m.getResult();
418 log.info( "cell count = " + r.getCells().size() );
419 assertNotNull( r );
420 assertTrue( "sample query returned " + r.getCells().size() +
421 " cells, when expecting " + expectedNumCells,
422 r.getCells().size() == expectedNumCells );
423 } catch (OlapException e) {
424 log.error(e);
425 throw new RuntimeException (e);
426 }
427 }
428
429 public void testOlapUnitFind() {
430 ResourceLookup[] results
431 = getRepository().findResource(null, FilterCriteria.createFilter(OlapUnit.class));
432 assertTrue("got results: " + results.length + " when expecting 4", results.length == 4);
433 }
434
435 private OlapModel getModel( String viewName ) {
436 RepositoryService rep = getRepository();
437 OlapUnit unit = (OlapUnit)rep.getResource( null, "/olap/views/" + viewName );
438 return getConnectionService().createOlapModel( null, unit );
439 }
440
441
444 private RepositoryService mRepository;
445 public RepositoryService getRepository() {
446 return mRepository;
447 }
448 public void setRepository(RepositoryService repository) {
449 mRepository = repository;
450 }
451
452
455 private OlapConnectionService mConnectionService;
456 public OlapConnectionService getConnectionService() {
457 return mConnectionService;
458 }
459 public void setConnectionService( OlapConnectionService cs ) {
460 mConnectionService = cs;
461 }
462
463 }
464 |
Popular Tags |
Java API By Example, From Geeks To Geeks.