--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/README	Sun May 23 10:51:35 2010 +0200
@@ -0,0 +1,394 @@
+* Requirements
+
+  1. Provide an interface for the user to insert connection
+     credentials: server hostname, database name, user name and
+     password.
+
+  2. Avoid that the user has to enter those information too often,
+     like when retrieving many objects from the repository. This is
+     done caching the credentials for a configurable amount of time
+
+  3. Provide understandable error messages.
+  
+  4. Track open connections, and avoid excessive proliferation of open
+     connections to the database, due to miss behaving user functions.
+
+  This is all about managing connections, so I would propose to call
+  this component LTPDAConnectionManager.
+
+
+* Interface definition
+
+** LTPDAConnectionManager()
+
+   Isntantiates a singleton class managing credentials and connections.
+
+   Credentials are cached. Credentials, except password are cached
+   forever, passwords are cached for a configurable amount of time.
+
+   Connections are tracked putting each handed out connection into a
+   list. When a new connection is requested, or when requested via a
+   dedicated method, the connection list is walked and closed
+   connections are removed from it. When the connection number exceeds
+   a configurable maximum, no more connection are instantiated.
+
+   The singleton clas is implemented as an handle matlab class that
+   stores an handle to itself in appdata storage. When the class is
+   instantiates it returns an handle to the copy referenced in
+   appdata.
+
+*** appdataKey
+
+    Static mehod that returns the key used to store the handle to
+    instance data in appdata.
+
+*** credentialsExpiry
+    
+    Property. Taken from user preferences. Time after which the
+    credentials are not valid anymore.
+
+*** maxConnectionsNumber
+
+    Property. Taken from user preferences. The maximum number of
+    connections that can be open at the same time toward the same
+    host.
+
+*** cachePassword
+
+    Property. Taken from user preferences. Defines if password should
+    be cached along with other database access credentials. It may
+    take the values:
+
+    0. passwords are not cached,
+    1. passwords are always cached,
+    2. ask if the users desires to cache the given password.
+
+*** connect(hostname, database, username, password)
+
+    Try to connect to the database with the given credentials. If the
+    connection attempt fails with an 'access denied' error, prompt the
+    user for username using the given username as default, and
+    password. Present the option to cache password. Cache username and
+    password accordingly to user choice. Do not cache passwords by
+    default.
+
+    Return an object implementing the java.sql.Connection interface,
+    representing a connection to the given database, with the given
+    credentials.
+
+    Connection errors, other than access denied are reported as
+    fatal exceptions. User hitting the cancel button on input forms is
+    reported as an 'user canceled' exception.
+
+*** connect(hostname, database, username)
+
+    Search the credentials cache for user password. If it is not
+    found, or the connection attempt fails with an access denied
+    error, prompt the user for username, using the given username as
+    default, and password. Present the option to cache password. Try
+    to connect to the database with the given credentials. When the
+    connections succeeds, cache username and password accordingly to
+    user choice.
+
+    Return an object implementing the java.sql.Connection interface,
+    representing a connection to the given database, with the given
+    credentials.
+
+    Errors, other than acces denied, are reported as fatal
+    exceptions. User hitting the cancel button on input forms is
+    reported as an 'user canceled' exception. Access denied errors are
+    catched and credentials are asked again.
+
+*** connect(hostname, database)
+
+    Search the credentials cache looking for usernames and passwords
+    for accessing the given database. If credentials are not found,
+    there is more than one set of credentials matching, or the
+    connection attempt fails with an 'acces denied' error, prompt the
+    user for username and password. Present the option to cache
+    password. When the connections succeeds, cache username and
+    password accordingly to user choice.
+
+    Return an object implementing the java.sql.Connection interface,
+    representing a connection to the given database, with the given
+    credentials.
+
+    Errors, other than acces denied, are reported as fatal
+    exceptions. User hitting the cancel button on input forms is
+    reported as an 'user canceled' exception. Access denied errors are
+    catched and credentials are asked again.
+
+*** connect()
+
+    Make the user choose between a list of hostname-database-username
+    tuples obtained from the credentials cache, and the possibility of
+    entering a hostname and a database. Then proceed as in the case of
+    the call to connect(hostname, database).
+
+    The list of hostname-database pairs can be initialized with data
+    from the LTPDA toolbox preferences or witj the add() method.
+
+*** connect(plist)
+
+    Same as the previous calls but taking parameters from PLIST.
+
+    If PLIST has a 'connection' parameter, and if this is a valid java
+    object, implementing the java.sql.Connection, return it instead of
+    creating a new connection. If the object does not fulfill the
+    requirements throw a meaningful error.
+
+*** count()
+
+    Return the number of open connections in the connection pool. It
+    has the side effect of removing from the pool any close connection.
+
+*** close(ids)
+
+    Close connections with the given IDs in the connections pool. If no
+    ID is given close all connection.
+
+*** clear()
+
+    Clear credentials cache.
+
+*** add(credentials)
+
+    Add the given credentials to the cache.
+
+
+** Utilities
+
+*** utils.jmysql
+
+    Collection of utility functions for interfacing to a database from
+    a pure matlab environment. Except connect() all the functions take
+    a java object implementing java.sql.Connection as first parameter.
+
+*** utils.jmysql.connect(hostname, database, username, password)
+
+    Return an object implementing the java.sql.Connection interface,
+    representing a connection to the given database, with the given
+    credentials.
+
+    Throw an error if the connection fails.
+
+    This function can be used when the programmer wants full controll
+    on the connection, stepping aside of the connection manager.
+
+    Should not be used in toolbox functions or by the toolbox users.
+
+*** utils.jmysql.execute(conn, query, varargin)
+
+    Execute the query QUERY, with the parameters specified in VARARGIN
+    through the connection CONN. Returns the results in a 2d cell
+    array. See the actual implementation.
+
+    QUERY is a string, CONN is a java object implementing
+    java.sql.Connection, VARARGIN can contain any base matlab type. we
+    can think about introducing the marshalling for time() objects
+    into SQL strings.
+
+    See current implementation in CVS.
+
+*** plist.REPOSITORY_CREDENTIALS_PLIST
+
+    Static method that returns a default plist that specifies the
+    credentials required to connect to a repository.  Should be
+    combined in the default plist of the ltpda methods that require
+    a connection to the database. Those parameters are:
+
+      hostname - Server hostname
+      database - Database name
+      username - User login name
+      password - Password
+
+    In addition to those any ltpda method requiring a database
+    connection should accept a 'connection' parameter in his plist. An
+    open connection can be provided though this parameter. The caller
+    is responsible of closing the connection once it is done with it.
+
+
+* Examples
+
+   function out = useless(a, b, plist)
+
+     % obtain a database connection
+     cm = LTPDAConnectionManager();
+     conn = cm.connect(pl)
+     
+     % ask the database to sum A and B
+     out = utils.jmysql.execute(conn, 'SELECT ?+?', a, b);
+
+     % out is a cell array containing A + B
+     disp out{1};
+
+     % check who is in charge of the connection
+     if conn ~= find(pl, 'connection')
+       % close connection
+       conn.close()
+     end
+
+   end
+
+   This function can be used as follows:
+
+   - useless(1, 2) 
+
+     Will ask the user for hostname, database, username, password.
+
+   - useless(1, 2, plist('hostname', 'localhost', 'database', 'test'))
+
+     Will ask the user for username and password.
+
+   - useless(1, 2, plist('hostname', 'localhost', 'database', 'test', 'username', 'daniele'))
+
+     Will ask the user for password.
+
+   - useless(1, 2, plist('hostname', 'localhost', 'database', 'test', 'username', 'daniele', 'password', 'passwd'))
+
+     Will not ask the user.
+
+   - conn = utils.jmysql.conn('localhost', 'test', 'daniele', 'passwd')
+     useless(1, 2, plist('connection', conn));
+     useless(3, 4, plist('connection', conn));
+     conn.close();
+
+     Anything is asked to the user. The connection is provided by the
+     user and should not be closed inside the function. This is
+     usefull for continuously running automated processes.
+
+
+   This code is a stupid example of connections not being closed properly:
+
+   pl = plist('hostname', 'localhost', 'database', 'test', 'username', 'daniele', 'password', 'passwd');
+   cm = LTPDAConnectionManager();
+   cm.maxConnectionsNumber = 20;
+   for kk = 1:100
+     conn{kk} = cm.connect(pl);
+   end
+   
+   It should fail at iteration 21 with an error similat to
+
+   ### too many connections open to 'localhost'
+
+
+* Use cases
+
+** Interactive usage
+
+   A connection can be obtained from the connection manager in the
+   following ways, requiring user interaction:
+
+   1. Open a connection without providing any credential.
+
+      A. If there are cached credentials show a list of (hostname,
+      database, username) touple, give the possibiity to chose between
+      any of those and creating a new one.
+
+      B. If there are no cached credentials for the (hostname,
+      database, username) touple, or the user choses 'new' in the
+      previeus step, a dialog box asks the user for hostname and
+      database. The hostname field is an editable drop down lists
+      populated with data from the user preferences and cached
+      credentials. ADVANCED: the database filed is an editable drop
+      down list, when the user presses the drop down button, a list of
+      databases is fetch from the server.
+
+      C. If there are cached credentials for the (hostname, database,
+      username) touple go to E.
+
+      D. A dialog box asks the user for username and password and
+      gives the option to remember credentials. Username field is an
+      editable drop down populated with usernames from the credentials
+      cache, the username last entered username this (hostname,
+      database) pair is the default. The provided username is cached
+      into the credential cache.
+
+      E. Connection to the database it attempt. If the connection
+      fails go back to C. If the connection succeds and the user
+      selected the 'store credentials' options save the password in
+      the credentials cache.
+
+      F. Return the connection to the caller.
+
+   2. Open a connection providing hostname and database.
+
+      A. If there are cached credentials go to 1.E.
+
+      B. If there are no cached credentials go to 1.D.
+      
+   3. Open a connection providing hostname, database, username.
+
+      A. If there are cached credentials for the (hostname, database,
+      username) touple go to 1.E.
+
+      A. If there are no cached credentials for the (hostname, database,
+      username) touple go to 1.D.
+      
+   4. Open a connection providing hostname, database, username, password.
+
+      A. Go to 1.E.
+
+** Non interactive usage
+
+   A connection can be obtained without requiring user interaction in
+   the following ways:
+
+   1. Known good credendials can be provided to the connection manager
+      connect() method. If called with four arguments it will not
+      require user interaction if it connects successfully to the
+      database.
+
+   2. Known good credentials can be added to the cache with the connection
+      manager add() method. Any call to the connection manager
+      connect() method specifying at least hostname and database
+      matching the ones inserted in the cache, will then use the
+      cached credentials. User interaction is required if the
+      connection fails.
+
+   3. A connection can be obtained with the utils.jmysql.connect()
+      function and given to the ltpda methods requiring a database
+      connection with the 'connection' plist parameter. In this way
+      the caller has full control on the connection.
+
+** Functions chaining
+
+   In the case where a procedure needs to call several ltpda methods
+   requiring database access, it is recommended that the connection is
+   created in the outermost function, and passed to called functions
+   using the 'connection' plist parameter.
+
+   This avoids to query the user for connection credentials more than
+   once during the executing of the same procedure, even when password
+   caching is disabled. It also avoids the performance penalty of
+   connecting multiple times to the server.
+
+** Multiple users scenario
+
+   Consider the case where two users are sharing the same matlab
+   instance. The two users must be able to interleave the creation of
+   database connections, each one with his own credentials. This can
+   be accomplished in different ways:
+
+   1. If the interleaving of the operations of two users is frequent,
+      the connection manager can be configured, via the user
+      preferences, to never cache passwords. The users will be always
+      queried for their credentials.
+
+      If procedures are well written, each one should not require to
+      enter user credentials more than once.
+
+   2. If the interleaving of the two users is not so frequent, the
+      connection manager can be configured, via the user preferences,
+      to cache passwords for a short time.
+
+   3. The connection manager can be tricked into creating multiple
+      user profiles for the same database connection. For example:
+
+      cm = LTPDAConnectionManager();
+      cm.add(credentials('host', 'db', 'user1');
+      cm.add(credentials('host', 'db', 'user2');
+      
+      Password caching can be enabled. The connection manager will
+      make the user decide which credentials to use. However the use
+      of the user own credentials is not enforced.