[
https://issues.apache.org/jira/browse/ROL-1927?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13088458#comment-13088458
]
Nick Padilla commented on ROL-1927:
-----------------------------------
Hey David, sorry about that, will use diff patches instead next time.
> Roller 5 MSSQL Issues/Fixes
> ---------------------------
>
> Key: ROL-1927
> URL: https://issues.apache.org/jira/browse/ROL-1927
> Project: Roller
> Issue Type: Bug
> Components: Macros
> Affects Versions: 5.0
> Environment: Tomcat 6.0.32
> MS SQL Server 2008 R2 Express
> Sun JDK 1.6.0.26
> Ubutnu 11.04(Linux Kernal 2.6.38-10-generic)
> Google Chrome, stable release.
> Reporter: Nick Padilla
> Assignee: David Johnson
> Fix For: 5.1
>
> Attachments: 400-to-500-migration.vm, JPAWeblogManagerImpl.java,
> createdb.vm, macros.vm
>
>
> When using MSSQL there are a few things that go wrong in the upgrade and new
> install. First of all the .vm files that build the .sql are building .sql
> files that will not run on MS SQL Server. Mostly problems with NOT using the
> $db. variables, fixed those so that they create datetime columns instead of
> using timestamp. There were a few problems with the macros.vm file as well
> as it wasn't taking into account the MSSQL syntax, fixed those as well. Now
> when I build the project it builds the create and update .sql files correctly
> and they run without errors.
> The .sql script errors wasn't the only thing that was causing a problem when
> using the MSSQL, there was an even more insidious problem. I am not 100%
> sure which is the problem, OpenJPA not supporting in-memory operations when
> using MSSQL or if the MSSQL doesn't support the in-memory operations. My
> reasoning behind this is because of the problem i encountered, and a few more
> have encountered it as well but didn't actually fix the problem. So what
> happens is this, I create a new DB and roller instance, use the default
> Container and DB providers, which is Jetty and Derby. Go through the motions
> of creating a user and weblog, everything goes fine. However, when I load up
> tomcat with the same instance of Roller and point it at a MSSQL database, it
> does its thing (creates the db and such, happening correctly now) and i
> create my user no problem but it errors out when creating the weblog. The
> error is misleading, as it is saying that there was a Failed Object,
> WeblogPermissions. This object is now just a decorator for the
> ObjectPermission class, but that isn't the problem. The problem is that the
> weblog gets flushed on line 287 in the JPAWeblogManagerImpl class, this
> persists the object to the db. Then next line of code sets up the needed
> objects when creating a new weblog, it creates the WeblogPermissions (and
> other objects) and stores them in memory. However, I found that when this
> call is made "pingTargetMgr.getCommonPingTargets().iterator();" at the end of
> the addWeblogContents(Weblog) method, it attempts to use a named query that
> requires the object tree be persisted to the db. It expects that the
> WeblogPermissions be accessible from the database, but it isn't in the
> database its in memory, since it hasn't been flushed yet. The named query
> filters down to a inInMemory(int) call which is in
> org.apache.openjpa.kernel.QueryImpl, this method then tries to flush the
> current transaction. This is when the error happens, not sure why it can't
> flush, but it tries to find the WeblogPermissions and can't. Either way that
> is where the error source is from, why it can't flush the transaction then
> run the named query is odd, but it is what it is.
> Instead of finding out why the call to flush() errors out I decided to move a
> bit of the code around and make an explicit call to flush() prior to the
> getCommonPingTargets() call. This persists the objects in memory and so the
> getCommonPingTargets() returns normally, without errors. I have been able
> to successfully update and create instances of Roller 5, from Roller 4 only.
> I didn't go through all the previous .vm files to fix them for MSSQL, just
> the 400-to-500 one.
> Here are a list of files I have edited:
> org.apache.roller.weblogger.business.jpa.JPAWeblogManagerImpl
> /roller-weblogger-business/src/main/resources/sql/400-to-500-migration.vm
> /roller-weblogger-business/src/main/resources/sql/createdb.vm
> /roller-weblogger-business/src/main/resources/sql/macros.vm
> I have also sent out the (ICLA) so I can contribute this patch, and any
> others I need to in the future. All the tests ran fine and I did test using
> Derby and Jetty to ensure nothing I did was wrong for the different
> container/db provider. I can't think of any reason why my logic would be
> wrong, or cause problems for, other containers or db providers. Once I have
> a login for the Roller ticketing system I will create the ticket for these
> changes. Would you suggest two issues, one for the SQL files an the other
> for the code change? Please let me know what you will need, I have the
> files, of course, but also the distributions that contain the fixes for
> production use. I would like to commit these changes myself, probably to
> trunk I would assume, so let me know what needs to be done on my end, or if
> that is possible. Thanks for your time and a very cool project!
> As a side note, I have built tomcat distributions, .tar.gz and .zip, that
> work with MSSQL; should work with any other provider as well. I have them
> available here:
> http://gotime.monstersoftwarellc.com/roller-weblogger-5.0.0-for-tomcat.tar.gz
> http://gotime.monstersoftwarellc.com/roller-weblogger-5.0.0-for-tomcat.zip
> Please let me know if there are any questions, comments, or concerns.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
