You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by amsmota <am...@gmail.com> on 2008/05/01 10:21:23 UTC
Using diferent database schemas
Hi again.
We are using a postgresql db with JackRabbit and I noticed that all the
tables are created on the "public" database schema. Now we have a
requirement to use different schemas in the same database.
What will be the better way to handle this? Use different schema.ddl? Extend
SimpleDbPersistenceManager?
Cheers.
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p16993168.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
This one seems easy to do, just 2 classes to extend, I'll post something
later...
amsmota wrote:
>
> Hi again.
>
> We are using a postgresql db with JackRabbit and I noticed that all the
> tables are created on the "public" database schema. Now we have a
> requirement to use different schemas in the same database.
>
> What will be the better way to handle this? Use different schema.ddl?
> Extend SimpleDbPersistenceManager?
>
> Cheers.
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p16993313.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
Of course, separating the schema from the prefix does that also...
Nevertheless I attached my "ugly" solution to the Jira, as is working
perfectly for my use cases now and maybe can be useful.
Cheers.
amsmota wrote:
>
> Ok, now I'm finishing it. Actually I did implement the "no-schema schema"
> backward compatibility *iif* there is only one "user" schema in the
> database (presumably the "default" schema).
>
> Regarding the question of "ugliness" discussed above, actually this "hack"
> as one big advantage, this way I can have the "workspace" tables and the
> "version" tables and the "filessystem" tables in different schemas using
> the schemaObjectPrefix, which can be handy for maintenance and
> house-keeping, by specifing only "schema." and no "prefix" at all:
>
> Repository/FileSystem/param[name="schemaObjectPrefix" value="FileSystem."]
>
> Repository/Workspace/FileSystem/param[name="schemaObjectPrefix"
> value="Repository."]
> Repository/Workspace/PersistenceManager/param[name="schemaObjectPrefix"
> value="Repository."]
>
> Repository/Versioning/FileSystem/param[name="schemaObjectPrefix"
> value="Versions."]
> Repository/Versioning/PersistenceManager/param[name="schemaObjectPrefix"
> value="Versions."]
>
> So it's not so "ugly" after all... :-)
>
> I didn't test it thoroughly , but I'll post the code in the Jira asap if
> you guys think it's usefull.
>
> Cheers.
>
>
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17208553.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
Ok, now I'm finishing it. Actually I did implement the "no-schema schema"
backward compatibility *iif* there is only one "user" schema in the database
(presumably the "default" schema).
Regarding the question of "ugliness" discussed above, actually this "hack"
as one big advantage, this way I can have the "workspace" tables and the
"version" tables and the "filessystem" tables in different schemas using the
schemaObjectPrefix, which can be handy for maintenance and house-keeping, by
specifing only "schema." and no "prefix" at all:
Repository/FileSystem/param[name="schemaObjectPrefix" value="FileSystem."]
Repository/Workspace/FileSystem/param[name="schemaObjectPrefix"
value="Repository."]
Repository/Workspace/PersistenceManager/param[name="schemaObjectPrefix"
value="Repository."]
Repository/Versioning/FileSystem/param[name="schemaObjectPrefix"
value="Versions."]
Repository/Versioning/PersistenceManager/param[name="schemaObjectPrefix"
value="Versions."]
So it's not so "ugly" after all... :-)
I didn't test it thoroughly , but I'll post the code in the Jira asap if you
guys think it's usefull.
Cheers.
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17146429.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
Ok, forget this, I should have made the changes before I post... I forgot the
system schemas, and that there is no way of telling which one is what...
So I'm dropping the "no-schema schema" altogether.
amsmota wrote:
>
> I think I'm dropping this, I don't have the time to come up with a good
> solution. For what I know all the schema stuff is vendor specific, and
> even within each vendor is version specific, so I'm wasting my time here.
>
> One final change for partial compatibility I'm making is this:
>
> if schemaObjectPrefix.includesSchema
> schemaToUse = schemaObjectPrefix.includedSchema
> else
> if databaseMetada.getNumberOfSchemas() > 1
> throw schemaError
> end
> schemaToUse = databaseMetada.getNamesOfSchemas()[0]
> end
>
> This way the code works "my" way with multiple schemas and with the "old"
> "no-schema schema" providing there is only one schema in the db...
>
> Do you think it's acceptable? Do you still want the code?
>
> Cheers.
>
>
>
>
>
>
>
>
> amsmota wrote:
>>
>> My use case changed a bit but now I'm having a tough time testing for
>> back compatibility.
>>
>> Basically my requirement now is that I shall have the possibility of
>> having 2 or more schemas holding repositories, and so the different
>> tables. I'm having inconsistent results when trying to implement this
>> *and* at the same time be compatible with the original "no-scheme"
>> scheme.
>>
>> I'll keep on testing tomorrow and I'll post back the results.
>>
>
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17145657.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
I think I'm dropping this, I don't have the time to come up with a good
solution. For what I know all the schema stuff is vendor specific, and even
within each vendor is version specific, so I'm wasting my time here.
One final change for partial compatibility I'm making is this:
if schemaObjectPrefix.includesSchema
schemaToUse = schemaObjectPrefix.includedSchema
else
if databaseMetada.getNumberOfSchemas() > 1
throw schemaError
end
schemaToUse = databaseMetada.getNamesOfSchemas()[0]
end
This way the code works "my" way with multiple schemas and with the "old"
"no-schema schema" providing there is only one schema in the db...
Do you think it's acceptable? Do you still want the code?
Cheers.
amsmota wrote:
>
> My use case changed a bit but now I'm having a tough time testing for back
> compatibility.
>
> Basically my requirement now is that I shall have the possibility of
> having 2 or more schemas holding repositories, and so the different
> tables. I'm having inconsistent results when trying to implement this
> *and* at the same time be compatible with the original "no-scheme" scheme.
>
> I'll keep on testing tomorrow and I'll post back the results.
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17145150.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
My use case changed a bit but now I'm having a tough time testing for back
compatibility.
Basically my requirement now is that I shall have the possibility of having
2 or more schemas holding repositories, and so the different tables. I'm
having inconsistent results when trying to implement this *and* at the same
time be compatible with the original "no-scheme" scheme.
I'll keep on testing tomorrow and I'll post back the results.
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17127551.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by Thomas Müller <th...@day.com>.
Hi,
> Ok, I just implemented this and tested for back compat and it's working *in
> the cases I have now*, i haven't dobne a complete test because I don't even
> know all the possibilities of the configuration file. I implemented only the
> cases I mentioned on the other post I referred earlier.
>
> I changed code in DatabasePersistenceManager and DatabaseFileSystem and on
> the corresponding DDL.
>
> Should I post the code in here? Or in JIRA? Or not at all?
Could you please post your changes as an attachment in the JIRA issue?
https://issues.apache.org/jira/browse/JCR-1180
It would be great if it's in the form of a 'patch'. If this is too complicated,
just the source code is OK.
> It is somewhat a ugly solution, thus...
I agree. In my view this is an ugly hack. It will fail with quoted
identifiers: 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).
Regards,
Thomas
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
Ok, I just implemented this and tested for back compat and it's working *in
the cases I have now*, i haven't dobne a complete test because I don't even
know all the possibilities of the configuration file. I implemented only the
cases I mentioned on the other post I referred earlier.
I changed code in DatabasePersistenceManager and DatabaseFileSystem and on
the corresponding DDL.
Should I post the code in here? Or in JIRA? Or not at all?
Cheers.
amsmota wrote:
>
> How about implementing it like this?
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17087811.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
How about implementing it like this?
if schemaObjectPrefix.hasADot
dbSchema = everythingUpToAndIncludingTheFirst(".")
schemaObjectPrefix = everythingFromTheFirstUntilTheEnd(".")
schemaObjectPrefix.escapeTheCharacters()
else
continueToDoWhatIsDoingNow
end
That way the backward compatibility is assured.
It is somewhat a ugly solution, thus...
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17079909.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
Hi.
In fact it's going to be configuration, yes. We don't need to resolve the
schema at runtime. Now I was looking at the Jira, but it seems that is going
nowhere. So I think I have two options now.
- to use the schemaObjectPrefix to add the schema, removing the escape of
the chars and changing the DDL to not include it when creating indexes
- to use the patch on the Jira (or a similar one)
So my first option is easy enough for me, however it poses some questions.
I'm going to make
http://www.nabble.com/Use-of-diferent-or-same-schemaObjectPrefixes-td17079492.html
another post about it.
What do you think will be the better option?
Cheers.
Thomas Müller-2 wrote:
>
> Hi,
>
>> I tested and it seems it doesn't work.
>
> I think user name and schema name should be part of the configuration,
> and shouldn't be a function. Could you explain why it can only be
> resolved at runtime in your case?
>
> Regards,
> Thomas
>
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17079528.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by Thomas Müller <th...@day.com>.
Hi,
> I tested and it seems it doesn't work.
You are right, that was wrong information.
There is already a bug / feature request for this:
https://issues.apache.org/jira/browse/JCR-1180
> Nevertheless that wouldn't work for me, I still have to know what the
> requirement is but probably I have to resolve the schema on runtime, maybe
> by user name or something.
I think user name and schema name should be part of the configuration,
and shouldn't be a function. Could you explain why it can only be
resolved at runtime in your case?
Regards,
Thomas
Re: Using diferent database schemas
Posted by amsmota <am...@gmail.com>.
Hi.
I tested and it seems it doesn't work. Indeed, in the
http://jdbc.postgresql.org/documentation/83/connect.html parameters section
of the docs the "schema" property doesn't appear.
Nevertheless that wouldn't work for me, I still have to know what the
requirement is but probably I have to resolve the schema on runtime, maybe
by user name or something. But it seems easy to do by extending only a
couple of classes.
Cheers.
Thomas Müller-2 wrote:
>
> Hi,
>
> For PostgreSQL, I believe you can specify the schema in the connection
> URL as in:
>
> jdbc:postgresql://localhost:5432/test?schema=myschema
>
> This should solve the problem.
>
> Regards,
> Thomas
>
>
--
View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p17010179.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: Using diferent database schemas
Posted by Thomas Müller <th...@day.com>.
Hi,
For PostgreSQL, I believe you can specify the schema in the connection
URL as in:
jdbc:postgresql://localhost:5432/test?schema=myschema
This should solve the problem.
Regards,
Thomas
On Thu, May 1, 2008 at 10:21 AM, amsmota <am...@gmail.com> wrote:
>
> Hi again.
>
> We are using a postgresql db with JackRabbit and I noticed that all the
> tables are created on the "public" database schema. Now we have a
> requirement to use different schemas in the same database.
>
> What will be the better way to handle this? Use different schema.ddl? Extend
> SimpleDbPersistenceManager?
>
> Cheers.
> --
> View this message in context: http://www.nabble.com/Using-diferent-database-schemas-tp16993168p16993168.html
> Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
>
>