[Opensource] New EDG Coverage, multiple databases

[Michael Rimov]

Peter, and all others struggling with multiple db stuff

I wrote some into EDG regarding this and your problems... I've omitted 
screenshots and the formatting is a bit messed up thanks to email clients, 
but it should help.  The biggest confusion is that when dealing with 
multiple databases, you either have two completely independent databases in 
which case you want Expresso also created in the other database, or you 
have to program in the Schema to have OtherDBMap work properly to map just 
a few tables to the other db context.  It probably could be improved, but 
at least it's sort of documented now.  Text from the new sections included 
below.

HTH and sorry it took so long to reply.
                                                 -Mike


-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Database Objects in Multiple Databases
Expresso has the capability to define and maintain connection pools for 
multiple databases, and to tie database objects to particular data sources.
Why Multiple Databases?
There are several cases where you would like to deal with multiple 
databases. The most obvious reason for development purposes is to test your 
application against many databases at one time without having to modify 
many configurations. Other reasons include having a connection to a read 
only data warehouse application, virtual hosting, and others.
There are two ways to set up datebases. One way is to define a full 
Expresso-like environment where each database has it's own security tables, 
isolated data, etc. Each data context will also have it's own security as 
well. The other way is for Expresso to store all of it's own database 
bookeeping in one data context and link to some pre-defined tables used in 
another database. This method is especially excellant for data warehousing 
applications where the back-end database must not have any bookkeeping 
tables installed in it.
Independent Multiple Databases
Expresso must have at least one database connection defined to operate 
called 'default'. The default database context contains setup and 
configuration information that Expresso needs to operate. The default 
database is always the database that Expresso will assume is being used 
when a DBObject is manipulated unless setDataContext() [Or the older name 
setDBName()] is called first either directly or through an appropriate 
constructor.
If you take a look at your expresso-config.xml file you'll see some 
information like so:

         <context name="default">

                 <description>Hypersonic Database</description>
                 <jdbc driver="org.hsqldb.jdbcDriver"
                 url="jdbc:hsqldb:%web-app%WEB-INF/db/default/default"
                 connectFormat="3"
                 login="sa"
                 password=""
                 cache="y"
                 createTableIndicies="true"
                 limitationPosition=""
                 escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler"/>

                 <type-mapping>
                         <java-type>LONGVARCHAR</java-type>
                         <db-type>LONGVARCHAR</db-type>
                 </type-mapping>
                 <images>%context%/%expresso-dir%/images</images>
                 <startJobHandler>y</startJobHandler>
                 <showStackTrace>y</showStackTrace>
                 <mailDebug>n</mailDebug>

         </context>


As you can see, at the top of the file, the context is named default as 
expected. The context definition includes a definition of a JDBC connection 
to a hypersonic database.

         <!-- Old Context Handler -->
         <context name="default">

                 <description>Hypersonic Database</description>
                 <jdbc driver="org.hsqldb.jdbcDriver"
                 url="jdbc:hsqldb:%web-app%WEB-INF/db/default/default"
                 connectFormat="3"
                 login="sa"
                 password=""
                 cache="y"
                 createTableIndicies="true"
                 limitationPosition=""
                 escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler"/>

                 <type-mapping>
                         <java-type>LONGVARCHAR</java-type>
                         <db-type>LONGVARCHAR</db-type>
                 </type-mapping>
                 <images>%context%/%expresso-dir%/images</images>
                 <startJobHandler>y</startJobHandler>
                 <showStackTrace>y</showStackTrace>
                 <mailDebug>n</mailDebug>

         </context>
         <!-- Here we add our new context handler -->
         <context name="test">

                 <description>A sample test database</description>
                 <jdbc driver="org.hsqldb.jdbcDriver"
                         <!-- Database URL points to a new location -->
                 url="jdbc:hsqldb:%web-app%WEB-INF/db/test/test"
                 connectFormat="3"
                 login="sa"
                 password=""
                 cache="y"
                 createTableIndicies="true"
                 limitationPosition=""
                 escapeHandler="com.jcorporate.expresso.core.db.DoubleQuoteEscapeHandler"/>

                 <type-mapping>
                         <java-type>LONGVARCHAR</java-type>
                         <db-type>LONGVARCHAR</db-type>
                 </type-mapping>
                 <images>%context%/%expresso-dir%/images</images>
                 <startJobHandler>y</startJobHandler>
                 <showStackTrace>y</showStackTrace>
                 <mailDebug>n</mailDebug>

         </context>



When the servlet engine now loads Expresso, Expresso will load connections 
to both a data context named 'test' and one named 'default'. Using this 
configuration, each data context will be completely independent. You are in 
a position to run DBCreate on your new context. Go to the setup page, and 
click on the "Create/Verify Database Structure & Perform Initial Setup ", 
and you'll see a screen similar to that shown here.

In this I have 3 different contexts defined. I can choose to run DBCreate 
against any of them to define what I want.
So once we've runhow do we use this? An application can have a user deal 
with multiple database contexts by calling the function 
setDataContext([Database Name]); So given the config file above, you could 
have the following code to retrieve a list of all UserPreference settings 
for the data base context 'test':

         UserPreference userPreference = new UserPreference();
         userPreferences.setRequestingUid(SecuredDBObject.SYSTEM_ACCOUNT);
         userPreference.setDataContext("test");
         ArrayList allPreferences = userPreference.searchAndRetrieveList();

When browsing code, you may find code that uses setDBName() rather than 
setDataContext(). The two functions are currently equivilant, although 
setDBName() will be eventually phased out of use, and thus it is 
recommended that you call setDataContext();
When working with controllers, there is a shortcut available for setting 
the security context of the database object as well as the user's id. If 
you are within a State handler for a controller (to be discussed shortly), 
the above code can be shorted to.

         UserPreference userPreference = new UserPreference(request);
         ArrayList allPreferences = userPreference.searchAndRetrieveList();

Where request is the ControllerRequest object handed to you by the Expresso 
framework.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.jcorporate.com/pipermail/opensource/attachments/20020905/9f3f4d70/attachment-0002.htm