EPSG Oracle Plugin
------------------

The ``gt-epsg-oracle`` module makes available to your application a ``CRSAuthority`` implementation that is backed by an Oracle database. You will need to load your Oracle database with the tables distributed by the ``www.epsg.org``.

Notes:

* The ``gt-epsg-oracle`` module is unsupported:
  
  * There is no module maintainer available for assistance (please use the user list for any problem discussions)
  * It is not subject to GeoTools quality assurance standards prior to release

* Please remember that only one authority for "EPSG" should be on your CLASSPATH at a time in order to avoid conflict.

Loading EPSG Tables
^^^^^^^^^^^^^^^^^^^

You can now directly download the EPSG database in oracle form (previously you had to run a script to convert the access database into a format Oracle could import).

1. Go to: http://www.epsg.org/CurrentDB.html
2. Go down to the shipping List and find a file similar to the following:
   
   * Version 6.12: ``epsg-v6_12sql-Oracle.zip``

3. Use the ``EPSG_v6_12.mdb_Tables_Oracle.sql`` file to SQL CREATE all the tables
4. Use the ``EPSG_v6_12.mdb_Data_Oracle.sql`` file to SQL INSERT all the data
5. Use the ``EPSG_v6_12.mdb_FKeys_Oracle.sql`` file to SQL ALTER get all the keys straightened out

Configuration
^^^^^^^^^^^^^^

The ``gt-epsg-oracle`` module is set up to use a DataSource that you provide.::
    
    ReferencingFactoryContainer container = new ReferencingFactoryContainer( hints );


**Oracle DataSource**

The first example is the use of ``OracleDataSource`` - the one provided by your oracle driver.

Direct use of ``OracleDataSource``::
    
    OracleDataSource source = new OracleDataSource();
    source.setUser( user );
    source.setPassword( password );
    source.setURL( url );
    
    Hints hints = new Hints( Hints.EPSG_DATA_SOURCE, source );

Although the above example shows creating an ``OracleDataSource``; usually the instance is configured by your application container and needs to be obtained using a JNDI lookup.

**BasicDataSource**

The next example is the use of ``BasicDataSource`` as provided as part of the commons DBCP project. This is a great choice when using a desktop application.

The following connection management facilities are provided:
  
  ``Hints.EPSG_DATA_SOURCE``
    A DataSource instance, or JNDI name
  
  ``Hints.AUTHORITY_MAX_ACTIVE``
    Maximum number of connections used
    
    Controls the number of database connections the ``gt-epsg-module`` will use at one time.

  ``Hints.AUTHORITY_MAX_IDLE``
   Max number of connection at rest
  
  ``Hints.AUTHORITY_MIN_EVICT_IDLETIME``
    How long before to wait before reclaiming an unused connection
  
  ``Hints.AUTHORITY_TIME_BETWEEN_EVICTION_RUNS``
    How often we check for idle connections

  **Reserved Connections**
  
  ``Hints.AUTHORITY_MIN_IDLE``
    Minimum number of connection at rest
  
  ``Hints.AUTHORITY_SOFTMIN_EVICT_IDLETIME``
    How do we ensure we have this many connections
  
  **Cache Control**
  
  ``Hints.CACHE_POLICY``
    Use "weak", "all", "fixed" or "none"
  
  ``Hints.CACHE_LIMIT``
    Limit on the number of cached results
  
Example using the popular ``commons-dbcp`` implementation of DataSource::
    
    BasicDataSource source = new BasicDataSource();
    source.setDriverClassName("oracle.jdbc.OracleDriver");
    source.setUsername( user );
    source.setPassword( password );
    source.setUrl( url );
    source.setMaxActive(10);
    source.setMaxIdle(1); 
    
    Map config = new HashMap();
    config.put( Hints.EPSG_DATA_SOURCE, source );
    config.put( Hints.AUTHORITY_MAX_ACTIVE, new Intenger( 3 ));
    config.put( Hints.AUTHORITY_MAX_IDLE, Integer.valueOf( 1 ));
    config.put( Hints.AUTHORITY_MIN_IDLE, Integer.valueOf( 0 ));
    

Please be careful can configure your ``BasicDataSource`` to provide more connections then the ``gt-epsg-oracle`` module will ask for.:

* Number of available connections: ``source.setMaxActive(10)``
* Number of connections used::
    
    new Hints(Hints.AUTHORITY_MAX_ACTIVE, Integer.valueOf(3) );

* Ensure that we return the connections (before the DataSource reclaims them)::
    
    Hints.AUTHORITY_MIN_IDLE, Integer.valueOf( 0 );

* If you do not do this the ``gt-epsg-oracle`` module will encounter problems of the following form.::
    
    Database failure while creating a 'CoordinateReferenceSystem' object for code "4326"

**JNDI**

If you are working in an JNDI environment (like a J2EE application) you can specify the name used to lookup the ``DataSource``.::
  
  Hints hints = new Hints( Hints.EPSG_DATA_SOURCE, "jdbc/EPSG" );

You may want to be careful and use a proper JNDI Name::
  
  Name name = initialContext.combineName( "jdbc", "EPSG" );
  Hints hints = new Hints( Hints.EPSG_DATA_SOURCE, name );

Performance
^^^^^^^^^^^

The following hints effect the performance of ``epsg-oracle`` plugin and may be used for performance tuning.

* Desktop
  
  There are only about 8000 things in the EPSG database, you may want to cache them all if you are a desktop
  application::
    
    BasicDataSource source = new BasicDataSource();
    source.setDriverClassName("oracle.jdbc.OracleDriver");
    source.setUsername( user );
    source.setPassword( password );
    source.setUrl( url );
    source.setMaxActive(5);
    source.setMaxIdle(1); 
    
    Map config = new HashMap();
    config.put( Hints.EPSG_DATA_SOURCE, source );
    config.put( Hints.AUTHORITY_MAX_ACTIVE, 5 );
    config.put( Hints.AUTHORITY_MAX_IDLE, 1 );
    config.put( Hints.CACHE_POLICY, "all" );
    
    Hints hints = new Hints( config );
  
  We are going to keep one one "idle" connection available (until it times out) on the off chance we need it again in a
  hurry. A single client is not going to need many connections at once - and after a while the cache gradually take over
  and prevent us using the database at all.
  
  The cache policy of all does have the risk of using up a lot of memory (``MathTransforms`` and so on are cached as you use
  ``CoordinateReferenceSystems`` and so on).

* Server
  
  In a "proper" server environment we need to return the connections as soon as possible. The following settings will
  keep 3 connections in reserve (until they time out) in order to quickly respond to multiple threads.::
    
    BasicDataSource source = new BasicDataSource();
    source.setDriverClassName("oracle.jdbc.OracleDriver");
    source.setUsername( user );
    source.setPassword( password );
    source.setUrl( url );
    source.setMaxActive(20);
    source.setMaxIdle(3); 
    
    Map config = new HashMap();
    config.put( Hints.EPSG_DATA_SOURCE, source );
    config.put( Hints.AUTHORITY_MAX_ACTIVE, 20 );
    config.put( Hints.AUTHORITY_MAX_IDLE, 3 );
    config.put( Hints.CACHE_POLICY, "weak" );
    config.put( Hints.CACHE_LIMT, 1000 );
    
    Hints hints = new Hints( config );
  
  We are using a "weak" cache that will return memory used by coordinate reference systems objects when they are no
  longer in use by any thread. For this server we are expecting only 100 coordinate reference systems to be used (WSG84
  and the UTM zones), but we have chosen a CACHE_LIMIT of 1000 in order to account for all the ``MathTransforms`` between
  these projections.

* Memory
  
  In this configuration we are going to hold a connection open and not cache anything.::
    
    BasicDataSource source = new BasicDataSource();
    source.setDriverClassName("oracle.jdbc.OracleDriver");
    source.setUsername( user );
    source.setPassword( password );
    source.setUrl( url );
    source.setMaxActive(5);
    source.setMaxIdle(2);  
    
    Map config = new HashMap();
    config.put( Hints.EPSG_DATA_SOURCE, source );
    config.put( Hints.AUTHORITY_MAX_ACTIVE, 5 );
    config.put( Hints.AUTHORITY_MAX_IDLE, 2 );
    config.put( Hints.AUTHORITY_MIN_IDLE, 1 );
    config.put( Hints.CACHE_POLICY, "none" );
    
    Hints hints = new Hints( config );
  
  Because we are always going to get a cache miss we are going to hold at least one connection open in order to respond
  quickly to requests. When working with an open connection the ``OracleDialectEPSGFactory`` is quite quick. Please note
  that we are only retrieving the definitions from the database, the referencing subsystem will still "intern"
  ``CoordinateReferenceSystem`` objects (it remembers what objects are in use so that it can prevent the creation of
  duplicates).
