* 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

    Package containing a collection of functions and classes 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.

    NOTE: Currently we use a MySQL database, however all the described
    functions make no assumptions on the kind of database. It would be
    fairly trivial to extend those to work on different databases,
    exploiting the abstraction offered by the JDBC layer. In this case
    a proper name for the package would be utils.db.


*** utils.jmysql.credentials
    
    A simple class for organizing, in a convenient way, the
    credentials required to establish a connection to a database
    server.

*** 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.


# Local Variables:
# mode: org
# End: