[kepler-dev] Database connections

[Daniel Crawl]

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
>