[jira] Created: (JCR-1180) DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema

DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
---------------------------------------------------------------------------------

                 Key: JCR-1180
                 URL: https://issues.apache.org/jira/browse/JCR-1180
             Project: Jackrabbit
          Issue Type: Improvement
          Components: jackrabbit-core
    Affects Versions: 1.4
         Environment: All
            Reporter: Kev Jackson
             Fix For: 1.4


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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kev Jackson updated JCR-1180:
-----------------------------

    Attachment: jackrabbit-core-add-schema.patch

Patch which fixes the issue

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>    Affects Versions: 1.4
>         Environment: All
>            Reporter: Kev Jackson
>             Fix For: 1.4
>
>         Attachments: jackrabbit-core-add-schema.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536228 ]

Thomas Mueller commented on JCR-1180:
-------------------------------------

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

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>    Affects Versions: 1.4
>         Environment: All
>            Reporter: Kev Jackson
>             Fix For: 1.4
>
>         Attachments: jackrabbit-core-add-schema.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kev Jackson updated JCR-1180:
-----------------------------

    Attachment:     (was: jackrabbit-core-add-schema.patch)

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>    Affects Versions: 1.4
>         Environment: All
>            Reporter: Kev Jackson
>             Fix For: 1.4
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kev Jackson updated JCR-1180:
-----------------------------

    Attachment: jackrabbit-core.patch

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

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>    Affects Versions: 1.4
>         Environment: All
>            Reporter: Kev Jackson
>             Fix For: 1.4
>
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Thomas Mueller-6]

Hi,

Thanks for your patch! I'm not sure if you read my comment about
compatibility? For details, see here:

https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Thanks,
Thomas

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jukka Zitting updated JCR-1180:
-------------------------------

             Priority: Minor  (was: Major)
    Affects Version/s:     (was: 1.4)

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Priority: Minor
>             Fix For: 1.4
>
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12539388 ]

Thomas Mueller commented on JCR-1180:
-------------------------------------

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

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Priority: Minor
>             Fix For: 1.4
>
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jukka Zitting updated JCR-1180:
-------------------------------

    Fix Version/s:     (was: 1.4)

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.

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Priority: Minor
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Mueller reassigned JCR-1180:
-----------------------------------

    Assignee: Thomas Mueller

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Assignee: Thomas Mueller
>            Priority: Minor
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594811#action_12594811 ]

Thomas Mueller commented on JCR-1180:
-------------------------------------

> 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).

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Assignee: Thomas Mueller
>            Priority: Minor
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12596100#action_12596100 ]

Antonio Mota commented on JCR-1180:
-----------------------------------

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

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Assignee: Thomas Mueller
>            Priority: Minor
>         Attachments: jackrabbit-core.patch
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Antonio Mota updated JCR-1180:
------------------------------

    Attachment: postgresql.ddl
                postgresql.ddl

The .ddl files

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Assignee: Thomas Mueller
>            Priority: Minor
>         Attachments: DatabaseFileSystem.java, DatabasePersistenceManager.java, jackrabbit-core.patch, postgresql.ddl, postgresql.ddl
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Antonio Mota updated JCR-1180:
------------------------------

    Attachment: DatabaseFileSystem.java
                DatabasePersistenceManager.java

The .java classes

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Assignee: Thomas Mueller
>            Priority: Minor
>         Attachments: DatabaseFileSystem.java, DatabasePersistenceManager.java, jackrabbit-core.patch, postgresql.ddl, postgresql.ddl
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/JCR-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Antonio Mota updated JCR-1180:
------------------------------

    Comment: was deleted

> DatabaseFileSystem and DatabasePersistenceManager don't allow choice of db schema
> ---------------------------------------------------------------------------------
>
>                 Key: JCR-1180
>                 URL: https://issues.apache.org/jira/browse/JCR-1180
>             Project: Jackrabbit
>          Issue Type: Improvement
>          Components: jackrabbit-core
>         Environment: All
>            Reporter: Kev Jackson
>            Assignee: Thomas Mueller
>            Priority: Minor
>         Attachments: DatabaseFileSystem.java, DatabasePersistenceManager.java, jackrabbit-core.patch, postgresql.ddl, postgresql.ddl
>
>
> 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

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [