COOL - Supported Connection String Formats

To connect to a database, the user must provide COOL the details for the connection.
Those details are passed to COOL API in the form of a connection string.

The allowed formats for a connection string are two:

1. <CORAL connection string>[(<CORAL role>)]/<COOL database name>
2. <engine>://<server>;schema=<schema>;dbname=<COOL database name>;[user=<user>;password=<password>]

The first format is the preferred one, since it allows to take advantage of the CORAL advanced functionalities like replica lookup and automatic fallback, or LFC based replica lookup and authentication.
The second format is provided only for backward compatibility.

CORAL-aware connection string

The connection string is made of 3 parts, the first and the third are compulsory, while the second is optional.

The first part (<CORAL connection string>) is passed directly to CORAL and any CORAL connection string is allowed.
Some examples of simple connection strings are:

• oracle://server/schema
  Connect to the Oracle instance server to use the schema called schema
• mysql://host/database
  Connect to the MySQL server host to use the MySQL database database
• sqlite_file:path to sqlite file
  Connect to the SQLite file identified by path to sqlite file (it can be an absolute or relative path)

Another, more interesting possibility, is to use the CORAL replica lookup functionality and use an "alias" as first part of the connection string.
For example, we can have in the dblookup.xml file the following lines:

<?xml version="1.0" ?>
<servicelist>
  <logicalservice name="MyCOOL">
    <service name="mysql://myserver1/cooldb" accessMode="readonly" authentication="password" />
    <service name="oracle://ora_srv/cool_main" accessMode="update" authentication="password" />
  </logicalservice>
</servicelist>  

you can use the string MyCOOL as the first part of the connection string and CORAL will decide to which server to connect to.

The second part mast be surrounded by parenthesis and it is again CORAL specific. It declares the role to use for the connection.
The role is an abstraction of the concept of user. The role will allow to select from the authentication.xml file which username and password to use to connect to a server.
For example we can have a file authentication.xml like this:

<?xml version="1.0" ?>
<connectionlist>
  <connection name="oracle://ora_srv/cool_main">
    <parameter name="user" value="dafault_user" />
    <parameter name="password" value="PASSWORD" />
    <role name="writer">
      <parameter name="user" value="oracle_writer" />
      <parameter name="password" value="PASSWORD" />
    </role>
    <role name="owner">
      <parameter name="user" value="cool_main" />
      <parameter name="password" value="PASSWORD" />
    </role>
  </connection>
  <connection name="mysql://myserver1/cooldb">
    <parameter name="user" value="mysql_reader" />
    <parameter name="password" value="PASSWORD" />
    <role name="writer">
      <parameter name="user" value="mysql_writer" />
      <parameter name="password" value="PASSWORD" />
    </role>
  </connection>
</connectionlist>

In this case, if you do not specify the role, you will use the user default_user when connecting to the Oracle database of the previous example, while mysql_reader will be used to connect to MySQL. If you specify the role to be writer with something like MyCOOL(writer), you will use oracle_writer and mysql_writer respectively for Oracle and MySQL.

The third part of the connection string must be separated from the first two parts by a "/" and can consist of up to 8 upper case alphabetic characters, numbers or "_" (underscore) (the equivalent regular expression is "[A-Z][A-Z0-9_]{0,7}"). Valid examples are COOLDB, MARCO123, TEST_N1, etc.
This string will identify the COOL database instance inside an Oracle or MySQL schema or inside an SQLite file.

Putting all together, these are few examples of valid connection strings:

• oracle://ora_srv/cool_main(owner)/TEST_1
• mysql://myserver1/cooldb/COOLDB
• sqlite_file:mydir/MyDB.db/MARCO123
• MyCOOL/COOLDB
• MyCOOL(writer)/TEST_1

Old style connection string

As already said, this connection string format is mainly for backward compatibility and can be replaced with an equivalent CORAL-aware connection string.
The old style connection string

is equivalent to the CORAL-aware one

The only thing that is not available with the new format is the explicit specification of username and password.
Note: to make the old-style connection string to work with username and password, both have to be specified otherwise they will be ignored.