Conditions DB

1
Conditions DB

• Current Status of
• Oracle Implementation

31.01.2002 Emil Pilecki
2
General Proposals

• Implementation of the agreed ConditionDB
  interface,
• with minimal changes for client code
• Changes in connect string and DB info string
  (when creating conditionDBs)
• Implementation choices
• Oracle 9i server compatibility
• Use relational data model
• DB access through the OCCI interface(transparent
  to users)
• Performance optimization for reading conditions
  data (most frequent operation - queries)

3
Why Relational Data Model?

• Data model is simpler
• Sufficient for Conditions Data
• Well known and reliable
• Less storage overhead
• Less client-side processing

4
Relational Design (ERD)Folders, Folder Sets and
Condition Objects
Possible data relation
Necessary data relation
One to many relation
Foreign key is a part of primary key for that table
Attribute is a part of primary key
Attribute cannot be null
o Null value allowed for this attribute
r Attribute is a foreign key
u Attribute is a part of Unique constraint
Folder_set folder_set_id name o description o
attributes r parent_set_id
Condition_object object_id since till
insertion_time layer o description r data_id r
folder_id
Folder folder_id name o description o
attributes r parent_set_id
5
Relational Design (ERD)Tags
Folder_set folder_set_id name o description o
attributes r parent_set_id
Folder_tag assignment_time r tag_id r
folder_id
Folder folder_id name o description o
attributes r parent_set_id
6
Description of Data Model

• Folder_Sets hierarchical table stores full
  structureof detectors parts
• Folder table stores information about conditions
  relatedwith detectors parts
• Condition_Objects table stores intervals for
  conditions
• Condition_Data table stores data for intervals
  (separated because several intervals can share
  the same data)
• Tags table stores information about all tags
• Folder_Sets_Tags, Folder_Tags, Object_Tags
  tablesstores information about assignment of
  tags to folder, folder sets and objects

7
Performance issues

• How to improve performance?
• Enhance data model
• Tune up the database

8
Enhance data model for performance(choice
between performance and storage overhead)

• Materialized views for data that is frequently
  accessed
• Views for folder paths and folder sets paths
• View for intervals that forms current head
• Partitioning option for Condition_Objectsand
  Condition_Data tables
• Use of indexes

9
Server-side processing

• PL/SQL stored procedures used for data
  insert/modify operations to speed them up and
  minimize network traffic
• Better performance as data insert/modify
  operationsare are faster when processed on the
  server.
• Creating Folder Sets, Folders, Tags, Condition
  Objects, taggingand untagging each can be done
  with one PL/SQL procedurecall and so only one
  interaction with database.
• Exception mechanism used for server-side
  errors.Exceptions cought and re-thrown on client
  side as ConditionDBException.

10
Client-side processing

• OCCI embedded SQL used for querying and creating
  condition databases
• Embedded SQL can easily run any SQL query
• Multiple Conditions DB feature possible with
  dynamic SQL

11
Current status

• Completed part of implementation
• Database server side PL/SQL procedures
• Client side ( C interface implementation )
• Connection ConditionsDB creation
• Folder Set Folder Management
• Condition Objects creation
• Set up of Sun environment
• In progress
• Tags
• Set up of Linux environment and deployment

12
Further plans

• Condition Objects finding, retrieval, browsing
  10.02.2002
• Tags management 20.02.2002
• First complete release (alpha) end of February
  2002
• Acceptance test expected from users
• Production release afterwards

13
Summary

• No changes in interface behavior
• Relational data model
• OCCI for database access
• Some optimization ideas for fast reading
• First production release available in March