Transcript Fellow Seminar

Evaluation of the
C++ binding to the
Oracle Database System
Dirk Geppert and Krzysztof Nienartowicz, IT/DB
CERN IT Fellow Seminar November 20, 2002
Outline
Report on work performed during IT/DB fellowship in 2001
• Part I: Dirk
• Functionality tests
• Part II: Krzysztof
• Benchmarks and Performance issues
IT/DB Oracle tutorials
OO database, C++ and Java, 20 Feb 2003
http://wwwinfo.cern.ch/db/events.html
Presentations at HEP database project workshops
29-31 Jan 2002 and 11-13 Jul 2001
http://hep-proj-database.web.cern.ch/hep-proj-database/
Dirk Geppert
IT Fellow Seminar, November 20, 2002
2
Outline Part I
•
•
•
•
•
•
Motivation
Short intro to OCCI & OTT
OTT usage
Object models
Problems & Improvements
Summary & Conclusions
Dirk Geppert
IT Fellow Seminar, November 20, 2002
3
Motivation
• Functionality and flexibility for a persistent
event store of HEP data provided
(object models & access patterns)?
• Oracle database
• OCCI & OTT
• CERN member of Oracle OCCI beta test program
• Close contact to
Oracle development
• Oracle Metalink
• Early releases,
access to bug fixes
Dirk Geppert
9.0.0.0 beta
April 2001
9.0.1.0
June 2001
9.0.1.3 (patch set)
December 2001
9.2.0.0 beta
February 2002
9.2.0.1 beta
March 2002
9.2.0.1
May 2002
9.2.0.2 (patch set)
October 2002
IT Fellow Seminar, November 20, 2002
4
Oracle C++ Call Interface: OCCI
• Oracle C++ Call Interface (API)
• C++ binding on top of C binding (wrapper): a ‘better OCI’
• ANSI C++, STL
• JDBC like
• Features:
• Relational programming
- Associative access to Relational Data
- SQL DDL, DML, queries, PL/SQL
• Object programming
- Associative & Navigational access to objects
• Advanced features:
• thread safety, connection pooling, complex object retrieval,
metadata, client-side object cache, …
Dirk Geppert
IT Fellow Seminar, November 20, 2002
5
Object Type Translator: OTT
SQL DDL
2
INTYPE file
1
OTT
Type
Definitions
MAPFILE file
CPPFILE file
Database
Server
3
HFILE file
OCCI Source
4
Compiler
OCCI library
Linker
Object file
5
Executable
Dirk Geppert
IT Fellow Seminar, November 20, 2002
6
OTT standard vs. alternative usage
User types (transient) already exist: no modifications wanted
• Do not touch x_C,
extensions to user class
• Existing code has to be modified
• Run OTT once, post-process
• Customize streamer
(readSQL, writeSQL):
use transient data members
• No modifications to existing code,
only to OTT generated classes
• Additional advantage:
client memory space
Dirk Geppert
IT Fellow Seminar, November 20, 2002
PObject
C++
Vertex_C
Vertex
x,y,z
SQL
Vertex_O
x,y,z
PObject
C++
Vertex_C
Vertex
x,y,z
7
Object models
• Implementation of existing HEP data model
• Provide typical OM, but: keep it simple
• test OCCI/OTT functionality
• Choice: NOMAD RAW & DST
•
•
•
•
Different RAW models (VArray, LOB)
Subset of DST (showing all required functionality)
Read data from ZEBRA
Populate/read event data to/from OracleDB using OCCI
successful
Dirk Geppert
IT Fellow Seminar, November 20, 2002
8
NOMAD object model
Dirk Geppert
IT Fellow Seminar, November 20, 2002
9
NOMAD event display
• Existing NOMAD DST event display
• Root GUI reading ZEBRA event store
• Store events in OracleDB,
use OCCI for object retrieval
• Keep Root GUI
• OCCI demo for Oracle Open World 2001
in San Francisco
Dirk Geppert
IT Fellow Seminar, November 20, 2002
10
Dirk Geppert
IT Fellow Seminar, November 20, 2002
11
Dirk Geppert
IT Fellow Seminar, November 20, 2002
12
Problems & Improvements
Problems reported (as bug or enhancement request);
some solved, some fixed in future Oracle (minor) releases
• SQL
o No abstract data types, wait for Oracle 10i
• OTT

o
o
o
o
Member initialization
set & get inlined
Private-Protected members
Forward declarations
readSQL return type
(inherited instead of base persistent type)
post-process (with set of simple scripts)
Dirk Geppert
IT Fellow Seminar, November 20, 2002
13
Problems & Improvements
cont’d
• OCCI
 VARRAY, LOB and String data types
 Embedded objects: no destructor called
 Client side object cache
 Memory leaks
 Objects were created on heap,
now ‘new with placement’ into cache;
embedded objects still on heap
 Control for cache size
o Control for garbage collection
considerable progress,
would like to extend functionality/API
Dirk Geppert
IT Fellow Seminar, November 20, 2002
14
Summary & Conclusions
• OTT
Flexibility & functionality not sufficient for HEP
but: generated code can be used as template
and easily extended/modified to fit needs
• OCCI
Significant progress during IT/DB beta testing
- From Oracle 9.0 to 9.2 (~ 1year)
- Oracle contacts (e.g. development) very cooperative
Required functionality for HEP persistency is available
- Implemented object models are non-trivial
- Associative access to relational
- Navigational access to database objects
Ready to use for e.g. ConditionsDB, LCG catalogs
Dirk Geppert
IT Fellow Seminar, November 20, 2002
15
SQL  OTT  C++
create type ARRAY_O
as varray(1000) of number
/
create type A_O as OBJECT (
n number, a ARRAY_O, p REF A_O)
/
create table A_T of A_O;
PObject
A_C
type A_O
generate A_C as A
insert into A_T
values (A_O(0,null,null));
SQL
Dirk Geppert
Number n
Vector< Number > a
Ref< A > p
A
OTT
IT Fellow Seminar, November 20, 2002
OCCI
16
Generated C++ classes
class A_C : public oracle::occi::Pobject {
private:
// attributes …
public:
// set & get attributes …
Persistent
Object
transient
void *operator new(size_t size);
void *operator new(size_t size, void *ctxOCCI_);
void *operator new(size_t size, const oracle::occi::Connection * sess,
const OCCI_STD_NAMESPACE::string& table);
OCCI_STD_NAMESPACE::string getSQLTypeName() const;
persistent
A_C();
A_C(void *ctxOCCI_) : oracle::occi::PObject (ctxOCCI_) { };
static void *readSQL(void *ctxOCCI_);
virtual void readSQL(oracle::occi::AnyData& streamOCCI_);
static void writeSQL(void *objOCCI_, void *ctxOCCI_);
SQL
virtual void writeSQL(oracle::occi::AnyData& streamOCCI_);}
Dirk Geppert
IT Fellow Seminar, November 20, 2002
17
Example Session
Environment *env=Environment::createEnvironment(Environment::OBJECT);
RegisterMappings(env);
Connection *conn=env->createConnection(“scott”,”tiger”,””);
get a reference
Statement *stmt=conn->createStatement();
ResultSet *resultSet=stmt->executeQuery(“select REF(t) from a_T t where n=0”);
if (ResultSet::DATA_AVAILABLE == resultSet->next()) {
Ref<A> ref=resultSet->getRef(1);
}
conn->commit();
stmt->closeResultSet(resultSet);
create/modify/delete
objects
conn->terminateConnection(conn);
Environment::terminateEnvironment(env);
easy to use
Dirk Geppert
IT Fellow Seminar, November 20, 2002
18
Typical usage
Creating new objects
modify
Ref<A> ref=resultSet->getRef(1);
ref_next=new(conn,”A_T”) A();
ref->setP(ref_next); // forward
ref->markModified();
persistent
Example navigation
Ref<A> ref=resultSet->getRef(1);
do {
reference
ref->dump();
dereference,
pin
Dirk Geppert
A *ptr=ref.ptr(); // if needed
} while (!((ref=ref->getP()).isNull()));
IT Fellow Seminar, November 20, 2002
pointer
19