Hi Andreas,
I can see that for many applications it would be useful to have a
concept of a database-wide default schema, that is, a schema that all
users start out in when they get a fresh connection. That is not the way
Derby works. For Derby, the default schema is user-specific, not
database-wide. This is true even in the degenerate case of an embedded
application which does not run with authentication turned on: in that
case the username defaults to APP and the default schema is APP's schema.
The 2003 SQL standard does not offer any guidance on this topic.
According to part 2, section 4.37.2 (SQL-session identification), the
default schema of a session is vendor-defined.
I don't remember any deep reason about why Derby has this default
behavior. It may be nothing more complicated than the fact that the
original programmers were familiar with this default from previous
databases which they had worked on.
Some databases let you bind a default schema to a username. See for
instance http://msdn.microsoft.com/en-us/library/ms190387.aspx. I think
it would be straightforward to build such a mechanism for
Derby--although this would take you into session startup logic and the
devil could be in the details. I think there are plenty of people around
who understand that logic and who could coach a newcomer through this
project.
In the meantime, the only workaround I see is for your application to
force the schema to a default value when your application hands out
connections.
Please pardon me if I have talked past your real concern.
Hope this helps,
-Rick
Andreas Kyrmegalos wrote:
Thanks Øystein,
I was just checking that now too, with builtin authentication. Same
result. My impression was that default schemas are attached to the
database not the connecting user. So, that since a user has been
authenticated and if there are no authorization locks in place there
wouldn't be a need to set the schema each time a user connects.
Something like a default behavior. But as you said the default
behavior is have the connecting username as the starting schema.
Thanks again. What I 'm trying to figure out is the rational behind
such an approach. If someone could shed some light on this?
Andreas
Øystein Grøvlen wrote:
The default schema is the same as the username, regardless of whether
it exists or not. You can use 'set schema' to switch to another schema.
