FairDB Virtual Database Denis Bertini GSI - Scientific Computing - - PowerPoint PPT Presentation

▶
fairdb virtual database
SMART_READER_LITE
LIVE PREVIEW

FairDB Virtual Database Denis Bertini GSI - Scientific Computing - - PowerPoint PPT Presentation

Apr 20, 2023 42 likes •335 views

FairDB Virtual Database Denis Bertini GSI - Scientific Computing November 19, 2014 Outline FairDB 1 Design Supported Databases FairRoot Integration Features 2 Connectivity Versioning SQL I/O Parameter Class Error Handling R3B-Califa

slide-1
SLIDE 1

FairDB Virtual Database

Denis Bertini

GSI - Scientific Computing

November 19, 2014

slide-2
SLIDE 2

Outline

1

FairDB

Design Supported Databases FairRoot Integration

2

Features

Connectivity Versioning SQL I/O Parameter Class Error Handling

3

R3B-Califa Example

ROOT-TGeo to SQL-DB conversion

4

User Manual (PDF)

5

Summary

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 2 / 29

slide-3
SLIDE 3

FairDB Problem Statement

Which Database?

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 3 / 29

slide-4
SLIDE 4

FairDB Design

FairDB Design

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 4 / 29

slide-5
SLIDE 5

FairDB SQL Databases supported

FairDB Supported Databases

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 5 / 29

slide-6
SLIDE 6

FairDB FairRoot Integration

FairDB within FairRoot

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 6 / 29

slide-7
SLIDE 7

Connectivity Concepts

Connectivity

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 7 / 29

slide-8
SLIDE 8

Connectivity Configuration

Configuration

JDBC style: defines a list of DB URLs, a user name and a password Configuration via environment variables of type FAIRDB TSQL * Configuration example:

export FAIRDB TSQL URL= " mysql://localhost/r3b; pgsql://localhost:5432/R3B; sqlite://test.sqlite" export FAIRDB TSQL USER= "usr mysql;usr postgres;usr sqlite" export FAIRDB TSQL PSWD= "passwd1;passwd2;passwd3";

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 8 / 29

slide-9
SLIDE 9

Connectivity Multiple Connections

Multiple Connections

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 9 / 29

slide-10
SLIDE 10

Version Management Concepts

Concepts

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 10 / 29

slide-11
SLIDE 11

Version Management Metadata Auxiliary Table

Version Management

Query process:

1

Condition (timestamp, detector, version) as primary key

2

Condition converted to a unique seq id

3

seq id used as key to access all relevant rows in the data table

4

User get access to all fetched rows

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 11 / 29

slide-12
SLIDE 12

SQL I/O I/O Components

I/O Components

Data Organization

Condition: event date, time, version, detector Interval: a condition extended to a time window ObjToTableMap: Object mapped to single row in a table Single or Composite: (a set of ObjToTableMap: sharing a Interval)

I/O process Write by Interval of Validity (IoV)

Use case: do calibrate channel in crate , estimate interval of validity which it remains valid and store in the DB

Read using a Condition

Use case: given a condition, get all calibration constants for every channel in every crate. Caching mechanism: cache owns query results for reuse, caller just gets const pointer.

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 12 / 29

slide-13
SLIDE 13

SQL I/O Templated SQL I/O

Templated SQL I/O

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 13 / 29

slide-14
SLIDE 14

Parameter Class FairParSet Redesigned

FairParSet Redesigned

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 14 / 29

slide-15
SLIDE 15

Parameter Class SQL-Aware Parameter Class

SQL-Aware Parameter Class

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 15 / 29

slide-16
SLIDE 16

Parameter Class Data Types

Data Types

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 16 / 29

slide-17
SLIDE 17

Error Handling Connection Info

Connection Info

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 17 / 29

slide-18
SLIDE 18

Error Handling SQL Info

SQL Info

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 18 / 29

slide-19
SLIDE 19

Error Handling RollBack

Rollback

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 19 / 29

slide-20
SLIDE 20

Getting Started Availability

Availability

External packages March 2014

Root with DB plugins New PostGreSQL Driver ROOT v5-34-15 or higher. https://subversion.gsi.de/fairroot/fairsoft/trunk/

Use the git version of FairRoot base

git clone https://github.com/FairRootGroup/FairRoot

Example directory containing the FairDB tutorial5 adapt the FairDB config script dbconfig.sh

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 20 / 29

slide-21
SLIDE 21

Getting Started Parameter Example

FairDbTutPar

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 21 / 29

slide-22
SLIDE 22

Getting Started Writing Macro

sql param write.C

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 22 / 29

slide-23
SLIDE 23

Getting Started Relational Model

Data and Metadata Tables

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 23 / 29

slide-24
SLIDE 24

Getting Started Reading Macro

sql param read.C

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 24 / 29

slide-25
SLIDE 25

R3B Database ROOT to DB conversion

Example: ROOT (TGeo) to DB automatic conversion

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 25 / 29

slide-26
SLIDE 26

R3B Database Crystal Nodes Tables

Crystal Nodes Tables

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 26 / 29

slide-27
SLIDE 27

Documentation User Manual

User Manual

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 27 / 29

slide-28
SLIDE 28

Documentation Contents

Contents

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 28 / 29

slide-29
SLIDE 29

Summary

Summary

FairDB implements a uniform access to any SQL database

It does not fix the DBMS technology

Independent of the FairRoot framework

Supports PostgreSQL, MySQL, SQLite, Oracle

User Manual (available in PDF format) Ongoing work

Implementing Realistic tests R3B DB projects : Califa-NeuLand, Epics, LandO2 to R3BRoot

Online Database concept

Multi-level and ditributed Caching mechanism (NoSQL based) Using MQ Methods SQL-RDBMS used as archiving engine

Denis Bertini (Scientific Computing) ICC/SPIRAL2 Computing Meeting November 19, 2014 29 / 29