Building with Postgresql

[mpenningroth]

I have been trying to get Jasperintelligence to build with Postgresql as its repository.

 

I am able to run the three build steps agains mysql just fine.

 

I then got the build (parts one and two) to work with Postgresql.

 

The unit tests are failing however (6 failures and 9 errors).

 

The first error is in the Tear down HibernateAccessTest.

 

I turned on the hibernate logging and I don't understand what the issue is.

 

I have attached the output from both a mysql run and the postgresql run.

 

Seems like the jndiName is not being set properly.

 

Any ideas?

 

(BTW after hacking my way through some tomcat configuration files, I am able to connect to jasperserver with postgresql as the repository. I am hitting a problem in that postgresql can't deal with blobs in autocommit mode though. Anothe topic...)

 

I have a word document that has some of the issues I found when I was doing the build. Just let me know if you want it... [file name=teardownprob.txt size=81722]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/teardownprob.txt[/file]

[swood]

Hi Mark!

 

There was a patch done for JI 1.0.1 at http://jasperforge.org/sf/go/artf1206?nav=1. It may help to look at that.

 

It looks like your problems are in the settings.xml configuration and related files.

 

Attached are some config files I have used to generate for Postgres.

 

Here is a section from the Maven settings.xml that uses those files:

 

Code:

       	 <test.hibernate.cfg>C:/Docume~1/swood/postgres.hibernate.cfg.xml</test.hibernate.cfg>
       	 <test.hibernate.jdbc.properties>C:/Docume~1/swood/postgres.jdbc.properties</test.hibernate.jdbc.properties>
       	 <!-- When you run the metadata repository DDL generation, do you want the
       	      database to be dropped and recreated?					-->
       	 <metadata.database.generate>true</metadata.database.generate>
	 <repository.database.driver.groupId>postgresql</repository.database.driver.groupId>
	 <repository.database.driver.artifactId>postgresql</repository.database.driver.artifactId>
	 <repository.database.driver.version>8.1-404.jdbc3</repository.database.driver.version>
	 	 <js.quartz.script>C:/Docume~1/swood/My Documents/workspace/jasperserver-trunk/scripts/quartz/tables_mysql_innodb.sql</js.quartz.script>

 

As you found, Postgres needs to read blobs in a transaction, which MySQL and other databases do not. I think the fix will be to change the metadata repository data source in the Spring configurations.

 

In WEB-INF/applicationContext.xml, try changing the XX bean definition as follows - drop the readOnly configurations on get* and find*:

 

 

Code:

[code] <bean id="hibernateRepoServiceTransactionInterceptor" class="org.springframework.transaction.interceptor.TransactionInterceptor"> <property name="transactionManager" ref="transactionManager"/> <property name="transactionAttributes"> <props> <prop key="get*">PROPAGATION_REQUIRED</prop> <prop key="find*">PROPAGATION_REQUIRED</prop> <prop key="createFolder">PROPAGATION_REQUIRED</prop> <prop key="save*">PROPAGATION_REQUIRED</prop> <prop key="delete*">PROPAGATION_REQUIRED</prop> <prop key="*">PROPAGATION_SUPPORTS</prop> </props> </property> </bean>

 

 

Let me know how you go.

 

 

Sherman

JasperSoft [file name=postgres.txt size=2134]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/postgres.txt[/file]

Post edited by: swood, at: 2006/10/21 20:33

[mpenningroth]

Thanks for the suggestions Sherman.

 

I had already changed my Maven settings to use my postgres database.

The SQL generated by the hibernate mapping tool (attached) looks very different than the SQL contained in the above patch. The sql in the patch does not have any blob (bytea in postgres) columns. They have all bee replaced with oid . The sql also does not make use of the postgres serial columns, which could come into play I guess. I will attempt to hand modify the sql that the hibernate mapping tool creates to see if I can make any progress. [file name=jasperserverCreate_ddl.txt size=20035]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/jasperserverCreate_ddl.txt[/file]

Post edited by: mpenningroth, at: 2006/10/23 13:12

[swood]

It would be better use the database script generated from the 1.1 source, as you have done.

 

Did you try changing the bean definitions like I outlined?

 

 

Sherman

JasperSoft

[mpenningroth]

Things are looking better. I made the changes to the bean definitions, and I also modified a couple of the other .xml .properties files under jasperserver-war .

I can now run the unit tests without errors, but I am still having some problems with the webapps. Do the unit tests rely on the config files under the jasperserve-war directory? The blob pproblem is gone, but I think I have a sugarcrm config problem. I am running with foodmart and jasper repository under postgres, and the mysql sugarcrm.

Thanks for your help. I plan to go back through the process and make sure I identify all the steps I had to do to the original build. I will make those avaiable here when I am done.:)

[swood]

If the unit tests run, the metadata repository should be fine. Can you post the error you are getting related to SugarCRM?

 

 

Sherman

JasperSoft [file name=postgres-2f2981d8bc9d63f48e2ce0db9330f865.txt size=2116]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/postgres-2f2981d8bc9d63f48e2ce0db9330f865.txt[/file]

[mpenningroth]

It is working now. I was using a different tomcat image and I had failed to put the mysql driver in the common/lib folder.

 

OK I am still having problems with blobs in postgres.

 

The readonly property on the beans do not make a difference.

Post edited by: mpenningroth, at: 2006/10/25 14:49

[mpenningroth]

I was finally able to build and run on postgres 8.1 .

 

Please see the readme.txt in the attached zip file for what I had to do to get this to work.

 

I haven't really done much with the system yet, but it does run through the unit tests, and the samples in the web app works the same as the mysql one.

 

Regards,

Mark

 

Woops. My zipfile is too large. Let me know if you want it!

Post edited by: mpenningroth, at: 2006/11/01 17:53

[swood]

Can we add it as a patch at http://jasperforge.org/sf/tracker/do/listArtifacts/projects.jasperintelligence/tracker.patches

?

 

 

 

Sherman

JasperSoft

[mpenningroth]

We could but I don't know how to do this.

[anandharaj]

Hi Mark....

 

I also working on JI with PostgreSQL...now im having problem in step two build... if you can provide the patch for PostgreSQL... it will be easier to me..

 

10q

[anandharaj]

Ok, now i found why my step two build fail. The JDBC driver is hard-coded in two POM's file as MySQL. Have you notice this? I change to PostgreSQL and it works fine.

[anandharaj]

Now im having problem in third build. I attached the error msg. FYI, i only create database for jasperserver and no db for foodmart/sugarcrm. is this cause the problem? [file name=unit_test_result.txt size=139894]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/unit_test_result.txt[/file]

Post edited by: anandharaj, at: 2006/11/06 07:07

[mpenningroth]

I haven't seen the specific error you sent. I also did not have to change any pom files as these use properties from the other settings.

 

I have attached a zip file that documents my changes. Please see the readme file for more info. NOTE: Due to the size of the zipfile, I removed the sugarcrm script for postgres. You still should be able to run with foodmart and sugarcrm on mysql, and the jasper repository on postgres. You will of course have to make sure your maven setting properly reflect this.

 

Sorry I guess the zip is too big still...

Post edited by: mpenningroth, at: 2006/11/06 12:20

[mpenningroth]

I uploaded two zipfiles to the ftp.cincom.com/incoming/jasperintell_postgres .

 

These will be available for a couple of days.

 

One has the sugarcrm script and the other does not.

 

Use anonymous ftp.

[swood]

Thanks, Mark!

 

We will get these on as a patch on JasperForge.

 

 

 

Sherman

JasperSoft

[mpenningroth]

I ran into an issue with quartz that I was able to get around by adding this line to my js.quartz.properties file:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

 

I did not spend any time on trying to make thsi property driven from the build. It should/could be. Maybe I will find time to do so when I get the 1.1 build going.

[aperinet]

Hello,

 

I am currently working on jasperserver repository migration from mysql to postgresql.

I did some modifications, and some functionalities are working (login, user creation...) but some others are not like role creation with some users.

I would be interested by the zip file provided by Mark to see if I didn't do something, but I cannot find it in the patches repository (and the link to to the ftp server provided by Mark seems to not contain the requested zip file).

 

Is there somewhere where I can find this patch?

 

Thank you in advance

alexis

[mpenningroth]

I put the zip file back on the ftp server. It will automatically be deleted from the server in a couple of days.

[manuel]

I have a problem with JI with postgres. I´ve built the application without problem, but when i try to login in JI, the login page appears without problem and when i type my username and passwd, an error appears. I think this error is in database structure or SQL grammar. The error is the following

 

---------------------

nested exception is org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar

....

nested exception is java.sql.SQLException: ERROR: doesn´t exists column this_6_.datasource

---------------------

 

The full error is in the file attached.

 

Thanks in advance.

Manuel.

 

[file name=ERROR_MESSAGE.txt size=55611]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/ERROR_MESSAGE.txt[/file] ERROR-MESSAGE.txt [/file]