CORAL 1.9.1 User guide

12 October 2007


Table of Contents

1. Introduction
2. Software Project Scope
3. CORAL Architecture
4. Developing Client Software Components
5. Public API Description
6. Public API Reference
7. Implementation Components

About this document

CORAL is a software toolkit which is part of the LCG Persistency Framework. This document is a User Guide targeting mainly developers of software components using directly the CORAL API.

The User Guide describes the overall architecture of the system as it has derived from the need to serve the relevant use cases coming from the LHC experiments. It gives guidelines for developing efficient CORAL-based components and applications. Finally, it describes all released components in detail.

1. Introduction

CORAL is the set of software deliverables of the "Database Access and Distribution" work package of the POOL project. The development of libraries for the vendor-independent database access, collectively referred to as the Relational Abstraction Layer (RAL), started within POOL in spring 2004. At that time the main requirement that needed to be met was the creation of an insulation layer that would allow the development of software components responsible for accessing data in RDBMS without the knowledge of the subtle differences among the various technology-specific solutions. The access patterns which had to be served were the ones followed by the existing relational components of POOL, which were implemented based on particular technologies, and were used either in production (FileCatalog, Collection) or in validation/prototyping (Relational Storage Manager, Conditions Database) environments.

The RAL is publicly available with every POOL release as of June 2004 together with the first direct clients such as the RelationalFileCatalog and RelationalCollection components. Since then the usage of RAL in POOL has been extended with the development of the RelationalStorageService component and the COOL (Conditions Database) project. At the same time within the LHC experiment software projects new applications have been developed using directly the RAL public API in order to serve different use cases from the ones which were the basis for the definition of the RAL architecture and design. Given that the software components based on RAL are expected to be used in a grid-enabled and distributed environment, RAL had to be extended in order to address issues such as database service indirection, client-side monitoring and certificate-based authentication mechanisms.

In spring 2005 a formal review took place of the existing RAL API with feedback from the direct clients of RAL, within and outside POOL. In this review all the emerging use cases have been considered. The outcome was the design of an improved version of RAL which is now being developed and packaged independently of the rest of the POOL components under the new name CORAL (COmmon Relational Abstraction Layer).

2. Software Project Scope

2.1. A Relational Abstraction Layer

The primary goal of CORAL is to provide functionality for accessing data in relational databases using a C++ and SQL-free API, shielding the user from the technology-specific APIs and removing at the same time the need to submit directly SQL commands. Therefore CORAL allows the development of software components that can be used without any code modification or conditional constructs against multiple relational technologies.

A user is not expected to be an expert on all possible optimization techniques relevant to a particular relational technology. This is handled as much as possible by the RDBMS-specific implementations of the CORAL interfaces. In fact, a user is expected to be familiar only with the basic concepts of relational database systems, while the only SQL fragments that would ever have to be provided are the condition clauses in queries. On the other hand, the CORAL interfaces have been designed such that a user is guided towards standard "best" practices in RDBMS programming, such as variable binding, result-set prefetching, bulk operations, etc.

The CORAL API is limiting the set of generated SQL statements to a relational server to a rather small subset of whatever would be possible through an interface for issuing arbitrary SQL statements. Thanks to this feature, applications which are based on CORAL are easier to monitor and tune.

2.2. CORAL and Database Deployment on the Grid

CORAL is expected to be used by applications running on a grid-enabled and distributed environment. In such environment there is a need to

  • allow for physical database service indirection,
  • separate the database connectivity from the authentication mechanisms,
  • enable certificate-based authentication mechanisms,
  • enable client-side connection pooling and service failover,
  • enable client-side monitoring.

The acritecture and design of CORAL provides the relevant infrastructure and mechanisms addressing these issues.

2.3. What CORAL is NOT

  • A general purpose C++ connectivity library, such as ODBC, JDBC, the python or perl DBI.
  • A system to accomodate all possible use cases for accessing relational data. It is restricted to the use cases relevant to the data handling and analysis of the LHC experiments.
  • A system to perform C++ object I/O with an RDBMS.

3. CORAL Architecture

3.1. Primary Use Cases and Software Requirements

CORAL has been developed in order to provide technology independent access to relational databases for the various components of POOL, the Conditions Database project (COOL) and several applications and components of the software frameworks of the LHC experiments. The access patterns that are possible through the CORAL API reflect the needs of the applications of the LHC experiments.

In order to achieve the maximum insulation from the technology-specific implementations, we have chosen to minimize the SQL exposed through the public API of CORAL to the clients. Given that the CORAL API is a set of C++ interfaces (mainly pure abstract classes), no SQL types are exposed to the user for the description of tables, views and query result sets. The SQL to C++ type mapping (and vice versa) is handled by the technology-specific implementation component. The need for the maximal SQL insulation from the client software can be demonstrated simply showing the SQL statements for the two mostly used technologies (MySQL and Oracle) in rather simple tasks:

  • Creation of a table
    • MySQL SQL syntax:
      CREATE TABLE T_t ( I BIGINT, X DOUBLE )
      
    • Oracle SQL syntax:
      CREATE TABLE "T_t" ( I NUMBER(20), X BINARY_DOUBLE )
      
  • Query fetching only the first rows of the result set
    • MySQL SQL syntax:
      SELECT X FROM T_t ORDER BY I LIMIT 5
      
    • Oracle SQL syntax:
      SELECT * FROM (SELECT X FROM "T_t" ORDER BY I) WHERE ROWNUM < 5
      

The CORAL approach is to present an identical API for such cases, where from the functionality point of view are identical (among the technologies), but the necessary SQL statement is significantly different.

The design of CORAL addresses the requirement of being able to use different authentication mechanisms (user/password pairs, certificates) when connecting to a database schema. Moreover, it takes into account the fact that databases are addressed logically in a user application, which means that there might be a level of indirection before connecting to an actual database.

Finally, in a distributed environment there is always a need for some client-side monitoring of the operations with a database. CORAL had to allow the software plugging of adapters which send the relevant information to existing monitoring frameworks.

3.2. Architectural Choices

CORAL has adopted a component-based architecture for several reasons:

  • Minimization of the compile-time dependencies of any client software component.
  • Implementation of the insulation layer in terms of abstract interfaces.
  • Enabling the parallel and independent development of the various CORAL components, hence more efficient unit and integration testing.
  • Allowing for multiple implementations of the same interfaces.
  • Allowing for the self-consistent deployment of only a subset of the full CORAL library set.

The realization of the component architecture has been based on the plugin management and the component framework of the SEAL project. This allows the development, testing and deployment of new implementation components without any single change in existing libraries and the configuration. Moreover, the SEAL component framework and its context hierarchy in particular, allow the various implementation components to make use of the functionality of other components exclusively through the relevant abstract interfaces without selecting by value (i.e. by specifying the corresponding logical name) any particular implementation. Instead, a component performs a search in its context hierarchy for any implementation of a particular interface.

The requirement of flexible authentication mechanisms in a secure, distributed and grid-controlled environment had a consequence that a connection to a database is attempted, the authentication credentials should not be provided directly by the client software. Instead, appropriate authentication modules have to be invoked to provide the necessary credentials depending on the "database role" of the user and the read/write accessibility of the particular database. This lead to a design of the CORAL API with decoupled sets of interfaces for database connection and for the retrieval of the authentication credentials. The decoupled interface sets were therefore implemented by completely decoupled components.

The same approach has been followed for the functionalities of client-side monitoring and database service lookup. This lead naturally to an architecture with the minimum number of dependency levels among the software components.

Most implementation components implement a set of the public interfaces. There are also cases where components implement "developer-level" interfaces, which are simply abstract classes, not useable by the client software, but only by the various CORAL components. This is the case for example of the components implementing the interfaces related to the client-side monitoring, where the interfaces are called only by the components implementing the RDBMS-specific functionality. This can be shown schematically in the following picture:

It has to be noted that there is also a rather granular class hierarchy of C++ exceptions that are thrown in case of error conditions. The granularity in the exception hierarchy allows the client code to follow different recovery procedures depending on the source of the error and the conditions under which it occurs.

3.3. Static view of the architecture

The CORAL packages and their dependencies are shown in the following picture:

The public API of CORAL is defined in the CoralBase and RelationalAccess packages. The former contains the definitions of the name/type/value structures (AttributeList and related classes) which are used as row buffers for writing and reading data from a database and a Blob C++ type which is used for I/O with table columns of SQL type BLOB. The latter contains the user- and developer-level interfaces (abstract classes) which expose the full functionality of CORAL. A client software component is expected to include the header files only from these two packages and link against the two corresponding libraries.

The interfaces defined in RelationalAccess are implemented in several component libraries which are loaded at run time and only at demand by the SEAL plugin management mechanism. Shared implementation code is kept in the CoralCommon package. With this arrangement we have achieved to minimize the number of dependency levels and code duplication across the various packages.

Currently there are

  • four RDBMS-specific implementations for the interface set related to the functionality of accessing data in a relational database (Oracle, SQLite, MySQL and Frontier),
  • two implementations of the interface set related to the retrieval of authentication credentials (one based on XML files and another on environment variables),
  • an implementation of the interface set responsible for peforming the necessary technology dispatching given a connection string (RelationalService),
  • two implementations of the interface set responsible for peforming logical to physical database service lookup operations (one based on XML files and another on LFC),
  • an implementation based on LFC for both the lookup and authentication service funtionality,
  • a simple implementation of the interface set responsible for registering and reporting monitoring events generated by the RDBMS plugins (MonitoringService),
  • an implementation of the interface set responsible for managing open connections on the client and for the overall system configuration (ConnectionService)

In addition to the above, there is also the PyCoral package, which provides a coral python module which exposes the CORAL User API in python.

The CORAL architecture allows the development of additional or alternative implementations of the base interfaces which can work immediatelly with the rest of the system. The only necessary requirement in order to achieve that is the strict compliance to the interface semantics.

4. Developing Client Software Components

4.1. Building a component against CORAL

In order to develop applications or software components based on CORAL one should have in the INCLUDE path the include directory of a CORAL installation and link against the RelationalAccess and CoralBase libraries. Moreover, since CORAL depends on SEAL for the definition of the 64-bit integer, and the its Exception class, one should also have in the INCLUDE path the include directories of a SEAL installation of the relevant version and link against the SealKernel, PluginManager and SealBase libraries.

A CORAL release x.y.z is installed under the afs directory

/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_X_Y_Z
The INCLUDE path should contain the directory
/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_X_Y_Z/include
and the libraries for a platform (operating system / compiler / compiler flag) PLATFORM under
/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_X_Y_Z/PLATFORM/lib

This means that for a linux plaform the compile/link statement of a simple CORAL application should be something like the following:
g++ -o MyCoralApplication MyCoralApplication.cpp \
     -D_GNU_SOURCE -DGNU_SOURCE -fPIC -pthread -pipe -ansi -pedantic-errors \
     -I/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_X_Y_Z/include \
     -I/afs/cern.ch/sw/lcg/app/releases/SEAL/SEAL_Xx_Yy_Zz/include \
     -I/afs/cern.ch/sw/lcg/app/releases/SEAL/SEAL_Xx_Yy_Zz/PLATFORM/include \
     -L/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_X_Y_Z/PLATFORM/lib \
     -llcg_coral_RelationalAccess -llcg_coral_CoralBase \
     -L/afs/cern.ch/sw/lcg/app/releases/SEAL/SEAL_Xx_Yy_Zz/PLATFORM/lib \
     -llcg_SealKernel -llcg_PluginManager -llcg_SealBase \
     -luuid -lpcre -lnsl -lcrypt -ldl -Wl,-E

4.2. Setting up the run-time environment

In order to run an application which is using CORAL one should include in the LD_LIBRARY_PATH environment variable (the PATH variable for win32 platforms) the library directories of CORAL, SEAL, and of the external packages used either directly (eg. uuid) or at run-time when a plugin is loaded. For example the XML-based implementation plugins will require the shared libraries for XercesC, the Oracle plugin will require the Oracle instant client libraries, etc.

Moreover, in order for the system to know which plugins are available the SEAL_PLUGINS path environment variable should include the directories

/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_X_Y_Z/PLATFORM/lib/modules
and
/afs/cern.ch/sw/lcg/app/releases/SEAL/SEAL_Xx_Yy_Zz/PLATFORM/lib/modules

For the development of new plugin implementations of the CORAL interfaces, one should append to the above environment variables the directories where the corresponding libraries and plugin definitions reside.

4.3. External dependencies and supported platforms

The currently supported platforms are:

  • slc3_ia32_gcc323_dbg
  • slc4_ia32_gcc34
  • slc4_ia32_gcc34_dbg
  • slc4_amd64_gcc34
  • slc4_amd64_gcc34_dbg
  • osx_ia32
  • osx_ia32_dbg
  • win32_vc71_dbg

The external dependencies are listed in the following table:

External packageVersionRequired byPlatform
SEAL1.9.3AllAll
Boost1.33.1SEALAll
uuid1.38SEALAll
pcre4.4SEALAll
rx1.5SEALwin32_vc71_dbg
XercesC2.7.0XMLLookupService, XMLAuthenticationServiceAll
Oracle (OCI)10.2.0.3-fullOracleAccessAll
SQLite3.4.0SQLiteAccessAll
MySQL5.0.18MySQLAccessAll
frontier_client2.7.4FrontierAccessAll but win32
expat1.95.8frontier_clientAll but win32
lfc1.6.5LFCLookupService, LFCReplicaServiceslc3_ia32_gcc323(_dbg)
vdt1.2.3_globusrls215blfcslc3_ia32_gcc323(_dbg)

4.4. Useful guidelines and programming tips

Here is a list of tips which address the most frequently encountered problems.

  • USE BIND VARIABLES WHEREVER YOU CAN.

    CORAL takes care for it automatically wherever this is possible, but whenever queries are issued it is up to the user to ensure that this is done.

  • Try to always define the output specification in a query. Since there is not a 1-1 mapping between C++ and SQL types one should not rely on the output C++ type as it is guessed by the backend.
  • Note that whenever specifying the type of a column variable in a table, the call is a hint to the relevant backend. The reasons are the same as for above.
  • Keep a pointer to an IConnectionProxy in your classes, and release it as soon as you are done with the underlying database.
  • Avoid "injecting" SQL through the CORAL API, since it may result to technology-specific code.

5. Public API Description

5.1. Description of the CoralBase API functionality

5.1.1. Data Types

All operations with the CORAL API use exclusivelly C++ types. No SQL types are exposed. This means that column variables appearing in table descriptions and query result sets are mapped to a compatible C++ types. The C++ types supported in CORAL are:

  • All the primitive C++ types: bool, (unsigned) char, (unsigned) short, (unsigned) int, (unsigned) long, float, (long) double.
  • The string type of the C++ standard library: std::string
  • The platform independent 64-bit integer types provided by SEAL (SealBase): seal::IntBits<64>::SLeast (signed 64-bit integer), seal::IntBits<64>::ULeast (unsigned 64-bit integer)
  • The BLOB type defined by CORAL (CoralBase): coral::Blob
  • The DATE type defined by CORAL (CoralBase): coral::Date
  • The TIMESTAMP type defined by CORAL (CoralBase): coral::TimeStamp

The Blob type is a wrapper class of a contiguous memory segment, which can be resized on user request. This type is suitable for I/O of raw data or LOB SQL types.

The Date and TimeStamp types wrap the seal::Time class in order to perform I/O to tables containing DATE and TIMESTAMP columns.

5.1.2. The AttributeList

The main interface which is used for all the data exchange between a CORAL-based software component and a relational database is the AttributeList class. It is a container of Attributes, which are named variables of simple types (listed above). An Attribute holds the actual data of the named variable and its Specification, which holds its name and its C++ type. The Attribute also holds the null-ness of the variable, a concept which is inherent in SQL variables but absent in C++ ones. The AttributeList can therefore be thought of as a buffer holding the data of a table row. It is used both for input and output operations.

The Attribute class provides access to the actual data which is either fast or accompanied with type checking. This allows a user to choose the access method that is mostly relevant to the use case or problem in consideration.

The AttributeList and Attribute classes have been designed and optimized for database I/O operations. For this reason the following capabilities have been added to their basic functionality:

Sharing of data
The actual C++ variable of an Attribute can be shared among other Attributes. This allows the construction of different AttributeLists accessing the same data, thus avoiding the need of copying and bookkeeping. For this reason, the variables in the Attributes are reference counted.
Binding of external data
A user can define Attributes which correspond to variables that are external to the class. This capability facilitates the I/O of complex structures to a database without intermediate data copies.
Sharing of specifications
Attributes and AttributeLists may share specifications. This feature, among other things, reduces the memory consumption of software components which create and destroy such objects rather frequently.

5.2. Example of usage of the CoralBase API

Examples of usage of the Blob and AttributeList types can be found under the CoralBase unit tests:

5.3. Description of the RelationalAccess API functionality

5.3.1. Design of the Interfaces

5.3.1.1. UML diagram of the abstract classes

The public interfaces of the CORAL API are depicted in the following UML class diagram.

5.3.1.2. Exceptions

Most of the methods of the abstract interfaces return a void or a reference to an object. In case of error conditions there is usually an error message in the form of a SEAL message stream that is generated by the implementation plugin. In addition to that a relevant C++ exception is thrown. All exceptions thrown by coral derive from the base coral::Exception class, which in turn is an extension of the SEAL Exception. Which specific exception is thrown in each case is documented in the methods of the RelationalAccess interfaces and it is part of the method semantics. The full exception hierarchy is shown in the following UML class diagram.

5.3.2. Accessing a database

5.3.2.1. The Relational Service and Technology Domains

The software entry point for accessing a relational database through the RelationalAccess package is the IRelationalService interface. CORAL provides a default implementation as a SEAL component with the label "CORAL/Services/RelationalService". For more details refer to the documentation of the RelationalService package.

The IRelationalService interface provides access to IRelationalDomain objects for the various different technology types. The IRelationalDomain interface provides information about the technology type, the implementation of the corresponding plugin module and its version.

An IRelationalDomain object is retrieved by the IRelationalService specifying either the technology type, or a connection string (which contains the technology type). The IRelationalDomain is the base class for the corresponding SEAL component of a technology plugin. The component should be named according to the technology name with the following convention: If the technology type is "MyRDBMS", then the IRelationalService will be searching for a plugin labeled as "CORAL/RelationalPlugins/MyRDBMS". Optionally the implementation name can be specified. In this case the plugin should be labeled as "CORAL/RelationalPlugins/MyRDBMS/MyImplementation".

The Relational Service may handle several implementation plugins for a given RDBMS technology. By default a native implementation (a plugin where the implementation name is not specified in its label) has a higher priority than all the others. A user can at any moment change the default implementation for a given technology either through the IRelationalService API, or by setting accordingly the corresponding property of the Relational Service implementation.

5.3.2.2. Connection strings

The IRelationalDomain interface acts as a factory for IConnection objects which are used for the physical connection to a relational database. Such an object is obtained by specifying a connection string which has to have the format

technology_protocol://hostName:portNumber/databaseOrSchemaName
where technology can be oracle, mysql, odbc, etc. (all small letters) and refers to the actual plugin implementing the RelationalAccess abstract interfaces. The protocol is optional and can be file, http, etc. It is used for server-less, file-based databases, like SQLite. The port number is optional as well.

With a given IConnection object a user may start a session, using the ISession interface, with its associated transactional context. In case the back-end allows, for the same connecion, several parallel and independent sessions may me started.

5.3.2.3. The authentication mechanism

The ISession interface allows a user authenticate to a database specifying a user name and a password. Alternatively a reference to an IAuthenticationService object may be passed explicitly or implicitly looking for such a registered service anywhere in the corresponding context tree.

The IAuthenticationService is a simple interface which returns for a given connection string the connection credentials (typically user name and password) through an IAuthenticationCredentials object. It has been introduced in the design of the component so that authentication parameters need not appear in the connection string, which may have to be shared by users with different access rights. Refer to the relevant documentation for the semantics of the existing implementations of this interface.

5.3.2.4. Service Indirection

The CORAL API accomodates the use case where relational data may be distributed on a computing grid. In this case a user addresses a database service not through its physical characteristics, but rather using a logical name. The ILookupService class defines the interface that a software module performing logical-to-physical name lookups for database services. A user, may specify not only the logical name, but may also restrict the lookup with the access mode (database role) and the authentication mechanism for the available replicas. The result of the lookup operation is an IDatabaseServiceSet object, which is a container of IDatabaseServiceDescription objects. An IDatabaseServiceDescription describes a replica which fulfills the lookup criteria. The ILookupService returns the available replicas in the order that they should be tried out.

The Lookup Service can be through of as the equivalent of the File Catalog in the POOL framework.

The ILookupService interface delivers the list of all the available replicas corresponding to a logical name with an ordering which is dependent on the back end. In grid applications accessing databases in a distributed environment a re-ordering might be desirable in order to pick up first the most proximate database service. To this end the CORAL API defines an IReplicaSortingAlgorithm.h interface whose implementations can be used to encode any ordering logic.

5.3.2.5. Client-side Monitoring

In a production environment it is important to monitor the activities in a database server. The monitoring output gives hints not only on how to tune applications and optimize schemas. It also contributes towards the definition of the most efficient deployment models and hardware configurations. Server-side monitoring is usually a responsibility of the database administrators. It produces information such as the performance of the server as a function of the number of concurrent clients running an application, or the most frequent or resource consuming operations.

Client-side monitoring can be used to gather information which is specific to a particular application. Combined with the information of the server-side monitoring it may reveal the contribution of the specific application to the server load and/or the need for tuning of the application itself, or it may hint problems in the deployment model.

CORAL facilitates client-side monitoring with the IMonitoringService developer-level interface. It is an interface which is used by the RDBMS-specific plugin implementations to register events that need to be monitored. Such events are the connecting to and disconnecting from a database server, the starting and ending of a user session, the signaling of transaction boundaries, and mostly important the issuing of SQL statements and the associated execution time.

In order for a RDBMS-specific plugin implementation to record monitoring data it is necessary that there is an implementation of the IMonitoringService interface, defined as a SEAL component, reachable by the plugin through its local context tree (like an IAuthenticationService implementation). Moreover, the user should explicitly enable the monitoring, specifying also the verbosity level, through the IMonitoring interface accessible through the ISession.

The reporting of the monitoring events is done through the IMonitoringReporter interface accessible from the IMonitoringService object.

5.3.2.6. Connecting to a database in the most generic way

In CORAL, connecting to a database, starting a user session and authenticating are three separate operations. The IConnection and ISession interfaces have been designed such that they can facilitate application-level connection pooling. The IConnectionService is the interface for components that are responsible for providing to the client components handles to physical connections that are re-used and probed for the status of the physical connection before they are made available for use. In particular, the IConnectionService is an abstract factory for ISessionProxy objects which make sure that a physical connection to the underlying server is always valid.

The use of the IConnectionService is highly recommended in applications in a distributed environment, since any implementation is responsible of provide a valid ISessionProxy object which corresponds to the best replica of a logical database service. In that respect, the role of an IConnectionService implementation is the general coordination of the various high-level CORAL components. Thus an IConnectionService has to:

  • Load if necessary an implementation of the IRelationalService interface and use it internally in subsequent calls.
  • Load if necessary an implementation of the IAuthenticationService and use it internally in subsequent calls.
  • Load if necessary an implementation of the IMonitoringService and issue calls to the IMonitoring interface of the ISession objects it manages.
  • Load if necessary an implementation of the ILookupService and use it whenever a logical database service name is specified in a request for an ISessionProxy.

The Connection Service will also make sure that the number of physical connections to a given database is minimized, by creating several ISession objects under a single IConnection.

The IConnectionService interface can also be used for configuring a CORAL-based application. In particular, its IConnectionServiceConfiguration interface can be used to select the particular implementations of the various interfaces that should be loaded. Moreover, it can be used to override the default parameters that are related to the connection re-attempting and replica failover mechanisms.

Finally, it provides access to IMonitoringReporter interface of the loaded IMonitoringService, to allow a user trigger the reporting of the monitoring events.

Given that the IConnectionService is the logical entry point for CORAL, as of release 1.7.0, a ConnectionService facade object is defined, which implements the IConnectionService interface and hides all the underlying complexities related to the loading and looking up of the various plugin libraries.

5.3.2.7. Transactional context

Every operation with a relational database using the RelationalAccess component is executed within a transactional context. There is a single transactional context for each session. A reference to the ITransaction interface can be retrieved by an ISession object once a connection has been established.

A user can start a transaction in an update (default) or read-only mode, commit and roll back the changes.

5.3.3. Managing schemas

Once connected to a relational database, the user works with the data defined under the schema defined for the table collection (MySQL database, Oracle user schema, SQLite file, etc.) specified in the connection string. The ISchema interface which is retrieved by the ISession (or ISessionProxy) object allows the user to list, create and drop tables in the working schema. This is the nominal working schema, defined by the connection string itself. A user may also work with any other named schema for which the necessary access rights have been granted, through the relevant call in ISession (or ISessionProxy). The ISchema object which is obtained is completely independent from the nominal or other named ones. The only things that are shared are the transaction sequence and the type converter.

The ISchema can be used to retrieve a reference to an ITable object specifying its name. This interface allows the user to

  • retrieve the description of the corresponding table via the ITableDescription interface

  • set the access privileges using the ITablePrivilegeManager interface

  • alter the table definition using the ITableSchemaEditor interface

  • perform data manipulation on the table through the ITableDataEditor interface

  • execute queries with the data of the corresponding table

The ITableDescription object allows the user to retrieve the following information from a table:

  • The column names and types (the C++ equivalents)
  • The NULLness and the UNIQUEness of a column
  • The definition (column names) of its primary key, in case there is one, using the IPrimaryKey interface
  • The definitions (key name, column names, referenced table, referenced columns) of its foreign keys using the IForeignKey interface
  • The definitions (column names, uniqueness) of its indices using the IIndex interface
  • The definitions (column names) of its multi-column unique constraints using the IUniqueConstraint interface

In order to create a new table in the schema the user has to construct an ITableDescription object and pass it to the ISchema. The component provides the TableDescription class which implements the ITableDescription and ITableSchemaEditor interfaces for this purpose.

Views can be created using IViewFactory objects which can obtained from the ISchema interface. They extend the IQueryDefinition interface and are used to create IView objects, which can be used to retrieve the original definition of the created views and to describe their columns.

By default, for most technologies, a table or view is created without any access right to anybody but the table owner. It is therefore a good practice for tables that will be exposed to other database users to use the ITablePrivilegeManager interface to grant the relevant access rights immediatelly after the table creation.

Note that the semantics of the interfaces assume that the names of the tables, views, columns, keys, indices and constraints are case sensitive. Given though that some databases do not preserve the case, it is highly recommended to capitalize all such system names, especially if the CORAL is expected to be used for cross-populating databases of different technologies.

5.3.4. Performing data manipulation

The ITableDataEditor allows the privileged user perform DML operations on a table. In particular it is allowed to

  • Insert a new row. The input row is fed with an AttributeList with an AttributeListSpecification that is compatible to a subset of the table's columns and types.
  • Modify existing rows. In this case the SET and WHERE clauses of the corresponding SQL statement have to be provided by the user. The user is recommended to use bind variables for the WHERE and SET clauses which can be passed through an AttributeList.
  • Delete existing rows. In this case the WHERE clause of the corresponding SQL statement have to be provided by the user. The user is recommended to use bind variables for the WHERE clause.
  • Insert query result sets (INSERT-SELECT statements) using an IOperationWithQuery object.
  • Perform bulk operations of the above using IBulkOperation and IBulkOperationWithQuery objects. Such operations are highly recommmended in repetitive operations where only the input data change. In this case the user specifies the row buffer to be bound and the number of rows to be cached at the client side before the data are actually sent to the server.

5.3.5. Issuing queries

Queries can by issued using the IQuery interface. The ISchema and ITable interfaces acts as a factories of IQuery objects. The IQuery class extends the IQueryDefinition class, which is the interface for defining the query logic. A query is formed by

  • Specifying the output variables. In case the IQuery object has been constructed from an ITable, if no variable is specified a wildcard query is formed. For each variable an alias can be specified.
  • Specifying the table list. In case the IQuery object has been constructed from an ITable this is not required. In case of a general query the name of a table or a view can be specified, providing optionally an alias.
  • Defining the query condition. The user has to specify the WHERE clause of the corresponding SQL statement. It is highly recommended to use bind variables passing the corresponding AttributeList.
  • Specifying order variables. By doing so the user instructs the system to append to the SQL query an ORDER BY clause.
  • Limiting the number rows in the result set. This is a very useful operation when the maximum number of rows that will be retrieved at a query is known.
  • Defining sub-queries. A user can define a sub-query by retrieving a reference to an IQueryDefinition, whose result-set can be used as an input table in the parent query.
  • Apply set operations. A user can define a query by retrieving a reference to an IQueryDefinition which appears as the RHS query in the resulting SQL statement.

The IQuery interface controls the actual execution of the query by:

  • Defining the return type of the output variables The return type of the output variables or expressions is internally deduced by the RDBMS client software. However, the user may redefine the output types, either fully by specifying an AttributeList to be used as the output buffer, or by partially by specifying the C++ type of a column in the result set.
  • Defining the number of prefetched rows or the size of the buffer on the client side for the rows if the result set. This operation can be used to reduce the number of roundtrips to the server for fetching the result of a query.
  • Locking the rows of the result set. This operation blocks other concurrent clients from updating the affected rows. This is a useful feature which serves the use cases where the protection of data consistency from parallel write/update applications is required.

On execution the IQuery interface returns a reference to an ICursor object. It is simply an iterator over the rows of the result set. The Attributes in the output buffer (AttributeList) have the values of the underlying variables updated in every iteration. The associated boolean variables expressing the NULL-ness of the values are updated as well.

5.3.6. Controlling the behaviour of web caches

Some RDBMS implementations may use a web cache for fast retrieval and efficient deployment of read-only data. This comes with the cost of the risk that data may become stale from some moment and onwards. Depending on the data model an application may be designed that some tables should always be refreshed before they are accessed for the first time within an application.

CORAL allows an application to fully control of web caches through the use of the IWebCacheControl interface, that can be retrieved from an IConnectionService object. This interface allows the client component to define policies related to a particular schema, as this is defined by the logical or physical connection string. The RDBMS plugins may use then the caching policies defined by the client components, through calls to the IWebCacheInfo objects corresponding to the particular schema.

6. Public API Reference

6.2. Reference documentation of the RelationalAccess public interfaces

7. Implementation Components

7.1. PyCoral Semantics

7.1.1. Definitions and capabilities of the coral python module

PyCoral is an extension module of python, developed using the python C API. It is a python interface to the CORAL package or in other words it provides CORAL equivalent functionalities to python programmers. The coral module can be directly imported to python environment by using the "import" command of python. In other words, just after starting the python interpreter one just needs to type in "import coral" in the python interpreter for using the various classes and functions of the coral module. The subsequent invocations of any of the coral module specific classes and functions will require prepending the class name with "coral.". For avoiding the prepend string one must load the coral module using the command "from coral import *" in the python interpreter.

There are two types of classes in the coral module, namely the "exposed" classes and the "unexposed" classes. The "exposed" classes are the ones which are directly available to the user for instantiation. The "unexposed" classes are the ones which can never be directly used by the user for creating objects. The objects of such types will be return values of some function of the exposed types or unexposed types. Following are the seven (07) "exposed" classes of the coral module: "ConnectionService", "Context", "AttributeList", "Blob", "Date", "TableDescription", and "TimeStamp".

Following is the list of "unexposed" classes of the coral module: "IConnectionServiceConfiguration", "AttributeSpecification", "Attribute", "AttributeListIterator", "ICursorIterator", "ISessionProxy", "ISchema", "IQueryDefinition", "IOperationWithQuery", "IBulkOperation", "IBulkOperationWithQuery", "ITable", "ITableDataEditor", "ITableSchemaEditor", "ITableDescription", "ITablePrivilegeManager", "ISessionProperties", "IPrimaryKey", "IForeignKey", "IUniqueConstraint", "IIndex", "IColumn", "ITransaction", "ITypeConverter", "IQuery", "IWebCacheControl", "IWebCacheInfo", "ICursor", "IView", "IViewFactory", and "IMonitoringReporter"

7.1.2. Setting up the environment

The PYTHONPATH path environment variable should contain both the lib and python subdirectories of a CORAL installation.

7.1.3. Examples of usage

Example 1: Importing coral module into the python interpreter and then instantiating one AttributeList with list1 as its name.

import coral
list1 = coral.AttributeList()
OR
from coral import *
list1 = AttributeList()

Example 2: Reading and writing python objects into Blob with cPickle.

The following example creates an AttributeList named "w" then it extends the AttributeList to have a "blob" type attribute. Then a tuple named "li1" is generated with item values from 1 to 499. The tuple is then written using the dump method of the cPickle module. The serialized tuple kept in blob can be read using the loads or load function of cPickle module.

import cPickle //import pickling module
import coral // import coral module
w = coral.AttributeList() // create an attributeList named w
w.extend("h","blob") // Extend the attributeList with blob type object
li1 = [] // create an empty tuple named "li1"
li2 = [] // cretae an empty tuple named "li2"
for j in range(1,500):
 li1.append(j) // append the int values 1 to 499 to the tuple "li1"
cPickle.dump(li1,h,1) // serialize li1[] to the blob named "h"
li2 = cPickle.load(w[0].data())
OR
li2 = cPickle.loads(w[0].data().readline()) // unserialize the contents of the 
                                               blob held at the 0th index of 
                                               the attributeList into tuple 
                                               "li2", thus copying "li1" to 
                                               "li2"

Example 3: Various ways of accessing bufferRows of the AttributeList using the cursor.

cursor = query.execute() //cursor created with seperated line of code
while (cursor.next()): // C++ style
 currentRow = cursor.currentRow()
 print str(currentRow)
OR
for currentRow in cursor: // Python style
 print str(currentRow)
OR 
for currentRow in query.execute(): // Python style without creating cursor 
                                      using another line of code
 print str(currentRow)

Example 4: Various ways of accessing attributes in an AttributeList

print attrList[0].data() // Prints data value for attribute at index 0
OR
print attrList['X'].data() // Prints data value for attribute with name 'X'
OR
for attribute in attrList:
 print attribute.data()   // Prints data value of all the attributes in
                             the attributeList one ny one using the iterator
                             protocol

Example 5: Setting an attribute value to NULL.

attrList[0].setData(None) // Sets the value of the attribute at index 0 in the 
                              attrList to NULL
OR
attrList['X'].setData(None) // Sets the value of the attribute with name as 'X'
                                in the attrList to NULL

Example 6: Compare two attributeLists

cmp(list1,list2) // compares the two attribute lists list1 and list2
                 // 0 is returned for success and -1 for failure

Example 7: Printing string representation of the AttributeList

print str(attList1) // prints the string representation of the attList1.

7.2. PyCoral Reference

7.3. ConnectionService Semantics

7.3.1. Architecture and object hierarchy

The ConnectionService API is based on two main: the IConnectionService class, which acts as a central service to open the session on the database backends, and the ISessionProxy, providing proxy objects to the physical database sessions. With the ConnectionService API, the user can configure most of the parameters required for the setting up of specific service implementation (authentication, database back-end instantiation, connection string resolution, monitoring) and specific policies for the handling of connection pooling, retries and failover. The configuration is accessible on a specific interface, the IConnectionServiceConfiguration. More specific parameter access are provided by the ISessionProperties (for a few session-specific quantities), the IWebCacheControl (for setting up of Web-cache related policies), the IMonitoringReporter (for the access to monitoring data).

7.3.2. Service connection via session proxy

The ISessionProxy provides access to the database schema and the transaction control.

To obtain ISessionProxy object, one must provide a connection string specifying the service to be accessed. The string can explicitly describe a physical database service (when formed according to the specific rules supported by the CORAL relational service). Alternatively, the string can be an user-defined label, mapped logically to one or more explicit (physical) connection strings (replicas). The mapping between logical connection strings and their replicas is maintained and handled through the ILookupService interface, with its concrete implementations.

7.3.3. Re-connection

The user can use the ISessionProxy interface in a similar way as a physical ISession object (see example below). The underlying connection is handled internally and is guaranteed to be open for the lifetime of the ISessionProxy instance. In order to maximize the reliability, an automatic recover policy based on reconnect and failover is applied in case of failure of the connection.

7.3.4. Connection pooling

One of the main task of the ConnectionService is the efficient connection management. The aim is to allow the re-use of already opened connections, in order to minimize on the database server side the CPU consumption required by the creation of new connection. For this pourpose, the connections are kept in two pools:

  • the ACTIVE pool, hosting the connection currently in use by some client of the Connection Service instance. As soon as a new session is opened, the corresponding connection will populate the ACTIVE pool.

  • the IDLE pool, hosting the connection released by all of the clients of the Connection Service instance. As soon as a session is dropped by its user, the corresponding connection will populate the IDLE pool.

The connectiond stored in the IDLE pool can be of course re-used to open new session, by any database user. For Read-Only session, also active connections (associated to sessions already open) can be shared among more proxy sessions, if the request comes from the same database user. At each request for a new Session, first the idle pool is searched. In case no idle connection is found matching the requested connection string, then the active pool is searched. When also in this case no suitable connection is found, a new connection is created from scratch. The session sharing (enabled by default) can be disabled esplicitly in the IConnectionServiceConfiguration.

After a specific timeout the connections stored in the IDLE Pool will be closed. The timeout can be configured by method 'setConnectionTimeOut' in class IConnectionServiceConfiguration. The cleanup of timed out session is internally triggered at each connection and delete pool operation. However, the purging can be forced explicitly at any moment, by calling the method IConnectionService::purgeTimedOutConnection.

7.3.5. Authentication

The authentication is always implicit. The user cannot specify explicitly the database service username and password in the connection method call. The two parameters are retrieved "internally" by the authentication service, where they are stored in entries mapped to the corresponding connection string. Such feature implies that only a single pair user/password can be associated to a given connection string.

7.3.6. Connection Retry

The creation of a new connection, when requested, may fail for a various type of reasons. For some categories of failure, a recovey can be attempted by simply iterating the connection request. Such policy applies to some specific failure, like the ones due to a network glitch, the overloading of the service, or to a short service unavailability. The connection service can distinguish such condition, and apply the connection re-try according to the user configuration. However, for other failure types, like for wrong authentication parameters, the retry will be not applied. The connection is attempted with a specific period of time between each trials (retry period), during a well defined total time (retry timeout) on the given database service. In case of insuccess, the given connection string is registered on an internal list of excluded replicas, for a specific duration of time (exclusion time out). After this timeout, the connection is re-admitted in the replica list. The three parameters retry period, retry timeout and exclusion time can be modified at run time by the user, via the IConnectionServiceConfiguration interface.

7.3.7. Replica management and failover

The Connection Service delegates the resolution of the connection string to an other service, the database lookup service, implementing the ILookupService interface. The lookup service provides a list of possible physical replicas for logical database/service name, with the associated authentication mechanisms and access mode.

For read-only access, the user can define a set of equivalent database service. In this case, more than one physical connection string can be used for the connection and a failover mechanism can be applied. The connection string associated to the replicas are used in the order they are stored, attempting the connection to the first entry, and moving to the next in case of failure. A similar mechanism applies to the re-connect policy, in case an established connection fails during its lifetime.

7.4. Examples

7.4.1. Connection service instantiation

seal::PluginManager* pm = seal::PluginManager::get(); pm->initialise(); seal::Handle<seal::ComponentLoader> loader = new seal::ComponentLoader( context ); loader->load( "CORAL/Services/ConnectionService" ); std::vector< seal::IHandle<coral::IConnectionService> > v_svc; context->query( v_svc ); if ( v_svc.empty() ) { throw std::runtime_error( "Could not locate the connection service" ); } seal::IHandle<coral::IConnectionService> theConnectionService = v_svc.front();

7.4.2. Connection service initialization

IConnectionServiceConfiguration& conf = theConnectionService.configuration(); // disable the failover to the next replica conf.disableReplicaFailOver(); // disable the connection sharing for read only replica conf.disableConnectionSharing(); // disbale the use of update-capable idle connection for read only session conf.disableReadOnlySessionOnUpdateConnections(); // set the connection retrial period conf.setConnectionRetrialPeriod(10); // set the connection retrial time out conf.setConnectionRetrialTimeOut(60); // set the timeout for idle connection conf.setConnectionTimeOut(120); // set the timeout in the exclusion list conf.setMissingConnectionExclusionTime(3600); // set the monitoring level conf.setMonitoringLevel(monitor::Debug); // enforce the usage of myAuthentication service conf.setDefaultAuthenticationService("myplugins/MyAuthenticationService");

7.4.3. Access to a database service

// connect to a database described by a logical connection string try { std::string connectionString = "myDatabase"; // connect in update mode ISessionProxy* session = theConnectionService->connect( connectionString, Update ); // start an update transaction session->transaction().start(); // create a table in the nominal schema coral::TableDescription descr; descr.setName("MYTABLE"); descr.insertColumn("N_X",coral::AttributeSpecification::typeNameForType<int>()); descr.insertColumn("N_S",coral::AttributeSpecification::typeNameForType<std::string>()); session->nominalSchema().createTable( descr ); // commit changes session->transaction().commit(); // delete the session proxy (the connection will be not dropped) delete session; // reconnect in read-only mode - the same connection will be re-used session = theConnectionService->connect( connectionString, ReadOnly ); // start a read-only transaction session->transaction().start( true ); std::set<std::string> tables = session->nominalSchema().listTables(); session->transaction().commit(); delete session; for(std::set<std::string>::const_iterator iT=tables.begin(); iT!=tables.end();iT++){ std::cout << "Found table:" << *iT << std::endl; } // force clean up of timed-out connections theConnectionService->purgeConnectionPool(); } catch ( const ConnectionServiceException& exc) { std::cout << "ERROR: " << exc.what() << std::endl; }

7.4.4. Increasing the failover coverage

// connect to a database described by a logical connection string std::string connectionString = "myDatabase"; int maxtries = 5; ISessionProxy* session = 0; bool success = false; bool give_up = false; int itry = 0; while( !success && !give_up ){ try { itry++; session = theConnectionService->connect( connectionString, ReadOnly ); // start a read-only transaction session->transaction().start( true ); std::set<std::string> tables = session->nominalSchema().listTables(); session->transaction().commit(); delete session; session = 0; success = true; // do something... } catch (const ConnectionServiceException& exc){ give_up = true; delete session; session = 0; std::cout << "ERROR:" << exc.what() << std::endl; } catch (const SessionException& exc){ if(itry==maxtries) give_up = true; } }

7.5. ConnectionService Reference

7.5.1. class IConnectionService

7.5.1.1. Members
  • ISessionProxy* connect( const std::string& connectionName, AccessMode accessMode = Update );

    Parameters:

    •  connectionName 
      : the logical or physical connection string

    •  accessMode 
      : the access mode capabilities of the connection. Possible values: ReadOnly, Update. Default: Update

    Effects: Delivers a valid session proxy object, constructed around an active connection to the requested service. The ownership of the session object is fully controlled by the user code.

    Returns: The pointer to the session proxy object, constructed by new. The ownership of the session object is fully controlled by the user code.

    Throws: When no valid connection is available, a ConnectionServiceException object is thrown by value.

  • IConnectionServiceConfiguration& configuration();

    Returns: a reference of the internal configuration parameter store. The object can be accesses in read/write mode, for changing and reading the configuration parameters.

    Throws: never throws.

  • void purgeConnectionPool() ;

    Effects: Cleans up the IDLE pool, closing and deleting the connections for which the lifetime reached the timeout. No disconnect is enforced to the other connections in IDLE pool, no action is taken for connection in the ACTIVE pool.

    Throws: never throws.

  • const IMonitoringReporter& monitoringReporter() const ;

    Returns: a reference of the internal monitoring reporter, which can be used for read-only access.

    Throws: never throws.

  • IWebCacheControl& webCacheControl();

    Returns: a reference of the internal object for the control of the web cache. Its usage make sense only for the Frontier back-end plug-in.

    Throws: never throws.

7.5.2. class IConnectionServiceConfiguration

7.5.2.1. Members
  • void enableReplicaFailOver();

    Effects: Enables the failing over to the next available replica in case the first provided by the ILookupService is not available. Otherwise the ConnectionService gives up.

    Postcondition: isReplicaFailoverEnabled() == true.

    Throws: never throws.

  • void disableReplicaFailOver();

    Effects: Disables the failing over to the next available replica in case the first provided by the ILookupService is not available.

    Postcondition: isReplicaFailoverEnabled() == false.

    Throws: never throws.

  • bool isReplicaFailOverEnabled();

    Returns: TRUE if the failing over to the next available replica is enabled. Default value is TRUE.

    Throws: never throws.

  • void enableConnectionSharing() ;

    Effects: Enables the sharing of the same physical connection among more read-only session proxies.

    Postcondition: isConnectionSharingEnabled() == true.

    Throws: never throws.

  • void disableConnectionSharing() ;

    Effects: Disables the sharing of the same physical connection among more read-only session proxies.

    Postcondition: isConnectionSharingEnabled() == false.

    Throws: never throws.

  • bool isConnectionSharingEnabled();

    Returns: TRUE if the connection sharing is enabled. Default value is TRUE.

    Throws: never throws.

  • void enableReadOnlySessionOnUpdateConnections() ;

    Effects: Enables the re-use of idle connections opened in update mode for read-Only sessions.

    Postcondition: isReadOnlySessionOnUpdateConnectionsEnabled == true.

    Throws: never throws.

  • void disableReadOnlySessionOnUpdateConnections() ;

    Effects: Disables the re-use of idle connections opened in update mode for read-Only sessions.

    Postcondition: isReadOnlySessionOnUpdateConnectionsEnabled == false.

    Throws: never throws.

  • bool isReadOnlySessionOnUpdateConnectionsEnabled();

    Returns: TRUE if the re-use of Update connections for Read-Only sessions is enabled . Default value is TRUE.

    Throws: never throws.

  • void setConnectionRetrialPeriod( int timeOutInSeconds ) ;

    Parameters:
    •  timeOutInSeconds 
      : the time out in seconds

    Effects: Sets the period of connection retrials on the requested service (time interval between two retrials).

    Postcondition: connectionRetrialPeriod() == timeOutInSeconds.

    Throws: never throws.

  • int connectionRetrialPeriod();

    Returns: the rate of connection retrials on the requested service (time interval between two retrials). Default value is 10 sec.

    Throws: never throws.

  • void setConnectionRetrialTimeOut( int timeOutInSeconds ) ;

    Parameters:
    •  timeOutInSeconds 
      : the time out in seconds

    Effects: Sets the time out for the connection retrials before the connection service fails over to the next available replica or gives up.

    Postcondition: connectionRetrialTimeOut() == timeOutInSeconds.

    Throws: never throws.

  • int connectionRetrialTimeOut();

    Returns: the rate of connection retrials on the requested service (time interval between two retrials). Default value is 60 sec.

    Throws: never throws.

  • void setConnectionTimeOut( int timeOutInSeconds );

    Parameters:
    •  timeOutInSeconds 
      : the time out in seconds

    Effects: Sets the time out (in seconds) for the IDLE connections.

    Postcondition: connectionTimeOut() == timeOutInSeconds.

    Throws: never throws.

  • int connectionTimeOut();

    Returns: the time out (in seconds) for the IDLE connections. Default value is 300 sec.

    Throws: never throws.

  • void setMissingConnectionExclusionTime( int timeInSeconds );

    Parameters:
    •  timeInSeconds 
      : the time in seconds

    Effects: Sets the time duration of exclusion from failover list for a connection not available.

    Postcondition: missingConnectionExclusionTime() == timeInSeconds.

    Throws: never throws.

  • int missingConnectionExclusionTime();

    Returns: the time duration of exclusion from failover list for a connection not available. Default value is 3600 sec.

    Throws: never throws.

  • void setMonitoringLevel( monitor::Level level );

    Parameters:
    •  level 
      : the monitoring level. Possible values in coral::IMontoring

    Effects: Sets the monitoring level for the new sessions.

    Postcondition: monitoringLevel() == level.

    Throws: never throws.

  • monitor::Level monitoringLevel() ;

    Returns: the current monitoring level. Default is monitor::Off.

    Throws: never throws.

  • void setDefaultAuthenticationService( const std::string serviceName );

    Parameters:
    •  serviceName 
      : the name of the service to be used.

    Effects: Sets the default authentication service to be used for the new sessions. The corresponding plug-in is loaded when necessary, and only if no other implementation of the corresponding interface is found in the context.

    Throws: never throws.

  • void setDefaultLookupService( const std::string serviceName );

    Parameters:
    •  serviceName 
      : the name of the service to be used.

    Effects: Sets the default lookup service to be used for the new sessions. The corresponding plug-in is loaded when necessary, and only if no other implementation of the corresponding interface is found in the context.

    Throws: never throws.

  • void setDefaultRelationalService( const std::string serviceName );

    Parameters:
    •  serviceName 
      : the name of the service to be used.

    Effects: Sets the default relational service to be used for the new sessions. The corresponding plug-in is loaded when necessary, and only if no other implementation of the corresponding interface is found in the context.

    Throws: never throws.

  • void setDefaultMonitoringService( const std::string serviceName );

    Parameters:
    •  serviceName 
      : the name of the service to be used.

    Effects: Sets the default monitoring service to be used for the new sessions. The corresponding plug-in is loaded when necessary, and only if no other implementation of the corresponding interface is found in the context.

    Throws: never throws.

7.5.3. class ISessionProxy

7.5.3.1. Members
  • ISessionProperties& properties();

    Returns: a reference of the internal property store, containing read-only data related to the underlying connection. Since the underlying connection is probed before the object info construction, it always contains valid data.

    Throws: ConnectionServiceException if the underlying connection becomes invalid and cannot be recovered. InvalidSessionProxyException if the associated Connection service is unavailable.

  • ISchema& nominalSchema();

    Returns: a reference to the object describing the working schema of the connection. Since the underlying connection is probed before the object info construction, it is always a valid schema.

    Throws: ConnectionServiceException if the underlying connection becomes invalid and cannot be recovered. InvalidSessionProxyException if the associated Connection service is unavailable.

  • ISchema& schema();

    Returns: a reference to the object describing the specified schema. Since the underlying connection is probed before the object info construction, it is always a valid schema.

    Throws: ConnectionServiceException if the underlying connection becomes invalid and cannot be recovered. InvalidSessionProxyException if the associated Connection service is unavailable.

  • ITransaction& transaction();

    Returns: a reference to the object describing the transaction. Since the underlying connection is probed before the object info construction, it is always a valid transaction.

    Throws: ConnectionServiceException if the underlying connection becomes invalid and cannot be recovered. InvalidSessionProxyException if the associated Connection service is unavailable.

  • ITypeConverter& typeConverter();

    Returns: a reference to the type converter of the active session. Since the underlying connection is probed before the object info construction, it is always a valid type converter.

    Throws: ConnectionServiceException if the underlying connection becomes invalid and cannot be recovered. InvalidSessionProxyException if the associated Connection service is unavailable.

  • bool isConnectionShared();

    Returns: TRUE if the underlying connection is shared among more session proxies. To be used to control read-only transaction of shared sessions. Only the last remaining session (the one having isConnectionShared()== false ) must call the transaction commit.

    Throws: InvalidSessionProxyException if the associated Connection service is unavailable.

7.5.4. class ISessionProperties

7.5.4.1. Members
  • std::string flavorName() ;

    Returns: the name of the RDBMS flavour for the underlying connection.

    Throws: never throws.

  • std::string serverVersion();

    Returns: the version of the database server of the underying connection

    Throws: never throws.

7.5.5. class IWebCacheControl

7.5.5.1. Members
  • void refreshSchemaInfo( const std::string& connection );

    Parameters:
    •  connection 
      : the connection name

    Effects: Instructs the RDBMS backend that all the tables within the schema specified by the physical or logical connection should be refreshed, in case they are accessed.

    Throws: never throws.

  • void refreshTable( const std::string& connection, const std::string& tableName );

    Parameters:
    •  connection 
      : the connection name

    •  tableName 
      : the specific table name

    Effects: Instructs the RDBMS backend that the specified table within the schema specified by the physical or logical connection should be refreshed in case it is accessed.

    Throws: never throws.

  • const IWebCacheInfo& webCacheInfo( const std::string& connection ) const ;

    Parameters:
    •  connection 
      : the connection name

    Returns: the web cache information for a schema given the corresponding physical or logical connection.

    Throws: never throws.

  • int compressionLevel();

    Returns: the current compression level in use.

    Throws: never throws.

  • void setCompressionLevel( int level );

    Parameters:
    •  level 
      : the compression level: 0 - off, 1 - fast, 5 - default, 9 - maximum

    Effects: Sets the compression level for data transfer

    Throws: never throws.

  • void setProxyList( const std::vector<std::string>& proxyList );

    Parameters:
    •  proxyList 
      : the list of the web cache proxies

    Effects: Sets the list of the web cache proxies for the plug-in internal fail-over mechanism

    Throws: never throws.

7.5.6. class IWebCacheInfo

7.5.6.1. Members
  • bool isSchemaInfoCached() const ;

    Returns: TRUE if the schema info (data dictionary) is cached, i.e.it does not need to be refreshed

    Throws: never throws.

  • bool isTableCached( const std::string& tableName ) const;

    Parameters:
    •  tableName 
      : the specific table of the schema

    Returns: TRUE if a table in the schema is cached, i.e.it does not need to be refreshed

    Throws: never throws.

7.5.7. class IWebCacheInfo

7.5.7.1. Members
  • std::set< std::string > monitoredDataSources() const ;

    Returns: the set of currently monitored data sources.

    Throws: never throws.

  • void report( unsigned int level=coral::monitor::Default ) const;

    Parameters:
    •  level 
      : the monitoring level used as a filter

    Effects: Reports the events for all data sources being monitored.

    Throws: never throws.

  • void report( const std::string& contextKey, unsigned int level=coral::monitor::Default ) const ;

    Parameters:
    •  contextKey 
      : the data source name

    •  level 
      : the monitoring level used as a filter

    Effects: Reports the events for a given data source name of a given monitoring level.

    Throws: never throws.

  • void reportToOutputStream( const std::string& contextKey, std::ostream& os, unsigned int level=coral::monitor::Default ) const ;

    Parameters:
    •  contextKey 
      : the data source name

    •  os 
      : the reference the output stream to be used.

    •  level 
      : the monitoring level used as a filter

    Effects: Reports the events for a given data source name to the specified output stream.

    Throws: never throws.

7.6. RelationalService Semantics

The RelationalService component is responsible for managing the IRelationalDomain objects that are delivered to the user. Given a connection string corresponding to an actual database service, it extracts the technology name and returns the corresponding IRelationalDomain object. It also loads if necessary into its local context the plugin library that provides the implementation of the specific technology.

By default, for a technology named rdbmsName, it will try to load a plugin labeled:

CORAL/RelationalPlugins/rdbmsName
which should correspond to an implementation based on the base (most commonly C) API of this RDBMS technology. In case no such plugin exists, it will try to load any plugin with a label of a format:
CORAL/RelationalPlugins/rdbmsName/anImplementationName

The user may chose to use a specific implementation, instead of the default or any other existing non-default implementation, either by giving a call to the setDefaultImplementationForDomain() method of the IRelationalService interface, or by setting accordingly the DefaultImplementations property of this component.

7.7. RelationalService Reference

7.7.1. Properties of the "CORAL/Services/RelationalService" component

  • DefaultImplementations : defines the list of default implementations for the given technology. Each entry should have the format

    technologyName:implementationName

7.8. OracleAccess Semantics

7.8.1. The logical data hiererchy

In order to connect to an Oracle database using CORAL one should specify a connection string which typically has a format such as

oracle://databaseServerName:port/schemaName/serviceName
where the port number is optional.

The schema name is in practice the user name of an oracle user. A user has access to the tables defined in the schema of another user for which all the relevant privileges have been granted.

The database server name refers either to the logical name which should appear as an entry in the $(TNS_ADMIN)/tnsnames.ora file, or an actual server hosting the database.

7.8.2. Controlling Oracle-specific parameters

7.8.2.1. Table spaces

New tables, indices and LOBs are created in the default table space of the user. This may not always be the optimum behaviour for a user who may wish that indices especially are created in different table spaces. There are three ways which allow the user specify the table space for new tables and indices:

  1. By setting the environment variables CORAL_ORA_TS_TAB, CORAL_ORA_TS_IND and CORAL_ORA_TS_LOB to the name of the table space for new tables, indices and LOBs respectively.

  2. By setting the properties TableSpaceForTables, TableSpaceForIndices and TableSpaceForLobs of the IRelationalDomain component "CORAL/RelationalPlugins/oracle" to the name of the table space for new tables, indices and LOBsrespectively.

  3. By overriding the default values (empty string) in the relevant methods of the CORAL API with the actual ones.

7.8.2.2. LOB handling

When reading back a LOB object from the database, this is done in chunks which have a default size of 1024 bytes (1 KB). This default value can changed by setting the property LobChunkSize or the environment variable CORAL_ORA_LOB_CHUNK_SIZE accordingly.

7.8.2.3. Enabling SQL TRACE

A user can trigger the generation of a trace file in the database server, whenever he/she authenticates, by setting the environment variable CORAL_ORA_SQL_TRACE_ON to any value.

In case the environment variable CORAL_ORA_CERN_TRACE_ON is set, it takes precedence over CORAL_ORA_SQL_TRACE_ON and executes the CERN_TRACE package in the server, which has to be already installed on the server by the system administrators. This also triggers the generation of the trace file, and whenever the user session is ended the trace file is sent by mail to the address stored in the environment variable CORAL_ORA_CERN_TRACE_ON.

7.9. OracleAccess Reference

7.9.1. Properties of the "CORAL/RelationalPlugins/oracle" component

  • TableSpaceForTables : defines the table space where tables are created. It takes its default value from the environment variable CORAL_ORA_TS_TAB. If not specified neither there, nor in the CORAL API, the user default is used.

  • TableSpaceForIndices : defines the table space where indices are created. It takes its default value from the environment variable CORAL_ORA_TS_IND. If not specified neither there, nor in the CORAL API, the user default is used.

  • TableSpaceForIndices : defines the table space where LOBs are created. It takes its default value from the environment variable CORAL_ORA_TS_LOB. If not specified neither there, nor in the CORAL API, the user default is used.

  • LobChunkSize : defines the size in bytes of the chunks when reading a LOB from the database. It takes its default value from the environment variable CORAL_ORA_LOB_CHUNK_SIZE. If not specified a default of 1024 bytes (1KB) is used.

7.9.2. Other environment variables

  • CORAL_ORA_SQL_TRACE_ON : if set to any value, it enables the generation of a trace file on the database server.

  • CORAL_ORA_CERN_TRACE_ON : if set, it enables the generation of a trace file on the database server, which is sent to the e-mail address defined in this variable.

7.10. SQLiteAccess Semantics

7.10.1. The logical data hiererchy

In order to connect to a SQLite database file using CORAL one should specify a connection string which typically has a format such as

  sqlite_file:filename.db
  sqlite_file:///home/dbfiles/f1.db
In the case the contact string has the format: sqlite_file:my.db

-if SQLITE_FILE_PATH variable is set, SQLiteAccess read/write my.db in this dir, exception if the dir does not exist;

-if SQLITE_FILE_PATH variable is not set, SQLiteAccess read/write my.db in the currentdir

In the case the contact string has the absolute path format:

  sqlite_file:///tmp/my.db
the SQLITE_FILE_PATH variable is not taken into consideration

There is zero configuration required to read/write data into sqlite files.

The schema name is fixed as "main".

sqlite database doesnot have its own SQL types. SQLiteAccess defines the following SQL to c++ type mapping: BOOLEAN(bool), CHAR(char), UNSIGNEDCHAR(unsigned char), SHORT(short), UNSIGNEDSHORT(unsigned short), INT(int), UNSIGNEDINT(unsigned int), LONG(long), UNSIGNEDLONG(unsigned long), ULONGLONG(unsigned long long), SLONGLONG(long long), FLOAT(float), DOUBLE(double), LONGDOUBLE(long double), TEXT(std::string), BLOB(coral::blob), DATE(coral::date), TIMESTAMP(coral::timestamp)

Note: if the SQL type in a sqlite database is not found in this list, SQLiteAccess will NOT recognise it.

7.11. SQLiteAccess Reference

7.11.1. Properties of the "CORAL/RelationalPlugins/sqlite" component

There's no specific parameters to be set for the sqlite domain.

7.12. MySQLAccess Semantics

7.12.1. The logical data hiererchy

In order to connect to an MySQL database using CORAL one should specify a connection string which typically has a format such as

 
   mysql://databaseServerName:port/schemaOrDbName
  
where the port number is optional (default is port number 3306).

The schema name is in practice the same as MySQL user account name. A user has access to the tables defined in the schema of another user for which all the relevant privileges have been granted.

7.12.2. Controlling MySQL-specific parameters

7.12.2.1. Table spaces

New tables, indices and LOBs are created in the default table space as set in the MySQL server configuration file. This can't be changed yet in the 4.0.x version. In 4.1.x version one can enable tablespace per table feature but it's not controllable parameter on the client side so this can't be in sync with OracleAccess plug-in.

7.12.2.2. (C)LOB handling

When reading back a LOB object from the database server we rely entirely on MySQL machinery. We might change it in the future to perform retrieval in chunks with configurable size if we see a gain in performance. There is an important client & server side parameter max_alowed_packet_size which specifies the maximum size for the transfer of CLOB/BLOB data. Our server setting in /etc/my.cnf is:

 
     set-variable=max_allowed_packet=21000000
   
which means roughly 21MB maximum size of CLOB/BLOB data per one transfer. If the size is exceeded MySQL will issue an error. We do not set anything yet in CORAL for the client side.

7.12.2.3. Silent type conversions

When a column is created and its size is more then 255 characters the silent (VAR)CHAR conversion into CLOB type happens. CORAL does it already for 4.0, MySQL itself does it from version 4.1.6 onwards. Since we will provide 4.1 & later support in CORAL soon we consider it a safe behavior.

If user defines a column of type std::string without specifying the size and whether it is fixed or not the SQL type becomes VARCHAR(255) for this column defintion. It is better to be more explicit when defining table column description.

7.12.2.4. Silent index creation

The silent creation of indices for foreign key columns happens in the referencing table to avoid the MySQL error:

   ERROR 1005 (00000): Can't create table './DbName/TableName.frm' (errno: 150)
   
The reason is that MySQL performs renaming of the altered table and if there is no index created it does not know anymore about the column and issues an error. Beware, this is valid so far for the InnoDB tables (the CORAL default engine). This error might not appear for MyISAM tables as for this engine MySQL apparently parses the SQL for FK constraints but ignores it completely. Note that MySQL does not maintain referential integrity for MyISAM tables.

7.12.2.5. CLOB/BLOB unique constraints, indexes & implications

The implication for unique constraints on CLOB/BLOB columns is that the mandatory prefix must be used for index creation otherwise there is MySQL error:

   ERROR 1170 (00000): BLOB column 'ColumnName' used in key specification without a key length
   
For configuring this prefix see MySQLAccess ReferenceManual.

Foreign key constraints of the CLOB/BLOB types are not supported, it's just pure craziness! The primary keys are allowed to be of a CLOB type which is crazy as well but building a foreign key in addition is a complete madness. Doing it just doubles the total storage requirements at least not saying that updating such tables for many rows will take the whole life-time.

The index prefix is not supported for foreign key constraints. There is simply too much restrictions being applied and is very complex to build DDL for it. The golden rule is to make sure the referenced key & the foreign key are the same type and size to avoid surprises.

7.12.2.6. Enabling SQL TRACE

There are some ways to enable debugging of MySQL server to see what a particular session is doing but it does not seem yet as a systematic activity performed by a standard user and is more targeted to MySQL developers and testers. This has not been investigated yet in detail as there was no strong interest from our user community to do it.

7.13. MySQLAccess Reference

7.13.1. MySQLAccess environment variables

  • CORAL_MYSQL_CLOB_PREFIX_LEN : if set to a numeric value in the range from 0 to 255, it affects the orefix size of the index prefix of all the index column of the CLOB MySQL types like TINYTEXT, TEXT, MEDIUMTEXT & LONGTEXT. If not set the default built-in prefix size is 32 bytes/chars.

7.14. FrontierAccess Semantics

7.14.1. The logical data hierarchy

In order to connect to an Oracle database using Frontier one should specify a connection string which typically used to have the format as

    frontier://FrontierServerName:port/serviceBaseURL/SCHEMA_NAME
   
which gets translated automatically into valid Frontier URL in form:
    http://FrontierServerName:port/serviceBaseURL
   
where the port number is optional but one has to pay attention to it as the same connection string can differ only in port number which may indicate that the server is cache instead of an application server.

The schema name is in practice the user name of an oracle user. A user has access to the tables defined in the schema of another user for which all the relevant privileges have been granted. Frontier application server usually connects only via one acount, so when the tables are created in an Oracle database to which Frontier application server connects, then is required that the proper SELECT access rights on these tables are granted to the Frontier user, otherwise FrontierAccess will not work.

The FrontierAccess now decodes and passes down to the underlying frontier_client library the new conneciton string format which may look like:

    frontier://(proxyurl=http://srv.fnal.gov:3128)(proxyurl=http://srv15.fnal.gov:3128)
               (serverurl=http://frontier.cern.ch:8000/Frontier)(serverurl=http://frontier1.cern.ch:8000/Frontier)
               (retrieve-ziplevel=5)
              /SCHEMA_NAME
   
The complex connection string now contains all the parameters to let the frontier_client library to perform load-balancing and fail-over itself and configure the compression level used for data transfer.

7.14.2. Controlling Frontier-specific parameters

7.14.2.1. Refreshing cache contents

FrontierAccess is primarily going to access the cached information. There are, however, situations where it is not desired or a fresh information is needed. This typically happens when database schema has been updated. In order to make sure that FrontierAccess uses the most recent database objects it forces a cache to be refreshed for data dictionary queries. For the the data queries the cache access is enabled & preferred. In cases where there is a need to refresh cache contents due to updates in the back-end databases one can control the refresh behavior by using coral::IWebCacheControl API. This interface allows to inform the FrontierAccess plug-in to execute its queries with refresh parameter enabled, thus causing the refresh of all the Squid caches on the way up to a Frontier server. The access to the coral::IWebCacheControl interface is via coral::ConnectionService component. Once the web cache control handle is obtained one can force reloading of a whole database schema, e.g. the queries involving any table in the given schema will be executed with refresh enabled or one can do finer selection by registering specific table names so the FrontierAccess plug-in enables refresh only for the queries which involve any of the registred tables.

Imagine the following data source setup using CORAL data service lookup mechanism reading input from dblookup.xml file:

    
    <?xml version="1.0" ?>
     <servicelist>
       <logicalservice name="/coral/CoolFrontier">
         <service name="frontier://afrontier.cern.ch:8080/Frontier/CORAL" accessMode="readonly" />
       </logicalservice>
     <servicelist>
    
    
then the application code needs to do the following to switch on refresh for the all queries to schema CORAL as follows:
      // Locate the CORAL ConnectionService component
      seal::IHandle<coral::IConnectionService>
      connectionService = appcontext->query<coral::IConnectionService>( "CORAL/Services/ConnectionService" );
      // Setup refresh
      coral::IWebCacheControl& webcache = connectionService->webCacheControl();
      // Set refresh for the whole schema, e.g. each query in this schema will skip Squid cache
      // NOTE! One must specify the physical connection string as the plugins don't know about the logical ones!!!
      webcache.refreshSchemaInfo( "http://atlasfrontier.cern.ch:8080/Frontier" );
    
Please note the fact that one needs to specify the real physical connection string being actually used to connect to the Frontier server and with the "frontier://" prefix being already rewritten as "http://". The same applies for the new complex conneciton string syntax just the "http://" will not be there because is not needed anymore. One can select also the particular tables in the target schema to become subject to refresh for each query run against them. See the API of coral::IWebCacheControl interface.

7.14.2.2. Enabling Frontier client debug log

A user can trigger the more verbose log output, by setting the environment variable FRONTIER_LOG_LEVEL=Debug.

7.15. FrontierAccess Reference

7.15.1. Other environment variables

  • FRONTIER_LOG_LEVEL : if set to "Debug", it enables the debug log output for the frontier client.

7.16. XMLAuthenticationService Semantics

The XMLAuthenticationService component ("CORAL/Services/XMLAuthenticationService") provides an implementation of the IAuthenticationService interface which is based on the parsing of an XML input file, from where a list of known connection strings is read. For each connection string a user name and a password are specified in the file.

In addition to the credentials corresponding to the default database role, one may define several roles for a given connection string.

The XML file should have a format like the following:


<connectionlist>

<connection name="technology1://host1/db1">
  <parameter name="user" value="dbuser1" />
  <parameter name="password" value="dbpasswd1" />
</connection>

<connection name="technology2://host2/db2">
  <parameter name="user" value="dbuser2" />
  <parameter name="password" value="dbpasswd2" />

  <role name="writer">
    <parameter name="user" value="dbuser2_writer" />
    <parameter name="password" value="dbpasswd2_writer" />
  </role>

  <role name="updater">
    <parameter name="user" value="dbuser2_updater" />
    <parameter name="password" value="dbpasswd2_updater" />
  </role>

</connection>
.
.
.
</connectionlist>

By default the file name of the authentication file that is read is called authentication.xml. This however can be changed by setting accordingly the value of the component's property named AuthenticationFile.

If the authentication file name does not correspond to an absolute path, the system will try to search it in the current working directory and then in the directories defined in the path environment variable CORAL_AUTH_PATH.

If the authentication file is found in multiple places in the directories specified by CORAL_AUTH_PATH, then repeated entries are not overriden, but the first one found is kept.

7.17. XMLAuthenticationService Reference

7.17.1. Properties of the "CORAL/Services/XMLAuthenticationService" component

  • AuthenticationFile : defines the name of the XML input file. Default value is authentication.xml

7.17.2. Environment variables

  • CORAL_AUTH_PATH : a path environment variable, where the XML input file is searched for. The current working directory has always higher priority.

7.18. EnvironmentAuthenticationService Semantics

The EnvironmentAuthenticationService component ("CORAL/Services/EnvironmentAuthenticationService") provides an implementation of the IAuthenticationService interface which is based on the use of two environment variables. In particular the database user name and password are retrieved from the environment variables CORAL_AUTH_USER and CORAL_AUTH_PASSWORD respectively, completely ignoring what the actual connection string is.

This implementation is provided mainly to facilitate easy development of CORAL-based applications.

7.19. XMLLookupService Semantics

The XMLLookupService is responsible for logical-physical mapping and is providing list of possible replica based on logical database/service name, authentication method and access mode in which database will be accessed.

Service specific parameters can be specified as child nodes of the service entries (see example below).

The XML file should have a format like the following:


<servicelist>

  <logicalservice name="myservice1">
    <service name="oracle://host1/schema1" accessMode="update" authentication="password" />
    <service name="oracle://host2/schema1" accessMode="readonly" authentication="password" />
    <service name="oracle://host3/schema1" accessMode="update" authentication="certificate" />
  </logicalservice>

  <logicalservice name="myservice2">
    <service name="oracle://host1/schema2" accessMode="readonly" authentication="password certificate" />
    <service name="oracle://host2/schema2" accessMode="readonly" authentication="certificate" />
    <service name="oracle://host3/schema2" accessMode="update" authentication="certificate password" />
  </logicalservice>

  <logicalservice name="myservice3">
    <service name="oracle://host1/schema2" accessMode="update" authentication="password" >
      <parameter name="ConnectionRetrialPeriod" value="60" />
      <parameter name="ConnectionRetrialTimeOut" value="360" />
      <parameter name="ConnectionTimeOut" value="3000" />
    </service>
  </logicalservice>

</servicelist>


The dblookup.xml file is searched for in the current working directory and, if not found, the directories defined in the CORAL_DBLOOKUP_PATH environment path variable.

XMLLookup service returns a list of possible replica as object of class IDatabaseServiceSet defined in RelationalAccess package. Through IDatabaseServiceSet information can be accessed about number of available replicas, replica connection string, allowed access mode and authentication procedure of originating site.

7.20. XMLLookupService Reference

7.20.1. Properties of the "CORAL/Services/XMLLookupService" component

  • Properties of Lookup File : The name of the file can be set through property manager of the service. The authentication methods include options of password or certificate, depending on originating site. Access Mode can be 'readonly' or 'update'.

7.20.2. Environment variables related to the "CORAL/Services/XMLLookupService" component

  • CORAL_DBLOOKUP_PATH : a path environment variable, where the XML input file is searched for. The current working directory has always higher priority.

7.20.3. Connection related service parameters

Currently three parameters are supported (see ConnectionService documentation for their meaning and usage):

  • ConnectionRetrialPeriod.

  • ConnectionRetrialTimeOut.

  • ConnectionTimeOut.

7.21. LFCLookupService Semantics

The LFCLookupService is responsible for logical-physical mapping and for providing the list of possible replicas based on logical service name, authentication method and access mode. The lookup is done from LFC server set through the environment variable LFC_HOST.

LFCLookupService returns a list of possible replicas as object of class IDatabaseServiceSet defined in RelationalAccess package. Through IDatabaseServiceSet information can be accessed about number of available replicas, replica connection string, allowed access mode and authentication procedure.

7.22. LFCLookupService Reference

7.22.1. Properties of the "CORAL/Services/LFCLookupService" component

  • Strategy of Lookup

    An interface of ILookupService is implemented to perform the logical-physical mapping by providing the list of available replicas from the LFC server with specified authentication method and access mode. Authentication Method is a string, specifying the method used for authentication (parameter to the lookup function, which can be password or certificate). Access Mode can be read-only or update (parameter to the lookup function, default is update).

7.22.2. Command line tools

A set of LFCDbLookupService administrating tools API has been provided for the management of mapping information stored in LFC. These command line tools are implemented using LFC APIs for adding, removing, listing the replica entries for specified logical connection string and to export the replica entries of a logical connection string into XML file. The following command line tools are available:

  • coral_add_db_replica- Adds a new replica for the specified logical connection string. Usage of the CLI:

    coral_add_db_replica

    • [-h contact_string] (can be skipped if environment variable LFC_HOST is set)

    • -l logical_connection_string (mandatory parameter)

    • -p physical_replica (mandatory parameter)

    • [-r ] (optional for read-only replica, default is "update")

    • [-a authentication_method] (optional, default is "password")

  • coral_remove_db_replica- Removes an existing replica or all replicas mapped to a logical connection string. Usage of the CLI:

    coral_remove_db_replica

    • [-h contact_string] (can be skipped if environment variable LFC_HOST is set)

    • -l logical_connection_string (mandatory parameter)

    • [-p physical_replica] (optional, only removes the specified replica. If not specified, the entire replica entries for the logical_connection_string are removed)

  • coral_list_db_replicas- Lists available replicas mapped to a logical connection string - with optional filters. Usage of the CLI:

  • coral_list_db_replicas

    • [-h contact_string] (can be skipped if environment variable LFC_HOST is set)

    • [-l logical_connection_string] (optional filter for a specified logical connection string. If logical connection string is not specified, replicas for all logical connection strings available on the host are listed.)

    • [-r] (optional filter for read-only, only replicas with read-only mode are listed)

    • [-u] (optional filter for update, only replicas with update mode are listed)

  • coral_export_db_catalogue- Exports the db catalogue - Extracts the subset of dbreplica catalogue creating xml files in the format like dblookup.xml. Usage of the CLI:

  • coral_export_db_catalogue

    • [-h contact_string] (can be skipped if environment variable LFC_HOST is set)

    • [-f file name] (optional, default is dblookup.xml)

    • [-l logical_connection_string] (optional filter for a specified logical connection string. If logical connection string is not specified, replicas for all logical connection strings available on the host are exportted to the xml file.)

    • [-r] (optional filter for read-only, only replicas with read-only mode are exported)

    • [-u] (optional filter for update, only replicas with update mode are exported)

7.23. LFCReplicaService Semantics

7.23.1. Architecture

The LFCReplicaService is a database replica catalogue using an LFC server as a storage backend. The translation of the LFC stored data to the database-related information is implemented in the client-side code, based on the LFC C-API client. Each database replica is stored with its associated information, including the authentication credential (pair username+password), database server host name, status of the server. In order to protect the access to the replica entry for the disclosure of the associated authentication credentials, the LFCReplicaService fully uses the LFC built-in security scheme permission. The LFC uses the Grid (proxy) certificates extended with the VOMS information to establish the identity of the client, and regulates the access to the file entries with a file-system like access permission scheme, which includes ACL's. In order to adopt this security scheme, the LFCReplicaService defines a database catalogue entry (=a logical file name on the LFC side) for each role to be coupled to a credential pair.In such a way the role entry, represented by an entry in the LFC file system, can be protected for the access using the standard LFC security scheme, allowing only the intended grid roles or groups to access the file and disclose the authentication credentials.

7.23.2. LFC Server Connection

The LFC C-API client does not allow an explicit connection with a given LFC server. The connection is established implicitly, at any function call accessing the LFC name server. The LFC host is specified with the environment variable LFC_HOST. In order to be authenticated into a secure LFC name server, the user needs a valid grid certificate to be available in the execution context. In case of missing valid certificate, the LFC will return the error message: "send2nsd: NS002 - send error : No valid credential found".

7.23.3. Deployment of CORAL database catalogue into an existing LFC server

In order to use an existing LFC server as a database catalogue, very little deployment operation is required. Since the operation of the database entries involves the management of the database authentication credentials, it is assumed that it will be performed by a super-user, acting as database catalogue administrator. The first action required is the definition of the database catalogue area in the LFC file system. By default, the LFCReplicaService assumes the folder /database as a root base folder for the database entries. Optionally, a user defined folder can be assumed as root, by setting the environment variable CORAL_LFC_BASEDIR. In both cases, the corrisponding folder has to be created and set with the necessary access permission RWX to the db catalogue administrator. In order to add a replica set, the administrator will run the coral_replica_manager -add command, specifying all the required parameters. The entry created will be by default not accessible to the world. In order to let a specific grid identity (role or group) access the database entry, the db administrator has to run the coral_replica_manager -set_perm command for all the group entitled to access the replicas.

Example:

  • logical connection name: /atlas/ecal/calib

  • physical connection string: oracle://ecal_calib/calib_writer

  • username: calib_reader

  • password: xyz2006

  • database server: atlr

  • coral role: writer

  • to be visible for grid role: offline_reader

the commands necessary:

coral_replica_manager -add -l /atlas/ecal/calib -c oracle://ecal_calib/calib_writer 
                           -r writer -u calib_reader -p xyz2006 

coral_replica_manager -set_perm -l /atlas/ecal/calib -r writer -g offline_writer -ro

7.23.4. Storage of database password in the LFC server

In order to avoid storage of clear text password in the LFC tables, a simple key-based mechansim of encryption has been implemented.

7.24. LFCReplicaService Reference

7.24.1. class ILookupService

7.24.1.1. Members
  •   

    IDatabaseServiceSet* lookup( const std::string& logicalName, coral::AccessMode accessMode = coral::Update, std::string authenticationMechanism = "" ) const;

    Parameters:

    •  logicalName 
      : the logical connection string, which can be described by a file absolute or relative path, with an arbitrary complex folder hierarchy. The root base folder in the LFC filesystem is fixed to /database by default, and can be moved to other location using the environment variable CORAL_LFC_BASEDIR.

    •  accessMode 
      : the access mode capability of the connection. Possible values are items of the coral::AccessMode enumeration. Default value is Update, which means that with no parameter specified, only the Update capable connection will be considered.

    •  authenticationMechanism 
      : the authentication mechanism required by the physical connections. Default value is an empty string, which will be interpreted by applying no filter to the available connections (all the mechansim are considered.). In the LFC replica service, only two mechansim can be currently associated to the physical connections: "password", to be used for credential based authentication, and "none", when no authentication is required.

    Effects: Resolves the specified logical connection string, looking up the LFC database catalogue according to the access permission of the underlying grid role or group. In case of successfull access, a data structure is filled with the associated replica information, while in the service memory the are stored the authentication credentials required by all of the physical connections involved in the selection.

    Returns: A user owned pointer to the data structure holding the replica list. The free of this memory allocation does not have any effect on the service.

7.24.2. class IAuthenticationService

7.24.2.1. Members
  • IAuthenticationCredential& credentials( const std::string& connectionString ) const;

    Parameters:

    •  connectionString 
      : the physical connection string requiring the authentication parameters.

    Effects: This method call DOES NOT trigger any access to the LFC catalogue. It only delivers a reference to the already existing authentication data for the default role (named coral_default_role), loaded by the previous last lookup call. Any call to this method which is not anticipated by a lookup call, will throw an Authentication exception, because no data will be present in the service credential store.

    Returns: A reference to the current credential store loaded in the service memory. It contains valid data only after a call of the corresponding lookup method. The role looked up for the credential pairs (username+password) is the default role (named coral_default_role).

  • IAuthenticationCredential& credentials( const std::string& connectionString, const std::string& role ) const;

    Parameters:

    •  connectionString 
      : the physical connection string requiring the authentication parameters.

    •  role 
      : the role defining the identity for the authentication.

    Effects:Same effect as for the above method.

    Returns:Same return value as the above method, selecting the credential pair associated to the specified role.

7.24.3. Command line tools

A set of LFCDbLookupService administrating commands has been provided for the management of mapping information stored in LFC. These command line tools are implemented using LFC APIs for adding, removing, listing the replica entries (with the accociated credential informaton) for specified logical connection string and to export the replica entries of a logical connection string into XML file. All of the available commands are issued with a single executable:

coral_replica_manager

The usage sintax is:

coral_replica_manager [COMMAND] [PARAMETER_0]... [PARAMETER_N]

The available commands:

  • -add ( --add ) : Adds a new replica entry for the specified logical connection string and role Usage:

    -add

    • -l lcs ( the logical connection string to be mapped to the replica )

    • -c pcs ( the physical connection string defining the replica )

    • [-h host] ( the database host of the replica - if not specified is extracted from the physical connection string)

    • [-r role] ( the role associated to the replica credential pairs - default=coral_default_role )

    • [-u username]( the username associated to the replica )

    • [-p password]( the password associated to the replica )

    • [-np]( no authentication credential associated to the replica )

    • [-ro]( read-only capable physical connection )

    • [-rw]( update capable physical connection - default )

    • [-help]( help for the add command )

  • -ls ( --list ) : Lists available replicas mapped to a logical connection string - with optional filters. Usage:

    -ls

    • [-h host] ( select replicas with the specified database host)

    • [-l lcs ] ( select replica of the the specified logical connection string (or path )

    • [-c pcs ] ( select replicas with the sepcified physical connection string )

    • [-r role]( selecting replicas with the credential associated to the specified role )

    • [-np]( select replicas with no authentication required )

    • [-u username]( select replicas with the specified username in the credential pair )

    • [-s ]( scan option - recursively scan inner folders )

    • [-ro]( select replicas with read-only capability )

    • [-rw]( select replicas with update capability )

    • [-on]( select replicas with status on )

    • [-off]( select replicas with status off )

    • [-help]( help for the list command )

  • -set ( --set_param ) : Change the status or the passoword values to the specified replica(s) Usage:

    -set

    • -h host ( select replicas with the specified database host)

    • [-u username]( select replicas with the specified username in the credential pair )

    • [-p password]( the new password associated to the replica, replacing the existing one. )

    • [-on]( the new value for the replica status=on )

    • [-off]( the new value for the replica status=off )

    • [-help]( help for the set command )

  • -del ( --delete ) : Removes the existing replica(s) mapped to a logical connection string. Usage:

    -del

    • -l lcs ( select replica(s) of the the specified logical connection string (or path )

    • [-h host] ( select replicas with the specified database host)

    • [-c pcs ] ( select replicas with the sepcified physical connection string )

    • [-r role]( selecting replicas with the credential associated to the specified role )

    • [-np]( select replicas with no authentication required )

    • [-u username]( select replicas with the specified username in the credential pair )

    • [-s ]( scan option - recursively scan inner folders )

    • [-ro]( select replicas with read-only capability )

    • [-rw]( select replicas with update capability )

    • [-on]( select replicas with status on )

    • [-off]( select replicas with status off )

    • [-help]( help for the del command )

  • -val ( --validate_param ) : Validate the specified passoword against the current value for the specified replica(s) Usage:

    -val

    • -h host ( select replicas with the specified database host)

    • -u username( select replicas with the specified username in the credential pair )

    • -p password( the password associated to the replica, to be validated. )

    • [-help]( help for the val command )

  • -set_perm ( --set_permission) : Set the specified access permission for the specified LFC group entry (mapped to a Grid role or group), to the specified logical connection string and role. Usage:

    -set_perm

    • -g group ( the LFC group entry to be granted )

    • -l lcs ( the logical connection string to access )

    • -r role ( the role associated to the credential pair)

    • [-na] ( grant no access permission )

    • [-rw]( grant read-write access permission )

    • [-ro]( grant read-only access permission )

    • [-wo]( grant write only access permission )

    • [-help]( help for the set_perm command )

  • -exp ( --export ) : Export available replicas and associated credential information into xml files, in the format supported by XMLLookupService and XMLAuthenticationService, with optional filters. Usage:

    -exp

    • [-h host] ( select replicas with the specified database host)

    • [-l lcs ] ( select replica of the the specified logical connection string (or path )

    • [-c pcs ] ( select replicas with the sepcified physical connection string )

    • [-r role]( selecting replicas with the credential associated to the specified role )

    • [-np]( select replicas with no authentication required )

    • [-u username]( select replicas with the specified username in the credential pair )

    • [-s ]( scan option - recursively scan inner folders )

    • [-ro]( select replicas with read-only capability )

    • [-rw]( select replicas with update capability )

    • [-on]( select replicas with status on )

    • [-off]( select replicas with status off )

    • [-af authentication_file]( specify the name of the authentication file - default=authentication.xml )

    • [-lf lookup_file ]( specify the name of the lookup file - default=dblookup.xml )

    • [-help]( help for the exp command )

  • -imp ( --import ) : Import available replicas and associated credential information from xml files, in the format supported by XMLLookupService and XMLAuthenticationService. Usage:

    -imp

    • [-af authentication_file]( specify the name of the authentication file - default=authentication.xml )

    • [-lf lookup_file ]( specify the name of the lookup file - default=dblookup.xml )

    • [-help]( help for the exp command )

  • -help ( --help ) : Displays the general help of the tool Usage:

    -help

7.25. MonitoringService Semantics

7.25.1. The logical data hierarchy

The monitoring service is not used directly by application code but rather steered via user level IMonitoring interface. Only CORAL plugins use this service directly to record any monitoring event when it happens.

7.25.2. Controlling MonitoringService parameters

The events recording may happen at different levels configurable via coral::IMonitoring user level interface. The level determines the granurality of the recored information from informational up to the low level details about various steps performed by CORAL plugins to be used mostly for debugging purposes.

7.26. MonitoringService Reference

7.26.1. MonitoringService environment variables

There are no environment variables defined yet. Stay tuned.

7.27. CoralCommon Semantics

7.27.1. Shared implementations for the Authentication Service components

The package provides the definition and implementation of the AuthenticationCredentials class, which is a transient implementation of the IAuthenticationCredentials interface. It provides shared code for the various implementations of the IAuthenticationService interface.

7.27.2. Shared implementations for the Lookup Service components

The DatabaseServiceSet class is a transient implementation of the IDatabaseServiceSet interface. It can be used as a shared implementation in components providing implementations of the ILookupService interface, where the result set of a lookup query is fully retrieved before in can be made available to the client. This is a behaviour which is expected to be always valid for any ILookupService implementation.

7.27.3. Shared implementations for the Monitoring Service components and the related developer-level interfaces

The developer level interfaces and default implementations related the the client-side monitoring are defined within the coral::monitor namespace. This namespace defines:

  • The state-less class MonitoringEventDescription and the singleton object monitoringEventDescription, which is used for the definition of the string literals corresponding to a monitoring event. The RDBMS-specific plugins use its methods for retrieving this string literals instead of hard-coded strings.
  • The namespace Event, where the Record and Payload classes are defined. The latter are used as shared code by the various plugins providing implementations for the IMonitoringService interface.
  • The enumeration ReportType, which is used by the various implementations of the IMonitoringService interface.

7.27.4. A URI parser for the connection strings

The package provides a simple parser for processing the database connection strings supplied to the IRelationalService and/or IConnectionService interfaces by the client. The URIParser class extracts from a valid connection string the RDBMS technology name, the optional protocol, the host name, the optional port number, and the name which defines the schema/service name. In case the supplied connection string is not valid or cannot be parsed, a URIException object is thrown. The URIParser class is also used in the plugins providing the RDBMS-specific implementations.