db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Schema issues?
Date Wed, 13 Apr 2005 14:04:15 GMT
Øystein Grøvlen wrote:
> I have a few questions to the following ij-session:
> ----------------------------------------------------------------------
> ij version 10.1
> ij> connect 'jdbc:derby:TestDB;create=true;user="user1";password="user1"';
> ij> create table testschema.testtab(num int, addr varchar(40));
> 0 rows inserted/updated/deleted
> ij> insert into testtab2 values(7000, 'TRONDHEIM');
> ERROR 42Y07: Schema 'user1' does not exist
> ----------------------------------------------------------------------
>  - Derby seems to automatically create schemas that are referred by
>    create table.  Is this according to the standard?  I think this
>    behavior is problematic since mis-spellings of schema names will
>    not be caught.

Not sure that mis-spellings is something that Derby has to be concerned
with, you could mis-spell many items within a SQL statement, e.g. in
your examples you could mis-spell the column names, table names,
'TRONDHEIM', mis-enter the varchar length, the value 7000.

I couldn't see anything in the standard about this either way, <table
definition> doesn't seem to refer much to the schema name.

>  - I would expect "Table 'testtab2' does not exist" for the insert
>    statement.  I guess what happens here is that Derby tries to find
>    the table in the default schema which is 'user1' since I am
>    connected as this user.  It then discovers that this schema does
>    not exists and flag this error.  In my opinion, the error message
>    is confusing and should be considered a bug.  I can create a Jira
>    issue for this, but I would like to hear what other people think.

A table not found error might be more useful, I think it would be good
to keep the schema does not exist exception chained to the table not
found exception. This behaviour most likely occurs for all CREATE

>  - In my opinion, a default schema should be created by default.  Is
>    there any particular reason for not doing so?

Depending on when the schema was created you could end up with many
schema definitions that are never used, wasting space. E.g. if the
application has many different users connecting and the schema is
created at connection time, then there will be many schemas. But if the
first action of the application is to perform a SET SCHEMA to a known
schema, then all of those default schemas are created for no reason.

The default schema for a user is created automatically when some CREATE
statement is issued for an object without a qualified name. This is the
same mechanism, implicit schema creation, you discuss in your first point.


View raw message