>>>>> "DJD" == Daniel John Debrunner <[EMAIL PROTECTED]> writes:
DJD> �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.
DJD> Not sure that mis-spellings is something that Derby has to be concerned
DJD> with, you could mis-spell many items within a SQL statement, e.g. in
DJD> your examples you could mis-spell the column names, table names,
DJD> 'TRONDHEIM', mis-enter the varchar length, the value 7000.
You are right. I was thinking this was similar to FORTRAN's implicit
declaration of variables which can create a lot of problems, but in
this case you will get an error when one later try to use the created
table.
DJD> I couldn't see anything in the standard about this either way, <table
DJD> 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.
DJD> A table not found error might be more useful, I think it would be good
DJD> to keep the schema does not exist exception chained to the table not
DJD> found exception. This behaviour most likely occurs for all CREATE
DJD> statements.
I can file a Jira issue for this.
>> - In my opinion, a default schema should be created by default. Is
>> there any particular reason for not doing so?
DJD> Depending on when the schema was created you could end up with many
DJD> schema definitions that are never used, wasting space. E.g. if the
DJD> application has many different users connecting and the schema is
DJD> created at connection time, then there will be many schemas. But if the
DJD> first action of the application is to perform a SET SCHEMA to a known
DJD> schema, then all of those default schemas are created for no reason.
DJD> The default schema for a user is created automatically when
DJD> some CREATE statement is issued for an object without a
DJD> qualified name. This is the same mechanism, implicit schema
DJD> creation, you discuss in your first point.
I see, combined with the automatic creation of schema on first create
this will not be an usability issue.
The reason I started looking at this is that in an application where
several threads create tables in parallell, a thread get the following
exception when creating a table in schema WIS without first creating
the schema:
ERROR X0Y68: Schema 'WIS' already exists.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:322)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(DataDictionaryImpl.java:1512)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptorNow(DataDictionaryImpl.java:1498)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(DataDictionaryImpl.java:1479)
at
org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantAction(CreateSchemaConstantAction.java:148)
at
org.apache.derby.impl.sql.execute.DDLConstantAction.getSchemaDescriptorForCreate(DDLConstantAction.java:142)
at
org.apache.derby.impl.sql.execute.CreateTableConstantAction.executeConstantAction(CreateTableConstantAction.java:218)
at
org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:57)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:367)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1109)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:519)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
at myapp.WiscInsertClient.run(WiscInsertClient.java:67)
at java.lang.Thread.run(Thread.java:534)
It suspect there is a race condition where one thread detects that the
schema does not exists, but when it tries to create it, the scema has
already been created by another thread.
--
�ystein
