Uploaded image for project: 'Grouper'
  1. Grouper
  2. GRP-2323

SQL table sync job not working with postgres



    • Bug
    • Resolution: Unresolved
    • Minor
    • None
    • 2.4.0.patch
    • None


      During the initialization of a syncTables job, the table and column information is obtained from the connection metadata. It converts the schema and table name to uppercase, on the assumption that it is stored that way in the database. But postgres stores its information in all lower case. This makes the job unusable with postgres, and it errors with

      ===> loader ran successfully: fullSync: true, key: EXT_IDENTITY, finalLog: true, state: retrieveCount, databaseFrom: grouper, tableFrom: EXT_IDENTITY, databaseTo: grouper, tableTo: uf_EXT_IDENTITY, exception: java.lang.RuntimeException:Cant find grouping column! EXT_IDENTITY
              at edu.internet2.middleware.grouperClient.jdbc.tableSync.GcTableSyncTableMetadata.getGroupingColumnMetadata(GcTableSyncTableMetadata.java:118)
              at edu.internet2.middleware.grouperClient.jdbc.tableSync.GcTableSync.fullSync(GcTableSync.java:634)
              at edu.internet2.middleware.grouper.app.tableSync.TableSyncOtherJob.run(TableSyncOtherJob.java:60)
              at edu.internet2.middleware.grouper.app.loader.OtherJobBase.execute(OtherJobBase.java:205)
              at edu.internet2.middleware.grouper.app.loader.GrouperLoaderType$2.runJob(GrouperLoaderType.java:203)
              at edu.internet2.middleware.grouper.app.loader.GrouperLoaderJob.runJob(GrouperLoaderJob.java:465)
              at edu.internet2.middleware.grouper.app.loader.GrouperLoader.runOnceByJobName(GrouperLoader.java:1667)
              at edu.internet2.middleware.grouper.app.loader.GrouperLoader.runOnceByJobName(GrouperLoader.java:1615)
              at edu.internet2.middleware.grouper.app.loader.GrouperLoader$runOnceByJobName.call(Unknown Source)
              at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)

      Doing it the right way with DatabaseMetaData.storesLowerCaseIdentifiers(), storesUpperCaseIdentifiers(), supportsMixedCaseIdentifiers() ... and then setting the case correctly for every identifier sounds painful to do this for every identifier, plus there are storesXXXCaseQuotedIdentifiers() versions of these, so you need to check all the identifiers for whether they are quoted or not.

      Since queries themselves aren't sensitive, maybe this can use a stub resultset metadata instead of the database metadata? I.e., "select Key_CoLuMns..., Other_Columns... from ScHeMa.TaBlE where 0=1". Then, since you already know how many key columns and other columns you have, just pick them out by index. So offload the case calculations to the database.




            chris.hyzer@at.internet2.edu Chris Hyzer (upenn.edu)
            chad.redman@at.internet2.edu Chad Redman (unc.edu)
            0 Vote for this issue
            2 Start watching this issue