[kepler-dev] Database connections

[Matthew Jones]

The JDBC connection string uses colon as field separators, always starts 
with jdbc:driver, where the specific driver chosen determines how to 
interpret fields in the rest of the string.  In your case, for the 
oracle driver, there are multiple clients (thin, thick, etc).  For the 
thin client, the next field is the connection info, which is in the format:
    user/password at TNSHost
the user and password are optional and can be provided later on login, 
in which case the string starts with the '@' symbol.  the next field in 
the TNS listener port, and then the final field is the Database name 
(SID in oracle parlance).

Other JDBC drivers use a completely different syntax.

Matt

Edward A. Lee wrote:
> 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  
> 
> _______________________________________________
> Kepler-dev mailing list
> Kepler-dev at ecoinformatics.org
> http://mercury.nceas.ucsb.edu/ecoinformatics/mailman/listinfo/kepler-dev

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Matthew B. Jones
Director of Informatics Research and Development
National Center for Ecological Analysis and Synthesis (NCEAS)
UC Santa Barbara
jones at nceas.ucsb.edu                       Ph: 1-907-523-1960
http://www.nceas.ucsb.edu/ecoinfo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~