Jackrabbit Content Repository
  1. Jackrabbit Content Repository
  2. JCR-1180

DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema

    Details

    • Type: Improvement Improvement
    • Status: Patch Available
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: jackrabbit-core
    • Labels:
      None
    • Environment:
      All

      Description

      I have a need to store my repository objects under a different db schema than the default for the rdbms (I'm using postgresql, so in my case the default is 'public')

      The current implementation of the DatabasePersistenceManager and DatabaseFileSystem do not support changing the schema.

      Problems:

      • schemaObjectPrefix allows the user to add a table prefix, but you cannot use this to set a schema ie <schema>.table, as the . is stripped out and replaced with an escaped version
      • schema param currently refers to a ddl resource, not what people would naturally think is the param to set the schema for the repository

      Fix:

      • rename the current schema -> schemaDDL
      • add an optional schema param which allows the user to select which schema they want to use
      • improve error messages so that when an incorrect schemaDDL is chosen the user doesn't have to dig through nabble etc to find an answer
      1. jackrabbit-core.patch
        25 kB
        Kev Jackson
      2. DatabasePersistenceManager.java
        39 kB
        Antonio Mota
      3. DatabaseFileSystem.java
        45 kB
        Antonio Mota
      4. postgresql.ddl
        1 kB
        Antonio Mota
      5. postgresql.ddl
        2 kB
        Antonio Mota
      6. TablePrefix.patch
        73 kB
        Stephen Byrne
      7. TablePrefix.patch
        73 kB
        Stephen Byrne
      8. JCR-1180_2.2.7.patch
        73 kB
        Stephen Byrne

        Activity

        Hide
        Kev Jackson added a comment -

        Patch which fixes the issue

        Show
        Kev Jackson added a comment - Patch which fixes the issue
        Hide
        Thomas Mueller added a comment -

        Hi,

        > schema param currently refers to a ddl resource,
        > not what people would naturally think is the param to set the schema for the repository

        You are right, 'schema' is unfortunate. I also thought it means the database schema, and spent a lot of time because of this (actually, twice - the last time two weeks ago). I would love to change it to schemaDDL, the only problem I see is compatibility. What about: support a new setting 'schemaDDL' (or what about just 'ddl', or 'ddlResource'), and change all samples to use this property name. But still support 'schema' as well for backward compatibility.

        > - add an optional schema param which allows the user to select which schema they want to use

        what about 'schemaName' to avoid a clash with the deprecated 'schema' property? Currently the user name is hard coded (ResultSet rs = metaData.getTables(null, userName, tableName, null))

        > - improve error messages so that when an incorrect schemaDDL is chosen the user doesn't have to dig through nabble etc to find an answer

        Sure. What about "Configuration error: The resource xyz.ddl could not be found"?

        Thanks,
        Thomas

        Show
        Thomas Mueller added a comment - Hi, > schema param currently refers to a ddl resource, > not what people would naturally think is the param to set the schema for the repository You are right, 'schema' is unfortunate. I also thought it means the database schema, and spent a lot of time because of this (actually, twice - the last time two weeks ago). I would love to change it to schemaDDL, the only problem I see is compatibility. What about: support a new setting 'schemaDDL' (or what about just 'ddl', or 'ddlResource'), and change all samples to use this property name. But still support 'schema' as well for backward compatibility. > - add an optional schema param which allows the user to select which schema they want to use what about 'schemaName' to avoid a clash with the deprecated 'schema' property? Currently the user name is hard coded (ResultSet rs = metaData.getTables(null, userName, tableName, null)) > - improve error messages so that when an incorrect schemaDDL is chosen the user doesn't have to dig through nabble etc to find an answer Sure. What about "Configuration error: The resource xyz.ddl could not be found"? Thanks, Thomas
        Hide
        Kev Jackson added a comment -

        -replaces previous patch, fixes issue with checkSchema trying to create tables that already exist

        Show
        Kev Jackson added a comment - -replaces previous patch, fixes issue with checkSchema trying to create tables that already exist
        Hide
        Thomas Mueller added a comment -

        Hi,

        The error message when using the wrong 'schema' is now changed in revision 591046.
        Kev, I'm not sure if you read my comment about compatibility: What about: support a new setting 'schemaDDL' (or what about just 'ddl', or 'ddlResource'), and change all samples to use this property name. But still support 'schema' as well for backward compatibility.

        Thomas

        Show
        Thomas Mueller added a comment - Hi, The error message when using the wrong 'schema' is now changed in revision 591046. Kev, I'm not sure if you read my comment about compatibility: What about: support a new setting 'schemaDDL' (or what about just 'ddl', or 'ddlResource'), and change all samples to use this property name. But still support 'schema' as well for backward compatibility. Thomas
        Hide
        Jukka Zitting added a comment -

        Dropping this from 1.4. The proposed patch seems too complex to me and introduces a backwards-incompatible configuration change. I'd rather see something that works within the scope of the existing schemaObjectPrefix option.

        Show
        Jukka Zitting added a comment - Dropping this from 1.4. The proposed patch seems too complex to me and introduces a backwards-incompatible configuration change. I'd rather see something that works within the scope of the existing schemaObjectPrefix option.
        Hide
        Thomas Mueller added a comment -

        > something that works within the scope of the existing schemaObjectPrefix option

        I think splitting schemaObjectPrefix in 'schema name' 'dot' 'table name prefix' is an ugly hack: Any database identifier can contain spaces and dots if it's quoted. Correct parsing and splitting the schemaObjectPrefix would be really ugly and database dependent (MS SQL Server supports [] quotes, MySQL backticks; but most database use double quotes, which need to be escaped in XML). I think it's better to use a distinct case sensitive property 'schemaName' as in the java.sql.DatabaseMetaData.getColumns and so forth. In my view, the schemaObjectPrefix should be kept as is (tableNamePrefix would actually be the right name).

        Show
        Thomas Mueller added a comment - > something that works within the scope of the existing schemaObjectPrefix option I think splitting schemaObjectPrefix in 'schema name' 'dot' 'table name prefix' is an ugly hack: Any database identifier can contain spaces and dots if it's quoted. Correct parsing and splitting the schemaObjectPrefix would be really ugly and database dependent (MS SQL Server supports [] quotes, MySQL backticks; but most database use double quotes, which need to be escaped in XML). I think it's better to use a distinct case sensitive property 'schemaName' as in the java.sql.DatabaseMetaData.getColumns and so forth. In my view, the schemaObjectPrefix should be kept as is (tableNamePrefix would actually be the right name).
        Hide
        Antonio Mota added a comment -

        I'm going to attach the files I modified to implement the "ugly" solution discussed here:

        http://www.nabble.com/Using-diferent-database-schemas-td16993168.html

        Show
        Antonio Mota added a comment - I'm going to attach the files I modified to implement the "ugly" solution discussed here: http://www.nabble.com/Using-diferent-database-schemas-td16993168.html
        Hide
        Stephen Byrne added a comment -

        Patch to add support for tablePrefix property to add schema support to DatabaseFileSystem and BundleDbPersistenceManager

        Show
        Stephen Byrne added a comment - Patch to add support for tablePrefix property to add schema support to DatabaseFileSystem and BundleDbPersistenceManager
        Hide
        Stephen Byrne added a comment -

        TablePrefix.patch uses tablePrefix as DbDataStore does. The patch is against 2.2.5.

        Show
        Stephen Byrne added a comment - TablePrefix.patch uses tablePrefix as DbDataStore does. The patch is against 2.2.5.
        Hide
        Stephen Byrne added a comment -

        Update to previous TablePrefix.patch that fixes schema check when a schema is specified via tablePrefix.

        Show
        Stephen Byrne added a comment - Update to previous TablePrefix.patch that fixes schema check when a schema is specified via tablePrefix.
        Hide
        Stephen Byrne added a comment -

        Updated patch against 2.2.7.

        If I hope to get this patch included, what should I patch against?

        Show
        Stephen Byrne added a comment - Updated patch against 2.2.7. If I hope to get this patch included, what should I patch against?
        Hide
        Sascha Theves added a comment -

        Can someone please increase the priority of this issue because it makes Jackrabbit unusable in scenarios where you have to use a db schema name. And can somebody tell me in which version this fix will be included?

        Show
        Sascha Theves added a comment - Can someone please increase the priority of this issue because it makes Jackrabbit unusable in scenarios where you have to use a db schema name. And can somebody tell me in which version this fix will be included?

          People

          • Assignee:
            Thomas Mueller
            Reporter:
            Kev Jackson
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development