Derby for bedework

Derby issues

When attempting to access the db we might see errrors like this

Schema 'SA' does not exist

Apparently this is due to attempting to access an entity in a database that has not yet been populated. I would suggest that this issue should be resolved and the message made more informative as a lot of people spend a lot of time over this one. I suspect this prevents us from getting any meaningful response if we want to check for the presence or otherwise of database tables.

This message might be a little enlightening

There was a thread in which the error 'schema does not exist' came up,
and Dan Debrunner explained:
http://mail-archives.apache.org/eyebrowse/ReadMsg?listName=derby-user@db.apache.org&msgNo=106
and I think it might be worthwhile adding something like the following
to the 'Using Derby' FAQ section.
Most of the following is from Dan's explanation on that thread, except 
the very last 'or'.

Q: Why do I get the error 'schema does not exist'?

Answer:
The current schema for any connection defaults to a schema corresponding
to the user name. If no user name is supplied then the user name (and
hence current schema) defaults to APP.

However even though the current schema is set to the user name, that
schema may not exist. A schema is only created by CREATE SCHEMA or
creating an object (table etc.) in that schema (this is implicit schema
creation).

The one exception to this is the APP schema, which is always created,
though applications should not depend on that.

So you will see the schema not exists error if your application tries to
access the current schema before any objects have been created in it.
Possibilities are you try to perform a DROP TABLE before creating it, or
try to SELECT from a table to see if it should be created or not, or
when there is a problem with the sql for the very first object you try
to create in a particular schema and no explicit CREATE SCHEMA was issued..

---

Bedework database

put in 3.6 manual

For the quickstart calendar database(s) we can use embeddeded derby. A variation of the default-ds.xml file can define a different database. We will create a datasource calDb and link to that from the services and the calendar ear itself.

For production an external database is probably desirable. To initialise this database we could automatically start a setupdb service which checks for the presence of one or more of th etables. In their absence it could create the tables by default.

Adding the initial data could be carried out at the same time eliminating the required run of the dump/restore script.

Here is the bedework-ds.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE datasources
        PUBLIC "-//JBoss//DTD JBOSS JCA Config 1.5//EN"
        "http://www.jboss.org/j2ee/dtd/jboss-ds_1_5.dtd">

<datasources>
    <local-tx-datasource>
        <jndi-name>CalendarDS</jndi-name>
        <driver-class>org.apache.derby.jdbc.EmbeddedDriver</driver-class>
        <!--connection-url>jdbc:derby:${jboss.server.data.dir}{/}derby{/}bedeworkCalDb3p6;create=true</connection-url-->
        <connection-url>jdbc:derby:IGNORED</connection-url>
        <user-name>IGNORED</user-name>
        <password>IGNORED</password>
        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>5</idle-timeout-minutes>
        <track-statements/>
    </local-tx-datasource>
    
    <!-- Don't forget to install derby-plugin.jar and derby*.jar into jboss/server/default/lib -->
    <mbean code="org.jboss.jdbc.DerbyDatabase" name="jboss:service=CalendarDS">
        <attribute name="Database">bedeworkCalDb3p6</attribute>
        <attribute name="User">sa</attribute>
        <attribute name="Password"></attribute>
    </mbean>

</datasources>