Transcript Status of CORAL - Indico

Ioannis Papadopoulos
CERN-IT-PSS
Status of CORAL
...and the public release version 1.0.0
LCG Applications Area Meeting
Wednesday 23/11/2005
1
What is CORAL
●
●
●
●
POOL/RAL (Relational Abstraction Layer) +
POOL/AttributeList repackaged/improved/extended
An outcome of the POOL/RAL functional review held
last spring
a C++, SQL-free, technology-independent API for
accessing and manipulating RDBMS schemata and data
a software system providing hooks for components
addressing the specific needs of deployment and
distribution of relational data (service indirection, secure
authentication mechanisms, client-side monitoring, clientside connection pooling, etc.)
2
C++, SQL-free API (I)
Example 1: Table creation
coral::ISchema& schema = session.nominalSchema();
coral::TableDescription tableDescription;
tableDescription.setName( “T_t” );
tableDescription.insertColumn( “I”, “long long” );
tableDescription.insertColumn( “X”, “double” );
schema.createTable( tableDescription);
Oracle
CREATE TABLE “T_t” ( I NUMBER(20),
X BINARY_DOUBLE)
MySQL
CREATE TABLE T_t
( I BIGINT,
X DOUBLE PRECISION)
3
C++, SQL-free API (II)
Example 2: Issuing a query
coral::ITable& table = schema.tableHandle( “T_t” );
coral::IQuery* query = table.newQuery();
query->addToOutputList( “X” );
query->addToOrderList( “I” );
query->limitReturnedRows( 5 );
coral::ICursor& cursor = query->execute();
Oracle
SELECT * FROM
( SELECT X FROM “T_t” ORDER BY I )
WHERE ROWNUM < 6
MySQL
SELECT X FROM T_t ORDER BY I LIMIT 5
4
The POOL/RAL review
●
●
Held in spring 2005 with input from direct RAL users
(POOL components, COOL, experiment applications)
Revised existing and proposed new functionality
–
Reviewed methods and classes
●
–
Improved DDL, DML and Queries
●
●
–
Inefficient or unnecessary methods, classes doing too much,...
Finer control of variable definition (especially in strings)
Support for BLOBs, generalized bulk operations, INSERT/SELECT
statements, set operations
Addressed the needs of distributed deployment
●
Client-side monitoring
●
Service indirection
●
Connection pooling and failover
5
CORAL version 1.0.0
●
First public release
●
●
–
Based on SEAL_1_7_6
●
●
●
●
–
●
●
●
2 packages for the public API (100 kB + 170 kB)
1 internal package for common code (70 kB)
9 plugin packages (2.8 MB in total)
3 integration tests exercising the full API
Can co-exist with POOL/RAL
–
–
●
Plugin manager through component model
MessageStream
64-bit integer types
Timers
Contents:
●
●
/afs/cern.ch/sw/lcg/app/releases/CORAL/CORAL_1_0_0
slc3_ia32_gcc323, slc3_ia32_gcc323_dbg, win32_vc71_dbg
Classes in different namespaces (coral instead of pool)
Different library names (lcg_coral_PackageName)
Includes all of the existing functionality in current POOL/RAL
–
and implements most of the new functionality of the RAL review.
6
CORAL Architecture
7
CORAL API Packages
●
CoralBase
–
Types (a simple Blob type)
–
Row buffers (a new AttributeList)
–
Base exception class
●
●
coral::Exception : public seal::Exception
RelationalAccess
–
Abstract interfaces modeling the access patterns to RDBMS
–
Abstract interfaces for related services
●
authentication, monitoring, service indirection, connection pooling and
service failover
–
Exception hierarchy
–
A simple transient class to describe a table
8
CORAL API Highlights
●
Bulk insert operations
From POOL/RAL
→ round-trips to the server are minimized.
●
Use of bind variables
→ SQL parsing on the server is avoided.
●
Client-side caching of query results (row pre-fetching)
→ round-trips to the server are minimized when fetching the
result set of a query.
●
Optimizations and “best practices” implemented in the
RDBMS plugins
→ users may concentrate on the functionality of their
own use cases.
●
Implementations based on the SEAL component model
9
CORAL API Highlights
●
Improved DDL
–
●
finer control of variable definition
●
String size/variability
●
Tablespaces for BLOBs
–
multi-column unique constraints
–
support for read-only views
New in CORAL
Improved DML
–
–
generalized bulk operations:
●
generalized to support update/delete operations
●
improved resource utilization on the database server
support for INSERT/SELECT statements
●
Support for BLOB I/O and set operations in queries
●
Full exception hierarchy
●
Interfaces for Service indirection, connection pooling
and client-side monitoring
10
RDBMS-plugins
●
●
●
Oracle
–
Based on OCI (instant client) v. 10.1.0.3-1
–
Full implementation of the CORAL API and its semantics
SQLite
–
Based on sqlite C API v. 3.2.1
–
Minor limitations are solved in more recent sqlite versions.
MySQL
–
Based on MySQL C API v. 4.0.24
●
will upgrade soon to 4.1 and later to 5.0
–
Sub-selects and true bind variables as of v. 4.1
–
Views as of v. 5.0
11
Authentication mechanisms
●
Architectural choice to separate data location from identification
–
–
●
A contact string: technology://host/schema
An RDBMS plugin asks the loaded IAuthenticationService implementation
for the credential parameters corresponding to “technology://host/schema”
Two implementations
–
Environment-based
●
–
User/password pair from CORAL_AUTH_USER / CORAL_AUTH_PASSWORD
XML-based
<connectionlist>
<connection name="technology://host/schema">
<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" />
</connection>
</connectionlist>
12
Client-side Monitoring
●
●
●
RDBMS plugins push information to the loaded
implementation of the IMonitoringService interface
–
Start/end of connection or user session
–
Begin/end/abort transaction
–
Time taken to execute a statement (idle time on client)
Information data are sent together with a source
identifier (i.e. the connection string)
CORAL provides a simple implementation
–
mainly to serve as an example
–
coupling to existing monitoring services from the experimentspecific infrastructure is expected to be a rather simple task
13
Database service indirection
●
The ILookupService interface acts like a “file catalog” for database
services
–
For a given “logical service name” return the available replicas saticfying the
criteria on
●
●
●
The access pattern (read-only or update)
The authentication scheme (user/passwd pair, certificate)
CORAL provides an XML-based implementation
<servicelist>
<logicalservice name=“/the/conditions/database">
<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=“/the/collections/database">
<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>
</servicelist>
14
Connection pooling & service failover
●
Responsibilities of an implementation of the
IConnectionService interface:
–
Connection pooling
●
Minimize open connections to a database server
–
●
–
●
Re-attempts physical connection if dropped
Fails over to another available replica
Overall system configuration
●
●
●
Open connections are re-used
Provide ISessionProxy objects
●
–
One writer and several readers share the same physical connection
Loads the required Authentication, Lookup, Relational and Monitoring
services
User may override default policies for timeout and failover processes
Current implementation in CORAL:
–
–
Re-used effort & code from ATLAS
Should be used as the entry point to the CORAL system
15
Connecting to a database schema (I)
Give me a session handle
for /my/logical/database
IConnectionService
This is a logical connection string.
I have to load and ask an
ILookupService implementation
Give me a valid connection string
for /my/logical/database
ConnectionService
ILookupService
Try the following connection
strings with this order:
oracle://oradb/oraschema
mysql://myhost/mydb
sqlite_file:///dbfile.db
I have to look it up
in my XML file
XMLLookupService
16
Connecting to a database schema (II)
Give me an IRelationalDomain for
oracle://oradb/oraschema
ConnectionService
IRelationalService
Here you are!
Is there any plugin labeled
CORAL/RelationalPlugins/oracle
?
RelationalService
OracleAccess
IRelationalDomain
17
Connecting to a database schema (III)
Connect to oracle:://oradb/oraschema, authenticate,
start the monitoring and give me a valid session handle
ConnectionService
IRelationalDomain
I have managed to connect,
but now I need the
authentication credentials
What is the user name and password
for oracle:://oradb/oraschema ?
OracleAccess
IAuthenticationService
Try user1 and passwd1
I have to look it up
in my XML file
XMLAuthenticationService
18
Connecting to a database schema (IV)
Record the start time of this session!
IMonitoringService
IRelationalDomain
OracleAccess
MonitoringService
Here is a valid session handle!
Great! I do not need to
try another replica!
ConnectionService
ISession
IRelationalDomain
Registering event for
oracle://oradb/oraschema
Here is your session handle!
Or simply:
coral::ISessionProxy* session= connectionService->connect(“/my/logical/database”);
19
Documentation
●
●
●
http://pool.cern.ch/coral/
Re-used machinery from POOL to create
documentation from the xml fragments (docbook DTD)
tagged together with the source code
Deliverables generated automatically during the
release procedure
–
User Guide and Component Descriptions
–
Instructions, guidelines, etc.
●
–
●
all of them are parts of the User Guide
Doxygen documentation
Accessing the CVS repository:
http://coral.cvs.cern.ch/
20
Outlook
●
Migrate POOL Components to CORAL
●
Help users migrate from POOL/RAL to CORAL
●
Provide Frontier plugin
●
A grid-enabled ILookupService implementation?
●
React early on the feedback from users
–
so that the software is mature enough for next
year’s data data challenges
21
The ones to hammer if something does
not work according to specs
(a.k.a. the CORAL Team)
●
Radovan Chytracek
–
●
Dirk Düllmann
–
●
architecture, base components and services, Oracle
Yulia Shapiro
–
●
infrastructure, POOL-ORA requirements
Ioannis Papadopoulos
–
●
3D requirements
Giacomo Govi
–
●
MySQL, Monitoring, Frontier
connection and lookup services
Zhen Xie
–
SQLite, POOL-ORA requirements
22