[kepler-dev] Database connections

[Edward A. Lee]

I like the idea of (optionally) connecting DatabaseManager to the graph...
I will look into that.  It could have two input ports, one that triggers
opening of the connection and one that triggers closing.  If these are
disconnected, then it would open on preinitialize().  In all cases, if the
connection is still open on wrapup(), it will be closed.  The database
parameters could become PortParameters, so they could optionally be
specified as inputs.

Since I don't have much background with databases, I could use some help
with getting the terminology right... If I get a database connection using
the string:

   "jdbc:oracle:thin:@buffy.eecs.berkeley.edu:1521:acgeecs"

I presume that:

   "jdbc:oracle:thin" is the "database format" (?).  Is the ":@" part of this?

   "buffy.eecs.berkeley.edu" is the host

   "1521" is the port

   "acgeecs" is the database (?)

Is this right?
Edward

At 11:56 AM 6/5/2008, Daniel Crawl wrote:

>Hi Edward,
>
>
>Edward A. Lee wrote:
>>Looking over Efrat's database actors in org.geon, I'm wondering
>>about whether this design is the best choice.  In particular, he
>>has an actor OpenDBConnection that produces on its output a
>>DBConnectionToken. This is provided as an input to DatabaseQuery
>>and to CloseDBConnection.
>>
>>This design seems to me to have two problems:
>>
>>1) If the model fails to fire the CloseDBConnection, either through
>>modeling error or an exception, then the database connection remains
>>open.
>>
>>2) It seems like it would make it awkward to make repeated queries
>>on the same database, which would seem to be a common situation.
>>
>>  
>
>I recently made two updates to the OpenDBConnection, DatabaseQuery,
>and DatabaseWriter actors that attempt to fix this awkwardness:
>
>1. After reading the DBConnectionToken in DatabaseQuery or
>DatabaseWriter, the token consumption rate for that port is set to
>zero. These actors can now perform multiple queries or updates with
>directors such as DDF that use these rates. However, this design
>still is awkward in some cases. For example, if I want to use
>DatabaseQuery in an IterateOverArray actor, I must create an array
>of DBConnectionTokens.
>
>2. Alternatively, OpenDBConnection now outputs a record token
>containing the information necessary to create a database connection.
>DatabaseQuery and DatabaseWriter read this token via a port parameter.
>If DatabaseQuery is inside an IterateOverArray actor, I can store
>the token as a workflow parameter and reference it in DatabaseQuery.
>A disadvantage of this approach is that a new database connection
>is created for each actor using the record token.
>
>
>>I'm considering an alternative design with:
>>
>>DatabaseManager: An actor with no input ports or output ports
>>that opens a connection in preinitialize() and closes it in
>>wrapup().  Note that wrapup() is guaranteed to be called even
>>if the model fails with an exception.
>>
>>DatabaseQuery: An actor with a parameter that references the
>>DatabaseManager by name and makes queries when fired.
>>
>>
>>Any comments on the relative merits of these designs?
>>
>>Edward
>>
>>
>>  
>
>DatabaseManager would allow DatabaseQuerys and DatabaseWriters to
>share a connection. It also provides better encapsulation than
>storing the connection info inside a workflow parameter.
>
>Since DatabaseManager is disconnected from the graph, I think
>there are several disadvantages:
>
>1. Less control of when the connection is opened. In
>long-running workflows I may not want to keep the connection open
>the entire time.
>
>2. Connection parameters such as database location, type, etc.,
>cannot depend on the output of actors.
>
>3. For SDF (maybe others?) allowDisconnectedGraphs must be true.
>
>What if DatabaseManager optionally could be connected to the graph
>with a single input and output port? (Input tokens would be passed
>to the output). When connected to the graph, the database connection
>is created in fire() instead of preinitialize().
>
> --dan
>>------------ Edward A. Lee
>>Chair of EECS and Robert S. Pepper Distinguished Professor
>>231 Cory Hall, UC Berkeley, Berkeley, CA 94720-1770
>>phone: 510-642-0253, fax: 510-642-2845
>>eal at eecs.Berkeley.EDU, http://www.eecs.berkeley.edu/Faculty/Homepages/lee.html  
>>
>>_______________________________________________
>>Kepler-dev mailing list
>>Kepler-dev at ecoinformatics.org
>>http://mercury.nceas.ucsb.edu/ecoinformatics/mailman/listinfo/kepler-dev
>>  
>
>

------------ 
Edward A. Lee
Chair of EECS and Robert S. Pepper Distinguished Professor
231 Cory Hall, UC Berkeley, Berkeley, CA 94720-1770
phone: 510-642-0253, fax: 510-642-2845
eal at eecs.Berkeley.EDU, http://www.eecs.berkeley.edu/Faculty/Homepages/lee.html