0 Replies Latest reply on May 14, 2008 7:44 PM by Eric Smith

    Is the portal database case sensitive?

    Eric Smith Newbie

      JBoss AS 4.2.2; Portal 2.6.4; jtds driver; Microsoft SQL server 2005
      When I create a portal database with a case sensitive collation, then let the portal create the tables on the first startup, I notice that some table names are created in uppercase (JBP_PAGE), some in lowercase (jbp_cms_perm). Also some tables have lowercase field names (jbp_users.jbp_uid) and some have uppercase field names (jbp_cms_perm.ID).

      When I look at the HyperSonic table list after a fresh startup, all tablenames and fields seem to be uppercase.

      Meanwhile when I login to my case sensitive database an exception occurs because a portal query is using the wrong case (p.id should be p.ID):

      SELECT p.* from jbp_cms_perm p,jbp_cms_perm_role r,jbp_role_membership m,jbp_roles roles,jbp_users users WHERE p.id=r.cms_perm_id AND r.role_id=roles.jbp_name AND m.jbp_rid=roles.jbp_rid AND m.jbp_uid=users.jbp_uid AND users.jbp_uname=?
      
      java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query
       at org.jboss.portal.cms.security.AuthorizationProviderImpl.findPermissionsByUser(AuthorizationProviderImpl.java:464)
       at org.jboss.portal.cms.security.AuthorizationProviderImpl.getSecurityBindings(AuthorizationProviderImpl.java:179)
       at org.jboss.portal.cms.impl.jcr.command.ACLEnforcer.getPermissions(ACLEnforcer.java:581)


      So I wondered if portal databases are supposed to be case INsensitive collation and I set one up that way but then I've hit other problems, including this one with a case insensitive exception: http://www.jboss.com/index.html?module=bb&op=viewtopic&t=135281
      and possibly this transaction problem:
      http://www.jboss.com/index.html?module=bb&op=viewtopic&t=134329

      So I am wondering: (1) is the portal database supposed to have case sensitive table and field names? if yes, then (2) do you agree there is a bug in the creation of tables and/or portal query composition under Microsoft sql server?

      I will add it would be nice if the portal tables followed a standard, for instance all uppercase or all lowercase table and field names -- regardless of the case sensitive question.